优化-索引

提高SELECT操作性能的最佳方法是在查询中测试的一个或多个列上创建索引。索引项的作用类似于指向表行的指针，允许查询快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。所有MySQL数据类型都可以被索引。

尽管为查询中使用的每个可能的列创建索引很有诱惑力，但不必要的索引会浪费MySQL确定要使用哪些索引的空间和时间。索引还增加了插入、更新和删除的成本，因为每个索引都必须更新。必须找到正确的平衡点，才能使用最佳索引集实现快速查询。

MySQL如何使用索引

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

大多数MySQL索引（PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT）都存储在B树中。例外情况：空间数据类型的索引使用R树；内存表也支持哈希索引；InnoDB使用倒排列表作为全文索引。

MySQL对这些操作使用索引：

• 快速查找与WHERE子句匹配的行。
• 会思考排除行。如果在多个索引之间有一个选择，MySQL通常使用查找最小行数（最具选择性的索引）的索引。
• 如果表具有多列索引，优化器可以使用索引的任何最左边的前列来查找行。例如，如果在（col1、col2、col3）上有三列索引，则可以在(col1), (col1, col2), and (col1, col2, col3)上用索引搜索功能。
• 在执行联接时从其他表检索行。如果列声明为相同的类型和大小，MySQL可以更有效地对它们使用索引。在这个上下文中，如果VARCHAR和CHAR声明为相同的大小，则它们被认为是相同的。例如，VARCHAR（10）和CHAR（10）大小相同，但VARCHAR（10）和CHAR（15）大小不同。

对于非二进制字符串列之间的比较，两列应使用相同的字符集。例如，将utf8列与latin1列进行比较就不允许使用索引。

如果在不进行转换的情况下无法直接比较值，则比较不同列（例如，将字符串列与时态列或数值列进行比较）可能会阻止索引的使用。对于给定的值（如数字列中的1），它可以与字符串列中的任意数量的值（如“1”、“1”、“00001”或“01.e1”）进行比较。这就排除了对字符串列使用任何索引的可能性。

• 查找特定索引列 key_col的MIN（）或MAX（）值。这是一个预处理器优化的，该预处理器检查是否在索引中 key_col 之前发生的所有键部分上使用WHERE key_part_N=constant。在本例中，MySQL对每个MIN（）或MAX（）表达式执行单键查找，并将其替换为常量。如果所有表达式都替换为常量，则查询将立即返回。例如：

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

• 如果排序或分组是在可用索引的最左边前置代号上完成的（例如，ORDER BY key part1，key part2），则对表进行排序或分组。如果所有键部分后面都跟着DESC，则按相反的顺序读取键。
• 在某些情况下，查询可以优化检索值，而无需查询数据行。（为查询提供所有必要结果的索引称为覆盖索引。）如果查询仅用从某个表中使用某些索引的列，则可以从索引树中检索选定的值以获得更快的速度：

SELECT key_part3 FROM tbl_name
  WHERE key_part1=1

索引对于小表，或报表查询处理大部分或所有行的大表上的查询不太重要。当查询需要访问大多数行时，按顺序读取比通过索引读取要快。顺序读取使磁盘查找最小化，即使查询不需要所有行。

Primary Key 优化

表的主键表示在最重要的查询中使用的列或列集。它有一个相关的索引，用于快速查询性能。查询性能得益于非空优化，因为它不能包含任何空值。使用InnoDB存储引擎，表数据在物理上被组织成基于主键列的超快速查找和排序。

如果表很大且很重要，但没有一个明显的列或一组列用作主键，则可以创建一个单独的列，并使用自动增量值作为主键。当您使用外键联接表时，这些唯一的id可以用作指向其他表中相应行的指针。

Foreign Key优化

如果一个表有许多列，并且您查询了许多不同的列组合，那么将不常用的数据拆分为单独的表，每个表有几列，并通过从主表中复制数字ID列将它们与主表关联起来，可能会更有效。这样，每个小表都可以有一个主键来快速查找其数据，并且您可以使用join操作只查询所需的一组列。根据数据的分布方式，查询可能执行更少的I/O并占用更少的缓存，因为相关列被一起打包在磁盘上。（为了使性能最大化，查询尽量从磁盘读取尽可能少的数据块；只有几个列的表可以在每个数据块中拟合更多的行。）

Column Indexes

最常见的索引类型涉及一个列，它将该列中的值的副本存储在数据结构中，允许快速查找具有相应列值的行。B树数据结构允许索引快速查找特定值、一组值或一系列值，这些值对应于WHERE子句中的运算符，如=、>、≤、BETWEEN、IN等。

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

Index Prefixes

在字符串列上使用索引规范中的col_name（N）语法，可以创建只使用列的前N个字符的索引。以这种只使用列值的前缀的方式的索引可以使索引文件更小。索引BLOB或文本列时，必须指定索引的前缀长度。例如：

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

前缀最长可达1000字节（InnoDB表为767字节，除非设置了 innodb_large_prefix）。

Note

前缀限制以字节为单位，而CREATE TABLE、ALTER TABLE和CREATE INDEX语句中的前缀长度被解释为非二进制字符串类型（CHAR、VARCHAR、TEXT）的字符数和二进制字符串类型（binary、VARBINARY、BLOB）的字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑此问题。

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

FULLTEXT Indexes

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

优化应用于针对单个InnoDB表的某些类型的全文查询。具有这些特征的查询特别有效：

