首页  思维导图  详情

mysql性能调优

2023-01-07 00:16:08   0  举报





仅支持查看

AI智能生成

mysql性能调优

MySQL

作者其他创作

大纲/内容

索引

索引定义

帮助mysql高效获取数据的排好序的数据结构（查询快）

索引数据结构

二叉树

树的高度不好控制，如果遇到递增数据，相当于一个单链表

红黑树(平衡二叉树)

数据量大时，树的高度太高，查询效率就很低

Hash表

查询效率高，但只适合=，in等值查找，不能使用范围查找

对索引的key进行一次hash计算就可以定位出数据存储的位置

很多时候Hash索引要比B+ 树索引更高效

hash冲突问题

B树

叶子节点具有相同的高度，叶子节点的指针为空

所有索引元素不重复

节点中的数据从左至右依次递增

B+树

非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引

b+树所有的索引都为下一层叶子节点的第一个索引元素

叶子节点包含所有索引字段

叶子节点用指针双向连接，提高区间访问(范围查询)的性能

数据库索引常驻内存，查找效率高，耗时主要在于I/O操作

b树和b+树的区别

相同高度下，b+树比b树存放更多的数据；相同数据量下，b+树比b树高度更低，查找的速度更快

b+树叶子节点之间存在双向指针，b树没有

b+树有冗余索引，b树没有

b+树非叶子节点不存储data，b树存储非叶子节点存储data

数据结构实践网址

存储引擎索引实现

MyISAM

MyISAM索引文件和数据文件是分离的，分开存储(非聚集)

InnoDB

InnoDB索引实现(聚集索引)

表数据文件本身就是按B+Tree组织的一个索引结构文件

聚集索引-叶节点包含了完整的数据记录

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

为什么非主键索引结构叶子节点存储的是主键值？

索引类型

聚集索引/聚簇索引

索引和数据存放在一起

非聚集索引

索引位置只存放数据所在的内存地址，不存放数据

联合索引

多个字段一起作为索引，遵循索引最左前缀原理，排序时从第一个字段开始排序，第一个字段相同时才排序第二个字段

Explain工具

作用：使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈

常用命令

explain、explain extend、explain  partitions

shou warnings

explain输出的列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。 id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

select_type

表示对应行是简单还是复杂的查询

simple：简单查询，查询不包含子查询和union

primary：复杂查询中最外层的 select

subquery：包含在 select 中的子查询（不在 from 子句中）

derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

union：在 union 中的第二个和随后的 select

table

这一列表示 explain 的一行正在访问哪个表

当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询

当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id

type

跟sql的查询效率相关，这一列表示关联类型或访问类型

查询效率从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL， 一般来说，得保证查询达到range级别，最好达到ref

null：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

system、const：const即查询部分primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system即表里只有一条数据

eq_ref：primary key 或 unique key 索引的所有部分被【连接】使用 ，最多只会返回一条符合条件的记录。简单的 select 查询不会出现这种 type。

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。        简单查询时，使用普通索引等值常量查询；关联查询时，被关联表使用唯一性索引的一部分关联主表的某个字段。

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index：扫描整个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

all：扫描整个聚簇索引，即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

possible_keys

这一列显示查询可能使用哪些索引来查找

explain 时可能出现 possible_keys 有列，而 keys 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询

keys

这一列显示mysql实际使用了哪个索引来优化对该表的访问

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

这一列显示了mysql在索引里实际使用的字节数，通过这个值可以算出具体使用了索引中的哪些列

key_len计算规则

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

rows

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数

extra

这一列展示的是额外信息

Using index：使用覆盖索引

覆盖索引

定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中 获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个 查询结果只通过辅助索引就能拿到结果，不需要回表。即通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

如果查询的结果集在主键索引(聚簇索引)和辅助索引中都有，优先走二级索引，因为辅助索引通常更小

Using where：使用 where 语句来处理结果(没有使用到索引)，并且查询的列未被索引覆盖

Using index condition：查询条件用到了复合索引的一部分，范围查询，且查询的列未被索引覆盖

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。考虑使用索引来优化的。

Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

索引最佳实践

1.全值匹配

2.最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列（复合索引，当前面的索引列使用了范围查找，后面的索引列就不能再使用，因为不再有序）

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句（不用回表）

6.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

7.is null,is not null 一般情况下也无法使用索引

