主页 / dba / 问题 / 343452
Accepted
yaki_nuka
Asked: 2024-11-06 23:54:58 +0800 CST

MySQL：内部使用 NAME_CONST

  • 772

我使用的是 MySQL 8.0.32，我对内部转换有一些疑问，我不明白。除此之外，有时这种转换会显示在 SHOW FULL PROCESSLIST 中，因为它的执行时间太长。

这是我的桌子。

CREATE TABLE `provinces` (
 `ID` VARCHAR(2) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 `PROVINCIA` VARCHAR(45) NOT NULL COLLATE 'utf8mb4_swedish_ci',
  PRIMARY KEY (`ID`)
  ) COLLATE='utf8mb4_swedish_ci' ENGINE=InnoDB;

我有 53 行这样的

ID 省
01 阿拉瓦
02 阿尔巴塞特

MySQL 函数是：

CREATE FUNCTION `get_provincia`( `id_provincia` VARCHAR(2)  )
RETURNS varchar(45)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE desc_provincia VARCHAR(45) DEFAULT '-';

    SELECT PROVINCIA INTO desc_provincia
    FROM provinces
    WHERE ID = id_provincia;
    
    RETURN desc_provincia;
END

该功能的 SHOW STATUS 显示：

  • 字符集客户端: utf8mb4
  • 排序规则连接：utf8mb4_0900_ai_ci
  • 数据库排序规则：latin1_swedish_ci

该函数作为查询中的字段被调用。

SELECT customer_id, customer_name, ..., get_provincia(SUBSTRING(zip_code,1,2))...
FROM ...

当我使用 SHOW FULL PROCESSLIST 检查执行情况时，函数内部的查询显示如下：

SELECT provincia INTO desc_provincia
FROM provincias
WHERE id =  NAME_CONST('id_provincia',_latin1'15' COLLATE 'latin1_swedish_ci')

因此，我有以下问题：

  1. 为什么 MySQL 强制使用 NAME_CONST？
  2. 这会给性能带来影响吗？我不明白为什么非常简单的查询会在 SHOW FULL PROCESSLIST 命令的“时间”列中显示“2”值，因为它需要 2 秒。

提前致谢。

mysql

1 个回答

  1. Best Answer

    我在 MySQL 版本上测试了您的示例8.0.39-0ubuntu0.22.04.1。

    CREATE DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE test;

CREATE TABLE `provinces` (
 `ID` VARCHAR(2) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 `PROVINCIA` VARCHAR(45) NOT NULL COLLATE 'utf8mb4_swedish_ci',
 `zip_code` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_swedish_ci',
  PRIMARY KEY (`ID`)
) COLLATE='utf8mb4_swedish_ci' ENGINE=InnoDB;

INSERT INTO provinces VALUES ('01', 'Álava', '01000'), ('02', 'Albacete', '02000'),
('03', 'Alicante', '03000'),('04', 'Almería', '04000'),('05', 'Ávila', '05000'),('06', 'Badajoz', '06000'),
('07', 'Balears', '07000'), ('08', 'Barcelona', '08000'),('09', 'Burgos', '09000'),('10', 'Cáceres', '10000'),
('11', 'Cádiz', '11000'),('12', 'Castellón', '12000'),('13', 'Ciudad Real', '13000'),('14', 'Córdoba', '14000'),
('15', 'A Coruña', '15000'),('16', 'Cuenca', '16000'),('17', 'Girona', '17000'),('18', 'Granada', '18000'),
('19', 'Guadalajara', '19000'),('20', 'Guipúzcoa', '20000'),('21', 'Huelva', '21000'),('22', 'Huesca', '22000'),
('23', 'Jaén', '23000'),('24', 'León', '24000'),('25', 'Lleida', '25000'),('26', 'La Rioja', '26000'),('27', 'Lugo', '27000'),
('28', 'Madrid', '28000'),('29', 'Málaga', '29000'),('30', 'Murcia', '30000'),('31', 'Navarra', '31000'),
('32', 'Ourense', '32000'),('33', 'Asturias', '33000'),('34', 'Palencia', '34000'),('35', 'Las Palmas', '35000'),
('36', 'Pontevedra', '36000'),('37', 'Salamanca', '37000'),('38', 'Santa Cruz de Tenerife', '38000'),('39', 'Cantabria', '39000'),
('40', 'Segovia', '40000'),('41', 'Sevilla', '41000'),('42', 'Soria', '42000'),('43', 'Tarragona', '43000'),
('44', 'Teruel', '44000'),('45', 'Toledo', '45000'),('46', 'Valencia', '46000'),('47', 'Valladolid', '47000'),
('48', 'Bizkaia', '48000'),('49', 'Zamora', '49000'),('50', 'Zaragoza', '50000'),('51', 'Ceuta', '51000'),('52', 'Melilla', '52000');

    我创建了以下函数

    DELIMITER //

