mysql 复合索引树 mysql的复合索引

转载

jacksky 2023-09-22 06:38:50

文章标签 mysql 复合索引树 mysql复合主键还需要单独加索引吗? 数据主键 MySQL 文章分类 MySQL 数据库

mysql 复合索引树 mysql的复合索引_mysql复合主键还需要单独加索引吗?

1. 前言

MySQL涉及到的知识多且深，这里主要捡两个最基础也是后端RD最常接触到的点来展开：InnoDB的事务及索引原理，偏理论，面试中被问到的概率非常大。为了更好的说明原理，贴了很多图，大多来源于网络，侵删。

2. InnoDB存储引擎

2.1 MySQL分层架构

mysql 复合索引树 mysql的复合索引_mysql复合主键还需要单独加索引吗?_02

分层架构

接入层：主要负责连接处理、授权认证、安全等事宜。
服务层：查询解析、分析、优化、缓存及所有内置函数，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图、binlog、表锁等。
存储引擎层：负责MySQL中数据的存储和提取，服务层通过API与存储引擎通信，存储引擎包含几十个底层函数API，每种引擎提供一套具体实现。
系统文件层：负责底层文件系统的读写。

这种分层架构，可以将各层的职责划分得很清晰，方便扩展。

2.2 InnoDB存储引擎

InnoDB属存储引擎层，是MySQL的默认存储引擎(5.1版本及以上)。InnoDB相较其它存储引擎的主要特点有：支持事务、支持高并发、自动崩溃恢复、基于聚簇索引组织表数据等。我们主要关注如下问题：InnoDB是如何保证事务？如何支持高并发？数据如何存储？

3. 事务原理

事务具有4个基本特征，分别是：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Duration)，简称ACID，这是标准SQL规范，InnoDB通过自己的方式实现之。

3.1 ACID特性

原子性：最小工作单元，要么全成功，要么全失败。
一致性：事务开始和结束后，数据库的完整性不会被破坏。
隔离性：事务之间互不影响，四种隔离级别 RU(读未提交)、RC(读已提交)、RR(可重复读)、SERIALIZABLE (串行化)。
持久性：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失。

mysql 复合索引树 mysql的复合索引_主键_03

acid特性
主要关注下隔离性，InnoDB默认隔离级别为RR，该级别下InnoDB通过MVCC机制—— “非阻塞的快照读和加锁(行锁+间隙锁)的当前读”避免了幻读的发生。那么什么是幻读呢？所谓幻读，是指同一个事务里，相同语句的当前读，返回的记录是完全相同的 (记录数量一致，记录本身也一致)，后面的当前读，不会比第一次返回更多的记录 (幻象)。

3.2 事务日志

InnoDB 使用undo、 redo log来保证事务原子性、一致性及持久性，同时采用预写日志方式将随机写入变成顺序追加写入，提升事务性能。

undo log：记录事务变更前的状态。操作数据之前，先将数据备份到undo log，然后进行数据修改，如果出现错误或用户执行了rollback语句，则系统就可以利用undo log中的历史版本恢复到事务开始之前的状态。
redo log：记录事务将要变更后的状态。事务提交时，只要将redo log持久化即可，数据可在内存中变更。当系统崩溃时，虽然数据没有落盘，但是redo log已持久化，系统可以根据redo log的内容，将所有数据恢复到最新的状态。
checkpoint：随着时间的积累，redo log会变的很大很大。如果每次都从第一条记录开始恢复，恢复的过程就会很慢。为了减少恢复的时间，就引入了checkpoint机制。定期将databuffer的内容刷新到磁盘datafile内，然后清除checkpoint之前的redo log。
自动恢复：InnoDB通过加载最新快照，然后重放最近的checkpoint点之后所有redo log事务(包括未提交和回滚了的)，再通过undo log回滚那些未提交的事务，来完成数据恢复。需要注意的地方是，undo log其实也是行数据，对其写操作也会记录到redo log内，即undo log也是通过redo log来保证持久化的。

事务流程

mysql 复合索引树 mysql的复合索引_主键_04

上图为事务写操作执行的大致过程，整个过程中只有一次刷盘操作，即事务提交时的redo log的写盘。其实写盘并不一定会立马持久化到磁盘，要看数据库配置，默认情况下Innodb_flush_log_at_trx_commit=1，即一次redo log写盘操作会立即写到磁盘中，是最保险的方案。

mysql 复合索引树 mysql的复合索引_主键_05

redo log写盘

InnoDB中多个事务共享一个redo log buffer，写盘时，会将当前buffer中的多个事务日志持久化，而不管事务有没有commit，而且并不是只有事务commit才会触发redo log写盘，其它操作如redo log buffer空间不足、触发checkpoint、实例shutdown及binlog切换时都会触发redo log写盘操作。

3.3 MVCC

InnoDB使用MVCC机制来提升RR隔离级别的并发性。MVCC (Multi-Version Concurrency Control)多版本并发控制协议，将读操作分成两类：快照读 与当前读 。读取的是记录的最新版本，会对返回的记录加上锁，确保其他事务不能并发修改。

快照读：简单的查询操作，属于快照读，不加锁。如：

1 select * from table where ?;

