网站首页技术博客

B-Tree

存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

建立在B-Tree结构（从技术上来说是B+Tree）上的索引

B-Tree索引能够加快访问速度，因为存储引擎不再需要进行全表扫描获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较子节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

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

全值匹配

全值匹配指的是和索引中的所有列进行匹配。

匹配最左前缀

只是用索引的第一列。

匹配列前缀

也可以只匹配某一列的值的开头部门。

匹配范围值

只是用索引的第一列。

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

第一列的全匹配，第二列的列前缀匹配。

只访问索引的数据（覆盖索引）

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

举例说明：

#创建索引
ALTER TABLE `jr_person`ADD INDEX `name_mobile` (`name`, 'alias',`mobile`) USING BTREE;
#全值匹配
EXPLAIN SELECT * from jr_person where alias = '维修工' and NAME = '刘国军' and mobile = '15837109838';
执行结果
+----+-------------+-----------+-------+---------------+-------------+---------+-------------+------+-------+
| id | select_type | table     | type  | possible_keys | key         | key_len | ref         | rows | Extra |
+----+-------------+-----------+-------+---------------+-------------+---------+-------------+------+-------+
|  1 | SIMPLE      | jr_person | const | name_mobile   | name_mobile | 127     | const,const |    1 |       |
+----+-------------+-----------+-------+---------------+-------------+---------+-------------+------+-------+
1 row in set (0.64 sec)

#匹配最左前缀，即只是用索引第一列
EXPLAIN SELECT * from jr_person where NAME = '刘国军';
执行结果
+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | jr_person | ref  | name_mobile   | name_mobile | 82      | const |    1 | Using where |
+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

#匹配列前缀
EXPLAIN SELECT * from jr_person where name like '刘%';
执行结果
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | jr_person | range | name_mobile   | name_mobile | 82      | NULL |  216 | Using where |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.00 sec)

#匹配范围值
EXPLAIN SELECT * from jr_person where name BETWEEN '刘' and '张';
执行结果
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | jr_person | range | name_mobile   | name_mobile | 82      | NULL |  776 | Using where |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
1 row in set (0.04 sec)

#精确匹配某一列并范围匹配另外一列
EXPLAIN SELECT * from jr_person where NAME = '刘国军' and  alias like '维%';
+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table     | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | jr_person | ref  | name_mobile   | name_mobile | 82      | const |    1 | Using where |
+----+-------------+-----------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

#只访问索引的查询
EXPLAIN SELECT name,alias,mobile from jr_person where alias = '维修工' and NAME = '刘国军' and mobile = '15837109838';
执行结果
+----+-------------+-----------+-------+---------------+-------------+---------+-------------+------+-------+
| id | select_type | table     | type  | possible_keys | key         | key_len | ref         | rows | Extra |
+----+-------------+-----------+-------+---------------+-------------+---------+-------------+------+-------+
|  1 | SIMPLE      | jr_person | const | name_mobile   | name_mobile | 127     | const,const |    1 |       |
+----+-------------+-----------+-------+---------------+-------------+---------+-------------+------+-------+
1 row in set (0.00 sec)

关于Explan各个字段的介绍参考这篇博文：Explain命令详解