8.3.1 MySQL是如何使用索引的

最新推荐文章于 2024-03-23 13:55:36 发布

肥飞又变身啦

最新推荐文章于 2024-03-23 13:55:36 发布

阅读量203

点赞数

分类专栏： mysql 文章标签： mysql

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

原文：https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

8.3.1 MySQL是如何使用索引的

索引能够通过特定列上的值快速查找到数据行。如果没有索引，MySQL必须从第一行顺序读取全表数据才能查找到相关的数据，表越大，越消耗资源。如果在要查询的列上有索引，MySQL可以快速定位到数据而不用扫描全部数据，这比顺序读取表数据要快很多很多。

大多数MySQL索引(PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT)使用B-trees结构存储.空间索引使用R-trees,MEMORY表还支持hash索引；InnoDB使用倒排列表实现的全文索引(FULLTEXT indexes).

通常，我们需要按下面讨论的方式使用索引。hash索引的有点特殊，具体请参考Section 8.3.8, “Comparison of B-Tree and Hash Indexes”。

MySQL使用索引规则:

为了快速的找到where条件匹配的数据.
为了排除某些行：如果可以选择多个索引,MySQL一般会选择匹配行最少的那个索引.也就是最selective的索引。

selective：数据分布的一个属性，数值=column distinct(values)/table rows。数值越大，使用该索引查询效率越高。如果你（或优化器）预估到使用某索引匹配到的行更少，则使用这个索引效率越高。
如果一个表有多列索引,优化器会使用最左匹配原则使用该索引.举例,如果你有一个三列索引(col1, col2, col3),这个索引可以匹配(col1), (col1, col2), 和 (col1, col2, col3)搜索条件.更多信息Section 8.3.5, “Multiple-Column Indexes”.
使用join关联多表查询时,如果索引使用的相同类型同等长度的列,执行会更快.在此场景下，varchar和char长度相同时,MySQL会认为两者是相同的.比如，VARCHAR(10) 和 CHAR(10) 长度相同,但是VARCHAR(10) 和 CHAR(15)不同。

比较两个字符类型的列时,它们使用的字符集必须相同.否则不会使用索引.

比较两个不同类型的列时,如果类型不能直接比较（必须使用conversion函数）则不会使用索引。比如数字1，它与字符’1’, ’ 1’, ‘00001’, or ‘01.e1’相等。但这两个类型不能直接比较，所以不能使用索引。
为了在建立了索引的列key_col上查询MIN() 或 MAX()值,当查询条件使用 WHERE key_part_N =固定值,且索引中key_col前面的key都使用了固定值,此时预处理器会优化这个sql。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.
```
SELECT MIN(key_part2),MAX(key_part2)
FROM tbl_name WHERE key_part1=10;
```
如果对一个表使用最左匹配的索引排序或分组时(比如, ORDER BY key_part1, key_part2),如果所有的类都是按DESC排序,索引对使用倒序读取.See Section 8.2.1.13, “ORDER BY Optimization”, and Section 8.2.1.14, “GROUP BY Optimization”.
在有些情况下,一个查询语句直接从索引中取值而不用从数据行上取值.(如果一个索引包含了查询的所有列,这个索引被称为覆盖索引covering index.)如果一个查询使用到的列都包含在某些索引上,这个查询会从索引tree上获取数据,这将会极大的提高查询速度:
```
SELECT key_part3 FROM tbl_name
WHERE key_part1=1
```
covering index
An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.

Any column index or composite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.