MySQL

MySQL

MySQL 基础

函数

字符串函数

select concat('hello', 'world');-- 字符串拼接

select upper('hello world');-- 大写

select lower('HELLO WORLD');-- 小写

select lpad('hello world', 20, '*');-- 字符串左填充

select rpad('hello world', 20, '*');-- 字符串右填充

select trim(' hello   worl d ');-- 去除字符串首尾空格

select ltrim(' hello worl d');-- 去除字符串左空格

select rtrim(' hello worl d ');-- 去除字符串右空格

select substring('hello world', 1, 3);-- 字符串截取

select replace('hello world', 'hello', 'hi');-- 字符串替换

数值函数

elect ceil(5.6);-- 上取整

select floor(5.6);-- 下取整

select mod(8, 3);-- 取余

select power(5, 3);-- 取幂

select rand();-- 随机数

select round(5.6);-- 四舍五入

日期函数

select curdate();-- 当前日期

select curtime();-- 当前时间

select now();-- 当前时间

select date_add(now(), interval 9 day);-- 求相加后的时间

select datediff(now(), '2020-05-01');-- 求两时间相隔天数

多表查询

内连接

隐式内连接

select emp.name as '员工名称', dept.name as '部门名称'
from emp,
     dept
where emp.dept_id = dept.id;

显式内连接

select e.name as '员工名称', d.name as '部门名称'
from emp e
         join dept d on e.dept_id = d.id;

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

外连接

select e.name, d.name as '部门名称'
from emp e
         left join dept d on e.dept_id = d.id;

左外连接相当于查询表 1（左表）的所有数据，当然也包含表 1 和表 2 交集部分的数据。

select d.name, e.name
from emp e
         right join dept d on e.dept_id = d.id;

右外连接相当于查询表 2（右表）的所有数据，当然也包含表 1 和表 2 交集部分的数据。

自连接

自连接查询是自己连接自己，也就是把一张表连接查询多次

-- 查询所有员工 emp 及其领导的名字
select e.name as '员工名称', m.name as '上级名称'
from emp e
         join emp m on e.managerid = m.id;

在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

联合查询

把多次查询的结果合并起来，形成一个新的查询结果集。的多张表的列数必须保持一致，字段类型也需要保持一致。

-- 查询工资小于5000和大于10000的员工
select *
from emp
where salary < 5000
union
select *
from emp
where salary > 10000;

子查询

标量子查询--子查询结果为单个值

-- 查询学工部的员工
select *
from emp
where dept_id = (select id from dept where name = '学工部');

列子查询--子查询返回的结果是一列

select *
from emp
where dept_id in (select id from dept where name = '学工部' or name = '教研部');

select *
from emp
where dept_id not in (select id from dept where name = '学工部' or name = '教研部');

select *
from emp
where salary > all ((select salary from emp where dept_id = (select id from dept where name = '学工部')));

select *
from emp
where salary > any ((select salary from emp where dept_id = (select id from dept where name = '学工部')));

行子查询--子查询返回的结果是一行

select *
from emp
where (salary, managerid) = (select salary, managerid from emp where name = '吴用');

表子查询--子查询返回的结果是多行多列

select *
from emp
where (salary, managerid) in (select salary, managerid from emp where name = '吴用' or name = '武松');

select e.name, d.name as '部门名称'
from (select * from emp where entry_date > '2015-01-01') e
         left join dept d on e.dept_id = d.id;

事务

原子性，持久性，隔离性，一致性

事务状态及打开方式

select @@autocommit-- 用于查看事务状态，1(默认)为自动开启事务，0为手动开启事务，需要
set autocommit = 0。

控制事务方式：

• 自动开启事务时

start transaction;-- 开启事务

select *
from account
where name = '张三';
update account
set money = money - 100
where name = '张三';
update account
set money = money + 100
where name = '李四';

commit;-- 提交事务

rollback;-- 回滚事务

• 手动开启事务时

select *
from account
where name = '张三';
update account
set money = money - 100
where name = '张三';
update account
set money = money + 100
where name = '李四';

commit;
rollback;

隔离级别

SELECT @@TRANSACTION_ISOLATION; -- 查看事务隔离级别
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | 
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }-- 设置事务隔离级别 

事务隔离级别越高，数据越安全，但是性能越低。

MySQL 进阶

存储引擎

InnoDB（默认）引擎与 MyISAM 引擎的区别：

1. InnoDB 支持事务，而 MyISAM 不支持
2. InnoDB 支持行锁和表锁，而 MyISAM 只支持表锁
3. InnoDB 支持外键，而 MyISAM 不支持

