MySql进阶

Mysql

一、MySql逻辑架构

1. MySql逻辑架构介绍

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1. 连接层
   最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。如，navicat

2. 服务层
   第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3. 引擎层
   存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

4. 存储层
   数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

2.数据库引擎

通过show engines可以查询数据库支持的所有引擎， 我们常用的引擎有MyISAM和InnoDB。下面我们主要讨论这两个引擎。

查询结果中
Engine参数指存储引擎名称:
Support参数说明MySQI是否支持该类型引擎;
Comment参数表示对该引擎的评论:
Transaction参数表示是否支持事务处理:
XA参数表示是否分布式交易处理的XA规范:
Savepoints参数表示是否支持保存点，以方便事务的回滚操作

那么怎么看mysql当前默认的存储引擎:
mysql> show variables like ‘%storage_engine%’;

上面显示，默认存储引擎是InnoDB，当前存储引擎也是InnoDB。

那么这两个存储引擎有什么区别呢？
现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎。
黑色背景为重点

扩展：
那么阿里用什么呢，在几年前，阿里的确也用Mysql。

Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。

该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好,

阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。
AliSql+AliRedis

3.Sql执行顺序

笛卡尔积大家都学过，复习一下，两张表，T1 5条记录，T2 8条记录，执行select * from T1，T2，一共有多少条，很简单5*8＝40条。

这么多条数据，肯定可以把数据都查出来，但是数据太乱了，因此需要where过滤，那么有没有一条公式能让我们写出标准的sql语句呢。

上图就是写Sql语句的规范，按照这个规范可以写出好的sql语句。

不知道大家有没有思考过一个问题，我们写sql代码的时候，先select … 然后在from…，那么计算机执行这条语句的时候也是这样的顺序吗？

其实机读的时候的顺序是从from开始，因为计算机需要知道你要操作哪个表。
整理的鱼刺图如下。

二、事务

1. 事务概览

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功，则事物被顺利执行。

三大关键点:

1. 在MySQL中只有使用了Innodb 数据库引擎的数据库或表才支持事务。
2. 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
3. 事务用来管理insert,update,delete语句，也即写操作才用到事务

事务控制
在MySQL命令行的默认设置下，事务都是自动提交的既后面自动加一条COMMIT，即执行SQL语句后就会马上执行COMMIT操作。因此要显式地开启一一个事务务须使用命令BEGIN或START，TRANSACTION或者执行命令SET AUTOCOMMIT=O，用来禁止使用当前会话的自动提交。

MYSQL事务处理主要有两种方法:

1. 用BEGIN, ROLLBACK, COMMIT来实现
   （1）BEGIN或START TRANSACTION开始一个事务
   （2）ROLLBACK事务回滚
   （3）COMMIT事务确认
2. 直接用SET来改变MySQL的自动提交模式:
   （1）SET AUTOCOMMIT=O禁止自动提交
   （2）SET AUTOCOMMIT=1开启自动提交

2. 数据一致性

脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

Mysql支持4种事务隔离级别。
Mysql默认命事务隔离级别为: REPEATABLE READ

查看当前的隔离级别两种方法:
每启动一个mysql程序，就会获得一一个单独的数据库连接.每个数据库连接都有一
个全局变量@@tx_isolation，表示当前的事务隔离级别。

• 查看当前的隔离级别: SELECT @ @tx isolation;
• 查看当前数据库的事务隔离级别: show variables like ‘tx_isolation’;

SELECT @ @tx isolation;

show variables like ‘tx_isolation’;

3. 代码演示数据一致性

SET SESSION TRANSACTION ISOL ATION LEVEL READ UNCOMMITTED; --未提交读
SET SESSION TRANSACTION ISOL ATION LEVEL READ COMMITTED; --已提交读
SET SESSION TRANSACTION ISOL ATION LEVEL REPEATABLE READ; --可重复读
SET SESSION TRANSACTION ISOL ATION LEVEL SERIALIZABLE;  --可序列号

未提交读- READ UNCOMMITED

首先我们打开两个Sql窗口，分别将事务级别设置成READ UNCOMMITED

接着我们在左窗口插入一条数据(注意，没有COMMIT)，由于READ UNCOMMITED的特性，右窗口也可以得到数据，所以右窗口拿着数据去办事了，在右窗口得到数据之后，左窗口rollback(此时仍未提交)，rollback之后，左窗口的数据已经修改，但是右窗口却已经拿着之前错误的数据办事去了。

举个例子，小明抄班长考试题，班长写一个，小明抄一个，直到小明把所有的题都抄完了，交卷了，这时候班长发现好多题都做错了，要逐个修改答案。最后的结果就是，班长100分，小明挂科。

对于这种读到错误的数据，叫做脏读。

由于左边窗口回滚了，所以刚刚插入的数据也没有了，此时右边窗口在select，会发现和上次查询的结果不一样，这就叫做不可重复读。

举个例子，小明抄班长考试题，小明问班长第一题选什么，班长说A，过了一会班长修改了答案，小明此时闲的无聊问班长，为什么第一题选A啊，班长说，我选的B啊，没选A，此时小明蒙了，不知道这道题到底选A还是选B。

