MySQL 优化：解释“额外”列包含“使用位置”
所以我一直认为在Extra一栏看到“UsingWhere”是一件好事。然而，我正计划为我的同事提供一份关于解释解释的棕色袋午餐，但现在我不太确定。 MySQL 文档在有关“UsingWhere”的注释中这样说：
WHERE 子句用于限制哪些行与下一个表匹配或发送到客户端。除非您特别打算从表中获取或检查所有行，否则如果 Extra 值不是“Using where”并且表连接类型是“ALL”或“索引”，则查询中可能会出现问题。 即使您对 WHERE 子句的所有部分都使用索引，如果列可以为 NULL，您也可能会看到“使用 where”。
这使我相信，即使我的 WHERE 子句仅包含 WHERE 子句的部分内容，索引，如果列可以有 NULL 值，MySQL 仍然会检查行。
这是真的吗？如果是这样，如果不需要，我是否应该将列更改为不包含 NULL？我会看到速度提升吗？
So I always thought that seeing "Using Where" in the Extra column was a good thing. However, I was planning a brown bag lunch for my coworkers on intepreting EXPLAIN and now I'm not so sure. The MySQL doc says this in the notes about "Using Where":
A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. Even if you are using an index for all parts of a WHERE clause, you may see Using where if the column can be NULL.
This leads me to believe that even if my WHERE clause only contains parts of the index, that MySQL will still examine rows if the columns can have NULL values.
Is that true? If so, should I change the columns to not include NULL if I don't need it? Will I see a speed boost?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
可空列确实有开销，因为需要额外检查空条件。如果列不必为空，或者您的要求不允许为空，那么一定要使该列
不为空。至于索引，就看索引的构建了。如果索引是用
(a,b,c)定义的，并且您在 where 子句中使用b,c，则该索引不能用作a不在进行中。Nullable columns do have overhead, because of the extra need to check for the null condition. if a column doesn't have to be null, or your requirements don't allow for null, then definitely make the column
not null.As for the indexes, depends on the index construction. If the index is defined with
(a,b,c)and you're usingb,cin your where clause, this index cannot be used asais not in play.