需要Mysql优化帮助

Question

我有一个包含 4397898 条记录的表 TABLE_A

+-------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------+------+-----+---------+----------------+
| id | bigint(11) | NO | PRI | NULL | auto_increment |
| usrid | int(11) | YES | | NULL | |
| grpid | int(11) | YES | | NULL | |
| catid | int(11) | YES | MUL | NULL | |
| folderid | int(11) | NO | MUL | 5 | |
| popid | int(11) | YES | MUL | NULL | |
| accid | int(11) | YES | MUL | NULL | |
| contentid | bigint(11) | YES | MUL | NULL | |
| priority | smallint(6) | YES | | NULL | |
| rating | smallint(6) | NO | | 3 | |
| fromid | int(11) | YES | | NULL | |
| ctxid | varchar(255) | YES | | NULL | |
| ctxmsgid | varchar(255) | YES | | NULL | |
| starred | enum('Y','N') | YES | | N | |
| links | enum('y','n') | YES | | n | |
+-------------------+---------------+------+-----+---------+----------------+

它的索引如下

+---------+----+--------------+-----+-------------+-----+-------------+----------+--------+------+------------+
| Table | Nu | Key_name | Seq | Column_name | Col | Cardinality | Sub_part | Packed | Null | Index_type |
+---------+----+--------------+-----+-------------+-----+-------------+----------+--------+------+------------+
| TABLE_A | 0 | PRIMARY | 1 | id | A | 4617132 | NULL | NULL | | BTREE |
| TABLE_A | 1 | catIndx | 1 | catid | A | 256507 | NULL | NULL | YES | BTREE |
| TABLE_A | 1 | contentIndx | 1 | contentid | A | 4617132 | NULL | NULL | YES | BTREE |
| TABLE_A | 1 | catIndx_2 | 1 | catid | A | 18 | NULL | NULL | YES | BTREE |
| TABLE_A | 1 | catIndx_2 | 2 | popid | A | 2013 | NULL | NULL | YES | BTREE |
| TABLE_A | 1 | folderidIndx | 1 | folderid | A | 13619 | NULL | NULL | | BTREE |
| TABLE_A | 1 | accIndex | 1 | accid | A | 1532 | NULL | NULL | YES | BTREE |
| TABLE_A | 1 | popindx | 1 | popid | A | 1532 | NULL | NULL | YES | BTREE |
+---------+----+--------------+-----+-------------+-----+-------------+----------+--------+------+------------+

以下查询

explain SELECT
intCommIndx
FROM TABLE_A
WHERE (
(popid IN('-1',2407 ,22 ,1203 ,1342 ,1207 ,3 ,1254 ,2663 ,1250 ,3461 ,1251 ,14 ,1174 ,120 ,2406 ,2402 ,325 ,925 ,1210 ,2280 ,1 ,1202 ,1560 ,775 ,776 ,789 ,777 ,778 ,12 ,779 ,780 ,781 ,782 ,783 ,784 ,785 ,786 ,787 ,788 ,1209 ,19 ,26 ,9 ,24 ,4 ,25 ,21 ,18 ,1309 ,967 ,1212 ,6 ,9633 ,5 ,2671 ,17 ,13 ,1211 ,749 ,752 ,747 ,750 ,748 ,9302 ,1470 ,190 ,188 ,9711 ,9710 ,9512 ,11512 ,9514 ,9515 ,9516 ,11511 ,9513 ,9316 ,9453 ,1641 ,4986 ,1639 ,1640 ,7814 ,10042 ,9452 ,11236 ,11241 ,11238 ,11239 ,11237 ,11242 ,11240 ,1711 ) OR intpop3indx = -1)
AND catid = 5 )

explain SELECT
id
FROM TABLE_A
WHERE (
(popid IN('-1',2407 ,22 ,1203 ,1342 ,1207 ,3 ,1254 ,2663 ,1250 ,3461 ,1251 ,14 ,1174 ,120 ,2406 ,2402 ,325 ,925 ,1210 ,2280 ,1 ,1202 ,1560 ,775 ,776 ,789 ,777 ,778 ,12 ,779 ,780 ,781 ,782 ,783 ,784 ,785 ,786 ,787 ,788 ,1209 ,19 ,26 ,9 ,24 ,4 ,25 ,21 ,18 ,1309 ,967 ,1212 ,6 ,9633 ,5 ,2671 ,17 ,13 ,1211 ,749 ,752 ,747 ,750 ,748 ,9302 ,1470 ,190 ,188 ,9711 ,9710 ,9512 ,11512 ,9514 ,9515 ,9516 ,11511 ,9513 ,9316 ,9453 ,1641 ,4986 ,1639 ,1640 ,7814 ,10042 ,9452 ,11236 ,11241 ,11238 ,11239 ,11237 ,11242 ,11240 ,1711 ) OR popid = -1)
AND catid = 5 )
给

