基于营销资源系统云化后的数据库查询性能优化探究

黎刚

关键词 数据库 优化 索引 分页

1研究背景

在营销资源系统云化后的情况下，分布式MySQL数据库具备读写分离功能，其可以承载高可靠的数据集，即“ 一主两备” 数据集。系统的开发框架是“SpingBoot+Ibatis+MySQL”。系统云化初期，在数据查询方面出现了很多问题，最为典型的问题就是页面查询缓慢、客户感知极差[1] 。通过对全量的慢SQL进行分析可以发现，出现上述问题的原因主要有两个：一是没有建立合理的索引;二是很多需要查询的语句没有走上索引。为了解决系统查询缓慢的问题，本文对全量的慢SQL 语句进行了分析和优化，最终使系统的查询语句速度维持都在1 秒以内，且CPU 和磁盘IO 的占比始终处于合理水平[2] 。

2优化技术

测试的硬件环境和软件环境为：逻辑CPU 核数为64 个; 内存总量为377GB;交换空间为31GB;单个数据集的存储量为1.5TB;操作系统为CentOS?7?x86_64。

测试结构如表1。整张表的数据量为：22987997。

为了保持文章简洁，我们确定查询条件如表2。为了使后文不再重复，统一用“查询条件”代替表2 的查询条件。

3营销资源系统云化后索引的使用方法

索引就像是一幢大楼的房间编号。如果需要寻找某个房间的人，首先应该查找房间编号，找到该房间后，就能快速找到对应的人[3] 。

营销资源系统云化后，为了提供高效的查询性能，所有的库表需要建立合理的索引，因此全量的查询语句都需要走上索引。

本文采用EXPLAIN 对查询语句进行分析，对库表为MKT_RES_INST。图1 为重点关注数据。

根据MKT_RES_INST 表可知，在分布式数据库的环境中，单个分片承接的上限为1000 万条左右。根据业务量进行综合考虑，本文设计了16 个分片，数据量为22987997。

通过分析可以发现，一个查询语句是否走上索引在消耗的时间方面有较大差异，如表3 所示。

4使用limit 对分页进行优化

在本文模拟的云化的场景中，分页业务场景需要返回某几行数据，limit 则可以指定返回的记录数。当数据量达到2000 万条以上时，如果仅需获取部分数据，一定要注意规避全表扫描的问题，否则查询效率会很低[4] 。

使用合理的分页方式可以提高分页的效率，切忌利用limit offset 和row_count 进行分页。以语句select? from MKT_RES_INST limit 100000，1;为例，MySQL首先会扫描100000 行，再读取一行，可见效率之差。而优化思路则是：砍掉跳页功能（直接跳到第X 页，最后一页等），每次读取根据上一页的最大ID 做范围查询。

优化举例：selectMKT_RES_INST_ID， MKT_RES_INST_NBR from MKT_RES_INST limit 866644， 10。用该语句做分页查询可知，当数据量达到2000 万时，需要消耗很多时间。用本文的测试数据对其进行测试验证如表4 所示。

5营销资源系统云化项目全量慢SQL优化

营销资源系统云化项目大约经历了一年半的上线过程。系统云化初期，在数据查询方面出现了很多问题，如页面查询缓慢以及查询时间超过1 秒的全量的慢SQL 超过100 条。通过对全量的慢SQL 进行分析可以发现，其原因主要有两个：一是没有建立合理的索引;二是查询的很多语句没有走上索引。为了解决该问题，本文对系统进行了全量的慢SQL 语句分析和优化[5] 。

5.1查询语句中禁止使用“?”

在分布式数据库的DML 审计中，要求查询语句不能跨片查询。查询语句在不跨片的前提下，会将语句中的“?” 按照表中数据字典对应的全部列名进行依次转换，该过程耗时较长。优化办法是：仅列出所需的字段名，不查询不需要展示的字段，并且项目中的全部语句禁止使用“?”[6] 。

在查询语句中采用“?”，会消耗数据字典转换为全量列名的时间，同时会增加CPU、磁盘IO、数据网络传输的时间，所以尽量不要使用“?”。字段提取按照“需多少、提多少”的原则。若用“?”，验证数据如表6 所示。

明確提取的字段去掉“?”后，取1000 条数据的结果如表7 所示。

5.2若限制条件中其他字段没有索引，禁止用or

在对or 的两边进行查询时，若存在一个不是索引字段，而其他条件有索引字段，最后的查询结果是：走不上索引。本文对该场景进行了优化，采用union all替代or，测试证明查询的效果较好（见表8 和表9）。

原因分析： 由于“ or CREATE _ STAFF =500905505”没有走上索引，造成整个查询都没有走上索引，查询耗时较长。

原因分析：查询语句的前半部分走上了索引，后半部分没有走上索引，但整体的查询性能得到提升。

5.3避免在where 中对字段进行null 值判断或者对字段进行函数操作

5.3.1禁止在where 中使用null 值判断

测试表明，如果where 中有null 的判断，查询不能走上索引，查询是全表扫描的。本文利用explain 分析的结果如图2 所示。

5.3.2 避免在where 中对字段进行函数操作

如果在索引列上使用了函数运算会导致索引失效，此时可将计算放到索引列外的表达式上。比如，在表10 中，TIMESTAMPDIFF（DAY， a. create_date，now（）） <= 1。

原因分析：在索引列上使用了函数运算，导致索引失效。

优化为：create_date> = DATE_FORMAT（DATE_SUB（NOW（ ），INTERVAL 1 DAY），'%Y?%m?%d %H：%i：%S'） ，查询效率明显提升（表11）。

原因分析：将计算放到索引列外的表达式上，确保查询语句走上了索引，查询性能才有大幅度的提升。

5.4联合索引遵循“最左前缀”法则

本文使用的联合索引为idx_STATUS_CD_LAN_ACPT，其基于STATUS_CD，LAN_ID，ACPT_STATE 三个字段而创建，如索引idx_STATUS_CD_LAN_ACPT（STATUS_CD，LAN_ID，ACPT_STATE），遵循“最左前缀”法则。

按最左边第一个字段的查询条件，符合“最左前缀”法则，是可以走上索引的（表12）。

耗时：21ms。

按最左边前两个字段的查询条件，也是可以走上索引的（表13）。

按右边第一个字段的查询条件，无法走上索引（表14）。

5.5禁止使用%前缀进行模糊查询

使用LIKE“% REMARK”或者LIKE“% REMARK%”等查询语句不能走上索引，因为此时查询语句会对全表进行扫描。但是，使用LIKE “REMARK %”查询语句可以走上索引。

使用LIKE“%name%”查询语句，不能走上索引（表15）。

测试数据表明： LIKE 语句一定要使用LIKE“REMARK %”等后缀进行模糊查询，确保走上索引。

6结论