【MySQL之MySQL底层分析篇】系统学习MySQL，从应用SQL语法到底层知识讲解，这将是你见过最完成的知识体系

参考文章&＃xff1a;学习Mysql这一篇就够了


参考文章1&＃xff1a;MySQL面试题目


参考文章2&＃xff1a;MySQL面试题目


参考文章&＃xff1a;MySQL数据库学习宝典


参考文章&＃xff1a;Mysql存储引擎


参考文章&＃xff1a;MySQL 索引底层选择B&＃43;Tree


参考文章&＃xff1a;全文索引


参考文章1&＃xff1a;MyISAM 和 InnoDB介绍


参考文章2&＃xff1a;MyISAM 和 InnoDB区别


参考文章&＃xff1a;索引失效


参考文章&＃xff1a;探索MySQL是否走索引(一)——范围查询一定走索引吗&＃xff1f;


工具&＃xff1a;提供一个很好用的国外数据结构模拟网站


参考B站视频&＃xff1a;总体概括


参考B站视频&＃xff1a;详细讲解
MySQL体系结构

Client Connectors&＃xff1a;接入方&＃xff0c;程序员通过Client Connectors&＃xff08;JDBC、ODBC&＃xff09;与MySQL打交道
Connection Pool&＃xff1a;连接池&＃xff0c;管理缓冲用户连接、用户名、密码、权限校验等需要缓存的需求
SQL Interface&＃xff1a;SQL接口&＃xff0c;接受用户的SQL命令&＃xff0c;并且返回用户需要查询的结果
Parser&＃xff1a;解析器&＃xff0c;验证和解析SQL命令
Optimizer&＃xff1a;查询优化器&＃xff0c;在查询之前会对SQL语句进行优化
Cache和Buffer&＃xff1a;缓存区&＃xff0c;如果查询缓存命中&＃xff0c;查询语句就可以直接去查询缓存中取数据
pluggable storage Engines&＃xff1a;插件式存储引擎&＃xff0c;MySQL与文件系统打交道
File System&＃xff1a;文件系统&＃xff0c;数据、日志&＃xff08;redo&＃xff0c;undo&＃xff09;、索引、错误日志、查询记录、慢查询等

MySQL存储结构&＃xff08;以InnoDB为例&＃xff09;

1. InnoDB创建数据表
InnoDB创建数据表&＃xff0c;会将数据表分为表结构和表内容存储

• 表结构&＃xff1a;存储在.frm文件中
• 表内容&＃xff1a;以前存储在系统表空间中&＃xff0c;现在默认存储在独立表空间.ibd

2. ibd文件
.ibd文件分为叶子节点段和非叶子节点段和回滚段

• 叶子节点段&＃xff1a;B&＃43;树的叶子节点
• 非叶子节点段&＃xff1a;B&＃43;树的非叶子节点
• 回滚段&＃xff1a;为存储一些特殊的数据而定义的段

如果没有设置索引&＃xff0c;那么所有的节点就不会区分成叶子节点段和非叶子节点段

3. 段
段不对应表空间的某一连续物理区域&＃xff0c;而是一个逻辑概念&＃xff0c;它是由表空间这个物理空间中的若干个零散页和一系列完整的区组成。

• 叶子节点段&＃xff1a;存放在区
• 非叶子节点段&＃xff1a;存放在页

4. 区

为什么要有区

• 上面提到页与页之间通过双向链表进行连接&＃xff0c;若页与页间隔太远的话&＃xff0c;而随机I/O【定义&＃xff1a;读写操作时间连续&＃xff0c;但访问地址不连续&＃xff0c;随机分布在磁盘的地址空间中】是非常慢的。因此为了尽量让链表上相邻的页物理上也相邻&＃xff0c;引入了区。
• 一个区在物理位置上就是连续的64个页。数据量大的时候&＃xff0c;可一次性分配多个区&＃xff0c;不必再按照页为单位进行分配了。消除很多的随机I/O。

碎片区

• 对于数据量太小的表不足以填满整个区的情况&＃xff0c;造成空间的浪费。因此针对这种情况&＃xff0c;InnoDB提出了碎片区的概念。
• 碎片区中的页并不都是为了存储同一个段而存在的&＃xff0c;比如有的页用于段A&＃xff0c;有的用于段B&＃xff0c;有的页甚至哪个段也不属于。碎片区直属于表空间&＃xff0c;并不属于任何一个段。

综上&＃xff0c;为段分配的策略是

• 起初向表中插入数据的时候&＃xff0c;段是从某个碎片区以单个页面来分配存储空间的。
  当某个段已经占用了32个碎片区页面之后&＃xff0c;就会申请以完整的区为单位来分配存储空间。

5. 数据页

1. 页头File Header

