MySQL字符集和优化

MySQL字符集

字符集介绍

字符(Character)是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。字符集(Character set)是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同。

常见字符集名称：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等。计算机要准确的处理各种字符集文字，就需要进行字符编码，以便计算机能够识别和存储各种文字。

常见字符集介绍

ASCII字符集

ASCII字符集：American Standard Code for Information Interchange，美国信息互换标准编码。7位（bits）表示一个字符，共128字符，字符值从0到127。包括控制字符：回车键、退格、换行键等。可显示字符：英文大小写字符、阿拉伯数字和西文符号。

ASCII扩展字符集

ASCII扩展字符集：它是从ASCII字符集扩充出来的，扩充后的符号增加了表格符号、计算符号、希腊字母和特殊的拉丁符号。ASCII扩展字符集使用8位（bits）表示一个字符，共256字符。

GB2312

GB2312由原中国国家标准总局发布，收录简化汉字及一般符号、序号、数字、拉丁字母、希腊字母、俄文字母、汉语拼音符号、汉语注音字母，共 7445 个图形字符。其中包括6763个汉字，用双字节表示。

Big5

Big5又称大五码或五大码，1984年由台湾财团法人信息工业策进会和五家软件公司宏碁 (Acer)、神通 (MiTAC)、佳佳、零壹 (Zero One)、大众 (FIC)创立，故称大五码。字符集共收录13,053个中文字，该字符集在中国台湾使用。Big5码使用了双字节储存方法。

GB 18030

GB 18030字符集标准解决汉字、日文、朝鲜语和中国少数民族文字组成的大字符集计算机编码问题。是我国政府于2000年3月17日发布的新的汉字编码国家标准，收录了27484个汉字，覆盖中文、日文、朝鲜语和中国少数民族文字。满足中国大陆、香港、台湾、日本和韩国等东亚地区信息交换多文种、大字量、多用途、统一编码格式的要求。GB 18030标准采用单字节、双字节和四字节三种方式对字符编码。单字节部分对应于ASCII码的相应部分，双字节部分收录内容主要包括GB13000.1全部CJK汉字20902个、有关标点符号、表意文字描述符13个、增补的汉字和部首/构件80个、双字节编码的欧元符号等。四字节部分收录了上述双字节字符之外的，包括CJK统一汉字扩充在内的GB 13000.1中的全部字符。

Unicode

Unicode字符集编码是Universal Multiple-Octet Coded Character Set 通用多八位编码字符集的简称，是由一个名为 Unicode 学术学会(Unicode Consortium)的机构制订的字符编码系统，支持现今世界各种不同语言的书面文本的交换、处理及显示。

UTF-8

UTF-8是Unicode的其中一个使用方式。 UTF是 Unicode Tranformation Format，即把Unicode转作某种格式的意思。UTF-8使用可变长度字节来储存 Unicode字符，又称万国码。例如ASCII字母继续使用1字节储存，重音文字、希腊字母或西里尔字母等使用2字节来储存，而常用的汉字就要使用3字节。辅助平面字符则使用4字节。

设置mysql字符集方式

设置数据库字符集

创建数据库时，可以通过 CHARACTER SET 和 COLLATE 选项来指定字符集和排序规则。例如

CREATE DATABASE my_database
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

utf8mb4 是一个支持多语言字符的字符集，推荐使用它代替 utf8。

utf8mb4_unicode_ci 是一种区分大小写的排序规则。

如果数据库已经存在并且需要修改字符集，可以使用以下 SQL：

ALTER DATABASE my_database
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

设置表字符集

创建表时，可以为每个表指定字符集。例如：

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果表已经存在，可以通过 ALTER TABLE 来修改字符集：

ALTER TABLE my_table
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

设置列字符集

在表中的列也可以指定字符集。例如：

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

如果列已经存在，修改列的字符集可以使用以下 SQL：

ALTER TABLE my_table
    MODIFY name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

设置连接字符集

MySQL 客户端和服务器之间的通信是基于字符的，可能会使用不同的字符编码格式。如果字符集不一致，可能会出现乱码或者数据存储不正确的情况。例如，在一个 UTF-8 编码的数据库中，如果客户端使用的是不同的字符集（如 Latin1），向数据库发送包含中文字符的数据时，中文字符可能会被错误地存储为乱码。

