MySql

MySql索引

以下文章采自：索引介绍、索引原理、B+树结构

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+ 树。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

1
2
3
为表设置索引要付出代价的：
1.增加了数据库的存储空间
2.在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在 O(log2n)的复杂度内获取到相应数据。

1
2
3
4
5
6
7
8
9
10
创建索引的好处：
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
1
2
3
4
5
6
创建索引的坏处：
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

Hash索引

1
2
1.基于哈希表的实现，只有精确匹配索引所有列的查询才有效
2.在mysql中，只有memory的存储引擎显式支持哈希索引

聚簇索引

聚簇索引也叫聚集索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
聚集索引确定表中数据的物理顺序。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引，通常是主键。

不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起。

非聚集索引

非聚集索引也称作辅助索引，该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息。

数据文件跟索引文件分开存放。

1
2
3
4
5
6
7
8
9
10
11
12
聚集索引
1.纪录的索引顺序与物理顺序相同
  因此更适合between and和order by操作
2.叶子结点直接对应数据
  从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索引

非聚集索引
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引，需要更多磁盘和内容
  多个索引会影响insert和update的速度

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。

主键索引

1
2
3
1.主键不允许空值，唯一索引允许空值
2.主键只允许一个，唯一索引允许多个
3.主键产生唯一的聚集索引，唯一索引产生唯一的非聚集索引

mysql

为什么建议使用主键自增的索引？

mysql

1
2
3
4
如果插入的是 ID = 350 的一行数据，由于 B+ 树是有序的，那么需要将下面的叶子节点进行移动，腾出位置来插入 ID = 350 的数据，这样就会比较消耗时间。
如果刚好 R4 所在的数据页已经满了，需要进行页分裂操作，这样会更加糟糕。

但是，如果我们的主键是自增的，每次插入的 ID 都会比前面的大，那么我们每次只需要在后面插入就行， 不需要移动位置、分裂等操作，这样可以提高性能。

单列索引

即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；

当一个表中查询大的情况下，where条件中有多个，如果使用多个单列索引，根据mysql优化器策略，造成可能只使用一个索引，其他索引会失效，导致会全盘扫描表。

组合索引

即一个索引包含多个列。

当一个表中查询大的情况下，where条件中有多个，那么可以使用组合查询,不会扫描表，直接从索引中获取，查询效率高。

1
2
3
4
5
6
7
场景：
设置单列索引：name
设置组合索引：name,age

SQL：select name from user where name = 'admin';

Mysql优化器优化后，它会走组合索引，而不是走单列索引。

覆盖索引

1
2
3
1、如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引

2、不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值

局部性原理和磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘 I/O。

为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。

预读的长度一般为页（4K）（page）的整倍数。

页分裂

页可能填充至100%，在页填满了之后，下一页会继续接管新的记录。但如果下一页也满了，数据又需要按顺序插入，就会产生页分裂。

1
2
3
4
5
6
InnoDB的做法是（简化版）：

创建新页
判断当前页可以从哪里进行分裂（记录行层面）
移动记录行
重新定义页之间的关系

等于创建了一个新页，并且把当前页的数据迁移部分到新页中，然后插入在当前页和下一页的中间。

页合并

当你删了一行记录时，实际上记录并没有被物理删除，记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

mysql

当页中删除的记录达到MERGE_THRESHOLD（默认页体积的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

B树演变成B+树的过程和原因

mysql

mysql

mysql

1
2
B+树的根节点会被存储在内存中，其他节点存储在磁盘中
B+树的叶子节点（数据节点）会串起来的形成双向链表，用以支持前后遍历

Order By

1
2
3
4
5
6
双路排序：
第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。

这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高

两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作
1
2
单路排序：
先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据

当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式.

子查询

1
2
1.当Limit的值过大，尽量使用子查询，能够提高效率
2.尽量使用联表查询代替子查询，因为子查询会产生临时表

索引优化

索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

Mysql存储引擎

1
2
3
4
5
6
innodb存储引擎：
undolog
redolog

server层：
binlog

ACID中，最重要的是一致性，其中A的原子性是通过undolog来保证的，隔离性是通过锁机制(MVCC、间隙锁)实现的，持久性是通过redolog来实现的，一致性是通过原子性、隔离性、持久性来实现的。

redolog

mysql

mysql

第二种最安全，第三种效率最高。

