Mysql进阶

视图

视图就是将一条SQL语句封装起来，之后使用sql时，只需要查询视图即可，视图不保存数据，数据还是在表中。

视图是基于查询的虚拟表。通俗的理解，视图就是一条 SELECT 语句执行后返回的结果集。

#定义视图
CREATE VIEW 视图名 AS SELECT 列 1，列 2... FROM 表(查询语句);
#使用视图
SELECT * FROM 视图名
#删除视图
drop view 视图名

存储过程

将一些逻辑处理的代码可以事先存储在数据库中，然后使用时直接调用即可，可以减少应用程序与数据库之间交互的次数。

语法

存储过程事先需要创建好（包含逻辑），可以向存储过程中传入参数

参数分为3种，in（输入参数）out（输出参数）inout（既可以输入，也可以输出）

#创建存储过程的语法格式
create procedure 存储过程名（[in 变量名 类型，out 参数 2，…]）
begin
	[declare 变量名 类型 [DEFAULT 值];]
	存储过程语句块;
end;
语法解析：
1.存储过程的参数分为 in，out，inout 三种类型。
2.in 代表输入参数（默认情况下为 in 参数），表示该参数的值必须由调用程序指定。
3.out 代表输出参数，表示该参数的值经存储过程计算后，将 out 参数的计算结果返回给调用程序。
4.inout 代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序指定，又可以将 inout 参数的计算结果返回给调用程序。
5.存储过程中的语句必须包含在 begin 和 end 之间。
6.declare 中用来声明变量,变量默认赋值使用 default,语句块中改变变量值,使用 set 变量=值;

函数

函数语法

-- 创建函数
create function 函数名([参数列表]) returns 数据类型
begin
	DECLARE 变量；
		sql 语句;
	return 值;
end;
注意：
1.参数列表包含两部分：参数名 参数类型
2.函数体：肯定会有 return 语句，如果没有会报错
3.函数体中仅有一句话，则可以省略 begin end
4.使用 delimter 语句设置结束标记
设置函数可以没有参数
SET GLOBAL log_bin_trust_function_creators=TRUE;

-- 删除函数
DROP FUNCTION 函数名;

触发器

概述

触发器（trigger）是一种特殊的存储过程，其特殊性在于它并不需要用户直接调用，而是在对表添加、修改、删除之前或者之后自动执行的存储过程。

触发器具有以下特点：

1.与表相关联

触发器定义在特定的表上，这个表称为触发器表。

2.自动激活触发器

当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这 个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。

3.不能直接调用

与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。

4.作为事务的一部分

触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。

语法

定义触发器的语法规则：
CREATE TRIGGER 触发器名称 触发时机 触发事件	
ON 表名称
FOR EACH ROW -- 行级触发
BEGIN
	语句
END;
语法解析：
1.触发器名称：是用来标识触发器的，由用户自定义。
2.触发时机：其值是 before 或 after。
3.触发事件：其值是 insert，update 和 delete
4.表名称：标识建立触发器的表名，即在哪张表上建立触发器
5.语句：是触发器程序体，触发器程序可以使用 begin 和 end 作为开始和结束,中间包含多条语句；

Mysql架构

1、连接层：负责接收客户端的连接请求，可以进行认证（验证账号密码）

2、服务层：接收sql，语法解析，优化，缓存

3、引擎层：引擎层是真正落地实现的具体方式，不同的存储引擎特点不同。

4、物理文件存储层：使用各种文件用来存储数据，以及各种日志文件

Mysql引擎

存储引擎

存储引擎是具体的操作数据的方式

InnoDB：支持事物，支持行级锁（一个事物对某行数据操作时，只会锁住某一行数据，不锁定其它行，效率高）支持外键约束，支持缓存，支持全文索引，不会存储表中的总行数。

MyISAM：不支持事务，不支持主外键，不支持行级锁，支持表锁（进行dml操作时会锁定整张表），主要用于查询多，增删改较少的场景，支持全文索引，存储表的总行数

索引

为什么要有索引呢？

对于少量的数据，查询 的速度会很快，但是，当随着数据量的增加，性能会急剧下降。100 万条数据逐 页查询的时间是无法被用户接受的。

什么是索引？

