mysql(3)

最新推荐文章于 2024-05-19 03:18:57 发布

qq_46146522

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量407

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 b+树

本文链接：https://blog.csdn.net/qq_46146522/article/details/126817125

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

mysqlB+树索引

索引分类

聚簇索引

聚簇索引有以下特点，InnoDb 会自动创建聚簇索引。

使用记录主键值的大小进行记录和页的排序

页内的记录是按照主键的大小顺序排成一个单向链表。
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

B+ 树的叶子节点存储的是完整的用户记录(包括隐藏列)。

二级索引（辅助索引）

二级索引需要手动创建

使用非主键记录列c2的大小进行记录和页的排序
B+ 树的叶子节点存储的并不是完整的用户记录，而只是 c2列+主键这两个列的值
二级索引的目录项中有c2列，主键，页号，当在插入相同c2数据时，可再比较主键值以确定具体插入位置

！！！所以当我们用二级索引查找到相关记录时，还得通过（聚簇索引）主键再去找到确切的用户记录，这个二次查询的过程我们称之为回表

联合索引（本质上也是二级索引）

当我们同时用到多个列的排序规则时，就得用到我们的联合索引，

这里假设有 c2 列和c3 列

每条目录项记录都由 c2 、 c3 、页号这三个部分组成，各条记录先按照 c2 列的值进行排序，如果记录

的 c2 列相同，则按照 c3 列的值进行排序。

B+ 树叶子节点处的用户记录由 c2 、 c3 和主键 c1 列组成。

索引建立

InnoDB 和 MyISAM 会自动为主键或者声明为 UNIQUE 的列去自动建立 B+ 树索引,其他的列就需要我们显示创建索引了。

每建立一个索引都会建立一棵 B+ 树

#创建表时建立
CREATE TALBE 表名 (
 各种列的信息 ··· , 
 [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

#修改表时建立
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

#删除索引
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

索引名建议以 idx_为前缀,

索引使用

B+树中即有索引目录项，又有真实数据记录项，

B+ 树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。

查询时，从根节点出发，先通过索引目录查询到数据页，在通过数据页中的数据页目录折半查找到具体的数据组，每组数据最多8行数据，依次遍历即可找到最终的那一条数据了；所以InnoDB在查询数据时，得经过两层目录；

最左匹配原则

联合索引中，只能取从左向右的前缀用作索引。

这里我们建立一个(name, birthday, phone_number)三个字段的联合索引

这里能用到的索引就只是
     1.  name
     2. （name，birthday）
     3. (name, birthday, phone_number)
SELECT * FROM person_info WHERE name = ‘Ashburn’ AND birthday = ‘1990-09-27’ AND phone_num

ber = ‘15123983239’;

三个列都用到了，索引当然生效

SELECT * FROM person_info WHERE name = ‘Ashburn’;

SELECT * FROM person_info WHERE birthday = ‘1990-09-27’;

2 语句中用不到联合索引，违背了向左匹配原则，跳过了name 列；

匹配列前缀

字符串的比较是依次比较单个字符，当我们用like匹配时

SELECT * FROM person_info WHERE name LIKE ‘As%’;

SELECT * FROM person_info WHERE name LIKE ‘%As%’;

1 语句中AS开头的字符串，可以利用到字符串的排序规则，但2 语句中 AS在中间的不可使用字符串的排序规则，只能一一遍历，此时索引就会失效；

匹配范围值

在使用联合进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引最左边的那个

列进行范围查找的时候才能用到 B+ 树索引，比方说这样：

SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’ AND birthday > '1980-01-0

1’;

上边这个查询可以分成两个部分：

通过条件 name > ‘Asa’ AND name < ‘Barlow’ 来对 name 进行范围，查找的结果可能有多条 name 值不同的记录，

对这些 name 值不同的记录继续通过 birthday > ‘1980-01-01’ 条件继续过滤。

所以只在第一个通过name 匹配时，用到索引；birthday 的排序规则只在name相同的时候才用得到；

那么也就是说，只要name相同就可以用到birthday 范围查询索引，例如：

SELECT * FROM person_info WHERE name = ‘Ashburn’ AND birthday > ‘1980-01-01’ AND birthday

< ‘2000-12-31’ AND phone_number > ‘15100000000’;

这里范围查询用了birthday 做索引，后面的phone_number 就不可用了；

在order by 排序的字段加索引，也可加快排序速度， B+树用户记录本来就是按照顺序排列的，

在排序中，也有不可使用索引的情况：

上述的范围查询，匹配最左字段做为索引，其他字段不可用
ASC（自然排序），DESC 混用
WHERE子句中出现非排序使用到的索引列
SELECT * FROM person_info WHERE country = ‘China’ ORDER BY name LIMIT 10; country不是索引，先通过country 一一遍历找到结果，此时已经到了叶子节点，若使用name 索引，得重新搜索，在与刚才的结果合并，效率太差了；
排序列包含非同一个索引的列，比如： ORDER BY name, country ；
排序列使用了复杂的表达式，比如 ORDER BY UPPER(name)；

我们的group by 同样也可以使用索引；

例如：

SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, ph

one_number

先把记录按照 name 值进行分组，所有 name 值相同的记录划分为一组。

将每个 name 值相同的分组里的记录再按照 birthday 的值进行分组，将 birthday 值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。

再将上一步中产生的小分组按照 phone_number 的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，然后把若干个小分组再细分成更多的小小分组。

然后针对那些小小分组进行统计，比如在我们这个查询语句中就是统计每个小小分组包含的记录条数。如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有了索引的话，恰巧这个分组顺序又和我们的 B+ 树中的索引列的顺序是一致的，而我们的 B+ 树索引又是按照索引列排好序的，这不正好么，所以可以直接使用 B+ 树索引进行分组。

索引覆盖

当查询字段里包含非索引字段时，例如我们常用的（select *）此时就需要回表操作，回表操作是需要消耗性能的；

在 select 时，建议查询我们的索引字段，此时就不需要回表了，这也就是我们的索引覆盖；

如何挑选索引

只为用于搜索、排序或分组的列创建索引

考虑列的基数

基数指数据去重后的个数，若基数小，则重复数据多，不利于排序；所以建议给基数大的列加索引

索引列的类型尽量小

索引字符串值的前缀 ，例如：KEY idx_name (name(10)) ，name前10个字符

用了前缀做索引后，便不可使用name做索引排序了；

让索引列在比较表达式中单独出现 ，例如WHERE my_col * 2 < 4 用不到索引，

如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出

现的话，是用不到索引的。

主键自增带来的好处

主键在插入时，就可以从小到大依次插入，就不会存在中间插入的情况，当我们数据页满数据时，再中间插入一个数据，就得页分裂，消耗性能；

避免冗余和重复索引