设计表索引
在具有 id、几个键值和其他几个“数据”列的表中：
T1 : id , k1 , k2 , k3 , data1 , data2 , ...
id 是唯一的。
k1 有数百个不同的值。
对于 k1 的每个不同值，k2 有数千个不同值。
对于 k1,k2 的每个不同组合，都有几十（或更多一点）行，其中通常 k3 是所有这些记录的空字符串，而其他一些 k3 值每个 (k1,k2) 只会出现一次，并且偶尔会出现两三次。
我想要获取一组记录其中 k1=K1V 和 K2=K2V 和 K3=K3V， 但我也知道这相当于询问 where k1=K1V and K3=K3V 或 where k2=K2V and K3=K3V 因为 K2 的任何特定值都会始终具有相同的 K1 值。
或者也许我应该询问 where K3=K3V and K2=K2V and K1=K1V 或 where K3=K3V and K1=K1V 或 where K3=K3V and K2=K2V ？ （因为K3列中空串值的忏悔）。
进行此查询的最佳方式是什么？我应该为此创建什么索引？
如果已知 K2 的不同值与 K1 的不同值的数量差异很大，它会改变答案吗？ （即一些 K1V 有 5 个与之关联的 K2V，而另一些则有数千个）
In a table with an id, several key values and several other "data" columns:
T1 : id , k1 , k2 , k3 , data1, data2, ...
id is unique.
There are several hundreds of distinct values for k1.
For each distinct value of k1 there are several thousands of distinct values for k2.
For each distinct combination of k1,k2 there are a few tens (or a little bit more) rows, where usually k3 be an empty string for all those records and some other k3 values it will only appear once per (k1,k2) and occasionally it will appear two or three times.
I will want to get a set of records where k1=K1V and K2=K2V and K3=K3V,
but I also know that this is equivalent to asking about where k1=K1V and K3=K3V or where k2=K2V and K3=K3V because the any specifiv value for K2 will always have the same K1 value.
or maybe I should ask about where K3=K3V and K2=K2V and K1=K1V or where K3=K3V and K1=K1V or where K3=K3V and K2=K2V ? (because of the repentance of the empty string value in the K3 column).
What would be the best way to make this query ? and what index should I create for this?
would it change the answer if the variance in the amount of distinct values for K2 on the different values for K1 was known to be big? (i.e. some K1V's have 5 K2V's associated with them while others have thousends)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果键之间存在依赖关系，那么听起来您的表没有标准化。我建议首先将表标准化为第三范式，然后在外键上放置索引（至少）。然后应该添加额外的索引来支持特定的查询。
It sounds like your table is not normalized if there are dependencies between keys. I would suggest normalizing your table to 3rd normal form first, and then putting indexes on the foreign keys (at a minimum). Additional indexes should then be added to support specific queries.