8.like以通配符开头（'$abc...'、'%kkk'）mysql索引失效会变成全表扫描操作

9.字符串不加单引号索引失效

10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化

11.范围查询优化

当possible_keys有值而keys为null。没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。 比如范围查询，可能是由于单次数据量查询过大导致优化器最终选择不走索引 优化方法：可以将大的范围拆分成多个小范围

索引使用总结

MySQL的内部组件结构

结构图

组件

连接器

作用：连接器负责跟navicat等客户端建立连接、获取权限、维持和管理连接

建立连接，连接命令：mysql ‐h host[数据库地址] ‐u root[用户] ‐p root[密码] ‐P 3306

获取权限：用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限

维持和管理连接：长链接默认关闭时间为8个小时

其他命令

问题：开发当中我们大多数时候用的都是长连接,把连接放在Pool内进行管理，但是长连接有些时候会导致 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。怎么解决这类问题呢？

查询缓存

mysql拿到查询请求后，先到查询缓存中查看是否有缓存，有的话就直接返回缓存结果

弊端：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

查询缓存开关query_cache_typ

使用查询缓存：SQL_CACHE关键字

运行的缓存信息：重点Qcache_hits 命中次数、Qcache_inserts 未命中插入的缓存次数

mysql8.0已经移除了查询缓存功能

词法分析器

sql语句分析步骤

1、词法分析

MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”

2、语法分析

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，比如关键字错误或缺少等

3、语义分析

4、构造执行树

5、生成执行计划

6、计划的执行

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序

执行器

先判断是否有执行权限，有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。

默认存储引擎是innoDB

bin-log归档

SQL执行时，会将sql语句的执行逻辑记录在我们的bin-log当中，因此误删也可以恢复数据

binlog是Server层实现的二进制日志,他会记录我们的cud操作

特点

1、Binlog在MySQL的Server层实现（引擎共用）

2、Binlog为逻辑日志,记录的是一条语句的原始逻辑

3、Binlog不限大小,追加写入,不会覆盖以前的日志

开启binlog功能

配置my.cnf文件

binlog命令

常用命令

恢复数据命令

Mysql索引优化实战

示例表

易错实例

1、联合索引第一个字段用范围可能不会走索引

2、强制走索引

3、覆盖索引优化

4、in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

5、like KK% 一般情况都会走索引

索引下推

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，如果第一个辅助索引字段条件是name like 'kk%'，只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

索引下推优化：可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。

索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引

为什么范围查找Mysql没有用索引下推优化？

Mysql如何选择合适的索引

trace工具

影响mysql性能，用时再开启，用完关闭

示例

重点信息

全表扫描成本计算

"table_scan": { --全表扫描情况 "rows": 10123, --扫描行数 "cost": 2054.7 --查询成本 }

辅助索引成本计算

"range_scan_alternatives": [                       {                         "index": "idx_name_age_position",                         "ranges": [                           "a < name"      --索引使用范围                         ] /* ranges */,                         "index_dives_for_eq_ranges": true,                         "rowid_ordered": false,    --使用该索引获取的记录是否按照主键排序                         "using_mrr": false,                         "index_only": false,       --是否使用覆盖索引                         "rows": 5061,              --索引扫描行数                         "cost": 6074.2,            --索引使用成本                         "chosen": false,           --是否选择该索引                         "cause": "cost"                       }                     ]

最终选择

"considered_access_paths": [   --最终选择的访问路径                     {                       "rows_to_scan": 10123,                       "access_type": "scan",     --访问类型：为scan，全表扫描                       "resulting_rows": 10123,                       "cost": 2052.6,                       "chosen": true,            --确定选择                       "use_tmp_table": true                     }                   ]

Order by与Group by优化

示例

EXPLAIN select * from emplovees where name = 'Lilei' and position = 'dev'order by age;

EXPLAIN select * from emplovees where name = 'Lilei' order by position;

EXPLAIN select * from emplovees where name = 'Lilei' order by age,position;

EXPLAIN select * from emplovees where name = 'Lilei' order by position,age;

EXPLAIN select * from emplovees where name = 'Lilei' and age = 18 order by position,age;

EXPLAIN select * from emplovees where name = 'Lilei' order by age asc, position desc;

EXPLAIN select * from emplovees where name in ('Lilei','zhuge') order by age , position;

