MySQL教程FG082-MySQL索引创建与管理

内容简介：本文主要介绍MySQL相关知识，包括安装、配置、优化等内容。风哥教程参考MySQL官方文档MySQL SQL Syntax、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn 02 学习交流加群风哥微信: itpux-com

Part01-基础概念与理论知识

索引是数据库中提高查询性能的重要结构，正确创建和管理索引对于优化数据库性能至关重要。本文将详细介绍MySQL中索引的创建、查看、修改和删除等操作，帮助开发者和DBA更好地管理数据库索引。 03 学习交流加群风哥QQ113257174

Part02-生产环境规划与建议

2.1 使用CREATE INDEX语句

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

-- 创建唯一索引
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

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

-- 创建前缀索引
CREATE INDEX idx_employees_name_prefix ON employees(name(10));

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

-- 创建空间索引
CREATE SPATIAL INDEX idx_locations_coordinates ON locations(coordinates);

2.2 使用ALTER TABLE语句

-- 添加普通索引
ALTER TABLE employees ADD INDEX idx_employees_name(name);

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

-- 添加复合索引
ALTER TABLE employees ADD INDEX idx_employees_department_salary(department_id, salary);

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

-- 添加空间索引
ALTER TABLE locations ADD SPATIAL INDEX idx_locations_coordinates(coordinates);

2.3 在CREATE TABLE时创建索引

-- 在创建表时创建索引
CREATE TABLE employees (
    id INT PRIMARY KEY, -- 主键索引
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE, -- 唯一索引
    department_id INT,
    salary DECIMAL(10,2),
    INDEX idx_employees_name(name), -- 普通索引
    INDEX idx_employees_department_salary(department_id, salary) -- 复合索引
);

-- 创建带全文索引的表
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX idx_articles_content(content)
);

-- 创建带空间索引的表
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    coordinates POINT,
    SPATIAL INDEX idx_locations_coordinates(coordinates)
);

Part03-生产环境项目实施方案

3.1 普通索引

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

-- 或
ALTER TABLE employees ADD INDEX idx_employees_name(name);

3.2 唯一索引

-- 创建唯一索引
CREATE UNIQUE INDEX idx_employees_email ON employees(email);

-- 或
ALTER TABLE employees ADD UNIQUE INDEX idx_employees_email(email);

3.3 复合索引

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

-- 或
ALTER TABLE employees ADD INDEX idx_employees_department_salary(department_id, salary);

3.4 全文索引

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

-- 或
ALTER TABLE articles ADD FULLTEXT INDEX idx_articles_content(content);

3.5 空间索引

-- 创建空间索引
CREATE SPATIAL INDEX idx_locations_coordinates ON locations(coordinates);

-- 或
ALTER TABLE locations ADD SPATIAL INDEX idx_locations_coordinates(coordinates);

3.6 前缀索引

-- 创建前缀索引
CREATE INDEX idx_employees_name_prefix ON employees(name(10));

-- 或
ALTER TABLE employees ADD INDEX idx_employees_name_prefix(name(10));

Part04-生产案例与实战讲解

4.1 使用SHOW INDEX语句

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

-- 查看表的所有索引（更简洁的方式）
SHOW INDEXES FROM employees;

-- 查看表的所有索引（另一种方式）
SHOW KEYS FROM employees;

4.2 查询INFORMATION_SCHEMA.STATISTICS表

-- 查询表的索引信息
SELECT 
    table_name, 
    index_name, 
    column_name, 
    seq_in_index, 
    index_type 
FROM 
    information_schema.statistics 
WHERE 
    table_schema = 'your_database' 
    AND table_name = 'employees' 
ORDER BY 
    index_name, seq_in_index;

Part05-风哥经验总结与分享

5.1 重命名索引

-- 重命名索引（MySQL 5.7+）
ALTER TABLE employees RENAME INDEX idx_employees_name TO idx_emp_name;

5.2 修改索引类型

-- 修改索引类型需要先删除旧索引，再创建新索引
-- 删除旧索引
DROP INDEX idx_employees_name ON employees;
-- 创建新索引
CREATE INDEX idx_employees_name ON employees(name) USING BTREE;

6. 删除索引

6.1 使用DROP INDEX语句

-- 删除索引
DROP INDEX idx_employees_name ON employees;

-- 删除唯一索引
DROP INDEX idx_employees_email ON employees;

6.2 使用ALTER TABLE语句

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

-- 删除唯一索引
ALTER TABLE employees DROP INDEX idx_employees_email;

