IT评测·应用市场-qidao123.com技术社区

标题: 深入浅出 SQL 优化：全面提升查询性能的本事 [打印本页]

作者: 小秦哥 时间: 2024-8-3 11:23
标题: 深入浅出 SQL 优化：全面提升查询性能的本事
前言

在数据驱动的天下中，SQL 查询优化是提升数据库性能的关键。通过深入研究实行筹划，分析查询语句，优化表连接，限定条件和排序等方面，我们可以显著提高查询效率，为用户提供更快速的响应。本文将深入探究 SQL 优化的关键技能，资助您提高数据库的性能。
因本文篇幅较长，所以绘制了一张SQl查询优化概要的脑图，方便各人更体系的了解本文的结构。脑图如下：

一、表结构分析

1. 索引分析

为常用的查询条件创建符合的索引。但也要避免在频仍更新的表上创建过多索引，过多的索引会增加数据插入、更新和删除的开销。同时优先使用组合索引，以提高多条件查询的性能。考虑使用覆盖索引，以淘汰对主表的访问。
2. 数据类型分析

考虑使用符合的数据类型：例如，对于范围查询，使用整数可能比字符串更高效。
3. 思考反范式计划的适用场景与潜在风险

在某些情况下，为了提高查询性能，可以考虑使用反范式计划。但须要留意以下潜在风险：
3.1数据冗余

反范式可能导致数据重复存储，增加存储空间和维护成本。
3.2 数据一致性

须要确保应用步伐能够正确处理数据同步和更新操作，以维护数据的一致性。
3.3 更新性能

反范式可能导致更新操作变得更加复杂和耗时。
4. 关注临时表的创建与使用。

在某些复杂的查询中，可能须要创建临时表来存储中间效果。须要留意以下事项：
4.1.尽量淘汰临时表的使用，以降低体系资源的消耗。

4.2 使用符合的索引和数据类型优化临时表的性能。

4.3 实时清理临时表，避免占用过多的存储空间。

二、SQL 语句分析

1.查询列优化

1.1 去除SELECT子句中不须要的字段

避免使用*进行全字段查询，同时去除SELECT子句中不须要的字段，以淘汰数据传输和处理负担。
1.2 聚合函数优化

select子句中可以包含count(), min(), max()等聚合函数。而索引和列的可空性常常资助MySQL优化掉这些聚合函数。好比，为了查找一个位于B树最左边的列的最小值，那么直接找索引的第一行就可以了。如果MySQL使用了这种优化，那么在explain中看到“select tables optimized away”。同样地，没有where子句的count(*)通常也会被一些存储引擎优化掉（好比MyISAM总是保留着表行数的正确值）。
但是对于没有索引直接可用的min()和max()时，一般做不到很好地优化。可以尝试优化掉min()和max()，利用数据的有序性共同limit 1将SQL等价转化。
应用场景：

select min(user_id) from user where user_name = '小明';

复制代码

由于在user_name上没有索引，所以查询会扫描整个表。从理论上说，如果MySQL扫描主键，它应该在发现第一个匹配之后就立即停止，由于主键是按照升序分列的，这意味着后续的行会有较大的user_id。但是在这个例子中，MySQL会扫描整个表。一个变通的方式就是去掉min()，并且使用limit来改写这个查询，如下：

select user_id from user use index(primary) where user_name = ''小明' limit 1;

复制代码

这个通用策略在MySQL试图扫描高出须要的行时能很好地工作。
1.3 加入控制结构避免重复实现优化

访问同一张表的连续几个查询应该引起留意，即使它们嵌在if ... then ... else结构中，也是如此。看下面的应用场景：它包含了两个查询，但只引用到了一张表。

select count(*) from user_intention where user_id= 1 and intention_type = 1;
select count(*) from user_intention where user_id= 1 and intention_type = 2;

复制代码

这两个SQL访问同一张表bill，扫描同一个索引(index_accountid_expenditure_billtime)两次。这些开销属于重复开销。可以使用汇总技能，不用依次检查差别的条件了，一遍就能网络到多个效果，然后再测试这些效果，而不须要在访问数据库。应该汇总技能改写合并上述两个SQL得到新的SQL：

select sum(case intention_type when 1 then 1 else 0 end) as p1_sum, sum(case intention_type when 2 then 1 else 0 end) as p2_sum from user_intention where user_id=1;

复制代码

