创建索引的原则

最后更新于：2022-11-07 13:42:47

原则只是参考，不要死守教条

建议创建索引的场景

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

不建议创建索引的场景

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

索引失效的场景及解决方案

可能导致索引失效的场景

索引列不独立。独立是指：列不能是表达式的一部分，也不能是函数的参数

示例 1

explain 
select 
    * 
from 
    employees 
where 
    emp_no + 1 = 10001

解决方案：
事先计算好表达式的值，再传过来，避免在SQL where 条件=的左侧做计算

explain 
select 
    * 
from 
    employees 
where 
    emp_no  = 10000

示例 2

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%'

解决方案：
尽量避免使用左模糊，如果避免不了，可以考虑使用搜索引擎

explain 
select 
    * 
from 
    employees 
where 
 first_name like 'Geo%'

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

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

解决方案：
额外添加索引

字符串条件未使用 '' 引起来

explain 
select 
    * 
from 
    employees 
where 
 dept_no = 3

解决方案：
规范SQL语句

explain 
select 
    * 
from 
    employees 
where 
 dept_no = '3'

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

索引为index(last_name,first_name)

explain 
select 
    * 
from 
    employees 
where 
 first_name = 'Georgi'

解决方案：
调整索引的顺序index(first_name,last_name),或者创建一个作用在first_name上的索引index(first_name)

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

单列索引无法存储null的值，符合索引无法存储全为null的值
查询时，采用is null 条件时，不能利用索引，只能全表扫描

隐式转换导致索引失效

创建表的时候尽量规范一些，比如统一使用int，或者使用bigint