SQL优化13连问，收藏好-六虎

前语

咱们好，我是田螺。

金三银四现已开端啦，预备了SQL优化的13连问，内容非常干！相信咱们看完必定会有协助的。

公众号：捡田螺的小男孩 （有田螺精心预备的面试PDF）
github地址，感谢每颗star：github

1.日常工作中，你是怎么优化SQL的？

咱们能够从这几个维度答复这个问题：

剖析慢查询日志
运用explain检查履行方案
索引优化
深分页优化
防止全表扫描
防止回来不必要的数据（如select详细字段而不是select*）
运用适宜的数据类型（如能够运用int类型的话，就不要规划为varchar）
优化sql结构（如join优化等等）
恰当分批量进行 (如批量更新、删去)
定时整理无用的数据
恰当分库分表
读写分离

2. 是否遇到过深分页问题，怎么处理

咱们能够经过削减回表次数来优化。一般有标签记载法和推迟相关法。

标签记载法

便是标记一下前次查询到哪一条了，下次再来查的时分，从该条开端往下扫描。就好像看书一样，前次看到哪里了，你就折叠一下或许夹个书签，下次来看的时分，直接就翻到啦。

假定上一次记载到100000，则SQL能够修正为：

select  id,name,balance FROM account where id > 100000 limit 10;

这样的话，后边不管翻多少页，功能都会不错的，由于射中了id索引。可是这种方法有局限性：需求一种相似连续自增的字段。

推迟相关法

推迟相关法，便是把条件转移到主键索引树，然后削减回表。假定原生SQL是这样的的，其间id是主键，create_time是普通索引

select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;

运用推迟相关法优化，如下：

select  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN
(SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) 
AS acct2 on acct1.id= acct2.id;

优化思路便是，先经过idx_create_time二级索引树查询到满意条件的主键ID，再与原表经过主键ID内衔接，这样后边直接走了主键索引了，一同也削减了回表。

3. 聊聊explain履行方案

当explain与SQL一同运用时，MySQL将显现来自优化器的有关句子履行方案的信息。即MySQL解说了它将怎么处理该句子，包括有关怎么衔接表以及以何种顺序衔接表等信息。

一条简略SQL，运用了explain的效果如下：

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

3.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：全表扫描

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.说说大表的优化方案

数据库规划优化

合理的数据库规划能够极大地前进查询功率。咱们在规划大表时，能够考虑拆分表、运用分区表、添加索引等方法来优化表结构。一同也要防止运用很多冗余字段、防止频频运用join查询等操作。

索引优化

关于大表的查询操作，索引优化是非常重要的一环。能够考虑添加或许修正索引、运用覆盖索引、运用联合索引等方法来前进查询功率。一同也要留意定时整理冗余的索引以及关于常常运用的查询句子树立索引。

分区优化

将大表依照某个列分红多个分区表，每个分区表的数据量较小，能够前进查询和更新的功能。分区表还能够协助在保护表结构的一同，削减锁表时刻，前进并发处理才能。

数据整理归档

关于一些历史数据或许无用数据，能够进行定时归档，防止数据过多造成SQL查询功率下降。一同也要留意关于大表进行定时的数据备份以及紧迫数据恢复的预备工作。

缓存优化

关于一些常常被查询的数据，能够运用缓存优化。运用Redis等缓存中间件来缓存常用的数据，以削减查询数据库的次数，前进查询功率。

SQL句子优化

在编写SQL查询句子时，要尽或许地简略明了，防止杂乱的查询句子，一同也要防止一些不必要的查询操作。关于杂乱的查询句子，能够运用Explain履行方案来进行优化。一同也要留意防止运用OR等耗费功能的操作符。

分库分表

假如数据量千万等级，需求考虑分库分表哈。分库分表相关知识点，能够看我之前这篇文章哈，咱们为什么要分库分表？

5.哪些因素或许导致MySQL慢查询？

慢查询一般有以下这些原因：

咱们有兴趣能够看下。我之前写的这篇文章哈：盘点MySQL慢查询的12个原因

6.怎么运用索引优化SQL查询？

添加适宜索引（在where、group by、order by等后边的字段添加适宜索引）
挑选适宜的索引类型 (B-tree索引合适范围查询、哈希索引合适等值查询)
留意不合适加索引的场景（数据量少的表，更新频频的字段，区分度低的字段）
加索引的时分，需求考虑覆盖索引，削减回表，考虑联合索引的最左前缀准则
explain检查SQL的履行方案，承认是否会射中索引。
留意索引并不是越多越好，一般主张在单个表中不要超越5个索引。由于索引会占用磁盘空间，索引更新代价高。

7.聊聊慢SQL的优化思路

检查慢查询日志记载，剖析慢SQL
explain剖析SQL的履行方案
profile 剖析履行耗时
Optimizer Trace剖析概况
承认问题并采用相应的办法

7.1 检查慢查询日志记载，剖析慢SQL

怎么定位慢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'指令，检查超越多少时刻，才记载到慢查询日志，如下：

long_query_time表明查询超越多少秒才记载到慢查询日志。

咱们能够经过慢查日志，定位那些履行功率较低的SQL句子，要点关注剖析。

7.2 explain检查剖析SQL的履行方案

当定位出查询功率低的SQL后，能够运用explain检查SQL的履行方案。

一条简略SQL，运用了explain的效果如下：

一般来说，咱们需求要点关注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检查最近一条SQL的剖析。也能够运用show profile for query id（其间id便是show profiles中的QUERY_ID）检查详细一条的SQL句子剖析。

