在数据库日常使用中,因索引失效而导致的没有充分利用索引,是导致数据库查询效率低下的一个重要原因之一。
MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引 。索引提供了高效访问数据的方法,并且加快查询的速度,因此索引对查询的速度有着至关重要的影响。
- 使用索引可以快速地定位表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。
- 如果查询时没有使用索引,查询语句就会扫描表中的所有记录。在数据量大的情况下,这样查询的速度会很慢。
大多数情况下都(默认)采用B+树 来构建索引。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。
其实,用不用索引,最终都是优化器说了算。优化器是基于基于cost开销,它不是基干规则,也不是基于语义。怎么样开销小就怎么来。 另外,SOL语句是否使用索引,跟数据库版本,数据量,数据选择度都有关系。
接下来介绍索引失效的十一种情况:
一、全值匹配
student表中有字段【id】和【age】,并创建以下两个索引
CREATE INDEX idx_id ON student(id);
CREATE INDEX idx_id_age ON student(id,age);
此时执行以下查询语句
# idx_id 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE id = 1;
# idx_id 索引失效, idx_id_age 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE id = 1 AND age = 17;
二、最佳左前缀法则
在MySQL建立联合索引时会遵守最佳左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。
为student表中字段【age】、【classId】、【name】创建以下索引:
CREATE INDEX idx_age_cid_name ON student(age, class_id, NAME);
执行以下SQL语句:
# idx_age_cid_name 索引未生效,因为索引最左侧age没有匹配
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE class_id = 3 AND NAME = 'Jerry';
# idx_age_cid_name 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE age = 17 AND class_id = 3 AND NAME = 'Jerry';
结论:MySQL可以为多个字段创建索引,一个索引可以包括16个字段。对于多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时,多列(或联合)索引不会被使用。
三、主键插入原则
如果此时再插入一条主键值为 9 的记录,那它插入的位置就如下图:
四、计算、使用函数导致索引失效
① 计算
在student的字段【stuno】上加入以下索引:
CREATE INDEX idx_stu ON student(stuno);
执行SQL语句:
#索引生效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student
WHERE stuno = 900000;
#从b+树中取出值计算后再比较,索引失效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student
WHERE stuno+1 = 900001;
② 函数
为student的字段【name】加入以下索引:
CREATE INDEX idx_name ON student(NAME);
以下语句idx_name索引失效
# 运用了函数,idx_name 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.NAME,3) = 'Jer';
五、类型转换(自动或手动)导致索引失效
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
- name=123发生类型转换,索引失效。
六、范围条件右边的列索引失效
在student表上的创建联合索引
CREATE INDEX idx_age_cid_name ON student(age, class_id, NAME);
执行以下SQL语句发现只用到了 idx_age_cid_name 索引的【age】和【class_id】字段
EXPLAIN SELECT SQL_NO_CACHE * FROM student s
WHERE s.age=30 AND s.class_id>20 AND s.name = 'abc' ;
因为sql语句中class_id是范围查询,索引中范围以后的字段会失效
- 范围右边(这里指的是索引中的排列)的列不能使用。例如:(<), (<=), (>=), (>),between等。
- 更改索引,将范围查询条件放置语句最后:
CREATE INDEX idx_age_name_cid ON student(age, NAME, class_id);
七、不等于(!= 或者<>)索引失效
CREATE INDEX idx_name ON student(NAME);
#对于以下SQL语句 idx_name 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE NAME <> 'Jerry';
#或者
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE NAME != 'Jerry';
八、空值判断
is null可以使用索引,is not null无法触发索引
CREATE INDEX idx_age ON student(age);
# idx_age 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE age IS NULL;
#idx_name 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE age IS NOT NULL;
结论:
- 最好在设计数据表的时候就将字段设置为 NOT NULL 约束,可以将INT类型的字段的默认值设置为 0以及将字符类型的默认值设置为空字符串(”)来替代NULL。
- 同理,在查询中使用 not like 也无法使用索引,导致全表扫描。
九、like以通配符%开头索引失效
# idx_name 索引失效,因为Like为左模糊匹配
EXPLAIN SELECT id, stuno, NAME FROM student
WHERE NAME LIKE '%abc';
结论:页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
十、OR 前后存在非索引的列,索引失效
假设此时student表中只有以下索引
CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_name ON student(name);
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE age = 10 OR class_id = 100;
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE age = 10 OR name = 'Abel';
十一、数据库和表的字符集统一使用utf8mb4
- 统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。
- 不 同的 字符集 进行比较前需要进行 转换 会造成索引失效。
总结
- 对于单列索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择能够包含当前query中的where子句中更多字段的索引。
- 在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面。
总之,书写 SQL 语句时,尽量避免造成索引失效的情况。