总结了MySQL优化索引的20条建议！必读

11 个月前

已认证账号

1. MySQL如何使用索引

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

大多数MySQL索引（主键、唯一、索引和全文）都存储在b-tree中。例外：空间数据类型的索引使用r-tree；内存表也支持散列索引。InnoDB对全文索引使用倒置的列表。

MySQL使用索引来执行以下操作：

①快速找到符合where子句的行。

②如果你可以在多个索引之间进行选择，MySQL通常使用找到最少行数的索引（最具选择性的索引）。

③如果有多个列索引（也称为 "复合索引 "或 "联合索引"），优化器可以使用索引的任何最左边的前缀来查找记录。例如，如果有一个关于（col1, col2, col3）的三列索引，就有一个关于（col1）、（col1, col2）和（col1, col2, col3）的索引搜索函数。

④当使用连接查询从其他表中检索记录时，如果MySQL声明了相同的类型和大小，它可以更有效地使用列上的索引。在这种情况下，如果varchar和char被声明为相同的大小，则被认为是相同的。

例如，varchar（10）和char（10）是相同的大小，但是varchar（10）和char（15）是不同的大小。

⑤对于非二进制字符串列之间的比较，这两列应该使用相同的字符集。

⑥如果在可用索引的最左边的前缀上进行排序或分组（例如，按key_part1，key_part2排序），则表被排序或分组。如果desc后面是所有的键部分，那么键将以相反的顺序被读取。

⑦在某些情况下，MySQL可以使用索引来满足order by子句，并避免执行文件排序操作时涉及的额外排序。

⑧在某些情况下，查询可以被优化，以检索值而不查询数据行。(为查询提供所有必要结果的索引被称为覆盖索引)如果查询只使用表中某些索引所包含的列，那么可以从索引树中检索所选的值，以提高速度。

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

2. 避免全表扫描

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

①表太小，执行全表扫描比索引查找快得多。这对于少于10行和行长较短的表来说是很常见的。

②索引列不用于on或where句子中。

③将索引列与常量值进行比较，MySQL已经计算出（基于索引树）常量覆盖了表的很大一部分，表的扫描会更快。

④你正在通过另一列使用一个具有低cardinality的键（许多行与键值相匹配）。在这种情况下，MySQL假定通过使用这个键，可以执行许多键的查找，表的扫描会更快。

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

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

①使用分析TABLE tbl_ Name来更新键的分布。

②使用强制索引来告诉MySQL，与使用给定的索引相比，表扫描非常昂贵。

3. 列索引

b-tree数据结构使索引能够快速找到特定的值、一组值或与where子句中的运算符（如=、>、≤、between、in等）相对应的一系列值。

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

3.1索引前缀

使用col_ Name（n）可以创建一个只使用列的前n个字符的索引。在InnoDB表中，前缀的长度最多为767字节。

3.2全文索引

全文索引是用于全文搜索的。只有InnoDB和MyISAM存储引擎支持全文本索引，并且只支持char、varchar和text列。索引总是在整个列上，并且不支持列前缀索引。

3.3空间索引

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

3.4内存存储引擎上的索引

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

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

idx_ The name index是基于last_ Name和first_ name列上面的索引，它可以用来指定last_ Name和first_ name值组合的查询，也可以用来只指定last_ Name值，因为该索引匹配最左边的前缀。

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

然而，idx_名字索引不能用于以下查询。

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

考虑下面的SQL语句。

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

如果col1和col2上有一个多列索引，你可以直接抓取相应的记录。如果col1和col2上有单独的单列索引，优化器会尝试使用索引合并优化，或者通过确定哪个索引需要排除更多的记录来找到限制性最强的索引，并使用该索引来获取记录。

如果表有一个多列索引，优化器可以使用索引的任何最左边的前缀来寻找行。例如，如果有一个三列索引（col1，col2，col3），那么在（col1），（col1，col2），（col1，col2，col3）上有一个索引搜索函数。

如果该列不构成索引的最左边的前缀，MySQL则不能使用索引执行查找。

看看下面的SQL语句。

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

5. b-tree索引和哈希索引的比较

5.1b-tree索引的特点

b-tree索引可以在表达式中使用=, >, > =, <, < =, 操作符在列之间进行比较。如果like的参数是一个不以通配符开头的常数字符串，那么索引也可以用于like的比较。

下面的子句不使用索引。

/* LIKE值以通配符开始 */
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
/* 该LIKE值不是一个常数 */
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

任何没有覆盖where子句中所有和级别的索引都不会被用来优化查询。换句话说，为了能够使用索引，必须在每个和组中使用索引的前缀。

下面的where子句使用了索引。

... 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
 /* 经过优化 "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
 /* 可以在index1上使用index，但不能在index2或index3上使用index*/
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面的where子句不使用索引。

 /* 未使用index_part1 */
... WHERE index_part2=1 AND index_part3=2