查看“MySQL 优化：优化锁操作”的源代码

[[category:MySQL]]

== 关于 ==
改善 SELECT 操作性能的最好方法是在查询中测试的一个或多个列上创建'''索引'''。索引条目的作用类似于指向表行的指针，从而使查询可以快速确定哪些行与 WHERE 子句中的条件匹配，并检索这些行的其他列值。所有 MySQL 数据类型都可以构建索引。

尽管可能会为查询中使用的每个可能的列创建索引，但不必要的索引会浪费空间和时间，使 MySQL 难以确定要使用的索引。索引还会增加插入，更新和删除的成本，因为必须更新每个索引。您必须找到适当的平衡，才能使用最佳索引集来实现快速查询。

== MySQL 如何使用索引 ==
'''索引用于快速查找具有特定列值的行'''。
* 没有索引，MySQL 必须从第一行开始，然后通读整个表以找到相关的行。桌子越大，花费越多。如果表中有相关列的索引，MySQL 可以快速确定要在数据文件中间查找的位置，而不必查看所有数据。这比顺序读取每一行要快得多。
* 大多数 MySQL 索引（“PRIMARY KEY”，“UNIQUE”，“INDEX”和“FULLTEXT”）存储在 '''B-trees''' 中。
** 例外：空间数据类型的索引使用 '''R 树'''； MEMORY 表也支持 hash indexes; InnoDB 对 FULLTEXT 索引使用反向列表【？】。


MySQL 使用索引进行以下操作：
# 快速查找与 WHERE 子句匹配的行。
# 从考虑中消除行。如果可以在多个索引之间进行选择，MySQL 通常会使用找到最少行数（最多selective索引）的索引。
# 如果表具有多列索引，则'''优化器可以使用索引的任何最左前缀来查找行'''。例如，如果在 (col1, col2, col3) 上具有三列索引，则在 (col1)，(col1, col2) 和 (col1, col2, col3) 上具有索引搜索功能。
# 执行联接时从其他表中检索行。如果声明相同的类型和大小，MySQL 可以更有效地在列上使用索引。在这种情况下，'''如果 VARCHAR 和 CHAR 声明为相同大小，则认为它们相同'''。


NOTE：
* 对于非二进制字符串列之间的比较，两个列应使用相同的字符集。例如，'''将 utf8 列与 latin1 列进行比较会排除使用索引'''。
* 如果不能不通过转换直接比较值，则'''比较不同的列可能会阻止使用索引'''（例如，将字符串列与时间或数字列进行比较）。对于数字列中的给定值（例如 1），它的值可能等于字符串列中的任何数量的值，例如'1'，' 1'，'00001'或'01.e1'。这排除了对字符串列使用任何索引的可能性。
* 查找特定索引列 key_col 的“MIN()”或“MAX()”值。这由预处理器优化，该预处理器检查索引中 key_col 之前出现的所有关键部分是否使用“WHERE key_part_N = constant”。在这种情况下，MySQL 对每个“MIN()”或“MAX()”表达式执行一次键查找，并将其替换为常量。如果所有表达式都用常量替换，查询将立即返回。例如：【？？？？？？】
*: <syntaxhighlight lang="mysql">
SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;
</syntaxhighlight>
* 如果排序或分组是在可用索引的最左前缀（例如“ORDER BY key_part1, key_part2”）上完成的，则对表进行排序或分组。【？？？？？？】
** 如果所有关键部分后跟“DESC”，则按相反顺序读取密钥。
* 在某些情况下，可以优化查询以检索值而无需查询数据行。（为查询提供所有必要结果的索引称为covering index，即'''覆盖索引'''）如果查询仅从表中使用某些索引中包含的列，则可以从索引树中检索所选值，以提高速度：【？？？？？？】
*: <syntaxhighlight lang="mysql">
SELECT key_part3 FROM tbl_name
  WHERE key_part1=1
</syntaxhighlight>


