一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第11天，点击查看活动详情

索引

• 索引是数据库优化的最常用的方式,通过使用索引可以解决MySQL数据库服务器中大多数的性能优化问题
• MySQL执行器评估使用索引查询比全表查询较慢,就会使用全表查询.即使建立的索引,也不会使用索引
  • 比如某个字段的数据条件符合的数据在全表中占比较大,即使对这个字段建立索引,也会执行全表查询,因为全表查询比使用索引执行更快
  • 比如对建立索引的某个字段进行NULL值判定. 如果NULL值比较多,使用IS NULL就会使用全表扫描,使用IS NOT NULL就会使用索引执行. 如果NULL值比较少,使用IS NULL就是使用索引执行,使用IS NOT NULL就会使用全表扫描

避免索引失效

全值匹配

• 全值匹配:
  • 对于复合索引中所有列都指定具体值
  • 这个情况下,索引生效,执行效率高

最左前缀法则

• 如果一个索引包含多个列,也就是复合索引,要遵循最左前缀法则
• 查询要包含索引的最左前列,并且不跳过索引中的列
  • 如果包含索引的最左前列,但是跳过索引中的列,就会导致跳过的索引列后面的列索引失效

避免使用范围查询

• 对于复合索引中,如果索引中的某一列使用范围查询,那么范围查询的索引列后面的列索引失效

避免运算操作

• 对查询条件中索引的列使用运算操作,就会导致索引失效

字符串要加单引号

• 对于复合索引中的列如果是字符串类型,不加单引号会导致索引失效

查询要覆盖索引

• 尽量保证查询的字段覆盖索引的字段,只访问索引字段的查询,也就是说索引列要完全包含需要查询的列.减少使用select *进行查询
• 查询的列超出索引的列.分析执行计划中的Extra字段为Using index condition, 表示查找使用了索引,但是需要回表查询索引列之外的数据
• Extra字段的取值:
  • Using index : 使用索引查询,查询的字段的列包含在索引列中
  • Using where : 使用索引查询,但是需要回表查询需要的数据
  • Using index condition : 使用索引查询,但是需要回表查询索引列之外的数据
  • Using where;Usingindex : 使用索引查询,需要查询的数据都能够在索引列中找到,直接返回查询的数据

多个条件使用in

• in条件中的索引是生效的
• not in中的会导致索引失效

or的前后都要使用索引字段

• where条件中使用or时,如果or前面的列使用了索引,但是or后面的列没有使用索引,就会导致or前面使用的索引列失效

查询要以%结尾进行LIKE模糊匹配

• 使用以 % 开头进行头部的LIKE模糊匹配查询,就会导致索引失效
• 使用以 % 结尾进行尾部的LIKE模糊匹配查询,索引生效
• 使用覆盖索引可以解决 % 头部LIKE模糊查询索引失效的问题: 保证要查询的字段列都是建立了索引的列

多使用复合索引代替单列索引

• 复合索引:
  • 创建一个复合索引(index1, index2, index3),相当于创建了三个索引:
    • index1
    • index1,index2
    • index1,index2,index3
• 单列索引:
  • 创建多个单列索引,数据库只会选择一个最优的索引使用,也就是辨识度最高的索引,不会使用全部的单列索引

查看索引使用情况

• 使用命令查看索引使用情况的属性:

-- 查看当前会话的索引使用情况
show status like 'Handler_read%';

-- 查看系统级全局的索引使用情况
show global status like 'Handler_read%';

• 索引相关属性的含义:
  • Handler_read_first: 第一条数据被索引读的次数
    • 这个值越低越好
    • 如果值较高,说明服务器正在执行大量的全索引扫描
  • Handler_read_key: 一个行被索引读的次数
    • 这个值越高越好
    • 如果值较低,说明索性得到的性能提升低,因为索引不经常使用
  • Handler_read_next: 按照键顺序读下一行的请求数
    • 如果使用范围约束或执行索引扫描来查询索引列,这个值增加
  • Handler_read_prev: 按照键顺序读上一行的请求数
    • 这种读方法主要用于优化ORDER BY ... DESC形式的SQL语句
  • Handler_read_rnd: 根据固定位置读一行的请求数
    • 这个值越高,意味着运行效率越低,应该建立索引进行补救
    • 如果正在执行大量查询并且需要对结果进行排序时,这个值会很高
    • 可能使用了大量MySQL中需要扫描整个表的查询或者没有正确的使用键
  • Handler_read_rnd_next: 在数据文件中读下一行的请求数
    • 如果正在执行大量的表扫描,这个值会很高
    • 说明表的索引不正确或者写入的查询没有使用索引

SQL提示

• SQL提示:
  • 在SQL语句中加入人为的提示来达到SQL语句优化的目标
  • 是数据库优化的一个重要手段

USE INDEX

• USE INDEX:
  • 在SQL语句的表名后面使用
  • 提供MySQL执行时参考使用的索引列表,可以避免MySQL去比较其余的可用索引来提高SQL语句的查询性能

SELECT * FROM user_info USE INDEX(idx_user_name) WHERE name='Chova';

IGNORE INDEX

• IGNORE INDEX:
  • 在SQL语句的表名后面使用
  • 使得MySQL执行时忽略一个或者多个索引

SELECT * FROM user_info IGNORE INDEX(idx_user_name) WHERE name='Chova';

FORCE INDEX

• FORCE INDEX:
  • 在SQL语句的表名后面使用
  • 强制MySQL执行时使用特定的索引

SELECT * FROM user_info FORCE INDEX(id_user_name) WHERE name='Chova';