深入浅出MySQL-06-【索引的设计和使用】-易微帮

文章目录

前言
1.索引概述
2.设计索引的原则
3.索引设计的误区
4.索引设计的一般步骤
5.BTREE索引和HASH索引
6.索引在MySQL 8.0中的改进
- 6.1.不可见索引
- 6.2.倒序索引
7.总结

前言

环境：

Windows11
MySQL-8.0.35

1.索引概述

所有MySQL列类型都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎(如MyISAM、InnoDB、BDB、MEMORY等)对每个表至少支持16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

MyISAM 和InnoDB存储引擎的表默认创建的都是BTREE索引。除了直接在单列或者多列上直接创建索引外，MySQL5.7之后可以通过虚拟列索引来实现函数索引的功能，同时MySQL也支持前缀索引，即对索引字段的前N个字符创建索引。前缀索引的长度跟存储引擎相关。

MySQL中还支持全文本(FULLTEXT)索引，该索引可以用于全文搜索。ImnnoDB和MyISAM存储引擎虽然支持FULLTEXT索引，但只限于 CHAR、VARCHAR 和 TEXT列。索引总是对整个列进行的，不支持局部(前缀)索引。

MySQL也可以为空间列类型创建索引，MySQL5.7中，InnoDB存储引擎也开始支持空间类型索引，索引以R-Trees的数据结构保存。

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

// 使用mysql的帮助文档查看创建索引的SQL语句
mysql> ? create index;
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]
... ...

也可以使用ALTER TABLE的语法来增加索引，语法类似。

索引的删除语句如下：

mysql> ? drop index;
Name: 'DROP INDEX'
Description:
Syntax:
DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option] ...
 ... ...

2.设计索引的原则

索引的设计可以遵循一些已有的原则，创建索引的时候尽量考虑这些原则，便于提升索引的使用效率、更高效的使用索引：

要在条件列上创建索引，而不是查询列。也就是说最适合索引的列是出现在 WHERE子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。
尽量使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。
使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，有一个 CHAR(200)列，如果在前 10 个或 20 个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前 10个或 20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘IO较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
利用最左前缀。在创建一个n列的索引时，实际相当于创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。例如以 a、b、c的顺序在3列上创建一个组合索引之后，利用 a=?或者 a=? and b=?或者 a=?and b=?andc=?这3种条件的查询，都可以使用这个索引。通过这种方式，可以有效的降低索引的数量，提高索引的使用效率。
对于 InnoDB 存储引擎的表，尽量手工指定主键。记录默认会按照一定的顺序保存,如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列,按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以 InnoDB 表尽量自己指定主键。当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，有效地减少索引的磁盘占用，提高索引的缓存效果。
对于 InnoDB 存储引擎的表，尽量手工指定主键。记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以 ImnoDB 表尽量自己指定主键。当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，有效地减少索引的磁盘占用，提高索引的缓存效果。

3.索引设计的误区

设计索引时，有一些常见的误区，总结如下：

不是所有的表都需要创建索引。通常来说，数据量很小的表，除了主键外，再创建索引没有太大的意义，索引扫描和全表扫描相比，并不会带来性能的大幅提升。而大表的查询、更新、删除操作则要尽可能通过索引。对于大表来说，任何全表扫描对于系统来说都会是非常大的冲击，因此每个操作都尽可能通过索引进行。这类表要经常统计操作频率较高的 SQL，然后对这些 SQL进行分析，提取最常用的一些选择性高的列来创建索引。
不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错误的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL选择不到所要使用的最好索引。因此，只保持所需的索引有利于查询优化。
谨慎创建低选择度索引。对于选择性低并且数据分布均衡的列，因为过滤的结果集大，创建索引的效果通常不好；但如果列的选择性低但数据分布不均衡，比如男女比例为99%:1%，那么此时创建索引对于查询条件为’女"的过滤结果集就比较小，索引的效率较高，此时创建索引就比较合适。在MySQL8.0之后也可以使用直方图取得类似的优化效果。

4.索引设计的一般步骤

通过上面的介绍，当对一个大表做索引设计时，一般可以采取下面的步骤：