索引

索引结构

B+tree 的特点是：

1. 只有叶子节点保存行数据。
2. 所有叶子节点形成一个双向链表

为什么 InnoDB 存储引擎选择使用 B+tree 索引结构？

1. 相较于二叉树，树的层级更低，在千万级数据下也才 3-4 层。
2. 相较于 hash，支持范围查询。
3. 相较于 Btree，非叶子节点只保存指针而没有键值，一页中能保存的指针数量更多，层级更低。

索引分类

MySQL 中索引分为主键索引、唯一索引、常规索引、全文索引四种

聚集索引&二级索引

在 InnoDB 存储引擎中，索引按照存储类型分为两类，其中聚集索引必须存在，且只有一个。

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。

• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

• 如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。

• 聚集索引的叶子节点下面挂的是其对应的行数据。

• 二级索引的叶子节点下面挂的是其对应的主键值。

回表查询：先到二级索引中查找数据得到主键值，再返回聚集索引中查找数据得到行数据。

以下两条 SQL 语句，那个执行效率高？ 为什么？ A. select * from user where id = 10 ; B. select * from user where name = 'Arm' ; 备注： id 为主键，name 字段创建的有索引；

答：A

• A 语句直接走聚集索引，直接返回数据。
• 而 B 语句需要先查询 name 字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

索引语法

show index from user;-- 查看索引
create unique index idx_name on user(name);-- 创建非空索引
create index idx_phone_email on user(phone,email);-- 创建联合索引
create index idx_profession on user( profession);-- 创建普通索引
drop index idx_name on user ;-- 删除索引

索引使用，失效情况

最左前缀法则

联合索引要遵守最左前缀法则。最左前缀法则指只有出现某一联合索引最左边的列时，该索引才会生效，否则全表扫描，若中间字段没有，则该字段后续的索引都失效。

现在有一个三字段联合索引 idx_profession_status_phone

-- 三字段都存在
explain select * from user where status='1' and phone='84958948374' and profession='软件工程'；

-- 缺失最左字段profession
explain select * from user where status='1' and phone='84958948374';

-- 缺失status字段，phone也会失效
explain select * from user where profession='软件工程'and phone='84958948374';

最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段（即是第一个字段）必须存在，与我们编写 SQL 时，条件编写的先后顺序无关。

范围查询

explain select * from tb_user where profession = '软件工程' and age >= 30 and 
status = '0';

若使用范围查询（>），则该范围查询右边的列索引失效，但>=索引不会失效。

运算操作

explain select * from user where substring(profession,3,2)='工程'

索引字段进行运算操作，索引会失效。

模糊匹配

explain select * from user where profession like '软件%';-- 正常
explain select * from user where profession like '%工程';-- 失效

• 模糊匹配中，后面模糊，索引正常。
• 模糊匹配中，前面模糊，索引失效。

字符串不加引号

explain select * from tb_user where profession = '软件工程' and age = 31 and status 
= '0'; -- 正常
explain select * from tb_user where profession = '软件工程' and age = 31 and status 
= 0;-- 失效

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

or 连接条件

or 分割时，必须两边都有索引时，才会索引生效，只要有一边没有索引，则索引失效。

数据分布影响

如果 MySQL 评估使用索引比全表更慢，则不使用索引。

SQL 提示

若某一字段含有多个索引，MySQL 在使用时会自动匹配一个索引，此时需要指定索引进行优化操作。

-- 索引提示的三种方法
explain select * from user use index(idx_profession_status) where profession='软件工程';-- 指定索引,只是建议，不一定生效
explain select * from user force index(idx_profession_status) where profession='软件工程';-- 强制索引，强制生效
explain select * from user ignore index(idx_profession) where profession='软件工程';-- 忽略索引

覆盖索引

通过设置联合索引，使用字段查询，避免 selsect *会造成的回表查询。

一张表， 有四个字段（id， username， password， status）， 由于数据量大， 需要对以下 SQL 语句进行优化， 该如何进行才是最优方案： select * from emp where username='张三'；

答： Create index idx_name_pass_sta on emp; select id，username，password，status from emp where username='张三'；

此处为 username， password， status 三个字段创建联合索引，在二级索引查询张三时，其叶子节点的键值分别为三个字段值和 id 值，直接得到所有数据，不需要再返回聚集索引回表查询，执行性能更高。

前缀索引

-- 计算选择性
select count(distinct substring(email,1,5))/count(*) from user;

取 email 的前 5 个字符再去重后的数量与总数量比较，选择性越大越好，重复率越小。

-- 创建前缀索引
create index idx_email on user(email(5));