2. 页尾File Trailer
页头和页尾的校验和相对应&＃xff0c;由于我们的操作系统传输单元数据块通常是4KB&＃xff0c;一个页是4个数据块。如果碰到我断电的情况&＃xff0c;那么一些不走运的页&＃xff0c;可能只传输了一两个数据块&＃xff0c;并没有完整传输&＃xff0c;这种不完整就需要用页头和页尾的校验和通过一些验证算法进行验证。默认使用crc32.

3. 数据行

MySQL说&＃xff1a;数据行有4种格式&＃xff1a;DYNAMIC&＃xff0c;REDUNDANT&＃xff0c;COMPACT&＃xff0c;COMPRESSED&＃xff0c;默认为DYNAMIC&＃xff0c;可以通过innodb_default_row_format进行查询和变更。

真实数据区

MySQL说&＃xff1a;第一个就是主键位置&＃xff0c;如果是复合主键&＃xff0c;那也会依序排在这里。如果没有主键&＃xff0c;就会优先用一个NOT NULL、UNIQUE的列作为主键&＃xff0c;如果这个都没有的话&＃xff0c;我的innoDB就会构建一个6B的DB_ROW_ID字段存储在这里。
MySQL说&＃xff1a;紧接着是6B的事务ID字段DB_TX_ID&＃xff0c;7B的回滚指针字段DB_ROLL_PTR
MySQL说&＃xff1a;继续向右就记录除了主键和值为null的列之外的真实数据了&＃xff0c;因为值为null的列会用其他方法表示&＃xff0c;目的就是为了节省空间。

额外信息区

下一行&＃xff1a;下一个数据行真实数据的地址。
行类型&＃xff1a;0代表普通数据行&＃xff0c;1代表索引目录行&＃xff0c;2是最小行&＃xff0c;3是最大行。
位置&＃xff1a;用13bit的heap_no来标记该行在整个页的位置
组行数&＃xff1a;如果这个行是分组的最后一行&＃xff0c;则在这里标记该组的行数
每层最小&＃xff1a;不懂&＃xff0c;但是在后面视频有详细讲解
删除标记&＃xff1a;在删除数据行时并不会直接移除&＃xff0c;而是修改这个标记。同时将这行的next_record指向一个称为垃圾链表的地方&＃xff0c;这个链表会用于事务回滚。
NULL值列表&＃xff1a;用来记录值为NULL的列&＃xff0c;根据列的个数来开辟合适的长度。
可变字段长度列表&＃xff1a;记录了数据行里所有变成字段的实际长度。

4. 最大行和最小行
每当我体内创建一个新页&＃xff0c;都会自动分配两个行。一个行类型为2的最小行Infimum&＃xff0c;固定在0号heap_no位置。一个行类型为3的最大行Supremum&＃xff0c;固定在1号heap_no位置。这两个行并不存储真实的信息&＃xff0c;而是作为数据行链表的头和尾。

5. 页目录
当程序猿大哥想要查询数据时&＃xff0c;需要沿着链表顺序一个个比对查找&＃xff0c;这样显然是不行的。所以我进化出了一个页目录的器官&＃xff0c;它会将页内包括头行、尾行在内的所有行进行分组&＃xff0c;约定头行为单独为一组&＃xff0c;其他每个组最多有8条数据。同时把每个组最后一个行在页中的地址&＃xff0c;按照主键从小到大的顺序记录到页中&＃xff0c;这个区域叫做页目录&＃xff0c;页目录的每一个位置称为一个槽&＃xff0c;每个槽对应一个分组。
在页目录中根据二分法查找到组&＃xff0c;再在组里面遍历八个数据行即可找到。

MySQL执行流程&＃xff08;以InnoDB为例&＃xff09;

1. 数据写入原理

省流&＃xff1a;

• 执行器执行写入操作&＃xff0c;把数据存储在Buffer Pool&＃xff0c;把操作信息存储在redo log Buffer
• 并且还把相关信息写入undo log和bin log
• 在一定时间之后&＃xff0c;redo log Buffer将操作信息写入redo log
• 再过一段时间之后&＃xff0c;Buffer Pool会把数据写入到磁盘文件中

• MySQL深深地知道一切的逻辑处理和写入都只操作内存中的数据&＃xff0c;这个内存缓存区被称为Buffer Pool。InnoDB会把它需要写入的数据插入或更新到Buffer Pool中。
• 当然&＃xff0c;为了能够让已经写入的数据支持回滚&＃xff0c;就需要在这之前将数据的旧值记录到另一个地方&＃xff0c;那就是Undo Log文件。
• 将数据写到Buffer Pool内存之后&＃xff0c;InnoDB就会让它的小线程们在一些特定的时机从内存中把需要更新写入的数据读出来&＃xff0c;同时写入磁盘。
• 一旦MySQL断电或者服务挂掉睡着&＃xff0c;那么处在内存区Buffer Pool中的数据就会随着内存失效而永久丢失。为了应对这种突发情况&＃xff0c;我的大脑InnoDB研发了一套redo Log体系。这套体系在数据进入Buffer Pool之后会将“更新写入信息”放入内存的另一个区域Redo Log Buffer&＃xff0c;然而光写入内存也无法解决刚才的问题&＃xff0c;为了保险起见还是要把它刷到磁盘中。我的InnoDB提供了多种Redo Log的刷盘机制。

