一篇读懂MySQL调优

haidi8

于 2023-03-12 10:32:08 发布

阅读量330

点赞数

分类专栏：面试专题文章标签： mysql 数据结构 b树

本文链接：https://blog.csdn.net/qq_45058208/article/details/129472883

版权

面试专题专栏收录该内容

17 篇文章 0 订阅

订阅专栏

一、基本概念

1.1、什么是索引

索引是用于加快数据库查询的一种有序的数据结构

1.2、索引作用是什么？

加快数据检索(如果没建索引，会进行逐行比对查询，效率很低。)

1.3、什么是块？什么是页？

计算机的存储空间是一块一块的，通常一块用完了再用另一块，每个存储块是4k
一般mysql每次IO是一页，一页是4块16k

1.4、索引查询快的主要原因是什么？

减少了磁盘IO次数（影响查询效率最大的因素是磁盘IO，并且每次IO是1页「4块，16k」，数据量比较大是需要较多次IO）

1.5、索引的数据结构是什么？

B+树

二、为什么使用B+树存储索引？

B+树减少了IO操作且底层节点是所有数据的有序排列，便于范围查找，排序查找，分组查找以及去重查找

首先，索引存储是k-v格式的，即索引-行数据，那么常见可以选择的数据结构有：hash表、二叉树、B树、B+树。

2.1、hash表

【hash表】

需要很优良的hash算法避免数据散列带来的浪费空间和查询快慢不均匀，并且hash表是无序的，不利于范围查找，相当于全表扫描，但是由于hash是在内存中进行的，所以即使如此依旧很快，但是核心问题就是在内存中太消耗内存。

2.2、二叉树

【二叉树】

每个节点只有2个子树，如果数据量很大的时候，那树的层级就很深，查找次数会很大，影响查询速率。
并且如果存储的索引列是递增的，那会变成单边增长成一个链表的形式，仍然需要逐行对比查询，效率跟没建索引差不多。

2.3、红黑树

【红黑树】
红黑树会自动分叉，在索引递增存储时不会形成链表的形式
但是与二叉树同样的问题，每个节点只有2个子树，如果数据量很大的时候，那树的层级就很深，查找次数会很大，影响查询速率。

2.4、B树

【B树】

B树相当于二叉树来说，每个节点可以有多个子树，这样就保证了层级较浅，查询效率提高，但是，由于索引数据存在磁盘，查询需要IO操作，IO操作相对内存来说是非常慢的，因此需要尽量减少IO操作次数，因此读取数据是按照磁盘块(文件系统读写数据的最小单位)读取，而Innodb中页(内存的最小存储单位。页的大小通常为磁盘块大小的 2^n 倍)的默认大小是16kb，由于B树的页中存储的是k-v，大大降低了页中存储的索引数，因此，增加了IO操作次数，降低了查询效率。另外，B树的数据分散在各个节点，要实现范围查找，排序查找，分组查找以及去重查找相对较复杂，也降低了速率。

2.5、B+树

【B+树】

B+树除底层页以外，页中只存k，这样单页中就能存储更多的索引值，减少了IO操作，从而加快了查询速率。

因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，并且叶子/非叶子节点的每页之间都通过指针连接，形成双线链表，从而B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

这样如果我们的B+树一个节点可以存储1000个键值，第三层需要存数据，假如是存16个值，那么3层B+树可以存储1000×1000×16=1600万个数据。一般根节点是常驻内存的，所以一般我们查找1600万条数据，只需要2次磁盘IO。

三、索引优化

3.1、理论知识

避免索引失效的核心：索引查找就是二分，凡是不能二分查找的情况都属于索引失效的情况。

3.1.1、联合索引

联合索引排序规则相当于sql的order by col1, col2, col3

3.1.2、最左前缀原则

例如我们定义了(name,password)两个联合索引字段，我们使用如下sql索引可以生效。

where name = '张三' and password = '2';

使用如下sql，索引也是可以生效的，因为在mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划

where password = '1' and name = '王五';

