SQL优化之踩过的坑_【马哥Linux运维】

正看资料看的过瘾，突然收到报警，说服务器负载太高……...

正看资料看的过瘾，突然收到报警，说服务器负载太高，好吧，登录服务器看看，我擦嘞，还能不能愉快的玩耍了？下面是当时的负载情况

看见mysql使用cpu已经到了2000，io没有等待。

说明应该没有大的临时表，或者文件排序，但是SQL语句肯定还是有问题的。

好吧，那进数据库看看到底在干嘛，
执行

show full processlist

后，发现有好几百个连接在执行同一条SQL语句，看见SQL也还好，不复杂，是子查询，我最恶心的子查询。

那就EXPLAIN一下咯，线上的东东我就不在这里贴出来了，后面我会创建类似的表来解释。

表结构简单如下：

mysql> desc t1;

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

| 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 t2;

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

| 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>

索引如下：

mysql> show index from t1;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| t1    |          0 | PRIMARY  |            1 | id          | A         |         329 |     NULL | NULL   |      | BTREE      |         |               |

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

1 row in set (0.00 sec)



mysql> show index from t2;

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

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

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

| t2    |          0 | PRIMARY  |            1 | id          | A         |       12826 |     NULL | NULL   |      | BTREE      |         |               |

| t2    |          1 | role_id  |            1 | role_id     | A         |         583 |     NULL | NULL   |      | BTREE      |         |               |

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

2 rows in set (0.00 sec)



mysql>

当时大量SQL语句如下：

mysql> select privilege_id from t2 where role_id in (select role_id from t1 where id=193);

那么你会觉得这语句有问题么？

你会说这哪有什么问题啊。

t2表role_id有索引，t1的表id是主键，肯定走索引啦！

但是就是这么坑，那我们EXPLAIN一下，结果如下：

mysql> explain select privilege_id from t2 where role_id in (select role_id from t1 where id=193);



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

| id | select_type        | table | type  | possible_keys | key     | key_len | ref   | rows  | Extra       |

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

|  1 | PRIMARY            | t2    | ALL   | NULL          | NULL    | NULL    | NULL  | 12826 | Using where |

|  2 | DEPENDENT SUBQUERY | t1    | const | PRIMARY       | PRIMARY | 4       | const |     1 |             |

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

2 rows in set (0.00 sec)



mysql>

what？发生了什么事情，怎么和想的不一样？

这不科学啊，虽然数据量不多，但是执行频率非常高的情况下，也是一种悲剧。

好吧，我本来就不喜欢子查询，我改成了join看看。

mysql> explain select a.privilege_id from t2 as a inner join t1 as b on a.role_id=b.role_id and b.id=193;

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

| 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 |  128 |       |

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

2 rows in set (0.00 sec)



mysql>

这下更郁闷了，怎么join就走索引了呢？

后来想到in后面接受多个值，但是我的t1表的id是主键肯定只有一条记录，那么我可以改成=，那么我们试试。

mysql> explain select privilege_id from t2 where role_id = (select role_id from t1 where id=193);

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

| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |

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

|  1 | PRIMARY     | t2    | ref   | role_id       | role_id | 4       | const |  128 | Using where |

|  2 | SUBQUERY    | t1    | const | PRIMARY       | PRIMARY | 4       |       |    1 |             |

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

2 rows in set (0.00 sec)



mysql>

我去，这样真的可以走索引，好吧。晕了。。

这时突然想到或许优化器的问题，还是一个朋友提醒，这下才明白。

原来子查询这里role_id（）有限制，这个括号里的查询要基于唯一索引或是主键。不过在更高版本已经修复了这个问题。下面给出例子。

percoan-5.5.38的版本:

mysql> show variables like &#39;%version&#39;;

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

| Variable_name    | Value       |

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

| innodb_version   | 5.5.38-35.2 |

| protocol_version | 10          |

| version          | 5.5.38-35.2 |

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

3 rows in set (0.01 sec)



mysql> explain select privilege_id from t2 where role_id in (select role_id from t1 where id=193);

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

| id | select_type        | table | type  | possible_keys | key     | key_len | ref   | rows  | Extra       |

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

|  1 | PRIMARY            | t2    | ALL   | NULL          | NULL    | NULL    | NULL  | 12826 | Using where |

|  2 | DEPENDENT SUBQUERY | t1    | const | PRIMARY       | PRIMARY | 4       | const |     1 |             |

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

2 rows in set (0.00 sec)



mysql>

percona-5.6.21版本:

[root@localhost [test]> show  variables like &#39;%version&#39;;

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

| Variable_name    | Value           |

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

| innodb_version   | 5.6.21-rel70.0  |

| protocol_version | 10              |

| version          | 5.6.21-70.0-log |

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

3 rows in set (0.00 sec)



[root@localhost [test]> explain select privilege_id from t2 where role_id in (select role_id from t1 where id=193);

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

| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |

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

|  1 | SIMPLE      | t1    | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |

|  1 | SIMPLE      | t2    | ref   | role_id       | role_id | 4       | const |  129 | NULL  |

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

2 rows in set (0.00 sec)



[root@localhost [test]>

可以看见5.6.21的版本已经不受影响了。

后面让开发同学修改成join以后，负载慢慢的下来了。下面是zabbix监控到的负载情况：

总结：

子查询虽然写起来方便，且简单易懂，但是我们还是尽量的使用join，因为在5.6版本以前的子查询的性能实在不怎么样。

作者：Atlas
出处：Atlas的博客http://www.cnblogs.com/gomysql
您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。

马哥Linux，智者的选择

☀ 马哥linux运维微信公众号致力于linux运维相关知识的推送，秉承授人以鱼不如授人以渔的理念，为广大linux爱好者推送互联网前沿技术和提供咨询！

☀ 咨询电话：400-080-6560☀ 官方站点：www.magedu.com

☀ 官方博客：www.178linux.com

☀ 官方博客：mageedu.blog.51cto.com