索引

索引是数据库中用于提高查询效率的数据结构。本章将介绍MySQL索引的类型、创建和管理方法。

索引概述

sql

/*
 * 索引的作用：
 * 1. 加快数据检索速度
 * 2. 加速排序和分组
 * 3. 加速表连接
 * 4. 保证数据唯一性
 * 
 * 索引的代价：
 * 1. 占用存储空间
 * 2. 降低写入速度（INSERT/UPDATE/DELETE）
 * 3. 需要维护成本
 */

-- 查看表的索引
SHOW INDEX FROM employees;

-- 查看表结构和索引
SHOW CREATE TABLE employees;

索引类型

主键索引

sql

-- 主键索引：自动创建，数据唯一且不能为NULL
CREATE TABLE users (
    id INT PRIMARY KEY,  -- 自动创建主键索引
    name VARCHAR(50)
);

-- 或在表创建后添加
CREATE TABLE users2 (
    id INT,
    name VARCHAR(50)
);

ALTER TABLE users2 ADD PRIMARY KEY (id);

-- 删除主键
ALTER TABLE users2 DROP PRIMARY KEY;

唯一索引

sql

-- 唯一索引：数据唯一，允许NULL
CREATE TABLE emails (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE  -- 自动创建唯一索引
);

-- 创建表后添加唯一索引
CREATE UNIQUE INDEX idx_email ON emails(email);

-- 或使用ALTER TABLE
ALTER TABLE emails ADD UNIQUE INDEX idx_email (email);

-- 复合唯一索引
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE KEY uk_user_role (user_id, role_id)
);

普通索引

sql

-- 创建普通索引
CREATE INDEX idx_name ON employees(name);

-- 使用ALTER TABLE创建
ALTER TABLE employees ADD INDEX idx_name (name);

-- 复合索引（多列索引）
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

-- 前缀索引（字符串前N个字符）
CREATE INDEX idx_name_prefix ON employees(name(10));

-- 删除索引
DROP INDEX idx_name ON employees;
ALTER TABLE employees DROP INDEX idx_name;

全文索引

sql

-- 全文索引：用于文本搜索
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_content (content)
);

-- 创建全文索引
CREATE FULLTEXT INDEX ft_title_content ON articles(title, content);

-- 使用全文索引搜索
INSERT INTO articles (title, content) VALUES
('MySQL教程', 'MySQL是最流行的关系型数据库'),
('Python教程', 'Python是一种编程语言'),
('Java教程', 'Java是一种面向对象的编程语言');

-- 自然语言搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库');

-- 布尔模式搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('+编程 -Java' IN BOOLEAN MODE);

-- 查询扩展搜索
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('编程' WITH QUERY EXPANSION);

空间索引

sql

-- 空间索引：用于地理数据
CREATE TABLE locations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    position POINT,
    SPATIAL INDEX idx_position (position)
);

-- 插入空间数据
INSERT INTO locations (name, position) VALUES
('北京', POINT(116.4074, 39.9042)),
('上海', POINT(121.4737, 31.2304));

-- 空间查询
SELECT name FROM locations 
WHERE ST_Distance_Sphere(position, POINT(116.4074, 39.9042)) < 100000;

索引设计原则

sql

/*
 * 索引设计原则：
 * 
 * 1. 选择性高的列
 *    - 唯一性高的列效果好
 *    - 性别、状态等选择性低的列不适合
 * 
 * 2. WHERE、JOIN、ORDER BY、GROUP BY中的列
 * 
 * 3. 使用前缀索引
 *    - 长字符串只索引前N个字符
 * 
 * 4. 复合索引顺序
 *    - 最左前缀原则
 *    - 高选择性列放前面
 * 
 * 5. 避免过度索引
 *    - 索引不是越多越好
 *    - 影响写入性能
 * 
 * 6. 小表不需要索引
 *    - 全表扫描更快
 */

-- 选择性计算
SELECT 
    COUNT(DISTINCT department_id) / COUNT(*) AS dept_selectivity,
    COUNT(DISTINCT salary) / COUNT(*) AS salary_selectivity
FROM employees;

-- 创建高选择性索引
CREATE INDEX idx_salary ON employees(salary);  -- 选择性高

-- 低选择性列不适合单独索引
-- CREATE INDEX idx_dept ON employees(department_id);  -- 选择性低

复合索引

sql