但是，使用如下sql索引就会失效

where password = '1'and name like '%张%';

联合索引，最左的索引在每一层中都是有序的，后面的索引只有在左边索引之下是有序的，整体是无序的，因此条件中不带最左的是无法使用二分法查找的，因此必须保证**最左原则（查询条件中必须带联合索引的最左的索引，查询才会走索引）**

3.1.3、主键索引与非主键索引与回表

InnoDB的主键索引是聚集索引，非主键索引是非聚集索引

非主键索引叶子节点只存了主键值，需要回表查询，因此主键索引比非主键索引（稀疏索引）快

(回表：非主键索引只能获得主键索引的值，并不能获得其他字段值，想要获取其他字段值需要携带主键值返回到主键索引再次检索出想要的其他字段值)

3.1.4、覆盖索引

有如下两个sql，已知id为主键，name为索引

-- 语句A
select id from user_table where name= '张三';
-- 语句B
select password from user_table where name= '张三';

【语句A】：因为name的索引树的叶子节点包含id、name，因此就不需要回表了，name已经覆盖了我们的查询需求，我问称为覆盖索引

【语句B】：我们需要通过name查到id后回表查到password

因此，当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引树中，可以直接使用索引查询而不需要回表。这就是覆盖索引。

例如上面的语句B是一个高频查询的语句，我们可以建立(name,password)的联合索引，这样，查询的时候就不需要再去回表操作了，可以提高查询效率，所以使用覆盖索引是一个常用的性能优化手段。

3.1.5、索引下推

如下一个sql，其中name和is_del是联合索引

select * from t_user where name like '张%' and is_del=1;

在MySQL 5.6之前，只能从匹配的位置一个个回表。到主键索引上找出数据行，再对比字段值

根据（username,is_del）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据，然后再筛选出未删除的用户数据。过程如下图(每一个虚线箭头表示回表一次)：

在MySQL 5.6中引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对**联合索引**中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

InnoDB在(name,is_del)索引内部就判断了数据是否逻辑删除，对于逻辑删除的记录，直接判断并跳过。在我们的这个例子中，只需要对ID1、ID4这两条记录回表取数据判断，就只需要回表2次

我们也需要注意：

innodb引擎的表，索引下推只能用于二级索引，因为innodb的主键索引树叶子结点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果
索引下推一般可用于所求查询字段（select列）不是/不全是联合索引的字段，查询条件为多条件查询且查询条件子句（where/order by）字段全是联合索引

3.3、常见优化

-- 建立联合索引(另外id为主键)
ALTER TABLE table1 ADD INDEX idx_table1_nameAgePos(name, age, pos);

全职匹配我最爱

-- 条件与索引一一对应，方便覆盖索引
SELECT * FROM table1 WHERE name = 'July' AND age = 25 AND pos = 'dev';

最左前缀要遵守

-- 查询条件中必须带联合索引的最左的索引，查询才会走索引
SELECT * FROM table1 WHERE name = 'July' AND age = 25;

中间兄弟不能断

-- 跳过了索引age，则只有name走索引，后面的pos不走索引（在name='Jack'条件之下全表扫描）
SELECT * FROM table1 WHERE name='Jack' AND pos = 'dev';

索引列上少计算，varchar引号不可丢

-- 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
-- 其中后面'Jack'引号不能丢也是如此，如果是丢了，则会进行自动类型转换，就相当于计算。
SELECT * FROM table1 WHERE left(name, 4) = 'Jack';

范围之后全失效

-- age是范围查询，因此age后面的索引失效，即pos不走索引
SELECT * FROM table1 WHERE name = 'July' AND age > 25 AND pos = 'dev';

Like百分写最后

-- Like百分号写在后面才会走索引
SELECT * FROM table1 WHERE name like 'July%' AND age = 25;
SELECT * FROM table1 WHERE name like '%July' AND age = 25; # %July和%July%均不走索引

覆盖索引不写*