# 将“更新写入信息”写入到操作系统Page cache和Redo Log Buffer&＃xff0c;并立刻刷新到磁盘中
innoDB_flush_log_at_trx_commit &＃61; 1;
# 将“更新写入信息”写入到Redo Log Buffer&＃xff0c;每隔一秒钟才进行系统内存放入和刷盘操作
innoDB_flush_log_at_trx_commit &＃61; 0;
# 将“更新写入信息”写入到操作系统Page cache和Redo Log Buffer&＃xff0c;每隔一秒钟才进行系统内存放入和刷盘操作
innoDB_flush_log_at_trx_commit &＃61; 2;


上述一系列操作都集成在MySQL内存&＃xff0c;我的程序猿大哥是不能直接操作的&＃xff0c;当我的程序猿大哥执行了错误的SQL语句就不能回复如初的。为了帮程序猿大哥解决这个问题&＃xff0c;我进化出了Bin Log文件。它可以给程序猿大哥提供变更历史查询、数据库备份和恢复、主从复制等功能&＃xff0c;我会在Redo日志写入的同时&＃xff0c;对Bin Log进行刷盘操作&＃xff0c;在Bin Log刷盘成功后&＃xff0c;我会告知Redo日志事务“已提交”这个信息&＃xff0c;Redo日志也会打入commit标志。这样&＃xff0c;一次数据写入的流程就完成啦。

提问&＃xff1a;既然为了防止断电&＃xff0c;redolog会立刻写入硬盘&＃xff0c;那为什么不干脆直接将buffer pool中的数据写入硬盘呢&＃xff1f;
一个事务可能包含多个语句&＃xff0c;数据不太可能都连续&＃xff0c;从bufferpool刷盘就需要大量磁盘io&＃xff0c;性能很低。而redolog日志本身不记录真实数据而是一些偏移量、页号之类的很小&＃xff0c;刷盘机制也都是顺序io&＃xff0c;所以效率高。

2. 数据查询原理

省流&＃xff1a;

• 查询缓存&＃xff0c;命中则返回结果&＃xff0c;不过5.8之后就把缓存撤销了
• 解析器&＃xff08;词法分析、语法分析&＃xff09;
• 优化器
• 多次执行同一个SQL&＃xff0c;则提交一次模板&＃xff0c;随后只需要多次提交参数

1. 查询缓存

查询缓存在5.7版本里是默认关闭的&＃xff0c;可以通过query cache type参数查看和设置。到了MySQL8.0&＃xff0c;整个缓存都删除了&＃xff0c;为什么官方都不建议使用这个看上去还不错的功能呢&＃xff1f;首先每次进来查询都要经过查询缓存&＃xff0c;加上每次查询条件不同&＃xff0c;导致命中率低&＃xff0c;那就非常消耗性能了。其次在没有命中缓存&＃xff0c;那就要创建新的缓存&＃xff0c;也会造成一定的消耗。最后&＃xff0c;为了保证数据一致&＃xff0c;会添加表级锁&＃xff0c;当表内数据更新后需要将这个表对应的缓存均设为失效&＃xff0c;又是一波不小的消耗。综上所述&＃xff0c;很多情况下“查询缓存”并不能为我们带来实质的效率提升。&＃xff08;最终原因就是缓存命中率低&＃xff09;。

# 查询缓存开关
SHOW VARIABLES LIKE &＃39;query_cache_type&＃39;;
# 设置缓存开关
SET query_cache_type &＃61; 0;
#query_cache_type&＃61;2时&＃xff0c;代表按需使用&＃xff0c;用SQL_CACHE对某表开启缓存
SELECT SQL_CACHE * FROM test;


2. SQL解析器

词法分析&＃xff1a;通过sql/sql_lex.cc代码将SQL语句切分为各种Token词&＃xff0c;通过其中一个定义了各类关键字、操作符的数组symbols[]进行关键字、非关键字的标记。

语法分析&＃xff1a;使用bison这个语法分析器&＃xff0c;通过sql/sql_yacc.cc规则代码将语句解析为一个语法树。

3. 预处理器

MySQL说&＃xff1a;预处理器将请求进行拆分&＃xff0c;先提交SQL模板语句&＃xff0c;然后再提交参数并进行执行。通过这种方式&＃xff0c;对于重复执行的语句来说&＃xff0c;可以提交并处理一次模板即可&＃xff0c;然后不断的提交参数就可以实现多次执行&＃xff0c;从而提高执行的效率。

MySQL存储引擎

1. 为什么需要不同的存储引擎

