什么是回表？_回表的定义和用法[通俗易懂]

大家好，欢迎来到IT知识分享网。

当需要查询的数据在索引树中不存在的时候，需要再次到聚集索引中去获取，这 个过程叫做回表

使用INNODB存储引擎的索引都维护了一个主键索引。

product(id,name,price)   id为主键
# 为product表中的name列创建了一个索引。
create index product_name_index on product(`name`);
​
select * from product where id = 1; # 直接查询主键索引
select * from product where name = 'test';  # 需要回表

name辅助索引中叶子节点仅保存了主键的值。因此根据name查找的数据分两步：

1. 在辅助索引中查找name为test的id

2. 根据id到主键索引中查找data，取data中的所有的列。

什么是索引覆盖？

select id from product where name = 'test'; # 不需要回表

如上语句则不需要回表。因为在根据辅助索引查找name为test的数据时，就能够得到id。称为索引覆盖

最左匹配原则

当b+树的数据项是复合的数据结构，如(name,age,sex)的时候 .b+树是按照从 左到右的顺序来建⽴搜索树的

# 一个复合索引，该索引有3个列构成
create index idx on test1(name,age,sex);
​
# 能够命中索引
select * from test1 where name = '' and age = 0 and sex = 1;
# 不能够命中idx索引
select * from test1 where age = 0 and sex = 1;
# 可以命中idx索引
select * from test1 where name = '';
select * from test1 where name = '' and sex = 1;

索引区分度

创建索引的列要选择重复性小的列作为索引列。

索引覆盖

查询中采用的索引树中包含了查询所需要的所有字段的值，不需要再去聚集索引 检索数据，这种叫索引覆盖

select id,name from test1 where name='javacode3500000';

name对应idx1索引， id为主键，所以idx1索引树叶⼦节点中包含了name、 id的 值，这个查询只⽤⾛idx1这⼀个索引就可以了，如果select后⾯使⽤，还需要⼀ 次回表获取sex、 email的值。 所以写sql的时候，尽量避免使⽤， *可能会多⼀次回表操作，需要看⼀下是否 可以使⽤索引覆盖来实现，效率更⾼⼀些。

索引下推

简称ICP， Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储 引擎层使用索引过滤数据的已种优化放式， ICP可以减少存储引擎访问回表的次数以及MySQL服务器访问存储引擎的次数。

mysql> select count(id) from test1 a where name like 'javacode35%' and sex = 1;

回表的方式：
1. 走name索引检索出以javacode35的第一条记录，得到记录的id 
2. 利用id去主键索引中查询出这条记录R1 
3. 判断R1中的sex是否为1，然后重复上面的操作，直到找到所有记录为止。
上面的过程中需要走name索引以及需要回表操作。
​
如果采用ICP的方式，我们可以这么做，创建一个(name,sex)的组合索引，查询过程如
下：
1. 走(name,sex)索引检索出以javacode35的第一条记录，可以得到(name,sex,id)，
记做R1 
2. 判断R1.sex是否为1，然后重复上面的操作，直到找到所有记录为止
这个过程中不需要回表操作了，通过索引的数据就可以完成整个条件的过滤，速
度比上面的更快一些。

存储过程

一组预编译好的sql语句集合，理解成批处理语句。

好处：

• 提高代码的重用性

• 简化操作

• 减少编译次数并且减少和数据库服务器连接的次数，提高了效率。

create procedure 过程名([参数 参数类型])
begin  # 等价于 {
 # 代码块
end    # 等价于 }

参数类型的分类

• in: 可以理解为调用过程时，传递给过程的形参

• out： 可以理解为调用过程后，过程返回的结果

• inout：该参数既可以作为输如也可以作为输出，也就是说该参数需要在调用的 时候传入值，又可以作为返回值。

执行过程

call 过程名称(实参)

删除存储过程

drop procedure [if exists] 存储过程名称; 

存储过程只能已个个删除，不能批量删除。

带in参数的存储过程

创建存储过程：
/*设置结束符为$*/
DELIMITER $
/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc2;
/*创建存储过程proc2*/
CREATE PROCEDURE proc2(id int,age int,in name varchar(16))
BEGIN
INSERT INTO t_user VALUES (id,age,name);
END $
/*将结束符置为;*/
DELIMITER ;
/*调用存储过程：*/
/*创建了3个自定义变量*/
SELECT @id:=3,@age:=56,@name:='张学友';
/*调用存储过程*/
CALL proc2(@id,@age,@name);

带out参数的存储过程

/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc3;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc3*/
CREATE PROCEDURE proc3(id int,age int,in name varchar(16),out
user_count int,out max_id INT)
BEGIN
INSERT INTO t_user VALUES (id,age,name);
/*查询出t_user表的记录，放⼊user_count中,max_id⽤来存储t_user中最⼩的id*/
SELECT COUNT(*),max(id) into user_count,max_id from t_user;
END $

调用存储过程

/*创建了3个⾃定义变量*/
SELECT @id:=4,@age:=55,@name:='郭富城';
/*调⽤存储过程*/
CALL proc3(@id,@age,@name,@user_count,@max_id);
​
/*查询过程返回的结果*/
select @user_count,@max_id;

带inout参数的存储过程

/*如果存储过程存在则删除*/
DROP PROCEDURE IF EXISTS proc4;
/*设置结束符为$*/
DELIMITER $
/*创建存储过程proc4*/
CREATE PROCEDURE proc4(INOUT a int,INOUT b int)
BEGIN
SET a = a*2;
select b*2 into b;
END $
/*将结束符置为;*/
DELIMITER ;
/*调用存储过程：*/
/*创建了2个⾃定义变量*/
set @a=10,@b:=20;
/*调用存储过程*/
CALL proc4(@a,@b);
​
mysql> SELECT @a,@b;
+------+------+
| @a | @b |
+------+------+
| 20 | 40 |
+------+------+
1 row in set (0.00 sec)

函数

一组预编译好的sql语句集合，理解成批处理语句。类似于java中的方法，但是必须有返回 值。

create function 函数名(参数名称 参数类型)
returns 返回值类型
begin
函数体
end
 参数是可选的。
 返回值是必须的。

/*删除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 ;

存储过程和函数的区别

存储过程的关键字为procedure，返回值可以有多个，调用时用call关键字， 一般用于执行比较复杂的的过程体、更新、创建等语句。 函数的关键字为function， 返回值必须有一个，调用时使用select关键字，一般用于查询单个值并返 回 。

系统函数：

• max

• min

• sum

• count

• avg

•