索引的使用

索引使用

索引的代价

• 空间上的代价

  这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的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) -- 二级索引
);

person_info表会为聚簇索引和idx_name_birthday_phone_number索引建立2棵B+树

这个idx_name_birthday_phone_number索引对应的B+树中页面和记录的排序方式就是这样的：

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

-- 通过idx_name_birthday_phone_number索引查询，条件顺序无所谓，有优化器处理的
SELECT * FROM person_info WHERE birthday = '1990-09-27' AND phone_number = '15123983239' AND name = 'Ashburn';
-- 通过索引
SELECT * FROM person_info WHERE name = 'Ashburn';
-- 无法通过索引，因为birthday = '1990-09-27'的值是分散的
SELECT * FROM person_info WHERE birthday = '1990-09-27';
-- 只用到name索引，因为phone_number = '15123983239'的值也是分散的
SELECT * FROM person_info WHERE name = 'Ashburn' AND phone_number = '15123983239';

匹配列前缀

-- 可以通过索引
SELECT * FROM person_info WHERE name LIKE 'As%';
-- 无法使用索引
SELECT * FROM person_info WHERE name LIKE '%As%';
-- 想要使用LIKE ’xxx%‘，可以建表时将数据逆向，这样等价与查询’%xxx‘可以使用到索引

排序

索引排序方向需要一致

-- 没有用到索引排序，可以想一想name从左到右查询，然后name相同birthdauy则从右到左查询；查询完相同name又要向右到另一个name，birthday有向左.....
SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;
-- 可以用到
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;
-- 
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday DESC, phone_number DESC LIMIT 10;

回表的代价

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

在使用idx_name_birthday_phone_number索引进行查询时大致可以分为这两个步骤：

1. 从索引idx_name_birthday_phone_number对应的B+树中取出name值在Asa～Barlow之间的用户记录。
2. 由于索引idx_name_birthday_phone_number对应的B+树用户记录中只包含name、birthday、phone_number、id这4个字段，而查询列表是*，意味着要查询表中所有字段，也就是还要包括country字段。这时需要把从上一步中获取到的每一条记录的id字段都到聚簇索引对应的B+树中找到完整的用户记录，也就是我们通常所说的回表，然后把完整的用户记录返回给查询用户。

由于索引idx_name_birthday_phone_number对应的B+树中的记录首先会按照name列的值进行排序，所以值在Asa～Barlow之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我们也可以称为顺序I/O。根据第1步中获取到的记录的id字段的值可能并不相连，而在聚簇索引中记录是根据id（也就是主键）的顺序排列的，所以根据这些并不连续的id值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以称为随机I/O。一般情况下，顺序I/O比随机I/O的性能高很多，所以步骤1的执行可能很快，而步骤2就慢一些。所以这个使用索引idx_name_birthday_phone_number的查询有这么两个特点：

• 会使用到两个B+树索引，一个二级索引，一个聚簇索引。
• 访问二级索引使用顺序I/O，访问聚簇索引使用随机I/O。

需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。比方说name值在Asa～Barlow之间的用户记录数量占全部记录数量90%以上，那么如果使用idx_name_birthday_phone_number索引的话，有90%多的id值需要回表，这不是吃力不讨好么，还不如直接去扫描聚簇索引（也就是全表扫描）。

需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。比方说name值在Asa～Barlow之间的用户记录数量占全部记录数量90%以上，那么如果使用idx_name_birthday_phone_number索引的话，有90%多的id值需要回表，这不是吃力不讨好么，还不如直接去扫描聚簇索引（也就是全表扫描）。

那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执行查询呢？这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。一般情况下，限制查询获取较少的记录数会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询，因为回表的记录越少，性能提升就越高，比方说上边的查询可以改写成这样：

sql
复制代码SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow' LIMIT 10;

添加了LIMIT 10的查询更容易让优化器采用二级索引 + 回表的方式进行查询。

对于有排序需求的查询，上边讨论的采用全表扫描还是二级索引 + 回表的方式进行查询的条件也是成立的，比方说下边这个查询：

SELECT * FROM person_info ORDER BY name, birthday, phone_number;

由于查询列表是*，所以如果使用二级索引进行排序的话，需要把排序完的二级索引记录全部进行回表操作，这样操作的成本还不如直接遍历聚簇索引然后再进行文件排序（filesort）低，所以优化器会倾向于使用全表扫描的方式执行查询。如果我们加了LIMIT子句，比如这样：

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

这样需要回表的记录特别少，优化器就会倾向于使用二级索引 + 回表的方式执行查询。

为了减少回表，建议select字段只包含索引列

挑选索引

1. select后的字段不需要索引，where后的字段要
2. 索引列的重复值要尽量少
3. 索引列的数据类型要小，便于比较和存储：(字符列可以使用前缀)

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

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

1. WHERE my_col * 2 < 4 : 创建一个新列，没有使用到索引
2. WHERE my_col < 4/2 ：使用到索引