1.什么是BufferPool？
Buffer Pool
基本概念
Buffer Pool
：缓冲池，简称
BP
。其作⽤是⽤来缓存表数据与索引数据，减 少磁盘IO
操作，提升效率。
Buffer Pool
由
缓存数据⻚
(Page)
和 对缓存数据⻚进⾏描述的
控制块
组成
, 控制块中存储着对应缓存⻚的所属的 表空间、数据⻚的编号、以及对应缓 存⻚在Buffer Pool中的地址等信息
.
Buffer Pool
默认⼤⼩是
128M,
以
Page
⻚为单位，
Page
⻚默认⼤⼩
16K
，⽽ 控制块的⼤⼩约为数据⻚的5%
，⼤ 概是
800
字节。
注 : Buffer Pool ⼤⼩为 128M 指的就是缓存⻚的⼤⼩，控制块则⼀般占5%，所以每次会多申请 6M 的内存空间⽤于存放控制块
如何判断⼀个⻚是否在
BufferPool
中缓存
?
MySQl
中有⼀个哈希表数据结构，它使⽤表空间号
+
数据⻚号，作为⼀个 key，然后缓冲⻚对应的控制块作为
value
。
当需要访问某个⻚的数据时，先从哈希表中根据表空间号
+
⻚号看看是
否存在对应的缓冲⻚。
如果有，则直接使⽤；如果没有，就从
free
链表中选出⼀个空闲的缓冲
⻚，然后把磁盘中对应的⻚加载到该缓冲⻚的位置
2.InnoDB如何管理Page⻚？
Page
⻚分类
BP
的底层采⽤链表数据结构管理
Page
。在
InnoDB
访问表记录和索引时会 在Page
⻚中缓存，以后使⽤可以减少磁盘
IO
操作，提升效率。
Page
根据状态可以分为三种类型：
free page
： 空闲
page
，未被使⽤
clean page
：被使⽤
page
，数据没有被修改过
dirty page
：脏⻚，被使⽤
page
，数据被修改过，
Page
⻚中数据和磁盘 的数据产⽣了不⼀致
Page
⻚如何管理
针对上⾯所说的三种
page
类型，
InnoDB
通过三种链表结构来维护和管理
1. free list
：表示空闲缓冲区，管理
free page
free
链表是把所有空闲的缓冲⻚对应的控制块作为⼀个个的节点放到⼀ 个链表中，这个链表便称之为free
链表
基节点
: free
链表中只有⼀个基节点是不记录缓存⻚信息
(
单独申请空 间)
，它⾥⾯就存放了
free
链表的头节点的地址，尾节点的地址，还有 free链表⾥当前有多少个节点。
2.flush list
： 表示需要刷新到磁盘的缓冲区，管理
dirty page
，内部
page 按修改时间排序。
InnoDB
引擎为了提⾼处理效率，在每次修改缓冲⻚后，并不是⽴刻把 修改刷新到磁盘上，⽽是在未来的某个时间点进⾏刷新操作.
所以需要 使⽤到flush
链表存储脏⻚，凡是被修改过的缓冲⻚对应的控制块都会作 为节点加⼊到flush
链表
.
flush
链表的结构与
free
链表的结构相似
3.lru list
：表示正在使⽤的缓冲区，管理
clean page
和
dirty page
，缓冲区 以midpoint
为基点，前⾯链表称为
new
列表区，存放经常访问的数据，占 63%；后⾯的链表称为
old
列表区，存放使⽤较少数据，占
37%
3.为什么写缓冲区，仅适用于非唯一普通索引页？
change Buffer
基本概念
Change Buffer
：写缓冲区
,
是针对⼆级索引
(
辅助索引
)
⻚的更新优化措施。
作⽤
:
在进⾏
DML
操作时，如果请求的辅助索引（⼆级索引）没有在缓冲 池中时，并不会⽴刻将磁盘⻚加载到缓冲池，⽽是在CB
记录缓冲变更，等 未来数据被读取时，再将数据合并恢复到BP
中。
1. ChangeBuffer
⽤于存储
SQL
变更操作，⽐如
Insert/Update/Delete
等 SQL语句
2. ChangeBuffer
中的每个变更操作都有其对应的数据⻚，并且该数据⻚ 未加载到缓存中；
3.
当
ChangeBuffer
中变更操作对应的数据⻚加载到缓存中后，
InnoDB
会 把变更操作Merge
到数据⻚上；
4. InnoDB
会定期加载
ChangeBuffer
中操作对应的数据⻚到缓存中，并 Merge变更操作；
change buffer
更新流程
写缓冲区，仅适⽤于⾮唯⼀普通索引⻚，为什么？
如果在索引设置唯⼀性，在进⾏修改时，
InnoDB
必须要做唯⼀性校验，因此必须查询磁盘，做⼀次IO
操 作。会直接将记录查询到 BufferPool中，然后在缓冲池修改，不会在
ChangeBuffer
操作。
4.MySQL为什么改进LRU算法？
普通
LRU
算法
LRU = Least Recently Used
（最近最少使⽤）
:
就是末尾淘汰法，新数据从 链表头部加⼊，释放空间时从末尾淘汰.
1.
当要访问某个⻚时，如果不在
Buffer Pool
，需要把该⻚加载到缓冲池
, 并且把该缓冲⻚对应的控制块作为节点添加到LRU
链表的头部。
2.
当要访问某个⻚时，如果在
Buffer Pool
中，则直接把该⻚对应的控制块 移动到LRU
链表的头部
3.
当需要释放空间时
,
从最末尾淘汰
普通
LRU
链表的优缺点
优点
所有最近使⽤的数据都在链表表头，最近未使⽤的数据都在链表表尾
,
保 证热数据能最快被获取到。
缺点
如果发⽣全表扫描（⽐如：没有建⽴合适的索引
or
查询时使⽤
select * 等），则有很⼤可能将真正的热数据淘汰掉.
由于
MySQL
中存在预读机制，很多预读的⻚都会被放到
LRU
链表的表 头。如果这些预读的⻚都没有⽤到的话，这样，会导致很多尾部的缓冲 ⻚很快就会被淘汰。
改进型
LRU
算法
改进型
LRU
：将链表分为
new
和
old
两个部分，加⼊元素时并不是从表头插⼊，⽽是从中间midpoint
位置插⼊
(
就是说从磁盘中新读出的数据会放在冷 数据区的头部)
，如果数据很快被访问，那么
page
就会向
new
列表头部移 动，如果数据没有被访问，会逐步向old
尾部移动，等待淘汰。
冷数据区的数据⻚什么时候会被转到到热数据区呢
?
1.
如果该数据⻚在
LRU
链表中存在时间超过
1s
，就将其移动到链表头部
( 链表指的是整个LRU
链表
)
2.
如果该数据⻚在
LRU
链表中存在的时间短于
1s
，其位置不变
(
由于全表扫 描有⼀个特点，就是它对某个⻚的频繁访问总耗时会很短)
3. 1s
这个时间是由参数
innodb_old_blocks_time
控制的
5.使⽤索引⼀定可以提升效率吗？
索引就是排好序的
,
帮助我们进⾏快速查找的数据结构
.
简单来讲，索引就是⼀种将数据库中的记录按照特殊形式存储的数据结 构。通过索引，能够显著地提⾼数据查询的效率，从⽽提升服务器的性能.
索引的优势与劣势
优点
提⾼数据检索的效率
,
降低数据库的
IO
成本
通过索引列对数据进⾏排序
,
降低数据排序的成本
,
降低了
CPU
的消耗
缺点
创建索引和维护索引要耗费时间，这种时间随着数据量的增加⽽增加
索引需要占物理空间，除了数据表占⽤数据空间之外，每⼀个索引 还要占⽤⼀定的物理空间
当对表中的数据进⾏增加、删除和修改的时候，索引也要动态的维 护，降低了数据的维护速度
创建索引的原则
在经常需要搜索的列上创建索引，可以加快搜索的速度；
在作为主键的列上创建索引，强制该列的唯⼀性和组织表中数据的 排列结构；
在经常⽤在连接的列上，这些列主要是⼀些外键，可以加快连接的 速度；
在经常需要根据范围进⾏搜索的列上创建索引，因为索引已经排 序，其指定的范围是连续的；
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可 以利⽤索引的排序，加快排序查询时间；
在经常使⽤在
WHERE
⼦句中的列上⾯创建索引，加快条件的判断速 度。
6.介绍一下Page页的结构？
Page
是整个
InnoDB
存储的最基本构件，也是
InnoDB
磁盘管理的最⼩单 位，与数据库相关的所有内容都存储在这种Page
结构⾥。
Page
分为⼏种类型，常⻅的⻚类型有数据⻚（
B+tree Node
）
Undo
⻚ （Undo Log Page
）系统⻚（
System Page
） 事务数据⻚（
Transaction System Page）等
Page
各部分说明
File Header
字段⽤于记录
Page
的头信息，其中⽐较重要的是 FIL_PAGE_PREV 和
FIL_PAGE_NEXT
字段，通过这两个字段，我们可以 找到该⻚的上⼀⻚和下⼀⻚，实际上所有⻚通过两个字段可以形成⼀条 双向链表
Page Header
字段⽤于记录
Page
的状态信息。
Infimum
和
Supremum
是两个伪⾏记录，
Infimum
（下确界）记录⽐ 该⻚中任何主键值都要⼩的值，Supremum
（上确界）记录⽐该⻚中 任何主键值都要⼤的值，这个伪记录分别构成了⻚中记录的边界。
User Records
中存放的是实际的数据⾏记录
Free Space 中存放的是空闲空间，被删除的⾏记录会被记录成空闲空间
Page Directory 记录着与⼆叉查找相关的信息
File Trailer 存储⽤于检测数据完整性的校验和等数据。
⻚结构整体上可以分为三⼤部分，分别为通⽤部分
(
⽂件头、⽂件尾
)
、存储
记录空间、索引部分。
1.
通⽤部分
(File Header&File Trailer ) 通⽤部分 :
主要指⽂件头和⽂件尾，将⻚的内容进⾏封装，通过⽂件头和⽂ 件尾校验的CheckSum
⽅式来确保⻚的传输是完整的。
其中⽐较重要的是在⽂件头中的
FIL_PAGE_PREV
和
FIL_PAGE_NEXT
字 段，通过这两个字段，我们可以找到该⻚的上⼀⻚和下⼀⻚，实际上所有 ⻚通过两个字段可以形成⼀条双向链表
2.
记录部分
(User Records&Free Space)
⻚的主要作⽤是存储记录，所以
“
最⼩和最⼤记录
”
和
“
⽤户记录
”
部分占了⻚ 结构的主要空间。另外空闲空间是个灵活的部分，当有新的记录插⼊时， 会从空闲空间中进⾏分配⽤于存储新记录
3)
数据⽬录部分
(Page Directory)
数据⻚中⾏记录按照主键值由⼩到⼤顺序串联成⼀个单链表
(
⻚中记录是以
单向链表的形式进⾏存储的
)
，且单链表的链表头为最⼩记录，链表尾为最 ⼤记录。并且为了更快速地定位到指定的⾏记录，通过 Page Directory 实现⽬录的功能，借助 Page Directory
使⽤⼆分法快速找到需要查找的 ⾏记录。
7.说⼀下聚簇索引与非聚簇索引？
聚集索引与⾮聚集索引的区别是：叶节点是否存放⼀整⾏记录
聚簇索引
:
将数据存储与索引放到了⼀块
,
索引结构的叶⼦节点保存了⾏ 数据.
⾮聚簇索引
：将数据与索引分开存储，索引结构的叶⼦节点指向了数据 对应的位置.
InnoDB
主键使⽤的是聚簇索引，
MyISAM
不管是主键索引，还是⼆级索引 使⽤的都是⾮聚簇索引。
在
InnoDB
引擎中，主键索引采⽤的就是聚簇索引结构存储。
聚簇索引（聚集索引）
聚簇索引是⼀种数据存储⽅式，
InnoDB
的聚簇索引就是按照主键顺序 构建 B+Tree
结构。
B+Tree
的叶⼦节点就是⾏记录，⾏记录和主键值紧 凑地存储在⼀起。 这也意味着 InnoDB
的主键索引就是数据表本身，它 按主键顺序存放了整张表的数据，占⽤的空间就是整个表数据量的⼤ ⼩。通常说的主键索引就是聚集索引。
InnoDB
的表要求必须要有聚簇索引：
如果表定义了主键，则主键索引就是聚簇索引
如果表没有定义主键，则第⼀个⾮空
unique
列作为聚簇索引
否则
InnoDB
会从建⼀个隐藏的
row-id
作为聚簇索引
辅助索引
InnoDB
辅助索引，也叫作⼆级索引，是根据索引列构建
B+Tree
结构。但在 B+Tree
的叶⼦节点中只存了索引列和主键的信息。⼆级索引占⽤ 的空间会⽐聚簇索引⼩很多， 通常创建辅助索引就是为了提升查询效 率。⼀个表InnoDB只能创建⼀个聚簇索引，但可以创建多个辅助索引。
非聚簇索引
与
InnoDB
表存储不同，
MyISM
使⽤的是⾮聚簇索引，⾮聚簇索引的两棵B+树看上去没什么不同 ，节点的结构完全⼀致只是存储的内容不同⽽已，主键索引B+
树的节点存储了主键，辅助键索引
B+
树存储了辅助键。
表数据存储在独⽴的地⽅，这两颗
B+
树的叶⼦节点都使⽤⼀个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于 索引树是独⽴的，通过辅助键检索⽆需访问主键的索引树
聚簇索引的优点
1.
当你需要取出⼀定范围内的数据时，⽤聚簇索引也⽐⽤⾮聚簇索引好。
2.
当通过聚簇索引查找⽬标数据时理论上⽐⾮聚簇索引要快，因为⾮聚簇 索引定位到对应主键时还要多⼀次⽬标记录寻址,
即多⼀次
I/O
。
3.
使⽤覆盖索引扫描的查询可以直接使⽤⻚节点中的主键值。
聚簇索引的缺点
1.
插⼊速度严重依赖于插⼊顺序 。
2.
更新主键的代价很⾼，因为将会导致被更新的⾏移动 。
3.
⼆级索引访问需要两次索引查找，第⼀次找到主键值，第⼆次根据主键值找到行数据。
8.索引有哪几种类型？
1
）普通索引
这是最基本的索引类型，基于普通字段建⽴的索引，没有任何限制。
2
）唯⼀索引
与
"
普通索引
"
类似，不同的就是：索引字段的值必须唯⼀，但允许有空
值 。
3
）主键索引
它是⼀种特殊的唯⼀索引，不允许有空值。在创建或修改表时追加主键
约束即可，每个表只能有⼀个主键。
4
）复合索引
⽤户可以在多个列上建⽴索引，这种索引叫做组复合索引（组合索
引）。复合索引可以代替多个单⼀索引，相⽐多个单⼀索引复合索引所
需的开销更小。
复合索引使⽤注意事项：
何时使⽤复合索引，要根据
where
条件建索引，注意不要过多使⽤
索引，过多使⽤会对更新操作效率有很⼤影响。
如果表已经建⽴了
(col1
，
col2)
，就没有必要再单独建⽴（
col1
）；
如果现在有
(col1)
索引，如果查询需要
col1
和
col2
条件可以建⽴
(col1,col2)
复合索引，对于查询有⼀定提⾼。
5)
全⽂索引
查询操作在数据量⽐较少时，可以使⽤
like
模糊查询，但是对于⼤量的⽂本 数据检索，效率很低。如果使⽤全⽂索引，查询速度会⽐like
快很多倍。
在
MySQL 5.6
以前的版本，只有
MyISAM
存储引擎⽀持全⽂索引，从 MySQL 5.6开始
MyISAM
和
InnoDB
存储引擎均⽀持。
全⽂索引⽅式有⾃然语⾔检索
IN NATURAL LANGUAGE MODE
和布尔检索IN BOOLEAN MODE两种
和常⽤的
like
模糊查询不同，全⽂索引有⾃⼰的语法格式，使⽤
match
和 against 关键字,例如
全⽂索引使⽤注意事项：
全⽂索引必须在字符串、⽂本字段上建⽴。
全⽂索引字段值必须在最⼩字符和最⼤字符之间的才会有效。
（innodb
：
3-84
；
myisam
：
4-84
）
9.介绍⼀下最佳左前缀法则？
1)
最佳左前缀法则
最佳左前缀法则
:
如果创建的是联合索引
,
就要遵循该法则
.
使⽤索引时， where后⾯的条件需要从索引的最左前列开始使⽤
,
并且不能跳过索引中的 列使⽤。
场景
1:
按照索引字段顺序使⽤，三个字段都使⽤了索引
,
没有问题。
场景
2:
直接跳过
user_name
使⽤索引字段，索引⽆效，未使⽤到索引。
场景3:
不按照创建联合索引的顺序
,
使⽤索引
where
后⾯查询条件顺序是
user_age
、
user_level
、
user_name
与我们创建的索引顺序 user_name
、
user_age
、
user_level
不⼀致， 为什么还是使⽤了索引，原因是因为MySql
底层优化器对其进⾏了优化。
最佳左前缀底层原理
MySQL
创建联合索引的规则是
:
⾸先会对联合索引最左边的字段进⾏排序(
例⼦中是
user_name
),
在第⼀个字段的基础之上再对第⼆个字段进行排序 (
例⼦中是
user_age
) .
最佳左前缀原则其实是和
B+
树的结构有关系
,
最左字段肯定是有序的
,
第⼆个字段则是⽆序的(
联合索引的排序⽅式是
:
先按照第⼀个字段进⾏排序,
如果第⼀个字段相等再根据第⼆个字段排序
).
所以如果直接使⽤第⼆ 个字段 user_age
通常是使⽤不到索引的
.
10.什么是索引下推？
索引下推（
index condition pushdown
）简称
ICP
，在
Mysql5.6
的版本上推出，⽤于优化查询。
需求
:
查询
users
表中
"
名字第⼀个字是张，年龄为
10
岁的所有记录
"
。
根据最左前缀法则，该语句在搜索索引树的时候，只能匹配到名字第⼀个 字是‘
张
’
的记录，接下来是怎么处理的呢？当然就是从该记录开始，逐个回 表，到主键索引上找出相应的记录，再⽐对 age
这个字段的值是否符合。
图
1:
在
(name,age)
索引⾥⾯特意去掉了
age
的值，这个过程
InnoDB
并 不会去看 age
的值，只是按顺序把
“name
第⼀个字是
’
张
’”
的记录⼀条条取 出来回表。因此，需要回表 4
次
MySQL 5.6
引⼊了索引下推优化，可以在索引遍历过程中，对索引中包含 的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
图
2: InnoDB
在
(name,age)
索引内部就判断了
age
是否等于
10
，对于不 等于 10
的记录，直接判断并跳过
,
减少回表次数
.
总结
如果没有索引下推优化（或称
ICP
优化），当进⾏索引查询时，⾸先根据索 引来查找记录，然后再根据where
条件来过滤记录；
在⽀持
ICP
优化后，
MySQL
会在取出索引的同时，判断是否可以进⾏
where条件过滤再进⾏索引查询，也就是说提前执⾏where
的部分过滤操作，在 某些场景下，可以大大减少回表次数，从⽽提升整体性能。
11.什么是自适应哈希索引？
⾃适应
Hash
索引（
Adatptive Hash Index
，内部简称
AHI
）是
InnoDB
的三 ⼤特性之⼀，还有两个是 Buffer Pool
简称
BP
、双写缓冲区（
Doublewrite Buffer）。
1
、⾃适应即我们不需要⾃⼰处理，当
InnoDB
引擎根据查询统计发现某⼀ 查询满⾜hash
索引的数据结构特点，就会给其建⽴⼀个
hash
索引；
2
、
hash
索引底层的数据结构是散列表（
Hash
表），其数据特点就是⽐较 适合在内存中使⽤，⾃适应Hash
索引存在于
InnoDB
架构中的缓存中（不存 在于磁盘架构中），⻅下⾯的InnoDB
架构图。
3
、⾃适应
hash
索引只适合搜索等值的查询，如
select * from table where index_col='xxx'，⽽对于其他查找类型，如范围查找，是不能使⽤的；
Adaptive Hash Index
是针对
B+
树
Search Path
的优化，因此所有会涉及到 Search Path的操作，均可使⽤此
Hash
索引进⾏优化
.
根据索引键值
(
前缀
)
快速定位到叶⼦节点满⾜条件记录的
Offset
，减少了 B+树
Search Path
的代价，将
B+
树从
Root
节点⾄
Leaf
节点的路径定位，优 化为Hash Index
的快速查询。
InnoDB
的⾃适应
Hash
索引是默认开启的，可以通过配置下⾯的参数设置进行关闭。
⾃适应
Hash
索引使⽤分⽚进⾏实现的，分⽚数可以使⽤配置参数设置：
12.为什么LIKE以%开头索引会失效？
like
查询为范围查询，
%
出现在左边，则索引失效。
%
出现在右边索引未失效.
场景
1:
两边都有
%
或者 字段左边有
%,
索引都会失效。
场景
2:
字段右边有
%,
索引⽣效
解决
%
出现在左边索引失效的⽅法，使⽤覆盖索引
对⽐场景
1
可以知道
,
通过使⽤覆盖索引
type = index
,
并且
extra = Using index ,
从全表扫描变成了全索引扫描
.
like
失效的原因
1.
%
号在右
:
由于
B+
树的索引顺序,是按照⾸字⺟的⼤⼩进⾏排序,
%
号在右的匹配⼜是匹配⾸字⺟。所以可以在B+
树上进⾏有序的查找,查找⾸字⺟符合要求的数据。所以有些时候可以⽤到索引.
2.
%
号在左
:
是匹配字符串尾部的数据，我们上⾯说了排序规则，尾部的 字⺟是没有顺序的，所以不能按照索引顺序查询，就⽤不到索引.
3.
两个
%%
号
:
这个是查询任意位置的字⺟满⾜条件即可，只有⾸字⺟是进⾏索引排序的，其他位置的字⺟都是相对⽆序的，所以查找任意位置的字⺟是⽤不上索引的.
13.自增还是UUID？数据库主键的类型该如何选择？
auto_increment
的优点：
1.
字段⻓度较
uuid
⼩很多，可以是
bigint
甚⾄是
int
类型，这对检索的性能 会有所影响。
2.
在写的⽅⾯，因为是⾃增的，所以主键是趋势⾃增的，也就是说新增的 数据永远在后⾯，这点对于性能有很⼤的提升。
3.
数据库⾃动编号，速度快，⽽且是增量增⻓，按顺序存放，对于检索⾮ 常有利。
4.
数字型，占⽤空间⼩，易排序，在程序中传递也⽅便。
auto_increment
的缺点：
1.
由于是⾃增，很容易通过⽹络爬⾍知晓当前系统的业务量。
2.
⾼并发的情况下，竞争⾃增锁会降低数据库的吞吐能⼒。
3.
数据迁移或分库分表场景下，⾃增⽅式不再适⽤。
UUID
的优点：
1.
不会冲突。进⾏数据拆分、合并存储的时候，能保证主键全局的唯⼀性
2.
可以在应⽤层⽣成，提⾼数据库吞吐能⼒
UUID
的缺点：
1.
影响插⼊速度， 并且造成硬盘使⽤率低。与⾃增相⽐，最⼤的缺陷就是 随机io
，下⾯我们会去具体解释
2.
字符串类型相⽐整数类型肯定更消耗空间，⽽且会⽐整数类型操作慢。
uuid
和⾃增
id
的索引结构对⽐
1
、
使⽤⾃增
id
的内部结构
⾃增的主键的值是顺序的，所以
InnoDB
把每⼀条记录都存储在⼀条记录的后⾯。
当达到⻚⾯的最⼤填充因⼦时候（
InnoDB
默认的最⼤填充因⼦是⻚⼤ ⼩的 15/16
，会留出
1/16
的空间留作以后的修改）。
下⼀条记录就会写⼊新的⻚中，⼀旦数据按照这种顺序的⽅式加载，主 键⻚就会近乎于顺序的记录填满，提升了⻚⾯的最⼤填充率，不会有页的浪费。
新插⼊的⾏⼀定会在原有的最⼤数据⾏下⼀⾏，
MySQL
定位和寻址很 快，不会为计算新⾏的位置⽽做出额外的消耗。减少了⻚分裂和碎⽚的 产⽣。
2
、
使⽤
uuid
的索引内部结构
插⼊
UUID
： 新的记录可能会插⼊之前记录的中间，因此需要移动之前的记录
被写满已经刷新到磁盘上的⻚可能会被重新读取
因为
uuid
相对顺序的⾃增
id
来说是毫⽆规律可⾔的，新⾏的值不⼀定要⽐之前的主键的值要⼤，所以 innodb
⽆法做到总是把新⾏插⼊到索引的最 后，⽽是需要为新⾏寻找新的合适的位置从⽽来分配新的空间。
这个过程需要做很多额外的操作，数据的毫⽆顺序会导致数据分布散乱，将会导致以下的问题：
1.
写⼊的⽬标⻚很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb
在插⼊之前不得不先找到并从磁盘读取⽬标 ⻚到内存中，这将导致⼤量的随机 IO
。
2.
因为写⼊是乱序的，
innodb
不得不频繁的做⻚分裂操作，以便为新的⾏分配空间，⻚分裂导致移动⼤量的数据，⼀次插⼊最少需要修改三个⻚以上。
3.
由于频繁的⻚分裂，⻚会变得稀疏并被不规则的填充，最终会导致数据会有碎⽚。
4.
在把随机值（
uuid
和雪花
id
）载⼊到聚簇索引（
InnoDB
默认的索引类型）以后，有时候会需要做⼀次 OPTIMEIZE TABLE
来重建表并优化⻚的填充，这将⼜需要⼀定的时间消耗。
结论：使⽤
InnoDB
应该尽可能的按主键的⾃增顺序插⼊，并且尽可能使⽤单调的增加的聚簇键的值来插⼊新⾏。如果是分库分表场景下，分布式主键ID的⽣成⽅案 优先选择雪花算法⽣成全局唯⼀主键（雪花算法⽣成的主键在⼀定程度上是有序的）。
14.InnoDB与MyISAM的区别？
InnoDB
和
MyISAM
是使⽤
MySQL
时最常⽤的两种引擎类型，我们重点来看下两者区别。
事务和外键
InnoDB
⽀持事务和外键，具有安全性和完整性，适合⼤量
insert
或update操作
MyISAM
不⽀持事务和外键，它提供⾼速存储和检索，适合⼤量的select查询操作
锁机制
InnoDB
⽀持⾏级锁，锁定指定记录。基于索引来加锁实现。
MyISAM
⽀持表级锁，锁定整张表。
索引结构
InnoDB
使⽤聚集索引（聚簇索引），索引和记录在⼀起存储，既缓存索引，也缓存记录。
MyISAM
使⽤⾮聚集索引（⾮聚簇索引），索引和记录分开。
并发处理能⼒
MyISAM
使⽤表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。
InnoDB
读写阻塞可以与隔离级别有关，可以采⽤多版本并发控制（MVCC
）来⽀持⾼并发
存储⽂件
InnoDB
表对应两个⽂件，⼀个
.frm
表结构⽂件，⼀个
.ibd
数据⽂件。InnoDB表最⼤⽀持
64TB
；
MyISAM
表对应三个⽂件，⼀个
.frm
表结构⽂件，⼀个
MYD
表数据问件，⼀个.MYI
索引⽂件。从
MySQL5.0
开始默认限制是
256TB
。
MyISAM 适⽤场景
1.不需要事务⽀持（不⽀持）
2.
并发相对较低（锁定机制问题）
3..数据修改相对较少，以读为主
4.数据⼀致性要求不⾼
InnoDB 适⽤场景
1.需要事务⽀持（具有较好的事务特性）
2.⾏级锁定对⾼并发有很好的适应能⼒
3.数据更新较为频繁的场景
4.数据⼀致性要求较⾼
5.硬件设备内存较⼤，可以利⽤
InnoDB
较好的缓存能⼒来提⾼内存利⽤率，减少磁盘IO
两种引擎该如何选择？
1.是否需要事务？有，
InnoDB
2.是否存在并发修改？有，
InnoDB
3.是否追求快速查询，且数据修改少？是，
MyISAM
4.在绝⼤多数情况下，推荐使⽤
InnoDB
扩展资料：各个存储引擎特性对⽐
15.B树和B+树的区别是什么？
1
）
B-Tree
介绍
B-Tree
是⼀种平衡的多路查找树
,B
树允许⼀个节点存放多个数据
.
这样可以 在尽可能减少树的深度的同时,
存放更多的数据
(
把瘦⾼的树变的矮胖
).
B-Tree
中所有节点的⼦树个数的最⼤值称为
B-Tree
的阶
,
⽤
m
表示
.
⼀颗
m
阶 的B
树
,
如果不为空
,
就必须满⾜以下条件
.
m阶的B-Tree满⾜以下条件:
1.
每个节点最多拥有
m-1
个关键字
(
根节点除外
),
也就是
m
个⼦树
2.
根节点⾄少有两个⼦树
(
可以没有⼦树
,
有就必须是两个
)
3.
分⽀节点⾄少有
(m/2)
颗⼦树
(
除去根节点和叶⼦节点其他都是分⽀节点
)
4.
所有叶⼦节点都在同⼀层
,
并且以升序排序
什么是
B-Tree
的阶
?
所有节点中，节点【
60,70,90
】拥有的⼦节点数⽬最多，四个⼦节点（灰 ⾊节点），所以上⾯的B-Tree
为
4
阶
B
树。
B-Tree
结构存储索引的特点
为了描述
B-Tree
⾸先定义⼀条记录为⼀个键值对
[key, data]
，
key
为记录的 键值，对应表中的主键值(
聚簇索引
)
，
data
为⼀⾏记录中除主键外的数据。 对于不同的记录，key
值互不相同
索引值和data
数据分布在整棵树结构中
白色块部分是指针
,
存储着⼦节点的地址信息。
每个节点可以存放多个索引值及对应的
data
数据树节点中的多个索引值从左到右升序排列
B-Tree
的查找操作
B-Tree
的每个节点的元素可以视为⼀次
I/O
读取，树的⾼度表示最多的
I/O 次数，在相同数量的总元素个数下，每个节点的元素个数越多，⾼度越低，查询所需的I/O次数越少
.
B-Tree
总结
优点
: B
树可以在内部节点存储键值和相关记录数据，因此把频繁访问的数据放在靠近根节点的位置将⼤⼤提⾼热点数据的查询效率。
缺点
: B
树中每个节点不仅包含数据的
key
值
,
还有
data
数据
.
所以当
data数据较⼤时,
会导致每个节点存储的
key
值减少
,
并且导致
B
树的层数变⾼
.增加查询时的IO次数
.
使⽤场景
: B
树主要应⽤于⽂件系统以及部分数据库索引，如 MongoDB，⼤部分关系型数据库索引则是使⽤
B+
树实现
2
）
B+Tree
B+Tree
是在
B-Tree
基础上的⼀种优化，使其更适合实现存储索引结构， InnoDB存储引擎就是⽤
B+Tree
实现其索引结构。
B+Tree
的特征
1.⾮叶⼦节点只存储键值信息
.
2.所有叶⼦节点之间都有⼀个链指针
.
3.数据记录都存放在叶⼦节点中
.
B+Tree
的优势
1. B+Tree
是
B Tree
的变种，
B Tree
能解决的问题，
B+Tree
也能够解决 （降低树的⾼度，增⼤节点存储数据量）
2. B+Tree
扫库和扫表能⼒更强，如果我们要根据索引去进⾏数据表的扫描，对BTree
进⾏扫描，需要把整棵树遍历⼀遍，⽽
B+Tree
只需要遍历他的所有叶⼦节点即可（叶⼦节点之间有引⽤）。
3. B+Tree
磁盘读写能⼒更强，他的根节点和⽀节点不保存数据区，所有根节点和⽀节点同样⼤⼩的情况下，保存的关键字要⽐B Tree
要多。⽽叶⼦节点不保存⼦节点引⽤。所以，B+Tree
读写⼀次磁盘加载的关键字⽐B Tree更多。
4. B+Tree
排序能⼒更强，如上⾯的图中可以看出，
B+Tree
天然具有排序功能。
5. B+Tree
查询效率更加稳定，每次查询数据，查询
IO
次数⼀定是稳定的。当然这个每个⼈的理解都不同，因为在B Tree
如果根节点命中直接返回，确实效率更⾼。
扫一扫
122万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
