MySQL学习笔记：基础模型三索引

山鬼谣me

已于 2022-10-20 11:25:11 修改

阅读量460

点赞数

分类专栏： MySQL 文章标签： mysql 索引

于 2020-11-23 19:46:39 首次发布

本文链接：https://blog.csdn.net/u013066244/article/details/109996889

版权

MySQL 专栏收录该内容

22 篇文章 3 订阅

订阅专栏

环境

MacBook Pro

序言

MySQL 技术内幕 InnoDB 存储引擎第二版

顺序读要远远大于离散读

在满足语句需求的情况下，尽量少地访问资源是数据库设计的重要原则之一。

疑问

Q : B+索引能通过给定的键值找到具体行吗？
A : 不能，B+树索引能找到的只是被查找数据行所在页。
然后数据库通过把页读进内存，再在内存中查找，最后得到要查找的数据。
Q：基于上面的问题，那么在内存中是如何进行查找的呢？
A：我们知道每页Page Directory中的槽是按照主键顺序存放的，对于某一条具体记录的查询是
通过对Page Directory进行二分查找找到的。

B+树的演化而来

二叉查找树 —> 平衡二叉树 —> B树 —> B+树演化而来。

B+树： B树和索引顺序访问方法演化而来。

Q：怎么去理解B+树？
A：一棵树，首先它要满足是个二叉查找树，也就是左边肯定小于右边，
接着就是要满足平衡二叉树，也就是各子节点数差不会大于1。
接着就是满足B树，B树又名平衡多路查找树，可以看出其也是平衡树。

B树的特点：
1、排序方式：所有节点关键字是按递增的方式进行排列的；即：左小右大；
2、子节点：非叶子节点子节点数量： >=1，且 <= M (M是多路查找的路径，M=3，查找路径（子节点）有三个）；
ceil()函数是：取正向无穷大的值。
3、关键字的数量：子节点关键字的数量 >= ceil(M/2 - 1) 且小于等于 M-1;
4、叶子节点：所有叶子节点都在同一层，叶子节点除了记录关键字和关键字记录的指针，还会记录指向子节点的指针，只不过其指向的是null；

B+ 树和B树是有区别的，所以上面在满足平衡二叉树后，并不是真的去满足B树的特点，而是B+树的特点；
B+ 树是由：B树 + 索引顺序访问方法演化而来的。

B+ 树的特点：

1、和B树相比，非叶子节点只保存关键字的索引，不保存关键字记录的指针；
这使得B+每个非叶子节点可以保存更多的关键字信息。也正因如此，所有的数据地址必须到叶子节点上才能获取到。
因此，每次数据查询的次数都一样。
2、叶子节点通过指针，形成双向链表
3、非叶子节点的子节点数=关键字数

索引

模型

这里的模型，也可以理解为索引的数据结构：

模型名称	说明
哈希表	适合等值查询
有序数组	等值查询和范围查询性能都非常优秀，只适用静态存储引擎，不再修改数据的情况
搜索树

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

聚集索引

非数据页，即索引页，存放的仅仅是键值及指向数据页的偏移量（页号，因为页号从0开始的，所以偏移量也就相当于页号）

数据页：存放的是完整的每行的记录。叶子节点

举例：

insert into t select 1, repeat('a', 7000), -1;
insert into t select 2, repeat('a', 7000), -1;
insert into t select 3, repeat('a', 7000), -1;
insert into t select 4, repeat('a', 7000), -1;

那么B+树索引形式如下：

在这里插入图片描述

表定义时INT是无符号的，因此主键1 ：二进制是0x80000001

聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，
也将聚集索引的叶子节点称为数据页。
聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。
由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。

辅助索引

非主键字段构建的索引即为辅助索引；

聚集索引只能有一个，辅助索引可以有多个；

辅助索引构造的也是B+树，和聚集索引区别在于叶子节点上并不包含行记录的全部数据；
叶子节点除了包含键值外，每个叶子节点中的索引行还包含一个书签（聚集索引键：主键值）

书签的作用：告诉InnoDB存储引擎哪里可以找到索引相应的行数据；
由于InnoDB存储引擎表是索引组织表，因此辅助索引的书签也就是相应行数据的聚集索引键；

辅助索引查找数据过程

假设辅助索引树的高度为3，那么通过辅助索引遍历到叶子节点需要3次，找到指定的主键；
假设聚集索引树的高度也是3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页。
因此，共需要6次，逻辑IO获取到完整的数据；

在这里插入图片描述

联合索引

假设有这么一张表：

create table t (
	a int,
	b int,
	primary key (a),
	key idx_a_b (a, b)
)engine=innodb

插入数据：

insert into t value(2, 4);
insert into t value(1, 2);
insert into t value(2, 1);
insert into t value(1, 2);
insert into t value(3, 2);
insert into t value(3, 1);

联合索引结构：

在这里插入图片描述
本质上说，联合索引也是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2；
其和单个键值的B+树并没有什么不同，键值都是排序的；
排序的规则：
① 先按a排序，如果a相同，那么按照b排序；依此类推；

对于使用联合索引：

-- 可以使用；
select * from t where a=xxx and b=xxx;
-- 可以使用；因为a已经排好序了，所以也可以
select * from t where a=xxx;
-- 不可以；仔细观察，你会发现b其实是乱序的，1,、2、1、4、1、2
select * from t where b=xxx;

不过按照上面的排序规则，其实有个好处，就是当已知确定第一个键值的情况下，第二个键值是已经排序好了的；

也就是如果执行：

-- 这种情况下，因为第一个键值a，已经确定了，
-- 所以b也变成了有序的了；上图中符合要求的：b:1、2
select * from t where a=1 order by b desc limit 3;

覆盖索引

定义：从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

使用覆盖索引的好处：辅助索引不包含整行记录的所有信息。故其大小要远小于聚集索引；

首先，覆盖索引其实就是辅助索引；

正常使用辅助索引：利用辅助索引查找到叶子节点中主键的Id，然后再去聚集索引中把记录行找到；

覆盖索引：比如，查找：id和name，在已有的辅助索引idx_id_name中查找，发现已经满足了数据要求，并不需要利用叶子节点中的主键id去查询聚集索引；

所以，覆盖索引，其实就是利用查询字段，创建相应的联合索引；

索引的管理

这里最值得一提的是：MySQL可以创建，只索引一个列的开头部分数据；

比如有字段varchar(255) name，但是只想用到name的前100个字符来进行索引，
那么就可以：

alter table t add key idx_name (name(100));

可以写alter table t add index idx_name (name(100));
即：add key 和 add index 是等效的作用；
因为是：MySQL为了兼容其他的系统，会有同样功能的，但是字符表示不一样；
例如：INTEGER和INT都是整型

查看表的索引

show index from t

在这里插入图片描述

字段	描述
table	索引所在的表名
Non_unique	非唯一索引，可以看到primary key是0，因为必须是唯一的。
Key_name	索引的名字；可以通过名字来执行drop index
Seq_in_index	索引中该列的位置，如果看联合索引idx_a_c就比较直观了
Column_name	索引列的名称
Collation	列以什么方式存储在索引中。可以是A或者NULL。B+树索引总是A，即排序。如果是Hash索引，就会显示NULL
Sub_part	是否是列的部分被索引；假设索引了100个字符，那么就会显示100，全部显示NULL
Packed	关键字如何被压缩，没有被压缩显示NULL
Null	是否索引的列含有NULL值。如果允许NULL值，显示Yes
Index_type	索引的类型。BTREE：B+树索引
Comment	注释

什么时候创建索引

一般经验是，在访问表中很少一部分时使用B+树索引才有意义。
对于性别字段、地区字段、类型字段，它们取值范围很小，称为低选择性。
比如按性别查询，查询结果可能是该表的50%的数据，这种情况是没有必要添加B+索引的。
相反，如果某个字段的取值范围很广，几乎没有重复 – 高选择性；这种场景就非常适合使用B+索引；

那么怎么查看索引是高选择性呢？

show index from stock_check_item

cardinality

补充:

第一原则：如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
空间原则：如果既需要联合索引，又需要各自的索引，那么就考虑空间原则；比如name比age字段大，建议创建：（name，age）和age索引。

最左前缀原则

在这里插入图片描述

最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

索引下推

我们要查询：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

现在有（name, age)索引