关系型数据库&＃xff0c;就是用来存储各种数据信息的。根据不同业务场景&＃xff0c;比如说&＃xff1a;有的表简单&＃xff0c;有的表复杂&＃xff0c;有的表根本不用来存储任何长期的数据&＃xff0c;有的表需要查询非常快。在我们实际的业务开发中&＃xff0c;可能需要用到各种各样的表&＃xff0c;不同的表也意味着存储不同类型的数据&＃xff0c;数据的处理上也就会存在着差异。针对 MySQL 来说&＃xff0c;它提供了很多类型的存储引擎来供我们选择&＃xff0c;我们可以根据业务需求来选择不同的存储引擎&＃xff0c;最大程度的发挥 MySQL 的强大之处。

2. 如何为数据指定不同的存储引擎&＃xff0c;数据粒度又是多少

一个库中的每一个表都可以指定选择存储引擎&＃xff0c;即数据粒度就是一个数据表

3. MySQL支持哪些存储引擎

我们可以使用命令&＃xff1a;show engines;来查看 MySQL 支持哪些存储引擎&＃xff0c;如下图所示。我们可以看到 MySQL 默认使用的存储引擎是 InnoDB。

4. MySQL支持的存储引擎各自有什么特性

1. CSV存储引擎
特点&＃xff1a;没有索引&＃xff0c;没有自增&＃xff0c;列必须为NOT NULL
缺点&＃xff1a;不适用于大表或者数据查询、排序等处理操作
2. Archive存储引擎
特点&＃xff1a;只支持insert 和 select 两种操作、只支持自增ID列建立索引、支持行级锁、不支持事务
缺点&＃xff1a;不适用于对数据的处理操作
优点&＃xff1a;数据占用磁盘少
3. Memory存储引擎
特点&＃xff1a;实际的应用场景中用到的很少&＃xff0c;但是它与优化器查询有很大的关系。对于精确查询非常高效、存储在内存、支持Hash索引、B- Tree索引
优点&＃xff1a; 数据都是存储在内存中&＃xff0c;IO效率比其他引擎高很多
缺点&＃xff1a;保证不了持久性&＃xff0c;不支持大数据存储类型

4. InnoDB存储引擎
特点&＃xff1a;支持事务、支持外键、支持行级锁、支持表级锁、必须有主键、5.8后支持全文索引、辅助索引和主键索引之间存在层级关系
5. MyISAM存储引擎
特点&＃xff1a;不支持事务、不支持外键、不支持行级锁、支持表级锁、主键不是必须的、支持全文索引、MyISAM辅助索引和主键索引则是平级关系

索引

1. 索引的概念

索引是一种利用某种规则的数据结构与实际数据的关系加快数据查找的功能&＃xff1b;索引数据节点中有着实际文件的位置&＃xff0c;因为索引是根据特定的规则和算法构建的,在查找的时候遵循索引的规则可以快速查找到对应数据的节点&＃xff0c;从而达到快速查找数据的效果&＃xff1b;说白了&＃xff0c;就是把每个数据都放在不同的盒子里&＃xff0c;再根据数据结构和算法快速找到指定的盒子。

2. 为什么要使用索引

a. 没有索引的情况下访问数据

b. 使用平衡二叉树索引

---

总结&＃xff1a;你的数据并不是乱放的&＃xff0c;而是根据一定的规则放置的。这样才能按一定的规则快速查找到&＃xff0c;无需遍历整个数据表。

3. InnoDB索引底层为什么选择B&＃43;Tree

接下来&＃xff0c;我们将从 二叉树、平衡二叉树、B-Tree、B&＃43;Tree 等方面来分析&＃xff0c;了解 MySQL 索引底层为什么会选择 B&＃43;Tree 。

---

a. 二叉树

场景一&＃xff1a;可以避免遍历整张数据表

场景二&＃xff1a;我们会发现&＃xff0c;这种链表样式的二叉树检索和全表扫描没有太大的区别

总结

我们发现二叉树最终的检索效率&＃xff0c;其实是取决于数据的分布情况。如果数据分布均匀&＃xff0c;还是能提高一定的查询效率的&＃xff1b;数据如果分布不均匀&＃xff0c;用二叉树来做索引的话&＃xff0c;还是存在着一定的不足性。

---

b. 二叉平衡树&＃xff08;解决二叉树线性链表的问题&＃xff09;

二叉平衡树检索数据的过程

1. 磁盘块分析&＃xff1a;一个节点就是一个磁盘块&＃xff0c;一个磁盘块保存有如下内容

• 关键字&＃xff1a;比如说我们创建 ID 为索引&＃xff0c;ID&＃61;15&＃xff0c;那么15 就是一个关键字。
• 数据区&＃xff1a;指向真正数据存储的磁盘位置(内存地址)&＃xff0c;通过数据区去加载数据。一般情况下&＃xff0c;为了节省空间&＃xff0c;数据区是不会保存数据的&＃xff0c;而是指向磁盘的一个位置&＃xff0c;然后去磁盘把内容加载过来&＃xff0c;达到快速检索的目的。
• 子节点引用&＃xff1a;指向子节点的引用。

