MySQL 索引

你那是什么调调

已于 2024-01-12 13:59:41 修改

阅读量333

点赞数

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

于 2023-02-28 17:29:26 首次发布

本文链接：https://blog.csdn.net/name_sakura/article/details/129262874

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1.索引种类

MySQL中索引有四种：

主键索引：不允许重复，不允许为NULL，一个表只能有一个
唯一索引：不允许重复，允许为NULL，一个表可以有多个
普通索引：允许重复，允许为NULL
组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

2.创建索引注意点

索引应该建在查询应用频繁的字段
在用于 where 判断、 order 排序和 join 的(on)字段上创建索引。
索引的个数应该适量
索引需要占用空间；更新时候也需要维护。
区分度低的字段，例如性别，不要建索引。
离散度太低的字段，扫描的行数降低的有限。
频繁更新的值，不要作为主键或者索引
维护索引文件需要成本；还会导致页分裂，IO次数增多。
组合索引把散列性高(区分度高)的值放在前面
为了满足最左前缀匹配原则
创建组合索引，而不是修改单列索引。
组合索引代替多个单列索引（对于单列索引，MySQL基本只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
过长的字段，使用前缀索引。当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
不建议用无序的值(例如身份证、UUID )作为索引
当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

3.索引失效的情况

查询条件包含or，可能导致索引失效
如果字段类型是字符串，where时一定用引号括起来，否则会因为隐式类型转换，索引失效
like通配符可能导致索引失效。
联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
在索引列上使用mysql的内置函数，索引失效。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。
索引字段上使用is null， is not null，可能导致索引失效。
左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。
MySQL优化器估计使用全表扫描要比使用索引快,则不使用索引。

补充

https://www.jianshu.com/p/3ccca0444432

4.为什么索引使用B+树存储

https://mp.weixin.qq.com/s?__biz=MzUxODAzNDg4NQ==&mid=2247502168&idx=1&sn=ff63afcea1e8835fca3fe7a97e6922b4&scene=21#wechat_redirect

（1）如果使用数组来存储索引

索引数据最好能按顺序排列，这样可以使用「二分查找法」高效定位数据。

假设用数组存储索引，比如下面有一个排序的数组，如果要找出数字 3，最简单办法是遍历查询，时间复杂度是 O(n)，查询效率不高。因为该数组是有序的，所以我们可以采用二分查找法。二分查找法每次都把查询的范围减半，时间复杂度降到了 O(log2n)，但是每次查找都需要不断计算中间位置。
在这里插入图片描述
缺点：

插入新元素的性能低
使用二分查找时，每次查找都要不断计算中间的位置

（2）如果使用二叉查找树来存储索引

二叉查找树：一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，这样我们在查询数据时，不需要计算中间节点的位置了，只需将查找的数据与节点的数据进行比较。

优点：解决了连续结构插入新元素开销很大的问题，同时又保持着天然的二分结构
在这里插入图片描述
缺点：

当每次插入的元素都是二叉查找树中最大的元素，二叉查找树就会退化成了一条链表，查找数据的时间复杂度变成了 O(n)
不能范围查询

（3）如果使用平衡二叉查找树来存储索引

在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树的高度差不能超过 1。也就是说节点的左子树和右子树仍然为平衡二叉树，这样查询操作的时间复杂度就会一直维持在 O(logn)。
在这里插入图片描述

缺点：

虽然能保持查询操作的时间复杂度在O(logn)，但本质上是二叉树，每个节点只能有 2 个子节点，节点个数越多，树的高度也会相应变高，这样就会增加磁盘的 I/O 次数，从而影响数据查询的效率

（4）如果使用B树来存储索引

为降低树的高度，后面就出来了 B 树，它不再限制一个节点只能有 2 个子节点，而是允每个节点最多有 M-1个数据和最多有M个子节点，超过这些要求的话，就会分裂节点，从而降低树的高度。M称为B树的阶，所以B树就是一个多叉树。

B树中每个节点都存放着索引和数据，数据遍布整个树结构，搜索可能在非叶子节点结束，最好的情况是O(1)，下图是一个4阶B树。
在这里插入图片描述

实例

跟据关键字 {20、30、50、52、60、69、70}，创建一棵3阶B树。非叶子节点至多有2个数据、3个子节点：
在这里插入图片描述
缺点：

B 树的每个节点都包含数据（索引+记录），而用户的记录数据的大小很可能远远超过了索引数据，这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。而且，在我们查询位于底层的某个节点（比如 A 记录）过程中，「非 A 记录节点」里的记录数据会从磁盘加载到内存，但是这些记录数据是没用的，我们只是想读取这些节点的索引数据来做比较查询，而「非 A 记录节点」里的记录数据对我们是没用的，这样不仅增多磁盘 I/O 操作次数，也占用内存资源。