对于这种同一事务，多次查询同一资源但是结果却不同的现象，叫做不可重复读。

以提交读- READ COMMITED
首先我们打开两个Sql窗口，分别将事务级别设置成READ COMMITED

我们可以看到，左窗口插入了一条数据，但是没提交。
右窗口查询tx表，什么也没查到。
区别于 READ UNCOMMITED， 我们发现READ COMMITED没有有脏读现象，所以READ COMMITED避免了脏读。

当左窗口COMMIT提交之后，右窗口就可以查到数据了，既事务提交之后，其他会话才能访问此次事务的数据。
但是我们也发现，右窗口在同一事务对同一资源查询却查询到了不同的结果，因此存在不可重读的现象。

可重复读- READ COMMITED
首先我们打开两个Sql窗口，分别将事务级别设置成REPEATABLE READ

右窗口首先查询数据，发现表中存在一个数据，‘1’， 左窗口在一个事务中新增一条数据，‘2’，此时提交事务，右窗口再次查询数据，发现还是1。

在同一事务中，查询同一资源多次，得到的结果是一样的，因此REPEATABLE READ可以避免不可重复读。只有当前事务提交之后，再次查询，才能查到更新后的数据。

但也正是这种现象，导致了左窗口在事务提交之前明明插入了一条数据，但是右窗口却查不到，这就仿佛产生了幻觉，这种现象叫做幻读。

脏读也可以避免，就不演示了。

可序列化-SERIALIZABLE;
首先我们打开两个Sql窗口，分别将事务级别设置成SERIALIZABLE。

哎，世界上最难过的事情就是解决了旧的问题又会产生新的问题。

左窗口插入了一条数据，‘3’，此时还没有提交事务，这时右窗口查询，发现处于阻塞等待状态。这时候大家可能猜到为什么了。那就是冰糖胡芦，吃了第一个才能吃第二个。

当左窗口COMMIT之后，右窗口才查出数据。很明显，这样的机制使得上述三种麻烦都不会产生，但是对于并发效率是极其低的。

三、join

1. join概览

Join分为左连接，右连接，内连接，A表独有，B表独有，全连接，AB各有。

有点糊涂？没关系，我们用例子来介绍Join。

参考Sql语句，所用引擎都为INNODB。

CREATE TABLE `tbl_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `locAdd` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `tbl_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
 
 
INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
 
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
 
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
 
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
 
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);

部门表，id，部门名，楼层

员工表，id， name， 部门id

分别对这些表进行插入操作。

查询部门表

查询员工表

简单解释下插入的表

在部门表中，一共有4个部门，ID为5的部门没有被引用，因为他是一个特殊的部门，就像老板的部门是8888一样。

在员工表中，ID1-7为公司正式员工，ID为8的员工为试用员工，故分配临时部门。

在演示之前，我们先对两个表进行笛卡尔积查询，发现
查询部分

我们发现40条记录，结果正常。

现在我们开始演示

---

2.内连接

问题：求出公司所有正式员工的信息。
提示：部门表没有被正式员工引用的部门和实习员工都不应该显示出来，故求A∩B。
如图所示

---

3.左连接

问题：展示出公司所有员工信息。
提示：员工表除了正式员工外，试用员工也应该查询出来，如果没有对应部门，那部门信息应该留空。故应该取员工表独有部分，和A∩B，故就是求A表所有信息。

如图所示：

4.右连接

与左连接相反，不做解释。

---

5.A表独有

问题：求出没有被分配正式部门的员工
提示：就是试用员工，他只被分配了一个临时部门，实际上部门表并没有他引用的部门。
如图所示：

---

6.B表独有

与A表独有相反，不做解释。

---

7.全连接

问题：求出AB两表所有信息。
提示：特殊部门和试用员工也要查出来。
如图所示

咦？怎么报错了，看起来和图上的语法一样啊。是不是哪里写错了？其实不是，按照SQL99的规范，Mysql不支持FULL OUTET的关键字，那是不是就没办法了？

解决方案如图所示

我们发现实际上，全连接就是A的独有+B的独有，但是我们也知道，A和B连个独有叠加在一起，相交的部分会重复所以要对它们去重。

参考上图，第一行是左连接，也就是A的独有，第三行是右连接是B的独有，union是合并加去重。

---

8.AB各有

问题：找出实习员工以及特殊部门。
提示：就是找出AB独有去掉AB共有，代码实现原理和全连接相反。
如图所示

四 、PL/SQL - 函数和存储过程

1. 函数和存储过程简介

不知道大家的数据库里面有没有千万条数据。如果你想在你的数据库里插入千万条数据怎么作？一条一条插？还是用JAVA写个循环？

那日常中我们非常常用的一个需求，比如更新A表之后，B、C两个表的也要随着A表更新而更新那怎么做？

例如同一插入操作，我希望在1、3、5 周插入A表，2、4、6 周插入B表，我又该怎么做。

有人可能会问了，你说这些东西，涉及到了逻辑操作，我们日常学到SQL都是增删改查啊，这些问题可能要借助编程语言实现了。

其实完全不用，Mysql支持PL/SQL，简单的说就是可以让你的SQL做简单的逻辑操作，以此代替java代码，是不是很酷。

既然已经说过了，SQL做简单的逻辑操作，那是不是它也可以向其他语言一样，可以有函数呢，别说还真有，Msql支持函数和存储过程。

和java的方法一样，PL/SQL的函数也支持参数，返回值等。

函数是有返回值的逻辑集合体。如now()/max()/min()… 这些都是系统自带的。

由于Mysql对自己的过度保护，我们自定义的函数( sql封装体 ) Mysql是不认可的，我们需要将给Mysql信任白名单告知。

那么存储过程是什么呢？存储过程其实就是没有返回值的函数。

下面通过例子一起学习函数和存储过程的用法。

那么我们依然用老办法，实践是检验真理的最好方式。上代码。

# 新建库
create database bigData;
use bigData;
 
 
#1 建表dept
CREATE TABLE dept(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
dname VARCHAR(20) NOT NULL DEFAULT "",  
loc VARCHAR(13) NOT NULL DEFAULT ""  
) ENGINE=INNODB DEFAULT CHARSET=GBK ;  
 
 
#2 建表emp
CREATE TABLE emp  
(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
hiredate DATE NOT NULL,/*入职时间*/  
sal DECIMAL(7,2) NOT NULL,/*薪水*/  
comm DECIMAL(7,2) NOT NULL,/*红利*/  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  
)ENGINE=INNODB DEFAULT CHARSET=GBK ; 

简单的建两张表。
本次的目标是，随机往表里插入1000W数据
接下来我们就写函数吧！
等等…
好像添加一个白名单。
设置参数log_bin_trust_function_creators

为什么要设置这个参数？

当开启二进制日志后(可以执行show variables like 'log_bin’查看是否开启)，
如果变量log_bin_trust_function_creators为OFF，那么创建或修改存储函数就会报

“ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, 
or READS SQL DATA in its declaration and binary logging is enabled 
(you *might* want to use the less safe log_bin_trust_function_creators variable)”

这样的错误。

【解决方法】
show variables like ‘log_bin_trust_function_creators’;
set global log_bin_trust_function_creators=1;

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

那么我们按照操作一步一步来。

我们的二进制日志已经开启。如图

查询log_bin_trust_function_creators，发现是关闭的，不可以创建函数。

接下来我们把他开启。

再次查询发现已经是NO了。

我们PL/SQL函数的语法和JAVA有很多相近之处，我们写一个随机生成字符串的函数。

2. 使用自定义函数随机生成字符串

 #换行符的分割标识，原来是以；结尾，由于函数中也要用到；所以用$$代替。
DELIMITER $$  
#创建函数 rand_string(n INT), 参数为INT型的变量n，返回值类型为VARCHAR长度为255
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
#函数的开始
BEGIN
#定义varchar型变量，chars_str ，初始化为26个字母，用来后期随机从中挑选字符。
 DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 #定义一个varchar型的return_str，默认为空字符串，用来返回结果。
 DECLARE return_str VARCHAR(255) DEFAULT '';
 #定义一个变量i，默认为0
 DECLARE i INT DEFAULT 0;
 #循环，条件为i<n，n为想返回多少位随机数，每次循环生成一个随机数
 WHILE i < n DO
 #CONCAT是将后面的参数追加到return_str这个参数中
 #SUBSTRING和java一样，就是切割字符串，chars_str为源串，
 #1+RAND*52为生成52个随机数
 #FLOOR(1+RAND*52)，由于1+RAND*52生成的书是浮点数，故用FLOOR来取整
 #floor函数在官方的解释是，返回小于等于该值的最大整数。
 #这句的作用就是将随机生成的一个字符追加到return_str
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 #i++
 SET i = i + 1;
 #循环体结束
 END WHILE;
 #返回随机字符串
 RETURN return_str;
END $$
 
#假如要删除
#drop function rand_string;

运行结果如下

我们在写一个随机生成数字

3. 使用自定义函数随机生成数字

#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END $$
 
 
#假如要删除
#drop function rand_num;

运行结果

函数是有返回值的，因此我们创建了具有返回值的随机字符串和随机数字，我们可以用这些随机数据作为我们即将插入需要动态变化的资源字段。那么批量插入字段是不用返回值的，因此我们下一步采用存储过程插入数据。

4. 使用存储过程插入员工表

我们创建一个插入员工表 的存储过程，insert_emp()；

DELIMITER $$

#创建存储过成，START为员工开始编号，max_num为插入多少条数据
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成0  关闭自动提交
 SET autocommit = 0;    
 REPEAT  #存储过程中的循环
 SET i = i + 1;  #游标

#插入员工
#员工号对应Start+i如100，101，102
#员工姓名为我们自己写的随机生成的6位字符串
#部门编号为随机生成的3位数
 INSERT INTO emp (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());  
 #循环终止条件
 UNTIL i = max_num  
 #循环体结束
 END REPEAT;
 #手动提交  
 COMMIT;  
 END $$
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;

5. 使用存储过程插入部门表

然后在定义一个部门插入存储过程

#执行存储过程，往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept (deptno ,dname,loc ) VALUES ((START+i) ,rand_string(10),rand_string(8));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END $$ 
 
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;

做一个简单的测试：

两个插入存储过程都写完了，自动插入的感觉太爽了，那是不是可以直接插入千万条数据呢，如果不想让你的电脑卡死，我建议你分批插入。

我插入了500000条数据用了41.78秒。。。。

这是查询的结果，从员工编号可以确定我们插入了500000条数据

五 、MySql索引结构

1.基础回顾

我们在大学的时候都学过计算机组成原理，在这门课中，我们学习了硬盘的结构以及原理。
一个硬盘结构图如下图所示。

在写入数据的时候，距离盘面 3 纳米的磁头会利用电磁铁，改变磁盘上磁性材料的极性来记录数据，两种极性分别对应 0 或 1 。

而读取数据时，旁边的读取器可以识别磁性材料的不同极性，再还原成 0 或 1 。

一片磁盘分为若干个磁道，每个磁道又分为各个扇区。扇区是磁盘存储的最小数据块，大小一般是 512 字节。

因此，磁头要想读取某个文件，必须在电机驱动下，先找到对应的磁道，再等磁盘转到对应扇区才行，一般会有十几毫秒的延迟，这就让机械硬盘在读取分散于磁盘各处的数据时，速度将大幅降低。

而在磁道寻址的过程是很耗费时间的，就像我们查询新华字典一样，如果不用目录查找一个字的话，一页一页的翻需要很长时间，如果用事先约定好的规则(如偏旁部首)查询可以很快很轻松的查询到我们要查的文件。

Mysql也一样，索引的目的在于提高查询效率，可以类比字典，

如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从上往下找到y字母，再找到剩下的sql。每查询一个字母都是O(n)的时间复杂度。

如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？

是不是觉得如果没有索引，这个事情根本无法完成？

所以使用索引好处多多。

• 索引能极大的减少存储引擎需要扫描的数据量
• 索引可以把随机IO变成顺序IO
• 索引可以帮助我们在进行分组、排序等操作时，避免使用临时表

2. 索引检索原理

本章用到了很多数据结构，如果数据结构不好的同学，建议自学数据结构。

那么索引的检索原理是什么呢？
你可以简单理解为“排好序的快速查找B树数据结构”
B+树中的B代表平衡（balance）而不是二叉（binary）

在讨论B+树前，我们先讨论一下二叉树。

二叉树是利用了折半查找的方式，

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。

这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

那么前面我们说过了，Mysql的索引是用B+树(加强版多路平衡查找树)作为基础数据结构的，那么为什么要采用B+树呢，别的数据结构不好吗？
我们知道很多很多可以用来快速查询的数据结构与算法，比如数组，Hash，二分查找，又如二叉搜索树，再例如AVL平衡树，B树(Balance Tree多路平衡查找树)等。

下面我们就聊聊索引算法的发展史。

3. 索引数据结构的发展史

开头放一个时间复杂度图，忘记的同学复习一下。

其实对于查找，我们可以大致分为三类

• 数组
  作为一个人尽皆知的数据结构，数组的单点插入时间复杂度为O(N),单点查询的时间复杂度为O(1)。

• Hash
  不得不承认Hash是一个很优秀的算法，单点插入时间复杂度为O(1)，单点查询的时间复杂度为O(1)，那么为什么Mysql不采用Hash作为索引算法呢？

• 树
  二叉树的算法都是O(Log n)，在这可以发现，单按照效率而言，Hash是优于树的，比如在总数据量为8的序列中查询，树需要log2 8 = 3需要3次可以查到，而Hash只需要1次，但是不要忽略一个致命问题，那就是区间查找和排序，对数树来说，区间查找的时间复杂度依然是O(log n)，而Hash却已经变成了O(n)，两害相衡取其轻，综合考虑，选择树作为索引的算法。

既然奠定算法，我们复习下树的知识。

二叉树

二叉树的特点：
1、一个节点只能有两个子节点，也就是一个节点度不能超过2
2、左子节点 小于 本节点；右子节点大于等于 本节点，比我大的向右，比我小的向左

对该二叉树的节点进行查找发现:
深度为1的节点的查找次数为1，
深度为2的节点的查找次数为2，
深度为N的节点的查找次数为N，
结论：因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次

二叉树看起来不错嘛，为什么要用B+？

极端情况？左右倾错误。

第1种情况
如果id的值是持续递增的话，建立出的树会是什么样的结构？

第2种情况

很可怕吧，极端的情况下，时间复杂度会退化成恐怖的O(n)。

那么我们能不能在建树的时候，把树 “正” 过来呢，不要这么偏激。

平衡二叉树

什么是平衡二叉树

平衡二叉树的特点：
1、一个节点只能有两个子节点，也就是一个节点度不能超过2
2、左子节点 小于 本节点；右子节点大于等于 本节点，比我大的向右，比我小的向左

从算法的数学逻辑来讲，平衡二叉树的查找速度和比较次数都是较小的，说明磁盘IO的次数也很少，那为什么我们选择BTREE？

理想丰满，现实骨感。我们不得不考虑一个最坑爹的问题。我们以二叉树作为索引结构，举个例子：

假设树高是4，查找的值是10，我们的流程如下：

初始加载树

第1次查找：

第2次查找：

第3次查找：

第4次查找：

从上一步的查找过程中发现的规律？

磁盘的IO次数是由什么决定的？

树高，也即磁盘的IO次数最坏的情况下就等于树的高度。

平衡二叉树产生的问题：

树高度问题导致磁盘IO过多

还是不行，哎

我们需要将"瘦高"的身形变成"矮胖"，通过降低树的高度达到减少IO的次数
那么有没有什么办法把树压扁呢，如果三叉呢？

B树

我们发现B树不是二叉树，因为它可能会有三个叉，所以又叫二三树。后面会详细介绍B树。

我们发现，B树比平衡二叉树树高要低。

别急，在了解B树的检索原理前，我们先了解一下磁盘页/块

那如果一个数据库中的内容很多，需要将所有的索引都加载进来吗？

• 数据库索引是存储在磁盘上的，如果数据很大，必然导致索引的大小也会很大，超过几个G（好比新华字典字数多必然导致目录厚）

• 当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是：逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。

树只是逻辑结构，物理上数据还是存储在磁盘中，每个节点存储在磁盘页中。

磁盘页/块

首先我们用SQL查询一下页的信息

 SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

Innodb_page_size
INNODB page size (DEFAULT 16KB).
Many VALUES are counted IN pages; the page size enables them TO be easily converted TO bytes

说明每个页的大小是16kb。

底层原理

系统从磁盘读取数据到内存时是以磁盘块(block) 为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么(防止重复读取浪费时间)。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。

系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。(争取塞满整个页)
InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B树检索原理

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。

模拟查找关键字29的过程：
根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

结论：B树比平衡二叉树减少了一次IO操作

其实b树就已经很好了，但是丧心病狂的工程师们还不满意，继续优化。

B+树

B+树和B树的最明显的区别就是所有数据都拿到了叶子节点，叶子节点采用链表数据结构。

图中可以看出所有data信息都移动叶子节点中，而且子节点和子节点之间会有个指针指向，这个也是B+树的核心点，这样可以大大提升范围查询效率，也方便遍历整个树

1. 非叶子节点不再存储数据，数据只存储在同一层的叶子节点上；
2. 叶子之间，增加了链表，获取所有节点，不再需要中序遍历；

B+树的检索原理

B树(注意是B树) 的结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B树的深度较大，增大查询时的磁盘I/O次数进而影响查询效率。

但在B+树中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+树的高度。

1. InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针， 在B+树中叶子节点存放数据，非叶子节点存放键值+指针。
2. 索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，首先找到根页进而在去数据页中查找到需要的数据

B+树算法： 通过继承了B树的特征，B+树相比B树，新增叶子节点与非叶子节点关系。

• 叶子节点中包含了键值和数据，
• 非叶子节点中只是包含键值和子节点引用，不包含数据。
• 通过非叶子节点查询叶子节点获取对应的数据，所有相邻的叶子节点包含非叶子节点使用链表进行结合，叶子节点是顺序排序并且相邻节点有顺序引用的关系 。

结论：从B树到B+树

B+树是在B树基础上的一种优化使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+树实现其索引结构。

B+树相对于B树有几点不同？

• 非叶子节点只存储键值信息。

• 所有叶子节点之间都有一个链指针。

• 数据记录都存放在叶子节点中。

这章的最后，我们看一下上述结构算法复杂度

大O表示法

六、索引优化

1. 索引基本类型与语法规则

如果我们想买一本书，肯定去搜索书名而不是去搜索ISBN码，那么一个商品库里有那么多的树，当我们 WHERE book = ’ XX '的时候，想想是不是很恐怖，所以我们要在对应字段上建立索引。

索引大致上分为以下几类。

1.单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
如：

create index idx_book_ bName on t_book (bookName) //单值索引
select * from t_book where bookName=' core java ‘

2.唯一索引
索引列的值必须唯一，但允许有空值。
如：

create index unique idx_book_ bName on t_book (bookName) //单值索引
select * from t_book where bookName=' core java ‘

主键就是唯一索引

3.复合索引
即一个索引包含多个列

create index idx_ book_ bNameathpci on t_book (bookName, author, price)//复合索引
select * from t_book where bookName=' core java' and author=' z3’;

有人想问，那我多建立几个单值索引不就好了嘛？你可以这么用，但是那不是闲的嘛。。。。不推荐

索引语法

1.创建

• 方法1：

使用cerate [] 里的内容为可选，选中就是唯一索引
CREATE  [UNIQUE ] INDEX indexName ON mytable(columnname(length)); 

• 方法二

和下面的ALTER区分
ALTER TABLE 表名 ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length)) 

