SQL 进阶

空空空肘子
113 阅读4分钟

1. 存储引擎

  1. MySQL体系结构

MySQL

MySQL

  1. 存储引擎简介

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

存储引擎简介

create table my_myisam(
    id int,
    name varchar(10),
)engine = MyISAM;
  1. 存储引擎特点

InnoDB

InnoDB

MyISAM

Memory

differences

  1. 存储引擎选择

存储引擎选择

MyISAM 被 mongoDB 取代，Memory 被 redis 取代

2. 索引 index

2.1 索引概述

index

优缺点

2.2 索引结构

2.2.1 简介

索引结构

索引引擎的支持情况

2.2.2 B+ tree

二叉树

红黑树是自平衡二叉树

B树 B树：多路平衡查找树。 Degree代表一层有几个指针，最多存储数据的个数为（degree - 1）。 如上图，最上面一层存储4个元素：20、30、62、89。5个指针分别为 <20、>20 && <30、> 30 && < 62、>62 && <89、>89。

插入时，如果一层的元素个数大于degree - 1，那么中间元素向上分裂，层数 + 1。

B+

B+树

  • 所有元素都会在叶子节点储存，树型结构只作为索引
  • 叶子节点形成单向链表

B+树

2.2.3 Hash

hash

hash+

为什么InnoDB存储引擎选择B+ tree？

  • 相对于二叉树，层级更少，搜索效率高
  • 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。
  • 对于Hash，B+支持范围匹配及排序操作

2.3 索引分类

索引分类

索引分类InnoDB

索引分类例 聚集索引叶子节点挂整行数据，二级索引叶子节点挂primary key（🆔）

回表查询 先在二级索引中找到编号，然后再利用标号在聚集索引中找到并返回整行信息。

思考题

思考题 上面的效率高，上面只需要查找聚集索引。下面需要先查二级索引，再通过二级索引查到的id，查聚集索引。

  1. InnoDB主键索引的B+ tree高度有多高？

思考2

2.4 索引语法

索引语法

案例

--- 1
create index idx_user_name on tb_user(name);
show index from tb_user;

--- 2
create unique index idx_user_phone on tb_user(phone);

--- 3
create index idx_user_pro_age_sta on tb_user(profession, age, status);

--- 4
create index idx_user_email on tb_user(email);

--- delete
drop index idx_user_email on tb_user;

2.5 SQL性能分析

查看执行频次

慢查询日志

profile

profile details

explain

explain2

2.6 索引使用

  1. 最左前缀法则：如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

  2. 范围查询：联合索引中，出现范围查询（>，<），范围查询右侧的列索引失效。

  3. 索引列运算：在索引列上进行运算操作，索引将失效

  4. 字符串不加引号：字符串类型使用时不加引号，索引将失效。

  5. 模糊查询：尾部模糊匹配，索引不失效。头部模糊匹配，索引失效。

  6. or连接的条件：用or分割开的条件，如果or前的条件中有索引，or后的条件没有索引，那么涉及的索引都不会被用到。

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

  8. SQL提示：SQL提示，是优化数据库的一个重要手段。简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

    SQL提示

  9. 覆盖索引：尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部都能找到），减少使用select *。

  10. 前缀索引： 前缀索引

  11. 单列索引和联合索引： 单列索引和联合索引

2.7 索引设计原则

索引设计原则

3. SQL优化

3.1 插入数据

insert

insert_lot

3.2 主键优化

页分裂

页合并

主键设计原则

3.3 order by 优化

order by 优化

order by 优化2

order by 优化3

3.4 group by 优化

group by 优化

3.5 limit 优化

limit 优化

3.6 count 优化

count 优化

3.7 update 优化

update 优化

4. 视图/存储过程/触发器

4.1 视图

视图

视图操作

--- 创建视图
create or replace view stu_v_1 as select id, name from student where id <= 10;

--- 查询视图
show create view stu_v_1;
select * from stu_v_1;
select * from stu_v_1 where id < 3;

--- 修改视图
create or replace view stu_v_1 as select id, name,no from student where id <= 10;
alter view stu_v_1 as select id, name from student where id <= 10;