SQL只须要扫描索引(index_accountid_expenditure_billtime)一次以及回表bill访问一次就能得到效果，节省了很大的开销。
2、表连接优化

2.1 去除未涉及的表及非须要的连接

编写SQL时，应该检查一下在from子句中出现的那些表的作用。这此中主要会出现3种类型的表：
(1) 从中返回数据的表，其字段可能用于也可能没有用于where子句的过滤条件中；
(2) 该表中没有要返回的数据，但在where子句的条件中使用了该表的列；
(3) 仅作为另两种表之间的“粘合剂”而出现的表，使SQL可以通过表连接将那些1、2类型的表连在一起。
(4) 既没有从该表中返回数据，也没有过滤条件涉及该表，同时该表也没有到场联接。
对于第4类型的表，属于from子句中整个SQL未涉及的表，可以从from子句中去掉。
对于第3类型的表，举例分析：表A联接表B，联接列是ab，同时表B联接表C。此中表B是第3类型的表。须要分析，A表中的ab列是否可以为空值：如果可以空值，则A和B的联接对最后的效果集是有贡献的，不能去掉表B及A与B的联接；反之，不可以为空，如果表A可以直接联接表C的话，可以去掉表B，表A直接联接表C。上述结论是基于以下分析：空值的值是未知的，不能说它等于任何东西，两个空值也不相等。对于表A中的列ab可以为空值的情况，如果去掉了表B及表A与它的联接，那么表A中那些其它属性列的值符合过滤条件，但ab列对应值为空值的记录有可能进入最后的效果集。因此在这种情况，去掉表B及相关联接，可能改变效果集。
如下场景：

select a.amount from bill b, user u,account a where a.account_id=u.account_id and u.account_id=b.account_id and b.expenditure>100.00;

复制代码

分析SQL中表c的account_id不可以为空值，所以可以去掉表b及其相关的联接，改写成：

select a.amount from bill b,account a where a.account_id=b.account_id and b.expenditure>100.00;

复制代码

2.2 选择符合的连接方式

SQL中常见的连接方式包括内连接（INNER JOIN）、左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）、全连接（FULL JOIN）、嵌套循环连接（Nested Loop Join）、哈希连接（Hash Join）、排序合并连接（Merge Join）和笛卡尔积（Cartesian Product）等。
(1) 内连接（INNER JOIN）：返回两个或多个表中存在匹配的记录。如果在一个表中有匹配的值，则内连接就会返回这些值所在的行。它通常用于查询多个表中相关联的数据。
(2) 左外连接（LEFT JOIN）：返回左表中的所有记录和右表中匹配记录的组合。如果左表中的行在右表中没有匹配，则这些行仍会出现在查询效果中，但右表中对应的列将添补NULL值。
(3) 右外连接（RIGHT JOIN）：返回右表中的所有记录和左表中匹配记录的组合。如果右表中的行在左表中没有匹配，则这些行仍会出现在查询效果中，但左表中对应的列将添补NULL值。
(4) 全连接（FULL JOIN）：返回两个表中的所有记录。如果一个表中的行在另一个表中没有匹配，则这些行仍会出现在查询效果中，但另一个表中对应的列将添补NULL值。
(5) 嵌套循环连接（Nested Loop Join）：循环嵌套连接是最基本的连接，须要进行循环嵌套，这种连接方式的过程是从一个表中取出一条记录，然后在另一个表中查找与之匹配的记录，如果找到匹配的记录，则将这两条记录组合起来，如果没有找到匹配的记录，则继续从第一个表中取出下一条记录，重复这个过程，直到第一个表中的所有记录都处理完为止。这种连接方式适用于连接的表较小，并且连接条件简单的情况。
(6) 哈希连接（Hash Join）：将两个表的数据分别进行哈希处理，然后根据哈希值将两个表中的数据进行匹配，找到匹配的记录。这种连接方式适用于连接的表较大，并且连接条件复杂的情况。
(7) 排序合并连接（Merge Join）：将两个表按照连接条件进行排序，然后将两个表中的数据进行合并，找到匹配的记录。这种连接方式适用于连接的表较大，并且连接条件简单的情况。
(8) 笛卡尔积（Cartesian Product）：将两个表中的所有记录进行组合，形成一个新的效果集，效果会合的记录数为两个表中记录数的乘积。这种连接方式适用于生成所有可能的组合情况，通常用于测试或数据分析。大部分情况下我们都要在实际 SQL 中避免直接使用笛卡尔积，由于它会使“数据爆炸”，尤其是数据量很大的时间。假设A表中的数据为m行，B表中的数据有n行，那么A和B做笛卡尔积，效果为m*n行。
在MySQL中，只有一种join算法，就是nested loop join，它没有很多其他数据库提供的hash Join，也没有sort merge join。nested loop join实际上就是通过驱动表的效果集作为循环基础数据，然后将该效果会合的数据（联接键对应的值）作为过滤条件一条条地到下一个表中查询数据，最后合并效果。如果还有第三个表参与join，则把前两个表的join效果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。
好比一个表t1，t2，t3的联接，通过explain观察到的实行筹划中join的类型（explain中type行的值）是：