+----+-------------+---------+------+---------------------------+---------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------------------+---------+---------+-------+--------+-------------+
| 1 | SIMPLE | TABLE_A | ref | catIndx,catIndx_2,popindx | catIndx | 5 | const | 649800 | Using where |
+----+-------------+---------+------+---------------------------+---------+---------+-------+--------+-------------+

如何提高查询速度？

结果中只有 850 行。
mysql> SELECT
-> count(id)
-> FROM TABLE_A
-> WHERE (
-> (popid IN('-1',2407,22,1203,1342,1207,3,1254,2663,1250,3461,1251,14,1174,120,2406,2402,325,925,1210,2280,1,1202,1560,775,776,789,777,778,12,779,780,781,782,783,784,785,786,787,788,1209,19,26,9,24,4,25,21,18,1309,967,1212,6,9633,5,2671,17,13,1211,749,752,747,750,748,9302,1470,190,188,9711,9710,9512,11512,9514,9515,9516,11511,9513,9316,9453,1641,4986,1639,1640,7814,10042,9452,11236,11241,11238,11239,11237,11242,11240,1711) OR intpop3indx = -1)
-> AND catid = 5 );
+--------------------+
| count(id) |
+--------------------+
| 850 |
+--------------------+
1 row in set (11.22 sec)

我可以进行哪些更改才能在几毫秒内获得这 850 条记录？

Answer 1

这是查询:

SELECT intCommIndx 
FROM TABLE_A
WHERE ( (popid IN('-1',2407 ,22 ,1203   ,1342   ,1207   ,3  ,1254   ,2663   ,1250   ,3461   ,1251   ,14 ,1174   ,120    ,2406   ,2402   ,325    ,925    ,1210   ,2280   ,1  ,1202   ,1560   ,775    ,776    ,789    ,777    ,778    ,12 ,779    ,780    ,781    ,782    ,783    ,784    ,785    ,786    ,787    ,788    ,1209   ,19 ,26 ,9  ,24 ,4  ,25 ,21 ,18 ,1309   ,967    ,1212   ,6  ,9633   ,5  ,2671   ,17 ,13 ,1211   ,749    ,752    ,747    ,750    ,748    ,9302   ,1470   ,190    ,188    ,9711   ,9710   ,9512   ,11512  ,9514   ,9515   ,9516   ,11511  ,9513   ,9316   ,9453   ,1641   ,4986   ,1639   ,1640   ,7814   ,10042  ,9452   ,11236  ,11241  ,11238  ,11239  ,11237  ,11242  ,11240  ,1711   ) OR intpop3indx = -1
        ) AND
        catid = 5
       )

使用 or 进行查询可能很难优化。我建议在表上创建两个索引，然后重写查询。这两个索引是 intCommIndx(catid, popid, intCommIndx)和 intCommIndx(catid, intpop3indx, intCommIndx) .那么新的查询是:

SELECT intCommIndx 
FROM TABLE_A
WHERE catid = 5 and
      popid IN ('-1',2407 ,22 ,1203   ,1342   ,1207   ,3  ,1254   ,2663   ,1250   ,3461   ,1251   ,14 ,1174   ,120    ,2406   ,2402   ,325    ,925    ,1210   ,2280   ,1  ,1202   ,1560   ,775    ,776    ,789    ,777    ,778    ,12 ,779    ,780    ,781    ,782    ,783    ,784    ,785    ,786    ,787    ,788    ,1209   ,19 ,26 ,9  ,24 ,4  ,25 ,21 ,18 ,1309   ,967    ,1212   ,6  ,9633   ,5  ,2671   ,17 ,13 ,1211   ,749    ,752    ,747    ,750    ,748    ,9302   ,1470   ,190    ,188    ,9711   ,9710   ,9512   ,11512  ,9514   ,9515   ,9516   ,11511  ,9513   ,9316   ,9453   ,1641   ,4986   ,1639   ,1640   ,7814   ,10042  ,9452   ,11236  ,11241  ,11238  ,11239  ,11237  ,11242  ,11240  ,1711   )
UNION
SELECT intCommIndx 
FROM TABLE_A
WHERE catid = 5 and intpop3indx = -1;

这将允许仅使用索引来满足每个子查询。