索引是类似于书的目录，可以通过目录（索引）快速的定位数据真实的位置，是排好序的，快速查找的数据结构（B+树），在数据库中单独维护一个树，树中的每个节点存储主键和数据的物理地址，这样就可以通过树形结构，快速锁定到数据的位置

索引原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等.

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引类似于书的目录，在一本书前面加上目录，查找内容时不必逐页翻阅就能够快速地找到所需的内容。借助索引，执行查询时不必扫描整个表就能够快速 地找到所需要的数据。

索引优势

快速的定位数据的位置，减少了IO次数，降低了排序的成本

索引劣势

维护索引需要占用存储空间，增删改操作时，还需要额外的维护索引树，需要消耗时间

索引分类

主键索引： 设定为主键后数据库会自动建立索引

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

删除建主键索引：

ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引： 一个索引只包含单个列，一个表可以有多个单列索引

创建单值索引：

CREATE INDEX 索引名 ON 表名(列名);

删除索引：

DROP INDEX 索引名;

唯一索引： 保证索引列的值必须唯一，允许为 null

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

删除索引：

DROP INDEX 索引名 ON 表名;

组合索引(复合索引)： 一个索引对应多个列，注意要满足组合索引最左前缀原则：a,b,c三列 a,b建立组合索引，满足最左前缀原则，必须要出现最左边的列

a and b 生效

b and a 生效

a and c 生效

b and c 不生效

创建复合索引：

CREATE INDEX 索引名 ON 表名(列 1,列 2…);

删除索引：

DROP INDEX 索引名 ON 表名;

全文索引：

​ 模糊查询 like 导致索引失效 这时就使用全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词')

查看索引：

SHOW INDEX FROM 表名;

创建索引原则

哪些情况需要创建索引

主键自动建立唯一索引

频繁作为查询条件的字段应该创建索引(where 后面的语句)

查询中与其它表关联的字段，外键关系建立索引

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

分组中的字段

哪些情况不要创建索引

表记录太少

经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE,因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件

Where 条件里用不到的字段不创建索引

数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果。例如男，女

索引数据结构

Mysql 索引使用的是 B+树，因为索引是用来加快查询的，而 B+树通过对数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元 素，从而可以使得 B+树的高度不会太高.并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等 SQL 语句。

首先二叉树，红黑树（自平衡）被排除，树的高度较高，会影响查询效率

B-树 自平衡多路的树，在一个结点中放多个数据，横向扩展，降低了树的高度

B+树 自平衡多路搜索树，排好序，非叶子结点只存储索引，数据都存储在叶子结点。会存在数据冗余，叶子结点之间会有一个相互指向的指针（对于自增主键，范围查找非常合适）

聚簇索引和非聚簇索引

聚簇索引

​ 找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引。

​ InnoDB引擎下，数据都在叶子节点存储，通过主键查找，找到了主键，也就找到了数据

非聚簇索引

​ 索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)，再次回表查询,非聚簇索引也叫做辅助索引。

通过学号（非主键）查询学号和姓名，先通过学号找主键，再次通过主键去查找数据，这种场景是非聚簇的。

MyISAM引擎中，除了只查询主键列以外，查询其它列的都是非聚簇索引

判断是聚簇索引还是非聚簇索引：能否直接命中数据

事务

概述

事务是数据为了保证数据操作的原子性，隔离性，持久性，一致性，数据库提供了一套机制，在同一事物中，如果有多条sql执行，事务确保执行的可靠性。

特性

一般来说，事务是必须满足 4 个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务中的多条sql要么都执行，要么都不执行

持久性：一旦事务提交，数据就持久保存在硬盘上

隔离性：有好多个事务同时对数据进行操作，需要不同的隔离机制，来进行控制，防止数据不完整。

一致性：数据经过很多次操作，最终的结果要与预期的结果一致，保证数据的完整性

原子性，隔离性，持久性都是为了保证数据的一致性。

隔离级别

事务有4种隔离级别：

1、读 未提交 ：B事务读到了A事务未提交的数据

问题：产生脏读（垃圾数据，因为A事务可能会回滚）

解决办法：设置隔离级别为读 已提交

2、读 已提交：B事务读到了A事务已经提交的事务

解决了脏读问题

产生问题：不可重复读（B 事务在开启后的两次查询中，两次查询结果不一致）