EXPLAIN select * from emplovees where name > 'a' order by namec;

优化总结

1、MySQL支持两种方式的排序filesort和index，Using index是指扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。         1) order by语句使用索引最左前列。         2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。

where高于having，能写在where中的限定条件就不要去having限定了

Using filesort文件排序原理详解

单路排序

定义：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

trace工具查看sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >

双路排序(回表排序模式)

定义：首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；

trace工具可以看到sort_mode信息里显示< sort_key, rowid >

排序方式选择

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式

字段的总长度 < max_length_for_sort_data ，使用单路排序模式 字段的总长度 > max_length_for_sort_data ，使用双路排序模式

对比

对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段

通过调整max_length_for_sort_data的大小，可以让优化器选择使用不同的算法

索引设计原则

1、代码先行，索引后上

2、联合索引尽量覆盖条件

3、不要在小基数字段上建立索引

4、长字符串我们可以采用前缀索引

5、where与order by冲突时优先where

6、基于慢sql查询做优化

索引设计实战

分页查询优化

示例表

普通分页sql

mysql> select * from employees limit 10000,10; 表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。

常见的分页场景优化技巧

1、根据自增且连续的主键排序的分页查询

mysql> EXPLAIN select * from employees limit 90000,5; 优化：mysql> EXPLAIN select * from employees where id > 90000 limit 5;

2、根据非主键字段排序的分页查询

mysql> EXPLAIN select * from employees ORDER BY name limit 90000,5; 优化：mysql> EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

Join关联查询优化

示例表

mysql的表关联的两种算法

1、 嵌套循环连接  Nested-Loop Join(NLJ) 算法

一次一行循环地从驱动表中读取行，在这行数据中取到关联字段，根据关联字段在被驱动表里取出满足条件的行，然后取出两张表的结果合集。

mysql>EXPLAIN select * from t1 inner join t2 on t1.a= t2.a; Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ

适合被驱动表关联字段有索引的情况

2、 基于块的嵌套循环连接  Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比

mysql>EXPLAIN select * from t1 inner join t2 on t1.b= t2.b; Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法

适合被驱动表关联字段没有索引的情况

对于关联sql的优化

关联字段加索引

join操作时尽量选择NLJ算法，驱动表过滤的条件也尽量要走索引，避免全表扫描

小表驱动大表

select * from t2 straight_join t1 on t2.a = t1.a;  指定mysql选 t2 表作为驱动表

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤完成之后，数据量小的那个表，就是“小表”

in和exsits优化

原则：小表驱动大表，即小的数据集驱动大的数据集，数据量表小的表先执行

in：当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)   #等价于：   for(select id from B){       select * from A where A.id = B.id     }

exists：当A表的数据集小于B表的数据集时，exists优于in

select * from A where id in (select id from B)   #等价于：   for(select id from B){       select * from A where A.id = B.id     } #A表与B表的ID字段应建立索引

将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

count(*)查询优化

示例

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)  

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)  

count(1)>count(字段)，count(1)不需要取出字段统计，就用常量1做统计，count(字段)需要取出字段

count(*) 是例外，mysql并不会把全部字段取出来，做了优化不取值，按行累加，效率最高，不需要用count(列名)或count(常量)来替代 count(*)

常见优化方法

1、查询mysql自己维护的总行数

2、表总行数的估计值：show table status

3、将总数维护到Redis里

4、增加数据库计数表

MySQL数据类型选择

步骤

1. 确定合适的大类型：数字、字符串、时间、二进制

2. 确定具体的类型：有无符号、取值范围、变长定长等

3. 数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能和用更少的硬件资源。尽量把字段定义为NOT NULL，避免使用NULL

1、数值类型优化建议

如果整形数据没有负数，建议指定为无符号类型，容量可以扩大一倍

建议使用TINYINT代替ENUM、BITENUM、SET

不要用INT(10)类似的方法指定字段显示宽度，直接用INT

DECIMAL最适合保存准确度要求高，而且用于计算的数据。注意长度设置

* 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作

整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT

INT显示宽度：TINYINT(5)中5的作用就是，当需要在查询结果前填充0时，命令中加上ZEROFILL就可以实现

2、日期和时间

MySQL能存储的最小时间粒度为秒

使用DATE、TIME、DATETIME来存储时间，而不是使用字符串

