MySQL 索引创建和优化实践

本文以 employees 表为例子,结合具体的索引运用实践案例,通过分析 EXPLAIN 关键字获取执行计划,来验证我们这些索引实践。如果是执行计划相关的详细信息,大家可以参考 mysql 官网 explain 介绍。

MySQL 索引创建和优化实践

本文以 employees 表为例子,结合具体的索引运用实践案例,通过分析 EXPLAIN 关键字获取执行计划,来验证我们这些索引实践。如果是执行计划相关的详细信息,大家可以参考 mysql 官网 explain 介绍。

mysql 版本: 5.7.23

使用的表

  • CREATETABLEemployees(
  • idint(11)NOTNULLAUTO_INCREMENT,
  • namevarchar(24)NOTNULLDEFAULT''COMMENT'姓名',
  • ageint(11)NOTNULLDEFAULT'0'COMMENT'年龄',
  • positionvarchar(20)NOTNULLDEFAULT''COMMENT'职位',
  • hire_timetimestampNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'入职时间',
  • PRIMARYKEY(id),
  • KEYidx_name_age_positionUSINGBTREE(name,age,position)
  • )ENGINE=InnoDBAUTO_INCREMENT=4CHARSET=utf8COMMENT'员工记录表';
  • INSERTINTOemployees(name,age,position,hire_time)VALUES('LiLei',22,'manager',NOW());
  • INSERTINTOemployees(name,age,position,hire_time)VALUES('WaKen',23,'dev',NOW());
  • INSERTINTOemployees(name,age,position,hire_time)VALUES('Lucy',23,'dev',NOW());
  • 复合索引数据结构

    下面是一个复合索引的是示意图

    MySQL 索引创建和优化实践

    索引最佳实践

    1. 全值匹配

    对于全值匹配,就是查询条件能够命中索引的全部列,或者复合索引需要命中左边的数据

  • EXPLAINSELECT*FROMemployeesWHEREname='LiLei';
  • MySQL 索引创建和优化实践

  • EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=22;
  • MySQL 索引创建和优化实践

  • EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=22ANDposition='manager';
  • MySQL 索引创建和优化实践

    2.最佳左前缀法则

    如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

  • EXPLAINSELECT*FROMemployeesWHEREage=22ANDposition='manager';
  • MySQL 索引创建和优化实践

  • EXPLAINSELECT*FROMemployeesWHEREposition='manager';
  • MySQL 索引创建和优化实践

  • EXPLAINSELECT*FROMemployeesWHEREname='LiLei';
  • MySQL 索引创建和优化实践

    3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

    使用全值匹配能够正常使用索引

  • EXPLAINSELECT*FROMemployeesWHEREname='LiLei';
  • MySQL 索引创建和优化实践

    使用 left 或者其他的数据库函数导致索引未命中。会走一个全表扫描。

  • EXPLAINSELECT*FROMemployeesWHEREleft(name,3)='LiLei';
  • MySQL 索引创建和优化实践

    4.存储引擎不能使用索引中范围条件右边的列

    全值匹配如下。

  • EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=22ANDposition='manager';
  • MySQL 索引创建和优化实践

    如果我们中间增加一个范围查找,会导致右边的查询条件无法使用索引。我们对比一下,上下两条 sql 的差别。

  • EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage>22ANDposition='manager';
  • MySQL 索引创建和优化实践

    5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句

    对于 select 指定字段查询,如果查询的所有列都是索引上的数据,那么可以减少 “回表”。查询的效率高于 select *

  • explainselectname,agefromemployeeswherename='Lilei'andage=23andposition='manage';
  • MySQL 索引创建和优化实践

  • EXPLAINSELECT*FROMemployeesWHEREname='LiLei'ANDage=23ANDposition='manager';
  • MySQL 索引创建和优化实践

    6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

  • EXPLAINSELECT*FROMemployeesWHEREname!='LiLei';
  • MySQL 索引创建和优化实践

    7.is null,is not null 也无法使用索引

    因为在 mysql 变长的数据类型中,如 varchar 。null 不会存储,它需要一个额外的标志位来存储。所以如果我们使用 is null , is not null 是无法使用索引的。所以我们在 DDL 语句中尽量让每个字段都有默认值。

  • EXPLAINSELECT*FROMemployeesWHEREnameisnull;
  • MySQL 索引创建和优化实践

    8.like以通配符开头('$abc…')mysql索引失效会变成全表扫描操作

  • EXPLAINSELECT*FROMemployeesWHEREnamelike'%Lei';
  • MySQL 索引创建和优化实践

  • EXPLAINSELECT*FROMemployeesWHEREnamelike'Lei%';
  • MySQL 索引创建和优化实践

    问题:解决like'%字符串%'索引不被使用的方法?a)使用覆盖索引,查询字段必须是建立覆盖索引字段

  • EXPLAINSELECTname,age,positionFROMemployeesWHEREnamelike'%Lei%';
  • b)当覆盖索引指向的字段是varchar(380)及380以上的字段时,覆盖索引会失效!

    9. 如果查询条件导致类型转换会导致索引失效

    字符串不加单引号索引失效

    加上单引号,无需数据转换 name 存储数据也是 varchar 类型。

  • EXPLAINSELECT*FROMemployeesWHEREname='1000';
  • MySQL 索引创建和优化实践

    如果不加单引号,发生数据类型转换导致本次查询索引失效。

  • EXPLAINSELECT*FROMemployeesWHEREname=1000;
  • MySQL 索引创建和优化实践

    10.少用or,用它连接时很多情况下索引会失效

  • EXPLAINSELECT*FROMemployeesWHEREname='LiLei'orname='HanMeimei';
  • MySQL 索引创建和优化实践

    小总结

    like KK%相当于=常量,%KK和%KK% 相当于范围 下面是常见的几种索引查询失效或者,可用的判定

    总结 1

    MySQL 索引创建和优化实践

    总结 2

    MySQL 索引创建和优化实践

    参考文档

    https://blog.csdn.net/qq_38138069/article/details/82998658

    https://www.processon.com/u/5e26625de4b00fbcc45e576d

    https://zhuanlan.zhihu.com/p/94190700

    https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

    原文地址:https://mp.weixin.qq.com/s/VGzbOE0kSrR3CT6nJeCVng

    版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/5353/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

    (0)
    上一篇 2022-07-25 6:27:06
    下一篇 2022-07-25 6:27:20

    软件定制开发公司

    相关阅读

    发表回复

    登录后才能评论
    通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!