MySQL如何使用索引

拼搏现实的明天。 2023-07-25 14:15 58阅读 0赞

#### 概念 ####

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

大多数MySQL索引（`PRIMARY KEY`， `UNIQUE`，`INDEX`和 `FULLTEXT`）存储在 B树。例外：空间数据类型的索引使用R树；MEMORY 表还支持哈希索引 ; InnoDB对FULLTEXT索引使用倒排列表。

#### MySQL使用索引进行以下操作： ####

*  `WHERE` 快速 查找与子句匹配的行。
 *  如果表具有多列索引，那么优化器可以使用索引的任何最左前缀来查找行。举例来说，如果你有一个三列的索引 `(col1, col2, col3)`，你有索引的搜索功能 `(col1)` ，`(col1, col2)` 以及 `(col1, col2, col3)` 。
 *  执行联接时从其他表中检索行。如果声明相同的类型和大小，MySQL可以更有效地在列上使用索引。在这种情况下， `VARCHAR` 与 `CHAR` 被认为是相同的，如果它们被声明为相同的大小。例如， `VARCHAR(10)` 和 `CHAR(10)` 是相同的大小，但是 `VARCHAR(10)` 和 `CHAR(15)` 不是。

为了在非二进制字符串列之间进行比较，两个列应使用相同的字符集。例如，将一utf8列与一 latin1列进行比较会排除使用索引。

如果不能不通过转换直接比较值，则比较不同的列（例如，将字符串列与时间或数字列进行比较）可能会阻止使用索引。对于给定的值，如1 在数值列，它可能比较等于在字符串列，例如任何数量的值 ‘1’，’ 1’， ‘00001’，或’01.e1’。这排除了对字符串列使用任何索引的可能性。

*  查找特定索引列的 `MIN()` 或 `MAX()` 值 `key_col` 。这由预处理器优化，该预处理器检查您是否正在 索引中之前出现的所有关键部分上使用。在这种情况下，MySQL为每个表达式或 表达式执行一次键查找，并将其替换为常量。如果所有表达式都用常量替换，查询将立即返回。例如： `WHERE key_part_N = constantkey_colMIN() MAX()`

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

*  如果排序或分组是在可用索引的最左前缀（例如）上完成的，则对表进行排序或分组 。如果所有关键部分后面都有，则按相反顺序读取密钥。（或者，如果索引是降序索引，则按向前顺序读取键。）。 `order by key_part1, key_part2DESC`
 *  在某些情况下，查询可以优化为检索值而无需查询数据行。（为查询提供所有必要结果的索引称为 覆盖索引。）如果查询仅从表中使用某些索引中包含的列，则可以从索引树中检索所选值，以提高速度：

SELECT key_part3 
    FROM tbl_name
    WHERE key_part1=1

对于报表查询处理大多数或所有行的小型表或大型表，索引的重要性不那么重要。当查询需要访问大多数行时，顺序读取要比处理索引快。顺序读取可以最大程度地减少磁盘查找，即使查询不需要所有行。

--------------------

#### 查看更多 ####

关注微信公众号可获取免费电子书和简历模板  
![在这里插入图片描述][format_png_pic_center]

[format_png_pic_center]: /images/20230528/ebcee868d7f445f69d2b3de873b78627.png