2.删除

DROP INDEX [indexName] ON mytable; 

3.查看

SHOW INDEX FROM table_name\G

4.使用ALTER命令

方法1：

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

方法2：

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 

方法3：

 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)

操作好像挺简单，但是

索引建立了就可以了吗？索引建立了查表就会块吗，效率就会高吗，索引建立的好不好？是否被引用？如果索引建立了，不被用那不就是垃圾吗。下面继续

2. 索引和EXPLAIN

前面我们了解了我们手写SQL语句和机读SQL语句顺序是不同的，所以说Mysql是会自动优化我们写的SQL语句。
因此我们可以，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
借用官网的文档，大致了解下
http://dev.mysql.com/doc/refman/5.5/en/explain-output.html

explain可以分析我们的查询语句或是表结构的性能瓶颈，具体有什么用呢？大致分为以下几点

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

这是官网的原话，有没有觉得哪个字都是中国字，但是就是读不懂，不要急，慢慢来。

explain语法很简单如下图

sql写的好不好我们要眼见为实，数据说话。

但是分析出来的数据都是什么意思呢？

其实我们在说这些之前，要在了解以下一条SQL的执行流程。如图所示

图很容易懂，就不解释了。

本次是以5.5的版本进行讲解，5.8之后略有不同，但是大致一样。

