MySQL 数据库索引详细教程

MySQL 数据库索引详细教程

一、索引底子概念

1. 什么是索引

索引是数据库中用于提高查询性能的数据结构，类似于书籍的目录。它可以资助数据库引擎快速定位到表中的特定命据，而不需要扫描整个表。
2. 索引的优点

• 大大加快数据的检索速度
  
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
  
• 加速表与表之间的连接
  
• 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
  

3. 索引的缺点

• 创建和维护索引需要泯灭时间
  
• 索引需要占用物理空间
  
• 当对表中的数据进行增编削时，索引也要动态维护，低落了数据的维护速度
  

二、MySQL索引类型

1. B-Tree索引（默认索引类型）

1. -- 创建B-Tree索引
2. CREATE INDEX idx_name ON table_name(column_name);
4. -- 多列索引
5. CREATE INDEX idx_name ON table_name(col1, col2, col3);

复制代码

2. 唯一索引

1. CREATE UNIQUE INDEX idx_name ON table_name(column_name);

复制代码

3. 主键索引

1. -- 创建表时指定主键
2. CREATE TABLE table_name (
3.     id INT NOT NULL,
4.     PRIMARY KEY (id)
5. );
7. -- 修改表添加主键
8. ALTER TABLE table_name ADD PRIMARY KEY (column_name);

复制代码

4. 全文索引（FULLTEXT）

1. -- 创建全文索引
2. CREATE FULLTEXT INDEX idx_name ON table_name(column_name);
4. -- 使用全文索引查询
5. SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_term');

复制代码

5. 空间索引（SPATIAL）

1. CREATE SPATIAL INDEX idx_name ON table_name(column_name);

复制代码

6. 哈希索引（MEMORY引擎支持）

1. CREATE INDEX idx_name USING HASH ON table_name(column_name);

复制代码

三、索引的创建与管理

1. 创建索引

1. -- 基本语法
2. CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
3. ON table_name (column_name [(length)] [ASC|DESC], ...);
5. -- 示例
6. CREATE INDEX idx_customer_name ON customers(last_name, first_name);

复制代码

2. 查看索引

1. -- 查看表的索引
2. SHOW INDEX FROM table_name;
4. -- 或使用
5. SHOW KEYS FROM table_name;

复制代码

3. 删除索引

1. DROP INDEX index_name ON table_name;

复制代码

4. 修改索引

MySQL不支持直接修改索引，需要先删除再重建。
四、索引计划原则

1. 选择符合的列建立索引

• 经常作为查询条件的列（WHERE子句）
  
• 经常用于表连接的列
  
• 经常需要排序的列（ORDER BY子句）
  
• 经常需要分组统计的列（GROUP BY子句）
  

2. 避免过分索引

• 索引并非越多越好，每个额外的索引都会占用存储空间并低落写操作性能
  
• 一般建议单表索引不超过5-6个
  

3. 思量索引的选择性

选择性高的列更得当建立索引，选择性计算：

1. 选择性 = 不重复的索引值数量 / 表中记录总数

复制代码

4. 复合索引计划原则

• 最左前缀原则：索引(a,b,c)可以用于查询条件a、a,b或a,b,c，但不能用于b,c
  
• 将选择性高的列放在前面
  
• 将经常用于查询条件的列放在前面
  
• 将需要排序的列放在背面
  

五、索引优化技巧

1. 使用EXPLAIN分析查询

1. EXPLAIN SELECT * FROM customers WHERE last_name = 'Smith';

复制代码

2. 覆盖索引

当查询的列都包含在索引中时，MySQL可以直接从索引获取数据而不需要回表。

1. -- 假设有索引(last_name, first_name)
2. 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. 前缀索引

对于长字符串列，可以只索引前几个字符。

1. CREATE INDEX idx_name ON table_name(column_name(10));

复制代码

2. 使用索引进行排序

1. -- 如果索引是(a,b), 以下查询可以使用索引排序
2. SELECT * FROM table_name ORDER BY a, b;
4. -- 以下查询不能使用索引排序
5. SELECT * FROM table_name ORDER BY b;

复制代码

3. 冗余和重复索引

避免创立功能相同的索引：

1. -- 已有索引(a,b), 再创建索引(a)就是冗余的
2. -- 已有主键id, 再创建唯一索引id就是重复的

复制代码

4. 索引合并

MySQL偶然会使用多个索引并将结果合并：

1. -- 假设有索引a和b
2. SELECT * FROM table_name WHERE a = 1 OR b = 2;

复制代码

七、实践案例

案例1：电商平台用户表

1. CREATE TABLE users (
2.     user_id INT NOT NULL AUTO_INCREMENT,
3.     username VARCHAR(50) NOT NULL,
4.     email VARCHAR(100) NOT NULL,
5.     phone VARCHAR(20),
6.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
7.     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
8.     PRIMARY KEY (user_id),
9.     UNIQUE KEY idx_username (username),
10.     UNIQUE KEY idx_email (email),
11.     INDEX idx_phone (phone),
12.     INDEX idx_created (created_at)
13. );

复制代码

案例2：订单查询优化

1. -- 原始查询
2. SELECT * FROM orders WHERE customer_id = 100 AND status = 'shipped' ORDER BY order_date DESC;
4. -- 优化方案
5. 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企服之家，中国第一个企服评测及商务社交产业平台。