3
sqlserver2016 内连接查询有索引居然table scan
source link: https://www.oschina.net/question/1864890_2322962
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
sqlserver2016 内连接查询有索引居然table scan
(sql简化过了，所以业务上可能不需要关联，请忽略)
两张表tab_order_info，tab_order_info_goods，tab_order_info中id为主键（非聚集索引），tab_order_info_goods的order_id就是tab_order_info的id，也加了非聚集索引，CODE_TS同样加了非聚集索引。
sql如下：
SELECT
COUNT (1) AS num
FROM
tab_order_info_goods t1,
tab_order_info t2
WHERE
t1.order_id = t2.id
GROUP BY
t1.CODE_TS
两张表的数据都是500W，执行居然要60S左右，查看执行计划tab_order_info_goods 显示table scan
但是我确认 tab_order_info_goods的order_id和CODE_TS是加了索引的，tab_order_info的id是主键也肯定是有索引，
sql这么简单为啥出现table scan呢？？？求解了
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK