1.1.1. 使用索引

索引的代价

  1. 空间上:每建立一个索引就是一个 B+ 树,每个树的节点都是一个页,一个页有 16KB 大小,一颗很大的树有很多页,就会占据很大的存储空间。
  2. 时间上:在 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_number = '15123983239';

这三个列的顺序不影响查询效率,mysql 的查询优化器会帮助我们优化。

匹配最左边的列

mysql 会先搜索 name 列,当 name 相同时才会搜索 birthday,甚至 phone_number 列。 所以,下面两个 sql 可以使用索引,最后一个不行。

SELECT * FROM person_info WHERE name = 'Ashburn';
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1990-09-27';
# 不能使用索引
SELECT * FROM person_info WHERE birthday = '1990-09-27';
匹配列前缀

mysql 创建二级索引,如果这个列是字符串类型的,会有这样的特点:先按照第一个字符排序,当第一个字符相同时,再按照第二个字符排序,以此类推。

# 可以使用索引
SELECT * FROM person_info WHERE name LIKE 'As%';

# 不能使用索引
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. 由于所有记录都是由链表连接起来的,所以很容易取出之间的数据。
  4. 得到这些记录的主键值,再「回表」。

但是,如果对多个列进行范围查找,只有索引最左边的那个列才会使用到 b+ 的索引:

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

过程:

  1. 先匹配 name 值的范围,name 值的结果可能不同
  2. 由于 name 值可能不同,所以通过 birthday 进行筛选是不是用索引的。name 值只有相同的情况下才能对 birthday 进行排序。
精确匹配某一列并范围匹配另外一列
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';

过程:

  1. 先匹配 name 列。
  2. 由于 name 值相同,所以可以使用索引再范围匹配 birthday 列。
  3. 由于 birthday 不同了,所以 phone_number 列不能使用索引。
用于排序
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

mysql 在创建索引时,先按照 name 从小到大排序,再依次按照 birthday、phone_number 排序。

当排序顺序一致时:

# 直接从左向右读 10 条记录
ORDER BY name, birthday LIMIT 10

# 直接从右向左读 10 条记录
ORDER BY name DESC, birthday DESC LIMIT 10

但是,如果顺序不一致,则不会使用到索引,如

SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;
其他情况

以下三种情况都不会使用索引:

# WHERE子句中出现非排序使用到的索引列
SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;

# 排序列包含非同一个索引的列
SELECT * FROM person_info ORDER BY name, country LIMIT 10;

# 排序列使用了复杂的表达式
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;
用于分组
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

过程:

  1. 先按照 name 分组,所有 name 相同记录划分为一组。
  2. 再将 name 相同的中 birthday 相同的放到另一组。
  3. 再在 birthday 中 phone_number 相同的放到另一组。

所以整个过程看起来就是大组分小组,小组分小小组。

回表的代价
SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';

以这个 sql 为例子说明,由于索引关系可以通过 name 列进行查找,由于 Asc ~ Barlow 之间的记录是相连的,可以很快从磁盘中将其取出,这种读取称之为:顺序 I/O

而我们查出了 Asc ~ Barlow 对应的主键时,需要进行「回表」操作,而由于查询出来的主键是非连续的,所以这种读取方式被称之为:随机 I/0

即: 访问二级索引是 「顺序 I/0」 访问聚簇索引是 「随机 I/0」

1

覆盖索引

为了彻底告别 「回表」操作,建议查询列表最好只包含索引列:

SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow'

因为只查询 name, birthday, phone_number 值,而二级索引的叶子页记录正好包含这三列值。所以不需要回表了。

如何挑选索引
  1. 只为用于搜索、排序或分组的列创建索引
  2. 考虑列的基数,如 2, 5, 8, 2, 5, 8, 2, 5, 8 虽然有 9 条记录,但基数是 3。基数越大,值越分散,效率才高。
  3. 索引列的类型尽量小(范围)。比如我们能使用INT就不要使用BIGINT,能使用MEDIUMINT就不要使用INT~。因为数据类型越小,CPU 查询越快,索引占用的空间越小,一条记录能塞更多记录,从来减少 I/0 带来的性能损耗。
Copyright © Kagami丶 2019 all right reserved,powered by Gitbook该文件修订时间: 2019-12-10 21:47:13

results matching ""

    No results matching ""