-- 删除主键索引（需要先删除自增属性）
ALTER TABLE employees MODIFY COLUMN id INT;
ALTER TABLE employees DROP PRIMARY KEY;

7. 索引管理最佳实践

7.1 定期检查索引

定期查看表的索引状态
检查索引是否被使用
识别冗余索引

7.2 优化索引结构

删除不使用的索引
合并重复索引
优化复合索引的顺序

7.3 监控索引使用情况

-- 启用性能模式
SET GLOBAL performance_schema = ON;

-- 查看索引使用情况
SELECT 
    object_schema, 
    object_name, 
    index_name, 
    count_star AS access_count 
FROM 
    performance_schema.table_io_waits_summary_by_index_usage 
WHERE 
    object_schema = 'your_database' 
    AND object_name = 'employees' 
ORDER BY 
    access_count DESC;

7.4 索引维护

定期重建索引
优化表结构
监控索引碎片

8. 常见错误和解决方案

8.1 索引创建错误

错误信息	原因	解决方案
ERROR 1061 (42000): Duplicate key name ‘idx_employees_name’	索引名重复	使用不同的索引名
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes	索引键长度超过限制	使用前缀索引或缩短列长度
ERROR 1214 (HY000): The used table type doesn’t support FULLTEXT indexes	存储引擎不支持全文索引	使用InnoDB或MyISAM存储引擎

8.2 索引删除错误

错误信息	原因	解决方案
ERROR 1091 (42000): Can’t DROP ‘idx_employees_name’; check that column/key exists	索引不存在	检查索引名是否正确
ERROR 1025 (HY000): Error on rename of ‘./test/#sql-1234’ to ‘./test/employees’ (errno: 150)	外键约束限制	先删除相关的外键约束

9. 示例演示

9.1 完整的索引管理示例

-- 创建测试表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10,2),
    hire_date DATE
);

-- 插入测试数据
INSERT INTO employees (name, email, department_id, salary, hire_date) VALUES
('风哥1号', 'zhangsan@fgedu.net.cn', 1, 10000, '2023-01-15'),
('风哥2号', 'lisi@fgedu.net.cn', 1, 15000, '2022-06-10'),
('王五', 'wangwu@fgedu.net.cn', 2, 6000, '2023-03-20'),
('赵六', 'zhaoliu@fgedu.net.cn', 3, 9000, '2021-11-05'),
('孙七', 'sunqi@fgedu.net.cn', 1, 12000, '2022-09-01');

-- 创建索引
CREATE INDEX idx_employees_name ON employees(name);
CREATE UNIQUE INDEX idx_employees_email ON employees(email);
CREATE INDEX idx_employees_department_salary ON employees(department_id, salary);
CREATE INDEX idx_employees_hire_date ON employees(hire_date);

-- 查看索引
SHOW INDEX FROM employees;

-- 重命名索引
ALTER TABLE employees RENAME INDEX idx_employees_name TO idx_emp_name;

-- 查看修改后的索引
SHOW INDEX FROM employees;

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

-- 查看最终索引
SHOW INDEX FROM employees;

9.2 索引使用示例

-- 使用索引进行查询
EXPLAIN SELECT * FROM employees WHERE department_id = 1 AND salary > 10000;
EXPLAIN SELECT * FROM employees WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

-- 分析索引使用情况
SET GLOBAL performance_schema = ON;

-- 执行查询
SELECT * FROM employees WHERE department_id = 1 AND salary > 10000;
SELECT * FROM employees WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

-- 查看索引使用情况
SELECT 
    object_schema, 
    object_name, 
    index_name, 
    count_star AS access_count 
FROM 
    performance_schema.table_io_waits_summary_by_index_usage 
WHERE 
    object_schema = 'your_database' 
    AND object_name = 'employees' 
ORDER BY 
    access_count DESC;

10. 总结

索引的创建与管理是MySQL数据库性能优化的重要环节。本文详细介绍了MySQL中索引的创建、查看、修改和删除等操作，以及不同类型索引的创建方法和最佳实践。 04 风哥提示：

在实际应用中，应该根据具体的查询需求和数据特点来创建和管理索引。合理的索引设计可以显著提高数据库的查询性能，而过多或不合理的索引则会影响数据库的写入性能和存储空间。 05更多学习教程公众号风哥教程itpux_com

通过定期检查和优化索引结构，监控索引使用情况，以及遵循索引管理的最佳实践，可以确保数据库系统的高效运行，提升应用系统的响应速度和用户体验。 06 from mysql视频:www.itpux.com

GF-MySQL数据库培训文档系列

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html