MySQL索引优化分析，SQL优化，慢查询分析

1 配置环境的说明 MySQL的版本信息： 系统版本信息： 2 索引的分析 2.1数据准备 2.1.1数据库建表SQL 表的说明： id是自增主键，name是唯一索引，age 是非唯一索引，desc无索引 CREATE TABLE `index_test` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT C 1 配置环境的说明 MySQL的版本信息： 系统版本信息： 2 索引的分析 2.1数据准备 2.1.1数据库建表SQL 表的说明： id是自增主键，name是唯一索引，age 是非唯一索引，desc无索引 2.1.2 表中测试数据 2.2 索引分析 2.2.1 使用explain查看sql的执行计划 在 mysql 中可以在sql前面加上explain语句，来显示该条sql的执行计划，输出内容如下： 2.2.2 explain详解 2.2.2.1 select_type select_type表示查询语句的类型，取值主要有以下几种： simple：表示是简单的单表查询 primary：表示子查询的外表 derived：派生表的查询 subquery: 子查询的内部第一个SQL union：表示union操作被连接的表 union result：表示连接操作之后的结果表 depend union 表示子查询中union语句 depend subquery 表示子查询中生成的结果 MySQL（Linux） MySQL 9.6.0是面向Linux平台的2026年创新版本，核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层，确保所有数据变更均被完整记录至Binlog，彻底解决了CDC（变更数据捕获）与主从复制中的数据不一致难题。此外，该版本引入container_aware启动选项以原生适配容器环境，并对审计日志进行了组件化重构，为追求极致数据一致性与云原生体验的开发者提供了全新选择。 下载 2.2.2.2 table 当前SQL查询涉及到的表的表名， 注意 这里有时候是中间结果表的表名，MySQL会按照自己的规则生成 2.2.2.3 type type的取值在很大的程度上反应了SQL的执行性能，                               按照性能由高到底，type的取值依次为：NULL，system，const，eq_reg，ref，range，index，ALL NULL 不用查表，速度最快 system当表中只有一条数据的时候 type为system const常数查询 一般是根据唯一键或者主键等值查询 eq_reg 表连接的时候 在b表查询出来的结果在a表这中按照唯一索引值查询一行 ref非唯一索引查询 range 使用唯一索引返回扫描 index 扫描整个索引文件，例如覆盖索引的查询，效率只是比全表查询略快，因为索引文件一般比数据文件小，所以一次读入内存的索引数据更多，这样磁盘IO                             就会更少 All表示全表扫描，是效率最低的一种查询 2.2.2.4 possible key 表示可能使用的索引，显示的顺序与表连接的顺序无关 2.2.2.5 key 表示MySQL执行本条sql选的索引的名字，可以通过force idex 和 ignore index 来强制改变sql执行所需要的索引 2.2.2.6 key_len 表示该条索引的占用的自己树，是根据索引字段的类型计算出来的，                                例如  int(11)  索引长度是4                                         varchar(128)并且编码是U8  索引长度的计算方法为 ： 128*3+2 2.2.2.7 ref 表示使用哪个列从表中选择行，取值有科恩个是const 2.2.2.8 rows 表示执行该条SQL必须扫描的行数 2.2.2.8 extra 包含了MySQL生成执行计划的详细信息： distinct 查找唯一值，一旦找到就不在继续查找了（暂时没有想好例子） record 没有找到理想的索引 use file sort 使用外排来排序  效率比较低 use index 使用覆盖索引返回数据，没有扫描表 use tempoary 使用临时表来组合返回数据 效率较低 use where 使用where条件过滤返回的数据，在MySQL的存储引擎层没有过滤完数据，只能在MySQL服务层去过滤数据 2.3 profiling详解 2.3.1 开启profiling 因为profiling是比较消耗资源的，所以一般的MySQL默认都关闭了profiling功能，并且profiling只是针对当前session有效，目前不支持全局的profiling，可以通过如下的命令查看并开发profiling功能： 2.3.2 profiling的使用 2.3.2.1 查询当前session的profiling的概要信息 可以使用 show profiles命令获取当前session所执行的sql的概要信息 2.3.2.2 profiling详解 profiling的语法如下： 使用示例： 结果说明： 在使用profiling查看sql的详细执行计划的时候，主要关注的是前两列即：status和duration status 表示sql的执行状态和 show full process list 查看到的状态一致 duration 表示每个状态执行的时间 可以看到sql的主要执行时间消耗在哪里 其次需要关注的是cup，io,swap的详细信息 cup表示 cpu的消耗时间 swap表示机器的swap情况 io表示io的消耗情况 3 无效索引 在很多时候MySQL的表建立了索引，并且在查询条件中也使用了索引进行筛选，但是并不一定会使用到索引，例如下面的几种情况 3.1筛选条件包含了隐式转换 下面的例子中，name字段添加了唯一索引，但是name字段的类型是varchar类型的，而筛选添加时int类型，发生了隐式转换，所以走全表扫描。这里比较隐晦。在上周有一个项目分析酒店订单的时候，本来hive中的酒店订单包含了酒店项目的所有订单，订单id是varchar类型的，而我们需要统计QTA中参加某一个活动的订单，需要查询QTA的订单详情库，（QTA订单详情是hive中订单的子集）里面的订单ID是long类型的，最开始查询的时候就直接在一个表查询完后再另外一个表查询，结果看到一条简单的sql执行起来巨慢。最后分析原因就定位到了这个上面。 3.2 不支持函数式索引 age字段上面添加了非唯一索引，但是使用了绝对值函数，所以age字段上面的索引就无法使用了。这个在处理日期的时候经常遇到这样的坑 3.3 索引扫描的代价大于直接全表扫描 如果只有索引过滤的数据比较少，那么会直接走全表扫描，因为使用索引的时候会先扫描一遍索引，然后根据扫描到的索引回表找到所需要的数据，这样扫描的效率其实更低，所以直接走全表扫描 3.4 使用“%”前缀匹配的时候 name字段添加了唯一索引 但是使用‘%’作为前缀匹配条件，所以不使用索引，直接走全表扫描 3.5 复合索引非左前缀匹配 在使用复合索引的时候 如果不是使用的左前缀筛选条件 则不会使用索引，还是会全表扫描 3.5 or筛选添加前后都有索引的时候才会走索引 在使用or作为筛选条件的时候，or的前后筛选条件都必须添加索引 这样才能使用索引 否则 整条sql都无法使用索引