Mysql

MySQL 單表查詢性能

  • September 17, 2021

我有一個緩慢的查詢（大約 6-10 分鐘），我一直在努力加快速度，我非常感謝任何指針。我已經按照各種指南根據列的基數創建索引，但我已經沒有想法了。

作業系統是 Windows Server 2016 標準版，配備雙 Xeon E5-2620 v3 @ 2.4Ghz、30 GB RAM，這是一個測試環境，可以加強。我不確定儲存 - 我只知道它是一個 VMWare 虛擬 SCSI 驅動器。

我正在執行 MySQL 8.0.12 社區。

我的創建表程式碼是：

CREATE TABLE `cps_dispensing_data_summary`
            (
   `year` INT(4) DEFAULT NULL,
   `month` INT(2) NOT NULL,
   `dispenser` VARCHAR(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `scan_ref` VARCHAR(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `pi_drug_formulation` VARCHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `line_no` INT(10) DEFAULT NULL,
   `ppd_code` MEDIUMINT(6) UNSIGNED ZEROFILL DEFAULT NULL,
   `qty` DECIMAL(10,2) DEFAULT NULL,
   `packsize` DECIMAL(7,2) DEFAULT NULL,
   `gic_for_71` DECIMAL(12,2) DEFAULT NULL,
   `gic_for_no_71` DECIMAL(12,2) DEFAULT NULL,
   `form_type_code` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `pd_form_barcode` VARCHAR(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   
   KEY `year_month_ppdcode_packsize_dispenser` (`year`,`month`,`ppd_code`,`packsize`,`dispenser`),
   KEY `year_month_dispenser` (`year`,`month`,`dispenser`),
   KEY `ppdcode_packsize_year_month` (`ppd_code`,`packsize`,`year`,`month`)
   
   USING btree)

   engine=innodb DEFAULT charset=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

該表包含大約 212,000,000 行，大約 25GiB，索引使用大約 25GiB。

我正在執行的查詢很長，其中大約有 1000 個條件


(data_summ.PPD_CODE = x AND data_summ.PACKSIZE = y) OR...

所以我在這裡截斷了它，但希望你明白：

SELECT PACKSIZE, YEAR, MONTH, SUM(GIC_FOR_NO_71) AS GIC_FOR_NO_71, SUM(QTY/PACKSIZE) AS PACKS
   FROM cps_dispensing_data_summary data_summ
   WHERE
   YEAR = 2021 and MONTH = 5
   AND
   ((data_summ.PPD_CODE = 520 AND data_summ.PACKSIZE = 300) OR
(data_summ.PPD_CODE = 530 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 531 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 1100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 1300 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 1310 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 1312 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 1313 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 1314 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 1500 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2000 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2010 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 2010 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2200 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2210 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2220 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2221 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2222 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2230 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 2240 AND data_summ.PACKSIZE = 200) OR
(data_summ.PPD_CODE = 2400 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 2410 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 2700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2710 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 2800 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 3100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 3110 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 3300 AND data_summ.PACKSIZE = 12) OR
(data_summ.PPD_CODE = 3310 AND data_summ.PACKSIZE = 5) OR
(data_summ.PPD_CODE = 3320 AND data_summ.PACKSIZE = 20) OR
(data_summ.PPD_CODE = 3320 AND data_summ.PACKSIZE = 60) OR
(data_summ.PPD_CODE = 4100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4110 AND data_summ.PACKSIZE = 70) OR
(data_summ.PPD_CODE = 4400 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4410 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4710 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4730 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 4740 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 4910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 4911 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 5200 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 5210 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 5212 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 5400 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 6100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 6110 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 6120 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 6121 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9010 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9012 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 9013 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 9200 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 9910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10710 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10730 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 10800 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10810 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 10820 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 10900 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 12100 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12100 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 12101 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12110 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12110 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 12111 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12112 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 12120 AND data_summ.PACKSIZE = 15) OR
(data_summ.PPD_CODE = 14000 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 14010 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 14012 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 14700 AND data_summ.PACKSIZE = 200) OR
(data_summ.PPD_CODE = 14900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 15800 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 15810 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 15812 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 17000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 20300 AND data_summ.PACKSIZE = 20) OR
(data_summ.PPD_CODE = 20300 AND data_summ.PACKSIZE = 60) OR
(data_summ.PPD_CODE = 20300 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 20700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 20710 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 20712 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 22720 AND data_summ.PACKSIZE = 32) OR
(data_summ.PPD_CODE = 22720 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 22721 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 22730 AND data_summ.PACKSIZE = 32) OR
(data_summ.PPD_CODE = 22750 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 22760 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 25750 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 27000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 27010 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 27012 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 27200 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 28800 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 28800 AND data_summ.PACKSIZE = 1000) OR
(data_summ.PPD_CODE = 29900 AND data_summ.PACKSIZE = 2000) OR
(data_summ.PPD_CODE = 32400 AND data_summ.PACKSIZE = 1000) OR
(data_summ.PPD_CODE = 32500 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 33000 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33010 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33030 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33031 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33100 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 33121 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33124 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 33200 AND data_summ.PACKSIZE = 5) OR
(data_summ.PPD_CODE = 33300 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 33310 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 34000 AND data_summ.PACKSIZE = 60) OR
(data_summ.PPD_CODE = 34010 AND data_summ.PACKSIZE = 60) OR
(data_summ.PPD_CODE = 34012 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 34100 AND data_summ.PACKSIZE = 21) OR
(data_summ.PPD_CODE = 34101 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 34110 AND data_summ.PACKSIZE = 21) OR
(data_summ.PPD_CODE = 34120 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 34121 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 34130 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 34150 AND data_summ.PACKSIZE = 2) OR
(data_summ.PPD_CODE = 34300 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 34400 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 34410 AND data_summ.PACKSIZE = 90) OR
(data_summ.PPD_CODE = 35900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 35910 AND data_summ.PACKSIZE = 150) OR
(data_summ.PPD_CODE = 36000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36010 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36210 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36212 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36300 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36310 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36400 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 36410 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 36412 AND data_summ.PACKSIZE = 30) OR
(data_summ.PPD_CODE = 36600 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 36610 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36700 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 36710 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 36712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36713 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 36900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 36910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37100 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37110 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37200 AND data_summ.PACKSIZE = 21) OR
(data_summ.PPD_CODE = 37210 AND data_summ.PACKSIZE = 21) OR
(data_summ.PPD_CODE = 37400 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37410 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37412 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37600 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37610 AND data_summ.PACKSIZE = 6) OR
(data_summ.PPD_CODE = 37610 AND data_summ.PACKSIZE = 14) OR
(data_summ.PPD_CODE = 37700 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37710 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37712 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37800 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 37810 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 37810 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 38000 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 39200 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 39210 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 39300 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 39400 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 39410 AND data_summ.PACKSIZE = 84) OR
(data_summ.PPD_CODE = 39500 AND data_summ.PACKSIZE = 8) OR
(data_summ.PPD_CODE = 39500 AND data_summ.PACKSIZE = 50) OR
(data_summ.PPD_CODE = 39510 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 39800 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 39810 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 39812 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 39900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 39910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 40000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 40010 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 40800 AND data_summ.PACKSIZE = 500) OR
(data_summ.PPD_CODE = 41200 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 41210 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 41800 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 41810 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 41820 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 43400 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 44000 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 44010 AND data_summ.PACKSIZE = 56) OR
(data_summ.PPD_CODE = 44400 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 44410 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 46900 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 46910 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 46911 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 47100 AND data_summ.PACKSIZE = 10) OR
(data_summ.PPD_CODE = 47120 AND data_summ.PACKSIZE = 4) OR
(data_summ.PPD_CODE = 49400 AND data_summ.PACKSIZE = 200) OR
(data_summ.PPD_CODE = 49410 AND data_summ.PACKSIZE = 100) OR
(data_summ.PPD_CODE = 49800 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 49810 AND data_summ.PACKSIZE = 28) OR
(data_summ.PPD_CODE = 49812 AND data_summ.PACKSIZE = 28))
   
   GROUP BY PPD_CODE, PACKSIZE

完整查詢的解釋輸出為：

還有我的慢查詢日誌：

# Time: 2021-09-16T16:26:32.927850Z
# Query_time: 386.492964  Lock_time: 0.014415 Rows_sent: 962  Rows_examined: 5898140
SET timestamp=1631809592;

你認為我的索引或表結構會更好還是硬體問題？

有一個關於年、月、packsize 和 ppd_code 的索引。

然後更改您的查詢，以便只指定一次給定的包大小，如

OR ( data_summ.PACKSIZE = 28 and data_summ.PPD_CODE in (
530, 531, 1100, 1300, 1500, 2220, 2221, 2222, 2700, 2710, 2712, 3100,
3110, 4100, 4400, 4410, 4700, 4710, 4712, 4910, 4911, 5200, 5210, 5212,
5400, 6100, 6110, 6120, 6121, 9000, 9010, 9200, 9900, 9910, 10700,
10710, 10712, 10800, 10810, 14900, 17000, 20700, 22721, 27000, 27010,
27012, 28800, 32500, 33100, 33300, 33310, 35900, 36000, 36010, 36210,
36212, 36300, 36310, 36610, 36712, 36900, 36910, 37600, 37700, 37710,
37712, 37810, 39200, 39210, 39510, 39900, 39910, 40000, 40010, 41200,
41210, 41800, 41810, 43400, 44000, 44400, 44410, 46900, 46910, 46911,
49800, 49810, 49812))

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

相關問答

Mysql

如何使 MySQL 在整數範圍選擇查詢中使用索引

  • December 20, 2021
檢視問答
Mysql

為大量條目（性能）編制固定長度字元串（10 個字元 - 電話號碼）的索引？

  • August 26, 2021
檢視問答
Mysql

MariaDB（MySQL）主鍵範圍結合全文索引時查詢慢

  • October 31, 2020
檢視問答
Mysql

使用多個不等式索引 GROUP BY Select

  • April 28, 2020
檢視問答
Mysql

為什麼在 MySQL 中批量多列鍵查詢這麼慢？

  • January 15, 2019
檢視問答
Mysql

使用具有日期範圍的複合索引

  • December 7, 2018
檢視問答