MySQL —— explain 查看执行计划与 MySQL 优化
文章目录
explain 查看执行计划
使用 explain 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。可以获得关于查询的执行计划的详细信息，分析查询语句或者表结构的性能瓶颈。在 5.6 以及以后的版本中，除了 select，其他比如 insert，update 和 delete 均可以使用 explain 查看执行计划，从而知道 mysql 是如何处理 sql 语句，分析查询语句或者表结构的性能瓶颈。
explain 的作用——查看执行计划
通过 explain+sql 语句可以知道如下内容：
- 表的读取顺序（对应 id）；
- 数据读取操作的操作类型（对应 select_type）；
- 可以使用的索引（对应 possible_keys）；
- 实际使用的索引（对应 key）；
- 表之间的引用（对应 ref）
- 每张表被优化器查询的行数 。（对应 rows）；
explain 查看执行计划返回信息详解
查看执行计划返回列如下：
表的读取顺序（id）
-
id 列指示了各个表在查询中的读取顺序。对于联合查询，id 的值越大，表示该表的读取优先级越高。相同的 id 表示这些操作是同时进行的。
-
id 相同，执行顺序由上到下。如下：t1->t3->t2。
-
id不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。如下：t3->t1->t2。
-
id同时存在相同与不同。id 越大越先执行，id 相同从上到下顺序执行。
-
规则：
- id 相同，执行顺序由上至下；
- id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。
查询类型（select_type）
表示查询中每个选择的类型。主要用于区别普通查询，联合查询，子查询等的复杂查询。常见的值包括：
- simple —— 简单的 select 查询，查询中不包含子查询或者 UNION 查询；
- primary —— 查询中若包含任何复杂的子部分，最外层查询被标记；
- subquery —— 在 select 或 where 列表中包含的子查询；
- derived —— 在 from 列表中包含的子查询被标记为 derived（衍生），MySQL 会递归执行这些子查询，把结果放到临时表中。
- union —— 如果第二个 select 出现在 UNION 之后，则被标记为 UNION；如果 union 包含在 from 子句的子查询中，外层 select 被标记为 derived。
- union result：从 UNION 表获取结果的 SELECT。
数据库表名（table）
- 显示这一行的数据是关于哪张表的。
联接类型（type）
显示查询使用的连接方法，按照性能从好到坏的排序：system > const > eq_ref > ref > range > index > ALL。
-
system：表示表只有一行（即系统表，例如 information_schema 中的表），是最优的连接类型。
在这种情况下，MySQL 不需要进行任何查找，直接返回结果。
-
const：通过主键或唯一索引在表中找指定常量值的一行记录。const （常量值）与 primary key 或者 unique 索引比较，最多只匹配一行数据。
-- 找到一行数据，where 条件使用 = -- id为主键 EXPLAIN SELECT * FROM tb1 WHERE id = 1; -
eq_ref：通过主键或唯一索引扫描，通常在连接查询中使用，每个匹配的行只返回一行数据。
-- 找到一行数据，where 条件使用 = -- 在多表连接中，使用主键或唯一索引进行连接 EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id; -
ref：通过非唯一性索引扫描，返回匹配的多个行，本质上也是一种索引访问，可能会找多个符合条件的行，属于查找和扫描的混合体。
-- 找到多行数据，where 条件使用 =，也就是有多行满足等于条件的行 -- ategory_id 是一个非唯一索引 EXPLAIN SELECT * FROM articles WHERE category_id = 5; -
range：使用索引进行范围扫描。检索给定范围的行，返回一个范围的行。一般就是 where 语句中出现了 between，<，>，in 等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。
-- 找到多行数据，where 条件使用范围条件 -- price 是一个索引； EXPLAIN SELECT * FROM products WHERE price BETWEEN 10 AND 20; -
index： 全索引扫描，表示扫描整个索引而不是表。all 与 index 区别为 index 类型只遍历索引树。通常比全表扫描快，因为索引文件比数据文件小很多。（相当于索引覆盖，只在索引文件查找便能获取结果）
-- name 是一个索引； EXPLAIN SELECT * FROM products WHERE name LIKE 'A%'; -
all：全表扫描，效率最低。遍历全表以找到匹配的行。（不使用索引或索引失效）
注意：一般保证查询至少达到 range 级别，最好能达到 ref。
SELECT * FROM users;
可用的索引（possible_keys）
- 显示可能应用在这张表中的索引。查询涉及的字段上若存在多个索引，则索引将被列出，但不一定被查询实际使用。如果该字段为 NULL，表示没有可用的索引。（可能会列出如 idx_name、idx_age 等）
实际使用的索引（key）
- 显示 MySQL 实际使用的索引。如果该字段为 NULL，表示查询没有使用索引。查询中如果使用覆盖索引，则该索引和查询的 select 字段重叠。（如果查询使用了 idx_age 索引，则显示为 idx_age）
使用的索引长度（key_len）
- 表示 MySQL 在查询中使用的索引的字节数，帮助评估索引选择的效率。在不损失精度的情况下，长度越短越好。如果键是 NULL，则长度为 NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。如果 key_len 为 4，表示使用的索引长度为 4 字节。
表之间的引用（ref）
- 指出与哪个列或常量进行比较，通常用于指示索引的连接条件。即在索引查找过程中，用于与索引相匹配的列或常量。通常用于说明如何通过某个索引查找相关的记录。
- ref 字段可能包含以下几种值：
- 常量：如果查询条件中使用了常量，ref 字段的值将是 const，表示该常量用于查找索引。
- 列名：如果是通过其他列进行查找，ref 字段会显示相应的列名。例如，如果通过主键或其他索引列查找，可能会显示为表名和列名（如 student.id）。
- NULL：如果没有使用索引进行查找，该字段将显示为 NULL。
- 如；
-- student 表有一个复合索引 (age, school) EXPLAIN SELECT name FROM student WHERE age = 20 AND school = 'XYZ School';
key 列显示使用了 idx_age_school 索引。+----+-------------+--------+-------+---------------+---------+---------+-----------------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+-----------------+------+-----------------------------+ | 1 | SIMPLE | student | ref | idx_age_school| idx_age_school | 8 | const,const | 50 | Using where; Using index | +----+-------------+--------+-------+---------------+---------+---------+-----------------+------+-----------------------------+
ref 列显示为 const,const，表示查询条件中的 age 和 school 的值是常量，用于与索引匹配。
每张表被优化器查询的行数（rows）
- 根据表统计信息以及索引选用情况，大致估算出找到所需的记录需要扫描的行数。这个数字越小，查询的效率通常越高。如果显示 100，表示 MySQL 预计需扫描 100 行数据。
额外信息（Extra）
其他额外信息，提供关于查询执行的更多细节，也是十分重要的额外信息。常见的值包括：
-
Using filesort：说明 mysql 使用了一种额外的排序机制，而不是直接利用索引中已经排序的数据。这种情况下，MySQL 需要在内存中或临时文件中对结果集进行排序，这个过程被称为“文件排序”。（需要避免这种情况，会影响性能）
MySQL 中无法利用索引完成排序操作称为 “文件排序”，即无法使用索引已经排好序的去排序。当查询中包含 ORDER BY 子句，并且无法有效地利用索引进行排序时，就会出现 Using filesort。尽量使用索引去排序。
-
Using temporary：使用了临时表保存中间结果，需要避免这种情况，会影响性能。这通常发生在需要对结果进行排序 order by 和分组查询 group by，并且无法有效地利用索引进行排序或分组。排序和分组尽量使用索引。
-
Using index：表示相应的 select 操作用使用了覆盖索引，避免访问了表的数据行。（这种情况是很好的）
- 如果同时出现 using where，表明索引被用来执行索引键值的查找，同时还需要从数据表中获取与这些索引键相对应的行数据（索引用来执行查找，去数据表中取数据回来）。这种情况通常表明查询较为复杂，虽然使用了索引，但仍然需要访问数据表。
- 如果没有同时出现 using where，表明索引用来读取数据而非执行查询动作（查询字段与索引字段是同列，可以直接从索引中取出数据返回，不需要去数据表中取数据。）
-
Using where：表明使用 where 过滤。
-
using join buffer：使用了连接缓存。
-
impossible where： where 子句的值总是 false，不能用来获取任何元组。
-
select tables optimized away：在没有 group by 子句的情况下，基于索引优化 Min、max 操作或者对于 MyISAM 存储引擎优化 count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
-
distinct：优化 distinct 操作，在找到第一匹配的元组后即停止找同样值的动作，使用 DISTINCT 可能会导致查询性能下降，尽量少用，或者尽量让 DISTINCT 操作的字段是索引字段；
MySQL 优化
优化大致步骤：
-
慢查询的开启并捕获。通过配置慢查询日志来捕获执行时间超过指定阈值的查询。通过设置以下参数，可以启用慢查询日志：
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 设置慢查询的阈值（单位：秒） -
explain+慢SQL分析。对捕获到的慢查询，使用 EXPLAIN 关键字分析查询的执行计划。
-
show profile 查询 SQL在 Mysql 服务器里面的执行细节和生命周期情况。使用 SHOW PROFILES 可以查看查询的详细执行时间，分析具体的时间消耗在哪里。
-
SQL数据库服务器的参数调优。例如：
- innodb_buffer_pool_size：增加此参数可以提高 InnoDB 存储引擎的性能，尤其是对于大数据集。
- query_cache_size：如果适用，可以适当调整查询缓存的大小。
- max_connections：根据并发连接的需求调整最大连接数。
优化慢查询思路：
- 分析语句，是否加载了不必要的字段/数据。
- 仅选择所需的字段，而不是使用 SELECT *。
- 使用 LIMIT 子句来限制返回的行数，减少处理的数据量。
- 分析 SQL 执行句话，是否命中索引等。
- 如果 SQL 很复杂，优化 SQL 结构。
- 如果表数据量太大，考虑分表。
表结构优化
-
尽量使用数字型字段，数字型字段在比较时通常比字符型字段更高效。
若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
-
尽可能的使用 varchar 代替 char
变长字段存储空间小，可以节省存储空间。使用 VARCHAR 适合于长度变化较大的字段，例如名称、地址等。对于固定长度的字段（如状态码），可以考虑使用 CHAR。
-
当索引列大量重复数据时，可以把索引删除掉
索引的主要目的是加速查询。如果某个列的值高度重复（如性别字段通常只有少数几个值），则索引的效益会大打折扣，因为数据库在查找时仍然需要扫描大量的重复值。
索引会占用额外的存储空间，并且在插入、更新或删除数据时，需要维护索引，这会增加性能开销。
-
合理规范化，通过将数据分散到多个表中，消除冗余，减少数据重复。确保每个表只存储一类数据，遵循第三范式（3NF）。
-
考虑分库分表，如果表数据量太大，可以考虑分库、分表、分区。
索引优化
索引优化策略
-
最好全值匹配；
-
最左前缀法则：如果索引了多列，查询从索引的最左前列开始，且不能跳过索引中的列；
-- 建立复合索引 index(a,b,c) where a=3; -- 使用索引a where a=3 and b=5; -- 使用索引a，b where a=3 and c=2 and b=4; -- 使用索引a，b,c where b=3 -- 不使用索引b where b=4 and c=5;-- 不使用索引b，c where a=3 and c=5;-- 使用索引a，不使用索引c where a=3 and b>4 and c=5;-- 使用索引a，b，不使用索引c where a=3 and b like 'KK%' and c=4;-- 使用索引a，b，c where a=3 and b like '%KK' and c=4;-- 使用索引a，不使用索引b，c -
尽量使用覆盖索引，只访问索引的查询（索引列和查询列一致），减少 select *，不要返回用不到的任何字段；
-
尽量避免在 where 子句中对字段进行表达式操作，不在索引列上做任何操作（计算，函数，（自动或手动）类型转换），会导致索引失效而转向全表扫描；
select id from t where num/2=100应改为：
select id from t where num=100*2 -
存储引擎不能使用索引中范围条件右边的列，即范围条件之后的索引条件全失效；
-
MySQL 在使用不等于（!= 或 <>）的时候，无法使用索引，会导致全表扫描；
-
应尽量避免在 where 子句中对字段进行 null 值判断，is null，is not null 也无法使用索引；如：
SELECT * FROM tb WHERE name is null;-- 不走索引，全表扫描。可以在 name 上设置默认值，确保表中 name 列没有null值，然后这样查询时查默认值就表示查null。
-
like 以通配符开头（’%aa‘）索引会失效，变成全表扫描；即对于 like 查询，”%” 不要放在前面。
SELECT * FROM tb WHERE name LIKE 'kk%' ;-- 走索引； SELECT * FROM WHERE name LIKE "%kk";-- 不走索引。面试：如果就是需要使用 “%kk”、或 “%kk%”，怎么处理让索引不失效？这时可以使用覆盖索引，这样便能使用各种形式。（但是主要存在没有索引覆盖的字段，就会导致索引失效，全表扫描）
SELECT id,name,age FROM WHERE name LIKE "%kk%"; -- 走索引，其中id,name,age 字段都建立了索引。 SELECT id,name,age，email FROM WHERE name LIKE "%kk%"; -- 不走索引，其中id,name,age 字段都建立了索引，email 字段没建立索引。 -
字符串不加单引号，索引失效；
SELECT * FROM tb WHERE name = '100' ;-- 走索引；
SELECT * FROM WHERE name = 100 ;-- 不走索引。
-
尽量避免在 where 子句中使用 or 来连接条件，用它来连接时候会使索引失效；如：
select id from t where num=10 or num=20可以这样查询：
select id from t where num=10 union all select id from t where num=20 -
为较长的字符串使用前缀索引；
-
尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加 (a,b) 的索引，那么只需要修改原来的索引即可。
-
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by涉及的列上建立索引。
-
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female 几乎各一半，那么即使在 sex 上建了索引也对查询效率起不了作用。
-
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
-
应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
-
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
-
尽可能的使用 varchar/varchar 代替 char/char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
-
任何地方都不要使用 select * from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。
-
尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引)。
-
避免频繁创建和删除临时表，以减少系统表资源的消耗。
-
临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
-
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度;如果数据量不大，为了缓和系统表的资源，应先 create table，然后 insert。
-
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
-
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
-
使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
-
与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
-
在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
-
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
-
尽量避免大事务操作，提高系统并发能力。
索引不适合哪些场景?
- 数据量少的不适合加索引。
- 更新比较频繁的也不适合加索引；
- 区分度低的字段不适合加索引（如性别）。
join 连接索引
-
双表分析
左连接在右表的字段加索引，右连接在左表的字段加索引；
如：Left Join 条件用于确定如何从右表搜索行，左边数据一定有，所以右边数据一定要建索引。
- 左连接返回左表中的所有行以及右表中匹配的行。如果右表中没有匹配的行，则结果中相应的右表字段将为 NULL。
- 在进行左连接时，右表的连接条件字段应该建立索引。这是因为左表中的所有数据都会被返回，而右表的数据需要通过索引来快速查找匹配项。
小表驱动大表
- 即小的数据集驱动大的数据集。
- 小表：指的是数据量相对较小的表，通常在内存中处理时比较高效。
- 大表：指的是数据量较大、行数众多的表，通常需要更多的 I/O 操作来处理。
- 在执行连接查询时，选择小表作为驱动表，可以减少查询的复杂性和 I/O 负担，从而提高查询性能。
IN 与 EXISTS
-
in
select * from A where id in (select id from B);等价于：先执行 select id from B，扫描 B 表；后执行 select * from A where A.id = B.id，扫描 A 表。
- MySQL 首先执行子查询 SELECT id FROM B，生成一个包含所有 B 表 id 的列表。
- 然后，主查询会检查 A 表中的每一行，看看其 id 是否在这个列表中。
当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists。
-
exists
将主查询的数据放在子查询中做条件验证，根据结果 TRUE 和 FALSE 来决定主查询中的数据是否需要保留。EXISTS 子查询只返回 TRUE 或 FALSE ，因此子查询中的 SELECT * 可以是 SELECT 1 或者其他，MySql 的官方说在实际执行时会忽略 SELECT 清单，因此是没有什么区别的。
select * from A where exists (select 1 from B where B.id = A.id);等价于：先执行 select * from A，扫描 A 表；后执行 select * from B where B.id = A.id，扫描 B 表。
- MySQL 会遍历 A 表中的每一行，针对每一行执行子查询 SELECT 1 FROM B WHERE B.id = A.id。
- 子查询只需返回 TRUE 或 FALSE，表示 A 表中的当前行是否在 B 表中存在对应的 id。
当 A 表的数据集小于 B 表的数据集时，用 exists 优于 in。
in 后面跟的是小表，exists 后面跟的是大表。
NLJ
- 什么是 Nested-Loop Join？
- Index Nested-Loop Join 怎么优化连接？
- Block Nested-Loop Join 怎么优化连接？
Nested-Loop Join（NLJ、嵌套循环连接）
NLJ 算法：将驱动表/外部表的结果集作为循环基础数据，然后循环从该基础数据集中取一条结果数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表 join，则将前面的表的结果集作为循环数据，取到每行再到联接到下一个表中循环匹配，获取结果集返回给客户端。
-- t1称为外层表，也可称为驱动表。
-- t2称为内层表，也可称为被驱动表。
select * from t1 inner join t2 on t1.id=t2.tid
伪代码形式：
//伪代码表示：
List<Row> result = new ArrayList<>();
for(Row r1 in List<Row> t1){
for(Row r2 in List<Row> t2){
if(r1.id = r2.tid){
result.add(r1.join(r2));
}
}
}
在 Mysql 的实现中，Nested-Loop Join 有3种实现的算法：
-
Simple Nested-Loop Join（SNLJ）： 简单嵌套循环连接；
循环从第一个表中依次读取行，取到每行再到联接的下一个表中循环匹配。这个过程会重复多次直到剩余的表都被联接了。因为 NLJ 算法是通过外循环的行去匹配内循环的行，所以内循环的表会被扫描多次。如果 table1 有100条数据，table2 有100条数据，那么数据比较的次数=100 * 100 =10000次，这种查询效率会非常慢。
-
Index Nested-Loop Join（INLJ）：索引嵌套循环连接；
索引嵌套循环连接是基于索引进行连接的算法，索引是基于内层表的，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较， 从而利用索引的查询减少了对内层表的匹配次数，优势极大的提升了 join的性能。
原来的匹配次数 = 外层表行数 * 内层表行数 优化后的匹配次数= 外层表的行数 * 内层表索引的高度 -
Block Nested-Loop Join（BNLJ）：缓存块嵌套循环连接；
将外循环的多行缓存到 Join Buffer 里，然后拿 join buffer 里的数据批量与内层表的数据进行匹配，从而减少减少内循环的表被扫描的次数。例如，如果 10 行读入缓冲区并且缓冲区传递给下一个内循环，在内循环读到的每行可以和缓冲区的 10 行做比较。这样使内循环表被扫描的次数减少了一个数量级。
在选择 Join 算法时，会有优先级，理论上会优先判断能否使用 INLJ、BNLJ：
Index Nested-Loop Join > Block Nested-Loop Join > Simple Nested-Loop Join
Order By 与 Group By 关键字优化
Mysql 两种排序方式：文件排序（Using filesort）和扫描有序索引排序（Using index）。Index 效率高，它指 Mysql 扫描索引本身完成排序。FileSort 效率低。
Order By 关键字优化原则：
- order by 子句尽量使用 Index 方式排序，避免使用 FileSort 方式排序。
- 尽可能在索引列上完成排序方式，遵照索引建的最佳左前缀原则。
- 如果不在索引列上，FielSort 有两种算法：mysql 就要启动双路排序和单路排序。
Order by 满足以下两种情况，会使用 Index 方式排序：
-
Order by 语句使用索引最左前列。
-
使用 Where 子句与 Order by 子句条件列组合满足索引最左前列。
-- 创建索引 index a_ b_c(a, b, c) . --order by 能使用素引最左前缀，下面走索引 ORDER BY a ORDER BY a,b ORDER BY a, b, C ORDER BY a DESC, b DESC, c DESC -- 同升或同降 --如果WHERE使用素引的最左前缀定义为常量。则 order by 能使用素引，下面走索引 WHERE a=const ORDER BY b,c WHERE a=const ANDb = const ORDER BY c WHERE a=const OR DERBY b,c WHERE a=const AND b>const ORDER BY b, c --不能使用索引进行排序 ORDER BY a ASC, b DESC, c DESC /* 排序不一致*/ WHERE x = const ORDER BY b,c /*丢失a索引*/ WHERE a = const ORDER BY c /*丢失b索引*/ WHERE a = const ORDER BY a,d /*d不是索引的一一部分*/ WHERE a in (1，2) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询”*/
双路排序和单路排序的理解
-
双路排序
-
Mysql4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
-
从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。
（先读取磁盘返回每行排序列的数据，排序后再读取磁盘顺序取数据）
缺点：取一批数据，要对磁盘进行两次扫描，众所周知，I/O 是很耗时的，所以在 Mysql4.1 之后，出现了第二种改进的算法，就是单路排序。
-
-
单路排序
从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 I/O 变成了 I/O，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
（先读取磁盘返回每行所有列数据，再进行排序输出，避免二次读取磁盘）
可能存在的问题：在 sort_buffer 中，因为是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能去 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort_buffer 容量大小，再排……，从而导致多次 I/O。
优化策略：
-
增大 sort_buffer_size 参数的设置（用于单路排序的内存大小）。
-
增大 max_length_for_sort_data 参数的设置（单次排序字段大小。(单次排序请求)）。
-
不要使用 select *，去掉 select 后面不需要的字段（select 后多余的字段，排序的时候也会带着一起，很占内存，所以去掉没有用的）。
1、当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。 2、两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size。 不管用哪种算法，提高 sort_buffer_size 这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。 提高 max_length_for_sort_data 这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。
GROUP BY 关键字优化：
与 Order By 类似。
-
group by 实质是先排序后进行分组，遵照索引建的最佳左前缀。
-
当无法使用索引列，增大 max_length_for_sort_data 参数的设置和增大 sort_buffer_size 参数的设置。
-
where 高于 having，能写在 where 限定的条件就不要去 having 限定了。
原文地址：https://blog.csdn.net/longool/article/details/143813728
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！