MySQL索引优化实战

最新推荐文章于 2024-08-02 17:13:21 发布

xue_520java

最新推荐文章于 2024-08-02 17:13:21 发布

阅读量162

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/wo82933418/article/details/132834940

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL索引优化实战

数据准备

 CREATE TABLE `employees` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
     `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
     `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
     `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
     PRIMARY KEY (`id`),
     KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
     ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

 ‐‐ 插入一些示例数据
 drop procedure if exists insert_emp;
 delimiter ;;
 create procedure insert_emp()
 	begin
 		declare i int;
 			set i=1;
 			while(i<=100000)do
 			insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');
 			set i=i+1;
 			end while;
 		end;;
 delimiter ;
 call insert_emp();

一、索引优化

1、全值匹配和key_len长度计算

--以下三条语句都遵循联合索引的定义顺序
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

执行结果

在这里插入图片描述

全值匹配：联合索引中的索引字段用得越多，筛选出来的结果越少，sql执行效率越高。

三次查询都使用到了索引，区别在于使用的索引长度不同，关于key_len长度的计算：

查询结果一：使用到name varchar(24)，那么key_len=24x3+2=74；
查询结果二：使用到name和age int(11)，那么key_len=74 + 4 =78；
查询结果三：使用到name和age 和 position varchar(20) ，那么key_len=78+(20x3+2)=140；

int(11)中，11 代表的并不是长度，而是字符的显示宽度，int 固定占用4字节

key_len的计算规则

**字符串类型：char(n) 和 varchar(n)，5.0.3以后版本中，**n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节。

char(n)：如果存汉字长度就是 3n 字节

varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

字符串类型	长度	数值类型	长度	时间类型	长度
char(n)	存汉字长度为 3n 字节	tinyint	1字节	date	3字节
varchar(n)	存汉字长度为 3n + 2 字节	smallint	2字节	timestamp	4字节
		int	4字节	datetime	8字节
		bigint	8字节

注：如果字段允许为 NULL，则需要额外1字节记录是否为 NULL，索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

2、最左前缀匹配原则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（b+Tree数据结构）

**问:**如果不遵循最左前缀的，但是索引字段都用上了，结果会怎么样呢？

mysql底层执行sql时自动进行优化，但是一般不建议把这种优化让mysql底层来做，而是我们写sql时，就按照联合索引定义的顺序来写。

**问:**如果不遵循最左前缀的，且不用联合索引第一个索引字段，结果会怎么样呢？
```
mysql将进行全表扫描，且不能使用索引
```

联合索引第一个字段用范围不会走索引

联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描

强制走索引

SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';

虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高

覆盖索引优化

SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

3、避免在索引列上计算

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

在索引列上使用函数，索引会失效，原因是定义的索引跟函数计算后的结果无关，因此也就无法使用到索引

4、如果是范围查询,联合索引范围索引字段后面的列会失效

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

在这里插入图片描述

根据索引构建的B+树可知，age字段根据范围查找后的结果中第三个字段position的排列并不是有序的。如上，范围查找打乱了第三个索引的有序性，所以只能用到前两个索引。

5、索引列尽量包含查询列

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句。减少回表操作

6、Mysql在使用不等于，not in，not exists 时无法使用索引

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

注：< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。
mysql内部优化器会进行评估，数据量小的情况下，相比使用索引，使用全表扫描，反而效率更高。

7、is null，is not null 一般情况下也无法使用索引

8、like 以通配符开头(‘%abc…’)，索引失效会变成全表扫描

Mysql 底层对 like 进行了优化，相当于等号匹配；而对于相关函数，执行起来会比较复杂(需要计算)，Mysql无相应的优化。

like KK%相当于=常量(索引下推)，%KK和%KK% 相当于范围。

问题：解决like’%字符串%'索引不被使用的方法？

使用覆盖索引，查询字段必须是建立覆盖索引字段，如果不能使用覆盖索引则可能需要借助搜索引擎

9、字段类型转换导致索引失效

比如：字符串不加单引号索引失效

10、少用or或in进行查询

用or或in查询时，mysql不一定使用索引，当需要多次扫描索引树时，可能性能不如全表扫描

mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

11、Order by 和 Group by 优化

优化总结：

（1）MySQL支持两种方式的排序 filesort 和 index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低(内存排序/磁盘排序)。

（2）order by满足两种情况会使用Using index。

order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列

（3）尽量在索引列上完成排序，遵循索引建立(索引创建的顺序)时的最左前缀法则，如果order by的条件不在索引列上，就会产生Using filesort

（4）能用覆盖索引尽量用覆盖索引

（5）group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null 禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

12、分页查询优化

--分页
select * from employees limit 10000,10;

表示从表 ‘employees’ 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要**查询一张大表比较靠后的数据，执行效率非常低。**

（1）根据自增且连续的主键排序的分页查询优化

前提：表中数据主键是自增并且连续的

-- 优化前
select * from employees limit 90000,5;
 
-- 优化后
select * from employees where id > 90000 limit 5;

但是，这条改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致查询的结果不一致。

另外如果原 SQL 是 order by 非主键的字段，按照上面说的方法改写也会导致两条 SQL 的结果不一致。所以这种改写得满足以下两个条件：

主键自增且连续
结果是按照主键排序的

（2）根据非主键字段排序的分页查询优化

根据非主键字段排序的分页查询，SQL 如下：

select * from employees ORDER BY name limit 90000,5;
EXPLAIN select * from employees ORDER BY name limit 90000,5;

发现并没有使用 name 字段的索引(key 字段对应的值为 null)，具体原因：扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

知道不走索引的原因，那么怎么优化呢?

关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL改写如下


select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
-- 分析
EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

需要的结果与原 SQL 一致，执行时间减少了一半以上，我们再对比优化前后sql的执行计划，原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是**索引排序**。

13、关联查询优化

Mysql的表关联常见有两种算法：**Nested-Loop Join 算法**和 Block Nested-Loop Join 算法

关联字段加索引，让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引
小表驱动大表

14、in和exists优化

**原则：**小表驱动大表，即小的数据集驱动大的数据集。

**in：**当B表的数据集小于A表的数据集时，in优于exists // B < A

**exists：**当A表的数据集小于B表的数据集时，exists优于in // A < B

将主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定主查询的数据是否保留

15、count(*)查询优化

四种count方式sql

EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

注意：以上4条sql只有根据某个字段count不会统计字段为null值的数据行

四个sql的执行计划一样，说明这四个sql执行效率应该差不多

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)

字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)

字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。

count() 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count()。

为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？

因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。

16、索引使用总结

在这里插入图片描述

二、trace工具分析

Mysql如何选择合适的索引？

对于不同SQL的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟。

开启 trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后需要立即关闭

-- 开启trace工具
set session optimizer_trace="enabled=on", end_markers_in_json = on;
‐‐ 关闭trace工具
set session optimizer_trace="enabled=off";

其中TRACE的分析结果如下：

{
  "steps": [
    {
      "join_preparation": { --第一阶段：SQL准备阶段，格式化sql
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": { --第二阶段：SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": { --条件处理
              "condition": "WHERE",
              "original_condition": "(`employees`.`name` > 'a')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [ --表依赖详情
              {
                "table": "`employees`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [ --预估表的访问成本
              {
                "table": "`employees`",
                "range_analysis": {
                  "table_scan": { --全表扫描情况
                    "rows": 3,  --扫描行数
                    "cost": 3.7 --查询成本
                  } /* table_scan */,
                  "potential_range_indexes": [ --查询可能使用的索引
                    {
                      "index": "PRIMARY", --主键索引
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_age_position", --辅助索引
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "position",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": { --分析各个索引使用成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "a < name" --索引使用范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,
                        "index_only": false, --是否使用覆盖索引
                        "rows": 3, --索引扫描行数
                        "cost": 4.61, --索引使用成本
                        "chosen": false, --是否选择该索引
                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`employees`",
                "best_access_path": { --最优访问路径
                  "considered_access_paths": [ --最终选择的访问路径
                    {
                      "rows_to_scan": 3,
                      "access_type": "scan", --访问类型：为scan，全表扫描
                      "resulting_rows": 3,
                      "cost": 1.6,
                      "chosen": true --确定选择
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 1.6,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`employees`.`name` > 'a')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "(`employees`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_explain": { --第三阶段：SQL执行阶段
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_explain */
    }
  ] /* steps */
}

三、索引下堆

like ‘KK%’ 一般情况都会走索引，原因是like ‘KK%’ 用到了索引下推优化

什么是索引下推？

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则：WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’; 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个**回表**，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。

MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引(聚簇索引)树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

四、Using filesort文件排序原理

filesort 文件排序方式

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序

用trace工具可以看到sort_mode信息里显示：

< sort_key, additional_fields >或者< sort_key,packed_additional_fields >
双路排序(又叫回表排序模式)：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；

用trace工具可以看到sort_mode信息里显示：< sort_key, rowid >

MySQL 通过比较系统变量 max_length_for_sort_data (默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式；
如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式。

对比两个排序模式：

单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

如果 MySQL 排序内存 sort_buffer 配置的比较小，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。
如果 MySQL 排序内存配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。

所以，MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

sort_data ，那么使用单路排序模式；

如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式。

对比两个排序模式：

如果 MySQL 排序内存 sort_buffer 配置的比较小，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。
如果 MySQL 排序内存配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。

所以，MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都做过优化，不要轻易调整。

xue_520java

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引优化实战

Mysql如何选择合适的索引？对于不同SQL的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟。开启 trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后需要立即关闭-- 开启trace工具‐‐ 关闭trace工具"steps": ["join_preparation": { --第一阶段：SQL准备阶段，格式化sql"steps": [},
复制链接

扫一扫