SQL 优化

Insert

• 大规模数据插入使用 load 加载本地文件。

load data local infile 'D://A-Java//tb_sku1.sql' into table tb_sku fields terminated by ',' lines terminated by '\n';-- 数据表字段间分割符合为','。

• 主键保持顺序插入，避免页分裂。
• 手动开启事务。

start transaction; 
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); 
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); 
commit;

Order by

Using index 的性能高，Using filesort 的性能低

• 排序字段有索引，索引生效性能高。
• 满足最左前缀法则。

explain select id,age,phone from tb_user order by age,phone;

现在有 idx_phone_age 索引，order by 后面的字段排序顺序必须满足索引名的顺序才能使索引生效。

• 设置索引字段的排序方式

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

索引创建时默认为 asc，可在创建时指定索引的排序方式，可以提高指定排序方式语句的性能。

explain select id,age,phone from tb_user order by age asc , phone desc ;

Limit

对于较大数据的分页查询，数据越大性能越低，可以通过覆盖索引+子查询来提高性能。

select * from tb_sku limit 1000000,10;-- 1.3s
select * from tb_sku limit 1900000,10;-- 2.1s
select id from tb_sku order by id limit 1900000,10;-- 1.62s
select * from tb_sku s ,(select id from tb_sku order by id limit 1900000,10) a where s.id=a.id;-- 1.4s

Group by

在分组时，可以通过索引提高执行效率，但同时也必须满足最左前缀法则。

Count

按照效率排序的话，count（字段） < count（主键 id） < count（1） ≈ count（），所以尽量使用 count（）。

Update

利用 InnoDB 将索引由表锁转为行锁从而提高执行效率。

在更新时，添加了索引的字段会被添加行锁，其他字段会被添加表锁。

当开启事务时，对有索引字段操作时，只会锁住这一行，其他事务依旧可以更新表中的其他行的任意字段。但是若操作没有索引的字段，会被锁住整张表，其他事务暂时不能更新表中任意行的任意字段，必须等待最先事务提交后才能操作，性能低。

视图

视图语法

-- 创建视图
create view vw_user as select id,name,phone from user where id>15 with cascaded check option;

create view vw_user as select id,name,phone from user where id>15 with local check option;

-- 显示视图创建语句
show create view vw_user;
-- 修改视图
alter view vw_user as select id, name from user where id > 20;   
-- 删除视图
drop view vw_user;

v2 视图是基于 v1 视图的，如果在 v2 视图创建的时候指定了检查选项为 cascaded，但是 v1 视图创建时未指定检查选项。 则在执行检查时，不仅会检查 v2，还会级联检查 v2 的关联视图 v1。此处 id=25 的数据不可插入。

v2 视图是基于 v1 视图的，如果在 v2 视图创建的时候指定了检查选项为 local ，但是 v1 视图创建时未指定检查选项。 则在执行检查时，只会检查 v2，不会检查 v2 的关联视图 v1。此处 id=25 的数据可以插入。

cascaded 选项既会检查是否符合本表的条件，同时也会检查是否符合本表依赖的父表的条件，只有两张表的条件同时符合才会插入成功。 local 选项只会检查是否符合本表的条件，若符合本表条件就直接插入成功。

视图更新

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

• 聚合函数或窗口函数（SUM（）、 MIN（）、 MAX（）、 COUNT（）等）
• DISTINCT
• GROUP BY
• HAVING
• UNION 或者 UNION ALL

视图作用

• 简单 视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
• 安全 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
• 数据独立 视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

存储过程是对 SQL 语句的封装，减少数据在数据库和应用服务器之间的传输。

语法

create procedure p1()-- 创建存储过程
begin
-- SQL语句
end;

call p1();-- 调用存储过程

show create procedure p1;-- 显示存储过程创建语句

select *
from information_schema.ROUTINES
where ROUTINE_SCHEMA = 'db01';-- 显示某数据库中所有存储过程

drop procedure if exists p1;-- 删除存储过程

变量

• 系统变量

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量 
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '%...%'; -- 可以通过LIKE模糊匹配方式查找变量 
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ; 
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

• 自定义变量

set @a = 1;-- 1 声明全局变量
set @a = @a + 1;-- 2
select id into @a from user where name = '项羽';-- 9

create procedure p1()
begin
    declare b int;-- 声明局部变量
    set b = @a + 1;-- 10
    select b;
end;
call p1();

逻辑结构

• 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();

• case when

