mysql> explain extended select f_some_id from d_ec_some1.t_tbl_test_time_08 where f_qiye_id=5077665 and f_type=9 and f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53' order by f_some_id limit 300; +----+-------------+-----------------------+-------+----------------+---------------+---------+------+-------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+-------+----------------+---------------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | t_tbl_test_time_08 | index | f_contact_time | some_qiye_type | 12 | NULL | 16032 | 2248.49 | Using where | +----+-------------+-----------------------+-------+----------------+---------------+---------+------+-------+----------+-------------+ 1 row in set -- 指定一个索引 mysql> explain extended select f_some_id from d_ec_some1.t_tbl_test_time_08 use index(f_contact_time) where f_qiye_id=5077665 and f_type=9 and f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53' order by f_some_id limit 300; +----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+----------+---------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+----------+---------------------------------------------------------------+ | 1 | SIMPLE | t_tbl_test_time_08 | range | f_contact_time | f_contact_time | 5 | NULL | 360478 | 100 | Using index condition; Using where; Using MRR; Using filesort | +----+-------------+-----------------------+-------+----------------+----------------+---------+------+--------+----------+---------------------------------------------------------------+ 1 row in set
第一个explain结果里面，type=index，表示 full index scan。注意这里看到的 index 不代表“查询用到索引了”。全索引扫描比全表扫描并不好到拿去，甚至更慢（因为随机IO）。是否用到正确的索引要看key那一列: some_qiye_type 索引定义是 (f_some_id,f_qiye_id,f_type)，从key_len=12看得出这三列都用上了（12=4+5+3）。但实际这个执行计划需要200多秒。
第二个explain，在sql里面指定了 use index(f_contact_time)，依据是where条件里面f_contact_time的范围固定4s，猜想数据量不会很大，过滤效果会比较好。
这里的filtered部分，表示执行后的时间？
Extra列:
Using index condition: 用到了索引下推特性。Using where是回表拿数据。关于ICP见文后参考。
Using MRR: 用到了 Multi-Range Read 优化特性。mysql在通过二级索引范围查找的时候，得到的记录在物理上是无序的，为了减少去获取数据的随机IO，它会在内存缓冲区里面先根据rowid快速排序，然后顺序IO去拉取数据。（这个缓冲区大小参数由 read_rnd_buffer_size 控制）
mysql> set profiling=1; mysql> select f_some_id from d_ec_some1.t_tbl_test_time_08 where f_qiye_id=5077665 and f_type=9 and f_contact_time > '2017-10-17 14:23:49' and f_contact_time < '2017-10-17 14:23:53' order by f_some_id limit 300;
另外这个表上只有一个唯一索引，而且该唯一索引有字段允许null，所以没有主键。 加一个自增主键 f_id bigint unsigned not null 修改f_some_id字段为 f_some_id bigint unsigned NOT NULL 修改f_qiye_id字段为 f_qiye_id int unsigned NOT NULL 修改字段f_type字段为 tinyint NOT NULL 总之一句话：所有（作为索引的）字段，都定义为NOT NULL，f_some_id全部定义为bigint。最终表结构：
1 2 3 4 5 6 7 8 9 10
CREATE TABLE `t_tbl_test_time_16` ( `f_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `f_some_id` bigint(20) unsigned NOT NULL, `f_qiye_id` int(11) unsigned NOT NULL DEFAULT '0', `f_type` tinyint(3) NOT NULL DEFAULT '0' COMMENT '有效联系类型 1: QQ联系，2:拨打电话，3:发送邮件，4:发送短信，5:添加跟进记录，6:拜访客户，7:EC联系，8:更新客户阶段', `f_contact_time` timestamp NOT NULL DEFAULT '1970-01-01 16:00:01', PRIMARY KEY(f_id), UNIQUE KEY `some_qiye_type` (`f_some_id`,`f_qiye_id`,`f_type`) USING BTREE, KEY `idx_qiye_time` (`f_qiye_id`,`f_contact_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;