索引基础与索引的几种类型之 B-Tree

博主： keke996
发布时间：2021 年 03 月 15 日
1556次浏览
暂无评论
2261字数
分类：码农的故事

索引可以包含一个或多个列的值.如果索引包含多个列,那么列的顺序也十分重要,因为MySQL只能高效地使用索引的最左前缀列.创建一个包含两个列的索引,和创建两个只包含一列的索引是大不相同的.

索引的类型

B-Tree索引
当说起索引的时候,如果没有特别指明类型,那多半说的是B-Tree索引,它使用B-Tree数据结构来储存数据.大多数M有SQL引擎都支持这种索引.Archive引擎是一个例外5.1之前Archive不支持任何索引,直到5.1才开始支持单个自增列(AUTO_INCREMENT)的索引.
储存引擎以不同的方式使用B-Tree索引,性能各有不同,各有优势.例如,MyISAM使用前缀压缩技术使得索引更小,但InnoDB则按照原数据格式进行存储.再入MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行.
B-Tree通常以为着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同.
B-Tree索引能够加快访问数据的速度,因为储存引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始搜索.根节点的槽中存放了指向子节点的指针,储存引擎根据这些指针向下层查找.通过比较节点页的值和要查询的值可以找到合适的指针进入下层子节点,这些指针实际上定义了子节点页中的值的上限和下限.最终储存引擎要么是找到对应的值,要么该值不存在.

加入设有如下数据表

CREATE TABLE People(
        last_name varchar(50)    not null,
        first_name varchar(50)    not null,
        dob date    not null,
        gender enum('m','f')    not null,
        key(last_name,first_name,dob)
    );

对于表中的每一行数据,索引中包含了last_name(姓氏),first_name(名)和dob列的值.

请注意,索引对多个值进行排序的一句是CREATE TABLE语句中定义索引时序的顺序.

可以使用B-Tree索引的查询类型
B-Tree索引适合于全键值/键值范围或键前缀查找.其中键前缀查找只适用于最左前缀的查找.前面所述的索引对如下类型的查询有效.

- 全值匹配
   全值匹配指的是和索引中所有列进行匹配.例如前面提到的所以可用于查找姓名为Cuba Allen 出生于1960-01-01的人

- 匹配最左前缀
   索引可用于查找所有姓Allen的人,即只使用索引的第一列.

- 匹配列前缀
   也可以只匹配某一列的值的开头部分.例如索引可用于查找所有以A开头的姓的人这里也只用了索引的第一列

- 匹配范围值
   例如前面提到的所以可以用于查找姓在Allen和Barrymore之间的人.这里也只使用了索引的第一列.

- 精确匹配某一列并范围匹配另一列
   前面提到的索引也可以用于查找所有姓Allen,并且名字是字母K开头的人.即第一列last_name全匹配,第二列first_name范围匹配.

- 只访问索引的查询
   B-Tree通常可以支持"只访问索引的查询",即查询只需要访问索引,而无需访问数据行.

因为索引树种的节点是有序的,所以除了按值查找之外,索引还可以用于查询中的 ORDER BY操作(按顺序查找).一般来说,如果B-Tree可以按照某种方式找到值,那么也可以按照这种方式用于排序.所以,如果ORDER BY子句满足前面列出的集中查询类型,则这个索引也可以满足对应的排序需求.

下面是一些关于B-Tree索引的限值:

- 如果不是按照索引的最左列开始查找,则无法使用索引.例如上面的例子中的索引无法用于查找名字为Bill的人,也无法查找某个铁定生日的人,因为这两列都不是最左数据列.类似地,也无法查找姓氏以某个字母结尾的人.

- 不能跳过索引中的列.也就是说,前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人.如果不指定名(first_name),则MySQL只能使用索引的第一列.
 - 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找.例如有查询
       WHERE last_name ='Smith' AND first_name LIKE 'J%' AND dob = '1976-12-23'
   这个查询只能使用索引的前两列 last_name 和 first_name,因为这里的LIKE是一个范围条件(但是服务器可以吧其余列用于其他目的).如果范围查询列值的数量有限,那么可以通过使用多个等于条件来代替范围条件.

所以可以明白,前面提到的索引列的顺序是多么的重要:这些限制都和索引列的顺序有关.在优化性能的时候,可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求.

也有些限制并不是B-Tree本身导致,而是MySQL优化器和存储引擎使用索引的方式导致的,这部分限制在未来的版本中可能就不再是限制了.

最后修改：2021 年 03 月 15 日 09 : 18 AM

随便看看就好,打赏个一毛两毛也不错

发表评论取消回复

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

w4j1e
我想当个公交车司机，或者奶茶店兑奶茶粉的老板＞﹏＜
陶小桃Blog
境外的直接禁止访问，可以减少很多无畏的损失，这个地址是美国内华...
Lsnow
太惨了
W4J1e
今天也被刷了，从头天中午12点刷到第二天上午十点才收到又拍云的...
fish
牛，扫网段扫到了你的博客￣﹃￣

索引基础与索引的几种类型之 B-Tree

keke996 • 2021 年 03 月 15 日

索引的类型

加入设有如下数据表

对于表中的每一行数据,索引中包含了last_name(姓氏),first_name(名)和dob列的值.

请注意,索引对多个值进行排序的一句是CREATE TABLE语句中定义索引时序的顺序.

- 全值匹配
   全值匹配指的是和索引中所有列进行匹配.例如前面提到的所以可用于查找姓名为Cuba Allen 出生于1960-01-01的人

- 匹配最左前缀
   索引可用于查找所有姓Allen的人,即只使用索引的第一列.

- 匹配列前缀
   也可以只匹配某一列的值的开头部分.例如索引可用于查找所有以A开头的姓的人这里也只用了索引的第一列

- 匹配范围值
   例如前面提到的所以可以用于查找姓在Allen和Barrymore之间的人.这里也只使用了索引的第一列.

- 只访问索引的查询
   B-Tree通常可以支持"只访问索引的查询",即查询只需要访问索引,而无需访问数据行.

下面是一些关于B-Tree索引的限值:

也有些限制并不是B-Tree本身导致,而是MySQL优化器和存储引擎使用索引的方式导致的,这部分限制在未来的版本中可能就不再是限制了.

索引基础与索引的几种类型之 B-Tree

发表评论取消回复

tp5.1中数据操作where条件的分组以及FIND_IN_SET的数组表达式

一个人吃饭真累

弄堂口的炸串

我手贱啊，改数据库没加where啊，全没了啊

教练，我想学画画

php 判断一个数是否为质数

tp5.1 在验证器中使用多语言返回验证结果

只有深夜属于自己

索引的优点

索引基础与索引的几种类型之 B-Tree

索引基础与索引的几种类型之 B-Tree

发表评论 取消回复

索引基础与索引的几种类型 之 B-Tree

发表评论取消回复

索引基础与索引的几种类型之 B-Tree