MySQL 非关系型数据库底层剖析-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_45065902/article/details/144581704

1. MySQL索引

1.1. 概要

索引是帮助MySQL提高获取数据的排好序的数据结构

1.2. 索引分类

1.2.1. 功能逻辑区分

1.2.1.1. 普通索引

最基本的索引类型，没有唯一性的限制，主要用于提高查询性能。一个表可以有多个普通索引

这个例子为users表的username列创建了一个普通索引，用来加速基于username的查询

CREATE INDEX idx_username ON users (username);

1.2.1.2. 唯一索引

表中的列创建了唯一约束时，数据库会自动建立唯一索引，索引列中的值必须是唯一的，但是允许为空值。

单独创建和删除唯一索引的语法：
创建：alter table 表名 add unique 索引名(字段)或 create unique index 索引名 on 表名(字段)
删除：drop index 索引名 on 表名

1.2.1.3. 主键索引

表中的列设定为主键后，数据库会自动建立主键索引，索引列中的值必须是唯一的，不允许有空值

单独创建和删除主键索引的语法
创建：alter table 表名 add primary key(字段)
删除：alter table 表名 drop primary key

CREATE TABLE users (

id INT AUTO_INCREMENT,

username VARCHAR(50) NOT NULL,

email VARCHAR(100) NOT NULL,

PRIMARY KEY (id)

);

id列被定义为主键，MySQL会自动为这个列创建一个主键索引

1.2.1.4. 全文索引

专门用于全文搜索的索引。只有在MyISAM和InnoDB（从MySQL 5.6版本开始支持）存储引擎的CHAR、VARCHAR或TEXT类型列上才能创建全文索引。

CREATE FULLTEXT INDEX ft_idx_content ON articles (content);

articles表的content列上创建了一个全文索引，用于全文搜索

1.2.2. 物理逻辑区分

1.2.2.1. 聚簇索引（只有一个）

索引文件和数据文件是存储在同一个文件的
聚簇索引是通过主键聚集数据，若定义了主键，则主键索引就为聚簇索引。若没定义主键，则表中第一个非空唯一的列作为聚簇索引。都不满足时，InnoDB会建一个隐藏列row-id作为聚簇索引。所以InnoDB引擎的表要求必须有聚簇索引（主键索引）

聚簇索引就是包含行数据的索引，例如主键索引，它既包含了主键ID的数据，也包含了整行数据。所以它就是聚簇索引

聚簇索引能够直接通过索引就能获取到所需要的行数据，无需再次回查主键ID索引

聚簇索引值存在一个，二级索引（非聚簇索引结构）可以有多个

1.2.2.2. 非聚簇索引

索引文件和数据文件是分离的。索引文件存储B+Tree结构，数据文件存储表中的数据行
索引文件是按照索引键值和表数据内存地址构建的B+Tree，其结构的叶子节点存储了索引列的值和指向数据文件中记录的物理位置（通常磁盘地址）的指针
执行查询时，会利用非聚簇索引中的索引列值对B+Tree从根节点逐层查找，找到叶子节点。从叶子节点中获取记录的物理位置（磁盘地址）找到数据文件，从数据文件中获取响应的记录。当索引覆盖扫描时，可以直接从索引文件中返回这些值，无需再访问数据文件。

MyISM采用的是非聚簇索引

1.2.3. 作用字段区分

1.2.3.1. 单列索引（单值索引）

一个索引只包含单个列，一个表可以有多个单值索引。

创建：alter table 表名 add index 索引名(字段) 或

create index 索引名 on 表名(字段)

删除：drop index 索引名 on 表名

1.2.3.2. 联合索引（组合索引）

一个索引包含多个列

创建：create index 索引名 on 表名(字段1，字段2)
或 alter table 表名 add index 索引名(字段1，字段2)
删除：drop index 索引名 on 表名

1.3. 索引数据结构

1.3.1. 二叉树