table join type
t1 range
t2 ref
t3 All

复制代码

则相应的这个联接的算法伪代码如下：

for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions
send to client
}
}
}

复制代码

2.3 尽量采用小表驱动大表的策略

针对MySQL这种比较简单join算法，只能通过嵌套循环来实现，根据表的数据量和连接条件，选择符合的连接顺序是很紧张的。如果驱动效果集越大，所需循环也就越多，那么被驱动表的访问次数自然也就越多，而且每次访问被驱动表，即使所需的IO很少，循环次数多了，总量也不可能小，而且每次循环都不能避免消耗CPU，所以CPU运算量也会跟着增加。但是不能仅以表的大小来作为驱动表的判定依据，如果小表过滤后所剩下的效果集比大表过滤得到的效果集还大，效果就会在嵌套循环中带来更多的循环次数。反之，所须要的循环次数就会更小，总体IO量和CPU运算量也会更少。所以，在优化join联接时，最基本的原则是“小效果集驱动大效果集”，通过这个原则来淘汰循环次数。
如下场景：
通过explain观察MySQL为该SQL订定的实行筹划：

select a.expenditure, b.balance from bill a, account b where a.account_id = b.account_id and a.payee=13301087 and b.account_bank='icbc.beijing.fengtai';

复制代码

join中选取表bill为驱动表。
如果通过explain观察，发现MySQL在join过程中选取的驱动表不是很符合的话，发起开始通过索引，再通过hint技能straight_join来干预MySQL，使其按照最优的方式选取驱动表，订定最优的实行筹划。
2.4 确保关联字段在驱动表上具备高效索引

于nested loop join算法，内层循环是整个join实行过程中实行次数最多的，如果每次内层循环中实行的操作能节省很少的资源，就能在整个join循环中节约很多的资源。
而被驱动表的join column能使用索引正是基于上述考虑的。只有让被驱动表的join条件字段被索引了，才能保证循环中每次查询都能通过索引敏捷定位到所需的行，如许可以淘汰内层一次循环所消耗的资源；否则只能通过扫表等操作来找到所须要的行。
例如：

select b.expenditure, a.balance from bill b, account a where a.account_id = b.account_id and b.payee=111 and a.bank='icbc';

复制代码

join过程中被驱动表join条件列account_id是primary key，通过主键索引每次内层循环定位所需的行非常快且开销非常小。
3、限定条件优化

3.1限定条件的分列与index的命中

由于MySQL中的MyISAM，InnoDB等引擎使用的索引主要都是B-Tree数据类型，所以对where子句中的限定条件的分列顺序会有一些限定。这些限定包括：

where子句中限定条件分列最好为对应索引的最左前缀；
where子句中的range(范围)条件后的限定条件可能会导致索引失效

对于1，MySQL 5.0以上的MySQL优化器可以自己调整where子句中限定条件的顺序以使用对应的索引。但是这个过程本身会增加优化器的压力，尤其是where子句中的限定条件和对应索引包含列较多时。所以发起在写SQL的where子句中考虑计划使用的索引中列的顺序，相应地调整where子句中限定条件的顺序，以满意最左前缀的限定。
对于2，可以考虑同时调整索引中列的顺序及where子句中限定条件的顺序：将range（范围）限定条件调整到where子句的最后（最右）部分；将range条件对应的列调整到索引中列顺序的最后(最右)部。
举例：

select account_id from bill where expenditure>1.00 and payee=111;

复制代码

如上语句，expenditure>1.00是一个范围限定条件，可以调整其至payee条件之后。同时计划索引index_payee_expenditure时考虑到这个问题，索引中列顺序为payee,expenditure)。

3.2 添加索引

