MySql千万级表性能优化

近期在处理一个千万级表数据的优化措施与总结（不考虑硬件方面）：

1）数据库做主从，读写分离

2）表结构把不常用的字段列移入附加表

3）查询语句优化，避免全表扫描，、

4）按需合理增加索引

5）做分区

6）业务逻辑优化、合理使用缓存

7）表拆分（比如把历史数据按年拆分出去）

• • 读写分离（主从）

数据库做主从，主负责写，从负责读

• • 表结构

设计遵循范式，把不常用的字段列移入附加表

• • 分区

  查看当前MySQL版本是否支持分区操作：SHOW PLUGINS;

目前MySQL支持分区类型：RANGE分区，LIST分区，HASH分区，KEY分区

注：如果表存在主键或唯一索引时，分区列字段必须包含在此索引中

1. RANGE

连续区间的分区 - 基于属于一个给定连续区间的列值，把多行分配给分区；主要用于日期列的分区，优化器只能对YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()这类函数进行优化选择；

alter table game_order PARTITION BY RANGE(YEAR(settle_time))

(

PARTITION p0 VALUES LESS THEN (2019) ENGINE = InnoDB,

PARTITION p1 VALUES LESS THEN (2020) ENGINE = InnoDB,

PARTITION p2 VALUES LESS THEN (2021) ENGINE = InnoDB,

PARTITION p3 VALUES LESS THEN (2022) ENGINE = InnoDB,

PARTITION p4 VALUES LESS THEN (MAXVALUE) ENGINE = InnoDB

 );

1. LIST

离散区间的分区 - 类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择，不是连续的，使用VALUES IN；

alter table game_order PARTITION BY LIST (game_type)

(

PARTITION p0 VALUES IN (1,2) ENGINE = InnoDB,

PARTITION p1 VALUES IN (3,4) ENGINE = InnoDB,

PARTITION p2 VALUES IN (5,6) ENGINE = InnoDB,

PARTITION p3 VALUES IN (7,8) ENGINE = InnoDB

 );

1. HASH

平均分区       - 基于用户定义的表达式的返回值来进行选择的分区，将数据平均分布到定义的分区中，保证每个分区的数据大致相同；

alter table game_order PARTITION BY HASH(game_type)

  PARTITIONS 8;

1. KEY  

Key分区 - 类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数；

alter table game_order PARTITION BY KEY(game_type)

  PARTITIONS 8;

 查看当前语句命中什么分区：EXPLAIN PARTITIONS 查询语句

字段相关在下栏索引中提及

• • 索引

了解索引前，先了解一下EXPLAIN  相关字段说明

参考资料：MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

• • 查询语句
  • 参考地址MySQL :: MySQL 5.7 Reference Manual :: 8.2.1 Optimizing SELECT Statements