1.possible-keys分析

• possible-keys；顯示可能應(yīng)用在這張表上的索引，一個或者多個。
  查詢設(shè)計到的字段上若存在索引，則該索引被列出，但是不一定被實際使用

mysql> EXPLAIN SELECT t3.id from t3 where  t3.age in (10.20) and t3.id in (1,2,3);
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | t3    | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

2.key分析

• key:實際使用的索引，如果為null,則沒有使用索引
  查詢中若使用了覆蓋索引，則該索引僅出現(xiàn)在key列表中(key的值不一定是possible-keys的子集)
• 復(fù)合索引-->覆蓋索引:
• 復(fù)合索引：create index index_co1_co2 on t1(co1,co2);
• 覆蓋索引：就是查詢的字段和建立的復(fù)合索引的字段一一對應(yīng)，

mysql> explain SELECT id,name from t1; -- 不創(chuàng)建索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

mysql> explain SELECT id,name from t1;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | index | NULL          | index_id_name | 37      | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+

3.執(zhí)行SQL文件

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t1
-- ----------------------------
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t1
-- ----------------------------
INSERT INTO `t1` VALUES ('1', 'downeyjr_1');
INSERT INTO `t1` VALUES ('2', 'downeyjr_2');
INSERT INTO `t1` VALUES ('3', 'downeyjr_3');

-- ----------------------------
-- Table structure for t2
-- ----------------------------
DROP TABLE IF EXISTS `t2`;
CREATE TABLE `t2` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t2
-- ----------------------------
INSERT INTO `t2` VALUES ('1', 'downeyjr_1');

-- ----------------------------
-- Table structure for t3
-- ----------------------------
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(5) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t3
-- ----------------------------
INSERT INTO `t3` VALUES ('1', 'downeyjr_1', '10');
INSERT INTO `t3` VALUES ('2', 'downeyjr_1', '20');
INSERT INTO `t3` VALUES ('3', 'downeyjr_1', '30');

CREATE INDEX index_id_name on t1(id,name) -- 創(chuàng)建復(fù)合索引