MySQL8.0性能优化（实践）

一台几年前的旧笔记本电脑的虚拟系统运行环境，作为本次实践的运行工具，仅供参考。

案例环境：Linux、Docker、MySQLCommunity8.0.31、InnoDB。

过早的MySQL版本不一定适用本章内容，仅围绕 InnoDB 引擎的阐述。

一、索引1.1 索引的管理

-- create 方式创建create [unique] index {index_name} on {tab_name}({col_name}[(length)]);-- alter表 方式创建alter {tab_name} add [unique] index {index_name} on ({col_name}[(length)]);-- 创建组合索引create index {index_name} on ({col_name1}[(length)], {col_name2}[(length)], {col_name3}[(length)]);-- unique：唯一索引-- col_name：一列为单列索引；逗号隔开的多列为组合索引-- length：字段中前几个字符有效，避免无限长度（通常能够明显区分值即可的长度；如：员工表的Email，@后面都一样）-- 查看表中的索引show index from {tab_name};-- 删除索引drop index {index_name} on {tab_name};

1.2 索引创建的场景

过多查询的表，过少写入的表。

数据量过大导致的查询效率慢。

经常作为条件查询的列。

批量的重复值，不适合创建索引；比如列

值过少重复的列，适合创建索引；比如、列

1.3 理想的索引特征

• 尽量能够覆盖常用字段
• 字段值区分度高
• 字段长度小（合适的长度，不是越小越好，至少能足够区分每个值）
• 相对低频的写入操作，以及高频的查询操作的表和字段上建立索引

通过非聚集索引检索记录的时候，需要2次操作，先在非聚集索引中检索出主键，然后再到聚集索引中检索出主键对应的记录，这个过程叫做回表，比聚集索引多了一次操作。

1.4 非主键索引

where全部为and时，无所谓位置，都会命中索引（当多个条件中有索引的时候，并且关系是and的时候，会自动匹配索引区分度高的）

where后面为 or 时，索引列 依影响数据范围越精确 按序靠前写。

1.5 索引的使用

使用原则：

• 按条件后面涉及到的列，创建出组合索引
• 越精确的条件，就排在条件的顺序首位，最左匹配原则

-- 按现有数据，计算哪个列最精确；越精确的列，位置越靠前优先。 select sum(depno=28), sum(username like 'Sol%'), sum(position='dev') from tab_emp; +---------------+---------------------------+---------------------+ | sum(depno=28) | sum(username like 'Sol%') | sum(position='dev') | +---------------+---------------------------+---------------------+ | 366551        | 3                         | 109                 | +---------------+---------------------------+---------------------+-- 由此得出：username列的范围最精确，应该放到where后的首位；不在组合索引的列放到最后。-- 如下组合索引的创建方式：create index {index_name} on {tab_name}(username,position,depno);-- 如下组合索引的查询方式：select username,position,depno from tab_emp where username like 'Sol%' and position='dev' and depno=106 and age<27

1.5.1 使用索引查询

这里准备两张两千万相同表数据，测试效果如下图：

1.5.2 组合索引的使用

表创建的组合索引，如下图：

两千万数据表，组合索引查询效果，如下图：

总结：组合索引所包含的列，尽量在where, order中写全，非索引列或过少的组合索引列可能不会产生索引效果。

1.5.3 高性能分页查询

通常MySQL分页时用到的limit，当limit值过大时，查询效果会很慢。

当如 limit 9000000,10 时，需要先查询出900万数据，再抛掉900万数据；这900万的过程可否省略？

假如：每次查询一页时，把当前页的最后一条数据的重要栏位都做记录，并标识是第几页；当查询它的下页时，拿它的最后一条数据的重要栏位作为追加的查询条件，如何呢…??

下图示例：usercode 为主要的索引及排序字段，上页的最后一条作为追加条件，再往下取5条，效果有了显著提升。(排序列重复数据呢?)当然适用于类似code、time等这样重复数据较少的列。

1.6 索引覆盖，避免回表查询

当查询的列中包含了非索引列，系统相当于扫描了两遍数据，如果能只扫描了一遍，也提高了查询效率。

回表查询的过程：

1. 先按已有索引查询到数据，得出此数据的主键值
2. 再按主键值，再次检索出具体的数据，获取其它列的值

查询涉及到的列都为组合索引列时，包括：select、where、order、group等，索引覆盖(索引下推)，避免回表查询。

避免使用*，以避免回表查询；不常用的查询列或text类型的列，尽量以单独的扩展表存放。

通常列表数据需要的列并不多，查询的时候可以考虑为索引列；通常详细信息时涵盖的列多，可通过主键单独查询。

1.7 命中索引1.7.1 无效索引

列类型转换可能会导致索引无效；如：

• 字符转数值，会导致索引无效
• 数值转字符，不影响索引。

不建议类型的转换，尽量按原类型查询。

条件中的函数导致索引无效；索引列不能用在函数内。如：where abs(Id) > 200

