1、索引(重点)

说说你对 MySQL 索引的理解？

数据库索引的原理,为什么要用 B+树,为什么不用二叉树？

聚集索引与非聚集索引的区别？

InnoDB引擎中的索引策略,了解过吗？

创建索引的方式有哪些？

聚簇索引/非聚簇索引,mysql索引底层实现,为什么不用B-tree,为什么不用hash,叶子结点存放的是数据还是指向数据的内存地址,使用索引需要注意的几个地方？

什么是索引

• MYSQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构,所以说索引的本质是:数据结构
• 索引的目的在于提高查询效率,可以类比字典、 火车站的车次表、图书的目录等 。
• 可以简单的理解为“排好序的快速查找数据结构”,数据本身之外,数据库还维护者一个满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

下图是一种可能的索引方式示例。

左边的数据表,一共有两列七条记录,最左边的是数据记录的物理地址

为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值,和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到对应的数据,从而快速检索出符合条件的记录。

索引本身也很大,不可能全部存储在内存中,一般以索引文件的形式存储在磁盘上

平常说的索引,没有特别指明的话,就是B+树(多路搜索树,不一定是二叉树)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。此外还有哈希索引等。

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。索引的一个主要目的就是加快检索表中数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

创建索引

创建:

• 创建索引:CREATE [UNIQUE] INDEX indexName ON mytable(username(length));

  • 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。

• 修改表结构(添加索引):ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)

• 删除:DROP INDEX [indexName] ON mytable;

• 查看:SHOW INDEX FROM table_name\G,可以通过添加\G 来格式化输出信息。

使用ALERT命令:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通索引,索引值可出现多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)该语句指定了索引为 FULLTEXT ,用于全文索引。,

1. 直接创建索引和间接创建索引

   • 直接创建索引: CREATE INDEX mycolumn_index ON mytable (myclumn),直接在某一张表的列上面建立索引。
   • 间接创建索引: 定义主键约束或者唯一性键约束,可以间接创建索引。

2. 普通索引和唯一性索引

   • 普通索引:CREATE INDEX mycolumn_index ON mytable (myclumn)
   • 唯一性索引:保证在索引列中的全部数据是唯一的,对聚簇索引和非聚簇索引都可以使用CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

3. 单个索引和复合索引

   • 单个索引:即非复合索引,通常使用一个字段创建索引。
   • 复合索引:又叫组合索引,在索引建立语句中同时包含多个字段名,最多16个字段CREATE INDEX name_index ON username(firstname,lastname)

4. 聚簇索引和非聚簇索引(聚集索引,群集索引)

   • 聚簇索引:物理索引,与基表的物理顺序相同,数据值的顺序总是按照顺序排列CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH ALLOW_DUP_ROW(允许有重复记录的聚簇索引)

   • 非聚簇索引:CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

索引的优缺点

索引的特点:

1. 索引可以加快数据库的检索速度
2. 索引降低了数据库插入、修改、删除等维护任务的速度
3. 索引创建在表上,不能创建在视图上
4. 索引既可以直接创建,也可以间接创建
5. 可以在优化隐藏中,使用索引

优点:

• 大大加快数据检索的速度,这个是建立索引的主要原因。
• 创建唯一性索引,保证数据库表中每一行数据的唯一性
• 将随机I/O变成顺序I/O(因为B+树的叶子节点是连接在一起的)
• 加速表与表之间的连接
• 索引降低了数据库插入、修改、删除等维护任务的速度、
• 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
• 索引创建在表上,不能创建在视图上
• 索引既可以直接创建,也可以间接创建。

总之建立索引就是为了提高数据库的性能。

缺点:

1. 从时间角度来考虑:建立索引和维护索引都需要时间开销,会影响数据库的性能,特别是随着数据量增加的时候。
2. 从空间考虑:索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大
3. 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度

索引分类

从数据结构角度

• B+树索引
• Hash索引
• Full-Text全文索引
• R-Tree索引

从物理存储角度

• 聚集索引(clustered index)
• 非聚集索引(non-clustered index),也叫辅助索引(secondary index)聚集索引和非聚集索引都是B+树结构

从逻辑角度

• 主键索引:主键索引是一种特殊的唯一索引,不允许有空值,在 MySQL 的 InnoDB 的表中,当没有显示的指定表的主键时,InnoDB 会自动先检查表中是否有唯一索引的字段,如果有,则选择该字段为默认的主键,否则 InnoDB 将会自动创建一个 6Byte 的自增主键。
• 普通索引(Index) ,单列索引:普通索引的唯一作用就是为了快速查询数据,一张表允许创建多个普通索引,并允许数据重复和 NULL。
• 多列索引(复合索引、联合索引):复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合
• 唯一索引(Unique Key) :唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据,但是允许数据为 NULL,一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性,而不是为了查询效率。
• 前缀索引(Prefix) :前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引,相比普通索引建立的数据更小,因为只取前几个字符。
• 全文索引(Full Text) :全文索引主要是为了检索大文本数据中的关键字的信息,是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引,5.6 之后 InnoDB 也支持了全文索引。

1、为什么MySQL 索引中用B+tree,不用B-tree 或者其他树,为什么不用 Hash 索引

2、聚簇索引/非聚簇索引是什么?

3、MySQL 索引底层实现,叶子结点存放的是数据还是指向数据的内存地址,使用索引需要注意的几个地方？

4、使用索引查询一定能提高查询的性能吗？为什么?

2、什么是聚簇索引,什么是非聚簇索引？

聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

• 聚簇索引:将数据和索引放到一起存储,索引结构的叶子节点保留了数据行，索引即数据，数据即索引；
• 非聚簇索引:将数据进和索引分开存储,索引叶子节点存储的是指向数据行的地址。

在InnoDB存储引擎中,默认的索引为B+树索引,利用主键创建的索引为主索引,也是聚簇索引,在主索引之上创建的索引为辅助索引,也是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢,因为辅助索引中的叶子节点存储的是主键。

在MyISAM存储引擎中,默认的索引也是B+树索引,但主索引和辅助索引都是非聚簇索引,也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。可以从非常经典的两张图看看它们的区别(图片来源于网络):

在MyISAM引擎中,不管使用的是主键索引,还是非主键索引,那么叶子节点中存储的都是数据的偏移量,所以根据这个偏移量,可以直接取查找数据,但是在Innodb中,如果使用的是非主键索引,那么还需要定位主键索引才能够找到数据。

物理存储结构对比

聚集索引（InnoDB主键索引）

物理文件：InnoDB表对应两个文件

• .frm：表结构定义
• .ibd：数据和索引（聚集索引和数据一起存储）

非聚集索引（MyISAM索引）

物理文件：MyISAM表对应三个文件

• .frm：表结构定义
• .MYD：数据文件
• .MYI：索引文件

聚集索引优缺点

优点 聚集索引的查询速度非常的快,因为整个 B+树本身就是一颗多叉平衡树,叶子节点也都是有序的,定位到索引的节点,就相当于定位到了数据。

缺点

1. 依赖于有序的数据 :因为 B+树是多路平衡树,如果索引的数据不是有序的,那么就需要在插入时排序,如果数据是整型还好,否则类似于字符串或 UUID 这种又长又难比较的数据,插入或查找的速度肯定比较慢。
2. 更新代价大 : 如果对索引列的数据被修改时,那么对应的索引也将会被修改,而且况聚集索引的叶子节点还存放着数据,修改代价肯定是较大的,所以对于主键索引来说,主键一般都是不可被修改的。

非聚集索引优缺点

优点：

更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了,非聚集索引的叶子节点是不存放数据的

缺点:

1. 跟聚集索引一样,非聚集索引也依赖于有序的数据
2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。

这是 MySQL 的表的文件截图:

聚集索引和非聚集索引:

查询过程

聚集索引查询

-- 查询条件使用主键
SELECT * FROM users WHERE id = 5;

非聚集索引查询（InnoDB二级索引）

-- 查询条件使用非索引列
SELECT * FROM users WHERE name = 'Tom';

MyISAM非聚集索引查询

-- MyISAM表
SELECT * FROM users_myisam WHERE name = 'Tom';

索引覆盖优化

-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 查询1: 需要回表
SELECT * FROM users WHERE name = 'Tom';
-- 二级索引找到主键 → 回表查所有列

-- 查询2: 索引覆盖
SELECT name, age FROM users WHERE name = 'Tom';
-- Extra: Using index
-- ✅ 直接从二级索引返回数据，无需回表

-- 查询3: 部分覆盖
SELECT name, id FROM users WHERE name = 'Tom';
-- id是主键，也存储在二级索引中
-- ✅ 无需回表

二级索引叶子节点内容：

• 索引列的值
• 主键值（用于回表）

性能对比

操作类型	聚集索引	非聚集索引
主键等值查询	⚡ 极快（一次查找）	较快（索引+可能回表）
主键范围查询	⚡ 极快（数据连续）	较快（索引有序）
二级索引查询	需要回表	视情况（可能回表）
插入	较慢（数据重排）	较快（追加数据文件）
更新主键	❌ 非常慢（移动数据）	不允许
更新非索引列	较快	较快

索引组织表 vs 堆表

特性	InnoDB（聚集索引）	MyISAM（堆表）
数据存储	按主键顺序存储	按插入顺序存储
主键索引	聚集索引，包含整行	非聚集索引，指向数据行
二级索引	存储主键值	存储行指针
插入性能	可能触发页分裂	追加写入，性能好
更新主键	非常慢（需移动数据）	不支持（主键不可变）
碎片问题	需要定期OPTIMIZE	需要定期OPTIMIZE
空间利用率	可能有空洞	顺序写入，利用率高

小结

维度	聚集索引	非聚集索引
数据结构	B+树	B+树
叶子节点	存储整行数据	存储指向数据的指针（主键或行地址）
数据有序性	物理有序	逻辑有序
每表数量	只能1个	可以多个
查找过程	一次索引查找	通常需要两次查找（回表）
回表操作	不需要	可能需要
索引覆盖	总是覆盖	可实现覆盖索引
插入性能	较慢（可能页分裂）	较快（追加）
主键更新	非常慢	不支持（主键不能变）
适用场景	主键查询、范围扫描	各种查询条件
典型引擎	InnoDB	MyISAM, InnoDB二级索引

聚集索引让数据和索引在一起，一次查找就能获取全部；非聚集索引是数据的目录，找到条目后还需要按图索骥找到实际数据。

3、Mysql索引实现原理

首先要明白索引(index)是在存储引擎(storage engine)层面实现的,而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型,它们的实现和行为也可能有所差别。

B+Tree索引

MyISAM 和 InnoDB 存储引擎,都使用 B+Tree的数据结构,它相对与 B-Tree结构,所有的数据都存放在叶子节点上,且把叶子节点通过指针连接到一起,形成了一条数据链表,以加快相邻数据的检索效率。

先了解下 B-Tree 和 B+Tree 的区别

B-Tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。

InnoDB 存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB,可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K,在 MySQL 中可通过如下命令查看页的大小:show variables like 'innodb_page_size';

而系统一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数,提高查询效率。

B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述 B-Tree,首先定义一条记录为一个二元组[key, data] ,key为记录的键值,对应表中的主键值,data 为一行记录中除主键外的数据。对于不同的记录,key值互不相同。

一棵m阶的B-Tree有如下特性:

1. 每个节点最多有m个孩子。
2. 除了根节点和叶子节点外,其它每个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点,则至少有2个孩子
4. 所有叶子节点都在同一层,且不包含其它关键字信息
5. 每个非终端节点包含n个关键字信息(P0,P1,…Pn, k1,…kn)
6. 关键字的个数n满足:ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)为关键字,且关键字升序排序
8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki,但都大于k(i-1)

B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个 3 阶的 B-Tree:

每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例,关键字为17和35,P1指针指向的子树的数据范围为小于17,P2指针指向的子树的数据范围为17~35,P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程:

