我们必须熟悉 InnoDB 存储引擎的 B+树索引的这些结论：

  • 每个索引都对应一棵 B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在 B+树的叶子节点，所有目录项记录都存储在内节点。
  • InnoDB 存储引擎会自动为主键（如果没有它会自动添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
  • 我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
  • B+ 树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。

如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联
合索引后边的列排序。

  • 通过索引查找记录是从 B+树的根节点开始，一层一层向下搜索。

由于每个页面都按照索引列的值建立了 Page Directory（页目录），所以在页面内的查找非常快。

索引的代价

索引在空间和时间上都会拖后腿：

  • 空间上的代价：这个是显而易见的，每建立一个索引都要为它建立一棵 B+树，每一棵 B+树的每一个节点都是一个数据页，一个页默认占用 16KB 的存储空间，一棵很大的 B+树由许多数据页组成，是一片很大的存储空间。
  • 时间上的代价：每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+树索引。增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要花费额外的时间进行一些记录移位，页面分裂、页面回收之类的操作来维护好节点和记录的排序。

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

B+树索引的适用条件

该小节用以下 person_info 表演示，建表 SQL 语句如下：

  1. create table person_info(
  2.     id int not null auto_increment,
  3.     name varchar(100) not null,
  4.     birthday date not null,
  5.     phone_number char(11) not null,
  6.     country varchar(100) not null,
  7.     primary key (id),
  8.     key idx_name_birthday_phone_number (name, birthday, phone_number)
  9. );

额外定义了一个二级索引 idx_name_birthday_phone_number，它是由 3 个列组成的联合索引。
所以在这个索引对应的 B+树的叶子节点处存储的用户记录只保留 name、birthday、phone_number 这三个列的值以及主键 id 的值，并不会保存 country 列的值。
该二级索引的示意图如下：
图片.png
从图中可以看出，该二级索引对应的 B+树中页面和记录的排序方式就是这样的：

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

图省略一些不必要的部分，比如：记录的额外信息，各页面的页号等，
其中内节点中目录项记录的页号信息我们用箭头来代替，
在记录结构中只保留name、birthday、phone_number、id 这四个列的真实数据值。

全值匹配

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

  1. select * 
  2. from person_info 
  3. where name = 'Ashburn' 
  4.     and birthday = '1990-09-27' 
  5.     and phone_number = '15123983239';

该二极索引包含的 3 个列在这个查询语句中都展现出来了。
可以想象一下这个查询过程：

  • 因为 B+树的数据页和记录是先按照 name 列的值进行排序的，所以可以先很快定位 name 列的值是Ashburn 的记录位置。
  • 在 name 列相同的记录里又是按照 birthday 列的值进行排序的，所以在 name 列的值是 Ashburn 的记录里又可以快速定位 birthday 列的值是 ‘1990-09-27’ 的记录。
  • 如果 name 和 birthday 列的值都是相同的，那记录是按照 phone_number 列的值排序的，所以联合索引中的三个列都可能被用到。

如果我们调换 name、birthday、phone_number 这几个搜索列的顺序对查询的执行过程没有影响。
因为 MySQL 有查询优化器，它会分析这些搜索条件并且按照可以使用的索引中列的顺序来决定先使用哪个搜索条件，后使用哪个搜索条件。

匹配左边连续的列

搜索语句中也可以不用包含全部联合索引中的列，只包含左边连续的列就行，比如下边的查询语句：

  1. select * 
  2. from person_info 
  3. where name = 'Ashburn' 
  4.     and birthday = '1990-09-27';

用不到索引的情况
搜索条件中必须出现左边连续的列才可以使用到这个 B+树索引。
比如下边的查询语句就用不到这个 B+树索引：

  1. select * 
  2. from person_info 
  3. where birthday = '1990-09-27';

因为 B+树的数据页和记录是先按照 name 列的值排序的，
在 name 列的值相同的情况下才使用 birthday 列的值进行排序，
也就是说 name 列的值不同的记录中 birthday 的值可能是无序的。

如果想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。
比如：该二级索引中列的定义顺序是 name、birthday、phone_number，
如果我们的搜索条件中只有 name 和 phone_number，而没有中间的 birthday，
这样只能用到 name 列的索引，birthday 和 phone_number 的索引就用不上了，
因为 name 值相同的记录先按照 birthday 的值进行排序，birthday 值相同的记录才按照 phone_number 值进行排序。

匹配列前缀

为某个列建立索引的意思就是在对应的 B+树的记录中使用该列的值进行排序，
比如： 该二级索引会先用 name 列的值进行排序，所以这个联合索引对应的 B+树中的记录的 name 列的排列就是这样的：

  1. Aaron
  2. Aaron
  3. ...
  4. Aaron
  5. Asa
  6. Ashburn
  7. ...
  8. Ashburn
  9. Baird
  10. Barlow
  11. ...
  12. Barlow

