[MySQL高级](四) 索引优化之索引失效详解数据库
索引的建立是为了让我们更加高效快速的查询出结果,但是,要想充分利用起索引,我们首先要解决的最大问题就是要避免索引失效,下面我们来一起通过实例来探讨造成索引失效的情况,并通过优化SQL查询语句来避免索引失效。
➤ 准备工作:、
CREATE TABLE `staffs` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 主键id, `name` varchar(24) NOT NULL DEFAULT COMMENT 姓名, `age` int(11) NOT NULL DEFAULT 0 COMMENT 年龄, `pos` varchar(20) NOT NULL COMMENT 职位, `add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入职时间, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;插入基础数据
INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES (1, 王洪玉, 25, 总经理, 2018-05-22 09:45:44); INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES (2, July, 25, 实习生, 2018-05-22 09:45:58); INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES (3, 李四, 20, 实习生, 2018-05-22 09:46:04); INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES (4, 王玉, 21, 老板娘, 2018-05-22 09:46:17); INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES (5, 王五, 22, 服务员, 2018-05-22 09:46:26); INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES (6, 赵六, 80, 传菜生, 2018-05-22 09:46:45);
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos (NAME, age, pos);
SHOW INDEX FROM staffs
【知识补充】:
MYSQL EXPLAIN解析一 EXTRA中的USING INDEX,USING WHERE,USING INDEX CONDITION
using index 和using where只要使用了索引我们基本都能经常看到,而using index condition则是在mysql5.6后新加的新特性
全值匹配指的是我要查询的语句的字段和顺序恰好和建立的索引的字段和顺序一致,否则索引失效。
☀ 正确的使用方式
EXPLAIN SELECT * FROM staffs WHERE NAME = July;
EXPLAIN SELECT * FROM staffs WHERE NAME = July AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = July AND age = 25 AND pos = 实习生;
☁ 索引失效
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 实习生;
EXPLAIN SELECT * FROM staffs WHERE pos = 实习生;
✈ 总结:由上面的结果我们可以看出,当索引是按照name,age,pos顺序建立的时候,如果查询条件不是以name开头,就会导致索引失效。总结一句话就是:带头大哥不能死!
2.2 最佳左前缀法则如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。
EXPLAIN SELECT * FROM staffs WHERE NAME = July AND pos = 实习生;
如上图所示,如果是匹配了两个索引字段,那么key_len应该至少大于74,而且ref应该是两个const。由此可见,该查询语句只使用了name字段的索引,后面的索引就失效了。
✈ 总结:由上面的结果我们可以看出,当索引是按照name,age,pos顺序建立的时候,如果查询条件是以name开头,但是没有按顺序,就会导致后面的索引失效。总结一句话就是:中间兄弟不能断!
2.3 不在索引列上做任何操作这里的任何操作包括计算、函数、(自动or手动)类型转换,会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM staffs WHERE NAME = July;
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = July;
EXPLAIN SELECT * FROM staffs WHERE NAME = July AND age = 25 AND pos = 实习生;
EXPLAIN SELECT * FROM staffs WHERE NAME = July AND age 25 AND pos = 实习生;
✈ 总结:当我们使用age 25这种范围查找的时候,type变为了range,并且key_len等于78,说明已经使用了name,和age的索引,但是,age的索引变为了范围排序,而并不是精确查找,导致后面的pos索引失效。所以总结就是:范围之后全失效!
2.5 尽量使用覆盖索引尽量使用索引的查询即索引列和查询列一致,减少select *
EXPLAIN SELECT * FROM staffs WHERE NAME = July AND age = 25 AND pos = 实习生;
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = July AND age = 25 AND pos = 实习生;
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = July AND age 25 AND pos = 实习生;
EXPLAIN SELECT * FROM staffs WHERE NAME = July;
EXPLAIN SELECT * FROM staffs WHERE name != July;
EXPLAIN SELECT * FROM staffs WHERE name July;
EXPLAIN SELECT * FROM staffs WHERE name is null;
EXPLAIN SELECT * FROM staffs WHERE name is not null;
EXPLAIN SELECT * FROM staffs WHERE name like %July%
EXPLAIN SELECT * FROM staffs WHERE name like %July
EXPLAIN SELECT * FROM staffs WHERE name like July%
那么,我们的需求就是要模糊查询带 July 的条件,你在后面加%匹配符明显不符合我的需求,那么有没有一种方法解决like’%字符串%’时索引不被使用的方法呢?
EXPLAIN SELECT id,name,age FROM staffs WHERE name like %July%
这种方式就是通过覆盖索引方式解决索引丢失问题,但是问题又来了,如果我要查询的字段多呢,如下SQL,就不能使用覆盖索引了,所以说还是存在局限性。
EXPLAIN SELECT id,name,age,add_time FROM staffs WHERE name like %July%
我们在数据库中添加一条数据,SQL如下:
INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES (7, 200, 0, 假人, 2018-05-22 15:02:55);
需要注意的是,我们的name字段类型为varchar类型,我们插入了一个名字为“200”的假人。
我们通过下面两个SQL进行查询,都能够得到正确的数据,这是因为mysql自动给200做了类型转换。
SELECT * FROM staffs WHERE name = 200; SELECT * FROM staffs WHERE name = 200;
我们来看一下这两个SQL的执行情况
EXPLAIN SELECT * FROM staffs WHERE name = 200;
EXPLAIN SELECT * FROM staffs WHERE name = 200;
从上图所示可以看出,第二条的SQL语句索引失效,全表扫描。
EXPLAIN SELECT * FROM staffs WHERE name = July or age = 25
创建表并创建索引
CREATE TABLE `test03` ( `id` int(11) NOT NULL, `c1` varchar(255) DEFAULT NULL, `c2` varchar(255) DEFAULT NULL, `c3` varchar(255) DEFAULT NULL, `c4` varchar(255) DEFAULT NULL, `c5` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; create index idx_test03_c1234 on test03(c1,c2,c3,c4);
【问题】我们创建了复合索引idx_test03_c1234,根据以下SQL分析索引的使用情况
mysql查询优化器自动优化![这里写图片描述](http://blog.ytso.com/zb_users/plugin/LazyLoad/usr/loading.gif)
![这里写图片描述](http://blog.ytso.com/zb_users/plugin/LazyLoad/usr/loading.gif)
![这里写图片描述](http://blog.ytso.com/zb_users/plugin/LazyLoad/usr/loading.gif)
![这里写图片描述](http://blog.ytso.com/zb_users/plugin/LazyLoad/usr/loading.gif)
![这里写图片描述](http://blog.ytso.com/zb_users/plugin/LazyLoad/usr/loading.gif)
![这里写图片描述](http://blog.ytso.com/zb_users/plugin/LazyLoad/usr/loading.gif)
![这里写图片描述](http://blog.ytso.com/zb_users/plugin/LazyLoad/usr/loading.gif)
全值匹配我最爱,最左前缀要遵守; 带头大哥不能死,中间兄弟不能断; 索引列上少计算,范围之后全失效; LIKE百分写最右,覆盖索引不写星; 不能空值还有OR,索引失效要少用; VAR引号不可丢,SQL高级也不难;
3922.html
mysql相关文章
- MySQL Status Handler_read_rnd 数据库状态作用意思及如何正确
- 记录MySQL 数据库查询:一步一步演示!(怎么获取mysql数据库)
- MySQL:优化大数据处理性能(mysql大数据处理优化)
- 查看MySQL用户名的方法(查看mysql的用户名)
- 在Linux系统中卸载MySQL数据库(linux下卸载mysql)
- 表操作MySQL中如何删除表(mysql删除表数据库)
- 存储MySQL数据库:键值存储的优势(mysql数据库键值)
- MySQL事件定时,让数据库管理更智能(mysql事件定时)
- 管理简易 MySQL 菜单管理系统实现(mysql菜单)
- MySQL存储过程:实战练习分享(mysql存储过程练习)
- MySQL数据库中如何存储表情符号?(mysql表情符号)
- 维护MySQL数据库状态维护:优化稳定运行(mysql 数据库状态)
- 优化MySQL数据库,轻松节省空间(mysql数据库整理)
- Oracle数据迁移至MySQL:技术与实践(oracle迁移到mysql)
- MySQL中事务的重要性及作用(mysql中事务作用)
- 使用C语言实现MySQL事务提交(c mysql事务提交)
- MySQL插件让Bash更强大(bash插件mysql)
- 数据库用cmd建立MySQL数据库的指南(cmd建立mysql)
- cmd终端连接MySQL一步步操作指南(cmd到mysql)
- 失去关联MySQL外码缺失的后果(mysql不加外码)
- MySQL日志存储位置及作用详解(mysql下日志的目录)
- MySQL数据库遭遇两个冲突问题,如何解决(mysql下了两个冲突)
- MySQL中的不等于操作符1还是不是1(mysql 不等则是1)
- MySQL数据显示不用科学计数法,避免数据误差(mysql不用科学计数法)