MK
摩柯社区 - 一个极简的技术知识社区
AI 面试

MySQL字符集对查询性能的影响

2024-03-013.3k 阅读

MySQL字符集基础概述

字符集概念

在计算机中，字符集是一套规定了字符与二进制数值之间对应关系的规则集合。不同的字符集包含不同的字符范围和编码方式。例如，ASCII字符集主要包含英文字母、数字和一些常见符号，采用7位二进制编码，最多能表示128个字符。而UTF - 8字符集则是一种可变长度的编码方式，它可以表示世界上几乎所有的字符，其编码长度从1字节到4字节不等。

在MySQL中，字符集的概念更为复杂，它不仅涉及到数据存储时的编码方式，还影响到数据的比较、排序等操作。MySQL支持多种字符集，常见的如UTF - 8、GBK、Latin1等。每种字符集都有其特点和适用场景。例如，UTF - 8通用性强，能处理多种语言，但对于纯英文数据存储相对Latin1会占用更多空间；GBK则主要适用于简体中文环境，对中文的存储和处理有一定优势。

MySQL字符集相关设置

  1. 服务器级字符集：这是MySQL服务器启动时默认使用的字符集。可以在MySQL配置文件（如my.cnf或my.ini）中通过character - set - server参数来设置。例如，将其设置为UTF - 8：
[mysqld]
character - set - server = utf8mb4

重启MySQL服务后，新创建的数据库和表如果没有显式指定字符集，将默认使用此服务器级字符集。

  1. 数据库级字符集：创建数据库时可以指定字符集。例如，创建一个使用GBK字符集的数据库：
CREATE DATABASE my_db_name CHARACTER SET gbk;

数据库级字符集优先于服务器级字符集，当在此数据库中创建表时，如果未显式指定表的字符集，将使用数据库级字符集。

  1. 表级字符集：创建表时同样可以指定字符集。比如：
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50)
) CHARACTER SET utf8mb4;

表级字符集优先于数据库级字符集，对该表内的数据存储和操作产生直接影响。

  1. 列级字符集：在某些情况下，还可以为表中的特定列指定字符集。例如：
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50) CHARACTER SET latin1
) CHARACTER SET utf8mb4;

这里表的默认字符集是UTF - 8mb4，但name列使用了Latin1字符集。列级字符集优先级最高，如果对该列进行操作，将遵循此字符集规则。

字符集校对规则

校对规则（Collation）决定了字符如何比较和排序。不同的字符集通常有多种校对规则与之关联。例如，UTF - 8字符集就有utf8_general_ci、utf8_unicode_ci等校对规则。其中，_ci表示不区分大小写（case - insensitive），_cs表示区分大小写（case - sensitive）。

  1. 服务器级校对规则：同样在MySQL配置文件中设置，通过collation - server参数，如：
[mysqld]
collation - server = utf8mb4_general_ci
  1. 数据库级校对规则：创建数据库时指定，例如：
CREATE DATABASE my_db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  1. 表级校对规则：创建表时设置，如：
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

这里utf8mb4_bin校对规则将按字节进行比较，区分大小写。

  1. 列级校对规则：为特定列指定校对规则，例如：
CREATE TABLE my_table (
    id INT,
    name VARCHAR(50) COLLATE latin1_general_cs
) CHARACTER SET utf8mb4;

此name列使用了latin1_general_cs校对规则，对Latin1字符集的字符进行区分大小写的比较。

字符集对查询性能影响的理论分析

存储层面影响

  1. 空间占用差异：不同字符集对同一字符的编码长度不同。以一个简单的例子说明，对于英文字母a，在Latin1字符集中，它占用1个字节，编码为0x61。而在UTF - 8字符集中，同样是a，也占用1个字节（因为ASCII部分在UTF - 8中编码相同）。但对于中文字符“中”，在GBK字符集中占用2个字节，编码为0xD6D0；在UTF - 8中则占用3个字节，编码为0xE4B8AD。

假设我们有一个包含大量中文字符的表，如果使用GBK字符集，数据存储所需的空间相对UTF - 8会少一些（在仅考虑中文字符存储的情况下）。然而，如果表中同时包含多种语言字符，UTF - 8的通用性使其更适合，但可能会占用更多空间。

在查询时，数据库需要从磁盘读取数据到内存。如果数据量较大，字符集导致的空间占用差异会影响磁盘I/O性能。较小的空间占用意味着在相同的磁盘I/O带宽下，可以读取更多的数据。例如，一个表在GBK字符集下存储大小为100MB，而在UTF - 8字符集下为150MB。在进行全表扫描查询时，GBK字符集可能会因为磁盘I/O量相对较小而有一定性能优势。

  1. 存储结构与索引：MySQL的存储引擎（如InnoDB、MyISAM等）在处理不同字符集数据时，其内部存储结构可能会有细微差异。对于索引，字符集也会产生影响。例如，InnoDB存储引擎的B - Tree索引结构中，键值的存储和比较依赖于字符集和校对规则。