对于报表查询处理大多数或所有行的小型表或大型表，索引的重要性不那么重要。'''当查询需要访问大多数行时，顺序读取要比处理索引快'''。顺序读取可最大程度地减少磁盘查找，即使查询不需要所有行。

== 主键优化 ==
表的主键表示您在最重要的查询中使用的一列或一组列。它具有关联的索引，可提高查询性能。'''查询性能受益于“NOT NULL”优化'''【？？？】，因为它不能包含任何 NULL 值。使用 InnoDB 存储引擎，可以对表数据进行物理组织，以根据一个或多个主键列进行超快速查找和排序。

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

== 外键优化 ==
如果一个表有许多列，并且您查询了许多不同的列组合，将不常用的数据拆分为单独的表（每个表包含几列），然后通过复制数字 ID 将它们关联回主表的列可能会比较有效。这样，每个小表都可以具有一个主键来快速查找其数据，并且您可以使用联接操作查询所需的列集。根据相关数据的分布方式，查询可能执行较少的 I/O 并占用较少的缓存，因为相关的列打包在磁盘上。（为了最大化性能，查询尝试从磁盘上读取尽可能少的数据块；只有几列的表可以在每个数据块中容纳更多的行）【拆分具有很多列的表为多个表】

== 列索引 ==
索引的最常见类型涉及单个列，该列将来自该列的值的副本存储在数据结构中，从而允许快速查找具有相应列值的行。 B 树数据结构使索引可以在“WHERE”子句中快速找到与诸如“=”，“>”，“≤”，“BETWEEN”，“IN”等的运算符相对应的特定值，一组值或一系列值。【范围查询经不经过索引？？？】


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

=== '''索引前缀''' ===
使用字符串列的索引规范中的“'''col_name(N)'''”语法，可以'''创建仅使用该列的前 N 字符的索引'''。以这种方式仅索引列值的前缀可以使索引文件小得多。
* 为“BLOB”或“TEXT”列构建索引时，必须为索引指定前缀长度。例如：
*: <syntaxhighlight lang="mysql">
CREATE表test (blob_col BLOB, INDEX(blob_col(10)));
</syntaxhighlight>


前缀最长可以为 '''1000''' 个字节（对于 InnoDB 表，则为 '''767''' 个字节，除非您设置了“innodb_large_prefix”）。
# '''前缀限制以字节为单位'''，
# 而“CREATE TABLE”，“ALTER TABLE”和“CREATE INDEX”语句中的前缀长度被解释为：
## 非二进制字符串类型（CHAR，VARCHAR，TEXT）的'''字符数'''
## 二进制字符串类型（BINARY，VARBINARY，BLOB的字节数））的'''字节数'''


如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。【？？？】

=== FULLTEXT 索引 ===
FULLTEXT 索引用于全文搜索。
* 仅 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引，并且仅支持 '''CHAR'''，'''VARCHAR''' 和 '''TEXT''' 列（非二进制字符串类型）。
* 索引始终在整个列上进行，并且不支持列前缀索引。


优化适用于针对单个 InnoDB 表的某些类型的 FULLTEXT 查询。具有以下 Feature 的查询特别有效：
* FULLTEXT 查询仅返回文档 ID 或 文档ID和搜索排名。
* FULLTEXT 查询以分数的降序对匹配行进行排序，并应用“LIMIT”子句以获取前 N 个匹配行。
** 为了应用此优化，必须没有“WHERE”子句，并且只有一个“ORDER BY”子句按降序排列。
* FULLTEXT 查询仅检索与搜索词匹配的行的“COUNT(*)”值，而没有其他“WHERE”子句。将“WHERE”子句编码为“'''WHERE MATCH(text) AGAINST ('other_text')'''”，而无需任何“> 0”比较运算符。


