SQL执行慢的原因分析以及调优手段

目录

• 开发规范
  • ※谨慎使用 MySQL 分区表
  • ※经常一起使用的列放到一个表中
  • ※禁止在数据库中存储文件（比如图片）这类大的二进制数据
  • 所有表必须使用 InnoDB 存储引擎
  • 数据库和表的字符集统一使用 UTF8
  • 尽量控制单表数据量的大小，建议控制在 500 万以内
  • ※优先选择符合存储需要的最小的数据类型
  • ※尽可能把所有列定义为 NOT NULL
  • 使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间
• 索引规范
  • 建议单张表索引不超过 5 个
  • 禁止给表中的每一列都建立单独的索引
  • 每个 InnoDB 表必须有个主键
  • 常见索引列建议
  • 创建联合索引如何选择索引列的顺序
  • 对于频繁的查询优先考虑使用覆盖索引
  • ※索引 SET 规范
• SQL语句优化
  • 如何找出需要优化的 SQL 语句
  • 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询
  • 充分利用表上已经存在的索引
  • 尽量避免使用子查询
  • 用IN来替换OR
  • 读取适当的记录LIMIT M,N
  • 若两个结果集没有重复使用UNION ALL
  • 尽可能批量Insert插入
  • WHERE 从句中禁止对列进行函数转换和计算
  • 优化Group By语句
  • 优化Join语句

1、大多数情况下很正常，偶尔很慢，则有如下原因