当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认

TIMESTAMP是UTC时间戳，与时区相关

DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关

3、字符串

字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR

CHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合

尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联

BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关

BLOB和TEXT都不能有默认值

事务隔离级别和锁机制

事务

事务的ACID属性

原子性(Atomicity)

事务里面的修改，要么都执行，要么都不执行

一致性(Consisdent)

事务开始和结束时，数据必须保证一致性

隔离性(Isolation)

事务执行时，不能被别的事务的并发操作影响

持久化(Durable)

事务执行后更改的数据必须是永久性的，必须保存到磁盘上

并发事务带来的问题

更新丢失/脏写

后面的更新覆盖了前面其他事务的更新操作

脏读

读取到了其他事务已更新但未提交的数据，不符合一致性

不可重复读

事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

幻读

事务A读到了事务B新增的数据，不符合隔离性

事务隔离级别

分类

读未提交

读已提交

可重复读

可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）

可串行化

串行模式下innodb的查询也会被加上行锁

如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围(就算该行数据还未被插入也会加锁，这种是间隙锁)都会被加锁

数据库的事务隔离越严格,并发副作用越小,并发能力就越差,因为事务隔离实质上就是使事务在一定程度上“串行化”进行

常看当前数据库的事务隔离级别: show variables like 'tx_isolation'; 设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

Mysql默认的事务隔离级别是可重复读

数据库锁

定义

锁是计算机协调多个进程或线程并发访问某一资源的机制

锁分类

从性能上分

乐观锁

用版本对比来实现(如果更新数据时，发现数据的版本不一致，就不更新重新获取数据)

悲观锁

从数据操作的粒度分

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景

sql语句

加锁：lock table 表名称 read(write),表名称2 read(write);

查看锁：show open tables;

解锁：unlock tables;

行锁

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)

从对数据操作的类型分

读锁 (共享锁，S锁)

读锁会阻塞写，但是不会阻塞读

写锁 (排它锁，X锁)

写锁则会把读和写都阻塞

意向锁 (I锁，针对表锁)

mysql自己加的锁，用于提高加锁的效率

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁

InnoDB与MYISAM的最大不同有两点

InnoDB支持事务（TRANSACTION）

InnoDB支持行级锁

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙。间隙锁在某些情况下可以解决可重复读隔离级别的幻读问题

间隙锁是在可重复读隔离级别下才会生效

举例：比如表数据id有1,2,3,10,20，那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间。在事务中执行update account set name = 'zhuge' where id > 8 and id <18;  则其他事务没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在(3,20]区间都无法修改数据，注意最后那个20也是包含在内的。

临键锁(Next-key Locks)

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁

总结

可重复读级别下，无索引行锁会升级为表锁

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的所带来的性能损耗比表级锁定高一点，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的

行锁分析

分析工具：show status like 'innodb_row_lock%';

对各个状态量说明： Innodb_row_lock_current_waits: 当前正在等待锁定的数量 Innodb_row_lock_time: 等待总时间长 Innodb_row_lock_time_avg: 平均等待时长 Innodb_row_lock_time_max：最长等待时长 Innodb_row_lock_waits: 等待总次数

查看锁信息

查看事务：select * from INFORMATION_SCHEMA.INNODB_TRX;

查看锁：select * from INFORMATION_SCHEMA.INNODB_LOCKS;

查看锁等待：select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

释放锁：kill trx_mysql_thread_id;    锁线程id可以从INNODB_TRX表里查看到

查看锁等待详细信息：show engine innodb status\G;

死锁

死锁示例

查看近期死锁日志信息：show engine innodb status\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

锁优化建议

1.尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

2.合理设计索引，尽量缩小锁的范围

3.尽可能减少检索条件范围，避免间隙锁

4.尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

5.尽可能低级别事务隔离

MVCC多版本并发控制机制

定义

Mysql使用MVCC实现，在可重复读隔离级别下如何保证事务较高的隔离性

对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥

Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制

undo日志版本链与 read view机制详解

undo日志版本链

一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

trx_id：修改该数据的事务id，roll_pointer：指向上一次修改数据日志的指针

read-view

在可重复读级别，事务开启后，第一次执行任何一条查询语句时，会生成当前事务的一致性视图read-view，不会再改变

在读已提交级别，事务开启后，每一次执行任意查询语句时，都会生成事务的一致性视图read-view