（同上文的索引分析）为常用的查询条件创建符合的索引。但也要避免在频仍更新的表上创建过多索引，过多的索引会增加数据插入、更新和删除的开销。同时优先使用组合索引，以提高多条件查询的性能。考虑使用覆盖索引，以淘汰对主表的访问。
3.3 留意导致索引失效的场景

3.3.1 隐式转换

3.3.2 索引列使用了函数

3.3.3 索引列使用了列运算

3.3.4 如果是组合索引是否满意最左原则

查看组合索引列是否满意最左原则, 不满意(不使用第一列)可能会导致不走组合索引, 也可能是index skip scan （效率通常较低）
3.3.5 不当模糊查询

模糊查询 like 的常见用法有3种(只有第1种的会走索引，其他都会导致索引失效):
a.模糊匹配后面任意字符:like’张%’
b.模糊匹配前面任意字符:like’%张’
c.模糊匹配前后任意字符:like’%张%’
留意：当数据量高出某个百分比后会放弃走索引
3.3.6 使用反向筛选

曾经听到如许一种说法使用 !=、is not null、not in、not exsits等反向筛选是不会走索引的。这其实是不对的，至少不完全对。
MySQL中决定使不使用某个索引实行查询的依据就是成本够不敷小，要扫描的二级索引记录条数越多，那么须要实行的回表操作的次数也就越多，到达了某个比例时，使用二级索引实行查询的成本也就高出了全表扫描的成本（举一个极度的例子，比方说要扫描的全部的二级索引记录，那就要对每条记录实行一遍回表操作，自然不如直接扫描聚簇索引来的快）。
做了个验证：
一个大概3万数据的表，如果只有10多个记录是null值，is null走索引，not null和!=没走索引，如果大部分都是null值，只有部分几条数据有值，is null，not null和!=都走索引。
同样的not in、not exsits中扫除的值数量较少，数据库优化器可能会认为使用索引来快速查找这些值并扫除它们是高效的，那这时就会走索引。扫除的值数量很多，接近或高出了表中行数的一半，优化器可能会认为进行全表扫描比使用索引更高效，这时就不走索引。
3.3.7 范围限定in、exsits值数量过多

in、exsits子句中的值数量过多时，数据库优化器可能会选择全表扫描而不是使用索引
3.3.8 使用 or 操作符:当查询条件包含 or 连接的条件，索引也会失效。

在某些情况下，SQL语句中使用OR关键字可能会导致索引失效。详细是否失效须要看OR左右双方的查询列是否命中相同的索引。假设USER表中的user_id列有索引，age列没有索引。下面这条语句其实是命中索引的：

select * from `user` where user_id = 1 or user_id = 2;

复制代码

但是这条语句是无法命中索引的：

select * from `user` where user_id = 1 or age = 20;

复制代码

假设age列也有索引的话，依然是无法命中索引的。
3.4 优化范围限定条件

在SQL中常常会出现多个range限定条件。这里的范围限定条件包括>, <, between等。这些范围限定条件会对相应的索引使用造成影响。正常情况下，即使将这些范围条件调到where子句中的最后（最右）部，第一个范围限定条件后的范围条件对应的索引列也都无法在索引中同时被使用。

select account_id from bill where payee between 51906734 and 51907000 and expenditure>0.00;

复制代码

由于payee between 51906734 and 51907000这个条件，得到的实行筹划中只能使用索引index_payee_expenditure中的payee列，而无法使用expenditure列。
在SQL中出现范围限定条件后，可以考虑这个范围条件对应到数据时，包含的值是否是有限个（个数不是太多）。如果存在这种特性，可以将范围条件转换为多个等值条件in()。MySQL对于in()条件处理和等值条件一样，不会影响索引中其他列（右边列）的使用。针对上述应用场景，可以改为用in()：

select account_id from bill where payee in (51907000, 51906734, 51906740) and expenditure>0.00;

复制代码

经过改写的SQL就可以使用索引index_payee_expenditure中包含的全部列信息。
3.5 索引合并

index merge（索引合并）方法适用于通过多个等值条件index定位或range扫描搜刮数据行并将效果合并成一个的SQL语句。合并会产生并集、交集等。
对于下面的SQL，MySQL优化器会考虑使用index merge算法。

select id from user where user_id=1 or account_id=1;

复制代码

通过explain观察该SQL的实行筹划，得到MySQL同时使用了user_id列和account_id列上的两个索引。在这种情况下使用index merge会大大加速SQL的实行，可以将上面的SQL与下面的SQL进行比较。

