使用IN运算符优化MySQL查询 [英] Optimizing MySQL queries with IN operator

问题描述

我有一个MySQL数据库，其中的产品表很大.他们每个人都有自己的id和categoryId字段，其中有一个类别ID属于该产品.现在，我有一个查询，它从给定的类别中提取产品，例如:

I have a MySQL database with a fairly large table where the products are. Each of them has its own id and categoryId field where there is a category id belongs to this product. Now I have a query that pulls out products from given categories such as:

SELECT * FROM products WHERE categoryId IN ( 1, 2, 3, 4, 5, 34, 6, 7, 8, 9, 10, 11, 12 )

当然，可以使用WHERE子句和ORDER BY排序，但是这不是.假设这些产品为25万，每天的访问量超过10万.在这种情况下，slow_log表会以较大的生成时间来注册这些查询的权重.

Of course, come a WHERE clause and ORDER BY sort but not in this thing. Let's say that these products is 250k and the visits are over 100k per day. Under such conditions in the table slow_log registered weight of these queries with large generation time.

您对如何优化给定的问题有任何想法吗?

Do you have any ideas how to optimize the given problem?

表引擎是MyISAM.

Table engine is MyISAM.

推荐答案

在这种情况下，对categoryId的索引将无济于事，IN(...)查询将始终产生序列扫描而不是索引查找.

Index on categoryId won't help in this case, IN (...) queries will produce sequence scan instead of index lookup anyway.

我会考虑首先重新设计系统，以摆脱多个类别选择，如果不合适，则缓存查询结果.

I would consider first redesigning the system to get rid of multiple category select and if it is not appropriate, caching query results.

例如，您可以创建一个帮助程序表items_category_groups(hash，item_id)，并在对多个类别进行客户端查询之后，对它们的组合ID进行哈希处理并查找此表.如果找不到，请进行昂贵的查询并填写该表.如果找到，请进行廉价查询以连接这些表.其他缓存工具(如memcached)也可以使用.

For example, you can create a helper table items_category_groups(hash, item_id) and after client query on multiple categories hash their combined ids and lookup this table. If not found, make an expensive query and fill this table. If found, make a cheap query joining these tables. Other caching tools like memcached will work too.

这篇关于使用IN运算符优化MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！