2. 检索数据的过程

• 首先将磁盘块1加载到内存中&＃xff0c;获取到根节点数据。让 12 和 15 这个关键字进行比对&＃xff0c;12<15&＃xff0c;会基于 P1 (P1、P2是指向下一个磁盘块的地址)&＃xff0c;通过顺序 IO 快速的去加载磁盘块 2 中的内容&＃xff1b;
• 12 和 10 继续比对&＃xff0c;12 > 10&＃xff0c;走P2 &＃xff0c;加载磁盘块 5&＃xff0c;然后命中 12。命中后&＃xff0c;获取到数据区地址&＃xff0c;再次加载磁盘中的数据。
  

平衡二叉树&＃xff0c;还存在哪些缺陷、问题

• 太深了&＃xff1a;比如说我们检索 12&＃xff0c;需要做 3 次 IO 操作获得数据。(3层高度只能存放 7 个数据)如果我们数据达到亿级别&＃xff0c;那么这棵二叉树会有多大&＃xff0c;我们无法想象。然而IO 操作是很费时的&＃xff0c;显然无法满足条件。
• 太小了&＃xff1a;通过N多次 IO 操作后&＃xff0c;拿到的数据太少了
• 没能很好的利用数据交换特性
• 没有很好的利用磁盘IO的预读能力

---

c. B树&＃xff08;解决了平衡二叉树的缺点&＃xff09;

B-Tree相比平衡二叉树&＃xff0c;优势在哪&＃xff1f;
相比平衡二叉树(共3层能存储7个数据)&＃xff0c;B-Tree同样是三层&＃xff0c;这个存储的数据就很多了&＃xff0c;翻倍形式的增长。B-Tree 为多路平衡树&＃xff0c;如果我们定成1000路&＃xff0c;同样三层 IO 操作&＃xff0c;它能检索的数据就更多了。(形象理解&＃xff1a;平衡二叉树就是瘦高&＃xff0c;B-Tree就是矮胖) 在这种层度上&＃xff0c;多路平衡查找树完胜平衡二叉树。

相比平衡二叉树(共3层能存储7个数据)&＃xff0c;B-Tree同样是三层&＃xff0c;这个存储的数据就很多了&＃xff0c;翻倍形式的增长。B-Tree 为多路平衡树&＃xff0c;如果我们定成1000路&＃xff0c;同样三层 IO 操作&＃xff0c;它能检索的数据就更多了。(形象理解&＃xff1a;平衡二叉树就是瘦高&＃xff0c;B-Tree就是矮胖) 在这种层度上&＃xff0c;多路平衡查找树完胜平衡二叉树。
还记得操作系统是一次性交互 4K 数据&＃xff0c;MySQL 一个节点 是 16K。如果我们以 ID 作为索引&＃xff0c;一个索引数据&＃xff08;索引数据 4byte &＃43; 其他冗余数据大约 4byte(数据区&＃43;子类引用等数据)&＃xff09;共 8byte (字节)的话&＃xff0c;16K 能存放 &＃xff08;16*1024/8&＃61;&＃xff09;2048 个索引&＃xff0c;那就能存放&＃xff08;2048&＃43;1&＃61;&＃xff09;2049路。这 2049 路能存放多少数据&＃xff0c;就多到数不清了吧。(Tips&＃xff1a;加索引时&＃xff0c;我们之前只知道数据类型、字段定义越精简越好&＃xff0c;为什么呢&＃xff1f;因为定义数据类型、字段时&＃xff0c;越越精简占用空间越小&＃xff0c;一个磁盘块能存放的数据就更多。)


B-Tree如何检索数据

我们以检索 32 为例&＃xff0c;流程如下&＃xff1a;
首先将磁盘块1加载到内存中&＃xff0c;获取到根节点数据。然后让 32 和 17、35进行对比&＃xff0c;发现 17 <32 <35。此时会基于 P2 (P1、P2、P3是指向下一个磁盘块的地址)&＃xff0c;通过顺序 IO 快速的去加载磁盘块 3 中的内容&＃xff1b;(Tips&＃xff1a;检索数据X&＃xff1a;X<17 走P1 1735 走P3)
32 > 29&＃xff0c;走P3&＃xff0c;加载磁盘块 10 中的内容&＃xff0c;然后命中 32。命中后&＃xff0c;获取到数据区地址&＃xff0c;再根据地址来加载磁盘中的数据。

B Tree缺点

• B-Tree查询单个值效率并不低&＃xff0c;但是如果要搜索大于10小于15这样一个范围&＃xff0c;或者要遍历所有数据&＃xff0c;那么B树就力不从心了。
• B- Tree中&＃xff0c;要查找的数据离根节点近查找的快&＃xff0c;离根节点远查找的慢。这样不稳定的搜索时间并不利于我为程序猿大哥们做执行成本的估算。
• B- Tree虽然已经降低了树的高度&＃xff0c;但是仍然有下降空间

