mysql查询慢之后

|——缘起：早上刚一到公司就反映公司网站爆卡，线上erp完全无法使用，赶紧扔掉手中包子。

———————————————————————————————————————

|——排查：开发中会经常发现开发人员查一下没用索引的语句或者没有limit n的语句，这些没语句会对数据库造成很大的影响，例如一个几千万条记录的大表要全部扫描，或者是不停的做filesort，对数据库和服务器造成io影响等。

而到了线上库，除了出现没有索引的语句，没有用limit的语句，还多了一个情况,mysql连接数过多的问题。

现在，我们找去哪些长时间无响应的sql，查出他们，优化他们。

Mysql5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。

mysql> show variables like 'long%'; 注：这个long_query_time是用来定义慢于多少秒的才算“慢查询”

|——查出来是2秒就算慢，默认是10秒。这里改的话：

mysql> set long_query_time=2; 注： 我设置了2, 也就是执行时间超过2秒的都算慢查询。

|——然后看看是否打开慢查询日志：

mysql> show variables like 'slow%'; //查看是否打开慢查询

 

mysql> show variables like 'slow%';

+---------------------+-----------------------------+

| Variable_name       | Value                       |

+---------------------+-----------------------------+

| slow_launch_time    |  2                          |

| slow_query_log      |  ON                         | //是否打开日志记录

| slow_query_log_file |  /data/mysql/mysqld-slow.log| //日志存放目录

+---------------------+-----------------------------+

3 rows in set (0.00 sec)

|————如果未打开慢查询日志：

mysql> set global slow_query_log='ON' 注：打开日志记录 一旦slow_query_log变量被设置为ON，mysql会立即开始记录。 /etc/my.cnf 里面可以设置上面MYSQL全局变量的初始值。 long_query_time=1 slow_query_log_file=/tmp/slow.log

 

|—————到这里，我们就可以根据日志里记录下来的sql语句执行的时间长短进行相应优化，加limit或者加索引。

排查的事情交给开发仔细排查，修改，优化。面对当前的mysql锁死，必须立即解决呐。

 

|—— 解除Locked

mysql> show processlist;

列出所有当前查询语句的查询状态[现在是查询正常，早上来的时候完全不是这样,一大列的locked]

 

|————批量kill 掉locked查询进程

 select concat('kill ',id,';') from information_schema.processlist where user='dev_team';

+------------------------+

| concat('kill ',id,';') |

+------------------------+

| kill 3101; //kill 掉单个查询

| kill 2946; //同上

+------------------------+

2 rows in set (0.00 sec) 

mysql> select concat('kill ',id,';') from information_schema.processlist where user='dev_team' into outfile '/tmp/a.txt';

query ok,2 rows affected (0.00 sec) 

mysql> source /tmp/a.txt; //全部kill掉

query ok, 0 rows affected (0.00 sec) 

|——最后：更改表引擎为innodB

 

说明：为啥要更改表引擎：MyISAM是表级锁，InnoDB是行级锁。道理就是你去看电影，一号厅4号座位被人座了，那5号座没人你可以去坐，这是针对行级锁，不能说一号厅整个厅你都不能去了，这是表级锁。

|——————查询 vmeipai库下所有表的引擎情况 [单个表的情况：show table status from 数据库库名 where name='表名' 或者：show create table 表名]

mysql> SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = 'vmeipai' ORDER BY table_name DESC;

 

|——————批量更改MyIsAm为InnoDB

+-------------------------------------+------------+--------+
| table_name                          | table_type | engine |
+-------------------------------------+------------+--------+
| v_timelinedata                      | BASE TABLE | MyISAM |
| tempview                            | BASE TABLE | MyISAM |
| shang_user_red                      | BASE TABLE | MyISAM |
| shang_user_priv                     | BASE TABLE | MyISAM |
| shang_user_exhibit                  | BASE TABLE | MyISAM |
| shang_user_coupon                   | BASE TABLE | MyISAM |
| shang_uploaded_file                 | BASE TABLE | InnoDB |
| shang_trade                         | BASE TABLE | MyISAM |

 

|——————更改表引擎：alter table [table_name] engine = innodb;

 

生成一张sql语句列:

 

mysql --user=root --password=passwd -e "show table status from [databaseName] where Engine <> 'InnoDB'\G"|grep Name|awk '{print "alter table "$2" engine=innodb;";}' >mysqlchange

 

 

参考：

http://www.holmesian.org/MySQL_to_InnoDB  批量修改Mysql表引擎为InnoDB的方法

http://www.cnblogs.com/lina1006/archive/2011/04/29/2032894.html 什么事mysql数据库

http://bbs.et8.net/bbs/showthread.php?t=1046281  【求助】频繁读写（select\update）的Mysql数据表引擎用InnoDB还是MyISAM

 

参考：

http://www.jb51.net/article/30121.htm  mysql服务器查询慢原因分析与解决方法小结

http://c20031776.blog.163.com/blog/static/68471625201122895832654/  mysql批量kill locked进度方法

http://zhumeng8337797.blog.163.com/blog/static/100768914201121183834733/   大量locked进程中，找出哪条SQL语句locked致使其他语句waiting

 

参考：http://www.cnblogs.com/jyginger/archive/2011/04/27/2030017.html  修改mysql用户密码