MySQL高级

索引

索引的概述

MYSQL官方对索引的定义为:索( index)是帮助MySQL高效获取数据的数据结构(有序)，在数据之外，数库系统还维护者满足特定查城算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示
意图所示:

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址(注意逻辑上相邻的记录在磁盘上也并不是一定物理相的 )。为了如快CO2的查找，可以维护一个右边所示的二又查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二又查找快速
获取到相应数据。

般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工

索引的优劣势

优势：
1. 类似书籍的目录索引，提高数据的检索效率、降低数据库的IO成本
2. 通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗
劣势：
1. 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2. 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MVSOL 不仅要保存教据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引的结构

索是在MYSOL的存引层中实现的，而不是在服务器层实现的。所以每种存储引警的索引都不一定完全相同，也不是所有的存储引警都支:所有的索引类型的。MySQL目前提供了以下4种索引:

TREE 索引 : 最常见的索引类型，大部分索引都支持 B 树索引。
ASH 索: 只有Memory引l警支持，使用场景简单。
R-tree 索引(空间索):空间索是MISAM擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍
Full-text ( 全文索引): 全文索引也是MylSAM的一个特殊索类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

索引	InnDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 树索引，统称为索引。

B-Tree结构

BTree又叫多路平衡搜索树，一颗m又的BTree特性如下

树中每个节点最多包含m个孩子
除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子
若根节点不是叶子节点，则至少有两个孩子。
所有的叶子节点都在同一层
每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

以5双BTree为例，key的数量: 公式推导[cel(m/2)-1]= n = m-1，所以 2<= n<=4，当n>4时，中间节点分裂到父节点，两边节点分裂.插入C N G A H E K Q M F W L T Z D P R X Y S 数据为例。

演变如下：

插入前四个字母
插入H,n>4中间元素G字母向上分裂到新的节点
插入E、K、Q不需要分裂
插入M，中间元素M字母向上分裂到父节点G
插入F，W，L，T不需要分裂
插入Z，中间元素T向上分裂到父节点中
插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂
最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分后父节点DGMT的n>5，中间节点M向上分裂

到此，该BTREE树就已经构建完成了，BTREE树和二又树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE的层级结构比二又树小，因此搜索速度快。

B+Tree结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
所有的非叶子节点都可以看作是key的索引部分

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定

MySQL中的B+Tree

MySa索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
MySQL中的 B+Tree 索引结构示意图:

索引的分类

单值索引 : 即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引 : 索引列的值必须唯一，但允许有空值
复合索引 : 即一个索引包含多个列

索引的语法

创建索引

语法：

create index [indexName] on [tableName(tableColumn)]

实例：

查看索引

语法：

show index from [tableName]

案例：

删除索引

语法：

drop index [indexName] on [tableName]

案例：

alter（ALTER）命令

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
```
alter  table  tb_name  add  primary  key(column_list); 
```
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
```
alter  table  tb_name  add  unique index_name(column_list);
```

添加普通索引，索引值可以出现多次。

alter  table  tb_name  add  index index_name(column_list);

该语句指定了索引为FULLTEXT，用于全文索引

alter  table  tb_name  add  fulltext  index_name(column_list);

索引的设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

对查询频次较高，且数据量比较大的表建立索引。
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
使用唯一索引，区分度越高，使用索引的效率越高。
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

创建复合索引:

	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

视图

视图的概述

视图(View)是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，通俗的讲，视图就是一条SELECT语句执行后返回的结果集，所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。
1. 简单
  
  使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过好的复合条件的结果集。
2. 安全
  
  使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3. 数据独立
  
  数据独立:一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响，源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

视图的基本语法

创建视图

语法：

create view [viewName] as [sql_for_select];

案例：

修改视图

语法：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

选项 : 
	WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。

	LOCAL ： 只要满足本视图的条件就可以更新。
	CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

示例 , 创建city_country_view视图 , 执行如下SQL :

create or replace view city_country_view 
as 
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;

查看视图

从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。

同样，在使用 SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。

如果需要查询某个视图的定义，可以使用 SHOW CREATE VIEW 命令进行查看：

