数据准备
CREATE TABLE staffs(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT '年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` TIMESTAMP NOT NULL DEFAULT current_timestamp COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES ('zs', 22, 'manager', now());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES ('july', 23, 'dev', now());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES ('2000', 23, 'dev', now());
SELECT * from staffs;
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(`name`, `age`, `pos`);
索引失效
- 1.全值匹配我最爱
- 2.最佳左前缀法则:如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。
- 3.不在索引列上做任何操作(计算、函数、(自动或手动)类型转换),会导致索引失效而转向全表扫描。
- 4.存储引擎不能使用索引中范围条件右边的列
- 5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
- 6.mysql在使用不等于(!=或者 < >)的时候无法使用索引会导致全表扫描。
- 7.is null, is not null 也无法使用索引
- 8.like以通配符开头('%abc...')mysql索引失效,变成全表扫描
- 9.字符串不加单引号索引失效
- 10.少用or,用它来连接时索引会失效。
mysql> EXPLAIN SELECT * FROM staffs where name='july';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs where name='july' AND age=25;
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM staffs where name='july' AND age=25 AND pos='dev';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
只用索引的最后两列
mysql> EXPLAIN SELECT * FROM staffs where age=25 AND pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
没有使用索引
只使用索引的开头和结尾列
mysql> EXPLAIN SELECT * FROM staffs where name='july' AND pos='dev';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
用到了索引,但是只用到了name列的索引, pos列的索引没有用到