整理表上的所有SQL，重点时select、update 和 delete 操作的where条件所用到的列的组合、关联查询的关联条件等。
整理所有查询 SQL的预期执行频率。
整理所有涉及的列的选择度，列的不同值相比总非空行数的比例越大，选择度越好，比如全部都是唯一值的主键列选择度最高。当然，上面所提到的查询频率、选择度，都是估算的值，能够在设计索引时作为参考即可。
遵照之前提到的设计原则，给表选择合适的主键。
优先给那些执行频率最高的SQL创建索引，执行频率很高的SQL，使用到的索引的效率对整体性能影响也会比较大，选择其中选择度最高的列来创建索引，如果选择度都不够好，那么应该考虑是否可以使用其他选择度更好的条件，或者选择创建联合索引。
按执行频率排序，依次检查是否需要为每个SQL创建索引，可以复用之前已经创建的索引的SQL，无须再重复创建索引，除非SQL执行频率很高，新创建的索引，对选择度提升也很大。
索引合并，利用复合索引来降低索引的总数，充分利用最左前缀的原则，让索引可以被尽可能多地复用，同时在保证复用率的情况下，把选择度更高的列放到索引的更左侧。
上线之后，通过慢查询分析、执行计划分析、索引使用统计，来确定索引的实际使用情况，并根据情况做出调整。

5.BTREE索引和HASH索引

BTREE索引和 HASH索引，这两种不同类型的索引各有其不同的适用范围。HASH索引有一些重要的特征在使用时需特别注意，如下所示：

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

而对于 BTREE 索引，当使用>、<、>=、<=、BETWEEN、!=或者>，或者 LIKE‘pattern’（其中’pattern’不以通配符开始）操作符时，都可以使用相关列上的索引。例如下面这个查询适用于BTREE和HASH索引：

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%' and key_col between 'zhangsan' and 'wangwu';

当对索引字段进行范围查询的时候，只有BTREE索引可以通过索引访问，而HASH索引实际上是全盘扫描的。

6.索引在MySQL 8.0中的改进

索引的正确使用，对于MySQL的性能优化，起着非常关键的作用。在MySQL8.0中索引也引人了不少新的特性。下面介绍几个比较重点的改进。

6.1.不可见索引

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

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

CREATE TABLE t1 (
	col int,
	col2 int
	INDEX col_idx(col) INVISIBLE
) ENGINE = InnoDB

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

CREATE INDEX col_idx(col) INVISIBLE;
ALTER TABLE t1 ADD INDEX_col_idx(col) INVISIBLE;

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

ALTER TABLE t1 ALTER INDEX col_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX col_idx VISIBLE;

为什么数据库中要设计这么一种消耗资源，却又不能够对SQL起到任何优化左右的索引呢？实际上，引入不可见索引的目的，主要是为了减小对于表上的索引进行调整时的潜在风险。

随着表的数据量增大，达到了几百GB几TB甚至更大的时候，如果此时对表上的索引进行调整，往往面临着很大的风险。例如，当删除一个认为不再需要的索引时，一旦系统中还存在个别使用这个索引的SOL，那么这些SOL的执行计划有可能会变成对这个大表的全表扫描，这会对数据库服务器造成巨大冲击或直接导致服务不可用。而由于表的数据量大，重建索引需要的时间和消耗的系统资源也会很大，很难马上通过重建索引解决问题。

有了不可见索引，当需要删除一个表上的冗余索引时，可以先将索引设置为不可见，而不是直接删除，一旦发现没有这个索引之后，对系统性能产生了负面影响，可以很方便地恢复这个索引，而不再需要重建索引。

同样，当增加一个索引之后，如果发现对系统带来了负面影响，可以首先将索引设置为不可见，待系统负载恢复正常后，再做索引的删除，避免了系统压力大的时候雪上加霜。

6.2.倒序索引

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

MySQL中的倒序索引起到的作用比较弱，这里不做讨论了。

7.总结

索引用于快速找出在某个列中有某个特定值的行。如果不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻数据文件的中间，没有必要看所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意如果需要访问大部分行，顺序读取要快得多，因为此时应避免磁盘搜索。

大多数 MySQL索引(如PRIMARY KEY、UNIQUE、INDEX和FULLTEXT等)在 BTREE中存储。只是空间列类型的索引使用BTREE，并目MEMORY表还支持HASH索引。

深入浅出MySQL-06-【索引的设计和使用】