字符串排序的本质就是比较哪个字符串大，哪个字符串小，
比较字符串大小就用到了该列的字符集和比较规则。
一般的比较规则是逐个比较字符的大小。
所以一个排好序的字符串列有这样的特点：

  • 先按照字符串的第一个字符进行排序。
  • 如果第一个字符相同再按照第二个字符进行排序。
  • 如果第二个字符相同再按照第三个字符进行排序，依此类推。

也就是说这些字符串的前缀都是排好序的，
所以：对于字符串类型的索引列来说，只匹配它的前缀也是可以快速定位记录的。

比如：查询名字以 ‘As’ 开头的记录，那就可以这么写查询语句：

  1. select * 
  2. from person_info 
  3. where name like 'As%';

但是如果只给出后缀或者中间的某个字符串，比如这样：

  1. select * 
  2. from person_info 
  3. where name like '%As%';

MySQL 就无法快速定位记录位置了，因为字符串中间有 ‘As’ 的字符串并没有排好序，所以只能全表扫描。

匹配范围值

所有记录都是按照索引列的值从小到大的顺序排好序的，
所以这极大的方便查找索引列的值在某个范围内的记录。
比如下边这个查询语句：

  1. select * 
  2. from person_info 
  3. where name > 'Asa' and name < 'Barlow';

由于 B+树中的数据页和记录是先按 name 列排序的，所以上边的查询过程如下：

  • 找到 name 值为 Asa 的记录。
  • 找到 name 值为 Barlow 的记录。
  • 由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），

所以他们之间的记录可以很容易的取出来

  • 找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。

在使用联合索引进行范围查找时需要注意：如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找时才能用到 B+树索引。
比如这样：

  1. select * 
  2. from person_info 
  3. where name > 'Asa' and name < 'Barlow' and birthday > '1980-01-01';

上边这个查询可以分成两个部分：

  1. 通过条件 name > ‘Asa’ and name < ‘Barlow’ 来对 name 进行范围查找，查找的结果可能有多条 name 值不同的记录，
  2. 对这些 name 值不同的记录继续通过 birthday > ‘1980-01-01’ 条件继续过滤。

这样子对于该二级索引来说，只能用到 name 列的部分，用不到 birthday 列的部分。

精确匹配某几列并范围匹配下一列

如果左边的列是精确查找，则接下来的一列可以进行范围查找，比如这样：

  1. select * 
  2. from person_info 
  3. where name = 'Ashburn' 
  4.     and birthday > '1980-01-01' 
  5.   and birthday < '2000-12-31' 
  6.   and phone_number > '15100000000';

这个查询的条件可以分为 3 个部分：

  1. name = ‘Ashburn’，对 name 列进行精确查找，使用 B+树索引了。
  2. birthday > ‘1980-01-01’ and birthday < ‘2000-12-31’，由于 name 列是精确查找，通过 name = ‘Ashburn’ 条件查找后得到的结果的 name 值是相同的，所以 birthday 列的值是有序的。

所以此时对 birthday 列进行范围查找是可以用到 B+树索引。

  1. phone_number > ‘15100000000’，通过 birthday 的范围查找的记录的 birthday 的值可能不同，所以这个条件无法再利用 B+树索引，只能遍历上一步查询得到的记录。

同理，下边的查询也是可能用到该联合索引的：

  1. select * 
  2. from person_info 
  3. where name = 'Ashburn' 
  4.     and birthday = '1980-01-01' 
  5.   and phone_number > '15100000000';

用于排序

在写查询语句时经常要对查询出来的记录通过 order by 子句按照某种规则进行排序。
一般情况下，我们只能把记录都加载到内存中，再用一些排序算法在内存中对这些记录进行排序，
有时可能查询的结果集太大，以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序完成后再把排好序的结果集返回到客户端。
在 MySQL 中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序 (filesort)，文件排序速度比较慢。

但是如果 order by 子句里使用到了索引列，就有可能省去在内存或文件中排序的步骤，
比如下边这个简单的查询语句：

  1. select * from person_info ORDER BY name, birthday, phone_number LIMIT 10;

这个查询的结果集需要先按照 name 值排序，如果记录的 name 值相同，则按照 birthday 来排序，如果 birthday 的值相同，则按照 phone_number 排序。
因为这个 B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了。

使用联合索引进行排序的注意事项
对于联合索引 order by 子句后边的列的顺序也必须按照索引列的顺序给出，否则用不了 B+树索引。
同理，order by name、order by name, birthday 这种匹配索引左边的列的形式可以使用部分 B+树索引。当 order by 子句后面的列中左边列的值为常量，也可以使用后边的列进行排序，比如这样：

  1. select * from person_info where name = 'A' order by birthday, phone_number limit 10;

