前言
经过几年的发展,公司系统累计的数据越来越多,部分表已经达到了百万级别,曾经的表结构设计随意,考虑不全面,导致系统部分功能已经出现了性能瓶颈。这种情况的长期出现轻则影响用户体验,降低产品质量和中心口碑;重则打击用户对系统的信任,影响公司发展。
由于我们的数据库使用的MYSQL数据库,所以针对它的一些性能优化大致方向
我们作为java后端程序员,在我们的能力范围内,我们要先对mysql有一个基础的认识,然后再从索引优化、SQL开发优化两个方向进行。
什么是MYSQL的存储引擎?
存储引擎就是存储数据、建立索引、更新 / 查询数据等技术的实现方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。
Mysql常见的存储引擎是 InnoDB 、MyISAM
MyISAM:
1. 不支持事务,不支持外键
2. 支持表,不支持行锁
3. 访问速度快
InnoDB:
1. DML 操作(增、删、改)遵循 ACID(事务安全表) 模型,支持事务。
2. 行级锁,提高并发访问性能。
3. 支持外键(FOREIGN KEY)约束,保证数据的完整性和正确性(但我们平时数据库表设计时已经很少建立真的外键,而是用逻辑外键)
InnoDB是我们主要使用的存储引擎,我们将重点介绍
InnoDB的逻辑存储结构(重点)
Row:行,存储引擎的数据是按行进行存放在页中,行与行之间通过单向链表连接
Page:页,InnoDb磁盘管理的最小单元,一页默认大小16KB,页与页之间通过双向链表连接。常见的类型:索引页、数据页、Undo页、系统页等
Extent:区,表空间的单元结构,每个大小1M,当默认每页16KB时,一共有64个页
Segment:段,
数据段(Leaf node segment):数据段存放着 B+ 树的叶子节点
索引段(Non-leaf node segment):索引段存放着 B+ 树的 非叶子节点
回滚段 (Rollback segment)
Tablespace:区,表空间是 InnoDB 存储引擎逻辑结构的最高层,用于存储记录、索引等数据。
一页是16KB,数据库每一行的数据都会填充到页里,直到填满
数据页生成流程
现在有张表,结构如下:
CREATE TABLE `gs_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) COMMENT '姓名',
`sex` int(1) COMMENT '性别:1-男;2-女;',
`amount` int(11) COMMENT '资产,单位:元',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
数据库有500条数据
第一页的数据填满(此处假设为100条数据占用16K,实际情况不一定)后,会生成第二页
当所有表数据加载完成后,示意图如下:
这是一个数据区
索引页生成流程
继续以我们的用户表为例,现在有了5个数据页,每个数据页都有自己的索引
Mysql又将所有索引都当成数据,制作成索引页,如下
(这里只是假设索引页只能存3个索引,实际上可以存很多)
索引页创建完成后,mysql还会创建根索引页,根页只有一个
B+树聚集索引(主键索引)
在没有普通索引,只有一个主键索引的时候,我们就这一棵树,所有select 都只有查这一棵树。
Where条件除了主键外,所有列都将做全表扫描
B+树非聚集索引(普通索引)
CREATE TABLE `gs_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL COMMENT '姓名',
`sex` int(1) DEFAULT NULL COMMENT '性别:1-男;2-女;',
`amount` int(11) DEFAULT NULL COMMENT '资产,单位:元',
PRIMARY KEY (`id`),
KEY `index_user_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=501 DEFAULT CHARSET=utf8mb4;
增加一个索引后`index_user_name` (`name`),就会新生成一颗B+树,如下
此时mysql为我们的表gs_user一共生成了2棵树,以后每新增一个索引,就会新增一棵树。每一棵树都有自己的
什么是二次查询(回表)?
Innodb 是以聚集索引的顺序来存储的,对于 Innodb 来说,普通索引在叶子节点中所保存的是行的主键信息,如果是用非聚集索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询(俗称回表)才能获取我们真实所需要的数据。而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询 ,减少了 IO 操作,提升了查询效率。
可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的,对于 IO 密集型的范围查找来说,对比随机从磁盘读取每一行的数据 IO 要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。
索引设计原则
1.索引数量合理
单张表中索引数量不超过5个
单个索引中的字段数不超过5个
每张表都有一个聚集索引(主键索引),默认生成第一棵B+树,以后每创建一个普通索引就会新增一个B+树;每次增删改数据mysql都会维护所有的B+树,所以如果索引太多,反而会影响效率。Mysql一张表理论上最大支持2000W记录
2.索引字段长度合理
CREATE TABLE `gs_company` (
`id` varchar(32) NOT NULL COMMENT '主键',
`create_date` varchar(32) DEFAULT NULL COMMENT '创建时间'
) 我们很多创建时间是varchar(32),其实yyyy-MM-dd HH:mm:ss只要19个字符,所以可以改成varchar(19)
3.最左前缀原则
Mysql会一直向右匹配直到遇到范围查询(大于、小于、between、like)
比如现在有一个索引 index(a,b,c,d)
使用where a=1 and b=2 and c>3 and d=4
只有a、b、c会用到索引、d无法使用索引
4.避免单列索引
禁止给表中的每一列都建立单独的索引
5.避免重复索引
创建索引index(a,b,c),相当于创建了index(a,b,c)、index(a,b)、index(a)
禁止重复索引示例:primary key(id)、index(id)、unique index(id)
6.索引覆盖
包含了所有查询字段 (where,select,order by,group by 包含的字段) 的索引
避免 Innodb 表进行索引的二次查询(避免回表)
7.尽量选高区分度作为前缀索引
区分度表示此列不重复的程度,越高越好
区分度 = count(distinct 列名)/count(*)
性别这种值只有1、2的列不能做索引列
SQL开发规范
1、EXPLAIN查看执行计划
理论上任何语句使用前都要查看执行计划
2、有排序需求的页面检查索引
排序列如果没有索引或者索引失效,会严重影响性能
3、索引列不参与计算
禁止出现 where id*5>10 where id+1>5
要转换成 where id>10/5 where id>5-1
4、禁用前缀模糊查询
where col %like% 会索引失效
where col like% 用后缀模糊
5、不使用select*
显示指定列 select col1 , col2 , col3
前端用什么字段返回什么字段,尽量走覆盖索引
6、避免 JOIN 关联太多的表
Join会产生笛卡尔积,如果数据量太大就影响性能
7、避免数据类型的隐式转换
隐式转换会导致索引失效如 Where amount = ‘100’ (amount 是int型)
8、慎用EXISTS、in
SELECT id,name
from table a -- a表先执行
where EXISTS ( select 1 from table b where xx=yy )
EXISTS适用外小内大(a表量小,b表量大)
SELECT id,name
from table a
where a.id in ( select id from table b -- b先执行 )
in适用外大内小(a表量大,b表量小)
用常见问题解析
百万级订单表的回表、文件排序
Playwork订单表结构如下
CREATE TABLE `gs_order` (
`id` varchar(32) NOT NULL COMMENT '主键,订单编号',
...... 其他字段省略
`out_create_date` varchar(19) DEFAULT NULL COMMENT '订单创建时间',
PRIMARY KEY (`id`),
KEY `index_go_out_create_date` (`out_create_date`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='主订单';
out_create_date 排序字段上有索引,索引上存的值是(out_create_date,id),而select返回字段如果刚好是 这2个字段,就没有回表操作,直接返回索引的数据就行了
耗时0.4秒
然后在select中加入其他字段
为了方便测试,这里强制使用索引 FORCE INDEX(index_go_out_create_date),由于需要select返回的字段不在非聚集索引里,所以需要二次回表,耗时4.6秒
索引查出来(id,out_create_date) 后,
mysq会做一个优化操作:把id按顺序排好,再到主键索引走去数据,将无序IO读取变成顺序IO读取
但是因为毕竟有所有数据都要回表操作,整体速度慢了很多,
(分页查询加上limit20只取前面还是很快的,因为只回表20条数据)
不使用索引
没有强制使用索引,虽然order by 是索引列,但还是走了ALL全表扫描,然后将所有数据做文件排序 耗时6.6秒
订单表(百万级) | 耗时 |
索引覆盖 | 0.4秒 |
索引+回表 | 4.6秒 |
全盘扫描+文件排序 | 6.6秒 |
深度分页问题
第一页速度很快(limit 0,20),0.02秒,但是查询后面的页数越来越慢(limit 800000,20),这是因为mysql工作原理就是先读取前面n条记录,然后抛弃前n条,读后面m条想要的,所以n越大,偏移量越大,性能就越差。
解决方案,最大最小值分页:
在前端数据浏览翻页,或者大数据分批导出等场景下,是可以将上一页的最大值或最小值当成参数作为查询条件的,sql如下,
第一页SQL,取出最顶部的20条数据
SELECT a.id,
...... 省略其的字段
a.out_create_date
FROM gs_order a
order by a.out_create_date desc
limit 20
第二页SQL,直接就跳过了
SELECT a.id,
...... 省略其的字段
a.out_create_date
FROM gs_order a
where out_create_date < '2024-01-05 09:17:32'
order by a.out_create_date desc
limit 20
(注意,第二页不是传统的limit 20,20 , 而是 limit 20 )
缺点1,就是out_create_date这个排序列如果有重复值,这种方法可能会丢失掉中间一些数据,所以必须保证 用来分页的字段是唯一值才有能保证准备性
缺点2,无法精准跳转到某一页,只能一页一页滑动