Início / dba / Perguntas / 343452
Accepted
yaki_nuka
Asked: 2024-11-06 23:54:58 +0800 CST

MySQL: usando NAME_CONST internamente

  • 772

Estou usando o MySQL 8.0.32. Tenho algumas perguntas sobre uma conversão interna, que não entendo. Além disso, às vezes essa conversão é mostrada em SHOW FULL PROCESSLIST, pois sua execução era muito longa.

Esta é a minha mesa.

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;

Eu tenho 53 linhas como essas

EU IA PROVÍNCIA
01 Álava
02 Albacete

A função 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

O SHOW STATUS desta função mostra:

  • character_set_client: utf8mb4
  • conexão_de_agrupamento: utf8mb4_0900_ai_ci
  • database_collation: latin1_swedish_ci

Essa função é invocada como um campo em uma consulta.

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

Quando estou revisando a execução com SHOW FULL PROCESSLIST, a consulta dentro da função é mostrada assim:

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

Então, eu tenho essas perguntas:

  1. Por que o NAME_CONST é forçado pelo MySQL?
  2. Isso tem impacto no desempenho? Não entendo que uma consulta muito simples mostre o valor "2" na coluna "Time" do comando SHOW FULL PROCESSLIST, pois demora 2 segundos.

Desde já, obrigado.

mysql

1 respostas

  1. Best Answer

    Testei seu exemplo na versão 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');

    Criei a seguinte função

    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 ;

    Um script bash simples para capturar a SHOW FULL PROCESSLISTsaída

    #!/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

    Executando a consulta

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

    produzido

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

    Por que o NAME_CONST é forçado pelo MySQL?

    Devido à incompatibilidade de ordenação entre a tabela de províncias que usa utf8mb4_swedish_ci e os parâmetros de consulta que usam latin1_swedish_ci devido à configuração de ordenação do banco de dados latin1_swedish_ci.

    Isso tem impacto no desempenho? Não entendo que uma consulta muito simples mostre o valor "2" na coluna "Time" do comando SHOW FULL PROCESSLIST, pois demora 2 segundos.

    Normalmente, cada conversão de agrupamento afeta o desempenho, como vemos em JOINS etc.

    O que eu sugiro?

    Modifique o parâmetro da função a ser usada utf8mb4_swedish_cie sempre use a mesma ordenação, se possível.

    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 ;

    Depois de executar a mesma consulta SELECT acima, o SHOW FULL PROCESSLISTresultado é

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

relate perguntas