如何在MySQL上创建有效的索引

Question

有没有人可以帮助我如何创建索引，以便我的查询能够顺利执行。

目前，我有以下查询，它返回8k+或记录。但它需要2秒或更长时间才能完成。tblproduction上的当前记录是16million+

SELECT COUNT(fldglobalid) AS PackagesDone
  FROM tblproduction
 WHERE fldemployeeno = 'APD100401'
   AND fldstarttime BETWEEN '2013-08-14 07:18:06' AND '2013-08-14 16:01:58'
   AND fldshift = 'B'
   AND fldprojectgroup = 'FTO'
   AND fldGlobalID <> 0;

我有以下当前索引，但它的查询执行时间仍然较长

Index_1
  fldEmployeeNo
  fldStartTime

Index_2
  fldEmployeeNo
  fldTask
  fldTaskStatus

Index_3
  fldGlobalId
  fldProjectGroup

Index_4
  fldGlobalId

我已经通过FORCE_Index使用了所有这些索引，但是查询的执行时间仍然更长。

敬请指教，谢谢！

Answer 1

这开始是戈登·林诺夫的回答，但太长了。

It would be better to include fldGlobalId in the index as well -不，它不会-这对性能是适得其反的-它不会提高检索数据的速度(查询不用于不等式)，但会导致更频繁的索引更新，从而增加索引碎片(因此可能会恶化SELECT的性能)，并降低插入和更新的性能。

理想情况下，您应该设计您的模式来优化所有查询-这是一项相当大的任务，但由于您只提供了一个...

目前的查询将只使用单个索引进行解析，因此该索引应该包括查询中除非匹配(即fldGlobalID)之外的所有具有谓词的字段。

字段的顺序很重要:如果没有使用不同谓词集的其他查询，则相对基数最高的字段应该排在第一位。如果不分析数据(从表中选择COUNT(DISTINCT字段)/COUNT(*))，就很难知道这是什么，但猜测顺序应该是fldstarttime、fldemployeeno、fldprojectgroup、fldshift

如果对fldshift的依赖来自fldemployeeno (即员工总是，或者至少超过90%的时间)，那么在索引中包含fldshift只会增加它的大小，而不会使它更有效率。

你没有说你使用的是哪种类型的索引--btree可以处理范围，散列和不平等。由于这里的最高基数谓词使用范围，因此btree索引将比基于散列的索引更有效。

Answer 2

您可以使用一个索引。以下是略微重新排列的查询：

SELECT COUNT(fldglobalid) AS PackagesDone
  FROM tblproduction
 WHERE fldemployeeno = 'APD100401'
   AND fldshift = 'B'
   AND fldprojectgroup = 'FTO'
   AND fldstarttime BETWEEN '2013-08-14 07:18:06' AND '2013-08-14 16:01:58'
   AND fldGlobalID <> 0;

(我只是将相等条件一起移到了where子句的顶部)。

查询应该使用tblproduction(fldemployeeno, fldshift, fldprojectgroup, fldstarttime)上的索引。最好也在索引中包含fldGlobalId，这样索引就可以“覆盖”查询(查询中的所有列都在索引中)。所以，试试这个索引：

tblproduction(fldemployeeno, fldshift, fldprojectgroup, fldstarttime, fldGlobalID)