MySQL - SQL_BIG_SELECTS

2021-11-20 00:00:00 select mysql

嘿，我一直在研究 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

  1. 在多少行时，MySQL 决定查询是BIG SELECT"?
  2. 适当的索引通常能解决这个问题吗?
  3. SQL_BIG_SELECTS 是否被视为最后的手段"，还是一种好的做法?
  4. 如何在配置中设置SQL_BIG_SELECTS=1"(无需执行查询)?
  5. 还有其他值得了解的替代方案吗?

提前致谢！

推荐答案

  1. MySQL 根据max_join_size"的值确定查询是否为大选择".如果查询可能需要检查的行数超过此数量，则它会将其视为大选择".使用show variables"查看最大连接大小的值.

  1. 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.

相关文章