不做大哥好多年
xiaonaiqiang
2021-02-22
目录

04.MySQL索引常识原理

MySQL的索引类型包括B+树、聚簇索引、非聚簇索引、哈希索引和空间索引。

B+树索引常用于单列、组合索引以及全文检索。

其中聚簇索引将数据存储在叶子节点，非聚簇索引通过主键指针查找数据。

组合索引遵循最左前缀原则。B树与B+树的区别在于数据存储结构，B+树只在叶子节点存储数据，适合范围查找。

MySQL中InnoDB引擎使用聚簇索引，MyISAM则使用非聚簇索引，数据存储和索引结构不同。

# 01.MySQL索引

# 1.1 MySQL索引类型

# 1、B+Tree索引

  • 1）单列索引
    • 主键索引(不能为空)
      • 设定为主键后数据库会自动建立索引
    • 唯一索引(可为空)
      • 索引列的值必须唯一，但允许有空值
    • 普通索引(可重复)
      • 可以为空，可以重复
    • 前缀索引
      • 前缀索引只考虑列值的前几个字符
  • 2）组合索引(一个索引包含多个列)
    • 最左前缀
  • 3）全文索引
    • 全文索引和B+Tree的LIKE查询功能类似，LIKE语句通常会导致全表扫描，特别是在大型表中可能效率较低
    • MySQL中的全文索引是通过构建倒排索引来实现的，将词映射到包含这些单词的文档的索引结构
    • 倒排索引可以快速高效的 全文搜索和模糊查询

# 2、聚簇索引

  • MyISAM引擎通常用于支持非聚簇索引
  • InnoDB默认是支持聚簇索引（也支持非聚簇索引）
  • 聚簇索引
    • 聚簇索引就是按照每张表的主键构造一颗B+树，叶子节点中存放的就是整张表的行记录数据
    • 缺点：插入速度严重依赖于插入顺序
  • 非聚簇索引（数据是分离的）
    • 辅助索引叶子节点存储的不再是行的物理位置，而是主键值
    • 通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页
  • 关键区别
    • 聚簇索引确定实际物理存储位置，非聚簇索引仅包含索引列的值和指向实际数据行的指针
    • 一张表只能有一个聚簇索引，但可以有多个非聚簇索引
    • 性能影响
      • 对于范围查询或按索引列排序，聚簇索引通常具有更好的性能
      • 非聚簇索引对于单列的查找和特定的连接操作可能更高效（范围和排序查询性能差）

# 3、hash索引

  • Hash索引通过将索引列的值通过哈希函数映射到一个哈希表的桶中，从而实现快速的索引查找
  • 在MySQL中，Hash索引的使用场景相对有限，在范围查询、排序等场景下表现不佳

# 4、空间索引

  • 空间索引支持对空间数据进行快速的空间查询和分析
  • 空间索引可用于存储和查询地理位置信息，如移动设备的实时位置、商家的地理分布等
  • 通过空间索引，可以在地理空间中快速找到相关的数据

# 1.2 组合索引

# 1、复合索引特点

  • 联合索引可以为多个字段创建一个索引

  • 比如，我们在（a,b,c）字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段

  • 其实联合索引的查找就跟查字典是一样的，先根据第一个字母查，然后再根据第二个字母查

  • 或者只根据第一个字母查，但是不能跳过第一个字母从第二个字母开始查。这就是所谓的最左前缀原理。*

  • 联合索引的特点就是：

    • 1）第一个字段一定是有序的
    • 2）当第一个字段值相等的时候，第二个字段又是有序的，比如下表中当A=2时所有B的值是有序排列的，依次类推，当同一个B值得所有C字段是有序排列的

# 2、最左前缀原理

'''最左前缀原理'''

-- 1、以下的查询方式都可以用到索引

select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；
-- 上面三个查询按照 （a ）, （a，b ）,（a，b，c ）的顺序都可以利用到索引，这就是最左前缀匹配。

-- 2、如果查询语句是（只会用到索引a）
select * from table where a=1 and c=3； 

-- 3、这样不会用的索引
select * from table where b=2 and c=3； 
-- 因为没有用到最左前缀a，所以这个查询是用户到索引的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 1.3 MySQL索引失效

  • 1、like语句失效
    • like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
-- 不能命中索引
select * from table where name LIKE "%张%"
select * from table where name LIKE "%张"
-- 可以命中索引
select * from table where name LIKE "张%"
1
2
3
4
5
  • 2、or语句失效
    • 当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
-- 命中索引
select * from table where uid=24 or uid=18;
-- 无法命中索引
select * from table where uid=24 or name=zhangsan;
1
2
3
4
  • 3、组合索引失效
    • 组合索引，不是使用第一列索引，索引失效
-- 只会用到索引a
select * from table where a=1 and c=3；
-- 因为没有用到最左前缀a，所以无法使用索引
select * from table where b=2 and c=3； 
-- 命中索引
select * from table where a=1 and b=2 and c=3；
1
2
3
4
5
6

# 02.B-tree/B+tree