不使用索引进行排序的几种情况
asc、desc 混用
对于使用联合索引进行排序的场景，要求各个排序列的排序顺序是一致的，也就是要么各个列都是 asc 规则排序，要么都是 desc 规则排序。
排序列包含非该索引的列
有时候用来排序的多个列中出现了非该索引的列，这种情况也不能使用索引进行排序，
比如下面这样：

  1. select * from person_infoorder by name, country limit 10;

country 并不属于该联合索引中的列，所以无法使用索引进行排序。
排序列使用了复杂的表达式
要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式，
比如这样：

  1. select * from person_info order by UPPER(name) limit 10;

使用了 upper 函数修饰过的列就不是单独的列，这样就无法使用索引进行排序。

用于分组

有时为了方便统计表中的信息，会把表中的记录按照某些列进行分组。
比如下边这个分组查询：

  1. select name, birthday, phone_number, COUNT(*) 
  2. from person_info 
  3. group by name, birthday, phone_number;

这个查询语句相当于做了 3 次分组操作：

  1. 先把记录按照 name 值进行分组，所有 name 值相同的记录划分为一组。
  2. 将每个 name 值相同的分组里的记录再按照 birthday 的值进行分组，将 birthday 值相同的记录放到一个小分组里，所以看起来就像在一个大分组里又化分了好多小分组。
  3. 再将上一步中产生的小分组按照 phone_number 的值分成更小的分组，所以整体上看起来就像是先把记录分成一个大分组，然后把大分组分成若干个小分组，然后把若干个小分组再细分成更多的分组。

和使用 B+树索引进行排序是一个道理，分组列的顺序也需要和索引列的顺序一致，也可以只使用索引列中左边的列进行分组。

回表的代价

需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询使用全表扫描也不使用二级索引。
什么时候采用全表扫描的方式，什么使用采用二级索引 + 回表的方式去执行查询呢？
这个就是查询优化器需要做的工作，查询优化器会事先对表中的记录计算一些统计数据，
然后再利用这些统计数据根据查询条件计算一下需要回表的记录数，需要回表的记录数越多，
就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。
当然优化器做的分析工作不仅仅是这么简单，但是大致上是这个过程。
一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询。
覆盖索引
最好在查询列表里只包含索引列，比如这样：

  1. select name, birthday, phone_number 
  2. from person_info 
  3.     where name > 'Asa' 
  4.   and name < 'Barlow'

因为只查询这三个索引列的值，所以在通过该二级索引得到结果后就不必到聚簇索引中再查找记录的剩余列，也就是country列的值了，这样就省去了回表操作带来的性能损耗。
我们把这种只需要用到索引的查询方式称为索引覆盖。

如何挑选索引

只为用于搜索、排序或分组的列创建索引

也就是说，只为出现在 where 子句中的列、连接子句中的连接列，
或者出现在 order by 或 group by 子句中的列创建索引，
而出现在查询列表中的列就没必要建立索引了。
比如：

  1. select birthday, country from person_name where name = 'Ashburn';

像查询列表中的 birthday、country 这两个列就不需要建立索引，只需要为出现在 where 子句中的 name 列创建索引就可以了。

考虑列的基数

列的基数指的是：某一列中不重复数据的个数。
比如：某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8，虽然有 9 条记录，但该列的基数却是 3。
也就是说，在记录行数一定的情况下，
列的基数越大，该列中的值越分散，
列的基数越小，该列中的值越集中。
这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。

假设某个列的基数为 1，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，
因为所有值都一样就无法排序，无法进行快速查找了，
而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。
所以结论就是：最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

索引列的类型尽量小

在定义表结构时，要显式的指定列的类型，
以整数类型为例，有 tinyint、mediumint、int、bigint 这么几种，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。
我们这里所说的类型大小指的就是：该类型表示的数据范围的大小。
如果想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型。
比如：能使用 int 就不要使用 bigint，这是因为：

  • 数据类型越小，在查询时进行的比较操作越快（这是 CPU 层次的东西）
  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/O。

索引字符串值的前缀

一个字符串由若干个字符组成，在 MySQL 中如果使用 utf8 字符集去存储字符串的话，编码一个字符需要占用 1~3 个字节。
假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。
在我们需要为这个字符串列建立索引时，那就意味着在对应的 B+树中有这么两个问题：

  • B+树索引中的记录需要把该列的完整字符串存储起来，而且字符串越长，在索引中占用的存储空间越大。
  • 如果 B+树索引中索引列存储的字符串很长，在做字符串比较时会花费更多的时间。

