mysql查询优化-字符排序，及其分析

desc salaries;

+-----------+---------+------+-----+---------+-------+

| Field     | Type    | Null | Key | Default | Extra |

+-----------+---------+------+-----+---------+-------+

| emp_no    | int(11) | NO   | PRI | NULL    |       |

| salary    | int(11) | NO   |     | NULL    |       |

| from_date | date    | NO   | PRI | NULL    |       |

| to_date   | date    | NO   |     | NULL    |       |

+-----------+---------+------+-----+---------+-------+

4 rows in set (0.01 sec)

上图为表结构；

表数据：

select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+
1 row in set (0.37 sec)

 

/* 打开 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 * from salaries order by to_date desc limit 15000;
/* 查看 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 差值 */
select @b-@a;

mysql> select @b-@a;
+---------+
| @b-@a   |
+---------+
| 2844048 |
+---------+
1 row in set (0.00 sec)

 

 

"filesort_summary": {
              "rows": 2844047,
              "examined_rows": 2844047,
              "number_of_tmp_files": 272,
              "sort_buffer_size": 262128,
              "sort_mode": "<sort_key, additional_fields>"
            }