MySQL数据库调优————创建索引的原则和索引失效及解决方案

创建索引的原则

建议创建索引的场景

• select语句，频繁作为where条件的字段
• update/delete语句的where条件
• 需要分组、排序的字段
• distinct所使用的字段
• 字段的值有唯一性约束
• 对于多表查询，联接字段应创建索引，且类型无比保持一致
  • 避免隐式转换

不建议创建索引的场景

• where子句里用不到的字段
• 表的记录非常少
• 有大量重复数据，选择性低
  • 索引的选择性越高，查询效率越好，因为可以在查找时过滤更多的行
• 频繁更新的字段，如果创建索引要考虑其索引维护开销

索引失效与解决方案

可能导致索引失效的场景：

• 索引列不独立。独立是指：列不能是表达式的一部分，也不能是函数的参数
• 使用了左模糊查询
• 使用OR查询的部分字段没有索引
• 字符串条件未使用’ ’ 引起来
• 不符合最左前缀原则的查询
• 索引字段建议添加NOT NULL约束
• 隐式转换导致索引失效

索引列不独立

索引字段进行了表达式计算

explain
select *
from employees
where emp_no + 1 = 10003;

执行结果

从expain执行结果可以看到并未用到索引，解决方案，事先在代码中计算好表达式的值，再传入SQL中，应该避免在SQL语句的where条件子句等号左侧做计算。

explain
select *
from employees
where emp_no = 10002;

修改之后的执行结果

索引字段是函数的参数

explain
select *
from employees
where SUBSTRING(first_name,1,3) = 'GEO';

执行结果

该语句并没有用到索引，解决方案，同样先行计算好结果再传入，在where子句左侧不要使用函数；或者使用等价的SQL去实现。

explain
select *
from employees
where first_name like 'GEO%';

执行结果

使用左模糊

explain
select *
from employees
where first_name like '%GEO';

执行结果

解决方案，在进行模糊查询时应尽量避免使用左模糊查询，如果无法避免，且在有必要的情况下可以考虑使用搜索引擎来解决。

使用OR查询的部分字段没有索引

explain
select *
from employees
where first_name = 'Georgi'
or last_name = 'Georgi';

执行结果

解决方案，分别为条件中的字段创建索引。

create index employees_last_name_index on employees (last_name);

再次执行expain结果

字符串条件未使用单引号引起来

explain
select *
from dept_emp
where dept_no = 3;

执行结果

解决方案，在编写SQL语句的时候，要规范，对于字符串的条件要用单引号引起来。

explain
select *
from dept_emp
where dept_no = '3';

执行结果

不符合最左前缀原则的查询

存在index(last_name,first_name)

explain
select *
from employees
where first_name = 'Facello';

执行结果

解决方案，调整索引的顺序，使其变为index(first_name,last_name)或创建first_name的单独索引。

调整顺序后执行结果

索引字段建议添加NOT NULL约束

单列索引无法存储null值，复合索引无法存储全为null的值。查询时采用is null条件时，不能利用到索引，只能全表扫描。且，MySQL官方建议尽量把字段定义为NOT NULL。

这种情况不做演示，解决方案很简单，在建表时把索引字段设置为NOT NULL，根据官方建议，甚至可以把所有的字段都设置成NOT NULL并为字段赋默认值。

隐式转换导致索引失效

当联表查询时，如果作为联接条件的两个字段的类型不一致，则会进行隐式转换，也会导致索引失效，所以在创建表时要进行充分的考虑，使两个字段的类型保持一致。