MySQL如何优化索引

原作者: [db:作者] 来自: [db:来源] 收藏邀请

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

idx_name索引是建立在last_name和first_name列之上的索引，该索引可以用于指定了last_name和first_name值组合的查询，也可以用于仅指定last_name值的查询，因为该索引是最左前缀匹配的。

因此，idx_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_name索引不能用于下列查询：

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 和 Hash 索引的比较

B树索引特征

B树（B-tree）索引可用于使用=，>，>=，<，<=，BETWEEN运算符的表达式中的列比较。如果LIKE的参数是一个不以通配符开头的常量字符串，则该索引也可以用于LIKE比较。

下列这些子句不会使用索引：

/* the LIKE value begins with a wildcard character */
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
/* the LIKE value is not a constant */
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

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

下列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

  /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5

  /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面这些WHERE子句不会使用索引：

/* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

  /* Index is not used in both parts of the WHERE clause */
... WHERE index=1 OR A=10

  /* No index spans all rows */
... WHERE index_part1=1 OR index_part2=10

有时，即使有可用的索引，MySQL也不使用索引。发生这种情况的一种可能原因是，优化器估计使用索引将需要访问表中很大比例的行。（在这种情况下，表扫描可能会更快，因为它需要更少的查找。）但是，如果这样的查询使用LIMIT只检索某些行，则MySQL仍然使用索引，因为它可以更快地找到返回结果的几行。

哈希索引特征

哈希索引与刚刚讨论的索引具有一些不同的特征：

哈希索引只用于=或者<=>运算符的相等比较（但非常快），不用于比较运算符来查找值的范围。依赖于这种单值查找的系统被称为“键值对存储”，为了将MySQL用于此类应用，请尽可能地使用哈希索引。
优化器无法使用哈希索引来加快 ORDER BY 操作。（哈希类型的索引不能用于按顺序搜索下一个条目）
MySQL无法确定两个值之间大约有多少行（范围优化器使用它来决定使用哪个索引）
只有整个keys可用于搜索行。（对于B树索引，key的任何最左边的前缀都可用于查找行）

B-tree

树型数据结构，广泛用于数据库索引中。该结构始终保持有序，从而可以快速查找精确匹配（等于运算符）和范围（例如，大于，小于和BETWEEN运算符）。此类索引可用于大多数存储引擎，例如InnoDB和MyISAM。

因为B树节点可以有很多子节点，所以B树与二叉树不同，后者的每个节点最多只能有2个子节点。

术语B树的使用旨在参考索引设计的一般类别。由于经典B树设计中不存在复杂性，MySQL存储引擎使用的B树结构可能被视为变体。

Hash index

一种索引类型，专用于使用相等运算符而不是范围运算符的查询。它可用于MEMORY表。尽管出于历史原因，哈希索引是MEMORY表的默认索引，但是该存储引擎还支持B树索引，对于一般用途的查询而言，B树索引通常是更好的选择。

6. 优化数据大小

设计表以使得它们在磁盘上占用最少的空间。通过减少写入磁盘和从磁盘读取的数据量，这可以带来巨大的改进。较小的表通常在查询执行期间处理其内容时需要较少的主内存。表数据的任何空间减少都会导致索引变小，从而可以更快地处理索引。

MySQL支持许多不同的存储引擎（表类型）和行格式。对于每个表，可以决定使用哪种存储和索引方法。为应用程序选择适当的表格式可以大大提高性能。

Table Columns

尽可能使用最有效（最小）的数据类型。MySQL具有许多专门的类型，可以节省磁盘空间和内存。例如，如果可能，使用较小的整数类型以获得较小的表。MEDIUMINT通常比INT更好，因为MEDIUMINT列使用的空间要少25％。
如果可能，将列声明为NOT NULL。通过更好地使用索引并消除测试每个值是否为NULL的开销，它可以使SQL操作更快。而且还节省了一些存储空间，每列一比特。如果表中确实需要NULL值，那就用它们。只要避免使用默认设置，该默认设置允许每列中都为NULL值。

Row Format

为了通过压缩形式存储表数据来进一步减少空间，请在创建InnoDB表时指定ROW_FORMAT=COMPRESSED

Indexes

表的主键索引应尽可能短。这使得识别每一行变得容易而高效。对于InnoDB表，主键列在每个辅助索引条目中都是重复的，因此如果你有许多辅助索引，则较短的主键可节省大量空间。
仅创建需要提高查询性能的索引。索引很适合检索，但是会降低插入和更新操作的速度。如果你主要通过搜索列的组合来访问表，请在表上创建单个组合索引，而不是为每个列创建单独的索引。索引的第一部分应该是最常用的列。如果从表中查询时总是使用许多列，则索引中的第一列应是重复次数最多的列，以便更好地压缩索引。
如果是一个长字符串列，则很可能在第一个字符上具有唯一的前缀，这种情况下最好使用MySQL前缀进行索引（PS：只对前几个字符进行索引）。索引越短越快，这不仅是因为它们需要较少的磁盘空间，而且还因为它们还会使索引缓存中的命中次数增加，从而减少磁盘寻道次数。

Joins

在具有相同数据类型的不同表中声明具有相同信息的列，以加快基于相应列的联接。
保持列名简单，以便可以在不同的表中使用相同的名称，并简化联接查询。例如，在名为customer的表中，使用name列名代替customer_name。为了使你的名称可移植到其他SQL服务器中，请考虑将名称长度控制在18个字符以内。

Normalization

通常，尽量保持所有数据不冗余(数据库理论中称为第三范式)。为它们分配唯一的id来代替一个重复冗长的值，根据需要在多个较小的表中重复这些id，并通过在join子句中引用id来连接查询中的表。

7. 优化数据类型

数值类型

行的唯一标识最好使用数值而不是字符串，因为大数值比相应的字符串占用更少的存储字节，因此传输和比较它们更快，占用的内存也更少。

字符和字符串类型

在比较来自不同列的值时，尽可能使用相同的字符集和排序规则声明这些列，以避免在运行查询时进行字符串转换。
对于小于8KB的列值，请使用二进制VARCHAR而不是BLOB。 GROUP BY和ORDER BY子句可以生成临时表，并且如果原始表不包含任何BLOB列，则这些临时表可以使用MEMORY存储引擎。
如果一个表包含名称和地址等字符串列，但是许多查询没有检索这些列，那么可以考虑将字符串列分割成单独的表，并在必要时使用带有外键的连接查询。当MySQL从一行中检索任何值时，它读取包含该行所有列(可能还有其他相邻行)的数据块。保持每行较小，只包含最常用的列，可以让每个数据块容纳更多的行。这种紧凑的表减少了常见查询的磁盘I/O和内存使用。
当在InnoDB表中使用一个随机生成的值作为主键时，最好在它前面加上一个升序值，比如当前日期和时间(如果可能的话)。当连续的主键值物理上彼此相邻存储时，InnoDB可以更快地插入和检索它们。