--- 删除视图
drop view if exists stu_v_1;

视图的检查选项

cascaded：不止check本身，也要check依赖的其他view

image.png local：看依赖的view有没有check语句，有就check，没有就不check。

update view

advantage of view

例：

--- 1.开发人员在操作users表时，只能看到用户的基本字段，屏蔽手机号和邮箱
create view user_view as select id,name,profeesion,age,gender,status from user;
select * from user_view;
--- 2. 查询每个学生所选修的课程（三张表联查），这个功能在很多业务中用到，为了简化操作，定义一个视图。

create view stu_course_view as select s.name student_name, s.no student_name, c.name course_name from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;

4.2 存储过程

存储过程 特点：

  • 封装、复用
  • 可以接受参数，也可以返回数据
  • 减少网络交互，效率提升

基本操作

基本操作2

--- 存储过程基本语法
--- 创建
create procedure p1()
begin
    select count(*) from student;
end$$
delimiter $$  -- 重新定义结束符号
delimiter ; -- 复原分号结束
-- 调用
call p1();

-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'it';
show create procedure p1;
-- 删除
drop procedure if exists p1;

系统变量

--- 变量：系统变量
--- 查看系统变量
show session variables;
show session variables like 'auto%';
select @@global.autocommit;
select @@session.autocommit;
--- 设置系统变量
set session autocommit = 0;  -- 关闭自动提交开关
insert into course(id, name) values (5, 'joey');
commit;

用户定义变量

--- 变量： 用户变量
-- 赋值
set @myname = 'it';
set @myage := 10;  
set @mygender := 'M', @myhobby := 'java';
select count(*) into @mycount from tb_user;
--- sql中比较运算和赋值都是=(没有==)，为了区分，赋值建议使用 :=

-- 使用
select @myname, @myage, @mygender, @myhobby;

局部变量

--- 变量：局部变量
-- 声明 declare
-- 赋值
create prcedure p2()
begin
    declare stu_count int default 0;
    select count(*) into stu_count from student;
    select stu_count;
end;
call p2();

一些操作

  1. if image.png

    create procedure p1()
begin
    declare score int default 58;
    declare result vachar(10);
    if score >= 85 then 
        set result := 'A';
    elseif score >= 60 then
        set result := 'B';
    else
        set result := 'D';
    end if;
    select result;
end;
call p1(); -- output D

  2. 参数

    参数

    create procedure p2(in score int, out result varchar(10))
begin
    if score >= 85 then 
        set result := 'A';
    elseif score >= 60 then
        set result := 'B';
    else
        set result := 'D';
    end if;
end;
call p2(score: 68, result: @result);
select @result;

--- 换算成50分制 --> inout
create procedure p3(inout score double)
begin
    set score := score * 0.5;
end;
set @score = 78;
call p3(score: @score);
select @score; -- 39

  3. case

    case

    --- 根据传入月份，判定季节
create procedure p4(in month int)
begin
    declare result varchar(10);
    case
        when month >= 1 and month <= 3 then
            set result := 'Spring';
        when month >= 4 and month <= 6 then
            set result := 'Summer';
        when month >= 7 and month <= 9 then
            set result := 'Fall';
        when month >= 10 and month <= 12 then
            set result := 'Winter';
        else
            set result = 'unkonwn';
     end case;
     select concat('month is', month ,'season is', result);
 end;
 
 call p4(month: 4);

  4. 循环 while

    while

    --- 计算从1到n的值，n为传入的参数值
