我们看到了执行结果中 type 为 range 级别,使用了范围查找,而 position 字段并没有用到索引(没有使用到BTree的索引去查询),只是从 name = 'zhangsan' AND work_age > 2 条件返回的结果集中,再过滤符合 position 字段条件的数据。
5、尽量使用覆盖索引
覆盖索引:简单理解,只访问建了索引的列。减少使用 SELECT * 语句查询列。
使用了覆盖索引:
EXPLAIN SELECT name,work_age FROM staff WHERE name= 'zhangsan' AND work_age = 3;
使用了 SELECT * 查询:
EXPLAIN SELECT * FROM staff WHERE name= 'zhangsan' AND work_age = 3;
我们重点看下使用了 覆盖索引 方式查询,会在结果中 Extra 列显示 Using index ,这说明在查询列包含了索引列,不需要再次回表查询了。而如果使用 SELECT * 方式查询,查询列包含非索引的列,Extra 显示为 NULL,所以还会进行回表查询。
附一个曾经线上SQL的优化记录:
artist 表有几十万条的数据量,第一条执行的SQL没有索引直接查询,查询耗时 0.557 毫秒;第一次优化新建 founded 字段作为普通索引,查询耗时 0.0224 毫秒;第二次优化再次重建联合索引 founded_name,优化后查询耗时:0.0051 毫秒。因为使用了覆盖索引查询方式,基于此优化,SQL查询效率提升非常明显。
6、范围条件查找能够命中索引
范围条件主要包括 <、<=、>、>=、between 等。
若条件中范围列有普通索引和主键索引同时存在, 优先使用主键索引:
EXPLAIN SELECT * FROM staff WHERE staff.s_no > 10 AND staff.id > 2;
范围列可以用到索引,注意联合索引必须符合最左前缀法则,如果查询条件中有两个范围列则无法全用到索引,优化器会去选择:
EXPLAIN SELECT * FROM staff WHERE staff.name != 'zl' AND staff.s_no > 1;
若条件中范围查询和等值查询同时存在,优先匹配等值查询列的索引:
EXPLAIN SELECT * FROM staff WHERE staff.s_no > 10 AND staff.s_name = 'zl';
7、索引列不为 NULL,IS NOT NULL无法使用索引
索引列建议都使用 NOT NULL 约束 及默认值,单列索引不存 NULL 值,联合索引不存全部为 NULL 的值,如果列允许为 NULL,查询结果可能不符合预期。
staff 表中为 remark 字段新建普通索引:
ALTER TABLE staff ADD INDEX idx_remark (remark);IS NULL 查询命中索引:
EXPLAIN SELECT * FROM staff WHERE staff.remark IS NULL;
IS NOT NULL 查询不会命中索引:
EXPLAIN SELECT * FROM staff WHERE staff.name IS NOT NULL;
8、模糊条件查询以通配符开头索引失效
like '%xx' 或 like '%xx%' 前导模糊查询不能命中索引:
EXPLAIN SELECT * from staff where name like '%zhang%';