MySQL索引

掌握索引类型、创建方法和查询优化技巧

全站首页 频道首页

什么是索引？

索引是数据库中用于快速查找数据的数据结构。类似于书籍的目录，索引可以帮助数据库引擎快速定位到所需的数据，而无需扫描整个表。

索引的工作原理

索引通过创建指向表中数据位置的指针来工作。当执行查询时，数据库首先检查索引，找到所需数据的位置，然后直接访问这些位置，而不是扫描整个表。

索引的数据结构

MySQL主要使用B+树作为索引的数据结构，这种结构适合磁盘存储，能够高效支持范围查询和等值查询。

索引的优点

  • 大大加快数据的检索速度
  • 通过创建唯一索引保证数据唯一性
  • 加速表与表之间的连接
  • 减少分组和排序的时间
  • 提高数据库整体性能

索引的缺点

  • 创建和维护索引需要时间
  • 索引需要占用物理空间
  • 对表进行增删改时，索引也需要维护，降低数据维护速度
  • 过多的索引会影响写入性能

索引类型

普通索引

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

SQL示例 
-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建表时定义普通索引
CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    INDEX idx_username (username)
);

唯一索引

索引列的值必须唯一，但允许有空值。唯一索引可以确保列中不出现重复值。

SQL示例 
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建表时定义唯一索引
CREATE TABLE users (
    id INT,
    email VARCHAR(100),
    UNIQUE INDEX idx_email (email)
);

主键索引

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

SQL示例 
-- 添加主键索引
ALTER TABLE users ADD PRIMARY KEY (id);

-- 创建表时定义主键
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

复合索引

基于多个列的索引。复合索引遵循最左前缀原则，查询时只有使用索引的最左前缀才会使用索引。

SQL示例 
-- 创建复合索引
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- 复合索引使用场景
-- 以下查询会使用索引：
SELECT * FROM users WHERE last_name = 'Smith';
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John' AND age = 30;

-- 以下查询不会使用索引（不满足最左前缀原则）：
SELECT * FROM users WHERE first_name = 'John';
SELECT * FROM users WHERE age = 30;

全文索引

用于全文搜索，仅适用于MyISAM和InnoDB存储引擎。全文索引可以对文本内容进行高效的搜索。

SQL示例 
-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文索引搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('MySQL database');

-- 布尔模式全文搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

空间索引

用于地理空间数据类型，如POINT、LINESTRING、POLYGON等。

SQL示例 
-- 创建空间索引
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position POINT NOT NULL,
    SPATIAL INDEX idx_position (position)
);

创建索引的方法

创建表时创建索引

SQL示例 
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    INDEX idx_username (username),
    INDEX idx_age (age),
    INDEX idx_username_age (username, age)
);

修改表添加索引

SQL示例 
-- 添加普通索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 添加复合索引
ALTER TABLE users ADD INDEX idx_name_age (last_name, age);

-- 添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);

删除索引

SQL示例 
-- 删除索引
DROP INDEX idx_username ON users;

-- 或者使用ALTER TABLE
ALTER TABLE users DROP INDEX idx_username;

-- 删除主键索引
ALTER TABLE users DROP PRIMARY KEY;

查看索引信息

SQL示例 
-- 查看表的索引信息
SHOW INDEX FROM users;

-- 查看索引创建语句
SHOW CREATE TABLE users;

-- 从information_schema查看索引信息
SELECT * 
FROM information_schema.STATISTICS 
WHERE table_name = 'users';

索引使用原则

适合创建索引的情况

  • 主键自动创建唯一索引
  • 频繁作为查询条件的字段
  • 查询中与其他表关联的字段
  • 查询中排序的字段
  • 查询中统计或分组的字段
  • 具有高选择性的列（不同值多的列）
  • 经常需要范围查询的列

不适合创建索引的情况

  • 表记录太少
  • 经常增删改的表
  • 数据重复且分布均匀的字段
  • 频繁更新的字段
  • WHERE条件中用不到的字段
  • 数据量小的表
  • 很少被查询的列
最佳实践：索引不是越多越好，过多的索引会影响写入性能。应该根据实际查询需求创建合适的索引。定期分析查询性能，删除不必要的索引。

索引选择策略

索引选择性

索引选择性是指不重复的索引值与总记录数的比例。选择性越高，索引效率越好。

SQL示例 
-- 计算索引选择性
SELECT 
    COUNT(DISTINCT column_name) / COUNT(*) AS selectivity
FROM table_name;

前缀索引

对于较长的字符串列，可以只对列的前缀创建索引，节省索引空间。

SQL示例 
-- 创建前缀索引
CREATE INDEX idx_email_prefix ON users(email(10));

EXPLAIN分析查询

使用EXPLAIN命令可以分析SQL查询的执行计划，了解索引使用情况：

SQL示例 
-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'john';

-- 分析连接查询
EXPLAIN SELECT u.username, o.order_date 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.country = 'China';

EXPLAIN结果解读

列名 描述
id 查询标识符
select_type 查询类型（SIMPLE, PRIMARY, SUBQUERY等）
table 访问的表
type 连接类型（const, eq_ref, ref, range, index, ALL）
possible_keys 可能使用的索引
key 实际使用的索引
key_len 使用的索引长度
ref 与索引比较的列
rows 估计要检查的行数
Extra 额外信息（Using where, Using index等）

索引优化案例

案例1：慢查询优化

SQL示例 
-- 优化前（没有合适的索引）
SELECT * FROM orders 
WHERE user_id = 123 
AND order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 优化后（创建复合索引）
CREATE INDEX idx_user_date ON orders(user_id, order_date);

案例2：排序优化

SQL示例 
-- 优化前（需要文件排序）
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY price DESC;

-- 优化后（使用索引排序）
CREATE INDEX idx_category_price ON products(category_id, price DESC);

索引维护

分析索引使用情况

SQL示例 
-- 查看索引使用统计
SELECT 
    index_name,
    stat_value
FROM mysql.innodb_index_stats 
WHERE table_name = 'users' 
AND database_name = 'mydb' 
AND stat_name = 'n_diff_pfx01';

重建索引

SQL示例 
-- 重建表索引（InnoDB）
ALTER TABLE table_name ENGINE = InnoDB;

-- 优化表（MyISAM）
OPTIMIZE TABLE table_name;

下一步学习

掌握了索引知识后，您可以继续学习：