MySQL 优化 —— MySQL 如何使用索引

引言

本文翻译自MySQL 官网 :How MySQL Uses Indexes ,MySQL 版本 5.7 。

提升 SELECT 操作性能最好的方式就是在查询的一列或多列上建立索引。索引的行为类似指向表数据的指针,可以让查询能够快速判断哪个记录满足 WHERE 子句中的条件,然后取得这些记录的其他字段的值。所有的 MySQL 数据类型都支持索引。

虽然在可能用于查询的所有字段上都建立索引的做法是非常诱人的,但是不必要的索引既浪费存储空间,同时也浪费了MySQL 决定用哪个索引的时间。索引也会增加 insert、update、delete 等更新操作的开销,因为每个索引都必须更新。你必须找到恰当的平衡点使用最理想的索引集合来实现快速的查询。

MySQL 如何使用索引

索引被用于快速查找特定的列值对应的记录。没有索引,MySQL 就必须得从表的第一行开始,然后读取整张表才能找到符合条件的记录。表越大,花费的时间就越多。如果表里有一个正好适合查询情况的索引,MySQL 就可以快速在表中确定对应的位置而不需要搜索所有数据。这比连续读取每一行要快不少。

绝大多数MySQL 索引(PRIMARY KEY,UNIQUE ,INDEX  和 FULLTEXT)都以 B 树的形式存储。例外的情况:空间数据类型(spatial data type)的索引使用 R 树,MEMORY 存储引擎也支持 hash 索引。InnoDB 使用倒排表(inverted lists)来实现 FULLTEXT 索引

下面的讨论描述了索引使用的一般情况。关于 hash 索引的具体特征请移步至:https://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html (未来我会对该章进行单独翻译,并会更新此处的连接)

MySQL 会在以下操作中使用索引:

1、用于快速找到匹配 WHERE 子句的记录。

2、用于缩小数据检索范围。如果有多个索引可供选择,MySQL normally uses the index that finds the smallest number of rows (the most selective index). MySQL 通常会使用可以找到最小记录数的索引(最具选择性的索引)。

3、如果表有一个复合索引,那么索引中任何最左侧的前缀都可以被优化器使用。例如,如果你有一个三列复合索引如(col1, col2, col3) ,那么你有三种索引搜索的可选方案:(col1)、(col1,col2)、以及(col1,col2, col3)。

4、当执行连接查询时取得其他表中的记录,如果索引字段声明了同样的类型和大小,那么 MySQL 会更有效地利用该列上的索引。在这种语境下,VARCHAR 和 CHAR 如果大小一致,那么就可以认为是同种类型。例如, VARCHAR(10) 和 CHAR(10) 具有相同的大小,但是 VARCHAR(10) 和 CHAR(15) 就不是了。

对于比较非二进制字符串列值,两个列必须具有相同的字符集。例如,比较 utf8 的字段和 latin1 的字段就会影响到使用索引。

比较不同类型的字段(如字符串和时间类型或数值类型等),如果两个值不经过转换就无法直接比较的话,那么同样会无法使用索引。有一个给定的数值类型 1 ,可能会与'1','  1','00001' 或 '01.e1' 这样的字符串比较。那么这种情况就无法使用任何索引。

5、To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX()expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example: 

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

(上面这段话我没有理解官方文档的意思,有能翻译的同学帮忙评论区留个言!非常感谢)

6、用于排序或分组已经使用索引的左前缀排好序或分好组的表(例如,ORDER BY key_part1, key_part2)。如果所有字段都使用 DESC ,那么索引就会以相反的顺序读取。

7、有些情况,优化器可以优化查询,不需要访问原始记录,就可以获取数据。(可以获取全部查询必要信息的索引叫做覆盖索引)如果查询列表只查询了那些包含在索引中的字段,那么查询的值可以以更快的速度从索引树中获取:

SELECT key_part3 FROM tbl_name
  WHERE key_part1=1

索引对于小表不那么重要,对于那些需要查询绝大多数行或全部行的大表也不那么重要。如果查询需要访问绝大多数记录,那么按序读取会比使用索引更快。连续的读取可以最小化磁盘搜索,即使并不是所有记录都需要查询。

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页