1. 根据根节点找到磁盘块1,读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间(17,35),找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3,读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间(26,30),找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8,读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程,发现需要3次磁盘I/O操作,和3次内存查找操作。由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数,使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率。

B+Tree

B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构,InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同:

1. 非叶子节点只存储键值信息;
2. 所有叶子节点之间都有一个链指针;
3. 数据记录都存放在叶子节点中

将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:

通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

可能上面例子中只有22条数据记录,看不出B+Tree的优点,下面做一个推算:

InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为10^{3)。也就是说一个深度为3的B+Tree索引可以维护10}3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

B+Tree性质

1. 通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。
2. 当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

核心实现原理

MySQL索引的实现原理，核心可以概括为：在存储引擎层，以B+树作为主要数据结构，通过高效的查找、排序和索引间协作机制，来大幅提升数据检索速度。

下面我将从数据结构、不同存储引擎的实现差异，以及索引的协同工作方式这三个方面，为你详细拆解。

核心数据结构：B+树

MySQL的索引主要在存储引擎层实现，其中最主流、最核心的数据结构就是B+树。InnoDB和MyISAM引擎都使用它，但在具体实现上有所不同。

为什么是B+树？

数据库索引通常存储在磁盘上，数据量庞大无法一次载入内存。因此，索引设计的首要目标是减少磁盘I/O次数。B+树因其独特设计成为最佳选择：

• 对比二叉树：在数据量大的情况下（如100万），二叉树树高可达20层，意味着查找一个数据最多需要20次磁盘I/O。而B+树是多叉树，在MySQL InnoDB中一个节点默认16KB，若存储整数索引（如bigint 8字节），一个节点可存放约 1170个 键值。因此，一个树高为3的B+树就能存放约1170×1170×16 ≈ 2000万条数据，查找只需3次I/O，效率极高。
• 对比B树：B+树的非叶子节点只存储键值，不存储数据，这使得它能在一个节点内容纳更多键值，进一步降低了树的高度和I/O次数。同时，B+树的叶子节点通过指针形成有序链表，非常适合进行范围查询（如 BETWEEN, >, <）和排序。
• 对比哈希索引：哈希索引虽然等值查询（=）极快（时间复杂度O(1)），但它是无序的，完全不支持范围查询和排序。因此，哈希只适用于特定场景（如Memory引擎），而B+树是通用的选择。

下图清晰地展示了B+树的这种结构：

两大存储引擎的索引实现

MySQL最常用的InnoDB和MyISAM引擎，虽然底层都用了B+树，但数据的组织方式截然不同，这也决定了它们各自的性能特点。

InnoDB引擎：聚集索引

InnoDB是默认存储引擎，其数据文件本身就是按B+树组织的一个索引结构，这就是聚集索引。

• 数据即索引：在InnoDB中，表数据文件（.ibd）就是主键索引。其叶子节点直接存储了整行数据。
• 只有一个聚集索引：由于数据只能有一份物理排序方式，因此一张表只能有一个聚集索引。其生成规则如下：
  1. 优先使用用户定义的主键（PRIMARY KEY）。
  2. 若无主键，则选择第一个唯一非空索引（UNIQUE NOT NULL）。
  3. 若两者皆无，InnoDB会自动生成一个6字节的隐藏ROWID作为聚集索引。
• 二级索引（辅助索引）：除主键外的其他索引都是二级索引。其叶子节点存储的不是数据本身，而是行的主键值。因此，通过二级索引查找数据需要回表：先在二级索引找到主键ID，再到聚集索引中根据ID查找完整数据。

MyISAM引擎：非聚集索引

MyISAM是5.5版本前的默认引擎，它的索引实现是典型的非聚集索引。

• 索引与数据分离：MyISAM的索引文件（.MYI）和数据文件（.MYD）是分开存储的。
• 索引指向数据地址：无论主键索引还是二级索引，其B+树叶子节点存储的都是指向数据行的磁盘地址。因此，在MyISAM中，通过任何索引查找数据，都需要根据这个地址去数据文件中读取，不需要“回表”的概念，但所有索引的查找路径都是一样的。

索引的协同工作与高效查询

了解了基本结构，我们来看一个查询在InnoDB中是如何利用索引工作的。

• 查询过程（以二级索引为例） 假设执行 SELECT * FROM users WHERE name = 'Tom';，其过程如下：

  1. 在name字段的二级索引B+树中，查找键值Tom。
  2. 从二级索引的叶子节点获取到对应行的主键ID。
  3. 拿着这个主键ID，到主键的聚集索引B+树中查找。
  4. 在聚集索引的叶子节点获取到完整的行数据，返回结果。

• 关键优化机制

  • 覆盖索引：如果一个索引（通常是联合索引）包含了查询所需的所有字段，那么查询时无需回表，直接从索引就能获得结果。这是一种非常重要的查询优化手段，可以大幅提升查询速度。
  • 索引下推：这是MySQL 5.6引入的优化。当使用联合索引查询时，可以在存储引擎层直接对索引中包含的字段进行过滤，只将真正符合条件的结果返回给服务器层，从而减少回表次数和服务器层与存储引擎层的数据交互。

总结

维度	核心要点	关键设计/优化
数据结构	使用B+树，通过降低树高减少磁盘I/O，并支持高效的范围查询与排序。	非叶子节点仅存键值，叶子节点通过指针形成有序链表。
InnoDB实现	聚集索引：数据即主键索引，叶子节点存整行数据；二级索引叶子节点存主键值。	表必须有且只有一个聚集索引；二级索引查询需“回表”。
MyISAM实现	非聚集索引：索引与数据分离，索引叶子节点存数据的磁盘地址。	主键索引与二级索引结构无差异，查找路径一致。
协同机制	通过索引间的协作完成查询。	覆盖索引（避免回表）、索引下推（ICP，减少回表次数）是两大性能优化法宝。

MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域,存放的并不是实际的数据记录,而是数据记录的地址。索引文件与数据文件分离,这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引区别并不大,只是主键索引不能有重复的关键字。

在MyISAM中,索引(含叶子节点)存放在单独的.myi文件中,叶子节点存放的是数据的物理地址偏移量(通过偏移量访问就是随机访问,速度很快)。

主索引是指主键索引,键值不可能重复;辅助索引则是普通索引,键值可能重复。

通过索引查找数据的流程:先从索引文件中查找到索引节点,从中拿到数据的文件指针,再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。

InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域,存放的就是实际的数据记录(对于主索引,此处会存放表中所有的数据记录;对于辅助索引此处会引用主键,检索的时候通过主键到主键索引中找到对应数据行),或者说,InnoDB的数据文件本身就是主键索引文件,这样的索引被称为"“聚簇索引”,一个表只能有一个聚簇索引。

主键索引

我们知道InnoDB索引是聚集索引,它的索引和数据是存入同一个.idb文件中的,因此它的索引结构是在同一个树节点中同时存放索引和数据,如下图中最底层的叶子节点有三行数据,对应于数据表中的id、stu_id、name数据项。

在Innodb中,索引分叶子节点和非叶子节点,非叶子节点就像新华字典的目录,单独存放在索引段中,叶子节点则是顺序排列的,在数据段中。Innodb的数据文件可以按照表来切分(只需要开启innodb_file_per_table),切分后存放在xxx.ibd中,默认不切分,存放在xxx.ibdata中。

辅助(非主键)索引

这次我们以示例中学生表中的name列建立辅助索引,它的索引结构跟主键索引的结构有很大差别,在最底层的叶子结点有两行数据,第一行的字符串是辅助索引,按照ASCII码进行排序,第二行的整数是主键的值。

这就意味着,对name列进行条件搜索,需要两个步骤:

1. 在辅助索引上检索name,到达其叶子节点获取对应的主键;
2. 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询”

InnoDB 索引结构需要注意的点

1. 数据文件本身就是索引文件
2. 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
3. 聚集索引中叶节点包含了完整的数据记录
4. InnoDB 表必须要有主键,并且推荐使用整型自增主键

正如我们上面介绍 InnoDB 存储结构,索引与数据是共同存储的,不管是主键索引还是辅助索引,在查找时都是通过先查找到索引节点才能拿到相对应的数据,如果我们在设计表结构时没有显式指定索引列的话,MySQL 会从表中选择数据不重复的列建立索引,如果没有符合的列,则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,并且这个字段长度为6个字节,类型为整型。

为什么推荐使用整型自增主键而不是选择UUID

• UUID是字符串,比整型消耗更多的存储空间;
• 在B+树中进行查找时需要跟经过的节点值比较大小,整型数据的比较运算比字符串更快速;
• 自增的整型索引在磁盘中会连续存储,在读取一页数据时也是连续;UUID是随机产生的,读取的上下两行数据存储是分散的,不适合执行where id > 5 && id < 20的条件查询语句。
• 在插入或删除数据时,整型自增主键会在叶子结点的末尾建立新的叶子节点,不会破坏左侧子树的结构;UUID主键很容易出现这样的情况,B+树为了维持自身的特性,有可能会进行结构的重构,消耗更多的时间。

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

保证数据一致性和节省存储空间,可以这么理解:商城系统订单表会存储一个用户ID作为关联外键,而不推荐存储完整的用户信息,因为当我们用户表中的信息(真实名称、手机号、收货地址···)修改后,不需要再次维护订单表的用户数据,同时也节省了存储空间。

Hash索引

