MySQL-索引

智博的自留地

已于 2022-10-24 15:42:34 修改

阅读量212

点赞数

分类专栏： MySQL 文章标签：数据库

于 2022-10-24 15:41:43 首次发布

本文链接：https://blog.csdn.net/XZB119211/article/details/127473039

版权

MySQL 专栏收录该内容

9 篇文章 1 订阅

订阅专栏

MySQL-索引

问题：索引是什么？？？

答案：官方的定义，索引是帮助mysql高效获取数据的一种排好序的数据结构。

1. 索引的分类

主键索引：

主键是一种唯一性索引，它必须指定为primarykey,一个表只能有一个主键，但是主键可以包含多个列！
当我们指定一个主键时，mysql会帮我们自动为这个主键建立一个索引。

唯一索引：

索引列的值必须唯一，但允许有空值，但只允许有一个空值。

create unique index 索引名称 on 表名(字段名);

普通索引：

基本的索引类型，值可以为空，并且没有唯一性限制。

create index 索引名称 on 表名(字段名);

全文索引：

全文索引的类型为FULLTEXT，全文索引可以在char,varchar和text类型的列上创建。

组合索引：

即一个索引包含多个列，专门用于组合搜索

create index 索引名称 on 表名(字段名1,字段名2,字段名3);