# 2.1 B-Tree

  • 每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。

  • 两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。

  • 以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

  • 模拟查找关键字29的过程
'''模拟查找关键字29的过程：'''
## 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
## 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
## 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
## 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
## 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
## 在磁盘块8中的关键字列表中找到关键字29。
1
2
3
4
5
6
7

# 2.2 B+tree

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

  • B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值

  • 而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小

  • 当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

  • 在B+Tree中，所有根节点只存储 键和指针，只有叶子节点才存放数据

  • 因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的
  • 那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单
  • B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的

# 2.4 MySQL底层索引存储

  • 1 MB = 1024 KB = 1024 × 1024 Byte字节 = 1,048,576 bit (1bit 实际就是一个0或者1)

  • 1）InnoDB存储引擎中页的大小为16KB（B+树非叶节点存储 键 + 指针）

    • 索引指针为6个字节，而 ID类型 bigint(long)占8个字节，每个索引项占用 14 字节
    • 那么一页存储数据：16*1024/14≈1170行
    • 假设每条数据大小为1kb，那么每个叶子节点可存放 16条数据
    • 如果第二层是叶子节点：有1170个叶子节点，16*1170 条数据

  • 2）3层深度B+树存储最大行数约为 2000 万条数据

    • 如果第三层是叶子节点：有 1170 * 1170 个叶子节点，每个叶子节点可存 16条数据
    • 所以 三层深度b+tree 最大可存储：16*1170*1170 = 21902400行（约为两千万数据）
    • 所以江湖流传的 2000 万数据是把每行数据的大小当做 1kb 计算的
    • 所以每个页（叶子节点）有 16kb，能存 16 条数据

  • 3）4层深度B+树存储最大行数约为 256 亿 条数据

    • 16*1170*1170*1170 = 25625808000 （约为256 亿数据）

  • 说明：

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

# 03.聚集索引&非聚集索引

# 0、回表和索引覆盖

  • 回表

    • 在 InnoDB 存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值

    • 再通过主键值查出主键索引里面没有符合要求的数据

    • 它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表

  • 覆盖索引了

    • 在辅助索引里面，不管是单列索引还是联合索引
    • 如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引
    • 这时候使用的索引就叫做覆盖索引，避免了回表

# 1、聚集&非聚集区别

在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引

  • InnoDB（聚集索引）
    • 聚簇索引一般指的是主键索引，叶子节点直接存储行数据
    • 非聚簇索引的叶子节点上存储的并不是真正的行数据，而是主键 ID
    • 非聚簇索引进行查询，会得到主键 ID，再使用主键 ID 去聚簇索引上找到真正的行数据（也叫回表）
  • MyISAM（非聚集索引）
    • 主键索引和数据是分开存储的，叶子节点存储该列对应的主键（不存储表中的数据）
    • 需要根据主键再去聚集索引中进行查找（我们称为回表）
  • 存储方式
    • InnoDB使用聚簇索引，叶子节点直接存储数据
    • 而MyISAM使用非聚簇索引，叶子节点上存储的并不是真正的行数据，而是主键 ID
  • 索引维护
    • InnoDB的B+Tree索引需要维护聚簇索引和辅助索引（secondary index），因此维护代价较高
    • MyISAM的B+Tree索引只需要维护辅助索引，因此维护代价较低
  • 性能
    • InnoDB的B+Tree索引在高并发情况下，由于需要维护聚簇索引和辅助索引，写入性能较差
    • MyISAM的B+Tree索引只需要维护辅助索引，写入性能较好，读取大量数据是性能差于 InnoDB

# 2、InnoDB 和 MyISAM 存储区别

  • 存储引擎是InnoDB, 在data目录下会看到2类文件：.frm、.ibd

    • 1）*.frm--表结构的文件。
    • 2）*.ibd--表数据文件

  • 存储引擎是MyISAM, 在data目录下会看到3类文件：.frm、.myi、.myd

    • 1）*.frm--表定义，是描述表结构的文件。
    • 2）*.MYD--"D"数据信息文件，是表的数据文件。
    • 3）*.MYI--"I"索引信息文件，是表数据文件中任何索引的数据树

# 1、存储区别比较

  • InnoDB的主键必须使用自增的字段，根据B+树的性质，如果字段自增，那么索引值是顺序添加的
  • 而MyISAM没有这个限制，当然自增是最好的
  • InnoDB 聚簇索引
    • InnoDB叶子节点直接存储数据，无需回行IO读取数据
  • InnoDB非聚簇索引
    • 而InnoDB的辅助索引叶子节点存储主键ID，需要拿到主键值，再去查主键索引
  • MyISAM 非聚簇索引
    • MyISAM的索引和数据是分开的，它的主键索引和辅助索引结构是一样的，都是B+树
    • 而叶子节点的data存储的是指向具体**数据存储的位置地址**，而不是具体数据
    • 所以它的查询是先查了索引，然后回行IO读取数据