• 主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置;如果发生Hash碰撞(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储。
• 检索算法:在检索查询时,就再次对待查关键字再次执行相同的Hash算法,得到Hash值,到对应Hash表对应位置取出数据即可,如果发生Hash碰撞,则需要在取值时进行筛选。目前使用Hash索引的数据库并不多,主要有Memory等。
• MySQL目前有Memory引擎和NDB引擎支持Hash索引。

full-text全文索引

• 全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。
• 它用于替代效率较低的LIKE模糊匹配操作,而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
• 同样使用B-Tree存放索引数据,但使用的是特定的算法,将字段数据分割后再进行索引(一般每4个字节一次分割),索引文件存储的是分割前的索引字符串集合,与分割后的索引信息,对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

R-Tree空间索引

空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型

为什么Mysql索引要用B+树不是B树？

用B+树不用B树考虑的是IO对性能的影响,B树的每个节点都存储数据,而B+树只有叶子节点才存储数据,所以查找相同数据量的情况下,B树的高度更高,IO更频繁。数据库索引是存储在磁盘上的,当数据量大时,就不能把整个索引全部加载到内存了,只能逐一加载每一个磁盘页(对应索引树的节点)。其中在MySQL底层对B+树进行进一步优化:在叶子节点中是双向链表,且在链表的头结点和尾节点也是循环指向的。

为何不采用Hash方式？

因为Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。

所以,哈希索引只适用于等值查询的场景。而B+Tree是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则,如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题。

4、MySQL覆盖索引

覆盖索引(Covering Index),或者叫索引覆盖, 也就是平时所说的不需要回表操作

• 就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

• 索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据,当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含(覆盖)满足查询结果的数据就叫做覆盖索引。

• 判断标准

使用explain,可以通过输出的extra列来判断,对于一个索引覆盖查询,显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询;

5、索引的数据结构

索引的数据结构主要有B+树和哈希表,对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引,默认的索引类型为B+树索引 。

B+树索引

熟悉数据结构的同学都知道,B+树、平衡二叉树、红黑树都是经典的数据结构。在B+树中,所有的记录节点都是按照键值大小的顺序放在叶子节点上,如下图。

关于B树B+树这种数据结构,请参考文章:数据结构-(2-3树,2-3-4树,B-树,B+树)

从上图可以看出 ,因为B+树具有有序性,并且所有的数据都存放在叶子节点,所以查找的效率非常高,并且支持排序和范围查找。

B+树的索引又可以分为主索引和辅助索引。其中主索引为聚簇索引,辅助索引为非聚簇索引。聚簇索引是以主键作为B+ 树索引的键值所构成的B+树索引,聚簇索引的叶子节点存储着完整的数据记录;

非聚簇索引是以非主键的列作为B+树索引的键值所构成的B+树索引,非聚簇索引的叶子节点存储着主键值。所以使用非聚簇索引进行查询时,会先找到主键值,然后到根据聚簇索引找到主键对应的数据域。上图中叶子节点存储的是数据记录,为聚簇索引的结构图,非聚簇索引的结构图如下:

上图中的字母为数据的非主键的列值,假设要查询该列值为B的信息,则需先找到主键7,在到聚簇索引中查询主键7所对应的数据域。

哈希索引

哈希索引是基于哈希表实现的,对于每一行数据,存储引擎会对索引列通过哈希算法进行哈希计算得到哈希码,并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的,将哈希码的值作为哈希表的key值,将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是o(1),一般多用于精确查找 。

Hash索引和B+树的区别？

因为两者数据结构上的差异导致它们的使用场景也不同,哈希索引一般多用于精确的等值查找,B+索引则多用于除了精确的等值查找外的其他查找。在大多数情况下,会选择使用B+树索引。

• 哈希索引不支持排序,因为哈希表是无序的。
• 哈希索引不支持范围查找。
• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
• 因为哈希表中会存在哈希冲突,所以哈希索引的性能是不稳定的,而B+树索引的性能是相对稳定的,每次查询都是从根节点到叶子节点 。

B树和B+树的区别？

B树和B+树最主要的区别主要有两点:

• B树中的内部节点和叶子节点均存放键和值,而B+树的内部节点只有键没有值,叶子节点存放所有的键和值。
• B＋树的叶子节点是通过相连在一起的,方便顺序检索。

两者的结构图如下:

B树

B+树

关于B树B+树这种数据结构,请参考文章:数据结构-(2-3树,2-3-4树,B-树,B+树)

6、数据库为什么使用B+树而不是B树？

1. B树适用于随机检索,因为B树中非叶子节点存储的有键对应的值,而B+树适用于随机检索和顺序检索,因为B+树中所有的叶子节点都连接在一起,可以顺序查找。
2. B+树的空间利用率更高,因为B树每个节点要存储键和值,而B+树的内部节点只存储键,这样B+树的一个节点就可以存储更多的索引,从而使树的高度变低,减少了I/O次数,使得数据检索速度更快。相反B+树中,每一个节点存储的数据是有限的,这也就导致了相同的数据量,使用B树高度会更高,导致IO次数更多。
3. B+树的叶子节点都是连接在一起的,所以范围查找,顺序查找更加方便
4. B+树的性能更加稳定,因为在B+树中,每次查询都是从根节点到叶子节点,而在B树中,要查询的值可能不在叶子节点,在内部节点就已经找到。

那在什么情况适合使用B树呢,因为B树的内部节点也可以存储值,所以可以把一些频繁访问的值放在距离根节点比较近的地方,这样就可以提高查询效率。综上所述,B+树的性能更加适合作为数据库的索引

用B+树不用B树考虑的是IO对性能的影响,B树的每个节点都存储数据,而B+树只有叶子节点才存储数据,所以查找相同数据量的情况下,B树的高度更高,IO更频繁。

数据库索引是存储在磁盘上的,当数据量大时,就不能把整个索引全部加载到内存了,只能逐一加载每一个磁盘页(对应索引树的节点)。其中在MySQL底层对B+树进行进一步优化:在叶子节点中是双向链表,且在链表的头结点和尾节点也是循环指向的。

7、为何不采用Hash方式？

因为Hash索引底层是哈希表,哈希表是一种以key-value存储数据的结构,所以多个数据在存储关系上是完全没有任何顺序关系的,所以,对于区间查询是无法直接通过索引查询的,就需要全表扫描。

因此,哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树,所以他的节点是天然有序的(左子节点小于父节点、父节点小于右子节点),所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则,如果有大量重复键值得情况下,哈希索引的效率会很低,因为存在哈希碰撞问题。

8、非聚簇索引一定会进行回表查询吗？

上面是说了非聚簇索引的叶子节点存储的是主键,也就是说要先通过非聚簇索引找到主键,再通过聚簇索引找到主键所对应的数据,后面这个再通过聚簇索引找到主键对应的数据的过程就是回表查询,那么非聚簇索引就一定会进行回表查询吗 ？

答案是不一定的,这里涉及到一个索引覆盖的问题,如果查询的数据再辅助索引上完全能获取到便不需要回表查询。例如有一张表存储着个人信息包括id、name、age等字段。假设聚簇索引是以ID为键值构建的索引,非聚簇索引是以name为键值构建的索引, select id,name from user where name =zhangsan; 这个查询便不需要进行回表查询因为,通过非聚簇索引已经能全部检索出数据,这就是索引覆盖的情况。如果查询语句是这样, select id,name,age from user where name ='zhangsan; 则需要进行回表查询,因为通过非聚簇索引不能检索出age的值。那应该如何解决那呢？只需要将索引覆盖即可,建立age和name的联合索引再使用 select id,name,age from user where name = 'zhangsan; 进行查询即可。所以通过索引覆盖能解决非聚簇索引回表查询的问题。

9、介绍一下覆盖索引

如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中,如果不是主键索引,叶子节点存储的是主键+列值。最终还是要“回表”,也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的,不做回表操作！

覆盖索引即需要查询的字段正好是索引的字段,那么直接根据该索引,就可以查到数据了,而无需回表查询。

如主键索引,如果一条 SQL 需要查询主键,那么正好根据主键索引就可以查到主键。

再如普通索引,如果一条 SQL 需要查询 name,name 字段正好有索引, 那么直接根据这个索引就可以查到数据,也无需回表。

覆盖索引:

10、数据库事务

1、事务的隔离级别有哪些？MySQL的默认隔离级别是什么？

2、什么是幻读,脏读,不可重复读呢？

3、MySQL事务的四大特性以及实现原理

4、MVCC熟悉吗,它的底层原理？

什么是数据库事务？

百度百科的解释:数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列,这些操作要么全部执行,要么全部不执行,是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成

事务的四大特性是什么？(ACID特性)

事务是由一组SQL语句组成的逻辑处理单元,具有4个属性,通常简称为事务的ACID属性。

• A (Atomicity) 原子性:整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样
• C (Consistency) 一致性:在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏
• I (Isolation)隔离性:一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的,并发执行的各个事务之间不能互相干扰
• D (Durability) 持久性:在事务完成以后,该事务所对数据库所作的更改便持久的保存在数据库之中,并不会被回滚

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性,使用 undo log(回滚日志) 来保证事务的原子性。

MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性( 默认支持的隔离级别是 REPEATABLE-READ )。

保证了事务的持久性、原子性、隔离性之后,一致性才能得到保障。

数并发事务带来哪些问题?

在典型的应用程序中,多个事务并发运行,经常会操作相同的数据来完成各自的任务(多个用户对同一数据进行操作)。并发虽然是必须的,但可能会导致以下的问题。

• 脏读(Dirty Reads):事务A更新了数据,但还没有提交,这时事务B读取到事务A更新后的数据,然后事务A回滚了,事务B读取到的数据就成为脏数据了。

因为事务A读取到事务B未提交的数据,这就是脏读。

• 不可重复读(Non-Repeatable Reads):事务A对数据进行多次读取,事务B在事务A多次读取的过程中执行了更新操作并提交了,导致事务A多次读取到的数据并不一致。

事务A被事务B干扰到了！在事务A范围内,两个相同的查询,读取同一条记录,却返回了不同的数据,这就是不可重复读。

• 幻读(Phantom Reads):幻读与不可重复读类似。它发生在一个事务A读取了几行数据,接着另一个并发事务B插入了一些数据时。在随后的查询中,事务A就会发现多了一些原本不存在的记录,就好像发生了幻觉一样,所以称为幻读。

事务A查询一个范围的结果集,另一个并发事务B往这个范围中插入新的数据,并提交事务,然后事务A再次查询相同的范围,两次读取到的结果集却不一样了,这就是幻读。

• 丢失修改(Lost Update):事务A和事务B都对同一个数据进行修改,事务A先修改,事务B随后修改,事务B的修改覆盖了事务A的修改。

不可重复度和幻读看起来比较像,它们主要的区别是:在不可重复读中,发现数据不一致主要是数据被更新了。在幻读中,发现数据不一致主要是数据增多或者减少了。

幻读和不可重复读的区别:

• 不可重复读的重点是修改:在同一事务中,同样的条件,第一次读的数据和第二次读的数据不一样。(因为中间有其他事务提交了修改)
• 幻读的重点在于新增或者删除:在同一事务中,同样的条件,,第一次和第二次读出来的记录数不一样。(因为中间有其他事务提交了插入/删除)

对比维度	不可重复读	幻读
操作类型	UPDATE（更新）	INSERT（插入）/ DELETE（删除）
影响范围	单个已存在的记录	记录集合的数量
锁定对象	已存在的行	行之间的间隙（范围）
解决机制	行锁（Record Lock）	间隙锁（Gap Lock）/ 临键锁（Next-Key Lock）
RR隔离级别行为	通过MVCC避免	通过间隙锁避免
是否违反一致性	读取到过时数据	读取到新出现的数据
示例场景	两次读取同一个人年龄不同	两次统计人数结果不同

• 不可重复读：同一条记录的内容被修改（数据变化）
• 幻读：记录的数量发生变化（数据增删）

并发事务处理带来的问题的解决办法:

• “丢失修改”通常是应该完全避免的。但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决,因此,防止更新丢失应该是应用的责任。
• “脏读” 、 “不可重复读”和“幻读” ,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决:
  • 一种是加锁:在读取数据前,对其加锁,阻止其他事务对数据进行修改。
  • 另一种是数据多版本并发控制(MultiVersion Concurrency Control,简称 MVCC 或 MCC),也称为多版本数据库:不用加任何锁, 通过一定机制生成一个数据请求时间点的一致性数据快照 (Snapshot), 并用这个快照来提供一定级别 (语句级或事务级) 的一致性读取。从用户的角度来看,好象是数据库可以提供同一数据的多个版本。

数据库的隔离级别有哪些？

数据库事务的隔离级别有4种,由低到高分别为

• READ-UNCOMMITTED(读未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
• SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。

查看当前数据库的事务隔离级别:

show variables like 'tx_isolation'

数据库的隔离级别可以解决数据库的脏读、不可重复读、幻读等问题。

MySQL的默认隔离级别是可重复读。

这里需要注意的是:与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ(可重读) 事务隔离级别下使用的是 Next-Key Lock 锁算法,因此可以避免幻读的产生,这与其他数据库系统(如 SQL Server)是不同的。所以说 InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读) 已经可以完全保证事务的隔离性要求,即达到了 SQL 标准的 SERIALIZABLE(可串行化) 隔离级别。

MySQL InnoDB 的 REPEATABLE-READ(可重读)并不保证避免幻读,需要应用使用加锁读来保证。而这个加锁度使用到的机制就是 Next-Key Locks。

因为隔离级别越低,事务请求的锁越少,所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ,但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ(可重读) 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。

下面通过事例一一阐述在事务的并发操作中可能会出现脏读,不可重复读,幻读和事务隔离级别的联系。

数据库的事务隔离越严格,并发副作用越小,但付出的代价就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。

那怎么解决脏读呢？Read committed！读提交,能解决脏读问题。

那怎么解决可能的不可重复读问题？Repeatable read ！

重复读,就是在开始读取数据(事务开启)时,不再允许修改操作。 MySQL的默认事务隔离级别

分析:重复读可以解决不可重复读问题。写到这里,应该明白的一点就是,不可重复读对应的是修改,即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作,而不是UPDATE操作。

那怎么解决幻读问题？Serializable！

Serializable 是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。简单来说,Serializable会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用问题。这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。

比较

需要说明的是,事务隔离级别和数据访问的并发性是对立的,事务隔离级别越高并发性就越差。所以要根据具体的应用来确定合适的事务隔离级别,这个地方没有万能的原则。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)。我们可以通过SELECT @@tx_isolation;命令来查看,MySQL 8.0 该命令改为SELECT @@transaction_isolation;

