MySQL进阶篇

MySQL进阶篇

存储引擎

• 存储引擎简介

创建表时，指定存储引擎
create table 表名(
	...
)engine = innodb[];

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

• InnoDB

特点：1、DML（增删改）操作遵循ACID模型（原子性、一致性、隔离性、持久性），支持事务；2、行级锁；3、支持外键

逻辑存储结构：表空间->段->区（1M）->页（16K）->行

• MyISAM

特点：1、不支持事务，不支持外键；2、支持表锁，不支持行锁；3、访问速度快

• Memory

特点：1、速度快；2、hash索引

索引

• 介绍

索引时帮助MySQL高效获取数据的数据结构

优点：1、提高查询效率；2、提高排序效率

缺点：1、占据磁盘空间；2、影响增删改的效率

• B-Tree（多路平衡查找树）

[动态变化示意图参考网站]( B-Tree Visualization (usfca.edu) )

• B+Tree

相对于B-Tree的区别：

1、所有数据都会出现在叶子节点

2、叶子节点形成一个单向链表

• Hash

特点：

1、Hash索引只能用于对等比较，不支持范围查询

2、无法利用索引完成排序操作

3、查询效率高，通常只需要一次检索就可以了（前提是没有出现Hash碰撞），效率通常高于B+Tree索引

• 索引分类

聚集索引：将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据----->特点：必须有，而且只有一个

二级索引：将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键----->特点：可以存在多个

聚集索引的选取规则：

1、如果有主键，那么主键就是聚集索引

2、没有主键，那么就将第一个unique字段设置为聚集索引

3、如果主键和unique字段都没有，那么就自动生成一个rowid作为隐藏的聚集索引

回表查询：先走二级索引取到聚集索引的主键值，再到聚集索引拿到这一行数据。

• 索引语法

创建索引
create [unique|fulltext] index 索引名称 on 表名(字段名);

查看索引
show index from 表名;

删除索引
drop index index_name on 表名;

练习

1、name字段可能重复，为其创建索引并删除

创建
create index idx_emp_name on emp(name);

删除
drop index idx_emp_name on emp;

2、idcard字段为身份证，非空且唯一，为其创建索引

create [unique] index idx_emp_idcard on emp(idcard);

3、为gender、age、workaddress创建联合索引

create index idx_emp_gen_age_workadd on emp(gender, age, workaddress);

• SQL性能分析

SQL执行频率
show global status like 'Com_______';-- 一共7个下划线

慢查询日志
show variables like 'slow_query_log';

profile详情---查看语句执行时间
show profiles;

explain执行计划
explain select 字段列表 from 表名 where 条件;

explain各字段含义：

id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。以“查询选修了MySQL课程的学生（子查询）”

select_type：说明当前sql语句的查询类型

type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all

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

key：实际用到的索引，如果为NULL，则没有使用

key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的

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

extra：额外的信息

斜体字段需要重点留意

查询选修了MySQL课程的学生（子查询）
explain select * from student where id in(select studen_id from student_course where student_id = (select id from course where name = 'MySQL'));

创建表完成之后如果需要加主键用：

alter table 表名 add constraint [主键名] primary key (字段);

• 最左前缀法则

如果索引多列（联合索引），要遵循最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。（与顺序无关）。业务允许的情况下，尽量使用大于等于或者小于等于

• 索引失效情况

1、索引列运算

2、字符串不加引号

3、头部的模糊匹配，索引失效。尾部模糊匹配，索引不会失效

4、or语句。如果or语句左右两边的字段中有一个没有索引，那么整个索引失效，只有or两端字段都有索引才不会失效

5、数据分布影响：要是MySQL评估全表扫描比索引更快，那么索引失效走全表扫描

• SQL提示

在一个字段同时拥有联合索引和单列索引时，在执行查询语句的时候MySQL会自动选择联合索引，那么如果想要使用单列索引，这里就需要用到SQL提示，它是优化数据库的重要手段，是在SQL语句中加入一些认为的提示来达到优化操作的目的。

建议使用指定索引（MySQL不一定会用）
explain select * from emp use index(idx_emp_workadd) where workaddress = '内蒙';
或强制使用
explain select * from emp force index(idx_emp_workadd) where workaddress = '内蒙';

