MySQL如何优化索引

1. MySQL如何使用索引

索引用于快速查找具有特定列值的行。如果没有索引，MySQL必须从第一行开始，然后遍历整个表以找到相关的行。表越大，花费越多。如果表中有相关列的索引，MySQL可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。

大多数MySQL索引（PRIMARY KEY，UNIQUE，INDEX和FULLTEXT）存储在B树（B-tree）中。例外情况：空间数据类型的索引使用R树； MEMORY表还支持哈希索引。 InnoDB对FULLTEXT索引使用倒排列表。

MySQL使用索引进行以下操作：

• 快速查找与WHERE子句匹配的行
• 如果可以在多个索引之间进行选择，则MySQL通常会使用查找最小行数（最具选择性的索引）的索引
• 有多列索引（也叫“复合索引”或者“联合索引”），那么优化器可以使用索引的任何最左前缀来查找行。 例如，如果在（col1，col2，col3）上有一个三列索引，则在（col1），（col1，col2）和（col1，col2，col3）上都有索引搜索功能。
• 使用关联（join）查询从其他表中检索行时，如果声明相同的类型和大小，MySQL可以更有效地在列上使用索引。在这种情况下，如果将VARCHAR和CHAR声明为相同的大小，则认为它们相同。例如，VARCHAR（10）和CHAR（10）的大小相同，但VARCHAR（10）和CHAR（15）的大小不同。
• 对于非二进制字符串列之间的比较，两个列应使用相同的字符集
• 如果排序或分组是在可用索引的最左前缀（例如，ORDER BY key_part1，key_part2）上完成的，则对表进行排序或分组。如果在所有key部分后面都跟随有DESC，则将以相反的顺序读取key。
• 在某些情况下，MySQL可以使用索引来满足ORDER BY子句，并避免执行文件排序操作时涉及的额外排序。
• 在某些情况下，可以优化查询以检索值而无需查询数据行。（为查询提供所有必要结果的索引称为覆盖索引）如果查询仅从表中使用某些索引中包含的列，则可以从索引树中检索所选值以提高速度

最后，索引对小表的查询不太重要。当查询需要访问大多数行时，顺序读取比处理索引快。

2. 避免全表扫描

当MySQL使用全表扫描来解析查询时，EXPLAIN的输出在type列中显示ALL。 这通常在以下情况下发生：

• 表太小，以至于执行全表扫描要比索引查找要快得多。对于少于10行且行长度较短的表，这是很常见的。
• 在ON或WHERE字句中没有使用索引列。
• 将索引列与常量值进行比较，而MySQL已计算（基于索引树）常量覆盖了表的很大一部分并且表扫描会更快。
• 你正在通过另一列使用基数低的键（许多行与键值匹配）。在这种情况下，MySQL假定通过使用该键，它有可能执行许多键查找，并且表扫描会更快。

对于小表，表扫描通常是合适的，并且对性能的影响可以忽略不计。

对于大表，可以尝试以下技术，以避免优化器错误地选择表扫描：

• 用ANALYZE TABLE tbl_name来更新key的分布
• 使用FORCE INDEX来告诉MySQL相比于使用给定的索引来说，表扫描是非常昂贵的

3. 列索引

B树（B-tree）数据结构使索引可以在WHERE子句中快速找到与运算符（例如=，>，≤，BETWEEN，IN等）相对应的特定值，一组值或一系列值。

每个存储引擎都会定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，并且索引总长度至少为256个字节。

索引前缀

用col_name(N)可以创建仅使用列的前N个字符的索引。在InnoDB表中，前缀最长767字节。

全文索引

FULLTEXT索引用于全文搜索。仅InnoDB和MyISAM存储引擎支持FULLTEXT索引，并且仅支持CHAR，VARCHAR和TEXT列。索引始终在整个列上进行，并且不支持列前缀索引。

空间索引

指依据空间对象的位置和形状或空间对象之间的某种空间关系按一定的顺序排列的一种数据结构

MEMORY存储引擎上的索引

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

4. 多列索引

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

假设有一张表示这样定义的：

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