3、可重复 读：B事务在开启后的两次查询中，两次查询结果都是相同的

解决了不可重复读的问题

产生问题：一般的查询SQL不会产生幻读

​ Select … for update 会出现幻读

4、串行化（serializable）：加锁，只要有一个事务进行操作，其他事务就得等待，即使是读操作，也会锁定，是最安全的，速度是最慢的。

可以解决以上所有的问题

Mysql中默认的隔离级别为可重复读

事务实现原理

原子性： 原子性实现 依赖的是undo log（回滚日志），保存每一次操作的反向操作，如果我们执行insert操作，那么日志中保存delete操作，当回滚时执行反向操作

持久性： 每次当执行修改数据操作时，先会将语句保存到redo log(重做日志)中，当 mysql 服务器意外崩溃或者宕机后，保证已经提交的事务，确定持久化到磁盘中的一种措施。

隔离级别实现原理(MVCC)

MVCC（多版本并发控制 Multi-Version Concurrent Control），是 MySQL 提高性能的一种方式，配合 Undo log 和版本链,让不同事务的读写、 写-读操作可以并发执行，从而提升系统性能。

主要是针对读已提交和可重复读。

读已提交：只要别的事务提交了，那么另一个事物就可以看到，

​ 特点：有可能同一个事务两次查询数据不一致，时时访问到的是最新的数据。当前读，每次读的时候都会给版本链拍照，所以读到的数据是最新的（已提交的数据）。

可重复读：B事务开始后，第一次读到的数据和之后读到的数据是一致的，这个过程中别的事务已经修改过了，也叫快照读，第一次读的时候，会把版本链拍照，下次读时，从版本快照中读，所以第一次和第二次读到的数据是一致的。

使用隔离级别机制，为了实现并发的读-写，写-读操作，提高效率

锁机制

概述

事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

行锁，间隙锁，表锁

行锁： 行级锁是Mysql中锁定粒度最小的一种锁，只给操作的行加锁，如果两个事务操作的是同一行，必须逐个执行

​ 特点：开销大，加锁慢，会出现死锁，发生锁的冲突概率最低，并发度也最高。

间隙锁： 一般指的是范围区间，对某一个区间进行加锁 例如 id>5 and id<10

表锁： 对整个表加锁，MyISAM默认是支持表锁。

​ 特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

共享锁（S）： 又称读锁，如果事务在读数据时，不想让其它事务写，还要让其它数据读，那么查询语句可以加共享锁。select … lock in share mode

排他锁（X）： 就是独占锁，写操作默认加排他锁，当我们读数据时，要求数据足够准确，可以给读操作加排他锁。select …for update

Sql优化

为什么要对SQL进行优化？

随着时间的积累，业务数据量的增多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对 SQL 的优化就很有必要。

SQL优化的方法

1、查询sql尽量不要使用select * ，而是具体字段

2、避免在where子句中使用 or 来连接条件，使用union all把两个sql结果合并

3、尽量使用数值代替字符串类型

​ 字符会降低查询和连接的性能，并会增加存储开销；

4、使用varchar代替char

​ varchar 变长字段按数据内容实际长度存储，可以节省存储空间； char 按声明大小存储，不足补空格； 其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

5、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

6、应尽量避免索引失效

7、inner join 、left join、right join，优先使用 inner join

8、提高 group by 语句的效率

​ 反例:先分组，再过滤

​ 正例:先过滤，后分组

9、清空表时优先使用 truncate

truncate table 比 delete 速度快，且使用的系统和事务日志资源少.

delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table 通过释放存储表数据所用的数据页来删除数据.

10、表连接不宜太多，索引不宜太多，一般 5 个以内

11、避免在索引列上使用内置函数

12、使用 explain 分析你 SQL 执行计划

执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 作用

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

EXPLAIN 使用

在 select 语句之前增加 explain 关键字，执行查询会返回执行计划的信息， 而不是执行 SQL。

EXPLAIN SELECT * FROM USER WHERE id = 1

概要描述

id:选择标识符

select_type:表示查询的类型。

table:输出结果集的表

partitions:匹配的分区

type:表示表的连接类型

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引

key_len:索引字段的长度

ref:列与索引的比较

rows:扫描出的行数(估算的行数)

filtered:按表条件过滤的行百分比

Extra:执行情况的描述和说明