我要评论在 mysql 中，索引是提高查询性能的重要工具。以下是关于 mysql 索引添加和删除的详细说明：
1. 添加索引
1.1 创建表时添加索引
-- 创建表时添加普通索引
create table users (
id int primary key auto_increment,
username varchar(50) not null,
email varchar(100) not null,
created_at timestamp default current_timestamp,
index idx_username (username), -- 普通索引
unique index idx_email (email) -- 唯一索引
);
-- 创建表时添加复合索引
create table orders (
id int primary key auto_increment,
user_id int not null,
order_date date not null,
amount decimal(10,2),
index idx_user_date (user_id, order_date) -- 复合索引
);1.2 使用 alter table 添加索引
-- 添加普通索引 alter table users add index idx_username (username); -- 添加唯一索引 alter table users add unique index idx_email (email); -- 添加主键索引（如果表没有主键） alter table users add primary key (id); -- 添加复合索引 alter table orders add index idx_user_date (user_id, order_date); -- 添加全文索引 alter table articles add fulltext index idx_content (content);
1.3 使用 create index 语句
-- 创建普通索引 create index idx_username on users (username); -- 创建唯一索引 create unique index idx_email on users (email); -- 创建复合索引 create index idx_user_date on orders (user_id, order_date); -- 创建全文索引 create fulltext index idx_content on articles (content);
2. 删除索引
2.1 使用 alter table 删除索引
-- 删除普通索引 alter table users drop index idx_username; -- 删除唯一索引 alter table users drop index idx_email; -- 删除主键索引 alter table users drop primary key;
2.2 使用 drop index 语句
-- 删除索引 drop index idx_username on users; -- 删除唯一索引 drop index idx_email on users;
3. 查看索引信息
-- 查看表的索引信息
show index from users;
-- 查看创建表的语句（包含索引信息）
show create table users;
-- 通过信息模式查看索引
select
table_name,
index_name,
column_name,
seq_in_index
from information_schema.statistics
where table_schema = 'your_database_name'
and table_name = 'users';4. 索引类型说明
4.1 不同类型的索引
-- 普通索引（最基本的索引，没有唯一性限制） create index idx_name on table_name (column_name); -- 唯一索引（确保列值的唯一性） create unique index idx_name on table_name (column_name); -- 主键索引（特殊的唯一索引，不允许null值） alter table table_name add primary key (column_name); -- 复合索引（多列组合的索引） create index idx_name on table_name (col1, col2, col3); -- 全文索引（用于全文搜索） create fulltext index idx_name on table_name (text_column); -- 空间索引（用于地理数据） create spatial index idx_name on table_name (spatial_column);
5. 实际示例
-- 创建示例表
create table employees (
id int auto_increment,
first_name varchar(50),
last_name varchar(50),
email varchar(100),
department_id int,
hire_date date,
salary decimal(10,2),
primary key (id)
);
-- 添加各种索引
-- 单列索引
alter table employees add index idx_last_name (last_name);
-- 唯一索引
alter table employees add unique index idx_email (email);
-- 复合索引
alter table employees add index idx_dept_hire (department_id, hire_date);
-- 查看索引
show index from employees;
-- 删除索引
alter table employees drop index idx_last_name;
drop index idx_email on employees;6. 最佳实践和注意事项
6.1 索引设计建议
-- 为经常查询的列创建索引 create index idx_frequently_queried on table_name (frequently_queried_column); -- 为外键列创建索引 create index idx_foreign_key on child_table (parent_id); -- 为where子句中的列创建索引 create index idx_where_condition on table_name (column_used_in_where); -- 为order by和group by的列创建索引 create index idx_order_group on table_name (column_used_for_ordering);
6.2 注意事项
- 不要过度索引：每个索引都会增加写操作的开销
- 选择合适的前缀长度：对于长文本列
create index idx_name on table_name (column_name(10)); -- 前缀索引
- 考虑索引选择性：高选择性的列更适合建索引
- 定期维护索引：使用
analyze table更新索引统计信息
6.3 性能监控
-- 检查索引使用情况 explain select * from users where username = 'john'; -- 查看未使用的索引（需要开启性能模式） select * from sys.schema_unused_indexes;
通过合理使用索引，可以显著提高 mysql 数据库的查询性能，但需要根据具体的查询模式和数据特征来设计和维护索引。
到此这篇关于mysql索引添加与删除方法实际示例的文章就介绍到这了,更多相关mysql索引添加与删除内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论