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;
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索引失效。
范围查询
使用范围查询,右侧的索引失效
尽量将>
的场景使用转为>=
的使用,可以规避这种情况。
索引失效情况
- 不要在索引所在的列上进行运算操作,否则索引将失效。
- 字符串类型字段作为索引时,如果查询时字符串不加单引号,则索引失效。
- 模糊查询时,如果仅仅时尾部模糊匹配,索引可以正常使用,但如果是头部模糊匹配,索引则会失效。因此
应该规避头部模糊匹配的查询
。 - 用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;
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;
存储过程
存储过程是事先经过编译并存储在数据库中的一段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;
存储过程的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;
存储过程的条件处理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;
触发器
触发器是与表有关的数据库对象,指的是在操作增删改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;
锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源(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