我有一个这样的表:
CREATE TABLE `ng1` (
`word1` TEXT NOT NULL,
`kd` TEXT NOT NULL,
`kd1` TEXT NOT NULL,
`kd2` TEXT,
`kd3` TEXT,
`dt` INTEGER DEFAULT '0',
`dm` INTEGER DEFAULT '0',
`freq` REAL DEFAULT '0',
`lastused` INTEGER DEFAULT '0',
PRIMARY KEY(word1)
);
我有以下类型的查询:
SELECT * FROM ng1 WHERE kd='abc' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd1='a' AND dt='1' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd2='ab' AND dt='1' AND dm='1' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd3='abc' AND dt='1' AND dm='1' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd3='abc' AND word1 LIKE 'abc%' AND dt='1' AND dm='1' ORDER BY freq DESC
那么,什么样的指数可以帮助我获得最佳表现呢?
为我需要在查询中使用的每个字段创建多个索引--index1:kd
- 索引2:kd1
- index3:kd2。。。。和用于kd3、dt、dm、freq字段的索引
创建多列的多个索引:
- 索引1:kd,频率
- 索引2:kd1,频率
- 索引3:kd2,频率
- 索引4:kd3,频率
- 索引5:kd3,dt,dm,freq
创建具有多列的单个索引:
- 索引1:kd,kd1,kd2,kd3,dt,dm,freq
这些注释适用于MySQL。
首先,不能对TEXT
进行索引。您可以将VARCHAR
索引到某个限制(通常为255)。因此,在可行的情况下,将TEXT
更改为一些合理的长度VARCHAR(n)
。
让我们看看您的示例:
SELECT * FROM ng1 WHERE kd='abc' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd1='a' AND dt='1' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd2='ab' AND dt='1' AND dm='1' ORDER BY freq DESC
SELECT * FROM ng1 WHERE kd3='abc' AND dt='1' AND dm='1' ORDER BY freq DESC
这四个基本上都是一样的:WHERE
只有'='AND'd在一起。因此,用这些变量按任意顺序创建一个复合索引。由于您完成了WHERE
,但没有GROUP BY
,因此可以转到ORDER BY
。将freq
添加到末尾。例如,最后一个需要
INDEX(kd3, dt, dm, freq) or
INDEX(dm, dt, kd4, freq) or ...
这种情况不同,因为LIKE
:
SELECT * FROM ng1 WHERE kd3='abc' AND word1 LIKE 'abc%' AND dt='1' AND dm='1' ORDER BY freq DESC
没有前导通配符的LIKE
被认为是一个"范围",有点像word1 >= 'abc' AND word1 < 'abd'
。与ORDER BY
一样,范围将是索引中使用的最后一个列。所以,你能做的最好的事情就是:
INDEX(kd3, dt, dm, word1)
前3个可以是任何顺序,但word1
必须在之后。添加freq
将毫无用处。
我在我的索引食谱中讨论了这一点以及更多内容。
因此,在您的示例中,5个SELECTs
的5个不同索引是最优的。无论出于何种原因,如果您希望最小化索引的数量,这里有两种方法:
有时优化器会很乐意跳过WHERE
并为ORDER BY
使用索引。在这种情况下,INDEX(freq)
对所有5个都"总比什么都没有好"。
你的问题暗示了一些"巩固"。我建议INDEX(dt, dm, kd3)
,按照这个顺序,处理最后4个SELECTs
"总比什么都没有好"。dt
必须是#2的第一个。添加kd3
是因为它有助于#4和#5。
其他备注。。。
NULL
/NOT NULL
无关紧要。
PRIMARY KEY(word1)
"聊胜于无",但仅适用于#5。而且,根据基数的不同,它可能会被优先使用,而不是我建议的。
写入操作(INSERT
、DELETE
,有时还有UPDATE
)由于具有大量索引而变慢。然而,通常SELECTs
的好处大于此。所以,只要你不"索引每一列",就不用担心索引的数量。