mysql性能优化（索引优化为主）

前言

经过几年的发展，公司系统累计的数据越来越多，部分表已经达到了百万级别，曾经的表结构设计随意，考虑不全面，导致系统部分功能已经出现了性能瓶颈。这种情况的长期出现轻则影响用户体验，降低产品质量和中心口碑；重则打击用户对系统的信任，影响公司发展。

由于我们的数据库使用的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，无法精准跳转到某一页，只能一页一页滑动