php - 如何以正确的方式对查询建立索引

Question

我正在尝试使我的数据库更加优化，并开始对其建立索引，但不确定如何正确执行。

我有这个查询:

$year = date("Y");
$thisYear = $year;
//$nextYear = $thisYear + 1; 
$sql = mysql_query("SELECT SUM(points) as userpoints
                                          FROM ".$prefix."_publicpoints
                                          WHERE date BETWEEN '$thisYear" . "-01-01' AND '$thisYear" . "-12-31' AND fk_player_id = $playerid");
$row = mysql_fetch_assoc($sql); 
$userPoints = $row['userpoints'];

$sql = mysql_query("SELECT 
                       fk_player_id
                    FROM ".$prefix."_publicpoints
                    WHERE date BETWEEN '$thisYear" . "-01-01' AND '$thisYear" . "-12-31'
                    GROUP BY fk_player_id
                    HAVING SUM(points) > $userPoints");
$row = mysql_fetch_assoc($sql);
$userWrank = mysql_num_rows($sql)+1;

我不确定如何对此进行索引？我已尝试对 fk_player_id 建立索引，但它仍然会查看所有行 (287937)。

我已经对日期字段建立了索引，这在解释中给了我这个:

<强>1简单的nf_公共(public)点范围IDX日期IDX日期3无效的143969使用带有推送条件的 where ；使用临时...

我还对同一张表进行了 2 次调用...可以一次完成吗？

我如何对此进行索引和/或者可以更智能地完成它？

Answer 1

您绝对应该花一些时间阅读有关索引的内容，有很多关于索引的文章，了解正在发生的事情很重要。

从广义上讲，索引对表的行强加了排序。

为了简单起见，假设一个表只是一个大的 CSV 文件。每当插入一行时，它都会插入到末尾。因此，表的“自然”顺序就是插入行的顺序。

假设您已将 CSV 文件加载到一个非常基本的电子表格应用程序中。该电子表格所做的只是显示数据，并按顺序对行进行编号。

现在假设您需要查找第三列中具有某个值“M”的所有行。鉴于您拥有的资源，您只有一种选择。您扫描表检查每行第三列的值。如果您有很多行，此方法(“表扫描”)可能需要很长时间!

现在想象一下，除了这个表之外，您还有一个索引。该特定索引是第三列中的值的索引。该索引以某种有意义的顺序(例如按字母顺序)列出了第三列中的所有值，并为每个值提供了该值出现的行号列表。

现在您有了一个很好的策略来查找第三列值为 M 的所有行!例如，您可以执行二分搜索!表扫描要求您查看 N 行(其中 N 是行数)，而二分搜索在最坏的情况下只要求您查看 log-n 索引条目。哇，这肯定容易多了!

当然，如果您有此索引，并且要向表中添加行(最后，因为这就是我们的概念表的工作方式)，则每次都需要更新索引。因此，在编写新行时您会做更多的工作，但在搜索某些内容时可以节省大量时间。

因此，一般来说，索引会在读取效率和写入效率之间进行权衡。如果没有索引，插入速度会非常快——数据库引擎只需向表中添加一行即可。添加索引时，引擎必须在执行插入时更新每个索引。

另一方面，读取速度变得更快。

希望这能涵盖您的前两个问题(正如其他人已经回答的那样 - 您需要找到适当的平衡点)。

你的第三个场景有点复杂。如果您使用 LIKE，索引引擎通常会帮助您将读取速度提高到第一个“%”。换句话说，如果您选择 WHERE 列 LIKE 'foo%bar%'，数据库将使用索引查找列以“foo”开头的所有行，然后需要扫描中间行集以查找子集包含“酒吧”。 SELECT ... WHERE 列 LIKE '%bar%' 无法使用索引。我希望你能明白为什么。

最后，您需要开始考虑多个列上的索引。概念是相同的，并且行为类似于 LIKE 的东西——本质上，如果你在 (a,b,c) 上有一个索引，引擎将继续尽可能地从左到右使用索引。因此，对 a 列的搜索可能会使用 (a,b,c) 索引，就像对 (a,b) 的搜索一样。但是，如果您搜索 WHERE b=5 AND c=1，则引擎需要执行全表扫描)

希望这有助于阐明一些问题，但我必须重申，您最好花几个小时来深入研究能够深入解释这些事情的好文章。阅读特定数据库服务器的文档也是一个好主意。查询规划器实现和使用索引的方式差异很大。

更多信息和示例请访问此处:http://blog.sqlauthority.com/category/sql-index/