Mysql - 进阶篇

东厂胡一刀
138 阅读9分钟

MySql目录.jpg

学习视频地址 www.bilibili.com/video/BV1Kr…

一、存储引擎

1、 mysql 体系 1.png

2、引擎介绍

2.png

3、mysql所有引擎

3.png

4、InnoDB 特点 4.png 555.png

5、MyISAM 特点

6.png

6、Memory 引擎特点

7.png

7、对比 8.png

8、选择 9.png

9、查看索引语句、和怎么选择引擎

show create table account; 可以看见默认是 InnoDb
Create table table1 {
  id ...
  name ....
} engine = innodb

二、索引

索引介绍

  • 什么是索引

2.png

  • 为什么要用索引 22.png

  • 索引的优缺点 222.png

索引的结构

  • 索引都有哪些结构 3.png

  • 由此可见大部分情况用的都是 B+Tree 的索引结构、下面会介绍B+结构的好处 33.png

数据结构介绍

333.png

  • B树的结构 3333.png

  • B+树的结构 444.png

444444.png

由此可见、索引的B+树是优化的、这样叶子节点会形成双向列表、方便范围查找

  • Hash 索引结构- 唯一标标识-不支持范围查找 5.png

55.png

555.png

6.png

索引的分类

7.png

66.png

666.png

右下图可见、二级索引查找需要、会表查找主键索引，要查两次，所以在正常情况下、聚集索引的查找速度要大于二级索引 6666.png

索引的创建

见代码

show tables ;

create table tb_user(
   id int primary key auto_increment comment '主键',
   name varchar(50) not null comment '用户名',
   phone varchar(11) not null comment '手机号',
   email varchar(100) comment '邮箱',
   profession varchar(11) comment '专业',
   age tinyint unsigned comment '年龄',
   gender char(1) comment '性别 , 1: 男, 2: 女',
   status char(1) comment '状态',
   createtime datetime comment '创建时间'
) comment '系统用户表';


INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO itcast.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

show tables ;
select  * from tb_user;

# 查询表索引
show index from tb_user;

# 给name字段添加非唯一索引
create index idx_user_name on tb_user(name);

# 给home字段添加唯一索引
create unique index idx_user_phone on tb_user(phone);

# 给prof age status 添加联合索引
create index idx_user_pro_age_status on tb_user(profession,age,status);

create index idx_email on tb_user(email);

# 删除email索引
drop index idx_email on tb_user;

# 查询表执行频率 其中包含 update insert select
show global status like 'Com_______';

SQL 执行效率监控

# 查询表执行频率 其中包含 update insert select
show global status like 'Com_______';


#查看是否支持  profile；
select @@have_profiling;

# 查看是否开启
select @@profiling;

# 开始profiles；
set profiling = 1;

# 查看所有的执行语句的执行时间
show profiles ;

# 查看指定语句的执行详情
show profile for query 51;

select COUNT(*) from tb_sku;

show index from tb_user;

select * from tb_user;

explain select * from tb_user where phone = '17799990004'

1.png

2.png

22.png

3.png

4.png

44.png

索引的使用

  • 联合索引遵守最左侧原则 5.png

  • 联合索引使用 >= 运算不要用 > 不然索引可能会失效 55.png

  • 列运算千万别丢了 引号。 不要进行运算 6.png

66.png

666.png

7.png

77.png

8.png

88.png

888.png

9.png

99.png

10.png

101.png

102.png

三、SQL优化

5.png

