索引的代价

空间代价:

• 每个 B+ 树的节点使用 16KB

时间代价:

• 维护索引是耗时的

一个表上的索引越多, 就会占用更多的存储空间, 在增删改时的性能越差.

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

匹配左边的列

• 使用联合索引靠左边的列

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

# 这个例子用不到 B+ 树索引
SELECT * FROM person_info WHERE birthday = '1990-09-27';

# 这个例子只能使用 name
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%';

匹配范围值

• 由于记录是排好序的, 所以直接取查好的两端值那段
• 多个范围查找时, 只有最靠左的列的范围查找才用索引

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

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

• 左边的列使用精确查找, 右边的列使用范围查找

# name, birthday 用到索引, phone_number 没用到
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';
# 都用到了
SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday = '1980-01-01' AND phone_number > '15100000000';

用于排序

• 在MySQL中，把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort）
• 如果ORDER BY子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤

# ORDER BY name, birthday, phone_number 三个字段
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

使用联合索引进行排序注意事项

• order by 后的列的顺序也必须是索引列的顺序
• order by + 左边部分索引
• where + 左边部分索引 + order by 右边部分索引:

SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;

• 不能使用索引进行排序的情况
  • ASC, DESC 混用 (mysql8.0 支持逆序索引)
  • WHERE 子句中出现非排序使用到的索引列
  • 排序列包含非同一个索引的列

SELECT * FROM person_info WHERE country = 'China' ORDER BY name LIMIT 10;
SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;
SELECT * FROM person_info ORDER BY name, country LIMIT 10;

• 排序列使用了复杂的表达式

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

用于分组

• 分组列的顺序需要和索引列的顺序一致
• 可以只使用左边的列进行分组

回表的代价

• 在使用索引的情况下, 二级索引时顺序 I/O 的, 快, 回表时是随即 I/O 的, 慢
• 尽量避免过多的回表

一般情况下，限制查询记录数会让优化器更倾向于选择使用二级索引 + 回表的方式进行查询:

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

覆盖索引

彻底告别回表, 建议查询列只包含索引列:

• 这种只查询索引列的方式称为索引覆盖

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

所以引出 “挑选索引”, 使用哪些列作为索引:

• 只为用于搜索, 排序, 分组的列创建索引
  • WHERE, JOIN, ORDER BY, GROUP BY 后出现的列
• 考虑列的基数
  • 列的基数: 某一列中不重复数据的个数
  • 基数越小越, 索引越没用, 没法排序
• 索引列的类型尽量小
  • 时间, cpu 计算快
  • 空间, 减少磁盘 I/O
• 索引字符串值的前缀
  • 不使用完整的字符串作为索引, 只使用前几个字符
  • 会导致无法使用索引排序

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

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

WHERE my_col * 2 < 4 # 不使用索引
WHERE my_col < 4/2

主键插入顺序

• 页分裂
• 记录移动

优化:

• 设置自增 ID 的主键

冗余和重复索引

# name 上的重复索引, 没好处
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))
);

# 主键本身自动生成聚簇索引
CREATE TABLE repeat_index_demo (
    c1 INT PRIMARY KEY,
    c2 INT,
    UNIQUE uidx_c1 (c1),
    INDEX idx_c1 (c1)
);