SQL优化13连问

前言

本篇文章来源于「捡田螺的小男孩」

1. 日常工作中，如何优化SQL？

大致可以从这几个维度回答这个问题：

• 分析慢查询日志

• 使用explain查看执行计划

• 索引优化

• 深分页优化

• 避免全表扫描

• 避免返回不必要的数据(使用select 具体字段代替select*)

• 使用合适的数据类型(如可以使用你太类型，就不要设计varchar)

• 优化sql结构（如 join优化等）

• 适当分批量进行(如批量更新、删除)

• 定期清理无用的数据

• 适当分库分表

• 读写分离

2. 如何解决深分页的问题？

可以通过减少回表次数来优化，一般有标签记录法和延迟关联法

• 标签记录法

  就是标记一下上次查询到哪一条来，下次再来查询，从该条开始往下扫描，就好像书签一样，下次来看的时候直接就翻到来。

  假设上一次记录到1000000，则SQL可以修改为：

  select id,name from account where id > 1000000 limit 10;

  这样的化，后面无论翻多少页，性能都是不错的，因为命中了id索引，但是这样方式有局限性：需要一种类似连续自增的字段。

• 延迟关联法

  延迟关联法：就是把条件转移到主键索引树，然后减少回表

  假设原生的SQL是这样的，其中ID是主键，create_time是普通索引

  select id, name, balance from account where craete_time > '2023-01-01' limit 100000,10;

  使用延迟关联法优化，如下：

  select t1.id, t1.name, t1.balance from account t1 inner join 
      (select a.id from account a where a.create_time > '2023-01-01' limit 100000, 10)
      as t2 on t1.id = t2.id

  优化思路就是：先通过idx_create_time二级索引书查询满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走主键索引来，同时减少来回表

3. 聊聊explain执行计划

当explain与SQL一起使用时，显示来自优化起的相关语句执行计划的信息。即mysql解释了它将如何处理该语句，包括有关如何连接表以及何种顺序连接表等信息。

一条简单SQL，使用explain的效果如下：

一般来说，我们需要重点关注type、rows、filtered、extra、key。

3.1 type

type表示连接类型，查看索引执行情况的一个重要指标，以下性能从好到坏依次：system>const> eq_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子查询

• range：常用于范围查询，比如：between…and 或 IN 等操作

• index：全索引扫描

• ALL：全表扫描

3.2 rows

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

3.3 filtered

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

3.4 extra

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

• Using filesort: 表示按文件排序，一般是在指定排序和索引排序不一致的情况才会出现，一般见于order by语句

• Using index: 表示是否使用了覆盖索引

• Using temporary: 表示是否使用了临时表，性能特别差，需要重点优化，一般多见于group by / union语句

• Using where: 表示使用了where条件过滤

• Using index condition: mysql5.6之后新增的索引下推，在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据

3.5 key

该列表示实际用到的索引，一般配合possible_keys列一起看。
注意：有时候，explain 配合show warnings；可以查看优化后最终执行的SQL。

4. 说说大表的优化方案

4.1 数据库设计优化

合理的数据库设计可以极大地提高查询效率。我们在设计大表时可以考虑拆分表、使用分区表、添加索引等方式查看优化表结构。同时也要避免使用大量冗余字段、避免频繁使用josn查询等操作

4.2 索引优化

对于大表的查询操作，索引优化时非常重要的一环。可以考虑增加或修改索引、使用覆盖索引、使用联合索引等方式来提高查询效率。。同时也要注意
定期清理冗余的索引以及对经常使用的查询语句建立索引

4.3 分区优化

将大表按照某个列可以分成多个分区表，每个分区表的缩量较小，可以提高查询和更新的性能。分区表还可以帮助维护表结构的同时，减少锁表的时间，提高并发处理能力。

4.4 数据清理归档

对于一些历史数据或者无用数据，可以进行定期归档，避免数据过多造成SQL查询效率降低。 同时也要注意对于大表进行定期的数据备份以及紧急数据恢复的准备工作。

4.5 缓存优化

对于一些经常被查询的数据，可以使用缓存优化。使用Redis等缓存中间件来缓存常用的数据，以减少查询数据库的次数，提高查询效率.

4.6 SQL语句优化

