MySQL大数据分页调优实践
一、实践回顾
MySQL版本5.7.28。
需求是将A表(350W记录)中的有效数据存入ES中。所谓有效数据是指未逻辑删除且未Disabled的数据，其实就是一组where条件。
原有的方案是使用SQL的Limit功能分页读取数据，将数据压入MQ中，消费者监听MQ并将数据存入ES。伴随着pageIndex的增长，MySQL查询速度越来越缓慢，3个小时仅处理了150W数据。
为加快数据同步速度，在对SQL Explain之后，确定了两条优化方案：
1. SQL仅取A表的主键，消费者根据主键列表再获取A表各字段信息；
2. 优化SQL分页，通过使用lastId和limit组合获取分页数据。
最终，采用单消费者单线程的方式，仅用40分钟就处理完成了全部350数据，比之前提速10倍以上。
二、步骤过程
1. 我们先来看一下使用传统limit在2000页200W数据之后的性能表现:
select id, xxx, xxx from A where xxx and enabled = 1 and deleted = 0 order by id limit 2300000, 1000;
这个耗时在网络繁忙的情况下甚至达到过30秒的速度。
Explain一下该SQL
2. 在来看一下使用传统limit仅对主键进行分页之后的性能表现:
select id from A where xxx and enabled = 1 and deleted = 0 order by id limit 2300000, 1000;
3. 采用lastId过滤后的结果:
select id from A where xxx and enabled = 1 and deleted = 0 order by id limit 1000;
注意，以上三次explain的结果均显示possible_keys中的idx_enabled未在实际查询中使用，这是因为enabled是一个bit类型字段，该类型的索引区分度太低。
三、Explain字段
Explain有两种格式，一种是按字段格式展示，另一种是Json格式，各个字段的对应关系如下：
| Column | Json Name | 描述 |
| id | select_id | 语句编号 |
| select_type | None | 查询(select)的类型 |
| table | table_name | 当前语句的表名 |
| partitions | partitions | 匹配的分区 |
| type | access_type | 连接类型 |
| possible_keys | possible_keys | 可用的索引 |
| key | key | 当前查询实际使用的索引 |
| key_len | key_len | 使用的索引的长度 |
| ref | ref | 哪些列或常量被用来与"key"字段中的索引进行比较。 |
| rows | rows | 被检索的行数。对InnoDB存储引擎来讲，这是一个估计值。 |
| filtered | filtered | 被过滤的行数占"rows"字段的比例。也是一个估计值。 |
| Extra | None | 额外信息。 |
四、Explain字段详解
1.id: SQL语句编号，如果有子查询，会按照查询顺序编号。如果SQL中包含union关键字，该值可能为null。
2.select_type: 每个查询(select)子句的类型，共有以下类型：
| select_type | Json Name | 描述 |
| SIMPLE | None | 简单查询语句(不包含UNION或子查询) |
| PRIMARY | None | 对于包含子查询的复杂语句，最外层的语句会被标记为PRIMARY |
| UNION | None | 在UNION语句中第一个之后的select语句 |
| DEPENDENT UNION | dependent (true) | 在UNION语句中第一个之后的select语句，依赖外层查询 |
| UNION RESULT | union_result | 在UNION语句的结果 |
| SUBQUERY | None | 子查询中第一个select语句 |
| DEPENDENT SUBQUERY | dependent (true) | 子查询中第一个select语句，依赖外部查询 |
| DERIVED | None | 派生表，from后的子查询 |
| MATERIALIZED | materialized_from_subquery | 视图查询 |
| UNCACHEABLE SUBQUERY | cacheable (false) | 如果一个子查询的结果对于外部查询无法缓存，只能重新计算，则标记此类型 |
| UNCACHEABLE UNION | cacheable (false) | 对于上面的UNCACHEABLE SUBQUERY，如果它同时属于union语句中第二个或之后的查询 |
3. table: 每个查询的表，除了实际表名之外，还有三种特殊格式的表名：
(1)<unionM,N> 这种格式说明数据来源于两个查询union之后的结果，M和N是union前后两个查询的id编号；
(2)<derivedN> 这种格式说明数据来源于派生表的结果，N是派生查询的id编号；
(3)<subqueryN> 这种格式说明数据来源于子查询的结果，N是子查询的id编号；
4. partitions: 当前查询所匹配的分区，若无分区则值为NULL；
5. type: 当前查询的访问类型(access type)，性能从优到劣分别如下：
(1)system, 如果当前查询的表只有一行数据；
(2)const, 如果当前查询只匹配最多一条数据；
(3)eq_ref, 如果查询全部使用了主键或者唯一非空索引，不同于上面两种类型，这是绝大多数查询中性能最佳的访问类型；
(4)ref, 如果查询使用了普通索引；
(5)fulltext, 如果查询使用了全文检索索引；
(6)ref_or_null, 与ref类型类似，但MySQL额外进行了空值处理；如: SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL；
(7)index_merge, 对于同一个表，如果检索条件中的多个字段均使用了索引，则MySQL可能对多个索引列进行index merge优化；
(8)unique_subquery, 在in类型的子查询中，当子查询返回了拥有唯一索引(比如主键)的字段时，MySQL会使用该类型来提升当前查询的性能；
(9)index_subquery, 与unique_subquery类似，应用于返回的字段拥有普通索引；
(10)range, 查询语句只检索指定范围的行，当查询语句包含 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, 或 IN()时，可能触发该种访问类型；
(11)index, 全索引树检索，当查询的字段仅有部分拥有索引时，MySQL可能会触发该类访问。绝大多数情况下，扫描全索引树要比扫描全表更快，因为前者远小于后者；该访问类型分为两种情形：
a. 如果当前查询所需的列均包含在索引树中，所查询的数据需要扫描全树，此时type=index，Extra=Using index；
b. 如果当前查询按索引树的顺序进行全表扫描，此时type=index，Extra中没有Using index。
(12)ALL, 全表扫描。这是需要绝对避免的类型。
6. possible_keys: 当前查询MySQL可以使用的索引，但MySQL在具体执行查询过程中可能不会使用其中的全部或部分索引。如果值为NULL，说明本次查询没有可用的索引。
7. key: 当前查询MySQL实际使用的索引。
8. key_len: 当前查询所用索引的长度(字节)。可空字段的索引长度要长于非空字段的索引长度，所以字段最好设置为非空。
9. ref: 哪些列或常量被用来与"key"字段中的索引进行比较。
10. rows: 被检索的行数。对InnoDB存储引擎来讲，这是一个估计值。
11. filtered: 被过滤的行数占"rows"字段的比例。也是一个估计值。
12. Extra: 包含MySQL执行当前查询的额外信息。