这里需要注意的是:与 SQL 标准不同的地方在于InnoDB 存储引擎在 **REPEATABLE-READ(可重读)**事务隔离级别下使用的是Next-Key Lock 算法,因此可以避免幻读的产生,这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读)已经可以完全保证事务的隔离性要求,即达到了 SQL标准的 **SERIALIZABLE(可串行化)**隔离级别,而且保留了比较好的并发性能。

因为隔离级别越低,事务请求的锁越少,所以大部分数据库系统的隔离级别都是READ-COMMITTED(读已提交):,但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ(可重读)**并不会有任何性能损失。

InnoDB 存储引擎提供了对 XA 事务的支持,并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源(transactional resources)参与到一个全局的事务中。事务资源通常是关系型数据库系统,但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交,要么都回滚,这对于事务原有的 ACID 要求又有了提高。另外,在使用分布式事务时,InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

数据库的事务是如何实现的？

我们这里以 MySQL 的 InnoDB 引擎为例来简单说一下。

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性,使用 undo log(回滚日志) 来保证事务的原子性。

MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性( 默认支持的隔离级别是 REPEATABLE-READ )。

保证了事务的持久性、原子性、隔离性之后,一致性才能得到保障。

隔离级别是如何实现的？

事务的隔离机制主要是依靠锁机制和MVCC(多版本并发控制)实现的,提交读和可重复读可以通过MVCC实现,串行化可以通过锁机制实现。

数据库是通过加锁,来实现事务的隔离性的。这就好像,如果你想一个人静静,不被别人打扰,你就可以在房门上加上一把锁。

加锁确实好使,可以保证隔离性。比如串行化隔离级别就是加锁实现的。但是频繁的加锁,导致读数据时,没办法修改,修改数据时,没办法读取,大大降低了数据库性能。

那么,如何解决加锁后的性能问题的？

答案就是,MVCC多版本并发控制！它实现读取数据不用加锁,可以让读取数据同时修改。修改数据时同时可读取,是一种乐观锁的思想。

解决不可重复读：MVCC + 行锁

原理：在RR隔离级别，事务第一次读取时创建快照（ReadView），后续所有读取都从这个快照获取数据，因此不会看到其他事务已提交的更新。

解决幻读：间隙锁 + 临键锁

原理：在RR隔离级别，InnoDB使用临键锁（Next-Key Lock） = 记录锁 + 间隙锁，锁定查询范围涉及的索引记录及其间的空隙，防止其他事务在该范围内插入新记录。

不同隔离级别对比

重要说明：

1. MySQL的RR级别通过间隙锁真正解决了幻读，这是MySQL与标准SQL规范的不同之处
2. 标准SQL中RR级别只要求解决不可重复读，不要求解决幻读
3. MySQL通过更强的锁定机制，在RR级别就实现了可串行化级别的幻读防护

11、什么是MVCC

MVCC(multiple version concurrent control)是一种控制并发的方法,主要用来提高数据库的并发性能。

MVCC,即Multi-Version Concurrency Control (多版本并发控制)。它是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问,在编程语言中实现事务内存。

通俗的讲,数据库中同时存在多个版本的数据,并不是整个数据库的多个版本,而是某一条记录的多个版本同时存在,在某个事务对其进行操作的时候,需要查看这一条记录的隐藏列事务版本id,比对事务id并根据事物隔离级别去判断读取哪个版本的数据。

数据库隔离级别读已提交、可重复读 都是基于MVCC实现的,相对于加锁简单粗暴的方式,它用更好的方式去处理读写冲突,能有效提高数据库并发性能。

在了解MVCC时应该先了解当前读和快照读。

• 当前读:读取的是数据库的最新版本,并且在读取时要保证其他事务不会修该当前记录,所以会对读取的记录加锁。
• 快照读:不加锁读取操作即为快照读,使用MVCC来读取快照中的数据,避免加锁带来的性能损耗。

可以看到MVCC的作用就是在不加锁的情况下,解决数据库读写冲突问题,并且解决脏读、幻读、不可重复读等问题,但是不能解决丢失修改问题。

MVCC让每个事务都仿佛拥有数据库的一个"快照"，读操作读快照，写操作创建新版本，读写互不阻塞。

MVCC实现原理分析:

事务版本号

事务每次开启前,都会从数据库获得一个自增长的事务ID,可以从事务ID判断事务的执行先后顺序。这就是事务版本号。

隐藏字段

InnoDB中，每行记录除了用户定义的字段外，还包含三个隐藏字段：trx_id、roll_pointer,如果表中没有主键和非NULL唯一键时,则还会有第三个隐藏的主键列row_id。

-- 用户看到的表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 实际存储的结构（伪代码）
CREATE TABLE users_actual (
    -- 用户字段
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    
    -- InnoDB隐藏字段
    DB_ROW_ID INT,           -- 行ID（无主键时使用）
    DB_TRX_ID INT,           -- 最后修改该行的事务ID
    DB_ROLL_PTR INT          -- 回滚指针，指向undo log中的旧版本
);

隐藏字段	作用	长度
DB_TRX_ID	记录最后修改/插入该行的事务ID	6字节
DB_ROLL_PTR	回滚指针，指向undo log中的前一个版本	7字节
DB_ROW_ID	隐式自增ID（无主键时作为聚集索引）	6字节

undo log

undo log,回滚日志,用于记录数据被修改前的信息。在表记录修改之前,会先把数据拷贝到undo log里,如果事务回滚,即可以通过undo log来还原数据。

可以这样认为,当delete一条记录时,undo log 中会记录一条对应的insert记录,当update一条记录时,它记录一条对应相反的update记录。

Undo Log作用：

1. 事务回滚：用于撤销未提交事务的修改
2. 多版本读：为其他事务提供旧版本数据

两种Undo Log：

1. insert undo log：INSERT操作产生，事务提交后可直接删除
2. update undo log：UPDATE/DELETE操作产生，用于MVCC，需要保留到无事务需要时

版本链

多个事务并行操作某一行数据时,不同事务对该行数据的修改会产生多个版本,然后通过回滚指针(roll_pointer),连成一个链表,这个链表就称为版本链。如下:

其实,通过版本链,我们就可以看出事务版本号、表格隐藏的列和undo log它们之间的关系。我们再来小分析一下。

1. 假设现在有一张core_user表,表里面有一条数据,id为1,名字为孙权:

2. 现在开启一个事务A:对core_user表执行update core_user set name ="曹操" where id=1,会进行如下流程操作
3. 首先获得一个事务ID=100
4. 把core_user表修改前的数据,拷贝到undo log
5. 修改core_user表中,id=1的数据,名字改为曹操
6. 把修改后的数据事务Id=101改成当前事务版本号,并把roll_pointer指向undo log数据地址。

Read View

ReadView 是MVCC实现一致性读的核心数据结构，它记录了某个时刻所有活跃事务的信息。

// ReadView结构（概念）
struct ReadView {
    trx_id_t creator_trx_id;     // 创建该ReadView的事务ID
    trx_id_t m_low_limit_id;      // 高水位：大于该ID的事务都不可见
    trx_id_t m_up_limit_id;       // 低水位：小于该ID的事务都可见
    ids_t m_ids;                  // 活跃事务ID列表
};

• Read View是什么呢？ 它就是事务执行SQL语句时,产生的读视图。实际上在innodb中,每个SQL语句执行前都会得到一个Read View。
• Read View有什么用呢？ 它主要是用来做可见性判断的,即判断当前事务可见哪个版本的数据~

Read View是如何保证可见性判断的呢？我们先看看Read view 的几个重要属性

• m_ids:当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。
• min_limit_id:表示在生成Read View时,当前系统中活跃的读写事务中最小的事务id,即m_ids中的最小值。
• max_limit_id:表示生成Read View时,系统中应该分配给下一个事务的id值。
• creator_trx_id: 创建当前Read View的事务ID

Read view 匹配条件规则如下:

1. 如果数据事务ID trx_id < min_limit_id,表明生成该版本的事务在生成Read View前,已经提交(因为事务ID是递增的),所以该版本可以被当前事务访问。
2. 如果trx_id>= max_limit_id,表明生成该版本的事务在生成ReadView后才生成,所以该版本不可以被当前事务访问。
3. 如果 min_limit_id =<trx_id< max_limit_id,需腰分3种情况讨论

• (1).如果m_ids包含trx_id,则代表Read View生成时刻,这个事务还未提交,但是如果数据的trx_id等于creator_trx_id的话,表明数据是自己生成的,因此是可见的。
• (2)如果m_ids包含trx_id,并且trx_id不等于creator_trx_id,则Read View生成时,事务未提交,并且不是自己生产的,所以当前事务也是看不见的;
• (3).如果m_ids不包含trx_id,则说明你这个事务在Read View生成之前就已经提交了,修改的结果,当前事务是能看见的。

ReadView判断规则:

简化规则：

1. 比自己早提交的 → 可见
2. 比自己晚开始的 → 不可见
3. 未提交的 → 不可见
4. 自己修改的 → 可见

MVCC工作流程

INSERT操作

BEGIN;  -- 事务ID=100
INSERT INTO users (id, name, age) VALUES (1, 'Tom', 25);

UPDATE操作

-- 初始记录: id=1, name='Tom', age=25, trx_id=90

BEGIN;  -- 事务ID=100
UPDATE users SET age = 26 WHERE id = 1;

SELECT操作（一致性非锁定读）

-- 场景：RR隔离级别
BEGIN;  -- 事务A, trx_id=100
SELECT * FROM users WHERE id = 1;
-- 第一次查询创建ReadView

不同隔离级别下的MVCC行为

READ COMMITTED（读已提交）

-- 每次查询都创建新的ReadView
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;
SELECT * FROM users WHERE id = 1;  -- 创建ReadView1
-- 其他事务提交
SELECT * FROM users WHERE id = 1;  -- 创建ReadView2，能看到已提交的修改
COMMIT;

REPEATABLE READ（可重复读）- MySQL默认

-- 只在第一次查询时创建ReadView
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN;
SELECT * FROM users WHERE id = 1;  -- 创建ReadView，整个事务使用同一个
-- 其他事务提交
SELECT * FROM users WHERE id = 1;  -- 使用同一个ReadView，看不到修改
COMMIT;

快照读 VS 当前读

-- 快照读（Snapshot Read）：不加锁，使用MVCC
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE age > 20;  -- 快照读

-- 当前读（Current Read）：加锁，读取最新版本
SELECT * FROM users WHERE id = 1 FOR UPDATE;  -- 加X锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;  -- 加S锁
UPDATE users SET age = 26 WHERE id = 1;  -- 先当前读，再加锁更新
DELETE FROM users WHERE id = 1;  -- 先当前读，再加锁删除
INSERT INTO users ...  -- 加锁

MVCC 优势和代价

优势

优势	说明
读写不互斥	读操作不会阻塞写，写操作不会阻塞读
高并发	读操作无需加锁，大大提升并发性能
一致性读	无需加锁即可实现一致性读取
死锁减少	读操作不加锁，减少了死锁可能性

代价

代价	说明	解决方案
存储开销	需要额外空间存储隐藏字段和undo log	定期清理purge
版本链维护	版本链过长影响查询性能	合理配置innodb_purge_threads
清理负担	需要purge线程清理无用版本	监控History list length
事务ID限制	事务ID有限（6字节，约281万亿）	循环使用，wrap安全处理

mvcc实现原理分析

