《深入浅出MySQL》第九章 - 索引的设计与使用

一、索引概述

---

索引是数据库中用来提高性能的常用工具，本章主要介绍 MySQL 5.7 支持的索引类型。

• 添加索引
  索引在创建表的时候可以同时创建，也可以随时增加新的索引。创建新索引的语法如下：

  CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    ON tbl_name (index_col_name[(length)] [ASC | DESC], ...);
  

  例如：为city表的city_nm字段创建10字节的前缀索引：

  CREATE INDEX idx_city_nm ON city(city_nm(10));
  

  也可以用ALTER TABLE的语法来增加索引，语法与CREATE TABLE类似：

  ALTER TABLE tbl_name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
  (index_col_name[(length)] [ASC | DESC], ...);
  

  例如：为city表的city_nm字段创建10字节的前缀索引：

  ALTER TABLE city ADD INDEX idx_city_nm city(city_nm(10));
  

• 删除索引

  DROP INDEX index_name ON tbl_name;
  

  例如，删除city表上的索引idx_city_nm：

  DROP INDEX idx_city_nm ON city;
  

二、设计索引的原则

---

• 要在条件列上创建索引，而不是查询列。换言之，最适合索引的列是出现在WHERE子句中的列，或链接子句中指定的列。
• 尽量使用高选择度索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。如：
  • 存放出生日期的列具有不同值，很容易区分各行。
  • 用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处。因为不管搜索哪个值，都会得出大约一半的值。
• 尽量使用短索引。如果对字符串进行索引，应该指定一个前缀长度。例如一个CHAR(200)列，若前10个或20个自负内，多数值是唯一的，那么就不要对整个列进行索引。因为：
  • 较小的索引涉及的磁盘IO较少。
  • 较短的值比较起来更快。
  • 对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
• 多利用最左前缀。
  
  

三、索引设计的误区

---

• 不是所有的表都需要创建索引，常见的如码表、配置表等数据量很小的表，除主键外，再创建索引没有太大的意义。
• 大表大查询、更新、删除操作要尽可能通过索引。这类表要经常统计操作频率较高的SQL，然后对这些SQL进行分析，提取最长用对一些选择性高的列来创建索引。
• 不要过度索引。过度索引会导致：
  • 占用额外的磁盘空间。
  • 降低“写”操作的性能。
  • 修改表内容时，索引必须进行更新，有时可能需要重构。此时，索引越多，所花时间越长。
  • MySQL在生成一个执行计划时，要考虑各个索引，索引过多会导致MySQL在选择最适索引的时候产生困惑，这也需要花费时间。
    
    

四、索引设计的一般步骤

---

1. 整理表上所有SQL。重点包括 select、update、delete 操作的 where 条件所用到的列的组合、关联查询的关联条件等。
2. 整理所有查询SQL的预期执行频率。
3. 整理所有涉及的列的选择度。列的不同值相比总非空行数的比例越大，选择度越高。如前文讨论的“出生日期”与“性别”。
4. 遵照值钱提到的设计原则，给表选择合适的主键，没有特别合适的列时，建议使用自增列作为主键。
5. 优先给那些执行频率最高的SQL创建索引，执行频率很高的SQL，使用到的索引的效率对整体性能影响也会比较大，选择其中选择度最高的列来创建索引，如果选择度都不够好，那么应该考虑是否可以使用其他选择度更好的条件，或者选择创建联合索引。
6. 按执行频率排序，依次检查是否需要为每个SQL都创建索引，可以复用值钱已经创建的索引的SQL，无需再重复创建索引。除非SQL助兴频率很高，创建新的索引，对选择度提升也很大。
7. 索引合并，利用符合索引来降低索引的总数，充分利用最左前缀原则，让索引可以呗尽可能的多复用。同时在保证复用率的情况下，把选择度更高的列放到索引的更左侧。
8. 上线之后，通过慢查询分析、执行计划分析、索引使用统计，来确定索引的实际使用情况，并根据情况作出调整。

五、BTREE 索引与 HASH 索引

---

MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引各有其不同的适用范围：HASH索引有如下重要的特征在使用的时候需要特别注意：

• 只用于使用 = 或 <=> 操作符的等式比较。
• 优化器不能使用 HASH 索引来加速 ORDER BY 操作。
• MySQL不能确定在两个值之间大约有多少行，如果将一个 MyISAM 表改为 HASH 索引的 MEMORY 表，会影响到一些查询的执行效率。只能使用整个关键字来搜索一行。
• 只能使用整个关键字来搜索一行。

而对于 BTREE 索引，当使用 >、<、>=、<=、BETWEEN、!=、<>、 like 'pattern%' 操作符时，都可以使用相关列上的索引：

• 适用于 HASH 和 BTREE

  SELECT * FROM t1 WHERE key_col=1 OR key_col in (15, 18, 20);
  

• 仅适用于BTREE

  SELECT * FROM t1 WHERE key_col>1 AND key_col <10;
  SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col  BETWEEN 'lisa' AND 'simon';
  

六、索引在 MySQL8.0 中的改进

---

1、不可见索引

所谓“不可见”，指的是对于查询优化器不可见，SQL在执行时自然也就不会选择。换言之，就是“屏蔽”了该索引。但在查看表结构的时候索引仍然可以看到。也可以通过 information_schema.statistics 或者 show index 来查看索引是否可见的状态。

索引默认是可见的，我们可以通过在创建索引时指定 invisible 关键字来创建不可见索引：

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX idx_i (i) INVISIBLE
) ENGINE = InnoDB;

也可以通过命令来单独添加不可见索引：

CREATE INDEX idx_j ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX idx_k (k) INVISIBLE;

也可以通过 alter table 命令来修改索引是否可见：

ALTER TABLE t1 ALTER INDEX idx_i INVISIBLE;
ALTER TABLE t1 ALTER INDEX idx_i VISIBLE;

引入不可见索引的目的主要是为了减小对于标上的索引进行调整时的潜在风险。如：对上产上数据量达到几百GB，甚至更多的时候，贸然之间删除索引的风险是极大的。此时可以先将该索引屏蔽，查看屏蔽该索引后是否会对系统造成实质性的影响，若影响过大，解除屏蔽即可恢复索引。

2、倒序索引

在 MySQL 8.0 中，正式增加了对倒序索引对支持，在之前对版本中，虽然在创建索引的时候可以指定 desc 关键字，但是实际上MySQL仍然会保存为正序索引。

CREATE TABLE t1 (
  a INT,
  b INT,
  INDEX idx_a_b (a DESC, b)
);

倒序索引在某些情况下，可以起到更好的作用，但是相对于Oracle而言，效果没那么明显，有待未来版本继续加强。需要注意的是，由于倒序索引的引入，MySQL 8.0 取消了对 group by 操作的隐式排序。