通过设置连接字符集，确保客户端和服务器之间在数据交换时使用相同的编码格式，从而避免出现编码问题。

在 MySQL 会话级别，连接字符集控制了客户端与服务器之间的数据交换编码。要确保客户端和数据库之间使用相同的字符集，可以在连接时设置字符集：

SET NAMES 'utf8mb4';

或者分别设置字符集和排序规则：

SET character_set_client = 'utf8mb4';
SET character_set_connection = 'utf8mb4';
SET character_set_results = 'utf8mb4';

修改 MySQL 配置文件

为了确保 MySQL 启动时默认使用某个字符集，可以修改 MySQL 的配置文件（通常是 my.cnf 或 my.ini），在 [mysqld] 部分设置默认字符集：

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

此外，如果你希望客户端和连接也默认使用该字符集，可以在配置文件中设置：

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

修改配置文件后，重启 MySQL 服务以使更改生效。

查看当前字符集设置

你可以使用以下 SQL 命令查看当前数据库、表、列、连接的字符集：

查看当前数据库的字符集：

SHOW CREATE DATABASE my_database;

查看当前表的字符集：

SHOW TABLE STATUS WHERE name = 'my_table';

查看当前连接的字符集：

SHOW VARIABLES LIKE 'character_set%';

实战，迁移数据

背景

公司业务数据book,由于之前建表没注意字符集的问题，导致之前写入的数据出现乱码。现在要将之前的数据和现在数据的字符集一致，不出现乱码情况，将字符集为latin1已有记录的数据转成utf8，并且已经存在的记录不乱码。

mysql> select * from books;
+-----+----------------------------------------------------------------------------------------------------------------+---------+-------------------------------------------------------------------------+-------+------------+------------------------+-------------+
| bId | bName                                                                                                          | bTypeId | publishing                                                              | price | pubDate    | author                 | ISBN        |
+-----+----------------------------------------------------------------------------------------------------------------+---------+-------------------------------------------------------------------------+-------+------------+------------------------+-------------+
|   1 | ç½‘ç«™åˆ¶ä½œç›´é€šè½¦                                                                                          | 2       | ç”µè„‘çˆ±å¥½è€…æ‚å¿—ç¤¾                                                |    34 | 2004-10-01 | è‹—å£®                 | 7505380796  |
|   2 | é»‘å®¢ä¸Žç½‘ç»œå®‰å…¨                                                                                          | 6       | èˆªç©ºå·¥ä¸šå‡ºç‰ˆç¤¾                                                   |    41 | 2002-07-01 | ç™½ç«‹

步骤思路

1：建库及建表的语句导出，修改为utf8
2：导出之前所有的数据
3：修改mysql服务端和客户端编码为utf8
4：删除原有的库表及数据
5：导入新的建库及建表语句
6：导入之前的数据

#导出表结构
mysqldump -uroot -p123456 --default-character-set=latin1 -d book > create_books_table.sql
参数详解：
--default-character-set 保持原表的字符编码集
-d：这个选项表示 "no data"（不导出数据），即仅导出数据库表的结构（CREATE TABLE 语句）。
#导出表数据
mysqldump -uroot -p123456 --quick --no-create-info --extended-insert --default-character-set=latin1 book > book_data.sql
参数详解：
--quick：强制MySQLdump以一行一行检索数据，输入到内存中。锁的范围小，影响范围小。
--no-create-info ：不要创建表的语句。（也就是说只要表中的数据）
--extended-insert ：减小IO。加快导出速度。

#将导出的两个表进入批量修改%s/latin1/utf8/g

#先导入建库建表语句、在导入表中的数据
mysql -uroot -p123456 book< create_books_table.sql 
mysql -uroot -p123456 book< book_data.sql

mysql调优

操作系统的优化

修改TCP端口限制

vim /etc/sysctl.conf
---------
net.ipv4.ip_local_port_range = 1024 65000

sysctl -p #加载调优参数、看到打印在终端即生效

