MySQL进阶

（一）、存储引擎

MySQL的体系结构

连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限

服务层：第二层架构主要完成大多数的核心服务功能、如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等

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

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

1.1 存储引擎简介

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

MySQL默认存储引擎：InnoDB

创建表时可以指定存储引擎：

查看当前数据库支持的存储引擎：show engines;

1.2 InnoDB

是一种兼顾高可靠性和高性能的通用引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎

特点：

文件：每张表都对应一个表空间文件，后缀是.ibd

参数是控制是否每一张表都对应一个表空间文件，默认是打开的
查询参数：show variables like 'innodb_file_per_table';

逻辑存储结构：

1.3 MyISAM和Memory

MyISAM是MySQL早期默认引擎

特点：

此存储引擎涉及到的文件：

Memory引擎的表数据是存放在内存中的，由于受到硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用

特点：
1.内存存放
2.支持hash索引（默认）

文件：
xxx.sdi：存储表结构信息

三种引擎的特点：

1.4 引擎的选择

（二）、安装MySQL（Linux版本）

Linux安装：http://t.csdn.cn/CZGgT

MySQL8.0.33版本安装：http://t.csdn.cn/pDm4i

远程连接失败需要关闭Linux的防火墙：systemctl stop firewalld.service
查看防火墙状态：firewall-cmd --state
登录mysql： mysql -u root -p

（三）、索引

索引的优缺点：

3.1 索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构

不同引擎对索引的支持情况：

平常说的索引，一般默认都是B+tree索引

3.2 Btree

多路平衡查找树
如果最大度数为n，则一个节点下最多n个子节点（每个节点最多存储n-1个key，n个指针）

以最大度数为5的Btree为例子，一个节点下最多5个子节点（每个节点最多存储4个key，5个指针）

3.3 B+tree

所有数据会出现在叶子节点
叶子节点形成一个单向链表

3.4 hash

哈希所有就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中
如果产生hash碰撞，使用链表解决

特点：

在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的

3.5 为什么InnoDB引擎使用B+tree

3.6 索引分类

在InnoDB的存储引擎当中，根据索引的存储形式，又可以分为下列两种

二级索引数据底下挂的是对应的主键。先查询二级索引，再查聚集索引（回表查询）

聚集索引的选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

3.7 索引语法

例：

create unique index id on tb_stu(id); -- 创建索引
show index from tb_stu;-- 查看此表的索引
drop index id on tb_stu; -- 删除索引

3.8 SQL性能分析

3.8.1 SQL执行频率

com后面是7个下划线

3.8.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志

MySQL的慢查询日志默认没有开启，需要在MySQL中的配置文件（/etc/my.cnf）中配置下列信息

show  variables like 'slow_query_log'; -- 查看慢查询日志有没有开启

1.在虚拟机中修改配置文件，先找到mysql位置

which mysql

2.在路径后添加 --verbose --help|grep -A 1 'Default options'

3.vi /etc/my.cnf 进入vi编辑器
vi命令详解：http://t.csdn.cn/qSUb1

修改完后重启mysql：systemctl restart mysqld

3.8.3 show profiles

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过have_profiling参数，查看当前MySQL是否支持

默认的profiling是关闭的，可以通过set语句在session/global级别开启profiling

查看指令的执行耗时：

3.8.4 explain

explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中标如何连接和连接的顺序

语法：

explain执行计划各字段含义：

1. id： id大，先查询
   

2. select_type： 查询类型

3. type：
   

4. possible_key：

   显示可能应用在这张表上的索引，一个或多个

5. key：
   实际使用到的索引，如果为NULL，表示没有用到索引

6. key_len：
   

7. rows：
   

8. filtered：
   表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

9. extra
   在前面这些字段没有展示出来的信息，会在此展现

3.9 索引使用规则

最左前缀法则：

只有当最左边的索引存在才会触发最左前缀法则，才会走联合索引

在联合索引中，出现范围查询，范围查询右侧的列索引失效
例：pas是联合索引，但是此时status的索引就不会走

使用=，可以规避这种情况

索引失效一：
不要在索引列上进行运算操作，索引将失效
字符串类型字段使用时，不加引号，索引将失效

尾部使用模糊匹配，索引不失效，头部进行模糊匹配，索引失效

索引失效二：
or连接的条件：

数据分布影响：
如果MySQL评估使用索引会比全表更慢，则不使用索引

3.10 SQL提示

在SQL语句中加入一些人为的提示来达到优化操作的目的
use：建议使用此索引
ignore：忽视此索引
force：强迫使用此索引

3.11 覆盖索引&回表查询

覆盖索引：简单来说就是二级索引可以找到所有需要返回的值，不需要回表查询
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少使用select *

extra出现的内容：

