在mysql中使用where子句时应该使用索引吗？
我有一个存储大约 50k 行的表，这是我的查询：
select * from video where status = 1 and filter = 1 and category = 4
这些列是tinyint，我想知道我应该在这些列上建立索引吗？
i have a table that stores about 50k rows and here's my query :
select * from video where status = 1 and filter = 1 and category = 4
these columns are tinyint and i wonder should i do index on these columns ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不！
您应该始终考虑索引选择性！如果索引上的 where 子句的结果将返回表的 20% 以上，则不会使用该索引，但会保留该索引。
例如，您的视频状态为 1、2、3，那么您就有 3 种可能的状态。如果这些指数分布良好，我们可以认为其中大约为 33.3%。然后，查询：
不会使用该索引！相反，将使用全表扫描。
请阅读有关内容。这确实很重要，也是一个常见的错误。
Google 搜索
No!
You should always consider index selectivity! If the result of a where clause on an index will return more than 20% of the table, then the index is NOT used, but it's mantained.
So, for example, the status of your video will be 1, 2, 3, then you have 3 posible states. If those indexes are well distributed we can think that will be 33.3% of them aprox. Then, the query:
will NOT use that index! Instead, a FULL TABLE SCAN would be used.
Please, read about it. It's really important and a common mistake.
Google search
如果您为此查询创建索引，请尝试
(status,filter,category)（顺序可能不同）。但这取决于表行中具有
(status,filter,category) = (1,1,4)的百分比。百分比越小，使用索引的查询运行得越好。正如 santiagobasulto 所指出的，如果索引选择性不高，索引无论如何都不会用于查询，但会运行表的完整扫描。If you create an index for this query, try
(status, filter, category)(the order may be different).But it depends on what percent of your table rows have
(status, filter, category) = (1,1,4). The smallest percent, the better your query will run using the index. As santiagobasulto noted, if the index selectivity is not high, the index will not be used for the query anyway but a full scan of the table will be run.我肯定会使用索引。他们使选择更快。但如果表中有很多插入，那么我会再考虑一下。插入语句上的索引会增加很多过载，并且插入语句会很慢，因为每次插入时它都必须重新构造索引。
I would definitely use indexes. They make the select faster. But then if there are a lot of inserts into the table, then I would think again. Indexes on insert statements adds a lot of overload and your insert statement will be slow as it has to restructure the indexes every time you make an insert.