执行计划包含的信息如图如图所示(就是分析出的字段)

3. 分析字段解释

---

id(重点)

这可不是数据表中的id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，一般情况下id越多表越复杂。

id字段的内容又分为以下三种情况

1.id相同，执行顺序由上至下

我们发现id都是1，table的顺序是t1，t3，t2，表明三个表是顺着加载，但是有人一定会有疑惑，我们form的顺序明明是t1，t2，t3啊，怎么到了执行顺序中就变成了，t1，t3，t2了呢，我们前面说过，手写和机读的顺序是不同的。

2.id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

我们都知道，括号的优先级是很高的，被括号修饰的语句肯定是最先执行的，我们在分析表中的id，发现是1，2，3，再看对应的table字段，发现是t2，t1，t3，正好对应id越大表越先被执行。

所以 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

因此在下一条开始前，先记一句口诀，id相同顺着走，id不同大的先走。

3.id相同不同，同时存在

我们依然还是分析下语句，还是先执行括号里的语句，括号里面查询到信息生成了一个临时表s1，然后和t2合作共同查询出了t3.id。

在分析下，查询出的表id为2的字段是最大的，发现对应的表是t3，t3是在括号里面的最先用到的表，然后再看第一行和第二行，发现id都是1，因此他们对应的表在按照从上到下顺序执行。

细心的朋友一定发现了，第一行对应的表名是derived2，这是什么意思呢？其实这是临时表的意思，derived后面的2对应的是id为2的表t3，意思是这个临时表是通过t3表诞生的。既s1表。

---

select_type

select_type查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
select_type全部字段如下：

天哪，怎么有这么多字段，想想脑袋都烦！ 🙃
但是不用急，我总结出来6条常用的参数，至于剩下的语句，他们可能出现的参数就和外星人一样少，我们就放弃他们了。

这六条分别是：

1. SIMPLE
简单的 select 查询,查询中不包含子查询或者UNION

2. PRIMARY
查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY，联系3一起看。

3. SUBQUERY
在SELECT或WHERE列表中包含了子查询，如

我们发现t1和t3都在括号里，是t2的子查询，因此他们是SUBQUERY，引用他们的，最外层的查询则是t2表，因此t2是PRIMARY。

4. DERIVED

在FROM列表中包含的子查询被标记为DERIVED(衍生)
MySQL会递归执行这些子查询, 把结果放在临时表里。

说白了，就是哪个表被引用创建了虚表，如s1引用了t3。因此t3的select_type就是DERIVED。

5. UNION

UNION这个关键字好熟悉啊，不就是我们学习join的时候全连接中学到的连接并去重的关键字嘛。select_type为UNION表示这个表和其他的表发生了UNION关系。

根据查询语句可知，若第二个SELECT出现在UNION之后，则被标记为UNION；

若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED

6. UNION RESULT
图见第五条

从UNION表获取结果的SELECT，就是a和b的UNION结果集的查询。

---

table
显示这一行的数据是关于哪张表的

---

type(重点)

简单的说，type决定了你用什么样的方式用到了索引。

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

太长了？别担心

整理好的常用指标排序。
system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

1. system
表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

括号里面查询出的结果生成一张只有一条数据的临时表，在这张临时表中查询数据，type就是system。

2. const

表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

可以看到第一条查询语句，type类型为ALL，由于id为主键唯一索引，由于where id = 1， 1是常量，所以type类型是const。

想一想，那如果id>1呢？

3. eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
对于这个样例，我们先简单建一个表

简单的查询一下

我们发现t1表和t2表只有一条数据，并且id=1

