MySQL系列(15)：索引优化

　　给列创建索引是提高 SELECT 查询性能的最佳方式。索引类似于表行的指针，在查询时可以根据索引快速定位某些行与 WHERE 子句中的条件匹配，并检索该行的其他列值。MySQL 所有数据类型都可以创建索引。

　　虽然添加索引对查询带来的性能提效果是显著的，但也不是为每一个列都创建索引为好，索引还会增加 插入、更新和删除的成功，因为必须更新每个索引。　

使用索引

　　索引用于快速查找具有特定列值的行。如果没有索引，MySQL 必须从第一行开始，然后读取整个表以查找相关行。表越大，成本越高。如果表中相关列建立了索引，MySQL 可以快速定位需要的数据在数据中的位置，而无需查看所有数据，这比按顺序读取每一行快得多。

　　大多数 MySQL 索引(PRIMARY KEY，UNIQUE，INDEX 和 FULLTEXT)都存在 B-tree 中。而一些特殊的数据类型的索引数据结构会有些不同，如空间数据类型的索引使用 R-tree ，MEMORY 表也支持哈希索引，InnoDB 使用反转列表处理FULLTEXT 索引。

MySQL如何使用索引

1. 快速查找与 where 条件匹配的行。

2. 缩小索引范围。如果需要在多个索引之间选择，MySQL 通常使用查找最少行数的索引(最具有选择性的索引，即更能快速定位具体行的索引)。

3. 如果表有多列索引，优化器可以使用索引的任何最左边的前缀查找行。

   例如，如果在（col1、col2、col3）上有三列索引，则在（col1）、（col1、col2）和（col1、col2、col3）上有索引搜索功能。

4. 在执行联接(join)查询时，哪果联接的列的类型和大小(长度)相同，MySQL 可以更有效地使用这些列的索引。

5. 对于非二进制字符串列之间的比较，两列应使用相同的字符串，否则会排除使用索引。

   例如，将 utf8 列与 latin1 列进行比较会排除使用索引。

6. 如果无法在不进行类型转换的情况下直接比较值，列类型不相同，可能会阻止使用索引(即必须进行类型转换才能进行比较，会导致索引失效)。

   例如，将字符串列与时间或数字列进行比较。如数字 1 与字符串的数量值(如 “1”,”00001” 或 “01.e1”)进行比较，这就排除了使用字符串列的任何索引。

7. 对指定的索引列(key_col)使用 MIN() 或 MAX() 函数，MySQL 预处理器进行优化处理。

   预处理器会检查是否在指定索引列之前的所有关键部分(关键索引列)使用 WHERE key_part_N = constant，如下示例，

   SELECT MIN(key_part2),MAX(key_part2)  FROM tbl_name WHERE key_part1=10;

   示例中，MySQL 为每个 min() 或 max() 表达式执行一个单键查找(即一个条件查找)，并将其规换为一个常量。如果所有表达式都被常量替换，则查询立即返回。

8. 如果最左前缀(例如，ORDER BY key_part1，key_part2)索引在排序或分组可用，则对表进行排序或分组；如果关键列指定倒序(DESC)，则按相反的顺序读取键(key)。

9. 使用覆盖索引，在某些情况下，查询会被优化，在不访问数据行的情况下，直接通过索引获取列值(为查询直接提供值的称为覆盖索引)。如果查询仅使用表中包含某些索引的列，则可以从索引树(index tree)中快速获取查询的值。

   注：覆盖索引只是一种查询效果，是使用索引列做为条件来查询索引列的值。

   SELECT key_part3 FROM tbl_name WHERE key_part1=1

索引优化

主键优化

尽可能使用主键，主键的效率是最高的，在SQL语句中也尽可能使用主键做为条件查询。

主键创建在查询中最经常使用的列，主键列具有关联的索引，可以实现快速查询的性能要求。

主键不能包含任保 NULL 值，主键查询受益于 NOT NULL 优化。使用 InnoDB 存储引擎，可以根据主键进行超快速查找和排序。

