mysql 查看索引深度_mysql 学习 - 索引深度理解

使用索引的代价

在熟悉了B+树索引原理之后，本篇文章的主题是唠叨如何更好的使用索引，虽然索引是个好东西，可不能乱建，在介绍如何更好的使用索引之前先要了解一下使用这玩意儿的代价，它在空间和时间上都会拖后腿： 空间上的代价:

这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成

时间上的代价:

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录(也就是不论是用户记录还是目录项记录)都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作

所以说，一个表上索引建的越多，就会占用越多的存储空间，在增删改记录的时候性能就越差。

B+树索引适用的条件

这里的内容基于我们对于 b+ 树索引的理解程度. 非常重要. 首先，B+树索引并不是万能的，并不是所有的查询语句都能用到我们建立的索引。

创建一个表:

CREATE TABLE person_info(

id INT NOT NULL auto_increment,

name VARCHAR(100) NOT NULL,

birthday DATE NOT NULL,

phone_number CHAR(11) NOT NULL,

country varchar(100) NOT NULL,

PRIMARY KEY (id),

KEY idx_name_birthday_phone_number (name, birthday, phone_number)

);

1.表中的主键是id列，它存储一个自动递增的整数。所以InnoDB存储引擎会自动为id列建立聚簇索引。

2.我们额外定义了一个二级索引idx_name_birthday_phone_number，它是由3个列组成的联合索引。所以在这个索引对应的B+树的叶子节点处存储的用户记录只保留name、birthday、phone_number这三个列的值以及主键id的值，并不会保存country列的值。

idx_name_birthday_phone_number的二级索引示意图(简略版):

从图中可以看出，这个 idx_name_birthday_phone_number 索引对应的 B+ 树中页面和记录的排序方式就是这样的：

先按照name列的值进行排序。

如果name列的值相同，则按照birthday列的值进行排序。

如果birthday列的值也相同，则按照phone_number的值进行排序。

这个排序方式是非常非常重要的, 涉及到索引能否为你的查询语句提升效率.

全值匹配

如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配，比方说下边这个查找语句：

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27' AND phone_number = '15123983239';

我们建立的 idx_name_birthday_phone_number 索引包含的 3 个列在这个查询语句中都展现出来了。大家可以想象一下这个查询过程：

因为B+树的数据页和记录先是按照name列的值进行排序的，所以先可以很快定位name列的值是Ashburn的记录位置。

在name列相同的记录里又是按照birthday列的值进行排序的，所以在name列的值是Ashburn的记录里又可以快速定位birthday列的值是'1990-09-27'的记录。

如果很不幸，name和birthday列的值都是相同的，那记录是按照phone_number列的值排序的，所以联合索引中的三个列都可能被用到。

有个疑问，WHERE子句中的几个搜索条件的顺序对查询结果有啥影响么？也就是说如果我们调换name、birth