在这个查询语句中，只使用到了t2表中的id主键索引，查询结果只有一条，因此是eq_ref。

4. ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

这个样例中，在col1和col2的字段上创建了复合索引，select语句用到了复合索引中的col1，然后查到了7条记录，看清不是1条，是7条。

5. range
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

6. index
Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

select id， 由于id既是字段，也是索引，因此select查的实际上是主键唯一索引，因此使用到了索引，type类型为index。

7. ALL
select * 的坏处就是混合查询，全表扫描，没有用到索引，这也就是为什么不建议用select * 去查询
数据。

---

possible_keys
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

与下一条 key 一起详细介绍

---

key(重点)

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引和查询的select字段重叠(覆盖索引概念)

而possible_keys和key也有四种搭配

1. 理论上用不到索引，实际上也没用到
   
   见图第三条

2. 理论上用不到索引，实际上用到了
   

3. 理论上用到了索引，实际上用不到
   

4. 理论上用到了索引实际上用到了
   
   见图第二条
   
   这个也是

---

key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

这个容易误会，如果有同一种结果，两种方案解决，当然是越短越好。因为做的事情不同，有的复杂任务key_len就是会很长，这不代表它不好。

---

rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，同一任务，rows越短性能越高。

上图分别是建立索引前后两条语句性能对比，我们参考rows，发现没建立索引的语句rows=640，而使用到了索引的语句rows行仅为195.

---

ref
显示索引的哪一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即 ‘ac’。

对于t2表的ALL，大家也不用慌张，并不是由ALL性能就会低的可怕，这种情况在连表查询中是很常见的，数据库中有一条规定，叫做小表驱动大表。

虽然上图循环的次数都是9W次，但是我们要毫不犹豫的选择第一种，打个比方，如果外层循环是建立连接，如果第一种方案就是建立3次连接，第二种方案可就是建立3万次连接了。

所以在数据库查询种，一定要用小表去驱动大表。

---

Extra(重点)

包含不适合在其他列中显示但十分重要的额外信息

1.Using filesort
出现这个，不好，九死一生

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”

我们都知道，每个字段的索引是基于b+树的，而这种数据结构必然要对数据进行一个整理排序，但是有的sql语句比较奇葩，我们排好序建立的索引没有覆盖这条sql语句，因此mysql只能为了这条sql语句重新排序。

举个例子

我们分析下这条sql语句，发现key=idx_col1_col2_col3，说明这条sql语句用到了索引，在extra这个字段我们发现，using where(使用到了where)，Using index(使用到了索引)，Using filesor(艾玛)。我们发现了Using filesor，我们刚才说了，碰到这个提示，我们就知道了，我们建立的索引和sql语句不契合，那我们分析下原因吧。

就像上楼一样，人们都需要从一楼上到二楼在上到三楼，没人从一楼直接飞到三楼(别跟我扯电梯)，这就像是索引，没有满足人(sql语句)的要求，因此工程师(mysq)l就单独为这个人建立了一条空中楼梯(重新排序)。

在这条语句中，我们发现select col1用到了col1，order by col3用到了col3，那col2呢？是不是就像断了楼梯一样形象？因此导致了 Using filesort。

那怎修改？

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

那当然利用一下二楼了(col2)，指定sql语句使用col2就ok。

---

2. Using temporary

也不是啥好东西

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

但是仅仅是分组或者排序惹的祸吗？别忘了这里有两张非直接关联的表，这样的查询，如果有查询条件或者排序分组的时候往往都需要创建临时表（这个没有办法，想想也知道）。

现在记住一条口诀，范围之后会失效。大致意思就是入托range用到了索引，那么在range后面在继续使用索引range的索引对于后面的字段是无效的。看不太懂举个例子。

简单分析下，type类型为range，说明是范围查询，key字段引用到了索引，extra中居然同时出现了，Useing temporary与Using filesort 黑白双煞可怕。

在分析语句，我们发现col1 in(’ac‘,‘ab’,‘aa’) 用到了col1索引，group by col2 用到了col2索引，看起来没问题啊，想想上面的口诀，range之后会失效，说明在group by的时候并没有col1，因此group by中只是用到了col2索引，没用到col1索引，没有一楼想上二楼可能吗？

修改方法：

手动为group by 连接col1，实现col1和col2覆盖，可以弹道rows从最初的569优化成了4，真的是提升巨大呢。

---

3. USING index
别害怕，这是好东西

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;

如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

总说覆盖索引，解释下

覆盖索引（Covering Index）,也叫做索引覆盖。

• 理解方式一:
  就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

• 理解方式二:
  索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

注意：
如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

4. 索引失效

什么是索引失效？字面意思。
话不多说，上代码，建表sql

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('z3',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',24,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',25,'dev',NOW());
 
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

建完表简单验证下

我们分步学习，学完之后就知道什么是索引失效了。

---

1.全值匹配我最爱

就是索引建立的个数和顺序我们使用的时候也尽量保持一致和顺序相同。

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 = 'dev';

为什么顺序也要相同呢？我们先验证下结果，看看调换调用顺序会不会影响查询效率。

