MySQL - SQL_BIG_SELECTS [英] MySQL - SQL_BIG_SELECTS
问题描述
嘿，我一直在研究 SQL_BIG_SELECTS，但到目前为止 MySQL 文档一直没有帮助.我正在寻找一些见解，以防止出现以下错误.
Hey, I've been investigating SQL_BIG_SELECTS, but the MySQL documentation so far has been pretty unhelpful. I'm looking for some insight as to preventing errors like the one below from appearing.
错误 1104:SELECT 会检查太多记录并且可能需要很长时间.检查您的 WHERE 并使用 SET OPTION SQL_BIG_SELECTS=1 如果 SELECT 没问题
ERROR 1104: The SELECT would examine too many records and probably take a very long time. Check your WHERE and use SET OPTION SQL_BIG_SELECTS=1 if the SELECT is ok
- 在多少行时，MySQL 决定查询是BIG SELECT"?
- 适当的索引通常能解决这个问题吗?
- SQL_BIG_SELECTS 是否被视为最后的手段"，还是一种好的做法?
- 如何在配置中设置SQL_BIG_SELECTS=1"(无需执行查询)?
- 还有其他值得了解的替代方案吗?
提前致谢！
推荐答案
MySQL 根据max_join_size"的值确定查询是否为大选择".如果查询可能需要检查的行数超过此数量，则它会将其视为大选择".使用show variables"查看最大连接大小的值.
MySQL determines whether or not a query is a 'big select' based on the value of 'max_join_size'. If the query is likely to have to examine more than this number of rows, it will consider it a 'big select'. Use 'show variables' to view the value of the max join size.
我相信索引和特别好的 where 子句可以防止这个问题的发生.
I believe that indexing and particular a good where clause will prevent this problem from occuring.
SQL_BIG_SELECTS 用于防止用户意外执行过大的查询.可以在mysql.cnf中设置为ON或者启动时使用命令行选项.
SQL_BIG_SELECTS is used to prevent users from accidentally executing excessively large queries. It is okay to set it to ON in mysql.cnf or using the command-line option at startup.
您可以在 my.cnf 中或在服务器启动时设置 SQL_BIG_SELECTS.也可以使用 SET SESSION SQL_BIG_SELECTS=1 以会话为基础进行设置.
You can set SQL_BIG_SELECTS in my.cnf or at server startup. It can also be set on a session basis with SET SESSION SQL_BIG_SELECTS=1.
我想不到.我只会检查您的查询以确保您确实需要使用它.我们的服务器默认开启，max_join_size 非常大.
Not that I can think of. I would just check your query to make sure that you really need to use it. Our servers have it turned on by default, and max_join_size is very large.
这篇关于MySQL - SQL_BIG_SELECTS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