在编写SQL语句时，要尽可能地简单明了，避免复杂的查询语句，同时也要避免一些不必要的查询插座。对于复杂的查询语句，使用explain执行计划来哟话，同时也要注意避免使用OR等耗性能的操作符.

4.7 分库分表

如果数据量千万级别，需要考虑分库分表

5. 导致慢查询的因素有哪些

慢查询一般有以下几种原因：

6. 如何使用索引优化SQL查询

• 添加合适索引（在where、group by、 order by等后面的字段添加合适索引）

• 选择合适的索引类型（B-tree索引适合范围查询、哈希索引适合等值查询）

• 注意不适合添加索引的场景（数据量少的表，更新频繁的字段，区分度低的字段）

• 加索引的适合，需要考虑覆盖索引，减少回表，考虑联合索引的最左前缀原则

• explain查看SQL的执行计划，确认是否会命中索引

• 注意索引不是越多越好，通常建议单个表中不要超过5个索引，因为索引会占用磁盘空间，索引更新代价高

7. 聊聊慢SQL的优化思路

1. 查看慢查询日志记录，分析慢SQL

2. explain分析SQL的执行计划

3. profile分析执行耗时

4. Optimizer Trace分析详情

5. 确定问题采用相应措施

7.1 查看慢查询日志记录，分析慢SQL

可以通过slow log 来查看慢SQL，默认情况下，mysql是不开启慢查询日志(slow query log)，需要手动打开

show variables like 'slow_query_log%'

• slow_query_log: 表示慢查询开启状态

• slow_query_log_file: 表示慢查询日志存放的问题

还可以使用show variables like 'long_query_time' 命令，查看超过多少时间，才记录到慢查询日志里面

7.2 explain查看分析SQL的执行计划

当定位出查询效率低的SQL后，可以使用explain查看SQL的执行计划。mysql将显示来自优化起的有关语句执行计划的信息。即mysql解释了它将如何处理该语句，包括有关如何连接表以及何种顺序连接表等信息

一般来说，重点关注type, rows, filtered, extra, key

7.3 profile分析执行耗时

explain只是看到SQL的预估执行计划，如果需要了解SQL真正的执行线程状况及消耗的时间，需要使用profiling。开启profiling参数后，后续执行的SQL语句后悔记录其资源开销，包括IO、上下文切换、CPU、内存等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。

profiling默认时关闭的，我们可以使用show variables like '%profil%' 查看是否开启

可以使用set profiling = on开启，开启后可以运行几条SQL，然后使用show profiles查看执行耗时

show profiles会显示最近发给服务的多条语句，条数由变量profiling_history_size定义，默认15。

如果需要查看单挑SQL的分析，可以使用show profile for query id，其中ID就是show profiles的query_id

7.4 Optimizer Trace分析详情

profile只能看到SQL的执行耗时，但是无法看到SQL真正的执行的过程信息，即不知道mysql优化起时如何选择执行计划。这时候，可以使用Optimizer trace，它可以跟踪执行语句的解析优化执行的全过程。

我们可以使用set optimizer_trace="enabled=on"开启，接着执行要跟踪的SQL，最后执行select * from information_schema.optimizer_trace跟踪，如下：

查看分析其执行树，会包括三个阶段：

• join_preparation: 准本阶段

• join_optimization: 分析阶段

• join_execution: 执行阶段

7.5 确定问题并采用相应的措施

• 多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，可以优化索引

• 优化SQL语句，比如一些in 元素过多问题（分批），深分页问题（基于上一次数据过滤等），进行时间分段查询

• SQL没办法很好优化，可以改用ES的方式，或者数仓

• 如果但表数据量大导致慢查询，可以考虑分库分表

• 如果数据库在刷脏页导致慢查询，可以考虑是否优化一些参数

• 如果存量数据过大，可以考虑部分数据归档

8. SQL执行过程，如何优化

和慢查询SQL排查解决很像，可以从以下几个方面入手：

• 确认瓶颈

  首先，通过查看MySQL日志，慢查询日志，explain分析SQL的执行计划，profile 分析执行耗时，Optimizer Trace分析详情等操作，确定查询执行的瓶颈在哪里。只有确定了瓶颈，才能有针对性地进行优化。

