MySQL千万级数据存储以及查询优化

1.当前问题介绍

当前系统有一个交易流水表，按照每天一个表的策略进行分表存储交易流水。现在每张表大约3000W-4000W笔记录，即每天大约有3000w-4000w笔交易发生。该表采用Innodb存储引擎，默认支持事务。

流水记录的ID由程序自己生成，基于UUID实现，无序混乱。

现在碰到的问题是：每天下午，交易无法写入数据表，且当表的存储数据量增长到一定程序则无法进行查询。综上所述，涉及交易流水的相关操作在数据量增长到一定量之后均无法执行。

2.方案测试和选择

2.1.修改存储引擎

Innodb引擎由于要考虑事务控制，相对于MyISAM引擎，无论是插入还是查询，性能都相对较差，尤其是count全表数据条数。MyISAM引擎记录了数据表的记录数，当进行count全表数据量时，直接返回已记录的数据，耗时几乎可以忽略不计。经过测试，InnoDB引擎的插入性能耗时是MyISAM引擎的3倍。

2.2.增加分区策略

在修改引擎的基础上，再在原有分表策略之上增加分区策略，能够更好的对数据进行管理，同时提高数据查询效率。按小时分区示例代码如下：

DROP TABLE IF EXISTS `persons_myisam_patition`;
CREATE TABLE `persons_myisam_patition` (
  `id` varchar(20) NOT NULL,
  `name` varchar(10) NOT NULL,
  `age` int(11) NOT NULL,
  `profile` varchar(255) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `createdate` varchar(8) NOT NULL,
  `createtime` varchar(9) NOT NULL,
  `insertdatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`createtime`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
/*!50500 PARTITION BY RANGE  COLUMNS(createtime)
(PARTITION p1 VALUES LESS THAN ('010000000') ENGINE = MyISAM,
 PARTITION p2 VALUES LESS THAN ('020000000') ENGINE = MyISAM,
 PARTITION p3 VALUES LESS THAN ('030000000') ENGINE = MyISAM,
 PARTITION p4 VALUES LESS THAN ('040000000') ENGINE = MyISAM,
 PARTITION p5 VALUES LESS THAN ('050000000') ENGINE = MyISAM,
 PARTITION p6 VALUES LESS THAN ('060000000') ENGINE = MyISAM,
 PARTITION p7 VALUES LESS THAN ('070000000') ENGINE = MyISAM,
 PARTITION p8 VALUES LESS THAN ('080000000') ENGINE = MyISAM,
 PARTITION p9 VALUES LESS THAN ('090000000') ENGINE = MyISAM,
 PARTITION p10 VALUES LESS THAN ('100000000') ENGINE = MyISAM,
 PARTITION p11 VALUES LESS THAN ('110000000') ENGINE = MyISAM,
 PARTITION p12 VALUES LESS THAN ('120000000') ENGINE = MyISAM,
 PARTITION p13 VALUES LESS THAN ('130000000') ENGINE = MyISAM,
 PARTITION p14 VALUES LESS THAN ('140000000') ENGINE = MyISAM,
 PARTITION p15 VALUES LESS THAN ('150000000') ENGINE = MyISAM,
 PARTITION p16 VALUES LESS THAN ('160000000') ENGINE = MyISAM,
 PARTITION p17 VALUES LESS THAN ('170000000') ENGINE = MyISAM,
 PARTITION p18 VALUES LESS THAN ('180000000') ENGINE = MyISAM,
 PARTITION p19 VALUES LESS THAN ('190000000') ENGINE = MyISAM,
 PARTITION p20 VALUES LESS THAN ('200000000') ENGINE = MyISAM,
 PARTITION p21 VALUES LESS THAN ('210000000') ENGINE = MyISAM,
 PARTITION p22 VALUES LESS THAN ('220000000') ENGINE = MyISAM,
 PARTITION p23 VALUES LESS THAN ('230000000') ENGINE = MyISAM,
 PARTITION p24 VALUES LESS THAN ('240000000') ENGINE = MyISAM) */;

往分区表写入数据之后，查看每个分区的记录数，执行命令如下：

SELECT partition_name part,
	partition_expression expr,
  partition_description descr,
  table_rows 
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = 'persons_myisam_patition';

2.3.增加字符串短索引

查询时间字段为createtime，数据类型为varchar(9)，形式为HHmmssSSS。为了提高查询效率需要对该字段创建索引。根据查询业务可知，查询区间粒度为分钟，所以为该字段只需要创建4个字符的短索引，既能满足查询需求，同时也能减少索引占用空间，提高插入效率。索引创建命令：

CREATE INDEX idx_createtime ON persons_myisam_patition(createtime(4));

2.4.创建全文索引

全文检索在MySQL里面很早就支持了，只不过一直以来只支持英文。缘由是他从来都使用空格来作为分词的分隔符，而对于中文来讲，显然用空格就不合适，需要针对中文语义进行分词。但从MySQL 5.7开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

2.4.1.设置必要的参数

在使用中文检索分词插件ngram之前，先得在MySQL配置文件里面设置他的分词大小（默认是2），比如：

[mysqld]
ngram_token_size=2

查看参数执行命令：SHOW VARIABLES LIKE ‘%ngram%’;

2.4.2.添加全文索引

alter table persons_myisam_patition add fulltext index testfulltext(profile,logo) with parser ngram;

2.5.MyISAM相关参数调整

2.5.1.并发读写的设置

关于myisam的insert写和读的并发关系，受限制于系统参数concurrent_insert，这个看参数concurrent_insert来控制并发的读与insert是否共存。
当concurrent_insert=0时，此时读不能与insert写共存。
当concurrent_insert=1时，如果表中没有空数据块时，读可以与insert写共存，insert新增加的数据将插入到数据文件尾部
当concurrent_insert=2时，不论有没有空数据块，insert新增加的数据都将插入到数据文件尾部。
在mysql的配置文件my.ini中[mysqld]模块，增加配置项：concurrent_insert=2
查看配置参数：show variables like ‘%concurrent%’;

2.5.2.读写优先级设置

缺省情况下，写操作的优先级要高于读操作的优先级，即便是先发送的读请求，后发送的写请求，此时也会优先处理写请求，然后再处理读请求。这就造成一个问题：一旦我发出若干个写请求，就会堵塞所有的读请求，直到写请求全都处理完，才有机会处理读请求。此时可以考虑使用 max_write_lock_count：
max_write_lock_count=1
有了这样的设置，当系统处理一个写操作后，就会暂停写操作，给读操作执行的机会。
我们还可以更干脆点，直接降低写操作的优先级，给读操作更高的优先级。
low-priority-updates=1
配置参数查看：
show variables like ‘max_write_lock_count’;
show variables like ‘low-priority-updates’;

2.6.修改流水ID的生成方法

流水ID混乱无序，在大数据量查询过程中无法被利用。所以本文档提出生成有序的流水ID。序列ID生成算法采用雪花算法，具体实现类请参考项目代码。

3.需求查询优化

3.1.查询当天总交易量

因为采用分表策略，每天交易流水存储到一个以日期命名的数据表。查询当天的总交易量只需执行：
SELECT count(*) FROM persons_myisam_patition；
采用MyISAM存储引擎，查询效率飞快。

3.2.查询当天每分钟交易量

每分钟交易量可以从快照数据表查询获得。交易量本身就是每分钟快照指标，在快照节点已计算得到，查询效率高。

3.3.分页查询指定时间的交易信息

该需求在交易查询功能中实现。交易查询必须要指定查询时间区间，区间粒度为分钟，区间不能跨天。
该需求实现分为两步，第一步计算指定时间区间的交易量，第二步分页查询指定区间的交易信息。
由于区间粒度为分钟，第一步交易量的计算可以从快照数据表查询获得；如果带了其他查询条件，则在条件字段创建索引，从流水表查询；

select count(*) from persons_myisam_patition where createtime BETWEEN '101200000' AND '105900000' and name = ‘andy’

；
第二步查询交易信息，实现语句：

select * from persons_myisam_patition where createtime BETWEEN '101200000' AND '105900000' limit 10000,10；

查询效率较高。