-- 尽量使用覆盖索引(尽量只查联合索引列+主键列)，减少select *
select id, name, age, pos from table1 where name = 'July' AND age = 25 AND pos = 'dev';

其他

命名短小原则，索引命名过长会使索引文件变大，损耗内存。

3.4、索引小题

企业微信截图_908a8b598b084a10a8926d06b710bf5a.png

【答案】：4

【解析】

1、在userNum这个索引树上只有userNum和id, select * 需要回表

2、同上

3、在name, phoneNum联合索引树上只有id, name, phoneNum,而查询字段需要userNum依旧需要回表

4、在name, phoneNum联合索引树上有id, name, phoneNum，因此查id, phoneNum是不需要回表的。

四、索引问答

4.1、什么时候建索引？

给条件和排序查询中常用到的字段建立索引

建立索引常用的规则如下：

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；

4.2、索引的缺点是什么？为什么不建议建太多索引？

【索引的缺点】
1.创建索引和维护索引(当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护)要耗费时间，这种时间随着数据量的增加而增加（insert/update/delete变慢）

2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大

【为什么不建议建太多索引】
太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大

4.3、索引用整型还是字符串？

用整型
1、整型占用空间较小，根节点可以存储更多的key(索引值)
2、因为索引是有排序的，整型排序比较快，并且字符串不容易做到自增，新增数据需要分裂之前的排序

4.4、索引要不要自增？为什么？

要

索引自增可以减少分裂。如果不是自增的，那索引值是无序的，但是B+树底层节点是排序的，因此当需要插入的页满了，则需要分裂为两个页，上层也需要做出相应变化。而如果是自增的，那只需要往后追加，不会影响前面的数据。

4.5、Mysql存储引擎(聚集索引、非聚集索引)？

存储引擎是作用于表的，不同表可以设置不同存储引擎
【MyISAM】

索引文件与数据文件是分开存储的（非聚集索引「辅助索引」）
叶子节点存储的是数据的地址

【Innodb】

索引文件与数据文件不分开存储（聚集索引）
叶子节点存储的是数据

4.6、什么是覆盖索引？

当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引树中，可以直接使用索引查询而不需要回表。这就是覆盖索引。

4.7、什么是索引下推?

在索引遍历过程中，对**联合索引**中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

4.8、举例几个索引优化点

见3.3、常见优化

五、深度MySQL调优

5.1. MySQL深入调优

