Mysql 查询性能调优总结

一、查询分析性能的相关配置

1.1 配置显示查询性能的参数

在 MySQL 中，SHOW PROFILES 命令用于显示最近的查询性能概况，帮助你分析哪些查询比较耗时。

show profiles;

需要启用参数 profiling，才能使用上述功能，其相关参数设置如下：

// 所有与显示查询性能有关的参数
SHOW VARIABLES LIKE '%profiling%';// 开启显示查询性能的功能
SET profiling = 1;// 是否支持 profiling
SHOW VARIABLES LIKE 'have_profiling';// profiling 历史记录的最大条数
SHOW VARIABLES LIKE 'profiling_history_size';

1.2、Explain 分析

explain 命令可获取 MySQL 如何执行 SELECT 语句的信息，包括是否使用了索引、进行了哪些类型的连接、扫描了多少行数据等。

使用方法

在 select 语句前加上 explain 即可

输出字段解释

字段名	说明
select_type	查询的类型：比如 SIMPLE（简单的 SELECT，不使用 UNION 或子查询）、PRIMARY（查询中最外层的 SELECT）、UNION（UNION 中的第二个或后续的 SELECT 语句）、DEPENDENT UNION（UNION 中的第二个或后续的 SELECT 语句，取决于外部查询）、SUBQUERY（子查询中的第一个 SELECT）、DEPENDENT SUBQUERY（子查询中的第一个 SELECT，取决于外部查询）等
table	输出行所对应的表
type	连接类型：ALL（全表扫描）、index（索引全扫描）、range（索引范围扫描）、ref（非唯一性索引扫描，返回匹配某个单值的所有行）、eq_ref（唯一性索引扫描，对于每个索引键，表中最多只有一条匹配行）、const/system（表最多有一个匹配行，通常用于主键或唯一索引比较）、NULL（不用访问表或索引，就能得到所需数据）
possible_keys	可能应用在这张表上的索引
key	实际使用的索引
ref	显示索引的哪一列或常数被用于查找值
rows	MySQL 认为必须检查的行数，以找到查询所需行，这是估算的行数
filtered	表示返回结果的行占开始查找行的百分比
extra	包含不适合在其他列中显示的额外信息，比如是否使用了文件排序（Using filesort）、是否使用了临时表（Using temporary）、是否使用了回表（Using where）等

mysql官网中有关查询计划的说明：MySQL :: MySQL 8.4 Reference Manual :: 10.8.2 EXPLAIN Output Format

二、性能调优方案

2.1 表及数据量背景介绍

以表 order_master_test 为例，其中含有id主键索引及index_reportDate、index_createAt 辅助索引，数据量为470多万，具体表结构为：

// 创建表
CREATE TABLE `order_masters_test` (`id` bigint NOT NULL AUTO_INCREMENT,`group_id` bigint DEFAULT NULL,`shop_id` bigint DEFAULT NULL,`shop_name` longtext,`report_date` bigint DEFAULT NULL,`order_key` longtext,`order_type` longtext,`total_amount` float DEFAULT NULL,`area_name` longtext,`table_name` longtext,`channel_key` longtext,`channel_name` longtext,`order_status` int unsigned DEFAULT NULL,`create_by` longtext,`create_at` bigint DEFAULT NULL,`paid_amount` float DEFAULT NULL,`promotion_amount` float DEFAULT NULL,`checkout_by` longtext,`checkout_at` bigint DEFAULT NULL,`pay_desc` longtext,`customer_qty` int unsigned DEFAULT NULL,`order_remark` longtext,PRIMARY KEY (`id`),KEY `index_reportDate` (`report_date`),KEY `index_createAt` (`create_at`)
) ENGINE=InnoDB AUTO_INCREMENT=5111719 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;// 插入具体数据及批量插入数据
INSERT INTO order_masters_test
(`group_id`,
`shop_id`,
`shop_name`,
`report_date`,
`order_key`,
`order_type`,
`total_amount`,
`area_name`,
`table_name`,
`channel_key`,
`channel_name`,
`order_status`,
`create_by`,
`create_at`,
`paid_amount`,
`promotion_amount`,
`checkout_by`,
`checkout_at`,
`pay_desc`,
`customer_qty`,
`order_remark`)
VALUES
('955', '12345', '望京总店', '20240620', 'DD20240620111', '堂食', '90', '', '', '', '', '1', '001', '0', '0', '0', '', '0', '', '0', '测试订单11111');insert into order_masters_test(`group_id`,`shop_id`,`shop_name`,`report_date`,`order_key`,`order_type`,`total_amount`,`area_name`,`table_name`,`channel_key`,`channel_name`,`order_status`,`create_by`,`create_at`,`paid_amount`,`promotion_amount`,`checkout_by`,`checkout_at`,`pay_desc`,`customer_qty`,`order_remark`
) 
select `group_id`,`shop_id`,`shop_name`,`report_date`,concat(date_format(now(), '%Y%m%d%H%i%s'), LPAD(FLOOR(1 + (RAND() * 10000)), 4, '0')),`order_type`,`total_amount`,`area_name`,`table_name`,`channel_key`,`channel_name`,`order_status`,`create_by`,date_format(now(), '%Y%m%d%H%i%s'),`paid_amount`,`promotion_amount`,`checkout_by`,`checkout_at`,`pay_desc`,`customer_qty`,`order_remark`
from order_masters_test;