如果表很大且很重要，难以判断那些列适合用作主键，则可以创建一个自增列来作为主键。需要使用外键连接表时，可以将主键列用作其它表的关联列，指向相应数据行的指针。

外键优化

如果一个表有许多列，并且查询用到了许多不同的列组合，那么可以将不常用的数据拆分为单独的只有几个列的表，并使用主表的主键作为外键进行表关联，可能会更有效。这样，每个小表都可以有一个用于快速查找其数据的主键，主表和小表可通过联接操作仅量询所需的列集。

数据在磁盘存储是分散的，在大表拆小表后，查询可能会执行较少的 I/O 并占用较少的高速缓存，因为较少的列的数据可能存放在相同的数据块。（为了最大限度地提高性能，查询尝试从磁盘读取尽可能少的数据块；只有几个列的表可以在每个数据块中容纳更多的行。

普通索引

普通索引是最常见的索引类型，涉及单个列。是创建该列的值的副本存放在索引数据结构中，允许快速查找该索引列值的相应的数据行。

B-tree 数据结构允许索引在 WHERE 子句中快速找到与运行符( 如，**=、>、>=、<、<=、BETWEEN、IN**等)对应的特定值，一组值或一系列值。

每个存储引擎定义了每个表最大的索引数和最大的索引长度，至少支持 16 个索引，总索引长度至少为 256 个字节。大多数存储引擎支持更高的限制。

1. 前缀索引(Index Prefixes)

   使用 字符串类型的列，可以创建令使用列的前 N 个字符的索引，可以使索引文件更小。索引长度 20 个字符时，基本可以达到 90% 的区分。BLOB 和 TEXT 列创建索引，必须指定前缀长度。

   前缀最长可达 1000 个字符(InnoDB 表为 767 个字符，可以修改 innodb_large_prefix 项的值)。

   如果搜索项超过索引前缀长度，则索引用于排除不匹配的行，并检查剩余的行以查找可能匹配的项。

2. 全文索引(FULLTEXT Indexes)

   只有 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引，并且令支持 **CHAR, VARCHAR 和 TEXT **列，索引是创建在整个列数据上，并且不支持前缀索引。

   全文索引优化适用于针对单个 InnoDB 表的某些类型的 FULLTEXT 查询。具有以下特征的查询特别有效：

   • FULLTEXT 查询仅返回文档ID，或者 文档ID 和 搜索排名。
   • 不使用 WHERE 子句，并只能按降序排列单个 ORDER BY 子句。FULLTEXT 查询按照得分的对匹配的行进行降序排序，并应用 LIMIT 子句来获取前 N 个匹配的行。
   • FULLTEXT 查询令检索与搜索匹配的行的 COUNT(*) 值，条件语句使用的是 WHERE MATCH (col1,col2,…) AGAINST (‘other_text’)，而不是带任何比较运算符的 WHERE 子句。

3. 空间索引(Spatial Indexes)

   可以在空间数据类型上创建索引。MyISAM 和 InnoDB 支持空间类型的 R-tree 索引。其它存储引擎使用 B-tree 索引为空间类型编制索引(不支持空间类型索引的存档除外 )。

多列索引

MySQL 可以创建复合索引(即 多列索引)。一个多列索引最多可包含 16 个列。如果在索引定义中，以正确的顺序指定列，则单个复合索引可以加速同一表上的多种查询(如第一列，前两列，前三列等)。

多列索引可视为排序数据，各行多列索引的值来自于关联索引列的值。

注意：
复合索引还可使用替代方式，将需要加入多列索引的列拼接后进行散列计算，引入一个散列列来存放多列的散列值，此列会很短，并建立索引，则它可能比由多列组成的的 “宽” 索引更快。

SELECT * FROM tbl_name WHERE hash_col=MD5(CONCAT(val1,val2)) AND col1=val1 AND col2=val2;

多列索引使用示例：

