mysql百万级数据查询优化实践-从开始到开除

在某个项目中，订单表每天会产生5-10万条记录。

先前对大数据查询没多少处理经验，碰到难以查动的统计数据通常是建一张表，定时统计一次。或者写存储过程，优化查询过程。

按照以往一贯思路，建表，加索引。

然而这次在表里只有十多万数据时，每日统计查询竟然查不动了，查一次要四五秒。

如果是百万级数据，我还可以甩锅为服务器性能问题，十万数据查不动，要是汇报让领导升级服务器，估计领导直接让我去跟人事结算工资了。

我是这么设想的：

我：“查不动是服务器性能问题。”领导：“现在服务器一百一个月，十万一百，一百万一千。这样吧，你跟人事结下工资，我用你的工资买个高配服务器。”

于是我开始了百度寻找数据优化的方法。

表结构如下(字段有删减，字段个数，数据量对查询速度有影响)：

						CREATE TABLE `myorder`  (
						  `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '数据id',
						  `CREATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
						  `UPDATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
						  `sn` varchar(50) NOT NULL COMMENT '订单编号',
						  `client_id` int(11) NOT NULL COMMENT '供应商id',
						  `money` decimal(19,2) DEFAULT NULL COMMENT '订单金额',
						  PRIMARY KEY (`ID`) USING BTREE
						) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
						ALTER TABLE myorder ADD INDEX index_mo_sn ( `sn` ) ;
						ALTER TABLE myorder ADD INDEX index_mo_client_id ( `client_id` ) ;

SQL如下：

						select client_id,sum(money)
						from myorder
						where CREATE_TIME>=CURRENT_DATE
						group by client_id

去掉分组条件，查询很快。

经过尝试，第一次优化的结果便是给时间字段加上索引。

原以为问题解决了。某天晚上，一个电话把我吓得睡不着。领导发现每次统计查询要半分钟。

我纳闷了，不可能，昨天都没问题，怎么今天就萎了，四十万和五十万数据有很大区别？

于是我亲自去检阅了那个功能，确实查不动。然后我告诉领导：“服务器网络波动，我修复一下。”

经过测试，发现这天的数据量有点大，按时间查询都要十来秒，加上分组，时间更长。

过了十多分钟，领导问我：“怎么样了？”我：“服务商说他们网络被攻击了，正在修复。”我登录服务器，防火墙把80端口关了。

查看执行计划，查询没有走时间索引。最后得出结论，用时间做查询条件是行不通的。

静音了手机后，我细细思考，一度萌生了建个统计表定时统计的想法。但是一想这统计每次要十多秒，万一锁了表就很尴尬了。

经过一番思考，我想到了用ID查询，用前一天最后一条数据的ID做统计条件

SQL如下：

						select client_id,sum(money)
						from myorder
						where id>(select max(id) from myorder where CREATE_TIME<CURRENT_DATE)
						group by client_id

经测试，百万数据查询时间控制在1秒内。

随之还发现了一个小问题。因为分页查询分两步，第一步查询总条数，第二步查分页数据。在查总条数时，不加任何条件，查询速度有点慢，数据量越大速度越慢。

SQL如下：

						select count(1)
						from pay_order

100w数据查了2秒

查看执行计划可以发现，查询没走索引，让他走索引即可。

SQL如下：

						select count(1)
						from pay_order
						where id is not null

加上后只用了0.2秒

重新部署项目，给领导回拨了个电话。领导：“为什么不接我电话？”我：“刚临时有事出门，没带手机。”领导：“服务商告诉我他们网络没被攻击。”我诡辩道：“不可能，我刚什么都没做，要是没被攻击，它怎么自己好了。”领导语塞。

有一天，领导突然叫我去聊天。领导：“我发现现在查询很慢，特别是点最后一页。”我解释道：“数据量大了，查的就慢了。”领导：“我朋友公司那个开发月薪4K，百万级数据查询速度和你的差不多，你要对得起你8K的工资，快一倍要求不高吧？”我愕然，没想到还能这样比：“好像有那么点道理……我觉得可以升级一下服务器。”领导一脸不快：“要我给你配个天河一号？”我：“……”

首先看下最后一页查询速度慢的问题

按照百度到的方法，改写查询条件：

原SQL：

						select *
						from myorder
						limit 1000000,100

改写后：

						select *
						from myorder
						where id >= (SELECT id FROM myorder LIMIT 1000000, 1)
						limit 100

看着好像是没什么问题，遂部署上线。过了两天，领导突然找我。领导：“为什么倒序排序的数据不管哪一页都是一样的？”我一看，还真是，灵机一动：“服务商的数据库默认配置有问题，我处理一下。”

在实际中，我们的where后面会带很多查询条件，还会有多个字段组合升序降序的排序。有个好办法，禁用排序（尽可能的减少order by），禁用搜索。

经过多次测试发现，如果结果字段只有id这一列，查询速度会非常快。查看执行计划，结果列多加一个字段，就不走索引了。where和order by用到的字段都要测一遍，查看执行计划。

既然如此，那就把上面的SQL拆成两个查询

SELECT id FROM myorder where ... order by ... LIMIT 1000000, 100

select * from myorder where id in (1,2,3,4...)

先查出id，将id拼成字符串，再用in查询，查询速度没什么影响，数据准确

领导：“服务器怎么访问不了了？”我查看日志，发现有人试图导出几十万数据，内存溢出，应用崩了：“服务器网络波动。”没多久，领导：“服务商说网络没问题，还给我提了个建议。”我很好奇：“什么建议？”领导：“建议我开除你。”