查询一条记录,基于MVCC,是怎样的流程

1. 获取事务自己的版本号,即事务ID
2. 获取Read View
3. 查询得到的数据,然后Read View中的事务版本号进行比较。
4. 如果不符合Read View的可见性规则, 即就需要Undo log中历史快照;
5. 最后返回符合规则的数据

InnoDB 实现MVCC,是通过Read View+ Undo Log 实现的,Undo Log 保存了历史快照,Read View可见性规则帮助判断当前版本的数据是否可见。

读已提交(RC)隔离级别,存在不可重复读问题的分析历程

1. 创建core_user表,插入一条初始化数据,如下:

2. 隔离级别设置为读已提交(RC),事务A和事务B同时对core_user表进行查询和修改操作。

事务A: select * fom core_user where id=1
事务B: update core_user set name =”曹操”

最后事务A查询到的结果是,name=曹操的记录,我们基于MVCC,来分析一下执行流程:

(1). A开启事务,首先得到一个事务ID为100

(2).B开启事务,得到事务ID为101

(3).事务A生成一个Read View,read view对应的值如下

然后回到版本链:开始从版本链中挑选可见的记录:

由图可以看出,最新版本的列name的内容是孙权,该版本的trx_id值为100。开始执行read view可见性规则校验:

min_limit_id(100)=<trx_id(100)<102;
creator_trx_id = trx_id =100;

由此可得,trx_id=100的这个记录,当前事务是可见的。所以查到是name为孙权的记录。

(4). 事务B进行修改操作,把名字改为曹操。把原数据拷贝到undo log,然后对数据进行修改,标记事务ID和上一个数据版本在undo log的地址。

(5) 提交事务

(6) 事务A再次执行查询操作,新生成一个Read View,Read View对应的值如下

然后再次回到版本链:从版本链中挑选可见的记录:

从图可得,最新版本的列name的内容是曹操,该版本的trx_id值为101。开始执行Read View可见性规则校验:

min_limit_id(100)=<trx_id(101)<max_limit_id(102);
但是,trx_id=101,不属于m_ids集合

因此,trx_id=101这个记录,对于当前事务是可见的。所以SQL查询到的是name为曹操的记录。

可重复读(RR)隔离级别,解决不可重复读问题的分析

在RR隔离级别下,是如何解决不可重复读问题的呢？我们一起再来看下,

还是4.2小节那个流程,还是这个事务A和事务B,如下:

实际上,各种事务隔离级别下的Read view工作方式,是不一样的,RR可以解决不可重复读问题,就是跟Read view工作方式有关。

• 在读已提交(RC)隔离级别下,同一个事务里面,每一次查询都会产生一个新的Read View副本,这样就可能造成同一个事务里前后读取数据可能不一致的问题(不可重复读并发问题)。

• 在可重复读(RR)隔离级别下,一个事务里只会获取一次read view,都是副本共用的,从而保证每次查询的数据都是一样的。

实例分析

事务A再次执行查询操作,复用老的Read View副本,Read View对应的值如下

然后再次回到版本链:从版本链中挑选可见的记录:

从图可得,最新版本的列name的内容是曹操,该版本的trx_id值为101。开始执行read view可见性规则校验:

min_limit_id(100)=<trx_id(101)<max_limit_id(102);
因为m_ids{100,101}包含trx_id(101),
并且creator_trx_id (100) 不等于trx_id(101)

所以,trx_id=101这个记录,对于当前事务是不可见的。这时候呢,版本链roll_pointer跳到下一个版本,trx_id=100这个记录,再次校验是否可见:

min_limit_id(100)=<trx_id(100)< max_limit_id(102);
因为m_ids{100,101}包含trx_id(100),
并且creator_trx_id (100) 等于trx_id(100)

所以,trx_id=100这个记录,对于当前事务是可见的,所以两次查询结果,都是name=孙权的那个记录。即在可重复读(RR)隔离级别下,复用老的Read View副本,解决了不可重复读的问题。

在可重复读的隔离级别下,InnoDB的工作流程:

SELECT 作为查询的结果要满足两个条件:

1. 当前事务所要查询的数据行快照的创建版本号必须小于当前事务的版本号,这样做的目的是保证当前事务读取的数据行的快照要么是在当前事务开始前就已经存在的,要么就是当前事务自身插入或者修改过的。
2. 当前事务所要读取的数据行快照的删除版本号必须是大于当前事务的版本号,如果是小于等于的话,表示该数据行快照已经被删除,不能读取。

INSERT

1. 将当前系统版本号作为数据行快照的创建版本号。

DELETE

1. 将当前系统版本号作为数据行快照的删除版本号。

UPDATE

1. 保存当前系统版本号为更新前的数据行快照创建行版本号,并保存当前系统版本号为更新后的数据行快照的删除版本号,其实就是,先删除在插入即为更新。

总结一下,MVCC的作用就是在避免加锁的情况下最大限度解决读写并发冲突的问题,它可以实现提交读和可重复度两个隔离级

关键问题

1. MVCC解决了什么问题？
   • 读写并发冲突，实现高并发下的数据一致性
2. MVCC在RC和RR级别有什么区别？
   • RC：每个SELECT创建新的ReadView
   • RR：事务内第一个SELECT创建ReadView并复用
3. 快照读和当前读的区别？
   • 快照读：不加锁，读历史版本
   • 当前读：加锁，读最新版本
4. MVCC如何解决不可重复读？
   • 通过ReadView保证事务内多次读取结果一致
5. MVCC能解决幻读吗？
   • 快照读可以解决幻读（RR级别）
   • 当前读需要间隙锁配合

MVCC是InnoDB实现高并发的核心机制，通过数据多版本和一致性快照，让读操作不加锁也能看到一致的数据视图，实现了读写互不阻塞的优雅并发控制。

12、数据库锁原理

1、数据库的乐观锁和悲观锁？

2、MySQL 中有哪几种锁,列举一下？

3、MySQL中InnoDB引擎的行锁是怎么实现的？

4、MySQL 间隙锁有没有了解,死锁有没有了解,写一段会造成死锁的 sql 语句,死锁发生了如何解决,MySQL 有没有提供什么机制去解决死锁

Mysql数据库锁体系

什么是数据库锁

当数据库有并发事务的时候,保证数据访问顺序的机制称为锁机制,锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则。

打个比方,我们到淘宝上买一件商品,商品只有一件库存,这个时候如果还有另一个人买,那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事物,我们先从库存表中取出物品数量,然后插入订单,付款后插入付款表信息,然后更新商品数量。在这个过程中,使用锁可以对有限的资源进行保护,解决隔离和并发的矛盾。

锁的分类

从对数据操作的类型分类

• 读锁(共享锁):针对同一份数据,多个读操作可以同时进行,不会互相影响
• 写锁(排他锁):当前写操作没有完成前,它会阻断其他写锁和读锁

从对数据操作的粒度分类:

为了尽可能提高数据库的并发度,每次锁定的数据范围越小越好,理论上每次只锁定当前操作的数据的方案会得到最大的并发度,但是管理锁是很耗资源的事情(涉及获取,检查,释放锁等动作),因此数据库系统需要在高并发响应和系统性能两方面进行平衡,这样就产生了“锁粒度(Lock granularity)”的概念。

• 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低(MyISAM 和 MEMORY 存储引擎采用的是表级锁);
• 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高(InnoDB 存储引擎既支持行级锁也支持表级锁,但默认情况下是采用行级锁);
• 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

适用:从锁的角度来说,

表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;

而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

MyISAM 表锁

MyISAM 的表锁有两种模式:

• 表共享读锁 (Table Read Lock):不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
• 表独占写锁 (Table Write Lock):会阻塞其他用户对同一表的读和写操作;

MyISAM 表的读操作与写操作之间,以及写操作之间是串行的。当一个线程获得对一个表的写锁后, 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待,直到锁被释放为止。

默认情况下,写锁比读锁具有更高的优先级:当一个锁释放时,这个锁会优先给写锁队列中等候的获取锁请求,然后再给读锁队列中等候的获取锁请求。

InnoDB 行锁

InnoDB 实现了以下两种类型的行锁:

• 共享锁(S):允许事务读一行数据,具有锁兼容性质,允许多个事务同时获得该锁。
• 排他锁(X):允许事务删除或更新一行数据,具有排它性,某个事务要想获得锁,必须要等待其他事务释放该对象的锁。

X锁和其他锁都不兼容,S锁之和S锁兼容,S锁和X锁都是行级别锁,兼容是指对同一条记录(row)锁的兼容性情况。

此外,innodb支持多粒度锁定,这种锁允许事务在行级别和表级别上的锁同时存在,称之为意向锁(Intention Lock),意向锁将锁定的对象分为多个层次,意味着事务在更细粒度上进行加锁。意向锁设计的目的主要是为了在一个事务中揭示下一行将被请求的锁类型。

实现多粒度锁机制,InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁:

• 意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。(也就是说事务准备给表中的某几行添加共享锁)
• 意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。(事务想要获得一张表中的某几行的排它锁)

索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。

如何理解锁

若将上锁看成如上的这颗树,那么对最下层对象的上锁,也就是最细粒度的上锁,首先需要对粗粒度进行上锁,如上图所示,如果我们需要对最底层的记录进行上X锁,那么需要对数据库,表,页上意向锁IX Lock,最后对最底层的记录上X锁,若其中的任意一个部分导致等待,则该操作需要等待粗粒度锁的完成。

由于innodb的支持行级锁,所以意向锁不会阻塞全表扫描以外的任何请求,故表级意向锁和行级锁的兼容如下表所示:

加锁机制

乐观锁与悲观锁是两种并发控制的思想,可用于解决丢失更新问题

乐观锁会“乐观地”假定大概率不会发生并发更新冲突,访问、处理数据过程中不加锁,只在更新数据时再根据版本号或时间戳判断是否有冲突,有则处理,无则提交事务。用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式。

乐观锁的实现:

使用数据版本(Version)记录机制实现,这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识,一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时,将version字段的值一同读出,数据每更新一次,对此version值加一。当我们提交更新的时候,判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对,如果数据库表当前版本号与第一次取出来的version值相等,则予以更新,否则认为是过期数据

悲观锁会“悲观地”假定大概率会发生并发更新冲突,访问、处理数据前就加排他锁,在整个数据处理过程中锁定数据,事务提交或回滚后才释放锁。另外与乐观锁相对应的,悲观锁是由数据库自己实现了的,要用的时候,我们直接调用数据库的相关语句就可以了。

锁模式(InnoDB有三种行锁的算法)

记录锁

记录锁(Record Locks): 单个行记录上的锁。对索引项加锁,锁定符合条件的行。其他事务不能修改和删除加锁项;

SELECT * FROM table WHERE id = 1 FOR UPDATE;

它会在 id=1 的记录上加上记录锁,以阻止其他事务插入,更新,删除 id=1 这一行

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时,也会对该行数据加记录锁:

-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;

记录锁是通过唯一索引和主键索引实现的。

间隙锁

间隙锁(Gap Locks): 当我们使用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录,叫做“间隙”。InnoDB 也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。

对索引项之间的“间隙”加锁,锁定记录的范围(对第一条记录前的间隙或最后一条将记录后的间隙加锁),不包含索引项本身。其他事务不能在锁范围内插入数据,这样就防止了别的事务新增幻影行。

间隙锁基于非唯一索引,它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法,请务必牢记:使用间隙锁锁住的是一个区间,而不仅仅是这个区间中的每一条数据。

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

即所有在(1,10)区间内的记录行都会被锁住,所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞,但是 1 和 10 两条记录行并不会被锁住。

临建锁

GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况