忽略指定索引
explain select * from emp ignore index(idx_emp_workadd) where workaddress = '内蒙';

• 覆盖索引&&回表查询

减少select * 的使用目的是减少回表查询的概率，回表查询会降低性能，除非将该表的所有字段全都放在联合索引中，不然要是查询的字段不在联合索引中则会进行回表查询。

• 索引设计原则

SQL优化

• insert优化

1、批量插入。2、手动提交事务。3、主键顺序插入。3、大批量数据插入，使用load指令

• 主键优化

数据组织方式

在Innodb存储引擎中，表数据都是根据主键顺序组织存放的。页是Innodb引擎存储的最小单元，最大存储16K的数据

页分裂

页可以分裂，也可以填充一半，也可以填满。每个页包含2-N行数据，页与页之间用双向指针维护。

主键乱序插入容易导致页分裂

页合并

当页中删除记录达到某一定的域值时，Innodb会开始寻找最靠近的页的前后，看看是否可以将两个页合并以优化空间。

主键设计原则

1、满足业务条件下，尽量降低主键长度

2、插入数据时尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键

3、尽量不要使用自然主键，如身份证号

4、业务操作时，避免对主键的修改

• order by排序查询优化

1、根据排序字段建立合适的索引，多字段排序时，页遵循最左前缀法则

2、尽量使用覆盖索引

3、多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）

4、如果不可避免出现filesort，大量数据排序时，可以适当增大缓冲区大小sort_buffer_size(默认256K)

5、using index效率比using filesort效率高

• group by分组查询优化

1、在分组操作时，可以通过索引来提高效率

2、分组操作时，索引的使用也是满足最左前缀法则的

• limit优化

limit语句越往后代价越大

优化思路：一般分页查询时，通过创建覆盖索引能够更好地提高性能，可以通过覆盖索引加子查询形式进行优化

从第900001个数据开始查询，查询个数为10，总耗时19.39秒
select * from emp limit 900000,10;

拿到id
select id from emp order by id limit 900000,10;

多表联查,总耗时11秒，节省了8秒多
select * from emp e, (select id from emp order by id limit 900000,10) a where e.id = a.id;

• count优化

求取数据总记录数的时候建议用count(*)，效率最好，尽量不要用count(字段)，因为count字段的话会判断该字段是否为空，为空则不计入总数。

• update优化

在Innodb引擎中的行锁时针对索引加的锁，不是针对记录加的锁，在进行update语句的时候要是不是用的索引进行限定的话行锁会升级成表锁，并发性能会降低

视图/存储过程/触发器

视图

创建
create [or replace] view 视图名称 as select语句

查询
show create view 视图名称;
查看视图数据：select * from 视图名称;

修改
方式一：create [or replace] view 视图名称 as select语句
方式二：alter view 视图名称 as select语句

删除
drop view [if exists] 视图名称

视图可以理解为表的备份或者是虚拟的表，在大数据的情况下如果对表的增删改查没有把握的时候就使用视图。视图是可以对原表进行增删改操作的，但不是任何视图都能增删改

• 检查选项

cascaded级联

是MySQL默认的选项。当视图创建的时候在后面加了with cascaded check option，那么在操作这个视图时会检查当前视图和检查当前视图所依赖的所有视图是否满足条件

local

不同于cascaded，local对上级视图会进行判断是否有with check option，没有则不检查，有则检查。

• 视图的更新

视图中的行于基础表之间必须存在一对一的关系，才能对其进行更新

• 视图的作用

1、使用简单

2、操作安全

3、数据独立

视图应用案例

查询每个学生所选修的课程（三张表联查），这个功能在很多业务中有使用到，为了简化操作，定义一个视图

create view tb_stu_course_view as select s.name student_name, s.no student_no, c.name course_name from student s, student_course sc, course c where s.id = sc.student_id and c.id = sc.course_id;

这样以后在查询s.name,s.no,c.name这三个字段的时候就不再需要很长的select语句。只需要select * from tb_stu_course_view;即可

存储过程

• 概念

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化开发人员的工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。简单来说它就是数据库SQL语言层面的代码封装和重用

