索引用于快速查找具有特定列值的行。如果没有索引,MySQL 必须从第一行开始,然后通读整个表以找到相关行。桌子越大,成本就越高。如果表有相关列的索引,MySQL 可以快速确定要在数据文件中间查找的位置,而无需查看所有数据。这比顺序读取每一行要快得多。
大多数 MySQL 索引(PRIMARY KEY
、
UNIQUE
、INDEX
和
FULLTEXT
)都存储在
B 树中。例外:空间数据类型的索引使用 R 树;MEMORY
表也支持散列索引;InnoDB
使用倒排列表作为FULLTEXT
索引。
通常,索引的使用如以下讨论中所述。第 8.3.9 节“B-Tree 和哈希索引的比较”中描述了哈希索引特有的特征(在
MEMORY
表中使用)
。
MySQL 使用索引进行这些操作:
WHERE
快速找到与子句 匹配的行。从考虑中消除行。如果在多个索引之间进行选择,MySQL 通常会使用找到最少行数的索引(最具 选择性的索引)。
如果表有一个多列索引,那么优化器可以使用索引的任何最左边的前缀来查找行。例如,如果您在 上有一个三列索引 ,则您在、 和
(col1, col2, col3)
上具有索引搜索功能。有关详细信息,请参阅 第 8.3.6 节,“多列索引”。(col1)
(col1, col2)
(col1, col2, col3)
在执行连接时从其他表中检索行。如果它们被声明为相同的类型和大小,MySQL 可以更有效地使用列上的索引。在这种情况下, 如果它们被声明为相同的大小,则被认为是相同的
VARCHAR
。CHAR
例如,VARCHAR(10)
和CHAR(10)
是相同的大小,但VARCHAR(10)
和CHAR(15)
不是。对于非二进制字符串列之间的比较,两列应使用相同的字符集。例如,将
utf8mb4
列与latin1
列进行比较会排除使用索引。如果不转换就无法直接比较值,则比较不同的列(例如,将字符串列与时间或数字列进行比较)可能会阻止使用索引。对于数字列中的给定值
1
,它可能与字符串列中的任意数量的值(例如'1'
、' 1'
、'00001'
或)进行比较'01.e1'
。这排除了对字符串列使用任何索引。查找特定索引列的
MIN()
或 值。这是由预处理器优化的,预处理器检查您是否正在使用 索引中之前出现的所有关键部分。在这种情况下,MySQL 对每个or 表达式执行单个键查找,并将其替换为常量。如果所有表达式都替换为常量,则查询立即返回。例如:MAX()
key_col
WHERE
key_part_N
=constant
key_col
MIN()
MAX()
SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;
如果排序或分组是在可用索引(例如, )的最左边的前缀上完成的,则对表进行排序或分组。如果所有关键部分后面都有,则以相反的顺序读取关键。(或者,如果索引是降序索引,则按正向顺序读取键。)请参阅 第 8.2.1.16 节,“ORDER BY 优化”, 第 8.2.1.17 节,“GROUP BY 优化”和 第 8.3.13 节,“降序指数”。
ORDER BY
key_part1
,key_part2
DESC
在某些情况下,可以优化查询以在不查询数据行的情况下检索值。(为查询提供所有必要结果的索引称为 覆盖索引。)如果查询仅使用表中包含在某些索引中的列,则可以从索引树中检索所选值以提高速度:
SELECT key_part3 FROM tbl_name WHERE key_part1=1
对于报表查询处理大部分或所有行的小表或大表,索引不太重要。当查询需要访问大部分行时,顺序读取比通过索引读取更快。顺序读取最小化磁盘寻道,即使查询不需要所有行。有关详细信息,请参阅第 8.2.1.23 节,“避免全表扫描”。