## 一、两个引擎的对比
### MyISAM
- 不支持事务，但是整个操作是原子性的(事务具备四种特性：原子性、一致性、隔离性、持久性)
- 不支持外键，支持表锁，每次锁住的是整张表
MyISAM的表锁有读锁和写锁(<font color='red'>两个锁都是表级别</font>)
一个MyISAM表有三个文件：索引文件，表结构文件，数据文件
存储表的总行数，执行<font color='orange'>select count(*) from table</font>时只要简单的读出保存好的行数即可
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
<font color='red'>对于AUTO_INCREMENT类型的字段，在MyISAM表中，可以和其他字段一起建立联合索引。</font>
### InnoDB
- 支持事务，支持事务的四种隔离级别;是一种具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
- 支持行锁和外键约束，因此可以支持写并发
- 不存储总行数；也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。
- 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引
- DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除
- 一个Innodb表存储在一个文件内(共享表空间，表大小不受操作系统的限制)，也可能为多个(设置为独立表空间，表大小受操作系统限制，大小为2G)，受操作系统文件大小的限制
- 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问主键索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。
### 两者的区别
## 二、MySQL磁盘碎片
### 碎片产生的原因
- <font color='orange'>表的存储会出现碎片化</font>，每当删除了一行内容，该段空间就会变为空白、被留空，而在一段时间内的大量删除操作，会使这种留空的空间变得比存储列表内容所使用的空间更大；
- 当执行插入操作时，MySQL会尝试使用空白空间，但如果某个空白空间一直没有被大小合适的数据占用，仍然无法将其彻底占用，就形成了碎片；
- 当MySQL对数据进行扫描时，它扫描的对象实际是列表的容量需求上限，也就是数据被写入的区域中处于峰值位置的部分
如：<font color='orange'>一个表有1万行，每行10字节，会占用10万字节存储空间，执行删除操作，只留一行，实际内容只剩下10字节，但MySQL在读取时，仍看做是10万字节的表进行处理，所以，碎片越多，就会越来越影响查询性能。</font>
#### 查看表碎片的大小：
```MySQL
show table status like '表名';
```
结果中'Data_free'列的值就是碎片大小
#### 列出所有已经产生碎片的表：
```MySQL
select table_schema db, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;
```
#### 清理表碎片
1. MyISAM表：
```MySQL
optimize table 表名;
```
2. InnoDB表：
```MySQL
alter table 表名 engine = InnoDB;
```
## 三、数据库优化
<font color='orange'>是否需要清理碎片、上的锁是否为表锁</font>
<font color='orange'>select后不要用*，不然会回表</font>
#### 选取最适用的字段属性
<font color='red'>数据库中的表越小，在它上面执行的查询也就会越快。</font>因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小
**另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL**，这样在将来执行查询的时候，数据库<font color='red'>不用去比较NULL值</font>
因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多
#### 使用连接（JOIN）来代替子查询(Sub-Queries）
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以<font color='red'>避免事务或者表锁死</font>，并且写起来也很容易。但是，有些情况下，<font color='red'>子查询可以被更有效率的连接（JOIN）替代</font>。
#### 使用联合(UNION)来代替手动创建的临时表（不懂）
可以把需要使用临时表的两条或更多的<font color='orange'>select</font>查询合并的一个查询中。在客户端的查询会话<font color='red'>结束的时候</font>，临时表会被<font color='red'>自动删除</font>，从而保证数据库整齐、高效
用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。
#### 事务
事务原理是：<font color='red'>要么语句块中每条语句都操作成功，要么都失败</font>。换句话说，<font color='orange'>就是可以保持数据库中数据的一致性和完整性</font>。事物以<font color='orange'>BEGIN</font>关键字开始，<font color='orange'>COMMIT</font>关键字结束。在这之间的一条SQL操作失败，那么，<font color='orange'>rollback</font>命令就可以把数据库恢复到<font color='orange'>BEGIN</font>开始之前的状态。
<font color='cornflowerblue'>事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。</font>
#### 锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的<font color='red'>独占性</font>，有时会影响数据库的性能，尤其是在很大的应用系统中。<font color='red'>由于在事务执行的过程中，数据库将会被锁定</font>，因此其它的用户请求<font color='red'>只能暂时等待直到该事务结束</font>。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，就会产生比较严重的响应延迟。此时通过<font color='red'>锁定表</font>的方法来获得更好的性能
#### 使用外键
锁定表的方法可以<font color='red'>维护数据的完整性</font>，但是它<font color='red'>却不能保证数据的关联性</font>。这个时候我们就可以使用<font color='orange'>外键</font>。
#### 使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有<font color='red'>MAX()</font>,<font color='red'>MIN()</font>和<font color='red'>ORDERBY</font>这些命令的时候，性能提高更为明显。
一般说来，索引应建立在那些将用于<font color='red'>JOIN,WHERE</font>判断和<font color='red'>ORDER BY</font>排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。
#### 优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果<font color='red'>SQL语句使用不恰当的话</font>，索引将无法发挥它应有的作用。
- 不能将一个建有索引的<font color='orange'>int</font>字段和<font color='orange'>bigint</font>字段进行比较；但是作为特殊的情况，在<font color='orange'>CHAR</font>类型的字段和<font color='orange'>VARCHAR</font>类型字段的字段大小相同的时候，可以将它们进行比较。
- 在建有索引的字段上尽量<font color='orange'>不要使用函数</font>进行操作
- 在搜索字符型字段时，我们有时会使用<font color='red'>LIKE关键字和通配符</font>，这种做法虽然简单，但却也是<font color='red'>以牺牲系统性能为代价</font>的。
## 四、慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过<font color='orange'>long_query_time</font>（<font color='red'>long_query_time的默认值为10，意思是运行10S以上的语句</font>）值的SQL，则会被记录到慢查询日志中。
#### 慢查询日志相关参数
```MySQL
slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。
show variables like '%slow_query_log';
slow-query-log-file：MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
show variables like '%slow_query_log-file';
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
show VARIABLES like 'long_query_time%';
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
show variables like '%log_queries_not_using_indexes';
```
<font color='red'>log_output</font>：日志存储方式。<font color='red'>log_output='FILE'</font>表示将<font color='orange'>日志存入文件，默认值是'FILE'。</font><font color='red'>log_output='TABLE'</font>表示将<font color='orange'>日志存入数据库</font>，这样日志信息就会被写入到<font color='orange'>mysql.slow_log表</font>中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：<font color='orange'>log_output='FILE,TABLE'。</font>日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。
```mysql
查询慢查询日志存储方法
show variables like '%log_output'
修改慢查询日志存储位置
set global log_output = 'FILE,TABLE'
打印在MySQL中慢查询日志的位置
SELECT * FROM mysql.slow_log
```
默认情况下<font color='orange'>slow_query_log</font>的值为<font color='red'>OFF</font>，表示慢查询日志是禁用的
<font color='cornflowerblue'>那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？</font> 这个是由参数<font color='orange'>long_query_time</font>控制，默认情况下<font color='orange'>long_query_time</font>的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。关于运行时间正好等于<font color='orange'>long_query_time</font>的情况，并不会被记录下来。也就是说，在<font color='red'>mysql源码里是判断大于long_query_time，而非大于等于</font>。
## 五、本日重点讲解（不重要的东西）
可移植性不好，不易调试
### 1、触发器
与表有关的数据对象，在满足某种条件的时候，被动执行SQL语句
指事先为某张表<font color='red'>绑定一段代码</font>，当表中的<font color='red'>某些内容发生改变</font>（增、删、改）的时候，系统会<font color='red'>自动触发代码并执行</font>。
#### 作用：
- 可在写入数据前，强制检验或者转换数据(保证护数据安全)
- 触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚
#### 创建触发器
基本语句
```MySQL
delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
-- 触发器内容主体，每行用分号结尾
end 自定义的结束符合
```
<font color='orange'>on 表 for each</font>：触发对象，触发器绑定的实质是表中的所有行，因此当每一行发生指定改变时，触发器就会发生
##### 触发时间
当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：**数据操作前和操作后**
- before：表中数据发生改变前的状态
- after：表中数据发生改变后的状态
**PS**：如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)
##### 触发事件
触发器是针对数据发送改变才会被触发，对应的操作只有
- INSERT：没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据
- DELETE：既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据
- UPDATE：没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据
#### 查看触发器
1.查看全部触发器
```MySQL
show triggers;
```
2.查看触发器的创建语句
```MySQL
show create trigger 触发器名字;
```
3.触发触发器
```MySQL
drop trigger 触发器名字;
```
#### 删除触发器
触发器不能修改，只能删除
```MySQL
drop trigger + 触发器名字
```
### 2、存储过程
#### 变量
##### 局部变量
局部变量只在<font color='orange'>BEGIN</font>和<font color='orange'>END</font>的代码块中有效，执行完该代码块，变量就消失，在存储过程中最为常见。<font color='orange'>DECLARE</font>语句专门用于定义局部变量，可以使用<font color='orange'>DEFAULT</font>语句来指明默认值。定义多个变量：<font color='orange'>DECLARE x, y INT DEFAULT 0</font>。赋值用的是`SET`或`SELECT`语句：
```MySQL
DECLARE total_count INT DEFAULT 0
SET total_count = 10;
SELECT COUNT(*) INTO total_count FROM products
```
其中<font color='orange'>total_count</font>为局部变量
##### 用户变量
<font color='red'>用户变量与数据库连接有关</font>，一个客户端定义的变量不能被其它客户端看到或使用。当客户端退出时，该客户端连接的所有变量将失效。用户变量不需要声明，可以直接使用，用户变量以<font color='orange'>@var_name</font>的形式命名变量，<font color='red'>变量名必须以@开头</font>。
赋值语法，一般用<font color='orange'>SET</font>：
```MySQL
set @varName=value;
SELECT @var_name := value;
```
赋值后输出用<font color='orange'>select @varName</font>。
##### 系统变量
MySQL可以访问许多系统变量。当服务器运行时许多变量允许动态更改。这样就可以修改服务器操作而不需要停止并重启服务器。系统变量又包含两种：<font color='red'>会话变量影响具体客户端连接的操作，全局变量影响服务器整体操作。</font>
##### 全局变量
全局变量在 MySQL 启动的时候由服务器自动初始化他们的值, 这些默认的值可以在<font color='orange'>/etc/my.cnf</font>中修改。要想更改全局变量，必须具有<font color='red'>SUPER</font>权限。全局变量作用于<font color='red'>SERVER的整个生命周期</font>，<font color='red'>但重启后所有设置的全局变量均失效</font>。要想让全局变量重启后继续生效，需要更改相应的配置文件。
```MySQL
show global variables like 'wait_timeout%';
```
```mysql
更改等待时间
set global wait_timeout=4;
```
#### 存储过程创建
存储过程是一组已经预先编译好的<font color='orange'>sql</font>语句的集合，理解为批处理语句（增加流程控制语句），一般在复杂逻辑中才会使用存储过程
<font color='red'>存储过程的优点：</font>
- 提供了代码的可用性
- 简化了数据库操作，将业务逻辑的细节隐藏在存储过程中
- 减少了编译次数，减少了网络IO的次数，从而提高操作效率
<font color='red'>存储过程的创建</font>
```cpp
/*
delimiter $
create procedure 存储过程的名称（参数列表）
begin
局部变量的定义
多条sql语句
流程控制语句
eng;$
*/
```
如果存储过程中只有一条<font color='orange'>sql</font>语句可以省略<font color='orange'>begin end</font>
<font color='red'>参数列表</font>
| 参数模式 | 形参名称 | 参数类型 |
| :------- | -------- | ------------------------------------------------- |
| in | username | MySQL数据库中的数据类型（数值型，字符型，日期型） |
| out | pwd | MySQL数据库中的数据类型（数值型，字符型，日期型） |
| inout | xxx | MySQL数据库中的数据类型（数值型，字符型，日期型） |
<font color='red'>in</font>：声名该参数是一个输入型参数（<font color='orange'>类似Java中的形参</font>）
<font color='red'>out</font>：声名该参数为一个输出型参数（<font color='orange'>类型Java中的返回值</font>），在一个存储过程中<font color='orange'>可以定义多个out类型的参数</font>
<font color='red'>inout</font>：声名该参数可以为输入型参数，也可以为输出型参数
<font color='red'>存储过程调用</font>
```mysql
call 存储过程的名称（实参列表）
-- 实参列表中包含由输出类型的参数
```
<font color='red'>存储过程的演示</font>
- 无参的存储过程
```MySQL
-- 用于b_user表中插入2条数据
delimiter $ -- 自定义结束标志
create procedure pro_insert()
begin
insert into b_user(name,sex) values('1','1');
insert into b_user(name,sex) values('2','2');
end;$
-- 用这个存储过程
call pro_insert();
```
- 带有<font color='red'>in</font>模式参数的存储过程
```MySQL
-- 用于向b_user插入2条数据，性别由客户输入
delimiter $
create procedure pro_insert2(in sex char(1))
begin
insert into b_user(name,sex) values('1',sex);
insert into b_user(name,sex) values('2',sex);
end;$
call pro_insert2('男');
```
- 带有多个<font color='red'>in</font>模式参数的存储过程
```MySQL
-- 用于向b_user插入2条数据，性别由客户输入
delimiter $
create procedure pro_insert2(in name varchar(10),in sex char(1))
begin
insert into b_user(name,sex) values(name,sex);
insert into b_user(name,sex) values(name,sex);
end;$
call pro_insert2('name','男');
```
- 带<font color='red'>in，out</font>参数的存储过程
```MySQL
-- 判断用户登录，如果用户名和密码输入正确登录成功，否则登录失败
-- 根据输入的用户名和密码作为条件b_user表中查询，如果查询总行数==1，则认为登录成功，让resukt返回登录成功；否则登录失败
delimiter $
create procedure pro_login(in name varchar(20),in pwd varchar(10,out result varchar(20)))
begin
declare total int default 0; -- 用于存放查询总行数
select count(*) from b_user u where u.name= name and u.pwd= pwd; -- 将查询结果赋值给total局部变量
set result:= if(total=1,'登录成功','登录失败');
end;$
-- 存储过程如何执行
-- 解决判断，使用自定义变量
set @result:='';
call pro_login('李四','123',@result);
select @result;
```
- 删除存储过程
```MySQL
drop procedure 存储过程名称
```
- 查看存储过程
```MySQL
show create procedure 存储过程名称;
```
- 修改存储过程
```MySQL
drop
create
```
#### 流程控制语句
- <font color='red'>if</font>函数结构
```cpp
/*
if 逻辑表达式 then 语句1;
elseif 逻辑表达式2 then 语句2;
…………
else 语句n;
end if;
*/
```
- <font color='red'>case</font>结构
等值选择
```MySQL
case 字段|变量|表达式
when 值 then 值|语句
when 值 then 值
…………
else 值
end
```
不等值选择
```MySQL
case
when 逻辑表达式 then 语句1
……
else 语句n
end
```
- <font color='red'>循环结构</font>
while
```mysql
/*
while 逻辑表达式 do
循环体
end while;
*/
-- 需求：创建存储过程，输入一个值，返回1到该值的和
delimiter $
create procedure pro_sum(in input int,out total int)
begin
declare i int default 1;
declare sum_ int default 0;
while i<=input do
set sum_=sum_+i;
set i=i+1;
end while;
set total:=sum_;
end;$
set @result:=0;
call pro_sum(10,@result);
select @result;
```
- repeat
```mysql
repeat
循环体
until 逻辑表达式 -- 当满足逻辑表达式，跳出循环
end repeat;
delimiter $
create procedure pro_sum_loop(in input int,out total int)
begin
declare i int default 1;
declare sum_ int default 0;
repeat
set sum_:=sum_+i;
set i:=i+1;
until i>input
end REPEAT;
set total:=sum_;
end;$
set @result:=0;
call pro_sum(10,@result);
select @result;
```
### 3、存储函数
##### 1. 存储函数:
- 函数(Function)为一命名的存储程序,可带参数,并返回一计算值.
- 函数和过程的结构类似,但必须有一个return子句,用于返回函数值.
##### 2. 创建存储函数的语法:
```kotlin
create [or replace] function 函数名(参数列表)
return 函数值类型
as
PLSQL子程序体;
```
<font color='red'>注意事项:</font>
- (1) 与存储过程注意事项类似,不同的是,**必须有个返回值**;
- (2) 参数列表可以有,也可以没有.当没有时,函数名后面不要带括号.
```rust
create or replace function queryempannal(pempno in number)
return number
as
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=pempno;
return psal*12+nvl(pcomm,0);
end;
```
删除函数：
```MySQL
drop function 函数名;
```
### 4、定时任务
#### 查看任务是否开启
```MySQL
show variables like '%event_sche%';
```
开启定时策略
```MySQL
set global event_scheduler=1;
```
#### 创建定时任务
```MySQL
create event run_event
on schedule every 1 minute
on completion preserve disable
do call test_procedure ();
```
- create event day_event：是创建名为run_event的事件
- 创建周期定时的规则，意思是每分钟执行一次
- on completion preserve disable是表示创建后并不开始生效。
- do call test_procedure ()是该event(事件)的操作内容
#### 定时任务操作
查看
```MySQL
SELECT event_name,event_definition,interval_value,interval_field,status
FROM information_schema.EVENTS;
```
开启或关闭
```MySQL
alter event run_event on completion preserve enable;//开启定时任务
alter event run_event on completion preserve disable;//关闭定时任务
```
#### 定时规则
周期执行–关键字 EVERY
单位有：<font color='red'>second、minute、hour、day、week(周)、quarter(季度)、month、year</font>
```MySQL
on schedule every 1 week //每周执行1次
```