select id from user ignore index(idx_accountid) where user_id=1 or account_id=1;

复制代码

同时须要指出如果or条件是在同一属性列上的，MySQL则会考虑使用该属性列上的索引，这和上面所讲的or条件在差别属性列上是差别的。（此时相当于IN）

select id from user where user_id=1 or user_id=2;

复制代码

index merge优化对于这种or条件的并集操作比较有效。对于and条件的交集操作，其效果不如联合索引。如：

select id from user where user_id=1 and account_id=1;

复制代码

建(user_id,accout_id)的联合索引，MySQL使用该联合索引的效果要优于使用index merge的效果。
对于or条件和and条件同时存在的where子句，MySQL优化器会优先使用and子句中的索引，而放弃使用or条件的index merge。如果能确定or条件的index merge优于and子句中的索引，可以使用ignore index(and子句的索引)hint干预MySQL优化器订定的实行筹划。
4. 排序优化

在MySQL中，order by的实现有如下两种类型：

通过有序索引直接得到有序的数据，如许不用任何排序操作即可得到满意要求的有序数据；
须通过MySQL的排序算法将存储引擎返回的数据进行排序后得到有序的数据。

order by子句场景：

select create_time from bill where payee=48370945 order by expenditure;
select account_id from bill where create_time between '2009-09-07' and '2009-09-10' order by expenditure;

复制代码

第一个SQL使用索引index_payee_expenditure。Order by使用索引须要满意一些条件：where子句中涉及的列加order by子句中涉及的列要满意要使用的索引的最左前缀；同时where子句中的条件必须是等值条件。
第二个SQL无法借助索引index_payee_expenditure实现order by，须要通过MySQL本身的排序算法完成order by操作。
利用索引实现数据排序是MySQL中实现效果集排序的最佳方法，可以完全避免由于排序盘算带来的资源消耗。所以，在优化SQL中的order by时，尽可能利用已有的索引来避免实际的排序盘算，甚至可以增加索引字段，这可以很大幅度地提升order by操作的性能。当然这须要从整体上权衡，不能因此影响其他SQL的性能。
5. 分页优化

奥妙运用分页本事，如合理使用ROW_NUMBER，避免使用BETWEEN AND，并在适当情况下传递上一页最后一行的 ID 进行分页。
三、实行筹划分析

深入研究实行筹划，偏重关注成本突出的语句，以确定哪些查询消耗了大量资源。仔细检察全表扫描，分析其合理性，并寻找替代方案以提升效率。同时，密切关注索引列是否按照预设的索引进行扫描，确保索引的有效利用。
1.查看select语句的实行筹划

1.1 MySQL explain输出信息介绍

MySQL Query Optimizer通过实行explain命令告诉我们它将使用一个怎么样的实行筹划来优化query。因此explain是在优化query中最直接有效的验证我们想法的工具。
要使用explain，只需把explain 放在查询语句的关键字select前面就可以了。MySQL会在查询里设置一个标记，当它实行查询时，这个标记会促使MySQL返回实行筹划里每一步的信息。用不着真正实行。它会返回一行或多行。每行都会显示实行筹划的每一个组成部分，以及实行的次序。
MySQL5.6以前只能表明select查询，其他语句只有通过重写这些非select语句为select，才能够被explain。
下面来详细表明下explain功能中展示的各种信息的表明。
1.1.1 id

MySQL Query Optimizer选定的实行筹划中查询的序列号。表示查询中实行select子句或操作表的顺序，id值越大优先级越高，越先被实行。id相同，实行顺序由上至下，id为NULL最后实行。
1.1.2. select_type

select_type 表示对应行所使用的查询类型，有以下几种类型：

simple：简单查询。查询不包含子查询和union;
primary：复杂查询中最外层的select ;
subquery：包含在 select 中的子查询（不在 from 子句中）;
derived：包含在 from 子句中的子查询。MySQL会将效果存放在一个临时表中；
union：在 union 中的第二个和随后的 select；

1.1.3. table

这一列表示 explain 的一行正在访问的的表名称。
当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先实行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。
1.1.4. type

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL 。
一般来说，得保证查询到达range级别，最好到达ref ；

NULL
mysql能够在优化阶段分解查询语句，在实行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不须要在实行时访问表
const, system

mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的效果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速率比较快。system是 const的特例，表里只有一条元组匹配时为system；

