1.possible-keys分析
- possible-keys；显示可能应用在这张表上的索引，一个或者多个。
查询设计到的字段上若存在索引，则该索引被列出，但是不一定被实际使用
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,则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中(key的值不一定是possible-keys的子集)
- 复合索引-->覆盖索引:
- 复合索引：create index index_co1_co2 on t1(co1,co2);
- 覆盖索引：就是查询的字段和建立的复合索引的字段一一对应，
mysql> explain SELECT id,name from t1; -- 不创建索引
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 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.执行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) -- 创建复合索引
