我的数据库架构由以下两个表组成:
CREATE TABLE `categories` (
`id` bigint(20) NOT NULL auto_increment,
`title` varchar(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
和
CREATE TABLE `articles` (
`id` bigint(20) NOT NULL auto_increment,
`title` varchar(512) NOT NULL,
`body` longtext,
`state` varchar(7) NOT NULL,
`type` varchar(6) NOT NULL,
`category` bigint(20) default NULL,
`publishedAt` datetime default NULL,
PRIMARY KEY (`id`),
KEY `FK_category_to_article_category` (`category`),
CONSTRAINT `FK_category_to_article_category` FOREIGN KEY (`category`) REFERENCES `categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
对于articles表,
state
列具有“published”或“unpublished”等值,type
列具有“news”、“gossip”和“opinion”等值。我的应用程序执行许多类似以下的查询:
select * from articles where state="PUBLISHED" and type in ("NEWS","GOSSIP")
and category in (4) and publishedAt<=now() order by publishedAt desc;
我有大约10K篇文章,我正试图确定上面的查询是否在使用默认外键时性能更好,或者应该使用多列索引。
没有索引(使用“explain extended”):
+----+-------------+-------+------+---------------------------------+---------------------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------+---------------------------------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | this_ | ref | FK_category_to_article_category | FK_category_to_article_category | 9 | const | 630 | Using where; Using filesort |
+----+-------------+-------+------+---------------------------------+---------------------------------+---------+-------+------+-----------------------------+
如果我创建多列索引并再次解释(强制指定索引):
create index I_s_t_c_p on articles (state, type, category, publishedAt);
+----+-------------+-------+-------+---------------+-----------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | this_ | range | I_s_t_c_p | I_s_t_c_p | 61 | NULL | 1216 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+------------------------------------------+
查询实际返回的行数是630。在我看来,由于使用了所有索引列,多列索引的性能应该比fk更好,但使用索引时检查了~1200行这一事实让我感到困惑。我知道这些数字只是估计值,但这两个键之间的差别很大;使用组合索引,我们检查了两行的数量。
所以我的问题是:
为什么用多列索引检查这么多行?
由于使用fk,我们有一个连接类型“ref”,使用组合索引,我们有一个连接类型“range”,这是否意味着使用fk的查询比另一个查询更好/更快?
我是否应该使用检查的行数的估计值作为判断索引是否良好/最佳的标准?
在这个用例中,多列索引比fk好吗?我应该根据什么做决定?
一些附加信息:
在不强制查询索引的情况下,优化器选择了fk。当我对文章执行
analyze table
时,选择了多列索引。我正在使用mysql 5.0.15
索引信息
+----------+------------+---------------------------------+--------------+-------------+-------------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality | Index_type |
+----------+------------+---------------------------------+--------------+-------------+-------------+------------+
| articles | 0 | PRIMARY | 1 | id | 12561 | BTREE |
| articles | 1 | FK_category_to_article_category | 1 | category | 37 | BTREE |
| articles | 1 | I_s_t_c_p | 1 | state | 8 | BTREE |
| articles | 1 | I_s_t_c_p | 2 | type | 32 | BTREE |
| articles | 1 | I_s_t_c_p | 3 | category | 163 | BTREE |
| articles | 1 | I_s_t_c_p | 4 | publishedAt | 12561 | BTREE |
+----------+------------+---------------------------------+--------------+-------------+-------------+------------+
提前谢谢。
最佳答案
如您所见,publishedAt
上的索引与pk具有相同的基数。这没什么用。我将尝试创建一个复合索引,其中列的顺序为(category,type,state)
。这样,索引的第一部分是最有选择性的。