/*
 * 复合索引（多列索引）：
 * 遵循最左前缀原则
 */

-- 创建复合索引
CREATE INDEX idx_dept_salary_hire ON employees(department_id, salary, hire_date);

-- 可以使用索引的查询：
-- 1. 匹配所有列
SELECT * FROM employees 
WHERE department_id = 1 AND salary = 15000 AND hire_date = '2020-01-15';

-- 2. 匹配前两列
SELECT * FROM employees 
WHERE department_id = 1 AND salary = 15000;

-- 3. 匹配第一列
SELECT * FROM employees WHERE department_id = 1;

-- 4. 匹配第一列范围
SELECT * FROM employees WHERE department_id > 1;

-- 不能使用索引的查询：
-- 1. 跳过第一列
SELECT * FROM employees WHERE salary = 15000;

-- 2. 跳过中间列
SELECT * FROM employees 
WHERE department_id = 1 AND hire_date = '2020-01-15';

/*
 * 最左前缀原则：
 * 索引 (a, b, c)
 * - WHERE a = ?           ✓ 使用索引
 * - WHERE a = ? AND b = ? ✓ 使用索引
 * - WHERE a = ? AND b = ? AND c = ? ✓ 使用索引
 * - WHERE b = ?           ✗ 不使用索引
 * - WHERE c = ?           ✗ 不使用索引
 * - WHERE a = ? AND c = ? ✓ 部分使用索引（只用a）
 */

索引使用场景

sql

-- 适合创建索引的场景

-- 1. WHERE条件中的列
SELECT * FROM employees WHERE department_id = 1;
-- 需要索引：department_id

-- 2. JOIN连接的列
SELECT e.name, d.name 
FROM employees e 
JOIN departments d ON e.department_id = d.id;
-- 需要索引：employees.department_id

-- 3. ORDER BY排序的列
SELECT * FROM employees ORDER BY salary DESC;
-- 需要索引：salary

-- 4. GROUP BY分组的列
SELECT department_id, COUNT(*) 
FROM employees 
GROUP BY department_id;
-- 需要索引：department_id

-- 5. DISTINCT去重的列
SELECT DISTINCT department_id FROM employees;
-- 需要索引：department_id


-- 不适合创建索引的场景

-- 1. 频繁更新的列
-- 每次更新都需要维护索引

-- 2. 小表
-- 全表扫描更快

-- 3. 低选择性的列
-- 如性别、状态等

-- 4. 很少使用的列
-- 浪费存储空间

索引维护

sql

-- 查看索引使用情况
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE();

-- 分析表（更新索引统计信息）
ANALYZE TABLE employees;

-- 优化表（重建表和索引）
OPTIMIZE TABLE employees;

-- 检查表
CHECK TABLE employees;

-- 修复表（MyISAM）
REPAIR TABLE employees;

-- 强制使用索引
SELECT * FROM employees FORCE INDEX (idx_dept_salary) 
WHERE department_id = 1;

-- 忽略索引
SELECT * FROM employees IGNORE INDEX (idx_dept_salary) 
WHERE department_id = 1;

索引监控

sql

-- 查看索引使用统计
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_READ,
    COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = DATABASE()
ORDER BY COUNT_READ DESC;

-- 查看未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_READ = 0
AND COUNT_WRITE = 0
AND OBJECT_SCHEMA = DATABASE();

-- 查看冗余索引
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    REDUNDANT_INDEX_NAME,
    DOMINANT_INDEX_NAME
FROM sys.schema_redundant_indexes;

本章小结

本章学习了：

索引类型：主键、唯一、普通、全文、空间索引
索引创建：CREATE INDEX、ALTER TABLE
复合索引：最左前缀原则
索引设计：选择性、使用场景
索引维护：ANALYZE、OPTIMIZE
索引监控：查看使用情况

下一章，我们将学习视图，了解如何创建和使用视图。

索引 ​

索引概述 ​

索引类型 ​

主键索引 ​

唯一索引 ​

普通索引 ​

全文索引 ​

空间索引 ​

索引设计原则 ​

复合索引 ​

索引使用场景 ​

索引维护 ​

索引监控 ​

本章小结 ​

索引

索引概述

索引类型

主键索引

唯一索引

普通索引

全文索引

空间索引

索引设计原则

复合索引

索引使用场景

索引维护

索引监控

本章小结