当前读：特殊的读操作及插入/更新/删除操作，属于当前读，需要加锁。以下都是当前读：

1 select * from table where ? lock in share mode;

2 select * from table where ? for update;

3 insert into table values (…);

4 update table set ? where ?;

5 delete from table where ?;

快照读是通过undo log来实现多个版本的控制。如下图，每个数据行：row_id 为行id，trx_id表示最近修改的事务id，db_roll_ptr为指向undo segment中undo log的指针。快照读时，比较当前事务id与trx_id的关系，如果trx_id 小于事务id，则该条数据对当前事务可见，反之不可见，不可见时再通过db_roll_ptr查找历史版本记录，取出可见的最近的历史记录。undo log 的链路不会很深，后台purge线程定期清除无用的历史版本(在没有活动事务依赖时，undo log即可被删除)。

mysql 复合索引树 mysql的复合索引_MySQL_06

undo log

3.4 加锁分析：总结于何登成的《 InnoDB加锁处理分析》

当前读都会加锁，怎么加？则要看具体情景——隔离级别及索引情况。

在InnoDB的RR隔离级别下，对于同一条SQL语句：

DELETE FROM T1 WHERE ID=10;

当ID列为主键时：锁主键索引上id=10的记录。

mysql 复合索引树 mysql的复合索引_MySQL_07

当ID列为唯一索引：先锁唯一索引上的id=10的行，再锁主键索引上name=d的行。

mysql 复合索引树 mysql的复合索引_数据_08

当ID列为二级索引：在二级索引上，会给id=10的所有行加X锁，而且会给被锁行的前后范围加GAP锁；主键索引上，给相应记录加X锁。

mysql 复合索引树 mysql的复合索引_mysql复合主键还需要单独加索引吗?_09

当ID列未加索引：此种情况后果很严重！主键索引所有行都被加X锁，所有间隙被加GAP锁！全表的数据都被锁的，没有并发可言，因此一定要检查当前读的where条件语句是否走索引。

mysql 复合索引树 mysql的复合索引_MySQL_10

GAP锁的意义：当前事务占住间隙范围，避免其它事务往这个范围插入数据，引起幻读，只发生在RR隔离级别。如果id列是唯一索引(或主键索引 )，且当前读条件语句中的id不存在时，InnoDB也会给范围加GAP锁。

4. 索引结构

使用索引的优点：减少需要扫描的数据量，避免文件排序及临时表，将随机I/O变为顺序I/O等，从而达到更快的读写数据。InnoDB采用B+树的结构来组织索引。

4.1 B+树

InnoDB之所以采用B+树来组织索引，是由其扁平化的结构决定的。非叶子节点记录索引列的key值，真实数据只存于叶子节点，这样的好处是非叶子节点很适合做缓存(一个大节点约16k，能存储1200多个key值)。真实数据库中的B+树是非常扁平的，高度为3时容量可达22GB;高度为4时则可存储26TB；另外大节点之间用双向链表互连，方便顺序扫描。

mysql 复合索引树 mysql的复合索引_主键_11

B+树

mysql 复合索引树 mysql的复合索引_mysql复合主键还需要单独加索引吗?_12

大节点

4.2 聚簇索引及二级索引

聚簇索引：是按照每张表的主键，构造一颗B+树，同时叶子节点存放的是表的行纪录数据(聚集索引的叶子节点也称为数据页)。聚簇索引是一种数据存储方式。将主键id设为自增，可使随机insert变为顺序append，不会产生页分裂和碎片，提升写性能。
二级索引：InnoDB二级索引的叶节点存储的是主键id，查询数据时，先索引到主键id，再回聚簇表查询数据详情，需要走两次索引查找。主键的数据类型尽量要小，它直接影响索引树的存储空间。

4.3 高性能索引策略

正确地创建和使用索引是实现高性能查询的基础。

独立的列：指索引列不能是表达式的一部分，也不能是函数的参数。我们应该养成简化 WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。
前缀索引及索引选择性：有时候需要索引很长的字符列，这会让索引变得大且慢。可以索引开始的部分字符，能大大节约索引空间，提高索引效率，这就是前缀索引。索引的选择性越高则查询效率越高，前缀索引取多长字符，需要折中数据大小与选择性强弱。
合适的索引列顺序：索引不是越多越好，通常会建一个复合索引，以满足多个查询语句，这就要求合适的索引列顺序。复合索引的匹配规则是，最左前缀匹配，且遇到第一范围查询条件时，停止匹配。因此通常会将通用的列放索引前面，范围查询列放索引后面。
覆盖索引：如果一个索引包含(或者说覆盖)所有需要查询的字段的值，称之为“覆盖索引”。这是个非常有用的工具，能够极大的提高性能，只需要扫描二级索引而无须回表。
使用索引扫描来排序：MySQL有两种方式生成有序的结果，排序操作或者按索引顺序扫描。排序操作费时费空间，而索引扫描只需要从一条索引记录移到紧接着的下一条记录，是很快的。需要注意，只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能够使用索引来对结果做排序。

SQL优化跟索引息息相关，需要具体场景具体分析。EXPLAIN之后，关注有没有走预期的索引，有没有文件排序，扫描多少数据量等等。