查询mysql中执行效率低的sql语句？
时间：2006-07-14 来源：softiger
===============================
===============================
用show processlist;统计经常出现的sql：
每分钟运行一次show processlist并记录日志：
* * * * * (/home/mysql/bin/mysql -uuser -ppassword < /home/chedong/show_processlist.sql >> /home/chedong/mysql_processlist.log)
show_processlist.sql里就一句：
show processlist;
比如可以从日志中将包含where的字句过滤出来：
grep where mysql_processlist.log
如果发现有死锁，一定要重新审视一下数据库设计了，对于一般情况：查询速度很慢，就将sql where字句中没有索引的字段加上索引，如果是排序慢就将order by字句中没有索引的字段加上。对于有%like%的查询，考虑以后禁用和使用全文索引加速。
=============================== MySQL 自带 slow log 的分析工具 mysqldumpslow ，但是没有说明。本文通过分析该脚本，介绍了其用法。
slow log 是 MySQL 根据 SQL 语句的执行时间设定，写入的一个文件，用于分析执行较慢的语句。
只要在 my.cnf 文件中配置好：
log-slow-queries = [slow_query_log_filename]
即可记录超过默认的 10s 执行时间的 SQL 语句。
如果要修改默认设置，可以添加：
long_query_time = 5
设定为 5s 。
如果要记录所有 SQL 语句，可以写入：
log-long-format
# t=time, l=lock time, r=rows
# at, al, 以及 ar 是对应的平均值
mysqldumpslow 可以接受的参数有：
'v+', # verbose
'd+', # debug
's=s', # 排序 (t, at, l, al, r, ar etc)
'r!', # 倒排序 (largest last instead of first)
't=i', # 显示最高的 n 个查询
'a!', # 不把所有的数字以 N ，字符串以 'S' 显示
'n=i', # abstract numbers with at least n digits within names
'g=s', # grep: only consider stmts that include this string
'h=s', # hostname of db server for *-slow.log filename (can be wildcard)
'i=s', # name of server instance (if using mysql.server startup script)
'l!', # don't subtract lock time from total time
=============================== 错误搞定了，我因为我把log-slow-queries=/var/log/slowqueries.log放错了地方，我把它放在
[mysqld]
log-bin
log-slow-queries=/var/log/slowqueries.log
就没有问题了，难道还讲究地方不成？ =============================== 我的my.cnf文件我是这样写的：
log-bin
log-slow-queries=/var/log/slowquery.log
long_query_time = 10
log-long-format
server-id = 1
点击了几下页面，有关于数据库查询的页面，日志如下：
# Time: 060404 15:35:55
# User@Host: guoxin[guoxin] @ guoxin [127.0.0.1]
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 11
use guoxin;
select * from document where type='方案';
# Time: 060404 15:38:00
# User@Host: guoxin[guoxin] @ guoxin [127.0.0.1]
# Query_time: 0 Lock_time: 0 Rows_sent: 5 Rows_examined: 7
select * from product where type='硬件';
# Time: 060404 16:20:45
# User@Host: guoxin[guoxin] @ guoxin [127.0.0.1]
# Query_time: 0 Lock_time: 0 Rows_sent: 5 Rows_examined: 7
select * from product where type='硬件';
# Time: 060404 16:20:51
# User@Host: guoxin[guoxin] @ guoxin [127.0.0.1]
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 11
select * from document where type='方案';
我的时间设置是10秒，为什么日志里纪录的查询 Query_time: 0
不象你那个日志可以真实反映查询时间，我的怎么不行？ ========================================= SHOW PROCESSLIST状态的说明
SHOW PROCESSLISTSHOW [FULL] PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads. See section 4.6.7 KILL Syntax. If you don't use the FULL option, then only the first 100 characters of each query will be shown. Starting from 4.0.12, MySQL reports the hostname for TCP/IP connections in hostname:client_port format to make it easier to find out which client is doing what. This command is very useful if you get the 'too many connections' error message and want to find out what's going on. MySQL reserves one extra connection for a client with the SUPER privilege to ensure that you should always be able to login and check the system (assuming you are not giving this privilege to all your users). Some states commonly seen in mysqladmin processlist Checking table The thread is performing [automatic] checking of the table.
Closing tables Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, then you should check that you don't have a full disk or that the disk is not in very heavy use.
Connect Out Slave connecting to master.
Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory-based temporary table to a disk based one to save memory.
Creating tmp table The thread is creating a temporary table to hold a part of the result for the query.
deleting from main table When executing the first part of a multiple-table delete and we are only deleting from the first table.
deleting from reference tables When executing the second part of a multiple-table delete and we are deleting the matched rows from the other tables.
Flushing tables The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.
Killed Someone has sent a kill to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it may still take a short time for the thread to die. If the thread is locked by some other thread, the kill will take effect as soon as the other thread releases its lock.
Sending data The thread is processing rows for a SELECT statement and is also sending data to the client.
Sorting for group The thread is doing a sort to satisfy a GROUP BY.
Sorting for order The thread is doing a sort to satisfy a ORDER BY.
Opening tables This simply means that the thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example an ALTER TABLE or a LOCK TABLE can prevent opening a table until the command is finished.
Removing duplicates The query was using SELECT DISTINCT in such a way that MySQL couldn't optimise that distinct away at an early stage. Because of this MySQL has to do an extra stage to remove all duplicated rows before sending the result to the client.
Reopen table The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table and is now trying to reopen it.
Repair by sorting The repair code is using sorting to create indexes.
Repair with keycache The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.
Searching rows for update The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.
Sleeping The thread is wating for the client to send a new command to it.
System lock The thread is waiting for getting to get a external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the --skip-external-locking option.
Upgrading lock The INSERT DELAYED handler is trying to get a lock for the table to insert rows.
Updating The thread is searching for rows to update and updating them.
User Lock The thread is waiting on a GET_LOCK().
Waiting for tables The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. To be able to reopen the table it must however wait until all other threads have closed the table in question. This notification happens if another thread has used FLUSH TABLES or one of the following commands on the table in question: FLUSH TABLES table_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE or OPTIMIZE TABLE.
waiting for handler insert The INSERT DELAYED handler has processed all inserts and are waiting to get new ones.
Most states are very quick operations. If threads last in any of these states for many seconds, there may be a problem around that needs to be investigated. There are some other states that are not mentioned previously, but most of these are only useful to find bugs in mysqld. 非常好的一篇文章，向大家推荐 ========================================
mysql查询速度慢，解决一例（Copying to tmp table）
最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;
发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。
用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill 进程号；
后面的查询一下子都好了。 ok, 找到了问题的原因，此查询效率太低。问一下程序员，找来此查询的代码，用工具进行一下简单分析。
（说明：这里是我不喜欢mysql的原因之一，mysql我不知道从哪里能看到内存正在执行哪些sql,以及完整的sql是什么。）
explain
SELECT a.* , IF(b.`gid` IS NULL , 0, SUM( b.`mark` )) AS `score` ,
IF(c.`b_times` IS NULL ,0, c.`b_times`) AS `day_b_times`
FROM `league_info` AS a LEFT JOIN `mark_logs` AS b ON b.`day_date` = '2006-03-22'
AND b.`gid` = a.`id` LEFT JOIN `visit_stat` AS c ON c.`stat_id` = a.`id` AND c.`type` = 'league'
AND c.`day`='2006_03_22' WHERE a.`validate`='1' GROUP BY a.`id`
ORDER BY day_b_times DESC, a.`id`;
+----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+
| 1 | SIMPLE | a | ref | league_info_idx4 | league_info_idx4 | 1 | const | 1441 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | ref | mark_logs_idx1,mark_logs_idx2 | mark_logs_idx1 | 4 | new5jia1.a.id | 56 | |
| 1 | SIMPLE | c | ref | visit_stat_idx1 | visit_stat_idx1 | 26 | new5jia1.a.id,const | 10 | |
+----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+ 看了一下，其实此查询嵌套用得不好，作为程序员应该尽量避免用 not in ，in, left join ,right join 等等，不过这些不归我管，我只能提一些建议。
（顺便说一声：oracle里面可以用 exist ,not exist, minus等代替in ,not in 效率高出很多 ）
此分析对我没有太大的作用，因此用google查询了一下，发现网上一篇文章讲得很好，
（ http://clay111.blogchina.com/4721079.html 我给转贴了，感兴趣可以看看）
Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the
thread is now changing the in memory-based temporary table to a disk based one to save memory.
哦，原来是这样的，如果查询超出了tmp_table_size的限制，那么mysql用/tmp保存查询结果，然后返回给客户端。
set global tmp_table_size=209715200 (200M) 再次运行此查询,用/opt/mysql/bin/mysqladmin processlist; 进行观察,发现不会出现上述问题.
至此问题解决. 调节tmp_table_size 的时候发现另外一些参数
Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目 Qcache_free_memory 可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度。
文笔不太好，希望起到一个抛砖引玉的作用。欢迎能人继续补充
每分钟运行一次show processlist并记录日志：
* * * * * (/home/mysql/bin/mysql -uuser -ppassword < /home/chedong/show_processlist.sql >> /home/chedong/mysql_processlist.log)
show_processlist.sql里就一句：
show processlist;
比如可以从日志中将包含where的字句过滤出来：
grep where mysql_processlist.log
如果发现有死锁，一定要重新审视一下数据库设计了，对于一般情况：查询速度很慢，就将sql where字句中没有索引的字段加上索引，如果是排序慢就将order by字句中没有索引的字段加上。对于有%like%的查询，考虑以后禁用和使用全文索引加速。
=============================== MySQL 自带 slow log 的分析工具 mysqldumpslow ，但是没有说明。本文通过分析该脚本，介绍了其用法。
slow log 是 MySQL 根据 SQL 语句的执行时间设定，写入的一个文件，用于分析执行较慢的语句。
只要在 my.cnf 文件中配置好：
log-slow-queries = [slow_query_log_filename]
即可记录超过默认的 10s 执行时间的 SQL 语句。
如果要修改默认设置，可以添加：
long_query_time = 5
设定为 5s 。
如果要记录所有 SQL 语句，可以写入：
log-long-format
# t=time, l=lock time, r=rows
# at, al, 以及 ar 是对应的平均值
mysqldumpslow 可以接受的参数有：
'v+', # verbose
'd+', # debug
's=s', # 排序 (t, at, l, al, r, ar etc)
'r!', # 倒排序 (largest last instead of first)
't=i', # 显示最高的 n 个查询
'a!', # 不把所有的数字以 N ，字符串以 'S' 显示
'n=i', # abstract numbers with at least n digits within names
'g=s', # grep: only consider stmts that include this string
'h=s', # hostname of db server for *-slow.log filename (can be wildcard)
'i=s', # name of server instance (if using mysql.server startup script)
'l!', # don't subtract lock time from total time
=============================== 错误搞定了，我因为我把log-slow-queries=/var/log/slowqueries.log放错了地方，我把它放在
[mysqld]
log-bin
log-slow-queries=/var/log/slowqueries.log
就没有问题了，难道还讲究地方不成？ =============================== 我的my.cnf文件我是这样写的：
log-bin
log-slow-queries=/var/log/slowquery.log
long_query_time = 10
log-long-format
server-id = 1
点击了几下页面，有关于数据库查询的页面，日志如下：
# Time: 060404 15:35:55
# User@Host: guoxin[guoxin] @ guoxin [127.0.0.1]
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 11
use guoxin;
select * from document where type='方案';
# Time: 060404 15:38:00
# User@Host: guoxin[guoxin] @ guoxin [127.0.0.1]
# Query_time: 0 Lock_time: 0 Rows_sent: 5 Rows_examined: 7
select * from product where type='硬件';
# Time: 060404 16:20:45
# User@Host: guoxin[guoxin] @ guoxin [127.0.0.1]
# Query_time: 0 Lock_time: 0 Rows_sent: 5 Rows_examined: 7
select * from product where type='硬件';
# Time: 060404 16:20:51
# User@Host: guoxin[guoxin] @ guoxin [127.0.0.1]
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 11
select * from document where type='方案';
我的时间设置是10秒，为什么日志里纪录的查询 Query_time: 0
不象你那个日志可以真实反映查询时间，我的怎么不行？ ========================================= SHOW PROCESSLIST状态的说明
SHOW PROCESSLISTSHOW [FULL] PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist command. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads. See section 4.6.7 KILL Syntax. If you don't use the FULL option, then only the first 100 characters of each query will be shown. Starting from 4.0.12, MySQL reports the hostname for TCP/IP connections in hostname:client_port format to make it easier to find out which client is doing what. This command is very useful if you get the 'too many connections' error message and want to find out what's going on. MySQL reserves one extra connection for a client with the SUPER privilege to ensure that you should always be able to login and check the system (assuming you are not giving this privilege to all your users). Some states commonly seen in mysqladmin processlist Checking table The thread is performing [automatic] checking of the table.
Closing tables Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, then you should check that you don't have a full disk or that the disk is not in very heavy use.
Connect Out Slave connecting to master.
Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory-based temporary table to a disk based one to save memory.
Creating tmp table The thread is creating a temporary table to hold a part of the result for the query.
deleting from main table When executing the first part of a multiple-table delete and we are only deleting from the first table.
deleting from reference tables When executing the second part of a multiple-table delete and we are deleting the matched rows from the other tables.
Flushing tables The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.
Killed Someone has sent a kill to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it may still take a short time for the thread to die. If the thread is locked by some other thread, the kill will take effect as soon as the other thread releases its lock.
Sending data The thread is processing rows for a SELECT statement and is also sending data to the client.
Sorting for group The thread is doing a sort to satisfy a GROUP BY.
Sorting for order The thread is doing a sort to satisfy a ORDER BY.
Opening tables This simply means that the thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example an ALTER TABLE or a LOCK TABLE can prevent opening a table until the command is finished.
Removing duplicates The query was using SELECT DISTINCT in such a way that MySQL couldn't optimise that distinct away at an early stage. Because of this MySQL has to do an extra stage to remove all duplicated rows before sending the result to the client.
Reopen table The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table and is now trying to reopen it.
Repair by sorting The repair code is using sorting to create indexes.
Repair with keycache The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.
Searching rows for update The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.
Sleeping The thread is wating for the client to send a new command to it.
System lock The thread is waiting for getting to get a external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the --skip-external-locking option.
Upgrading lock The INSERT DELAYED handler is trying to get a lock for the table to insert rows.
Updating The thread is searching for rows to update and updating them.
User Lock The thread is waiting on a GET_LOCK().
Waiting for tables The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. To be able to reopen the table it must however wait until all other threads have closed the table in question. This notification happens if another thread has used FLUSH TABLES or one of the following commands on the table in question: FLUSH TABLES table_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE or OPTIMIZE TABLE.
waiting for handler insert The INSERT DELAYED handler has processed all inserts and are waiting to get new ones.
Most states are very quick operations. If threads last in any of these states for many seconds, there may be a problem around that needs to be investigated. There are some other states that are not mentioned previously, but most of these are only useful to find bugs in mysqld. 非常好的一篇文章，向大家推荐 ========================================
mysql查询速度慢，解决一例（Copying to tmp table）
最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;
发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。
用命令杀掉此查询 /opt/mysql/bin/mysqladmin kill 进程号；
后面的查询一下子都好了。 ok, 找到了问题的原因，此查询效率太低。问一下程序员，找来此查询的代码，用工具进行一下简单分析。
（说明：这里是我不喜欢mysql的原因之一，mysql我不知道从哪里能看到内存正在执行哪些sql,以及完整的sql是什么。）
explain
SELECT a.* , IF(b.`gid` IS NULL , 0, SUM( b.`mark` )) AS `score` ,
IF(c.`b_times` IS NULL ,0, c.`b_times`) AS `day_b_times`
FROM `league_info` AS a LEFT JOIN `mark_logs` AS b ON b.`day_date` = '2006-03-22'
AND b.`gid` = a.`id` LEFT JOIN `visit_stat` AS c ON c.`stat_id` = a.`id` AND c.`type` = 'league'
AND c.`day`='2006_03_22' WHERE a.`validate`='1' GROUP BY a.`id`
ORDER BY day_b_times DESC, a.`id`;
+----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+
| 1 | SIMPLE | a | ref | league_info_idx4 | league_info_idx4 | 1 | const | 1441 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | b | ref | mark_logs_idx1,mark_logs_idx2 | mark_logs_idx1 | 4 | new5jia1.a.id | 56 | |
| 1 | SIMPLE | c | ref | visit_stat_idx1 | visit_stat_idx1 | 26 | new5jia1.a.id,const | 10 | |
+----+-------------+-------+------+-------------------------------+------------------+---------+---------------------+------+----------------------------------------------+ 看了一下，其实此查询嵌套用得不好，作为程序员应该尽量避免用 not in ，in, left join ,right join 等等，不过这些不归我管，我只能提一些建议。
（顺便说一声：oracle里面可以用 exist ,not exist, minus等代替in ,not in 效率高出很多 ）
此分析对我没有太大的作用，因此用google查询了一下，发现网上一篇文章讲得很好，
（ http://clay111.blogchina.com/4721079.html 我给转贴了，感兴趣可以看看）
Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the
thread is now changing the in memory-based temporary table to a disk based one to save memory.
哦，原来是这样的，如果查询超出了tmp_table_size的限制，那么mysql用/tmp保存查询结果，然后返回给客户端。
set global tmp_table_size=209715200 (200M) 再次运行此查询,用/opt/mysql/bin/mysqladmin processlist; 进行观察,发现不会出现上述问题.
至此问题解决. 调节tmp_table_size 的时候发现另外一些参数
Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目 Qcache_free_memory 可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度。
文笔不太好，希望起到一个抛砖引玉的作用。欢迎能人继续补充
相关阅读 更多 +
