MySQL几个简单SQL的优化

#mysql

最近在做项目的时候,遇到了一些大数据量的操作,有大批量的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优化是一门细心的学问,需要不断去尝试,测试,找到最优方式,另外还有一点就是要结合实际情况,综合考虑选择合适的方式。

文章允许转载、使用,但需要保留链接文章署名 godpan.me,如有写的不当之处,也欢迎大家指正,联系邮箱:godpan.sen@gmail.com