博主最新文章
博主热门文章
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)新手园地& & & 硬件问题Linux系统管理Linux网络问题Linux环境编程Linux桌面系统国产LinuxBSD& & & BSD文档中心AIX& & & 新手入门& & & AIX文档中心& & & 资源下载& & & Power高级应用& & & IBM存储AS400Solaris& & & Solaris文档中心HP-UX& & & HP文档中心SCO UNIX& & & SCO文档中心互操作专区IRIXTru64 UNIXMac OS X门户网站运维集群和高可用服务器应用监控和防护虚拟化技术架构设计行业应用和管理服务器及硬件技术& & & 服务器资源下载云计算& & & 云计算文档中心& & & 云计算业界& & & 云计算资源下载存储备份& & & 存储文档中心& & & 存储业界& & & 存储资源下载& & & Symantec技术交流区安全技术网络技术& & & 网络技术文档中心C/C++& & & GUI编程& & & Functional编程内核源码& & & 内核问题移动开发& & & 移动开发技术资料ShellPerlJava& & & Java文档中心PHP& & & php文档中心Python& & & Python文档中心RubyCPU与编译器嵌入式开发驱动开发Web开发VoIP开发技术MySQL& & & MySQL文档中心SybaseOraclePostgreSQLDB2Informix数据仓库与数据挖掘NoSQL技术IT业界新闻与评论IT职业生涯& & & 猎头招聘IT图书与评论& & & CU技术图书大系& & & Linux书友会二手交易下载共享Linux文档专区IT培训与认证& & & 培训交流& & & 认证培训清茶斋投资理财运动地带快乐数码摄影& & & 摄影器材& & & 摄影比赛专区IT爱车族旅游天下站务交流版主会议室博客SNS站务交流区CU活动专区& & & Power活动专区& & & 拍卖交流区频道交流区
白手起家, 积分 4, 距离下一级还需 196 积分
论坛徽章:0
因为按我理解 CPU速度远远超过磁盘速度. 也就是说查询以及处理一条数据的操作上CPU是在等磁盘的.
可是,在一些情况下出现cpu彪高为什么呢? 难道不应该是磁盘满负载(任务队列满了, 数据通道并未满) CPU大量闲置吗?
无论是有索引和无索引的情况 按表面理解都应该发生我上面的现象才对.
但实际压力测试中我发现大量的select + 索引建得不好就会引起CPU彪高, 8核能够彪到600%左右.
当然我不是在问如何建立索引和如何优化. 而是想知道为什么会导致与我直观认知不一样的现象
我的测试环境没有做raid的双磁盘MYSQL数据文件在其中一个磁盘上. 8核 8G内存, innodb数据库
白手起家, 积分 148, 距离下一级还需 52 积分
论坛徽章:7
呃 不建立索引就是取出表中所有数据进行匹配 就想 A 表中 有 1000W跳数据。如果查询不为索引的 column 就是 CPU 就要计算 1000W 还不算 偏移读取 和 读取次数，和记录匹配。(以上是我猜的)
白手起家, 积分 4, 距离下一级还需 196 积分
论坛徽章:0
呃 不建立索引就是取出表中所有数据进行匹配 就想 A 表中 有 1000W跳数据。如果查询不为索引的 column 就是 ...
感谢帮忙思考.
但是如果查询数据要直接读表,也就是直接读磁盘的话 应该是磁盘负载远大于CPU负载的啊. 会发生磁盘队列严重堵塞而CPU大量闲置并且linux 的load average高涨.
可是, 实际上 mysql的查询能够让cpu狂飙到一个很高的地步.&&mysql是怎么办到的 我疑惑的是这个.
如果说全走内存缓存查询的话倒还能理解.
但是mysql显然不能把所有数据都缓存起来(至少我是这么认为的)
白手起家, 积分 148, 距离下一级还需 52 积分
论坛徽章:7
试试这个。
白手起家, 积分 148, 距离下一级还需 52 积分
论坛徽章:7
链接回复审核中 可以先 搜索 一下 mysql profiler。SQL 语句 分析工具。
论坛徽章:203
磁盘负载远大于CPU负载
cpu和磁盘可以直接比较吗？ 一个是计算单元，一个是存储单元
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备号 北京市公安局海淀分局网监中心备案编号：22
广播电视节目制作经营许可证(京) 字第1234号
中国互联网协会会员&&联系我们：
感谢所有关心和支持过ChinaUnix的朋友们
转载本站内容请注明原作者名及出处数据库内核月报 － 2015 / 10-MySQL · 答疑解惑 · 索引过滤性太差引起CPU飙高分析
数据库内核月报 － 2015 / 10-MySQL · 答疑解惑 · 索引过滤性太差引起CPU飙高分析
在操作数据库系统的时候，有个常识就是在建表的时候一定要建索引。为什么要建索引呢？
这里以MySQL的InnoDB存储引擎为例，因为InnoDB会以索引的排序为基准建立B+树，这样在检索数据的时候就可以通过B+树来查找，查找算法的时间复杂度是O(logn)级别的，避免全表扫描带来的性能下降和额外资源损耗。
在操作数据库系统的时候，有个常识就是在建表的时候一定要建索引。为什么要建索引呢？
这里以MySQL的InnoDB存储引擎为例，因为InnoDB会以索引的排序为基准建立B+树，这样在检索数据的时候就可以通过B+树来查找，查找算法的时间复杂度是O(logn)级别的，避免全表扫描带来的性能下降和额外资源损耗。
理论上一个表所有的字段都可以建索引，那么给哪些字段建索引效果好呢？
一个想法是给频繁在SQL的where条件中出现的字段建立索引，这样可以保证通过索引来查找数据。
有一点是经常被忽略的，那就是索引的过滤性。比如我们给一个整型字段加索引，而这个字段在几乎所有的记录上的值都是1（过滤性很差），那么我们通过这个索引来查找数据就会遍历大部分无关记录，造成浪费。
我们知道update语句也是通过索引来查找待更新的数据的，而且update会给索引查找的记录加上X锁，因此索引过滤性不好不但造成性能下降，还有可能造成锁争夺和锁等待的损耗。
下面给出一个具体的因为索引过滤性太差引起CPU飙高的case，在RDS的线上实例曾出现过类似的case。
在MySQL里我们建立这样一个表：
CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`n` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB;
然后我们给sbtest1加点数据，并且让索引k_1(k)的过滤性不好，表内一共条数据，索引k只有2个值50,51，如下所示：
mysql& select count(*) from sbtest1;
| count(*) |
1 row in set (1.80 sec)
mysql& select distinct k from sbtest1;
2 rows in set (2.22 sec)
然后我们用sysbench开32个并发的update，update语句如下：
UPDATE sbtest1 SET c='随机字符串' WHERE k=50或51 and n=随机值
执行show full processlist\G，可以看到这些update的状态大多处于”Searching rows for update”的状态。
mysql& show full processlist\G
*************************** 1. row ***************************
User: root
Command: Sleep
Info: NULL
Memory_used: 1146520
Memory_used_by_query: 8208
Logical_read: 53
Physical_sync_read: 2
Physical_async_read: 0
Temp_user_table_size: 0
Temp_sort_table_size: 0
Temp_sort_file_size: 0
*************************** 2. row ***************************
User: root
db: sbtest
Command: Query
State: Searching rows for update
Info: UPDATE sbtest1 SET c='---------' WHERE k=50 and n=4951641
Memory_used: 119840
Memory_used_by_query: 232
Logical_read: 4935
Physical_sync_read: 0
Physical_async_read: 0
Temp_user_table_size: 0
Temp_sort_table_size: 0
Temp_sort_file_size: 0
*************************** 3. row ***************************
User: root
db: sbtest
Command: Query
State: Searching rows for update
Info: UPDATE sbtest1 SET c='---------' WHERE k=51 and n=5033717
Memory_used: 119840
Memory_used_by_query: 232
Logical_read: 4949
Physical_sync_read: 5
Physical_async_read: 0
Temp_user_table_size: 0
Temp_sort_table_size: 0
Temp_sort_file_size: 0
“Searching rows for update”即MySQL正在寻找待更新的记录的状态，正常情况这个状态是非常快就结束的，但是这里却长时间处于这个状态，为什么呢？
由于表的索引过滤性太差，每个线程在查找的时候会遇到很多冲突的记录。
InnoDB在通过索引拿到记录后，会给这些记录上X锁，同时也会请求全局的lock_sys-&mutex和trx_sys-&mutex，所以这里我们判断每个线程都堵在锁等待这里。（ps: 关于InnoDB加锁的逻辑，可以查看）
这时候对系统用一下top命令，可以发现这个MySQL实例CPU飚的很高，我们再用perf工具看一下CPU飙高的MySQL调用堆栈是怎么样的，如下所示：
[.] _Z8ut_delaym
--- _Z8ut_delaym
|--99.99%-- _Z15mutex_spin_waitP10ib_mutex_tPKcm
|--88.88%-- _ZL20pfs_mutex_enter_funcP10ib_mutex_tPKcm.constprop.68
|--54.05%-- _ZL29lock_rec_convert_impl_to_explPK11buf_block_tPKhP12dict_index_tPKm
_Z34lock_clust_rec_read_check_and_lockmPK11buf_block_tPKhP12dict_index_tPKm9lock_modemP9que_thr_t
_ZL16sel_set_rec_lockPK11buf_block_tPKhP12dict_index_tPKmmmP9que_thr_t
_Z20row_search_for_mysqlPhmP14row_prebuilt_tmm
_ZN11ha_innobase10index_nextEPh
_ZN7handler13ha_index_nextEPh
_ZL8rr_indexP11READ_RECORD
_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_ordery15enum_duplicatesbPySB_
_Z21mysql_execute_commandP3THD
_Z11mysql_parseP3THDPcjP12Parser_state
_Z16dispatch_command19enum_server_commandP3THDPcj
_Z26threadpool_process_requestP3THD
_ZL11worker_mainPv
start_thread
--45.95%-- _Z15lock_rec_unlockP5trx_tPK11buf_block_tPKh9lock_mode
_Z20row_unlock_for_mysqlP14row_prebuilt_tm
_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_ordery15enum_duplicatesbPySB_
_Z21mysql_execute_commandP3THD
_Z11mysql_parseP3THDPcjP12Parser_state
_Z16dispatch_command19enum_server_commandP3THDPcj
_Z26threadpool_process_requestP3THD
_ZL11worker_mainPv
start_thread
我们看到耗CPU最高的调用函数栈是…mutex_spin_wait-&ut_delay，属于锁等待的逻辑。InnoDB在这里用的是自旋锁，锁等待是通过调用ut_delay做空循环实现的，会消耗CPU。这里证明了上面的判断是对的。
在这个case里涉及到的锁有记录锁、lock_sys-&mutex和trx_sys-&mutex，究竟是哪个锁等待时间最长呢？我们可以用下面的方法确认一下：
mysql& SELECT COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, EVENT_NAME FROM performance_schema.events_waits_summary_global_by_event_name where COUNT_STAR & 0 and EVENT_NAME like 'wait/synch/%' order by SUM_TIMER_WAIT desc limit 10;
| COUNT_STAR | SUM_TIMER_WAIT
| AVG_TIMER_WAIT | EVENT_NAME
| wait/synch/mutex/innodb/lock_mutex
| wait/synch/cond/threadpool/timer_cond
| wait/synch/cond/threadpool/worker_cond
156446 | wait/synch/mutex/innodb/trx_sys_mutex
35397 | wait/synch/mutex/innodb/trx_mutex
31027 | wait/synch/mutex/innodb/os_mutex
25346 | wait/synch/mutex/innodb/mutex_list_mutex
24909 | wait/synch/mutex/innodb/rw_lock_list_mutex |
163001 | wait/synch/rwlock/innodb/hash_table_locks
9853039 | wait/synch/mutex/innodb/dict_sys_mutex
10 rows in set (0.01 sec)
从上面的表可以确认，lock_mutex（在MySQL源码里对应的是lock_sys-&mutex）的锁等待累积时间最长（SUM_TIMER_WAIT）。lock_sys表示全局的InnoDB锁系统，在源码里看到InnoDB加/解某个记录锁的时候（这个case里是X锁），同时需要维护lock_sys，这时会请求lock_sys-&mutex。
在这个case里，因为在Searching rows for update的阶段频繁地加/解X锁，就会频繁请求lock_sys-&mutex，导致lock_sys-&mutex锁总等待时间过长，同时在等待的时候消耗了大量CPU。
当我们将索引改成过滤性好的（比如字段n），再做上述实验，就看不到那么多线程堵在”Searching rows for update”的阶段，而且实例的CPU消耗也降了很多。
通过以上实验，我们看到索引过滤性不好可能带来灾难性的结果：语句hang住以及主机CPU耗尽。因此我们在设计表的时候，应该对业务上的数据有充分的估计，选择过滤性好的字段作为索引。
用云栖社区APP，舒服~
【云栖快讯】新手小白必看！编程语言系列讲座火爆进行中，与行业资深专家一起学习Python、C++、JavaScript、Java！从入门到进阶&&
基于云安全大数据能力实现，通过防御SQL注入、XSS跨站脚本、常见Web服务器插件漏洞、木马...
支持以数据库为核心的结构化存储产品之间的数据传输。 它是一种集数据迁移、数据订阅及数据实时同...
MySQL 是全球最受欢迎的开源数据库，阿里云MySQL版 通过深度的内核优化和独享实例提供...
为您提供简单高效、处理能力可弹性伸缩的计算服务，帮助您快速构建更稳定、安全的应用，提升运维效...
订阅广场全新上线13596人阅读
Mysql占用CPU过高如何优化
一次生产DB服务器的 超负荷运行问题解决：
1.查看生产DB服务器top列表，
执行 top 命令
查看Cpu(s) 参数一直处于 98% 状态 ，load average达到了 5
(4核服务器)
可见DB已经超负荷运行了
2.使用root用户登录mysql
执行 show full processlist
查看慢查询，反复执行，发现一直有3个select 查询语句存在，为了缓解DB服务器压力，直接使用kill命令杀掉
慢查询的id
服务器压力缓解，恢复正常。
使用这条SQL语句去项目中找到对应功能，经查是没用的功能，直接隐藏这个功能，问题解决。
Mysql占用CPU过高如何优化？
MySQL处在高负载环境下，磁盘IO读写过多，肯定会占用很多资源，必然CP会U占用过高。
占用CPU过高，可以做如下考虑：
1.打开慢查询日志，查询是否是某个SQL语句占用过多资源，如果是的话，可以对SQL语句进行优化，比如优化 insert 语句、优化 group by 语句、优化 order by 语句、优化 join 语句等等；
2.考虑索引问题；
3.定期分析表，使用optimize table；
4.优化数据库对象；
5.考虑是否是锁问题；
6.调整一些MySQL Server参数，比如key_buffer_size、table_cache、innodb_buffer_pool_size、innodb_log_file_size等等；
7.如果数据量过大，可以考虑使用MySQL集群或者搭建高可用环境。
mysql show processlist + show status + kill Id命令详解
1. SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL 账户相关的线程）。请参见13.5.5.3节，“KILL语法”。如果您不使用FULL关键词，则只显示每个查询的前100个字符。本语句报告TCP/IP连接的主机名称（采用host_name:client_port格式），以方便地判定哪个客户端正在做什么。如果您得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。MySQL保留一个额外的连接，让拥有SUPER权限的 账户使用，以确保管理员能够随时连接和检查系统（假设您没有把此权限给予所有的用户）。这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：Checking table　正在检查数据表（这是自动的）。Closing tables　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。Connect Out　复制从服务器正在连接主服务器。Copying to tmp table on disk　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。Creating tmp table　正在创建临时表以存放部分查询结果。deleting from main table　服务器正在执行多表删除中的第一部分，刚删除第一个表。deleting from reference tables　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。Flushing tables　正在执行FLUSH TABLES，等待其他线程关闭数据表。Killed　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查 kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。Locked　被其他查询锁住了。Sending data　正在处理SELECT查询的记录，同时正在把结果发送给客户端。Sorting for group　正在为GROUP BY做排序。　Sorting for order　正在为ORDER BY做排序。Opening tables　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。Removing duplicates　正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。Reopen table　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。Repair by sorting　修复指令正在排序以创建索引。Repair with keycache　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。Searching rows for update　正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。Sleeping　正在等待客户端发送新请求.System lock　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。Upgrading lock　INSERT DELAYED正在尝试取得一个锁表以插入新记录。Updating　正在搜索匹配的记录，并且修改它们。User Lock　正在等待GET_LOCK()。Waiting for tables　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个 表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。waiting for handler insert　INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。　还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。mysql 查看当前连接数命令：如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。只列出前100条，如果想全列出请使用smysql&2. show statusAborted_clients 由于客户没有正确关闭连接已经死掉，已经放弃的连接数量。Aborted_connects 尝试已经失败的MySQL服务器的连接的次数。Connections 试图连接MySQL服务器的次数。Created_tmp_tables 当执行语句时，已经被创造了的隐含临时表的数量。Delayed_insert_threads 正在使用的延迟插入处理器线程的数量。Delayed_writes 用INSERT DELAYED写入的行数。Delayed_errors 用INSERT DELAYED写入的发生某些错误(可能重复键值)的行数。Flush_commands 执行FLUSH命令的次数。Handler_delete 请求从一张表中删除行的次数。Handler_read_first 请求读入表中第一行的次数。Handler_read_key 请求数字基于键读行。Handler_read_next 请求读入基于一个键的一行的次数。Handler_read_rnd 请求读入基于一个固定位置的一行的次数。Handler_update 请求更新表中一行的次数。Handler_write 请求向表中插入一行的次数。Key_blocks_used 用于关键字缓存的块的数量。Key_read_requests 请求从缓存读入一个键值的次数。Key_reads 从磁盘物理读入一个键值的次数。Key_write_requests 请求将一个关键字块写入缓存次数。Key_writes 将一个键值块物理写入磁盘的次数。Max_used_connections 同时使用的连接的最大数目。Not_flushed_key_blocks 在键缓存中已经改变但是还没被清空到磁盘上的键块。Not_flushed_delayed_rows 在INSERT DELAY队列中等待写入的行的数量。Open_tables 打开表的数量。Open_files 打开文件的数量。Open_streams 打开流的数量(主要用于日志记载）Opened_tables 已经打开的表的数量。Questions 发往服务器的查询的数量。Slow_queries 要花超过long_query_time时间的查询数量。Threads_connected 当前打开的连接的数量。Threads_running 不在睡眠的线程数量。Uptime 服务器工作了多少秒。After createThis occurs when the thread creates a table (including internal temporary tables), at the end of the function that creates the table. This state is used even if the table could not be created due to some error.AnalyzingThe thread is calculating a MyISAM table key distributions (for example, for ANALYZE TABLE).checking permissionsThe thread is checking whether the server has the required privileges to execute the statement.Checking tableThe thread is performing a table check operation.cleaning upThe thread has processed one command and is preparing to free memory and reset certain state variables.closing tablesThe thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.converting HEAP to MyISAMThe thread is converting an internal temporary table from a MEMORY table to an on-disk MyISAM table.copy to tmp tableThe thread is processing an ALTER TABLE statement. This state occurs after the table with the new structure has been created but before rows are copied into it.Copying to group tableIf a statement has different ORDER BY and GROUP BY criteria, the rows are sorted by group and copied to a temporary table.Copying to tmp tableThe server is copying to a temporary table in memory.Copying to tmp table on diskThe server is copying to a temporary table on disk. The temporary result set was larger than tmp_table_size and the thread is changing the temporary table from in-memory to disk-based format to save memory.Creating indexThe thread is processing ALTER TABLE ... ENABLE KEYS for a MyISAM table.Creating sort indexThe thread is processing a SELECT that is resolved using an internal temporary table.creating tableThe thread is creating a table. This includes creation of temporary tables.Creating tmp tableThe thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the state during that operation will be Copying to tmp table on disk.deleting from main tableThe server is executing the first part of a multiple-table delete. It is deleting only from the first table, and saving columns and offsets to be used for deleting from the other (reference) tables.deleting from reference tablesThe server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.discard_or_import_tablespaceThe thread is processing an ALTER TABLE ... DISCARD TABLESPACE or ALTER TABLE ... IMPORT TABLESPACE statement.endThis occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements.executingThe thread has begun executing a statement.Execution of init_commandThe thread is executing statements in the value of the init_command system variable.freeing itemsThe thread has executed a command. This state is usually followed by cleaning up.Flushing tablesThe thread is executing FLUSH TABLES and is waiting for all threads to close their tables.FULLTEXT initializationThe server is preparing to perform a natural-language full-text search.initThis occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements.KilledSomeone has sent a KILL statement 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 might still take a short time for the thread to die. If the thread is
locked by some other thread, the kill takes effect as soon as the other thread releases its lock.LockedThe query is locked by another query.logging slow queryThe thread is writing a statement to the slow-query log.NULLThis state is used for the SHOW PROCESSLIST state.loginThe initial state for a connection thread until the client has been authenticated successfully.Opening tables, Opening tableThe 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 statement can prevent opening a table until the statement is finished.preparingThis state occurs during query optimization.Purging old relay logsThe thread is removing unneeded relay log files.query endThis state occurs after processing a query but before the freeing items state.Reading from netThe server is reading a packet from the network.Removing duplicatesThe query was using SELECT DISTINCT in such a way that MySQL could not optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.removing tmp tableThe thread is removing an internal temporary table after processing a SELECT statement. This state is not used if no temporary table was created.renameThe thread is renaming a table.rename result tableThe thread is processing an ALTER TABLE statement, has created the new table, and is renaming it to replace the original table.Reopen tablesThe 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 trying to reopen it.Repair by sortingThe repair code is using a sort to create indexes.Repair doneThe thread has completed a multi-threaded repair for a MyISAM table.Repair with keycacheThe repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.Rolling backThe thread is rolling back a transaction.Saving stateFor MyISAM table operations such as repair or analysis, the thread is saving the new table state to the .MYI file header. State includes information such as number of rows, the AUTO_INCREMENT counter, and key distributions.Searching rows for updateThe 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.Sending dataThe thread is processing rows for a SELECT statement and also is sending data to the client.setupThe thread is beginning an ALTER TABLE operation.Sorting for groupThe thread is doing a sort to satisfy a GROUP BY.Sorting for orderThe thread is doing a sort to satisfy a ORDER BY.Sorting indexThe thread is sorting index pages for more efficient access during a MyISAM table optimization operation.Sorting resultFor a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.statisticsThe server is calculating statistics to develop a query execution plan.System lockThe thread is going to request or is waiting for an internal or external system lock for the table. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same tables,
you can disable external system locks with the --skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock (not
waiting for it).Table lockThe next thread state after System lock. The thread has acquired an external lock and is going to request an internal table lock.UpdatingThe thread is searching for rows to update and is updating them.updating main tableThe server is executing the first part of a multiple-table update. It is updating only the first table, and saving columns and offsets to be used for updating the other (reference) tables.updating reference tablesThe server is executing the second part of a multiple-table update and updating the matched rows from the other tables.User lockThe thread is going to request or is waiting for an advisory lock requested with a GET_LOCK() call. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it).Waiting for tables, Waiting for tableThe 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. However, to reopen the table, it must wait until all other threads have closed the table in question.This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.Waiting on condA generic state in which the thread is waiting for a condition to become true. No specific state information is available.Writing to netThe server is writing a packet to the network.3. kill mysql 执行线程(1)
-----------+
| 152 | root | 192.168.0.100:1808 | atomic_data | Query
| 5600 | copy to tmp table | alter table `atomic_data`.`basic_point_beijing_p1`
change `valid` `type` int(2) NULL ,
| 155 | root | 192.168.0.100:1944 | atomic_data | Query
| 4702 | Locked
| alter table `atomic_data`.`basic_point_beijing_p1`
change `valid` `type` int(2) NULL ,
| 158 | root | localhost
| atomic_data | Query
show processlist
(2) kill 152;
Query OK, 0 rows affected (0.00 sec)
+-----+------+--------------------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+| Id
| User | Host
Command | Time | State
|+-----+------+--------------------+-------------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+| 155 | root | 192.168.0.100:1944 | atomic_data | Query
| 4840 | copy to tmp table | alter table `atomic_data`.`basic_point_beijing_p1`
change `valid` `type` int(2) NULL ,
chang || 158 | root | localhost
atomic_data | Query
show processlist
以下内容来自mysql手册：
13.5.5.3. KILL语法
KILL [CONNECTION | QUERY] thread_id
每个与mysqld的连接都在一个独立的线程里运行，您可以使用SHOW PROCESSLIST语句查看哪些线程正在运行，并使用KILL thread_id语句终止一个线程。
KILL允许自选的CONNECTION或QUERY修改符：
· KILL CONNECTION与不含修改符的KILL一样：它会终止与给定的thread_id有关的连接。
· KILL QUERY会终止连接当前正在执行的语句，但是会保持连接的原状。
如果您拥有PROCESS权限，则您可以查看所有线程。如果您拥有SUPER权限，您可以终止所有线程和语句。否则，您只能查看和终止您自己的线程和语句。
您也可以使用mysqladmin processlist和mysqladmin kill命令来检查和终止线程。
注释：您不能同时使用KILL和Embedded MySQL Server库，因为内植的服务器只运行主机应用程序的线程。它不能创建任何自身的连接线程。
当您进行一个KILL时，对线程设置一个特有的终止标记。在多数情况下，线程终止可能要花一些时间，这是因为终止标记只会在在特定的间隔被检查：
· 在SELECT, ORDER BY和GROUP BY循环中，在读取一组行后检查标记。如果设置了终止标记，则该语句被放弃。
· 在ALTER TABLE过程中，在每组行从原来的表中被读取前，检查终止标记。如果设置了终止标记，则语句被放弃，临时表被删除。
· 在UPDATE或DELETE运行期间，在每个组读取之后以及每个已更行或已删除的行之后，检查终止标记。如果终止标记被设置，则该语句被放弃。注意，如果您正在使用事务，则变更不会被 回滚。
· GET_LOCK()会放弃和返回NULL。
· INSERT DELAYED线程会快速地刷新（插入）它在存储器中的所有的行，然后终止。
· 如果线程在表锁定管理程序中（状态：锁定），则表锁定被快速地放弃。
· 如果在写入调用中，线程正在等待空闲的磁盘空间，则写入被放弃，并伴随"disk full"错误消息。
· 警告：对MyISAM表终止一个REPAIR TABLE或OPTIMIZE TABLE操作会导致出现一个被损坏的没有用的表。对这样的表的任何读取或写入都会失败，直到您再次优化或修复它（不中断）。
1、通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令
mysql& select concat('KILL ',id,';') from information_schema.processlist where user='root';
+------------------------+
| concat('KILL ',id,';')
+------------------------+
| KILL 3101;
| KILL 2946;
+------------------------+
2 rows in set (0.00 sec)
mysql&select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';
Query OK, 2 rows affected (0.00 sec)
mysql&source /tmp/a.
Query OK, 0 rows affected (0.00 sec)
　　杀掉当前所有的MySQL连接
mysqladmin -uroot -p processlist|awk -F "|" '{print $2}'|xargs -n 1 mysqladmin -uroot -p kill
　　杀掉指定用户运行的连接，这里为Mike
mysqladmin -uroot -p processlist|awk -F "|" '{if($3 == "Mike")print $2}'|xargs -n 1 mysqladmin -uroot -p kill
3、通过SHEL脚本实现
#杀掉锁定的MySQL连接
for id in `mysqladmin processlist|grep -i locked|awk '{print $1}'`
mysqladmin kill ${id}
4、通过Maatkit工具集中提供的mk-kill命令进行
#杀掉超过60秒的sql
mk-kill -busy-time 60 -kill
#如果你想先不杀，先看看有哪些sql运行超过60秒
mk-kill -busy-time 60 -print
#如果你想杀掉，同时输出杀掉了哪些进程
mk-kill -busy-time 60 -print –kill
　　mk-kill更多用法可参考：
　　http://www.maatkit.org/doc/mk-kill.html
　　http://www.sbear.cn/archives/426
　　Maatkit工具集的其它用法可参考：
　　http://code.google.com/p/maatkit/wiki/TableOfContents?tm=6
　　参考文档：
　　http://www.google.com
　　http://www.orczhou.com/index.php/2010/10/kill-mysql-connectio-in-batch/
　　http://www.mysqlperformanceblog.com//mass-killing-of-mysql-connections/