Mysql索引问题

Question

谁能告诉我为什么mysql在下面的查询中没有使用正确的索引

SELECT `Slugs`.`slug` FROM `slugs` AS `Slugs`   
WHERE `Slugs`.`country_id` = 1 AND `Slugs`.`expired` = 0    
LIMIT 308400,300

我已经为where子句中引用的两列创建了复合索引

表slugs的表结构

CREATE TABLE IF NOT EXISTS `slugs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(255) NOT NULL,
  `post_fields` text NOT NULL,
  `slugdata` text NOT NULL,
  `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `country_id` int(11) NOT NULL DEFAULT '1',
  `expired` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug_2` (`slug`,`country_id`),
  KEY `updated_date` (`updated_date`),
  KEY `country_id` (`country_id`),
  KEY `slug` (`slug`),
  KEY `expired` (`expired`),
  KEY `country_id_2` (`country_id`,`expired`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1612485 ;

索引：

Keyname      Type   Unique  Packed  Field        Cardinality     Collation  Null    Comment
PRIMARY      BTREE  Yes     No      id               1406994      A     
slug_2       BTREE  Yes     No      slug             1406994      A     
                                    country_id       1406994      A 
updated_date BTREE  No      No      updated_date          21      A     
country_id   BTREE  No      No      country_id            21      A     
slug         BTREE  No      No      slug             1406994      A     
expired      BTREE  No      No      expired               21      A     
country_id_2 BTREE  No      No      country_id            21      A     
                                    expired               21      A

Explain输出

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  Slugs   ref     country_id,expired,country_id_2     country_id  4   const   670284  Using where

Answer 1

你说它没有使用正确的索引是什么意思？

country_id和country_id_2的基数都非常低，实际上这两个索引的基数是相同的-所以使用基于2列的索引没有任何好处。这意味着使用expired<>0的记录非常少。

但是为什么过期索引的基数也是21呢？

以下命令的输出是什么：

SELECT 'expired' AS fld
, COUNT(*) AS distinct_values
, AVG(n) AS rows
FROM 
(SELECT expired AS v
 , COUNT(*) AS n 
 FROM slugs 
 GROUP BY expired) ilv1
UNION
SELECT 'country_id',
, COUNT(*) AS distinct_values
, AVG(n) AS rows
FROM 
(SELECT country_id AS v
 , COUNT(*) AS n 
 FROM slugs 
 GROUP BY country_id) ilv2
    SELECT 'expired:country_id',
, COUNT(*) AS distinct_values
, AVG(n) AS rows
FROM 
(SELECT CONCAT(expired, country_id) AS v
 , COUNT(*) AS n 
 FROM slugs 
 GROUP BY CONCAT(expired, country_id)) ilv3;