MySql是怎么使用的索引，在哪些情况下会使用到索引

妖狐艹你老母 2022-07-24 06:17 287阅读 0赞

索引是用来快速查找指定的行。如果不使用索引，MySql必须从第一行开始读取整个表直到找到相关的行。如果是一张大表，这将是一大笔消耗。如果表为问题列建立一个索引，MySql可以快速的确定位置在寻找数据文件中而不用查找所有的数据。这是远远超过按顺序读取表的每一行。

大部分MySql 索引（`PRIMARY KEY`, `UNIQUE`, `INDEX`, and `FULLTEXT`）被存储在B-trees。

例外：

1.  空间数据类型使用R树索引;
2.  内存表也支持哈希索引;
3.  InnoDB为全文索引的使用反向链表（inverted lists）。

在一般情况下，索引的使用在下面描述里。哈希索引的具体特征（如用于存储表）在第9.3.8所描述的，“比较B树和哈希索引”[http://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html][http_dev.mysql.com_doc_refman_5.7_en_index-btree-hash.html]  。

MySql为以下这些操作使用索引：

1、为了快速查找匹配WHERE条件的行。

2、为了从考虑的条件中消除行。如果在多个索引之间选择一个，正常情况下，MySql使用找到行的最小数量的那个索引（部分selective索引[http://dev.mysql.com/doc/refman/5.7/en/glossary.html\#glos\_selectivity][http_dev.mysql.com_doc_refman_5.7_en_glossary.html_glos_selectivity]  ）。

3、如果表有一个multiple-column索引，任何一个索引的最左前缀可以通过使用优化器来查找行。例如，如果你有一个 three-column索引在(col1, col2, col3)，你能搜索索引在(col1), (col1, col2),和 (col1, col2, col3)。更多信息，看[http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html][http_dev.mysql.com_doc_refman_5.7_en_multiple-column-indexes.html] 。

4、当运行joins时，为了从其他表检索行。MySql可以更有效的使用索引在多列上如果他们声明的类型和大小是一样的话。在这个环境下，VARCHAR和CHAR是一样的如果他们声明的大小是一样的。例如，VARCHAR(10) and CHAR(10)是一样大小，但VARCHAR(10) and CHAR(15)不一样。

为了在非二进制字符串列之间比较，两列应该使用一样的字符集。例如，urf8列和latin1列比较，会消除索引的使用。

不同列的比较（字符串类型和日期或数值类型），可能防止索引的使用，如果没有转换的值不可以被直接地比较。对于一个给定的值如1在数值列，它可能在字符串列和任意数值比较，如'1', ' 1', '00001', 或者'01.e1'。这个规则会把字符串列排除在索引之外。

5、为了找到 MIN() or MAX()的值对于一个指定索引的列*key\_col.这是被一个预处理程序优化的，会检查你是否使用WHERE  key\_part\_N = constant 在所有关键部分，在索引key\_col列之前出现。在这种情况下，MySql用单个关键字查找每个 MIN() or MAX()表达式并用常量替换它。如果所有表达式被常量替换，查询会马上返回。例如：*

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

6、为了排序或分组一个表，如果正在排序或正在分组是完成在一个可用的索引的一个最左的前缀（例如， ORDER BY key\_part1, key\_part2）。如果所有关键字部分跟从DESC，这个关键字是被倒序读取的。 See Section 9.2.1.15, “ORDER BY Optimization”[http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html][http_dev.mysql.com_doc_refman_5.7_en_order-by-optimization.html]  , and Section 9.2.1.16, “GROUP BY Optimization”[http://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html][http_dev.mysql.com_doc_refman_5.7_en_group-by-optimization.html]  .

7、在一些情况下，查询可以优化检索值而不需咨询数据行。（为查询提供了所有必要的结果的索引称为覆盖索引[http://dev.mysql.com/doc/refman/5.7/en/glossary.html\#glos\_covering\_index][http_dev.mysql.com_doc_refman_5.7_en_glossary.html_glos_covering_index]  。）如果从某个表中查询只包含在某个索引中的列，则可以从索引树中检索选定的值以提高速度：

SELECT key_part3 FROM tbl_name
        WHERE key_part1=1

对于小表的查询索引不重要,或大表在报表查询大部分或所有的行的过程。 当查询需要访问大部分的行时,通过索引读取是比顺序工作更快。 顺序读取最大限度地减少磁盘的查找，即使不是所有的行都需要查询。 看 部分9.2.1.21,“如何避免全表扫描http://dev.mysql.com/doc/refman/5.7/en/how-to-avoid-table-scan.html” 细节。

翻译如有不足之处，请指出，谢谢！

翻译自：

[http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html][http_dev.mysql.com_doc_refman_5.7_en_mysql-indexes.html]

[http_dev.mysql.com_doc_refman_5.7_en_index-btree-hash.html]: http://dev.mysql.com/doc/refman/5.7/en/index-btree-hash.html
[http_dev.mysql.com_doc_refman_5.7_en_glossary.html_glos_selectivity]: http://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_selectivity
[http_dev.mysql.com_doc_refman_5.7_en_multiple-column-indexes.html]: http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html
[http_dev.mysql.com_doc_refman_5.7_en_order-by-optimization.html]: http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
[http_dev.mysql.com_doc_refman_5.7_en_group-by-optimization.html]: http://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html
[http_dev.mysql.com_doc_refman_5.7_en_glossary.html_glos_covering_index]: http://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_covering_index
[http_dev.mysql.com_doc_refman_5.7_en_mysql-indexes.html]: http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html