MySQL索引锁存储引擎

阅读数: 次 2021-06-18

数据库索引

何为索引？为什么要使用索引？

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

查询数据最基本的方式为全表扫描，即将整张表的数据全部或者分批次加载到内存中，磁盘存储的最小单位为块(512Byte)，由多行数据来组成。我们在查询数据时将块加载进内存，然后逐个块的去轮寻，找到目标数据即返回，这种查询方式是非常慢的，所以我们就需要引入一种更高效的查询机制即索引。索引的灵感来源于字典，类似于字典的目录。

索引的优缺点

优点

使用索引可以大大加快数据的检索速度（大大减少了检索的数据量）, 这也是创建索引的最主要的原因。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点

创建索引和维护索引需要消耗许多资源，当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
在数据量比较小的时候，直接加载进内存全表扫描要比索引查询方式更好。

索引底层的数据结构

二叉查找树

索引结构为什么不使用二叉查找树

影响数据库查询效率的因素主要与时间复杂度和IO次数有关。二叉树会面临特殊的情况即向一侧倾斜，这样就增加了时间复杂度。虽然采用平衡二叉树可以解决二叉树向一侧倾斜的情况，但是仍然存在效率问题。即索引块都在磁盘中，查询数据时会首先将根数据读入内存中，之后再逐渐二分查找读入数据发生IO，即检索的深度每增加1就会增加一次IO，平衡二叉树或者红黑树每个节点最多有2个孩子，但实际上数据库的数据块会非常多，因此树的深度会非常深，IO的次数自然巨大，这样一来检索的性能甚至会比全表扫描慢很多，无法满足优化查询的需求。

B 树 & B+树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体，其中B 即为 Balanced的意思。目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B树

B树根节点至少包含两个孩子
树中每一个节点最多包含m个孩子(m >= 2)
除根节点和叶子节点外，其他每个节点至少有ceil(m / 2) 个孩子(ceil -> 向上取整)
所有的叶子节点都位于同一层

B+树

非叶子节点的子树指针与关键字个数相同
非叶子节点的子树指针P[i]，指向关键字值 (k[i], k[i + 1])的子树
非叶子节点仅用来索引数据，数据都保存在叶子节点中
所有叶子节点均有一个链指针指向下一个叶子节点

相比于B树，B+树更适合做存储索引

B+树的磁盘读写代价更低
B+树的查询效率更稳定
B+树更有利于对数据库的扫描

B+-树内部结构并没有指向关键字具体信息的指针，即不存放数据只存放索引信息。因此内部节点相对于B-树更少，如果把所有同一内部节点的关键字放在同一盘块中，该盘块所能容纳的关键字个数也越多，一次性读入内存中需要查找的关键字也就越多，相对来说IO读写次数就降低了。

B+-树内部的节点并不是最终指向文件内容的节点，而只是叶子节点中关键字的索引。任何关键字的索引必须走一条从根节点到叶子节点的路径，所有关键字查找的路径长度相同，所以几乎每一个数据的查询效率都相同(O(logn)).

B树在提高了磁盘的IO性能的同时，并没有解决元素遍历效率的问题。而B+-Tree只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以对于数据库中频繁使用的范围查询B+-Tree更合适。

Hash表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据(时间复杂度接近O(1))

1 2	hash = hashfunc(key) index = hash % array_size

Hash索引根据Hash函数计算，只需要一次计算，便能够找到查询数据所在的头，并不像B+树从根节点再到叶子节点的定位。理论上Hash索引的查询效率更高，但是由于Hash本身的特性也带来了很多的局限和弊端：

仅仅能满足“=” ，“IN” ，不能使用范围查询
无法被用来避免数据的排序操作
不能利用部分索引键查询
不能避免全表扫描
遇到大量的Hash值相等的情况后性能并不一定就会比B+-Tree索引高

Hash索引比较的是进行哈希运算之后的hash值，所以只能用于等值的过滤，并不适用于基于范围的查询。因为经过相应hash算法处理后的hash值的大小关系并不能保证和hash运算前的大小关系一致。索引也同样不能进行排序操作。