如上图所示、SQL优化主要是针对索引的优化

  • 插入数据

    • 插入数据最好是顺序插入、如果不是顺序插入可能会导致后面的数据全部要移动。
    • 如果是本地大量数据最好使用 local 来插入
    • 删除数据的时候、不会立马删除，会暂时保留、等攒够了一页的50%在进行删除，降低性能损耗

  • 主键优化

    • 主键长度尽量短一点、有顺序的。不要使用 身份证号码这种，没有顺序且太长了

  • 排序优化

    • 排序最好使用索引了， 注意所以联合索引的最左原则。

  • group by

    • 最好使用索引进行分组。

  • limit 优化

    • 分页查询的时候、当起始索引太大的话、查询时间就比较长了， 这个时候要使用覆盖索引、子查询来实现。 例如下面的单表查询、改为连表查询
    select * frome tb_sku, (select id frome tb_sku order by id limit 20000000, 10) a where s.id = a.id;

  • count 优化

    • count(字段) < count(主键) <count(1) 约等于 count(*)
    • 所以最好使用 count 星号 来进进行长度查询

  • update 优化。

    • 尽量使用主键或者索引进行表的行更新， 避免帮Mysql 的 行级锁、给改成了表级锁。

四、视图、过程、触发器

视图

  • 视图的作用

5.png

  • 介绍

    • 视图其实是一张虚拟表、他的数据不在数据库中实际存在、行和列数据来自定义视图的查询中使用的表。并且是在使用图时动态生成的。

  • 视图的基本操作

-- 创建视图
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;

下面这种情况是都可以插入的，就算不满足视图条件

create or replace view stu_v_1 as select id,name from student where id <= 10 with cascaded check option ;

select * from stu_v_1;

insert into stu_v_1 values(6,'Tom');

insert into stu_v_1 values(17,'Tom22');

如果加上 check option 添加数据的时候就会去检查条件。默认是cascaded 不仅会检查本身的视图条件，还会去检查依赖视图的视图条件。 local的话会去查看依赖视图有没有条件，没有条件是不会检查的

-- cascaded
create or replace view stu_v_1 as select id,name from student where id <= 15 ;

insert into stu_v_1 values(5,'Tom');

insert into stu_v_1 values(16,'Tom');


create or replace view stu_v_2 as select id,name from stu_v_1 where id >= 10 with cascaded check option ;

insert into stu_v_2 values(13,'Tom');

insert into stu_v_2 values(17,'Tom');


create or replace view stu_v_3 as select id,name from stu_v_2 where id < 20 ;

insert into stu_v_3 values(14,'Tom');


-- local
create or replace view stu_v_4 as select id,name from student where id <= 15 with local check option;

insert into stu_v_4 values(5,'Tom');

insert into stu_v_4 values(16,'Tom');


create or replace view stu_v_5 as select id,name from stu_v_4 where id >= 10 with local check option ;

insert into stu_v_5 values(13,'Tom');

insert into stu_v_5 values(17,'Tom');

insert into stu_v_5 values(18,'Tom');

create or replace view stu_v_6 as select id,name from stu_v_5 where id < 20 ;

insert into stu_v_6 values(14,'Tom');

存储过程

存储过程其实就是一个代码块、让几个SQL一起执行。从而减少客户端和MySQL服务端的链接次数。方便封装、复用、可以接收参数，也可以返回数据。较少网路交互、提升效率。

1.png

存储过程的基础语法如下：

-- 存储过程基本语法
-- 创建
create procedure p1()
begin
    select count(*) from student;
end;

-- 调用
call p1();

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

show create procedure p1;

-- 删除
drop procedure if exists p1;

在学习存储过程中、我们会学到 一些if else 语句、 和一些、循环语句。 还会学习定义变量，在学习定义变量之前会先学习使用和查看系统变变量。


-- 变量: 系统变量
-- 查看系统变量
show session variables ;

show session variables like 'auto%';
show global variables like 'auto%';

select @@global.autocommit;
select @@session.autocommit;


-- 设置系统变量
set session autocommit = 1;

insert into course(id, name) VALUES (6, 'ES');

set global autocommit  = 0;

select @@global.autocommit;


-- 变量: 用户变量
-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';

select @mycolor := 'red';
select count(*) into @mycount from tb_user;

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

select @mycolor , @mycount;

select @abc;


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

call p2();

image.png

通过案例看一下存储过程的使用

