如何在大型mysql数据库中改进此查询？

Question

我的查询很简单，但响应时间为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秒内运行。与计数(*)或计数(test.code)或无限制1没有差别

Answer 1

"intersect“告诉我，您有一堆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 ..总是会给出准确的一行。(它也是无害的。)

More

FULLTEXT索引用于冗长的文本字符串，而不是日期。

DATE、DATETIME或TIMESTAMP应用于日期/时间。VARCHAR使得比较日期变得很困难。

11栏“日期”--这似乎很奇怪。也许“日期”并不意味着“日期”？