优化的mySQL表索引结构，更快地选择大范围的日常数据

Question

我想知道在mySQL表中布局我的数据的最佳格式，以便以最快的方式查询它，以收集每日值的数组，供php进一步利用。

到目前为止，我已经列出了这个表：

item_id    price_date     price_amount
      1    2000-03-01             22.4
      2    2000-03-01            19.23
      3    2000-03-01             13.4
      4    2000-03-01            14.95
      1    2000-03-02             22.5
      2    2000-03-02            19.42
      3    2000-03-02             13.4
      4    2000-03-02            13.95

将item_id定义为索引。

另外，我使用的是：

"SELECT DISTINCT price_date FROM table_name" 

若要获取包含唯一日期列表的数组，请执行以下操作。

此外，循环中的代码部分(也是我的优化问题的焦点)目前写成：

"SELECT price_amount FROM table_name WHERE item_id = 1 ORDER BY price_date"

第二个"SELECT“语句实际上是在一个循环中，我在这个循环中选择/存储请求的每个item_id的每日价格。

All当前运行正常，并正确地从mySQL中提取数据，但是，上面列出的两个"SELECT“语句每次运行都需要大约4-5秒才能完成，并且当循环遍历100+产品以创建摘要时，加起来就是一个非常低效/缓慢的信息系统。

有没有更有效的方法可以构造mySQL表和/或SELECT语句来更快地检索结果？也许在不同的列上定义了不同的索引？我使用EXPLAIN命令为每个查询返回信息，但是不确定如何使用EXPLAIN信息来提高查询的效率。

提前感谢任何可能提供帮助的mySQL向导。

Answer 1

单列索引

我正在使用的

：

“从table_name中选择不同的price_date”

若要获取包含唯一日期列表的数组，请执行以下操作。

如果为price_date列创建索引，则可以更有效地执行此查询：

ALTER TABLE table_name ADD INDEX price_idx (price_date);

多列索引

此外，循环中的代码部分(也是我的优化问题的焦点)目前写成：

"SELECT price_amount FROM table_name WHERE item_id =1 ORDER BY price_date“

对于第二个查询，您应该创建一个涵盖item_id和price_date列的索引：

 ALTER TABLE table_name ADD INDEX item_price_idx (item_id, price_date);

Answer 2

我知道这有点晚了，但我偶然发现了这一点，并认为我应该把我的想法混入其中。

良好地使用索引对加速查询非常有帮助(Explain显示了一些非常棒的结果，其中包括为特定查询选择的索引(如果有的话))。然而，高效的PHP将会提供更多的帮助。

在本例中，您没有显示PHP，但看起来您提供了一个日期列表，然后循环查找该日期内的所有商品以获得价格。像这样做会更有效率：

Select item_id，price_amount from table_name where price_date= order by item_id，price_amount

使用price_date、item_id、price_amount上的索引(最好是唯一索引)

然后，您只需对结果SQL执行一次循环，而不是一个具有多个SQL连接的循环(如果您的SQL服务器与PHP服务器是分开的，这一点尤其正确，因为外部网络连接可能会产生开销)。

4-5秒的单个查询虽然非常慢)，但至少是100倍的倍数)，因此它将指示问题(非常大的表，没有键可用)或磁盘问题(可能)。