例子说明
操作系统选择本地端口： 假设你的机器与远程数据库服务器的连接请求如下：

你的机器（客户端）IP 地址：192.168.1.100
数据库服务器（远程服务器）IP 地址：192.168.1.200
数据库服务端口：3306
当你使用客户端程序（如 MySQL 客户端）连接远程数据库时，操作系统会为这个连接分配一个本地端口，作为源端口来与远程数据库进行通信。这个端口号是从 net.ipv4.ip_local_port_range 设置的范围内选择的。

本地端口范围的最小值必须大于或等于1024;而端口范围的最大值则应小于或等于65535.修改完后保存此文件。

设置Linux的最大TCP并发连接数

#加载内核模块
modprobe nf_conntrack

#查看当前TCP连接数
cat /proc/sys/net/netfilter/nf_conntrack_events

#查看最大TCP可连接数
cat /proc/sys/net/netfilter/nf_conntrack_expect_max

#临时修改TCP最大连接数
echo '100000' > nf_conntrack_expect_max

#永久修改TCP最大连接数
vim /etc/sysctl.conf
----------
net.netfilter.nf_conntrack_expect_max = 512

设置TCP time_wait数量

vim /etc/sysctl.conf
net.ipv4.tcp_syncookies = 1 
net.ipv4.tcp_tw_reuse = 1  #tcp重用、将tcp连接重复使用。节省建立tcp连接此处和杀死tcp连接。节省内存占用
net.ipv4.tcp_tw_recycle = 1 #开启快速回收
net.ipv4.tcp_fin_timeout = 30 #连接时多久未操作断开连接

设置系统打开文件的最大限制（用户级别）

查看系统打开最大文件数

[root@server1 netfilter]# ulimit -n
1024

vim /etc/security/limits.conf
mysql   soft    nofile 102400   
mysql   hard    nofile 102400
mysql   soft    nproc 102400  
mysql   hard    nproc 102400

注释：soft软限制   hard硬限制   软限制不可以大于硬限制
写入加载配置文件
vim /etc/pam.d/login
session    required     /usr/lib64/security/pam_limits.so

1. 软限制（soft limit）
   软限制是当前进程可以使用的资源限制。当资源的使用超过软限制时，系统会发出警告或提醒，但不会直接终止进程。软限制可以在进程运行时临时修改，允许用户根据需要调整资源的使用。

调整：软限制可以在进程运行时由用户或管理员临时修改。
用途：软限制通常是为了预警和控制系统资源的合理使用。

2. 硬限制（hard limit）
   硬限制是操作系统强制执行的最大资源限制，用户和进程不能超过这个限制。硬限制通常由系统管理员设定，用户不能通过普通的操作来修改它。硬限制用于防止用户或进程使用过多的系统资源，以避免影响整个系统的稳定性。

调整：硬限制只能由 root 用户或管理员设置，普通用户无法修改。
用途：硬限制提供了系统的安全边界，防止进程滥用资源。
nofile：表示每个进程可以打开的最大文件描述符数（即每个进程可以同时打开的文件数）。
nproc：表示每个用户可以创建的最大进程数。

设置系统打开文件的最大限制（系统级别）

查看默认的系统打开文件最大格式
sysctl -a | grep fs.file-max

修改系统打开文件的最大限制
fs.file-max = 1000000

MySQL的优化

对查询开启缓存

query_cache_size=256M   #设置缓存大小
query_cache_type=1     #开启缓存功能

SHOW STATUS LIKE 'Qcache%'

SHOW STATUS LIKE 'Qcache%' 主要查询的是 查询缓存（Query Cache）的运行时统计数据。它提供的是与查询缓存的实际使用情况相关的统计信息。

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 44      |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

Qcache_hits：查询缓存的命中次数。表示从缓存中读取到结果的次数。
Qcache_inserts：查询缓存中插入的查询次数。表示有多少查询被缓存。
Qcache_lowmem_prunes：由于内存不足而被删除的查询次数。
Qcache_free_blocks：查询缓存中空闲的内存块数。
Qcache_total_blocks：查询缓存中总的内存块数。

 清理缓存：flush query cache;

强制限制mysql资源

max_connections=500 
wait_timeout=10  
max_connect_errors=10

