有些时候虽然数据库有索引,但是并不被优化器选择使用。
通过SHOW STATUS LIKE 'Handler_read%';查看索引的使用情况:
-
Handler_read_key:如果索引正在工作,Handler_read_key的值将很高。
-
Handler_read_rnd_next:数据文件中读取下一行的请求数,如果正在进行大量的表扫描,值将较高,则说明索引利用不理想。
索引优化规则
-
如果MySQL估计使用索引比全表扫描还慢,则不会使用索引。
前导模糊查询不能命中索引
全导模糊查询不能命中索引
后导模糊查询不能命中索引
-
数据类型出现隐式转换的时候不会命中索引,特别是当列类型是字符串,一定要将字符常量值用引号引起来。
EXPLAIN SELECT * FROM user WHERE name=1;
EXPLAIN SELECT * FROM user WHERE name='1';
注:查看执行计划,第一条SQL没有走索引,第二条SQL有走索引。
-
复合索引的情况下,查询条件不包含索引列最左边部分(不满足最左原则),不会命中符合索引。
name,age,status列创建复合索引:ALTER TABLE user ADD INDEX index_name (name,age,status);
根据最左原则,可以命中复合索引index_name:EXPLAIN SELECT * FROM user WHERE name='swj' AND status=1;
最左原则并不是说是查询条件的顺序: EXPLAIN SELECT * FROM user WHERE status=1 AND name='swj';
查询条件中是否包含索引最左列字段: EXPLAIN SELECT * FROM user WHERE status=2 ;
-
union、in、or都能够命中索引,建议使用in。
查询的CPU消耗:or>in>union
-
用or分割开的条件,如果or前的条件中列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到。
EXPLAIN SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96;
因为or后面的条件列中没有索引,那么后面的查询肯定要走全表扫描,在存在全表扫描的情况下,就没有必要多一次索引扫描增加IO访问。
-
负向条件查询不能使用索引,可以优化为in查询。
负向条件有:!=、<>、not in、not exists、not like等。
-
范围条件查询可以命中索引。范围条件有:<、<=、>、>=、between等。
-
数据库执行计算不会命中索引。
-
利用覆盖索引进行查询,避免回表。
-
建立索引的列,不允许为null。
IS NULL可以命中索引
IS NOT NULL不能命中索引
虽然IS NULL可以命中索引,但是NULL本身就不是一种好的数据库设计,应该使用NOT NULL约束以及默认值。
-
更新十分频繁的字段上不宜建立索引:因为更新操作会变更B+树,重建索引。这个过程是十分消耗数据库性能的。
-
区分度不大的字段上不宜建立索引:类似于性别这种区分度不大的字段,建立索引的意义不大。因为不能有效过滤数据,性能和全表扫描相当。另外返回数据的比例在30%以外的情况下,优化器不会选择使用索引。
-
业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。虽然唯一索引会影响insert速度,但是对于查询的速度提升是非常明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,在并发的情况下,依然有脏数据产生。
-
多表关联时,要保证关联字段上一定有索引。
-
创建索引时避免以下错误观念:索引越多越好,认为一个查询就需要建一个索引;宁缺勿滥,认为索引会消耗空间、严重拖慢更新和新增速度;抵制唯一索引,认为业务的唯一性一律需要在应用层通过“先查后插”方式解决;过早优化,在不了解系统的情况下就开始优化。