---

d. B&＃43; Tree

B&＃43;树&＃xff0c;如何检索数据呢&＃xff1f;

根据上图&＃xff0c;我们以检索 28 为例&＃xff0c;流程如下&＃xff1a;
在根节点&＃xff0c;我们就发现已经有 28 了。 B树则会直接停止向下检索。B&＃43;树则会继续向下检索【因为 B&＃43;树枝节点不存储数据&＃xff0c;数据都是保存在叶子节点】
然后他会继续向下去检索&＃xff0c;直到走到叶子节点&＃xff0c;然后命中 28。命中后&＃xff0c;它才会获取到数据区地址&＃xff0c;再根据地址来加载磁盘中的数据。

MySQL 为什么选择 B&＃43;Tree 呢&＃xff1f;

• 1.扫库、扫表能力更强&＃xff1a;B-Tree 扫库扫表&＃xff0c;会扫每一个枝节点&＃xff0c;最后再扫子节点&＃xff0c;基本都得扫一遍。B&＃43;Tree&＃xff0c;只扫子节点即可。
• 2.磁盘读写能力更强&＃xff1a;B&＃43;Tree 枝节点不保存数据&＃xff0c;只保存关键字和子节点引用。从理论上来将&＃xff0c;能保存的关键字就更多&＃xff0c;能加载的关键字就更多。从理论上来讲磁盘的读写能力就更强
• 3.查询效率更加稳定&＃xff1a;查询数据&＃xff1a;比如使用 B-Tree 第一次查询比较幸运&＃xff0c;第二层就出来了并返回&＃xff0c;用了0.2s。B树是平衡树&＃xff0c;此时如果又新插入一些数据&＃xff0c;导致刚刚查询的数据到达了底层&＃xff0c;那么此时第二次查询。时间就要大于0.2s了。即&＃xff1a;节点数据节点的深度是会变化的&＃xff0c;会随着数据的插入、删除而改变&＃xff0c;从而导致查询时间有所不稳定数据更多的话&＃xff0c;这个时间就会比第一次差距更大了&＃xff08;B&＃43;Tree查询&＃xff0c;都会去叶子节点查找数据&＃xff0c;相对B-Tree来说就更加稳定&＃xff09;

4. 什么时候不要用索引&＃xff0c;什么时候要用索引

a. 什么场景不要用索引

1. 数据更新性能比查询性能要求要高的情况下不要使用索引&＃xff0c;因为数据的更新的同时索引也要进行维护和更新&＃xff08;加了索引查询快但更新就会慢&＃xff09;;

2. 不要盲目的给表建太多索引&＃xff0c;因为索引本身的存储也要占用存储空间&＃xff0c;一旦更新操作频繁反而降低新性能;

3. 不要给不经常使用的列建索引&＃xff0c;不怎么查询还建索引干嘛;

4. 不要给高重复值的列建索引&＃xff0c;索引本身就是为了提高查询速度&＃xff0c;然而数据值高度重复&＃xff0c;数据区别性不高&＃xff0c;索引起不了效果&＃xff09;&＃xff08;比如说&＃xff1a;性别&＃xff09;;

5. 不要给img,tex.bit数据类型使用索引&＃xff0c;因为这种字段一般使用很少&＃xff0c;数据量太大;

b. 什么场景用索引

1. 经常要用于查询的列 where id&＃61;?;

2. 经常要用于排序(order by)&＃xff0c;分组(group by)的列&＃xff0c;因为索引已经排好序了;

3. 有值唯一性限制的列&＃xff0c;比如说主键、用户名&＃xff1b;

5. 聚簇索引与非聚簇索引

InnoDB使用聚簇索引&＃xff1a;索引节点包含数据

MyISAM使用非聚簇索引&＃xff1a;索引节点与数据分开

聚簇索引与非聚簇索引的合作查询

如果通过非聚簇索引查询&＃xff0c;首先会在这棵“非聚簇索引”树种快速找到叶子结点&＃xff0c;叶子结点中有聚簇索引的key&＃xff0c;拿着这个key再去聚簇索引中查询一遍&＃xff0c;就可以拿到真实数据了。以下是合作查询流程&＃xff1a;

6. 辅助索引

省流&＃xff1a;因为数据存放在聚簇索引中&＃xff0c;所以辅助索引找到key&＃xff0c;再根据key聚簇索引中找到数据

省流&＃xff1a;反正非聚簇索引找到key的数据地址也要到其他文件找数据&＃xff0c;倒不如辅助索引自己去找数据

7. 全文索引

