MySQL数据库（二）

---

layout: post
title: MySQL数据库（二）存储引擎、索引、SQL优化、视图、存储过程、触发器、锁与管理工具
description: MySQL数据库（二）存储引擎、索引、SQL优化、视图、存储过程、触发器、锁与管理工具
tag: 数据库

---

存储引擎

MySQL体系结构

MySQL存储引擎简介

存储引擎就是存储数据、建立索引、更新和查询数据等技术的实现方式，存储引擎是基于表的，而不是基于库的，所以存储引擎也被称为表类型
创建表的时候可以指定存储引擎，默认存储引擎为INNODB

InnoDB引擎特点

InnoDB引擎支持

• 事件
• 外键
• 行级锁

存储逻辑：

其他存储引擎MyISAM和Memory

MyISAM：MySQL早期默认引擎

Memory：
主要特点是存储在内存中，常用于临时表及缓存，访问速度快，支持hash索引

不同存储引擎的区别

存储引擎的选择

默认情况下InnoDB，它支持外键，事务和行级锁，用于存储业务系统中对于事务，数据完整性要求较高的核心数据。
MyISAM可以被MongoDB代替，存储业务系统中的非核心事务比如系统日志等。
Memory可以被Redis代替。

索引

索引(index)是帮助MysQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构。这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引结构

B树

引入B树的原因，二叉树与红黑树在处理大规模数据时检索链路太长！

B-Tree（多路平衡查找树）
一颗最大度数为5（5阶）的B树，每个节点最多存储4个key（5个数据范围，由4个 数据值构成，因此B树每个节点的数据数目比指针数（分支）少1），5个指针（5个分支）

B+树

B+树：B树的变种，所有的元素都会出现在叶子节点上，非叶子节点数据只起到索引的作用，且所有的叶子节点会构成一个单向链表。

而MySQL的B+树对经典B+树做了进一步优化，在原B+树基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+树，提供区间访问性能。

Hash索引

索引分类

在存储时，根据聚集索引构建B+树，每个数据头为聚集索引值，数据头下边挂着每一行的数据。同时，会根据其他字段建立二级索引（辅助索引），它的数据头为字段下的值，数据头下边挂着的是聚集索引值。

在查询时：
例如查询arm的信息，先根据二级索引找到arm的聚集索引id，再由id找所在行的数据。

索引语法

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

drop index inx_user_email on tb_user;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

SQL性能分析工具

表语句频次查询

通过语句查看数据库表的增删改查的频率。
show global status like 'Com_______'

慢查询日志

设置慢查询日志（慢查询日志中记录了所有执行时间超过指定参数的所有SQL语句的日志），MySQL的慢查询日志默认没有开启，需要设置下。

通过语句show variables like 'slow_query_log';可以查看慢查询日志的开启状态。

profile详情

使用profile详情可以让我们了解SQL时间都耗费到哪里去了。
通过select @@ have_profiling;查看是否支持profile操作
通过select @@profiling;查看profile是否开启
通过set profiling = 1;开启profiling

explain执行计划

在sql语句前加 explain和desc即可分析该条sql语句的执行情况。

explain返回表格各字段的解释：
其中重点关注type，通过type就可以语句的执行耗时是系统级system，还是常数级const等。

索引使用

最左前缀法则

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

下边这个例子中，联合索引有字段profession、age、status
如果查询条件不包含最左边的profession则不会使用索引。如果使用profession和status跳跃age，则age后的status索引失效。

范围查询

使用范围查询，右侧的索引失效
尽量将>的场景使用转为>=的使用，可以规避这种情况。

索引失效情况

1. 不要在索引所在的列上进行运算操作，否则索引将失效。
2. 字符串类型字段作为索引时，如果查询时字符串不加单引号，则索引失效。
3. 模糊查询时，如果仅仅时尾部模糊匹配，索引可以正常使用，但如果是头部模糊匹配，索引则会失效。因此应该规避头部模糊匹配的查询。
4. 用or分开的条件，如果or前的条件列有索引而后边的列没有索引，那么设计到的索引都不会被用到。

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

SQL提示

当有多个索引供使用时，MySQL默认选择的索引可能不是最适合的，可以假如SQL提示语句，告诉MySQL具体使用哪个索引以提高效率。
use index 是建议使用某个索引，MySQL可能会权衡是否使用
ignore index 是忽略索引
force index 则是强制使用某个索引。

覆盖索引与回表查询

