MYSQL 索引优化

优化和索引

提升SELECT 的最好方式是使用索引。索引条目作为表数据行的指针，使得查询能够很快的定位到所要查找的数据。所有的MySQL数据类型都可以创建索引。

不必要的索引会浪费存储空间，同时也会增加数据更新成本（数据更新时，索引也相应的需要被更新）。

MySQL 使用索引

索引用于快速定位特定值的表数据行。如果不使用索引，MySQL则需要从第一个数据行开始查找整个数据表，直到找到要查找的数据行，表越大，查找成本越高。如果查找条件的列存在索引，那么MySQL就可以快速定位需要查找的数据位置。

大部分MySQL 索引(PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) 都是以 B-trees形式存储。例外情况：基于R-trees的空间数据索引；MEMORY引擎的HASH索引；InnoDB 的基于倒序列表的全文索引。

MySQL 使用索引的操作情景：

  • Where条件查找。
  • 快速排序干扰数据。使用最具选择行的索引。
  • 最左前缀索引查找，如：在(col1, col2, col3)存在索引，则可以使用包括 (col1), (col1, col2)及 (col1, col2, col3)索引来进行查询。
  • 联合查询，从联合表查询数据。相同类型和大小的索引列使用更加高效。例如， VARCHAR and CHAR 列设定大小相同时，会被认为相同类型，如 VARCHAR(10) 和 CHAR(10)。

比较的列必须具有相同的字符类型。

  • 查找索引列的MIN() 或 MAX() 值。
  • GROUP BY 或者 ORDER BY 使用的索引列使用相同的排序方向。
  • 使用覆盖索引查询。

SELECT MIN(key_part2),MAX(key_part2)

  FROM tbl_name WHERE key_part1=10;

SELECT key_part3 FROM tbl_name

  WHERE key_part1=1

索引对于小表查询，或者需要访问大部分数据的大表（此时全表扫描比较高效）作用很小。

 

主键优化

主键即表查询应用列。主键上有相应的索引，用于快速查询。主键要求不能为null。InnoDB 引擎物理上以一种有助于快速查询的方式存储。

如果表比较大，且很重要，但是没有特别适合做主键的列，则，应该创建一个额外的列，以auto-increment方式增长，作为主键。可以作为联合查询的外键。

外键优化

如果表有很多的列，查询也有很多的组合，那么有必要将使用率较低的列划分到关联的不同表中，并使用主表主键进行关联。这样，每个小表都有个主键来提供快速查询使用，对于综合查询，可以使用相关的表进行联合查询。数据存储分布的不同及具体数据的组织形式不同，会对查询缓存需求及I/O访问产生较大影响。为了尽可能的提高性能，应该尽量减少磁盘I/O，一些具有较少列的表可以尽量一次将较多的行数据查询到内存。

列索引

最常见的索引类型通常涉及单个数据列，索引以一定的数据结构存储一列的数据，这样就可以快速定位这一列的某一特定值。B-tree 数据结构提供了对特定值，值列表，范围值包括=, >, ≤, BETWEEN, IN等在内的条件查询的快速定位。

不同存储引擎对于但表最大索引数及索引长度都有规定。所有的引擎都支持至少但表16个索引及索引总长度至少256 bytes的约定。一些引擎的限制限度宽泛。

前缀索引（Index Prefixes）

索引定义中，对于string类型列使用 col_name(N) 语句，可以创建只使用列前N 个字符作为索引数据。只使用列前部分数据作为索引能够减少索引空间占用。例如，对于BLOB 或者 TEXT 类型列，必须使用这种方式：

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

前缀最多支持使用长度为1000 bytes (InnoDB 最多支持767 bytes 长度，除非另外设置innodb_large_prefix 变量).

Note

前缀限制以bytes计量。但是，对于CREATE TABLE, ALTER TABLE, 和 CREATE INDEX 语句中，对于非二进制类型(CHAR, VARCHAR, TEXT) 是以字符数为计量限制的。对于二进制类型字符类型 (BINARY, VARBINARY, BLOB)，则是以bytes为计量限制的。

全文索引（FULLTEXT Indexes）

FULLTEXT 全文索引用以支持全文搜索。只有InnoDB 及 MyISAM 存储引擎对于 CHAR, VARCHAR和TEXT类型列支持全文索引。索引只能创建在列全部的值上，而不能使用列部分值。

