MySQL-索引优化(一)

数据准备

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列的索引没有用到

最佳左前缀法则口诀:带头大哥不能死,中间兄弟不能断

  • 2
    点赞
  • 16
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值