数据库索引&视图&存储一

1 索引

1.1 概述：

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

概念图

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

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

1.2 优缺点

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

1.2 索引结构

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。
所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引:
    BTREE 索引：最常见的索引类型,大部分索引都支持B树索引。 
    HASH 索引：只有Memory引擎支持,使用场景简单
    R-tree索引 (空间索引 ) ：空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少,不做特别介绍。
    Full-text (全文索引 ) ：全文索引也是MylSAM的一个特殊索引类型,主要用于全文索引, InnoDB从Mysq15.6版本开始支持全文索引。

MyISAM，InnoDB，Memory三种存储引擎对各种索引类型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE 索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 空间索引	不支持	支持	不支持
Full-text 全文索引	5.6版本之后支持	支持	不支持

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

1.2.1 BTREE 结构

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：
树中每个节点最多包含m个孩子。
除根节点与叶子节点外，每个节点至少有[ceil(m/2)个孩子。
若根节点不是叶子节点，则至少有两个孩子。
所有的叶子节点都在同一层。
每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <=n <=m-1。

BTREE 树和二叉树相比,查询数据的效率更高,因为对于相同的数据量来说, BTREE的层级结构比二叉树小,因此搜索速度快。

1.2.2 B+TREE 结构

B+Tree为BTree的变种, B+Tree与BTree的区别为:
1)n叉B+Tree最多含有n个key ,而BTree最多含有n-1个key。
2)B+Tree的叶子节点保存所有的key信息,依key大小顺序排列。
3)所有的非叶子节点都可以看作是key的索引部分。

B+TREE结构

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

1.2.3 MySQL中的B+TREE 结构

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

MySQL中的B+Tree索引结构示意图:

MySQL中的B+Tree索引结构示意图

1.3 索引分类

1)单值索引：即一个索引只包含单个列,一个表可以有多个单列索引。
2)唯一索引：索引列的值必须唯一,但允许有空值。
3)复否索引：即一个索引包含多个列

1.4 索引语法

准备环境

# 数据库
create database demo_01 default charset=utf8mb4;
use demo_01;

#表
CREATE TABLE city (
    city_id INT (11) NOT NULL AUTO_INCREMENT,
    city_name VARCHAR (50) NOT NULL,
    country_id INT (11) NOT NULL,
    PRIMARY KEY (city_id)
) ENGINE=INNODB DEFAULT CHARSET = utf8;

#表
CREATE TABLE country (
    country_id INT (11) NOT NULL AUTO_INCREMENT,
    country_name VARCHAR (100) NOT NULL,
    PRIMARY KEY (country_id) 
)ENGINE=INNODB DEFAULT CHARSET=utf8;

#插入数据
INSERT INTO city (city_id, city_name, country_id) VALUES (1, '西安', 1);
INSERT INTO city (city_id, city_name, country_id) VALUES (2, 'NewYork', 2);
INSERT INTO city (city_id, city_name, country_id) VALUES (3, '北京', 1);
INSERT INTO city (city_id, city_name, country_id) VALUES (4, '上海', 1);

INSERT INTO country (country_id, country_name) VALUES (1,'china');
INSERT INTO country (country_id, country_name) VALUES (2,'America');
INSERT INTO country (country_id, country_name) VALUES (3, 'Japan');
INSERT INTO country (country_id, country_name) VALUES (4, 'UK');

1.4.1 创建索引

通用语法
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name 
[USING index_type]
ON tbl-name(index_col-name, ...)
# 创建 指定索引类型 index 索引名称 [USING 索引类型（不指定使用类型，默认是B+树索引）on 表名(哪个字段)
UNIQUE 唯一索引
FULLTEXT 全文索引
SPATIAL  空间索引

create index idx_city_name on city(city_name);

1.4.2 删除索引

drop index idx_city_name on city;

1.4.3 ALTER 命令

通用语法
alter table 表名 add [primary key | unique | index  fulltext ] 索引名称 (字段列表)

1) alter table tb_name add primary key(column_list);
该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL

2) alter table tb_name add unique index_name(column_list);
这条语句创建索引的值必须是唯一的(除了NULL外, NULL可能会出现多冲)