语法：
```
show create view [viewName];
```

删除视图

语法

drop view [viewName]; 
or
drop view if exists [viewName];

案例：

存储过程和存储函数

存储过程和存储函数的概述

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

函数：是一个有返回值的过程；

过程：是一个没有返回值的函数；

存储过程的操作语法

创建存储过程

语法：

create procedure [procedureName]()
begin
[...SQL...]
end;

案例：
提示：

由于我们的创建procedure语句中含有两个[;]号，在MySQL中[;]好表示语句的结束，所以MySQL自带的客户端MySQLlCli识别不了所以会报错 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 。这里我们有两个解决方案
1. 使用市面上的SQL工具
  1. Navicat
  2. DBeaver
  3. ...
2. 使用其他的符号替代我们的[;]号，这里可以使用我们MySQL自带语句DELIMITER
  1. 该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。
‍

调用存储过程

语法
```
call [procedureName()];
```
案例：

查看存储过程

查询数据中所有的存储过程

MySQL5.0

select name from mysql.proc where db='[your_use_database]';

MySQL8.0

select 
	ROUTINE_NAME
from 
	information_schema.ROUTINES
where 
	ROUTINE_TYPE = 'PROCEDURE'
and 
	ROUTINE_SCHEMA = 'your_database_name';

查询存储过程的状态信息(只适用MySQL5)
```
show procedure status;
```
查询某个存储过程的定义
1. 语法：
```
show create procedure [procedureName] \G;
```
2. 案例：

删除存储过程

语法：
```
drop procedure [procedureName];
```
案例：

语法

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。

变量

declare：通过declare可以定义一个局部变量，该变量范围只作用于begin...end块中。

语法：

create procedure [procedureName()]
begin
	declare [valueData] int default 10;
	select concat("valueData的值为:",valueData);
end;

案例：

set:直接赋值使用 set，可以赋常量或者赋表达式，具体语法如下：

create procedure  [procedureName()]
begin
	declare numer int default 10;
	set numer = numer+10;
	select concat("numer的值为:",numer);
end;

也可以通过select ... into 方式进行赋值操作 :

DELIMITER $

CREATE  PROCEDURE pro_test5()
BEGIN
	declare  countnum int;
	select count(*) into countnum from city;
	select countnum;
END$

DELIMITER ;

if条件判断

语法结构

if search_condition then statement_list

	[elseif search_condition then statement_list] ...

	[else statement_list]

end if;

案例：

/*
	根据定义的身高变量，判定当前身高的所属的身材类型 
	180 及以上 ----------> 身材高挑
	170 - 180  ---------> 标准身材
	170 以下  ----------> 一般身材
*/
create procedure procedure_demo03()
begin
	declare height int default 170;
	declare description varchar(50) default '';

	if height >= 180 then 
		set description = '身材高挑';
	elseif height >= 170 and height < 180 then 
		set description = '标准身材';
	elseif height < 170 then 
		set description = '一般身材';
	end if;
	select concat('身高对应的身材为：', description);
end;

传递参数

语法格式：

create procedure procedure_name([in/out/inout] 参数名   参数类型)
...

IN :   该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT:   该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数

IN - 输入

案例：

--根据定义的身高变量，判定当前身高的所属的身材类型 
create procedure procedure_demo04(in height int)
	begin
		declare description varchar(50) default '';
		if height >= 180 then 
			set description = '身材高挑';
		elseif height >= 170 and height < 180 then 
			set description = '标准身材';
		elseif height < 170 then 
			set description = '一般身材';
		end if;
		select concat('身高对应的身材为：', description);
	end;

OUT-输出

案例：

	--根据传入的身高变量，获取当前身高的所属的身材类型  
	create procedure procedure_demo05(in height int,out description varchar(50) )
	begin
	--	declare description varchar(50) default '';
		if height >= 180 then 
			set description = '身材高挑';
		elseif height >= 170 and height < 180 then 
			set description = '标准身材';
		elseif height < 170 then 
			set description = '一般身材';
		end if;
	--	select concat('身高对应的身材为：', description);
	end;