临键锁(Next-key Locks): 临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。(临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。

Next-Key 可以理解为一种特殊的间隙锁,也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁,当某个事务持有该数据行的临键锁时,会锁住一段左开右闭区间的数据。

需要强调的一点是,InnoDB 中行级锁是基于索引实现的,临键锁只与非唯一索引列有关,在唯一索引列(包括主键列)上不存在临键锁。

对于行的查询,都是采用该方法,主要目的是解决幻读的问题。

记录锁

行锁在 InnoDB 中是基于索引实现的,所以一旦某个加锁操作没有使用索引,那么该锁就会退化为表锁。

顾名思义,记录锁就是为某行记录加锁,它封锁该行的索引记录:

-- id 列为主键列或唯一索引列
SELECT * FROM table WHERE id = 1 FOR UPDATE;

id 为 1 的记录行会被锁住。

需要注意的是:id 列必须为唯一索引列或主键列,否则上述语句加的锁就会变成临键锁。

同时查询语句必须为精准匹配(=),不能为 >、<、like等,否则也会退化成临键锁

在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时,也会对该行数据加记录锁:

`-- id 列为主键列或唯一索引列`
`UPDATE` `SET` `age = 50 ``WHERE` `id = 1;`

间隙锁

记录锁、间隙锁、临键锁都是排它锁

间隙锁基于非唯一索引,它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法,请务必牢记:使用间隙锁锁住的是一个区间,而不仅仅是这个区间中的每一条数据。

间隙锁是封锁索引记录中的间隔,或者第一条索引记录之前的范围,又或者最后一条索引记录之后的范围。

产生间隙锁的条件(RR事务隔离级别下;):

1. 使用普通索引锁定;
2. 使用多列唯一索引;
3. 使用唯一索引锁定多行记录。

以上情况,都会产生间隙锁,

对于使用唯一索引来搜索并给某一行记录加锁的语句,不会产生间隙锁。(这不包括搜索条件仅包括多列唯一索引的一些列的情况;在这种情况下,会产生间隙锁。)例如,如果id列具有唯一索引,则下面的语句仅对具有id值100的行使用记录锁,并不会产生间隙锁:

SELECT * FROM child WHERE id = 100 FOR UPDATE;

这条语句,就只会产生记录锁,不会产生间隙锁。

唯一索引的间隙锁

CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');

-- 隐藏的间隙
(-infinity, 1](1, 5](5, 7](7, 11](11, +infinity]

只使用记录锁,不会产生间隙锁

/* 开启事务1 */
BEGIN;
/* 查询 id = 5 的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE;
/* 延迟30秒执行,防止锁释放 */
SELECT SLEEP(30);

注意:以下的语句不是放在一个事务中执行,而是分开多次执行,每次事务中只有一条添加语句

/* 事务2插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张'); # 正常执行

/* 事务3插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '小东'); # 正常执行

/* 提交事务1,释放事务1的锁 */
COMMIT;

上诉的案例,由于主键是唯一索引,而且是只使用一个索引查询,并且只锁定一条记录,所以以上的例子,只会对 id = 5 的数据加上记录锁,而不会产生间隙锁。

因为使用的是主键索引,并且只查询一条数据,所以使用的是行锁,没有产生间隙锁。

产生间隙锁

我们继续在 id 唯一索引列上做以下的测试:

/* 开启事务1 */
BEGIN;
/* 查询 id 在 7 - 11 范围的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;
/* 延迟30秒执行,防止锁释放 */
SELECT SLEEP(30);

注意:以下的语句不是放在一个事务中执行,而是分开多次执行,每次事务中只有一条添加语句

/* 事务2插入一条 id = 3,name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (3, '小张1'); # 正常执行

/* 事务3插入一条 id = 4,name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 正常执行

/* 事务4插入一条 id = 6,name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 阻塞

/* 事务5插入一条 id = 8, name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 阻塞

/* 事务6插入一条 id = 9, name = '大东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (9, '大东'); # 阻塞

/* 事务7插入一条 id = 11, name = '李西' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (11, '李西'); # 阻塞

/* 事务8插入一条 id = 12, name = '张三' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (12, '张三'); # 正常执行

/* 提交事务1,释放事务1的锁 */
COMMIT;

从上面我们可以看到,(5, 7]、(7, 11] 这两个区间,都不可插入数据,其它区间,都可以正常插入数据。所以我们可以得出结论:当我们给 (5, 7] 这个区间加锁的时候,会锁住 (5, 7]、(7, 11] 这两个区间。

因为我们的查询,是基于一个区间额查询,所以产生了间隙锁。

我们再来测试如果我们锁住不存在的数据时,会怎样:

/* 开启事务1 */
BEGIN;
/* 查询 id = 3 这一条不存在的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 3 FOR UPDATE;
/* 延迟30秒执行,防止锁释放 */
SELECT SLEEP(30);

注意:以下的语句不是放在一个事务中执行,而是分开多次执行,每次事务中只有一条添加语句

/* 事务2插入一条 id = 3,name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (2, '小张1'); # 阻塞

/* 事务3插入一条 id = 4,name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 阻塞

/* 事务4插入一条 id = 6,name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 正常执行

/* 事务5插入一条 id = 8, name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 正常执行

/* 提交事务1,释放事务1的锁 */
COMMIT;

我们可以看出,指定查询某一条记录时,如果这条记录不存在,会产生间隙锁。

结论

1. 对于指定查询某一条记录的加锁语句,如果该记录不存在,会产生记录锁和间隙锁,如果记录存在,则只会产生记录锁,如:WHERE id = 5 FOR UPDATE;
2. 对于查找某一范围内的查询语句,会产生间隙锁,如:WHERE id BETWEEN 5 AND 7 FOR UPDATE;

普通索引的间隙锁

1. 在普通索引列上,不管是何种查询,只要加锁,都会产生间隙锁,这跟唯一索引不一样;
2. 在普通索引跟唯一索引中,数据间隙的分析,数据行是优先根据普通索引排序,再根据唯一索引排序。

临键锁(Next-key Locks)

临键锁,是记录锁与间隙锁的组合,它的封锁范围,既包含索引记录,又包含索引区间。

注:临键锁的主要目的,也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC,临键锁则也会失效。

小结

1. 记录锁、间隙锁、临键锁,都属于排它锁;
2. 记录锁就是锁住一行记录;
3. 间隙锁只有在事务隔离级别 RR 中才会产生,临建锁也是在RR级别下才能生效。
4. 唯一索引只有锁住多条记录或者一条不存在的记录的时候,才会产生间隙锁,指定给某条存在的记录加锁的时候,只会加记录锁,不会产生间隙锁;
5. 普通索引不管是锁住单条,还是多条记录,都会产生间隙锁;
6. 间隙锁会封锁该条记录相邻两个键之间的空白区域,防止其它事务在这个区域内插入、修改、删除数据,这是为了防止出现 幻读 现象;
7. 普通索引的间隙,优先以普通索引排序,然后再根据主键索引排序(多普通索引情况还未研究);
8. 事务级别是RC(读已提交)级别的话,间隙锁将会失效。

1. InnoDB 中的行锁的实现依赖于索引,一旦某个加锁操作没有使用到索引,那么该锁就会退化为表锁。
2. 记录锁存在于包括主键索引在内的唯一索引中,锁定单条索引记录。
3. 间隙锁存在于非唯一索引中,锁定开区间范围内的一段间隔,它是基于临键锁实现的。
4. 临键锁存在于非唯一索引中,该类型的每条记录的索引上都存在这种锁,它是一种特殊的间隙锁,锁定一段左开右闭的索引区间。

select for update有什么含义,会锁表还是锁行还是其他

for update 仅适用于InnoDB,且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时,通过“for update”语句,MySQL会对查询结果集中每行数据都添加排他锁,其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁！ 假设有个表单 products ,里面有id跟name二个栏位,id是主键。

• 明确指定主键,并且有此笔资料,row lock

SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

• 明确指定主键,若查无此笔资料,无lock

SELECT * FROM products WHERE id='-1' FOR UPDATE;

• 无主键,table lock

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

• 主键不明确,table lock

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

• 主键不明确,table lock

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。 注2: 要测试锁定的状况,可以利用MySQL的Command Mode ,开二个视窗来做测试。

MySQL 遇到过死锁问题吗,你是如何解决的？

13、数据库死锁

核心观点：不同存储引擎由于锁机制不同，避免死锁的策略也完全不同。InnoDB最复杂（行锁+间隙锁），MyISAM最简单（表锁无死锁）。

死锁产生

• 死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环
• 当事务试图以不同的顺序锁定资源时,就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
• 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句,有些存储引擎会产生死锁有些不会——死锁有双重原因:真正的数据冲突;存储引擎的实现方式。

检测死锁:数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复:死锁发生以后,只有部分或完全回滚其中一个事务,才能打破死锁,InnoDB目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁,多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测:发生死锁后,InnoDB 一般都能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。但在涉及外部锁,或涉及表锁的情况下,InnoDB 并不能完全自动检测到死锁, 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能:死锁会影响性能而不是会产生严重错误,因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上,当许多线程等待同一个锁时,死锁检测可能导致速度变慢。 有时当发生死锁时,禁用死锁检测(使用innodb_deadlock_detect配置选项)可能会更有效,这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

MyISAM避免死锁

锁特性:

• 在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,所以 MyISAM 表不会出现死锁。

MyISAM特性：

1. 只有表级锁，没有行锁
2. 读锁共享，写锁排他
3. 没有死锁风险（因为表锁是单次获取）
4. 但并发性能差

InnoDB避免死锁

事务优化策略: 固定访问顺序、减少锁范围、使用较低隔离级别

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁,可以在事务开始时通过为预期要修改的每个元祖(行)使用SELECT ... FOR UPDATE语句来获取必要的锁,即使这些行的更改语句是在之后才执行的。
• 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁、更新时再申请排他锁,因为这时候当用户再申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁
• 如果事务需要修改或锁定多个表,则应在每个事务中以相同的顺序使用加锁语句。 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会
• 通过SELECT ... LOCK IN SHARE MODE获取行的读锁后,如果当前事务再需要对该记录进行更新操作,则很有可能造成死锁。
• 改变事务隔离级别

如果出现死锁,可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的 SQL 语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

索引优化策略：确保索引减少锁范围、 覆盖索引减少锁竞争、使用乐观锁代替悲观锁

死锁检测与自动回滚：

相关参数

-- 查看死锁检测设置
SHOW VARIABLES LIKE 'innodb_deadlock_detect';
-- 默认ON，启用死锁检测

-- 设置锁等待超时（秒）
SET GLOBAL innodb_lock_wait_timeout = 50;
-- 默认50秒，超时自动回滚

不同存储引擎死锁对比

策略	InnoDB	MyISAM	Memory	NDB
锁粒度	行锁+间隙锁	表锁	表锁	分布式行锁
死锁可能性	高	无	低	中
死锁检测	自动检测回滚	不适用	无	分布式检测
自动重试	应用层处理	不适用	不适用	支持
乐观锁支持	✅	❌	❌	✅
SKIP LOCKED	✅ 8.0+	❌	❌	❌
NOWAIT	✅ 8.0+	❌	❌	✅

数据库锁与隔离级别的关系

数据库锁类型有哪些？

按照锁的粒度可以将MySQL锁分为三种:

MyISAM默认采用表级锁,InnoDB默认采用行级锁。

什么是数据库的乐观锁和悲观锁,如何实现？

• 乐观锁:系统假设数据的更新在大多数时候是不会产生冲突的,所以数据库只在更新操作提交的时候对数据检测冲突,如果存在冲突,则数据更新失败。
  • 乐观锁实现方式:一般通过版本号和CAS算法实现。
• 悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。通俗讲就是每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁。
  • 悲观锁的实现方式:通过数据库的锁机制实现,对查询语句添加for updata。

什么是死锁？如何避免？

死锁是指两个或者两个以上进程在执行过程中,由于竞争资源或者由于彼此通信而造成的一种阻塞的现象。在MySQL中,MyISAM是一次获得所需的全部锁,要么全部满足,要么等待,所以不会出现死锁。在InnoDB存储引擎中,除了单个SQL组成的事务外,锁都是逐步获得的,所以存在死锁问题。

如何避免MySQL发生死锁或锁冲突:

• 如果不同的程序并发存取多个表,尽量以相同的顺序访问表。
• 在程序以批量方式处理数据的时候,如果已经对数据排序,尽量保证每个线程按照固定的顺序来处理记录。
• 在事务中,如果需要更新记录,应直接申请足够级别的排他锁,而不应该先申请共享锁,更新时在申请排他锁,因为在当前用户申请排他锁时,其他事务可能已经获得了相同记录的共享锁,从而造成锁冲突或者死锁。
• 尽量使用较低的隔离级别。
• 尽量使用索引访问数据,使加锁更加准确,从而减少锁冲突的机会
• 合理选择事务的大小,小事务发生锁冲突的概率更低
• 尽量用相等的条件访问数据,可以避免Next-Key锁对并发插入的影响。
• 不要申请超过实际需要的锁级别,查询时尽量不要显示加锁
• 对于一些特定的事务,可以表锁来提高处理速度或减少死锁的概率。

小结

InnoDB（最常用）

• ✅ 固定访问顺序
• ✅ 使用RC隔离级别
• ✅ 添加合适索引
• ✅ 使用SKIP LOCKED (8.0+)
• ✅ 短事务

MyISAM（读多写少）

• ✅ 避免并发写
• ✅ 使用LOW_PRIORITY
• ✅ 读写分离

Memory（临时数据）

• ✅ 尽快提交
• ✅ 避免复杂事务

NDB（分布式）

• ✅ 使用乐观锁
• ✅ 合理分区
• ✅ 设置重试机制

14、Mysql调优手段

日常工作中你是怎么优化SQL的？

SQL优化的一般步骤是什么,怎么看执行计划(explain),如何理解其中各个字段的含义？

如何写sql能够有效的使用到复合索引？

一条sql执行过长的时间,你如何优化,从哪些方面入手？

什么是最左前缀原则？什么是最左匹配原则？

MySQL常见性能分析手段

在优化MySQL时,通常需要对数据库进行分析,常见的分析手段有慢查询日志,EXPLAIN 分析查询,profiling分析以及show命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

性能瓶颈定位

我们可以通过 show 命令查看 MySQL 状态及变量,找到系统的瓶颈:

show status ——显示状态信息(扩展show status like ‘XXX’)

show variables ——显示系统变量(扩展show variables like ‘XXX’)

show innodb status ——显示InnoDB存储引擎的状态

show processlist ——查看当前SQL执行,包括执行状态、是否锁表等

mysqladmin variables -u username -p password——显示系统变量

mysqladmin extended-status -u username -p password——显示状态信息

Explain(执行计划)

使用 Explain 关键字可以模拟优化器执行SQL查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

可以获取到那些信息:

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

如何使用:

• Explain + SQL语句
• 执行计划包含的信息(如果有分区表的话还会有partitions)

各字段解释

• id(select 查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序)

  • id相同,执行顺序从上往下
  • id全不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
  • id部分相同,执行顺序是先按照数字大的先执行,然后数字相同的按照从上往下的顺序执行

• select_type(查询类型,用于区别普通查询、联合查询、子查询等复杂查询)

  • SIMPLE :简单的select查询,查询中不包含子查询或UNION
  • PRIMARY:查询中若包含任何复杂的子部分,最外层查询被标记为PRIMARY
  • SUBQUERY:在select或where列表中包含了子查询
  • DERIVED:在from列表中包含的子查询被标记为DERIVED,MySQL会递归执行这些子查询,把结果放在临时表里
  • UNION:若第二个select出现在UNION之后,则被标记为UNION,若UNION包含在from子句的子查询中,外层select将被标记为DERIVED
  • UNION RESULT:从UNION表获取结果的select

• table(显示这一行的数据是关于哪张表的)

• type(显示查询使用了那种类型,从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL )

  • system:表只有一行记录(等于系统表),是 const 类型的特例,平时不会出现
  • const:表示通过索引一次就找到了,const 用于比较 primary key 或 unique 索引,因为只要匹配一行数据,所以很快,如将主键置于 where 列表中,mysql 就能将该查询转换为一个常量
  • eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
  • ref:非唯一性索引扫描,范围匹配某个单独值得所有行。本质上也是一种索引访问,他返回所有匹配某个单独值的行,然而,它可能也会找到多个符合条件的行,多以他应该属于查找和扫描的混合体
  • range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是在你的where语句中出现了between、<、>、in等的查询,这种范围扫描索引比全表扫描要好,因为它只需开始于索引的某一点,而结束于另一点,不用扫描全部索引
  • index:Full Index Scan,index于ALL区别为index类型只遍历索引树。通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)
  • ALL:Full Table Scan,将遍历全表找到匹配的行

  tip: 一般来说,得保证查询至少达到range级别,最好到达ref