eq_ref
primary key 或 unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

explain select * from film_actor left join film on film_actor.film_id = film.id;

复制代码

ref
相比 eq_ref，不使用唯一索引，而是使用普通索引大概唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
(1) 简单 select 查询，name是普通索引（非唯一索引）
explain select * from film where name = ‘film1’;
( 2)关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。
1. explain select film_id from film left join film_actor on film.id = film_actor.fi
2. lm_id;
复制代码
range
范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定范围的行。
1. explain select * from actor where id > 1;
复制代码
index
扫描全索引就能拿到效果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速率还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。
1. explain select * from film;
复制代码
ALL
即全表扫描，扫描你的聚簇索引的所有叶子节点.通常情况下这须要增加索引来进行优化。
1. explain select * from actor;
复制代码

1.1.5. possible_keys

这一列显示查询可能使用哪些索引来查找 explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是由于表中数据不多，mysql认为索引对此查询资助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
1.1.6. key

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想逼迫mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。须要留意的是查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列。possible_keys说明哪一个索引能有助于查询，而key显示的是优化器采用哪一个索引可以最小化查询成本。
1.1.7. key_len

该列显示了使用的索引的索引键长度。由于索引的最左策略，因此通过该值可以盘算查询中使用的索引情况。
1.1.8. ref

这一列显示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
1.1.9. rows

一列表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所须要读取的行数。
1.1.10. Extra

这一列展示的是额外信息。常见的紧张值如下：

Using index：使用覆盖索引

覆盖索引界说：mysql实行筹划explain效果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询效果只通过辅助索引就能拿到效果，不须要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

Using where：使用 where 语句来处理效果，并且查询的列未被索引覆盖
Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
Using temporary：mysql须要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

(1) actor.name没有索引，此时创建了张临时表来distinct

explain select distinct name from actor;

复制代码

(2) film.name创建了idx_name索引，此时查询时extra是using index,没有用临时表

explain select distinct name from film;

复制代码

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则须要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

(1) actor.name未创建索引，会浏览actor整个表，生存排序关键字name和对应的id，然后排序name并检索行记录

explain select * from actor order by name

复制代码

(2) film.name创建了idx_name索引,此时查询时extra是using index

explain select * from film order by name;

复制代码

Select tables optimized away：使用某些聚合函数（好比 max、min来访问存在索引的某个字段

explain select min(id) from film;

复制代码

1.2 extend explain输出信息

explain extended和普通的explain很相似，但是它会告知服务器把实行筹划“反编译”成select语句，然后立即实行show warnings就能看到这些生成的语句。这些语句是直接来自实行筹划，而不是原始的SQL语句。
1.3 explain的局限

explain只是一个近似，没有更多的细节。偶然它是一个很好的近似，但是偶然它会阔别真真相况，以下就是它的几个局限性：

explain不会告诉你关于触发器、存储过程的信息或用户自界说函数对查询的影响情况。
explain不考虑各种cache。
explain不能显示MySQL在实行查询时所作的优化工作。
一些显示出来的统计信息是估算的，不是很正确。
老版本MySQL的expalin只能表明select操作，其他操作要重写为select后查看实行筹划。

1.4 MySQL订定的实行筹划不一定最优

尽管优化器的作用是将很糟糕的语句转化成高效的处理方式，但即便是在索引创建的很合理，并且优化器得到正确的相关信息后，优化器还是可能会订定堕落误方向的实行筹划。一般会有两个方面的缘故起因：

优化器的缺陷。
查询太复杂，优化器只能在给它的有限时间内尝试大量可能的组合中很少一部分优化方案，因此可能无法找到最佳实行筹划。

针对这种情况，最有效的解决办法就是正确编写查询。以更简单更直接的方式写SQL通常能节省优化器很多工作，由于它能很快命中一个很好而且很高效的实行筹划。
2 .MySQL干预实行筹划的方法(hint)

如果不满意MySQL优化器选择的优化方案，可以使用一些优化提示来控制优化器的活动。下面简单介绍下在MySQL中常用的提示，以及使用它们的时机。
2.1 force index、use index、ignore index

这些提示告诉优化器在该表中查询时使用大概忽略该索引。
force index和using index是一样的。但是它告诉优化器，表扫描比起索引来说代价要高很多，即使索引不是非常有效。
在MySQL5.0及以前版本中，它们不会影响排序和分组使用的索引。
2.2 straight_join