read-view组成

由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成

事务里的任何sql查询结果需要从对应undo日志版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果

版本链对比规则

如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的

如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的

如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况：        a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见        b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见

注意

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据

Innodb引擎SQL执行的BufferPool缓存机制

写undo日志、redo日志，是顺序I/O，速度快；更新数据到磁盘是随机I/O，速度慢

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL？

1.更新表数据是随机I/O，如果来一个请求就直接对磁盘文件进行随机读写，更新磁盘文件里的数据性能可能相当差，不能让数据库抗住很高并发。

2.Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。

3.更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

执行步骤

1.加载缓存数据  加载id为1的记录所在的正整页数据到buffer pool中

2.写入更新数据的旧值到undo日志中，便于回滚

3.更新内存数据

4.写redo日志到redo-log buffer中

5.准备提交事务，讲redo日志写入磁盘

6.准备提交事务，将binlog日志写入磁盘，记录的是sql语句的执行逻辑

7.写入commit标记到redo日志文件里，提交事务完成，该标记为了保证事务提交后redo日志与binlog数据一致

InnoDB引擎底层存储和缓存原理

记录存储结构和索引页结构

InnoDB如何完成内存和磁盘数据交互

将数据划分未若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少吧内存中的16KB内容刷新到磁盘中

行格式

定义：用户操作一般是以记录为单位来向表中插入数据的，记录在磁盘上的存放方式称为行格式或者记录格式

行格式类型

Compact

Redunant（5.1之后不用了）

Dynamic

Dynamic 和 Compressed 行格式和Compact 行格式挺像，只不过在处理行溢出数据时有所不同

Compressed

Compressed 行格式和 Dynamic 不同的一点是，Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间

指定行格式命令

创建表指定行格式：create table test(id int,name text,...... ) row_format=dynamic;

修改表行格式：alter table test row_format=dynamic;

查看表行格式：show table status like '%xxx%'\G

示例表

Compact

记录的额外信息

变长字段长度列表

存储所有变长字段真实数据字节长度，各变长字段长度按逆序存放

实际存储

第一条数据：C1字段的‘aaaa‘十进制表示4，十六进制表示0x04 C2字段的‘bbb’十进制表示3，十六进制表示0x03 C4字段的‘d’ 十进制表示1，十六进制表示0x01 因为c3是char类型，不是变长字段。 所以第一条数据存入变长字段长度列表为：010304 同理，第二条数据存入变长字段长度列表为：0304

十六进制占一个字节，最大表示范围为0-255。如果可变长字段允许存储字段长度为W，分三种情况： 如果 W < 255 , 则1个字节就能存下 如果 W > 255 且真实存储的字节数 > 127, 使用2个字节 如果 W > 255 且 真实存储的字节数 < 127, 使用1个字节

注意：变长字段长度只存储非NULL的数据，若为null则不存储

CHAR(M)的存储

在compact行格式中，只会把变长类型的列存储在变长字段长度列表中，但这是因为我们采用的ascii字符集，该字符集定长字节。 如果采用变长字节的字符集，如gbk（1~2个字节），utf8(1~3)个字节，这时候CHAR(M)也会存储到变长字段字符集中

并且在存储数据的时候，至少占用M个字节，如char(10)在当前字段只存储一个字符串，也会占用10个字节。这是为了将来更新的值小于10个字节时候可以直接更新，而不需要重新开辟新的内存空间记录数据，防止之前的的数据产生空间碎片

NULL值列表

为了提高效率，记录中的null值统一存储在null列表里

步骤

1、首先是看看有哪些null字段，被not null修饰的字段都是不需要存储到null值列表的 2、如果表没有可以存储null的字段，则null值列表也不会存在，当前列表存储数据的时候，也是和变长字段长度列表一样，按逆序排列

实际存储

c1,c3,c4三个字段可以存储null，因为逆序排列，所以存储进去就是c4,c3,c1 Mysql规定null值列必须用整个字节位表示, 一个字节有8位, 因为表里有3个可以为null的字段, 所以剩下五位直接补0, 如果有9个可以为null的字段，需要两个字节来表示 第一条数据三个字段都有数据，所以为00000000转为十六进制就是0x00，第二条数据有c3和c4都为null，所以存储为00000110，转为十六进制就是0x06

