Mariadb

MariaDB 10.6 性能

  • June 23, 2022

我正在遷移我的數據庫更令我驚訝的是我的性能搜尋下降了，我已經檢查了表的所有索引並且它們是相同的

伺服器 新 MariaDB 10.6.8 CentOS 7 8 VCpu 2.8 16 RAM

在此處輸入圖像描述

在此處輸入圖像描述

CREATE TABLE `vendas` (
 `guid` varchar(40) DEFAULT uuid(),
 `id_venda` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
 `codigo` varchar(25) DEFAULT NULL,
 `erp` varchar(20) DEFAULT NULL,
 `fk_loja` int(11) DEFAULT NULL,
 `fk_cliente` int(11) DEFAULT NULL,
 `fk_frete` int(11) DEFAULT NULL,
 `fk_status` int(11) DEFAULT NULL,
 `fk_cupom` int(11) DEFAULT NULL,
 `shipment_id` int(11) DEFAULT NULL,
 `phpsessid` varchar(32) DEFAULT NULL,
 `subtotal` decimal(10,2) DEFAULT 0.00,
 `desconto` decimal(10,2) DEFAULT 0.00,
 `abatimento` decimal(10,2) DEFAULT 0.00,
 `desconto_boleto` decimal(12,4) DEFAULT 0.0000,
 `desconto_boleto_rede` decimal(12,4) DEFAULT 0.0000,
 `desconto_cartao` decimal(12,4) DEFAULT 0.0000,
 `acrescimo` decimal(10,2) DEFAULT 0.00,
 `tarifa` decimal(10,2) DEFAULT 0.00,
 `juros` decimal(10,2) DEFAULT 0.00,
 `data_compra` datetime DEFAULT '0000-00-00 00:00:00',
 `data_pagamento` datetime DEFAULT '0000-00-00 00:00:00',
 `data_preparo` datetime DEFAULT '0000-00-00 00:00:00',
 `data_envio` datetime DEFAULT '0000-00-00 00:00:00',
 `data_estimada` datetime DEFAULT NULL,
 `data_entrega` datetime DEFAULT '0000-00-00 00:00:00',
 `data_cancelamento` datetime DEFAULT '0000-00-00 00:00:00',
 `numero_objeto` varchar(100) DEFAULT '',
 `comentario` text DEFAULT NULL,
 `tipo_residencia` char(1) DEFAULT NULL,
 `endereco` varchar(100) DEFAULT NULL,
 `numero` varchar(50) DEFAULT NULL,
 `bairro` varchar(50) DEFAULT '',
 `complemento` varchar(100) DEFAULT '',
 `cidade` varchar(50) DEFAULT NULL,
 `fk_estado` int(11) DEFAULT NULL,
 `fk_pais` int(11) DEFAULT NULL,
 `provincia` varchar(100) DEFAULT NULL,
 `referencia` text DEFAULT NULL,
 `entregar_para` varchar(100) DEFAULT NULL,
 `cep` char(9) DEFAULT NULL,
 `fk_pagamento` int(11) DEFAULT 0,
 `parcelas` char(2) DEFAULT NULL,
 `fone1` varchar(15) DEFAULT NULL,
 `fone2` varchar(15) DEFAULT NULL,
 `ip` varchar(15) DEFAULT NULL,
 `cpf_cnpj` varchar(20) DEFAULT NULL,
 `prazo` int(2) DEFAULT 0,
 `ultima_alteracao` datetime DEFAULT NULL,
 `integrada` int(1) DEFAULT 0,
 `notificar` int(1) DEFAULT NULL,
 `host` varchar(100) DEFAULT NULL,
 `fk_marketplace` int(11) DEFAULT 1,
 `dynamic_cols` blob DEFAULT NULL,
 `tags` varchar(250) DEFAULT NULL,
 `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
 `whatsapp` int(1) DEFAULT 0,
 `coupon_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id_venda`),
 UNIQUE KEY `codigo` (`codigo`) USING BTREE,
 UNIQUE KEY `guid` (`guid`),
 UNIQUE KEY `erp` (`fk_loja`,`erp`) USING BTREE,
 KEY `vendas` (`id_venda`,`fk_loja`,`fk_cliente`,`fk_frete`,`fk_status`),
 KEY `fk_loja` (`fk_loja`),
 KEY `fk_cliente` (`fk_cliente`),
 KEY `fk_frete` (`fk_frete`),
 KEY `fk_status` (`fk_status`),
 KEY `fk_cupom` (`fk_cupom`),
 KEY `fk_estado` (`fk_estado`),
 KEY `fk_pais` (`fk_pais`),
 KEY `fk_pagamento` (`fk_pagamento`),
 KEY `phpsessid` (`phpsessid`),
 KEY `notificar` (`notificar`),
 KEY `vendas_ibfk_9` (`fk_marketplace`) USING BTREE,
 KEY `data_compra` (`data_compra`),
 KEY `tags` (`tags`),
 KEY `coupon_id` (`coupon_id`),
 KEY `created_at` (`created_at`),
 CONSTRAINT `vendas_ibfk_1` FOREIGN KEY (`fk_loja`) REFERENCES `lojas` (`id_loja`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_10` FOREIGN KEY (`coupon_id`) REFERENCES `discount_coupon` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_2` FOREIGN KEY (`fk_cliente`) REFERENCES `clientes` (`id_cliente`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_3` FOREIGN KEY (`fk_frete`) REFERENCES `fretes` (`id_frete`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_4` FOREIGN KEY (`fk_status`) REFERENCES `status` (`id_status`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_5` FOREIGN KEY (`fk_cupom`) REFERENCES `cupom` (`id_cupom`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_6` FOREIGN KEY (`fk_estado`) REFERENCES `estados` (`id_estado`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_7` FOREIGN KEY (`fk_pais`) REFERENCES `paises` (`id_pais`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_8` FOREIGN KEY (`fk_pagamento`) REFERENCES `pagamentos` (`id_pagamento`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_9` FOREIGN KEY (`fk_marketplace`) REFERENCES `marketplace` (`id_marketplace`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=693460 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `nfe` (
 `fk_venda` int(11) NOT NULL,
 `numero` int(11) NOT NULL,
 `serie` int(2) NOT NULL,
 `chave` varchar(60) DEFAULT NULL,
 `emissao` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
 `xml` longtext DEFAULT NULL,
 `arquivo` blob DEFAULT NULL,
 `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
 PRIMARY KEY (`fk_venda`),
 UNIQUE KEY `fk_venda` (`fk_venda`) USING BTREE,
 KEY `numero` (`numero`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

伺服器舊 MariaDB 10.5.16 CentOS 7 8 VCpu 2.2 16 RAM

CREATE TABLE `vendas` (
 `guid` varchar(40) DEFAULT uuid(),
 `id_venda` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
 `codigo` varchar(25) DEFAULT NULL,
 `erp` varchar(20) DEFAULT NULL,
 `fk_loja` int(11) DEFAULT NULL,
 `fk_cliente` int(11) DEFAULT NULL,
 `fk_frete` int(11) DEFAULT NULL,
 `fk_status` int(11) DEFAULT NULL,
 `fk_cupom` int(11) DEFAULT NULL,
 `shipment_id` int(11) DEFAULT NULL,
 `phpsessid` varchar(32) DEFAULT NULL,
 `subtotal` decimal(10,2) DEFAULT 0.00,
 `desconto` decimal(10,2) DEFAULT 0.00,
 `abatimento` decimal(10,2) DEFAULT 0.00,
 `desconto_boleto` decimal(12,4) DEFAULT 0.0000,
 `desconto_boleto_rede` decimal(12,4) DEFAULT 0.0000,
 `desconto_cartao` decimal(12,4) DEFAULT 0.0000,
 `acrescimo` decimal(10,2) DEFAULT 0.00,
 `tarifa` decimal(10,2) DEFAULT 0.00,
 `juros` decimal(10,2) DEFAULT 0.00,
 `data_compra` datetime DEFAULT '0000-00-00 00:00:00',
 `data_pagamento` datetime DEFAULT '0000-00-00 00:00:00',
 `data_preparo` datetime DEFAULT '0000-00-00 00:00:00',
 `data_envio` datetime DEFAULT '0000-00-00 00:00:00',
 `data_estimada` datetime DEFAULT NULL,
 `data_entrega` datetime DEFAULT '0000-00-00 00:00:00',
 `data_cancelamento` datetime DEFAULT '0000-00-00 00:00:00',
 `numero_objeto` varchar(100) DEFAULT '',
 `comentario` text DEFAULT NULL,
 `tipo_residencia` char(1) DEFAULT NULL,
 `endereco` varchar(100) DEFAULT NULL,
 `numero` varchar(50) DEFAULT NULL,
 `bairro` varchar(50) DEFAULT '',
 `complemento` varchar(100) DEFAULT '',
 `cidade` varchar(50) DEFAULT NULL,
 `fk_estado` int(11) DEFAULT NULL,
 `fk_pais` int(11) DEFAULT NULL,
 `provincia` varchar(100) DEFAULT NULL,
 `referencia` text DEFAULT NULL,
 `entregar_para` varchar(100) DEFAULT NULL,
 `cep` char(9) DEFAULT NULL,
 `fk_pagamento` int(11) DEFAULT 0,
 `parcelas` char(2) DEFAULT NULL,
 `fone1` varchar(15) DEFAULT NULL,
 `fone2` varchar(15) DEFAULT NULL,
 `ip` varchar(15) DEFAULT NULL,
 `cpf_cnpj` varchar(20) DEFAULT NULL,
 `prazo` int(2) DEFAULT 0,
 `ultima_alteracao` datetime DEFAULT NULL,
 `integrada` int(1) DEFAULT 0,
 `notificar` int(1) DEFAULT NULL,
 `host` varchar(100) DEFAULT NULL,
 `fk_marketplace` int(11) DEFAULT 1,
 `dynamic_cols` blob DEFAULT NULL,
 `tags` varchar(250) DEFAULT NULL,
 `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
 `whatsapp` int(1) DEFAULT 0,
 `coupon_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id_venda`),
 UNIQUE KEY `codigo` (`codigo`) USING BTREE,
 UNIQUE KEY `guid` (`guid`),
 UNIQUE KEY `erp` (`fk_loja`,`erp`) USING BTREE,
 KEY `vendas` (`id_venda`,`fk_loja`,`fk_cliente`,`fk_frete`,`fk_status`),
 KEY `fk_cliente` (`fk_cliente`),
 KEY `fk_frete` (`fk_frete`),
 KEY `fk_status` (`fk_status`),
 KEY `fk_cupom` (`fk_cupom`),
 KEY `fk_estado` (`fk_estado`),
 KEY `fk_pais` (`fk_pais`),
 KEY `fk_pagamento` (`fk_pagamento`),
 KEY `phpsessid` (`phpsessid`),
 KEY `notificar` (`notificar`),
 KEY `vendas_ibfk_9` (`fk_marketplace`) USING BTREE,
 KEY `tags` (`tags`),
 KEY `coupon_id` (`coupon_id`),
 KEY `created_at` (`created_at`),
 KEY `data_compra` (`data_compra`),
 CONSTRAINT `vendas_ibfk_10` FOREIGN KEY (`coupon_id`) REFERENCES `discount_coupon` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_11` FOREIGN KEY (`fk_loja`) REFERENCES `lojas` (`id_loja`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_2` FOREIGN KEY (`fk_cliente`) REFERENCES `clientes` (`id_cliente`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_3` FOREIGN KEY (`fk_frete`) REFERENCES `fretes` (`id_frete`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_4` FOREIGN KEY (`fk_status`) REFERENCES `status` (`id_status`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_5` FOREIGN KEY (`fk_cupom`) REFERENCES `cupom` (`id_cupom`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_6` FOREIGN KEY (`fk_estado`) REFERENCES `estados` (`id_estado`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_7` FOREIGN KEY (`fk_pais`) REFERENCES `paises` (`id_pais`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_8` FOREIGN KEY (`fk_pagamento`) REFERENCES `pagamentos` (`id_pagamento`) ON UPDATE CASCADE,
 CONSTRAINT `vendas_ibfk_9` FOREIGN KEY (`fk_marketplace`) REFERENCES `marketplace` (`id_marketplace`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=693701 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

CREATE TABLE `nfe` (
 `fk_venda` int(11) NOT NULL,
 `numero` int(11) NOT NULL,
 `serie` int(2) NOT NULL,
 `chave` varchar(60) DEFAULT NULL,
 `emissao` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
 `xml` longtext DEFAULT NULL,
 `arquivo` blob DEFAULT NULL,
 `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
 PRIMARY KEY (`fk_venda`),
 UNIQUE KEY `fk_venda` (`fk_venda`) USING BTREE,
 KEY `numero` (`numero`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

在此處輸入圖像描述 在此處輸入圖像描述

我能想到的三種可能性：

  • vendas: 替換INDEX(fk_loga)為INDEX(fk_loja, codigo)。我希望有更好的解釋。
  • 我假設status有PRIMARY KEY(status). JOINtostatus似乎沒用。如果刪除它，您會得到相同的答案嗎？這也將加快查詢速度。
  • SHOW VARIABLES LIKE "query_cache%";在每台伺服器上。那裡可能發生了變化。

在此處輸入圖像描述 在此處輸入圖像描述

向 status.tag 添加索引後 在此處輸入圖像描述 在此處輸入圖像描述

在這種情況下，超過我的索引庫對我來說毫無意義，狀態表很小，少於 30 條記錄

引用自：https://dba.stackexchange.com/questions/313671

相關問答

Mariadb

如何更改外鍵中的引用？

  • September 16, 2022
檢視問答
Database-Design

使用 MariaDB 數據庫進行數據輪換

  • September 14, 2022
檢視問答
Mariadb

在 MariaDB 中的某些位置開始和結束的字元串中查找重複的字母？

  • August 26, 2022
檢視問答
Mysql

MariaDB 10.6 出現無法終止的阻塞查詢問題

  • August 25, 2022
檢視問答
Mariadb

插入上的 MariaDB|‘刪除’/tmp/#sql-temptable-e931-bc-5.MAI’時出錯（錯誤程式碼：2“沒有這樣的文件或目錄”）’

  • August 24, 2022
檢視問答
Mysql

Mariadb：二進制日誌需要備份嗎？

  • August 23, 2022
檢視問答