当前位置：首页 > news >正文

MySQL索引操作全指南：创建、查看、优化

news 2025/7/14 18:24:17

MySQL 索引的核心操作语法，涵盖创建、查看、删除及优化场景，附详细示例：

一、创建索引

1. 建表时创建

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,       -- 主键索引 (自动创建)username VARCHAR(50) UNIQUE,             -- 唯一索引email VARCHAR(100),age INT,city VARCHAR(20),INDEX idx_age (age),                     -- 普通单列索引INDEX idx_city_age (city, age),          -- 联合索引FULLTEXT INDEX ft_idx_email (email)      -- 全文索引 (MyISAM/InnoDB)
) ENGINE=InnoDB;

2. 表已存在时添加

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_email (email);
-- 或
CREATE INDEX idx_email ON users (email);-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX uni_username (username);-- 添加联合索引
ALTER TABLE users ADD INDEX idx_city_age (city, age);-- 添加全文索引 (适用于文本搜索)
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_content (title, content);-- 添加前缀索引 (限制索引长度)
ALTER TABLE logs ADD INDEX idx_url_prefix (url(20));  -- 只索引url前20字符

二、查看索引

-- 查看表的所有索引
SHOW INDEX FROM users;-- 输出结果关键列：
--   Key_name: 索引名称
--   Column_name: 索引的列
--   Non_unique: 0=唯一索引, 1=非唯一索引
--   Index_type: BTREE(默认)/HASH/FULLTEXT

示例输出：

Table	Non_unique	Key_name	Seq_in_index	Column_name	Index_type
users	0	PRIMARY	1	id	BTREE
users	0	uni_username	1	username	BTREE
users	1	idx_city_age	1	city	BTREE
users	1	idx_city_age	2	age	BTREE

三、删除索引

-- 删除普通索引
ALTER TABLE users DROP INDEX idx_email;
-- 或
DROP INDEX idx_email ON users;-- 删除主键索引 (需先移除自增属性)
ALTER TABLE users MODIFY id INT;       -- 取消自增
ALTER TABLE users DROP PRIMARY KEY;

四、索引优化场景语法

1. 强制使用索引

SELECT * FROM users FORCE INDEX (idx_city_age) 
WHERE city = 'Beijing' AND age > 25;

2. 忽略索引

SELECT * FROM users IGNORE INDEX (idx_age) 
WHERE age > 30;  -- 测试无索引时的性能

3. 重建索引 (解决碎片化)

-- InnoDB 表重建
ALTER TABLE users ENGINE=InnoDB;
-- 或优化表
OPTIMIZE TABLE users;

五、常见问题处理

1. 重复索引检测

-- 查询冗余索引 (schema替换为数据库名)
SELECT table_name,index_name,GROUP_CONCAT(column_name) AS columns
FROM information_schema.STATISTICS 
WHERE table_schema = 'your_db'
GROUP BY table_name, index_name
HAVING COUNT(*) > 1;

2. 索引未生效排查

EXPLAIN SELECT * FROM users WHERE age = 30 AND city = 'Shanghai';

检查 EXPLAIN 结果：

type 为 ref/range → 索引生效
type 为 ALL → 全表扫描 (索引失效)
key 列为实际使用的索引名称

六、使用规范

场景	推荐操作
主键字段	用 `INT/BIGINT` 自增，避免使用 UUID
频繁查询的 WHERE 条件列	创建单列或联合索引
长文本字段	用前缀索引 `INDEX (column(20))` 或全文索引
JOIN 关联列	确保两表关联字段有相同类型的索引
排序/分组字段	对 `ORDER BY` 或 `GROUP BY` 的列建索引
低区分度字段 (如性别)	不单独建索引，可与其他列建联合索引 (如 `INDEX(gender, age)`)

示例：完整工作流

-- 1. 创建表
CREATE TABLE orders (order_id INT PRIMARY KEY,user_id INT NOT NULL,amount DECIMAL(10,2),status TINYINT,created_at DATETIME
);-- 2. 添加常用索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id),ADD INDEX idx_created_at (created_at),ADD INDEX idx_status_amount (status, amount);-- 3. 查看索引
SHOW INDEX FROM orders;-- 4. 删除冗余索引 (假设不需要按amount单独查)
DROP INDEX idx_status_amount ON orders;-- 5. 重建表优化
OPTIMIZE TABLE orders;