覆盖索引的意思就是，查询的的信息都能在索引中找到，那么就可以走一次二级索引拿到所有想要查询的信息。
如果索引没有覆盖所要查询的信息，则需要回表查询。
走回表查询要比不用回表效率低。

答案是username和password建立联合索引，因为这就是覆盖索引，不需要走回表查询，效率更高

前缀索引

建立前缀索引的语法与建立普通索引类似，只是在column(n)，即根据该字段前多少个字符建立索引。
可以通过计算选择性来决定具体使用多少个字符来建立索引，选择性越高，用到的前缀字符数越少，性能越好。

单列索引和联合索引

根据联合索引的最左前缀法则，应该将唯一出现的字段尽量放到联合索引的左边。

索引设计原则

• 针对数据量大，查询频繁的表建立索引。
• 针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
• 尽量选择区分度高的列作为索引，区分度越高，索引效率越高。
• 如果是字符串类型的字段，字段长度较长，可以针对字段的特点，建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 要控制索引的数量。索引不是多多益善，索引越多，维护索引结构的代价越大，会影响增删改的效率。
• 如果索引列不能存储NULL值，请在创建表使用NOT NULL约束，当优化器知道每列是否包含NULL值时，它可以更好确定哪个索引最有效用于查询。

SQL优化

插入语句

批量插入避免多次与数据库引擎的连接；
手动提交事务是一个道理；
主键顺序插入优先。

使用load指令大批量插入数据：

主键优化

主键乱序插入会产生页分裂现象：

数据删除时可能会发生页合并现象：

主键设计原则：

• 满足业务需求情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键
• 尽量不要使用UUID做主键或是其他自然主键如身份证号码，因为它们长度过长且是乱序的。
• 业务操作时，避免对主键修改。

order by优化

索引构建时默认是升序排序的。

• 根据排序字段建立合适索引，多字段排序时遵循最左前缀法则。
• 尽量使用覆盖索引。
• 多字段排序时，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
• 如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认为256k）

group by优化

分组也可以建立联合索引，同样满足最左前缀法则。

limit优化

例如要查询九百万开始后的10条数据：
1、通过id顺序来查询到id
2、通过将查到的id范围作为子查询的方式定位到数据行

--通过id顺序来查询到id
select id from tb_sku order by id limit 9000000, 10;
-- 通过将查到的id范围作为子查询的方式定位到数据行
select t.* from tb_sku t, (select id from tb_sku order by id limit ) s where t.id = s.id;
1
2
3
4

count优化

可以自己计数
建议采用count *

update优化

InnoDB引擎是行级锁，一行数据在upadate时会被锁定，如果update时条件不是针对索引的，行锁会升级为表锁，降低并发性能。
因此update时一定要根据索引定位来update

视图/存储过程/触发器

视图

视图（View）是一种虚拟存在的表，视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
即视图只保存了查询的SQL逻辑，不保存查询结果，因此创建视图时，主要工作就落在创建这条SQL查询语句上

视图使用

视图相等于给SQL查询结果拍了快照保存下查询结果的状态。视图中的数据也是可以增删改查的，视图其实不包含真实数据，对视图中的增删改查会体现到真实的数据表中。

检查选项cascade与local

视图在创建时可以增加检查选项，以规避对于视图的增删改查违背了视图定义的SQL。
MySQL提供了两个视图检查选项cascaded和local，默认值为cascaded。
cascade就是级联的意思，由于我们可以根据视图来创建新的视图，所以添加了cascade选项的视图不仅会检查本视图的条件是否冲突，还会检查所有依赖的底层视图是否冲突（不管底层的视图有没有设置检查选项都会进行检查），local选项只会检查所依赖的带有检查选项的视图。

视图更新的条件

如果想要视图可以更新，视图中的行与原表中的行必须是一一对应的。

视图的作用

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

视图使用案例

--1、为了安全，tb_user使用时，只能看到用户的基本字段、屏蔽手机号和邮箱
create view tb_user_view as select id, name, profession, age, gender, status, createtime from tb_user;

select * from tb_user_view;

--2、查询每个学生所选修的课程，这个功能在很多业务中使用，为了简化操作，定义一个视图
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. studentid and sc.course_id = c.id;
1
2
3
4
5
6
7

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句集合，本质就是对SQL语言层面的代码封装与重用，对于提高数据处理的效率有很多好处。
存储过程的特点是：

• 封装复用
• 可以接收参数也可以返回参数
• 减少网络请求

存储过程的使用

注意：在命令行中，执行创建存储的SQL时，需要通过关键字delimiter指定SQL的结束符，否则程序会自动认为第一个分号出现的位置为语句结尾

