SQL调优笔记

2020-08-07  本文已影响0人  于饼喵

1. SQL性能下降原因

开始由于数据较少，SQL的执行效率不会有太大影响，但当业务数据增多时，SQL的性能会逐渐下降。SQL性能下降主要有以下4种原因

2. SQL调优

SQL调优主要分为4个步骤：

2.1 慢查询的开启和捕获

2.1.1 查看日志开启情况

进入mysql后，可以使用show variables like '%low_query_log%'来查看慢查询日志开启情况，结果会返回slow_query_log的开启情况，一般是off，还有slow_query_log所在的位置

mysql -u root -p    # 今天mysql
# Enter password
show variables like '%slow_query_log%';

2.1.2 开启日志

输入set global slow_query_log = 1;开启慢查询日志
注意在调式时才打开慢查询日志，平时不需要打开

set global slow_query_log = 1;  # 开启慢查询日志

set global slow_query_log = 0;  # 关闭慢查询日志

2.1.3 查看和设置阈值

多慢的sql语句才会被记录在慢查询日志中呢?这时候就需要调整阈值，使用show variables like '%long_query_time%';可以查看当前的阈值，一般默认是10s

如果需要更小或者更大的阈值，可以使用set global long_query_time = ;来设定理想的阈值，根据实际情况设定对应阈值

show variables like '%long_query_time%';

set global long_query_time = 3;  # 设定阈值为3秒

2.1.4 查看低效SQL

在mysql中输入 show global status like'%slow_queries%'来查看低于阈值的sql语句条数，然后根据查询慢日志是否开启返回的结果slow_query_log可以查看慢查询日志文件所在位置，打开慢查询日志查看慢于阈值的低效SQL语句

 show global status like'%slow_queries%'

慢查询日志主要是用于低效SQL语句的捕获，捕获了对应的低效SQL语句后，就可以对其进行分析

2.2 使用explain分析

对于慢查询日志捕获的低效SQL语句，可以使用explain进行分析，分析其低效的原因
explain + SQL语句 可以查看单个SQL语句的执行计划，模拟优化器执行SQL语句，从而知道MYSQL是如何处理MYSQL语句的，进而分析查询语句或表结构的性能瓶颈

使用explian + SQL语句后会出现如图所示表格，各参数意义如下：

type类型从优到差的顺序为system > const > eq_ref > ref > range > index > ALL
要尽量避免出现ALL，因为全表扫描严重影响SQL性能

实际使用时，主要考察，id；type；key；row；Extra。考察type中是否有ALL；key是否存在索引失效；extra是否存在using filesort和using temporary这些严重影响性能的情况

2.3 使用show profile分析

对比explain，使用show profile可以更进一步分析低效的SQL 语句

show profile 提供可以用来分析当前会话中语句执行的资源消耗情况，用于SQL的调优测量，默认情况下，参数处于关闭状态

2.3.1 开启profile

使用SHOW VARIABLES LIKE 'profiling';查看profiling开启状态，默认关闭，保存15条运行结果

使用SET PROFILING=on; 开启profiling

SHOW VARIABLES LIKE 'profiling';  # 查看profiling开启状态

SET PROFILING=on;                  # 开启profiling

2.3.2 分析prifile

开启profile后，SQL语句会被记录在profiles中，使用show profiles; 可以查看语句对应的id和运行时间

一般查看cpu占用和io情况，可以使用show profile cpu ,block io for query <id>;来查看对应id语句的cpu和io情况，也可以改为ALL查看全部信息

show profiles     # 查看profiles

show profile cpu ,block io for query 3;  # 3可以改为其他id值

2.3.3. 相关结果说明

converting HEAP to MYISAM      #查询结果太大，内存不够用了往磁盘上搬了
Creating tmp table             #创建临时表
Copying to tmp table on disk ; #把内存中临时表复制到硬盘

2.4 SQL数据库服务器的调优

增大buffer缓冲区等方法进行数据库服务器调优

2.5 索引分析

2.5.1 索引基本概念

CREATE INDEX [indexName] ON 表明(列名)  # 创建索引

DROP INDEX [indexName] ON mytable;     # 删除索引

SHOW INDEX FROM table_name             # 查看索引

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …    # 强制索引

SELECT * FROM TABLE1 IGNORE INDEX (idx_)…      # 忽略索引

2.5.2 索引失效

2.5.3 索引优化

SQL索引失效主要是由于上述8种情况，除了注意避免以上情况导致的索引失效外，还要注意在建立索引上的优化

在建立索引时，要注意以下几种问题:

上一篇 下一篇

猜你喜欢

热点阅读