除了检查profile ，还能够检查cpu和io，如上图。

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的方法，或许数仓。
假如单表数据量过大导致慢查询，则能够考虑分库分表
假如数据库在刷脏页导致慢查询，考虑是否能够优化一些参数，跟DBA讨论优化方案
假如存量数据量太大，考虑是否能够让部分数据归档

我之前写了一篇文章，有关于导致慢查询的12个原因，咱们看一看一下哈:盘点MySQL慢查询的12个原因

8.一条sql履行过长的时刻，你怎么优化，从哪些方面下手？

这道面试题，其实跟慢SQl排查处理有点像，所以咱们答复得时分，能够参阅上一小节哈。咱们能够从这几个方面下手哈：

承认瓶颈
索引优化
优化SQL句子
数据库参数优化
剖析锁的情况
数据库硬件晋级

承认瓶颈

首先，经过检查MySQL日志，慢查询日志，explain剖析SQL的履行方案，profile 剖析履行耗时，Optimizer Trace剖析概况等操作，承认查询履行的瓶颈在哪里。只要承认了瓶颈，才能有针对性地进行优化。

索引优化

在承认了瓶颈之后，能够考虑经过添加索引来优化查询功率。能够依据查询句子的条件，添加相应的索引，从而加速查询速度。可是索引也会带来一些负面影响，如占用磁盘空间，下降写入功率等，所以需求依据详细情况权衡。

优化SQL句子

有些SQL句子自身或许存在一些问题，如join操作过于频频，运用了不必要的子查询等，这些都会导致查询功率低下。能够经过优化SQL句子来削减不必要的操作，从而前进查询功率。

数据库参数优化

数据库参数也会影响查询功率，能够经过修正数据库参数来优化查询功率，如修正内存缓存巨细、修正衔接池巨细等。不同的数据库参数优化方法不同，需求依据详细情况进行调整。

剖析锁的情况

查询履行时刻过长有或许是由于锁的问题导致的，需求剖析查询句子中是否存在锁的问题，假如存在锁的问题，能够考虑添加锁的并发度，从而前进查询功率。

数据库硬件晋级

假如以上方法都无法处理问题，能够考虑对数据库硬件进行晋级，如添加 CPU 数量、加速磁盘读写速度等，从而前进数据库的整体功能。

9. 罗列一下，常用的数据库规划优化技巧？

字段尽量防止运用NULL
合理挑选数据类型
字段挑选适宜的长度
正确运用索引
尽量少定义text类型
合理的数据表结构规划
恰当的冗余规划
优化SQL查询句子
一张表的字段不宜过多

10.罗列日常开发中，罗列十个书写高质量SQL的小技巧

查询SQL尽量不要运用select *，而是select详细字段。
小表驱动大表
优化你的like句子
尽量防止在索引列上运用mysql的内置函数
假如插入数据过多，考虑批量操作。
多用limit
小表驱动大表
exist & in合理利用
in元素不要过多
尽量用union all替换union

咱们能够参阅我之前这篇文章哈：后端程序员必备：书写高质量SQL的30条主张

11.index merge了解过嘛？

index merge是什么？

在MySQL中，当履行一个查询句子需求运用多个索引时，MySQL能够运用索引兼并(Index Merge)来优化查询功能。详细来说，索引兼并是将多个单列索引或多个联合索引兼并运用，以满意查询句子的需求。

当运用索引兼并时，MySQL会挑选最优的索引组合来履行查询，从而防止了全表扫描和排序操作，前进了查询功率。而关于运用多个单列索引的查询句子，MySQL也能够运用索引兼并来优化查询功能。

咱们能够看一个运用index merge的例子：

假定有一个名为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-02-01'

在履行该查询句子时，MySQL能够运用customer_id索引和order_date索引来优化查询。假如运用单个索引，则需求扫描整个索引树来匹配查询条件；但假如运用索引兼并，则能够先运用customer_id索引来过滤出契合条件的记载，然后再运用order_date索引来进一步过滤记载，从而大大削减了扫描的记载数，前进了查询功率。

咱们能够运用EXPLAIN关键字能够检查查询方案，承认是否运用了索引兼并。例如，履行以下句子：

EXPLAIN SELECT *
FROM orders
WHERE customer_id = 1
AND order_date >= '2022-01-01'
AND order_date < '2022-02-01'

假如查询方案中呈现了Using index merge的信息，则表明该查询运用了索引兼并优化。

12. order by查询功率慢,怎么优化.

咱们是否还记得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等参数优化；

咱们忘掉order by的话,能够看我之前的这篇文章哈: 看一遍就了解：order by详解

13. group by 查询慢的话,怎么优化呀.

group by一般用于分组统计，它表达的逻辑便是依据必定的规矩，进行分组。日常开发中，咱们运用得比较频频。假如不留意，很简单产生慢SQL。

group by或许会慢在哪里？由于它既用到暂时表，又默许用到排序。有时分还或许用到磁盘暂时表。

假如履行过程中，会发现内存暂时表巨细到达了上限（控制这个上限的参数便是tmp_table_size），会把内存暂时表转成磁盘暂时表。
假如数据量很大，很或许这个查询需求的磁盘暂时表，就会占用很多的磁盘空间。

怎么优化group by呢?

group by 后边的字段加索引
order by null 不必排序
尽量只运用内存暂时表
运用SQL_BIG_RESULT

咱们能够看下我这篇文章哈：看一遍就了解：group by详解

最后

我是捡田螺的小男孩，咱们假如觉得面试题的答案有不对的地方，在评论区提出来哈，一同前进，加油~ 假如觉得文章对你有协助，费事给个三连支持一下哈，感谢！留意：未经本人允许，不要转移我的文章到别的渠道！！

SQL优化13连问，收藏好

前语