mysql - 解释的难以理解的响应以及如何决定在 mysql 中添加索引

Question

我们有一个非常大的数据库，并且以非常简单的方式提出问题，我无法决定是否应该在日期字段上添加索引。

我的查询是:我是否应该在表 A 的日期字段上添加索引，该表非常大并且是日期(格式:2013-02-26 18:52:23)。以下是我的查询:

      SELECT As.id 
      FROM As INNER JOIN A_items ON A_items.A_id = As.id AND A_items.type IN ('BilledItem', 'CustomerItem') 
      WHERE   (As.A_date BETWEEN '2013-01-15 18:52:23' AND '2013-01-30 18:52:23') AND A_items.category_id in ('20219') and A_items.product_id IN ('ACCDYHGYUDZNY7FZ')

现在，当我对此使用 explain 时，它会给出以下结果:-

      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: A_items
      type: ref
      possible_keys: index_A_items_on_A_id,index_A_items_on_product_id,i_type_parent_id_item_type
      key: index_A_items_on_product_id
      key_len: 258
      ref: const
      rows: 221122
      Extra: Using where
      *************************** 2. row ***************************
      id: 1
      select_type: SIMPLE
      table: As
      type: eq_ref
      possible_keys: PRIMARY
      key: PRIMARY
      key_len: 8
      ref: database.A_items.A_id
      rows: 1
      Extra: Using where
      2 rows in set (0.00 sec)

但是，当我从查询中删除 A_items.product_id IN ('ACCDYHGYUDZNY7FZ') 并运行此命令时:-

      explain
      SELECT As.id 
      FROM As INNER JOIN A_items ON A_items.A_id = As.id AND A_items.type IN ('BilledItem', 'CustomerItem') 
      WHERE   (As.A_date BETWEEN '2013-01-15' AND '2013-01-30') AND A_items.category_id in ('2005')

我明白了:-

      *************************** 1. row ***************************
      id: 1
      select_type: SIMPLE
      table: As
      type: ALL
      possible_keys: PRIMARY
      key: NULL
      key_len: NULL
      ref: NULL
      rows: 15427520    <--Notice this big number
      Extra: Using where
      *************************** 2. row ***************************
      id: 1
      select_type: SIMPLE
      table: A_items
      type: ref
      possible_keys: index_A_items_on_A_id,i_type_parent_id_item_type
      key: index_A_items_on_A_id
      key_len: 8
      ref: database.As.id
      rows: 1
      Extra: Using where
      2 rows in set (0.00 sec)

我的问题是为什么第一次查询中没有显示这么多行，以及如何决定是否在日期字段上添加索引？？？

Answer 1

这实际上取决于您的数据。您能做的最好的事情就是尝试使用该索引。索引是否有帮助取决于查询类型，在这种情况下还取决于日期范围。如果选择的范围很小，索引应该有所帮助。但是例如，如果表仅保留 1 个月的数据，而您选择 20-25 天的数据，优化器可能仍会选择忽略索引。优化器将始终尝试选择将产生较少行的索引(但它并不确切地知道它 - 这是通过索引基数估计的，这在某些情况下可能不准确)。

拥有额外的索引不会损害 SELECT，但是对于每个额外的索引，所有 INSERT 和 UPDATE 都会变慢，因为所有索引也需要更新。因此，如果与选择相比，您对该表执行的插入相对较少，那么您可以拥有这个额外的索引，即使它对这个特定查询没有帮助。