MySQL（三）视图、存储过程、索引_mysql有视图么
赞
踩
文章目录
本系列文章：
MySQL（一）SQL语法、数据类型、常用函数、事务
MySQL（二）MySQL SQL练习题
MySQL（三）视图、存储过程、索引
MySQL（四）存储引擎、锁
MySQL（五）MySQL架构、数据库优化、主从复制
MySQL（六）SQL语句优化
MySQL（七）MySQL和Oracle、PostgreSQL的区别
一、视图
1.1 视图的基本概念
视图是在Mysql5之后出现的，是一种虚拟表，行和列的数据来自于定义视图时使用的一些表中，视图的数据是在使用视图的时候动态生成的，视图只保存了sql的逻辑，不保存查询的结果。使用视图是为了提高复杂SQL语句的复用性和表操作的安全性。
视图，本质上是一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。
视图的建立和删除不影响基本表，但视图的数据变化会影响到基表，基表的数据变化（增删改）也会影响到视图。
创建视图需要create view 权限，并且对于查询涉及的列有select权限；使用create or replace 或者 alter修改视图，那么还需要改视图的drop权限。
当视图来自多个基本表时，不允许添加和删除数据。
视图根本用途：简化sql查询，提高开发效率。
- 视图的使用场景
多个地方使用到同样的查询结果，并且该查询结果比较复杂的时候，我们可以使用视图来隐藏复杂的实现细节。即：
- 1、复用SQL语句；
- 2、使用表的部分字段而不是整个表；
- 3、保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
- 4、更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
- 视图的好处
- 1、简化复杂的sql操作，不用知道他的实现细节。
- 2、隔离了原始表，可以不让使用视图的人接触原始的表，从而保护原始数据，提高了安全性。
- 视图的缺点
- 1、
性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。- 2、
表依赖关系。将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。
- 视图的特点
- 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
- 视图是由基本表(实表)产生的表(虚表)。
- 视图的建立和删除不影响基本表。
- 对视图内容的更新(添加，删除和修改)直接影响基本表。
- 当视图来自多个基本表时，不允许添加和删除数据。视图的操作包括创建视图，查看视图，删除视图和修改视图。
视图和表的区别：
| 语法 | 实际中是否占用物理空间 | ||
|---|---|---|---|
| 视图 | 只是保存了sql的逻辑 | 增删改查，实际上我们只使用查询 | |
| 表 | 保存了数据 | 增删改查 |
1.2 视图操作语句
视图的操作包括创建视图，查看视图，删除视图和修改视图。
#创建视图 #在视图中，FROM关键字后面不能包含子查询 CREATE VIEW 视图名 AS SELECT 字段名 FROM 表名; #如果该视图存在，就修改，如果不存在，就创建新的视图 CREATE OR REPLACE VIEW 视图名 AS SELECT句; #修改视图 ALTER VIEW 视图名 AS SELECT 语句; ALTER VIEW 视图名 AS SELECT 视图; #查看视图 SHOW CREATE VIEW 视图名; DESC 视图名称; #删除视图 DROP VIEW 视图名; #重命名视图 RENAME TABLE 视图名 TO 新视图名;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
二、变量
环境：mysql5.7.25。
在使用mysql的过程中，变量也会经常用到，比如查询系统的配置，可以通过查看系统变量来了解，当我们需要修改系统的一些配置的时候，也可以通过修改系统变量的值来进行。
变量可以分为系统变量和自定义变量。
2.1 系统变量
系统变量由系统定义的，不是用户定义的，属于mysql服务器层面的。
系统变量又可分为：全局变量和会话变量。
-- 查看系统所有变量
show [global | session] variables;
-- 查看全局变量
show global variables;
-- 查看会话变量
show session variables;
show variables;
-- 查看满足条件的系统变量(like模糊匹配)
show [global|session] like '%变量名%';
-- 查看指定的系统变量的值
select @@[global.|session.]系统变量名称;
-- 赋值
set [global|session] 系统变量名=值;
set @@[global.|session.]系统变量名=值;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认为session级别。
全局变量的使用中用到了 @@ 关键字，自定义变量中使用了一个 @ 符号。
- 全局变量
Mysql服务器每次启动都会为所有的系统变量设置初始值。
/*查看所有全局变量*/
show global variables;
/*查看包含`tx`字符的变量*/
show global variables like '%tx%';
/*查看指定名称的系统变量的值，如查看事务默认自动提交设置*/
select @@global.autocommit;
/*为某个系统变量赋值*/
set global autocommit=0;
set @@global.autocommit=1;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 会话变量
针对当前会话（连接）有效。
/*查看所有会话变量*/
show session variables;
/*查看满足条件的步伐会话变量，如查看包含`char`字符变量名的会话变量*/
show session variables like '%char%';
/*查看指定的会话变量的值，如查看事务默认自动提交的设置*/
select @@autocommit;
select @@session.autocommit;
/*为某个会话变量赋值*/
set @@session.tx_isolation='read-uncommitted';
set session tx_isolation='read-committed';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
2.2 自定义变量
变量由用户自定义的，而不是系统提供的。使用步骤：
- 声明
- 赋值
- 使用（查看、比较、运算）
自定义变量分为：用户变量和局部变量。
- 用户变量
针对当前会话（连接）有效，作用域同会话变量。
-- 声明并初始化(要求声明时必须初始化)
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
-- 赋值
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
-- 使用
select @变量名;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 局部变量
declare用于定义局部变量变量，在存储过程和函数中通过declare定义变量在begin…end中，且在语句之前。并且可以通过重复定义多个变量declare变量的作用范围同编程里面类似，在这里一般是在对应的begin和end之间。
-- 声明
declare 变量名 变量类型;
declare 变量名 变量类型 [default 默认值];
-- 赋值
set 局部变量名=值;
set 局部变量名:=值;
select 局部变量名:=值;
-- 使用
select 局部变量名;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 用户变量和局部变量
| 作用域 | 定义位置 | 语法 | |
|---|---|---|---|
| 用户变量 | 当前会话 | 会话的任何地方 | 加 @ 符号，不用指定类型 |
| 局部变量 | 定义他的begin end之间 | begin end中的第一句话 | 不加 @ 符号，要指定类型 |
三、存储过程和自定义函数
线上程序有时候出现问题导致数据错误的时候，如果比较紧急，我们可以写一个存储来快速修复这块的数据，然后再去修复程序。
关于自定义函数，若Mysql内部自带的一些函数无法满足需求的话，我们可以自己开发一些自定义函数来使用。
3.1 储存过程
存储过程就是为了以后的使用而保存的一条或者多条MySQL语句的集合。可将视为批文件，虽然他们的作用不仅限于批处理。
3.1.1 储存过程的优点
- 1、安全性高
可设定只有某此用户才具有对指定存储过程的使用权。 - 2、性能好
1）存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
2）当对数据库进行复杂操作时(如对多个表进行增删改查时），要执行很多条SQL语句，可能要多次连接数据库。而换成存储过程，只需要连接一次数据库就可以了。 - 3、可重复使用
存储过程可以重复使用,可减少数据库开发人员的工作量。
3.1.2 储存过程的缺点
- 1、编写复杂
存储过程的编写更为复杂。 - 2、权限要求更严格
可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许使用，不允许创建。 - 3、维护难度高
1）重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译。
2）如果在一个程序系统中大量的使用存储过程，到程序交付后，生产环境中随着用户需求的增加会导致数据结构的变化，此时要修改存储过程来满足变化后的需求，此时的代价将非常巨大，所以存储过程不是越多越好。
3.2 储存过程的使用
- 创建存储过程
create procedure 存储过程名([参数模式] 参数名 参数类型)
begin
存储过程体
end
- 1
- 2
- 3
- 4
参数模式有3种：
in：该参数可以作为输入，也就是该参数需要调用方传入值。
out：该参数可以作为输出，也就是说该参数可以作为返回值。
inout：该参数既可以作为输入也可以作为输出，也就是说该参数需要在调用的时候传入值，又可以作为返回值。
参数模式默认为IN。
一个存储过程可以有多个输入、多个输出、多个输入输出参数。
- 调用存储过程
call 存储过程名称(参数列表);
- 1
- 删除存储过程
drop procedure [if exists] 存储过程名称;
- 1
存储过程只能一个个删除，不能批量删除。
存储过程不能修改，若涉及到修改的，可以先删除，然后重建。
- 查看存储过程
-- 可以查看存储过程详细创建语句
show create procedure 存储过程名称;
- 1
- 2
- 存储过程的使用示例
/*设置结束符为$*/
DELIMITER $
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc1;
/*创建存储过程proc1*/
CREATE PROCEDURE proc1()
BEGIN
INSERT INTO t_user VALUES (1,30,'zhangsan');
INSERT INTO t_user VALUES (2,50,'lisi');
END $
/*将结束符置为;*/
DELIMITER ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
delimiter用来设置结束符，当Mysql执行脚本的时候，遇到结束符的时候，会把结束符前面的所有语句作为一个整体运行，存储过程中的脚本有多个sql，但是需要作为一个整体运行，所以此处用到了delimiter。
mysql默认结束符是分号。
上面存储过程的功能：向t_user表中插入了2条数据。
3.3 函数
定义：一组预编译好的sql语句集合，理解成批处理语句。类似于java中的方法，但是必须有返回值。
语法：
-- 创建函数 -- 参数是可选的,返回值是必须的 create function 函数名(参数名称 参数类型) returns 返回值类型 begin 函数体 end -- 调用函数 select 函数名(实参列表); -- 删除函数 drop function [if exists] 函数名; -- 查看函数详细 show create function 函数名;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
函数使用示例(取出表里的最大id)：
/*删除fun1*/
DROP FUNCTION IF EXISTS fun1;
/*设置结束符为$*/
DELIMITER $
/*创建函数*/
CREATE FUNCTION fun1()
returns INT
BEGIN
DECLARE max_id int DEFAULT 0;
SELECT max(id) INTO max_id FROM t_user;
return max_id;
END $
/*设置结束符为;*/
DELIMITER ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
3.4 流程控制
- 1、if结构的使用(只能使用在begin end之间)
示例(实现用户数据的插入和新增，如果id存在，则修改，不存在则新增，并返回结果)：
/*删除id=7的记录*/ DELETE FROM t_user WHERE id=7; /*删除存储过程*/ DROP PROCEDURE IF EXISTS proc2; /*声明结束符为$*/ DELIMITER $ /*创建存储过程*/ CREATE PROCEDURE proc2(v_id int,v_sex varchar(8),v_name varchar(16),OUT result TINYINT) BEGIN DECLARE v_count TINYINT DEFAULT 0;/*用来保存user记录的数量*/ /*根据v_id查询数据放入v_count中*/ select count(id) into v_count from t_user where id = v_id; /*v_count>0表示数据存在，则修改，否则新增*/ if v_count>0 THEN BEGIN DECLARE lsex TINYINT; select if(lsex='男',1,2) into lsex; update t_user set sex = lsex,name = v_name where id = v_id; /*获取update影响行数*/ select ROW_COUNT() INTO result; END; else BEGIN DECLARE lsex TINYINT; select if(lsex='男',1,2) into lsex; insert into t_user VALUES (v_id,lsex,v_name); select 0 into result; END; END IF; END $ /*结束符置为;*/ DELIMITER ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 2、case结构的使用
在begin end中使用示例(接受3个参数：id、性别、姓名，然后插入到t_user表)：
/*删除存储过程proc1*/ DROP PROCEDURE IF EXISTS proc1; /*s删除id=6的记录*/ DELETE FROM t_user WHERE id=6; /*声明结束符为$*/ DELIMITER $ /*创建存储过程proc1*/ CREATE PROCEDURE proc1(id int,sex_str varchar(8),name varchar(16)) BEGIN /*声明变量v_sex用于存放性别*/ DECLARE v_sex TINYINT UNSIGNED; /*根据sex_str的值来设置性别*/ CASE sex_str when '男' THEN SET v_sex = 1; WHEN '女' THEN SET v_sex = 2; END CASE ; /*插入数据*/ INSERT INTO t_user VALUES (id,v_sex,name); END $ /*结束符置为;*/ DELIMITER ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
在函数中使用示例(根据t_user表sex的值，返回男女)：
/*删除存储过程proc1*/ DROP FUNCTION IF EXISTS fun1; /*声明结束符为$*/ DELIMITER $ /*创建存储过程proc1*/ CREATE FUNCTION fun1(sex TINYINT UNSIGNED) RETURNS varchar(8) BEGIN /*声明变量v_sex用于存放性别*/ DECLARE v_sex VARCHAR(8); CASE sex WHEN 1 THEN SET v_sex:='男'; ELSE SET v_sex:='女'; END CASE; RETURN v_sex; END $ /*结束符置为;*/ DELIMITER ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 3、循环
有3种写法：
- while：类似于java中的while循环。
- repeat：类似于java中的do while循环。
- loop：类似于java中的while(true)死循环，需要在内部进行控制。
-- 结束本次循环,类似于java中的 continue
iterate 循环标签;
-- 退出循环,类似于java中的 break
leave 循环标签;
- 1
- 2
- 3
- 4
- 4、while循环
语法：
[标签:]while 循环条件 do
循环体
end while [标签];
- 1
- 2
- 3
- 5、repeat循环
语法：
[标签:]repeat
循环体;
until 结束循环的条件 end repeat [标签];
- 1
- 2
- 3
- 6、loop循环
语法：
[标签:]loop
循环体;
end loop [标签];
- 1
- 2
- 3
3.5 游标
游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。游标可以用来遍历select查询的结果集，然后对每行数据进行处理。
游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行遍历数据的能力。
游标只能在存储过程和函数中使用。
- 游标的使用步骤
1）声明游标：这个过程只是创建了一个游标，需要指定这个游标需要遍历的select查询，声明游标时并不会去执行这个sql。
2）打开游标：打开游标的时候，会执行游标对应的select语句。
3）遍历数据：使用游标循环遍历select结果中每一行数据，然后进行处理。
4）关闭游标：游标使用完之后一定要关闭。 - 游标语法
-- 声明游标
-- 一个begin end中只能声明一个游标
DECLARE 游标名称 CURSOR FOR 查询语句;
-- 打开游标
open 游标名称;
-- 遍历游标
-- 取出当前行的结果，将结果放在对应的变量中，并将游标指针指向下一行的数据。
-- 当调用fetch的时候，会获取当前行的数据，如果当前行无数据，会引发mysql内部的 NOT FOUND错误。
fetch 游标名称 into 变量列表;
-- 关闭游标
close 游标名称;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 游标使用示例(计算test1表中a、b字段所有的和)
/*删除函数*/ DROP FUNCTION IF EXISTS fun1; /*声明结束符为$*/ DELIMITER $ /*创建函数*/ CREATE FUNCTION fun1(v_max_a int) RETURNS int BEGIN /*用于保存结果*/ DECLARE v_total int DEFAULT 0; /*创建一个变量，用来保存当前行中a的值*/ DECLARE v_a int DEFAULT 0; /*创建一个变量，用来保存当前行中b的值*/ DECLARE v_b int DEFAULT 0; /*创建游标结束标志变量*/ DECLARE v_done int DEFAULT FALSE; /*创建游标*/ DECLARE cur_test1 CURSOR FOR SELECT a,b from test1 where a<=v_max_a; /*设置游标结束时v_done的值为true，可以v_done来判断游标是否结束了*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; /*设置v_total初始值*/ SET v_total = 0; /*打开游标*/ OPEN cur_test1; /*使用Loop循环遍历游标*/ a:LOOP /*先获取当前行的数据，然后将当前行的数据放入v_a,v_b中，如果当前行无数据，v_done会被置 为true*/ FETCH cur_test1 INTO v_a, v_b; /*通过v_done来判断游标是否结束了，退出循环*/ if v_done THEN LEAVE a; END IF; /*对v_total值累加处理*/ SET v_total = v_total + v_a + v_b; END LOOP; /*关闭游标*/ CLOSE cur_test1; /*返回结果*/ RETURN v_total; END $ /*结束符置为;*/ DELIMITER ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
如果创建函数时报错“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary”，表明Mysql的设置默认是不允许创建函数。
解决办法有2种：1、执行SET GLOBAL log_bin_trust_function_creators = 1;，重启会失效。有主从复制的时候 从机必须要设置，不然会导致主从同步失败。2、在my.cnf里面设置log-bin-trust-function-creators=1，设置后需要重启服务。
3.6 异常
在执行存储过程时，可能会出现异常。此时我们需要使用游标，通过游标的方式来遍历select查询的结果集，然后对每行数据进行处理。
异常分为Mysql内部异常和外部异常。
Mysql内部异常：当我们执行一些sql的时候，可能违反了mysql的一些约束，导致mysql内部报错，如插入数据违反唯一约束，更新数据超时等，此时异常是由mysql内部抛出的，我们将这些由mysql抛出的异常统称为内部异常。
外部异常：当我们执行一个update的时候，可能我们期望影响1行，但是实际上影响的不是1行数据，这种情况：sql的执行结果和期望的结果不一致，这种情况也我们也把他作为外部异常处理，我们将sql执行结果和期望结果不一致的情况统称为外部异常。
Mysql内部异常捕获处理示例：
/*删除存储过程*/ DROP PROCEDURE IF EXISTS proc2; /*声明结束符为$*/ DELIMITER $ /*创建存储过程*/ CREATE PROCEDURE proc2(a1 int,a2 int) BEGIN /*声明一个变量，标识是否有sql异常*/ DECLARE hasSqlError int DEFAULT FALSE; /*在执行过程中出任何异常设置hasSqlError为TRUE*/ DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET hasSqlError=TRUE; /*开启事务*/ START TRANSACTION; INSERT INTO test1(a) VALUES (a1); INSERT INTO test1(a) VALUES (a2); /*根据hasSqlError判断是否有异常，做回滚和提交操作*/ IF hasSqlError THEN ROLLBACK; ELSE COMMIT; END IF; END $ /*结束符置为;*/ DELIMITER ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
Mysql外部异常捕获处理示例(模拟电商下单后更新金额操作)：
/*删除存储过程*/ DROP PROCEDURE IF EXISTS proc4; /*声明结束符为$*/ DELIMITER $ /*创建存储过程*/ CREATE PROCEDURE proc4(v_user_id int,v_price decimal(10,2),OUT v_msg varchar(64)) a:BEGIN /*保存当前余额*/ DECLARE v_available DECIMAL(10,2); /*保存版本号*/ DECLARE v_version INT DEFAULT 0; /*保存影响的行数*/ DECLARE v_update_count INT DEFAULT 0; /*1.查询余额，判断余额是否够*/ select a.available,a.version into v_available,v_version from t_funds a where a.user_id = v_user_id; if v_available<=v_price THEN SET v_msg='账户余额不足!'; /*退出*/ LEAVE a; END IF; /*模拟耗时5秒*/ SELECT sleep(5); /*2.余额减去price*/ SET v_available = v_available - v_price; /*3.更新余额*/ START TRANSACTION; UPDATE t_funds SET available = v_available WHERE user_id = v_user_id AND version = v_version; /*获取上面update影响行数*/ select ROW_COUNT() INTO v_update_count; IF v_update_count=1 THEN /*插入订单明细*/ INSERT INTO t_order (price) VALUES (v_price); SET v_msg='下单成功!'; /*提交事务*/ COMMIT; ELSE SET v_msg='下单失败,请重试!'; /*回滚事务*/ ROLLBACK; END IF; END $ /*结束符置为;*/ DELIMITER ;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
3.7 存储过程相关问题
3.7.1 存储过程和函数的区别
- 存储过程的关键字为procedure，返回值可以有多个，调用时用call，一般用于执行比较复杂的的过程体、更新、创建等语句。
- 函数的关键字为function，返回值必须有一个，调用用select，一般用于查询单个值并返回。
| 存储过程 | 函数 | |
|---|---|---|
| 返回值 | 可以有0个或者多个 | 必须有一个 |
| 关键字 | procedure | function |
| 调用方式 | call | select |
3.7.2 存储过程优化思路
- 尽量利用一些sql语句来替代一些小循环，例如聚合函数，求平均函数等。
- 中间结果存放于临时表，加索引。
- 少使用游标。 sql是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次读取。
- 事务越短越好。
- 使用 try-catch 处理错误异常。如果事务过多过长，或者隔离级别过高，都会造成并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极低。
- 查找语句尽量不要放在循环内。
四、触发器
在某个表发生更改时自动处理某些语句，这就是触发器。
触发器是MySQL响应delete 、update 、insert 、位于begin和end语句之间的一组语句而自动执行的一条MySQL语句。其他的语句不支持触发器。
触发器的使用场景：
- 可以通过数据库中的相关表实现级联更改。
- 实时监控某张表中的某个字段的更改而需要做出相应的处理。
触发器是一段能自动执行的程序，是一种特殊的存储过程，触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作时触发。诸如：update、insert、delete 这些操作的时候，系统会自动调用执行该表上对应的触发器。
4.1 创建触发器
在创建触发器时，需要给出 4 条语句（规则）：
- 唯一的触发器名；
- 触发器关联的表；
- 触发器应该响应的活动；
- 触发器何时执行(处理之前或者之后)
创建触发器语句示例：
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT
'Product added' INTO @info;
- 1
- 2
示例中创建了名为newproduct的新触发器。触发器可以在一个操作发生前或者发生后执行，这里AFTER INSERT是指此触发器在INSERT语句成功执行后执行。这个触发器还指定FOR EACH ROW， 因此代码对每个插入行都会执行。文本Product added将对每个插入的行显示一次。
注意事项：
- 触发器只有表才支持，视图，临时表都不支持触发器。
- 触发器是按照每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，因此，每个表最多支持六个触发器(Before Insert、After Insert、Before Update、After Update、Before Delete、After Delete)。
- 单一触发器不能与多个事件或多个表关联，所以，你需要一个对insert和update操作执行的触发器，则应该定义两个触发器。
- 触发器失败：如果before触发器失败，则MySQL将不执行请求的操作，此外，如果before触发器或者语句本身失败，MySQL则将不执行after触发器。
4.2 触发器的类别
4.2.1 INSERT触发器
在 insert 语句执行之前或者执行之后被执行的触发器。示例：
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT
NEW.order_num;
- 1
- 2
创建一个名为neworder的触发器，按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL 生成一个新的订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新order_num还没有生成。对于orders的每次插入使用这个触发器总是返回新的订单号。
4.2.2 DELETE触发器
在 delete 语句执行之前或者之后执行。示例：
CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num,order_date,cust_id) values
(OLD.order_num,OLD.order_date,OLD.cust_id);
END;
- 1
- 2
- 3
- 4
- 5
在任意订单被删除前将执行此触发器，它使用一条INSERT语句将OLD中的值(要被删除的订单) 保存到一个名为archive_orders的存档表中。
在这个触发器使用了BEGIN和END语句标记触发器体。这在此例子中并不是必须的，只是为了说明使用BEGIN END块的好处是触发器能够容纳多条SQL语句(在BEGIN END块中一条挨着一条)。
4.2.3 UPDATE触发器
在update语句执行之前或者之后执行。示例：
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET
NEW.vend_state = Upper(NEW.vemd_state);
- 1
- 2
保证州名缩写总是大写(不管UPFATE语句中是否给出了大写)，每次更新一行时，NEW.vend_state中的值(将用来更新表行的值)都用Upper(NEW.vend_state)替换。
4.3 总结
- 通常
before用于数据的验证和净化(为了保证插入表中的数据确实是需要的数据) ，也适用于update触发器。 - 创建触发器可能需要特殊的安全访问权限，但是触发器的执行是自动的，如果insert，update，或者delete语句能够执行，则相关的触发器也能执行。
- 触发器的一种非常有意义的使用就是创建审计跟踪。使用触发器，把更改(如果需要，甚至还有之前和之后的状态)记录到另外一个表是非常容易的。
- Mysql触发器不支持call语句，无法从触发器内调用存储过程。
最好不要使用触发器，原因：
- 触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程；
- 如果能够使用约束实现的，尽量不要使用触发器；
- 不要为不同的触发事件(Insert，Update和Delete)使用相同的触发器；
- 不要在触发器中使用事务型代码。
五、分区表
分区表是一个独立的逻辑表，其底层由多个物理子表组成。
当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。
分区非常适合在以下场景：
1）表非常大以至于无法全部放在内存中，或者只在表的最后部分有热点数据，其他均为历史数据。
2）分区表的数据更容易维护。（批量删除数据->清除整个分区）。
3）分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
4）可以使用分区表来避免某些特殊的瓶颈。
5）还可以备份和恢复独立的分区。
分区表也有一些限制：
1）一个表最多只能有1024个分区。
2）如果分区字段有主键或者唯一索引，那么所有主键列和唯一索引列都必须包含进来。
3）分区表中无法使用外键索引。
5.1 分区表原理
存储引擎管理分区的各个底层表和管理普通表一样，所有的底层表都必须使用相同的存储引擎，分区表的索引只是在各个底层表上各自加一个完全相同的索引。从存储引擎的角度，底层表和一个普通表没有任何不同。
- select
分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。 - insert
当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。 - delete
当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作。 - update
当更新一条记录时，分区层先打开并锁住所有的底层表，MySQL 先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。
虽然每个操作都会先打开并锁住所有的底层表，但这并不是分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，则会在分区层释放对应表锁，比如InnoDB，这个加锁和解锁的过程与普通InnoDB上的查询类似。
5.2 分区表的常见类型
5.2.1 Range分区
MySQL将会根据指定的拆分策略，把数据放在不同的表文件上。相当于在文件上被拆成了小块。但对外给客户的感觉还是一张表，是透明的。
示例：
CREATE TABLE tbl_new(
id INT NOT NULL PRIMARY KEY,
title VARCHAR(20) NOT NULL DEFAULT ''
)ENGINE MYISAM CHARSET utf8
PARTITION BY RANGE(id)(
PARTITION t0 VALUES LESS THAN(10),
PARTITION t1 VALUES LESS THAN(20),
PARTITION t2 VALUES LESS THAN(MAXVALUE)
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
0到10放在t0，10到20放在t1，大于20的放在t2。
如果要查询id在20以上的，那么会直接去t2分区查找。如果插入的记录的id在20以上，那么会插入到t2分区。
此时的物理文件：
可以看出，普通的InnoDB引擎的表是一个frm和一个ibd文件。分区之后的MyIasm引擎的表有一个frm和par文件，此外每个分区还有一个myi和myd文件。
frm：表的结构信息
par：表的分区信息
myi：表的索引信息
myd：表的数据信息
5.2.2 List分区
MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于：
LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值；
RANGE分区是从属于一个连续区间值的集合。
示例：
create table user (
uid int not null,
userName varchar(20),
area_id int
)engine myisam charset utf8
partition by list(area_id) (
partition bj values in (1),
partition sh values in (2),
partition gz values in (3),
partition sz values in (4)
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
5.3 分区表的缺点
- 1、NULL值会使分区过滤无效
分区的表达式的值可以是NULL；第一个分区是一个特殊分区，如果表达式的值为NULL或非法制，记录都会被存放到第一个分区。WHERE查询时即使看起来可以过滤到只有一个分区，但实际会检查两个分区，即第一个分区。最好是设置分区的列为NOT NULL。 - 2、分区列和索引列不匹配
如果定义的索引列和分区列不匹配，会导致索引无法进行分区过滤。
假设在列a上定义了索引，而在列b上进行分区。因为每个分区都有其独立的索引，所以扫描b上的索引就需要扫描每一个分区内对应的索引。 - 3、选择分区的成本可能很高
尤其是范围分区，对于回答“这一行属于哪个分区”、“这些符合查询条件的行在哪些分区”这样的问题的成本可能会非常高。其他的分区类型，比如键分区和哈希分区，就没有这样的问题。在批量插入时问题尤其严重。
5.4 分区的问题
打开和锁住所有底层表的成本可能很高。当查询访问分区表时，MySQL需要打开并锁住所有的底层表，这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，会影响到查询速度。可以通过批量操作来降低此类开销，比如批量插入、LOAD DATA INFILE和一次删除多行数据。维护分区的成本可能很高。例如重组分区，会先创建一个临时分区，然后将数据复制到其中，最后再删除原分区。所有分区必须使用相同的存储引擎。
六、索引
6.1 索引概述
- 索引是一种特殊的文件
InnoDB数据表上的索引是表空间的一个组成部分，它们包含着对数据表里所有记录的引用指针。 - 索引是一种数据结构
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为 B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。
通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是文件，它是要占据物理空间（存储在磁盘上）的。
在Mysql中，开发者会希望能查询速度快一些，经常用到的查询有：
- 按照id查询唯一一条记录
- 按照某些个字段查询对应的记录
- 查找某个范围的所有记录（between and）
- 对查询出来的结果排序
Mysql的索引的使用可以使上面的各种查询能够更快。
6.1.1 磁盘中数据的存取
以机械硬盘为例，熟悉几个概念：
扇区：磁盘存储的最小单位，扇区一般大小为512Byte。
磁盘块：文件系统与磁盘交互的的最小单位（计算机系统读写磁盘的最小单位），一个磁盘块由连续几个（ 2n）扇区组成，块一般大小一般为4KB。
磁盘读取数据：磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然不行。
Mysql中的页：Mysql中和磁盘交互的最小单位称为页，页是Mysql内部定义的一种数据结构，默认为16kb，相当于4个磁盘块，也就是说Mysql每次从磁盘中读取一次数据是16KB，要么不读取，要读取就是16KB，此值可以修改的。
假如对数据存储方式不做任何优化，直接将数据库中表的记录存储在磁盘中，假如某个表只有一个字段，为int类型，int占用4个byte，每个磁盘块可以存储1000条记录，100万的记录需要1000个磁盘块，如果我们需要从这100万记录中检索所需要的记录，需要读取1000个磁盘块的数据（需要1000次io），每次io需要9ms，那么1000次需要9000ms=9s，这种情况我们是无法接受的。
从上面的情况看，我们需要一种数据结构和算法：
- 需要一种数据存储结构：当从磁盘中检索数据的时候能，够减少磁盘的io次数，最好能够降低到一个稳定的常量值。
- 需要一种检索算法：当从磁盘中读取磁盘块的数据之后，这些块中可能包含多条记录，这些记录被加载到内存中，那么需要一种算法能够快速从内存多条记录中快速检索出目标数据。
6.1.2 常见的检索算法和数据结构
- 循环遍历查找
从一组无序的数据中查找目标数据，常见的方法是遍历查询，n条数据，时间复杂度为O(n)，最快需要1次，最坏的情况需要n次，查询效率不稳定。 - 二分法查找
用于在一个有序数组中快速定义某一个需要查找的数据。原理：先将一组无序的数据排序（升序或者降序）之后放在数组中，此处用升序来举例说明：用数组中间位置的数据A和需要查找的数据F对比，如果A=F，则结束查找；如果A<F，则将查找的范围缩小至数组中A数据右边的部分；如果A>F，则将查找范围缩小至数组中A数据左边的部分，继续按照上面的方法直到找到F为止。
二分法查找时间复杂度是:O(logN)(N为数据量)，100万数据查找最多只需要20次（220 =1048576 ）.
二分法查找数据的优点：定位数据非常快，前提是：目标数组是有序的。
如果我们将Mysql中表的数据以有序数组的方式存储在磁盘中，那么我们定位数据步骤是：
- 取出目标表的所有数据，存放在一个有序数组中。
- 如果目标表的数据量非常大，从磁盘中加载到内存中需要的内存也非常大。
步骤1取出所有数据耗费的io次数太多，步骤2耗费的内存空间太大，还有新增数据的时候，为了保证数组有序，插入数据会涉及到数组内部数据的移动，也是比较耗时的，因此这种方式不行。
- 链表
链表相当于在每个节点上增加一些指针，可以和前面或者后面的节点连接起来。
单链表：每个节点中有持有指向下一个节点的指针，只能按照一个方向遍历链表。示例：
class Node1{
private Object data;//存储数据
private Node1 nextNode;//指向下一个节点
}
- 1
- 2
- 3
- 4
双向链表：每个节点中两个指针，分别指向当前节点的上一个节点和下一个节点。示例：
class Node2{
private Object data;//存储数据
private Node1 prevNode;//指向上一个节点
private Node1 nextNode;//指向下一个节点
}
- 1
- 2
- 3
- 4
- 5
链表的优点：
- 可以快速定位到上一个或者下一个节点。
- 可以快速删除数据，只需改变指针的指向即可，这点比数组好。
链表的缺点：
- 无法向数组那样，通过下标随机访问数据。
- 查找数据需从第一个节点开始遍历，不利于数据的查找，查找时间和无序数据类似，需要全遍历，最差时间是O(N)。
- 二叉查找树
二叉树是每个结点最多有两个子树的树结构，通常子树被称作“左子树”和“右子树”。二叉树常被用于实现二叉查找树和二叉堆。二叉树的特性：
1、每个结点都包含一个元素以及n个子树，这里0≤n≤2。
2、左子树和右子树是有顺序的，次序不能任意颠倒，左子树的值要小于父结点，右子树的值要大于父结点。
数组[20,10,5,15,30,25,35]使用二叉查找树存储示例：
在该例子中，每个节点上面有两个指针，可以通过这2个指针快速访问左右子节点，检索任何一个数据最多只需要访问3个节点，相当于访问了3次数据，时间为O(logN)，查询速度还是比较快的。
但是如果我们插入数据是有序的，如[5,10,15,20,30,25,35]，那么结构就变成下面这样：
二叉树退化为了一个链表结构，查询数据最差就变为了O(N)。
二叉树的优缺点：
- 查询数据的效率不稳定，若树左右比较平衡的时，最差情况为O(logN)，如果插入数据是有序的，退化为了链表，查询时间变成了O(N)。
- 数据量大的情况下，会导致树的高度变高，如果每个节点对应磁盘的一个块来存储一条数据，需io次数大幅增加，显然用此结构来存储数据是不行的。
- 平衡二叉树（AVL树）
平衡二叉树是一种特殊的二叉树，所以他也满足前面说到的二叉查找树的两个特性，同时还有一个特性：
它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。
平衡二叉树相对于二叉树来说，树的左右比较平衡，不会出现二叉树那样退化成链表的情况，不管怎么插入数据，最终通过一些调整，都能够保证树左右高度相差不大于1。这样可以让查询速度比较稳定，查询中遍历节点控制在O(logN)范围内。
如果数据都存储在内存中，采用AVL树来存储，查询效率非常高。不过如果数据量大的时候，也会和二叉树一样，会导致树的高度变高，增加了io次数，显然用这种结构存储数据也不行。
- B-树
B-树在是平衡二叉树上进化来的，前面介绍的几种树，每个节点上面只有一个元素，而B-树节点中可以放多个元素，主要是为了降低树的高度。
一棵m阶的B-树有如下特性：
每个节点最多有m个孩子，m称为b树的阶。- 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
- 若根节点不是叶子节点，则至少有2个孩子。
- 所有叶子节点都在同一层，且不包含其它关键字信息。
- 每个非终端节点包含n个关键字（健值）信息。
- 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1。
- ki(i=1,…n)为关键字，且关键字升序排序。
- Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)。
为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。
B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个键将数据划分成的三个范围域，对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。
模拟查找关键字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操作是影响整个B-Tree查找效率的决定因素。B-树相对于avl树，通过在节点中增加节点内部数据的个数来减少磁盘的io操作。
Mysql是采用页方式来读写数据，每页是16KB，我们用B-树来存储Mysql的记录，每个节点对应Mysql中的一页（16KB），假如每行记录加上树节点中的1个指针占160Byte，那么每个节点可以存储1000（16KB/160byte）条数据，树的高度为3的节点大概可以存储（第一层1000+第二层10002+第三层10003 ）10亿条记录。从10亿记录中查找数据只需要3次io操作可以定位到目标数据所在的页，而页内部的数据又是有序的，然后将其加载到内存中用二分法查找，是非常快的。
可以看出使用B-树定位某个值还是很快的(10亿数据中3次io操作+内存中二分法)，但是也是有缺点的：B-树不利于范围查找，比如上图中我们需要查找[15,36]区间的数据，需要访问7个磁盘块（1/2/7/3/8/4/9），io次数又上去了，范围查找也是我们经常用到的，所以b-树也不太适合在磁盘中存储需要检索的数据。
- B+树
B+树结构图：
B+树的特征
- 每个结点至多有m个子女。
- 除根结点外,每个结点至少有[m/2]个子女，根结点至少有两个子女。
- 有k个子女的结点必有k个关键字。
- 父节点中持有访问子节点的指针。
- 父节点的关键字在子节点中都存在（如上面的1/20/35在每层都存在），要么是最小值，要么是最大值，如果节点中关键字是升序的方式，父节点的关键字是子节点的最小值。
- 最底层的节点是叶子节点。
- 除叶子节点之外，其他节点不保存数据，只保存关键字和指针。
- 叶子节点包含了所有数据的关键字以及data，叶子节点之间用链表连接起来，可以非常方便的支持范围查找。
B+树与B-树的几点不同
- b+树中一个节点如果有k个关键字，最多可以包含k个子节点（k个关键字对应k个指针）；而b-树对应k+1个子节点（多了一个指向子节点的指针）。
- b+树除叶子节点之外其他节点值存储关键字和指向子节点的指针，而b-树还存储了数据，这样同样大小情况下，b+树可以存储更多的关键字。
- b+树叶子节点中存储了所有关键字及data，并且多个节点用链表连接，从上图中看子节点中数据从左向右是有序的，这样快速可以支撑范围查找（先定位范围的最大值和最小值，然后子节点中依靠链表遍历范围数据）。
B-Tree和B+Tree该如何选择
- 1、B-Tree因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更高。
- 2、由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还需要遍历该关键字结点的根结点去搜索。
- 3、由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而B+Tree非叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。
- 数据库为什么使用B+树而不是B树
1、B树只适合随机检索，而B+树同时支持随机检索和顺序检索。
2、B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素。
3、B+树的查询效率更加稳定。
B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。
4、增删文件（节点）时，效率更高
因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。
6.1.3 常见的存储引擎和索引
InnoDB和MyISAM这两种引擎比较常用，这两种引擎中的索引都是使用b+树的结构来存储的。
- InnoDB中的索引
Innodb中有2种索引：主键索引（聚集索引）、辅助索引（非聚集索引）。
主键索引：每个表只有一个主键索引，b+树结构，叶子节点同时保存了主键的值和数据记录，其他节点只存储主键的值。
辅助索引：每个表可以有多个，b+树结构，叶子节点保存了索引字段的值以及主键的值，其他节点只存储索引指端的值。 - MyISAM引擎中的索引
B+树结构，MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
假设一个表存储了4行数据。其中Id作为主索引，Name作为辅助索引，聚簇索引和非聚簇索引的差异：
- InnoDB数据检索过程
如果需要搜索name='Ellison’的数据，需要2步：
- 先在辅助索引中检索到name='Ellison’的数据，获取id为14。
- 再到主键索引中检索id为14的记录。
辅助索引这个查询过程在Mysql中叫做回表。
- MyISAM数据检索过程
- 在索引中找到对应的关键字，获取关键字对应的记录的地址。
- 通过记录的地址查找到对应的数据记录。
- 两者对比
的最多的是innodb存储引擎，所以此处主要说一下innodb索引的情况，innodb中最好是采用主键查询，这样只需要一次索引，如果使用辅助索引检索，涉及到回表操作，比主键查询要耗时一些。
innodb中辅助索引为什么不像myisam那样存储记录的地址？表中的数据发生变更的时候，会影响其他记录地址的变化，如果辅助索引中记录数据的地址，此时会受影响，而主键的值一般是很少更新的，当页中的记录发生地址变更的时候，对辅助索引是没有影响的。
6.1.4 页结构
Mysql中页的结构，页是真正存储记录的地方，对应B+树中的一个节点，也是Mysql中读写数据的最小单位。
Mysql中页是innodb中存储数据的基本单位，也是mysql中管理数据的最小单位，和磁盘交互的时候都是以页来进行的，默认是16kb，mysql中采用b+树存储数据，页相当于b+树中的一个节点。页的结构：
每个Page都有通用的头和尾中部的内容根据Page的类型不同而发生变化。Page的头部详细信息：
Page的头部保存了两个指针，分别指向前一个Page和后一个Page，根据这两个指针我们很容易想象出Page链接起来就是一个双向链表的结构：
Page的主体内容中包含行数据和索引的存储，他们都位于Page的User Records部分，User Records占据Page的大部分空间，User Records由一条一条的Record组成。在一个Page内部，单链表的头尾由固定内容的两条记录来表示，字符串形式的"Infimum"代表开头，"Supremum"代表结尾，这两个用来代表开头结尾的Record存储在System Records的，Infinum、Supremum和User Records组成了一个单向链表结构。最初数据是按照插入的先后顺序排列的，但是随着新数据的插入和旧数据的删除，数据物理顺序会变得混乱，但他们依然通过链表的方式保持着逻辑上的先后顺序，如下图：
把User Record的组织形式和若干Page组合起来：
innodb为了快速查找记录，在页中定义了一个称之为page directory的目录槽（slots）,每个槽位占用两个字节（用于保存指向记录的地址），page directory中的多个slot组成了一个有序数组（可用于二分法快速定位记录，向下看），行记录被Page Directory逻辑的分成了多个块，块与块之间是有序的，能够加速记录的查找，如下图：
每个行记录的都有一个n_owned的区域（图中粉色区域），n_owned标识所属的slot这个这个块有多少条数据，伪记录Infimum的n_owned值总是1，记录Supremum的n_owned的取值范围为[1,8]，其他用户记录n_owned的取值范围[4,8]，并且只有每个块中最大的那条记录的n_owned才会有值，其他的用户记录的n_owned为0。
- 数据检索过程
在page中查询数据的时候，先通过b+树中查询方法定位到数据所在的页，然后将页内整体加载到内存中，通过二分法在page directory中检索数据，缩小范围，比如需要检索7，通过二分法查找到7位于slot2和slot3所指向的记录中间，然后从slot3指向的记录5开始向后向后一个个找，可以找到记录7，如果里面没有7，走到slot2向的记录8结束。
n_owned范围控制在[4,8]内，能保证每个slot管辖的范围内数据量控制在[4,8]个，能够加速目标数据的查找，当有数据插入的时候，page directory为了控制每个slot对应块中记录的个数（[4,8]），此时page directory中会对slot的数量进行调整。 - page的结构总结
- b+树中叶子页之间用双向链表连接的，能够实现范围查找。
- 页内部的记录之间是采用单向链表连接的，方便访问下一条记录。
- 为了加快页内部记录的查询，对页内记录上加了个有序的稀疏索引，叫页目录（page directory）。
整体上来说mysql中的索引用到了b+树，链表，二分法查找，做到了快速定位目标数据，快速范围查找。
6.1.5 索引数量*
任何标准表最多可以创建16个索引列，实际开发时一般不会超过5个。
6.2 聚集索引与非聚集索引
聚集索引和非聚集索引的根本区别是表中记录的物理顺序和索引的排列顺序是否一致。聚集索引的表中记录的物理顺序与索引的排列顺序一致。非聚集索引的记录的物理顺序和索引的顺序不一致。
6.2.1 聚集索引(主键索引)*
InnoDB使用表的主键构造主键索引树，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。
聚集索引的叶子节点就是整张表的行记录。InnoDB主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。
对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，它的值会随着数据的插入自增。
6.2.2 非聚集索引（辅助索引）*
也是b+树结构，不过有一点和聚集索引不同，非聚集索引叶子节点存储索引字段的值以及对应记录主键的值，其他节点只存储字段的值（索引字段）。
每个表可以有多个非聚集索引。
Mysql中非聚集索引分为：单列索引、复合索引和唯一索引。
Innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。
6.2.3 非聚簇索引一定会回表查询吗*
非主键索引，先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。
非聚簇索引未必会回表查询。如果查询语句所要求的字段全部命中了索引，那么就不必再进行回表查询。
举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。
避免回表的方式：被查询的字段均有索引。
6.2.4 如果一张表没有主键怎么办*
1、如果定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。
2、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。
3、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行数据的写入而主键递增。
6.2.5 MyISAM索引与InnoDB索引的区别*
InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。
6.3 五种索引
索引按逻辑功能可以划分为：普通索引、唯一索引、主键索引和全文索引，按作用字段个数可以划分为：单列索引和组合索引。单列索引一般就是普通索引或唯一索引，因此，我们可以认为有5种索引类型：普通索引、唯一索引、主键索引、组合索引和全文索引。
这5种类型索引的特点：
普通索引：仅加速查询；
唯一索引：加速查询 +列值唯一（可以有null）；
主键索引：加速查询 +列值唯一（不可以有null）+ 表中只有一个；
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并；
全文索引：对文本的内容进行分词，进行搜索。
6.3.1 主键索引*
主键一般都会设置主键索引，表示唯一 + 为空。表中至多只有一个主键索引。
创建主键索引的两种方式：
-- 在创建表的时候,指定主键索引1 create table table_primarykey ( id int primary key auto_increment , name varchar(20) ); -- 在创建表的时候,指定主键索引2(常用) create table table_primarykey ( id int auto_increment , name varchar(20), primary key(`id`) ); -- 先创建表,再增加主键索引 create table table_primarykey ( id int auto_increment , name varchar(20) ); alter table table_primarykey add primary key (id);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
6.2.2 唯一索引*
列值唯一。一个表允许多个列创建唯一索引。示例：
-- 建表时添加
create table table_uniquekey
( id int auto_increment ,
name varchar(20),
uniquekey(`name`)
);
-- 建表后添加索引
create unique index 索引名 on 表名(列名1,列名2……);
ALTER TABLE `table_name` ADD UNIQUE (`column_name`);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
6.2.3 普通索引*
基本的索引类型，值可以为空，且没有唯一性的限制。示例：
create table table_index
( id int primary key auto_increment ,
-- 在创建表的时候,指定普通索引
name varchar(20) ,
index index_name (name)
);
create table table_index
( id int primary key auto_increment ,
name varchar(20)
);
-- 建表后，添加普通索引
alter table table_index add index index_name (name);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
6.2.4 全文索引*
全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。
MyISAM支持，Innodb在Mysql5.6之后支持。
- 用like %就可以实现模糊匹配了，为什么还要全文索引
like %在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快N倍，速度不是一个数量级，但是全文索引可能存在精度问题。
全文索引创建示例：
--创建article表
CREATE TABLE article (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
--在title和content列上创建全文索引
FULLTEXT (title, content)
);
--给现有的article表的title和content字段创建全文索引
--索引名称为fulltext_article
ALTER TABLE articleADD FULLTEXT INDEX fulltext_article (title, content)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
6.2.5 组合索引*
多列值组成一个索引，专门用于组合搜索。
如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。例如：
假设存在组合索引(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即要想使用c2列进行查找，必需出现c1等于某值。
这就是最左匹配原则。简单来说，在两个列上的组合索引，有个前后顺序（c1，c2），在查询c1时可以使用该组合索引，在同时查询c1、c2时也可以使用该索引，但只查询c2时不能使用该索引。
组合索引创建示例：
CREATE TABLE `left_test` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `m_index` (`a`,`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
6.4 索引管理语句
6.4.1 创建索引
- 1、在执行CREATE TABLE时创建
CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 2、使用ALTER TABLE命令去增加索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
#添加主键
ALTER TABLE table_name ADD PRIMARY KEY (id);
ALTER TABLE table_name ADD CONSTRAINT pk_n PRIMARY KEY (id);
#添加外键
ALTER TABLE m ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES n(id);
- 1
- 2
- 3
- 4
- 5
- 6
ALTER TABLE用来创建普通索引、UNIQUE索引或主键索引。
其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
索引名index_name可自己命名。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。
如果字段是char、varchar类型，length可以小于字段实际长度，如果是blog、text等长文本类型，必须指定length。
- 3、使用CREATE INDEX命令创建
CREATE INDEX index_name ON table_name (column_list);
- 1
CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）。
6.4.2 删除索引
#根据索引名删除普通索引、唯一索引、全文索引
ALTER TABLE table_name DROP KEY index_name
#删除主键索引
ALTER TABLE table_name DROP PRIMARY KEY
#删除外键
ALTER TABLE m DROP FOREIGN KEY 'fk_id';
- 1
- 2
- 3
- 4
- 5
- 6
6.4.3 修改索引
可以先删除索引，再重建索引。
6.4.4 查看索引
-- 查看某个表中所有的索引
show index from 表名;
- 1
- 2
6.5 特殊的索引类型
6.5.1 前缀索引*
有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。
使用前缀索引时，难度在于：如何定义前缀截取的长度。
其中一种确定前缀长度的做法是：计算完整列的选择性，并使其前缀的选择性接近于完整列的选择性。
计算完整列的选择性示例：
-- 0.4283
select count(distinct city) / count(*) from city_demo;
- 1
- 2
可以在一个查询中针对不同前缀长度的选择性进行计算，这对于大表非常有用，在同一个查询中计算不同前缀长度的选择性示例：
-- 0.3367 0.4075 0.4208 0.4267
select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct left(city,6))/count(*) as sel6
from city_demo;
- 1
- 2
- 3
- 4
- 5
- 6
可以看见当索引前缀为6时的基数是0.4267，已经接近完整列选择性0.4283。因此在这个例子中，合适的前缀长度是6。创建索引示例：
alter table city_demo add key (city(6));
- 1
6.5.2 覆盖索引*
先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。
例如：select * from user_innodb where name = 'qingshan';
在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。
简单来说，如果一个索引包含（或覆盖）所有需要查询的字段的值，称为“覆盖索引”。
6.6 正确使用索引
- 通常说的这个查询走索引了是什么意思
当我们对某个字段的值进行某种检索的时候，如果这个检索过程中，我们能够快速定位到目标数据所在的页，有效的降低页的io操作，而不需要去扫描所有的数据页的时候，我们认为这种情况能够有效的利用索引，也称这个检索可以走索引。如果这个过程中不能够确定数据在那些页中，我们认为这种情况下索引对这个查询是无效的，此查询不走索引。
6.6.1 B+树中数据检索过程
- 唯一记录检索
所有的数据都是唯一的，查询105的记录，过程：
- 将P1页加载到内存。
- 在内存中采用二分法查找，可以确定105位于[100,150)中间，所以我们需要去加载100关联P4页。
- 将P4加载到内存中，采用二分法找到105的记录后退出。
- 查询某个值的所有记录
查询105的所有记录，过程：
- 将P1页加载到内存。
- 在内存中采用二分法查找，可以确定105位于[100,150)中间，100关联P4页。
- 将P4加载到内存中，采用二分法找到最有一个小于105的记录，即100，然后通过链表从100开始向后访问，找到所有的105记录，直到遇到第一个大于100的值为止。
- 范围查找
查询[55,150]所有记录，由于页和页之间是双向链表升序结构，页内部的数据是单项升序链表结构，所以只用找到范围的起始值所在的位置，然后通过依靠链表访问两个位置之间所有的数据即可，过程：
- 将P1页加载到内存。
- 内存中采用二分法找到55位于50关联的P3页中，150位于P5页中。
- 将P3加载到内存中，采用二分法找到第一个55的记录，然后通过链表结构继续向后访问P3中的60、67，当P3访问完毕之后，通过P3的nextpage指针访问下一页P4中所有记录，继续遍历P4中的所有记录，直到访问到P5中的150为止。
- 模糊匹配
1）查询以f 开头的所有记录，过程：
- 将P1数据加载到内存中。
- 在P1页的记录中采用二分法找到最后一个小于等于f的值，这个值是f，以及第一个大于f的，这个值是z，f指向叶节点P3，z指向叶节点P6，此时可以断定以f开头的记录可能存在于[P3,P6)这个范围的页内，即P3、P4、P5这三个页中。
加载P3这个页，在内部以二分法找到第一条f开头的记录，然后以链表方式继续向后访问P4、P5中的记录，即可以找到所有已f开头的数据。
2）查询包含f 的记录，写法是%f%。此时通过P1页中的记录是无法判断包含f的记录在那些页的，只能通过io的方式加载所有叶子节点，并且遍历所有记录进行过滤，才可以找到包含f的记录。所以如果使用了 %值% 这种方式，索引对查询是无效的。
- 最左匹配原则
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的。比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的搜索方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
下图中是3个字段(a,b,c)的联合索引，索引中数据的顺序是以 a asc,b asc,c asc 这种排序方式存储在节点中的，索引先以a字段升序，如果a相同的时候，以b字段升序，b相同的时候，以c字段升序。
1）查询a=1的记录。由于页中的记录是以 a asc,b asc,c asc 这种排序方式存储的，所以a字段是有序的，可以通过二分法快速检索到，过程：
- 将P1加载到内存中。
- 在内存中对P1中的记录采用二分法找，可以确定a=1的记录位于{1,1,1}和{1,5,1}关联的范围内，这两个值子节点分别是P2、P4。
- 加载叶子节点P2，在P2中采用二分法快速找到第一条a=1的记录，然后通过链表向下一条及下一页开始检索，直到在P4中找到第一个不满足a=1的记录为止。
2）查询a=1 and b=5的记录。方法和上面的一样，可以确定a=1 and b=5的记录位于{1,1,1}和{1,5,1}关联的范围内，查找过程和a=1查找步骤类似。
3）查询b=1的记录/按照c的值查询/按照b和c一起查。这种情况通过P1页中的记录，是无法判断b=1的记录在那些页中的，只能加锁索引树所有叶子节点，对所有记录进行遍历，然后进行过滤，此时索引是无效的。
4）按照[a,c]两个字段查询。这种只能利用到索引中的a字段了，通过a确定索引范围，然后加载a关联的所有记录，再对c的值进行过滤。
5）查询a=1 and b>=0 and c=1的记录。这种情况只能先确定a=1 and b>=0所在页的范围，然后对这个范围的所有页进行遍历，c字段在这个查询的过程中，是无法确定c的数据在哪些页的，此时我们称c是不走索引的，只有a、b能够有效的确定索引页的范围。
类似这种的还有>、<、between and，多字段索引的情况下，Mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
6.6.2 索引区分度*
2个有序数组：[1,2,3,4,5,6,7,8,8,9,10]、[1,1,1,1,1,8,8,8,8,8]。如果用二分法检索值为8的所有记录，过程为：先使用二分法找到最后一个小于8的记录，然后沿着这条记录向后获取下一个记录，和8对比，知道遇到第一个大于8的数字结束，或者到达数组末尾结束。
采用上面这种方法找到8的记录，第一个数组中更快的一些。因为第二个数组中含有8的比例更多的，需要访问以及匹配的次数更多一些。
这里就涉及到数据的区分度问题：索引区分度 = count(distint 记录) / count(记录)。
当索引区分度高的时候，检索数据更快一些，索引区分度太低，说明重复的数据比较多，检索的时候需要访问更多的记录才能够找到所有目标数据。当索引区分度低的时候，基本上接近于全索引数据的扫描了，此时查询速度是比较慢的。第一个数组索引区分度为1，第二个区分度为0.2，所以第一个检索更快的一些。
所以创建索引的时候，尽量选择区分度高的列作为索引。
6.6.3 正确使用索引
- between and 范围检索
如果检索的字段上有索引的话，效率比较高，如：
-- id为主键，有主键索引
select count(*) from test1 where id between 100 and 110;
- 1
- 2
但是如果范围太大，跨度的page也太多，速度也会比较慢。因此，使用between and的时候，区间跨度不要太大。
- 多个索引时查询如何走
比如在两个字段上建了索引，sql为：
select * from test1 where name='zhangsan' and sex=2;
- 1
实际的索引使用情况为：当多个条件中有索引的时候，并且关系是and的时候，会走索引区分度高的。
- 模糊查询
-- 走name字段上的索引
select count(*) from test1 a where a.name like 'zhangsan%';
-- 不走name字段上的索引
select count(*) from test1 a where a.name like '%zhangsan%';
- 1
- 2
- 3
- 4
- 回表
当需要查询的数据在索引树中不存在的时候，需要再次到聚集索引中去获取，这个过程叫做回表。示例：
select * from test1 where name='zhangsan';
- 1
查询*，由于name列所在的索引中只有 name、id 两个列的值，不包含 sex、email ，所以上面过程：
- 走name索引检索 javacode3500000 对应的记录，取出id为 3500000。
- 在主键索引中检索出 id=3500000 的记录，获取所有字段的值。
- 索引覆盖
查询中采用的索引树中包含了查询所需要的所有字段的值，不需要再去聚集索引检索数据，这种叫索引覆盖。示例：
select id,name from test1 where name='zhangsan';
- 1
name字段上有索引(如idx1)，id为主键，所以idx1索引树叶子节点中包含了name、id的值，这个查询只用走idx1这一个索引就可以了。
- 索引下推
Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式，ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。如：查询name以javacode35开头的，性别为1的记录数：
select count(id) from test1 a where name like 'javacode35%' and sex = 1;
- 1
常规过程：
- 走name索引检索出以javacode35的第一条记录，得到记录的id。
- 利用id去主键索引中查询出这条记录R1。
- 判断R1中的sex是否为1，然后重复上面的操作，直到找到所有记录为止。
该过程中需要走name索引以及需要回表操作。
如果采用ICP的方式，可以创建一个(name,sex)的组合索引，查询过程：
- 走(name,sex)索引检索出以javacode35的第一条记录，可以得到(name,sex,id)，记做R1
- 判断R1.sex是否为1，然后重复上面的操作，知道找到所有记录为止。
这个过程中不需要回表操作，通过索引的数据就可以完成整个条件的过滤，速度比上面的更快一些。
- 数字使字符串类索引失效
假如name字段为字符串类型，id数字类型，两个字段上均有索引。则：
-- 走索引
select * from test1 where name = '1';
-- 不走索引
select * from test1 where name = 1;
- 1
- 2
- 3
- 4
第2条sql不走索引的原因是：字符串和数字比较的时候，会将字符串强制转换为数字，然后进行比较，所以第二个查询变成了全表扫描，只能取出每条数据，将name转换为数字和1进行比较。
-- 走索引
select * from test1 where id = '4000000';
-- 走索引
select * from test1 where id = 4000000;
- 1
- 2
- 3
- 4
- 函数使索引无效
-- 走索引
select a.name+1 from test1 a where a.name = 'javacode1';
-- 不走索引
select * from test1 a where concat(a.name,'1') = 'javacode11';
- 1
- 2
- 3
- 4
第二个sql使用了函数之后，name所在的索引树是无法快速定位需要查找的数据所在的页的，只能将所有页的记录加载到内存中，然后对每条数据使用函数进行计算之后再进行条件判断，此时索引无效了，变成了全表数据扫描。
- 运算符使索引无效
-- 走索引
select * from test1 a where id = 2 - 1;
-- 不走索引
select * from test1 a where id+1 = 2;
- 1
- 2
- 3
- 4
第二个使用运算符，id所在的索引树是无法快速定位需要查找的数据所在的页的，只能将所有页的记录加载到内存中，然后对每条数据的id进行计算之后再判断是否等于1，此时索引无效了，变成了全表数据扫描。
- 使用索引优化排序
假如有个订单表t_order(id,user_id,addtime,price)，经常会查询某个用户的订单，并且按照addtime升序排序，应该怎么创建索引呢？
在user_id上创建索引，数据检索的过程：
- 走user_id索引，找到记录的的id。
- 通过id在主键索引中回表检索出整条数据。
- 重复上面的操作，获取所有目标记录。
- 在内存中对目标记录按照addtime进行排序。
数据量非常大的时候，排序还是比较慢的。如果查询出来的数据刚好是排好序的，那就会好很多。在Mysql中b+树数据的结构，记录是按照索引的值排序组成的链表，如果将user_id和addtime放在一起组成联合索引(user_id,addtime)，这样通过user_id检索出来的数据自然就是按照addtime排好序的，这样直接少了一步排序操作，效率更好，如果需addtime降序，只需要将结果翻转一下就可以了。
6.7 最左匹配原则
假设存在组合索引(c1,c2)，相关查询语句使用索引情况：
-- 能够使用索引
select * from t1 where c1=1 and c2=2;
select * from t1 where c1=1;
-- 不能使用索引
select * from t1 where c2=2
- 1
- 2
- 3
- 4
- 5
简单来说，在两个列上的组合索引，有个前后顺序（c1，c2），在查询c1时可以使用该组合索引，在同时查询c1、c2时也可以使用该索引，但只查询c2时不能使用该索引。
在最左前缀匹配原则中，Mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
例如组合索引（a,b,c），组合索引的生效原则是：从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。
比如：
- where a=3 and b=4 and c=5 … 这种三个索引顺序使用中间没有断点，全部发挥作用；
- where a=3 and c=5… 这种情况下b就是断点，a发挥了效果，c没有效果
- where b=3 and c=4… 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
- where b=45 and a=3 and c=5 … 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关。
6.8 设计索引的原则
6.8.1 应该创建索引的情况*
- 1、
最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。即：为经常需要排序(order by)、分组(group by)和关联(join)操作的字段建立索引；为常用作为查询条件(where)的字段建立索引。 - 2、
尽量使用高选择度索引。即某个字段的区分度较高，索引的效果越好。 - 3、
使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。 - 4、
利用最左匹配原则（使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则）。 - 5、
对于InnoDB存储引擎的表，尽量手工指定主键（即使用主键索引）。按照主键或者内部列(开发者未指定主键时系统自动生成的主键)进行的访问是最快的，所以InnoDB表尽量自己指定主键。 - 6、
定义有外键的数据列一定要建立索引。 - 7、使用联合索引时，
取值离散大的字段（变量各个取值之间的差异程度）的列放到联合索引的前面。 - 8、表的主键、外键必须有索引。
6.8.2 不应该创建索引的情况*
- 1、
不是所有的表都需要键索引
常见的配置表等数据量很小的表，除了主键之外，再创建索引没有太大的意义。 - 2、
不要过度索引
因为额外的索引要占磁盘空间，并降低写操作的性能。因此，只需保持所需的索引有利于查询优化。实际开发时，一般一个表至多建5个索引即可。 - 3、
谨慎选择低选择度索引
对于选择性低并且数据分布均衡的列，因为过滤的结果集大，创建索引的效果通常不好，如性别字段。但如果列的选择性低并且数据分布不均衡，比如男女比例为99:1，那么此时创建对于查询条件为‘女’的过滤结果集就比较小，索引的效率较高。 - 4、
尽量的扩展索引（组合索引），不要新建索引
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。 - 5、
更新频繁字段不适合创建索引 - 6、和适合建索引的条件相反的情况，均不适合键索引。如：where 条件中用不到的字段不适合建立索引。
6.9 索引生效和失效条件
6.9.1 索引生效条件*
- 1、匹配全指
对索引中所有列都指定具体值，示例：
select * from rental
where inventory_id = 373 and customer_id = 343;
- 1
- 2
- 2、匹配值的范围查询
对索引的值能够进行范围查询，示例：
select * from tental
where customer_id >= 373 and customer_id < 400;
- 1
- 2
- 3、匹配最左前缀
仅仅使用索引中的最左列进行查找。 - 4、仅仅对索引进行查询
当查询的列都在索引的字段中时，查询的效率更高，因为不用回表。 - 5、匹配列前缀
仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。比如title字段上有索引：
select title from film_text
where title like 'AFRICAN%';
- 1
- 2
- 6、is null走索引
总的来说，MySQL只对以下操作符才使用索引：
<
<=
=
>
>=
between
in
以及某些时候的like(不以通配符%或_开头的情形)。
6.9.2 索引失效条件*
- 1、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
- 2、不满足最左原则，则不会使用复合索引。
- 3、like查询是以%开头。
- 4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则会出现隐式转换，从而不使用索引。
- 5、如果Mysql估计使用全表扫描要比使用索引快,则不使用索引。
- 6、
索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）。 - 7、判断索引列是否不等于某个值时。
6.10 索引的相关问题
6.10.1 索引的基本原理
索引用来快速地寻找那些具有特定值的数据。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理很简单，就是把无序的数据变成有序的查询：
- 把创建了索引的列的内容进行排序；
- 对排序结果生成倒排表；
- 在倒排表内容上拼上数据地址链；
- 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。
6.10.2 索引的优缺点
- 索引的优点
可以大大加快数据的检索速度，提高系统的性能，这也是创建索引的最主要的原因。 - 索引的缺点
- 时间方面：
创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；- 空间方面：
索引需要占物理空间。
6.10.3 百万级别或以上的数据如何删除
关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件，所以当我们对数据的增加、修改、删除，都会产生额外的对索引文件的操作，这些操作需要消耗额外的IO，会降低增/改/删的执行效率。所以，在删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
因此，要删除有很多索引的表中数据的建议：
- 所以
想要删除百万数据的时候可以先删除索引；- 然后
删除其中无用数据；删除完成后重新创建索引。
与直接删除相比，绝对是要快速很多；并且万一删除中断,一切删除会回滚。
6.10.4 查看索引使用情况
如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引。
命令示例：
show status like 'Handler_read%';
- 1
结果示例：
6.10.5 索引条件下推
索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询。不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件。
当使用索引条件下推优化时，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器（即存储引擎做了一部数据筛选的断工作）。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
开启ICP：
set optimizer_switch='index_condition_pushdown=on';
- 1
6.10.6 主键使用自增ID还是UUID*
推荐使用自增ID，不要使用UUID。
因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。
总之，在数据量大一些的情况下，用自增主键性能会好一些。
1、如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。为了存储和查询性能应该使用自增长id做主键。
2、对于InnoDB的主索引，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，此时不适合使用UUID做物理主键，可以把它作为逻辑主键，物理理主键依然使用自增ID。为了全局的唯一性，应该用uuid做索引关联其他表或做外键。
6.10.7 主键索引和唯一索引的区别
主键不能有空值（非空+唯一），唯一索引可以为空。
主键可以是其他表的外键，唯一索引不可以。
一个表只能有一个主键，唯一索引可以多个。
主键索引是聚簇索引；唯一索引是非聚簇索引。
6.10.8 唯一索引比普通索引快吗
唯一索引不一定比普通索引快，还可能慢。
- 查询
在未使用 limit 1 的情况下，在匹配到一条数据后，唯一索引即返回。普通索引会继续匹配下一条数据，发现不匹配后返回。如此看来唯一索引少了一次匹配，但实际上这个消耗微乎其微。 - 更新
普通索引将记录放到change buffer中语句就执行完毕了。而对唯一索引而言，它必须要校验唯一性。因此，必须将数据页读入内存确定没有冲突，然后才能继续操作。对于写多读少的情况，普通索引利用 change buffer 有效减少了对磁盘的访问次数，因此普通索引性能要高于唯一索引。
6.10.9 Hash索引和B+树索引有什么区别
Hash索引底层就是Hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树,对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。
Hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围查询。
Hash索引不支持使用索引进行排序，原理同上。
Hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
Hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
Hash索引虽然在等值查询上较快，但是不稳定，性能不可预测。当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。
因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度，而不需要使用Hash索引。
6.10.10 为什么MySQL索引不使用红黑树*
B+树是一种多路平衡查找树，它相对于红黑树来说，有以下几个优势：
- 1、磁盘IO次数少
B+树的非叶子节点只存储key信息，而不存储data信息，这使得B+树的磁盘IO次数比红黑树少很多。在B+树中，每个节点都可以存储很多key，这样可以减少磁盘IO次数，提高查询效率。 - 2、支持范围查询
B+树的叶子节点是按照key排序的，并且相邻的叶子节点之间有指针相连，这使得B+树可以很方便地支持范围查询。而红黑树则需要遍历整棵树才能找到符合条件的节点。 - 3、适合磁盘存储
B+树的节点大小是固定的，这使得B+树很适合磁盘存储。而红黑树的节点大小是不固定的，这会导致在磁盘存储时，需要频繁地分配和释放内存。
6.10.11 B树和B+树*
- B树
B-tree利用了磁盘块的特性进行构建的树。每个磁盘块一个节点，每个节点包含了很多关键字。B树的节点关键字增多后树的层级比原来的二叉树少了了，减少数据查找的次数和复杂度。
B-tree巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页（每页为4K），这样每个节点只需要一次I/O就可以完全载入。
B-tree 的数据可以存在任何节点中。 - B+树
B+tree 是 B-tree 的变种，B+tree 数据只存储在叶子节点中。这样在B树的基础上每个节点存储的关键字数更更多，树的层级更更少所以查询数据更更快，所有指关键字指针都存在叶子节点，所以每次查找的次数都相同所以查询速度更更稳定。
6.10.12 B树和B+树的区别，为什么MySQL要使用B+树*
- B 树
1）关键字集合分布在整颗树中；
2）任何一个关键字出现且只出现在一个结点中；
3）搜索有可能在非叶子结点结束；
4）其搜索性能等价于在关键字全集内做一次二分查找。 - B+树
1）有 n 棵子树的非叶子结点中含有 n 个关键字（b 树是 n-1 个），这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b 树是每个关键字都保存数据）；
2）所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接；
3）所有的非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字；
4）通常在 b+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点；
5）同一个数字会在不同节点中重复出现，根节点的最大元素就是 b+树的最大元素。 - B+树相比于 B 树的查询优势
1）B+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；
2）B+树查询必须查找到叶子节点，B 树只要匹配到即可不用管元素位置，因此B+树查找更稳定（并不慢）；
3）对于范围查找来说，B+树只需遍历叶子节点链表即可，B 树却需要重复地中序遍历。
6.10.13 为什么Mysql用B+树做索引，不用B-树或平衡二叉树
- 平衡二叉树
1.非叶子节点最多拥有两个子节点。
2.非叶子节值大于左边子节点、小于右边子节点。
3.树的左右两边的层级数相差不会大于1。
4.没有值相等重复的节点。
- B-树
B-树和平衡二叉树稍有不同的是B-树属于多叉树又名平衡多路查找树（查找路径不只两个）：
1.在一个节点中，存放着数据（包括key和data）以及指针，且相互间隔。
2.同一个节点，key增序。
3.一个节点最左边的指针不为空，则它指定的节点左右的key小于最左边的key。右边同理。中间的指针指向的节点的key位于相邻两个key的中间。
4.B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。
5.每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
- B+树
1.内节点不存储data，只存储key和指针；叶子节点不存储指针，存key和data。
2.内节点和叶子节点大小不同。
3.每个节点的指针上限为2d而不是2d+1。
- 平衡二叉树的问题
为了解决二叉树数据有序时出现的线性插入树太深问题，树的深度会明显降低，虽然极大提高性能，但是当数据量很大时，一般mysql中一张表达到3-5百万条数据是很普遍，因此平衡二叉树的深度会非常大，mysql读取时会消耗大量IO。
不仅如此，计算机从磁盘读取数据时以页(4KB)为单位的，每次读取4096byte。平衡二叉树每个节点只保存了一个关键字（如int即4byte），浪费了4092byte，极大的浪费了读取空间。 - B-树相对于平衡二叉树的优点
平衡二叉树基本都是存储在内存中才会使用的数据结构。
在大规模数据存储的时候，平衡二叉树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。
我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。
磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定。
所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B-树可以有多个子女，从几十到上千，可以降低树的高度，解决了平衡二叉树读取消耗大量内存空间的问题。