所有非叶子节点至多有2个子节点
非叶子节点的左指针指向比其小的数，右指针指向比该关键字大的数
每个节点存储关键字和数据

缺点：如果MySQL使用二叉树来构建MySQL索引，打个比方，如果索引为 1,2,3,4,5,6 那么根据二叉树构建出来的二叉树如下

因为1是在这当中是最小的，所以1作为索引的根节点，构建出来的二叉树的高度为6，因此当我们查找索引为6的关键字是，那么我们对磁盘的进行6次的I/O（交互），假设每次对磁盘的读取时间为10毫秒，那么就要花费60毫秒才能找到该关键字

1.3.2. 红黑树（Red Black Tree）

红黑树具有良好的平衡性，查询效率较高，比较适用于内存中的数据结构
红黑树根二叉树类似，每个节点值存储一个关键字和数据
如果需要存储更多的数据，那么就需要更多的节点，更大的高度，随着那个数据量的增多，对内存的开销也越来越大，对磁盘的I/O次数也会越来越多，会影响索引的查询效率。

查找关键字 6 ，那么我们需要3次I/O，这在数据量小的时候还好，但是到了数据量多的时候，它的I/O次数也会增多。

1.3.3. Hash表

对索引的key 进行一次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+树索引更高效
仅能满足 “=” 和 “IN”，不支持范围查询
有Hash冲突问题

1.3.4. B-Tree

B树每个叶子节点都存储了关键字和数据（例如物理地址），每个叶子节点具有相同的深度，MySQL默认索引页的大小大概是16KB左右，互联网数据我们按照每行1KB左右来计算，那索引页最多只能存储16条数据。这就意味着，如果我们需要存储更多的数据，那就需要高度更高的树结构来完成。
同时，因为B树中，虽然节点中的索引是从左到右按大小排序的，但是节点之间没有指针连接，这就意味着，如果需要查询一个范围中的数据，一旦一个节点中的数据读取完毕，进行下次查询时，仍然需要从头节点进行查找下一个节点所在的位置，也就是说对范围查找无法得到更好的支持。

如果我们查找关键字6，那么只需要对磁盘进行2次I/O即可查询到我们需要的数据。相对于二叉树的 6次，红黑树的3次，效率又提高了一个档次。

1.3.5. B+Tree

B+数在非叶子节点中只存储关键字，不存储数据，每个节点可以存储更多的数据和指针
所有叶子节点冗余了索引关键字所在的数据行，意味更低的高度可以存储更多的数据
非叶子节点不存储数据，只存储索引（冗余），可以存放更多的索引
叶子节点包含所有索引字段
叶子节点用指针相连起来，提高了区间访问的速度

比如MySQL的索引页大小大概是16KB，非叶子节点的关键字索引和数据大概占用（8+6）个字节，那么每个非叶子节点所能存储的数据个数为 16KB / 14B 大概可以存储1170个索引数据，如果树的高度为3，因为MySQL索引页大小为16KB，那么最终高度为3的B+树所能存储的数据量为1170 * 1170 * 16（假设一行数据为1KB，那MySQL索引页大小大概是16KB，那叶子节点就能存储16条数据），大概是2千多万的数据量

同时，B+数的叶子节点构成了一个有序的链表，叶子节点之间存有相互指针，那么对于范围区间的的查询效率比B树高很多。

1.3.6. 为什么MySQL使用B+树结构做数据索引？