条件中的表达式导致索引无效；如：where (Id + 1) > 200

避免单列索引与组合索引的重复列；在组合索引中的列，去除单列索引。

全模糊查询导致索引无效；匹配开头不会影响索引，如 'Sol%'；全模糊或'%Sol'时无效。

1.7.2 Explain

显示执行过程，查看是否命中索引

explain select * from tab_emp where uname='Sol'-- 可能用到的索引、实际用到的索引、扫描了的行数+----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+| id | select_type | table   | type  | possible_keys | key           | key_len | ref   | rows | Extra                 |+----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+|  1 | SIMPLE      | tab_emp | range | idx_emp_uname | idx_emp_uname | 4       | const |    1 | Using index condition |+----+-------------+---------+-------+---------------+---------------+---------+-------+------+-----------------------+

在通常情况下，能不能命中索引，取决于索引列的值重复程度；如果是极少重复的值，就很容易命中索引。如果类似于状态或类型的值，重复程度很高，就很难命中索引，这是MySQL自动取舍的结果。

比如：没有索引的列-电话号码，有索引的列-部门，那么很难命中部门索引，因为MySQL认为[电话号码]更精确；或者使用force强行命中，通常MySQL的自动取舍是最有效的。

1.8 查询总结

避免使用*，以避免回表查询。

不常用的查询列或text类型的列，尽量以单独的扩展表存放。

条件避免使用函数。

条件避免过多的or，建议使用in()/union代替，in中的数据不可以极端海量，至少个数小于1000比较稳妥。

避免子查询，子查询的结果集是临时表不支持索引、或结果集过大、或重复扫描子表；以join代替子查询，尽量以inner join代替最为妥当。

避免使用'%Sol%'查询，或以'Sol%'代替。

二、表分区

表分区也就是把一张物理表的数据文件分成若干个数据文件存储，使得单个数据文件的量有限，有助于避免全表扫描数据，提升查询性能。

那，跨区查询的性能影响有多大，从整体看，表分区还是带来了不少的性能提升。

如果表中有主键列，分区列必须是主键列之一。比如：又有自增主键，又想按年份分区，那主键就是组合索引咯。(id+date)

2.1 分区的种类

HASH：按算法，平均分配到各分区

-- 表创建 HASH 分区12个CREATE TABLE clients (    id INT,    fname VARCHAR(30),    lname VARCHAR(30),    signed DATE)PARTITION BY HASH(MONTH(signed))PARTITIONS 12;

KEY：按算法，无序不等的分配到各分区

-- 表创建12个 KEY 分区CREATE TABLE clients_lk (    id INT,    fname VARCHAR(30),    lname VARCHAR(30),    signed DATE)PARTITION BY LINEAR KEY(signed)PARTITIONS 12;

RANGE：按划定的范围将数据存放到符合的分区

-- 按年份创建范围分区CREATE TABLE tr (    id INT,    name VARCHAR(50),    purchased DATE)PARTITION BY RANGE(YEAR(purchased)) (    PARTITION p0 VALUES LESS THAN (1990),    PARTITION p1 VALUES LESS THAN (1995),    PARTITION p2 VALUES LESS THAN (2000));

LIST：按定义的一组包含值将数据存放到符合的分区

-- LIST 分组包含方式CREATE TABLE tt (    id INT,    data INT)PARTITION BY LIST(data) (    PARTITION p0 VALUES IN (5, 10, 15),    PARTITION p1 VALUES IN (6, 12, 18));

2.2 分区的管理

新增 HASH/KEY 分区

-- 将原来的 12 个分区合并为 8 个分区ALTER TABLE clients COALESCE PARTITION 4;-- 在原有的基础上增加 6 个分区ALTER TABLE clients ADD PARTITION PARTITIONS 6;

新增 RANGE/LIST 分区

-- RANGE 追加分区ALTER TABLE tr ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));-- LIST 追加新分区（不可包含已存在的值）ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

变更 RANGE/LIST 分区

-- RANGE 拆分原有分区（重组分区）ALTER TABLE tr REORGANIZE PARTITION p0 INTO (        PARTITION n0 VALUES LESS THAN (1980),        PARTITION n1 VALUES LESS THAN (1990));-- RANGE 合并相邻分区ALTER TABLE tt REORGANIZE PARTITION s1,s2 INTO (    PARTITION s0 VALUES LESS THAN (1980));-- LIST 重组原有分区ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (    PARTITION p1 VALUES IN (6, 18),    PARTITION np VALUES in (4, 8, 12));

删除指定分区

-- 丢掉指定分区及其数据ALTER TABLE {TABLE_NAME} DROP PARTITION p2,p3;-- 删除指定分区，保留数据ALTER TABLE {TABLE_NAME} TRUNCATE PARTITION p2;-- 删除表全部分区，保留数据ALTER TABLE {TABLE_NAME} REMOVE PARTITIONING;

分区详细信息

