MySQL调优（慢查询、EXPLAIN以及索引、分页优化）

最新推荐文章于 2023-02-26 14:23:07 发布

木霖森77

最新推荐文章于 2023-02-26 14:23:07 发布

阅读量459

点赞数

分类专栏：数据库文章标签： mysql优化分页 limit offset

本文链接：https://blog.csdn.net/mulinsen77/article/details/86215127

版权

数据库专栏收录该内容

23 篇文章 1 订阅

订阅专栏

首先，为什么要进行MySQL的优化：
1.可能查询过慢导致页面无法加载或者阻塞数据无法提交；
2.避免数据库链接TimeOut产生页面5XX的错误。
在这里插入图片描述从上图可以看出，SQL以及索引的优化是最重要的，成本最低效果最好。所以我们来学习一下如何进行SQL的优化。

慢查询日志配置

慢查询日志用于对有效率问题的SQL进行监控。

show variables like 'slow_query_log'; //查看是否开启了慢查询
set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log'; //设置慢查询日志的位置
set global log_queries_not_using_indexes=ON; //是否记录未使用索引的查询
set global long_query_time=1;//设置记录超过多长时间的SQL语句
set global slow_query_log=ON;//设置慢查询日志是否开启

在这里插入图片描述

分析慢查询日志的工具

1.mysqlDumpSlow MYSQL自带慢查询分析工具

该工具使用方法可以直接通过 mysqldumpslow -h来查看

pt-query-digest

支持将分析结果保存在文件或者数据表中。

输出到文件
pt-query-digest slow.log > slow_log.report
输出到数据库表
pt-query-digest slow.log -review \
h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review \
--creat-reviewtable \
--review-history t=hostname_slow

其他使用方法可以通过 pt-query-digest -help自行查看

eg: pt-query-digest /home/mysql/data/mysql-slow.log | more
通过上面的命令，会列出慢查询日志的分析结果，分为三个部分。

第一部分中包含日志中有多少个SQL，多少个不同的SQL，SQL执行的时间范围，总的执行时间，最短的执行时间，最长的执行时间，平均执行时间，总锁定时间，总发送行数，总检索行数等等。
在这里插入图片描述

定位有问题的SQL

通过上面的慢查询日志分析我们可以定位需要优化的SQL,通常有三种：

1.查询次数多且每次查询占用时间长的SQL：通常为pt-query-digest分析的前几个查询。
2.IO大的SQL：注意pt-query-digest分析中的Rows examine项
3.未命中索引的SQL：注意pt-query-digest分析中Rows examine和Rows Send的对比。

通过Explain查询和分析SQL的执行计划

可以通过Explain查询SQL的执行计划，例子如下：
在这里插入图片描述

索引优化

选择合适的列进行索引

1.在where从句、groupBy从句 order by 从句以及on 从句中出现的列
2.索引的字段要小
3.联合索引的建立时，离散度大的列放在联合索引的前面。

维护和优化索引

要避免重复以及冗余的索引。
可以使用pt-duplicate-key-checker工具可以检查重复及冗余索引。

同时还要注意及时删除由于业务变更不再使用的索引。目前MySQL中还没有记录索引的使用情况，但在PerconMuSQL和MariaDB中可以通过INDEX_STATISTICS表来查看哪些索引未使用，在MySQL中目前只能通过慢查询日志配合pt-index-usage工具来进行索引的使用情况的分析。

总结：

SQL优化可以通过慢查询日志进行查询管理，可以定位需要优化的SQL

Explain查询和分析SQL的执行计划

索引的优化

MYSQL中的分页优化

首先我们学习一下有关limit语句的优化：

mysql数据库中的查询语句有关limit语句的优化。

一般limit是用在分页的程序的分页上的，当应用数据量够小时，也许感觉不到limit语句的任何问题，但当查询数据量达到一定程度时，limit的性能就会急剧下降。这个是通过大量实例得出来的结论。

例子