示例表的中前两条数据，变长字段长度列表+NULL值列表存储的数据为：第一条：010304,00   第二条：0304,06

每个允许存储 NULL 的列对应一个二进制位，二进制位的值为1时，代表该列的值为NULL，为0时，代表该列的值不为NULL

记录头信息

由固定的 5 个字节组成。 40 个二进制位，不同的位代表不同的意思

组成

预留位1：1位，没有使用

预留位2：1位，没有使用

Delete_mask：1位，标记该记录是否被删除

Min_rec_mask：1位，B+树的每层非子叶节点中的最小记录都会添加该标记

N_owned：4位，表示当前记录拥有的记录数（待理解数据插入时槽的位置如何确定）

Heap_no：13位，表示当前记录在该页中的位置信息，插入记录时写入

Recode_type：3位，表示当前记录类型，0表示普通记录，1表示B+树非子叶节点记录，2表示最小记录，3表示最大记录

Next_record：16位，表示下一条记录相对位置（从当前记录的真实数据到下一条记录的真实数据的地址偏移量）

记录的真实数据

自定义的列的值，不为null的时候，记录的真实数据就存在这里

隐藏列

DB_ROW_ID(row_id)：非必须，6 字节，表示行 ID，唯一标识一条记录。如果表没有自定义主键，也没有唯一性索引，就会生成此列

DB_TRX_ID：必须，6 字节，表示事务 ID

DB_ROLL_PTR：必须，7 字节，表示回滚指针

实际存储

第一行数据存的是：row_id,transaction_id,roll_pointer,c1,c2,c3,c4 第二行数据存的是：row_id,transaction_id,roll_pointer,c1,c2

Dynamic

MySQL5.7 的默认行格式就是 Dynamic

数据溢出

一个 VARCHAR(M)类型的列就最多可以存储 65532 个字节，1页的大小位16KB，也就是16384字节

定义：如果我们定义一个表，表中只有一个VARCHAR(60000)字段，然后让字段中插入60000个字符，一页就放不了一条记录，这就叫数据溢出

如何处理数据溢出

Compact 和 Redundant 行格式

在记录的真实数据处只会存储该列的该列的前 768 个字节的数据，然后把剩余的数据分散存储在几个其他的页中，记录的真实数据处用 20 个字节存储指向这些页的地址。这个过程也叫做行溢出，存储超出 768 字节的那些页面也被称为溢出页

Dynamic 和 Compressed 行格式

不会在记录的真实数据处存储数据，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址

索引页格式

定义：页是InnoDB 管理存储空间的基本单位，一个页的大小一般是 16KB。存放表中记录的页称为索引页，也可以叫数据页

索引页结构

1.File Header：38个字节，文件头部，页的一些通用信息

File Header 针对各种类型的页都通用，占用固定的 38 个字节，它描述了一些针对各种页都通用的一些信息

存放比如当前页的校验和、页的类型、页的编号、它的上一个页、下一个页等

页的类型：Undo 日志页、段信息节点、Insert Buffer 空闲列表、Insert Buffer位图、系统页、事务系统数据、表空间头部信息、扩展描述页、溢出页、索引页等

2.Page Header：56个字节，页面头部，数据页(index页)专有的信息

记录了数据页中存储的记录的状态信息，比如本页已经存储多少数据，第一条记录的地址是什么，页里多少个槽点等

是页的第二部分，固定了56个字节，专门存储各种状态

3.Infimum+supremun：26个字节，最小记录和最大记录，两个虚拟行记录

4.User records：大小不确定(看存储的数据)，用户记录，实际存储的行记录内容

存入逻辑

存储的记录会按照我们指定的行格式存储到 User Records 部分，每个页中一开始并没有user records空间，每插入一条记录，会从Free Space部分申请一个记录大小的空间分配给user records存储数据，直到free space没有剩余空间，这时候就会申请新的页

删除逻辑

记录被删除时，则会修改记录头信息中的 delete_mask 为 1。所有被删除掉的记录都会组成一个垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能就会把这些被删除的记录占用的存储空间覆盖掉

插入的记录在会记录自己在本页中的位置，写入了记录头信息中heap_no 部分。heap_no 值为 0 和 1 的记录是 InnoDB 自动给每个页增加的两个记录，称为伪记录或者虚拟记录，heap=0代表最小记录，heap=1代表最大记录，被单独放在Infimum+supremun部分

