市民信息

1
2
3
4
5
6
7
8
9
CREATE TABLE `t` (
  `id` INT(11) NOT NULL,
  `city` VARCHAR(16) NOT NULL,
  `name` VARCHAR(16) NOT NULL,
  `age` INT(11) NOT NULL,
  `addr` VARCHAR(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

查询语句

1
SELECT city,name,age FROM t WHERE city='杭州' ORDER BY name LIMIT 1000;

存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
DELIMITER ;;
CREATE PROCEDURE idata()
BEGIN
    DECLARE i INT;
    SET i=0;
    WHILE i<4000 DO
        INSERT INTO t VALUES (i,'杭州',concat('zhongmingmao',i),'20','XXX');
        SET i=i+1;
    END WHILE;
END;;
DELIMITER ;

CALL idata();

全字段排序

city索引树

满足city=’杭州’的行，主键为ID_X ~ ID_(X+N)

sort buffer

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> EXPLAIN SELECT city,name,age FROM t FORCE INDEX(city) WHERE city='杭州' ORDER BY name LIMIT 1000\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: city
          key: city
      key_len: 50
          ref: const
         rows: 4000
     filtered: 100.00
        Extra: Using index condition; Using filesort
  1. rows=4000：EXPLAIN是不考虑LIMIT的，代表匹配条件的总行数
  2. Using index condition：表示使用了索引下推
  3. Using filesort：表示需要排序，MySQL会为每个线程分配一块内存用于排序，即sort buffer
1
2
3
4
5
6
7
8
9
-- 1048576 Bytes = 1 MB
mysql> SHOW VARIABLES LIKE '%sort_buffer%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| innodb_sort_buffer_size | 67108864 |
| myisam_sort_buffer_size | 8388608  |
| sort_buffer_size        | 1048576  |
+-------------------------+----------+

执行过程

  1. 初始化sort buffer，确定放入三个字段：**city、name、age**
  2. 从city索引树找到第一个满足city=’杭州’的主键ID，即ID_X
  3. 然后拿着ID_X回表取出整行，将city、name、age这三个字段的值都存入sort buffer
  4. 回到city索引树取下一条记录，重复上述动作，直至city的值不满足条件为止，即ID_Y
  5. 对sort buffer中的数据按照name字段进行排序
    • 排序过程可能使用内部排序（内存，首选，快速排序/堆排序），也可能使用外部排序（磁盘，次选，归并排序）
    • 这取决于排序所需要的内存是否小于sort_buffer_size（默认1 MB）
  6. 按照排序结果取前1000行返回给客户端

观察指标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 打开慢查询日志
SET GLOBAL slow_query_log=ON;
SET long_query_time=0;

-- 查询optimizer_trace时需要用到临时表，internal_tmp_disk_storage_engine默认值为InnoDB
-- 采用默认值时，把数据从临时表取出来的时候，会将Innodb_rows_read+1，因此修改为MyISAM，减少干扰信息
SET GLOBAL internal_tmp_disk_storage_engine=MyISAM;

-- 将sort buffer设置为最小值，这是为了构造外部排序的场景，如果是内部排序则无需执行该语句
SET sort_buffer_size=32768;

-- 打开optimizer_trace，只对本线程有效
SET optimizer_trace='enabled=on';

-- @a 保存Innodb_rows_read的初始值
SELECT VARIABLE_VALUE INTO @a FROM  performance_schema.session_status WHERE variable_name = 'Innodb_rows_read';

-- 执行语句
SELECT city,name,age FROM t FORCE INDEX(city) WHERE city='杭州' ORDER BY name LIMIT 1000;

-- 查看optimizer_trace输出
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G;

-- @b 保存Innodb_rows_read的当前值
SELECT VARIABLE_VALUE INTO @b FROM performance_schema.session_status WHERE variable_name = 'Innodb_rows_read';

-- 计算Innodb_rows_read差值
-- MyISAM为4000，InnoDB为4001
SELECT @b-@a;

外部排序

慢查询日志
1
2
3
4
5
# Time: 2019-02-10T07:19:38.347053Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.012832  Lock_time: 0.000308 Rows_sent: 1000  Rows_examined: 5000
SET timestamp=1549783178;
SELECT city,name,age FROM t FORCE INDEX(city) WHERE city='杭州' ORDER BY name LIMIT 1000;
OPTIMIZER_TRACE
1
2
3
4
5
6
7
8
9
10
11
12
13
"filesort_summary": {
    "memory_available": 32768,
    "key_size": 32,
    "row_size": 140,
    "max_rows_per_buffer": 234,
    "num_rows_estimate": 16912,
    "num_rows_found": 4000,
    "num_examined_rows": 4000,
    "num_initial_chunks_spilled_to_disk": 9,
    "peak_memory_used": 35096,
    "sort_algorithm": "std::stable_sort",
    "sort_mode": "<fixed_sort_key, packed_additional_fields>"
}

In optimizer trace output, num_tmp_files did not actually indicate number of files.
It has been renamed to num_initial_chunks_spilled_to_disk and indicates the number of chunks before any merging has occurred.

  1. num_initial_chunks_spilled_to_disk=9，说明采用了外部排序，使用了磁盘临时文件
  2. peak_memory_used > memory_available：sort buffer空间不足
  3. 如果sort_buffer_size越小，num_initial_chunks_spilled_to_disk的值就越大
  4. 如果sort_buffer_size足够大，那么num_initial_chunks_spilled_to_disk=0，采用内部排序
  5. num_examined_rows=4000：参与排序的行数
  6. sort_mode含有的packed_additional_fields：排序过程中对字符串做了紧凑处理
    • 字段name为VARCHAR(16)，在排序过程中还是按照实际长度来分配空间
扫描行数

整个执行过程中总共扫描了4000行（如果internal_tmp_disk_storage_engine=InnoDB，返回4001）

1
2
3
4
5
6
mysql> SELECT @b-@a;
+-------+
| @b-@a |
+-------+
|  4000 |
+-------+

内部排序

慢查询日志

Query_time为0.007517，为采用外部排序的59%

1
2
3
4
5
# Time: 2019-02-10T07:36:36.442679Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.007517  Lock_time: 0.000242 Rows_sent: 1000  Rows_examined: 5000
SET timestamp=1549784196;
SELECT city,name,age FROM t FORCE INDEX(city) WHERE city='杭州' ORDER BY name LIMIT 1000;
OPTIMIZER_TRACE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
"filesort_information": [
    {
        "direction": "asc",
        "table": "`t` FORCE INDEX (`city`)",
        "field": "name"
    }
],
"filesort_priority_queue_optimization": {
    "limit": 1000,
    "chosen": true
},
...
"filesort_summary": {
    "memory_available": 1048576,
    "key_size": 32,
    "row_size": 138,
    "max_rows_per_buffer": 1001,
    "num_rows_estimate": 16912,
    "num_rows_found": 1001,
    "num_examined_rows": 4000,
    "num_initial_chunks_spilled_to_disk": 0,
    "peak_memory_used": 146146,
    "sort_algorithm": "std::stable_sort",
    "unpacked_addon_fields": "using_priority_queue",
    "sort_mode": "<fixed_sort_key, additional_fields>"
}
  1. num_initial_chunks_spilled_to_disk=0，说明采用了内部排序（堆排序），排序直接在sort buffer中完成
  2. peak_memory_used < memory_available：sort buffer空间充足
  3. num_examined_rows=4000：参与排序的行数
  4. filesort_priority_queue_optimization：采用优先级队列优化（堆排序）
扫描行数
1
2
3
4
5
6
mysql> SELECT @b-@a;
+-------+
| @b-@a |
+-------+
|  4000 |
+-------+

性能

  1. 全字段排序：对原表数据读一遍（覆盖索引的情况除外），其余操作都在sort buffer和临时文件中进行
  2. 如果查询要返回的字段很多，那么sort buffer中能同时放下的行就会变得很少
  3. 这时会分成很多个临时文件，排序性能就会很差
  4. 解决方案：采用_rowid排序_
    • 单行的长度不超过max_length_for_sort_data：全字段排序
    • 单行的长度超过max_length_for_sort_data：rowid排序
1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE '%max_length_for_sort_data%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 4096  |
+--------------------------+-------+

rowid排序

city、name和age三个字段的总长度最少为36，执行SET max_length_for_sort_data=16;

执行过程

  1. 初始化sort buffer，确定放入两个字段：**name（需要排序的字段）、id（索引组织表，主键）**
  2. 从city索引树找到第一个满足city=’杭州’的主键ID，即ID_X
  3. 然后拿着ID_X回表取出整行，将name和ID这两个字段的值存入sort buffer
  4. 回到city索引树取下一条记录，重复上述动作，直至city的值不满足条件为止，即ID_Y
  5. 对sort buffer中的数据按照name字段进行排序（当然也有可能仍然是外部排序）
  6. 遍历排序结果，取出前1000行，并按照主键id的值回表取出city，name和age三个字段返回给客户端
    • 其实，结果集只是一个逻辑概念，MySQL服务端在sort buffer排序完成后，不会再耗费内存来存储回表取回的内容
    • 实际上，MySQL服务端从排序后的sort buffer中依次取出id，回表取回内容后，直接返回给客户端

观察指标

1
2
3
-- 采用外部排序 + rowid排序
SET sort_buffer_size=32768;
SET max_length_for_sort_data=16;

慢查询日志

1
2
3
4
5
# Time: 2019-02-10T08:23:59.068672Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.012047  Lock_time: 0.000479 Rows_sent: 1000  Rows_examined: 5000
SET timestamp=1549787039;
SELECT city,name,age FROM t FORCE INDEX(city) WHERE city='杭州' ORDER BY name LIMIT 1000;

OPTIMIZER_TRACE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
"filesort_information": [
    {
        "direction": "asc",
        "table": "`t` FORCE INDEX (`city`)",
        "field": "name"
    }
],
"filesort_priority_queue_optimization": {
    "limit": 1000
},
...
"filesort_summary": {
    "memory_available": 32768,
    "key_size": 36,
    "row_size": 36,
    "max_rows_per_buffer": 910,
    "num_rows_estimate": 16912,
    "num_rows_found": 4000,
    "num_examined_rows": 4000,
    "num_initial_chunks_spilled_to_disk": 6,
    "peak_memory_used": 35008,
    "sort_algorithm": "std::stable_sort",
    "unpacked_addon_fields": "max_length_for_sort_data",
    "sort_mode": "<fixed_sort_key, rowid>"
}
  1. num_initial_chunks_spilled_to_disk，9->6，说明外部排序所需要的临时文件变少了
  2. sort_mode含有的rowid：采用rowid排序
  3. num_examined_rows=4000：参与排序的行数

扫描行数

扫描的行数变成了5000行（多出了1000行是回表操作）

1
2
3
4
5
6
mysql> SELECT @b-@a;
+-------+
| @b-@a |
+-------+
|  5000 |
+-------+

全字段排序 vs rowid排序

  1. MySQL只有在担心由于sort buffer太小而影响排序效率的时候，才会考虑使用rowid排序，rowid排序的优缺点如下
    • 优点：排序过程中，一次排序可以排序更多的行
    • 缺点：增加回表次数，与LIMIT N成正相关
  2. MySQL如果认为sort buffer足够大，会优先选择全字段排序
    • 把需要的所有字段都放到sort buffer，排序完成后直接从内存返回查询结果，无需回表
    • 体现了MySQL的一个设计思路
      • 尽量使用内存，减少磁盘访问
  3. MySQL排序是一个比较成本较高的操作，进一步的优化方案：联合索引、覆盖索引
    • 目的：移除Using filesort

优化方案

联合索引

1
ALTER TABLE t ADD INDEX city_user(city, name);

city_user索引树

explain

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> EXPLAIN SELECT city,name,age FROM t FORCE INDEX(city_user) WHERE city='杭州' ORDER BY name LIMIT 1000\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: city_user
          key: city_user
      key_len: 50
          ref: const
         rows: 4000
     filtered: 100.00
        Extra: Using index condition
  1. Extra里面已经移除了Using filesort，说明MySQL不需要排序操作了
  2. 联合索引city_user本身就是有序的，因此无需将4000行都扫描一遍，只需要扫描满足条件的前1000条记录即可
  3. Using index condition：表示使用了索引下推

执行过程

  1. 从city_user索引树找到第一个满足city=’杭州’的主键ID，即ID_X
  2. 然后拿着ID_X回表取出整行，取city、name和age三个字段的值，作为结果集的一部分直接返回给客户端
  3. 继续取city_user索引树的下一条记录，重复上述步骤，直到查到1000条记录或者不满足city=’杭州’时结束循环
  4. 这个过程不需要排序（当然也不需要外部排序用到的临时文件）

观察指标

慢查询日志

Rows_examined为1000，Query_time为上面全字段排序（内部排序）的情况耗时的49%

1
2
3
4
5
278 # Time: 2019-02-10T09:00:28.956622Z
279 # User@Host: root[root] @ localhost []  Id:     8
280 # Query_time: 0.003652  Lock_time: 0.000569 Rows_sent: 1000  Rows_examined: 1000
281 SET timestamp=1549789228;
282 SELECT city,name,age FROM t FORCE INDEX(city_user) WHERE city='杭州' ORDER BY name LIMIT 1000;
扫描行数
1
2
3
4
5
6
mysql> SELECT @b-@a;
+-------+
| @b-@a |
+-------+
|  1000 |
+-------+

覆盖索引

覆盖索引：索引上的信息足够满足查询需求，无需再回表，但维护索引是有代价的，需要权衡

1
ALTER TABLE t ADD INDEX city_user_age(city, name, age);

explain

Using index：表示使用覆盖索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> EXPLAIN SELECT city,name,age FROM t FORCE INDEX(city_user_age) WHERE city='杭州' ORDER BY name LIMIT 1000\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: city_user_age
          key: city_user_age
      key_len: 50
          ref: const
         rows: 4000
     filtered: 100.00
        Extra: Using where; Using index

执行过程

  1. 从city_user_age索引树找到第一个满足city=’杭州’的记录
    • 直接取出city、name和age这三个字段的值，作为结果集的一部分直接返回给客户端
  2. 继续取city_user_age索引树的下一条记录，重复上述步骤，直到查到1000条记录或者不满足city=’杭州’时结束循环

观察指标

慢查询日志

Rows_examined同样为1000，Query_time为上面使用联合索引city_user耗时的49%

1
2
3
4
5
# Time: 2019-02-10T09:16:20.911513Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 0.001800  Lock_time: 0.000366 Rows_sent: 1000  Rows_examined: 1000
SET timestamp=1549790180;
SELECT city,name,age FROM t FORCE INDEX(city_user_age) WHERE city='杭州' ORDER BY name LIMIT 1000;
扫描行数
1
2
3
4
5
6
mysql> SELECT @b-@a;
+-------+
| @b-@a |
+-------+
|  1000 |
+-------+

in语句优化

假设已有联合索引city_user(city,name)，查询语句如下

1
SELECT * FROM t WHERE city IN ('杭州','苏州') ORDER BY name LIMIT 100;

单个city内部，name是递增的，但在匹配多个city时，name就不能保证是递增的，因此这个SQL语句需要排序

explain

依然有Using filesort

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> EXPLAIN SELECT * FROM t FORCE INDEX(city_user) WHERE city IN ('杭州','苏州') ORDER BY name LIMIT 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: city_user
          key: city_user
      key_len: 50
          ref: NULL
         rows: 4001
     filtered: 100.00
        Extra: Using index condition; Using filesort

mysql> EXPLAIN SELECT * FROM t FORCE INDEX(city_user) WHERE city IN ('杭州') ORDER BY name LIMIT 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ref
possible_keys: city_user
          key: city_user
      key_len: 50
          ref: const
         rows: 4000
     filtered: 100.00
        Extra: Using index condition

解决方案

  1. 拆分语句，包装在同一个事务
  2. SELECT * FROM t WHERE city='杭州' ORDER BY name LIMIT 100;：不需要排序，客户端用一个内存数组A保存结果
  3. SELECT * FROM t WHERE city='苏州' ORDER BY name LIMIT 100;：不需要排序，客户端用一个内存数组B保存结果
  4. 内存数组A和内存数组B均为有序数组，可以采用内存中的归并排序

参考资料

《MySQL实战45讲》