没有索引下推的情况，每次查询到‘张’后，都需要回表过滤数据。
有了索引下推后，会对索引中包含的字段先做判断，直接过滤不满足条件的记录，减少回表次数。

在这里插入图片描述

Cardinality

定义：索引中不重复记录数量的预估值。

如上图，索引信息里cardinality这个字段非常关键，因为优化器会根据这个值来判断是否使用这个索引。
但是这个索引又不是实时更新的，因为代价太高了。

如果需要更新这个值，执行如下命令：

analyze table t

每次执行上面的命令，计算后的cardinality的值，可能都不一样。
这是因为该值是个预估值；而且基本上用户也不可能得到一个准确的值。
在实际应用中，cardinality/n_rows_in_table应尽可能接近1。
如果该值非常小，那么可以考虑该索引存在的必要性。

Cardinality如何统计的

首先，InnoDB内部更新cardinality信息的策略：
① 表中1/16的数据已发生过变化
② stat_modified_counter>2000000000。

①自从上次统计cardinality信息后，表中1/16的数据已经发生过变化。
②的情况是，如果对表中某一行数据频繁地进行更新操作，这时表的数据并没有增加。
InnoDB存储引擎内部有一个计算器stat_modified_counter,用来表示发生变化的次数；

InnoDB存储引擎内部使用的是采样的方法。默认情况下，对8个叶子节点进行采样。
过程如下：

取得B+树索引中叶子节点的数量，记为A。
随机取得B+树索引中8个叶子节点。统计每个页不同记录的个数，即为P1，P2,P3,p8
根据采样信息给出cardinality的预估值：cardinality = (P1+P2+P3.....+P8) * A/8。

正因为上面的计算方法，所以会出现每次得到的cardinality值可能是不同。
但是会有种情况：表足够小，表的叶子节点数小于或者等于8个，这种情况，随机获取的叶子都是一样的。

以下语句都会重新计算cardinality：

> analyze table t
> show table status t
> show index from t
> 访问information_schema架构下，表tables和statistics时

优化器不使用索引的情况

一般情况，范围查询、join链接操作等情况下。

先来看下如下SQL：

select * from orderdetails where orderid>10000 and orderid<102000;
-- 假设已经拥有了orderid索引和orderid、productid联合索引；

实际执行，使用explain分析发现，其并没有使用orderid索引或者联合索引，而是使用primary聚集索引，也就是全表扫描；
Q : 为什么会不走索引呢？
A : 因为用户要选取的数据是整行信息，而orderid索引不能覆盖到要查询的全部信息，所以要得依靠书签去查询聚集索引；
虽然orderid索引是有顺序，但是利用书签去查找数据则是无序的，因此变为了磁盘上的离散读。如果数据量少，
MySQL依然会使用辅助索引，但是当访问的数据量占整表蛮大一部分时（一般20%），优化器会选择聚集索引来查找数据。

小节：