MySQL索引优化之适合构建索引的几种情况详解

1. WHERE过滤条件的列

在对一张表进行查询时，如果需要WHERE语句来进行过滤，那么过滤条件的列是应该被索引化的。这可以使查询结果更加高效，因为索引可以帮助数据库在更快的时间内找到需要查询的数据。

示例1：

如果我们需要查询一张学生表格中成绩大于90分的学生，我们需要构建一个成绩(score)的索引。

SELECT * FROM student WHERE score > 90;

示例2：

假设我们有一个文章的评论表格，需要查询某个用户最近的评论。这里我们需要对用户ID(user_id)列进行索引化。

SELECT * FROM article_comments WHERE user_id = 1234 ORDER BY created_at DESC LIMIT 10;

2. JOIN表操作的列

如果我们需要对两个表进行JOIN操作，则JOIN操作的列也需要被索引化，这将使查询更加快速和高效。

示例：

假设我们有一个订单表和一个订单明细表，我们需要查询某个订单的所有明细信息，那么订单主键(ID)和订单明细表的主键(order_id)都需要被索引化。

SELECT * FROM orders JOIN order_details ON orders.id = order_details.order_id WHERE orders.id = 1234;

3. 列的基数（Cardinality）

对于基数较高（即唯一性高）的列，比如用户ID(user_id)或订单ID(order_id)等，建立索引将会大大提高查询效率。

示例：

假设我们有一个用户表，用户ID为唯一标示用户的列，我们需要查询某个用户的信息，那么用户ID(user_id)需要被索引化。

SELECT * FROM users WHERE user_id = 1234;

4. 取值范围较小的列

如果一个列只有很少的不同取值范围，那么该列对应的索引将会更加高效。

示例：

假设我们有一个电商网站的订单表，该表有一个列表示订单状态(status)，状态只有"待发货"、"待收货"、"已完成"等少数状态，那么该列需要被索引化。

SELECT * FROM orders WHERE status = "已完成";

结语

通过上述几种情况的讲解，我们可以看出，合理的索引构建可以极大的提升MySQL的查询效率。但是过多或不必要的索引会使数据库变慢，在实际开发中请慎重使用。