我的数据库架构由以下两个表组成:

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;

对于文章表，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 行让我困惑。我知道这些数字只是估计值，但两个 key 之间的差异非常大；使用组合索引，我们检查了双倍数量的行。

所以我的问题如下:

1. 为什么使用多列索引检查了这么多行？
2. 由于使用 FK 我们有一个连接类型“ref”并且使用组合索引我们有一个连接类型“range”，这是否意味着使用 FK 的查询比另一个查询更好/更快？
3. 我是否应该使用对检查的行数的估计作为判断索引是否良好/最佳的标准？
4. 在此用例中，多列索引是否比 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)。这样，索引的第一部分是最具选择性的。

关于使用索引优化 Mysql 查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6696678/