1. 什么是索引
MySQL索引原理及慢查询优化
索引是排好序的快速查找数据结构，索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql
- 有了这样的字典我们就可以避免全表扫描从而提高查找效率
- 既然排好序，所以索引会影响到
查找和order by
优势
- 提高了数据检索的效率，降低了数据库的IO成本
- 降低了数据排序的成本，降低了CPU的消耗
劣势
- 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引也要占用空间
- 少量数据，频繁增删改的数据以及很少使用的数据都不适合使用索引
- 虽然索引大大提高了查询速度，同时确会降低更新表的速度，因为每次更新要去改变索引信息
- 如果MySQL表过大就需要花费大量的时间研究建立最优秀的索引
总体来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作的时候，索引才是有效的；
- 对于非常小的表，大部分情况下使用简单的全表扫描效率更高，因为不用维护索引
- 对于中大型的表，索引就非常有效
- 对于特大型表，建立和使用索引的代价将随之增长，这种情况下则需要一种技术能直接区分出查询需要的一组数据，而不是一条一条记录的匹配，例如使用分区技术
2. 常见的索引模型
2.1 哈希索引
在MySQL中，只有Memory引擎显式支持哈希索引，这也是Memory引擎表的默认索引类型
Hash表适用于只有等值查询的场景
- 由于hash算法肯定会导致hash冲突，而这里使用拉链法解决hash冲突
- hash表不是有序的，所以在做去检查询等非等值查询的时候要把整个表遍历一遍，效率低
注意⚠️
- hash索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免对行的查找
- hash索引不按索引值顺序存储，所以无法用于排序，在做区间查询等非等值查询的时候要把整个表遍历一遍，效率低
- hash索引不支持部分索引列匹配查找，因为hash索引始终使用索引列的全部内容来计算哈希值的
自适应hash索引
InnoDB引擎有一个特殊的功能叫做 自适应哈希索引(adaptivehash index)。
当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。 这是一个完全自动的、 内部的行为，用户无法控制或者配置，不过如果有必要， 完全可以关闭该功能。
创建自定义hash索引
如果存储引擎不支持哈希索引，则可以模拟创建hash索引而享受hash索引的便利，例如只需要很小的索引就可以为超长的键创建索引
例如需要存储大量的URL，并且根据URL进行搜索查找，如果对于不支持hash索引或者自适应hash的存储引擎来说，使用B树来存储URL，存储的内容就会很大，因为URL本身都很长
select id from u where url="hrttp://www.mysql.com"
若删除原来url列上的索引，新增一个被索引的url_crc列，利用CRC32做hash，就可以使用下面的方式查询
select id from u where url_crc=CRC32("hrttp://www.mysql.com" ) AND url="hrttp://www.mysql.com"
这样性能会很高，因为现在使用的索引树url_crc体积更小
2.2 有序数组
有序数组在等值查询和范围查询场景中的性能就都非常优秀
- 相较于hash来说，在有序数组中查找使用二分法就能快速得到
- 这个索引结构支持范围查询
更新数据成本高，只适合用于静态存储引擎
- 如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高
- 比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据
2.3 二叉搜索树
在二叉树搜索的时间复杂度是O(log(n))，当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N))
二叉树的搜索效率最高，但实际上大多数数据库存储都不使用二叉树
- 索引是存储在磁盘上的，当数据量较大的时候我们无法一次把索引都加载到内存，只能逐一加载索引树的每个节点，每个节点也不是顺序存放的，所以实际上是逐一加载每个磁盘页；
- 在最坏情况下，磁盘的io次数等于树的高度
- 频繁的io是阻碍提升性能的瓶颈
如何减少io次数
时间局部性原理：假如你查询id为1的用户数据，过一段时间你还会查询id为1的数据，所以会将这部分数据缓存下来空间局部性原理：当你查询id为1的用户数据的时候，你有很大的概率会去查询id为2，3，4的用户的数据，所以会一次性的把id为1，2，3，4的数据都读到内存中去，这个最小的单位就是页
2.3 B树
漫画算法 什么是B树
之所以不使用搜索树是因为他的高度太高导致io操作频繁，所以解决方法就是降低树的高度，把瘦高的树变得矮胖
B树又称为平衡多路查找树，即不止两个子树的查找树，他的每个节点最多包含k个孩子，k被称为B树的阶，k的大小取决于磁盘页的大小
B树中所有节点都带有卫星数据，卫星数据指的是索引元素所指向的数据记录
需要补充的是，在数据库的聚集索引（Clustered Index）中，叶子节点直接包含卫星数据。在非聚集索引（NonClustered Index）中，叶子节点带有指向卫星数据的指针
一个m阶的B树有如下几个特点
- 根结点至少有两个子女。
- 每个中间节点都包含
k-1个元素和k个孩子，其中m/2 <= k <= m - 每一个叶子节点都包含
k-1个元素，其中m/2 <= k <= m - 所有的叶子结点都位于同一层。
- 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划
这样一次加载到内存的数据就多了，B树主要应用于文件系统和部分数据库索引，比如非关系型数据库MongoDB
2.4 B+树
漫画：什么是B+树？
一个m阶的B+树具有如下几个特征：
- 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
- 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
- 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
B+树只有叶子才带卫星数据，其他都只是索引
B+树的优势：
- 由于B+树的中间节点不带卫星数据，所以单一节点能存储更多的元素，使得查询的IO次数更少
- 所有查询都要查找到叶子节点，查询性能稳定
- 所有叶子节点形成有序链表，便于范围查询
3. 聚簇索引 vs 非聚簇索引
聚簇索引和非聚簇索引(通俗易懂 言简意赅) - 创天创世纪 - 博客园
3.1 聚簇索引
数据和索引存储到一起，找到索引就获取到了数据，Innobd中的主键索引是一种聚簇索引
聚簇索引是唯一的，因为无法同时把数据行存放在两个不同的地方；InnoDB一定会有一个聚簇索引来保存数据。非聚簇索引一定存储有聚簇索引的列值
InnoDB聚簇索引选择顺序：
- 默认选择主键来聚簇数据
- 没有主键，选择唯一的非空索引；
- 都没有，则隐式定义一个主键；
优点：
- 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
- 聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
- 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
- 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据
对于第三点，在innidb中使用自适应hash索引解决：
3.2 非聚簇索引
数据存储和索引分开，叶子节点存储对应的行，需要二次查找，通常称为[二级索引]或[辅助索引]
- 比如对于Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键
- 对于myism是在叶子结点记录数据地址
4. InnoDB 的索引模型 vs MyISM的索引模型
数据库文件存储是以页为存储单元的，一个页是8K（8192Byte），一个页就可以存放N行数据。我们常用的页类型就是数据页和索引页。一个页中除了存放基本数据之外还需要存放一些其他的数据，如页的信息、偏移量等
4.1 MyISM的索引模型
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址
4.2 InnoDB索引实现
InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同
- InnoDB的数据文件本身就是索引文件，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录，而其他节点不会保存完整的数据记录，仅仅保存索引（和指向下一层的指针）
根据叶子节点的内容，索引类型分为主键索引和非主键索引
- 主键索引，索引的key是数据表的主键，叶子节点存的是整行数据
- 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）
- 对于二级索引data域存储相应记录主键的值而不是地址，正是以为如此才不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大
基于主键索引和普通索引的查询有什么区别？ - 如果语句是
select * from T where ID=49，即主键查询方式，则只需要搜索 ID 这棵 B+ 树 - 如果语句是
select * from T where name=Alice，即普通索引查询方式，则需要先搜索 name 索引树，得到 ID 的值为 49，再到 ID 索引树搜索一次。这个过程称为回表
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询
- 用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效
- 如果正在使用的InnoDB表中没有什么数据需要聚集，那么可以定义一个代理键作为主键，这种主键的数据应该和应用无关
- 使用AUTO_INCREMENT自增列做主键在高并发的场景下可能造成明显的争用
- 使用UUID主键插入行不仅花费时间更长，而且索引占用的空间也更大，这一方面是因为主键字段更长，另一方面是由于页分裂和碎片造成的
5. 索引维护
假设，我们有一个主键列为 ID 的表，使用innoDB引擎，表中有字段 k，并且在 k 上有索引。
这个表的建表语句是：
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;
表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下
B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护
页分裂
- 如果插入新的行 ID 值为 700，则只需要在 R5 的记录后面插入一个新记录
- 如果新插入的 ID 值为 400，就相对麻烦了，需要逻辑上挪动后面的数据，空出位置。
- 而更糟的情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。
- 除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
页合并
- 有分裂就有合并，当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并
- 合并的过程，可以认为是分裂过程的逆过程
尽量使用自增主键
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT
插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值
- 自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂
- 由于每个非主键索引的叶子节点上都是主键的值，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
- 从性能和存储空间方面考量，自增主键往往是更合理的选择
6. 覆盖索引
覆盖索引
在使用二级索引的时候，回到主键索引树搜索的过程，我们称为回表，如果在这个查询里面，索引已经“覆盖了”我们的查询需求，我们称为覆盖索引，由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段
7. 普通索引 vs 唯一索引
普通索引和唯一索引，应该怎么选择？
如果在业务层已经保证了插入的数据不会重复，那么应该选择普通索引还是唯一索引？
① 对查询操作的影响
- 对于唯一性索引找到一条结果就不用再继续遍历索引树
- 对于普通索引还需要继续遍历
- 但是InnoDB的数据是按照数据页为单位来读写的，也就是说，当读到一条数据的时候，并不是将这个记录从磁盘中读出来，而是以页尾单位，将其整体读入内存，在InnoDB中，每个数据页的大小默认为16k；因为引擎是按页读取的，所以当找到k=5的操作的时候，他所在的数据页已经在内存中了；那么对于普通索引来说，要多做的那一次
查找和判断下一条记录的操作，就只需要在内存中的一次指针寻找和计算 - 当然，如果这个记录刚好是当前数据页的最后一条数据，就需要再曲磁盘查找下一个数据页
② 对于插入操作
- 因为changeBuffer的存在，普通索引的插入操作只需要更新change buffer中的值，等系统空闲再落盘
- 而对于唯一性索引必须在插入数据的前加载磁盘的所有页，判断这条数据存不存在
扫一扫
2076
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
