MySQL几个简单SQL的优化

最近在做项目的时候，遇到了一些大数据量的操作，有大批量的CRUD的操作，一开始的实现的方案经过性能测试，发现性能并不是很好，然后开始审查代码，对相关可以提升性能的操作进行了优化，这里分享给大家。

原则

首先我这里不讲索引相关的内容以及数据库相应参数的优化，这里假设你对索引已经有了相关的了解了，我总结了下我这次的优化，主要两个原则：

• 一些特定的场景，尽量用批处理处理数据，比如批量添加数据，批量修改数据；
• 结合业务尽量减少SQL的执行次数和查询不必要的数据；

场景实践

为模拟运行场景，我这里建了一个表，并往里面添加了300w条数据，表结构如下：

CREATE TABLE `tb_big_data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `weixin_id` varchar(64) NOT NULL,
 `openid` varchar(64) NOT NULL,
 `status` int(3) NOT NULL,
 `gmt_create` datetime NOT NULL,
 `gmt_modified` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY `weixin_id_gmt_create_openid` (`weixin_id`,`gmt_create`,`openid`)
) ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=utf8

1.分页查询小优化

分页查询老生常谈，网上各种优化方法都很多，这里就不提及了，这里只是分享一个小技巧：

如何在使用最普通的limit的时候提高性能？

假设我们现在有一条这样的SQL：

SELECT * FROM `tb_big_data` where weixin_id ='gh_266a30a8a1f6' and gmt_create > '2017-10-10 00:00:00' order by id asc limit 800000, 100;

执行时间：100 rows in set (1.53 sec)

假如我们现在不能进行其他优化，比如传入最小id，分表查询等策略，以及不进行SQL预热，怎么提高这条SQL的速度呢？
其实很简单我们只需要一个in操作即可：

SELECT * FROM `tb_big_data` t1 where t1.id in ( 
    SELECT tt.id FROM ( 
        SELECT id FROM `tb_big_data` t2 where weixin_id = 'gh_266a30a8a1f6' and gmt_create > '2017-10-10 00:00:00' order by t2.id asc limit 800100, 100
        ) as tt);

执行时间：100 rows in set (1.17 sec)

可以看出只需稍加修改，SQL的效率可以提高30%~40%，而且在单条数据记录越大的情况下效果越好，当然这不是最好的分页方法，这只是一个小技巧；

2.减少SQL查询

现在有一个需求我们现在有一个用户的列表（用户的唯一标识为openid）然后我们需要判断用户在当天是否有过相应的记录；

这是问题其实很简单，我们首先一想到的操作就是循环这个列表一个一个判断，很简单也很好实现，但是真正测试的时候发现性能却很差，尤其在数据量大的情况下，倍数级增长，这里有有网络数据传输消耗的时间和SQL本身的执行时间；

假设我们现在执行一条以下的SQL：

SELECT * FROM `tb_big_data` WHERE weixin_id ='gh_266a30a8a1f6' and gmt_create > '2017-10-13 00:00:00' and openid='2n6bvynihm5bzgyx';

执行时间：1 row in set (0.95 sec)

现在如果我们执行100次，不敢想象会是什么情况，庆幸自己发现了这个问题，因为在数据量少的情况下，这个问题表现的并不是那么严重，其实我们稍加改变就能以另一种高效的方式解决这个问题：

SELECT * FROM `tb_big_data` WHERE weixin_id ='gh_266a30a8a1f6' and gmt_create > '2017-10-13 00:00:00' and openid in ('2n6bvynihm5bzgyx','1stbvdnl63de2q37','3z8552gxzfi3wy27'...);

执行时间：100 row in set (1.05 sec)

发现了没有，还是用in，而且执行时间几乎与单条查询的时间一样，可见只是单一这一部分处理就可以提升了很大的性能。

3.特定场景使用SQL的批处理

这个跟上一点有一个相似点，那就是减少SQL执行，上面只是查询而已，而当出现大批量的CUD的操作时，执行每条SQL，数据库都会进行事务处理，这将会消耗大量的时间，而且极端情况下会引起大批量SQL等待无法执行，导致业务出错，正是因为这些原因，我们在一些适当的情况下可以使用批处理来解决这个问题。

（1）批量插入

批量插入比较简单，也比较常用，这里就给一下基本语法：

INSERT INTO table_name (field1,filed2,...) values (value11,value12,...),(value21,value22,...),...

（2）批量更新

我先举个简单的例子，我们现在来根据一些条件来更新数据，具体SQL如下：

update `tb_big_data` set status = 2 WHERE weixin_id ='gh_266a30a8a1f6' and gmt_create > '2017-10-13 00:00:00' and openid = '2n6bvynihm5bzgyx';

Query OK, 1 row affected (2.28 sec)
Rows matched: 1  Changed: 1  Warnings: 0

很惊讶，我们只是更新了一条记录，而且更新条件上是有复合索引的，没想到速度还那么慢，可以想象如果我们批量更新数据，那得耗时多少；

但是我们看一下另一条SQL：

update `tb_big_data` set status = 1 WHERE id = 900098;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

上面的id值为之前条件筛选出来的记录的id，是不是很惊讶，怎么这条SQL执行的时间几乎不需要什么时间，所以我们可以利用这个特点和批量查询简化批量更新，虽然这种方式不能让性能到最优，但是也能提升很大了，我进行了一个测试，根据相应条件批量更新100条数据：

方式	直接批量更新	先批量查主键再批量更新
耗时	289.12s	1.342s

可以看出这种方式相对对于普通方式来说，性能提升巨大，具体执行的时候我们也可以将这些SQL放在一个事务提交，减少数据库事务次数，但只这是一种在代码层面上的优化；

另外我们可以利用MySQL提供的特殊语法进行批量更新，具体语法为：

#语法
INSERT INTO table_name (id,field1,field2,...) VALUES  (id1,value11,value12,...),(id1,value11,value12,...),... on duplicate key update  field = VAULES(field);

#使用例子

INSERT INTO `tb_big_data` (id,weixin_id,openid,gmt_create,status) values  (1,'gh_266a30a8a1f6','w9q8fmodytjgppsr','2017-10-13 12:00:00',3),(2,'gh_266a30a8a1f6','bu1flmch4i8eegzf','2017-10-13 12:00:00',3) on duplicate key update status = VAULES(status);

经过测试这种方式在数据量小的情况下与上述方式效率差不多，但是随着数据量越来越大，性能也越来越好，缺点的话主要传输的数据量很大，不需要更新的字段也需要传输。

另外也不推荐大量数据的批量更新，一次不要超过1000条为好。

总结

总的来说，SQL优化是一门细心的学问，需要不断去尝试，测试，找到最优方式，另外还有一点就是要结合实际情况，综合考虑选择合适的方式。