• 基本语法

创建
create procedure 存储过程名称([参数列表])
begin
	-- 若干条SQL语句
end;

调用
call 名称([参数列表])

查看
show create procedure 名称;

删除
drop procedure [if exists] 名称;

-- 注意在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符，以下是例子
delimiter $$

create procedure p1()
begin
	select count(*) from student;
end&&

delimiter ;-- 要切换回来 不然应该以call p1()$$结束

call p1();

• 系统变量

查看系统变量 （默认session）
show [session|global] variables;-- 查看所有系统变量
show [session|global] variables like '...';-- 模糊匹配查找变量
select @@[session|global] 系统变量名 -- 查看指定变量的值

设置系统变量
set [session|global] 系统变量名=值;
set @@[session|global] 系统变量名=值;

• 用户定义变量（一个@为用户自定义变量，两个@为系统变量）

赋值
set @var_name := expr;-- 也可以使用=，但推荐使用:=  
或
select @var_name := expr;
或
select 字段名 into @var_name from 表名-- 将某个输出结果作为赋值

使用
select @var_name;

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

• 局部变量

局部变量的范围是在begin…end之间

声明
declare 变量名 变量类型 [default...];

赋值
set 变量名=值;
set 变量名=值;
select 字段名 into 变量名 from 表名...;

• if判断

if 条件 then
	...
else 条件2 then
	...
else
	...
end 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();
drop procedure p3;

• 参数

in：作为传入进来的参数

out：作为返回出去的结果

inout：既是输入又是输出类型

1、同样以分数等级进行判定为例

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;

call(18, @result);
select @result;

2、将传入的200分制的分数进行换算，换算成百分制

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

set @score := 88;
call p5(@score);
select @score;

• case

case
	when ... then ...;
	when ... then ...;
end case;

案例

对输入月份进行判断

create procedure p6(in month int)
begin
	declare result varchar(10);
	case
		when month >= 1 && month <= 3 then
			set result := '第一季度';
		when month >= 4 && month <= 6 then
			set result := '第二季度';
		when month >= 7 && month <= 9 then
			set result := '第三季度';
		when month >= 10 && month <= 12 then
			set result := '第四季度';
	end case;
	select concat('您输入的月份为：', )
end;

• while循环

while 条件 do
	SQL逻辑
end while;

案例

计算1累加到n的值

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

• repeat循环

特点：先参与循环，再判断条件

repeat
	SQL逻辑
	until 条件
end 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);

• loop循环

特点：没有退出语句，需要配合使用leave语句和iterate语句

[begin_label:]loop
	SQL逻辑
end loop[end_lable];

leave label;-- 退出指定标记的循环体
iterate label;-- 直接进入下一次循环

-- 案例同上
create procedure p9(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 p9(10);


-- 案例2：计算1到n偶数和
create procedure p10(in n int)
begin
	declare total int default 0;
	sum:loop
		if n <= 0 then
			leave sum;
		elseif n%2!=0 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);

• 游标cursor、条件处理程序handler

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果进行循环的处理。

声明游标
declare 游标名称 cursor for 查询语句;

打开游标
open 游标名称;

获取游标记录
fetch 游标名称 into 变量[,变量];

关闭游标
close 游标名称;

案例

逻辑
1、声明游标，存储查询结果集
2、准备：创建表结构
3、开启游标
4、获取游标中的记录
5、插入数据到新表中
6、关闭游标

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

    drop table if exists tb_emp_workaddress;
    create table if not exists tb_emp_workaddress(
        id int primary key auto_increment,
        name varchar(100),
        workaddress varchar(100)
    );

    open u_cursor;
    while true do
        fetch u_cursor into uname, uworkaddress;
        insert into tb_emp_workaddress values(null, uname, uworkaddress);
        end while;
    close u_cursor;
end;

call p11(30);
drop procedure p11;


-- handler语法
declare handler_action handler for condition_value [,condition_value]... statement;

handler_action
	continue：继续执行当前程序
	exit：终止执行当前程序