存储过程的变量

• 系统变量分为会话级别的变量和全局级别的变量。
  操作时默认查看的是会话级的系统变量。

• 用户自定义级别的变量。
  MySQL中=既有赋值的意思也有比较的意思，因此，在做赋值时最好使用:=来代表变量的赋值。
  
• 局部变量
  根据需要在局部生效的变量，需要declare声明，可作为存储过程中的局部变量和输入参数。局部变量的作用范围是在其内声明的BEGIN……END块
  

存储过程的if语句

if语句语法类似MATLAB，需要end if 与 if 语句配对

存储过程的参数

参数过程的默认参数类型分为IN、OUT和INOUT三种，其中默认类型为IN.
需要注意的是inout类型，既可以作为输入又可以作为输出，将用于某些需要转换的使用场景。

以下边的案例：

--定义分数等级转换的存储过程
set @result := '结果'; 
create procedure devide_level(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 devide_level(68, @result);
select @result;	

--将传入的200分制的分数进行换算，转换为百分制，然后返回分数。
create procedure transform(inout score double)
begin
	set score := score * 0.5;
end;
set @score = 78;
call transform(@score);
select @score; 				
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

存储过程的case语句

语法一对于值进行分支选择，语法二对于值的范围进行分支选择。

存储过程的while

存储过程的repeat

repeat是有条件的循环控制语句，当满足条件的时候退出循环，类似 do while

存储过程的loop

loop是SQL中比较特殊用法，通过以下案例说明它的作用：
此案例计算从1到n的偶数累加和。

存储过程中的cursor

根据传入的参数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);
	close		
	end while;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

存储过程的条件处理handler

例如上边的循环中，当cursor读取结束时，会出发错误状态 ‘02000’，而我们可以通过设置条件处理程序，在遇到cursor读取不到时关闭cursor。
declare exit handler for SQLSTATE '02000' close u_cursor;

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。
实际上所有的存储函数都能用存储过程代替

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;	
1
2
3
4
5
6
7
8
9
10

触发器

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

注意：对于插入而言，只有new指代新插入的数据，对于update而言，old指代更新前的数据，new指代更新后，对于delete而言只有old，指代已经删除的数据。

触发器使用与案例

--1、定义插入数据触发器
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, ',profession', new.profession));
end;

--查看触发器
show triggers;

--删除
-- drop trigger tb_user_insert_trigger;


--插入数据到tb_user
insert into tb_user(id, name, phone, profession, age, gender, status, createtime) values (25, 'lzy', '13147193766', 'lzy@qq.com', 'computer', 12, 0, 0, now());	

--2、定义修改数据触发器
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, ',profession', old.profession), '更新之后的数据内容：id=', new.id, ',name=', new.name,',phone=', new.phone, ',profession', new.profession));
end;

update tb_user set age = 20 where id = 23;

--2、定义修改数据触发器
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, ',profession', old.profession), '更新之后的数据内容：id=', new.id, ',name=', new.name,',phone=', new.phone, ',profession', new.profession));
end;

--3、定义删除数据触发器
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, 'update',now(), old.id, concat('删除之前的数据内容：id=', old.id, ',name=', old.name,',phone=', old.phone, ',profession', old.profession));
end;

show triggers;

delete from tb_user while id = 25;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源（CPU、RAM、I/O）争用以外，数据也是一种供多个用户共享的资源。
MySQL中的锁分为以下三类：

• 全局锁：锁定数据库中的所有的表
• 表级锁：每次操作锁住整张表
• 行级锁：每次操作锁住对应的行数据

全局锁

全局锁对整个数据库实例加锁，加锁后整个实例处于只读状态，后序的DML语句，DDL语句，已经更新操作的事务提交语句都将被堵塞。数据库变为只读状态
典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

注意：flush table with read lock; 加锁和unlock tables解锁是SQL语句，但mysqldump -u root -p 1234 数据库名 > 文件名.sql是shell命令，应该exit退出SQL连接后再执行。

表级锁

表级锁每次操作锁住整张表，发送锁冲突概率最高，并发度最低。注意分为以下三类：

• 表锁
• 元数据锁
• 意向锁

表锁

表锁又分为以下两类：

• 表共享读锁
  锁住表score：
  客户端1执行
  lock table score read;
  表score会变为只读状态，此时客户端1如果要增删改表score会报错，客户端2如果要增删改表score会进入等待状态，等待客户端1解锁后，客户端2的修改语句才会提交。

  unlock tables;