1. 表结构

   CREATE TABLE test (
       id         INT NOT NULL,
       last_name  CHAR(30) NOT NULL,
       first_name CHAR(30) NOT NULL,
       PRIMARY KEY (id),
       INDEX name (last_name,first_name)
   );

   多列索引 name 包含了 last_name, first_name 两列。该索引可在查询语句使用，对 last_name 和 first_name 范围内的值进行组合查询；也可仅指定 last_name 值进行查询，因为该列是索引的最左前缀。

2. 以下查询语句会使用组合索引 name

   SELECT * FROM test WHERE last_name='Widenius';
   SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
   SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');
   SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N';

3. 以下查询语句不会使用组合索引 name

   SELECT * FROM test WHERE first_name='Michael';
   SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';

4. 可以使用多列索引的SQL语句

   SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

   如果 col1 和 col2 存在多列索引，则可以直接获取相应的行。

   如果 col1 和 col2 存在单独的单列索引，优化器会尝试用索引合并优化；或尝试查找限制最大，能排除更多行的索引并使用该索引。

   如果创建多列索引，则优化器可以使用索引的任何最左前缀来查找行。 例如，如果有个三列（col1，col2，col3）组成的多列索引，则在（col1），（col1，col2）和（col1，col2，col3）上可以使用索引搜索功能。

5. 不能使用多列索引的SQL语句

   如果SQL语句的条件列不是多列索引的最左前缀，MySQL 就无法使用多列索引执行查询，如下示例

   SELECT * FROM tbl_name WHERE col1=val1;
   SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
   
   SELECT * FROM tbl_name WHERE col2=val2;
   SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

   如果给（col1、col2、col3）三列创建了多列索引，则只有前两个查询使用该索引。

   第三和第四个查询确实涉及索引列，但不使用索引执行查找，因为（col2）和（col2，col3）不是（col1，col2，col3）的最左边前缀。

索引小技巧

1. 如果表创建了单列索引和多列索引，并且存在相同的字段，则单列索引列应作为多列索引的末尾列。如下示例：

   mysql> show index from inventory;
   +-----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   | Table     | Non_unique | Key_name             | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
   +-----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
   | inventory |          0 | PRIMARY              |            1 | inventory_id | A         |        4581 | NULL     | NULL   |      | BTREE      |         |               |
   | inventory |          1 | idx_fk_film_id       |            1 | film_id      | A         |         958 | NULL     | NULL   |      | BTREE      |         |               |
   | inventory |          1 | idx_store_id_film_id |            1 | store_id     | A         |           2 | NULL     | NULL   |      | BTREE      |         |               |
   | inventory |          1 | idx_store_id_film_id |            2 | film_id      | A         |        1521 | NULL     | NULL   |      | BTREE      |         |               |
   +-----------+------------+----------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

   这样的好处是，单列条件查询时，不管选择那个列都能使用索引，若是多列索引列则自然满足最左原则。

验证索引使用

可以使用 EXPLAIN 来查看 SQL 语句的执行情况，查看 SQL 语句是否真正的使用了索引，使用是否达到预期。

生成列的索引

MySQL 优化器也会评估使用生成列的索引。这里的 生成列 指该列的值是通过调用 存储过程 执行 引用了其它列构建成的表达式生成的，如下

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

生成的 gc 列由表达式 f1 +1 定义，创建了 gc 列的索引，优化器在执行计划构建期间会考虑该索引。

1. 下面的查询中，WHERE 子句引用了 gc 列，优化器会评估该列上的索引使用是否可生成更高效的执行计划。

   SELECT * FROM t1 WHERE gc > 9;