B+树是多路平衡查找树，每个节点可以存储多个关键字和数据，因此可以在树高度较低的情况下，从而减少磁盘的 I/O次数，提高检索的效率。对比二叉树跟红黑树的节点，只能存储一个关键字和数据，因此二者需要更多的节点来存储相同的数据量。节点多势必会导致增加I/O 操作，这就会降低效率。
B+树具有很好的顺序性，因此可以提高区间查找的效率。由于B+树的叶子节点构成一个有序链表，因此在执行范围查询的时候，只需要遍历有序链表就行，不需要全表扫描，从而提高了查询的效率。相比较Hash就无法支持范围查找，对比B树来说，意味B树的叶子节点没有指针相连，无法知道其他节点的地址，因此，在完成一个节点的查询之后，需要再次从根节点进行I/O 定位查询下一个范围节点。
B+树可以减少索引更新的代价。由于B+树的非叶子节点只存储关键字，而不存储数据，因此在更新索引是，只需要更新叶子节点，而不需要更新非叶子节点，这可以减少索引更新的代价。
B+树对于大型数据集的存储和查询效率更高。由于B+树具有良好的平衡性和顺序性，因此在存储和查询大型数据集时，B+树的效率更高，能够更好的应对大规模数据的存储和查询的需求。

1.4. 索引设计原则

1.4.1. 代码先行，索引后上

并不是在建立表的时候就马上建立索引，应该等主体业务功能开发完毕后，把设计到该表的SQL拿出来分析，然后再创建索引

1.4.2. 联合索引尽量覆盖条件

尽量减少单值索引，让每个联合索引尽量包含 where、order by、group by的字段，还要确保联合索引尽量满足SQL查询的最左前缀法则

1.4.3. 不要在小基数上建立索引

索引基数：指这个字段表里总共有多少个不同的值，比如100万行记录，其中有个性别字段，其值不是男就是女，那这个字段的基数是：2。如果对这种小基数建立索引的话，还不如全表扫描了，因为你的索引树里包含男和女两种值，根本没办法进行快速二分查找，使用索引就没有太大的意义了。

一般建立索引，尽量使用哪些基数比较大的字段，就是值比较多的字段，这样才能充分发挥出B+树快速二分查找的优势

1.4.4. 长字符串我们可以采用前缀索引

对于varchar(255)的大字段做索引可能会比较占用磁盘空间，我们可以针对这个字段的前20个字符进行建立索引，也就是将这个字段的前20个字符放在索引树中 index(name(20), position)

1.4.5. where 和 order by 冲突时优先where

一般这种情况往往都是让where条件去使用索引来快速筛选出来一部分指定数据，接着再排序。因为大多数情况基于索引进行where筛选往往可以快速筛选出你想要的少部分数据，然后再做排序的成本可能会小很多。

1.4.6. 基于慢SQL查询做优化

可以根据监控一些慢SQL，针对这些慢SQL查询做特定的索引优化

1.5. 索引失效场景

没有遵循最左前缀原则

创建的联合索引：（age,sex,name）查询失效的语句：select * from t1 where age = 18 and name = 'Lilei';

在索引列上做了操作（计算、函数、手动或自动类型转换）

select * from emp left(name, 3) = 'Lilei';
select * from employees where date(hire_time) ='2018-09-30';

mysql在使用不等于(!= 或 <>)、not in、not exist 的时候会索引失效

小于（>）、大于（>）、>=、<=这些，mysql内部优化器会根据检索的比例、表大小等多个因素整体评估是否使用索引

is null、is not null也会导致索引失效

like 使用通配符（%）也会导致索引失效

如果该字段属性是字符串，在作为条件查询的时候不加单引号‘’ 也会导致索引失效

联合索引第一个字段使用范围查找不会使用索引

mysql认为第一个字段就使用范围查找，那结果集应该很大，回表效率不高，因此不如选择全表扫描

in 和 or 在表数据量大的情况下会走索引，不大的情况下，使用全表扫描效率会更高

2. MySQL事物

2.1. 概述

事务是一组要么全部成功，要么全部失败的一组操作，目的是为了保证数据最终的一致性

2.2. 事务属性（ACID）

2.2.1. 原子性（Atmicity）

当前这组事务，要么全部成功，要么全部失败。是由undo log日志保证的

2.2.2. 一致性（Consistent）

一致性是我们使用事务的最终目的，事务得到一致性是由其它的三个特性以及我们的代码的正确逻辑来保证的