• 表独占写锁
  客户端1执行：
  lock tables score write;
  客户端1会独占表score的读写，其他客户端的读写会被堵塞。

元数据锁

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

当对一张表进行增删改查的时候，加的是元数据锁中的读锁（共享），当对表结构进行变更操作时，加的是元数据锁中的写锁（排他），读锁与写锁之间是互斥的。

当客户端1开启事务，执行select语句，未提交时，其他客户端依旧可以正常读取，因为读锁是共享的，但其他客户端此时要改变表的结构alert table，则会出现堵塞。因为元数据锁的读锁和写锁是互斥的。

意向锁

下图的例子中，线程A指向update事务，会自动对所要更新的行数据加上行级锁，线程B想要对整张表上锁，上锁前需要逐行检查是否有行级锁，确认没有行级锁（没有锁冲突）后才能完成上锁，该过程的性能就很低下。

因此引入了意向锁
有了意向锁，线程A执行事务update时，除了给定行加上行级锁，还会加上意向锁，而线程B试图给整张表加锁时，可以直接检查意向锁的类型与自己要加的表锁类型是否冲突即可，不需要逐行确认。

意向锁又分为：

• 意向共享锁，与表锁的（read）锁兼容，与表锁想排他锁（write）互斥。
• 意向排他锁，与任意表锁类型都互斥。
  注：意向锁之间不会互斥。

当客户端1开启事务执行
select * from score where id = 1 lock in share mode;
尚未提交时，上述语句会给表加上行级锁和意向共享锁。此时客户端2可以正常给表加上读锁，因为意向共享锁与读锁是兼容的，但如果要加上写锁，则会出现堵塞。
等待客户端1提交事务后，才能正常加上写锁。

当客户端1开启事务执行
update score set math = 77 where id = 1;
update语句在加行级锁的同时会给表自动加上意向排他锁。此时客户端2无法给表加任意类型表锁，会堵塞。

行级锁

行级锁每次操作对应的行数据，发生冲突概率最低，并发度最高，应用在InnoDB存储引擎中。
InnoDB的索引是基于索引组织的，行锁是通过对索引上的索引加锁来实现的，而不是对记录加的锁。
行级锁主要分为以下三类：

• 行锁：锁定单个记录的锁。防止其他事务对此进行update和delete，在RC（read committed）和RR（read repeatable）的隔离级别下都是支持的。
• 间隙锁：锁定索引记录间隙（不包含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR(repeatable read )隔离级别下都支持。
• 临键锁：行锁和间隙锁的组合，同时锁住数据，并锁住数据前面的间隙Gap。是前面两种锁的组合，在RR（repeatable read）隔离级别下支持。

行锁

在执行增删改的时候，自动加排他锁。执行select时，默认不加任何锁，但可以通过语句手动加锁。

注意：InnoDB是根据索引进行加锁的，假如不通过索引检索数据，那么行级锁会升级为表锁。
例如：客户端1开启事务
执行：upgrade stu set name = 'jj' where name = 'lei';
这条语句根据name字段搜素，假如该字段没有建索引，在执行时行级锁就会升级为表锁。

间隙锁与临键锁

InnoDB引擎

逻辑存储结构

架构

内存架构

磁盘架构

后台线程

作用是将InnoDB存储引擎缓冲池中的数据刷新到磁盘中。

事务原理

redo log

redo log 即数据改变时先写改变的日志。确保数据的持久性，即便发送错误也可根据redo log恢复使用。

undo log

MVCC（多版本并发控制）

基本概念

MVCC-实现原理

1、利用记录中的隐藏字段获取最近事务ID和回滚指针。

2、根据事务ID和回滚指针定位到undo log 版本链

3、由版本链，readview（读取版本视图）

版本链访问规则：

RC与RR隔离级别下的readview

RC隔离级别下，在事务中每一次执行快照读时都生成readview。
在下表展示的版本链中，事务5查询id为30的记录，是一次快照读，生成的readview中，
m_ids（当前活跃的事务id集合）为3、4、5；min_trx_id（最小活跃事务id）为3，max_trx_id（最大预分配活跃事务id为最大活跃事务id再加上1，5+1）为6，creator_trx_id（readview创建事务id）为5。

RR隔离级别下，仅在事务中第一次执行快照读时生成readview，后续任然复用该readview，因此实现了可重复读

MySQL管理

系统数据库

常用工具

mysql

mysqladmin

mysqlbinlog

mysqlshow

mysqldump

mysqlimport/source