• possible_keys:(显示可能应用在这张表中的索引,一个或多个,查询涉及到的字段若存在索引,则该索引将被列出,但不一定被查询实际使用)

• key

  • 实际使用的索引,如果为NULL,则没有使用索引
  • 查询中若使用了覆盖索引,则该索引和查询的 select 字段重叠,仅出现在key列表中

key_len

• 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好
• key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

ref (显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值)

rows (根据表统计信息及索引选用情况,大致估算找到所需的记录所需要读取的行数)

Extra(包含不适合在其他列中显示但十分重要的额外信息)

1. using filesort: 说明mysql会对数据使用一个外部的索引排序,不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
2. Using temporary:使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3. using index:表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错,如果同时出现using where,表明索引被用来执行索引键值的查找;否则索引被用来读取数据而非执行查找操作
4. using where:使用了where过滤
5. using join buffer:使用了连接缓存
6. impossible where:where子句的值总是false,不能用来获取任何元祖
7. select tables optimized away:在没有group by子句的情况下,基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化
8. distinct:优化distinct操作,在找到第一匹配的元祖后即停止找同样值的动作

第一行(执行顺序4):id列为1,表示是union里的第一个select,select_type列的primary表示该查询为外层查询,table列被标记为,表示查询结果来自一个衍生表,其中derived3中3代表该查询衍生自第三个select查询,即id为3的select。【select d1.name......】

第二行(执行顺序2):id为3,是整个查询中第三个select的一部分。因查询包含在from中,所以为derived。【select id,name from t1 where other_column=''】

第三行(执行顺序3):select列表中的子查询select_type为subquery,为整个查询中的第二个select。【select id from t3】

第四行(执行顺序1):select_type为union,说明第四个select是union里的第二个select,最先执行【select name,id from t2】

第五行(执行顺序5):代表从union的临时表中读取行的阶段,table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

慢查询日志

MySQL 的慢查询日志是 MySQL 提供的一种日志记录,它用来记录在 MySQL 中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的 SQL,则会被记录到慢查询日志中。

• long_query_time 的默认值为10,意思是运行10秒以上的语句
• 默认情况下,MySQL数据库没有开启慢查询日志,需要手动设置参数开启

查看开启状态

SHOW VARIABLES LIKE '%slow_query_log%'

开启慢查询日志

• 临时配置:

set global slow_query_log='ON';
set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
set global long_query_time=2;

​ 也可set文件位置,系统会默认给一个缺省文件host_name-slow.log

​ 使用set操作开启慢查询日志只对当前数据库生效,如果MySQL重启则会失效。

• 永久配置:修改配置文件my.cnf或my.ini,在[mysqld]一行下面加入两个配置参数

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3

注:log-slow-queries 参数为慢查询日志存放的位置,一般这个目录要有 MySQL 的运行帐号的可写权限,一般都将这个目录设置为 MySQL 的数据存放目录;long_query_time=2 中的 2 表示查询超过两秒才记录;在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 参数,表示记录下没有使用索引的查询。

可以用 select sleep(4) 验证是否成功开启。

在生产环境中,如果手工分析日志,查找、分析SQL,还是比较费劲的,所以MySQL提供了日志分析工具mysqldumpslow。

通过 mysqldumpslow --help 查看操作帮助信息

• 得到返回记录集最多的10个SQL

  mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log

• 得到访问次数最多的10个SQL

  mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log

• 得到按照时间排序的前10条里面含有左连接的查询语句

  mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log

• 也可以和管道配合使用

  mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more

也可使用 pt-query-digest 分析 RDS MySQL 慢查询日志

Show Profile 分析查询

通过慢日志查询可以知道哪些 SQL 语句执行效率低下,通过 explain 我们可以得知 SQL 语句的具体执行情况,索引使用等,还可以结合Show Profile命令查看执行状态。

• Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

• 默认情况下,参数处于关闭状态,并保存最近15次的运行结果

分析步骤

1. 是否支持,看看当前的mysql版本是否支持

Show  variables like 'profiling';  --默认是关闭,使用前需要开启

2. 开启功能,默认是关闭,使用前需要开启

set profiling=1;

3. 运行SQL

4. 查看结果

5. 诊断SQL,show profile cpu,block io for query id(上一步前面的问题SQL数字号码)

6. 日常开发需要注意的结论

   1. converting HEAP to MyISAM 查询结果太大,内存都不够用了往磁盘上搬了。
   2. create tmp table 创建临时表,这个要注意
   3. Copying to tmp table on disk 把内存临时表复制到磁盘
   4. locked

性能优化

索引优化

1. 全值匹配我最爱
2. 最佳左前缀法则,比如建立了一个联合索引(a,b,c),那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select
6. is null ,is not null 也无法使用索引
7. like "xxxx%" 是可以用到索引的,like "%xxxx" 则不行(like "%xxx%" 同理)。like以通配符开头('%abc...')索引失效会变成全表扫描的操作,
8. 字符串不加单引号索引失效
9. 少用or,用它来连接时会索引失效
10. <,<=,=,>,>=,BETWEEN,IN 可用到索引,<>,not in ,!= 则不行,会导致全表扫描

那些关键字导致索引失效？

is null,is not null,like %模糊匹配,,or

一般性建议

• 对于单键索引,尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
• 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
• 少用Hint强制索引

查询优化

永远小标驱动大表(小的数据集驱动大的数据集)

slect * from A where id in (select id from B)`等价于
#等价于
select id from B
select * from A where A.id=B.id

当 B 表的数据集必须小于 A 表的数据集时,用 in 优于 exists

select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`

当 A 表的数据集小于B表的数据集时,用 exists优于用 in

注意:A表与B表的ID字段应建立索引。

order by关键字优化

• order by子句,尽量使用 Index 方式排序,避免使用 FileSort 方式排序
• MySQL 支持两种方式的排序,FileSort 和 Index,Index效率高,它指 MySQL 扫描索引本身完成排序,FileSort 效率较低;
• ORDER BY 满足两种情况,会使用Index方式排序;1、ORDER BY语句使用索引最左前列 2、使用where子句与ORDER BY子句条件列组合满足索引最左前列
• 尽可能在索引列上完成排序操作,遵照索引建的最佳最前缀
• 如果不在索引列上,filesort 有两种算法,mysql就要启动双路排序和单路排序
  • 双路排序:MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据
  • 单路排序:从磁盘读取查询需要的所有列,按照order by 列在 buffer对它们进行排序,然后扫描排序后的列表进行输出,效率高于双路排序
• 优化策略
  • 增大sort_buffer_size参数的设置
  • 增大max_lencth_for_sort_data参数的设置

GROUP BY关键字优化

• group by实质是先排序后进行分组,遵照索引建的最佳左前缀
• 当无法使用索引列,增大 max_length_for_sort_data 参数的设置,增大sort_buffer_size参数的设置
• where高于having,能写在where限定的条件就不要去having限定了

数据类型优化

