数据库索引-B-Tree索引

最新推荐文章于 2024-09-19 08:44:28 发布

夜空霓虹

最新推荐文章于 2024-09-19 08:44:28 发布

阅读量1.6k

点赞数

分类专栏：面试文章标签：数据库 mysql 索引优化

本文链接：https://blog.csdn.net/zhang_xiaomeng/article/details/54962560

版权

面试专栏收录该内容

35 篇文章 1 订阅

订阅专栏

1.索引简介

索引（键（key））是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。索引优化应该是对查询性能优化最有效的手段了，索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最优”的索引经常需要重写查询。

索引类似书的目录。在MySql中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。例子：

mysql>SELECT first_name FROM sakila.actor WHERE actor_id=5;

如果在actor_id列上建有索引，则MySQL将使用该索引找到actor_id为5的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

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

2.索引的类型

MySQL中，索引是在存储引擎层而不是服务器层实现的。不同存储引擎的索引的工作方式不一样，也不是所有存储引擎都支持所有类型的索引。即使多个存储引擎支持同一个类型的索引，其底层实现也可能不同。

下面介绍MySQL支持的索引类型，以及它们的优点和缺点。

B-Tree索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。大多数MySQL引擎支持这种索引。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

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列的值，下图展示该索引时如何组织数据的存储的。

可以使用B-Tree索引的查询类型，B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

全值匹配

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

匹配最左前缀

前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列。

匹配列前缀

也可以只匹配某一列的值的开头部分。例如前面提高的索引可用于查找所有以J开头的姓的人。这里也只使用了索引的第一列。

匹配范围值

例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人，这里也只使用了索引的第一列。

精确匹配某一列并范围匹配另外一列

前面提到的索引也可用于查找所有姓为Allen，并且名字是字母K开头（比如Kim、Karl等）的人。即第一列last_name全匹配，第二列first_name范围匹配。

只访问索引的查询

B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

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

下面是一些关于B-Tree索引的限制：

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

b.不能跳过索引的列。也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名（first_name）,则MySQL只能使用索引的第一列。

c.如果列中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如有查询WHERE last_name='Smith' AND first_name LIKE '%J’ AND dob ='1976-12-23'，这个查询只能使用索引的前两列，因为这里LIKE是是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。