2.2.3. 隔离性（Lsolation）

事务并发操作时，事务之间是不受影响的，互不干扰的。该属性是由MySQL的各种锁以及MVCC（多版本并发控制机制）来实现的

2.2.4. 持久性（Durable）

一旦我们这个事务提交了(commit)，那它对数据的修改是持久性的。该属性由redo log日志来保证

2.3. 事务的隔离级别

2.3.1. 读未提交

事务A 读到了事务B未提交的新增的数据，假如事务B后续因为一些原因执行了回滚操作，那事务A读到的这条数据就是脏数据。

影响：

脏读、不可重复读、幻读

2.3.2. 读已提交

事务A读到了事务B已经提交的新增的数据，会造成幻读的问题。

事务A在执行第一次查询操作，查询到了2条数据，然后再事务A执行第二次查询之前，事务B执行了新增操作并且提交了commit，那事务A就读到了事务B提交的数据。

会造成幻读：读取到别人新增的提交的数据，导致我们出现幻觉一样

不可重复读：对之前的数据不能够再次读取，每次读取到的都是数据库已经提交的最新的数据

2.3.3. 可重复读

事务A在任何情况下读取到的数据都是一样的，之前读到什么数据，再次去读读取的时候也是一样的。不会受到其他事物的影响。

事务A在任何时刻读取到的数据都是一样的，即使事务B新增的数据并且已经提交了，在该隔离级别下，事务A读到到的数据都是一样的，不受其它事务的影响。该隔离级别由MVCC机制来实现

2.3.4. 串行化

串行化就是让事务排队执行，解决了脏读、幻读、不可重复读的问题

2.4. 事物引发的问题

2.4.1. 脏读

事务A读取到了事务B新增的未提交的数据

2.4.2. 幻读

事务A读取到了事务B新增的已经提交的数据

2.4.3. 不可重复读

事务A在不同时刻读取到的数据不一样

2.4.4. 数据丢失

最后执行的事务会将当前事务之前的其他事务的数据覆盖掉，导致数据丢失

2.5. 事务优化

避免大事务

大事务的影响

并发情况下，数据库连接池容易被撑bao
锁定太多的数据，容易造成大量的阻塞和等待（更新操作会加锁）
执行时间长，容易造成主从延迟
一旦事务需要会滚，那么需要回滚时间就会比较长
undo log 膨胀
容易导致死锁

将查询等数据操作放到事务外
事务中尽量避免远程调用，如果一定要远程调用，要设置超时时间，防止事务等太久
事务尽量避免一次性处理太多的数据，可以拆分成多个事务依次处理
更新等升级加锁的操作尽量在事务靠后的位置
能异步处理的尽量一步处理
应用侧（业务代码）保证数据一致性，非事务执行

3. MySQL锁机制

3.1. 概述

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

3.2. 锁的分类

3.2.1. 锁性能分类

悲观锁

定义：

每次数据操作都假设是最坏的情况，每次操作都认为别人会修改。所以每次在获取数据时都会加上锁。因此线程想要获取这条数据就必须获取到对应的锁

场景：

写操作比较多的场景

特点：

优点：采用了“先取锁后访问”的策略，为数据处理安全提供了保证

缺点：

降低效率：由于每次操作都会额外的枷锁，因此会产生额外的开销，降低效率
会产生死锁。
降低并发性：其他事务要处理当前这条数据需要等待当前事务提交之后才能操作这条数据。

乐观锁

定义：

每次操作都保持一个乐观的态度，操作数据时不会对操作的数据进行加锁。使用版本对比或CAS机制

场景：

读操作比较多的场景

特点：

优点：无需加锁操作，并发性能高，不会阻塞读操作，事务冲突低

缺点：如果在写操作比较多的场景使用乐观锁会导致对比次数过多，影响性能

3.2.2. 对数据操作粒度分类

表锁

定义：

