mysql 列的基数_mysql – 涉及范围时,索引中的基数列更高？

首先,让我们尝试FORCE INDEX选择ef或fe.时间太短,无法清楚地了解哪个更快,但是`EXPLAIN显示了不同之处：

首先强制文件时间范围. (注意：WHERE中的顺序没有影响.)

mysql> EXPLAIN SELECT COUNT(*), AVG(fsize)

FROM files FORCE INDEX(fe)

WHERE ext = 'gif' AND filetime >= '2015-01-01'

AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+

| 1 | SIMPLE | files | range | fe | fe | 14 | NULL | 16684 | Using index condition |

+----+-------------+-------+-------+---------------+------+---------+------+-------+-----------------------+

首先强制低基数ext：

mysql> EXPLAIN SELECT COUNT(*), AVG(fsize)

FROM files FORCE INDEX(ef)

WHERE ext = 'gif' AND filetime >= '2015-01-01'

AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

| 1 | SIMPLE | files | range | ef | ef | 14 | NULL | 538 | Using index condition |

+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

显然,行表示ef更好.但是,让我们检查优化器跟踪.产量相当笨重;我只展示有趣的部分.不需要FORCE;跟踪将显示两个选项然后选择更好.

...

"potential_range_indices": [

...

{

"index": "fe",

"usable": true,

"key_parts": [

"filetime",

"ext",

"did",

"filename"

]

},

{

"index": "ef",

"usable": true,

"key_parts": [

"ext",

"filetime",

"did",

"filename"

]

}

],

…

"analyzing_range_alternatives": {

"range_scan_alternatives": [

{

"index": "fe",

"ranges": [

"2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"

],

"index_dives_for_eq_ranges": true,

"rowid_ordered": false,

"using_mrr": false,

"index_only": false,

"rows": 16684,

"cost": 20022,

"chosen": true

},

{

"index": "ef",

"ranges": [

"gif <= ext <= gif AND 2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"

],

"index_dives_for_eq_ranges": true,

"rowid_ordered": false,

"using_mrr": false,

"index_only": false,

"rows": 538,

"cost": 646.61,

"chosen": true

}

],

…

"attached_conditions_computation": [

{

"access_type_changed": {

"table": "`files`",

"index": "ef",

"old_type": "ref",

"new_type": "range",

"cause": "uses_more_keyparts"

}

}

使用fe(范围列第一),可以使用范围,但它估计扫描16684行捕获ext =’gif’.

使用ef(低基数ext优先),它可以使用索引的两列并在BTree中更有效地向下钻取.然后它发现估计538行,所有这些行对查询都很有用 – 不需要进一步过滤.

结论：

> INDEX(filetime,ext)仅使用第一列.

> INDEX(ext,filetime)使用了两列.

>无论基数如何,都将= test中涉及的列放在索引中.

>查询计划不会超出第一个“范围”列.

>“基数”与复合索引和此类查询无关.

(“使用索引条件”表示存储引擎(InnoDB)将使用超出用于过滤的索引的列.“)