condition_value
	SQLstate sqlstate_value：状态码，如02000
	SQLWARNING：所有以01开头的SQLSTATE代码的简写
	NOT FOUND：所有以02开头的SQLSTATE的简写
	SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。使用别名OLD和NEW来引用触发器中发生变化的记录内容，现在的触发器只支持行级触法。

• 语法

创建
create trigger 名称
before/after insert/update/delete
on 表名 for each row
begin
	trigger_stmt;执行语句
end;

查看
show triggers;

删除
drop trigger [schema_name.]触发器名;如果没有指定schema_name，默认当前数据库

• insert类型

create table user_logs(
    id int(11) not null auto_increment primary key ,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operation_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数'
) engine = innodb default char set = utf8;

create trigger emp_insert_trigger
    after insert on emp for each row
    begin
        insert into user_logs(id, operation, operation_time, operate_id, operate_params)
        values(null, 'insert', now(), new.id, concat('插入的数据内容为：id=', new.id, 'name=', new.name, 'gender=', new.gender, 'idcard=', new.idcard));
    end;

show triggers;

insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate, dept_id)
values(17, 00017, 'wmd', 'W', 77, '123456789012345617', '贵州', now(), 1);

• update类型

create trigger emp_update_trigger
    after update on emp for each row
    begin
        insert into user_logs(id, operation, operation_time, operate_id, operate_params)
        values(null, 'update', now(), new.id, concat('更新之前的数据内容为：id=', old.id, 'name=', old.name, 'gender=', old.gender, 'idcard=', old.idcard,
              ' | 更新之后的数据内容为：id=', new.id, 'name=', new.name, 'gender=', new.gender, 'idcard=', new.idcard));
    end;
    
update emp set name = 'lzl1' where name = 'lzl';
update emp set workaddress = '长沙' where id <= 5;进行5次触发器

锁

• 全局锁

不推荐使用，全局锁限制太广，加了全局锁其他用户就只能对数据库进行读操作。

• 表级锁

  • 表共享读锁

    上锁之后只能读，不能在当前客户端上写，在其他客户端上执行写会处于阻塞状态，知道读锁解除，阻塞状态结束

  • 表独占写锁

    上锁之后，当前客户端可以对表进行读和写操作，但在其他客户端对表的读和写操作都会处于阻塞状态

• 行锁

  锁定粒度最小，发生锁冲突的概率最低，并发度最高

事务原理

• 特性

原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性：事务完成时，必须使所有的数据都保持一致状态。

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发影响下的独立环境下运行。

持久性：事务一旦提交或者回滚，对数据库中的数据的改变是永久的。

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件主要包括两个部分：重做日志缓冲和重做日志文件，前者在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘发生错误时，进行数据恢复使用。

脏页：当执行事务中的某个语句时发现缓冲池中没有该数据，那么需要线程将数据读入缓冲区，这一块数据就称为脏页。

为什么采用该方法？—>由于事务操作时涉及多条语句，操作的数据页都是随机的，这时会出现大量的随机磁盘IO，非常影响性能。如果采用redolog方法，因为日志文件是追加在磁盘中的，是顺序存储，所以相较于随机磁盘IO会快很多。注意每隔一段时间会清理日志文件的，该过程的前提是在脏页写入过程中出错才使用的方法。

undo log

回滚日志，用于记录数据被修改之前的信息，作用包含两个：提供回滚和MVCC(多版本并发控制)。用来实现事务的原子性。采用段的方式进行管理和记录，内部包含1024个undo log段。

MVCC

• 当前读

读取的记录是最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。select…lock in share mode是一种当前读。

• 快照读

简单的select语句就是快照读。Innodb默认的隔离权限Repeatable Read：开启事务后第一个select语句才是快照读的地方。

• MVCC

多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。MVCC的具体实现需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

实现原理

• 隐藏字段

DB_TRX_DI：记录最后一次修改该记录的事务ID

DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本

DB_ROW_ID：隐藏主键，如果建表时没有指定主键，将会生成该字段

• undo log版本链

• readview

判断过程十分复杂，总结下来只有两句话

read committed：快照读的是最近一次提交的事务的数据

repeated read：在同一个事务中对同一条语句只会生成一个readview，并且都复用第一个readview

锁加上MVCC实现了事务四大特性的隔离性