记录链表

记录头信息中 next_record 记录了从当前记录的真实数据到下一条记录的真实数据的地址偏移量。这其实是个链表，可以通过一条记录找到它的下一条记录。下一条记录指得是按照主键值由小到大的顺序的下一条记录。

规定 Infimum记录（也就是最小记录heap_no=0） 的下一条记录就是本页中主键值最小的用户记录，而 本页中主键值最大的用户记录的下一条记录就是 Supremum 记录(也就是最小记录heap_no=1)

真实存储（待梳理学习）

示例表及数据

插入数据后user records部分 即多条数据记录格式

5.Free space：大小不确定，空间空间，页中尚未使用的剩余空间

6.Page directory：大小不确定，页面目录，页中某些记录对应的位置，地址值

主要解决记录链表的查找问题

页目录生成逻辑

1、将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组

2、每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned 属性表示该记录拥有多少条记录，也就是该组(槽)内共有几条记录

3、将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到Page Directory，也就是页目录页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的

4、每个分组中的记录条数是有规定的：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间

7. File Trailer：8个字节，文件尾部，校验当前页是否完整

所有页通用，可以分成 2 个小部分

前4个字节：页的校验和，和File Header中的校验和对应，如果File Header和File Trailer中的校验和不一致，说明页损坏

后 4 个字节：代表页面被最后修改时对应的日志序列位置（LSN），也和校验页的完整性有关

InnoDB 的体系结构

整体结构图（待画）

InnoDB 的表空间

独立表空间结构

系统表空间

InnoDB 的 Buffer Pool

InnoDB引擎底层事务原理

InnoDB如何实现事务ACID特性

事务的原子性是通过 undo log 来实现的，如果事务中间出现问题，可以通过undo log回滚数据

事务的持久性是通过 redo log 来实现的，如果出现意外状况导致数据未写入磁盘，可以通过redo log来修复数据

事务的隔离性是通过读/写锁+MVCC 来实现的，保证事务不会被别的事务的并发操作影响

事务四大特性的关系

C(一致性) 是最重要的，是目的

A(原子性)、I(隔离性)、D(持久性) 是手段，是数据库为了保证一致性提供的手段，同时还需要应用程序的支持

事务日志类型的实现

WAL预写机制（Mysql使用） Write-ahead logging

在使用 WAL 的系统中，所有的修改都先被写入到日志中，然后再被应用到系统中，通常包含 redo 和 undo 两部分信息

作用：如果一个系统直接将变更应用到系统状态中，那么在机器掉电重启之后系统需要知道操作是成功了，还是只有部分成功或者是失败了（为了恢复状态）。如果使用了 WAL，那么在重启之后系统可以通过比较日志和系统状态来决定是继续完成操作还是撤销操作

redo log

重做日志，每当有操作时，在数据变更之前将操作写入 redo log，这样系统Crash重启后修复数据，一致性

undo log

撤销日志，当一些变更执行到一半无法完成时，可以根据撤销日志恢复到变更之间的状态，原子性

Commit Logging（提交日志）

实现方式：只有在日志记录全部都安全落盘，数据库在日志中看到代表事务成功提交的“提交记录”(Commit Record)后，才会根据日志上的信息对真正的数据进行修改，修改完成后，再在日志中加入一条“结束记录”（End Record）表示事务已完成持久化

阿里的 OceanBase 则是使用的 Commit Logging来实现事务

Shadow Paging（影子分页）

实现方式：对数据的变动会写到硬盘的数据中，但并不是直接就地修改原先的数据，而是先将数据复制一份副本，保留原数据，修改副本数据。当事务成功提交，所有数据的修改都成功持久化之后，最后一步是去修改数据的引用指针，将引用从原数据改为新复制出来修改后的副本

原子性和持久性：“修改指针”被认为是原子操作，现代磁盘的写操作可以认为在硬件上保证了不会出现“改了半个值”的现象。

影子分页实现事务要比 Commit Logging 更加简单，但并发能力有限，所以高性能数据库应用不多

常用的轻量级数据库 SQLite Version 3 采用的事务机制Shadow Paging

redo 日志

为什么需要redo日志

对于一个已提交的事务，在事务提交后即使系统发生了崩溃，这个事务对数据库中所做的更改也不能丢失，如何保证持久性？

