mysql - 优化MySQL表结构

Question

我是 MySQL 新手。我有一个具有以下结构的表:

CREATE TABLE `gc_ads` (
  `n` int(10) unsigned NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL DEFAULT '',
  `detail` text NOT NULL,
  `c` char(6) DEFAULT NULL,
  `c_path` char(24) DEFAULT NULL,
  `a` char(8) DEFAULT NULL,
  `a_path` char(32) DEFAULT NULL,
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `edited` int(10) unsigned NOT NULL DEFAULT '0',
  `t1` int(10) unsigned NOT NULL DEFAULT '0',
  `t2` int(10) unsigned NOT NULL DEFAULT '0',
  `status` set('waiting','enabled','disabled','queue','wait','preview') NOT NULL DEFAULT '',
  `en_cats` tinyint(1) NOT NULL DEFAULT '0',
  `home_page` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`n`,`t1`,`t2`,`status`,`en_cats`),
  KEY `home_page_featured` (`status`,`home_page`),
  KEY `recount_index` (`c_path`,`a_path`,`t1`,`t2`,`status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

我需要运行以下查询:

select * from gc_ads where c_path in ("_9_","_9_30_","_9_101_","_9_102_","_9_103_","_9_105_","_9_106_","_9_30_132_","_9_30_133_","_9_30_134_","_9_30_135_","_9_30_136_","_9_30_137_","_9_30_138_","_9_30_139_","_9_30_140_","_9_30_141_","_9_30_142_","_9_30_143_","_9_30_144_","_9_30_145_","_9_30_146_","_9_30_147_","_9_30_148_","_9_30_149_","_9_30_150_","_9_30_151_","_9_30_152_","_9_30_153_","_9_30_154_","_9_30_155_","_9_30_156_","_9_30_157_","_9_30_158_","_9_30_159_","_9_30_160_","_9_30_161_","_9_30_162_","_9_101_222_","_9_101_221_","_9_101_220_","_9_101_219_","_9_101_218_","_9_101_217_","_9_101_216_","_9_101_215_","_9_101_214_","_9_101_213_","_9_101_212_","_9_101_211_","_9_101_210_","_9_101_209_","_9_101_208_","_9_101_207_","_9_101_206_","_9_101_205_","_9_101_204_","_9_101_203_","_9_101_202_","_9_101_201_","_9_101_200_","_9_101_199_","_9_101_198_","_9_101_196_","_9_101_197_","_9_101_195_","_9_194_","_9_101_223_","_9_101_224_","_9_101_225_","_9_102_226_","_9_102_227_","_9_102_228_","_9_102_229_","_9_102_230_","_9_102_231_","_9_102_232_","_9_102_233_","_9_102_234_","_9_102_235_","_9_102_236_","_9_102_237_","_9_102_238_","_9_102_239_","_9_102_240_","_9_102_241_","_9_102_242_","_9_102_243_","_9_102_244_","_9_102_245_","_9_102_246_","_9_102_247_","_9_102_248_","_9_102_249_","_9_102_250_","_9_102_251_","_9_102_252_","_9_102_253_","_9_102_254_","_9_102_255_","_9_102_256_") and status='enabled' order by created desc limit 0,50;

此查询大约需要 0.38 秒，查询大约 60,000 条记录。我认为这非常慢。 Esplain 显示以下内容:

+----+-------------+--------+------+----------------------------------+--------------------+---------+-------+-------+-----------------------------+
| id | select_type | table  | type | possible_keys                    | key                | key_len | ref   | rows  | Extra                       |
+----+-------------+--------+------+----------------------------------+--------------------+---------+-------+-------+-----------------------------+
|  1 | SIMPLE      | gc_ads | ref  | recount_index,home_page_featured | home_page_featured | 1       | const | 34347 | Using where; Using filesort |
+----+-------------+--------+------+----------------------------------+--------------------+---------+-------+-------+-----------------------------+

如果我运行此查询(相同的查询，但其中没有“and status='enabled'”，则需要大约 0.10 秒:

select * from gc_ads use index(recount_index, home_page_featured) where c_path in ("_9_","_9_30_","_9_101_","_9_102_","_9_103_","_9_105_","_9_106_","_9_30_132_","_9_30_133_","_9_30_134_","_9_30_135_","_9_30_136_","_9_30_137_","_9_30_138_","_9_30_139_","_9_30_140_","_9_30_141_","_9_30_142_","_9_30_143_","_9_30_144_","_9_30_145_","_9_30_146_","_9_30_147_","_9_30_148_","_9_30_149_","_9_30_150_","_9_30_151_","_9_30_152_","_9_30_153_","_9_30_154_","_9_30_155_","_9_30_156_","_9_30_157_","_9_30_158_","_9_30_159_","_9_30_160_","_9_30_161_","_9_30_162_","_9_101_222_","_9_101_221_","_9_101_220_","_9_101_219_","_9_101_218_","_9_101_217_","_9_101_216_","_9_101_215_","_9_101_214_","_9_101_213_","_9_101_212_","_9_101_211_","_9_101_210_","_9_101_209_","_9_101_208_","_9_101_207_","_9_101_206_","_9_101_205_","_9_101_204_","_9_101_203_","_9_101_202_","_9_101_201_","_9_101_200_","_9_101_199_","_9_101_198_","_9_101_196_","_9_101_197_","_9_101_195_","_9_194_","_9_101_223_","_9_101_224_","_9_101_225_","_9_102_226_","_9_102_227_","_9_102_228_","_9_102_229_","_9_102_230_","_9_102_231_","_9_102_232_","_9_102_233_","_9_102_234_","_9_102_235_","_9_102_236_","_9_102_237_","_9_102_238_","_9_102_239_","_9_102_240_","_9_102_241_","_9_102_242_","_9_102_243_","_9_102_244_","_9_102_245_","_9_102_246_","_9_102_247_","_9_102_248_","_9_102_249_","_9_102_250_","_9_102_251_","_9_102_252_","_9_102_253_","_9_102_254_","_9_102_255_","_9_102_256_") order by created desc limit 0,50;

解释显示以下内容:

+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-----------------------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows  | Extra                       |
+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | gc_ads | range | recount_index | recount_index | 73      | NULL | 14293 | Using where; Using filesort |
+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-----------------------------+

如果我在没有“order by ...”的情况下运行查询，速度会非常快(0.01s):

select * from gc_ads use index(recount_index, home_page_featured) where c_path in ("_9_","_9_30_","_9_101_","_9_102_","_9_103_","_9_105_","_9_106_","_9_30_132_","_9_30_133_","_9_30_134_","_9_30_135_","_9_30_136_","_9_30_137_","_9_30_138_","_9_30_139_","_9_30_140_","_9_30_141_","_9_30_142_","_9_30_143_","_9_30_144_","_9_30_145_","_9_30_146_","_9_30_147_","_9_30_148_","_9_30_149_","_9_30_150_","_9_30_151_","_9_30_152_","_9_30_153_","_9_30_154_","_9_30_155_","_9_30_156_","_9_30_157_","_9_30_158_","_9_30_159_","_9_30_160_","_9_30_161_","_9_30_162_","_9_101_222_","_9_101_221_","_9_101_220_","_9_101_219_","_9_101_218_","_9_101_217_","_9_101_216_","_9_101_215_","_9_101_214_","_9_101_213_","_9_101_212_","_9_101_211_","_9_101_210_","_9_101_209_","_9_101_208_","_9_101_207_","_9_101_206_","_9_101_205_","_9_101_204_","_9_101_203_","_9_101_202_","_9_101_201_","_9_101_200_","_9_101_199_","_9_101_198_","_9_101_196_","_9_101_197_","_9_101_195_","_9_194_","_9_101_223_","_9_101_224_","_9_101_225_","_9_102_226_","_9_102_227_","_9_102_228_","_9_102_229_","_9_102_230_","_9_102_231_","_9_102_232_","_9_102_233_","_9_102_234_","_9_102_235_","_9_102_236_","_9_102_237_","_9_102_238_","_9_102_239_","_9_102_240_","_9_102_241_","_9_102_242_","_9_102_243_","_9_102_244_","_9_102_245_","_9_102_246_","_9_102_247_","_9_102_248_","_9_102_249_","_9_102_250_","_9_102_251_","_9_102_252_","_9_102_253_","_9_102_254_","_9_102_255_","_9_102_256_") limit 0,50;

解释显示以下内容:

+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-------------+
| id | select_type | table  | type  | possible_keys | key           | key_len | ref  | rows  | Extra       |
+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-------------+
|  1 | SIMPLE      | gc_ads | range | recount_index | recount_index | 73      | NULL | 14293 | Using where |
+----+-------------+--------+-------+---------------+---------------+---------+------+-------+-------------+    

我猜我需要为此查询创建正确的索引，但我真的不知道如何开始解决这个问题。

Answer 1

这是您的查询:

select *
from gc_ads
where c_path in (. . .) and status='enabled'
order by created desc limit 0,50;

where 子句中的条件是 in 和相等。最好的索引是gc_ads(status, c_path,created)。 where 子句可以使用前两个元素。 order by 可以使用最后一个。

recount_index 不太正确，因为它在 status 之前有两个附加元素。