• 索引优化

  在确定了瓶颈之后，可以考虑通过增加索引来优化查询效率。可以根据查询语句的条件，增加相应的索引，从而加快查询速度。但是索引也会带来一些负面影响，如占用磁盘空间，降低写入效率等，所以需要根据具体情况权衡

• 优化SQL

  有些SQL语句本身可能存在一些问题，如join操作过于频繁，使用了不必要的子查询等，这些都会导致查询效率低下。可以通过优化SQL语句来减少不必要的操作，从而提高查询效率。

• 数据库参数优化

  数据库参数也会影响查询效率，可以通过修改数据库参数来优化查询效率，如修改内存缓存大小、修改连接池大小等。不同的数据库参数优化方式不同，需要根据具体情况进行调整

• 分析锁的情况

  查询执行时间过长有可能是由于锁的问题导致的，需要分析查询语句中是否存在锁的问题，如果存在锁的问题，可以考虑增加锁的并发度，从而提高查询效率。

• 数据库硬件升级

  如果以上方法都无法解决问题，可以考虑对数据库硬件进行升级，如增加 CPU 数量、加快磁盘读写速度等，从而提高数据库的整体性能。

9. 常用数据库设计优化技巧

• 字段尽量避免使用NULL

• 合理选择数据类型以及长度

• 正确使用索引

• 尽量少定义text类型

• 合理的数据表结构设计

• 适当的冗余设计

• 优化sql 语句

• 一张表的字段不宜过多

10. 列举常用书写高质量SQL小技巧

1. 查询SQL精良不要使用select * ，而是具体字段

2. 小表驱动大表

3. 优化like语句

4. 尽量避免在索引列上使用mysql的内置函数

5. 如果插入数据过多，考虑批量操作

6. 多用limit

7. exist & in合理利用

8. in 元素不要过多

9. 尽量使用union all替换union

11. index merge

在mysql中，当执行一个查询语句需要使用多个索引时，mysql可以使用索引合并（index merge）来优化查询性能。具体来说，索引合并是将多个单列索引或多个联合索引合并使用，以满足查询语句的需要。

当使用索引合并时，mysql会选择最有的索引组合来执行查询，从而避免了全表扫描和排序操作，提高来查询效率。而对于使用多个单列索引的查询语句，mysql也可以使用索引合并来优化查询性能。

例子：假设有一张orders表，包含order_id, customer_id, product_id,order_date等字段，其中order_id, customer_id, product_id都有建立索引

如果查询customer_id = 1、order_date在2022年1月1日至2022年2月1日之间的订单记录，可以使用以下SQL语句:

select * from orders where customer_id = 1 and order_date >= '2022-01-01' and order_date<= '2022-023-01'

在执行该查询语句时，mysql使用customer_id索引和order_date索引来优化查询，如果使用单个索引，则需要扫描整个索引树来匹配查询条件；如果使用索引合并，则可以先使用customer_id索引来过滤出符合条件的记录，然后再使用order_date索引
来进一步过滤记录，从而大大减少来扫描的记录数，提高来查询效率.

12. 如何优化 order by查询效率慢问题

order by排序：分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会就rowid，相反则走全字段排序.

rowid排序一般需要回表去找满足条件的数据，所以效率会慢一点，如果是order by排序，可能会借助磁盘文件排序的化，效率会更慢一些。

如何优化order by的文件排序：

• 因为数据是无序的，所以需要排序。如果数据本身就是有序的，就不会在用到文件排序来，而索引数据本身就是有序的，我们通过建立索引来优化order by 语句

• 还可以通过调整max_length_for_sort_data, sort_buffer_size等参数优化

13. 如何优化group by查询慢问题

group by一般用于分组统计，它的逻辑就是根据一定规则，进行分组。

group by可能会慢在哪里？ 因为它既用到临时表，有默认用到排序，可能还会用到磁盘临时表

• 如果执行过程中，会发现内存临时表大小到达了上限(参数是：tmp_table_size)，会把内存临时表转成磁盘临时表

• 如果数据量很大，可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间

如何优化group by？

• group by后面的字段加索引

• order by null 不用排序

• 尽量只是用内存临时表

• 使用SQL_BIG_RESULT

最后，在这里在此致敬原作者【捡田螺的小男孩】