MySQL中的慢查询是什么及有哪些威胁
发布时间：2023-06-16 15:21:36 所属栏目：MySql教程 来源：
导读：为大家详细介绍“MySQL中的慢查询是什么及有哪些危害”，内容详细，步骤清晰，细节处理妥当，希望这篇“MySQL中的慢查询是什么及有哪些危害”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深
|
为大家详细介绍“MySQL中的慢查询是什么及有哪些危害”，内容详细，步骤清晰，细节处理妥当，希望这篇“MySQL中的慢查询是什么及有哪些危害”文章能帮助大家解决疑惑，下面跟着小编的思路慢慢深入，一起来学习新知识吧。 一、什么是慢查询 什么是MySQL慢查询呢？其实就是查询的SQL语句耗费较长的时间。 具体耗费多久算慢查询呢？这其实因人而异，有些公司慢查询的阈值是100ms，有些的阈值可能是500ms，即查询的时间超过这个阈值即视为慢查询。 正常情况下，MySQL是不会自动开启慢查询的，且如果开启的话默认阈值是10秒 # slow_query_log 表示是否开启 mysql> show global variables like '%slow_query_log%'; +---------------------+--------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/0bd9099fc77f-slow.log | +---------------------+--------------------------------------+ # long_query_time 表示慢查询的阈值，默认10秒 show global variables like '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 二、慢查询的危害 既然我们这么关注慢查询，那它肯定是有一些不好的地方，常见的有这几个： 1、用户体验差。 我们访问一个东西，或者保存一个东西，都得等好久，那不得分分钟弃坑？等等，我知道体验是会差，但慢查询的阈值设置为100ms似不似太低了，我访问一个东西1-2秒应该也能接受吧。其实这个阈值并不算太低，因为这是一条SQL的阈值，而你一个接口可能要查好几次SQL，甚至调下外部接口都是很常见的。 2、占用MySQL内存，影响性能 MySQL内存本来就是有限的（大内存要加钱！），SQL为什么查询慢呢？有时候就是因为你全表扫导致查询的数据量很多，再加上各种筛选就变慢了，所以慢查询往往也会意味着内存占用的增高，内存一高，能够承载的SQL查询就变少了，性能也变差了。 3、造成DDL操作阻塞 众所周知，InnoDB引擎默认加的是行锁，但锁其实都是加在索引上的，如果筛选条件没有建立索引，会降级到表锁。而慢查询有一大部分原因都是因为没加索引导致的，所以慢查询时间过长，就会导致表锁的时间也很长，如果这时候执行DDL就会造成阻塞。 三、慢查询常见场景 既然慢查询造成的问题这么多，那一般什么场景下会出现慢查询呢？ 1、没加索引/没利用好索引 在没加索引的情况，就会造成全表扫描；又或者没走到索引（或者走的不是最优索引），这两张情况都会导致扫描行数增多，从而查询时间变慢。 下面是我测试的一个例子： # 这是我的表结构，算是一种比较常规的表 create table t_user_article ( id bigint unsigned auto_increment primary key, cid tinyint(2) default 0 not null comment 'id', title varchar(100) not null, author varchar(15) not null, content text not null, keywords varchar(255) not null, description varchar(255) not null, is_show tinyint(1) default 1 not null comment ' 1 0', is_delete tinyint(1) default 0 not null comment ' 1 0', is_top tinyint(1) default 0 not null comment ' 1 0', is_original tinyint(1) default 1 not null, click int(10) default 0 not null, created_at timestamp default CURRENT_TIMESTAMP not null, updated_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP ) collate = utf8mb4_unicode_ci; 在上述表结构下，我通过 [Fill Database](https://filldb.info/) 这个网站随机生成了一批数据进行测验，可以看到，在没加索引的前提下，基本5万条数据后就会开始出现慢查询了（假设阈值为100ms） 数据量 字段数量 查询类型 查询时间 1000 * 全表（ALL） 约80ms 50000 * 全表（ALL） 约120ms 100000 * 全表（ALL） 约180ms 2、单表数据量太大 如果本身单表数据量太大，可能超千万，或者达到亿级别，可能加了索引之后，个别查询还是存在慢查询的情况，这种貌似没啥好办法，要么就看索引设置得到底对不对，要么就只能分表了。 3、Limit 深分页 深分页的意思就是从比较后面的位置开始进行分页，比如每页有10条，然后我要看第十万页的数据，这时候的分页就会比较“深” 还是上面的 t_user_article 表，你可能会遇到这样的一条深分页查询： -- 个人测试： 106000条数据，耗时约 150ms select * from t_user_article where click > 0 order by id limit 100000, 10; 在这种情况下，即使你的 click 字段加了索引，查询速度可能还是很慢（测试后和不加差不多），因为二级索引树存的是主键ID，查到数据还需要进行回表才能决定是否丢弃，像上面的查询，回表的次数就达到了100010次，可想而知速度是非常慢的。 结合上面的分析，目前的解决思路都是先查出主键字段（id），避免回表，再根据主键查出所有字段。 第一种，延迟关联，此时SQL变为： -- 个人测试： 106000条数据，耗时约 90ms select * from t_user_article t1, (select id from t_user_article where click > 0 order by id limit 100000, 10) t2 WHERE t1.id = t2.id; 第二种，分开查询，分开查询的意思就是分两次查，此时SQL变为： -- 个人测试： 106000条数据，耗时约 80ms select id from t_user_article where click > 0 order by id limit 100000, 10; -- 个人测试： 106000条数据，耗时约 80ms select * from t_user_article where id in (上述查询得到的ID) 大家可能会很疑惑，为什么要分开查呢，毕竟分开查可能最终耗时比一次查询还要高！这是因为有些公司（比如我司）可能只对单条SQL的查询时长有要求，但对整体的并没有要求，这时候这种办法就能达到一个折中的效果。 另外，大家在网上可能会看到利用子查询解决的办法，比如改成这样： select * from t_user_article where id in (select id from t_user_article where click > 0 limit 100000, 10) 但这时候执行你会发现抛出一个错误： “This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery’”，翻译过来就是子查询不支持Limit，解决办法也很简单，多嵌套一层即可： -- 个人测试： 106000条数据，耗时约 200ms select * from t_user_article where id in (select t.id from (select id from t_user_article where click > 0 order by id limit 100000, 10) as t) 但问题是测试后发现耗时反而变长了，所以并没有列举为一种解决办法。 4、使用FileSort查询 什么是FileSort查询呢？其实就是当你使用 order by 关键字时，如果待排序的内容不能由所使用的索引直接完成，MySQL就有可能会进行FileSort。 当查询的数据较少，没有超过系统变量 sort_buffer_size 设定的大小，则直接在内存进行排序（快排）；如果超过该变量设定的大小，则会利用文件进行排序（归并）。 FileSort出现的场景主要有以下两种： 4.1 排序字段没加索引 # click 字段此时未加索引 explain select id, click from t_user_article where click > 0 order by click limit 10; # explain 结果： type：ALL Extra：Using where; Using filesort 解决办法就是在 click 字段上加索引。 4.2 使用两个字段排序，但是排序规则不同，一个正序，一个倒序 # click 字段此时已加索引 explain select id, click from t_user_article where click > 0 order by click desc, id asc limit 10; # explain 结果： type：range Extra：Using where; Using index; Using filesort （编辑：聊城站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