小知识

@description : 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。

@@global.sort_buffer_size : 这种在变量前加上 "@@" 符号, 叫做系统变量

case结构

方式一：

CASE case_value

  WHEN when_value THEN statement_list
  
  [WHEN when_value THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;

方式二：

CASE

  WHEN search_condition THEN statement_list
  
  [WHEN search_condition THEN statement_list] ...
  
  [ELSE statement_list]
  
END CASE;

案例：

create procedure procedure_demo06(in mon int)
begin
	declare result varchar(50) default '';
	case 
		when mon>=1 and mon <= 2 then
			set result ='第一季度';
		when mon>=3 and mon <= 5 then
			set result ='第二季度';
		when mon>=5 and mon <= 7 then
			set result ='第三季度';
		else
			set result ='第四季度';
	end case;
	select concat('传递月份为',result);
end;

while循环

语法结构

while search_condition do

	statement_list

end while;

案例：

-- 计算从1加到n的值
create procedure procedure_demo07(in n int)
begin
	declare num int default 0;
	declare t int default 1;
	while t <= n do
		set num = num + t;
		set t = t+1;
	end while;
	select num;
end;

repeat循环

有条件的循环控制语句, 当满足条件的时候退出循环。while 是满足条件才执行，repeat 是满足条件就退出循环。

语法结构 :

REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;

案例：

--计算从1加到n的值
create procedure procedure_demo08(in n int)
begin
	declare totol int default 0;
	repeat
		set totol = totol + n;
		set n = n-1;
	until n = 0 end repeat;
	select totol;
end;

loop循环

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现

语法
```
[begin_label:] LOOP

  statement_list

END LOOP [end_label]
```
如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

leave语句

用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用。下面是一个使用 LOOP 和 LEAVE 的简单例子 , 退出循环：

delimiter $

CREATE PROCEDURE pro_test11(n int)
BEGIN
  declare total int default 0;
  
  ins: LOOP
  
    IF n <= 0 then
      leave ins;
    END IF;
  
    set total = total + n;
    set n = n - 1;
  
  END LOOP ins;
  
  select total;
END$

delimiter ;

游标/光标

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

声明光标：

DECLARE cursor_name CURSOR FOR select_statement ;

OPEN 光标：

OPEN cursor_name ;

FETCH 光标：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 光标：

CLOSE cursor_name ;

**示例 : **

初始化脚本:

create table emp(
  id int(11) not null auto_increment ,
  name varchar(50) not null comment '姓名',
  age int(11) comment '年龄',
  salary int(11) comment '薪水',
  primary key(`id`)
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

-- 查询emp表中数据, 并逐行获取进行展示
create procedure pro_test11()
begin
  declare e_id int(11);
  declare e_name varchar(50);
  declare e_age int(11);
  declare e_salary int(11);
  declare emp_result cursor for select * from emp;
  
  open emp_result;
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  fetch emp_result into e_id,e_name,e_age,e_salary;
  select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary);
  
  close emp_result;
end$

通过循环结构 , 获取游标中的数据 :

DELIMITER $

create procedure pro_test12()
begin
  DECLARE id int(11);
  DECLARE name varchar(50);
  DECLARE age int(11);
  DECLARE salary int(11);
  DECLARE has_data int default 1;
  
  DECLARE emp_result CURSOR FOR select * from emp;
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
  
  open emp_result;
  
  repeat
    fetch emp_result into id , name , age , salary;
    select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
    until has_data = 0
  end repeat;
  
  close emp_result;
end$

DELIMITER ;

4.7 存储函数

语法结构:

CREATE FUNCTION function_name([param type ... ]) 
RETURNS type 
BEGIN
    ...
END;

案例 :

定义一个存储过程, 请求满足条件的总记录数 ;


delimiter $

create function count_city(countryId int)
returns int
begin
  declare cnum int ;
  
  select count(*) into cnum from city where country_id = countryId;
  
  return cnum;
end$

delimiter ;

调用:

select count_city(1);

select count_city(2);

存储函数

语法

CREATE FUNCTION function_name([param type ... ]) 
RETURNS type 
BEGIN
	...
END;

案例：

--定义一个存储过程, 请求满足条件的总记录数 ;
create function fun1(countryId int) 
returns int
DETERMINISTIC
READS SQL DATA
begin
	declare cunm int;
	select count(*) into cunm from city where country_id = countryId;
	return cunm;
end;

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW 和 OLD的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

创建触发器

语法结构 :

create trigger trigger_name 

before/after insert/update/delete

on tbl_name 

[ for each row ]  -- 行级触发器

begin

	trigger_stmt ;

end;

案例：

--通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;
--首先创建一张日志表 : 
create table emp_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作表的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

--创建触发器
create trigger emp_insert_trigger
after insert on emp
for each row
begin
    insert into emp_logs (id, operation, operate_time, operate_id, operate_params)
    values(null, 'update', now(), new.id, concat('修改后(id:', new.id, ', name:', new.name, ', age:', new.age, ', salary:', new.salary, ')'));
end;

触发器的删除/查看

查看触发器
1. 语法：
```
show triggers;
```
2. 案例：
删除触发器
1. 语法：
```
drop trigger [triggerName];
```
2. 案例：

Mysql的体系结构概览

整个MySQL Server由以下组成
- Connection Pool : 连接池组件
- Management Services & Utilities : 管理服务和工具组件
- SQL Interface : SQL接口组件
- Parser : 查询分析器组件
- Optimizer : 优化器组件
- Caches & Buffers : 缓冲池组件
- Pluggable Storage Engines : 存储引擎
- File System : 文件系统
连接层

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

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

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
存储层

数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

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

存储引擎

存储引擎概述

和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。

MySQL5.0支持的存储引擎包含： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

可以通过指定 show engines ，来查询当前数据库支持的存储引擎：

创建新表时如果不指定存储引擎，那么系统就会使用默认的存储引擎，MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

查看Mysql数据库默认的存储引擎，指令：

 show variables like '%storage_engine%' ;

各种存储引擎的特性

**下面重点介绍几种常用的存储引擎，并对比各个存储引擎之间的区别，如下表所示： **

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持
锁机制	行锁(适合高并发)	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持

下面我们将重点介绍最长使用的两种存储引擎： InnoDB、MyISAM ，另外两种 MEMORY、MERGE ，了解即可。

InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

InnoDB存储引擎不同于其他存储引擎的特点：
1. 事务控制
```
create database demo_02 default charset=utf8mb4;
use demo_02 
create table goods_innodb(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=innodb DEFAULT CHARSET=utf8;
```
```
start transaction;
insert into goods_innodb(id,name)values(null,'Meta20');
commit;
```
  测试，发现在InnoDB中是存在事务的；
2. 外键约束
  
  MySQL支持外键的存储引擎只有InnoDB ，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候，也会自动的创建对应的索引。
  
  下面两张表中， country_innodb是父表， country_id为主键索引，city_innodb表是子表，country_id字段为外键，对应于country_innodb表的主键country_id 。
```
create table country_innodb(
	country_id int NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


create table city_innodb(
	city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);
```
  在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL 和 NO ACTION。
  
  RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下，父表不能更新；
  
  CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；
  
  SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。
  
  针对上面创建的两个表，子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的，那么在主表删除记录的时候，如果子表有对应记录，则不允许删除，主表在更新记录的时候，如果子表有对应记录，则子表对应更新。
  
  表中数据如下图所示：
  
  外键信息可以使用如下两种方式查看：
```
show create table city_innodb ;
```
3. 存储方式
  
  InnoDB 存储表和索引有以下两种方式：
  1. 使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。
  2. 使用多表空间存储，这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。
    
    ![1556075336630](file://D:/Note_Apps/MyNote/MySQL/%E8%B5%84%E6%96%99-MySQL%E9%AB%98%E7%BA%A7%E6%95%99%E7%A8%8B/MySQL%20%E9%AB%98%E7%BA%A7%20-%20day-02/%E6%96%87%E6%A1%A3/assets/1556075336630.png?lastModify=1697118083)

MyISM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表。有以下两个比较重要的特点：

不支持事务


create table goods_myisam(
	id int NOT NULL AUTO_INCREMENT,
	name varchar(20) NOT NULL,
    primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;

文件存储方式

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是：

.frm (存储表定义)；

.MYD(MYData , 存储数据)；

.MYI(MYIndex , 存储索引)；

MEMORY

Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引，但是服务一旦关闭，表中的数据就会丢失。

MERGE

ERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

对于MERGE类型表的插入操作，是通过INSERT_METHOD子句定义插入的表，可以有3个不同的值，使用FIRST 或 LAST 值使得插入操作被相应地作用在第一或者最后一个表上，不定义这个子句或者定义为NO，表示不能对这个MERGE表执行插入操作。

可以对MERGE表进行DROP操作，但是这个操作只是删除MERGE表的定义，对内部的表是没有任何影响的。

下面是一个创建和使用MERGE表的示例：

创建3个测试表 order_1990, order_1991, order_all , 其中order_all是前两个表的MERGE表：

create table order_1990(
	order_id int ,
	order_money double(10,2),
	order_address varchar(50),
	primary key (order_id)
)engine = myisam default charset=utf8;


create table order_1991(
	order_id int ,
	order_money double(10,2),
	order_address varchar(50),
	primary key (order_id)
)engine = myisam default charset=utf8;


create table order_all(
	order_id int ,
	order_money double(10,2),
	order_address varchar(50),
	primary key (order_id)
)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default charset=utf8;

分别向两张表中插入记录

insert into order_1990 values(1,100.0,'北京');
insert into order_1990 values(2,100.0,'上海');

insert into order_1991 values(10,200.0,'北京');
insert into order_1991 values(11,200.0,'上海');

查询3张表中的数据。
1. order_1990中的数据：
2. order_1991中的数据：
3. order_all中的数据：
往order_all中插入一条记录，由于在MERGE表定义时，INSERT_METHOD 选择的是LAST，那么插入的数据会想最后一张表中插入。
```
insert into order_all values(100,10000.0,'西安')；
```

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适

SQL优化步骤

在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化，本章将详细介绍在 MySQL 中优化 SQL 语句的方法。

当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。

查看SQL执行率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

下面的命令显示了当前 session 中所有统计参数的值：

show global status like 'Com_______';--查看全局sql执行率

show status like 'Innodb_rows_%';

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。

Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。具体可以查看本书第 26 章中日志管理的相关部分。
show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看

2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

4） db列，显示这个进程目前连接的是哪个数据库

5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等

6） time列，显示这个状态持续的时间，单位是秒

7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成

8） info列，显示这个sql语句，是判断问题语句的一个重要依据

explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划：

explain  select * from tb_item where id = 1;

explain  select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3';

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system ---> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge ---> index_subquery -----> range -----> index ------> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

‍

posted @ 2023-10-08 21:35 lkjlwq 阅读(42) 评论(0) 收藏举报

刷新页面返回顶部

lkjlwq

MySQL高级

MySQL高级

索引

索引的概述

索引的优劣势

索引的结构

B-Tree结构

B+Tree结构

MySQL中的B+Tree

索引的分类

索引的语法

创建索引

查看索引

删除索引

alter（ALTER）命令

索引的设计原则

视图

视图的概述

视图的基本语法

创建视图

修改视图

查看视图

删除视图

存储过程和存储函数

存储过程和存储函数的概述

存储过程的操作语法

创建存储过程

调用存储过程

查看存储过程

删除存储过程

语法

变量

if条件判断

传递参数

case结构

while循环

repeat循环

loop循环

leave语句

游标/光标

4.7 存储函数

存储函数

触发器

创建触发器

触发器的删除/查看

Mysql的体系结构概览

存储引擎

存储引擎概述

各种存储引擎的特性

InnoDB

MyISM

MEMORY

MERGE

存储引擎的选择

SQL优化步骤

查看SQL执行率

定位低效率执行SQL

explain分析执行计划

公告