索引的使用

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)
);

可知,这个表会创建两个索引,一个聚簇索引和联合索引,聚簇索引如下图所示:

全值匹配

如果搜索条件中的列和索引列一致的话,这种情况被称为全值匹配,比如说如下查找语句:

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

那么查询过程如下:

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

改变搜索条件的位置,对搜索顺序没影响,Mysql有个茶村优化器的东西,会分析搜索条件,并按照可以使用的索引的顺序来决定先使用哪个搜索条件.

匹配左边的列

其实搜索也不用包含全部的列,只包含左边就行,比如下面的查询语句:

SELECT * FROM person_info WHERE name = 'Ashburn';

但是只有右边的搜索条件,就用不到索引,比如下面的搜索语句:

SELECT * FROM person_info WHERE birthday = '1990-09-27';

因为name值不同的记录中,birthday可能是无序的,无法使用二分查找,所以如果我们想使用联合索引中尽可能多的列,搜索条件中的各个列必须是联合索引中从最左边连续的列.

匹配列前缀

在页中需要对列进行排序,就需要用到列的排序规则,那么一个字符串的排序规则如下:

1. 先按照字符串的第一个字符进行排序
2. 如果第一个字符相同再按照第二个字符进行排序,以此类推

也就是说这些字符串的前n个字符,前缀都是排好序的,所以对于字符串类型的索引列来说,我们只匹配前缀也是可以快速定位记录的,比如:

SELECT * FROM person_info WHERE name LIKE 'As%';

匹配范围值

比如下面的查询语句:

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

查找过程如下:

1. 找到name值为Asa的记录
2. 找到name值为Barlow的记录
3. 找到这些记录的主键值,再到聚簇索引中回表查找完整的记录

如果对多个列同时进行范围查找的话,只有对最左边的那个列进行范围查找的时候才能用到B+树索引,比如:

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' AND birthday > '1980-01-0
1';

这个查询中通过name进行范围查找的记录可能并不是按照birthday列进行排序的,所以只能用到name列部分.

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

如果左边的列是精确查找,则右边的列可以进行范围查找,比如这样:

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday
< '2000-12-31' AND phone_number > '15100000000';

因为name值相同,那么birthday是排序好的,所以birthday还可以用到索引,但是phone_number就不能

用于排序

我们说说不能使用索引排序的情况,比如说这样的:

SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;

• 先从索引的最左边确定name列最小的值,然后找到name列等于该值得所有记录,然后从name列等于该值得最右边那条记录开始往左找10条记录
• 如果name列等于最小值得记录不足10条,再继续往右找name值第二小的记录,重复上边的过程,直到找到10条记录为止.

mysql觉得这样不如直接文件排序来的快,所以就直接规定使用联合索引的排序列必须是一致的.

要想使用索引进行排序,必须保证索引列是以单独列的形式出现,而不是修饰过的形式,比如:

SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

回表的代价

因为我们创建的联合索引,没有包含所有的字段,所以在查询所有字段的时候需要一次回表操作,由于联合索引B+树中的记录会按照name列的值进行排序,所以值在Asa~Barlow之间的记录在磁盘中的存储是相连的,集中分布在一个或几个数据页中,可以很快的从磁盘中读出来,这种读取方式可以称为顺序IO,根据从联合索引获取到的主键字段,而这些主键可能不是连续的,所以在聚簇索引中可能需要访问不同的数据页,这种方式可以称为随机IO.需要回表的记录越多,使用二级索引的性能越低.
实际上查询优化器会帮助我们判断什么时候采用全表扫描,什么时候使用二级索引+回表方式,如果需要回表的记录越少,优化器就会倾向采用二级索引,比如像这样的查询:

SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' LIMIT 10;

如何挑选索引

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

也就是说,只为出现在where字句中的列,连接字句中的连接列,或者出现在order by或者group by 字句中列创建索引

考虑列的基数

列的基数 指的是某一列中不重复数据的个数，比方说某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8 ，虽然有 9 条记录，但该列的基数却是 3 。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。这个 列的基数 指标非常重要，直接影响我们是否能有效的利用索引。假设某个列的基数为 1 ，也就是所有记录在该列中的值都一样，那为该列建立索引是没有用的，因为所有值都一样就无法排序，无法进行快速查找了～ 而且如果某个建立了二级索引的列的重复值特别多，那么使用这个二级索引查出的记录还可能要做回表操作，这样性能损耗就更大了。所以结论就是：最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

索引列的类型尽量小

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

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

索引字符串的前缀

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

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

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

CREATE TABLE person_info(
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

name(10) 就表示在建立的 B+ 树索引中只保留记录的前 10 个字符的编码，这种只索引字符串值的前缀的策略是我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。如果使用了索引列前缀，比方说前边只把 name 列的前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 ，让存储引擎自己为表生成主键，而不是我们手动插入.

冗余和重复索引

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

CREATE TABLE person_info(
id INT UNSIGNED 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(10), birthday, phone_number),
KEY idx_name (name(10))
);

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对name 列的索引就算是一个 冗余 索引，维护这个索引只会增加维护的成本，并不会对搜索有什么好处。
另一种情况，我们可能会对某个列重复建立索引，比方说这样：

CREATE TABLE repeat_index_demo (
c1 INT PRIMARY KEY,
c2 INT,
UNIQUE uidx_c1 (c1),
INDEX idx_c1 (c1)
);

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