MySQL：唯一约束多列性能优化
我看到大量关于多个问题的独特约束的问题，但没有一个符合我具体寻找的内容。如果这是重复的，我深表歉意。
我有一张桌子： 表A_id tableB_id
我的主键是两个表的唯一约束，并且我在两个列上都有索引。两者也是其所尊重的表的主键。
如果表 A 可能有 10,000,000 行，而表 B 可能有 2,000,000 行，则表 B 处于此约束中的次数很可能会少得多。遗憾的是，当我制定独特的约束将 TableB 作为第一列时，它是否更优化，因为要搜索的 TableA 较少（如果是的话，为什么），或者它没有什么区别，因为它不会先搜索一个另一个则一一查看。
提前致谢
I see tons of questions about unique constrains on multiple questions but none that match what I am specifically looking for. If this is a duplicate of one, I apologies.
I have a table that is just:
tableA_id
tableB_id
My primary key is a unique constraint on both tables, and i have a index on both columns. Both are also primary keys to their respected tables.
If tableA is likely to have say 10,000,000 rows and table B to have say 2,000,000 rows, it is more likely that TableB will be in this constraint far less times. That being sad, is it more optimized when I am making my unique constraint to put TableB as the first column since there are less to search for, TableA (if so why), or it makes no difference as it does not search one first then the other, rather goes 1 by one looking at both.
Thanks in advance
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
通常建议将具有更多不同值的列放在复合索引的左侧。这会产生更具选择性的索引，更适合查找特定值。
引用形式MySQL 文档：
但我的印象是您似乎正在尝试优化表插入时的失败。如果对表的写入次数多于读取次数，并且大多数写入都是重复的，那么您可能是对的。但即使在后一种情况下，MySql 也需要检查另一列的唯一性。因此，最好先放置一个具有更多不同值的列。
It is usually recommended to put a column with more distinct values on the left in a composite index. That results in a more selective index, which is better for finding a specific value.
A quote form MySQL docs:
But I have an impression that you seem to be trying to optimize failures on inserts to the table. And if you have more writes to the table than reads and most of the writes are duplicates, then you are probably right. But even in the latter case, MySql will need to check the other column for uniqueness. Thus, it is still better to put first a column with more distinct values.
根据您的描述，我假设您有以下内容：
在这种情况下，不需要 tableA_id 上的单列索引，因为任何可以使用该索引的语句也可以使用主键中的索引。所以你至少可以删除tableA_id上的单列索引。
我认为 MySQL 的优化器不够聪明，无法对包含
WHERE tableB_id = 42的语句使用 PK 索引。因此，如果您在语句中使用该 ID 作为单个条件，您可能希望在该列上保留单列索引。
如果您始终使用两个 ID 查询该表，则无需保留单列索引。
From your description I assume you have the following:
In that case the single-column index on
tableA_idis not necessary because any statement that could make use of that one, can also use the index from the primary key. So you can at least drop the single-column index on tableA_id.I don't think MySQL's optimizer is smart enough to use the PK index for a statement that contains
WHERE tableB_id = 42.So you probably want to keep the single column index on that column if you use that ID as a single criteria in your statements.
If you always query that table using both IDs, there is no need to keep the single column index.