对于Innodb单表上的特定类型的全文索引，MySQL会有些优化以优化查询：

  • FULLTEXT 查询只返回文档ID，或者文档ID和查询评级。
  • FULLTEXT 查询DESC 排序，并使用LIMIT 限制条件。对于这种类型优化，不能使用 WHERE 条件，并且只能有一个ORDER BY 条件。
  • FULLTEXT 查询 COUNT(*)结果，没有 WHERE 条件。或者使用 WHERE MATCH(text) AGAINST ('other_text')类型条件，不能有任何 > 0 的对比条件。

对于这种类型的基于全文查询，MySQL在执行查询优化的过程中进行判别。

全文搜索比非全文搜索要慢，因为多出了这样一个判断阶段。

用执行计划观察执行全文搜索的查询，当匹配数据出现在优化极端时，Extra列会有Select tables optimized away 的信息提示。

空间索引（Spatial Indexes）

创建在空间类型数据上的索引。MyISAM 和 InnoDB 支持空间类型数据上创建R-tree 结构类型索引。其它引擎索引结构为B-trees。(ARCHIVE引擎不支持空间索引)。

MEMORY 存储引擎上的索引

MEMORY 存储引擎好似用HASH 结构索引，同时也支持BTREE 结构索引。

 

多列索引

MySQL可以创建组合索引（创建于多列上的索引），一个索引最多包含16列。

MySQL可以使用多列索引进行查询，基于索引多列匹配，或者只匹配索引包含的第一列，前两列… 前n列。合理的排序，组合索引列，使之满足大多数的查询需求。

多列索引可以看作为排序数组，数组的每一行包含相关索引列的值组合。

Note

区别于多列索引，可以使用一种基于其它列hash值的列，如果这个hash列，足够短，具备合理的选择性。使用此列作为索引要比使用其所基于的多列更高效。使用如下：（限制比较大）

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

假定如下的表定义：

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 列的查询（索引前缀）。如下查询：

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

如下查询，则无法使用索引：

SELECT * FROM test WHERE first_name='Michael';

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

假定有如下查询：

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

如果有基于col1 和col2的多列索引，那么查询就可以直接使用索引。如果只有分别基于col1 和 col2的单列索引，优化器会尝试使用索引合并优化，或者尝试使用更具筛选性（能够排除更多的无关数据行的）的索引。

多列索引，可以使用任何的前缀索引来进行查询。如基于(col1, col2, col3)的索引，可使用的索引形式如下： (col1), (col1, col2), 和 (col1, col2, col3)。

如下：第1，2个查询可以使用此索引，第3，4个不支持使用此索引。

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;

InnoDB 和 MyISAM 索引统计数据收集

存储引擎会收集表的统计信息以供优化器使用。表统计数据是基于同一索引前缀值的行数据集合集。对于优化器来说，重要的统计数据为平均值集合的大小。

应用如下：

  • 预估每个ref 访问需要读取多少行数据。
  • 预估每个联合查询会产生多少条记录。也就是说，如下的操作会产生多少行数据：

(...) JOIN tbl_name ON tbl_name.key = expr

如果一个索引导致平均值集合的大小增加（索引的一个值对应数据表中的记录数），那么此索引可用性降低。为了优化的需求，最好每个索引值只对应小范围数据行。

平均值集合大小和表的基数相关，即值集合的总大小。SHOW INDEX FROM语句展示了基于N/S 的基数值， N 代表表行数，S 代表平均值集合大小。比值代表表中值集合的数量。

对于联合查询中的<=> 比较符，NULL 和其它值N（其它任何类型）无异。NULL <=> NULL 同处理 N <=> N 。

然而，联合查询中的 = 操作符，对于NULL的处理则不同，对于条件 expr1 = expr2 ， expr1 或者 expr2 或者都为NULL时，条件都不成立。这一情况影响ref （非唯一索引查找）类型访问中类似tbl_name.key = expr形式的条件查询，MySQL在条件值为expr 为 NULL时，将不会再访问表数据，因为条件永远不成立。

对于 = 比较符，无论表中有多少个NULL 值，为了优化，相关索引值集合大小为非NULL 值集合。然而，MySQL将不再收集和使用平均只集合大小。

对于InnoDB 和MyISAM 类型表，可以通过变量innodb_stats_method 和 myisam_stats_method 控制表统计信息的收集。变量值集合如下：

  • nulls_equal：所有的 NULL 值作等值对待（作为一个值集合）。

如果NULL 值的集合大小远远大于非NULL值集合大小时，这种配置将会增大平均值集合大小。使得在进行非NULL条件联合查询时，索引对于优化器看起来不如它实际有用。从而导致对于ref 访问，优化器将不再使用原本应该使用的索引。

  • nulls_unequal,：每个NULL 值都做不等值对待，形成N个不同的NULL值集合（大小为1）

如果表中有过多的NULL 值，将会降低整体的平均值集合大小。此时，如果非NULL值集合大小非常大，那么就会造成优化器高估索引在非NULL条件查询的可用性。从而导致优化器在ref访问时，使用到不合适索引。

  • nulls_ignored： NULL 值忽略。values are ignored.

If you tend to use many joins that use <=> rather than =, NULL values are not special in comparisons and one NULL is equal to another. In this case, nulls_equal is the appropriate statistics method.

innodb_stats_method 系统变量是全局性的； myisam_stats_method 则具有全局和会话级两个值。全局值影响相应的存储引擎对表统计数据的收集。会话级的值影响当前客户端连接的统计数据收集。也就是说，会话级设置可以在不影响其它客户端的情况下重新生成表的统计数据。

重新生成MyISAM 表统计数据，可以使用如下方法：

innodb_stats_method 和 myisam_stats_method的一些使用说明：

  • 可以指定触发，但是MySQL 的自动收集仍然在进行。
  • 统计数据产生的原因无从得知。
  • 只有InnoDB 和MyISAM 类型表有这些变量类型。其它引擎只一种类似 nulls_equal。

 

B-Tree及Hash 索引对比

B-Tree 索引特点

B-tree索引应用：=, >, >=, <, <=, 及 BETWEEN，对比值为常量且不以通配符起始的LIKE 条件，如下：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

In the first statement, only rows with 'Patrick' <= key_col < 'Patricl' are considered. In the second statement, only rows with 'Pat' <= key_col < 'Pau' are considered.

一下查询不使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';//条件值包含通配符

SELECT * FROM tbl_name WHERE key_col LIKE other_col; //非常量值

如果 ... LIKE '%string%' 条件 string 不超过3个字符串。MySQL 会使用Turbo Boyer-Moore 字符串查询算法来进行查询。

条件 col_name IS NULL 当col_name 上有索引时会使用索引。

对于含有多个 AND 组合的条件，只有当每个 AND 组都使用了索引或者索引前缀，查询才会使用索引。如下，使用索引场景：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */

... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1='hello'" */

... WHERE index_part1='hello' AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

如下将不使用索引：

    /* index_part1 is not used */

... WHERE index_part2=1 AND index_part3=2

 

    /*  Index is not used in both parts of the WHERE clause  */

... WHERE index=1 OR A=10

 

    /* No index spans all rows  */

... WHERE index_part1=1 OR index_part2=10

还有一种情况是有可用的索引，但是索引导致的数据扫描效率低于全表扫描查询，则不会使用索引。但是如果有使用LIMIT 限制，则总会使用索引。

Hash 索引特点

  • 只能等值或者不等值匹配（= 或 <=> ）但很快。使用此类查询的应用一般称之为key-value 存储。
  • 无法使用HASH索引优化ORDER BY 操作。(这种类型的索引无法用于查询排序)
  • MySQL无法通过此索引估计范围条件间的数据行（优化器对于范围查询的优化（选择索引））。
  • 只能使用全部的健来匹配查询（区别于B-tree 类型索引的最左前缀原则）

 

索引扩展

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)主索引，及基于列 (d)的二级索引，但是在内部，InnoDB 会自动将二级索引扩展为基于 (d, i1, i2)的二级索引。

优化器会自动考虑二级索引扩展包含的主键列。

优化器可以针对ref, range, 和index_merge 类型索引访问，松散索引扫描，联合查询和排序优化及MIN()/MAX() 优化使用扩展二级索引。

如下展示了优化器使用扩展二级索引与否对执行计划的影响。假定 t1 数据填充如下：

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

有如下查询：

EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

因为条件设计i1，d，主键索引无法包含，所以无法使用主键索引。相反，二级索引扩展后则正好匹配条件列。所以会使用扩展二级索引。是否使用扩展索引会影响执行计划的输出。

如果优化器不考虑索引扩展，那么二级索引则只包含列d，那么执行计划输出如下：

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: ref

possible_keys: PRIMARY,k_d

          key: k_d

      key_len: 4

          ref: const

         rows: 5

        Extra: Using where; Using index

优化器考虑索引扩展，则认定二级索引为 (d, i1, i2)，则根据最左前缀原则，使用二级索引，执行计划如下：

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

         type: ref

possible_keys: PRIMARY,k_d

          key: k_d

      key_len: 8

          ref: const,const

         rows: 1

        Extra: Using index