这两条语句我只是颠倒了调用顺序，结果也是一样的，那是不是颠倒没有意义呢，并不是，实际上没意mysql底层做了优化，在底层又把顺序正过来了，但是这也增加了mysql的开销。

---

2. 最佳左前缀法则(超重点)
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev'; 
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

首先看第一条语句，看起来的确使用索引了，因为用到了age，pos，但是实际上分析结果却显示并没有用到索引。
再看第二条语句，的确用到了pos，但是分析结果也显示没有用到索引。
为什么？

记不记得我说的没有一楼直接上二楼。我们仔细看，我们的复合索引是从name开始，然后age，最后pos，那他们分别就是1楼，2楼，3楼，第一个样例，name没开头，第二个样例mame没开头，第三个样例，是以name开头的，所以用到了索引。

如果把2楼，age干掉呢？索引还能不能引用？

发现只是使用了name，二楼没了不耽误我去一楼啊，部分索引用到了。

---

3.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。

在这个例子中，第一行的意思是查询name为july的人。
第二行是查询name前左四位等于july的人。
第三行是查询name前左三位等于july的人。

这个样例的意思想表达的就是 left(NAME,4)这是一个函数(虽然你们都懂)

我们再看，这样查询结果无疑是正确的，但是我们发现，他并没有用到索引，这是怎么回事呢？name上的确建立索引了啊？

只因为弄了个函数，因为计算的优先级比较高，mysql会牺牲索引先处理计算，函数也是计算的一种，因此，等号左面不要计算，计算放在右面。

---

4. 存储引擎不能使用索引中范围条件右边的列

换个颜色换个心情

还记得范围之后全失效嘛？上次用到的哪个小口诀。

第一行，用到了索引name
第二行，用到了索引，name，age，顺序是对的。
第三行，用到了索引，索引全覆盖。
第四行，注意age>11，这代表后面的pos会失效，所以生效的仅仅只有name。

---

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*

其实就是select * 和select 字段的区别。

第一条，查询验证结果
第二条，使用select * 查询，where用到了索引，所以extra 提示Using where
第三条，使用select 字段查询，查询字段索引全覆盖，where用到了索引，所以在Using where的基础上还多了using index的小金标。
第四条，type提示range，注意范围之后会失效，因此索引只用到了name，key_len=78
第五条，type提示ref，但是语句中也有range啊，我们前面了解了，ref要比range性能更好，那这条带有range语句凭什么呢，难道它不失效了？错！range后还是失效了，但是和前面语句不同的是，extra提示Using index，说明查询字段索引覆盖，同样是查询三个字段，但是第五条却可以直接在索引上拿字段，而第四条却只能苦苦遍历字段，这就是优势。

6.mysql 在使用不等于(!= 或者<>)的时候有时候无法使用索引会导致全表扫描
换颜色咯

我们发现带有不等用户(!=/<>)的字段索引全部失效了，这太可怕了，但是以也不要恐慌，我们不能因噎废食，两害相衡取其轻，我们认为可以就好了，只要数据亮不太大。

7.注意null/not null对索引的可能影响
先看官网说明

是不是看不懂😝别慌

我们举两个例子：
第一个例子

简单分析下，建表的时候，name 为NOT NULL，我们插入一条数据，在建立两条索引。一条复合索引name,age,pos，一条单值索引name。

查询一下，天哪，索引失效了，第一条，name is null， 更可怕的是table都搞没了。
第二条，name is not null， 同样失效了，possible_keys提示可能用到两个索引，但key提示一个索引都没用到。

再来一个例子

注意，这回name没有not null了。

我们发现，第一条type提示ref，key也提示用到索引了。
第二条，type虽然退化到了range，但是还是用到索引了。

因此网上有人说，NULL回导致索引失效，并不绝对。

---

8. like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

第一条，索引用到了。
第二条，加入like，发现索引失效了。
第三条，去掉右侧%，发现还是失效。
第四条，去掉左侧%，发现索引没失效。

记住个小口诀，like%写最又。

9.字符串不加单引号索引失效

很可怕，字符串不加但也好原来也会导致索引失效。
想想为什么？

因为不加双引号是属于数字类型，mysql底层会类型转换，前面是不是说了，不能有计算。

10.少用or,用它来连接时会索引失效

果然还是失效了

小总结

select * 且 假设index(a,b,c)

主要解释like
第一个like，用到三个的原因是，like也是范围查询，但是他和range不同的是，对于’kk%'中kk是确定的值，因此擦边可以连接abc。
第二个like，’%kk‘ 中%是模糊查询，因此只能用到a。
第三个，不解释，只能用到a，和第二个一样
第四个，k是额定的值，因此可以用到索引，abc。

【优化口诀】

全职匹配我最爱，最左前缀要遵守;
带头大哥不能死，中间兄弟不能断;
索引列上少计算，范围之后全失效;
LIKE百分写最右，覆盖索引不写*;
不等空值还有OR，索引影响要注意;
VAR引号不可丢，SQL优化有诀窍。