【mysql】B+树、索引

最新推荐文章于 2025-02-16 20:43:22 发布

原创最新推荐文章于 2025-02-16 20:43:22 发布 · 646 阅读

1 ·

CC 4.0 BY-SA版权

本文为博主原创文章，欢迎转载，转载请在文章首行明确标示来源。

文章标签：

#mysql

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了MySQL中的B+树原理，包括B+树的特性，以及在B+树基础上的聚集索引和非聚集索引。同时，讨论了不同类型的索引，如B+树索引、Hash索引和全文索引的适用场景。此外，还探讨了索引的优势、优化器选择索引的策略以及索引优化技巧，如联合索引和避免在索引列中使用NULL值。

B树/B+树

（b树、b+树与二叉查找树的不同在于它们每个根节点都可以有很多叶子节点，并且存放的数据不止一条，是2条以上，即一个数据块。相同的是它们的每个节点也是经过排序的（MySQL 是按照索引字段值对应字符集的排序规则对其进行升序排序的））

B树：根叶节点都会存部分数据，叶子节点之间互相没有指针

B+树：根存储指针，叶子节点存储数据（mysiam是数据库记录所在的页码，不是真正的行数据内容。页又叫块，数据存放单位依次为：表、区、段、页、行。每页默认16KB，可设为4、8KB等，有2到多行的数据。定位到页码后，就可以在对应的数据页通过二分查找（所有记录已经按照主键做好升序排序）快速定位主键对应记录的位置。innodb存的就是数据，数据过大会溢出，保证至少2行），每个根下有多个叶子，innodb使用的B+树叶子节点之间还互相有顺序访问指针，即双向循环链表，由于每个页大小固定，B树存了data会导致其能存储的指针变少，要存储同样多的数据会增加树的高度，B+树叶子节点互相有指针，更适合做区间查找、遍历。B+树相邻的元素往往整块被读入内存，因而缓存命中率相对较高。

B+树无论是否查询到数据，都是从根到叶的查找，但是B树可能查到根。

B+树是一种平衡查找树，但并不是最优二叉树，虽然最优二叉树查找性能最高，但是维护成本太高，而B+树在增删改后保持平衡代价较低。B树、B+树不是二叉树，它们可以拥有很多叶子节点。

B+树高度一般在2-4之间,页太小、数据量特别大等会导致B+树变高。

我在阿里云藏经阁看到一本《mysql表和索引优化实战》，算了理论情况下一亿行数据用B+树只需要4层，不过它好像没考虑页大小之类的问题？网上算法普遍还是关注页大小的（参考：https://zhuanlan.zhihu.com/p/86137284），不过就算参考也是三四层足够了。

另外并不是B+树一定就优于B树，mongodb就选了B树，因为它查询单条数据的业务场景更多，根节点也能存数据的话，算起来平均随机IO比B+树少，不过它查一个范围之类就比较慢，一个一个查，没什么存储规律。

索引分类

按结构：B+树索引、hash索引、全文索引

B+树索引又分为：聚集索引、非聚集索引

按约束：主键索引、唯一索引、普通索引、联合索引、全文索引

B+树索引、hash索引、全文索引

B+树索引使用B+树实现，查询和维护都很方便，支持区间查询

hash索引使用hash结构实现，查询指定key时间复杂度为O(1),非常快，但不支持区间查询

全文索引使用倒排索引结构实现，文件内容被表示为一系列关键词的集合，保存了关键词到文件ID的映射，所以它支持多种关键词查询，一般用来匹配字符串，不支持区间查询

B+树索引又分为：聚集索引、非聚集索引

聚集索引（聚簇索引）

每张表只能按某个键构造B+树，这个索引的叶子节点存放了完整行数据（溢出除外），这个键就是其聚簇索引，它是唯一的，Innodb中，聚簇索引默认就是主键索引。

（聚集索引是逻辑上连续的，而不是物理上连续）

二级索引（非聚集索引、辅助索引）

每张innodb表除了聚集索引，其他唯一、普通、联合索引都是二级索引，他们内部也是B+树结构，叶子节点存放的都是聚集索引的值，需要通过聚集索引的值再查询行数据，所以叫“二级索引”，myisam主键存放的是指向完整行数据所在页的指针，所以它也是非聚集索引，myisam没有聚集索引。

（聚集索引过大，会连带着所有二级索引占用更多空间）

（对于所有二级索引的 B+ 树而言，由于数据记录存储的只有主键信息，所以主键长度越小，二级索引的叶子节点就越小，占用的空间也越小，从性能和存储空间方面综合考量，自增主键往往是最合理的选择（整型数据相对字符串类型占用空间小；自增字段无需对插入位置进行定位，直接放到最后一个数据页的最后面的位置即可；查询也更快捷））

还可以如下分类：

主键、唯一、联合、普通、全文索引。

与唯一索引不同，主键索引不允许空值。

全文索引

innodb支持全文索引，仅适用于 char，varchar和 text列，需要支持中文时，在创建全文索引时需要指定 with parser ngram，需要设置[mysqld] ngram_token_size=要搜索的中文字符数，此字符数在符合业务需求的情况下尽量小可以产生尽量小的搜索索引，以加快查询速度。

全文索引用倒排索引结构实现，不是B+树。它在辅助表中存储了单词和单词自身在一个或多个文档所在位置之间的映射，通常用关联数组实现。innodb有6张辅助表，是存放在磁盘上的持久表。innodb的全文索引还有全文检索索引缓存，是一个红黑树结构（红黑树是一种自平衡二叉查找树，常用来实现关联数组，关于红黑树：https://mp.weixin.qq.com/s/rYnfz2SF2qR7n_ezIxsftQ）。根据（world，ilist）排序。

