MySQL高级——SQL语句优化

最新推荐文章于 2024-04-30 19:13:18 发布

Lurenjia915

最新推荐文章于 2024-04-30 19:13:18 发布

阅读量135

点赞数

分类专栏： MySQL高级文章标签： mysql 数据库 Powered by 金山文档

本文链接：https://blog.csdn.net/Lurenjia915/article/details/129059663

版权

MySQL高级专栏收录该内容

6 篇文章 0 订阅

订阅专栏

索引失效的几种情况：MySQL高级——索引失效的几种情况_Lurenjia915的博客-CSDN博客

EXPLAIN性能分析工具：MySQL高级——性能分析之EXPLAIN_Lurenjia915的博客-CSDN博客

在后端系统中经常会出现数据库查询缓慢的情况，有可能是因为硬件原因，但也有可能是因为SQL语句优化不到位也就是慢SQL的原因。在经过一段时间的观察分析后，如果系统中确实存在慢SQL的情况，我们需要通过以下几个步骤来分析并优化慢SQL：

第一步：开启慢查询日志，设置阈值比如超过5秒就是慢SQL，然后将慢SQL抓取出来；
第二步：使用Explain分析慢SQL，并进行优化；
第三步：如果前两步不行show profile更深入的查看服务器执行语句的工作情况。

1、慢查询日志

慢查询日志是MySQL提供的一种日志记录，它用来记录响应时间超过阀值的语句。具体指如果某条SQL运行时间超过long query time值，就会被记录到慢查询日志中。long query time的默认值为10，可以自己进行设置。

默认情况下，MySQL数据库没有开启慢查询日志，因为开启馒查询日志会或多或少带来一定的性能影响。如果调优需要的话，我们可以手动来开启这个参数。

使用慢查询日志

查看当前慢查询日志状态：

slow_query_log：慢查询日志是否开启，OFF为未开启
slow_query_log_file：当前慢查询日志输出位置

开启慢查询日志语句（重启后失效）：

set global slow_query_log=1

在my.cnf文件中添加以下语句开启慢查询日志（永久有效）：

slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow,log

修改慢SQL判断阈值：

set global long_query_time=3

慢查询日志示例：

慢查询日志分析工具（mysqldumpslow）

mysqldumpslow使用示例：

mysqldumpslow帮助信息：

s：是表示按照何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感的

2、Explain分析

id：分析几个大查询几个小查询和查询顺序，大步骤越少越好

select_type：分析使用了哪些查询，尽量不要有子查询

type：分析此次查询的类型，尽量优化到range、ref及以上级别

key_len：值越大说明索引中的字段用到的越多，可以分析出实际用了哪些字段

key：实际使用的索引名称

rows：估算出找到所需数据需要读取的行数，值越小越好

ref：分析索引字段与哪些值进行比较，可能是常量或者其他字段

Explain的使用详情还请看：MySQL高级——性能分析之EXPLAIN

3、SQL语句的编写建议

（1）关联查询时要使用小表驱动大表

比如左连接，驱动表为左表，要用左表每一行都在右表中找一次对应数据。就比如员工表和部门表，5个部门100个员工，以部门表为驱动表的话只需要在员工表中循环找5次数据即可。可能有人会想5次循环每次对比100条数据，和100次循环每次对比5条数据结果不是一样嘛？这时就体现出索引的作用，索引可以很快的找出数据而不是加载全表，但是驱动表必须加载全表。

下图为关联查询示例：

（2）关联查询最好在被驱动表的查询字段加索引

以下图为例：驱动表是class，被驱动表是book，关联的字段是card。在book表的card字段建立索引，极大的提高了查询效率。

（3）关联的表最好不要超过三个

如果关联的表过多会严重影响查询的性能。

（4）尽量不要使用子查询

子查询效率太差。执行子查询需要创建临时表，查询完毕后再删除这些临时表，多了一个创建和销毁临时表的过程，这严重影响了性能。如果业务量小，尽量使用关联查询代替子查询。如果数据量极其庞大，尽量在业务逻辑层处理数据，因为数据库是最底层的，性能瓶颈往往出现在数据库。

（5）尽量使用UNION ALL代替UNION

union和union all的区别是，一个在查询时去除重复数据一个没有去重。因为union多一个去重操作，所以会更加消耗数据库性能。跟前面的道理一样，数据库往往是业务中性能瓶颈，所以能不在数据库中做的操作就不要在数据库中做。

（6）最好不要使用SELECT *

在使用select取数据时要按需取，用不到的字段就不要取了，避免没必要的性能浪费。select *最好也不要使用，这是一个很好的编程习惯。

复合索引里包括的字段刚好是select查找时需要找的字段，这就叫做覆盖索引。此时直接从索引中就可以获取数据，不必读取数据行，大大的提高了性能。所以我们查询时尽量使用覆盖索引。

（7）排序和分组优化

减少查询数据库时的排序操作，建议将排序放到业务逻辑中去做，因为排序操作也会浪费一部分性能。核心思想还是数据库是性能瓶颈，能不在数据库中做的操作就不要在数据库中做。例如order by、group by、distinct这些语句，数据量大时尽量都不要在数据库中做。

因为where在group by之前执行可以过滤到一部分数据从而减小分组压力，所以where效率高于having能写在where限定的条件就不要写在having中了。使用order by、group by、distinct这些语句时，where条件过滤出来的结果集最好保持在1000行以内，否则SQL会很慢。