每次将整张表锁住。
开销小，加锁快，不会出现死锁；
锁的粒度最大，并发度最小，出现锁冲突概率最大

场景：

适用于整张表迁移的场景下

页锁

定义：

锁的粒度是针对数据页的粒度上，锁住的数据要比行锁锁的数据多，因锁锁住的是一页数据，因此页锁会造成数据浪费的现象，但是这种浪费最多只会对一页的数据。开销介于表锁和行锁中间，会出现死锁，并发度一般
页锁（BDB存储引擎特有）

行锁

定义

每次锁住一行记录，开销大，加锁慢，
锁的粒度力度最小，会出现死锁，出现锁冲突的概率最低，
并发度最高
针对表中的某一行记录进行加锁，Innodb行锁是针对索引字段加的锁（给索引字段加上标记），不是针对整行记录进行加锁，如果索引失效，那在可重复读的隔离级别下，会从行锁升级为表锁，在RC隔离级别下不会升级为表锁

对数据库操作的类型上

- 读锁（共享锁， S锁（Shared））

- - 针对同一份数据，多个读操作可以同时进行不受影响
  - select * from table where id=1 lock in share mode

- 写锁（排它锁， X锁（eXclusive））

- - 当前操作没有完成前，针对该条数据操作的读操作或写操作都会被阻塞，update 操作mysql底层都会默认在sql语句后面加 for update 写锁，查询也可以➕写锁（select * from table where id=1 for update）

- 意向锁（I锁（Intention Lock））

- - 概述

- - - 意向锁又称 I锁，针对表锁，主要是为了提高加表锁的效率，是MySQL数据库自己加的。当有事务给表的数据行加了共享锁或拍他锁，同时会给表设置一个标识，代表已经有了行锁，当有其它事务想给表加表锁的以后，就不需要对表中的数据逐行对比是否加了行锁可能会跟表锁冲突了，直接获取这个标识（意向锁）就可以了，特别是针对表的数据很多的时候，大大提高了效率。

- - 分类

- - - 意向排它锁（IX锁）
    - 意向共享锁（IS锁））

3.2.3. 对数据库操作类型上

读锁（共享锁、 S锁-shared）

针对同一份数据，多个读操作可以同时进行不受影响
select * from table where id=1 lock in share mode

写锁（排他锁、 X锁-eXclusive ）

当前操作没有完成前，针对该条数据操作的读操作或写操作都会被阻塞，update 操作mysql底层都会默认在sql语句后面加 for update 写锁
查询也可以➕写锁（select * from table where id=1 for update）

意向锁（I锁 - Intention Lock）

意向锁又称 I锁，针对表锁，主要是为了提高加表锁的效率，是MySQL数据库自己加的。
当有事务给表的数据行加了共享锁或拍他锁，同时会给表设置一个标识，代表已经有了行锁，当有其它事务想给表加表锁的以后，就不需要对表中的数据逐行对比是否加了行锁可能会跟表锁冲突了，直接获取这个标识（意向锁）就可以了，特别是针对表的数据很多的时候，大大提高了效率。

分类

意向排它锁（IX锁）
意向共享锁（IS锁）

3.2.4. 间隙锁（Gap Lock）

概述

概述：锁的是两个值之间的数据（x, y），只要在间隙范围内锁了一条不存在的记录，就会锁住整个间隙范围，不锁边界，防止其它Session在这个间隙范围插入数据，解决了可重复读隔离级别下幻读的问题。

- 如：1、表account数据有id = 1，id=5, id =10的3条数据，在可重复读隔离级别下执行 select * from account where id = 8 for update; 其它事务就没法在(5, 10)这个id间隙范围插入任何数据。2、执行 select * from account where id = 15 for update; 那在区间 (10, 正无穷）的id间隙范围内是无法插入任何数据的。3、 3.1. 根据索引列作为查询条件，查询一条不存在的记录，会将该记录范围的间隙都锁住（左开右开） 3.2. 根据非索引列查询一条不存在的记录，会将整表的间隙都锁住

生效隔离级别：可重复读隔离级别（有幻读的可能）阻塞insert操作

3.2.5. 临键锁

行锁 + 间隙锁（x, y] 左开右闭