全文搜索有两种模式：

in natural language mode(自然语言模式)：默认方式，根据系统ngram_token_size设置，拆分检索字符串。

in boolean mode(布尔方式)：可使用+、-、*、“”,进行字符串处理。

/*添加索引的两种方式:ALTER、CREATE*/
ALTER TABLE t ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;

CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;

/*在设置ngram_token_size=2后，这段的意思是查找名字中包含玉娇、娇王的用户，因此可以查到系统中王玉娇、刘玉娇两位用户*/
SELECT * FROM cb_user WHERE MATCH(username) AGAINST ('玉娇王');

/*查找名字中包含王的用户，注意不一定是姓王的，比如有人叫赵一王，也能搜出来*/
SELECT * FROM cb_user WHERE MATCH(username) AGAINST ('王*' IN BOOLEAN MODE);

/*搜索名字包含玉娇，或所有名字中包含王的用户*/
SELECT * FROM cb_user WHERE MATCH(username) AGAINST ("玉娇*王*" IN BOOLEAN MODE);

/*我可以继续拆分，搜索名字中包含玉的、或包含娇的、或名字中包含王的用户*/
SELECT * FROM cb_user WHERE MATCH(username) AGAINST ("玉*娇*王*" IN BOOLEAN MODE);

联合索引

联合索引是一个键值数量>=2，<=16的B+树，可以认为是排序数组，它根据第一个字段来构建。创建一个三列联合索引(col1, col2, col3)，相当于创建了 (col1)，(col1, col2)以及 (col1, col2, col3)三个索引。

联合索引优势：

1.减少磁盘开销。创建一个三列联合索引(col1, col2, col3)，相当于创建了 (col1)，(col1, col2)以及 (col1, col2, col3)三个索引。

2.加快查询速度。覆盖多列索引时，直接使用索引即可获取数据，无需回表。

3.减少排序。联合索引右边字段已经根据左侧相邻字段进行了排序处理，减少order by语句的重复排序。（where clo1 and clo2 order by clo3 可以直接获取查询结果，where clo1 order by clo3需要对clo3排序）

最左前缀匹配原则：

所使用的查询语句必须包含最左的条件，一直向右匹配直到遇到范围查询（>、<、>=、<=、between、like%、!=、or、is not null）、函数或表达式、（like “%value%”当前列都用不上索引，它就是全表扫描，大表不要用，其他范围查询当前列可以用到）之后的列就无法用到索引

*注意，or也是range查询，即：where clo1=a or clo1=b,clo1还能用上索引,clo2、clo3就用不上了。where clo1=a and (clo2=b or clo2=c)和(clo1=a and clo2=b) or (clo1=a and clo2=c)效果完全一致，都是clo1、clo2用上了，之后用不上。

in、is null在explain时也会当做范围查询，但是in 、is null不影响索引向右匹配

复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的（ref_or_null是通过首先读取参考键，然后单独搜索具有NULL键值的行来工作，is null 、is not null都被当做范围查询）

联合索引引用条件与where子句顺序无关，比如有联合索引(col1, col2, col3)，用where clo3='' and where clo2='' and where clo1=''也可以命中它

查看数据库中每个表的索引：

//数据库中所有表的索引
select table_name,index_name,group_concat(column_name) as index_column
from information_schema.statistics
where table_schema='库名'
group by table_name, index_name;
//单个表的索引，展示很详细，包括index的类型、状态（是否可见）、基数（太小的说明效率不高）、是否允许空值（最好不要有）、它会把一个联合索引拆开，告诉你是联合索引第几位，非常友好
show index from 表名

索引优势

加快查询速度

加快锁的效率

mysql的行锁是针对索引加的锁（当innodb表不指定索引时会隐式创建主键索引），不是针对记录，在需要加锁的语句中where条件如果包含索引列可以直接加锁，如果不包含，它会需要先锁整张表再过滤释放不符合条件的语句，这对并发非常不友好，所以索引可以加快锁的执行效率。

优化器不选择使用索引

在范围查找、join表等情况下，查找的数据量较多（20%以上），包含整行数据，此时读取索引加上根据索引去访问整行数据取得这部分数据的开销比磁盘上全表扫描开销大时，就会选择全表扫描。因为全表扫描会根据聚集索引进行顺序读，顺序读要远远快于离散读。

表非常小的时候，比如一个十行的字段又短的表，很可能直接就一页读完了，没必要用索引。

显式指定索引

但是，在优化器错误地选择了某个索引导致语句很慢，或索引过多优化器选择索引的开销就很大时，可以手动指定索引（前提是此索引得存在，你的条件也能用到此索引）

//use index：建议优化器使用此指定索引，但是优化器还会再自行判断要不要听你的建议
select * from 表名 use index(索引名) where '条件'

//force index：告诉优化器必须用我指定的索引
select * from 表名 force index(索引名) where '条件'

可以在set profiling = ON;后使用 show profiles; 查看语句执行速度。时间短的并不一定说明是最优语句，因为你可能只是命中了mysql查询缓存，为了测试便捷，可以看一下show variables like "%query_cache%";状态，ON的话可以设为OFF。

索引应用场景

索引并非越多越好。在访问表中很少一部分时使用B+树索引才有意义，即列的可选范围非常广，你的条件能选出其中非常小的一部分时，B+树才是高效的。

show index from 表名 可以查看每个索引的Cardinality（基数值），当Cardinality/表总行数越接近1，此索引越有效，如果非常小，此索引很可能是无用的。比如从一个表格中查询性别是男/女的用户，结果几乎在一半了，B+树就没有意义（但是此时优化器还是会选择索引）。但是一般在涉及表的join时，索引还是有意义的，还需要具体分析。