如何在 MySQL 中为查找表建立索引
我有一个 10M 行表 product ，其中包含 color (int)、price (float)、weight (float)、unitprice (int) 等字段...现在来自 Web 的用户动态生成查询，以随机条件（这里必须有颜色）和排序依据（例如
select * from product where color=1 and price >5 and price <220 and .... order by unitprice limit 75, 25;
select count(*) from product where color=3 and weight <500 and price <30 ... ;
如何索引表（InnoDB 或 NDB））从该表中查找数据，并具有大约 10 个可能的过滤字段（范围、排序） ...) 像这样在 MySQL 中吗？
编辑：根据我的理解，MySQL 很可能只会为查询选择一个索引，并且只有复合索引的左侧部分可以工作。显然，对所有可能的组合建立索引并不是一个可行的选择，例如(color,price,weight,create_date,unitprice,...)，(color,weight,price,create_date,unitprice , ....), (颜色、单价、重量, ....) .... 并非所有条件都必须出现在所有查询中。
您将如何对该表建立索引？
I have a 10M-row table product with fields like color (int), price (float), weight (float), unitprice (int), etc ... Now users from Web dynamically generate queries to lookup data from this table with random conditions (color is a must have here) and order-by such as
select * from product where color=1 and price >5 and price <220 and .... order by unitprice limit 75, 25;
select count(*) from product where color=3 and weight <500 and price <30 ... ;
How to index a table (InnoDB or NDB) with about 10 possible filtering fields (with range, sorting ...) like this in MySQL?
EDIT: In my understanding MySQL most likely will pick only one index for a query, and only the left hand part of a composite index will work. Obviously indexing all possible combinations is not a feasible option, such as (color, price, weight, create_date, unitprice, ....), (color, weight, price, create_date, unitprice, ....), (color, unitprice, weight, ....) .... Not all conditions are necessarily present in all queries.
What would you do to index this table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您想对任何字段进行快速查找/过滤/排序，则必须对所有字段建立索引。
如果颜色是必须的（即在每个查询中使用），那么最好为您拥有的每个
字段在(color, field)上创建复合索引。如果
color确实是每个常见查询的一部分，那么将聚集索引放在(color, Product_id)上也可能值得一试。If you want quick lookups/filters/sorts on any field, you must put indexes on all of them.
If color ist a must have (i.e. used in every query), it's best to make composite indexes on
(color, field)for eachfieldyou have.Putting the clustered index over
(color, product_id)might also be worth a try ifcolorreally is a part of every common query.正如托马拉克已经回答的那样，您可能应该为所有字段添加索引（以及复合索引，具体取决于您的查询）。但当然这会减慢写入速度。
如果您不确定如何使用索引，可以使用 解释一下命令。
As Tomalak already answered, you probably should add indexes for all fields (and composite indexes, depending on your queries). But of course this can slow writes down.
If you're not sure how the indexes will be used, you can use the explain command.