java.sql.SQLException: 内存不足以进行排序，请考虑增加服务器排序缓冲区大小

huangapple
go评论58阅读模式
英文:

java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size

问题

I am trying to run this query using java with JDBC:

SELECT * FROM jobs WHERE 0=0 # find all occurrences of the search string + any component of the search string
        OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%')
        OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%')
        OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%')
        OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%')
        AND date_posted >= now() - INTERVAL 30 DAY # ensure results are from the last month
ORDER BY
        # order first by results that begin with the search string
        job_title COLLATE utf8mb4_0900_ai_ci LIKE 'software engineer%' DESC,
        # order second by results that contain the search string
        IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'software engineer'), 0), 99999) DESC,
        # order third by results that contain the first component of the search string
        IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'software'), 0), 99999) DESC,
        # order forth by results that contain the second component of the search string
        IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'engineer'), 0), 99999) DESC,
        # order alphabetically
        job_title,
        IFNULL(NULLIF(INSTR(job_description COLLATE utf8mb4_0900_ai_ci, 'software engineer'), 0), 99999);

When I run this query in the MySQL terminal it runs fine. However, when I run it through JDBC, where `searchTokens = ['software', 'engineer']`

    var searchTokens = searchValue.split(" ");

    String query = "SELECT * FROM jobs WHERE 0=0 \n";

    for (String token : searchTokens) {
        query += String.format("    OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%%', '%s', '%%')\n", token);
        query += String.format("    OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%%', '%s', '%%')\n", token);
    }

    query += "    AND date_posted >= now() - INTERVAL 30 DAY\n ORDER BY\n";
    query += String.format("    job_title COLLATE utf8mb4_0900_ai_ci LIKE '%s%%' DESC,\n", searchValue);
    query +=
        String.format("    IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, '%s'), 0), 99999),\n", searchValue);

    for (String token : searchTokens) {
        query +=
            String.format("    IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, '%s'), 0), 99999),\n", token);
    }

    query += String.format("    job_title,\n" +
            "    IFNULL(NULLIF(INSTR(job_description COLLATE utf8mb4_0900_ai_ci, '%s'), 0), 99999);", searchValue);

    List<Map<String, Object>> result = jdbcTemplate.queryForList(query);

I get this error:

    nested exception is org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SELECT * FROM jobs WHERE 0=0 
        OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%')
        OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%')
        OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%')
        OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%')
        AND date_posted >= now() - INTERVAL 30 DAY
     ORDER BY
        job_title COLLATE utf8mb4_0900_ai_ci LIKE 'software engineer%' DESC,
        IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'software engineer'), 0), 99999),
        IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'software'), 0), 99999),
        IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, 'engineer'), 0), 99999),
        job_title,
        IFNULL(NULLIF(INSTR(job_description COLLATE utf8mb4_0900_ai_ci, 'software engineer'), 0), 99999);]; SQL state [HY001]; error code [1038]; Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size

    java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.17.jar:8.0.17]
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.17.jar:8.0.17]
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.17.jar:8.0.17]
        at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) ~[mysql-connector-java-8.0.17.jar:8.0.17]
        at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:439) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:452) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:462) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:490) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
        at com.jobtech.controller.JobroleSearchController.submit(JobroleSearchController.java:49) ~[classes/:na]
        ...
英文:

I am trying to run this query using java with JDBC:

