MySQL查询优化案例 - yiyixiaozhi/readingNotes GitHub Wiki

MySQL查询优化案例

环境：MySQL8

表结构

有一张用户出入记录表，当前有两个月的数据记录。

CREATE TABLE `t_student_entry_record` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `student_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户姓名',
  `entry_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录时间',
  PRIMARY KEY (`id`),
  KEY `entry_time` (`entry_time`)
) ENGINE=InnoDB AUTO_INCREMENT=7092786 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

当前建立了针对entry_time建立了索引，执行如下SQL进行查询，耗时20.357秒，查询到87万行结果。

SELECT id, entry_time, user_id, student_name
 FROM t_student_entry_record
 WHERE 
 entry_time BETWEEN '2020-12-01 00:00:00.0' AND '2020-12-31 23:59:59.999'

where单列查询

优化策略1：缩减查询时间范围，让索引生效

查看下执行计划:

EXPLAIN 
SELECT id, entry_time, user_id, student_name
 FROM t_student_entry_record
 WHERE 
 entry_time BETWEEN '2020-12-01 00:00:00.0' AND '2020-12-31 23:59:59.999'

可以看到type是ALL，说明执行了全表扫描。

    id  select_type  table                   partitions  type    possible_keys  key     key_len  ref        rows  filtered  Extra        
------  -----------  ----------------------  ----------  ------  -------------  ------  -------  ------  -------  --------  -------------
     1  SIMPLE       t_student_entry_record  (NULL)      ALL     entry_time     (NULL)  (NULL)   (NULL)  1573708     50.00  Using where

如果缩减where条件中entry_time的时间范围，可以看到type会变更为range，说明索引起了作用。

EXPLAIN
SELECT id, entry_time, user_id, student_name
 FROM t_student_entry_record
 WHERE 
 entry_time BETWEEN '2020-12-01 00:00:00.0' AND '2020-12-02 23:59:59.999'

执行结果如下：不但使用了索引，还使用了MRR（在使用索引做范围扫描的过程中减少磁盘随机IO和减少主键索引的访问次数）。

    id  select_type  table                   partitions  type    possible_keys  key         key_len  ref       rows  filtered  Extra                             
------  -----------  ----------------------  ----------  ------  -------------  ----------  -----
--  ------  ------  --------  ----------------------------------
     1  SIMPLE       t_student_entry_record  (NULL)      range   entry_time     entry_time  5        (NULL)  163358    100.00  Using index condition; Using MRR

优化后执行耗时0.128秒，查询到6万行结果。

优化策略2：仅查询索引的相关数据，让覆盖索引生效

由于user_id、student_name没有做索引，所以不查询与其有关的数据

SELECT id, entry_time
 FROM t_student_entry_record
 WHERE 
 entry_time BETWEEN '2020-12-01 00:00:00.0' AND '2020-12-31 23:59:59.999'

执行计划
    id  select_type  table                   partitions  type    possible_keys  key         key_len  ref       rows  filtered  Extra                     
------  -----------  ----------------------  ----------  ------  -------------  ----------  -------  ------  ------  --------  --------------------------
     1  SIMPLE       t_student_entry_record  (NULL)      range   entry_time     entry_time  5        (NULL)  786854    100.00  Using where; Using index

可以看到使用了索引来查询。耗时0.056秒，查询到879015行。

where中使用两个查询条件

使用两个单列索引

首先建立两个索引

ALTER TABLE `t_student_entry_record` ADD INDEX `user_id`(user_id);

看执行计划：

EXPLAIN
SELECT id, entry_time, user_id
 FROM t_student_entry_record
 WHERE 
 entry_time BETWEEN '2020-12-01 00:00:00.0' AND '2020-12-31 23:59:59.999'
 AND user_id IN (342829,342809,342831,343581,352562,180016,350069,350063,164512,350060,350067,350062,350071)

    id  select_type  table                   partitions  type    possible_keys       key      key_len  ref       rows  filtered  Extra                                          
------  -----------  ----------------------  ----------  ------  ------------------  -------  -------  ------  ------  --------  -----------------------------------------------
     1  SIMPLE       t_student_entry_record  (NULL)      range   entry_time,user_id  user_id  9        (NULL)     107     50.00  Using index condition; Using where; Using MRR  

0.051秒，查询到106行数据。

使用单个索引

索引使用多个字段

ALTER TABLE `t_student_entry_record` DROP INDEX `user_id`;
ALTER TABLE `t_student_entry_record` DROP INDEX `entry_time`;
ALTER TABLE `t_student_entry_record` ADD INDEX `entry_time_and_user_id`(entry_time,user_id);
EXPLAIN
SELECT id, entry_time, user_id
 FROM t_student_entry_record
 WHERE 
 entry_time BETWEEN '2020-12-01 00:00:00.0' AND '2020-12-31 23:59:59.999'
 AND user_id IN (342829,342809,342831,343581,352562,180016,350069,350063,164512,350060,350067,350062,350071)

查看执行计划

    id  select_type  table                   partitions  type    possible_keys           key                     key_len  ref       rows  filtered  Extra                     
------  -----------  ----------------------  ----------  ------  ----------------------  ----------------------  -------  ------  ------  --------  --------------------------
     1  SIMPLE       t_student_entry_record  (NULL)      range   entry_time_and_user_id  entry_time_and_user_id  14       (NULL)  786854     50.00  Using where; Using index  

0.613秒查询到106行数据

效果不是太理想，因为每个人的记录数有限，重复情况较少，所以可以调整索引中字段的顺序：

ALTER TABLE `t_student_entry_record` DROP INDEX `entry_time_and_user_id`;
ALTER TABLE `t_student_entry_record` ADD INDEX `user_id_and_entry_time`(user_id,entry_time);

查看SQL优化结果：

EXPLAIN
SELECT id, user_id, entry_time
 FROM t_student_entry_record
 WHERE 
  user_id IN (342829,342809,342831,343581,352562,180016,350069,350063,164512,350060,350067,350062,350071)
  AND entry_time BETWEEN '2020-12-01 00:00:00.0' AND '2020-12-31 23:59:59.999'

查看执行计划：
    id  select_type  table                   partitions  type    possible_keys           key                     key_len  ref       rows  filtered  Extra                     
------  -----------  ----------------------  ----------  ------  ----------------------  ----------------------  -------  ------  ------  --------  --------------------------
     1  SIMPLE       t_student_entry_record  (NULL)      range   user_id_and_entry_time  user_id_and_entry_time  14       (NULL)     106    100.00  Using where; Using index  

可以看到rows扫描行大幅度降低，最终在0.046秒查询到了106行数据。

explain各列解析说明

各列解析说明：

id：表示执行的顺序，id的值相同时，执行顺序是从上到下，id的值不同时，id的值越大，优先级越高，越先执行

select_type: 1、SIMPLE表示不包含子查询和union 2、查询中若包含子查询，最外层查询则标记为：PRIMARY 3、在select或者where列表中包含了子查询，则标记为：SUBQUERY 4、在from的子查询会标记为：DERIVED 5、从union selcect出来的结果被标志为：UNION RESULT

type: 表示找到需要行的访问类型 ALL，index，range，ref，eq_ref，const，system，NULL 性能从最差到最好

key: 表示使用哪个索引

rows: 表示影响的行数

extra: 表示查询帅选的类型，比如使用了where条件，索引条件，或者排序，或者临时表空间

⚠️ **GitHub.com Fallback** ⚠️