首页 > 代码库 > SQL语句导致性能问题
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的版本测试下：
以下用版本为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相当重要，希望大家以后不要踩类似的坑@.@
SQL语句导致性能问题
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。