索引优化的目的主要是让索引不失效,走正确的索引,今天主要分享的是最近整理的索引八大法则上篇,看完的话面试考索引应该没问题了~
下面主要以实验来帮助大家理解~
一、最佳左前缀法则
1. 定义
在创建了多列索引的情况下,查询从索引的最左前列开始且不能跳过索引中的列。
最佳左前缀法则就是说如果创建了多个索引,在使用索引时要按照创建索引的顺序来使用,不能缺少或跳过,当然如果只使用最左边的索引列,也就是第一个索引是可以的。
2. 环境准备
- DROP TABLE IF EXISTS `tb_emp`;
- CREATE TABLE `tb_emp` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `name` varchar(20) NOT NULL,
- `age` int(11) NOT NULL,
- gender varchar(10) NOT NULL,
- email varchar(20),
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Tom', '22','male','1@qq.com');
- INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Mary', '21','female','2@qq.com');
- INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Jack', '27','male','3@qq.com');
- INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Rose', '23','female','4@qq.com');
3. 创建组合索引
- create index idx_all on tb_emp(name,age,gender);
- show index from tb_emp;
这里用火车头代表name,车厢代表age,车尾代表gender。
4. 只有火车头
说明:
5. 只有车厢
说明:没使用火车头(name),直接用车厢,导致走全表扫描(type=ALL)
6. 火车头加车厢、火车头加车尾
说明:
火车头加车厢、火车头加车尾,虽然都是type=ref,但是观察key_len和ref两项,并对比只有火车头中的结果,可得出在使用火车头(name)和车尾(gender)时,只使用了部分索引也就是火车头(name)的索引。
通俗理解:火车头单独跑没问题,火车头与直接相连的车厢一起跑也没问题,但是火车头与车尾,如果中间没有车厢,只能火车头自己跑。
7. 火车头加车厢加车尾
说明:火车头加车厢加车尾,三者串联,就变成了奔跑的小火车。type=ref,key_len=128,ref=const,const,const。
二、索引列不做计算
在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效从而转向全表扫描。
1. 函数计算
说明:这里使用了函数计算,type=ALL,导致索引失效。
2. 隐式类型转换
说明:这里'123'是字符串,而123是数字,发生了隐式类型转换,导致全表扫描(type=ALL)
三、范围右边索引列全失效
存储引擎不能使用索引中范围右边的列,也就是说范围右边的索引列会失效。
对以上4个SQL进行分析:
结论:范围右边的索引列失效。
四、尽量使用覆盖索引
1. 覆盖索引定义
如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点:
覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引不存储索引列的值,所以mysql只能用B-tree索引做覆盖索引。
当发起一个索引覆盖查询时,在explain的extra列可以看到using index的信息
2. 对比是否使用覆盖索引好处
尽量使用覆盖索引(查询列和索引列尽量一致,通俗说就是对A、B列创建了索引,然后查询中也使用A、B列),减少select *的使用。
- mysql> explain select * from tb_emp where name='Jack' and age=27 and gender='male';
- mysql> explain select name,age,gender from tb_emp where name='Jack' and age=27 and gender='male';
说明:对比两个sql,第一个使用select *,第二个使用覆盖索引(查询列与条件列对应),可看到Extra从Null变成了Using index,提高检索效率。
我们在用ajax请求数据时,可能会遇到一次点击多次触发的可能。 (比如说:ajax ...
display-namedefaultroot/display-name servlet servlet-namedebugjsp/servlet-n...
在Sun的Java JDK 1.40版本中,Java自带了支持正则表达式的包,本文就抛砖引玉地...
在用java进行web业务开发的时候,对于页面上接收到的参数,除了极少数是步可预知...
本文重点给大家介绍AjaxFileUpload+Struts2实现多文件上传功能,具体实现代码大...
无论是Windows还是macOS,都少不了各种小工具的加持。作为操作系统中必不可少的...
MySQL 运维 - 从零开始学习 一、数据库类型 ? 常见的数据库类型 二、数据库管理...
SQL可以独立完成数据库生命周期中的全部活动,包括定义关系模式、录入数据、建立...
本文实例为大家分享了JS实现纸牌发牌动画的具体代码,供大家参考,具体内容如下 ...
信号章节 -- 信号章节总体概要 信号基本概念 信号是异步事件,发送信号的线程可...