MENU

MySQL 索引设计和使用

August 16, 2021 • Read: 1438 • 编码,MySQL

索引是数据库中用来提高性能的常用工具,本章主要介绍了 MySQL5.7 支持的索引类型,并简单的介绍了索引的设计原则。

索引概述

所有的 MySQL 列都可以被设为索引,对相关列使用索引是提高 SELECT 性能的最佳途径。根据存储引擎可以定义每个表最大的索引数和最大索引长度,每种存储引擎对每张表支持最少 16 个索引总索引长度至少为 265 个字节。大多数存储引擎有更高的限制。

MyISAM 和 InnoDB 存储引擎的表默认创建的都是 BTREE (B树)索引。除了在单列或多列上直接创建索引外,MySQL5.7 之后还可以通过虚拟列索引来实现函数索引的功能,同时 MySQL 也支持前缀索引,即对索引字段的前 N 个字符创建索引。前缀索引的长度跟从存储引擎相关,MyISAM 的表,前缀索引最长可以达到 1000 个字节长度。而 InnoDB 存储引擎 最长的 3072 字节。前缀的限制应以字节为单位进行测量,而 CREATE TABLE 语句中的前缀长度解释为字符数。使用多字节字符集时一定要考虑。

MySQL 还支持全文本(FULLTEXT)索引,该索引可用于全文检索。在 MySQ5.6 之后 MyISAM 和 InnoDB 存储引擎都支持 FULLTEXT 索引,但仅限于 CHAR、VARCHAR、TEXT列。索引对整个列进行的,不支持局部索引(前缀索引)。

MySQL 也可以为空间列类型创建索引,MySQL 5.7 之前只有 MyISAM 支持空间索引,且索引的字段必须是非空的。MySQL5.7 中,InnoDB 存储引擎也开始支持空间索引,索引以 R-Trees 的数据结构保存。

默认情况下,MEMORY 存储引擎使用 HASH 索引,但也支持 BTREE 索引。

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

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option]...
    
    index_col_name:
        col_name [(length)] [ASC | DESC]    

也可以是使用 ALTER TABLE 的语法来增加索引,语法与 CREATE INDEX 类似。

例如:为 city 表创建 10 字节的前缀索引,语法如下:

mysql> CREATE INDEX cityname on city (city(10));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果以 city 为条件进行查询,可以发现索引 cityname 被使用:

mysql> explain select * FROM city WHERE city = "Fuzhou" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ref
possible_keys: cityname
          key: cityname
      key_len: 32
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

删除索引的语法:

DROP INDEX index_name ON tbl_name

例如 删除 city 表的 cityname 索引,语法如下:

mysql> DROP INDEX cityname ON city;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

设计索引的原则

索引的设计可以参考一些已有的原则,创建索引时尽量考虑符合这些原则,便于提高索引的使用效率,更高效的使用索引。

  • 要在条件列上创建索引,而不是查询列。 也就是说,最适合索引的列是出现在 WHERE 语法中的列,或连接子句中指定的列,而不是出现在 SELECT 关键词后的列。
  • 尽量使用唯一索引。考虑某列中值的分布,索引的列基数越大,索引的效果越好。例如:出生日期的列具有不同值,很容易区分行。而记录性别的列,只有 M 和 F ,则对此列进行索引没有多大用处,因为不管是检索那个值,都几乎有一半的行。
  • 使用短索引。如果对字符串列进行索引,应该指定一个前缀的长度,只要有可能就应该这样做。例如:CHAR(200) 列,如果在前 10 或 20 个字符内,多数值的列是唯一的,那么就不要对整个列进行索引,对前 10 个或 20 个字符进行索引能够节省大量的索引空间,也可能会使查询更快。较少的索引涉及的磁盘的 IO 较少,较短的值比较起来更快。更重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多的块的可能性。
  • 利用最左前缀。在创建一个 n 列的索引时,实际相当于创建了 N 个 MySQL 可用的索引。多列索引可以起几个索引的作用,例如以 a、b、c 的顺序在 3 列上创建一个组合索引之后,利用 a = ? 或者 a = ? AND b = ?

    a = ? AND b = ? AND c = ? 都可以使用这个索引。通过这种方式可以有效降低索引的使用数量,提高索引的使用效率。

  • 对于 InnoDB 存储引擎的表,尽量手工指定主键。记录默认会按照一定的顺序保存,如果没有明确定义的主键,则按照主键的顺序保存。如果没有主键,但是有唯一索引,那么就按照唯一索引的顺序来保存。如果既没有主键也没有唯一索引,那么表中会自动生成一个内部列。按照这个列的顺序保存。按照主键或内部列来进行的最快的,所以 InnoDB 表尽量自己指定主键。当表中有多个字段都是唯一字段时,要选择最常作为条件访问的字段作为主键,提高查询的效率。还有,InnoDB 表的普通索引会保存主键的键值,所以主键要尽可能选择较短的数据类型,有效的减少磁盘占用,提高索引的缓存效果。

