如何在大型 mysql 數據庫中改進此查詢

我的查詢很簡單，但需要 10 秒才能回复。請檢查並幫助我改進

SELECT 
   COUNT(test.code) AS sumid
FROM
   test
WHERE
   test.state = '03' AND test.type = '0'
       AND test.year = '2020'
       AND test.active IN (1 , 2)
       AND test.status IN (1 , 4)
       AND test.marhale = 0
LIMIT 1

解釋那個節目：

id  select_type   table  partitions   type      possible_keys                         key                       key_len     ref     rows    filtered          Extra  
1   SIMPLE        test              index_merge type,year,marhale,state,status,active   year,state,marhale,type 2,6,2,1     20354   27.24                Using intersect(year,state,marhale,type); Using where

這張表是我在大數據庫中的重要表，我們有很多關於所有列的報告，這導致我們索引了許多欄位。

這個節目創建測試表：

CREATE TABLE `tashilat` (
`id` int(11) NOT NULL AUTO_INCREMENT, 
`eid` varchar(10) COLLATE utf8_persian_ci NOT NULL,
`hcode` char(15) COLLATE utf8_persian_ci NOT NULL,
`code` char(16) COLLATE utf8_persian_ci NOT NULL,
`listcode` varchar(12) COLLATE utf8_persian_ci DEFAULT NULL,
`year` smallint(4) NOT NULL DEFAULT '1392',
`ddate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`type` tinyint(4) NOT NULL,
`type_level` tinyint(4) NOT NULL,
`baseprice` varchar(10) COLLATE utf8_persian_ci NOT NULL,
`prices` varchar(10) COLLATE utf8_persian_ci NOT NULL,
`marhale` smallint(3) NOT NULL,
`maxmarhale` smallint(3) NOT NULL DEFAULT '0',
`paystatus` tinyint(1) NOT NULL DEFAULT '0',
`marhale_level` smallint(3) NOT NULL DEFAULT '0',
`pdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`paymethod` smallint(6) DEFAULT NULL,
`state` char(2) COLLATE utf8_persian_ci NOT NULL,
`bank` varchar(10) COLLATE utf8_persian_ci NOT NULL,
`branch` varchar(10) COLLATE utf8_persian_ci NOT NULL,
`tdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`daccept` tinyint(1) NOT NULL DEFAULT '1',
`dcode` varchar(32) COLLATE utf8_persian_ci DEFAULT NULL,
`mtime` varchar(10) COLLATE utf8_persian_ci DEFAULT NULL,
`mshkdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`ghtime` varchar(10) COLLATE utf8_persian_ci DEFAULT NULL,
`gh` varchar(10) COLLATE utf8_persian_ci DEFAULT NULL,
`submitted` tinyint(1) NOT NULL DEFAULT '0',
`submittedtype` int(11) DEFAULT NULL,
`submitteddate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`ztype` varchar(2) COLLATE utf8_persian_ci DEFAULT NULL,
`bdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`pcode` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`lastp` tinyint(4) DEFAULT '0',
`tozihat` text COLLATE utf8_persian_ci,
`ncode` varchar(32) COLLATE utf8_persian_ci DEFAULT NULL,
`ndate` varchar(32) COLLATE utf8_persian_ci DEFAULT NULL,
`ccode` varchar(32) COLLATE utf8_persian_ci DEFAULT NULL,
`cdate` varchar(12) COLLATE utf8_persian_ci DEFAULT NULL,
`bcode` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`bdate` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`secindicator` varchar(100) COLLATE utf8_persian_ci DEFAULT NULL,
`hesab` varchar(20) COLLATE utf8_persian_ci DEFAULT NULL,
`stage` varchar(3) COLLATE utf8_persian_ci DEFAULT NULL,
`loantopic` varchar(50) COLLATE utf8_persian_ci DEFAULT NULL,
`baz` tinyint(1) DEFAULT NULL,
`accept` tinyint(4) NOT NULL DEFAULT '1',
`user` varchar(32) COLLATE utf8_persian_ci NOT NULL,
`sdate` varchar(32) COLLATE utf8_persian_ci NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '1',
`activetype` tinyint(4) NOT NULL DEFAULT '0',
`status_fact` int(11) DEFAULT NULL,
`status_date` varchar(25) COLLATE utf8_persian_ci DEFAULT NULL,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `tashilat_idx_bank` (`bank`),
KEY `tashilat_idx_shobe` (`branch`),
KEY `submitted` (`submitted`) USING BTREE,
KEY `listcode` (`listcode`),
KEY `mshkdate` (`mshkdate`),
KEY `eid` (`eid`),
KEY `baseprice` (`baseprice`),
KEY `code` (`code`),
KEY `hcode` (`hcode`),
KEY `marhale_level` (`marhale_level`),
KEY `type` (`type`),
KEY `year` (`year`),
KEY `marhale` (`marhale`),
KEY `maxmarhale` (`maxmarhale`),
KEY `state` (`state`),
KEY `status` (`status`),
KEY `active` (`active`),
FULLTEXT KEY `sdate` (`sdate`),
FULLTEXT KEY `ndate` (`ndate`),
FULLTEXT KEY `cdate` (`cdate`)
) ENGINE=InnoDB AUTO_INCREMENT=11293550 DEFAULT CHARSET=utf8           COLLATE=utf8_persian_ci
 

我的查詢在 11 秒內執行。使用 count(*) 或使用 count(test.code) 或無限制 1 沒有差異

“相交”告訴我你有一堆 1 列索引，而你應該有一個包含所有這些列的“複合”索引。尤其：

INDEX(state, type, year, marhale,   -- any order is OK
     status, active)               -- later; in either order

也就是說，從使用 測試的所有列開始=。

另一個問題是COUNT(test.code). 這意味著“計算有多少行code IS NOT NULL”。如果你真的想“計算行數”，那就簡單地說COUNT(*)。

如果您想測試 NULLness，請在我推薦的索引code的*末尾添加。*這樣，它將是一個“覆蓋”索引，從而增加了另一個性能提升。（如果你選擇COUNT(*)，code在結尾處粘貼是無害的，但沒有幫助。）

如果您想進一步討論，請提供SHOW CREATE TABLE。

還有一件事。如果您添加我的複合索引，請刪除第一列的索引（可能DROP INDEX 'state'）。

這LIMIT 1是沒用的，因為SELECT COUNT(..) FROM ..總是會給出 1 行。（它也是無害的。）

更多的

FULLTEXT索引適用於冗長的文本字元串，而不是日期。

DATE, DATETIME, orTIMESTAMP應該用於日期/時間。 VARCHAR使比較日期變得困難。

11 個“日期”列——這看起來很奇怪。也許“日期”並不意味著“日期”？

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