php - 是否应该在 MySQL 中使用/创建尽可能多的索引？

Question

我意识到，当为用于“ORDER BY”的列创建索引时，对 MySQL 查询的响应变得更快，例如

SELECT username FROM table ORDER BY registration_date DESC

现在我想知道应该创建哪些索引来优化请求时间。例如，我经常使用以下查询:

SELECT username FROM table WHERE
    registration_date > ".(time() - 10000)."

SELECT username FROM table WHERE
    registration_date > ".(time() - 10000)."
    && status='active'

SELECT username FROM table WHERE
    status='active'

SELECT username FROM table ORDER BY registration_date DESC

SELECT username FROM table WHERE
    registration_date > ".(time() - 10000)."
    && status='active'
    ORDER BY birth_date DESC

问题 1:我应该为前三种请求类型设置单独的索引吗？ (即“registration_date”列的一个索引，“status”列的一个索引，以及两者的组合的另一列？)

问题 2:不同的索引是否独立用于“WHERE”和“ORDER BY”？比如说，我有一个针对“status”和“registration_date”列的组合索引，以及另一个仅针对“birth_date”列的索引。我是否应该为三列(“status”、“registration_date”和“birth_date”)设置另一个组合索引？

Answer 1

索引或查询优化没有硬性规定。每个案例都需要考虑和检查。

但是，一般来说，您可以并且应该将索引添加到您经常排序或在 WHERE 语句中使用的列。 (问题 2 的答案 -- 不，相同的索引可能用于ORDER BY 和WHERE) 做多列索引还是单列索引取决于查询的频率。此外，您应该注意单列索引可能由 mySQL 使用索引合并优化组合:

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.

(更多阅读:http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html)

多列索引还要求您小心构造查询，使索引列的使用与索引中的列顺序相匹配:

MySQL cannot use an index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

SELECT * FROM tbl_name WHERE col1=val1; SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

请记住，索引确实有其自身的性能考虑因素——“过度索引”一个表是可能的。每次插入记录或修改索引列时，都必须重建索引。这确实需要资源，并且根据表的大小和结构，在索引构建操作处于事件状态时，它可能会导致响应速度下降。

使用 EXPLAIN 可以准确地找出您的查询中发生了什么。分析、试验，不要过度。霰弹枪方法不适用于数据库优化。

文档

• MySQL EXPLAIN - http://dev.mysql.com/doc/refman/5.0/en/explain.html
• MySQL 如何使用索引 - http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
• 索引合并优化 - http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html