undolog

mysql

binlog

mysql

mysql

MyISAM表锁

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `mylock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('4', 'd');

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表共享读锁

一个session使用lock table给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误，同时，另一个session可以查询表中的记录，但更新就会出现锁等待。

1
2
3
4
5
#获得表的read锁定
lock table mylock read;

#释放锁
unlock tables;
1
2
3
4
#获取表的read local(session)锁定
lock table mylock read local

#其他session可以查询该表的记录、可以进行插入操作，但是更新会阻塞

表独占写锁

当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待，直到锁释放为止。

1
2
3
4
5
#获取表的write锁定
lock table mylock write;

#获取表的write锁定
unlock tables；

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺： 

1
2
3
4
5
6
7
8
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 352   |
| Table_locks_waited    | 2     |
+-----------------------+-------+
--如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显式加锁，上例中的加锁时为了演示效果。

InnoDB锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁。

行共享锁

共享锁又称读锁。允许多个事务对统一数据可以共享一把锁，都能访问到数据库，但是只能读不能修改，如果有其他事务进行修改操作，会被阻塞直到共享锁释放才修改成功。

1
2
3
4
5
6
#关闭事务自动提交
set autocommit=0;
#共享锁，其他事务能查，但是不能修改
select * from student where id = 1 lock in share mode;

commit;

行排他锁

排他锁又称写锁。排它锁不能与其他锁并存，如果一个事务获取了一个数据行的排它锁，其他事务就不能在获取该行的锁，只有当前获取了排它锁的事务可以对数据进行读取和修改。

1
2
3
4
5
6
#关闭事务自动提交
set autocommit=0;
#排它锁，其他事务能查，但是不能修改、也不能加其他锁
select * from student where id = 1 for undate;

commit;

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况： 

1
2
3
4
5
6
7
8
9
10
11
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

自增锁

针对自增列自增长的一个特殊的表级别锁。

1
2
#默认值1，代表连续，事务未提交则id永远丢失，会接着新的id递增
show variables like 'innodb_autoinc_lock_mode';

MVCC

脏读：事务能够读取未提交的数据。

不可重复读：当一个事务在执行过程中，数据被另外一个事务修改，造成本次事务前后读取的信息不一样。

幻读：当一个事务A读取某一个范围的数据时，另一个事务B在这个范围插入行，A事务再次读取这个范围的数据时，会产生幻读。

事务隔离级别的原理：MVCC + 锁

当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

关系

MVCC多版本并发控制指的是维持一个数据的多个版本，使得读写操作没有冲突，快照读是MySQL为实现MVCC的一个非阻塞读功能。MVCC模块在MySQL中的具体实现是由三个隐式字段，undo日志、read view三个组件来实现的。

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

解决脏读、幻读、不可重复读等事务隔离问题。

隐藏字段

DB_TRX_ID：6字节，最近修改事务id，记录创建这条记录或者最后一次修改该记录的事务id

DB_ROLL_PTR：7字节，回滚指针，指向这条记录的上一个版本,用于配合undolog，指向上一个旧版本

DB_ROW_JD：6字节，隐藏的主键，如果数据表没有主键，那么innodb会自动生成一个6字节的row_id

undolog

MVCC的快照版本数据就是存储在undolog中。

mysql

不同事务或者相同事务的对同一记录的修改，会导致该记录的undolog生成一条记录版本线性表，即链表，undolog的链首就是最新的旧记录，链尾就是最早的旧记录。

Read View

Read View是事务进行快照读操作的时候生产的读视图，在该事务执行快照读的那一刻，会生成一个数据系统当前的快照，记录并维护系统当前活跃事务的id，事务的id值是递增的。

1
2
3
4
5
读已提交原理：
在每次执行SQL的时候，MVCC都加载readView当前的能访问的最大的非活跃事务的id的数据

可重复读原理：
在开启事务的时候，就定义好了readView的值，不会改变，保证读取的事务id的数据是一致的。

如果事务中都是用快照读，那么不会产生幻读的问题，但是快照读和当前读一起使用的时候就会产生幻读。

通过使用next-key解决（select * from user where age = 20 for update）。

最后更新： 2021年03月16日 17:05

原始链接： https://midkuro.gitee.io/2020/09/09/mysql-index/

赏
× 请我吃糖~
打赏二维码