CREATE TABLE `employees` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR (24) NOT NULL DEFAULT '' COMMENT '姓名',
	`age` INT (11) NOT NULL DEFAULT '0' COMMENT '年龄',
	`position` VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
	`hire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
	PRIMARY KEY (`id`),
	KEY `idx_name_age_position` (`name`, `age`, `position`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = '员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

5.1. MySQL如何选择合适的索引

EXPLAIN select * from employees where name > 'a';

在这里插入图片描述
如果用name索引需要遍历name字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比较高，可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就能拿到所有结果，如下：

EXPLAIN select name,age,position from employees where name > 'a';

在这里插入图片描述

EXPLAIN select * from employees where name > 'zzz' ;

在这里插入图片描述

对于上面这两种 name>‘a’ 和 name>‘zzz’ 的执行结果，mysql最终是否选择走索引或者一张表涉及多个索引，mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭

在这里插入图片描述

-- 开启trace，必须在MySQL命令行中执行
set session optimizer_trace="enabled=on",end_markers_in_json=on; 
select * from employees where name > 'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

select * from employees where name > 'a' order by position | {
  "steps": [
    {
      "join_preparation": { //第一阶段：SQL准备阶段
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {//第二阶段：SQL优化阶段
        "select#": 1,
        "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 */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [//表依赖详情
              {
                "table": "`employees`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [//预估表的访问成本
              {
                "table": "`employees`",
                "range_analysis": {
                  "table_scan": { //全表扫描情况
                    "rows": 3, //扫描行数
                    "cost": 2.65 //查询成本
                  } /* table_scan */,
                  "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 */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_name_age_position",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "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,//是否使用覆盖索引
                        "in_memory": 1,
                        "rows": 3, //索引扫描行数
                        "cost": 1.31, //索引使用成本
                        "chosen": true //是否选择该索引
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": { 
                    "range_access_plan": {
                      "type": "range_scan", 
                      "index": "idx_name_age_position",
                      "rows": 3,
                      "ranges": [
                        "a < name"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 3,
                    "cost_for_plan": 1.31,
                    "chosen": true 
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`employees`",
                "best_access_path": { //最优访问路径
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 3,
                      "access_type": "range", //访问类型，范围扫描
                      "range_details": {
                        "used_index": "idx_name_age_position"
                      } /* range_details */,
                      "resulting_rows": 3,
                      "cost": 1.61,
                      "chosen": true, //确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 1.61,
                "sort_cost": 3,
                "new_cost_for_plan": 4.61,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`employees`.`name` > 'a')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "(`employees`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "optimizing_distinct_group_by_order_by": {
              "simplifying_order_by": {
                "original_clause": "`employees`.`position`",
                "items": [
                  {
                    "item": "`employees`.`position`"
                  }
                ] /* items */,
                "resulting_clause_is_simple": true,
                "resulting_clause": "`employees`.`position`"
              } /* simplifying_order_by */
            } /* optimizing_distinct_group_by_order_by */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "steps": [
              ] /* steps */,
              "index_order_summary": {
                "table": "`employees`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "idx_name_age_position",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`employees`",
                "original_table_condition": "(`employees`.`name` > 'a')",
                "final_table_condition   ": "(`employees`.`name` > 'a')"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`",
                "pushed_index_condition": "(`employees`.`name` > 'a')",
                "table_condition_attached": null
              }
            ] /* refine_plan */
          },
          {
            "considering_tmp_tables": [
              {
                "adding_sort_to_table": "employees"
              } /* filesort */
            ] /* considering_tmp_tables */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {//第三阶段：SQL执行阶段
        "select#": 1,
        "steps": [
          {
            "sorting_table": "employees",
            "filesort_information": [
              {
                "direction": "asc",
                "expression": "`employees`.`position`"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {//文件排序信息
              "memory_available": 262144,
              "key_size": 40,
              "row_size": 190,
              "max_rows_per_buffer": 15,
              "num_rows_estimate": 15,
              "num_rows_found": 3,
              "num_initial_chunks_spilled_to_disk": 0,
              "peak_memory_used": 32792,
              "sort_algorithm": "std::sort",
              "sort_mode": "<fixed_sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

结论：MySQL在执行SQL过程中会对SQL进行分析，并对全表扫描和当前SQL适用的所以扫描做出比对，择优选择扫描方式。

-- 分析完毕，需要关闭trace
set session optimizer_trace="enabled=off";

5.2. Order By与Group By优化

在这里插入图片描述

利用最左前缀匹配原则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

在这里插入图片描述

从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了 age，出现了Using filesort。

在这里插入图片描述

查找只用到索引中name字段部分，age和position用于排序，无Using filesort。

在这里插入图片描述

和上一案例中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为 name,age,position，但是排序的时候age和position颠倒位置了。

在这里插入图片描述

与上一案例对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

在这里插入图片描述

虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。

在这里插入图片描述

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

在这里插入图片描述

虽然使用了范围查询，但遵循最左前缀匹配法则，而排序也使用的该字段，所以没有出现Using filesort。MySQL8才支持。

优化总结：

MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
order by满足两种情况会使用Using index。
- order by语句使用索引最左前缀。
- 使用where子句与order by子句条件列组合满足索引最左前缀。
尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
如果order by的条件不在索引列上，就会产生Using filesort。
能用覆盖索引尽量用覆盖索引
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

5.3. 文件排序原理详解

单路排序

是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
双路排序（又叫回表排序模式）

是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >

MySQL 通过比较系统变量 max_length_for_sort_data(默认4096字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。 如果 max_length_for_sort_data 比查询字段的总长度大，那么使用单路排序模式；如果 max_length_for_sort_data 比查询字段的总长度小，那么使用双路排序模式。

-- 开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on; 
select * from employees where name = 'Lilei' order by position;
select * from information_schema.OPTIMIZER_TRACE;

"join_execution": {
    "select#": 1,
    "steps": [
        {
            "sorting_table": "employees",
            "filesort_information": [
                {
                    "direction": "asc",
                    "expression": "`employees`.`position`"
                }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
                "usable": false,
                "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
                "memory_available": 262144,
                "key_size": 40,
                "row_size": 190,
                "max_rows_per_buffer": 1323,
                "num_rows_estimate": 18446744073709551615,
                "num_rows_found": 1,
                "num_initial_chunks_spilled_to_disk": 0,
                "peak_memory_used": 0,
                "sort_algorithm": "none",
                "sort_mode": "<fixed_sort_key, packed_additional_fields>"  //单路排序
            } /* filesort_summary */
        }
    ] /* steps */
} /* join_execution */

-- employees表所有字段长度总和肯定大于10字节
SET  @@GLOBAL.max_length_for_sort_data = 10;
select * from employees where name = 'Lilei' order by position;
select * from information_schema.OPTIMIZER_TRACE;

单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

如果 MySQL 排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。

如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。

所以，MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认256M)，mysql很多参数设置都是做过优化的，不要轻易调整。

5.4. 分页查询优化

-- 如果存在一个名为temp的存储过程，就将其删除掉
DROP PROCEDURE IF EXISTS temp;
-- 创建存储过程
CREATE PROCEDURE temp()
BEGIN
  DECLARE num INT(11) DEFAULT 0;
	DECLARE error TINYINT(1) DEFAULT 0;
	-- 定义一个SQLException的处理器，一旦有SQLException发生，就会改变变量error的值
	-- CONTINUE关键字的主要作用表示如果出现了异常，存储过程继续执行
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error=1;
	-- 开启事务
	START TRANSACTION;
	WHILE num < 100000 DO
		INSERT INTO employees(`name`, `age`, `position`, `hire_time`) VALUES (CONCAT('LiLei',num), FLOOR(RAND()*100), 'manager', SYSDATE());
		SET num = num + 1;
	END WHILE;
	IF error = 0 THEN COMMIT;
	ELSE ROLLBACK;
	END IF;
END


CALL temp();

很多时候我们业务系统实现分页功能可能会用如下sql实现

SELECT * FROM employees LIMIT 10000,10;

表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

优化技巧：

根据自增且连续的主键排序的分页查询

-- 没有排序，表示通过主键排序
SELECT * FROM employees LIMIT 90000,5;
SELECT * FROM employees WHERE id > 90000 LIMIT 5;

上面两条SQL语句执行的结果是一样的，但是效率却相差很大。

EXPLAIN SELECT * FROM employees LIMIT 90000,5;
EXPLAIN SELECT * FROM employees WHERE id > 90000 LIMIT 5;

这种优化必须满足两个条件：主键自增并且是连续的，结果是按照主键排序或者结果没有写排序字段。

根据非主键字段排序的分页查询
```
EXPLAIN SELECT * FROM employees ORDER BY name LIMIT 90000,5;
```
扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

知道不走索引的原因，那么怎么优化呢？其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下：
```
EXPLAIN SELECT * FROM employees e INNER JOIN (SELECT id FROM employees ORDER BY name LIMIT 90000,5) ed ON e.id = ed.id;
```

5.5. Join关联查询优化

对于关联SQL的优化：

关联字段加索引
小表驱动大表

haidi8

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
一篇读懂MySQL调优

计算机的存储空间是一块一块的，通常一块用完了再用另一块，每个存储块是4k一般mysql每次IO是一页，一页是4块16k当sql语句的所求查询字段（select列）和查询条件字段（where子句）全都包含在一个索引树中，可以直接使用索引查询而不需要回表。这就是覆盖索引。在索引遍历过程中，对**联合索引**中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
复制链接

扫一扫