两种情况下，执行计划都指出将会使用二级索引，但是在考虑索引扩展时，会有如下的优化：

  • key_len 从4 变为8，表示查询使用了列d 和i1, 而不只是d（d和i1都是int类型，长度4）。
  • ref 值从const 变为 const,const ，因为使用了索引的两部分。
  • rows 扫描数从5 变为1，意味着InnoDB 引擎只需扫描较少的行就能匹配到最终结果。
  • Extra 值从Using where; Using index 变为 Using index，这意味着结果可以只通过索引查询来获取，而不需读取额外的数据列。

扩展索引对 SHOW STATUS的影响：

FLUSH TABLE t1;

FLUSH STATUS;

SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';

SHOW STATUS LIKE 'handler_read%'

前置语句FLUSH TABLES 及 FLUSH STATUS 会将刷新表缓存及清除计数器状态。

不使用索引扩展情况下，SHOW STATUS 输入如下：=

+-----------------------+-------+

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

+-----------------------+-------+

使用索引扩展情况下，SHOW STATUS 输入如下结果：Handler_read_next 从 5 减少到1，表明索引的高效使用。

+-----------------------+-------+

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

+-----------------------+-------+

The use_index_extensions flag of the通过设置系统变量 optimizer_switch 值中的use_index_extensions  标志来InnoDB 表扩展索引优化。默认情况下 use_index_extensions 启用。 可以通过如下设置：

SET optimizer_switch = 'use_index_extensions=off';

扩展索引的使用也需要服从索引列限制及索引长度限制。

 

索引优化使用

MySQL支持生成列上的索引，如下：

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

生成列gc 定义为 f1 + 1，其上定义了索引，优化器在执行查询时会考虑使用其列上的索引。如下。条件中使用生成列gc，优化器会评估其上索引的效率：

SELECT * FROM t1 WHERE gc > 9;

优化器可以使用生成列上的索引生成执行计划。即使是在没有直接的使用生成列名称的情况下，这种情况发生在WHERE, ORDER BY, 或者 GROUP BY 条件中涉及满足生成列定义的表达式的情景。如下查询，没有直接使用gc列，但是使用的表达式f1+1 符合gc的定义，所以会使用gc上的索引：

SELECT * FROM t1 WHERE f1 + 1 > 9;

优化器会识别出f1 + 1 符合gc列的定义，且gc上存在索引，所以在执行计划构建时就会考虑此索引，如下执行计划输出：

mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: t1

   partitions: NULL

         type: range

possible_keys: gc

          key: gc

      key_len: 5

          ref: NULL

         rows: 1

     filtered: 100.00

        Extra: Using index condition

事实上，优化器已经将表达式f1 + 1 替换为了相应的生成列。这可以通过 SHOW WARNINGS语句在扩展执行计划信息中看出，如下：

mysql> SHOW WARNINGS\G

*************************** 1. row ***************************

  Level: Note

   Code: 1003

Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`

         AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)

使用生成列索引的限制及条件：

  • 匹配查询表达式和生成列定义，两者必须完全一致，且结果类型一致。例如，对于生成列定义f1 + 1 和查询条件1 + f1是不一样的；假如f1 + 1 结果类型为整型，生成列类型为string，那么这两者也是不匹配的。
  • 这种优化包含=, <, <=, >, >=, BETWEEN, 和IN()比较符。

对于BETWEEN 和IN()以外的操作符，每个操作符都可以用一个匹配的生成列代替。对于BETWEEN 和 IN()操作符，只有第一个参数可以被生成列替代，同时另外一个参数必须具有相同的结果类型。BETWEEN 和 IN() 暂时不支持JSON 类型值比较。

  • 生成列的定义表达式必须包含至少一个函数操作，或者包含之前提到的操作符。表达式不能单纯的使用另外一个列的引用。例如 gc INT AS (f1) STORED 只包含另外一个列的引用，那么优化器就不会考虑此列上的索引。
  • 对于查询条件中生成列和使用JSON函数产生的string类型的有引号字符串值对比，JSON_UNQUOTE() 可以用来去除JSON函数产生的引号。如下：

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

需要变更为：

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

则，对于一下查询，优化器可以进行生成列匹配：

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

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

如果生成列定义不使用 JSON_UNQUOTE() ，那么只能匹配以上查询的第一个。

  • 当优化器没有选择我们希望的索引，那么我们也可以通过其它方式使强制调整优化器选择。

附加订阅

 

posted @ 2019-01-04 11:18  WindWant  阅读(1865)  评论(0)  编辑  收藏  举报
文章精选列表