MySQL 支持的数据类型非常多,选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据,下面几个简单的原则都有助于做出更好的选择。

• 更小的通常更好:一般情况下,应该尽量使用可以正确存储数据的最小数据类型。

  简单就好:简单的数据类型通常需要更少的CPU周期。例如,整数比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型比较复杂。

• 尽量避免NULL:通常情况下最好指定列为NOT NULL

分区,分库分表

Mysql分区

一般情况下我们创建的表对应一组存储文件,使用MyISAM存储引擎时是一个.MYI和.MYD文件,使用Innodb存储引擎时是一个.ibd和.frm(表结构)文件。

当数据量较大时(一般千万条记录级别以上),MySQL的性能就会开始下降,这时我们就需要将数据分散到多组存储文件,保证其单个文件的执行效率

能干嘛

• 逻辑数据分割
• 提高单一的写和读应用速度
• 提高分区范围读查询的速度
• 分割数据能够有多个不同的物理文件路径
• 高效的保存历史数据

如何使用

首先查看当前数据库是否支持分区

• MySQL5.6以及之前版本:

SHOW VARIABLES LIKE '%partition%';

MySQL5.6:

show plugins;

分区类型及操作

• RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。mysql将会根据指定的拆分策略,,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表,透明的。

  按照 range 来分,就是每个库一段连续的数据,这个一般是按比如时间范围来的,比如交易表啊,销售表啊等,可以根据年月来存放数据。可能会产生热点问题,大量的流量都打在最新的数据上了。

  range 来分,好处在于说,扩容的时候很简单。

• LIST分区:类似于按RANGE分区,每个分区必须明确定义。它们的主要区别在于,LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值,而RANGE分区是从属于一个连续区间值的集合。

• HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

  hash 分发,好处在于说,可以平均分配每个库的数据量和请求压力;坏处在于说扩容起来比较麻烦,会有一个数据迁移的过程,之前的数据需要重新计算 hash 值重新分配到不同的库或表

• KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

看上去分区表很帅气,为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

• 分区表,分区键设计不太灵活,如果不走分区键,很容易出现全表锁
• 一旦数据并发量上来,如果在分区表实施关联,就是一个灾难
• 自己分库分表,自己掌控业务场景与访问模式,可控。分区表,研发写了一个sql,都不确定mysql是怎么玩的,不太可控

随着业务的发展,业务越来越复杂,应用的模块越来越多,总的数据量很大,高并发读写操作均超过单个数据库服务器的处理能力怎么办？

这个时候就出现了数据分片,数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中。数据分片的有效手段就是对关系型数据库进行分库和分表。

区别于分区的是,分区一般都是放在单机里的,用的比较多的是时间范围分区,方便归档。只不过分库分表需要代码实现,分区则是mysql内部实现。分库分表和分区并不冲突,可以结合使用。

说说分库与分表的设计

MySQL分表

分表有两种分割方式,一种垂直拆分,另一种水平拆分。

• 垂直拆分

  垂直分表,通常是按照业务功能的使用频次,把主要的、热门的字段放在一起做为主要表。然后把不常用的,按照各自的业务属性进行聚集,拆分到不同的次要表中;主要表和次要表的关系一般都是一对一的。

• 水平拆分(数据分片)

  单表的容量不超过500W,否则建议水平拆分。是把一个表复制成同样表结构的不同表,然后把数据按照一定的规则划分,分别存储到这些表中,从而保证单表的容量不会太大,提升性能;当然这些结构一样的表,可以放在一个或多个数据库中。

  水平分割的几种方法:

  • 使用MD5哈希,做法是对UID进行md5加密,然后取前几位(我们这里取前两位),然后就可以将不同的UID哈希到不同的用户表(user_xx)中了。
  • 还可根据时间放入不同的表,比如:article_201601,article_201602。
  • 按热度拆分,高点击率的词条生成各自的一张表,低热度的词条都放在一张大表里,待低热度的词条达到一定的贴数后,再把低热度的表单独拆分成一张表。
  • 根据ID的值放入对应的表,第一个表user_0000,第二个100万的用户数据放在第二 个表user_0001中,随用户增加,直接添加用户表就行了。

MySQL分库

为什么要分库?

数据库集群环境后都是多台 slave,基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大,这个时候,单库并不能解决大规模并发写入的问题,所以就会考虑分库。

分库是什么？

一个库里表太多了,导致了海量数据,系统性能下降,把原本存储于一个库的表拆分存储到多个库上, 通常是将表按照功能模块、关系密切程度划分出来,部署到不同库上。

优点:

• 减少增量数据写入时的锁对查询的影响
• 由于单表数量下降,常见的查询操作由于减少了需要扫描的记录,使得单表单次查询所需的检索行数变少,减少了磁盘IO,时延变短,但是它无法解决单表数据量太大的问题

分库分表后的难题

分布式事务的问题,数据的完整性和一致性问题。

数据操作维度问题:用户、交易、订单各个不同的维度,用户查询维度、产品数据分析维度的不同对比分析角度。 跨库联合查询的问题,可能需要两次查询 跨节点的count、order by、group by以及聚合函数问题,可能需要分别在各个节点上得到结果后在应用程序端进行合并 额外的数据管理负担,如:访问数据表的导航定位 额外的数据运算压力,如:需要在多个节点执行,然后再合并计算程序编码开发难度提升,没有太好的框架解决,更多依赖业务看如何分,如何合,是个难题。

配主从,正经公司的话,也不会让 Javaer 去搞的,但还是要知道

主从复制

复制的基本原理

• slave 会从 master 读取 binlog 来进行数据同步
• 三个步骤
  1. master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件,binary log events;
  2. salve 将 master 的 binary log events 拷贝到它的中继日志(relay log);
  3. slave 重做中继日志中的事件,将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

复制的基本原则

• 每个 slave只有一个 master
• 每个 salve只能有一个唯一的服务器 ID
• 每个master可以有多个salve

复制的最大问题

• 延时

百万级别或以上的数据如何删除

关于索引:由于索引需要额外的维护成本,因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以,在我们删除数据库百万级别数据的时候,查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

1. 所以我们想要删除百万数据的时候可以先删除索引(此时大概耗时三分多钟)
2. 然后删除其中无用数据(此过程需要不到两分钟)
3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快,约十分钟左右。
4. 与之前的直接删除绝对是要快速很多,更别说万一删除中断,一切删除会回滚。那更是坑了。

数据库优化

大表如何优化？

1. 限定数据的范围:避免不带任何限制数据范围条件的查询语句。
2. 读写分离:主库负责写,从库负责读。
3. 垂直分表:将一个表按照字段分成多个表,每个表存储其中一部分字段。
4. 水平分表:在同一个数据库内,把一个表的数据按照一定规则拆分到多个表中。
5. 对单表进行优化:对表中的字段、索引、查询SQL进行优化。
6. 添加缓存

什么是垂直分表、垂直分库、水平分表、水平分库？

垂直分表:

将一个表按照字段分成多个表,每个表存储其中一部分字段。一般会将常用的字段放到一个表中,将不常用的字段放到另一个表中。

垂直分表的优势:

• 避免IO竞争减少锁表的概率。因为大的字段效率更低,第一数据量大,需要的读取时间长。第二,大字段占用的空间更大,单页内存储的行数变少,会使得IO操作增多。
• 可以更好地提升热门数据的查询效率

垂直分库:

按照业务对表进行分类,部署到不同的数据库上面,不同的数据库可以放到不同的服务器上面。

垂直分库的优势:

• 降低业务中的耦合,方便对不同的业务进行分级管理。
• 可以提升IO、数据库连接数、解决单机硬件资源的瓶颈问题

垂直拆分(分库、分表)的缺点:

• 主键出现冗余,需要管理冗余列
• 事务的处理变得复杂
• 仍然存在单表数据量过大的问题

水平分表:

• 在同一个数据库内,把同一个表的数据按照一定规则拆分到多个表中。

水平分表的优势:

• 解决了单表数据量过大的问题
• 避免IO竞争并减少锁表的概率

水平分库:

• 把同一个表的数据按照一定规则拆分到不同的数据库中,不同的数据库可以放到不同的服务器上

水平分库的优势:

• 解决了单库大数据量的瓶颈问题
• IO冲突减少,锁的竞争减少,某个数据库出现问题不影响其他数据库(可用性),提高了系统的稳 定性和可用性

水平拆分(分表、分库)的缺点:

• 分片事务一致性难以解决
• 跨节点JOIN性能差,逻辑会变得复杂
• 数据扩展难度大,不易维护

在系统设计时应根据业务耦合来确定垂直分库和垂直分表的方案,在数据访问压力不是特别大时应考虑缓存、读写分离等方法,若数据量很大,或持续增长可考虑水平分库分表,水平拆分所涉及的逻辑比较复杂,常见的方案有客户端架构和恶代理架构。

分库分表后,ID键如何处理？

分库分表后不能每个表的ID都是从1开始,所以需要一个全局ID,设置全局ID主要有以下几种方法:

• UUID:优点:本地生成ID,不需要远程调用;全局唯一不重复。缺点:占用空间大,不适合作为索引。
• 数据库自增ID:在分库分表表后使用数据库自增ID,需要一个专门用于生成主键的库,每次服务接收到请求,先向这个库中插入一条没有意义的数据,获取一个数据库自增的ID,利用这个ID去分库分表中写数据。优点:简单易实现。缺点:在高并发下存在瓶颈。系统结构如下图(图片来源于网络)

• Redis生成ID:优点:不依赖数据库,性能比较好。缺点:引入新的组件会使得系统复杂度增加
• Twitter的snowflake算法:是一个64位的long型的ID,其中有1bit是不用的,41bit作为毫秒数,
  • 10bit作为工作机器ID,12bit作为序列号。
  • 1bit:第一个bit默认为0,因为二进制中第一个bit为1的话为负数,但是ID不能为负数.
  • 41bit:表示的是时间戳,单位是毫秒。
  • 10bit:记录工作机器ID,其中5个bit表示机房ID,5个bit表示机器ID。
  • 12bit:用来记录同一毫秒内产生的不同ID。
• 美团的Leaf分布式ID生成系统 :[美团点评分布式ID生成系统 ](Leaf——美团点评分布式ID生成系统 - 美团技术团队 (meituan.com))

MySQL的复制原理及流程？如何实现主从复制？

MySQL复制:为保证主服务器和从服务器的数据一致性,在向主服务器插入数据后,从服务器会自动将主服务器中修改的数据同步过来。

主从复制的原理:

主从复制主要有三个线程:binlog线程,I/O线程,SQL线程。

• binlog线程:负责将主服务器上的数据更改写入到二进制日志(Binary log)中。
• I/O线程:负责从主服务器上读取二进制日志(Binary log),并写入从服务器的中继日志(Relaylog)中。
• SQL线程:负责读取中继日志,解析出主服务器中已经执行的数据更改并在从服务器中重放

复制过程如下(图片来源于网络):

1. Master在每个事务更新数据完成之前,将操作记录写入到binlog中。
2. Slave从库连接Master主库,并且Master有多少个Slave就会创建多少个binlog dump线程。当Master节点的binlog发生变化时,binlog dump会通知所有的Slave,并将相应的binlog发送给Slave。
3. I/O线程接收到binlog内容后,将其写入到中继日志(Relay log)中。
4. SQL线程读取中继日志,并在从服务器中重放。

补充图

主从复制的作用:

1. 高可用和故障转移
2. 负载均衡
3. 数据备份
4. 升级测试

了解读写分离吗？

读写分离主要依赖于主从复制,主从复制为读写分离服务。

读写分离的优势:

• 主服务器负责写,从服务器负责读,缓解了锁的竞争
• 从服务器可以使用MyISAM,提升查询性能及节约系统开销
• 增加冗余,提高可用性

贡献者

codingLab

版权所有

版权归属：codingLab

许可证：bugcode