MySQL索引类型详解：创建、使用场景与避坑指南

结果与目标

在数据库表中对字段建立索引可以极大提高查询速度。例如在包含10000条记录的表中查找特定用户名，若该字段已建立索引，MySQL可直接定位记录而无须扫描全表；若无索引，则需逐一比对所有记录。合理选择和使用索引类型，能在保证数据一致性的前提下显著优化性能。

准备条件

为了演示索引操作，我们需要一个基础表结构。假设我们创建了如下表：

CREATE TABLE mytable( 
   ID INT NOT NULL, 
   username VARCHAR(16) NOT NULL 
); 

我们将基于此表或扩展后的表进行后续索引类型的说明。

真实操作顺序

第1步：理解并创建普通索引

目的：获得基本的加速查询能力，无额外约束。 操作：普通索引没有任何限制，可通过以下三种方式创建：

1. 直接创建索引：适用于已存在的表。
   CREATE INDEX indexName ON mytable(username(length));
   注意：CHAR/VARCHAR类型length可小于实际长度；BLOB/TEXT类型必须指定length。
2. 修改表结构添加：
   ALTER mytable ADD INDEX [indexName] ON (username(length));
3. 建表时指定：
   CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

完成状态：成功执行上述语句后，可通过 DROP INDEX [indexName] ON mytable; 验证索引存在性或删除索引。

第2步：配置唯一索引

目的：确保索引列的数据唯一性，提高查询同时约束数据格式。 操作：唯一索引允许有空值，但列值必须唯一（组合索引则要求列值组合唯一）：

-- 创建唯一索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)); 
-- 修改表结构添加
ALTER mytable ADD UNIQUE [indexName] ON (username(length)); 
-- 建表时直接指定
CREATE TABLE mytable( 
   ID INT NOT NULL, 
   username VARCHAR(16) NOT NULL, 
   UNIQUE [indexName] (username(length)) 
); 

第3步：设置主键索引

目的：作为表的唯一标识，不允许空值且速度快。 操作：通常在建表时同时创建，每个表只能有一个主键：

CREATE TABLE mytable( 
   ID INT NOT NULL, 
   username VARCHAR(16) NOT NULL, 
   PRIMARY KEY(ID) 
);

也可使用 ALTER 命令追加，但需注意主键的唯一性约束。

第4步：构建组合索引以优化多条件查询

目的：利用单列无法同时匹配多个条件的局限，通过组合索引覆盖复合查询，减少扫描范围。 入口：扩展表结构增加更多字段。

CREATE TABLE mytable( 
   ID INT NOT NULL, 
   username VARCHAR(16) NOT NULL, 
   city VARCHAR(50) NOT NULL, 
   age INT NOT NULL 
); 

动作：将 name, city, age 建到一个索引中。为节省空间和提高INSERT速度，通常只取字符列的前缀：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

重要参数与原理：MySQL组合索引遵循“最左前缀”原则。上述组合索引相当于建立了三组索引：
1. username, city, age
2. username, city
3. username
因此，只有以 username 开头的查询才能用到该索引，例如：

• 有效：SELECT * FROM mytable WHERE username='admin' AND city='郑州'
• 有效：SELECT * FROM mytable WHERE username='admin'

而以下查询无效（不会使用该组合索引）：

• SELECT * FROM mytable WHERE age=20 AND city='郑州'
• SELECT * FROM mytable WHERE city='郑州'

第5步：把握建立索引的时机

目的：精准识别WHERE、JOIN子句中的高频过滤字段，避免无效索引占用资源。 操作：通常对 <, <=, =, >, >=, BETWEEN, IN 以及部分 LIKE 操作的字段建索引。

示例分析：

SELECT t.Name FROM mytable t LEFT JOIN mytable m 
ON t.Name=m.username WHERE m.age=20 AND m.city='郑州';

此时需对 m.age, m.city 以及 JOIN 关联键 username 建立索引。

LKIE特殊说明：仅当通配符不在首位时（如 'admin%'），索引才生效；开头为通配符（如 '%admin'）时会导致全表扫描。

第6步：评估索引不足之处

目的：权衡读写性能，防止索引滥用导致写操作变慢或磁盘膨胀。 风险提示：

1. 降低更新速度：INSERT、UPDATE、DELETE时需维护索引文件，增加I/O开销。
2. 占用磁盘空间：特别是在大表上建立多种组合索引时，索引文件体积会迅速增长。

第7步：落实使用索引的注意事项

目的：规避常见写法陷阱，确保索引始终生效。 操作规范：

1. 避免NULL值：索引不包含NULL值列，复合索引含NULL则失效。设计时应尽量避免默认值为NULL。
2. 使用短索引：长文本列（如CHAR(255)）尽量指定前缀长度（如10或20字符），提升查询并节省空间。
3. 谨慎排序：MySQL单次查询仅用一个索引。若WHERE已用索引，ORDER BY可能失效。尽量减少多列排序，或为排序列创建复合索引。
4. 慎用LIKE：推荐 'aaa%'，避免 '%aaa%'。
5. 避免列运算：
   错误：SELECT * FROM users WHERE YEAR(adddate)<2007;（导致全表扫描）
   正确：SELECT * FROM users WHERE adddate<'2007-01-01';
6. 避免NOT IN和<>：这些操作往往难以有效利用索引。

简要总结

以上就是MySQL索引类型及使用技巧的详细内容。正确区分普通、唯一、主键和组合索引，并严格遵守最左前缀、避免列运算等规范，是优化数据库性能的核心。更多关于MySQL优化的资料请关注本站其它相关文章！