create procedure p5(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 p4(10); -- 55

  5. 循环 repeat

    repeat

     --- 计算从1到n的值，n为传入的参数值
 create procedure p6(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 p5(10);  -- 55

  6. 循环 loop

    loop

    --- 计算从1到n的值，n为传入的参数值
create procedure p7(in n int)
begin
    declare total int default 0;
    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 p7(10); -- 55

---  计算从1到n的偶数值，n为传入的参数值
create procedure p8(in n int)
begin
    declare total int default 0;
    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 p8(10); -- 30

  7. 游标 cursor

    cursor

    --- 根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户name和profession
--- 并讲用户的name和profession插入到所创建的一张新表（id，name，orofession）中
--- 步骤1:声明cursor，存储查询结果
--- 2 准备：创建表结构
--- 3 开启cursor
--- 4 获取cursor中的记录
--- 5 插入数据到新表中
--- 6 关闭游标
create procedure p9(int uage int)
begin
    declare uname varcahr(100);
    decalre upro varcahr(100); -- 先声明普通变量再声明cursor
    declare u_cursor cursor for select name, profession from tb_user where age <= uage;

    
    drop table if exists tb_user_pro;
    create table if not exist tb_user_pro(
        int id primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    
    open u_cursor;
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values(null, uname, upro);
    end while;
    close u_cursor;
end;

call p9(40);  -- 查询age <=40

  8. 条件处理 handler

    handler

    --- 根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户name和profession
--- 并讲用户的name和profession插入到所创建的一张新表（id，name，orofession）中
--- 步骤1:声明cursor，存储查询结果
--- 2 准备：创建表结构
--- 3 开启cursor
--- 4 获取cursor中的记录
--- 5 插入数据到新表中
--- 6 关闭游标
create procedure p9(int uage int)
begin
    declare uname varcahr(100);
    decalre upro varcahr(100); -- 先声明普通变量再声明cursor
    declare u_cursor cursor for select name, profession from tb_user where age <= uage;
    
    declare exit handler for SQLSTATE '02000' close u_cursor;-- 声明handler，触发退出while，并关闭游标

    
    drop table if exists tb_user_pro;
    create table if not exist tb_user_pro(
        int id primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    
    open u_cursor;
    while true do
        fetch u_cursor into uname,upro;
        insert into tb_user_pro values(null, uname, upro);
    end while;
    close u_cursor;
end;

call p9(40);  -- 查询age <=40

4.3 存储函数

存储函数

---  计算从1到n的偶数值，n为传入的参数值
create function fun1(n int)
returns int deterministic
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;s
        set n := n - 1;
    end while;
    return total;
end;
select fun1(10); -- 55

4.4 触发器trigger

触发器

trigger语法

练习

--- 插入数据触发器
create trigger tb_user_insert_trigger
    after insert on tb_user for each row
begin
    insert into user_logs(id, operation, oprate_time, operate_id, operate_params) values (null, 'insert', now(), new.id, concat('data is',new.id,new.name, new.phone, new.email))
end;

-- 查看触发器
show triggers;

-- 删除
drop trigger tb_user_insert_trigger;

5. 锁

锁简介

锁分类

5.1 全局锁

全局锁

全局锁2

全局锁3

5.2 表级锁

表级锁

5.2.1 表锁

表锁

5.2.2 元数据锁

元数据锁

5.2.3 意向锁

意向锁

意向锁2

5.3 行级锁

行级锁

5.3.1 行锁

行锁

行锁2

行锁3

5.3.2 间隙锁 && 临键锁

间隙锁 && 临键锁

6. InnoDB引擎

6.1 逻辑存储结构

逻辑存储结构

6.2 架构

总架构

6.2.1 内存架构

buffer pool

change buffer

adaptive hash index

log buffer

6.2.2 磁盘结构

磁盘结构1

磁盘结构2

磁盘结构3

6.2.3 后台线程

后台线程

6.3 事务原理

6.3.1 事务

事务概念

事务分类 原子性：undo log 持久性：redo log 一致性：undo log + redo log 隔离性：锁 + MVCC

6.3.2 redo log 物理日志

redo log

6.3.3 undo log 逻辑日志

undo log

6.3.4 MVCC 多版本并发控制

6.3.4.1 基本概念

MVCC基本概念

6.3.4.2 实现原理1：隐藏字段

隐藏字段

6.3.4.3 实现原理2: undo log

undo log1

undo log2

6.3.4.4 实现原理3: readview

readview1

readview2

7. MYSQL管理

7.1 系统数据库

系统数据库

7.2 常用工具

mysql

mysqladmin

mysqlbinlog

mysqlshow

mysqldump

mysqlimport

avatar
文章
阅读
粉丝