我们前面说过索引列的字符串前缀是排好序的，所以索引的设计者提出了一个方案：可以只对字符串的前几个字符进行索引，也就是说在二级索引的记录中只保留字符串前几个字符。
这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。
这样只在 B+树中存储字符串的前几个字符的编码，既节约空间，又减少了字符串的比较时间，还大概能解决排序的问题。
比如在建表语句中只对 name 列的前10个字符进行索引可以这么写：

  1. create table person_info(
  2.     name varchar(100) not null,
  3.     birthday date not null,
  4.     phone_number char(11) not null,
  5.     country varchar(100) not null,
  6.     key idx_name_birthday_phone_number (name(10), birthday, phone_number)
  7. );

name(10) 就表示在建立的 B+树索引中只保留记录的前 10 个字符的编码，
这种只索引字符串值前缀的策略是被鼓励使用的，尤其是在字符串类型能存储的字符比较多的时候。

索引列前缀对排序的影响
如果使用了索引列前缀，比如：上边只把 name 列的前 10 个字符放到了二级索引中，
下边这个查询可能就有点儿尴尬了：

  1. select * from person_info order by name limit 10;

因为二级索引不包含完整的 name 信息，所以无法对前十个字符相同，后边的字符不同的记录进行排序，
也就是使用索引列前缀的方式无法支持使用索引进行排序，这样只能用文件排序了。

让索引列在比较表达式中单独出现

假设表中有一个整数列 my_col，为这个列建立索引。
下边的两个 where 子句虽然语义是一致的，但是在效率上却有差别：

  1. where my_col * 2 < 4
  2. where my_col < 4/2

第1个 where 子句中 my_col 列并不是以单独列的形式出现的，
而是以 my_col 2 这样的表达式的形式出现的，
存储引擎会依次遍历所有的记录，计算这个表达式的值是不是 < 4，所以这种情况下是使用不到为 my_col 列建立的 B+树索引的。
第2个 where 子句中 my_col 列是以单独列的形式出现的，这样的情况可以直接使用 B+树索引。
所以结论就是：*如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式 或者 函数调用形式出现的话，是用不到索引的。

主键插入顺序

对于使用 InnoDB 存储引擎的表来说，表中的数据实际上都是存储在聚簇索引的叶子节点的。
记录存储在数据页中，数据页和记录又是按照记录主键值从小到大的顺序进行排序的，
所以如果插入的记录的主键值是依次增大的话，那每插满一个数据页就换到下一个数据页继续插入，

而如果插入的主键值忽大忽小的话，这就比较麻烦了，
假设某个数据页存储的记录已经满了，它存储的主键值在1 ~100 之间，
如果此时再插入一条主键值为 9 的记录，此时需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的页中。
页面分裂和记录移位意味着：性能损耗。
所以最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。
所以建议：让主键列具有 auto_increment 属性，而不是我们手动插入。
主键列拥有 auto_increment 属性，在插入记录时存储引擎会自动为我们填入自增的主键值。

小贴士 一个数据库表只能有一个自增列，并且该列必须被定义为一个键

图片.png

冗余和重复索引

有时会有意或无意的就对同一个列创建了多个索引，比方说这样写建表语句：

  1. create table person_info(
  2.     id int not null auto_increment,
  3.     name varchar(100) not null,
  4.     birthday date not null,
  5.     phone_number char(11) not null,
  6.     country varchar(100) not null,
  7.     primary key (id),
  8.     key idx_name_birthday_phone_number (name(10), birthday, phone_number),
  9.     key idx_name (name(10))
  10. );

我们知道通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，
再创建一个专门针对 name 列的索引就是一个冗余索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。

另一种情况，我们可能会对某个列重复建立索引，比方说这样：

  1. create table repeat_index_demo (
  2.     c1 int primary key,
  3.     c2 int,
  4.     unique uidx_c1 (c1),
  5.     index idx_c1 (c1)
  6. );

我们看到，c1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，
可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。

总结

  1. B+树索引在空间和时间上都有代价，所以没事别瞎建索引。
  2. B+树索引适用于下边这些情况：
    • 全值匹配
    • 匹配左边连续的列
    • 匹配范围值
    • 精确匹配某几列并范围匹配下一列
    • 用于排序
    • 用于分组
  3. 在使用索引时需要注意下边这些事项：
    • 只为用于搜索、排序或分组的列创建索引
    • 为列的基数大的列创建索引
    • 索引列的类型尽量小
    • 可以只对字符串值的前缀建立索引
    • 只有索引列在比较表达式中单独出现才可以用索引
    • 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有 auto_increment 属性
    • 定位并删除表中的重复和冗余索引
    • 尽量用覆盖索引进行查询，避免回表带来的性能损耗