mysql 索引优化

我的mysql server 版本为5.7 commutity 版本，低于该版本的trace 参数可能不存在（5.6.x 之后）！

mysql> EXPLAIN select * from employees where name > 'a';
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    3 |      100 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set

如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描

还高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果，如下：

mysql> EXPLAIN select * from employees where name > 'zzz' ;
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | range | idx_name_age_position | idx_name_age_position | 74      | NULL |    1 |      100 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set

对于上面这两种 name>'a' 和 name>'zzz' 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最

终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用

完之后立即关闭：

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; 
Query OK, 0 rows affected

mysql>  select * from employees where name > 'a' order by position;
+----+-----------+-----+----------+---------------------+
| id | name      | age | position | hire_time           |
+----+-----------+-----+----------+---------------------+
|  2 | HanMeimei |  23 | dev      | 2020-03-14 16:45:53 |
|  3 | Lucy      |  23 | dev      | 2020-03-14 16:45:53 |
|  1 | LiLei     |  22 | manager  | 2020-03-14 16:45:53 |
+----+-----------+-----+----------+---------------------+
3 rows in set

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

step1：SQL准备阶段

 

step2:SQL 优化阶段

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 */

3.表依赖阶段：

"table_dependencies": [
              {
                "table": "`employees`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },

 

4.预估表的访问成本，

rows_estimation": [
              {
                "table": "`employees`",
                "range_analysis": {
                  "table_scan": {--全表扫描
                    "rows": 3,--扫描行
                    "cost": 3.7--查询成本
                  } /* table_scan */,

5.可能使用到的索引

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 */

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 */,

7.

table": "`employees`",
                "best_access_path": {‐‐最优访问路径
                  "considered_access_paths": [‐‐最终选择的访问路径

                    {
                      "rows_to_scan": 3,
                      "access_type": "scan", ‐‐访问类型：为scan，全表扫描

                      "resulting_rows": 3,
                      "cost": 1.6,
                      "chosen": true,    ‐‐确定选择

                      "use_tmp_table": true 
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 1.6,
                "sort_cost": 3,
                "new_cost_for_plan": 4.6,
                "chosen": true

 

8.SQL执行阶段

join_execution": {
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 3,
              "examined_rows": 3,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 200704,
              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */

 

结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描

 

同理， mysql> select * from employees where name > 'zzz' order by position;

查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描

 

 

 

常见sql深入优化

 

Order by与Group by优化

mysql> explain select * from employees where name='LiLei' and position='dev' order by age;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |    33.33 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set

 

利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用

在排序过程中，因为Extra字段里没有using filesort

 

mysql> explain select * from employees where name='LiLei'  order by age,position;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |      100 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set

查找只用到索引name，age和position用于排序，无Using filesort。

 

 

mysql> explain select * from employees where name='LiLei'  
  order by position,age;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |      100 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
1 row in set

和Case 3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为

name,age,position，但是排序的时候age和position颠倒位置了。

 

 

mysql> explain select * from employees where name='LiLei'  and age=18  order by position,age;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 78      | const,const |    1 |      100 | Using index condition |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-----------------------+
1 row in set

在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，

不会出现Using filesort。

 

mysql> explain select * from employees where name='xxx'  order by age asc,position desc;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table     | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_name_age_position | idx_name_age_position | 74      | const |    1 |      100 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+---------------------------------------+
1 row in set

虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的

排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

mysql> explain select * from employees where name in('LiLei','xxx') order by age,position;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    3 |    66.67 | Using where; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
1 row in set

对于排序来说，多个相等条件也是范围查询

mysql> explain select * from employees where name >'a' order by name;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table     | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | idx_name_age_position | NULL | NULL    | NULL |    3 |      100 | Using where; Using filesort |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-----------------------------+
1 row in set

用了filesort ,使用覆盖索引优化：

mysql> explain select name,age,position from employees where name >'a' order by name;
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | employees | NULL       | index | idx_name_age_position | idx_name_age_position | 140     | NULL |    3 |      100 | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set
 

优化总结：

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index

效率高，filesort效率低。

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

1) order by语句使用索引最左前列。

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

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group

by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中

的限定条件就不要去having限定了