SQL优化之踩过的坑(转)
来源:互联网 发布:网络协议栈开发 编辑:程序博客网 时间:2024/06/06 07:20
正看资料看的过瘾,突然收到报警,说服务器负载太高,好吧,登录服务器看看,我擦嘞,还能不能愉快的玩耍了?下面是当时的负载情况
看见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 '%version';+------------------+-------------+| 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 '%version';+------------------+-----------------+| 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版本以前的子查询的性能实在不怎么样。
阅读全文
0 0
- SQL优化之踩过的坑(转)
- spark SQL上踩过的坑
- 遇到过得基础的sql语句优化
- DOM性能优化 那些年踩过的坑
- 《C++之那些年踩过的坑(二)》
- Toolbar使用之踩过的坑
- Android踩过的坑之bluetoothSocket
- shell脚本之踩过的坑
- spark sql thrift server搭建及踩过的坑
- C--经典算法之排序---优化过的冒泡排序
- MySQL优化之SQL优化(一)
- 转:sql的优化
- 用过的sql
- 一条过之 一条SQL搞定信息增益的计算
- Oracle之SQL优化-索引的基本原理(二)
- 踩过的坑
- 踩过的坑
- 踩过的坑
- android gradle配置详解
- Sinatra编程
- android的gradle
- 如何跑从别人那里拷来的项目
- 原创一 Fragment 的生命周期
- SQL优化之踩过的坑(转)
- 小玩具——温度采集项目(一)
- 关于ThreadLocal的思考
- python 微信公众平台服务器设置
- Mac下搭建PHP开发环境
- 修改Maven默认骨架创建的web项目版本
- 透明报文转发
- 原创二 Fragment 动画
- 【大数据】医疗大数据“九大业务应用”相关研究