【案例】MySQL count操作优化案例一则
2023-09-14 08:57:29 时间
一 背景
二 分析
慢查询表结构如下
count(item_id)的意思是符合where条件的结果集中item_id非空集合的总和。
三 如何优化
根据该sql的业务需求是需要获取到某商家参加活动且活动截止时间大于当前时间的商品总数,可以使用如下sql满足要求:
四 小结
a 这个问题是在没有修改索引的基础中做出的优化,老的sql没有有效的利用当前的索引导致耗时操作
b 对于不同count类型的sql 总结如下
count(*)/count(1) 返回结果集的总和包括null和重复的值。
count(column) 返回结果集中非空 column 的总和,执行查询的过程中会校验字段是否非空。
c 在业务设计的时候 满足业务逻辑的前提下推荐使用count(*).
d 从官方文档中摘录 Using where 和 Using index 的区别
华为大佬的“百万级”MySQL笔记,基础+优化+架构一键搞定 MySQL不用多说,大家都知道它是目前最为活跃热门的开源数据库,由于成本低,操作简易的特点,所以在互联网企业中被广泛使用,即使是头部的BATJ。由此可见,想要在互联网行业混得风生水起,或者说想要进入BATJ等一线互联网公司,那么熟练掌握MySQL必定是一块必要的敲门砖。
我又吊打面试官了,凭借MySQL海量数据优化(理论+实战) 朋友们,又见面了,上篇文章咱们讲到MySQL分库分表的方法,这篇文章咱们就针对上一篇文章模拟在MySQL中海量数据的优化方法,文章干货较多,建议三连。 提示:以下是本篇文章正文内容,案例仅供参考
MySQL优化 在我们的实际场景中经常会遇到sql查询较慢的问题,今天特地写一篇文章来聊聊我对于MySQL调优相关内容的知识,以及从哪些点去进行优化.
某业务的数据库定期报 thread_runing 飙高,通定位发现一个慢查询sql导致会话堆积。执行sql 耗时如下
root@db 05:32:05 select count(item_id) from xxxtable where selid = 345705650 and end_time now(); +----------------+ | count(item_id) | +----------------+ | 2247052 | +----------------+ 1 row in set (4.65 sec)
二 分析
慢查询表结构如下
root@db show create table xxxtable \G *************************** 1. row *************************** Table: uac_shop_item_promotion_0091 Create Table: CREATE TABLE `uac_shop_item_promotion_0091` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 主键, `gmt_modified` datetime NOT NULL COMMENT 修改时间, `selid` bigint(20) NOT NULL COMMENT 分表字段, `end_time` datetime NOT NULL COMMENT 活动结束时间, `item_id` bigint(20) NOT NULL COMMENT 商品id, PRIMARY KEY (`id`), UNIQUE KEY `idx_uq_item` (`item_id`), KEY `idx_deller_id_end_time` (`selid`,`end_time`), KEY `idx_deller_id_start_time` (`selid`,`start_time`), KEY `idx_seller_item_start` (`selid`,`start_time`,`item_id`) ) ENGINE=InnoDB AUTO_INCREMENT=42132149 DEFAULT CHARSET=gbk COMMENT=索引表 1 row in set (0.00 sec)很明显出现问题的sql由于使用了count(item_id) ,而item_id字段并没有和 selid 和end_time 构成有效索引 故该sql 没有合理的使用索引 。查看其直系计划
root@db explain select count(item_id) from xxxtable where selid = 345705650 and end_time now() \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: xxxtable type: ref possible_keys: idx_deller_id_end_time,idx_deller_id_start_time,idx_seller_item_start key: idx_deller_id_end_time key_len: 8 ref: const rows: 1726757 Extra: Using where 1 row in set (0.00 sec)从key_len=8 和Extra: Using where 可以看出MySQL没有完全利用到idx_deller_id_end_time组合索引而是利用到了 selid字段作为过滤条件回表查询。
count(item_id)的意思是符合where条件的结果集中item_id非空集合的总和。
三 如何优化
根据该sql的业务需求是需要获取到某商家参加活动且活动截止时间大于当前时间的商品总数,可以使用如下sql满足要求:
select count(*) from xxxtable where selid = 345705650 and end_time now()执行时间仅为原来的1/4,新的sql发布之后thread_running报警消失,业务校验时间明显缩短。
root@db select count(*) from xxxtable where selid = 345705650 and end_time now(); +----------+ | count(*) | +----------+ | 2247052 | +----------+ 1 row in set (0.82 sec) root@db select count(1) from xxxtable where selid = 345705650 and end_time now(); +----------+ | count(1) | +----------+ | 2247052 | +----------+ 1 row in set (0.79 sec)优化后的sql的explain 方式如下:
root@db explain select count(*) from xxxtable where selid = 345705650 and end_time now() \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: xxxtable type: range possible_keys: idx_deller_id_end_time,idx_deller_id_start_time,idx_seller_item_start key: idx_deller_id_end_time key_len: 16 ref: NULL rows: 1726768 Extra: Using where; Using index 1 row in set (0.00 sec)
四 小结
a 这个问题是在没有修改索引的基础中做出的优化,老的sql没有有效的利用当前的索引导致耗时操作
b 对于不同count类型的sql 总结如下
count(*)/count(1) 返回结果集的总和包括null和重复的值。
count(column) 返回结果集中非空 column 的总和,执行查询的过程中会校验字段是否非空。
c 在业务设计的时候 满足业务逻辑的前提下推荐使用count(*).
d 从官方文档中摘录 Using where 和 Using index 的区别
Using index The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index. If the Extra column also says Using where, it means the index is being used to perform lookups of key values. Without Using where, the optimizer may be reading the index to avoid reading data rows but not using it for lookups. For example, if the index is a covering index for the query, the optimizer may scan it without using it for lookups. For InnoDB tables that have a user-defined clustered index, that index can be used even when Using index is absent from the Extra column. This is the case if type is index and key is PRIMARY. Using where A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. Even if you are using an index for all parts of a WHERE clause, you may see Using where if the column can be NULL.
华为大佬的“百万级”MySQL笔记,基础+优化+架构一键搞定 MySQL不用多说,大家都知道它是目前最为活跃热门的开源数据库,由于成本低,操作简易的特点,所以在互联网企业中被广泛使用,即使是头部的BATJ。由此可见,想要在互联网行业混得风生水起,或者说想要进入BATJ等一线互联网公司,那么熟练掌握MySQL必定是一块必要的敲门砖。
我又吊打面试官了,凭借MySQL海量数据优化(理论+实战) 朋友们,又见面了,上篇文章咱们讲到MySQL分库分表的方法,这篇文章咱们就针对上一篇文章模拟在MySQL中海量数据的优化方法,文章干货较多,建议三连。 提示:以下是本篇文章正文内容,案例仅供参考
MySQL优化 在我们的实际场景中经常会遇到sql查询较慢的问题,今天特地写一篇文章来聊聊我对于MySQL调优相关内容的知识,以及从哪些点去进行优化.
相关文章
- 管理MySQL的日月周数据管理最佳实践(mysql日月周数据)
- MySQL中实现时间加减操作(mysql时间加减)
- Mysql 停止触发器的简单操作指南(mysql停止触发器)
- MySQL AB复制:同步数据库的灵活工具(mysql的ab复制)
- 如何快速实现文本导入MySQL数据库(文本导入mysql数据库)
- MySQL数据库操作:掌握基本SQL语句(mysql数据库的语句)
- 解决MySQL转义引号的有效方法(mysql转义引号)
- MySQL数据库中的记录存储的位置(mysql记录的位置)
- MySQL数据库API: 打开新世界的大门(mysql数据库api)
- MySQL字符乱码问题解决方案(mysql字符乱码)
- MySQL写操作优化实战(mysql写优化)
- 如何优化MySQL内存配置(mysql内存配置)
- MySQL序列:解锁更高数据处理能力(mysql的序列)
- MySQL查询结果:实现百分比可视化(mysql查询结果显示)
- MySQL查询日志:记录数据库操作,优化性能(mysql查询日志)
- MySQL切换数据库教程,轻松掌握操作技巧(mysql如何切换数据库)
- MySQL查询语句详解,助您轻松操作数据库。(mysql中的查询语句)
- 实施MySQL集群:从安装到配置(mysql集群安装配置)
- MySQL语句:修改字段名的简易方法(mysql语句修改字段名)
- MySQL数据库主备之间的安全切换(mysql数据库主备切换)
- 深入剖析MySQL,优化数据库操作,提高效率与安全性的好处(mysql好处)
- MySQL修改值的操作技巧(mysql修改值)
- MySQL中重命名表的方法rename操作(mysql中rename)
- Cocoa如何调用MySQL便捷操作数据库(cocoa调用mysql)
- MySQL中如何实现2列相减操作(mysql中2列相减)
- MySQL操作用C编写SQL语句的基本方法(c mysql写语句)
- MySQL轻松切换数据库,无需使用USE命令(mysql不用use)