-- in/out/inout参数

-- 根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)。
-- score >= 85分，等级为优秀。
-- score >= 60分 且 score < 85分，等级为及格。
-- score < 60分，等级为不及格。

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(18, @result);
select @result;

游标的使用。 普通的变量只能接收普通的值。 没有办法接收表数据。 通过游标可以实现。 代码如下

-- 游标
-- 根据传入的参数uage，来查询用户表 tb_user中，所有的用户年龄小于等于uage的用户姓名（name）和专业（profession），
-- 并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。

-- 逻辑:
-- A. 声明游标, 存储查询结果集
-- B. 准备: 创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标

create procedure p11(in uage int)
begin
    declare uname varchar(100);
    declare upro varchar(100);
    declare u_cursor cursor for select name,profession from tb_user where age <= uage;
    declare exit handler for SQLSTATE '02000' close u_cursor;

    drop table if exists tb_user_pro;
    create table if not exists tb_user_pro(
        id int 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 p11(30);

handler 用于上面的循环判断。

触发器

就是一个可以监听表变化的东西。

1.png

  • 触发器的创建、删除、和插入触发器的演示

-- 触发器
-- 需求: 通过触发器记录 user 表的数据变更日志(user_logs) , 包含增加, 修改 , 删除 ;

-- 准备工作 : 日志表 user_logs
create table user_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) 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_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'insert', now(), new.id, concat('插入的数据内容为: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;


-- 查看
show triggers ;

-- 删除
drop trigger tb_user_insert_trigger;

-- 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26,'三皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());



-- 修改数据触发器
create trigger tb_user_update_trigger
    after update on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'update', now(), new.id,
        concat('更新之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
            ' | 更新之后的数据: id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;

show triggers ;

update tb_user set profession = '会计' where id = 23;

update tb_user set profession = '会计' where id <= 5;



-- 删除数据触发器
create trigger tb_user_delete_trigger
    after delete on tb_user for each row
begin
    insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
    (null, 'delete', now(), old.id,
        concat('删除之前的数据: id=',old.id,',name=',old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
end;

show triggers ;

delete from tb_user where id = 26;

五、锁


-- 增加全局锁
flush tables with read lock ;

-- 释放全局锁
unlock tables ;

-- 加了全局锁之后整个数据库只能查、不能写、增

-- 数据库备份。这个命令要在linux里面执行
mysqldump -uroot -p1234 dn01 > d:?db01:sql

-- 全局锁范围比较大，如果加了全局锁、业务基本上处于停止的状态

# 表级锁 、
    -- 表锁
      -- 读锁 （会阻塞其他客户端和自己的写、不会阻塞读）
         lock tables tb_user read ;  -- 加锁
         unlock tables ;  -- 解锁
      -- 写锁 （加了写锁、之后其他客户端不能读、不能写。 本客户端可以读写）
        lock tables tb_user write ;
        unlock tables ;
    -- 元数据锁  这个是系统自动控制的锁、在访问一张表的时候会自动加上。 主要是防止 MDL和DDL之间的冲突。
    -- 意向锁   就是防止行级锁、和表锁的冲突的。 避免加表锁的时候，一行行一检查每一行是否有行锁。
        -- 意向共享锁 - 和 read兼容
            select * from  tb_user where id = 1 lock in share mode ;
        -- 意向排它锁  - 和表锁都互斥。
            select * from  tb_user where id = 1 ; -- 默认就会加意向排他锁


# 行级锁
    -- 行锁：锁定单个行记录的锁、防止其他的事务对此进行 update 和 delete。
        -- 共享锁
        -- 排他锁
    -- 间隙锁。锁定索引记录间隙。确保索引记录的间隙布标防止其他事务在这个间隙进行insert。产生幻读。
    -- 临建锁。 就是行锁和间隙的组合。

1.png

2.png

3.png

4.png

5.png

6.png

avatar
文章
阅读
粉丝