小工具      在线工具  汉语词典  css  js  c++  java

解决mysql所有查询都有索引但速度慢的问题

mysql,mysql 额外说明

收录于:40天前

最近,有一个很奇怪的情况。查询限制10很慢,where中的两个字段和order by中的一个字段都建立了索引。这张表有1200W的数据,更新也很多,但是压力还是可以的。

配置文件 查看使用时间

profile可以查看执行时间

SET profiling = 1;

select t.* from t_user t where 1=1 and t.logintype= 'qq' and t.channel= 'scb_weixin' order by t.update_time DESC limit 10 ;

show PROFILES;

结果:

169    0.00231675    SHOW STATUS 170 0.00251925 SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID 171 0.00085025 SELECT STATE AS `状态`, ROUND(SUM(DURATION),7) AS `期间`, CONCAT(ROUND(SUM(DURATION)/0.001298*100,3), '%') AS `百分比` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=168 GROUP BY STATE ORDER BY SEQ 172 0.000857 SELECT * FROM `t_pdb`.`t_user` LIMIT 0 173 0.00316375 SHOW COLUMNS FROM `t_pdb`.`t_user`

show PROFILE for QUERY <从上面一个语句中找到,如 172>
结果:

starting    0.000135
checking permissions    6.7E-5
Opening tables    7.4E-5
init    0.000105
System lock    7.1E-5
optimizing    7.8E-5
statistics    0.000209
preparing    7.9E-5
Sorting result    6.6E-5
executing    6.5E-5
Sending data    8.5E-5
Creating sort index    33.105645
end 0.000179
query end 9.1E-5
closing tables    7.3E-5
freeing items    9.3E-5
logging slow query    0.000112
cleaning up    7.4E-5

显示状态视图

FLUSH STATUS;
select t.* from t_user t where 1=1 and t.logintype= 'qq' and t.channel= 'scb_weixin' order by t.update_time DESC limit 10 ;
show status where variable_name like 'handler%' or variable_name like 'created%';

结果:

Created_tmp_disk_tables    0
Created_tmp_files    0
Created_tmp_tables    0
Handler_commit    1
Handler_delete    0
Handler_discover    0
Handler_external_lock    6
Handler_mrr_init    0
Handler_prepare    0
Handler_read_first    0
Handler_read_key    4583930
Handler_read_last    0
Handler_read_next    15239952
Handler_read_prev    0
Handler_read_rnd    4583928
Handler_read_rnd_next    0
Handler_rollback    0
Handler_savepoint    0
Handler_savepoint_rollback    0
Handler_update    0
Handler_write    0

上面的数字是非常大的。网上查了一下,好像和订单有关。

实施计划

explain
select t.* from t_user t where 1=1 and t.logintype= 'qq' and t.channel= 'scb_weixin' order by t.update_time DESC limit 10 ;

结果:

1    SIMPLE    t        index_merge    idx_channel,idx_logintype    idx_channel,idx_logintype    99,99        3083430    100    Using intersect(idx_channel,idx_logintype); Using where; Using filesort

情况分析,

时间都花费在 Creating sort index,这是和order有关的语句,
把order 给去掉,或者order by id desc 则还是很快,说明貌似是 和update_time有关

解决方案:

一,修改mysql的部分参数,用于
https://forums.mysql.com/read.php?24,609971,609971

max_connections=10000
query_cache_size=0
table_open_cache=10000
tmp_table_size=150M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=175M
key_buffer_size=128M
read_buffer_size=1M
read_rnd_buffer_size=4M

二,增加order by 的参数的 where xxx is not null
这个立杆见影,把条件is not null加到where中 去

select t.* from t_user t where 1=1 and t.logintype= 'qq' and t.channel= 'scb_weixin' and t.update_time is not null order by t.update_time DESC limit 10 ;

跟进

相关情况我还没有找到相关资料,但是在where中添加了条件不为空,效果确实非常明显。如果您有相关信息,请留言告诉我^_^

附录

表结构

CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `scb_user_id` varchar(256) DEFAULT NULL COMMENT '', 。。。。。 PRIMARY KEY (`id`), UNIQUE KEY `ukey_login_name_type` (`login_name`,`logintype`,`channel`), KEY `idx_device_guid` (`device_guid`), KEY `idx_update_time` (`update_time`), KEY `idx_channel` (`channel`), KEY `idx_logintype` (`logintype`), KEY `idx_scb_user_id` (`scb_user_id`), KEY `idx_status` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=12915154 DEFAULT CHARSET=utf8;
. . .

相关推荐

额外说明

渗透测试-安全服务体系+OWASP top 10

安全服务体系+OWASP 介绍 注入injection 危害 常见的注入 如何防范 失效的身份认证 敏感数据泄露 XML外部实体(XXE) 失效的访问控制 安全配置错误 跨站脚本(xss) 不安全的反序列化 使用含有已知漏洞的组件 不足的日志记录和监控

额外说明

go 包循环引用 (import cycle not allowed)

前言 在项目中,我们或多或少都会遇到go的包循环引用的问题,类似这样 这就让人很头疼,为什么在其他语言中都没见过这种错误呢?这我们就得来谈谈go的设计理念了。 go 为什么不允许循环引用 曾经有人提议Go语言作者Rob Pike,想要在Go以后的版本去掉

额外说明

kubesphere容器平台部署

kubesphere简介 KubeSphere是一个以应用为中心的容器平台,完全开源,KubeSphere 帮助企业在云、虚拟化及物理机等任何环境中快速构建、部署和运维基于 Kubernetes 的容器架构,轻松实现微服务治理、多租户管理、DevOps

额外说明

Audacity 使用教程:轻松录制、编辑音频

Audacity 使用教程:轻松录制、编辑音频 1. 简介 Audacity 是一款免费、开源且功能强大的音频录制和编辑软件。它适用于 Windows、Mac 和 Linux 等多种操作系统,适合音乐制作、广播后期制作以及普通用户进行音频处理。本教程将带

额外说明

web3学习博客目录大全

Web3是关于互联网发展的一个概念,主要与基于区块链的去中心化、加密货币、非同质化代币(NFT)和元宇宙有关。但Web3尚且是一个模糊的广义概念,具体什么是web3时代,待你我共同创造! 由于会陆续更新web3相关博客,为了更方便阅读,会在这里整理一下博

额外说明

Leetcode 671: 二叉树中的第二最小值

想法: 首先,我们可以想到利用深度优先算法遍历这个二叉树。 这个深度优先搜索是有条件,我们只有在node的value等于left的value或者right的value时才会继续,不相等时就会停止。因为root的value是最小的,并且是在left和rig

额外说明

为什么短信验证码要设置有效期?

安全性:验证码的主要目的是为了验证用户的身份,防止恶意或未经授权的访问。如果验证码没有有效期,恶意用户或攻击者可以获取验证码后无限期地尝试使用它。通过设置有效期,可以限制验证码的生命周期,提高系统的安全性。 隐私保护:验证码通常包含用户敏感信息或个人身份

额外说明

Visual Studio 2008单元测试之NMock

  在做单元测试时,有时候需要其它的类的支持,但是其它的类很可能没有写好,或者根本就不存在,为了继续进行测试,我们不得不构造一个虚拟的类来帮助,NMock就是这样一个工具。 NMock2是NMock的针对FrameWork2.0的版本,免费的,网上教程非

额外说明

解决Win10找不到mfcm100.dll程序

其实很多用户玩单机游戏或者安装软件的时候就出现过这种问题,如果是新手第一时间会认为是软件或游戏出错了,其实并不是这样,其主要原因就是你电脑系统的该dll文件丢失了或者损坏了,这时你只需下载这个mfcm100.dll文件进行安装(前提是找到适合的版本),当

额外说明

短视频直播运营VIP系统课:零基础如何实现快速起号运营

新书名:短视频直播运营VIP系统课程:从零开始快速掌握账号运营技巧精髓 文章: 短视频直播运营已经成为一种新兴的赚钱方式和流量入口,但很多人可能会因为缺乏经验而面临运营的困惑。好消息是,有一门名为《短视频直播运营VIP系统课程:如何零基础实现快速启动运营

ads via 小工具