1. 概念

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询&＃xff0c;但是&＃xff0c;如果希望通过关键字的匹配来进行查询过滤&＃xff0c;那么就需要基于相似度的查询&＃xff0c;而不是原来的精确数值比较。全文索引就是为这种场景设计的。

你可能会说&＃xff0c;用 like &＃43; % 就可以实现模糊匹配了&＃xff0c;为什么还要全文索引&＃xff1f;like &＃43; % 在文本比较少时是合适的&＃xff0c;但是对于大量的文本数据检索&＃xff0c;是不可想象的。全文索引在大量的数据面前&＃xff0c;能比 like &＃43; % 快 N 倍&＃xff0c;速度不是一个数量级&＃xff0c;但是全文索引可能存在精度问题。

2. 实现

全文索引通常用倒排索引来实现&＃xff0c;倒排索引和BTree一样&＃xff0c;是一种索引结构&＃xff0c;它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射&＃xff0c;其拥有两种表现形式&＃xff1a;

• inverted file idnex&＃xff1a;{单词&＃xff0c;单词文档所在ID}
• full inverted index&＃xff1a;{单词&＃xff0c;&＃xff08;单词文档所在ID&＃xff0c;具体文档中的位置&＃xff09;}

例如有如下表 A&＃xff1a;

用 inverted file idnex 方式存储内容如下所示&＃xff1a;

用 full inverted index 方式存储内容如下所示&＃xff1a;

相比之下&＃xff0c;full inverted index会占用更多空间&＃xff0c;但能更好的定位数据&＃xff0c;并扩充其他一些搜索特性。InnoDB全文检索采用full inverted index方式。

3. 使用

3.1 创建全文索引

# 1.创建表时创建全文索引
create table fulltext_test (
id int(11) NOT NULL AUTO_INCREMENT,
content text NOT NULL,
tag varchar(255),
PRIMARY KEY (id),
FULLTEXT KEY content_tag_fulltext(content,tag) // 创建联合全文索引列
) ENGINE&＃61;MyISAM DEFAULT CHARSET&＃61;utf8;
# 2.在已存在的表上创建全文索引
create fulltext index content_tag_fulltext
on fulltext_test(content,tag);
# 3.通过 SQL 语句 ALTER TABLE 创建全文索引
alter table fulltext_test
add fulltext index content_tag_fulltext(content,tag);


3.2 删除全文索引

# 1. 直接使用 DROP INDEX 删除全文索引
drop index content_tag_fulltext
on fulltext_test;
# 2. 通过 SQL 语句 ALTER TABLE 删除全文索引
alter table fulltext_test
drop index content_tag_fulltext;


3.3 使用全文索引
和常用的模糊匹配使用 like &＃43; % 不同&＃xff0c;全文索引有自己的语法格式&＃xff0c;使用 match 和 against 关键字&＃xff0c;比如

select * from fulltext_test
where match(content,tag) against(&＃39;xxx xxx&＃39;);


8. 索引失效

准备
建立员工记录表staffs&＃xff08;id&＃xff0c;name&＃xff0c;age&＃xff0c;pos&＃xff0c;add_time&＃xff09;&＃xff0c;并且给表中name、age、pos字段添加索引&＃xff08;注意三个在字段的顺序&＃xff09;

alter table staff
add index idx_staffs_nameAgePos(name,age,pos)


8.1 最佳左前缀法则

1. 概念
指的是查询从索引的最左前列开始并且不跳过索引中的列

# 情况1&＃xff1a;name字段索引一直被使用
SELECT * FROM staffs WHERE NAME &＃61; ‘July’;
SELECT * FROM staffs WHERE NAME &＃61; ‘July’ and age &＃61; 25;
SELECT * FROM staffs WHERE NAME &＃61; ‘July’ and age &＃61; 25 and pos &＃61; &＃39;dev&＃39;;
SELECT * FROM staffs WHERE NAME &＃61; ‘July’ and pos &＃61; &＃39;dev&＃39; and age &＃61; 25;
# 情况2&＃xff1a;当不查找name而去查找后面age和pos字段时&＃xff0c;索引失效
SELECT * FROM staffs WHERE age &＃61; 25 and pos &＃61; &＃39;dev&＃39;;
# 情况3&＃xff1a;查找了name字段和pos字段&＃xff0c;而缺少了中间的age字段&＃xff0c;字段pos的索引失效&＃xff0c;只用到了name字段索引
SELECT * FROM staffs WHERE NAME &＃61; ‘July’ and pos &＃61; &＃39;dev&＃39;;


8.2 不要再索引列上做任何操作

不要在索引列上左任何操作&＃xff08;计算&＃xff0c;函数&＃xff0c;自动or手动类型转换&＃xff09;&＃xff0c;否则会导致索引失效转换为全表扫描。
下图可以看到&＃xff0c;没用left函数之前&＃xff0c;是一个正常的索引引用&＃xff0c;使用函数之后&＃xff0c;索引失效了。

---