-- 查询指定分区的数据SELECT * FROM tr PARTITION (p2);-- 查询各分区详细SELECT * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='tt';-- 查看某个分区的状态ALTER TABLE tr ANALYZE PARTITION p3;

修复分区

-- 检查分区是否损坏ALTER TABLE tr CHECK PARTITION p1;-- 修复分区ALTER TABLE tr REPAIR PARTITION p1, p2;-- 优化分区，整理分区碎片ALTER TABLE tr OPTIMIZE PARTITION p0, p1;-- 当前分区数据，重建分区ALTER TABLE tr REBUILD PARTITION p0, p1;

三、查询综合测试

2000万相同数据、相同表结构，相同的查询方式，测试效果如下图：(仅供参考)

数据量大了，查询慢；加索引了，数据量越大，写入越慢；

还是物理分表好呀~

四、SQL服务参数优化

仅列出了点官方认可的稳定性良好的可靠的参数，以 InnoDB 为主。

4.1 Connections

[mysqld]# 保持在缓存中的可用连接线程# default = -1（无）thread_cache_size = 16# 最大的连接线程数(关系型数据库)# default = 151max_connections = 1000# 最大的连接线程数(文档型/KV型)# default = 100#mysqlx_max_connections = 700

4.2 缓冲区 Buffer

[mysqld]# 缓冲区单位大小；default = 128Minnodb_buffer_pool_size = 128M# 缓冲区总大小，内存的70%，单位大小的倍数# default = 128Minnodb_buffer_pool_size = 6G# 以上两个参数的设定，MySQL会自动改变 innodb_buffer_pool_instances 的值

4.3 Sort merge passes

[mysqld]# 优化 order/group/distinct/join 的性能# SHOW GLOBAL STATUS 中的 Sort_merge_passes 过多就增加设置# default = 1Kmax_sort_length = 8K# default = 256Ksort_buffer_size = 2M# 通常别太大，海量join时大# default = 256K#join_buffer_size = 128M

4.4 I/O 线程数

[mysqld]# 异步I/O子系统# default = NOinnodb_use_native_aio = NO# 读数据线程数# default = 4innodb_read_io_threads = 32# 写入数据线程数# default = 4innodb_write_io_threads = 32

4.5 Capacity 容量

[mysqld]# default = 200innodb_io_capacity = 1000# default = 2000innodb_io_capacity_max = 2500# 数据日志容量值越大，恢复数据越慢# default = 100Minnodb_redo_log_capacity = 1G# 数据刷新到磁盘的方式# 有些同学说用 O_DSYNC 方式，在写入时，有很大提升。但官网说：# InnoDB does not use O_DSYNC directly because there have been problems with it on many varieties of Unix.# 也就是少部分系统可以使用，或者已经过确认。# 个人认为，默认值最可靠# innodb_flush_method = fsync

4.6 Open cache

[mysqld]# default = 5000open_files_limit = 10000# 计算公式：MAX((open_files_limit-10-max_connections)/2,400)# default = 4000table_open_cache = 4495# 超过16核的硬件，肯定要增加，以发挥出性能# default = 16table_open_cache_instances = 32

五、写入综合测试

测试目的：

经过【四、SQL服务参数优化】的配置后，分别测试空表状态批量写入200万和500万数据的耗时。

测试场景：

一台几年前的破笔记本，创建的虚拟机4C8G，Docker + MySQL8.0.31。

桌面应用以36个线程写入随机数据。

批量写入脚本：INSERT INTO TABLE ... VALUES (...),(...),(...) 的方式，INSERT 每次1000条。

表结构：聚集索引 + 两列的非聚集索引 + 一组三列的组合索引；(参照 1.5.2)

+------------+--------------+------+-----+-------------------+-------------------+| Field      | Type         | Null | Key | Default           | Extra             |+------------+--------------+------+-----+-------------------+-------------------+| id         | bigint       | NO   | PRI | NULL              | auto_increment    || usercode   | varchar(32)  | YES  | MUL | NULL              |                   || title      | varchar(128) | YES  |     | NULL              |                   || age        | int          | YES  | MUL | NULL              |                   || gender     | char(1)      | YES  |     | 男                |                   || phone      | char(11)     | YES  |     | NULL              |                   || job        | varchar(32)  | YES  |     | NULL              |                   || department | varchar(32)  | YES  |     | NULL              |                   || createtime | datetime     | NO   | PRI | CURRENT_TIMESTAMP | DEFAULT_GENERATED |+------------+--------------+------+-----+-------------------+-------------------+

测试结果：

逐步追加MySQL服务参数配置+表分区，最终有了成倍的性能提升；每次测试后的日志记录了优化的递进过程；
如下图：(日志不够细，懂就行)

经过逐步优化：

  200万数据写入耗时从 9分4秒，提升到 5分50秒；(无表分区)

  500万数据写入耗时从 41分33秒，提升到 6分50秒。(有表分区)

鄙人拙见，有不妥望指出，万分感谢。