如果使用区分大小写的校对规则（如utf8mb4_bin），索引在比较键值时会严格按照字节顺序，这可能会导致相同字符但大小写不同的记录在索引中被视为不同的键值。而不区分大小写的校对规则（如utf8mb4_general_ci）则会将大小写相同的字符视为相同的键值。这种差异会影响索引的构建和查询时对索引的利用效率。

例如，在一个包含用户名字段的表上创建索引，如果使用utf8mb4_general_ci校对规则，查询SELECT * FROM users WHERE name = 'John'和SELECT * FROM users WHERE name = 'john'可能会使用相同的索引路径，因为在这种校对规则下，这两个查询是等效的。但如果使用utf8mb4_bin校对规则，这两个查询会被视为不同的操作，可能会导致索引利用效率降低，特别是在数据量较大时。

比较和排序操作影响

  1. 字符比较算法：不同的校对规则决定了字符比较的具体算法。简单的校对规则如utf8_general_ci采用相对简单的比较方式，对于常见的字符比较效率较高。而复杂的校对规则，如utf8_unicode_ci，它遵循Unicode标准进行字符比较，考虑了更多的语言特性和字符等价关系，比较算法相对复杂。

在查询中涉及到比较操作（如WHERE子句中的比较）时，复杂的校对规则会增加CPU的计算开销。例如，在一个多国语言的论坛帖子表中，使用utf8_unicode_ci校对规则来比较帖子标题中的字符，当进行大量的LIKE查询时，由于其复杂的字符等价判断（如一些特殊字符的等价处理），会比使用utf8_general_ci校对规则消耗更多的CPU时间。

  1. 排序性能：排序操作在数据库查询中也很常见，如ORDER BY子句。字符集的校对规则同样影响排序性能。当使用区分大小写的校对规则进行排序时，排序结果会严格按照字符的大小写顺序。而不区分大小写的校对规则会将大小写相同的字符视为同一类进行排序。

假设有一个存储产品名称的表，使用utf8mb4_bin校对规则进行排序时，产品名称ProductA会排在producta之前。而使用utf8mb4_general_ci校对规则时，它们会被视为相同优先级，具体顺序取决于其他因素（如在表中的物理存储顺序等）。在数据量较大的情况下，区分大小写的排序可能会因为需要更细致的比较而消耗更多资源，影响查询性能。

字符集对查询性能影响的实验分析

实验环境搭建

  1. 硬件环境：使用一台配置为Intel Core i7 - 10700K CPU @ 3.80GHz，16GB内存，512GB SSD硬盘的计算机作为实验服务器。操作系统为Ubuntu 20.04 LTS。
  2. 软件环境：安装MySQL 8.0版本。通过修改MySQL配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，设置服务器级字符集和校对规则：
[mysqld]
character - set - server = utf8mb4
collation - server = utf8mb4_general_ci

重启MySQL服务使其生效。

实验表创建与数据插入

  1. 创建不同字符集的表：
    • 创建一个使用UTF - 8mb4字符集和utf8mb4_general_ci校对规则的表utf8_table：
CREATE TABLE utf8_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(255)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

- 创建一个使用GBK字符集和`gbk_chinese_ci`校对规则的表`gbk_table`：

CREATE TABLE gbk_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(255)
) CHARACTER SET gbk COLLATE gbk_chinese_ci;
  1. 数据插入：编写一个Python脚本，使用mysql - connector - python库向两个表中插入大量测试数据。脚本如下：
import mysql.connector

# 连接到MySQL数据库
conn_utf8 = mysql.connector.connect(
    user='root',
    password='password',
    host='127.0.0.1',
    database='test',
    charset='utf8mb4'
)
conn_gbk = mysql.connector.connect(
    user='root',
    password='password',
    host='127.0.0.1',
    database='test',
    charset='gbk'
)

cursor_utf8 = conn_utf8.cursor()
cursor_gbk = conn_gbk.cursor()

# 插入数据
for i in range(100000):
    data_utf8 = f'UTF - 8 content {i}'
    data_gbk = data_utf8.encode('gbk', 'ignore')
    cursor_utf8.execute("INSERT INTO utf8_table (content) VALUES (%s)", (data_utf8,))
    cursor_gbk.execute("INSERT INTO gbk_table (content) VALUES (%s)", (data_gbk,))

conn_utf8.commit()
conn_gbk.commit()

cursor_utf8.close()
cursor_gbk.close()
conn_utf8.close()
conn_gbk.close()

实验查询及性能测试

  1. 全表扫描查询：
    • 在utf8_table上执行全表扫描查询：
SELECT * FROM utf8_table;

- 在`gbk_table`上执行全表扫描查询：

SELECT * FROM gbk_table;