# 2、InnoDB 索引存储

  • 在InnoDB中，系统为每张表依据主键建立一颗B+树，将数据直接存放在叶子结点上
  • 如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引
  • 如果根据 id 主键查找，只需要查找主键索引这一颗 B+Tree 即可
  • 非数字主键查找需要借助辅助索引
    • 如果对Name作为主键进行索引，那么就需要有一个辅助结构，先找到该取值对应的行
    • 再在另外一个辅助B+树上进行检索，读取行数据，这样的话就需要一个添加一颗辅助键索引的B+树

# 3、MyISAM 索引存储

  • 非聚簇索引的B+树结构基本和聚簇索引一致，
  • 只是非聚簇索引的数据是单独存储的，所以在叶子结点只存了定位数据的指针

# 3、InnoDB索引查询

# 1、聚簇索引查询

  • 假设我们要查找id>=18并且id<40的用户数据
  • 1）我们从 页1找到 id=18的键值，根据 p2 指针定位到页3
  • 2）在页3我们可以找到键值18，根据页3 的 p1指针定位到页 8
  • 3）将页8读取到内存中后，因为页中的数据是链表进行有序连接的，可以二分法查找键值18

下面是范围查找过程

  • 4）因为是范围查找，页 8中找到键值为22的数据，然后页8中就没有数据了

  • 5）此时我们需要拿着页8中的p指针去读取页9中的数据（注意读取 页 9 的指针是页指针）

  • 6）因为页9不在内存中，就又会加载页9到内存中，直到找到 18 到 40 之间的所有数据结束

  • 注：聚簇索引将数据直接存放在叶子结点上，直接就可以取出整个行数据了

# 2、非聚簇索引查询

  • InnoDB的辅助索引的data存的是主键的值
  • 因此每次根据辅助索引查询，都得先查辅助索引，拿到主键值，再去查主键索引
  • 假的我们有一张表包含两个字段 id主键索引，user_id 辅助索引
  • 下面要根据辅助索引查找用户 user_id=33的用户这行数据所有信息
  • 1）和聚簇索引查找流程一致，先找到 user_id=33 的用户主键 id=47
  • 2）再到主键索引 B+Tree 中查询行数据（这个流程就是上面查聚簇索引的流程）

# 04.更新 sql 是如何执行的

# 1、更新语句写入过程

  • 更新语句的执行是 Server 层和引擎层配合完成，数据除了要写入表中，还要记录相应的日志

  • 1）执行器先找引擎获取 ID=2 这一行

    • ID 是主键，存储引擎检索数据，找到这一行
    • 如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器
    • 否则，需要先从磁盘读入内存，然后再返回

  • 2）执行器拿到引擎给的行数据，按照 sql 需求修改数据，再调用引擎接口写入这行新数据

  • 3）引擎更新数据到内存和redo log 中

    • 引擎将这行新数据更新到内存中
    • 同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态
    • 然后告知执行器执行完成了，随时可以提交事务

  • 4）执行器生成这个操作的 binlog，并把 binlog 写入磁盘

  • 5）执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log 改成提交（commit）状态，更新完成

为什么要两阶段提交呢？直接提交不行吗？

  • 先写入 redo log，后写入 binlog
    • redo log用于崩溃恢复和确保数据持久性，写入 redo log 系统崩溃会恢复这条数据
    • 如果binlog 没有对上面的更新语句进行保存，导致 MySQL 从库没有更新
  • 先写入 binlog，后写入redo log
    • binlog 用于主从同步
    • 如果 binlog 中有，redolog 没有，奔溃导致主库没有写入，但是从库写入了

# 2、创建 sql 写入过程

  • 1）执行器先通过解析SQL语句，确定需要在哪个表中创建新的数据行

  • 2）执行器根据SQL语句中的内容，生成新的行数据

  • 3）执行器调用存储引擎的接口，将新的行数据写入到存储引擎中

    • 如果新的行数据所在的数据页本来就在内存中，就直接写入到内存中
    • 否则，需要先分配新的数据页到内存中，然后再写入新的行数据

  • 4）引擎插入数据到内存和redo log 中

    • 存储引擎将新的行数据写入到内存中
    • 并同时将这个插入操作记录到redo log里面，此时redo log处于prepare状态
    • 然后告知执行器执行完成了，随时可以提交事务

  • 5）执行器生成这个操作的binlog，并把binlog写入磁盘

  • 6）执行器调用存储引擎的提交事务接口，存储引擎把刚刚写入的redo log改成提交（commit）状态，插入操作完成

  • 注意：

    • 在上述过程中，如果表中定义了主键或者唯一索引

    • 那么在插入新的行数据时，需要检查新的行数据是否满足主键或者唯一索引的约束条件

    • 如果不满足，那么插入操作会失败

上次更新: 2024/10/15 16:27:13
03.MySQL锁
05.MySQL慢查询

← 05.MySQL慢查询→

最近更新
01
16.CAP
09-27
02
13.HTTP协议
09-25
03
12.TCP协议
09-25
更多文章>
Theme by Vdoing | Copyright © 2019-2024 逍遥子 技术博客 京ICP备2021005373号