一台几年前的旧笔记本电脑的虚拟系统运行环境,作为本次实践的运行工具,仅供参考。
案例环境: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 索引创建的场景
过多查询的表,过少写入的表。
数据量过大导致的查询效率慢。
经常作为条件查询的列。
批量的重复值,不适合创建索引;比如<业务状态>列
值过少重复的列,适合创建索引;比如<usercode>、<email>列
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 索引覆盖,避免回表查询
当查询的列中包含了非索引列,系统相当于扫描了两遍数据,如果能只扫描了一遍,也提高了查询效率。
回表查询的过程:
- 先按已有索引查询到数据,得出此数据的主键值
- 再按主键值,再次检索出具体的数据,获取其它列的值
查询涉及到的列都为组合索引列时,包括: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 = 151
max_connections = 1000
# 最大的连接线程数(文档型/KV型)
# default = 100
#mysqlx_max_connections = 700
4.2 缓冲区 Buffer
[mysqld]
# 缓冲区单位大小;default = 128M
innodb_buffer_pool_size = 128M
# 缓冲区总大小,内存的70%,单位大小的倍数
# default = 128M
innodb_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 = 1K
max_sort_length = 8K
# default = 256K
sort_buffer_size = 2M
# 通常别太大,海量join时大
# default = 256K
#join_buffer_size = 128M
4.4 I/O 线程数
[mysqld]
# 异步I/O子系统
# default = NO
innodb_use_native_aio = NO
# 读数据线程数
# default = 4
innodb_read_io_threads = 32
# 写入数据线程数
# default = 4
innodb_write_io_threads = 32
4.5 Capacity 容量
[mysqld]
# default = 200
innodb_io_capacity = 1000
# default = 2000
innodb_io_capacity_max = 2500
# 数据日志容量值越大,恢复数据越慢
# default = 100M
innodb_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 = 5000
open_files_limit = 10000
# 计算公式:MAX((open_files_limit-10-max_connections)/2,400)
# default = 4000
table_open_cache = 4495
# 超过16核的硬件,肯定要增加,以发挥出性能
# default = 16
table_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秒。(有表分区)