11 索引优化

创建表

/*
Navicat MySQL Data Transfer

Source Server         : CentOS6.7--Clone
Source Server Version : 50554
Source Host           : 192.168.1.179:3306
Source Database       : db01

Target Server Type    : MYSQL
Target Server Version : 50554
File Encoding         : 65001

Date: 2017-12-18 11:32:01
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `staffs`
-- ----------------------------
DROP TABLE IF EXISTS `staffs`;
CREATE TABLE `staffs` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL COMMENT '姓名',
  `age` int(3) NOT NULL COMMENT '年龄',
  `pos` varchar(20) NOT NULL COMMENT '职位',
  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_staffs_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of staffs
-- ----------------------------
INSERT INTO `staffs` VALUES ('1', '张三', '22', '经理', '2017-12-18 11:25:18');
INSERT INTO `staffs` VALUES ('2', '李四', '23', '员工', '2017-12-18 11:25:48');
INSERT INTO `staffs` VALUES ('3', '王二', '23', '员工', '2017-12-18 11:25:49');

索引失效的情况

如果索引多列,查询时最左边的列不能缺失

#可以使用索引的三种情况
mysql> explain select * from staffs where name = "李四";
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 | Using where |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name = "李四" and age = 23;
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | const,const |    1 | Using where |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name = "李四" and age = 23 and pos = "员工";
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-------------+
| id | select_type | table  | type | possible_keys         | key                   | key_len | ref               | rows | Extra       |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | staffs | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 | Using where |
+----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-------------+
1 row in set (0.00 sec)

#下面SQL用不到索引
mysql> explain select * from staffs where age = 23 and pos = "员工";
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where pos = "员工";
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)