B+Tree
聚簇索引
特点:
- 按主键值的大小进行记录和页的排序:
- 数据页里的记录是按照主键值从小到大的一个单向链表;
- 数据页之间也是按照主键值从小到大排序的一个双向链表;
- B+树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表;
- B+树的叶子节点存储的是完整的用户记录,就是指这个记录中存储了所有列的值;
具有这两种特性的B+树称为聚簇索引,所有完整的用户记录都存放在这个聚簇索引的叶子节点处。InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中,聚簇索引就是数据的存储方式,也就是所谓的索引即数据,数据即索引;
二级索引(辅助索引)
聚簇索引只能在搜索条件是主键值时才能发挥作用,因为B+Tree中的数据都是按照主键进行排序的。当我们想以别的列作为搜索条件时,我们可以多建几棵B+树,不同的B+树采用不同的排序规则。
二级索引与聚簇索引的不同:
- 按指定的索引列的值来进行排序而不是主键;
- 叶子节点存储的不是完整的用户记录,而只是索引列+主键;
- 目录项记录中不是主键+页号,变成了索引列+页号;
- 在对二级索引进行查找数据时,需要根据主键值去聚簇索引中再查找一遍完整的用户记录,这个过程叫回表。
联合索引
以多个列的大小为排序规则建立B+树称为联合索引,本质上也是一个二级索引。
目录项记录的唯一性
我们需要保证B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对二级所用的内节点目录的内容实际上是由三个部分构成的:
- 索引列的值;
- 主键值;
- 页号;
先通过联合索引的B+树找到对应行节点,节点中包含主键,获取到主键再通过主键B+树找到主键对应的数据。
B+树索引总结
- 每个索引都对应一课B+树,用户记录都存储在B+树的叶子节点,所有目录都存储在非叶子节点;
- InnoDB存储引擎会自动为主键建立聚簇索引,聚簇索引的叶子节点包含完整的用户记录;
- 可以为指定的列建立二级索引,二级索引的叶子节点包含的用户记录由索引列+主键组成,所以如果想通过二级索引来查找完整的用户记录的话,需要回表操作,也就是通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
- B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表,而且每个页内的记录(不论 是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引
的话,则页面和记录先按照联合索引前边的列排序,如果该列值相同,再按照联合索引后边的列排序。 - 通过索引查找记录是从B+树的根节点开始,一层一层向下搜索。由于每个页面都按照索引列的值建立了 页目录,所以在这些页面中的查找非常快。
索引实战
索引的代价
空间上的代价
一个索引都会对应一个B+树,树中每一个节点都是一个数据页,一个页默认会占用16kb的存储空间,所以一个索引也是会占用磁盘空间的。
时间上的代价
索引是对数据的排序,那么当对数据表进行增删改操作时都需要去维护修改内容涉及到的B+树索引。在进行增删改时可能需要额外的时间进行一些记录移动,页面分裂,页面回收等操作来维护好排序。
索引实战
mysql> create table abc(
-> a int primary key auto_increment,
-> b varchar(5) not null,
-> c varchar(5) not null,
-> d varchar(5) not null,
-> e varchar(5) not null
-> );
建立联合索引
select * from abc where b=1 and c=1 and d=1;
全值匹配
mysql> explain select * from abc where b=1 and c=1 and d=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | abc | NULL | ALL | abcIndex | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
查询优化器会分析这些查询条件并且按照可以使用的索引列的顺序来决定使用那个查询条件。
匹配最左列
mysql> explain select * from abc where b=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | abc | NULL | ALL | abcIndex | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
mysql> explain select * from abc where b=1 and c=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | abc | NULL | ALL | abcIndex | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 4 warnings (0.00 sec)
mysql> explain select * from abc where c=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | abc | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看到如果从左边b列开始匹配才能用到联合索引,而直接使用c列匹配用不到建立的联合索引。这是因为B+树先是按照b列的值排序的,在b列只相同的情况下才使用c列进行排序。所以如果直接用c列去匹配是无序的,这就是最左列匹配。
匹配列前缀
mysql> explain select * from abc where b like '%101%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | abc | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from abc where b like '11%';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | abc | NULL | range | abcIndex | abcIndex | 22 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
因为通过%1%这个条件查询是无序的,所以只能通过全表扫描。而通过1%查询b对应的1已经排好了序,所以可以走索引查询。
匹配范围值
mysql> explain select * from abc where b>1 and b<3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | abc | NULL | ALL | abcIndex | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
由于B+树中的数据页和记录是先按b列排序的,所以查询过程是这样的:
- 找到b值为1的记录;
- 找到b值为3的记录;
- 由于所有记录都是由链表练级来的,它们之间的记录可以很容易取出来;
- 找到这些记录的主键值,再到聚簇索引中回表查找完整的记录;
注意
在使用联合进行范围查找时,只有对索引最左边的那个列进行范围查找时才能用到B+树索引。
mysql> explain select * from abc where b>1 and c>3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | abc | NULL | ALL | abcIndex | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
- 通过b>1来对b进行返回查找,找到的结果可能有多条不同记录;
- 对这些记录继续通过c>3继续过滤;
实际上只有b>1使用了B+树索引,c>3并没有使用到;
精确匹配某一列并范围匹配另一列
对于用一个联合索引,虽然对多个列都进行范围查找时只能用到最左边那个索引列,但是如果左边的列时精确查找,则右边的列可以使用索引进行范围查找;
mysql> explain select * from abc where b=2 and c>3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | abc | NULL | ALL | abcIndex | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 4 warnings (0.00 sec)
因为对于b=2的节点,c是排好序的;
排序
mysql> select * from abc order by b,c,d;
+---+---+---+---+----+
| a | b | c | d | e |
+---+---+---+---+----+
| 1 | 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 3 | 4 |
| 3 | 2 | 2 | 3 | 4 |
| 6 | 2 | 4 | 3 | 10 |
| 4 | 2 | 5 | 3 | 4 |
| 5 | 2 | 5 | 3 | 10 |
+---+---+---+---+----+
先按照b列排序,b列值相同,按c列排好序,c列值相同,按d列排好序。因为B+树本身就是按照这个规则排好序的,所以直接从索引中提取数据,然后进行回表操作取出记录就行了。
分组
mysql> select b,c,d,count(*) from abc group by b,c,d;
+---+---+---+----------+
| b | c | d | count(*) |
+---+---+---+----------+
| 1 | 1 | 1 | 1 |
| 1 | 2 | 3 | 1 |
| 2 | 2 | 3 | 1 |
| 2 | 4 | 3 | 1 |
| 2 | 5 | 3 | 2 |
+---+---+---+----------+
5 rows in set (0.00 sec)
这个查询相当于做了3次分组操作:
- 先把记录按照b值进行分组,所有b值相同的分为一组;
- 将每个b值相同的分组里的记录再按照c值进行分组,将c值相同的记录放到一个分组里;
- 再将上一步中产生的分组按照d值分成更小的组;
如果没有索引的话,这个分组过程全部需要在内存中实现,而如果有索引的话,正好这个分组顺序又和B+树中的索引咧顺序一致,所以直接从磁盘中按顺序取就好了。
对于使用联合索引进行排序或分组的注意事项
- 对于联合索引有个问题需要注意,order by的子句后边的列的顺序也必须按照索引列的顺序给出;
- 对于这种
order by b
或者order by b,c
或者select * from abc where b=1 order by c,d
也可以使用到索引。
不可以使用索引进行排序或分组的几种情况
ASC、DESC混用
如果order by子句后的列不加ASC或者DESC默认是按照ASC升序排序的。
mysql> explain select * from abc order by b asc,c desc;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | abc | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
因为对于联合索引我们要求各个排序列的排序顺序是一致的;
如何建立索引
考虑索引选择性
索引的选择性是指不重复的索引值也叫基数与表记录数的比值:
选择性=基数/记录数
取值范围(0,1],选择性越高索引的价值越大。如果选择性等于1,就代表这个列的不重复值和表记录数是一样的,如果选择性非常小,那么就代表这个列的重复值是很多的,不适合建立索引。
mysql> select count(distinct(a))/count(*) as aindex from abc;
+--------+
| aindex |
+--------+
| 1.0000 |
+--------+
1 row in set (0.00 sec)
mysql> select count(distinct(b))/count(*) as aindex from abc;
+--------+
| aindex |
+--------+
| 0.3333 |
+--------+
考虑前缀索引
用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列所用,同时因为索引key变短而减少索引文件的大小和维护开销;
总结
- 索引列的类型尽量小;
- 利用索引字符串值的前缀;
- 主键自增;
- 定位并删除表中的重复和冗余索引;
- 尽量使用主键索引进行查询,避免回表带来的性能损耗;