mysql 优化 range_MySQL · 答疑解惑 · MySQL 优化器 range 的代价计算-阿里云开发者社区...

本文我们从一个索引选择的问题出发，来研究一下 MySQL 中 range 代价的计算过程，进而分析这种计算过程中存在的问题。

问题现象

第一种情况：situation_unique_key_id

mysql> show create table cpa_order\G

*************************** 1. row ***************************

Table: cpa_order

Create Table: CREATE TABLE `cpa_order` (

`cpa_order_id` bigint(20) unsigned NOT NULL,

...

`settle_date` date DEFAULT NULL COMMENT,

`id` bigint(20) NOT NULL,

PRIMARY KEY (`cpa_order_id`),

UNIQUE KEY `id` (`id`),

KEY `cpao_settle_date_id` (`settle_date`,`id`),

) ENGINE=InnoDB DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: cpa_order

type: ref

possible_keys: id,cpao_settle_date_id

key: cpao_settle_date_id

key_len: 4

ref: const

rows: 7

Extra: Using index condition

1 row in set (0.00 sec)

SQL 语句执行过程可以看出，当 id 为 unique key 的时候，key_len= 4, 不难发现联合索引只使用了字段 cpao_settle_date_id ，而 id 并没有使用；

第二种情况：situation_without_key_id

mysql> alter table cpa_order drop index id;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G (我们称之为 situation_without_key_id)

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: cpa_order

type: range

possible_keys: cpao_settle_date_id

key: cpao_settle_date_id

key_len: 12

ref: NULL

rows: 3

Extra: Using index condition

1 row in set (0.00 sec)

第三种情况: situation_plain_key_id

mysql> explain select * from cpa_order where settle_date='2015-11-05' and id > 15 \G (我们称之为 situation_plain_key_id)

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: cpa_order

type: range

possible_keys: cpao_settle_date_id,id

key: cpao_settle_date_id

key_