典型问题&＃xff1a;字符串不加单引号会导致索引失效
name字段为varchar类型&＃xff0c;假设有一行name&＃61;‘2000’&＃xff0c;这时如果用name&＃61;2000条件查找也会有结果

用EXPLAIN分析

可以看出&＃xff0c;用name&＃61;2000作为判断条件&＃xff0c;不加单引号导致索引失效了&＃xff0c;为什么&＃xff1f;

因为name&＃61;2000也可以查出来是因为MySQL在底层做了一次类型转换&＃xff0c;把整形2000转换成了字符串’2000’&＃xff0c;所以索引失效。

8.3 存储引擎不能使用索引中范围条件右边的列

省流

并不是给一个列建立了索引&＃xff0c;对这个列进行范围查询的时候&＃xff0c;就会走索引&＃xff0c;他是有一个比例值的。比例值会随着版本、服务器、IO、数据量、数据重复情况而不同。MySQL根据比值来选择索引或全表扫描

提出问题

第一个查询全等值匹配没问题
第二个查询中间使用了范围条件&＃xff0c;结果导致manger的索引失效

但实际上&＃xff0c;where的范围条件判断后面是否走索引&＃xff0c;还需要做其他判断&＃xff0c;总的来说&＃xff0c;where的范围条件判断后面不一定走索引

范围查询一定走索引吗&＃xff1f;
现在就范围查询是否走索引这个问题讨论&＃xff0c;后面再总结范围查询后面的字段走不走索引。

1. MySQL5.5版本

准备数据&＃xff1a;

测试数据&＃xff1a;

分析数据&＃xff1a;
user_age <8 比值为&＃xff1a;35042/210463 &＃61; 0.1664&＃xff08;走索引&＃xff09;
user_age <&＃61; 8比值为&＃xff1a;(35042 &＃43; 2221)/210463 &＃61; 0.1770&＃xff08;不走索引&＃xff09;
以上可知&＃xff1a;比例值<&＃61;0.1664可以走索引&＃xff0c;比例值>&＃61;0.1770优化器认为全表扫描更快&＃xff0c;所以就不走索引。

2. MySQL5.6版本

准备数据&＃xff1a;

测试数据

分析数据&＃xff1a;
user_age <8 &＃xff1a;35042 / 208534 &＃61; 0.1680&＃xff08;走索引&＃xff09;
user_age <&＃61; 8&＃xff1a;(35042&＃43;2221)/208534 &＃61; 0.1786&＃xff08;不走索引&＃xff09;
由此可大致推断&＃xff1a;5.6版本下&＃xff0c;该比例值在 0.1680 ~ 0.1786之间。

3. MySQL5.7版本

准备数据&＃xff1a;

测试数据&＃xff1a;

分析数据&＃xff1a;
user_age <20&＃xff1a;87966 / 208303 &＃61; 0.422 &＃xff08;走索引&＃xff09;
user_age <&＃61; 20&＃xff1a;(87966 &＃43; 2093)/208303 &＃61; 0.4323&＃xff08;不走索引&＃xff09;

4. MySQL5.8版本

准备数据&＃xff1a;

测试数据&＃xff1a;

分析数据&＃xff1a;
user_age <12&＃xff1a;54798 / 208611 &＃61; 0.2626&＃xff08;走索引&＃xff09;
user_age <&＃61; 12&＃xff1a;(54798 &＃43; 2051)/208611 &＃61; 0.2725&＃xff08;不走索引&＃xff09;

范围查询后面的字段走不走索引&＃xff1f;
其实这个问题比较简单&＃xff0c;根据最佳左前缀法则&＃xff0c;在联合索引(name,age,pos)中&＃xff0c;如果age进行范围查询后都不走索引了&＃xff0c;pos还能走索引吗&＃xff1f;肯定不能啦。如果age还能走索引&＃xff0c;pos自然也能走索引。

8.4 尽量使用覆盖索引

只访问索引列的查询&＃xff0c;索引列和查询列一致&＃xff0c;避免select *

当只访问了索引列后&＃xff0c;Extra中出现了Using index&＃xff0c;表明是覆盖查询&＃xff0c;这是好的

8.5 like以通配符(%)开头的索引会失效变成全表扫描

通配符出现最前面说明所有都适配&＃xff0c;所有都要扫描
不以%开头则不失效

那我既想要以%作为开头来查询&＃xff0c;又想要这个查询是走索引的&＃xff0c;怎么办&＃xff1f;可以使用覆盖索引

准备数据&＃xff1a;tb1_user表中有id,name,age,email四个字段&＃xff0c;对name,age字段设置索引,id自动为主键索引。那么查询id,name,age字段都是覆盖索引&＃xff0c;都可以使索引生效

但是如果使用explain select * from tb1_user where name like &＃39;%aa%&＃39;就会索引失效&＃xff0c;因为此时不是覆盖索引&＃xff0c;不是覆盖索引使用%会失效