**组合索引匹配原则：**最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。但是mysql会自动调整查询中字段顺序，举例说明：索引字段为（name，age，dir）查询字段有以下几种：（name，dir，age）【可以】、（name，age，dir）【可以】、（age，dir）【不可】、(dir，name，age）【可以】，mysql自动调整查询字段顺序后依旧要符合最左匹配原则。

2、索引底层数据结构

场景：定义主键索引时候插入数据，无序插入10 条数据后查询左右数据发现有序显示。
思考：主键索引进行排序？为什么进行排序？【排序之后查询更快】
问题：主键索引底层是怎么实现的？

2.1 B+树实现的主键索引

1、首先我们来想存储的数据结构一般有什么？
答：数组、链表，假设数据库中的数据也用数组或者链表来存储，那么数组的查询时间复杂度是O(1)，根据数组下标即可找到，链表的时间复杂度是O(n)，因为链表需要从头开始遍历比较才可找到，数组和链表的存储方式显然不适用在数据量大的情况下来存储数据，会导致很大的查询时间的代价。
2、那mysql中用什么来作为存储数据的结构呢？
答：B+树。
下面我们来详细讲述一下MySQL中是如何实现通过B+树来存储数据的
要介绍 B+ 树索引，就不得不提二叉查找树，平衡二叉树和 B 树这三种数据结构。B+ 树就是从他们仨演化来的。

2.1.1 二叉查找树

首先二叉查找树就是普通的二叉树，根据数据的插入顺序来建立二叉查找树，二叉查找树的构建特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。
在这里插入图片描述
图中可以看出，通过对user表建立一个二叉查找树的索引。存储了key值和data值，如果我们要查找key=12这个节点，需要通过3次匹配可找到数据，如果单纯的通过数组或者链表查找的话需要通过6次对比才可找到。

2.1.2 平衡二叉树

上面讲述的是二叉查找树，我们根据二叉查找树构建如下数据时会构建出如下图的二叉查找树。
在这里插入图片描述
这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找 id=9 的用户信息，我们需要查找 5 次，也就相当于全表扫描了。
导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。
为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。
平衡二叉树又称 AVL 树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1。

下面是平衡二叉树和非平衡二叉树的对比：
在这里插入图片描述
由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。

平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。具体的调整方式这里就不介绍了。

平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

2.1.3 B树

因为内存的易失性。一般情况下，我们都会选择将 user 表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘 IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的 B 树。

B 树（Balance Tree）即为平衡树的意思，下图即是一棵 B 树：
在这里插入图片描述
图中的每个节点称为页，页就是我们上面说的磁盘块，在 MySQL 中数据读取的基本单位都是页，所以我们这里叫做页更符合 MySQL 中索引的底层数据结构。

从上图可以看出，B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的 B 树为 3 阶 B 树，高度也会很低。
假如我们要查找 id=28 的用户信息，那么我们在上图 B 树中查找的流程如下：

先找到根节点也就是页 1，判断 28 在键值 17 和 35 之间，那么我们根据页 1 中的指针 p2 找到页 3。
将 28 和页 3 中的键值相比较，28 在 26 和 30 之间，我们根据页 3 中的指针 p2 找到页 8。
将 28 和页 8 中的键值相比较，发现有匹配的键值 28，键值 28 对应的用户信息为（28，bv）。

2.1.4 B+树

B+树是B树的进一步优化，如下图所示是一颗B+ 树：
在这里插入图片描述
我们来分析一下B+ 树和B 树的区别：
1、B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。

为什么？ 因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。
如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。

如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。

一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。B+树的一般高度在2-4层。

2、 B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。子节点之间通过链表相连。
为什么？ 这样的结构使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而 B 树因为数据分散在各个节点，要实现这一点是很不容易的。

通过上图可以看到，在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

【注意：MyISAM 中的 B+ 树索引实现与 InnoDB 中的略有不同。在 MyISAM 中，B+ 树索引的叶子节点并不存储数据，而是存储数据的文件地址。】

在MySql中聚簇索引也就是主键索引的实现就是通过B+树来实现的

3、MySql中的主键索引
在这里插入图片描述
4、非主键索引（有主键的情况下，给name加了一个普通索引，需要进行回表操作）

根据子节点存储的索引主键进行回表操作，去到主键索引存储的数据里查找数据，如下图：

5、非主键索引（没有主键的情况下，给name加了一个索引，此时mysql底层依然使用B+Tree并且根据name来进行排序和存储数据，无需进行回表）
在这里插入图片描述
6、B+树特点

非叶子点不存储data，只存储冗余索引，可以存放更多的索引，可以添加更多的分支，而不需要增加树的高度
叶子节点包含所有索引字段
叶子节点用指针连接，在进行范围查找时，能提高区间访问的性能
7、哈希索引（常用）

特点：
对索引的key进行一次Hash运算就可以定位出数据存储的位置
很多时候Hash索引要比B+Tree索引效率要高
仅能满足=和in查询，不支持范围查询
hash冲突（哈希冲突就是两个不同值的东西，通过哈希函数计算出来的哈希值相同，这样他们存在数组中的时候就会发生冲突，这就是哈希冲突）解决方法： 开放地址法、拉链法、再哈希法、建立公共溢出区

除了上面的几种索引之外，这些索引根据存储方式上的不同，分为两类：聚簇索引和非聚簇索引

2.1.5 聚簇索引和非聚簇索引

聚簇索引： 将数据存储与索引放到一块，索引结构的叶子节点保存了行数据，InnoDB存储引擎使用的是局促索引
在这里插入图片描述
非局促索引： 将数据与索引分开存储，索引结构的叶子节点指向数据对应的具体位置，myisam存储引擎使用的是非聚簇索引

两者的区别：
聚簇索引，索引和数据是保存在同一个文件的，非聚簇索引的索引和数据是分开来保存的，所以在查询上，聚簇索引的效率比非聚簇索引高，聚簇索引查找到索引时，因为索引文件和数据文件绑定在一起的，所以找到了索引也代表着查找到数据了，而非聚簇索引需要进行回表操作。

说明：在InnoDB中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子结点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是要二次查找。

使用聚簇索引的优势：
问题：聚簇索引中每次使用辅助索引检索都要经过两次B+数查找，看上去聚簇索引的效果明显要低于非聚簇索引，那么聚簇索引的优势在哪？
1、由于行数据和聚簇索引的叶子节点存储在一起，同一页中会包含多条数据，访问同一数据页不同行记录时，已经把页加载到内存中，再次访问时，会在内存张完成访问，不需要访问磁盘，这样主键和行数据是一起被载入内存的，找到叶子节点的同时立即就可以返回行数据了。如果按主键ID来组织数据，获得数据效率会更高。
2、辅助索引的叶子结点，存储主键值，而不是数据的存放地址，好处是当行数据发生变化（增删改查）时，索引树的节点也需要分裂变化，或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生新一次的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

使用聚簇索引的注意事项：
当时用主键为聚簇索引时，主键最好不用uuid，因为uuid的值太过离散，不便于排序且可能新增的记录的uuid插入到索引树中间的位置，导致索引树调整起来比较复杂，消耗更多的时间和资源。

为什么innodb的表要建立主键，并且推荐使用自增的整形主键？
1.建立主键的原因
1.1）如果我们给表加了主键，那么mysql底层就会默认给我们的主键加上索引，就会通过主键索引的方式（底层使用B+Tree）来存储数据
1.2）如果我们没给表加主键，mysql会去看这个表有没有唯一索引，如果有唯一索引，就会按照唯一索引的方式（底层使用B+Tree，只是排序和主键排序不一样，排序会按照索引列来排序）来存储数据
1.3）如果一个表我们没有给它设置主键，其他字段也没有加唯一索引，那么这个时候，mysql会帮我们添加多一列rowid（整型自增）来充当主键，没必要让mysql干这么多活，有损性能，尽量在建表的时候都设置一个主键！

2.推荐使用自增整形主键的原因
2.1）方便索引的比较和排序，设想我们在使用uuid作为主键时，比较一个字符串，和比较一个整型数值，谁的效率高?
2.2）使用整型的自增主键对数的结构变动不大，设想我们使用uuid作为主键时，这时候索引主键值有可能在树的左半部分，也可能在树的右半部分，如果在树的左半部分，插入树中时，其他节点也要做出很大的变动，而使用整形自增的主键时，可以使得索引主键值稳定在树的右半部分存储，说到底，树结构变动越大，性能越低！

2.2 组合索引的底层数据实现

2.2.1 有主键的情况，组合索引的实现

1.给user表的name,age和gender加一个组合索引

create index nag_index on user(name,age,gender)

2.组合索引底层结构（有主建的情况下）图
在这里插入图片描述
3.需要进行回表操作

2.2.2 无主键的情况，组合索引的实现

1.给user表的name,age和gender加一个唯一的组合索引

create unique index unag_index on user(name,age,gender)

2.组合索引底层结构（没有有主建,组合索引唯一的情况下）图
在这里插入图片描述

3. 索引失效场景

3.1 模糊匹配LIKE以%开头，会导致索引失效。

explain select *from customer where company like '%abc'\G

如上模糊查询语句，模糊匹配在左边，会导致索引失效。把模糊匹配放到最右边索引有效，因为索引匹配原则是最左匹配原则。

explain select * from customer where company like 'abc%'\G

3.2 索引列进行计算，会导致索引失效。

explain select *from customer where age-1=20\G

如上索引where条件中age进行了计算又进行判断会导致索引失效，应该把计算放在最右边即可。

explain select *from customer where age=20+1\G

3.3 索引列使用函数，会导致索引失效。

explain select *from customer where lcase(company)='abc'\G

3.4 索引列类型转换，会导致索引失效。

explain select *from customer where phone=13012345678\G

phone应该是字符型，不应该保持原有类型，避免类型转换。

explain select *from customer where phone='13012345678'\G

3.5 索引列比较字符集不一致时，会导致索引失效。

3.6 使用or查询，不论另一项是否为索引列，都会导致索引失效。

3.7 组合索引，没有使用第一列索引，会导致索引失效。

4. InnoDB引擎和Myisam引擎的区别

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
InnoDB是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
InnoDB支持表锁和行锁，MyISAM仅支持表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB不支持全文索引，MyISAM支持全文索引（5.6后）
InnoDB适合大量的indert,delete和update操作，MyISAM适合大量的select操作