（5）如果使用B+树来存储索引

B+ 树就是对 B 树做了一个升级，MySQL 中索引的数据结构就是采用了 B+ 树，B+ 树结构如下图：
在这里插入图片描述

B+树与B树的差异

叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
所有索引都会在叶子节点出现，叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表；
非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
非叶子节点中有多少个子节点，就有多少个索引；、、

B+树比B树的优势：

是 B Tree 的变种，B Tree 能解决的问题，它都能解决。
每个节点存储更多关键字。
扫库、扫表能力更强
如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+Tree 拿到所有的数据。
B+Tree 的磁盘读写能力相对于 B Tree 来说更强，IO次数更少
根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多，IO次数更少。
排序能力更强
因为叶子节点上有下一个数据区的指针，数据形成了链表。
效率更加稳定
B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的

5.Hash索引和树索引的区别

在这里插入图片描述

B+ 树可以进行范围查询，Hash 索引不能。
B+ 树支持联合索引的最左侧原则，Hash 索引不支持。
B+ 树支持 order by 排序，Hash 索引不支持。
Hash 索引在等值查询上比 B+ 树效率更高。
B+ 树使用 like 进行模糊查询的时候，like 后面（比如 % 开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

6.聚簇索引与非聚簇索引的区别

（1）聚簇索引

聚簇索引（Clustered Index）一般指的是主键索引（如果存在主键索引的话），聚簇索引也被称之为聚集索引。
聚簇索引在 InnoDB 中是使用 B+ 树实现的，比如我们创建一张 student 表，它的构建 SQL 如下：

drop table if exists student;
create table student(
    id int primary key, 
    name varchar(16),
    class_id int not null, 
    index (class_id)
)engine=InnoDB;
-- 添加测试数据
insert into student(id,name,class_id) values(1,'张三',100),
    (2,'李四',200),(3,'王五',300);

以上 student 表中有一个聚簇索引（也就是主键索引）id，和一个非聚簇索引 class_id。聚簇索引 id 对应的 B+ 树如下图所示：

在这里插入图片描述
在聚簇索引的叶子节点直接存储用户信息的内存地址，我们使用内存地址可以直接找到相应的行数据。

（2）非聚簇索引

非聚簇索引在 InnoDB 引擎中，也叫二级索引，以上面 student 表为例，在 student 中非聚簇索引 class_id 对应 B+ 树如下图所示：

从上图我们可以看出，在非聚簇索引的叶子节点上存储的并不是真正的行数据，而是主键 ID，所以当我们使用非聚簇索引进行查询时，首先会得到一个主键 ID，然后再使用主键 ID 去聚簇索引上找到真正的行数据，我们把这个过程称之为回表查询。

7.回表

在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

例如: select * from user where name = ‘张三’;
例如:select * from user where name = ‘张三’;

8.覆盖索引

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

比如，select name from user where name = ‘张三’;

在这里插入图片描述

9.最左匹配原则

在InnoDB的联合索引中，查询的时候只有匹配了左边的值之后，才能匹配下一个。

根据最左匹配原则，创建一个组合索引，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和 (a1,a2,a3) 三个索引

为什么不从最左开始查，就无法匹配呢？

比如有一个user表，我们给 name 和 age 建立了一个组合索引。

ALTER TABLE user add INDEX comidx_name_phone (name,age);

组合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的 (name 在左边，age 在右边)。
在这里插入图片描述
这张图可以看出来，name 是有序的，age 是无序的。当 name 相等的时候， age 才是有序的。

这个时候我们使用 where name= ‘张三‘ and age = ‘20 ‘去查询数据的时候， B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name 相同的时候再比较age。但是如果查询条件没有 name，就不知道下一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以就没用上索引。

10.索引的下推优化

索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。

不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL Server，MySQL Server进行过滤条件的判断。
当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL Server将这一部分判断条件下推给存储引擎，然后由存储引擎通过判断索引是否符合MySQL Server传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

例如一张表，建了一个联合索引（name, age），查询语句：

select * from t_user where name like '张%' and age=10;

由于name使用了范围查询，根据最左匹配原则：不使用ICP，引擎层查找到name like '张%'的数据，再由Server层去过滤age=10这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段age。
在这里插入图片描述
但是，使用了索引下推优化，把where的条件放到了引擎层执行，直接根据name like ‘张%’ and age=10的条件进行过滤，减少了回表的次数。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

11.explain详解

https://www.cnblogs.com/annwyn/p/14432869.html

你那是什么调调

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL 索引

假设用数组存储索引，比如下面有一个排序的数组，如果要找出数字 3，最简单办法是遍历查询，时间复杂度是 O(n)，查询效率不高。在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。二叉查找树：一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点，这样我们在查询数据时，不需要计算中间节点的位置了，只需将查找的数据与节点的数据进行比较。
复制链接

扫一扫