MySQL 索引详解

MySQL索引是数据库优化中最重要的技术之一。索引可以大大提高MySQL的数据检索速度，就像书籍的目录可以帮助我们快速找到内容一样。没有索引的数据库查询需要全表扫描，效率极低；而合理使用索引可以将查询性能提升几个数量级。

索引的基本概念

索引是一种特殊的数据结构，它保存着数据表中一列或多列的值，并对这些值进行排序。通过索引，MySQL可以快速定位到数据的位置，而不必扫描整个表。

索引的优缺点：

优点：大大提高数据检索速度，加速表之间的连接，提高分组和排序的效率
缺点：占用额外磁盘空间，降低数据写入速度（INSERT、UPDATE、DELETE）

普通索引

普通索引是最基本的索引类型，没有任何限制，允许在定义索引的列中插入重复值和空值。

创建索引

这是最基本的索引创建方式：

CREATE INDEX indexName ON table_name(column_name(length));

参数说明：

indexName：索引名称
table_name：要创建索引的表名
column_name：要创建索引的列名
length：可选参数，对于CHAR、VARCHAR类型，可以指定索引长度；对于BLOB和TEXT类型，必须指定长度

修改表结构添加索引

ALTER TABLE table_name ADD INDEX index_name (column_name(length));

创建表时直接指定索引

CREATE TABLE table_name (
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX index_name (username(length))
);

删除索引

DROP INDEX index_name ON table_name;

注意：在创建索引时，如果数据量较大，可能会花费较长时间，并且会暂时锁定表。

唯一索引

唯一索引与普通索引类似，但索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name(column_name(length));

修改表结构添加唯一索引

ALTER TABLE table_name ADD UNIQUE index_name (column_name(length));

创建表时直接指定唯一索引

CREATE TABLE table_name (
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    UNIQUE index_name (username(length))
);

唯一索引的特点：

保证数据完整性，防止插入重复值
加速数据检索，特别是等值查询
一张表可以有多个唯一索引

主键索引

主键索引是一种特殊的唯一索引，不允许有空值。每个表只能有一个主键。

创建表时指定主键

CREATE TABLE table_name (
    ID INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID)
);

修改表添加主键

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

删除主键

ALTER TABLE table_name DROP PRIMARY KEY;

复合索引

复合索引是指在多个列上创建的索引。复合索引遵循最左前缀原则，即查询条件必须包含索引的最左列，才能使用索引。

创建复合索引

CREATE INDEX index_name ON table_name (column1, column2, column3);

复合索引使用示例

假设在(last_name, first_name)上创建了复合索引：

-- 可以使用索引
SELECT * FROM employees WHERE last_name = 'Smith';
SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

-- 不能使用索引（不满足最左前缀）
SELECT * FROM employees WHERE first_name = 'John';

使用 ALTER 命令管理索引

ALTER TABLE命令提供了多种方式来管理表的索引：

添加索引

添加主键：ALTER TABLE table_name ADD PRIMARY KEY (column_list)
添加唯一索引：ALTER TABLE table_name ADD UNIQUE index_name (column_list)
添加普通索引：ALTER TABLE table_name ADD INDEX index_name (column_list)
添加全文索引：ALTER TABLE table_name ADD FULLTEXT index_name (column_list)

实际示例

-- 添加普通索引
ALTER TABLE employees ADD INDEX idx_lastname (last_name);

-- 添加复合索引
ALTER TABLE employees ADD INDEX idx_fullname (last_name, first_name);

-- 删除索引
ALTER TABLE employees DROP INDEX idx_lastname;

显示索引信息

使用SHOW INDEX命令可以查看表的索引信息：

SHOW INDEX FROM table_name;

为了更好地查看结果，可以添加\G参数格式化输出：

SHOW INDEX FROM table_name\G

SHOW INDEX命令返回的重要字段包括：

Table: 表名
Non_unique: 索引是否允许重复值（0表示唯一索引）
Key_name: 索引名称
Seq_in_index: 索引中的列序列号
Column_name: 列名
Collation: 列在索引中的存储方式（A表示升序）
Cardinality: 索引中唯一值的估计数量
Index_type: 索引类型（BTREE, HASH等）

索引最佳实践

选择合适的列创建索引

频繁作为查询条件的列
经常需要排序、分组的列
作为外键的列
高选择性的列（唯一值多的列）

避免过度索引

索引会占用磁盘空间
降低数据修改操作（INSERT、UPDATE、DELETE）的性能
维护索引需要系统资源

索引使用注意事项

避免在索引列上使用函数或表达式
注意复合索引的最左前缀原则
小表通常不需要索引
定期分析和优化索引

性能提示： 使用EXPLAIN命令分析查询语句，查看是否使用了索引以及索引的使用效率。

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

MYSQL教程