深入浅出 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子句中的限制条件的排列顺序会有一些限制。这些限制包括：

1. where子句中限制条件排列最好为对应索引的最左前缀；
2. 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的实现有如下两种类型：

1. 通过有序索引直接获得有序的数据，这样不用任何排序操作即可得到满足要求的有序数据；
2. 须通过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 表示对应行所使用的查询类型，有以下几种类型：

1. simple：简单查询。查询不包含子查询和union;
2. primary：复杂查询中最外层的select ;
3. subquery：包含在 select 中的子查询（不在 from 子句中）;
4. derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中；
5. 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 ；

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

2. const, system

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

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

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

4. 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部分。

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

5. range
   范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定范围的行。

   explain select * from actor where id > 1;
   

6. index
   扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接 对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这 种通常比ALL快一些。

   explain select * from film;
   

7. ALL
   即全表扫描，扫描你的聚簇索引的所有叶子节点.通常情况下这需要增加索引来进行优化。

   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

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

1. Using index：使用覆盖索引

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

2. Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

3. Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

4. 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;

5. 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;

6. 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只是一个近似，没有更多的细节。有时它是一个很好的近似，但是有时它会远离真实情况，以下就是它的几个局限性：

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

1.4 MySQL制定的执行计划不一定最优

尽管优化器的作用是将很糟糕的语句转化成高效的处理方式，但即便是在索引建立的很合理，并且优化器得到正确的相关信息后，优化器还是可能会制定出错误方向的执行计划。一般会有两个方面的原因：

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

针对这种情况，最有效的解决办法就是正确编写查询。以更简单更直接的方式写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 优化是一个综合性的工作，需要全面考虑各个方面的因素。通过深入分析和精心调整，可以显著提高数据库的性能和查询效率。