【Mysql】Mysql高级部分笔记

2. 索引

2.1索引概念

索引（index）是帮助Mysql高效快速获取数据的数据结构。

没有建立索引、那么全表查询比较慢

2.1 索引的优劣势

优势：

1. 类似目录的索引，提高数据检索效率，奖励IO成本
2. 索引对数据排序，降低数据排序成本，降低CPU消耗

劣势：

1. 索引也是一张表，保存了主键和索引字段并指向一条记录，所以索引也是占空间的
2. 提高了查询效率，降低了更新表速度。因为要维护二叉树

2.2 索引结构

• BTree：最常见
• Hash：使用场景简单
• R-tree：空间索引，用于地理空间数据类型
• Full-text：全文索引

2.2.1 BTree，多路平衡搜索树

性质：

• 每个节点最多包含M个孩子。
• 除根节点和叶子结点外，每个节点至少需有()个孩子
• 若根节点不是叶子结点，则至少有两个孩子
• 所有叶子结点在同一层
• 每个非叶子结点有N个Key和N+1个指针组成

BTree比二叉树查找效率高

2.2.2 B+Tree

• N叉B+Tree最多包含N 个Key，erBTree最多包含N - 1个
• B+Tree的叶子结点保存所有的Key信息，依Key大小顺序排列
• 所有的非叶子结点都可以看做是Key 的索引部分

B+Tree查找都要查找到叶子结点，但BTree不用

2.2.3 Mysql索引对B+Tree的优化

在原有的基础上，增加了一个指向相邻叶子结点的链表指针，节形成了带有顺序指针的B+Tree，提高区间访问的性能

2.3 索引分类

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

2.4 索引语法

-- 创建索引： create index 索引名称 on 表名（字段名）;   普通索引
create index idx_city_name on city(city_name);

-- 查看索引：show index from 表名

---删除索引: drop index 索引名称 on 表名


--alter用法
-- 1. 该语句添加一个主键，这意味着索引值是唯一的且不为NULL

alter table tb_name add primary key(colum_list);

-- 2. 该语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现对次）
-- alter table 表名 add unique 唯一索引名(字段名)
alter table city add unique idx_city_name(city_name);