对同一张表在不同的地方取10条数据：
1）offset比较小时
select * from user limit 10,10; 这条sql语句多次运行，时间保持在0.0004-0.0005之间。
代码示例: select * from user where uid >=( select uid from user order by uid limit 10,1 ) limit 10; 这条sql语句多次运行，时间保持在0.0005-0.0006之间，主要是0.0006。结论：偏移offset较小时，直接使用limit较优。这个显然是子查询的原因。
2）offset大时
代码示例: select * from user limit 10000,10; 这条sql语句多次运行，时间保持在0.0187左右
代码示例: select * from user where uid >=( select uid from user order by uid limit 10000,1 ) limit 10; 这条sql语句多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。
通过以上对比，得出mysql limit查询语句优化经验： 使用limit语句时，当数据量偏移量较小时可以直接使用limit，当数据量偏移量较大时，可以适当的使用子查询来做相关的性能优化。

这种方式需要注意的是：
如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。

举例1：
如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。

SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);

举例2：
如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0，这个主键最好是int类型并且auto_increment

SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;

基础知识

offset：偏移量：虚拟模式下的程序，操作系统只提供了 flat 模式，地址本身就是一种偏移量。在操作系统中，偏移量指的是相对基地址之间的偏移，段地址是基地址，比如段地址为100，偏移5个地址就是105。

在Mysql的查询语句中，offset表示的是分页偏移量，例如：SELECT * FROM city ORDER BY id DESC LIMIT 100000, 15;通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。

一个错误的优化方法

对于获取总的存储记录的sql语句来说：

SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS可以在能够在分页查询时事先准备好符合条件的记录数，随后只要执行一句select FOUND_ROWS(); 就能获得总记录数。但是在大多数情况下，查询语句简短并不意味着性能的提高。

下面这条语句耗时9.28sec（InnoDB为存储引擎），当改为第二条语句时，同样可以完成操作，不过耗时为20.02sec，

SELECT COUNT(*) FROM city;
//SELECT SQL_CALC_FOUND_ROWS * FROM city ORDER BY id DESC LIMIT 100000, 15;

事实证明使用SQL_CALC_FOUND_ROWS做分页是很糟糕的想法。

高效的计算行数

采用的是MYISAM的话，直接执行count(*)获取行数即可。(InnoDB中没有获取行数的语句)
InnoDB中不保存行数，所以我们可以将行数缓存起来，然后可以通过一个守护进程定期更新或者用户的某些操作导致缓存失效时，执行下面的语句：

SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);

获取记录

简单的分页查询：方式一：
在这里插入图片描述
方式二：

SELECT id
FROM (
   SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
   FROM news 
   JOIN (SELECT @cnt:= 0)T
   WHERE id < $last_id
   ORDER BY id DESC
   LIMIT $perpage * $buttons
)C
WHERE cnt = 0;

通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。也就是说可以在页面上显示出每一页的id。

如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。

SET @p:= 0;
UPDATE news SET page=CEIL((@p:= @p + 1) / $perpage) ORDER BY id DESC;

还可以直接新增一个分页的表，用一个后台程序来维护：

UPDATE pagination T
JOIN (
   SELECT id, CEIL((@p:= @p + 1) / $perpage) page
   FROM news
   ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;

这样的话，获取任意一页的元素就变得很简单了：

SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;

在这里插入图片描述

分表

一般来说，数据量不大的时候，我们使用Offset或者limit就可以实现分页，但是数据量大的时候我们就需要进行分表的操作。

部分转自https://yemengying.com/2016/05/28/optimized-pagiantion-mysql/

木霖森77

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL调优（慢查询、EXPLAIN以及索引、分页优化）

首先，为什么要进行MySQL的优化：1.可能查询过慢导致页面无法加载或者阻塞数据无法提交；2.避免数据库链接TimeOut产生页面5XX的错误。从上图可以看出，SQL以及索引的优化是最重要的，成本最低效果最好。所以我们来学习一下如何进行SQL的优化。慢查询日志配置慢查询日志用于对有效率问题的SQL进行监控。show variables like 'slow_query_log'; ...
复制链接

扫一扫