使用MySQL的EXPLAIN语句分析查询执行计划，并记录查询执行时间。通过多次执行取平均值的方式得到较为准确的结果。在这个实验中，由于数据量相对较小且SSD硬盘性能较好，GBK字符集表的全表扫描查询时间略短于UTF - 8mb4字符集表，原因是GBK字符集数据存储占用空间相对较小，磁盘I/O量略低。

  1. 带条件的查询：
    • 在utf8_table上执行带条件的查询，如：
SELECT * FROM utf8_table WHERE content LIKE '%content 50000%';

- 在`gbk_table`上执行同样条件的查询（将条件字符串转换为GBK编码）：

SELECT * FROM gbk_table WHERE content LIKE '%' + CONVERT('content 50000' USING gbk) + '%';

同样使用EXPLAIN分析执行计划并记录查询时间。在这种情况下，由于查询条件涉及到字符比较，UTF - 8mb4和GBK字符集的查询性能差异不大，因为测试数据主要是英文字符，两种字符集在英文字符比较上效率相近。

  1. 排序查询：
    • 在utf8_table上执行排序查询：
SELECT * FROM utf8_table ORDER BY content;

- 在`gbk_table`上执行排序查询：

SELECT * FROM gbk_table ORDER BY content;

记录查询时间，发现使用不同字符集的表在排序查询时，性能差异主要体现在校对规则上。由于两个表分别使用了utf8mb4_general_ci和gbk_chinese_ci校对规则，它们的字符比较和排序方式略有不同，但在这种简单的测试数据下，性能差异并不显著。

字符集选择优化建议

通用场景下的选择

  1. 多语言支持：如果应用程序需要支持多种语言，如国际化网站，UTF - 8mb4字符集是首选。它几乎能涵盖世界上所有语言的字符，通用性强。在这种情况下，即使对于纯英文数据，虽然存储上会相对占用更多空间，但由于其统一的编码方式，在数据处理和查询时不需要进行复杂的字符集转换，整体性能较为稳定。

  2. 单语言环境：对于只处理一种语言的应用，如纯中文的本地企业内部管理系统，可以根据语言选择合适的字符集。如果是中文，GBK字符集在存储中文数据时空间占用相对UTF - 8mb4会少一些，在一些对空间敏感且性能要求较高的场景下可能更合适。但要注意，如果系统有未来扩展支持其他语言的可能性，还是建议优先选择UTF - 8mb4。

性能敏感场景优化

  1. 索引设计与校对规则：在创建索引时，要根据查询的实际需求选择合适的校对规则。如果查询中经常进行不区分大小写的比较，如用户名查找，使用不区分大小写的校对规则（如utf8mb4_general_ci）可以提高索引的利用效率。但如果数据本身对大小写敏感，如密码字段，使用区分大小写的校对规则（如utf8mb4_bin）更合适，不过要注意可能对查询性能产生的影响。

  2. 数据类型与字符集匹配：确保表中列的数据类型与字符集相匹配。例如，对于存储固定长度字符串的CHAR类型，如果字符集占用空间较大，可能会导致不必要的空间浪费。在这种情况下，可以考虑使用VARCHAR类型，它根据实际存储的字符长度动态分配空间。同时，要注意不同数据类型在不同字符集下的最大长度限制。例如，在UTF - 8mb4字符集中，VARCHAR类型的最大长度受限于数据库的最大行大小，一般为65535字节，但由于UTF - 8mb4每个字符最多占用4字节，实际能存储的字符数会相应减少。

  3. 查询优化与字符集转换：尽量避免在查询中进行字符集转换操作。例如，从一个字符集的表中查询数据，然后在应用层进行字符集转换后再展示，这种方式会增加额外的性能开销。如果确实需要不同字符集之间的数据交互，可以在数据库层面进行转换，并且尽量在连接数据库时就指定好合适的字符集，以减少不必要的转换次数。

字符集变更考虑

  1. 数据迁移：如果要对已有的数据库进行字符集变更，首先要考虑数据迁移的复杂性。例如，将一个GBK字符集的数据库转换为UTF - 8mb4字符集，需要确保数据在转换过程中不丢失或损坏。可以使用MySQL提供的工具，如mysqldump和mysql命令行工具，通过指定合适的字符集参数进行数据导出和导入。在导出数据时，使用--default - character - set = gbk参数，导入时使用--default - character - set = utf8mb4参数。但要注意，对于一些特殊字符或编码错误的数据，可能需要额外的处理。

  2. 应用层影响：字符集变更不仅会影响数据库层面，还可能对应用层产生影响。例如，应用程序中连接数据库的配置、数据处理逻辑等都可能需要相应调整。如果应用程序在读取数据库数据后进行了一些基于字符集的操作（如字符串截取、编码转换等），字符集变更后这些操作可能需要重新评估和修改，以确保应用程序的正常运行。同时，要对应用程序进行全面的测试，包括功能测试和性能测试，以验证字符集变更后的系统稳定性和性能表现。