-- 3. 添加普通索引，索引值可以出现多次
alter table tb_name add index index_name(colum_list;

-- 4. 指定索引为全文索引即fulltext
alter table tb_name add fulltext index_name(colum_list);

2.5 建立索引规则

• 查询频率高的表建立索引
• 索引字段选择，尽量用where中出现的字段
• 使用唯一索引，区分度高效率越高
• 索引不是越多越好，
• 使用短索引，因为索引也需要磁盘存储 ，
• 利用最左索引，N个列组成组合索引，

3. 视图

3.1 视图概述

​ 视图（View）是一种虚拟的表，视图并不自然数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。

视图的优势：

• 简单：使用视图不需要关心后面的表的结构等条件，是过滤好的结果集
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行和列，但是通过视图可以简单实现
• 数据独立：一旦视图结构确定，对原表的修改不会影响视图，只需要更新视图就可同步。

基本使用：

4. 存储过程和函数

4.1 含义

​ 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化开发人员的工作，减少数据在数据库和程序之间的传输，对于提高数据处理的效率是有好处。但是不推荐使用，尽量使用JAVA自己实现

函数：有返回值的过程

过程：没有返回值的函数

4.2 创建存储过程

CREATE procedure procedure_name(参数)
begin
	-- sql语句
end ;

-- 示例
delimiter $ --使;不生效，让$代替;  因为;可以让SQL语句生效，而最终还要加end;才是创建存储过程的结束
create procedure pro_test()
begin 
	select 'hello mysql';
end$

delimiter ; -- 使;生效，即还原

4.3 调用存储过程

call procedure_name

-- 示例
call pro_test(); $

4.4 查看存储过程

-- 查看db_name数据库中的所有存储过程
select name from mysql.proc where db='db_name';

-- 查看存储过程的状态信息
show procedure status;

-- 查看某个存储过程的定义
show create procedure test.protest \G; -- \G 表示纵向显示信息

4.5 删除存储过程

drop procedure pro_name;

4.6 存储过程的语法

4.6.1 变量

• 定义：delare 变量名

create procedure pro_test()
begin 
	declare num int default 10;
	select concat('num的值为：',num);
end $

• 赋值：set 或者使用select into

-- 定义
create procedure pro_test()
begin 
	declare num int default 0;
	set num = num + 10; -- 赋值
	select num;
end $

-- 调用
call pro_test()$


-- 方式二select  into
create procedure pro_test()
begin 
	declare num int ;
	select count(*) into num from city; -- 赋值
	select concat('city表中的记录数为：',num);
end $
-- 调用
call pro_test()$

4.6.2 if

create procedure pro_test()
begin 
	declare height int default 175;
	declare descriprion varchar(50) default '';
	if height >= 180 then
		set descriprion = '身高高';
	elseif height >= 170 then
		set descriprion = '身高中';
	else 
		set descriprion = '身高低';
	end if;
	select concat('身高：', height, '生产类型为：' , descriprion);
end $

4.6.3 传递参数

• create procedure pro_name([in/out/inout]参数名 参数类型) – (默认in)

-- 定义in 输入参数
create procedure pro_test(in height int)
begin 
	declare descriprion varchar(50) default '';
	if height >= 180 then
		set descriprion = '身高高';
	elseif height >= 170 then
		set descriprion = '身高中';
	else 
		set descriprion = '身高低';
	end if;
	select concat('身高：', height, '生产类型为：' , descriprion);
end $

-- 调用
call pro_test(180)$

-- 定义out 输出参数
create procedure pro_test(in height int, out descriprion varchar )
begin 

	if height >= 180 then
		set descriprion = '身高高';
	elseif height >= 170 then
		set descriprion = '身高中';
	else 
		set descriprion = '身高低';
	end if;
end $

-- 调用
call pro_test(180,@descriprion)$
select @descriprion;

-- @ 代表用户变量，结束释放
-- @@ 代表系统变量，结束不会释放

4.6.4 case结构

-- 定义
create procedure pro_test(mon int)
begin 
	declare result varchar(10);
	case 
		when mon >= 1 and mon <= 3 then
		set result = '第一季度';
		when mon >= 4 and mon <= 6 then
		set result = '第二季度';
		when mon >= 7 and mon <= 9 then
		set result = '第三季度';
		else
			set result = '第四季度';
	end case;
	select concat('传递的月份是：', mon, '结果为：' , result) as content;
end $

4.6.5 while 循环

create procedure pro_test(n int)
begin 
	declare total int default 0;
	declare num int default 1;
	while num <= n do
		set total = total + num ;
		set num = num + 1；
	end while;
	select total;
end $

4.6.7 repeat

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

-- 计算从1加到n的值
create procedure pro_test(n int)
begin 
	declare total int default 0;
	
	repeat
		set total = total + num ;
		set n = n - 1；
		until n = 0
	end repeat;
	select total;
end $

5. 触发器

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

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

6. 存储引擎

存储引擎就是存储数据，建立索引，更新数据等的实现方式。是基于表的，不是基于库的。Mysql默认的是InnoDB

6.1 几种常用的存储引擎

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

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

6.2 InnoDB

InnoDB存储引擎提高提交、回滚、崩溃恢复能力的事务安全。

6.2.1 事务控制

6.2.2 外键约束

在创建外键的时候， 要求父表必须有对应的索引 ， 子表在创建外键的时候， 也会自动的创建对应的索引。

6.2.3 存储方式

• 使用共享表空间存储
• 使用多表空间存储

6.3 MyISAM

6.4 MEMORY

6.5MERGE

6.6 存储引擎的选择

• InnoDB : 默认的。对事务有较高的要求，在并发条件要求数据一致性。
• MyIDAM：以读和插入操作为主，对事务要求不高
• MEMOR：存储在内存中，表不能太大
• MERGE：这对于存储诸如数据仓储等VLDB环境十分合适