MySQL索引优化
索引概述
1. 什么是索引
flowchart TD
subgraph 索引节点
A[根节点]
B[内部节点]
C[叶子节点]
end
A --> B
B --> C
索引是数据库中用于提高查询效率的数据结构，类似于书籍的目录。
2. 索引的优势
- 提高查询效率
- 加速表连接
- 实现唯一性约束
- 优化排序和分组
索引类型
1. 主键索引
-- 创建主键索引
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50)
);
-- 添加主键索引
ALTER TABLE users ADD PRIMARY KEY (id);
2. 普通索引
-- 创建普通索引
CREATE TABLE products (
id INT,
name VARCHAR(100),
INDEX idx_name (name)
);
-- 添加普通索引
CREATE INDEX idx_name ON products(name);
3. 唯一索引
-- 创建唯一索引
CREATE TABLE employees (
id INT,
email VARCHAR(100),
UNIQUE INDEX idx_email (email)
);
-- 添加唯一索引
CREATE UNIQUE INDEX idx_email ON employees(email);
4. 复合索引
-- 创建复合索引
CREATE TABLE orders (
id INT,
user_id INT,
create_time DATETIME,
INDEX idx_user_time (user_id, create_time)
);
5. 全文索引
-- 创建全文索引
CREATE TABLE articles (
id INT,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX idx_content (content)
);
索引优化策略
1. 索引设计原则
- 最左前缀原则
- 选择性原则
- 覆盖索引原则
- 索引列数限制
2. 索引使用分析
-- 查看索引使用情况
SHOW INDEX FROM table_name;
-- 分析表
ANALYZE TABLE table_name;
-- 优化表
OPTIMIZE TABLE table_name;
3. 索引失效场景
- 使用函数运算
- 使用OR条件
- 使用LIKE模糊查询
- 使用!=或<>操作符
- 使用IS NULL或IS NOT NULL
索引维护
1. 索引监控
-- 查看索引使用情况
SHOW INDEX FROM table_name;
-- 查看表状态
SHOW TABLE STATUS LIKE 'table_name';
-- 查看进程列表
SHOW PROCESSLIST;
2. 索引维护
-- 删除索引
DROP INDEX index_name ON table_name;
-- 重建索引
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name ADD INDEX index_name (column_name);
最佳实践
1. 索引选择
- 选择合适的索引类型
- 控制索引数量
- 考虑查询模式
- 注意索引大小
2. 性能优化
- 使用覆盖索引
- 避免索引失效
- 定期维护索引
- 监控索引使用
注意事项
-
索引设计
- 业务需求分析
- 查询模式分析
- 数据分布考虑
- 存储空间考虑
-
性能影响
- 写入性能
- 存储空间
- 维护成本
- 查询优化
-
运维管理
- 监控告警
- 性能分析
- 定期维护
- 故障处理
-
重要参数
- innodb_buffer_pool_size
- innodb_log_file_size
- max_heap_table_size
- key_buffer_size