对于组合索引，Hash索引在计算hash值的时候是组合键(即将组合索引键合并之后再一起运算的hash值，而不是单独计算hash值)，所以利用组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。而B+树是支持利用组合索引中的部分索引的。

Hash索引是将索引键通过hash运算后将运算结果的hash值和所对应的行指针信息存放在一个bucket中。由于不同的索引键存在相同的hash值，所以即使取出满足某个hash键的数据也无法从hash索引中直接完成查询，还是要通过访问bucket中的实际数据进行相应的比较。

对于选择性比较低的索引键，如果创建Hash索引会出现大量记录指针信息存放于同一个bucket中的情况，从而造成整体性能低下。在极端的情况下，所有的键计算出来的hash值都是相同的，都存放在同一个bucket中，就变成了线性存储，查询效率极低。Hash索引的不稳定也正是它无法成为主流索引的原因。

BitMap索引

某个字段有几种状态时适合位图索引
Oracle为主流的支持位图索引的数据库
位图索引锁的粒度大，封锁范围广，并发度低

索引的类型

数据结构分类可分为：B+tree索引、Hash索引、Full-text索引、R-tree索引
物理存储分类可分为：聚簇索引、二级索引（辅助索引）
字段特性分类可分为：主键索引、唯一键索引、普通索引、前缀索引
字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。一张数据表有只能有一个主键，并且主键不能为 null，不能重复。在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

密集索引与稀疏索引的区别

密集索引文件中的每一个搜索码值都对应一个索引值
稀疏索引文件只为索引码的某些值建立索引项

密集索引叶子节点保存的不仅仅是键值，还保存了位于同一行记录中的其他列的信息。由于密集索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序，所以一个表只能创建一个密集索引。

稀疏索引叶子节点只保存了键位信息及该行数据地址，非主键索引只保存了键位信息及其主键。定位到叶子节点之后仍然需要通过地址或者主键信息进一步定位到数据。

MySQL MyISAM存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引; InnoDB存储引擎, 有且只有一个密集索引。

InnoDB密集索引的选取规则如下 :

若主键被定义，则主键作为密集索引
如果没有主键被定义，该表的第一个唯一非空索引则作为密集索引
若不满足以上条件，innodb内部会生成一个隐藏主键（密集索引）
非主键索引存储相关键位和其对应的主键值，包含两次查找

从上图中可以看到，如果一个索引是聚集索引，则其叶子节点上存放的即是数据本身，而如果一个索引是稀疏索引，叶子节点存放的仅是地址，指向将要查找的数据。InnoDB如果查询条件为主键索引，则只需查询一次，但是辅助索引需要查询两次，先通过辅助索引查询到主键索引，再查询到数据。

如何定位并优化慢查询SQL

根据慢日志定位慢查询sql
使用explain等工具分许sql
修改sql或者尽量让sql走索引

根据慢日志定位慢查询sql

1.查找慢查询日志

1	show variables like '%quer%';

2.打开慢查询日志并设置慢查询阈值为1秒

1
2
3

set global slow_query_log = on;

set global long_query_time = 1;

3.重连数据库,更新设置

4.制造慢查询sql并执行

5.查看慢查询数据库的记录(慢查询sql的条数)

1	show status like '%slow_queries%';

6.定位慢查询sql地址

1	slow_query_log_file

使用explain等工具分许sql

Explain关键字分析

type表示查询数据的方式，根据效率从高到低排序有如下几种。

如果type为index或all，表示本次扫描为全表扫描，说明这个语句是需要优化的。

1	system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all

extra中出现以下2项意味着MySQL根本不能使用索引，效率会受到重大影响，应尽可能对此进行优化

extra项	描述
Using filesort	表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL中无法利用索引完成的排序操作称为”文件排序“
Using temporary	表示MySQL在对查询结果排序时使用临时表，常见于排序 order by和分组查询group by。

修改sql或者尽量让sql走索引

1.修改sql通过主键进行查找

2.添加索引

1	ALTER TABLE tb_test add index index_name(test_name);

联合索引的最左匹配原则的成因

最左匹配原则

MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如 a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)顺序的索引，d是无法使用索引的，如果建立(a,b,d,c)的索引则都可以使用到，a、b、d的顺序可以任意调整。
=和in可以乱序，比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

示例->对列col1、列col2和列col3建一个联合索引

1	create index admin_col1_col2_col3 on admin (col1,col2,col3);

联合索引 admin_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

1	select * from admin where clo1 = 1 and clo2 = 7 and clo4 = 5;

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

1	select * from admin where clo2 = 7;

上面这条语句就不会走联合索引，而是会进行全表扫描。

MySQL创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。因此第一个字段是绝对有序而第二个字段是无序的，因此直接使用第二个字段判断条件是用不到索引的。

数据库锁

什么是事务？

事务是逻辑上的一组操作，要么都执行，要么都不执行，是一个不可分割的工作单位。

事务的特性(ACID)

原子性： 事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性： 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来的问题

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

更新丢失（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

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

不可重复度和幻读区别：不可重复读的重点是修改，幻读的重点在于新增或者删除。

事务的隔离级别

READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED (Oracle 默认)	×	√	√
REPEATABLE-READ (MySQL默认)	×	×	√
SERIALIZABLE	×	×	×

设置隔离级别

1	SET [SESSION\|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED\|READ COMMITTED\|REPEATABLE READ\|SERIALIZABLE]

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下，允许应用使用 Next-Key Lock 锁算法来避免幻读的产生。这与其他数据库系统(如 SQL Server)是不同的。所以说虽然 InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） ，但是可以通过应用加锁读（例如 select * from table for update 语句）来保证不会产生幻读，而这个加锁度使用到的机制就是 Next-Key Lock 锁算法。从而达到了 SQL 标准的 SERIALIZABLE(可串行化) 隔离级别。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化) 隔离级别。

数据库锁的分类

划分类型	类别
粒度	表级锁、行级锁、页级锁
级别	共享锁、排他锁
加锁方式	自动锁、显示锁
操作	DML(数据操作) 、DDL(数据定义)
使用方式	乐观锁、悲观锁

MySQL存储引擎

查看 MySQL 提供的所有存储引擎

1	mysql> show engines;

从上图我们可以看出MySQL当前默认的存储引擎是InnoDB，并且只有InnoDB是事务性存储引擎，即只有InnoDB支持事务；

`MyISAM` 和 `InnoDB` 的区别

MySQL 5.5 之前，MyISAM 引擎是 MySQL 的默认存储引擎，5.5 版本之后，MySQL 引入了 InnoDB（事务性数据库引擎），默认的存储引擎改为 InnoDB。

View	Description
锁	`MyISAM`默认表级锁，不支持行级锁; `InnoDB`默认行级锁，支持表级锁。
事务	`MyISAM` 不提供事务支持; `InnoDB` 提供事务支持，具有提交(commit)和回滚(rollback)事务的能力。
外键	`MyISAM` 不支持外键，而 `InnoDB` 支持。
安全恢复	`MyISAM` 不支持数据库异常崩溃后的安全恢复；`InnoDB`支持
文件存储	`InnoDB`存储文件有 `.frm`(定义文件)、.`ibd`(数据文件)；`MyISAM` 是 `.frm`、`.myd`(数据文件)、`.myi`(索引文件) 即`MyISAM`将数据和索引分开存储，而`InnoDB`将数据和索引绑定存储。
索引	`MyISAM` 为稀疏索引；`InnoDB`为密集索引

🌈 拓展:

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性。
保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

`MyISAM` 和 `InnoDB` 的选择

MyISAM

频繁执行全表count语句，即count(*) -> MyISAM用一个变量保存记录总数
对数据进行增删改的频率不高，查询频繁

InnoDB

增删改查都比较频繁
可靠性要求高、要求支持事务

`InnoDB` 存储引擎锁的三种算法

Record lock：记录锁，单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock ：record+gap临键锁，锁定一个范围，包含记录本身

`InnoDB`为什么推荐使用自增ID作为主键

自增ID可以保证每次插入时B+树索引是从右边扩展的，可以避免B+树频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

参考资料