解决

1.在事务提交完成之前就把改事务所修改的所有页面都刷新到磁盘

问题

1.刷新一个完整的数据页太浪费了

2.随机IO速度慢，并发效率低

2.只记录页面上修改了的数据，如果系统崩溃重启，只需要将该事务执行过程中产生的 redo 日志刷新到磁盘 记录：将第 0 号表空间的 100 号页面的偏移量为 1000 处的值更新为 2

优点

1.redo日志占用的空间非常小

2.redo日志是顺序写入磁盘的

redo 日志格式

通用结构

type：该条 redo 日志的类型，redo 日志设计大约有 53 种不同的类型日志

space ID：表空间 ID

page number：页号

data：该条 redo 日志的具体内容

redo日志（物理日志）：记录一下在某个页面的某个偏移量处修改了几个字节的值以及具体被修改的内容

redo日志类型(根据页面中写入数据的多少划分)

MLOG_1BYTE（type 字段对应的十进制数字为 1）：表示在页面的某个偏移量处写入 1 个字节的 redo 日志类型

MLOG_2BYTE（type 字段对应的十进制数字为 2）：表示在页面的某个偏移量处写入 2 个字节的 redo 日志类型

MLOG_4BYTE（type 字段对应的十进制数字为 4）：表示在页面的某个偏移量处写入 4 个字节的 redo 日志类型

MLOG_8BYTE（type 字段对应的十进制数字为 8）：表示在页面的某个偏移量处写入 8 个字节的 redo 日志类型

MLOG_WRITE_STRING（type 字段对应的十进制数字为 30）：表示在页面的某个偏移量处写入一串数据

服务器内存全局变量

row_id

每用一次自增1，每当变量为256倍数时，将值更新到系统表空间的 Max Row ID 属性中

当系统启动时，会将系统表空间的 Max Row ID 属性加载到内存中，将该值加上256 之后赋值给全局变量

简单redo日志

Max Row ID 属性占用的存储空间是 8 个字节, 在修改系统表空间的Max Row ID时，会生成一条类型为MLOG_8BYTE 的 redo 日志 offset 代表在页面中的偏移量

复杂一些的 redo 日志类型

有时候执行一条语句会修改非常多的页面，包括系统数据页面和用户数据页面（用户数据指的就是聚簇索引和二级索引对应的 B+树）

表中包含多少个索引，一条 INSERT 语句就可能更新多少棵 B+树

针对某一棵 B+树来说，既可能更新叶子节点页面，也可能更新非叶子节点页面，也可能创建新的页面(叶子节点分裂、非叶子节点添加索引)

File Header、Page Header、Page Directory等等也会更新

当页被修改的地方非常多时，怎么记录redo日志

方法1：在每个修改的地方都记录一条 redo 日志。缺点：被修改的地方太多，redo日志太多

方法2：将整个页面的第一个被修改的字节到最后一个修改的字节之间所有的数据当成是一条物理 redo 日志中的具体数据。缺点：会将很多没有修改的数据页加入到redo日志中，很浪费

方法3：增加很多种redo日志类型来记录

复杂redo日志

既包含物理层面的意思，也包含逻辑层面的意思

物理层面看，这些日志都指明了对哪个表空间的哪个页进行了修改

逻辑层面看，在系统崩溃重启时，需要调用一些事先准备好的函数，执行完这些函数后才可以将页面恢复成系统崩溃前的样子

一个 redo 日志类型而只是把在本页面中变动（比如插入、修改）一条记录所有必备的要素记了下来，之后系统崩溃重启时，服务器会调用相关向某个页面变动（比如插入、修改）一条记录的那个函数，而 redo 日志中的那些数据就可以被当成是调用这个函数所需的参数，在调用完该函数后，页面中的相关值也就都被恢复到系统崩溃前的样子了。这就是所谓的逻辑日志的意思

redo日志的写入过程

log buffer redo日志缓冲区 (redo log buffer)

log buffer被划分成若干个连续的redo log block，我们可以通过启动参数 innodb_log_buffer_size 来指定 log buffer的大小，该启动参数的默认值为 16MB

redo log black 大小为512字节，向log buffer中写redo 日志也是顺序的，先往前面的block中写，写完之后再往下一个block中写

Mysql8.0新增特性详解