编程开发子分类浅谈MySQL中优化sql语句查询常用的30种方法_MySQL_ThinkSAAS
浅谈MySQL中优化sql语句查询常用的30种方法
浅谈MySQL中优化sql语句查询常用的30种方法
内容来源： 网络
PHP开发框架
开发工具/编程工具
服务器环境
ThinkSAAS商业授权：
ThinkSAAS为用户提供有偿个性定制开发服务
ThinkSAAS将为商业授权用户提供二次开发指导和技术支持
让ThinkSAAS更好，把建议拿来。
开发客服微信Mysql查询语句优化一则
&& 最近一直忙于开发业务，从原来的被替换成了Mysql，但在实际线上运行中发现有条sql执行起来非常慢，更奇怪的是这句sql还会导致整个数据库性能下降。这个问题非常严重！该sql和表结构如下：
SELECT name
&&&&&& ,COUNT(*) AS counts
&&&&&& ,type
FROM& entityNameTemp
WHERE postTime & ' 00:00:00'
GROUP BY name
ORDER BY counts DESC
| entityNameTemp | CREATE TABLE `entityNameTemp` (
& `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
& `name` varchar(600) DEFAULT NULL COMMENT '人名或者机构名',
& `type` enum('personName','organizationName') DEFAULT NULL COMMENT 'personName 人名；organizationName:机构名',
& `postTime` timestamp NULL DEFAULT NULL COMMENT '发帖时间',
& `createTime` timestamp NULL DEFAULT NULL COMMENT '创建时间',
& PRIMARY KEY (`id`)
) ENGINE=Innodb AUTO_INCREMENT=1931915 DEFAULT CHARSET=utf8&&&&&&&&&&&&&&&&&&&&&&& |
&&& 这是对一个百万级别的临时表，目的是做一下统计取排名前十的数据。先来查看一下执行计划：
+----+-------------+----------------+------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table&&&&&&&&& | type | possible_keys | key& | key_len | ref& | rows&&& | Extra&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& |
+----+-------------+----------------+------+---------------+------+---------+------+---------+----------------------------------------------+
|& 1 | SIMPLE&&&&& | entityNameTemp | ALL& | NULL&&&&&&&&& | NULL | NULL&&& | NULL | 1735829 | U U Using filesort |
+----+-------------+----------------+------+---------------+------+---------+------+---------+----------------------------------------------+
&&& 无疑是用到了临时表以及排序，且没有用上索引。但的执行计划实在很难定位具体问题。之前有查过mysql对临时文件的使用规则，主体思路是查看tmp_table_size参数，mysql会评估一下本次查询大概会需要用到的内存大小，如果小于该参数则会使用磁盘临时文件。但这个参数我已经改到了200m，但问题依旧。查看了一下，发现设置了参数但仍然使用了磁盘。查询发现Created_tmp_disk_tables参数在sql语句执行前后增加了1：
mysql& show status like& '%tmp%';
+-------------------------+-------+
| Variable_name&&&&&&&&&& | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 2&&&& |
| Created_tmp_files&&&&&& | 15&&& |
| Created_tmp_tables&&&&& | 7&&&& |
+-------------------------+-------+
&&& 这就非常奇怪了，因为我手动计算发现这些数据量绝对不会超过200m，理论上是应该要用内存临时表的。这只好拿出杀手锏，查看详细的执行计划。在命令行下依次执行1)set profiling = 1; 2)sql语句；3)就可以看到详细的时间消耗，另外可以用show profiles查看执行过的sql语句。当前sql语句执行情况分析如下：
+--------------------------------+------------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration&& |
+--------------------------------+------------+
| starting&&&&&&&&&&&&&&&&&&&&&& |&& 0.000023 |
| checking query cache for query |&& 0.000069 |
| Opening tables&&&& &&&&&&&&&&&&|&& 0.000016 |
| System lock&&&&&&&&&&&&&&&&&&& |&& 0.000008 |
| Table lock&&&&&&&&&&&&&&&&&&&& |&& 0.000036 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& |&& 0.000030 |
| optimizing&&&&&&&&&&&&&&&&&&&& |&& 0.000011 |
| statistics&&&&&&&&&&&&&&&&&&& &|&& 0.000018 |
| preparing&&&&&&&&&&&&&&&&&&&&& |&& 0.000014 |
| Creating tmp table&&&&&&&&&&&& |&& 0.000265 |
| executing&&&&&&&&&&&&&&&&&&&&& |&& 0.000008 |
| Copying to tmp table&&&&&&&&&& | 165.312749 |
| Sorting result&&&&&&&&&&&&&&&& |&& 0.258847 |
| Sending data&&&&&&&&&&&&&&&&&& |&& 0.000094 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |&& 0.000007 |
| removing tmp table&&&&&&&&&&&& |&& 0.302258 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& |&& 0.000026 |
| query end&&&&&&&&&&&&&&&&&&&&& |&& 0.000007 |
| freeing items&&&&&&&&&&&&&&&&& |&& 0.000171 |
| storing result in query cache& |&& 0.000017 |
| logging slow query&&&&&&&&&&&& |&& 0.000007 |
| logging slow query&&&&&&&&&&&& |&& 0.000006 |
| cleaning up&&&&&&&&&&&&&&&&&&& |&& 0.000008 |
+--------------------------------+------------+
&&& 从上可以清楚的看到时间消耗基本都花费在临时文件拷贝上了，对于排序其实还没花费多久。那问题的关键就是在于解决临时文件如何在内存中建立。
&&& 简单商讨了一下，觉得还是先建立索引看看吧。针对这个查询条件应该建立postTime和name的联合索引。但执行时发现：
mysql& alter table entityNameTemp add key idx_postTime_name ( postTime, name );
ERROR ): Specif max key length is 1000 bytes
&&& 这怎么会超过长度了呢？name字段应该很短才对，postTime还是一个时间字段更长不了。但是一检查发现居然建表的人写的name是varchar(600)。突然想到mysql读取时内存开辟是根据声明的长度来的，再一联想，mysql估计需要读取文件的大小就是根据字段声明来算出来的。果断修改name到varchar(20)，一执行就几秒了，再看一下详细时间消耗：
+--------------------------------+----------+
| Status&&&&&&&&&&&&&&&&&&&&&&&& | Duration |
+--------------------------------+----------+
| starting&&&&&&&&&&&&&&&&&&&&&& | 0.000036 |
| checking query cache for query | 0.000094 |
| Opening tables&&&&&&&&&&&&&&&& | 0.000216 |
| System lock&&&&&&&&& &&&&&&&&&&| 0.000010 |
| Table lock&&&&&&&&&&&&&&&&&&&& | 0.000038 |
| init&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000038 |
| optimizing&&&&&&&&&&&&&&&&&&&& | 0.000014 |
| statistics&&&&&&&&&&&&&&&&&&&& | 0.000019 |
| preparing&&&&&&&&&&&&&&&&&&&&& | 0.000018 |
| Creating tmp table&&&&&&&&&&&& | 0.000040 |
| executing&&&&&&&&&&&&&&&&&&&&& | 0.000008 |
| Copying to tmp table&&&&&&&&&& | 3.863467 |
| Sorting result&&&&&&&&&&&&&&&& | 0.092263 |
| Sending data&&&&&&&&&&&&&&&&&& | 0.000061 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000006 |
| removing tmp table&&&&&&&&&&&& | 0.004514 |
| end&&&&&&&&&&&&&&&&&&&&&&&&&&& | 0.000009 |
| query end&&&&&&&&&&&&&&&&&&&&& | 0.000005 |
| freeing items&&&&&&&&&&&&&&&&& | 0.000035 |
| storing result in query cache& | 0.000013 |
| logging slow query&&&&&&&&&&&& | 0.000005 |
| cleaning up&&&&&&&&&&&&&&&&&&& | 0.000005 |
+--------------------------------+----------+
&&& 问题基本算解决了，查看临时文件使用情况也确实使用了内存临时文件。加上索引试试，查看执行计划也用上索引了，但是实际执行效果来看提升效果不大。因为还是要拷贝到临时文件表，innodb对于count操作优化确实比较难。
&&& 另外一个问题就是对整个系统的影响，这估计是因为用到了磁盘会导致io占用过高。现在查询时间比较短，现象比较难重现了。mysql数据库能查询数据但是写不进去 语句没问题 root权限_mysql吧_百度贴吧
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&签到排名：今日本吧第个签到，本吧因你更精彩，明天继续来努力！
本吧签到人数：0成为超级会员，使用一键签到本月漏签0次！成为超级会员，赠送8张补签卡连续签到：天&&累计签到：天超级会员单次开通12个月以上，赠送连续签到卡3张
关注：36,294贴子：
mysql数据库能查询数据但是写不进去 语句没问题 root权限收藏
登录百度帐号推荐应用
为兴趣而生，贴吧更懂你。或