回表查询（查了二级索引，再查聚集索引）性能低于覆盖索引

3.12 前缀索引

一般用于长字符串或者文本

语法：n代表抽取前n个字符，建立索引

前缀长度：

求选择性：
先求该字段列表下的总共有多少条数据，再求出不重复的数据有多少条
用不重复的数据条数 / 总共数据条数

截取前n个字符时，尽量让选择性为1

3.13 单列索引与联合索引

单列索引：一个索引只包含单个列
联合索引：一个索引包含了多个列

如果存在多个查询条件，针对于查询字段建立索引时，建议建立联合索引，而非单列索引

多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询

尽量使用联合索引，性能更好

3.14 索引设计原则

（四）、SQL优化

插入数据优化

批量插入：建议数据量在500-1000
主键顺序插入性能高于乱序插入

大批量插入数据：使用load语句

语法：

第一个by指的是每个字段间用逗号分隔，后面/n指的是每行数据用/n分隔

4.1 主键优化

数据组织方式：
在InnoDB的存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

页分裂：主键乱序插入会发生的现象
页可以为空，也可以填充一半，也可以填充100%，每个页包含了2-N行（如果一行就是链表了）数据（如果一行数据过大，会行溢出），根据主键排列

页合并：

merge_threshold：合并页的阈值，可以自己设置，在创建表或者创建索引时指定

主键设计原则：

4.2 order by优化

索引默认是从小到大排的（升序），A就是升序，D就是降序

多个字段排序，如果一个升序一个降序，就会出现FileSort排序
可以创建一个数据升序，一个数据降序的联合索引

优化：

4.3 group by优化

直接分组 会出现 using temporary（临时表）
尽量添加索引，提高分组效率
分组操作时，索引的使用也满足最左前缀法则

4.4 limit优化

limit越往后排序，性能越低

优化：创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式优化

4.5 count优化

优化：自己计数

count的几种用法：

count（字段）：返回的是当前不为null的数据有多少条，不一定是总记录数

count（主键）：

count（1）：里面的数字就是，每一行放进去的数字n

count（*）：

效率排序：

4.6 update优化

InnoDB的行锁是针对索引加的锁，更新时要以索引为条件，并且该索引不能失效，否则会从行锁升级成表锁

（五）、视图

往视图里添加的数据，都是保存在基表当中

语法：插入还是用insert

视图检查选项：

使用cascaded，它会检查当前视图，并且会检查依赖的视图。
如下图，数据必须满足v1和v2的条件（即使v1没有检查，但形成了依赖关系，v1相当于被加了一个cascaded检查），但是v3没有检查，只要数据满足v1，v2条件，哪怕不满足v3条件也可以添加

Local：会递归的去找当前视图所依赖的视图，只有当后面有检查选项，才会判断是否满足这个视图的条件
先检查自己，如果有依赖关系，会找上级是否有加检查选项，没有就不检查是否满足上级的条件，有才检查

对v3添加数据，因为自身没有检查选项，所以找依赖v2，有检查选项，数据需要满足v2的条件，v1没有检查选项，则不检查。既数据只需满足v2视图的条件

更新：

作用：简单，安全，数据独立

（六）、存储过程

特点：

1. 封装，复用
2. 可以接收参数，也可以返回数据
3. 减少网络交互，效率提升

基本语法：
创建：

注：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符
delimiter +SQL语句的结束符号

调用：

查看：

删除：

-- 创建
create procedure p1()
begin
    select count(*) from  tb_stu;
end;

-- 调用
call p1();

-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='itheima';

show create procedure p1;

-- 删除
drop procedure if exists p1;

6.1 变量

• 系统变量：由MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（global），会话变量（session）

语法：查看系统变量时，如果不指定session和global，默认是session

MySQL重启过后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf 中配置

• 用户自定义变量：是用户根据自己需要定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”就可以。其作用域为当前连接

语法：赋值时尽量使用：=

注：用户自定义变量无需对其进行声明和初始化，只不过获取到的值为NULL

• 局部变量：是根据需要定义的在局部生效的变量，访问之前，需要declear声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明的begin end块。

语法：

-- 声明，赋值（用into）
create procedure p2()
begin
    declare stu_count int default 0;
    select count(*) into stu_count from tb_stu;
    select stu_count;
end;

6.2 if判断

语法：

练：

create procedure p3()
begin
    declare score int default 58;
    declare result varchar(10);
    if  score>=85 then
        set result :='优秀';
        elseif score>=60 then
        set  result :='及格';
        else
            set result:='不及格';
    end if;
    select result;

end;

call p3();-- 查看结果

6.3 参数

语法：

练：

