覆盖索引
覆盖索引(covering index)指只需要从辅助索引中就可以得到查询记录,而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息,故其大小远小于聚集索引,因此可以减少大量IO操作。
对于InnoDB存储引擎的辅助索引而言,由于其包含了主键信息,因此其叶子节点存放的数据为(primary key1, primary key2, … key1, key2, …)。例如,下列语句都可仅使用一次辅助联合索引来完成查询:
覆盖索引的另一个好处是对某些统计问题而言的。
创建一个buy_log表
CREATE TABLE buy_log{ userid INT UNSIGNED NOT NULL, buy_date DATE }ENGINE=InnoDB; ALTER TABLE buy_log ADD KEY(userid); ALTER TABLE buy_log ADD KEY(userid, buy_date);
对于表 buy_log
,要进行如下查询:
SEKECT COUNT(*) FROM buy_log;
InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于 buy_log
表上还有辅助索引,而辅助索引远小于聚集索引,选择辅助索引可以减少IO操作,故优化器的选择如图。
如图可知,possible_keys列为NULL,但是实际执行时优化器却选择了userid索引,而列Extra列的Using index就是代表了优化器进行索引覆盖操作。
此外,在通常情况下,诸如 (a, b)
的联合索引,一般是不可以选择列b中所谓的查询条件。但是如果是统计操作,并且是覆盖索引的,则优化器会进行选择。
优化器选择不使用索引的情况
在某些情况下,当执行EXPLAIN命令进行SQL语句的分析时,会发现优化器并没有选择索引去查找数据,而是通过扫描聚集索引,也就是直接进行全表扫描来得到数据。这种情况多发生于范围查找、JOIN连接操作等情况下。
对于 不能进行索引覆盖的情况,优化器选择辅助索引的情况是,通过辅助索引查找的数据是少量的。
MySQL查询不使用索引汇总
众所周知,增加索引是提高查询速度的有效途径,但是很多时候,即使增加了索引,查询仍然不使用索引,这种情况严重影响性能,这里就简单总结几条MySQL不使用索引的情况
如果MySQL估计使用索引比全表扫描更慢,则不使用索引。例如,如果列key均匀分布在1和100之间,下面的查询使用索引就不是很好:select * from table_name where key>1 and key<90;
如果使用MEMORY/HEAP表,并且where条件中不使用“=”进行索引列,那么不会用到索引,head表只有在“=”的条件下才会使用索引
用or分隔开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及到的索引都不会被用到,例如:select * from table_name where key1=‘a’ or key2=‘b’;如果在key1上有索引而在key2上没有索引,则该查询也不会走索引
复合索引,如果索引列不是复合索引的第一部分,则不使用索引(即不符合最左前缀),例如,复合索引为(key1,key2),则查询select * from table_name where key2=‘b’;将不会使用索引
如果like是以‘%’开始的,则该列上的索引不会被使用。例如select * from table_name where key1 like ‘%a’;该查询即使key1上存在索引,也不会被使用
如果列为字符串,则where条件中必须将字符常量值加引号,否则即使该列上存在索引,也不会被使用。例如,select * from table_name where key1=1;如果key1列保存的是字符串,即使key1上有索引,也不会被使用。
从上面可以看出,即使我们建立了索引,也不一定会被使用,那么我们如何知道我们索引的使用情况呢??在MySQL中,有Handler_read_key和Handler_read_rnd_key两个变量,如果Handler_read_key值很高而Handler_read_rnd_key的值很低,则表明索引经常不被使用,应该重新考虑建立索引。可以通过:show status like 'Handler_read%'来查看着连个参数的值。