2. 下面的查询中，没有直接引用索引列，优化器也可以在生成的列(gc)上使用索引来生成执行计划。

   SELECT * FROM t1 WHERE f1 + 1 > 9;

   如果 WHERE 、ORDER BY 或 GROUP BY 子句引用的表达式与某个索引生成列的定义匹配，则会使用该生成列上的索引。

   优化器识别出表达式 f1 + 1 与 gc 的定义匹配并且 gc 列创建了索引，因引在创建该语句的执行计划期间会考虑使用 gc 列的索引。

   实际上，优化器将表达式 f1 + 1 替换为与表达式匹配的生成列的名称。这在 EXPLAIN 的扩展警告信息(SHOW WARNINGS)中可以明显看到被重写的查询语句。

   使用 EXPLAIN 分析索引使用如下：

   mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9;
   +----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
   | id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                 |
   +----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
   |  1 | SIMPLE      | t1    | NULL       | range | gc            | gc  | 5       | NULL |    2 |      100 | Using index condition |
   +----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
   
   mysql> SHOW WARNINGS;
   +-------+------+-------------------------------------------------------------------------------------------------------------------------------+
   | Level | Code | Message                                                                                                                       |
   +-------+------+-------------------------------------------------------------------------------------------------------------------------------+
   | Note  | 1003 | /* select#1 */ select `sakila`.`t1`.`f1` AS `f1`,`sakila`.`t1`.`gc` AS `gc` from `sakila`.`t1` where (`sakila`.`t1`.`gc` > 9) |
   +-------+------+-------------------------------------------------------------------------------------------------------------------------------+

以下限制和条件适用于优化器使用生成的列索引：

1. 查询表达式必须与定义生成列的表达式相同，且结果类型必须相同。

   例如，如果生成的列表达式是 f1 + 1，若使用 1 + f1 或 将 f1 + 1 (整数表达式) 与字符串进行比较，优化器无法识别匹配项。

2. 生成列索引优化适用于 =, <, <=, >, >=, BETWEEN and IN() 运算符。

   对于=, <, <=, >, >= 可以用匹配的生成列替换任一操作数；对于 between 和 in() ，则只有第一个参数可以替换为匹配的生成列，其他参数秘必须具有相同的结果类型。对于涉及 JSON 值的比较，尚不支持 between 和 in()。

3. 生成的列必须定义为至少包含函数调用的表达式或引用其它列组成的运算表达式。

   表达式不能包含对另一列的简单引用，如 gc INT AS (f1) STORED ,是不会考虑 gc 上的索引的。

4. 对于生成列的定义来自引用了 JSON 类型列的 JSON 字符串中的属性值的表达式，创建了该生成列的索引，并想让优化器使用该生成列的索引，则从 JSON 字符串中抽取的值需要使用 JSON_UNQUOTE() 函数删除额外的双引号。

   为了直接将字符串与 JSON 函数结果进行比较，JSON 比较器会将引号删除处理，但索引查找不会做这样的操作。

   例，不要编写如下的列定义

   doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED

   而是要使用 JSON_UNQUOTE() 函数删除 JSON_EXTRACT() 返回值中的引号，如下

   doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED

   使用了 JSON_UNQUOTE() 函的定义，优化器可以检测到以下两种匹配

   ... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ...
   ... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...

   若生成列的定义没有 JSON_UNQUOTE() 函数，则优化器只能检测到上面的第一条比较语句是匹配的，才能使用该成生列的索引。

5. 如果优化器未能选择所需要的索引，则可以使用索引提示强制优化器进行不同的选择。

   SELECT  * FROM tb_name force index(index_name) WHERE ctime......

使用扩展索引

InnoDB 存储可以通过为每个二级索引附加主键列来自动扩展其性能。如下定义：

CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = InnoDB;

该表定义了联合主键( i1，i2)，还定义了 二级索引 ( k_d )，但 InnoDB 内部会扩展该二级索引并将其视为 (d，i1，i2)。

优化器在确定如何以及是否使用该索引时，会考虑扩展二级索引。这可以带来更高效的查询执行计划和更好的性能。

优化器可以使用扩展二级索引进行 ref, range, 和 index_merge 索引访问，进行松散索引扫描访问，进行连接和排序优化，以及进行 MIN() / MAX() 优化。

