MySQL 数据库索引详细教程
MySQL 数据库索引详细教程一、索引底子概念
1. 什么是索引
索引是数据库中用于提高查询性能的数据结构，类似于书籍的目录。它可以资助数据库引擎快速定位到表中的特定命据，而不需要扫描整个表。
2. 索引的优点
[*]大大加快数据的检索速度
[*]通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
[*]加速表与表之间的连接
[*]在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
3. 索引的缺点
[*]创建和维护索引需要泯灭时间
[*]索引需要占用物理空间
[*]当对表中的数据进行增编削时，索引也要动态维护，低落了数据的维护速度
二、MySQL索引类型
1. B-Tree索引（默认索引类型）
-- 创建B-Tree索引
CREATE INDEX idx_name ON table_name(column_name);
-- 多列索引
CREATE INDEX idx_name ON table_name(col1, col2, col3);
2. 唯一索引
CREATE UNIQUE INDEX idx_name ON table_name(column_name);
3. 主键索引
-- 创建表时指定主键
CREATE TABLE table_name (
id INT NOT NULL,
PRIMARY KEY (id)
);
-- 修改表添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
4. 全文索引（FULLTEXT）
-- 创建全文索引
CREATE FULLTEXT INDEX idx_name ON table_name(column_name);
-- 使用全文索引查询
SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_term');
5. 空间索引（SPATIAL）
CREATE SPATIAL INDEX idx_name ON table_name(column_name);
6. 哈希索引（MEMORY引擎支持）
CREATE INDEX idx_name USING HASH ON table_name(column_name);
三、索引的创建与管理
1. 创建索引
-- 基本语法
CREATE INDEX index_name
ON table_name (column_name [(length)] , ...);
-- 示例
CREATE INDEX idx_customer_name ON customers(last_name, first_name);
2. 查看索引
-- 查看表的索引
SHOW INDEX FROM table_name;
-- 或使用
SHOW KEYS FROM table_name;
3. 删除索引
DROP INDEX index_name ON table_name;
4. 修改索引
MySQL不支持直接修改索引，需要先删除再重建。
四、索引计划原则
1. 选择符合的列建立索引
[*]经常作为查询条件的列（WHERE子句）
[*]经常用于表连接的列
[*]经常需要排序的列（ORDER BY子句）
[*]经常需要分组统计的列（GROUP BY子句）
2. 避免过分索引
[*]索引并非越多越好，每个额外的索引都会占用存储空间并低落写操作性能
[*]一般建议单表索引不超过5-6个
3. 思量索引的选择性
选择性高的列更得当建立索引，选择性计算：
选择性 = 不重复的索引值数量 / 表中记录总数
4. 复合索引计划原则
[*]最左前缀原则：索引(a,b,c)可以用于查询条件a、a,b或a,b,c，但不能用于b,c
[*]将选择性高的列放在前面
[*]将经常用于查询条件的列放在前面
[*]将需要排序的列放在背面
五、索引优化技巧
1. 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM customers WHERE last_name = 'Smith';
2. 覆盖索引
当查询的列都包含在索引中时，MySQL可以直接从索引获取数据而不需要回表。
-- 假设有索引(last_name, first_name)
SELECT first_name FROM customers WHERE last_name = 'Smith';
3. 索引条件下推(ICP)
MySQL 5.6+支持，将WHERE条件推到存储引擎层进行过滤。
4. 避免索引失效的场景
[*]使用不等于操作（!= 或 <>）
[*]使用函数操作索引列（如WHERE YEAR(date_column) = 2023）
[*]使用OR连接条件（除非全部OR条件都有索引）
[*]使用LIKE以通配符开头（如’%name’）
[*]类型转换（如字符串列与数字比较）
六、高级索引战略
1. 前缀索引
对于长字符串列，可以只索引前几个字符。
CREATE INDEX idx_name ON table_name(column_name(10));
2. 使用索引进行排序
-- 如果索引是(a,b), 以下查询可以使用索引排序
SELECT * FROM table_name ORDER BY a, b;
-- 以下查询不能使用索引排序
SELECT * FROM table_name ORDER BY b;
3. 冗余和重复索引
避免创立功能相同的索引：
-- 已有索引(a,b), 再创建索引(a)就是冗余的
-- 已有主键id, 再创建唯一索引id就是重复的
4. 索引合并
MySQL偶然会使用多个索引并将结果合并：
-- 假设有索引a和b
SELECT * FROM table_name WHERE a = 1 OR b = 2;
七、实践案例
案例1：电商平台用户表
CREATE TABLE users (
user_id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (user_id),
UNIQUE KEY idx_username (username),
UNIQUE KEY idx_email (email),
INDEX idx_phone (phone),
INDEX idx_created (created_at)
);
案例2：订单查询优化
-- 原始查询
SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped' ORDER BY order_date DESC;
-- 优化方案
CREATE INDEX idx_customer_status_date ON orders(customer_id, status, order_date DESC);
八、常见问题解答
Q1: 为什么我的索引没有收效？
可能缘故原由：
[*]查询条件不符合最左前缀原则
[*]使用了导致索引失效的操作符或函数
[*]表数据量太小，优化器认为全表扫描更快
[*]索引列的数据分布不匀称
Q2: 怎样知道应该为哪些列创建索引？
[*]使用慢查询日志找出实行慢的查询
[*]使用EXPLAIN分析查询实行筹划
[*]关注频繁出现在WHERE、JOIN、ORDER BY、GROUP BY子句中的列
Q3: 索引对INSERT、UPDATE、DELETE操作有什么影响？
[*]INSERT：需要同时更新索引，低落性能
[*]UPDATE：如果更新了索引列，需要更新索引
[*]DELETE：需要从索引中删除相应条目
九、总结
公道计划和使用索引是MySQL性能优化的关键。需要根据现实查询模式计划索引，定期检察和优化索引战略，避免过分索引和索引不敷两个极端。通过EXPLAIN分析查询实行筹划，结合业务特点不断调解索引战略，才能获得最佳的数据库性能。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]