# 环境
mysql> use test
Database changed
mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.37-log |
+------------+
1 row in set (0.04 sec)

# 表结构
mysql> show create table tb\G;
*************************** 1. row ***************************
       Table: tb
Create Table: CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL COMMENT 'aaa',
  `c2` varchar(20) DEFAULT NULL,
  `t1` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=133012 DEFAULT CHARSET=utf8 COMMENT='aa'
1 row in set (0.02 sec)

# 数据分布
mysql> SELECT age,COUNT(*) FROM tb GROUP BY age WITH ROLLUP;
+------+----------+
| age  | COUNT(*) |
+------+----------+
|   -1 |   129484 |
|    2 |      100 |
| NULL |   129584 |
+------+----------+
3 rows in set (0.11 sec)


# select 语句
# SELECT id,age FROM tb WHERE age='-1' AND id>324 ORDER BY id LIMIT 100
##################################################### 正常索引:idx_ageidc2(age,id,c2)
mysql> ALTER TABLE tb ADD INDEX idx_ageidc2(age,id,c2);
Query OK, 0 rows affected (0.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT id,age FROM tb WHERE age='-1' AND id>324 ORDER BY id LIMIT 100\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb
         type: ref
possible_keys: PRIMARY,idx_ageidc2
          key: idx_ageidc2
      key_len: 5
          ref: const
         rows: 64912
        Extra: Using where; Using index
1 row in set (0.00 sec)


##################################################### 额外索引:idx_agec2(age,c2)，导致explain走主键，而不走index
mysql> ALTER TABLE tb ADD INDEX idx_agec2(age,c2);
Query OK, 0 rows affected (0.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT id,age FROM tb WHERE age='-1' AND id>324 ORDER BY id LIMIT 100\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb
         type: range
possible_keys: PRIMARY,idx_ageidc2,idx_agec2
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 64912
        Extra: Using where
1 row in set (0.00 sec)