扩展索引演示

下面演示对扩展二级索引的使用

1. 给上面创建的表插入数据

   INSERT INTO t1 VALUES
   (1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
   (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
   (1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
   (2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
   (2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
   (3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
   (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
   (3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
   (4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
   (4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
   (5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
   (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
   (5, 5, '2002-01-01');

2. 扩展二级索引使用分析

   优化器如果没有可考虑的扩展二级索引，则只视为单列索引( k_d )，EXPLAIN 分析如下：

   mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
   +----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+
   | id | select_type | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra       |
   +----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+
   |  1 | SIMPLE      | t1    | NULL       | ref  | k_d           | k_d | 4       | const |    5 |       10 | Using where |
   +----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+

   优化器如果没有可考虑的扩展二级索引的扩展，则会将索引视为 k_d as (d, i1, i2)，这样就可以使用最左前缀索引( d, i1)来生成更好的执行计划。 EXPLAIN 分析如下：

   mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
   +----+-------------+-------+------------+------+---------------+-----+---------+-------------+------+----------+-------------+
   | id | select_type | table | partitions | type | possible_keys | key | key_len | ref         | rows | filtered | Extra       |
   +----+-------------+-------+------------+------+---------------+-----+---------+-------------+------+----------+-------------+
   |  1 | SIMPLE      | t1    | NULL       | ref  | PRIMARY,k_d   | k_d | 8       | const,const |    1 |      100 | Using index |
   +----+-------------+-------+------------+------+---------------+-----+---------+-------------+------+----------+-------------+

3. EXPLAIN 结果对比分析

key 字段表示优化器使用了二级索引 k_d，但 EXPLAIN 输出显示使用了扩展索引的执行计划更优

• key_len 从 4 个字节变为 8 个字节，表示 key 查找使用了列 d 和 i1，而不仅仅是 d 列。
• ref 从 const 变为 const, const ，是因为 key 查找使用了两个关键引用 , 而不是一个。
• rows 从 5 减少到 1，表明 InnoDB 应该检查更少的行来产生结果。
• Extra 从 Using where 变为 Using index，表示只需索引就可读取行，而不需要查询数据行中的列。

4. 使用 SHOW STATUS 查看差异

   也可以使用 SHOW STATUS 来查看是否有使用扩展二级索引两者的差异。

   刷新表缓存和清除 show status 统计信息：

   FLUSH TABLE t1;
   FLUSH STATUS;
   SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
   SHOW STATUS LIKE 'handler_read%'

   没使用扩展二级索引

   +-----------------------+-------+
   | Variable_name         | Value |
   +-----------------------+-------+
   | Handler_read_first    | 0     |
   | Handler_read_key      | 1     |
   | Handler_read_last     | 0     |
   | Handler_read_next     | 5     |
   | Handler_read_prev     | 0     |
   | Handler_read_rnd      | 0     |
   | Handler_read_rnd_next | 0     |
   +-----------------------+-------+

   有使用扩展二级索引：

   +-----------------------+-------+
   | Variable_name         | Value |
   +-----------------------+-------+
   | Handler_read_first    | 0     |
   | Handler_read_key      | 1     |
   | Handler_read_last     | 0     |
   | Handler_read_next     | 1     |
   | Handler_read_prev     | 0     |
   | Handler_read_rnd      | 0     |
   | Handler_read_rnd_next | 0     |
   +-----------------------+-------+

   从结果可以看到，Handler_read_next 由 5 减 到了 1 ，说明索引的使用效率更高。

扩展索引设置

对于 InnoDB 表的二级索引，可以通过设置系统变量 optimizer_switch 的 use_index_extensions 标志来控制优化器是否考虑其扩展索引。默认开启了使用扩展索引。

禁用扩展索引：

SET optimizer_switch = 'use_index_extensions=off';

优化器对扩展索引的使用受 index(16) 关键索引 和 key length (3072 bytes) 最大长度的限制。