(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
(2)、执行的时候，遇到锁，如表锁、行锁。
(3)、sql写的烂

2、这条 SQL 语句一直执行的很慢，则有如下原因
(1)、没有用上索引或索引失效：例如该字段没有索引；或则由于对字段进行运算、函数操作导致无法用索引。
(2)、有索引但走了全表扫描

怎样判断是否走全表扫描：
索引区分度（索引的值不同越多，区分度越高），称为基数，而数据量大时不可能全部扫描一遍得到基数，而是采样部分数据进行预测，那有可能预测错了，导致走全表扫描。

优化角度：索引+sql语句+数据库结构优化+优化器优化+架构优化

对开发者来说，调优重点在开发规范、索引和线上慢查询。

开发规范

※谨慎使用 MySQL 分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表；

谨慎选择分区键，跨分区查询效率可能更低；

建议采用物理分表的方式管理大数据。

※经常一起使用的列放到一个表中

避免更多的关联操作。经常联合查询的表，可以考虑建立中间表。

※禁止在数据库中存储文件（比如图片）这类大的二进制数据

在数据库中存储文件会严重影响数据库性能，消耗过多存储空间。

文件（比如图片）这类大的二进制数据通常存储于文件服务器，数据库只存储文件地址信息。

所有表必须使用 InnoDB 存储引擎

InnoDB 支持事务，支持行级锁，更好的恢复性，高并发下性能更好。

数据库和表的字符集统一使用 UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效，如果数据库中有存储 emoji 表情的需要，字符集需要采用 utf8mb4 字符集。

尽量控制单表数据量的大小，建议控制在 500 万以内

可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

---

※优先选择符合存储需要的最小的数据类型

存储字节越小，占用也就空间越小，性能也越好。

1. 某些字符串可以转换成数字类型存储比如可以将 IP 地址转换成整型数据。

数字是连续的，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

• INET_ATON() ： 把 ip 转为无符号整型 (4-8 位)
• INET_NTOA() :把整型的 ip 转为地址

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

2. 对于非负型的数据 (如自增 ID,整型 IP，年龄) 来说,要优先使用无符号整型来存储。

因为无符号相对于有符号可以多出一倍的存储空间

3. 小数值类型（比如年龄、状态表示如 0/1）优先使用 TINYINT 类型。

※尽可能把所有列定义为 NOT NULL

除非有特别的原因使用 NULL 值，应该总是让字段保持 NOT NULL。

• 索引 NULL 列需要额外的空间来保存，所以要占用更多的空间；
• 进行比较和计算时要对 NULL 值做特别的处理。

使用 TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间

TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

TIMESTAMP 占用 4 字节和 INT 相同，但比 INT 可读性高

超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储

经常会有人用字符串存储日期型的数据（不正确的做法）

• 缺点 1：无法用日期函数进行计算和比较
• 缺点 2：用字符串存储日期要占用更多的空间

索引规范

建议单张表索引不超过 5 个

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

禁止给表中的每一列都建立单独的索引

不如使用一个联合索引

每个 InnoDB 表必须有个主键

InnoDB 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

InnoDB 是按照主键索引的顺序来组织表的

• 不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）
• 不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）
• 主键建议使用自增 ID 值

常见索引列建议

• 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
• 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
• 不用将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好
• 多表 join 的关联列

创建联合索引如何选择索引列的顺序

建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

• 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
• 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
• 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

对于频繁的查询优先考虑使用覆盖索引

覆盖索引：就是包含了所有查询字段 (where, select, order by, group by 包含的字段) 的索引

※索引 SET 规范

尽量避免使用外键约束

• 不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引
• 外键可用于保证数据的参照完整性，但建议在业务端实现
• 外键会影响父表和子表的写操作从而降低性能

SQL语句优化

如何找出需要优化的 SQL 语句

1. 通过查询 MySQL 的慢查询日志来发现需要进行优化的 SQL 语句；

2. explain查看SQL的执行计划，这样就知道是否命中索引了。

   当explain与SQL一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。

   

   重点关注type、rows、filtered、extra、key

   1. type

      type表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

      • system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
      • const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。
      • eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
      • ref : 常用于非主键和唯一索引扫描。
      • ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
      • index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
      • unique_subquery：类似于eq_ref，条件用了in子查询
      • index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
      • range：常用于范围查询，比如：between ... and 或 In 等操作
      • index：全索引扫描
      • ALL：全表扫描

   2. rows

      该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值。

   3. filtered

      该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

   4. extra

      该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：

      • Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
      • Using index ：表示是否用了覆盖索引。
      • Using temporary: 表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句。
      • Using where : 表示使用了where条件过滤.
      • Using index condition：MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

   5. key

      该列表示实际用到的索引。一般配合possible_keys列一起看。

3. show profile 分析

   了解SQL执行的线程的状态及消耗的时间。

   默认是关闭的，开启语句“set profiling = 1;”

   SHOW PROFILES ;
   SHOW PROFILE FOR QUERY  #{id};
   

4. trace

   trace分析优化器如何选择执行计划，通过trace文件能够进一步了解为什么选择A执行计划而不选择B执行计划。

禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

• SELECT * 消耗更多的 CPU 和 IO 以网络带宽资源
• SELECT * 无法使用覆盖索引
• SELECT <字段列表> 可减少表结构变更带来的影响

充分利用表上已经存在的索引

一个 SQL 只能利用到复合索引中的一列进行范围查询。如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到。

在定义联合索引时，如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧，使用 left join 或 not exists 来优化 not in 操作，因为 not in 也通常会使用索引失效。

尽量避免使用子查询

用join连接

用IN来替换OR

# 优化前
SELECT * FROM t WHERE id = 10 OR id = 30;
# 优化后
SELECT * FROM t WHERE id IN (10, 30);

对于连续的数值，能用between就不要用in了；再或者使用join连接来替换。

读取适当的记录LIMIT M,N

# 优化前
SELECT id.name FROM t LIMIT 866613, 20

对于limit m,n 的分页查询，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。因为MySQL并不是跳过offset行，而是取offset+N行，然后放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。

优化的方法如下：可以取前一页的最大行数的id（将上一次遍历到的最末尾的数据ID传给数据库，然后直接定位到该ID处，再往后面遍历数据），然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：

# 优化后
SELECT id.name FROM table_name WHERE id> 866612 LIMIT 20

若两个结果集没有重复使用UNION ALL

union 和 union all 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。

当然，union all 的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。

尽可能批量Insert插入

INSERT INTO t(id, name) VALUES(1, 'aaa');
INSERT INTO t(id, name) VALUES(2, 'bbb');
INSERT INTO t(id, name) VALUES(3, 'ccc');
 
-->
INSERT INTO t(id, name) VALUES(1, 'aaa'),(2, 'bbb'),(3, 'ccc');

WHERE 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引不推荐：

where date(create_time)='20190101'

推荐：

where create_time >= '20190101' and create_time < '20190102'

优化Group By语句

如果对group by 语句的结果没有排序要求，要在语句后面加上order by null (group默认会排序)；

尽量让group by 过程用上表的索引，确认方法是explain结果里没有Using temporary和Using filesort;

如果group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；

如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by 的结果。

使用where字句替换having子句：避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。

# 优化前
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER'
# 优化后
SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB

优化Join语句

当我们执行两个表的Join的时候，就会有一个比较的过程，逐条比较两个表的语句是比较慢的，因此可以把两个表中数据一次读进一个内存块中，在MySQL中执行：

show variables like 'join_buffer_size'

可以看到Join在内存中的缓存池大小，其大小将会影响Join语句的性能。在执行Join的时候，数据库会选择一个表把它要返回以及需要进行和其他表进行比较的数据放进join_buffer。

1. 当连接查询没有where条件时

• left join前面的表是驱动表，后面的表是被驱动表
• right join后面的表是驱动表，前面的表是被驱动表
• inner join / join会自动选择表数据比较少的作为驱动表
• traight_join(≈join)直接选择左边的表作为驱动表（语义上与join类似，但去除了join自动选择小表作为驱动表的特性）

2. 当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表

假如有表如右边：t1与t2表完全一样，a字段有索引，b无索引，t1有100条数据，t2有1000条数据

1. 对被驱动表的join字段上建立索引;
2. 当被驱动表的join字段上无法建立索引时，设置足够的 Join Buffer Size;
3. 尽量用 inner join(因为其会自动选择小表去驱动大表)，避免 left join（一般我们使用left join 的场景是大表驱动小表）和 NULL
4. left join是由左边决定的，左边一定都有，所以右边是我们的关键点，建立索引要建在右边。当然如果索引是在左边的，我们可以考虑使用右连接