MySQL 索引:创建、添加和删除教程
⚡ 智能摘要
MySQL 索引教程讲解了索引如何快速排序和定位数据。索引是基于一个或多个列创建的排序查找结构;CREATE INDEX 用于添加索引,SHOW INDEXES 用于检查索引,而 DROP INDEX 用于在写入密集型表的读写优势大于写入优势时删除索引。
什么是 MySQL 指数?
An 指数 in MySQL 索引是一种数据结构,它以有序的方式存储列值,以便引擎能够快速查找行。索引创建在最常用于筛选数据的列上。可以将索引想象成一个按字母顺序排序的列表:在有序列表中查找名称比在未排序的列表中找到名称要快得多。
索引的使用存在权衡——每次 INSERT 或 UPDATE 操作都需要维护索引,因此在写入频繁的表上添加过多索引会降低整体性能。一般来说,对于读取频率高于写入频率的表,应为 WHERE、JOIN 和 ORDER BY 子句中出现的列创建索引。
为什么要使用索引?
没人喜欢运行缓慢的系统。高性能几乎是所有基于数据库的应用程序的首要关注点。企业在硬件上投入巨资以保证查询速度,但硬件本身的性能提升终究有限。优化索引是一种更经济有效的方法。
响应速度慢通常是由于磁盘上的数据行按物理顺序存储造成的。如果没有索引, MySQL 必须扫描每一行才能找到与谓词匹配的行——即“全表扫描”。索引允许这样做。 MySQL 直接跳转到匹配的行,这使得 B 树查找的查询计划从 O(n) 变为大致 O(log n)。
语法:创建索引
索引可以在两个地方定义:
- 在创建表格时。
- 表格已存在。
示例:在 CREATE TABLE 语句中直接创建索引
如报名参加 myflixdb 数据库中,我们预计会对“全名”列进行大量搜索。以下脚本将创建一个新的 members_indexed 带有索引的表 full_names 列。
CREATE TABLE `members_indexed` (
`membership_number` INT(11) NOT NULL AUTO_INCREMENT,
`full_names` VARCHAR(150) DEFAULT NULL,
`gender` VARCHAR(6) DEFAULT NULL,
`date_of_birth` DATE DEFAULT NULL,
`physical_address` VARCHAR(255) DEFAULT NULL,
`postal_address` VARCHAR(255) DEFAULT NULL,
`contact_number` VARCHAR(75) DEFAULT NULL,
`email` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`membership_number`),
INDEX (`full_names`)
) ENGINE = InnoDB;
执行脚本 MySQL 工作台对抗 myflixdb 数据库。
刷新 myflixdb 去看看新的 members_indexed 桌子。 这 full_names 列现在显示在下方 指数 节点。
随着会员人数的增长,对以下内容的搜索查询也随之增加: members_indexed 使用 WHERE 和 ORDER BY 条件 full_names 比在原始服务器上执行相同的查询要快得多。 members 没有索引的表。
在表已存在之后添加索引
您经常会发现现有表需要索引——搜索查询速度很慢,并且 EXPLAIN 执行计划显示对 WHERE 子句中出现的列进行了全表扫描。 CREATE INDEX 该语句添加索引,而无需重新创建表。
CREATE INDEX `id_index` ON `table_name` (`column_name`);
具体例子——加快搜索速度 title 的专栏 movies 表:
CREATE INDEX `title_index` ON `movies` (`title`);
每个筛选条件的查询 movies.title 现在已启用新索引。除非查询本身拥有索引,否则基于其他列进行筛选的查询仍会扫描表。
注意: 当你的查询始终基于相同的列组合进行筛选或排序时,你可以创建跨多个列的复合索引。顺序很重要——首列决定了索引是否可用。
列出表上的索引
绝大部分储备使用 SHOW INDEXES 查看表中定义的每个索引。
SHOW INDEXES FROM `table_name`;
示例——列出索引 movies 表:
SHOW INDEXES FROM `movies`;
运行该语句 MySQL 工作台 驳 myflixdb 查看现有索引及其涵盖的列。
注意: 主键和外键会自动建立索引 MySQL每个索引都有一个唯一的名称,并列出它涵盖的列。
语法:删除索引
绝大部分储备使用 DROP INDEX 从表中删除现有索引。当写入密集型表因读取端不再发挥作用的索引而降低速度时,此操作非常有用。
DROP INDEX `index_id` ON `table_name`;
具体例子——放弃 full_names 索引 members_indexed:
DROP INDEX `full_names` ON `members_indexed`;
有哪些 MySQL 指数
MySQL 支持多种索引类型,每种类型都适用于不同的工作负载。
| 类型 | 目的 |
|---|---|
| 主键 | 唯一行标识符;与 InnoDB 中的表数据进行聚类。 |
| 独特性 | 确保唯一性,同时充当索引。 |
| 索引(B树) | 用于范围查询和相等性查找的默认二级索引。 |
| 全文 | 针对自然语言文本搜索进行了优化,支持 MATCH … AGAINST。 |
| 空间的 | 用于 GIS 数据类型(例如 POINT 和 POLYGON)的 R 树索引。 |
| HASH | 恒定时间相等性查找;由 MEMORY 存储引擎使用。 |
| 复合(多列) | 将多列合并为一个索引;遵循最左侧前缀规则。 |
最佳实践 MySQL 指数
以下习惯有助于保持指数的有效性,防止其沦为无用的负担。
- 索引的是查询模式,而不是列名: 添加与实际的 WHERE、JOIN 和 ORDER BY 子句相匹配的索引,而不是“每个听起来重要的列”。
- 观察综合指数排序: 要使用索引,查询中必须包含首列。
- 避免重复索引: 复合索引的前导前缀已经涵盖了对该前缀的单列查找。
- 使用 EXPLAIN 进行检查: 确认规划器确实选择了新的索引。
- 删除未使用的索引: 使用
sys.schema_unused_indexesin MySQL 5.7+ 查找没有任何内容读取的索引。 - 匹配数据类型: 如果 WHERE 子句将 VARCHAR 列与数字进行比较,则由于隐式转换,索引无法使用。



