添加字段来优化 MySQL 查询
我有一个包含 3 个字段的 MySQL 表：
- 位置
- 变量
- 值
我经常使用以下查询：
SELECT *
FROM Table
WHERE Location = '$Location'
AND Variable = '$Variable'
ORDER BY Location, Variable
我的表中有超过一百万行，查询有点慢。如果我添加一个字段 VariableLocation（变量和位置的组合），是否会提高查询速度？我可以将查询更改为：
SELECT *
FROM Table
WHERE VariableLocation = '$Location$Variable'
ORDER BY VariableLocation
I have a MySQL table with 3 fields:
- Location
- Variable
- Value
I frequently use the following query:
SELECT *
FROM Table
WHERE Location = '$Location'
AND Variable = '$Variable'
ORDER BY Location, Variable
I have over a million rows in my table and queries are somewhat slow. Would it increase query speed if I added a field VariableLocation, which is the Variable and the Location combined? I would be able to change the query to:
SELECT *
FROM Table
WHERE VariableLocation = '$Location$Variable'
ORDER BY VariableLocation
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我会为列
location和variable添加覆盖索引：...尽管如果变量 &位置对是唯一的，它们应该是主键。
合并这些列可能会带来比其价值更多的悲伤。例如，如果您需要仅按
位置或变量提取记录，则必须在子查询中对值进行子字符串化。I would add a covering index, for columns
locationandvariable:...though if the variable & location pairs are unique, they should be the primary key.
Combining the columns will likely cause more grief than it's worth. For example, if you need to pull out records by
locationorvariableonly, you'd have to substring the values in a subquery.尝试添加一个覆盖这两个字段的索引，这样您仍然可以获得性能提升，同时还能保持数据易于理解，因为这两列似乎不应该合并，但您这样做只是为了获得性能。
Try adding an index which covers the two fields you should then still get a performance boost but also keep your data understandable because it wouldn't seem like the two columns should be combine but you are just doing it to get performance.
我建议不要合并这些字段。相反，创建一个索引，以与 ORDER BY 子句相同的顺序覆盖两个字段：
组合索引和键仅在涉及索引的所有字段或从左到右读取的这些字段的子集的查询中使用。或者换句话说：如果在 WHERE 条件中使用 location，则会使用该索引，但按变量排序不会使用该索引。
当尝试优化查询时，EXPLAIN命令非常有用： EXPLAIN in mysql文档
I would advise against combining the fields. Instead, create an index that covers both fields in the same order as your ORDER BY clause:
Combined indices and keys are only used in queries that involve all fields of the index or a subset of these fields read from left to right. Or in other words: If you use location in a WHERE condition, this index would be used, but ordering by variable would not use the index.
When trying to optimize queries, the EXPLAIN command is quite helpful: EXPLAIN in mysql docs
更正更新：
礼貌：@paxdiablo：
表中的一列没有任何区别。您所需要的只是两个列上的索引，MySQL 引擎将使用该索引。在表中添加一列实际上比这更糟糕，因为它破坏了 3NF 并浪费空间。请参阅http://dev.mysql.com/doc/refman /5.0/en/mysql-indexes.html 其中指出：
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;如果 col1 和 col2 上存在多列索引，则可以直接获取适当的行。Correction Update:
Courtesy: @paxdiablo:
A column in the table will make no difference. All you need is an index over both columns and the MySQL engine will use that. Adding a column in the table is actually worse than that since it breaks 3NF and wastes space. See http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html which states:
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly.