Mysql查询语句优化一则

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

   最近一直忙于开发业务系统,数据库从原来的Oracle被替换成了Mysql,但在实际线上运行中发现有条sql执行起来非常慢,更奇怪的是这句sql还会导致整个数据库性能下降。这个问题非常严重!该sql和表结构如下:

 

 
  1. SELECT name 
  2.        ,COUNT(*) AS counts 
  3.        ,type 
  4. FROM  entityNameTemp 
  5. WHERE postTime > '2011-06-01 00:00:00' 
  6. GROUP BY name 
  7. ORDER BY counts DESC 
  8. LIMIT  10 

 
  1. | entityNameTemp | CREATE TABLE `entityNameTemp` ( 
  2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键'
  3.   `namevarchar(600) DEFAULT NULL COMMENT '人名或者机构名'
  4.   `type` enum('personName','organizationName'DEFAULT NULL COMMENT 'personName 人名;organizationName:机构名'
  5.   `postTime` timestamp NULL DEFAULT NULL COMMENT '发帖时间'
  6.   `createTime` timestamp NULL DEFAULT NULL COMMENT '创建时间'
  7.   PRIMARY KEY (`id`) 
  8. ) ENGINE=Innodb AUTO_INCREMENT=1931915 DEFAULT CHARSET=utf8                        | 

    这是对一个百万级别的临时表,目的是做一下统计取排名前十的数据。先来查看一下执行计划:

 
  1. +----+-------------+----------------+------+---------------+------+---------+------+---------+----------------------------------------------+ 
  2. | id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows    | Extra                                        | 
  3. +----+-------------+----------------+------+---------------+------+---------+------+---------+----------------------------------------------+ 
  4. |  1 | SIMPLE      | entityNameTemp | ALL  | NULL          | NULL | NULL    | NULL | 1735829 | Using where; Using temporary; Using filesort | 
  5. +----+-------------+----------------+------+---------------+------+---------+------+---------+----------------------------------------------+ 

    无疑是用到了临时表以及排序,且没有用上索引。但mysql的执行计划实在很难定位具体问题。之前有查过mysql对临时文件的使用规则,主体思路是查看tmp_table_size参数,mysql会评估一下本次查询大概会需要用到的内存大小,如果小于该参数则会使用磁盘临时文件。但这个参数我已经改到了200m,但问题依旧。查看了一下,发现设置了参数但仍然使用了磁盘。查询发现Created_tmp_disk_tables参数在sql语句执行前后增加了1:

 
  1. mysql> show status like  '%tmp%'; 
  2. +-------------------------+-------+ 
  3. | Variable_name           | Value | 
  4. +-------------------------+-------+ 
  5. | Created_tmp_disk_tables | 2     | 
  6. | Created_tmp_files       | 15    | 
  7. | Created_tmp_tables      | 7     | 
  8. +-------------------------+-------+ 

    这就非常奇怪了,因为我手动计算发现这些数据量绝对不会超过200m,理论上是应该要用内存临时表的。这只好拿出杀手锏,查看详细的执行计划。在命令行下依次执行:
    1) set profiling = 1; #启动分析功能
    2) #sql语句执行; 
    3) show profile; #就可以看到详细的时间消耗,另外可以用show profiles查看执行过的sql语句; 
    4) show profiles; #就可以看到刚才所有查询的执行时间。

    当前sql语句执行情况分析如下:

 
  1. mysql> show profile; 
  2. +--------------------------------+------------+ 
  3. | Status                         | Duration   | 
  4. +--------------------------------+------------+ 
  5. | starting                       |   0.000023 | 
  6. | checking query cache for query |   0.000069 | 
  7. | Opening tables                 |   0.000016 | 
  8. | System lock                    |   0.000008 | 
  9. | Table lock                     |   0.000036 | 
  10. | init                           |   0.000030 | 
  11. | optimizing                     |   0.000011 | 
  12. | statistics                     |   0.000018 | 
  13. | preparing                      |   0.000014 | 
  14. | Creating tmp table             |   0.000265 | 
  15. | executing                      |   0.000008 | 
  16. | Copying to tmp table           | 165.312749 | 
  17. | Sorting result                 |   0.258847 | 
  18. | Sending data                   |   0.000094 | 
  19. | end                            |   0.000007 | 
  20. | removing tmp table             |   0.302258 | 
  21. | end                            |   0.000026 | 
  22. | query end                      |   0.000007 | 
  23. | freeing items                  |   0.000171 | 
  24. | storing result in query cache  |   0.000017 | 
  25. | logging slow query             |   0.000007 | 
  26. | logging slow query             |   0.000006 | 
  27. | cleaning up                    |   0.000008 | 
  28. +--------------------------------+------------+ 

    从上可以清楚的看到时间消耗基本都花费在临时文件拷贝上了,对于排序其实还没花费多久。那问题的关键就是在于解决临时文件如何在内存中建立。

    简单商讨了一下,觉得还是先建立索引看看吧。针对这个查询条件应该建立postTime和name的联合索引。但执行时发现:

 
  1. mysql> alter table entityNameTemp add key idx_postTime_name ( postTime, name ); 
  2. ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes 

    这怎么会超过长度了呢?name字段应该很短才对,postTime还是一个时间字段更长不了。但是一检查发现居然建表的人写的name是varchar(600)。突然想到mysql读取时内存开辟是根据声明的长度来的,再一联想,mysql估计需要读取文件的大小就是根据字段声明来算出来的。果断修改name到varchar(20),一执行就几秒了,再看一下详细时间消耗:

 
  1. mysql> show profile; 
  2. +--------------------------------+----------+ 
  3. | Status                         | Duration | 
  4. +--------------------------------+----------+ 
  5. | starting                       | 0.000036 | 
  6. | checking query cache for query | 0.000094 | 
  7. | Opening tables                 | 0.000216 | 
  8. | System lock                    | 0.000010 | 
  9. | Table lock                     | 0.000038 | 
  10. | init                           | 0.000038 | 
  11. | optimizing                     | 0.000014 | 
  12. | statistics                     | 0.000019 | 
  13. | preparing                      | 0.000018 | 
  14. | Creating tmp table             | 0.000040 | 
  15. | executing                      | 0.000008 | 
  16. | Copying to tmp table           | 3.863467 | 
  17. | Sorting result                 | 0.092263 | 
  18. | Sending data                   | 0.000061 | 
  19. | end                            | 0.000006 | 
  20. | removing tmp table             | 0.004514 | 
  21. | end                            | 0.000009 | 
  22. | query end                      | 0.000005 | 
  23. | freeing items                  | 0.000035 | 
  24. | storing result in query cache  | 0.000013 | 
  25. | logging slow query             | 0.000005 | 
  26. | cleaning up                    | 0.000005 | 
  27. +--------------------------------+----------+ 

    问题基本算解决了,查看临时文件使用情况也确实使用了内存临时文件。加上索引试试,查看执行计划也用上索引了,但是实际执行效果来看提升效果不大。因为还是要拷贝到临时文件表,innodb对于count操作优化确实比较难。

    另外一个问题就是对整个系统的影响,这估计是因为用到了磁盘会导致io占用过高。现在查询时间比较短,现象比较难重现了。



本文转自passover 51CTO博客,原文链接:http://blog.51cto.com/passover/632493,如需转载请自行联系原作者

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4天前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
39 9
|
1月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
|
1月前
|
SQL 关系型数据库 MySQL
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
MySQL慢查询优化、索引优化,是必知必备,大厂面试高频,本文深入详解,建议收藏。关注【mikechen的互联网架构】,10年+BAT架构经验分享。
大厂面试官:聊下 MySQL 慢查询优化、索引优化?
|
8天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
49 18
|
4天前
|
SQL 关系型数据库 MySQL
MySQL 窗口函数详解:分析性查询的强大工具
MySQL 窗口函数从 8.0 版本开始支持,提供了一种灵活的方式处理 SQL 查询中的数据。无需分组即可对行集进行分析,常用于计算排名、累计和、移动平均值等。基本语法包括 `function_name([arguments]) OVER ([PARTITION BY columns] [ORDER BY columns] [frame_clause])`,常见函数有 `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `SUM()`, `AVG()` 等。窗口框架定义了计算聚合值时应包含的行。适用于复杂数据操作和分析报告。
37 11
|
1天前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
23 8
|
7天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
17 7
|
6天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
27 5
|
7天前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
36 6
|
1月前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
下一篇
DataWorks