Mysql索引问题

Question

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

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

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

表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

解释的输出

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 列的索引没有任何好处。这意味着过期 <>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;