将MySQL查询简化为一个查询以加快速度 [英] Reduce MySQL queries to one query to speed up

问题描述

在general_log文件中，我有以下查询:

In general_log file i have queries:

160806  9:53:26      11 Connect     dbname@localhost on 
     11 Query       SET NAMES utf8
     11 Query       SET character_set_client="utf8"
     11 Query       SET character_set_connection="utf8"
     11 Query       SET character_set_database="utf8"
     11 Query       SET character_set_results="utf8"
     11 Query       SET character_set_server="utf8"
     11 Query       SET character_set_system="utf8"
     11 Init DB     dbname

是否可以进行1个查询而不是7个查询?会大大加快速度吗?

Is that possible to make 1 query instead of 7 queries? Will it speed up significantly?

推荐答案

[SET NAMES]语句将三个会话系统变量character_set_client，character_set_connection和character_set_results设置为给定的字符集.将character_set_connection设置为charset_name还会将collat​​ion_connection设置为charset_name的默认排序规则.

[The SET NAMES] statement sets the three session system variables character_set_client, character_set_connection, and character_set_results to the given character set. Setting character_set_connection to charset_name also sets collation_connection to the default collation for charset_name.

因此，您可以跳过SET NAMES之后的几条语句.剩下:

So you can skip several of the statements after SET NAMES. That leaves:

SET NAMES utf8;
SET character_set_database="utf8";
SET character_set_server="utf8";
SET character_set_system="utf8";

您应该阅读手册这些变量上:

You should read the manual on these variables:

字符集数据库

默认数据库使用的字符集.每当默认数据库更改时，服务器都会设置此变量.如果没有默认数据库，则该变量的值与character_set_server相同.

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

您无需进行设置.

字符集服务器

服务器的默认字符集.

The server's default character set.

您无需在会话上下文中进行设置.在您的my.cnf文件中全局设置它，并将其保留.唯一的目的是在使用CREATE DATABASE时建立一个默认值，并且没有为命名数据库显式定义一个字符集.

There's no need for you to set this in your session context. Set it globally in your my.cnf file and leave it alone. The only thing it is for is to establish a default when you use CREATE DATABASE and don't explicitly define a character set for the named database.

字符集系统

服务器用于存储标识符的字符集.该值始终为utf8.

The character set used by the server for storing identifiers. The value is always utf8.

真的没有 供您更改.

仅留下:

SET NAMES utf8;

这篇关于将MySQL查询简化为一个查询以加快速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！