线上SQL优化

当时查看Lepus 的慢查询监控，看到大量的这类SQL语句，而且消耗时长有点长：

大量类似以下的SQL语句：

select aa_id,dealername,dealertype,membername from  test where level_id <=4 order by aa_id limit 243000, 100;

下面我们看一下SQL语句的执行计划：

mysql> explain select aa_id,dealername,dealertype,membername from  test where level_id <=4 order by aa_id limit 243000, 100;            
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 301508 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

很多人一看表结构，发现在列level_id没索引，添加一个普通索引就完事啦！下面我们来试试：

mysql> alter table test add key (level_id);
Query OK, 301508 rows affected (3.71 sec)
Records: 301508  Duplicates: 0  Warnings: 0

mysql> explain select * from  test where level_id <=4 order by aa_id limit 243000, 100;
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | test  | range | level_id      | level_id | 4       | NULL | 301393 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

mysql> 

添加索引后，可以看到用上索引了，但效果相对之前并没有很大的提升，还有些人可能会说，在level_id和aa_id添加组合索引，性能可能就好了，我们再来看下：

mysql> alter table test add key (level_id,aa_id);    
Query OK, 301508 rows affected (3.75 sec)
Records: 301508  Duplicates: 0  Warnings: 0

mysql> explain select * from  test where level_id <=4 order by aa_id limit 243000, 100;
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | test  | range | level_id      | level_id | 4       | NULL | 301218 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

mysql> 

可以看到，效果还是一样的差。为什么显示用上了索引，却还扫描了几十万行呢？

我们回顾一下不会用上索引的几种情况：（可以参考我的之前写的常用SQL语句优化）

• 两个表关联字段类型不一样（也包括长度不一样）
• 通过索引扫描的记录数超过30%，变成全表扫描
• 联合索引中，第一个索引列使用范围查询
• 联合索引中，第一个查询条件不是最左索引列
• 模糊查询条件列最左以通配符 % 开始
• 内存表(HEAP 表)使用HASH索引时，使用范围检索或者ORDER BY
• 两个独立索引，其中一个用于检索，一个用于排序
• 使用了不同的 ORDER BY 和 GROUP BY 表达式

上面的SQL语句，符合了上面的联合索引中，第一个索引使用范围查询所以用不上索引，我们直接查询看看用时为多少：

mysql> select * from test where level_id <=4 order by aa_id limit 243000, 100;
100 rows in set (1.63 sec)

没用上索引，那我们应该怎么优化它呢？我们应该用延迟关联，把sql语句修改为如下：

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT a.* FROM  test a,(select id from test where level_id <=4 order by aa_id limit 243000, 100) b where a.id=b.id ;
+----+-------------+------------+--------+---------------+----------+---------+------+--------+-----------------------------+
| id | select_type | table      | type   | possible_keys | key      | key_len | ref  | rows   | Extra                       |
+----+-------------+------------+--------+---------------+----------+---------+------+--------+-----------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL     | NULL    | NULL |    100 |                             |
|  1 | PRIMARY     | a          | eq_ref | PRIMARY       | PRIMARY  | 4       | b.id |      1 |                             |
|  2 | DERIVED     | test       | range  | level_id      | level_id | 4       | NULL | 301218 | Using where; Using filesort |
+----+-------------+------------+--------+---------------+----------+---------+------+--------+-----------------------------+
3 rows in set (0.30 sec)

mysql> SELECT a.* FROM  test a,(select id from test where level_id <=4 order by aa_id limit 243000, 100) b where a.id=b.id ;

100 rows in set (0.30 sec)

可以看到速度快了几倍，现在数据量只有几十万，如果几百万，效果会更明显，为什么这样写会比之前的效果好呢？因为通过覆盖索引返回所需数据行的主键，再通过主键获取所需数据，所以速度比之前快上不少。

 优化案例二：

 表结构是：

mysql> show create table test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `code` varchar(32) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `status` int(2) DEFAULT ‘1‘,
  `createat` datetime DEFAULT NULL,
  `write_id` int(11) DEFAULT NULL,
  `creator_id` int(11) DEFAULT NULL,
  `dealer_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_index` (`code`) USING BTREE,
  KEY `dealer_id` (`dealer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7014142 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

slowlog里有大量这样的查询：

select count( id ) from `test2` where createat between ‘2015-05-26 00:00:00‘ and ‘2015-05-26 23:59:59‘  and status not in(7) and creator_id=8774 and write_id=925;          

查看下执行计划：

mysql> explain select count( id ) from `test2` where createat between ‘2015-05-26 00:00:00‘ and ‘2015-05-26 23:59:59‘  and status not in(7) and creator_id=8774 and write_id=925;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    | NULL | 5135067 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> 

没有索引，做了全表扫描，有些开发人员创建表的时候考虑得不周到，导致频繁出现影响性能的sql，我们添加组合索引看看效果（这里要注意一下，在线上如果是5.6以下的版本，对于一些大数据的表，别直接添加索引，因为这个过程会阻塞DML操作的，如果添加索引需要的时间是几个小时或者更多，这是很悲剧的一件事情，个人经验，小数据的表发现没索引，或者索引设置的不合理，直接alter修改，大数据的表，就要用pt工具了。5.6版本的MySQL虽然支持了Online DDL,但也添加索引的时候，要考虑是否处于业务的高峰期，尽量选择业务量不繁忙的时候添加）：

mysql> alter table test2 add key (createat,status,creator_id,write_id);
Query OK, 0 rows affected (1 min 36.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count( id ) from `test2` where createat between ‘2015-05-26 00:00:00‘ and ‘2015-05-26 23:59:59‘  and status not in(7) and creator_id=8774 and write_id=925;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test2 | range | createat      | createat | 14      | NULL |    1 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> 

线上SQL优化

标签：