• 只返回文档ID、或文档ID和搜索列组的全文查询。
• 按分数降序排列匹配行并应用LIMIT子句获取前N个匹配行的全文查询。要应用此优化，不能有WHERE子句，并且只能有一个降序的ORDER BY子句。
• 全文查询，只检索与搜索词匹配的行的COUNT（*）值，不检索其他WHERE子句。将WHERE子句编码为 WHERE MATCH(text) AGAINST ('other_text')，不使用任何>0的比较运算符。

对于包含全文表达式的查询，MySQL在查询执行的优化阶段计算这些表达式。优化器不只是查看全文表达式并进行估算，它实际上是在开发执行计划的过程中对它们进行估算的。

这种行为的一个含义是，对于全文查询，EXPLAIN通常比对于优化阶段不进行表达式求值的非全文查询要慢。

EXPLAIN for full-text查询可能会在额外的列中显示经过优化的Select表，因为在优化过程中发生匹配；在这种情况下，在以后的执行过程中不需要进行表访问。

空间索引

可以在空间数据类型上创建索引。MyISAM和InnoDB支持空间类型的R树索引。其他存储引擎使用B-树索引空间类型（除了 ARCHIVE，它不支持空间类型索引）。

内存存储引擎中的索引

默认情况下，内存存储引擎使用哈希索引，但也支持BTREE索引。

Multiple-Column Indexes

MySQL可以创建复合索引（即多个列上的索引）。索引最多可包含16列。对于某些数据类型，可以索引列的前缀

MySQL可以对测试索引中所有列的查询使用多列索引，也可以对只测试第一列、前两列、前三列等的查询使用多列索引。如果在索引定义中按正确的顺序指定列，则单个组合索引可以加快对同一表的多种查询。

多列索引可以被视为排序数组，其中的行包含通过连接索引列的值而创建的值。

Note

作为复合索引的替代，可以引入基于其他列的信息进行“散列”的列。如果此列较短、相当唯一且已编制索引，则它可能比许多列上的“宽”索引快。在MySQL中，很容易使用这个额外的列：

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

名称索引是“姓氏”和“名字”列上的索引。索引可用于查询中的查找，这些查询为姓氏和姓氏值的组合指定已知范围内的值。它还可以用于仅指定姓氏值的查询，因为该列是索引的最左边前缀（如本节后面所述）。因此，名称索引用于以下查询中的查找：

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

但是，名称索引不用于以下查询中的查找：

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

假设您发出以下SELECT语句：

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

如果在COL1和COL2上存在多个列索引，则可以直接获取适当的行。如果在COL1和COL2上存在单独的单列索引，则优化器尝试使用索引合并优化（参见节2.2.1.3，“索引合并优化”），或者尝试通过确定哪个索引排除更多行并使用该索引来获取行来找到最有限制性的索引。

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

如果列不构成索引最左边的前缀，MySQL将无法使用索引执行查找。假设这里显示了SELECT语句：

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）最左边的前缀。

验证索引使用情况

始终检查所有查询是否真的使用您在表中创建的索引。使用该 EXPLAIN语句，如第8.8.1节“使用EXPLAIN优化查询”中所述。

 InnoDB和MyISAM索引统计信息收集

存储引擎收集有关表的统计信息，以供优化器使用。表统计信息基于值组，其中值组是一组具有相同键前缀值的行。出于优化目的，重要的统计数据是平均值组的大小。

MySQL通过以下方式使用平均值组大小：

• 估计每次ref访问 必须读取多少行
• 估计部分联接将产生多少行；也就是说，这种形式的操作将产生的行数：

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

随着索引的平均值组大小的增加，索引对于这两个目的的用处较小，因为每次查找的平均行数会增加：为了使索引有利于优化，最好每个索引值针对表中的少量行。当给定的索引值产生大量行时，该索引就不那么有用，MySQL也不太可能使用它。

平均值组大小与表基数有关，表基数是值组的数目。SHOW INDEX语句显示基于N/S的基数值，其中N是表中的行数，S是平均值组大小。该比率在表中产生近似数量的值组。

对于基于<=>比较运算符的联接，对NULL的处理与对任何其他值的处理没有区别：NULL<=>NULL，就像对任何其他N的N<=>N一样。

但是，对于基于=运算符的联接，空值与非空值不同：当expr1或expr2（或两者）为空时，expr1=expr2不为真。这会影响tbl_name.key=expr格式比较的ref访问：如果expr的当前值为空，MySQL将不访问表，因为比较不能为真。

对于InnoDB和MyISAM表，可以分别通过 innodb_stats_method and myisam_stats_method 系统变量来控制表统计信息的收集。这些变量有三个可能的值，它们的区别如下：

• 当变量设置为nulls_equal时，所有的空值都被视为相同的（即，它们都形成一个值组）。

如果空值组大小远高于平均非空值组大小，则此方法会将平均值组大小向上倾斜。这使得在优化器看来，索引的用处比查找非空值的联接的用处要小。因此，nulls_equal方法可能会导致优化器在需要时不使用索引进行ref访问。

• 当变量设置为 nulls_unequal时，将不认为空值相同。相反，每个空值形成一个大小为1的单独值组。

如果有许多空值，此方法会向下倾斜平均值组大小。如果平均非空值组大小较大，将每个空值作为大小为1的组进行计数会导致优化器高估查找非空值的联接的索引值。因此，当其他方法可能更好时， nulls_unequal的方法可能会导致优化器使用此索引进行ref查找。

• 当变量设置为nulls_ignored时，将忽略空值。

如果您倾向于使用多个使用<=>而不是=的连接，那么空值在比较中并不特殊，一个空值等于另一个空值。在这种情况下，nulls_equal是适当的统计方法。

。。。。