SQL语句导致性能问题
前阵子，突然收到服务器的报警信息，于是上服务器找问题，我擦，top看到mysql占的%cpu高得把我吓尿了
从以上的信息看，相信大家已经可以定位到底是那个程序导致服务器CPU负载过高了，但我们要做的是，找出mysql进程下，是什么动作导致服务器出现性能问题
以下做个实验，相信大家看了后也能猜到当时是什么导致高负载的，废话不多说：
表结构如下：
mysql> desc test1;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| role_id | int(11) | NO | | 0 | |
| referer | varchar(20) | NO | | | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> desc test2;
+--------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| role_id | int(11) | NO | MUL | 0 | |
| privilege_id | int(11) | NO | | 0 | |
+--------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
表的索引情况如下：
mysql> show index from test1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test1 | 0 | PRIMARY | 1 | id | A | 329 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
mysql> show index from test2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| test2 | 0 | PRIMARY | 1 | id | A | 12476 | NULL | NULL | | BTREE | |
| test2 | 1 | role_id | 1 | role_id | A | 415 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
当时执行show full processlist后，发现有好几百个连接在执行同一条SQL语句，看见SQL也还好，不复杂，是子查询
mysql> select privilege_id from t2 where role_id in (select role_id from t1 where id=193);
看着以上的SQL语句，写没什么问题啊，但用explain分析一看，我擦
mysql> explain select privilege_id from test2 where role_id in (select role_id from test1 where id=192); +----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+
| 1 | PRIMARY | test2 | ALL | NULL | NULL | NULL | NULL | 12476 | Using where | | 2 | DEPENDENT SUBQUERY | test1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ 2 rows in set (0.00 sec)
当时MySQL版本是：
mysql> select version(); +-----------+ | version() | +-----------+ | 5.1.66 | +-----------+ 1 row in set (0.00 sec)
但把SQL语句的子查询修改为以下的写法，执行效率就就像喝了可乐一样爽^0^：
select a.privilege_id from test2 as a inner join test1 as b on a.role_id = b.role_id and b.id=192;
看效果对比：
mysql> select version(); +-----------+ | version() | +-----------+ | 5.1.66 | +-----------+ 1 row in set (0.00 sec) mysql> explain select privilege_id from test2 where role_id in (select role_id from test1 where id=192); +----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ | 1 | PRIMARY | test2 | ALL | NULL | NULL | NULL | NULL | 12476 | Using where | | 2 | DEPENDENT SUBQUERY | test1 | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ 2 rows in set (0.00 sec) mysql> explain select a.privilege_id from test2 as a inner join test1 as b on a.role_id = b.role_id and b.id=192; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | a | ref | role_id | role_id | 4 | const | 32 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 2 rows in set (0.00 sec)
相信大家也能看到修改后跟修改前的差别了吧！
以下用版本为5.5的版本测试下：
mysql> select version(); +------------+ | version() | +------------+ | 5.5.30-log | +------------+ 1 row in set (0.00 sec) mysql> explain select privilege_id from test2 where role_id in (select role_id from test1 where id=192); +----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ | 1 | PRIMARY | test2 | ALL | NULL | NULL | NULL | NULL | 12195 | Using where | | 2 | DEPENDENT SUBQUERY | test1 | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+--------------------+-------+-------+---------------+---------+---------+-------+-------+-------------+ 2 rows in set (0.03 sec) mysql> explain select a.privilege_id from test2 as a inner join test1 as b on a.role_id = b.role_id and b.id=192; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | a | ref | role_id | role_id | 4 | const | 32 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 2 rows in set (0.01 sec)
MySQL5.5的版本和5.1的情况一样，如果用类似的子查询，可能会存在性能问题
以下用版本为5.6的版本测试下：
mysql> select version(); +------------+ | version() | +------------+ | 5.6.10-log | +------------+ 1 row in set (0.00 sec) mysql> explain select privilege_id from test2 where role_id in (select role_id from test1 where id=192); +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | test1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | | 1 | SIMPLE | test2 | ref | role_id | role_id | 4 | const | 32 | NULL | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 2 rows in set (0.07 sec) mysql> explain select a.privilege_id from test2 as a inner join test1 as b on a.role_id = b.role_id and b.id=192; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL | | 1 | SIMPLE | a | ref | role_id | role_id | 4 | const | 32 | NULL | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 2 rows in set (0.04 sec)
看到的两种查询结果是一样高效的，从以上的案例可以看出，写通用且性能高的SQL相当重要，希望大家以后不要踩类似的坑@.@
总结：mysql5.6版本无论在性能还是功能上，已经比之前的版本提升了不少，是一个不错的选择，另外，sql语句写得不适当，会带来很严重的性能问题
|
作者：陆炫志 出处：xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111 您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。 |
