定位及优化SQL语句的性能问题

短命女 2022-05-14 08:22 348阅读 0赞

**一、EXPLAIN**  
做MySQL优化，我们要善用 **EXPLAIN** 查看SQL执行计划。  
下面来个简单的示例，标注(1,2,3,4,5)我们要重点关注的数据  
![20190304171146535.png][]  
type列，连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别  
key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式  
key\_len列，索引长度  
rows列，扫描行数。该值是个预估值  
extra列，详细说明。注意常见的不太友好的值有：Using filesort, Using temporary  
**二、SQL语句中IN包含的值不应过多**  
MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。  
**三、SELECT语句务必指明字段名称**  
SELECT \*增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。  
**四、当只需要一条数据的时候，使用limit 1**  
这是为了使EXPLAIN中type列达到const类型  
**五、如果排序字段没有用到索引，就尽量少排序**  
**六、如果限制条件中其他字段没有索引，尽量少用or**  
or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果  
**七、尽量用union all代替union**  
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。**当然，union all的前提条件是两个结果集没有重复数据。**  
**八、不使用ORDER BY RAND()**  
select id from \`dynamic\` order by rand() limit 1000;  
上面的sql语句，可优化为  
select id from \`dynamic\` t1 join (select rand() \* (select max(id) from \`dynamic\`) as nid) t2 on t1.id > t2.nidlimit 1000;  
**九、区分in和exists， not in和not exists**  
select \* from 表A where id in (select id from 表B)  
上面sql语句相当于  
select \* from 表A where exists(select \* from 表B where 表B.id=表A.id)  
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以**IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。**  
关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。**如何高效的写出一个替代not exists的sql语句？**  
原sql语句  
select colname … from A表 where a.id not in (select b.id from B表)  
高效的sql语句  
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null  
取出的结果集如下图表示，A表不在B表中的数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70][]  
**十、使用合理的分页方式以提高分页的效率**  
select id,name from product limit 866613, 20  
使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。  
优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：  
select id,name from product where id> 866612 limit 20  
**十一、分段查询**  
在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。  
如下图这个sql语句，扫描的行数成百万级以上的时候就可以使用分段查询

![20190304171304435.png][]  
**十二、避免在 where 子句中对字段进行 null 值判断**  
对于null的判断会导致引擎放弃使用索引而进行全表扫描。  
Java架构/分布式/高并发：468897908  
**十三、不建议使用%前缀模糊查询**  
例如LIKE “%name”或者LIKE “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。  
**那如何查询%name%？**  
如下图所示，虽然给secret字段添加了索引，但在explain结果果并没有使用  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70 1][]  
那么如何解决这个问题呢，答案：**使用全文索引**  
在我们查询中经常会用到select id,fnum,fdst from dynamic\_201606 where user\_name like '%zhangsan%'; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。  
创建全文索引的sql语法是：  
ALTER TABLE \`dynamic\_201606\` ADD FULLTEXT INDEX \`idx\_user\_name\` (\`user\_name\`);  
使用全文索引的sql语句是：  
select id,fnum,fdst from dynamic\_201606 where match(user\_name) against('zhangsan' in boolean mode);  
**注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别**  
**十四、避免在where子句中对字段进行表达式操作**  
比如  
select user\_id,user\_project from user\_base where age\*2=36;  
中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成  
select user\_id,user\_project from user\_base where age=36/2;  
**十五、避免隐式类型转换**  
where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型  
**十六、对于联合索引来说，要遵守最左前缀法则**  
举列来说索引含有字段id,name,school，可以直接用id字段，也可以id,name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面  
**十七、必要时可以使用force index来强制查询走某个索引**  
有的时候MySQL优化器采取它认为合适的索引来检索sql语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用forceindex来强制优化器使用我们制定的索引。  
**十八、注意范围查询语句**  
对于联合索引来说，如果存在范围查询，比如between,>,<等条件时，会造成后面的索引字段失效。  
**十九、关于JOIN优化**  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70 2][]  
LEFT JOIN A表为驱动表  
INNER JOIN MySQL会自动找出那个数据少的表作用驱动表  
RIGHT JOIN B表为驱动表  
**注意：MySQL中没有full join，可以用以下方式来解决**  
select \* from A left join B on B.name = A.namewhere B.name is nullunion allselect \* from B;  
**尽量使用inner join，避免left join**  
参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。  
**合理利用索引**  
被驱动表的索引字段作为on的限制字段。  
**利用小表去驱动大表**  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70 3][]  
从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。  
**巧用STRAIGHT\_JOIN**  
inner join是由mysql选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT\_JOIN来强制连接顺序，在STRAIGHT\_JOIN左边的表名就是驱动表，右边则是被驱动表。**在使用STRAIGHT\_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT\_JOIN，否则可能造成查询结果不准确。**  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70 4][]  
这个方式有时可能减少3倍的时间。

## 执行计划 ##

我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。

而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

![70][]

([https://juejin.im/post/5a52386d51882573443c852a][https_juejin.im_post_5a52386d51882573443c852a])

基本语法

explain select ...

mysql的explain 命令可以用来分析select 语句的运行效果。

除此之外，explain 的extended 扩展能够在原本explain的基础上额外的提供一些查询优化的信息，这些信息可以通过mysql的show warnings命令得到。

mysql> explain extended select * from account;
    ******** 1. row ***************************
              id: 1
    select_type: SIMPLE
           table: account
            type: ALL
    possible_keys: NULL
             key: NULL
         key_len: NULL
             ref: NULL
            rows: 1
        filtered: 100.00
           Extra:
    1 row in set, 1 warning (0.00 sec)
    
    mysql> show warnings;
    *************1. row ***************************
    Level: Note
      Code: 1003
    Message: select `dbunit`.`account`.`id` AS `id`,`dbunit`.`account`.`name` AS `name` from `dbunit`.`account`
    1 row in set (0.00 sec)

另外，对于分区表的查询，需要使用partitions命令。

explain partitions select ...

## 执行计划包含的信息 ##

不同版本的Mysql和不同的存储引擎执行计划不完全相同，但基本信息都差不多。mysql执行计划主要包含以下信息:

![70 1][]

**id**

由一组数字组成。表示一个查询中各个子查询的执行顺序;

*  id相同执行顺序由上至下。
 *  ![70 2][]

*  id不同，id值越大优先级越高，越先被执行。
 *  ![70 3][]
 *  id为`null`时表示一个结果集，不需要使用它查询，常出现在包含`union`等查询语句中。
 *  ![70 4][]

**select\_type**

每个子查询的查询类型，一些常见的查询类型。

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>不包含任何子查询或union等查询</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>PRIMARY</td> 
   <td>包含子查询最外层查询就显示为&nbsp;<code>PRIMARY</code></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>SUBQUERY</td> 
   <td>在<code>select</code>或&nbsp;<code>where</code>字句中包含的查询</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>DERIVED</td> 
   <td><code>from</code>字句中包含的查询</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>UNION</td> 
   <td>出现在<code>union</code>后的查询语句中</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>UNION RESULT</td> 
   <td>从UNION中获取结果集，例如上文的第三个例子</td> 
  </tr> 
 </tbody> 
</table>

**table**

查询涉及到的数据表。

如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

**partitions**

表分区、表创建的时候可以指定通过那个列进行表分区。 举个例子：

create table tmp (
        id int unsigned not null AUTO_INCREMENT,
        name varchar(255),
        PRIMARY KEY (id)
    ) engine = innodb
    partition by key (id) partitions 5;

![70 5][]

**type**

访问类型

*  `ALL`   扫描全表数据
 *  `index` 遍历索引
 *  `range` 索引范围查找
 *  `index_subquery` 在子查询中使用 ref
 *  `unique_subquery` 在子查询中使用 eq\_ref
 *  `ref_or_null` 对`Null`进行索引的优化的 ref
 *  `fulltext` 使用全文索引
 *  `ref`   使用非唯一索引查找数据
 *  `eq_ref` 在`join`查询中使用`PRIMARY KEY`or`UNIQUE NOT NULL`索引关联。
 *  `const` 使用主键或者唯一索引，且匹配的结果只有一条记录。
 *  `system const` 连接类型的特例，查询的表为系统表。
 *  ![70 6][]

性能从好到差依次为：

system，const，eq\_ref，ref，fulltext，ref\_or\_null，unique\_subquery，index\_subquery，range，index\_merge，index，ALL，除了ALL之外，其他的type都可以使用到索引，除了index\_merge之外，其他的type只可以用到一个索引。

**所以，如果通过执行计划发现某张表的查询语句的type显示为ALL，那就要考虑添加索引，或者更换查询方式，使用索引进行查询。**

**possible\_keys**

可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 `NULL`时就要考虑当前的`SQL`是否需要优化了。

**key**

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

`TIPS:`查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中。

select\_type为index\_merge时，这里可能出现两个以上的索引，其他的select\_type这里只会出现一个。

**key\_length**

索引长度 char()、varchar()索引长度的计算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

其他类型索引长度的计算公式: ex:

CREATE TABLE `student` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `name` varchar(128) NOT NULL DEFAULT '',
     `age` int(11),
     PRIMARY KEY (`id`),
     UNIQUE KEY `idx` (`name`),
     KEY `idx_age` (`age`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

name 索引长度为： 编码为utf8mb4,列长为128,不允许为`NULL`,字段类型为`varchar(128)`。`key_length = 128 * 4 + 0 + 2 = 514;`

![70 7][]

age 索引长度：int类型占4位，允许`null`,索引长度为5。

![70 8][]

**ref**

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

**rows**

返回估算的结果集数目，注意这并不是一个准确值。

**extra**

`extra`的信息非常丰富，常见的有：

1.  Using index 使用覆盖索引
2.  Using where 使用了用where子句来过滤结果集
3.  Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
4.  Using temporary 使用了临时表。

** 一些SQL优化建议**

**1、SQL语句不要写的太复杂。**

一个SQL语句要尽量简单，不要嵌套太多层。

**2、使用『临时表』缓存中间结果。**

简化SQL语句的重要方法就是采用临时表暂存中间结果，这样可以避免程序中多次扫描主表，也大大减少了阻塞，提高了并发性能。

**3、使用like的时候要注意是否会导致全表扫**

有的时候会需要进行一些模糊查询比如

select id from table where username like ‘%hollis%’

关键词%hollis%，由于hollis前面用到了“%”，因此该查询会使用全表扫描，除非必要，否则不要在关键词前加%，  
**4、尽量避免使用!=或<>操作符**

在where语句中使用!=或<>，引擎将放弃使用索引而进行全表扫描。

**5、尽量避免使用 or 来连接条件**

在 where 子句中使用 or 来连接条件，引擎将放弃使用索引而进行全表扫描。

可以使用
    
    select id from t where num=10
    union all
    select id from t where num=20
    
    替代
    select id from t where num=10 or num=20

**6、尽量避免使用in和not in**

在 where 子句中使用 in和not in，引擎将放弃使用索引而进行全表扫描。

可以使用
    
    select id from t where num between 10 and 20
    
    替代
    select id from t where num in (10,20)

**7、可以考虑强制查询使用索引**

select * from table force index(PRI) limit 2;(强制使用主键)
    
    select * from table force index(hollis_index) limit 2;(强制使用索引"hollis_index")
    
    select * from table force index(PRI,hollis_index) limit 2;(强制使用索引"PRI和hollis_index")

**8、尽量避免使用表达式、函数等操作作为查询条件**

**9、尽量避免大事务操作，提高系统并发能力。**

**10、尽量避免使用游标**

**11、任何地方都不要使用 select \* from t ，用具体的字段列表代替“\*”，不要返回用不到的任何字段。**

**12、尽可能的使用 varchar/nvarchar 代替 char/nchar**

**13、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。**

**14、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率**

**15、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引**

[20190304171146535.png]: /images/20220514/75b7d2654e08466985dd2d96e7d2d74c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70]: /images/20220514/6ef4195ff12d4207b0ccd9e9ef633fe5.png
[20190304171304435.png]: /images/20220514/63a7229270734de78cf630c754630687.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70 1]: /images/20220514/21c9390c0a60475a8fca8276a3a1a5ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70 2]: /images/20220514/3ecea6b04a304bbf9e2eafa55e720d25.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70 3]: /images/20220514/a1de2a2d7509442081b2ad45b7dd3ea7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3czNzI0MjYwOTY_size_16_color_FFFFFF_t_70 4]: /images/20220514/e6d0dc8575764811b2d99fbe412153bc.png
[70]: /images/20220514/3c35974771644e2bb1f67a92e1f34a36.png
[https_juejin.im_post_5a52386d51882573443c852a]: https://juejin.im/post/5a52386d51882573443c852a
[70 1]: /images/20220514/c6aba7bfbe734084ba4e38f337381fcf.png
[70 2]: /images/20220514/01932a8a839342bdae7789f6f6ae74a7.png
[70 3]: /images/20220514/8e1f453886ac4f38a584e4ad91e8a66b.png
[70 4]: /images/20220514/92fc72855491497686020da7381f6f3e.png
[70 5]: /images/20220514/7de17a206c8b47d6bcd4eacb29b24fc2.png
[70 6]: /images/20220514/3109deeff9a043d6b83e3891761b85d8.png
[70 7]: /images/20220514/d8178c8ebd60426d80cd04248485628e.png
[70 8]: /images/20220514/46c3eb772d0f450d8e52a0e9177b3d57.png