SQL优化13连问，收藏好！-sql的优化有哪几种

原标题：SQL优化13连问，收藏好！前言大家好，我是田螺。金三银四已经开始啦，准备了SQL优化的13连问，内容非常干！相信大家看完一定会有帮助的。

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、key3.1 typetype表示连接类型，查看索引执行情况的一个重要指标以下性能从好到坏依次：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的优化思路查看慢查询日志记录，分析慢SQLexplain分析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的执行计划。

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

一般来说，我们需要重点关注type、rows、filtered、extra、key7.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 ordersWHERE customer_id = 1

AND order_date >= 2022-01-01AND order_date < 2022-02-01在执行该查询语句时，MySQL可以使用customer_id索引和order_date索引来优化查询。

如果使用单个索引，则需要扫描整个索引树来匹配查询条件；但如果使用索引合并，则可以先使用customer_id索引来过滤出符合条件的记录，然后再使用order_date索引来进一步过滤记录，从而大大减少了扫描的记录数，提高了查询效率。

大家可以使用EXPLAIN关键字可以查看查询计划，确认是否使用了索引合并例如，执行以下语句：EXPLAIN SELECT *FROM ordersWHERE customer_id = 1AND 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一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。

日常开发中，我们使用得比较频繁如果不注意，很容易产生慢SQLgroup by可能会慢在哪里？因为它既用到临时表，又默认用到排序有时候还可能用到磁盘临时表如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。

如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间如何优化group by呢?group by 后面的字段加索引order by null 不用排序尽量只使用内存临时表使用SQL_BIG_RESULT。

返回搜狐，查看更多责任编辑：