MySQL 优化：优化锁操作

来自Wikioe
Eijux（讨论 | 贡献）2021年4月26日 (一) 18:17的版本 （建立内容为“category:MySQL == 关于 == 改善 SELECT 操作性能的最好方法是在查询中测试的一个或多个列上创建'''索引'''。索引条目的作用…”的新页面）
(差异) ←上一版本 | 最后版本 (差异) | 下一版本→ (差异)
跳到导航 跳到搜索


关于

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

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

MySQL 如何使用索引

索引用于快速查找具有特定列值的行。

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


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

  1. 快速查找与 WHERE 子句匹配的行。
  2. 从考虑中消除行。如果可以在多个索引之间进行选择，MySQL 通常会使用找到最少行数（最多selective索引）的索引。
  3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行。例如，如果在 (col1, col2, col3) 上具有三列索引，则在 (col1)，(col1, col2) 和 (col1, col2, col3) 上具有索引搜索功能。
  4. 执行联接时从其他表中检索行。如果声明相同的类型和大小，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()”表达式执行一次键查找，并将其替换为常量。如果所有表达式都用常量替换，查询将立即返回。例如：【？？？？？？】 
    SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;
  • 如果排序或分组是在可用索引的最左前缀（例如“ORDER BY key_part1, key_part2”）上完成的，则对表进行排序或分组。【？？？？？？】
    • 如果所有关键部分后跟“DESC”，则按相反顺序读取密钥。
  • 在某些情况下，可以优化查询以检索值而无需查询数据行。（为查询提供所有必要结果的索引称为covering index，即覆盖索引）如果查询仅从表中使用某些索引中包含的列，则可以从索引树中检索所选值，以提高速度：【？？？？？？】 
    SELECT key_part3 FROM tbl_name
  WHERE key_part1=1


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

主键优化

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

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

外键优化

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

列索引

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


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

索引前缀

使用字符串列的索引规范中的“col_name(N)”语法，可以创建仅使用该列的前 N 字符的索引。以这种方式仅索引列值的前缀可以使索引文件小得多。

  • 为“BLOB”或“TEXT”列构建索引时，必须为索引指定前缀长度。例如： 
    CREATE表test (blob_col BLOB, INDEX(blob_col(10)));


前缀最长可以为 1000 个字节（对于 InnoDB 表，则为 767 个字节，除非您设置了“innodb_large_prefix”）。

  1. 前缀限制以字节为单位，
  2. 而“CREATE TABLE”，“ALTER TABLE”和“CREATE INDEX”语句中的前缀长度被解释为：
    1. 非二进制字符串类型（CHAR，VARCHAR，TEXT）的字符数
    2. 二进制字符串类型（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”；在这种情况下，以后执行期间无需进行表访问。

空间索引

您可以在空间数据类型上创建索引。

  1. MyISAM 和 InnoDB 支持有关空间类型的 R 树索引。
  2. 其他存储引擎使用 B 树来索引空间类型（ARCHIVE除外，不支持空间类型索引）。

MEMORY 存储引擎中的索引

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

复合索引

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

name 索引是 last_name 和 first_name 列的索引。该索引可用于查询中的查询，这些查询指定 last_name 和 first_name 值组合的已知范围内的值。它也可以用于仅指定 last_name 值的查询，因为该列是索引的最左前缀【但不能用于仅指定 first_name 值的查询，因为其不能作为索引前缀】。

因此，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';

但是，name 索引不能用于查找以下查询中：【因为：first_name 列不能作为 name 索引的索引前缀】 

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 上存在单独的单列索引，那么优化器将尝试使用索引合并优化，或者通过确定哪个索引排除更多行并使用该索引来获取行来查找限制性最强的索引。


如果表具有多列索引，那么优化器可以使用索引的任何最左前缀来查找行。

例如，如果在(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 语句来检查所有查询是否真的使用您在表中创建的索引。

InnoDB 和 MyISAM 索引统计信息收集

B 树和哈希索引的比较

索引 extensions 的使用

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

从 TIMESTAMP 列进行索引查找

取自“http://wiki.eijux.com/index.php?title=MySQL_优化：优化锁操作&oldid=2726”