3) alter table tb_name add index index_name(column_list);
添加普通索引,索引值可以出现多次

4) alter table tb_name add fulltext index_name(column_list);
该语句指定了索引为FULLTEXT,用于全文索引

1.5 索引设计原理

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率,更高效的使用索引。
对查询频次较高，且数据量比较大的表建立索引。

索引字段的选择，最佳候选列应当从where子句的条件中提取，
如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。

使用唯一索引，区分度越高，使用索引的效率越高。

索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。
对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。
另外索引过多的话， MysQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。

使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I0效率，也可以提升总体的访问效率。
假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MysQL访问索引的I0效率。

利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，
如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率

创建复合索引:
    CREATE INDEX idx_name_email_status oN tb_seller (NAME , email, STATUS);
就相当于对name 创建索引;
对name , email 创建了索引;
对name, email, status创建了索引

2 视图

2.1 概述

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

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

2.2 创建或修改视图

创建视图：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

解读：create view 视图名称 as 查询SQL语句 ; 

创建视图成功后就可以通过视图查看表数据，同时也可以通过视图更新表数据

修改视图：

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 :必须满足所有针对该视图的所有视图的条件才可以更新。

2.3 查看或删除视图

查看视图

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

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

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

删除视图

DROP VIEW [IF EXISTS] view_name [view_name] ...[RESTRICT | CASCADE]

示例： drop view 视图名称 ;

3 存储过程和函数

3.1 概述

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

3.2 创建存储过程

CREATE PROCEDURE procedure_name ([proc-parameter [....])
begin
    SQL语句
end;

示例：

知识小贴士
DELIMITER
该关键字用来声明SQL语句的分隔符,告诉MySQL解释器,该段命令是否已经结束了, mysq是否可以执行了。
默认情况下, delimiter是分号。在命令行客户端中,如果有一行命令以分号结束,那么回车后, mysa将会执行该命令。

3.3 调用或查看存储过程

调用：

call procedure_name ;

查看：

# 查询数据库中的所有的存储过程
select name from mysql.proc where db='数据库名称';
# 查询存储过程的状态信息
show procedure status;
show procedure status \G;
# 查询某个存储过程的定义
show create procedure 存储的名称 \G;

删除：

drop procedure [IF EXISTS] SP_name;

3.4 语法

3.4.1 变量

DECIARE

通过 DECLARE 可以定义一个局部变量,该变量的作用范围只能在 BEGIN..ND 块中。
DECLARE var_name[....] type [DEFAULT value]

#示例
create procedure pro_test2()
begin
    declare num int default 5;
    select num+ 10;
end;

set

直接赋值使用set，可以赋常量或者赋表达式，具体语法如下:
SET var_name = expr [var_name = expr] ...

#示例
CREATE PROCEDURE pro_test3()
BEGIN 
    DECLARE NAME VARCHAR (20);
    SET NAME = 'MYSQL';
    SELECT NAME 
END;

也可以通过select ...into 方式进行赋值
CREATE PROCEDURE pro_test4() 
BEGIN
    declare countnum int;
    select count(*) into countnum from city;
    select countnum;
END;

3.4.2 if条件判断

语法结构

if search_condition then statement_list
    [elseif search_condition then statement_list] ...
    [else statement_1ist]
end if;

3.4.3 传递参数

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

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

3.4.4 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;

3.4.5 while循环

待补充

3.4.6 repeat结构

待补充

3.4.7 loop语句

待补充

3.4.8 leave语句

待补充

3.4.9 游标/光标

待补充

3.5 存储函数

待补充

4 触发器

4.1 介绍

触发器是与表有关的数据车对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。
触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。
现在触发器还只支持行级触发,不支持语句级触发。

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

4.2 创建触发器

语法结构

create trigger trigger_name
before/after insert/update/delete
on tbl_name
    [ for each row ]--行级触发器
begin
    trigger_stmt;
end;

4.3 删除触发器

语法结构：

drop trigger [schema_name]trigger_name

如果没有指定schema_name，默认为当前数据库。

4.4 查看触发器

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
语法结构

show triggers ;

1 索引&视图&存储