对于包含全文表达式的查询，MySQL 在查询执行的优化阶段评估这些表达式。优化器不仅查看全文表达式并进行估计，而且还在制定执行计划的过程中对它们进行评估。
: 此行为的含义是，对于全文查询，EXPLAIN 通常比在优化阶段未进行表达式求值的非全文查询的慢。
* 全文查询的 EXPLAIN 可能会由于优化期间发生匹配而在 Extra 列中显示“Select tables optimized away”；在这种情况下，以后执行期间无需进行表访问。

=== 空间索引 ===
您可以在空间数据类型上创建索引。 
# MyISAM 和 InnoDB 支持有关空间类型的 '''R 树'''索引。
# 其他存储引擎使用 B 树来索引空间类型（ARCHIVE除外，不支持空间类型索引）。

=== MEMORY 存储引擎中的索引 ===
MEMORY 存储引擎默认使用'''HASH索引'''，但也支持 BTREE索引。

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


MySQL 可以将多列索引用于测试索引中所有列的查询，或者仅测试第一列，前两列，前三列等等的查询。
* 如果在索引定义中以正确的顺序指定列，则单个复合索引可以加快对同一表的几种查询。

多列索引可以被认为是排序数组，其行包含通过串联索引列的值而创建的值。


'''Note：'''【？？？】<br/>
作为复合索引的替代方法，您可以'''引入基于其他列信息“散列”的列'''。如果此列短，合理唯一并且已构建索引，则它可能比许多列上的“宽”索引快。在 MySQL 中，使用此额外的列非常容易：
<syntaxhighlight lang="mysql">
SELECT * FROM tbl_name
WHERE hash_col=MD5(CONCAT(val1,val2))
AND col1=val1 AND col2=val2;
</syntaxhighlight>


'''示例：'''<br/>
假设一个表具有以下规范：
<syntaxhighlight lang="mysql">
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)
);
</syntaxhighlight>
name 索引是 last_name 和 first_name 列的索引。该索引可用于查询中的查询，这些查询指定 last_name 和 first_name 值组合的已知范围内的值。'''它也可以用于仅指定 last_name 值的查询，因为该列是索引的最左前缀'''【但不能用于仅指定 first_name 值的查询，因为其不能作为'''索引前缀'''】。
:因此，name 索引用于以下查询中的查找：
<syntaxhighlight lang="mysql">
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';
</syntaxhighlight>
但是，name 索引不能用于查找以下查询中：【因为：first_name 列不能作为 name 索引的索引前缀】
<syntaxhighlight lang="mysql">
SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';
</syntaxhighlight>


假设您发出以下 SELECT 语句：
<syntaxhighlight lang="mysql">
SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;
</syntaxhighlight>
如果 col1 和 col2 上存在多列索引，则可以直接获取适当的行。如果 col1 和 col2 上存在单独的单列索引，那么优化器将尝试使用'''索引合并优化'''，或者通过确定哪个索引排除更多行并使用该索引来获取行来查找'''限制性最强的索引'''。


'''如果表具有多列索引，那么优化器可以使用索引的任何最左前缀来查找行'''。
: 例如，如果在(col1, col2, col3)上具有三列索引，则在(col1)，(col1, col2)和(col1, col2, col3)上具有索引搜索功能。
如果列不构成索引的最左前缀，则 MySQL 无法使用索引执行查找。假设您在这里显示了 SELECT 语句：
<syntaxhighlight lang="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;
</syntaxhighlight>
如果 (col1, col2, col3) 上存在索引，则仅前两个查询使用该索引。第三和第四查询确实涉及索引列，但是不使用索引来执行查找，因为 (col2) 和 (col2, col3) 不是 (col1, col2, col3) 的最左前缀。

== 验证索引使用情况 ==
使用 '''EXPLAIN''' 语句来检查所有查询是否真的使用您在表中创建的索引。

== InnoDB 和 MyISAM 索引统计信息收集 ==

== B 树和哈希索引的比较 ==

== 索引 extensions 的使用 ==

== 优化器对生成的列索引的使用 ==

== 从 TIMESTAMP 列进行索引查找 ==