3.3. 锁优化实践

尽可能让所有数据检索操作通过索引来完成，避免无索引列引发行锁升级为表锁
合理设计索引，尽量缩小锁的粒度和范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务的大小，减少锁定的资源和时间长度，涉及事务加锁的SQL尽量放在事务最后执行
尽可能用低的事务隔离级别

4. MVCC多版本并发控制机制

4.1. 定义

MVCC多版本控制机制是为了保证事务在不同的隔离级别下，其他事务对数据的修改也不会影响当前事务SQL查询结果。例如在MySQL的 读已提交和可重复读 隔离级别下，都是基于MVCC机制来保证事务之间的隔离性的。

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

4.2. undo日志版本链

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

4.3. read view机制

当前事务的一致性视图
在可重复读隔离级别下：

- 事务A开启后，当我们执行第一条查询SQL语句会生成属于当前事务的一致性视图（read-view）,该一致性视图在事务A执行commit操作之前永远都不会变化。
- 一致性视图（read-view）的组成：由执行第一条SQL查询时的所有未提交事务ID数组（数组中最小的ID为mix_id）和已创建的最大的事务ID（max_id）组成（min_id, max_id），在事务A中的SQL查询结果需要从对应的undo版本链中最新的一条数据开始逐条跟read-view做对比，从而得到最终的快照结果

在读已提交隔离级别下：每次执行查询SQL时都会重新生成一个新的一致性视图read-view

4.4. 版本链对比规则

1、如果trx_id < min_id ，证明事务id不在我们的一致性视图范围内，并且该事务id在我们未提交的事务范围之前生成的，表示这个已提交的事务，数据是可见的

2、如果 trx_id > max_id，那证明该事务ID是在我们当前事务之后生成的，数据不可见

3、如果 minx_id <= trx_id <= max_id，分情况而定

3.1. 如果trx_id虽然在我们这个区间数组范围内，但是trx_id又不存在我们这个一致性视图数组中，那证明是已提交的事务，因为我们的一致性视图存储的是未提交的事务id，那trx_id在范围内，但是不在数组中，那证明它是已提交的事务ID，数据是可见的。

3.2. 如果trx_id落在一致性视图范围内并且存在一致性视图数组中，那它就是未提交的事务ID，数据是不可见的

例子

一致性视图：[ min_trx_id, max_trx_id ] 是所有未提交的事务的ID数组，和当时生成一致性视图的最大的一个事务ID组成。例如：当时生成一致性视图有有以下事务ID：

100（已提交）150（未提交）160（已提交）165（未提交） 170（已提交）180（未提交）200（未提交） ..... 500（将来事务ID）

假如执行查询操作是在trx_id = 100 之后，trx_id = 150 之前这个时间内，

那么生成的一致性视图为：[ min_trx_id, max_trx_id ] -> [150, 165，180，200]

小于 min_trx_id 的都是可见的

那这时候trx_id = 100的事务ID不在一致性视图内，并且小于最小的未提交的事务ID，那trx_id = 100的对数据的修改对于当前事务来说是可见的。

min_trx_id <= trx_id <= max_trx_id

1. trx_id = 160 判断是在一致性视图 [150, 200]这个区间，但是它并不在这个一致性视图数组内，因为一致性视图数组存储的是未提交的事务ID，trx_id=160是已经提交的，因此在范围内，但是不包含这个160。那证明trx_id=160事务ID对数据的修改结果是可见的。
2. trx_id = 160 或者 trx_id=170 在一致性视图范围内，并且包含于一致性视图中，也就是这些未提交的事务ID中包含了这两个事务ID，那代表就是未提交的事务，数据是不可见的。