- max_connections：MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这是建立在服务器能支撑的情况下，因为如果连接数越多， MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

    可执行mysql> show variables like 'max_connections';查看mysql的最大连接数设置。
    注：数值过小会经常出现ERROR 1040: Too many connections错误
- wait_timeout：指的是MySQL在关闭一个非交互的连接之前所要等待的秒数（空闲时间）。

    可执行mysql> show variables like 'wait_timeout';查看wait_timeout的值。
    
- max_connect_errors：是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试连接失败的客户端，以防止暴力破解密码的情况, 当超过指定次数，MYSQL服务器将禁止该主机的连接请求。max_connect_errors的值与性能并无太大关系。

    可执行mysql> show variables like 'max_connect_errors';查看该参数的设置值。
    如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到执行：mysql> FLUSH HOSTS;

表高速缓存

表缓存的主要作用是提高 MySQL 的性能，减少频繁打开和关闭表所带来的开销。每当 MySQL 执行查询时，它需要打开表并读取表的元数据（如表的结构、索引等）。如果表缓存未启用或配置不当，每次访问表时都需要重新打开它，这会导致额外的磁盘 I/O 和时间开销。

通过将已打开的表存储在内存中，MySQL 可以避免重复打开相同的表，从而加速查询处理

你可以通过以下命令查看当前配置的表缓存大小：

SHOW VARIABLES LIKE 'table_open_cache';

你也可以通过修改 MySQL 配置文件来调整这个值：

[mysqld]
table_open_cache = 4096

修改后，需要重启 MySQL 服务才能生效。

启用慢查询日志

通过配置文件启用慢查询日志

编辑 MySQL 配置文件（例如 /etc/my.cnf 或 /etc/mysql/my.cnf）中的 [mysqld] 部分，添加或修改以下配置：

[mysqld]
slow_query_log = 1                  # 启用慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log  # 设置慢查询日志文件的路径
long_query_time = 2                 # 设置查询超过 2 秒的 SQL 语句被记录为慢查询
log_queries_not_using_indexes = 1   # 记录没有使用索引的查询

日志的内容包括执行的 SQL 语句、查询执行的时间、锁等待时间、扫描的行数、使用的索引等信息。例如：

# Time: 2024-12-21T10:47:13.872870Z
# User@Host: root[root] @ localhost []  Id: 12345
# Query_time: 3.125321  Lock_time: 0.000212 Rows_sent: 10  Rows_examined: 1000
SET timestamp=1612345678;
SELECT * FROM your_table WHERE some_column = 'some_value';

Query_time：查询执行的时间，单位是秒。
Lock_time：查询锁的等待时间。
Rows_sent：返回的行数。
Rows_examined：查询扫描的行数。

posted @ 2024-12-23 23:23 pro111 阅读(36) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· MySQL索引和外键

· MySQL主从架构

· MySQL优化

· 一: Mysql字符集问题

· MySQL系列之优化——1.优化哲学、2. 优化工具的使用、3. 优化思路分解、4. MySQL参数优化测试、5.1 参数优化、6. 参数优化结果、7. 锁的监控及处理、8. 主从优化

阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了

公告

昵称： pro111
园龄： 9个月
粉丝： 0
关注： 2

+加关注

2025年3月

日

一

二

三

四

五

六

procloud

MySQL字符集和优化

MySQL字符集和优化

MySQL字符集

字符集介绍

常见字符集介绍

ASCII字符集

ASCII扩展字符集

GB2312

Big5

GB 18030

Unicode

UTF-8

设置mysql字符集方式

设置数据库字符集

设置表字符集

设置列字符集

设置连接字符集

修改 MySQL 配置文件

查看当前字符集设置

实战，迁移数据

背景

步骤思路

mysql调优

操作系统的优化

修改TCP端口限制

设置Linux的最大TCP并发连接数

设置TCP time_wait数量

设置系统打开文件的最大限制（用户级别）

设置系统打开文件的最大限制（系统级别）

MySQL的优化

强制限制mysql资源

表高速缓存

启用慢查询日志

公告

搜索

常用链接

随笔分类

随笔档案

阅读排行榜