create procedure p4(in score int,out result varchar(10))
begin
    if  score>=85 then
        set result :='优秀';
    elseif score>=60 then
        set  result :='及格';
    else
            set result:='不及格';
    end if;
end;

call p4(65,@result);-- 用用户自定义变量接收结果
select @result;

练2：

create procedure p5(inout score double)
begin
    set score := score*0.5;
end;

set @score=78;
call p5(@score);-- 先为score赋值78，传入值78，然后再接收返回的值
select @score;

6.4 case

语法一：

语法二：

练：

create procedure p6(in month int)
begin
    declare result varchar(10);

    case
        when    month>=1 and month<=3 then
        set result :='第一季度';
        when    month>=4 and month<=6 then
        set result :='第二季度';
        when    month>=7 and month<=9 then
        set result :='第三季度';
        when    month>=10 and month<=12 then
        set result :='第四季度';
        else
            set result:='非法参数';
    end case ;

    select concat('您输入的月份为：',month,'所属季度为：',result);
end;

call p6(7);

6.5 循环

1. while循环：有条件的循环语句，满足条件，再执行循环体中的SQL语句。
   语法：
   
   练：
   

create procedure p7(in n int)
begin
    declare total int default 0;
    while n>0 do
        set total :=total+n;
        set n:=n-1;
        end while;
    select total;
end;

call p7(10);

2. repeat循环：repeat是有条件的循环控制语句，当满足条件的时候退出循环。

语法：

练：

create procedure p8(in n int)
begin
    declare total int default 0;
    repeat
        set total:=total+n;
        set n:=n-1;
    until n<=0
    end repeat;

    select total;
end;

call p8(10);

3. loop循环
   
   语法：
   
   练：
   

-- 1.

create procedure p9(in n int)
begin
    declare total int default 0;
    sum:loop -- sum相当于给这个loop循环取个名字
    if n<=0 then
        leave sum;
    end if;

    set total:=total+n;
    set  n:=n-1;
    end loop sum;

    select  total;
end;

call p9(10);


-- 2.
create procedure p10(in n int)
begin
    declare total int default 0;
    sum:loop -- sum相当于给这个loop循环取个名字
    if n<=0 then
        leave sum;
    end if;

    if n%2=1 then
        set n:=n-1;
        iterate sum; -- 跳过此次循环不再执行之后的语句，进入下一次循环
    end if;

    set total:=total+n;
    set  n:=n-1;
    end loop sum;

    select  total;
end;


call p10(10);

6.6 游标

语法：

create procedure p11(in uage int)
begin
    declare uname varchar(100); -- 变量的声明要在游标之前
    declare ugender char(1);
    declare u_cursor cursor for select name,gender from tb_stu where age<=uage;
    declare exit handler for sqlstate '02000' close u_cursor;-- 当底下的循环报错时，捕获报错的状态码，退出此程序，并关闭游标

    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(100),
        gender char(1)
    );

    open u_cursor;

    while true do -- 当游标里的数据读取完，会报错
        fetch u_cursor into uname,ugender;
        insert into tb_user_pro values (null,uname,ugender);
    end while;
    close u_cursor;
end;

call p11(40);

6.7 条件处理程序

可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤
语法：

可以不写具体的状态码，用底下的字符表示

（七）、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型，
语法：

练：累加1-n

create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;

    while n>0 do
        set total:=total+n;
        set n:=n-1;
        end while;

    return total;
end;


select fun1(50);

（八）、触发器

行级：影响了一行，就会触发一次。语句级（无论影响几行，触发一次）

触发器类型：

语法：

例：

-- 创建日志表
create table user_log(
    id  int not null auto_increment,
    operation varchar(20) not null  comment '操作类型，insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int not null comment '操作的id',
    operate_params varchar(500) comment '操作参数',
    primary key (`id`)
)engine=innodb default charset =utf8;


-- 插入数据的触发器
create trigger tb_user_insert_trigger
    after insert on tb_stu for each row
    begin
        insert into user_log(id, operation, operate_time, operate_id, operate_params) values
          (null,'insert',now(),new.id,
           concat('插入的数据内容为：id=',NEW.id,',name=',NEW.name,',gender=',NEW.gender));

    end;
-- 查看
show triggers ;

-- 删除
drop trigger tb_user_insert_trigger;

-- 插入数据
insert into tb_stu values (null,'三1','男',29);


-- 修改数据的触发器

create trigger tb_user_update_trigger
    after update on tb_stu for each row
    begin
        insert into user_log(id, operation, operate_time, operate_id, operate_params) values
          (null,'update',now(),NEW.id,
           concat('更新之前的数据为：id=',OLD.id,',name=',OLD.name,',gender=',OLD.gender,
             ' | 更新之后的数据为：id=',NEW.id,',name=',NEW.name,',gender=',NEW.gender ));