这个提示用于select语句中select关键字的后面，也可以用于连接语句。因此它的第一个用途是逼迫MySQL按照查询中表出现的顺序来连接表，第二个用途是当它出现在两个关联的表中间时，逼迫这两个表按照顺序连接。
straight_join在MySQL没有选择好的连接顺序，大概当优化器花费很长时间确定连接顺序的时间很有用。在后一种的情况下，线程将会在“统计”状态停留很长时间，添加这个提示将会淘汰优化器的搜刮空间。
可以使用explain查看优化器选择的联接顺序，然后按照顺序重写连接，并且加上straight_join提示。
2.3 sql_no_cache、sql_cache

sql_cache表明查询效果须要进行缓存，对效果进行缓存和变量query_cache_type，have_query_cache以及query_cache_limit的设置有关。
而sql_no_cache表明查询效果不须要进行缓存。
2.4 high_priority、low_priority

这两个提示决定了访问同一个表的SQL语句相对其它语句的优先级。
high_priority提示用于select和insert。是将一个查询语句放在队列的前面，而不是在队列中期待。
low_priority提示用于select、insert、update、replace、delete、load data。和high_priority相反，如果有其他语句访问数据，它就把当前语句放在队列的最后。
这两个提示不是指在查询上分配较多大概较少资源。它们只是影响服务器对访问表的队列的处理。
2.5 delayed

这个提示用于insert和update。使用了该提示的语句会立即返回并且将插入的列放在缓冲区中，在表空闲的时间再实行插入。它对于记录日志很有用，对于某些须要插入大量数据，对每一个语句都引发I/O操作但是又不盼望客户期待的应用步伐很有用。但是它有很多限定，好比：延迟插入不能运行于所有的存储引擎上（仅适用于MyISAM, Memory和Archive表），并且它也无法使用last_insert_id()。
2.6 sql_small_result、sql_big_result

这两个提示用于select语句。它们会告诉MySQL在group by或distinct查询中如何并且何时使用临时表。sql_small_result告诉优化器效果集会比较小，可以放在索引过的临时表中，以避免对分组后的数据排序。sql_big_result的意思是效果集比较大，最好使用磁盘上的临时表进行排序。
2.7 sql_buffer_result

这个提示告诉优化器将效果存放在临时表中，并且尽快释放掉表锁。
四、服务器问题

服务器硬件资源对于SQL性能的影响不容忽视，过低的服务器性能也产生慢SQL甚至造成内存溢出。以下是一些发起和补充内容：
1. 关注服务器性能指标

1.1 I/O吞吐量

关注磁盘I/O性能，确保数据读写速率满意需求。考虑使用SSD等高性能存储装备。
1.2 内存容量

确保服务器具有足够的内存资源，以便缓存常用数据和提高查询性能。
1.3 网络速率

评估网络带宽是否足够支持数据传输需求，避免成为性能瓶颈。
2. 数据库设置

根据服务器硬件资源和业务需求，合理设置数据库参数，如缓冲池大小、连接数等。
3. 考虑表分区

对于大型表，可以根据某些条件进行分区，以提高查询性能和管理效率。
五、步伐计划缺陷

在实际应用中，步伐计划缺陷可能导致SQL性能下降。以下是一些发起和补充内容：
1. 警惕锁或死锁问题

确保事务处理过程中正确使用锁，避免长时间占用锁导致其他事务无法实行。同时，留意检测和解决死锁问题。
2. 存在资源竞争

合理分配体系资源，避免多个任务或用户之间的资源竞争，提高体系整体性能。
3. 循环和太过嵌套

避免在SQL查询中使用循环和太过嵌套的结构，以淘汰查询复杂度和提高性能。
六、业务逻辑分析

在进行SQL优化时，除了对表结构和查询语句进行分析外，还须要从业务层面进行思考。以下是一些发起和补充内容：
1. 明白业务流程

了解业务需求和数据流转过程，以便找出潜在的性能瓶颈。
2. 区分报表型和OLTP型业务

针对差别类型的业务，采取差别的优化策略。例如，报表型业务可能更注重查询性能，而OLTP型业务可能更关注事务处理本领。
3. 从业务层面规避问题

例如，避免频仍的全表扫描、大量的数据排序等操作。
总结

综上所述，SQL 优化是一个综合性的工作，须要全面考虑各个方面的因素。通过深入分析和经心调整，可以显著提高数据库的性能和查询效率。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)