create procedure p4(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 result;
end;
call p4(5);

• while

create procedure p5(in n int)
begin
    declare sum int default 0;
    while n > 0
        do
            set sum = sum + n;
            set n = n - 1;
        end while;
    select sum;
end;
call p5(10);

• repeat

create procedure p6(in n int)
begin
    declare sum int default 0;
    repeat
        set sum = sum + n;
        set n = n - 1;
    until n = 0
        end repeat;
    select sum;
end;
call p6(10);

• loop

create procedure p5(in n int)
begin
    declare sum int default 0;
    label1:-- 此处为自定义标签名，标志这一个循环的开始和结束
    loop
        set sum = sum + n;
        set n = n - 1;
        if n = 0 then
            leave label1;
        end if;
    end loop;
    select sum;
end;
call p5(10);

• 游标：存储查询结果集

create procedure p6(in uage int)
begin
    declare uname varchar(10);
    declare uprofession varchar(10);-- 局部变量必须在声明游标前声明
    declare u_cursor cursor for select name, profession from user where age > uage;
    declare exit handler for SQLSTATE '02000' close u_cursor;-- 声明条件处理器,处理死循环

    create table user_result
    (
        id         int primary key auto_increment,
        name       varchar(10),
        profession varchar(10)
    );
    
    open u_cursor;-- 开启游标
    
    while true-- 死循环
        do
            fetch u_cursor into uname,uprofession;-- 获取数据
            insert into user_result(name, profession) values (uname, uprofession);
        end while;
        
    close u_cursor;-- 关闭游标
end;
call p6(40);

存储函数

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

create function f1(n int) returns int
    deterministic-- 必要参数
begin
    declare sum int default 0;
    while n > 0
        do
            set sum = sum + n;
            set n = n - 1;
        end while;
    return sum;-- 必须返回参数
end;
select f1(11);

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前（before）或之后（after），触发并执行触发器中定义的 SQL 语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录 ， 数据校验等操作。

-- 插入后
create trigger t1
    after insert
    on 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, ' profession=', new.profession,' status=', new.status, 'createtime=', new.createtime));
end;

-- 更新后
create trigger t2
    after update
    on 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, ' profession=',old.profession, ' status=', old.status, 'createtime=', old.createtime,'| 更新后的数据为：id=', new.id, ' name=', new.name, ' phone=', new.phone, ' profession=',new.profession, ' status=', new.status, 'createtime=', new.createtime));
end;

-- 删除后
create trigger t3
    after delete
    on 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, ' profession=', old.profession,' status=', old.status, 'createtime=', old.createtime));
end;

使用 new 来获取改动后的数据，用 old 来获取改动前的数据。

show triggers;-- 显示数据库中所有触发器
drop trigger t1;-- 删除触发器

锁

全局锁

整个数据库的表只读（用于数据备份，保证数据一致性）

flush tables with read lock;-- 锁定数据库
select * from user;-- 执行成功
update user set username='echo' where id=3;-- 执行失败
unlock tables;-- 解锁数据库

表级锁

表锁

• 读锁：在锁住期间，其他客户端只能读，不能写（指的是写的语句在等待）。解锁后，原本等待的写的语句才能生效。当前客户端的读语句正常执行，写语句无法执行，也不会等待。

-- 会话1:
lock tables user read;-- 添加读锁。

select  * from user;-- 执行成功
update user set username = 'jack' where id = 1;-- 执行失败也没有等待。

-- 会话2:
select  * from user;-- 执行成功
update user set username = 'jack' where id = 1;-- 执行等待

• 写锁，在锁住期间，其他客户端不能读，不能写（指的是写的语句在等待）。解锁后，原本等待的读语句才能生效。当前客户端的写语句和读语句正常执行

-- 会话1:
lock tables user write;-- 添加写锁

update user set username = 'mask' where id = 2;-- 执行成功
select  * from user;-- 执行成功

-- 会话2:
update user set username = 'jack' where id = 4;-- 执行等待
select  * from user;-- 执行失败

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁

meta data lock ， 元数据锁，简写 MDL。 MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免 DML 与 DDL 冲突，保证读写的正确性。

-- 查看数据库中的元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ;

意向锁

为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

• 意向共享锁（IS）: 由语句 select ... lock in share mode 添加 。与表锁共享锁 （read）兼容，与表锁排他锁（write）互斥。
• 意向排他锁（IX）: 由 insert、update、delete、select...for update 添加 。与表锁共享锁（read）及排他锁（write）都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

-- 查看意向锁及行锁
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

Next-Key Lock=Gap Lock + Record Lock

行锁分类

常见 SQL 执行时添加的锁的情况

间隙锁&临键锁

间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

innoDB 引擎