SELECT * FROM jobs WHERE 0=0 # find all occurrences of the search string + any component of the search string
OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;software&#39;, &#39;%&#39;)
OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;software&#39;, &#39;%&#39;)
OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;engineer&#39;, &#39;%&#39;)
OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;engineer&#39;, &#39;%&#39;)
AND date_posted &gt;= now() - INTERVAL 30 DAY # ensure results are from the last month
ORDER BY
# order first by results that begin with the search string
job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;software engineer&#39;, &#39;%&#39;) DESC,
# order second by results that contain the search string
IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, &#39;software engineer&#39;), 0), 99999) DESC,
# order third by results that contain the first component of the search string
IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, &#39;software&#39;), 0), 99999) DESC,
# order forth by results that contain the second component of the search string
IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, &#39;engineer&#39;), 0), 99999) DESC,
# order alphabetically
job_title,
IFNULL(NULLIF(INSTR(job_description COLLATE utf8mb4_0900_ai_ci, &#39;software engineer&#39;), 0), 99999);

When I run this query in the MySQL terminal it runs fine. However, when I run it through JDBC, where searchTokens = [&#39;software&#39;, &#39;engineer&#39;]

var searchTokens = searchValue.split(&quot; &quot;);
String query = &quot;SELECT * FROM jobs WHERE 0=0 \n&quot;;
for (String token : searchTokens) {
query += String.format(&quot;    OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%%&#39;, &#39;%s&#39;, &#39;%%&#39;)\n&quot;, token);
query += String.format(&quot;    OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%%&#39;, &#39;%s&#39;, &#39;%%&#39;)\n&quot;, token);
}
query += &quot;    AND date_posted &gt;= now() - INTERVAL 30 DAY\n ORDER BY\n&quot;;
query += String.format(&quot;    job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%s&#39;, &#39;%%&#39;) DESC,\n&quot;, searchValue);
query +=
String.format(&quot;    IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, &#39;%s&#39;), 0), 99999),\n&quot;, searchValue);
for (String token : searchTokens) {
query +=
String.format(&quot;    IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, &#39;%s&#39;), 0), 99999),\n&quot;, token);
}
query += String.format(&quot;    job_title,\n&quot; +
&quot;    IFNULL(NULLIF(INSTR(job_description COLLATE utf8mb4_0900_ai_ci, &#39;%s&#39;), 0), 99999);&quot;, searchValue);
List&lt;Map&lt;String, Object&gt;&gt; result = jdbcTemplate.queryForList(query);

I get this error:

nested exception is org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [SELECT * FROM jobs WHERE 0=0 
OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;software&#39;, &#39;%&#39;)
OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;software&#39;, &#39;%&#39;)
OR job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;engineer&#39;, &#39;%&#39;)
OR job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;engineer&#39;, &#39;%&#39;)
AND date_posted &gt;= now() - INTERVAL 30 DAY
ORDER BY
job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;software engineer&#39;, &#39;%&#39;) DESC,
IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, &#39;software engineer&#39;), 0), 99999),
IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, &#39;software&#39;), 0), 99999),
IFNULL(NULLIF(INSTR(job_title COLLATE utf8mb4_0900_ai_ci, &#39;engineer&#39;), 0), 99999),
job_title,
IFNULL(NULLIF(INSTR(job_description COLLATE utf8mb4_0900_ai_ci, &#39;software engineer&#39;), 0), 99999);]; SQL state [HY001]; error code [1038]; Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size] with root cause
java.sql.SQLException: Out of sort memory, consider increasing server sort buffer size
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.17.jar:8.0.17]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.17.jar:8.0.17]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.17.jar:8.0.17]
at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200) ~[mysql-connector-java-8.0.17.jar:8.0.17]
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:439) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:376) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:452) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:462) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:490) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at com.jobtech.controller.JobroleSearchController.submit(JobroleSearchController.java:49) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:567) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:878) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:792) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:652) ~[tomcat-embed-core-9.0.37.jar:4.0.FR]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:733) ~[tomcat-embed-core-9.0.37.jar:4.0.FR]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.37.jar:9.0.37]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.8.RELEASE.jar:5.2.8.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1589) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.37.jar:9.0.37]
at java.base/java.lang.Thread.run(Thread.java:830) ~[na:na]

I have tried setting sort_buffer_size to 256K, and this error is still persisting. However, I have read that it is bad to set the sort buffer size too large. What is causing my error in this SQL statement when it runs perfectly fine in the MySQL terminal?

答案1

得分: 2

你可以通过修复where子句来解决内存不足的问题。仅对最后一个条件应用日期过滤，因为括号使用错误。

因此：

WHERE (job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%') OR
       job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'software', '%') OR
       job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%') OR
       job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT('%', 'engineer', '%')
      ) AND
      date_posted >= now() - INTERVAL 30 DAY

注意：为了提高性能，您可能希望在此表上考虑全文索引。此外，查询中使用collate指示了数据建模问题。如果您想进行不区分大小写的搜索... 那在MySQL中这通常是默认设置。

英文:

You can probably fix the out-of-memory problem by fixing the where clause. The filtering on date only applies to the last condition, because the parentheses are wrong.

So:

WHERE (job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;software&#39;, &#39;%&#39;) OR
job_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;software&#39;, &#39;%&#39;) OR
job_title COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;engineer&#39;, &#39;%&#39;) OR
ob_description COLLATE utf8mb4_0900_ai_ci LIKE CONCAT(&#39;%&#39;, &#39;engineer&#39;, &#39;%&#39;)
) AND
date_posted &gt;= now() - INTERVAL 30 DAY

Note: For performance you might want to consider a full-text index on this table. Also, using collate in the query suggests a data modeling problem. If you want a case-insensitive search . . . well that is the normal default in MySQL anyway.

答案2

得分: 1

因为数据库的内部缓冲区对于查询来说太小了。

您可以在以下配置文件/etc/mysql/my.cnf中增加缓冲区大小：

将sort_buffer_size调整为512K。

英文:

It's because the internal buffer of the database is to small for the query.

You can increase the buffer in the following config file /etc/mysql/my.cnf:

sort_buffer_size to 512K

答案3

得分: 0

由于数据库的内部缓冲区对于查询来说太小了，所以会出现这个问题。

在MySQL命令中运行以下查询以增加内存：

SET GLOBAL sort_buffer_size = 512000000; // 服务器重启后会重置

要进行永久设置，编辑以下文件并添加：

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

sort_buffer_size = 512000000;

然后重启MySQL服务：

sudo service mysql restart

请注意，1073741824等于字节，将导致分配1 GB 内存。

此外，还有可能是其他原因导致了问题，您可以检查CPU、内存和慢查询日志：

SHOW ENGINE INNODB STATUS

然后您可以根据当前缓冲池大小分析输出：

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 412090368
Dictionary memory allocated 380237
Buffer pool size   24576
Free buffers       23618
Database pages     954
Old database pages 372
Modified db pages  0
Pending reads      0

有关更多详细信息，请参阅MySQL文档：MySQL 优化

英文:

It's because the internal buffer of the database is too small for the query.

Run the below query in MySQL command to increase the memory

SET GLOBAL sort_buffer_size = 512000000; // It&#39;ll reset after server restart

To set permanent, edit the below file and add:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
sort_buffer_size = 512000000;
sudo service mysql restart

Note that 1073741824 is equal to bytes and will result in 1 GB memory allocation.

Also, there is a possibility that something else causing the issue, you can check CPU, memory and slow query logs

SHOW ENGINE INNODB STATUS

You can then analyse the output base on current buffer pool size.

BUFFER POOL AND MEMORY
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 412090368
Dictionary memory allocated 380237
Buffer pool size   24576
Free buffers       23618
Database pages     954
Old database pages 372
Modified db pages  0
Pending reads      0

For more details, refer to MySQL docs: MySQL Optimization

huangapple
  • 本文由 发表于 2020年9月7日 19:09:11
  • 转载请务必保留本文链接：https://go.coder-hub.com/63776421.html
  • java
  • jdbc
  • mysql
  • spring
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定