EXPLAIN SELECT * FROM buy_log WHERE userid = 2;
优化器选择了使用单个列的聚集索引,因为这个索引的叶子结只有1个键值,也就是这个结点能存放更多的键值(一页有16KB,然后一个叶子结点就是一页)
可以看到选择器选择了userid_2这个索引,也就是联接索引,因为在这个联合索引中,buy_date是已经排序好了,如果使用这个索引进行取出数据,就无需再对buy_date做一次额外的排序操作(可以看到在Extra列上,只有使用了index scan和index,并没有using filesort)。
强制使用单一列来索引的话
EXPLAIN SELECT * FROM buy_log FORCE INDEX(userid
) WHERE userid = 2 ORDER BY buy_date DESC LIMIT 3;
可以看到Extra里拥有using filesort,即表明了需要额外的一次排序才能完成查询,而这次排序明显是对buy_date进行排序。
覆盖索引
InnoDB存储引擎支持覆盖索引,或称为索引覆盖,即从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录(一般辅助索引里面存放索引键值,然后还要根据索引键值去查找聚集索引),即叶子结点已经包含了想要的数据,不需要再去聚集索引中找完整的数据,引用覆盖索引的好处是辅助索引不包含整行记录的所有信息,故其大小会远小于聚集索引,因此可以减少IO操作。
对于InnoDB存储引擎的辅助索引而言,叶子结点是包含主键信息的,所以页中存储的内容大概是(primary key 1,primary key 2…,key 1,key 2)这样的数据,所以下列的SQL语句都可以直接查询这些数值出来,而不需要额外去找聚集索引
SELECT key2 FROM table WHERE key 1 = xxx;
SELECT primary key2,key2 FROM table WHERE key 1 = xxx;
SELECT primary key1,key2 FROM table WHERE key1 = xxx;
SELECT primary key1,primary key2,key1,key2 FROM table WHERE key1 = xxx;
覆盖索引的另一个好处就是,辅助索引会小于聚集索引(由于每条行数据的列不全),可以减少IO操作。
用上面的bug_log表为例
EXPLAIN SELECT COUNT(*) FROM buy_log;
Using index其实就是覆盖索引操作
此外,一般来说对于诸如(a,b)这样的联合索引,一般不可以选择b列作为查询条件,但如果是对于统计操作,会选择覆盖索引来进行优化。
INDEX HINT
MySQL数据库支持INDEX HINT(索引提示),显示地告诉优化器使用哪个索引,下面这两种情况可能需要使用到INDEX HINT。
-
MySQL数据库的优化器错误地选择了某个索引(这种情况比较少见,优化器在绝大部分情况下工作都是正确的),导致SQL语句运行得很慢。这时候就需要强制使用正确的索引
-
某SQL语句可以选择的索引非常多,这时优化器选择执行计划时间的开销可能会比较大,当大于执行SQL本身的时候,就需要强制使用某个索引了。
如何使用INDEX HINT
<