mysql 使用索引进行查询_使用索引进行Mysql查询优化

我的db模式由以下两个表组成：

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;

对于文章表,状态列具有“PUBLISHED”或“UNPUBLISHED”等值,类型列具有“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.当我对文章执行分析表时,选择了多列索引.

>我正在使用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相同的基数.这并没有真正帮助.我会尝试使用该顺序的列(类别,类型,状态)创建复合索引.这样,索引的第一部分是最具选择性的.