对超过 100 万行的表运行查询
我正在对我在“Where / Order by”中使用的所有列建立索引，我还能做些什么来加快查询速度吗？
查询非常简单，例如：
SELECT COUNT(*)
FROM TABLE
WHERE user = id
AND other_column = 'something'`
我使用 PHP 5，MySQL 客户端版本：4.1.22，我的表是 MyISAM。
I am indexing all the columns that I use in my Where / Order by, is there anything else I can do to speed the queries up?
The queries are very simple, like:
SELECT COUNT(*)
FROM TABLE
WHERE user = id
AND other_column = 'something'`
I am using PHP 5, MySQL client version: 4.1.22 and my tables are MyISAM.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
与您的 DBA 交谈。运行本地的
showplan等效项。对于像您的示例这样的查询，我怀疑列id和other_column上的覆盖索引会大大提高性能。 （我假设user是一个变量或niladic函数）。一个好的通用规则是索引中的列应按方差降序从左到右排列。也就是说，值变化最快的列应该是索引中的第一列，变化最慢的列应该是索引中的最后一列。似乎违反直觉，但就是这样。查询优化器喜欢尽快缩小范围。
Talk to your DBA. Run your local equivalent of
showplan. For a query like your sample, I would suspect that a covering index on the columnsidandother_columnwould greatly speed up performance. (I assumeuseris a variable or niladic function).A good general rule is the columns in the index should go from left to right in descending order of variance. That is, that column varying most rapidly in value should be the first column in the index and that column varying least rapidly should be the last column in the index. Seems counter intuitive, but there you go. The query optimizer likes narrowing things down as fast as possible.
如果您的所有查询都包含用户 ID，那么您可以首先假设 userid 应包含在每个索引中，可能作为第一个字段。 （我们可以假设用户 ID 具有高度选择性吗？即任何单个用户都不会拥有超过数千条记录？）
因此您的索引可能是：
如果您的用户 ID 确实具有选择性，例如几十条记录，那么仅该字段上的索引应该非常有效（亚秒返回）。
“user + otherfield”索引的好处是mysql甚至不需要查看数据记录。索引对于每条记录都有一个指针，并且它只能对指针进行计数。
If all your queries include a user id then you can start with the assumption that userid should be included in each of your indexes, probably as the first field. (Can we assume that the user id is highly selective? i.e. that any single user doesn't have more than several thousand records?)
So your indexes might be:
If your user id is really selective, like several dozen records, then just the index on that field should be pretty effective (sub-second return).
What's nice about a "user + otherfield" index is that mysql doesn't even need to look at the data records. The index has a pointer for each record and it can just count the pointers.