需要优化的查询语句为：

select * from order_masters_test where report_date = 20240620 order by create_at desc limit 200000, 10;

当前执行该语句，查询超时，如图：

2.2 初级调优：强制使用更合适的索引

经查询，表中report_date=20240620的数据总共有 524288，且查询执行计划发现，mysql使用了索引 index_reportDate 和文件排序。

分析原因：

mysql 使用 index_reportDate 索引，会首先查出满足条件(report_date=20240620)的52万条索引数据，再根据主键ID回表查找create_at字段，然后对52万条数据的create_at 进行降序排列，最后筛选出第200001到200010条最终数据。

注：此处省略了B+树索引的数据结构介绍，有关内容可以查看Mysql 存储引擎原理探究-CSDN博客

调优方案：

强制使用索引 index_createAt，先查找排好序的create_at值，再回表筛选 reportDate = 20240620 的数据，省去了文件排序的时间

使用方法：

在表名后增加语句 force index(index_createAt)

优化结果：

由查询超时提升到12.7 秒

2.3 中级调优：使用联合索引

分析原因：

经分析，查询语句使用了 report_date 字段过滤条件以及 create_at 字段排序，如果将这两个字段创建联合索引，则查询时间主要分布在查找联合索引及根据主键ID到主键索引中查找完整数据上，不用再进行文件排序等。

调优方案：

创建联合索引，具体sql语句如下：

-- 新建 report_date 和 create_at 字段的联合索引
create index index_reportDate_createAt on order_masters_test(report_date, create_at);

优化结果：

查询时间由12.7秒提升为6.29秒

2.4 高级调优：使用覆盖索引+join关联

分析原因：

查询语句中的 limit 200000,10 表示需要跳过前20万条记录，取接下来的10条数据，在上述两种调优方案中，都需要回表20万+次，其实前20万次回表都是无效的，所以如果能跳过这20万次回表，则能提高很大的查询效率。

调优方案：

通过联合索引 index_reportDate_createAt 先取出符合条件的10条数据的主键ID，当做临时表，再用这10个主键ID关联原表，从而获取完整数据，整个过程只需要回表10次，大大地提升查询性能。具体SQL语句如下：

select b.* from (
select id from order_masters_test 
where report_date = 20240620 order by create_at desc limit 200000, 10) temp
left join order_masters_test b on temp.id = b.id;

优化结果：

查询时间由6.29秒提升到69毫秒，性能大大提升。

2.5 索引合并

or 条件会使用索引合并，如下图：

不走索引的情况下，可以把 or 条件改为 union（去重）/ union all（不去重），如下：

实测中发现，分别执行上述的 where report_date = 20240623 和 create_at=20241219133733 耗时短，但用 union 合起来时则耗时了27s

注意：索引合并仅适用于单表查询

2.6 索引下推

索引下推（Index Condition Pushdown）的执行过程：mysql 数据库会在取出索引的同时，判断是否可以进行 where 条件的过滤，这样就不用先回表再进行where条件过滤发现数据不可用，减少了IO操作。ICP 优化支持 range、ref、eq_ref、ref_or_null 类型的查询，可在执行计划的列 Extra 看到 Using index condition。