MySQL“设置名称"慢查询日志顶部附近 [英] MySQL "SET NAMES" near the top of the slow query log

问题描述

在最近启动的网站上，我注意到，在该网站上实际的大量查询中，最昂贵的请求实际上是SET NAMES(在数百万个查询中)，它平均耗时超过2.3秒，而各种-join联合查询的时间远远少于2秒.最后，这将它放在慢查询日志的顶部，我只是想不出一种优化它的方法.

On a recently launched site, I noticed that, well over the actual heavy queries on the site, the most costly request, out of several million queries, is actually SET NAMES, which clocks over 2.3 seconds on average, while various multi-join union queries are well below 2 seconds. In the end, this places it near the top of the slow query log, and I just can't think of a way to optimize it.

这似乎不是一个普遍的问题，因为我没有找到有关谷歌搜索的信息.

This does not seem to be a common problem, as I found no reference to it googling.

由于SET NAMES与排序规则有关，因此以下是相关信息:

Since SET NAMES is about collation, here is the related information:

• MySQL字符集为UTF-8 Unicode(utf8)
• 数据库连接排序规则为utf8_unicode_ci
• 数据库及其所有表都使用utf8_general_ci

FWIW，它正在运行MySQL 5.0.45-log.数据库服务器是本地主机.

FWIW, this is running MySQL 5.0.45-log. The DB server is localhost.

推荐答案

如果所有查询都很慢，则SET NAMES在服务器上的优先级可能会很低，因此请等到负载减少为止.如果您还有很多其他查询需要花费很长时间才能执行，那么您可能想先尝试对它们进行优化.

If all queries are slow, SET NAMES can have a very low priority on the server, and therefore wait until the load dies down. If you have a lot of other queries that take a long time executing, you might want to try to optimize those first.

该问题"的另一种解决方案是将其添加到服务器上的my.cnf中:

Another solution to this "problem", could be adding this to your my.cnf on the server:

[mysqld]
init-connect = 'SET NAMES utf8'

这可确保在客户端连接时将字符集设置为UTF-8，因此客户端不必等待此查询"的结果.您可能要禁用软件中的SET NAMES查询.

This makes sure the character set is set to UTF-8 when a client connects, so the client does not have to wait for the result of this "query". You probably want to disable the SET NAMES query in your software.

主要来说，除非您真的有很多需要这么长时间的设置名称，否则我不会太担心.

Mainly, I wouldn't worry too much unless you really have A LOT of SET NAMES that takes this long.

这篇关于MySQL“设置名称"慢查询日志顶部附近的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！