CREATE DEFINER=`root`@`localhost` FUNCTION `get_provincia`( `id_provincia` VARCHAR(2) )
RETURNS VARCHAR(45) CHARSET latin1
READS SQL DATA
BEGIN
    DECLARE desc_provincia VARCHAR(45) DEFAULT '-';

    SELECT PROVINCIA INTO desc_provincia
    FROM provinces
    WHERE ID = id_provincia;

    RETURN desc_provincia;
END //

DELIMITER ;

    一个简单的 Bash 脚本来捕获SHOW FULL PROCESSLIST输出

    #!/bin/bash
end=$((SECONDS+60))  # Run for approximately 1 minute
while [ $SECONDS -lt $end ]; do
    mysql --user=root --password='zzzzzzzzzzzzzzzz' -e "SHOW FULL PROCESSLIST;" \
    >> /home/e.basha/slow_query_3_sec.txt
    usleep 100  # Sleep for 0.0001 seconds (100 microseconds)
done

    运行查询

    SELECT ID,PROVINCIA,get_provincia(SUBSTRING(zip_code,1,2)) FROM provinces;

    制作

    SELECT PROVINCIA INTO desc_provincia\n    FROM provinces\n    WHERE ID =  NAME_CONST('id_provincia',_latin1'08' COLLATE 'latin1_swedish_ci');

    为什么 MySQL 强制使用 NAME_CONST？

    由于数据库排序规则设置 latin1_swedish_ci，导致使用 utf8mb4_swedish_ci 的省份表与使用 latin1_swedish_ci 的查询参数之间的排序规则不匹配。

    这会给性能带来影响吗？我不明白为什么非常简单的查询会在 SHOW FULL PROCESSLIST 命令的“时间”列中显示“2”值，因为它需要 2 秒。

    通常每种排序规则转换都会影响性能，我们在 JOINS 等等中看到这一点。

    我有什么建议？

    修改要使用的函数参数utf8mb4_swedish_ci，并尽可能始终使用相同的排序规则。

    DELIMITER //

CREATE DEFINER=`root`@`localhost` FUNCTION `get_provincia`( `id_provincia` VARCHAR(2) CHARSET utf8mb4 COLLATE utf8mb4_swedish_ci)
RETURNS VARCHAR(45) CHARSET utf8mb4
READS SQL DATA
BEGIN
    DECLARE desc_provincia VARCHAR(45) DEFAULT '-';

    SELECT PROVINCIA INTO desc_provincia
    FROM provinces
    WHERE ID = id_provincia;

    RETURN desc_provincia;
END //

DELIMITER ;

    运行与上述相同的 SELECT 查询后，SHOW FULL PROCESSLIST结果为

    21139379    root    localhost   test    Query   0   closing tables  SELECT ID,PROVINCIA,get_provincia(SUBSTRING(zip_code,1,2)) FROM provinces
    • 1

相关问题