索引设计的误区

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

  • 不是所有的表都需要索引。

    • 通常来说,常见的代码表、配置表等数据量很小的表,除了主键外,再创建索引没有太大的意义。索引扫描和全表扫描相比,并不会带来性能的大幅提升。
    • 而大表的查询、更新、删除操作则尽可能要通过索引。对于大表来说,任何全表扫描对于系统来说都是非常大的冲击,因此每个操作都尽可能通过索引进行。这类表要经常统计操作频率较高的 SQL,然后对这些 SQL 进行分析,提取出最常用的一些选择性较高的列来创建索引。
  • 不要过度索引。

    • 索引不是越多越好,什么东西都加索引是错误的。
    • 每个额外的索引都需要占用磁盘空间,并降低写操作的性能。
    • 在修改表的内容时,索引必须进行更新,因此,索引使用越多,所花的时间越长。
    • 如果有一个索引很少使用或从不使用,那么会不必要的减缓表的修改速度。
    • MySQL 在生成一个计划时,要考虑各个索引,这也要花费时间。
    • 创建多余的索引给查询优化带来了更多的工作。
    • 索引太多,可能会使 MySQL 选择不到所要使用的最好索引。
    • 因此,只保持所需的索引有利于查询优化。
  • 谨慎创建低选择度索引

    • 对于选择性低并且数据分布均衡的列,因为过滤的结果集大,创建索引的效果通常不好;
    • 但如果列的选择性低,但数据分布不均衡,比如男女的比例为 99% : 1% ,那么此时创建索引对于查询条件为 “女”的过滤结果集就比较小,索引的效率就比较高,此时创建索引就比较合适。
    • 在 MySQL8.0 之后也可以使用直方图取得类似的优化效果。

索引设计的一般步骤

当对一个大表设计索引时,可以采用下面的步骤:

  1. 整理表上所有的SQL,重点包括 SELECTUPDATEDELETE 等操作的 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' (其中 '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%' OR key_col BETWEEN 'lisa' AND 'simon';

当对索引字段进行范围查询时,只有 BTREE 索引可以通过索引访问,而 HASH 索引则会进行全表扫描。

了解 BTREE 索引和 HASH 索引的区别后,当使用 MEMORY 表时,如果是默认创建的 HASH 索引,就要注意SQL语句的编写,确保可以使用上索引。如果一定要使用范围查询,那么创建索引时就应该选择 BTREE索引。

索引在 MySQL8.0 中的改进

MySQL8.0 中,索引增加了不少新特性,下面介绍几个重点的改进:

不可见索引

在 MySQL8.0 中,增加了对于不可见索引( invisible index )的支持,这是一个从 Oracle 数据库借鉴过来的新特性。所谓不可见索引,指的是对查询优化器不可见,SQL 在执行时也就自然不会选择,但查看表结构的时候索引仍然能看到,也可以通过 information_schema.statics 或者 show index 来查看索引是否可见的状态。

索引默认是可见的,可以通过 invisible 关键字设置不可见索引或通过命令单独添加不可见索引:

mysql> CREATE TABLE t1 (
    -> i int,
    -> j int,
    -> k int,
    -> INDEX i_idx(i) INVISIBLE
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.21 sec)

mysql> CREATE INDEX j_idx ON t1 (j) INVISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

可以通过命令设置已有索引是否可见:

-- 设置 i_idx 为不可见索引
mysql> ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 设置 i_idx 为可见索引
mysql> ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

设计这种索引主要是为了减少对于表上的索引进行调整时的潜在风险。

当一个表数据量达到 几百GB、几 TB 甚至更大的时候,此时对表上的索引进行调整,往往面临着很大的风险。例如:删除一个认为不需要的索引时,但其他SQL还在使用的索引。那么这些SQL在执行时,因为没有索引就会进行全表扫描,这样就会对服务器造成巨大冲击,很可能导致服务直接不可用。由于数据量过大,重建索引需要消耗的资源和时间也会很多,很难马上通过重建索引解决问题。

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

同样,当添加一个索引时,如果对系统产生了负面影响,可以先将索引蛇设为不可见索引,待系统恢复正常后,再将索引删除,避免再系统负载过大的时候雪上加霜。

倒序索引

在 MySQL8.0 中 正是添加了对 倒序索引的支持,在之前的版本中,虽然可以创建索引的时候指定 desc 关键字,但实际上 MySQL 仍会保存为正序索引,在 MySQL8.0 中,倒序索引能够正确的创建。

倒序索引在某些情况下,可以起到很好的作用。

需要注意的是,由于倒序索引的引入,MySQL8.0 中取消了对 GROUP BY 操作的隐式排序,如果业务中有依赖此特性的,升级数据库版本时要谨慎。


有索引时 MySQL 查询数据就不需要从第一行数据开始找,全表扫描的情况下表越大花的时间越长。如果有一个列索引,则 MySQL 可以直接从指定位置读取数据,如果有 1000 条数据,走索引比全表扫描快了至少 100 倍。

大多数 MySQL 索引(PRIMARY KEY 、UNIQUE、INDEX、FULLTEXT 等)在 BTREE 中存储。只是空间列类型的索引使用 BTREE。

Last Modified: September 12, 2021