end;

-- 更新数据
update tb_stu set age=33 where id=1;



-- 删除数据的触发器


create trigger tb_user_delete_trigger
    after delete on tb_stu for each row
    begin
        insert into user_log(id, operation, operate_time, operate_id, operate_params) values
          (null,'delete',now(),OLD.id,
           concat('删除之前的数据为：id=',OLD.id,',name=',OLD.name,',gender=',OLD.gender ));
end;

-- 删除数据
delete from tb_stu where id=2;

（九）、锁

锁的分类：

9.1 全局锁

语法：
加全局锁：flush tables with read lock;
解锁：unlock tables

一致性备份数据：mysqldump是用来备份数据的工具

特点：

在InnoDB的引擎当中，我们可以通过备份时加上参数 --signle–transaction 完成不加锁的一致性数据备份
语法：

9.2 表级锁

9.2.1 表锁

分为：

1. 表共享读锁（read）：会阻塞其他客户端的写，不会阻塞读
2. 表独占写锁（write）：会阻塞其他客户端的读，写

语法：

9.2.2 元数据锁

为了避免DML和DDL冲突，保证读写的正确性。此锁是自动加锁

对一张表增删改查时，加MDL读锁（共享），当对表结构进行变更操作时，加MDL写锁（排他）：

查看元数据锁：

9.2.3 意向锁

先加行锁，再加表锁
1.意向共享锁：由语句select，lock in share mode添加
2.意向排他锁：由insert、update、delete、select…for update添加

兼容性：

查看意向锁及行锁的加锁情况：

9.3 行级锁

每次操作锁住对应的行数据，锁定力度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中
InnoDB数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

9.3.1 行锁

锁定单个行记录，防止其他事务对此进行update和delete。在rc、rr隔离级别下都支持

两种类型的行锁：

加锁类型：

注：

9.3.2 间隙锁

锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在rr隔离级别下都支持。
间隙锁唯一目的是防止其他事务插入间隙。间隙可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁

注：

9.3.3 临键锁（next-key）

行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在rr隔离级别下支持

（十）、InnoDB引擎

10.1 逻辑存储结构

有表空间，段，区，页，行
表空间：idb文件，一个mysql实例可以对应多个表空间，用于存储记录、索引等数据

段：

区：

页：

行：InnoDB引擎数据就是按行存放的

10.2 架构

内存结构：

Buffer Pool：缓冲池

change buffer：更改缓冲区。可以减少磁盘io，提高效率

adaptive hash index ：自适应哈希索引

log buffer:日志缓冲区

磁盘结构：

系统表空间：

每个表的独立表空间：开启表示每个表会有独立表空间文件

通用表空间：

撤销表空间：

临时表空间：

双写缓冲区：

重做日志：

后台线程：四组后台线程

10.3 事务原理

事务的四大特性：
其中原子性，一致性，持久性是由redo log和undo log保证的
隔离性是由锁和MVCC保证的

10.3.1 redolog

主要用于脏页刷新，恢复数据

数据是先从缓冲池到redolog中，再将数据刷新进磁盘（WAL）

10.3.2 undolog

主要用于回滚数据和MVCC

10.4 MVCC

MVCC：多版本并发控制

当前读：

快照读：

10.4.1 隐藏字段

10.4.2 undolog版本链

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链，链表头部是最新的旧记录，链表尾部是最早的旧记录

10.4.3 readview

版本链数据访问规则：

不同隔离级别下，生成readview的时机不同

1. read committed：在事务中每一次执行快照读时生成readview （根据规则一个一个进行对比，找到符合条件的数据）
2. repeatable read：仅在事务中第一次执行快照读时生成readview，后续复用该readview （套用规则与RC一样）

（十一）、 MySQL管理

安装完MySQL，自带四个数据库

11.1 常用工具

1. mysql ：客户端工具
   语法：
   
   可以不连接mysql数据库完成sql语句，一般用来处理脚本文件

2. mysqladmin：执行管理操作的客户端程序。可以用来检查服务器当前配置和状态，创建并删除数据库等
   语法：
   

3. 服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的文本格式，就会用到mysqlbinlog
   mysqlbinlog：日志管理工具
   语法：
   

4. mysqlshow：客户端对象查找工具，用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引
   语法：
   

5. mysqldump：用来备份数据库或在不同数据库之间进行数据迁移，备份内容包含创建表，及插入表的SQL语句
   语法：
   
   备份要往mysql信任的目录下备份，不然-T会报错
   

6. mysqlimport/socure：客户端数据的导入工具，用来导入mysqldump加-T参数后导出的文本文件
   语法：
   
   如果导入sql文件，可以使用mysql中的socure指令（要在命令行使用）：