乐清门户网站建设,郑州企业推广,中国企业网中国商报,网页制作与网站建设完全学习手册光盘1.存储引擎
1.1MySQL体系结构 1).连接层
最上层是一些客户端和链接服务#xff0c;包含本地sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念#xff0c;为通过认证…1.存储引擎
1.1MySQL体系结构 1).连接层
最上层是一些客户端和链接服务包含本地sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2).服务层
第二层架构主要完成大多数的核心服务功能如SQL接口并完成缓存的查询SQL的分析和优化部分内置函数的执行。所有跨存储引擎的功能也在这一层实现如过程、函数等。在该层服务器会解析查询并创建相应的内部解析树并对其完成相应的优化如确定表的查询的顺序是否利用索引等最后生成相应的执行操作。如果是select语句服务器还会查询内部的缓存如果缓存空间足够大这样在解决大量读操作的环境中能够很好的提升系统的性能。
3).引擎层
存储引擎层存储引擎真正的负责了MySQL中数据的存储和提取服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能这样我们可以根据自己的需要来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。
4).存储层
数据存储层主要是将数据(如:redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上并完成与存储引擎的交互。
和其他数据库相比MySQL有点与众不同它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
1.2存储引擎介绍 大家可能没有听说过存储引擎但是一定听过引擎这个词引擎就是发动机是一个机器的核心组件。比如对于舰载机、直升机、火箭来说他们都有各自的引擎是他们最为核心的组件。而我们在选择引擎的时候需要在合适的场景选择合适的存储引擎就像在直升机上我们不能选择舰载机的引擎一样。
而对于存储引擎也是一样他是mysql数据库的核心我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎。
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的而不是基于库的所以存储引擎也可被称为表类型。我们可以在创建表的时候来指定选择的存储引擎如果没有指定将自动选择默认的存储引擎。
1).建表时指定存储引擎
CREATE TABLE 表名 (字段1 字段1类型 [COMMENT 字段1注释],......字段n 字段n类型 [COMMENT 字段n注释]
) ENGINE INNODB [COMMENT表注释];
2).查询当前数据库支持的存储引擎
show engines;
示例演示:
A.查询建表语句---默认存储引擎:InnoDB
show create table account; 可以看到创建表时即使没有指定存储引擎数据库也会自动选择默认的存储引擎。
B.查询当前数据库支持的存储引擎
show engines; C.创建表my_myisam ,并指定MyISAM存储引擎
create table my_myisam (id int,name varchar(10)
) engine MyISAM; D.创建表my_memory ,指定Memory存储引擎
create table my_memory (id int,name varchar(10)
) engine Memory;
1.3存储引擎特点
上面介绍了什么是存储引擎以及如何在建表时如何指定存储引擎接下来就来介绍下来上面重点提到的三种存储引擎InnoDB、MyISAM、Memory的特点。
1.3.1InnoDB
1).介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎在MySQL 5.5之后InnoDB是默认的MySQL存储引擎。
2).特点
DML操作遵循ACID模型支持事务行级锁提高并发访问性能支持外键FOREIGN KEY约束保证数据的完整性和正确性
3).文件
xxx.ibdxxx代表的是表名innoDB引擎的每张表都会对应这样一个表空间文件存储该表的表结构frm-早期的、sdi-新版的、数据和索引。
参数innodb_file_per_table
show variables like innodb_file_per_table; 如果该参数开启代表对于InnoDB引擎的表每一张表都对应一个ibd文件。直接打开MySQL的数据存放目录C:\ProgramData\MySQL\MySQL Server 8.0\Data这个目录下有很多文件夹不同的文件夹代表不同的数据库直接打开itcast文件夹。 可以看到里面有很多的ibd文件每一个ibd文件就对应一张表比如有一张表account就有这样的一个account.ibd文件而在这个ibd文件中不仅存放表结构、数据还会存放该表对应的索引信息。而该文件是基于二进制存储的不能直接基于记事本打开可以使用mysql提供的一个指令ibd2sdi通过该指令就可以从ibd文件中提取sdi信息而sdi数据字典信息中就包含该表的表结构。 4).逻辑存储结构 表空间: InnoDB存储引擎逻辑结构的最高层ibd文件其实就是表空间文件在表空间中可以包含多个Segment段。 段:表空间是由各个段组成的常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理都是引擎自身完成不需要人为对其控制一个段中包含多个区。区:区是表空间的单元结构每个区的大小为1M。默认情况下InnoDB存储引擎页大小为16K即一个区中一共有64个连续的页。页:页是组成区的最小单元页也是InnoDB存储引擎磁盘管理的最小单元每个页的大小默认为16KB。为了保证页的连续性InnoDB存储引擎每次从磁盘申请4-5个区。行: InnoDB存储引擎是面向行的也就是说数据是按行进行存放的在每一行中除了定义表时所指定的字段以外还包含两个隐藏字段(后面会详细介绍)。
1.3.2MyISAM
1).介绍
MyISAM是MySQL早期的默认存储引擎。
2).特点
不支持事务不支持外键支持表锁不支持行锁访问速度快
3).文件
xxx.sdi存储表结构信息
xxx.MYD:存储数据
xxx.MYI:存储索引 1.3.3Memory
1).介绍
Memory引擎的表数据时存储在内存中的由于受到硬件问题、或断电问题的影响只能将这些表作为临时表或缓存使用。
2).特点
内存存放hash索引默认
3).文件 1.3.4区别及特点 面试题: InnoDB引擎与MyISAM引擎的区别? ①.InnoDB引擎,支持事务,而MyISAM不支持。 ②.InnoDB引擎,支持行锁和表锁,而MyISAM仅支持表锁,不支持行锁。 ③.InnoDB引擎,支持外键,而MyISAM是不支持的。 主要是上述三点区别当然也可以从索引结构、存储限制等方面更加深入的回答具体参考如下官方文档 MySQL :: MySQL 8.0 Reference Manual :: 17.1 Introduction to InnoDB MySQL :: MySQL 8.0 Reference Manual :: 18.2 The MyISAM Storage Engine 1.4存储引擎选择
在选择存储引擎时应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统还可以根据实际情况选择多种存储引擎进行组合。
InnoDB:是Mysql的默认存储引擎支持事务、外键。如果应用对事务的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询之外还包含很多的更新、删除操作那么InnoDB存储引擎是比较合适的选择。MyISAM如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完整性、并发性要求不是很高那么选择这个存储引擎是非常合适的。MEMORY将所有数据保存在内存中访问速度快通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制太大的表无法缓存在内存中而且无法保障数据的安全性。
1.5MySQL安装Linux版本
1.准备一台Linux服务器
云服务器或者虚拟机都可以
Linux的版本为 CentOS7
2.下载Linux版MySQL安装包
https://downloads.mysql.com/archives/community/ 3.上传MySQL安装包 4.创建目录并解压 5.安装MySQL的安装包 6.启动MySQL服务 7.查询自动生成的root用户密码 8.修改root用户密码 9.创建用户 10.并给root用户分配权限 11.重新连接MySQL 12.通过DataGrip远程连接MySQL 2.索引
2.1索引概述
2.1.1介绍
索引index是帮助MySQL高效获取数据的数据结构(有序)。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。 一提到数据结构大家都会有所担心担心自己不能理解跟不上节奏。不过在这里大家完全不用担心后面在讲解时会详细介绍。
2.1.2演示
表结构及其数据如下 假如要执行的SQL语句为select * from user where age 45;
1).无索引情况 在无索引情况下就需要从第一行开始扫描一直扫描到最后一行称之为全表扫描性能很低。
2).有索引情况
如果针对于这张表建立了索引假设索引结构就是二叉树那么也就意味着会对age这个字段建立一个二叉树的索引结构。 此时在进行查询时只需要扫描三次就可以找到数据了极大的提高的查询的效率。 备注 这里只是假设索引的结构是二叉树介绍一下索引的大概原理只是一个示意图并不是索引的真实结构索引的真实结构后面会详细介绍。 2.1.3特点 2.2索引结构
2.2.1概述
MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的索引结构主要包含以下几种 上述是MySQL中所支持的所有的索引结构接下来再来看看不同的存储引擎对于索引结构的支持情况。 注意 平常所说的索引如果没有特别指明都是指B树结构组织的索引。 2.2.2二叉树
假如说MySQL的索引结构采用二叉树的数据结构比较理想的结构如下 如果主键是顺序插入的则会形成一个单向链表结构如下 所以如果选择二叉树作为索引结构会存在以下缺点
顺序插入时会形成一个链表查询性能大大降低。大数据量情况下层级较深检索速度慢。
此时可能会想到可以选择红黑树红黑树是一颗自平衡二叉树那这样即使是顺序插入数据最终形成的数据结构也是一颗平衡的二叉树,结构如下: 但是即使如此由于红黑树也是一颗二叉树所以也会存在一个缺点
大数据量情况下层级较深检索速度慢。
所以在MySQL的索引结构中并没有选择二叉树或者红黑树而选择的是BTree那么什么是BTree呢在详解BTree之前先来介绍一个B-Tree。
2.2.3B-Tree
B-TreeB树是一种多叉路衡查找树相对于二叉树B树每个节点可以有多个分支即多叉。以一颗最大度数max-degree为5(5阶)的b-tree为例那这个B树每个节点最多存储4个key5个指针 知识小贴士: 树的度数指的是一个节点的子节点个数。 可以通过一个数据结构可视化的网站来简单演示一下。
B-Tree Visualization 插入一组数据100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250。然后观察一些数据插入过程中节点的变化情况。 特点
5阶的B树每一个节点最多存储4个key对应5个指针。一旦节点存储的key数量到达5就会裂变中间元素向上分裂。在B树中非叶子节点和叶子节点都会存放数据。
2.2.4BTree
BTree是B-Tree的变种以一颗最大度数max-degree为44阶的btree为例来看一下其结构示意图 可以看到两部分
绿色框框起来的部分是索引部分仅仅起到索引数据的作用不存储数据。红色框框起来的部分是数据存储部分在其叶子节点中要存储具体的数据。 可以通过一个数据结构可视化的网站来简单演示一下。
B Tree Visualization 插入一组数据100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250。然后观察一些数据插入过程中节点的变化情况。 最终看到BTree与B-Tree相比主要有以下三点区别
所有的数据都会出现在叶子节点。叶子节点形成一个单向链表。非叶子节点仅仅起到索引数据作用具体的数据都是在叶子节点存放的。
上述所看到的结构是标准的BTree的数据结构接下来再来看看MySQL中优化之后的BTree。
MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的BTree提高区间访问的性能利于排序。 2.2.5Hash
MySQL中除了支持BTree索引还支持一种索引类型---Hash索引。
1).结构
哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。 如果两个(或多个)键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决。 2).特点
A. Hash索引只能用于对等比较(in)不支持范围查询between...
B.无法利用索引完成排序操作
C.查询效率高通常(不存在hash冲突的情况)只需要一次检索就可以了效率通常要高于Btree索引
3).存储引擎支持
在MySQL中支持hash索引的是Memory存储引擎。而InnoDB中具有自适应hash功能hash索引是InnoDB存储引擎根据BTree索引在指定条件下自动构建的。 思考题为什么InnoDB存储引擎选择使用Btree索引结构? A.相对于二叉树层级更少搜索效率高 B.对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低 C.相对Hash索引Btree支持范围匹配及排序操作 2.3索引分类
2.3.1索引分类
在MySQL数据库将索引的具体类型主要分为以下几类主键索引、唯一索引、常规索引、全文索引。 2.3.2聚集索引二级索引
而在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种 聚集索引选取规则:
如果存在主键主键索引就是聚集索引。 如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引和二级索引的具体结构如下 聚集索引的叶子节点下挂的是这一行的数据。 二级索引的叶子节点下挂的是该字段值对应的主键值。
接下来来分析一下当执行如下的SQL语句时具体的查找过程是什么样子的。 具体过程如下:
①.由于是根据name字段进行查询所以先根据nameArm到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到Arm对应的主键值10。
②.由于查询返回的数据是*所以此时还需要根据主键值10到聚集索引中查找10对应的记录最终找到10对应的行row。
③.最终拿到这一行的数据直接返回即可。 回表查询 这种先到二级索引中查找数据找到主键值然后再到聚集索引中根据主键值获取数据的方式就称之为回表查询。 思考题 以下两条SQL语句那个执行效率高?为什么? A. select * from user where id 10 ; B. select * from user where name Arm ; 备注:id为主键name字段创建的有索引 解答 A语句的执行性能要高于B语句。 因为A语句直接走聚集索引直接返回数据。而B语句需要先查询name字段的二级索引然后再查询聚集索引也就是需要进行回表查询。 思考题 InnoDB主键索引的Btree高度为多高呢? 假设: 一行数据大小为1k一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间主键即使为bigint占用字节数为8。 高度为2 n * 8 (n 1) * 6 16*1024 ,算出n约为1170 1171* 16 18736 也就是说如果树的高度为2则可以存储18000多条记录。 高度为3 1171 * 1171 * 16 21939856 也就是说如果树的高度为3则可以存储2200w左右的记录。 2.4索引语法
1).创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);
2).查看索引
SHOW INDEX FROM table_name;
3).删除索引
DROP INDEX index_name ON table_name;
案例演示:
先来创建一张表tb_user并且查询测试数据。
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 tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (吕布, 17799990000, lvbu666163.com, 软件工程, 23, 1, 6, 2001-02-02 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (曹操, 17799990001, caocao666qq.com, 通讯工程, 33, 1, 0, 2001-03-05 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (赵云, 17799990002, 17799990139.com, 英语, 34, 1, 2, 2002-03-02 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (孙悟空, 17799990003, 17799990sina.com, 工程造价, 54, 1, 0, 2001-07-02 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (花木兰, 17799990004, 19980729sina.com, 软件工程, 23, 2, 1, 2001-04-22 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (大乔, 17799990005, daqiao666sina.com, 舞蹈, 22, 2, 0, 2001-02-07 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (露娜, 17799990006, luna_lovesina.com, 应用数学, 24, 2, 0, 2001-02-08 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (程咬金, 17799990007, chengyaojin163.com, 化工, 38, 1, 5, 2001-05-23 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (项羽, 17799990008, xiaoyu666qq.com, 金属材料, 43, 1, 0, 2001-09-18 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (白起, 17799990009, baiqi666sina.com, 机械工程及其自动化, 27, 1, 2, 2001-08-16 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (韩信, 17799990010, hanxin520163.com, 无机非金属材料工程, 27, 1, 0, 2001-06-12 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (荆轲, 17799990011, jingke123163.com, 会计, 29, 1, 0, 2001-05-11 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (兰陵王, 17799990012, lanlinwang666126.com, 工程造价, 44, 1, 1, 2001-04-09 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (狂铁, 17799990013, kuangtiesina.com, 应用数学, 43, 1, 2, 2001-04-10 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (貂蝉, 17799990014, 84958948374qq.com, 软件工程, 40, 2, 3, 2001-02-12 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (妲己, 17799990015, 2783238293qq.com, 软件工程, 31, 2, 0, 2001-01-30 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (芈月, 17799990016, xiaomin2001sina.com, 工业经济, 35, 2, 0, 2000-05-03 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (嬴政, 17799990017, 8839434342qq.com, 化工, 38, 1, 1, 2001-08-08 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (狄仁杰, 17799990018, jujiamlm8166163.com, 国际贸易, 30, 1, 0, 2007-03-12 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (安琪拉, 17799990019, jdodm1h126.com, 城市规划, 51, 2, 0, 2001-08-15 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (典韦, 17799990020, ycaunanjian163.com, 城市规划, 52, 1, 2, 2000-04-12 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (廉颇, 17799990021, lianpo321126.com, 土木工程, 19, 1, 3, 2002-07-18 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (后羿, 17799990022, altycj2000139.com, 城市园林, 20, 1, 0, 2002-03-10 00:00:00);INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES (姜子牙, 17799990023, 37483844qq.com, 工程造价, 29, 1, 4, 2003-05-26 00:00:00);表结构中插入的数据如下 数据准备好了之后接下来就来完成如下需求
A. name字段为姓名字段该字段的值可能会重复为该字段创建索引。
CREATE INDEX idx_user_name ON tb_user(name);
B. phone手机号字段的值是非空且唯一的为该字段创建唯一索引。
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);
C.为profession、age、status创建联合索引。
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, status);
D.为email建立合适的索引来提升查询效率。
CREATE INDEX idx_email ON tb_user(email);
完成上述的需求之后再查看tb_user表的所有的索引数据。
show index from tb_user; 2.5SQL性能分析
2.5.1SQL执行频率
MySQL客户端连接成功后通过show [session|global] status命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次
--session是查看当前会话;
--global是查询全局数据;
SHOW GLOBAL STATUS LIKE Com_______; Com_delete:删除次数
Com_insert:插入次数
Com_select:查询次数
Com_update:更新次数
可以在当前数据库再执行几次查询操作然后再次查看执行频次看看Com_select参数会不会变化。 通过上述指令可以查看到当前数据库到底是以查询为主还是以增删改为主从而为数据库优化提供参考依据。如果是以增删改为主可以考虑不对其进行索引的优化。如果是以查询为主那么就要考虑对数据库的索引进行优化了。 那么通过查询SQL的执行频次就能够知道当前数据库到底是增删改为主还是查询为主。那假如说是以查询为主又该如何定位针对于那些查询语句进行优化呢可以借助于慢查询日志。
接下来就来介绍一下MySQL中的慢查询日志。
2.5.2慢查询日志
慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启可以查看一下系统变量slow_query_log。 如果要开启慢查询日志需要在MySQL的配置文件/etc/my.cnf中配置如下信息
#开启MySQL慢日志查询开关
slow_query_log1#设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志
long_query_time2 配置完毕之后通过以下指令重新启动MySQL服务器进行测试查看慢日志文件中记录的信息
/var/lib/mysql/localhost-slow.log。
systemctl restart mysqld
然后再次查看开关情况慢查询日志就已经打开了。 测试
A.执行如下SQL语句
--这条SQL执行效率比较高,执行耗时0.00sec
select * from tb_user;--由于tb_sku表中,预先存入了1000w的记录,count一次,耗时13.35sec
select count(*) from tb_sku;B.检查慢查询日志
最终发现在慢查询日志中只会记录执行时间超多预设时间2s的SQL执行较快的SQL是不会记录的。 那这样通过慢查询日志就可以定位出执行效率比较低的SQL从而有针对性的进行优化。
2.5.3profile详情
show profiles能够在做SQL优化时帮助了解时间都耗费到哪里去了。通过have_profiling参数能够看到当前MySQL是否支持profile操作
SELECT have_profiling; 可以看到当前MySQL是支持profile操作的但是开关是关闭的。可以通过set语句在session/global级别开启profiling
SET profiling 1;
开关已经打开了接下来所执行的SQL语句都会被MySQL记录并记录执行时间消耗到哪儿去了。直接执行如下的SQL语句
select * from tb_user;select * from tb_user where id 1;select * from tb_user where name 白起;select count(*) from tb_sku;
执行一系列的业务SQL的操作然后通过如下指令查看指令的执行耗时
--查看每一条SQL的耗时基本情况
show profiles;--查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;--查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
查看每一条SQL的耗时情况: 查看指定SQL各个阶段的耗时情况: 2.5.4explain
EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息包括在SELECT语句执行过程中表如何连接和连接的顺序。
语法:
--直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件; Explain执行计划中各个字段的含义: 2.6索引使用
2.6.1验证索引效率
在讲解索引的使用原则之前先通过一个简单的案例来验证一下索引看看是否能够通过索引来提升数据查询性能。在演示的时候还是使用之前准备的一张表tb_sku ,在这张表中准备了1000w的记录。 这张表中id为主键有主键索引而其他字段是没有建立索引的。先来查询其中的一条记录看看里面的字段情况执行如下SQL
select * from tb_sku where id 1\G; 可以看到即使有1000w的数据,根据id进行数据查询,性能依然很快因为主键id是有索引的。那么接下来再来根据sn字段进行查询执行如下SQL
SELECT * FROM tb_sku WHERE sn 100000003145001; 可以看到根据sn字段进行查询查询返回了一条数据结果耗时20.78sec就是因为sn没有索引而造成查询效率很低。
那么可以针对于sn字段建立一个索引建立了索引之后再次根据sn进行查询再来看一下查询耗时情况。
创建索引
create index idx_sku_sn on tb_sku(sn); 然后再次执行相同的SQL语句再次查看SQL的耗时。
SELECT * FROM tb_sku WHERE sn 100000003145001; 明显会看到sn字段建立了索引之后查询性能大大提升。建立索引前后查询耗时都不是一个数量级的。
2.6.2最左前缀法则
如果索引了多列联合索引要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将会部分失效(后面的字段索引失效)。
以tb_user表为例先来查看一下之前tb_user表所创建的索引。 在tb_user表中有一个联合索引这个联合索引涉及到三个字段顺序分别为professionagestatus。
对于最左前缀法则指的是查询时最左边的列也就是profession必须存在否则索引全部失效。而且中间不能跳过某一列否则该列后面的字段索引将失效。接下来来演示几组案例看一下具体的执行计划
explain select * from tb_user where profession 软件工程 and age 31 and status 0; explain select * from tb_user where profession 软件工程 and age 31; explain select * from tb_user where profession 软件工程; 以上的这三组测试中发现只要联合索引最左边的字段profession存在索引就会生效只不过索引的长度不同。而且由以上三组测试也可以推测出profession字段索引长度为47、age字段索引长度为2、status字段索引长度为5。
explain select * from tb_user where age 31 and status 0; explain select * from tb_user where status 0; 而通过上面的这两组测试也可以看到索引并未生效原因是因为不满足最左前缀法则联合索引最左边的列profession不存在。
explain select * from tb_user where profession 软件工程 and status 0; 上述的SQL查询时存在profession字段最左边的列是存在的索引满足最左前缀法则的基本条件。但是查询时跳过了age这个列所以后面的列索引是不会使用的也就是索引部分生效所以索引的长度就是47。 思考题 当执行SQL语句:explain select * from tb_user where age 31 and status 0 and profession 软件工程时是否满足最左前缀法则走不走上述的联合索引索引长度 可以看到是完全满足最左前缀法则的索引长度54联合索引是生效的。 注意最左前缀法则中指的最左边的列是指在查询时联合索引的最左边的字段(即是第一个字段)必须存在与编写SQL时条件编写的先后顺序无关。 2.6.3范围查询
联合索引中出现范围查询(,)范围查询右侧的列索引失效。
explain select * from tb_user where profession 软件工程 and age 30 and status 0; 当范围查询使用或时走联合索引了但是索引的长度为49就说明范围查询右边的status字段是没有走索引的。
explain select * from tb_user where profession 软件工程 and age 30 and status 0; 当范围查询使用或时走联合索引了但是索引的长度为54就说明所有的字段都是走索引的。
所以在业务允许的情况下尽可能的使用类似于或这类的范围查询而避免使用或
2.6.4索引失效情况
2.6.4.1索引列运算
不要在索引列上进行运算操作索引将失效。
在tb_user表中除了前面介绍的联合索引之外还有一个索引是phone字段的单列索引。 A.当根据phone字段进行等值匹配查询时,索引生效。
explain select * from tb_user where phone 17799990015; B.当根据phone字段进行函数运算操作之后索引失效。
explain select * from tb_user where substring(phone, 10, 2) 15; 2.6.4.2字符串不加引号
字符串类型字段使用时不加引号索引将失效。
接下来通过两组示例来看看对于字符串类型的字段加单引号与不加单引号的区别
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; explain select * from tb_user where phone 17799990015;explain select * from tb_user where phone 17799990015; 经过上面两组示例会明显的发现如果字符串不加单引号对于查询结果没什么影响但是数据库存在隐式类型转换索引将失效。
2.6.4.3模糊查询
如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。
接下来来看一下这三条SQL语句的执行效果查看一下其执行计划
由于下面查询语句中都是根据profession字段查询符合最左前缀法则联合索引是可以生效的主要看一下模糊查询时%加在关键字之前和加在关键字之后的影响。
explain select * from tb_user where profession like 软件%;explain select * from tb_user where profession like %工程;explain select * from tb_user where profession like %工%; 经过上述的测试发现在like模糊查询中在关键字后面加%索引可以生效。而如果在关键字前面加了%索引将会失效。
3.6.4.4 or连接条件
用or分割开的条件如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。
explain select * from tb_user where id 10 or age 23;explain select * from tb_user where phone 17799990017 or age 23; 由于age没有索引所以即使id、phone有索引索引也会失效。所以需要针对于age也要建立索引。
然后可以对age字段建立索引。
create index idx_user_age on tb_user(age); 建立了索引之后再次执行上述的SQL语句看看前后执行计划的变化。 最终发现当or连接的条件左右两侧字段都有索引时索引才会生效。
3.6.4.5数据分布影响
如果MySQL评估使用索引比全表更慢则不使用索引。
select * from tb_user where phone 17799990005;select * from tb_user where phone 17799990015; 经过测试发现相同的SQL语句只是传入的字段值不同最终的执行计划也完全不一样这是为什么呢
就是因为MySQL在查询时会评估使用索引的效率与走全表扫描的效率如果走全表扫描更快则放弃索引走全表扫描。因为索引是用来索引少量数据的如果通过索引查询返回大批量的数据则还不如走全表扫描来的快此时索引就会失效。
接下来再来看看is null与is not null操作是否走索引。
执行如下两条语句
explain select * from tb_user where profession is null;explain select * from tb_user where profession is not null; 接下来做一个操作将profession字段值全部更新为null。 然后再次执行上述的两条SQL查看SQL语句的执行计划。 最终看到一模一样的SQL语句先后执行了两次结果查询计划是不一样的为什么会出现这种现象这是和数据库的数据分布有关系。查询时MySQL会评估走索引快还是全表扫描快如果全表扫描更快则放弃索引走全表扫描。因此is null、is not null是否走索引得具体情况具体分析并不是固定的。
2.6.5SQL提示
目前tb_user表的数据情况如下: 索引情况如下: 把上述的idx_user_age, idx_email这两个之前测试使用过的索引直接删除。
drop index idx_user_age on tb_user;drop index idx_email on tb_user;
A.执行SQL : explain select * from tb_user where profession 软件工程; 查询走了联合索引。
B.执行SQL创建profession的单列索引create index idx_user_pro on tb_user(profession); C.创建单列索引后再次执行A中的SQL语句查看执行计划看看到底走哪个索引。 测试结果可以看到possible_keys中idx_user_pro_age_sta,idx_user_pro这两个索引都可能用到最终MySQL选择了idx_user_pro_age_sta索引。这是MySQL自动选择的结果。
那么能不能在查询的时候自己来指定使用哪个索引呢答案是肯定的此时就可以借助于MySQL的SQL提示来完成。接下来介绍一下SQL提示。
SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
1).use index建议MySQL使用哪一个索引完成此次查询仅仅是建议mysql内部还会再次进行评估。
explain select * from tb_user use index(idx_user_pro) where profession 软件工程;
2).ignore index忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession 软件工程;
3).force index强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession 软件工程;
示例演示
A. use index
explain select * from tb_user use index(idx_user_pro) where profession 软件工程; B. ignore index
explain select * from tb_user ignore index(idx_user_pro) where profession 软件工程; C. force index
explain select * from tb_user force index(idx_user_pro_age_sta) where profession 软件工程; 2.6.6覆盖索引
尽量使用覆盖索引减少select *。那么什么是覆盖索引呢覆盖索引是指查询使用了索引并且需要返回的列在该索引中已经全部能够找到。
接下来来看一组SQL的执行计划看看执行计划的差别然后再来具体做一个解析。
explain select id, profession from tb_user where profession 软件工程 and age 31 and status 0;explain select id, profession, age, status from tb_user where profession 软件工程 and age 31 and status 0;explain select id, profession, age, status, name from tb_user where profession 软件工程 and age 31 and status 0;explain select * from tb_user where profession 软件工程 and age 31 and status 0;
上述这几条SQL的执行结果为: 从上述的执行计划可以看到这四条SQL语句的执行计划前面所有的指标都是一样的看不出来差异。但是此时主要关注的是后面的Extra前面两条SQL的结果为Using where; UsingIndex ;而后面两条SQL的结果为:Usingindex condition。 因为在tb_user表中有一个联合索引idx_user_pro_age_sta该索引关联了三个字段profession、age、status而这个索引也是一个二级索引所以叶子节点下面挂的是这一行的主键id。所以当查询返回的数据在id、profession、age、status之中则直接走二级索引直接返回数据了。如果超出这个范围就需要拿到主键id再去扫描聚集索引再获取额外的数据了这个过程就是回表。而如果一直使用select *查询返回所有字段值很容易就会造成回表查询除非是根据主键查询此时只会扫描聚集索引。
为了更清楚的理解什么是覆盖索引什么是回表查询一起再来看下面的这组SQL的执行过程。 id是主键是一个聚集索引。name字段建立了普通索引是一个二级索引辅助索引。
B.执行SQL : select * from tb_user where id 2; 根据id查询直接走聚集索引查询一次索引扫描直接返回数据性能高。
C.执行SQLselet id,name from tb_user where name Arm; 虽然是根据name字段查询查询二级索引但是由于查询返回在字段为idname在name的二级索引中这两个值都是可以直接获取到的因为覆盖索引所以不需要回表查询性能高。
D.执行SQLselet id,name,gender from tb_user where name Arm; 由于在name的二级索引中不包含gender所以需要两次索引扫描也就是需要回表查询性能相对较差一点。 思考题 一张表,有四个字段(id, username, password, status),由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方案: select id,username,password from tb_user where username itcast; 答案:针对于username, password建立联合索引,sql为:create indexidx_user_name_pass on tb_user(username,password); 这样可以避免上述的SQL语句在查询的过程中出现回表查询。 2.6.7前缀索引
当字段类型为字符串varchartextlongtext等时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO影响查询效率。此时可以只将字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率。
1).语法
create index idx_xxxx on table_name(column(n));
示例:
为tb_user表的email字段建立长度为5的前缀索引。
create index idx_email_5 on tb_user(email(5)); 2).前缀长度
可以根据索引的选择性来决定而选择性是指不重复的索引值基数和数据表的记录总数的比值索引选择性越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性性能也是最好的。
select count(distinct email)/count(*) from tb_user;select count(distinct substring(email, 1, 5))/count(*) from tb_user; 3).前缀索引的查询流程 2.6.8单列索引与联合索引
单列索引即一个索引只包含单个列。
联合索引即一个索引包含了多个列。
先来看看tb_user表中目前的索引情况: 在查询出来的索引中既有单列索引又有联合索引。
接下来来执行一条SQL语句看看其执行计划 通过上述执行计划可以看出来在and连接的两个字段phone、name上都是有单列索引的但是最终mysql只会选择一个索引也就是说只能走一个字段的索引此时是会回表查询的。
紧接着再来创建一个phone和name字段的联合索引来查询一下执行计划。
create unique index idx_user_phone_name on tb_user(phone, name); 此时查询时就走了联合索引而在联合索引中包含phone、name的信息在叶子节点下挂的是对应的主键id所以查询是无需回表查询的。 在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引。 如果查询使用的是联合索引具体的结构示意图如下 2.7索引设计原则
1).针对于数据量较大且查询比较频繁的表建立索引。
2).针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引。
3).尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。
4).如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。
5).尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。
6).要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。
7).如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。
3. SQL优化
3.1插入数据
3.1.1insert
如果需要一次性往数据库表中插入多条记录可以从以下三个方面进行优化。
insert into tb_test values (1, tom);insert into tb_test values (2, cat);insert into tb_test values (3, jerry);.....
1).优化方案一
批量插入数据
insert into tb_test values (1, Tom), (2, Cat), (3, Jerry);
2).优化方案二
手动控制事务
start transaction;insert into tb_test values (1, Tom), (2, Cat), (3, Jerry);insert into tb_test values (4, Tom), (5, Cat), (6, Jerry);insert into tb_test values (7, Tom), (8, Cat), (9, Jerry);commit;
3).优化方案三
主键顺序插入性能要高于乱序插入。
主键乱序插入:
8 1 9 21 88 2 4 15 89 5 7 3主键顺序插入:
1 2 3 4 5 7 8 9 15 21 88 89
3.1.2大批量插入数据
如果一次性需要插入大批量数据(比如:几百万的记录)使用insert语句插入性能较低此时可以使用MySQL数据库提供的load指令进行插入。操作如下 可以执行如下指令将数据脚本文件中的数据加载到表结构中
--客户端连接服务端时加上参数-–local-infile
mysql --local-infile -u root -p--设置全局参数local_infile为1开启从本地加载文件导入数据的开关
set global local_infile 1;--执行load指令将准备好的数据加载到表结构中
load data local infile /root/sql1.log into table tb_user fields terminated by , lines terminated by \n; 主键顺序插入性能高于乱序插入 示例演示:
A.创建表结构
CREATE TABLE tb_user (id INT(11) NOT NULL AUTO_INCREMENT,username VARCHAR(50) NOT NULL,password VARCHAR(50) NOT NULL,name VARCHAR(20) NOT NULL,birthday DATE DEFAULT NULL,sex CHAR(1) DEFAULT NULL,PRIMARY KEY(id),UNIQUE KEY unique_user_username(username)
) ENGINEINNODB DEFAULT CHARSETutf8; B.设置参数
--客户端连接服务端时加上参数-–local-infile
mysql --local-infile -u root -p--设置全局参数local_infile为1开启从本地加载文件导入数据的开关
set global local_infile 1;
C. load加载数据
load data local infile /root/load_user_100w_sort.sql into table tb_user fields terminated by , lines terminated by \n; 看到插入100w的记录17s就完成了性能很好。 在load时主键顺序插入性能高于乱序插入 3.2主键优化
在上一小节提到主键顺序插入的性能是要高于乱序插入的。这一小节就来介绍一下具体的原因然后再分析一下主键又该如何设计。
1).数据组织方式
在InnoDB存储引擎中表数据都是根据主键顺序组织存放的这种存储方式的表称为索引组织表(index organized table IOT)。 行数据都是存储在聚集索引的叶子节点上的。而之前也讲解过InnoDB的逻辑结构图 在InnoDB引擎中数据行是记录在逻辑结构page页中的而每一个页的大小是固定的默认16K。那也就意味着一个页中所存储的行也是有限的如果插入的数据行row在该页存储不下将会存储到下一个页中页与页之间会通过指针连接。
2).页分裂
页可以为空也可以填充一半也可以填充100%。每个页包含了2-N行数据(如果一行数据过大会行溢出)根据主键排列。
A.主键顺序插入效果
①.从磁盘中申请页主键顺序插入 ②.第一个页没有满继续往第一页插入 ③.当第一个也写满之后再写入第二个页页与页之间会通过指针连接 ④.当第二页写满了再往第三页写入 B.主键乱序插入效果
①.假如1#,2#页都已经写满了存放了如图所示的数据 ②.此时再插入id为50的记录来看看会发生什么现象
会再次开启一个页写入新的页中吗 不会。因为索引结构的叶子节点是有顺序的。按照顺序应该存储在47之后。 但是47所在的1#页已经写满了存储不了50对应的数据了。那么此时会开辟一个新的页3#。 但是并不会直接将50存入3#页而是会将1#页后一半的数据移动到3#页然后在3#页插入50。 移动数据并插入id为50的数据之后那么此时这三个页之间的数据顺序是有问题的。1#的下一个页应该是3#3#的下一个页是2#。所以此时需要重新设置链表指针。
上述的这种现象称之为页分裂是比较耗费性能的操作。
3).页合并
目前表中已有数据的索引结构(叶子节点)如下 当对已有数据进行删除时具体的效果如下:
当删除一行记录时实际上记录并没有被物理删除只是记录被标记flaged为删除并且它的空间变得允许被其他记录声明使用。 当继续删除2#的数据记录 当页中删除的记录达到MERGE_THRESHOLD默认为页的50%InnoDB会开始寻找最靠近的页前或后看看是否可以将两个页合并以优化空间使用。 删除数据并将页合并之后再次插入新的数据20则直接插入3#页
这个里面所发生的合并页的这个现象就称之为页合并。 知识小贴士 MERGE_THRESHOLD合并页的阈值可以自己设置在创建表或者创建索引时指定。 4).索引设计原则
满足业务需求的情况下尽量降低主键的长度。 插入数据时尽量选择顺序插入选择使用AUTO_INCREMENT自增主键。尽量不要使用UUID做主键或者是其他自然主键如身份证号。业务操作时避免对主键的修改。
3.3order by优化
MySQL的排序有两种方式
Using filesort :通过表的索引或全表扫描读取满足条件的数据行然后在排序缓冲区sortbuffer中完成排序操作所有不是通过索引直接返回排序结果的排序都叫FileSort排序。Using index :通过有序索引顺序扫描直接返回有序数据这种情况即为using index不需要额外排序操作效率高。
对于以上的两种排序方式Using index的性能高而Using filesort的性能低在优化排序操作时尽量要优化为Using index。
接下来来做一个测试
A.数据准备
把之前测试时为tb_user表所建立的部分索引直接删除掉
drop index idx_user_phone on tb_user;drop index idx_user_phone_name on tb_user;drop index idx_user_name on tb_user; B.执行排序SQL
explain select id, age, phone from tb_user order by age; explain select id, age, phone from tb_user order by age, phone; 由于age, phone都没有索引所以此时再排序时出现Using filesort排序性能较低。
C.创建索引
--创建索引
create index idx_user_age_phone_aa on tb_user(age, phone); D.创建索引后根据age, phone进行升序排序
explain select id, age, phone from tb_user order by age; explain select id, age, phone from tb_user order by age, phone; 建立索引之后再次进行排序查询就由原来的Using filesort变为了Using index性能就是比较高的了。
E.创建索引后根据age, phone进行降序排序
explain select id, age, phone from tb_user order by age desc, phone desc; 也出现Using index但是此时Extra中出现了Backward index scan这个代表反向扫描索引因为在MySQL中我们创建的索引默认索引的叶子节点是从小到大排序的而此时查询排序时是从大到小所以在扫描时就是反向扫描就会出现Backward index scan。在MySQL8版本中支持降序索引也可以创建降序索引。
F.根据phoneage进行升序排序phone在前age在后。
explain select id, age, phone from tb_user order by phone, age; 排序时,也需要满足最左前缀法则,否则也会出现filesort。因为在创建索引的时候age是第一个字段phone是第二个字段所以排序时也就该按照这个顺序来否则就会出现Using filesort。
F.根据age, phone进行降序一个升序一个降序
explain select id, age, phone from tb_user order by age asc, phone desc; 因为创建索引时如果未指定顺序默认都是按照升序排序的而查询时一个升序一个降序此时就会出现Using filesort。 为了解决上述的问题可以创建一个索引这个联合索引中age升序排序phone倒序排序。
G.创建联合索引(age升序排序phone倒序排序)
create index idx_user_age_phone_ad on tb_user(age asc, phone desc); H.然后再次执行如下SQL
explain select id, age, phone from tb_user order by age asc, phone desc; 升序/降序联合索引结构图示: 由上述的测试,得出order by优化原则:
A.根据排序字段建立合适的索引多字段排序时也遵循最左前缀法则。
B.尽量使用覆盖索引。
C.多字段排序,一个升序一个降序此时需要注意联合索引在创建时的规则ASC/DESC。
D.如果不可避免的出现filesort大数据量排序时可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
3.4group by优化
分组操作主要来看看索引对于分组操作的影响。
首先先将tb_user表的索引全部删除掉。
drop index idx_user_pro_age_sta on tb_user;drop index idx_email_5 on tb_user; drop index idx_user_age_phone_aa on tb_user;drop index idx_user_age_phone_ad on tb_user; 接下来在没有索引的情况下执行如下SQL查询执行计划
explain select profession, count(*) from tb_user group by profession; 然后再针对于professionagestatus创建一个联合索引。
create index idx_user_pro_age_sta on tb_user(profession, age, status);
紧接着再执行前面相同的SQL查看执行计划。
explain select profession, count(*) from tb_user group by profession; 再执行如下的分组查询SQL查看执行计划 发现如果仅仅根据age分组就会出现Using temporary而如果是根据profession,age两个字段同时分组则不会出现Using temporary。原因是因为对于分组操作在联合索引中也是符合最左前缀法则的。
所以在分组操作中需要通过以下两点进行优化以提升性能
A.在分组操作时可以通过索引来提高效率。
B.分组操作时索引的使用也是满足最左前缀法则的。
3.5limit优化
在数据量比较大时如果进行limit分页查询在查询时越往后分页查询效率越低。
一起来看看执行limit分页查询耗时对比 通过测试会看到越往后分页查询效率越低这就是分页查询的问题所在。
因为当在进行分页查询时如果执行limit 2000000,10此时需要MySQL排序前2000010记录仅仅返回2000000 - 2000010的记录其他记录丢弃查询排序的代价非常大。
优化思路:一般分页查询时通过创建覆盖索引能够比较好地提高性能可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku t, (select id from tb_sku order by id limit 2000000, 10) a where t.id a.id;
3.6count优化
3.6.1概述
select count(*) from tb_user;
在之前的测试中发现如果数据量很大在执行count操作时是非常耗时的。
MyISAM引擎把一个表的总行数存在了磁盘上因此执行count(*)的时候会直接返回这个数效率很高但是如果是带条件的countMyISAM也慢。InnoDB引擎就麻烦了它执行count(*)的时候需要把数据一行一行地从引擎里面读出来然后累积计数。
如果说要大幅度提升InnoDB表的count效率主要的优化思路自己计数(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。
3.6.2count用法
count()是一个聚合函数对于返回的结果集一行行地判断如果count函数的参数不是NULL累计值就加1否则不加最后返回累计值。
用法count*、count主键、count字段、count数字 按照效率排序的话count(字段) count(主键id) count(1) ≈ count(*)所以尽量使用count(*)。 3.7update优化
主要需要注意一下update语句执行时的注意事项。
update course set name javaEE where id 1;
当在执行删除的SQL语句时会锁定id为1这一行的数据然后事务提交之后行锁释放。
但是当在执行如下SQL时。
update course set name SpringBoot where name PHP;
当开启多个事务在执行上述的SQL时会发现行锁升级为了表锁。导致该update语句的性能大大降低。 InnoDB的行锁是针对索引加的锁不是针对记录加的锁,并且该索引不能失效否则会从行锁升级为表锁。 4.视图/存储过程/触发器
4.1视图
4.1.1介绍
视图View是一种虚拟存在的表。视图中的数据并不在数据库中实际存在行和列数据来自定义视图的查询中使用的表并且是在使用视图时动态生成的。
通俗的讲视图只保存了查询的SQL逻辑不保存查询结果。所以在创建视图的时候主要的工作就落在创建这条SQL查询语句上。
4.1.2语法
1).创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS
SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
2).查询
-- 查看创建视图语句
SHOW CREATE VIEW 视图名称;-- 查看视图数据
SELECT * FROM 视图名称......;
3).修改
-- 方式一
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS
SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION]-- 方式二
ALTER VIEW 视图名称[(列名列表)] AS
SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
4).删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
演示示例
-- 创建视图
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;select * from stu_v_1;insert into stu_v_1 values(6, Tom);insert into stu_v_1 values(17, Tom22);
执行上述的SQL会发现id为6和17的数据都是可以成功插入的。但是执行查询查询出来的数据却没有id为17的记录。 因为在创建视图的时候指定的条件为id10, id为17的数据是不符合条件的所以没有查询出来但是这条数据确实是已经成功的插入到了基表中。
如果定义视图时指定了条件然后在插入、修改、删除数据时是否可以做到必须满足条件才能操作否则不能够操作呢答案是可以的这就需要借助于视图的检查选项了。
4.1.3检查选项
当使用WITH CHECK OPTION子句创建视图时MySQL会通过视图检查正在更改的每个行例如插入更新删除以使其符合视图的定义。MySQL允许基于另一个视图创建视图它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围mysql提供了两个选项CASCADED和LOCAL默认值为CASCADED。
1).CASCADED
级联。
比如v2视图是基于v1视图的如果在v2视图创建的时候指定了检查选项为cascaded但是v1视图创建时未指定检查选项。则在执行检查时不仅会检查v2还会级联检查v2的关联视图v1。 2).LOCAL
本地。
比如v2视图是基于v1视图的如果在v2视图创建的时候指定了检查选项为local但是v1视图创建时未指定检查选项。则在执行检查时只会检查v2不会检查v2的关联视图v1。 4.1.4视图的更新
要使视图可更新视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项则该视图不可更新
A.聚合函数或窗口函数SUM()、MIN()、MAX()、COUNT()等
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION或者UNION ALL
示例演示:
create view stu_v_count as
select count(*) from student;
上述的视图中就只有一个单行单列的数据如果对这个视图进行更新或插入的将会报错。
insert into stu_v_count values(10); 4.1.5视图作用
1).简单
视图不仅可以简化用户对数据的理解也可以简化他们的操作。那些被经常使用的查询可以被定义为视图从而使得用户不必为以后的操作每次指定全部的条件。
2).安全
数据库可以授权但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
3).数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。
4.1.6案例
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.courseid c.id;select * from tb_stu_course_view;
4.2存储过程
4.2.1介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合调用存储过程可以简化应用开发人员的很多工作减少数据在数据库和应用服务器之间的传输对于提高数据处理的效率是有好处的。存储过程思想上很简单就是数据库SQL语言层面的代码封装与重用。 特点:
封装复用-----------------------可以把某一业务SQL封装在存储过程中需要用到的时候直接调用即可。可以接收参数也可以返回数据--------在存储过程中可以传递参数也可以接收返回值。减少网络交互效率提升-------------如果涉及到多条SQL每执行一次都是一次网络传输。而如果封装在存储过程中只需要网络交互一次可能就可以了。
4.2.2基本语法
1).创建
CREATE PROCEDURE 存储过程名称 ([参数列表])
BEGIN
--SQL语句
END;
2).调用
CALL 名称([参数]);
3).查看
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA xxx;-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;
4).删除
DROP PROCEDURE [IF EXISTS] 存储过程名称; 注意: 在命令行中执行创建存储过程的SQL时需要通过关键字delimiter指定SQL语句的结束符。 演示示例:
-- 存储过程基本语法
-- 创建
create procedure p1 ()
beginselect count(*) from student;
end;-- 调用
call p1();-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA itcast;show create procedure p1;-- 删除
drop procedure if exists p1; 4.2.3变量
在MySQL中变量分为三种类型:系统变量、用户定义变量、局部变量。
4.2.3.1系统变量
系统变量是MySQL服务器提供不是用户定义的属于服务器层面。分为全局变量GLOBAL、会话变量SESSION。
1).查看系统变量
-- 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES;-- 可以通过LIKE模糊匹配方式查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE ......;-- 查看指定变量的值
SELECT [SESSION | GLOBAL] 系统变量名;
2).设置系统变量
SET [SESSION | GLOBAL] 系统变量名 值;SET [SESSION | GLOBAL] 系统变量名 值;
注意:
如果没有指定SESSION/GLOBAL默认是SESSION会话变量。
1.mysql服务重新启动之后所设置的全局参数会失效要想不失效可以在/etc/my.cnf中配置。
A.全局变量(GLOBAL):全局变量针对于所有的会话。
B.会话变量(SESSION):会话变量针对于单个会话在另外一个会话窗口就不生效了。
演示示例:
-- 查看系统变量
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;
4.2.3.2用户定义变量
用户定义变量是用户根据需要自己定义的变量用户变量不用提前声明在用的时候直接用变量名使用就可以。其作用域为当前连接。
1).赋值
方式一:
SET var_name expr [, var_name expr] ...;SET var_name : expr [, var_name: expr] ...;
赋值时可以使用也可以使用:。
方式二:
SELECT var_name : expr [, var_name : expr] ...;SELECT 字段名 INTO var_name FROM 表名;
2).使用
SELECT var_name; 注意: 用户定义的变量无需对其进行声明或初始化只不过获取到的值为NULL。 演示示例:
-- 赋值
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; 4.2.3.3局部变量
局部变量是根据需要定义的在局部生效的变量访问之前需要DECLARE声明。可用作存储过程内的局部变量和输入参数局部变量的范围是在其内声明的BEGIN ...END块。
1).声明
DECLARE 变量名 变量类型 [DEFAULT ...];
变量类型就是数据库字段类型INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
2).赋值
SET 变量名 值;SET 变量名 : 值;SELECT 字段名 INTO 变量名 FROM 表名 ...;
演示示例:
-- 声明局部变量-declare
-- 赋值
create procedure p2()
begindeclare stu_count int default 0;select count(*) into stu_count from student;select stu_count;
end;call p2();
4.2.4if
1).介绍
if用于做条件判断具体的语法结构为
IF 条件1 THEN.....
-- 可选
ELSEIF 条件2 THEN.....
-- 可选
ELSE.....
END IF;
在if条件判断的结构中ELSE IF结构可以有多个也可以没有。ELSE结构可以有也可以没有。
2).案例
根据定义的分数score变量判定当前分数对应的分数等级。
score 85分等级为优秀。score 60分且score 85分等级为及格。score 60分等级为不及格。
create procedure p3()
begindeclare score int default 58;declare result varchar(10);if score 85 thenset result : 优秀;elseif score 60 thenset result : 及格;elseset result : 不及格;end if;select result;end;call p3();
上述的需求虽然已经实现了但是也存在一些问题比如score分数是在存储过程中定义死的而且最终计算出来的分数等级也仅仅是最终查询展示出来而已。
那么能不能把score分数动态的传递进来计算出来的分数等级是否可以作为返回值返回呢答案是肯定的可以通过接下来所讲解的参数来解决上述的问题。
4.2.5参数
1).介绍
参数的类型主要分为以下三种IN、OUT、INOUT。具体的含义如下 用法
CREATE PROCEDURE 存储过程名称 ([IN/OUT/INOUT 参数名 参数类型])
BEGIN--SQL语句
END;
2).案例一
根据传入参数score判定当前分数对应的分数等级并返回。
score 85分等级为优秀。score 60分且score 85分等级为及格。score 60分等级为不及格。
create procedure p4(in score int, out result varchar(10))
beginif score 85 thenset result : 优秀;elseif score 60 thenset result : 及格;elseset result : 不及格;end if;end;-- 定义用户变量result来接收返回的数据,用户变量可以不用声明
call p4(18, result);select result;
3).案例二
将传入的200分制的分数进行换算换算成百分制然后返回。
create procedure p5(inout score double)
beginset score : score * 0.5;
end;set score 198;call p5(score);select score;
4.2.6case
1).介绍
case结构及作用和在基础篇中所讲解的流程控制函数很类似。有两种语法格式
语法1
--含义当case_value的值为when_value1时执行statement_list1当值为when_value2时执行statement_list2否则就执行statement_list
CASE case_valueWHEN when_value1 THEN statement_list1[ WHEN when_value2 THEN statement_list2 ]...[ ELSE statement_list ]
END CASE;
语法2
--含义当条件search_condition1成立时执行statement_list1当条件search_condition2成立时执行statement_list2否则就执行statement_list
CASE WHEN search_condition1 THEN statement_list1[ WHEN search_condition2 THEN statement_list2 ]...[ ELSE statement_list]
END CASE;
2).案例
根据传入的月份判定月份所属的季节要求采用case结构。
1-3月份为第一季度4-6月份为第二季度7-9月份为第三季度10-12月份为第四季度
create procedure p6(in month int)
begindeclare result varchar(10);casewhen month 1 and month 3 thenset result : 第一季度;when month 4 and month 6 thenset result : 第二季度;when month 7 and month 9 thenset result : 第三季度;when month 10 and month 12 thenset result : 第四季度;elseset result : 非法参数;end case;select concat(您输入的月份为:, month, ,所属的季度为:, result);end;call p6(16); 注意 如果判定条件有多个多个条件之间可以使用and或or进行连接。 4.2.7while
1).介绍
while循环是有条件的循环控制语句。满足条件后再执行循环体中的SQL语句。具体语法为
-- 先判定条件如果条件为true则执行逻辑否则不执行逻辑
WHILE 条件 DOSQL逻辑...
END WHILE;
2).案例
计算从1累加到n的值n为传入的参数值。
-- A.定义局部变量,记录累加之后的值;
-- B.每循环一次,就会对n进行减1,如果n减到0,则退出循环
create procedure p7 (in n int)
begindeclare total int default 0;while n 0 doset total : total n;set n : n - 1;end while;select total;end;call p7(100);
4.2.8repeat
1).介绍
repeat是有条件的循环控制语句,当满足until声明的条件的时候则退出循环。具体语法为
-- 先执行一次逻辑然后判定UNTIL条件是否满足如果满足则退出。如果不满足则继续下一次循环
REPEAT SQL逻辑...UNTIL 条件
END REPEAT;
2).案例
计算从1累加到n的值n为传入的参数值。(使用repeat实现)
-- A.定义局部变量,记录累加之后的值;
-- B.每循环一次,就会对n进行-1,如果n减到0,则退出循环
create procedure p8 (in n int)
begindeclare total int default 0;repeatset total : total n;set n : n - 1;until n 0end repeat;select total;end;call p8(10);
call p8(100);4.2.9loop
1).介绍
LOOP实现简单的循环如果不在SQL逻辑中增加退出循环的条件可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用
LEAVE配合循环使用退出循环。ITERATE必须用在循环中作用是跳过当前循环剩下的语句直接进入下一次循环。
[begin_label:] LOOPSQL 逻辑...
END LOOP [end_label];-- 退出指定标记的循环体
LEAVE label;-- 直接进入下一次循环
ITERATE label;上述语法中出现的begin_labelend_labellabel指的都是所自定义的标记。
2).案例一
计算从1累加到n的值n为传入的参数值。
-- A.定义局部变量,记录累加之后的值;
-- B.每循环一次,就会对n进行-1,如果n减到0,则退出循环----leave xx
create procedure p9 (in n int)
begindeclare total int default 0;sum : loopif n 0 thenleave sum;end if;set total : total n;set n : n - 1;end loop sum;select total;
end;call p9(100);
3).案例二
计算从1到n之间的偶数累加的值n为传入的参数值。
-- A.定义局部变量,记录累加之后的值;
-- B.每循环一次,就会对n进行-1,如果n减到0,则退出循环----leave xx
-- C.如果当次累加的数据是奇数,则直接进入下一次循环.--------iterate xx
create procedure p10 (in n int)
begindeclare total int default 0;sum : loopif n 0 thenleave sum;end if;if n % 2 1 thenset n : n - 1;iterate sum;end if;set total : total n;set n : n - 1;end loop sum;select total;end;call p10(100);4.2.10游标
1).介绍
游标CURSOR是用来存储查询结果集的数据类型,在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE其语法分别如下。
A.声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
B.打开游标
OPEN 游标名称;
C.获取游标记录
FETCH 游标名称 INTO 变量 [,变量];
D.关闭游标
CLOSE 游标名称;
2).案例
根据传入的参数uage来查询用户表tb_user中所有的用户年龄小于等于uage的用户姓名name和专业profession并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
-- 逻辑:
-- A.声明游标,存储查询结果集
-- B.准备:创建表结构
-- C.开启游标
-- D.获取游标中的记录
-- E.插入数据到新表中
-- F.关闭游标
create procedure p11 (in uage int)
begindeclare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name, profession from tb_user where age uage;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 dofetch u_cursor into uname, upro;insert into tb_user_pro values (null, uname, upro);end while;close u_cursor;end;call p11(30);
上述的存储过程最终在调用的过程中会报错之所以报错是因为上面的while循环中并没有退出条件。当游标的数据集获取完毕之后再次获取数据就会报错从而终止了程序的执行。 但是此时tb_user_pro表结构及其数据都已经插入成功了可以直接刷新表结构检查表结构中的数据。 上述的功能虽然实现了但是逻辑并不完善而且程序执行完毕获取不到数据数据库还报错。接下来就需要来完成这个存储过程并且解决这个问题。
要想解决这个问题就需要通过MySQL中提供的条件处理程序Handler来解决。
4.2.11条件处理程序
1).介绍
条件处理程序Handler可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。
具体语法为 2).案例
继续来完成在上一小节提出的这个需求并解决其中的问题。
根据传入的参数uage来查询用户表tb_user中所有的用户年龄小于等于uage的用户姓名name和专业profession并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
A.通过SQLSTATE指定具体的状态码
-- 逻辑:
-- A.声明游标,存储查询结果集
-- B.准备:创建表结构
-- C.开启游标
-- D.获取游标中的记录
-- E.插入数据到新表中
-- F.关闭游标
create procedure p11 (in uage int)
begindeclare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name, profession from tb_user where age uage;-- 声明条件处理程序当SQL语句执行抛出的状态码为02000时将关闭游标u_cursor并退出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 dofetch u_cursor into uname, upro;insert into tb_user_pro values (null, uname, upro);end while;close u_cursor;end;call p11(30);
B.通过SQLSTATE的代码简写方式NOT FOUND
02开头的状态码代码简写为NOT FOUND
create procedure p12 (in uage int)
begindeclare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name, profession from tb_user where age uage;-- 声明条件处理程序当SQL语句执行抛出的状态码为02开头时将关闭游标u_cursor并退出declare exit handler for not found 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 dofetch u_cursor into uname, upro;insert into tb_user_pro values (null, uname, upro);end while;close u_cursor;end;call p12(30);
具体的错误状态码可以参考官方文档
https://dev.mysql.com/doc/refman/8.0/en/declare-handler.htmlhttps://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
4.3存储函数
1).介绍
存储函数是有返回值的存储过程存储函数的参数只能是IN类型的。具体语法如下
CREATE FUNCTION 存储函数名称 ([参数列表])RETURNS type [characteristic ...]
BEGIN-- SQL语句RETURN ...;
END;
characteristic说明
DETERMINISTIC相同的输入参数总是产生相同的结果NO SQL不包含SQL语句。READS SQL DATA包含读取数据的语句但不包含写入数据的语句。
2).案例
计算从1累加到n的值n为传入的参数值。
create function fun1 (n int)returns int deterministic
begindeclare total int default 0;while n 0 doset total : total n;set n : n - 1;end while;return total;
end;select fun1(50);
在mysql8.0版本中binlog默认是开启的一旦开启了mysql就要求在定义存储过程时需要指定characteristic特性否则就会报如下错误 4.4触发器
4.4.1介绍
触发器是与表有关的数据库对象指在insert/update/delete之前(BEFORE)或之后(AFTER)触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容这与其他的数据库是相似的。现在触发器还只支持行级触发不支持语句级触发。 4.4.2语法 1).创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
-- 行级触发器
ON tbl_name FOR EACH ROW
BEGINtrigger_stmt;
END;
2).查看
SHOW TRIGGERS;
3).删除
-- 如果没有指定schema_name默认为当前数据库。
DROP TRIGGER [schema_name.] trigger_name;4.4.3案例
通过触发器记录tb_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;
A.插入数据触发器
create trigger tb_user_insert_triggerafter insert on tb_user for each row
begininsert 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;-- 插入数据到tb_user
insert into tb_user(id, name, phone, email, profession, age, gender, status, createtime) VALUES (26, 三皇子, 18809091212, erhuangzi163.com, 软件工程, 23, 1, 1, now());
测试完毕之后检查日志表中的数据是否可以正常插入以及插入数据的正确性。
B.修改数据触发器
create trigger tb_user_update_triggerafter update on tb_user for each row
begininsert 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;
测试完毕之后检查日志表中的数据是否可以正常插入以及插入数据的正确性。
C.删除数据触发器
create trigger tb_user_delete_triggerafter delete on tb_user for each rowbegininsert 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;
测试完毕之后检查日志表中的数据是否可以正常插入以及插入数据的正确性。
5.锁
5.1概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中除传统的计算资源CPU、RAM、I/O的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说锁对数据库而言显得尤其重要也更加复杂。
MySQL中的锁按照锁的粒度分分为以下三类
全局锁锁定数据库中的所有表。表级锁每次操作锁住整张表。行级锁每次操作锁住对应的行数据。
5.2全局锁
5.2.1介绍
全局锁就是对整个数据库实例加锁加锁后整个实例就处于只读状态后续的DML的写语句DDL语句已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份对所有的表进行锁定从而获取一致性视图保证数据的完整性。
为什么全库逻辑备份就需要加全局锁呢
A.一起先来分析一下不加全局锁可能存在的问题。
假设在数据库中存在这样三张表:tb_stock库存表tb_order订单表tb_orderlog订单日志表。 在进行数据备份时先备份了tb_stock库存表。然后接下来在业务系统中执行了下单操作扣减库存生成订单更新tb_stock表插入tb_order表。然后再执行备份tb_order表的逻辑。业务中执行插入订单日志操作。最后又备份了tb_orderlog表。
此时备份出来的数据是存在问题的。因为备份出来的数据tb_stock表与tb_order表的数据不一致(有最新操作的订单信息,但是库存数没减)。
那如何来规避这种问题呢?此时就可以借助于MySQL的全局锁来解决。
B.再来分析一下加了全局锁后的情况 对数据库进行进行逻辑备份之前先对整个数据库加上全局锁一旦加了全局锁之后其他的DDL、DML全部都处于阻塞状态但是可以执行DQL语句也就是处于只读状态而数据备份就是查询操作。那么数据在进行逻辑备份的过程中数据库中的数据就是不会发生变化的这样就保证了数据的一致性和完整性。
5.2.2语法
1).加全局锁
flush tables with read lock;
2).数据备份
mysqldump -uroot –p1234 itcast itcast.sql;
数据备份的相关指令,在后面MySQL管理章节,还会详细讲解。
3).释放锁
unlock tables;
5.2.3特点
数据库中加全局锁是一个比较重的操作存在以下问题
如果在主库上备份那么在备份期间都不能执行更新业务基本上就得停摆。如果在从库上备份那么在备份期间从库不能执行主库同步过来的二进制日志binlog会导致主从延迟。
在InnoDB引擎中可以在备份时加上参数--single-transaction参数来完成不加锁的一致性数据备份。
mysqldump --single-transaction -uroot –p123456 itcast itcast.sql;
5.3表级锁
5.3.1介绍
表级锁每次操作锁住整张表。锁定粒度大发生锁冲突的概率最高并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。
对于表级锁主要分为以下三类
表锁元数据锁meta data lockMDL意向锁
5.3.2表锁
对于表锁分为两类
表共享读锁read lock表独占写锁write lock
语法
加锁lock tables 表名 ... read/write。释放锁unlock tables / 客户端断开连接。
特点:
A.读锁 左侧为客户端一对指定表加了读锁不会影响右侧客户端二的读但是会阻塞右侧客户端的写。
测试: B.写锁 左侧为客户端一对指定表加了写锁会阻塞右侧客户端的读和写。
测试: 结论: 读锁不会阻塞其他客户端的读但是会阻塞写。写锁既会阻塞其他客户端的读又会阻塞其他客户端的写。 5.3.3元数据锁
meta data lock ,元数据锁简写MDL。
MDL加锁过程是系统自动控制无需显式使用在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性在表上有活动事务的时候不可以对元数据进行写入操作。为了避免DML与DDL冲突保证读写的正确性。
这里的元数据大家可以简单理解为就是一张表的表结构。也就是说某一张表涉及到未提交的事务时是不能够修改这张表的表结构的。
在MySQL5.5中引入了MDL当对一张表进行增删改查的时候加MDL读锁(共享)当对表结构进行变更操作的时候加MDL写锁(排他)。
常见的SQL操作时所添加的元数据锁 演示
当执行SELECT、INSERT、UPDATE、DELETE等语句时添加的是元数据共享锁SHARED_READ /SHARED_WRITE之间是兼容的。 当执行SELECT语句时添加的是元数据共享锁SHARED_READ会阻塞元数据排他锁EXCLUSIVE之间是互斥的。 可以通过下面的SQL来查看数据库中的元数据锁的情况
select object_type, object_schema, object_name, lock_type, lock_duration from performance_schema.metadata_locks;
在操作过程中可以通过上述的SQL语句来查看元数据锁的加锁情况。 5.3.4意向锁
1).介绍
为了避免DML在执行时加的行锁与表锁的冲突在InnoDB中引入了意向锁使得表锁不用检查每行数据是否加锁使用意向锁来减少表锁的检查。
假如没有意向锁客户端一对表加了行锁后客户端二如何给表加表锁呢来通过示意图简单分析一下
首先客户端一开启一个事务然后执行DML操作在执行DML语句时会对涉及到的行加行锁。 当客户端二想对这张表加表锁时会检查当前表是否有对应的行锁如果没有则添加表锁此时就会从第一行数据检查到最后一行数据效率较低。 有了意向锁之后:
客户端一在执行DML操作时会对涉及的行加行锁同时也会对该表加上意向锁。 而其他客户端在对这张表加表锁的时候会根据该表上所加的意向锁来判定是否可以成功加表锁而不用逐行判断行锁情况了。 2).分类
意向共享锁(IS):由语句select ...lock in share mode添加。与表锁共享锁(read)兼容与表锁排他锁(write)互斥。意向排他锁(IX):由insert、update、delete、select...for update添加。与表锁共享锁(read)及排他锁(write)都互斥意向锁之间不会互斥。 一旦事务提交了意向共享锁、意向排他锁都会自动释放。 可以通过以下SQL查看意向锁及行锁的加锁情况
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks; 演示
A.意向共享锁与表读锁是兼容的 B.意向排他锁与表读锁、写锁都是互斥的 5.4行级锁
5.4.1介绍
行级锁每次操作锁住对应的行数据。锁定粒度最小发生锁冲突的概率最低并发度最高。应用在InnoDB存储引擎中。
InnoDB的数据是基于索引组织的行锁是通过对索引上的索引项加锁来实现的而不是对记录加的锁。对于行级锁主要分为以下三类
行锁Record Lock锁定单个行记录的锁防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。间隙锁Gap Lock锁定索引记录间隙不含该记录确保索引记录间隙不变防止其他事务在这个间隙进行insert产生幻读。在RR隔离级别下都支持。临键锁Next-Key Lock行锁和间隙锁组合同时锁住数据并锁住数据前面的间隙Gap。在RR隔离级别下支持。
5.4.2行锁
1).介绍
InnoDB实现了以下两种类型的行锁
共享锁S允许一个事务去读一行阻止其他事务获得相同数据集的排它锁。排他锁X允许获取排他锁的事务更新数据阻止其他事务获得相同数据集的共享锁和排他锁。
两种行锁的兼容情况如下: 常见的SQL语句在执行时所加的行锁如下 2).演示
默认情况下InnoDB在REPEATABLE READ事务隔离级别运行InnoDB使用next-key锁进行搜索和索引扫描以防止幻读。
针对唯一索引进行检索时对已存在的记录进行等值匹配时将会自动优化为行锁。 InnoDB的行锁是针对于索引加的锁不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁此时就会升级为表锁。
可以通过以下SQL查看意向锁及行锁的加锁情况
select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;
示例演示
数据准备:
CREATE TABLE stu (id int NOT NULL PRIMARY KEY AUTO_INCREMENT,name varchar(255) DEFAULT NULL,age int NOT NULL
) ENGINE InnoDB CHARACTER SET utf8mb4;INSERT INTO stu VALUES (1, tom, 1);INSERT INTO stu VALUES (3, cat, 3);INSERT INTO stu VALUES (8, rose, 8);INSERT INTO stu VALUES (11, jetty, 11);INSERT INTO stu VALUES (19, lily, 19);INSERT INTO stu VALUES (25, luci, 25);
演示行锁的时候就通过上面这张表来演示一下。
A.普通的select语句执行时不会加锁。 B. select ... lock in share mode加共享锁共享锁与共享锁之间兼容。 共享锁与排他锁之间互斥。 客户端一获取的是id为1这行的共享锁客户端二是可以获取id为3这行的排它锁的因为不是同一行数据。而如果客户端二想获取id为1这行的排他锁会处于阻塞状态因为共享锁与排他锁之间互斥。
C.排它锁与排他锁之间互斥 当客户端一执行update语句会为id为1的记录加排他锁客户端二如果也执行update语句更新id为1的数据也要为id为1的数据加排他锁但是客户端二会处于阻塞状态因为排他锁之间是互斥的。直到客户端一把事务提交了才会把这一行的行锁释放此时客户端二解除阻塞。
D.无索引行锁升级为表锁
stu表中数据如下: 在两个客户端中执行如下操作:
在客户端一中开启事务并执行update语句更新name为Lily的数据也就是id为19的记录。然后在客户端二中更新id为3的记录却不能直接执行会处于阻塞状态为什么呢
原因就是因为此时客户端一根据name字段进行更新时name字段是没有索引的如果没有索引此时行锁会升级为表锁(因为行锁是对索引项加的锁而name没有索引)。
接下来再针对name字段建立索引索引建立之后再次做一个测试 此时可以看到客户端一开启事务然后依然是根据name进行更新。而客户端二在更新id为3的数据时更新成功并未进入阻塞状态。这样就说明根据索引字段进行更新操作就可以避免行锁升级为表锁的情况。
5.4.3间隙锁临键锁
默认情况下InnoDB在REPEATABLE READ事务隔离级别运行InnoDB使用next-key锁进行搜索和索引扫描以防止幻读。
索引上的等值查询(唯一索引)给不存在的记录加锁时,优化为间隙锁。索引上的等值查询(非唯一普通索引)向右遍历时最后一个值不满足查询需求时next-keylock退化为间隙锁。索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。 注意 间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。 示例演示
A.索引上的等值查询(唯一索引)给不存在的记录加锁时,优化为间隙锁。
B.索引上的等值查询(非唯一普通索引)向右遍历时最后一个值不满足查询需求时next-keylock退化为间隙锁。
介绍分析一下
知道InnoDB的B树索引叶子节点是有序的双向链表。假如要根据这个二级索引查询值为18的数据并加上共享锁是只锁定18这一行就可以了吗并不是因为是非唯一索引这个结构中可能有多个18的存在所以在加锁时会继续往后找找到一个不满足条件的值当前案例中也就是29。此时会对18加临键锁并对29之前的间隙加锁。 C.索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。
查询的条件为id19并添加共享锁。此时可以根据数据库表中现有的数据将数据分为三个部分
[19]
(19,25]
(25,∞]
所以数据库数据在加锁时就是将19加了行锁25的临键锁包含25及25之前的间隙正无穷的临键锁(正无穷及之前的间隙)。
6. InnoDB引擎
6.1逻辑存储结构
InnoDB的逻辑存储结构如下图所示: 1).表空间
表空间是InnoDB存储引擎逻辑结构的最高层如果用户启用了参数innodb_file_per_table(在8.0版本中默认开启)则每张表都会有一个表空间xxx.ibd一个mysql实例可以对应多个表空间用于存储记录、索引等数据。
2).段
段分为数据段Leaf node segment、索引段Non-leaf node segment、回滚段Rollback segmentInnoDB是索引组织表数据段就是B树的叶子节点索引段即为B树的非叶子节点。段用来管理多个Extent区。
3).区
区表空间的单元结构每个区的大小为1M。默认情况下InnoDB存储引擎页大小为16K即一个区中一共有64个连续的页。
4).页
页是InnoDB存储引擎磁盘管理的最小单元每个页的大小默认为16KB。为了保证页的连续性InnoDB存储引擎每次从磁盘申请4-5个区。
5).行
行InnoDB存储引擎数据是按行进行存放的。
在行中默认有两个隐藏字段
Trx_id每次对某条记录进行改动时都会把对应的事务id赋值给trx_id隐藏列。Roll_pointer每次对某条引记录进行改动时都会把旧的版本写入到undo日志中然后这个隐藏列就相当于一个指针可以通过它来找到该记录修改前的信息。
6.2架构
6.2.1概述
MySQL5.5版本开始默认使用InnoDB存储引擎它擅长事务处理具有崩溃恢复特性在日常开发中使用非常广泛。下面是InnoDB架构图左侧为内存结构右侧为磁盘结构。 6.2.2内存结构 在左侧的内存结构中主要分为这么四大块儿Buffer Pool、Change Buffer、AdaptiveHash Index、Log Buffer。接下来介绍一下这四个部分。
1).Buffer Pool
InnoDB存储引擎基于磁盘文件存储访问物理硬盘和在内存中进行访问速度相差很大为了尽可能弥补这两者之间的I/O效率的差值就需要把经常使用的数据加载到缓冲池中避免每次访问都进行磁盘I/O。
在InnoDB的缓冲池中不仅缓存了索引页和数据页还包含了undo页、插入缓存、自适应哈希索引以及InnoDB的锁信息等等。
缓冲池Buffer Pool是主内存中的一个区域里面可以缓存磁盘上经常操作的真实数据在执行增删改查操作时先操作缓冲池中的数据若缓冲池没有数据则从磁盘加载并缓存然后再以一定频率刷新到磁盘从而减少磁盘IO加快处理速度。
缓冲池以Page页为单位底层采用链表数据结构管理Page。根据状态将Page分为三种类型
free page空闲page未被使用。clean page被使用page数据没有被修改过。dirty page脏页被使用page数据被修改过也中数据与磁盘的数据产生了不一致。
在专用服务器上通常将多达80的物理内存分配给缓冲池。参数设置show variables
like innodb_buffer_pool_size; 2).Change Buffer
Change Buffer更改缓冲区针对于非唯一二级索引页在执行DML语句时如果这些数据Page没有在Buffer Pool中不会直接操作磁盘而会将数据变更存在更改缓冲区Change Buffer中在未来数据被读取时再将数据合并恢复到Buffer Pool中再将合并后的数据刷新到磁盘中。Change Buffer的意义是什么呢?
先来看一幅图这个是二级索引的结构图
与聚集索引不同二级索引通常是非唯一的并且以相对随机的顺序插入二级索引。同样删除和更新可能会影响索引树中不相邻的二级索引页如果每一次都操作磁盘会造成大量的磁盘IO。有了ChangeBuffer之后可以在缓冲池中进行合并处理减少磁盘IO。
3).Adaptive Hash Index
自适应hash索引用于优化对Buffer Pool数据的查询。MySQL的innoDB引擎中虽然没有直接支持hash索引但是给提供了一个功能就是这个自适应hash索引。因为前面讲到过hash索引在进行等值匹配时一般性能是要高于B树的因为hash索引一般只需要一次IO即可而B树可能需要几次匹配所以hash索引的效率要高但是hash索引又不适合做范围查询、模糊匹配等。
InnoDB存储引擎会监控对表上各索引页的查询如果观察到在特定的条件下hash索引可以提升速度则建立hash索引称之为自适应hash索引。
自适应哈希索引无需人工干预是系统根据情况自动完成。
参数adaptive_hash_index
4).Log Buffer
Log Buffer日志缓冲区用来保存要写入到磁盘中的log日志数据redo log、undo log默认大小为16MB日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务增加日志缓冲区的大小可以节省磁盘I/O。
参数:
innodb_log_buffer_size缓冲区大小
innodb_flush_log_at_trx_commit日志刷新到磁盘时机取值主要包含以下三个
1:日志在每次事务提交时写入并刷新到磁盘默认值。0:每秒将日志写入并刷新到磁盘一次。2:日志在每次事务提交后写入并每秒刷新到磁盘一次。 6.2.3磁盘结构
接下来再来看看InnoDB体系结构的右边部分也就是磁盘结构 1).System Tablespace
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数据字典、undolog等)
参数innodb_data_file_path 系统表空间默认的文件名叫ibdata1。
2).File-Per-Table Tablespaces
如果开启了innodb_file_per_table开关则每个表的文件表空间包含单个InnoDB表的数据和索引并存储在文件系统上的单个数据文件中。
开关参数innodb_file_per_table该参数默认开启。 那也就是说每创建一个表都会产生一个表空间文件如图 3).General Tablespaces
通用表空间需要通过CREATE TABLESPACE语法创建通用表空间在创建表时可以指定该表空间。
A.创建表空间
CREATE TABLESPACE ts_name ADD DATAFILE file_name ENGINE engine_name; B.创建表时指定表空间
CREATE TABLE xxx ... TABLESPACE ts_name; 4).Undo Tablespaces
撤销表空间MySQL实例在初始化时会自动创建两个默认的undo表空间初始大小16M用于存储undo log日志。
5).Temporary Tablespaces
InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。
6).Doublewrite Buffer Files
双写缓冲区innoDB引擎将数据页从Buffer Pool刷新到磁盘前先将数据页写入双写缓冲区文件中便于系统异常时恢复数据。 7).Redo Log
重做日志是用来实现事务的持久性。该日志文件由两部分组成重做日志缓冲redo logbuffer以及重做日志文件redo log,前者是在内存中后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中,用于在刷新脏页到磁盘时,发生错误时,进行数据恢复使用。
以循环方式写入重做日志文件涉及两个文件 前面介绍了InnoDB的内存结构以及磁盘结构那么内存中所更新的数据又是如何到磁盘中的呢此时就涉及到一组后台线程接下来就来介绍一些InnoDB中涉及到的后台线程。 6.2.4后台线程 在InnoDB的后台线程中分为4类分别是Master Thread、IO Thread、Purge Thread、Page Cleaner Thread。
1).Master Thread
核心后台线程负责调度其他线程还负责将缓冲池中的数据异步刷新到磁盘中,保持数据的一致性还包括脏页的刷新、合并插入缓存、undo页的回收。
2).IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求,这样可以极大地提高数据库的性能而IO Thread主要负责这些IO请求的回调。 可以通过以下的这条指令查看到InnoDB的状态信息其中就包含IO Thread信息。
show engine innodb status\G; 3).Purge Thread
主要用于回收事务已经提交了的undo log在事务提交之后undo log可能不用了就用它来回收。
4).Page Cleaner Thread
协助Master Thread刷新脏页到磁盘的线程它可以减轻Master Thread的工作压力减少阻塞。
6.3事务原理
6.3.1事务基础
1).事务
事务是一组操作的集合它是一个不可分割的工作单位事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。
2).特性
原子性Atomicity事务是不可分割的最小操作单元要么全部成功要么全部失败。一致性Consistency事务完成时必须使所有的数据都保持一致状态。隔离性Isolation数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行。持久性Durability事务一旦提交或回滚它对数据库中的数据的改变就是永久的。
那实际上研究事务的原理就是研究MySQL的InnoDB引擎是如何保证事务的这四大特性的。 而对于这四大特性实际上分为两个部分。其中的原子性、一致性、持久化实际上是由InnoDB中的两份日志来保证的一份是redo log日志一份是undo log日志。而持久性是通过数据库的锁加上MVCC来保证的。 在讲解事务原理的时候主要就是来研究一下redologundolog以及MVCC。
6.3.2redolog
重做日志记录的是事务提交时数据页的物理修改是用来实现事务的持久性。
该日志文件由两部分组成重做日志缓冲redo log buffer以及重做日志文件redo logfile,前者是在内存中后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用。
如果没有redolog可能会存在什么问题的一起来分析一下。
在InnoDB引擎中的内存结构中主要的内存区域就是缓冲池在缓冲池中缓存了很多的数据页。当在一个事务中执行多个增删改的操作时InnoDB引擎会先操作缓冲池中的数据如果缓冲区没有对应的数据会通过后台线程将磁盘中的数据加载出来存放在缓冲区中然后将缓冲池中的数据修改修改后的数据页称为脏页。而脏页则会在一定的时机通过后台线程刷新到磁盘中从而保证缓冲区与磁盘的数据一致。而缓冲区的脏页数据并不是实时刷新的而是一段时间之后将缓冲区的数据刷新到磁盘中假如刷新到磁盘的过程出错了而提示给用户事务提交成功而数据却没有持久化下来这就出现问题了没有保证事务的持久性。 那么如何解决上述的问题呢在InnoDB中提供了一份日志redo log接下来再来分析一下通过redolog如何解决这个问题。 有了redolog之后当对缓冲区的数据进行增删改之后会首先将操作的数据页的变化记录在redolog buffer中。在事务提交时会将redo log buffer中的数据刷新到redo log磁盘文件中。过一段时间之后如果刷新缓冲区的脏页到磁盘时发生错误此时就可以借助于redo log进行数据恢复这样就保证了事务的持久性。而如果脏页成功刷新到磁盘或或者涉及到的数据已经落盘此时redolog就没有作用了就可以删除了所以存在的两个redolog文件是循环写的。
那为什么每一次提交事务要刷新redo log到磁盘中呢而不是直接将buffer pool中的脏页刷新到磁盘呢?
因为在业务操作中操作数据一般都是随机读写磁盘的而不是顺序读写磁盘。而redo log在往磁盘文件中写入数据由于是日志文件所以都是顺序写的。顺序写的效率要远大于随机写。这种先写日志的方式称之为WALWrite-Ahead Logging。
6.3.3undolog
回滚日志用于记录数据被修改前的信息,作用包含两个:提供回滚(保证事务的原子性)和MVCC(多版本并发控制)。
undo log和redo log记录物理日志不一样它是逻辑日志。可以认为当delete一条记录时undolog中会记录一条对应的insert记录反之亦然当update一条记录时它记录一条对应相反的update记录。当执行rollback时就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。
Undo log销毁undolog在事务执行时产生事务提交时并不会立即删除undo log因为这些日志可能还用于MVCC。
Undo log存储undolog采用段的方式进行管理和记录存放在前面介绍的rollback segment回滚段中内部包含1024个undo log segment。
6.4MVCC
6.4.1基本概念
1).当前读
读取的是记录的最新版本读取时还要保证其他并发事务不能修改当前记录会对读取的记录进行加锁。对于日常的操作如select ...lock in share mode(共享锁)select ...for update、update、insert、delete(排他锁)都是一种当前读。
测试 在测试中可以看到即使是在默认的RR隔离级别下事务A中依然可以读取到事务B最新提交的内容因为在查询语句后面加上了lock in share mode共享锁此时是当前读操作。当然当加排他锁的时候也是当前读操作。
2).快照读
简单的select不加锁就是快照读快照读读取的是记录数据的可见版本有可能是历史数据不加锁是非阻塞读。
Read Committed每次select都生成一个快照读。Repeatable Read开启事务后第一个select语句才是快照读的地方。Serializable快照读会退化为当前读。
测试: 在测试中,看到即使事务B提交了数据,事务A中也查询不到。原因就是因为普通的select是快照读而在当前默认的RR隔离级别下开启事务后第一个select语句才是快照读的地方后面执行相同的select语句都是从快照中获取数据可能不是当前的最新数据这样也就保证了可重复读。
3).MVCC
全称Multi-Version Concurrency Control多版本并发控制。指维护一个数据的多个版本使得读写操作没有冲突快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。
接下来再来介绍一下InnoDB引擎的表中涉及到的隐藏字段、undolog以及readview从而来介绍一下MVCC的原理。
6.4.2隐藏字段
6.4.2.1介绍 当创建了上面的这张表在查看表结构的时候就可以显式的看到这三个字段。实际上除了这三个字段以外InnoDB还会自动的给添加三个隐藏字段及其含义分别是 而上述的前两个字段是肯定会添加的是否添加最后一个字段DB_ROW_ID得看当前表有没有主键如果有主键则不会添加该隐藏字段。
6.4.2.2测试
1).查看有主键的表stu
进入服务器中的/var/lib/mysql/itcast/ ,查看stu的表结构信息,通过如下指令:
ibd2sdi stu.ibd
查看到的表结构信息中有一栏columns在其中会看到处理建表时指定的字段以外还有额外的两个字段分别是DB_TRX_ID、DB_ROLL_PTR因为该表有主键所以没有DB_ROW_ID隐藏字段。 2).查看没有主键的表employee
建表语句
create table employee (id int,name varchar(10)
);
此时再通过以下指令来查看表结构及其其中的字段信息
ibd2sdi employee.ibd
查看到的表结构信息中有一栏columns在其中会看到处理建表时指定的字段以外还有额外的三个字段分别是DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID因为employee表是没有指定主键的。 6.4.3undolog
6.4.3.1介绍
回滚日志在insert、update、delete的时候产生的便于数据回滚的日志。当insert的时候产生的undo log日志只在回滚时需要在事务提交后可被立即删除。而update、delete的时候产生的undo log日志不仅在回滚时需要在快照读时也需要不会立即被删除。
6.4.3.2版本链
有一张表原始数据为 DB_TRX_ID :代表最近修改事务ID记录插入这条记录或最后一次修改该记录的事务ID是自增的。 DB_ROLL_PTR由于这条数据是才插入的没有被更新过所以该字段值为null。 然后有四个并发事务同时在访问这张表。
A.第一步
当事务2执行第一条修改语句时会记录undo log日志记录数据变更之前的样子;然后更新记录并且记录本次操作的事务ID回滚指针回滚指针用来指定如果发生回滚回滚到哪一个版本。 B.第二步 当事务3执行第一条修改语句时也会记录undo log日志记录数据变更之前的样子;然后更新记录并且记录本次操作的事务ID回滚指针回滚指针用来指定如果发生回滚回滚到哪一个版本。
C.第三步 当事务4执行第一条修改语句时也会记录undo log日志记录数据变更之前的样子;然后更新记录并且记录本次操作的事务ID回滚指针回滚指针用来指定如果发生回滚回滚到哪一个版本。 最终发现不同事务或相同事务对同一条记录进行修改会导致该记录的undolog生成一条记录版本链表链表的头部是最新的旧记录链表尾部是最早的旧记录。 6.4.4readview
ReadView读视图是快照读SQL执行时MVCC提取数据的依据记录并维护系统当前活跃的事务未提交的id。
ReadView中包含了四个核心字段 而在readview中就规定了版本链数据的访问规则
trx_id代表当前undolog版本链对应事务ID。 不同的隔离级别生成ReadView的时机不同
READ COMMITTED在事务中每一次执行快照读时生成ReadView。 REPEATABLE READ仅在事务中第一次执行快照读时生成ReadView后续复用该ReadView。
6.4.5原理分析
6.4.5.1 RC隔离级别
RC隔离级别下在事务中每一次执行快照读时生成ReadView。
来分析事务5中两次快照读读取数据是如何获取数据的?
在事务5中查询了两次id为30的记录由于隔离级别为Read Committed所以每一次进行快照读都会生成一个ReadView那么两次生成的ReadView如下。 那么这两次快照读在获取数据时就需要根据所生成的ReadView以及ReadView的版本链访问规则到undolog版本链中匹配数据最终决定此次快照读返回的数据。
A.先来看第一次快照读具体的读取过程 在进行匹配时会从undo log的版本链从上到下进行挨个匹配
先匹配这条记录这条记录对应的trx_id为4也就是将4带入右侧的匹配规则中。①不满足②不满足③不满足④也不满足都不满足则继续匹配undo log版本链的下一条。再匹配第二条这条记录对应的trx_id为3也就是将3带入右侧的匹配规则中。①不满足②不满足③不满足④也不满足都不满足则继续匹配undo log版本链的下一条。再匹配第三条这条记录对应的trx_id为2也就是将2带入右侧的匹配规则中。①不满足②满足终止匹配此次快照读返回的数据就是版本链中记录的这条数据。
B.再来看第二次快照读具体的读取过程: 在进行匹配时会从undo log的版本链从上到下进行挨个匹配
先匹配这条记录这条记录对应的trx_id为4也就是将4带入右侧的匹配规则中。①不满足②不满足③不满足④也不满足都不满足则继续匹配undo log版本链的下一条。 再匹配第二条这条记录对应的trx_id为3也就是将3带入右侧的匹配规则中。①不满足②满足。终止匹配此次快照读返回的数据就是版本链中记录的这条数据。
6.4.5.2 RR隔离级别
RR隔离级别下仅在事务中第一次执行快照读时生成ReadView后续复用该ReadView。而RR是可重复读在一个事务中执行两次相同的select语句查询到的结果是一样的。
那MySQL是如何做到可重复读的呢?简单分析一下就知道了 看到在RR隔离级别下只是在事务中第一次快照读时生成ReadView后续都是复用该ReadView那么既然ReadView都一样ReadView的版本链匹配规则也一样那么最终快照读返回的结果也是一样的。
所以呢MVCC的实现原理就是通过InnoDB表的隐藏字段、UndoLog版本链、ReadView来实现的。而MVCC 锁则实现了事务的隔离性。而一致性则是由redolog与undolog保证。 7.MySQL管理
7.1系统数据库
Mysql数据库安装完成后自带了以下四个数据库具体作用如下 7.2常用工具
7.2.1mysql
该mysql不是指mysql服务而是指mysql的客户端工具。
语法mysql [options] [database]选项-u, --username #指定用户名-p, --password[name] #指定密码-h, --hostname #指定服务器IP或域名-P, --portport #指定连接端口-e, --executename #执行SQL语句并退出
-e选项可以在Mysql客户端执行SQL语句而不用连接到MySQL数据库再执行对于一些批处理脚本这种方式尤其方便。
示例
mysql -uroot –p123456 db01 -e select * from stu; 7.2.2mysqladmin
mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。
通过帮助文档查看选项
mysqladmin --help 语法:mysqladmin [options] command ...选项:-u, --username #指定用户名-p, --password[name] #指定密码-h, --hostname #指定服务器IP或域名-P, --portport #指定连接端口 示例
mysqladmin -uroot –p1234 drop test01;mysqladmin -uroot –p1234 version; 7.2.3mysqlbinlog
由于服务器生成的二进制日志文件以二进制格式保存所以如果想要检查这些文本的文本格式就会使用到mysqlbinlog日志管理工具。
语法mysqlbinlog [options] log-files1 log-files2 ...选项-d, --databasename 指定数据库名称只列出指定的数据库相关操作。-o, --offset# 忽略掉日志中的前n行命令。-r, --result-filename 将输出的文本格式日志输出到指定文件。-s, --short-form 显示简单格式省略掉一些信息。--start-datatimedate1 --stop-datetimedate2 指定日期间隔内的所有日志。--start-positionpos1 --stop-positionpos2 指定位置间隔内的所有日志。
示例:
A.查看binlog.000008这个二进制文件中的数据信息 上述查看到的二进制日志文件数据信息量太多了不方便查询。可以加上一个参数-s来显示简单格式。 7.2.4mysqlshow
mysqlshow客户端对象查找工具用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。
语法mysqlshow [options] [db_name [table_name [col_name]]]选项--count 显示数据库及表的统计信息数据库表均可以不指定-i 显示指定数据库或者指定表的状态信息示例#查询test库中每个表中的字段数及行数
mysqlshow -uroot -p2143 test --count#查询test库中book表的详细情况
mysqlshow -uroot -p2143 test book --count
示例
A.查询每个数据库的表的数量及表中记录的数量
mysqlshow -uroot -p1234 --count B.查看数据库db01的统计信息
mysqlshow -uroot -p1234 db01 --count C.查看数据库db01中的course表的信息
mysqlshow -uroot -p1234 db01 course --count D.查看数据库db01中的course表的id字段的信息
mysqlshow -uroot -p1234 db01 course id --count 7.2.5mysqldump
mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表及插入表的SQL语句。
语法mysqldump [options] db_name [tables] mysqldump [options] --database/-B db1[db2 db3 ...]mysqldump [options] --all-databases/-A连接选项-u, --username 指定用户名-p, --password[name] 指定密码-h, --hostname 指定服务器ip或域名-P, --port# 指定连接端口输出选项--add-drop-database 在每个数据库创建语句前加上drop database语句--add-drop-table 在每个表创建语句前加上drop table语句,默认开启;不开启(--skip-add-drop-table)-n, --no-create-db 不包含数据库的创建语句-t, --no-create-info 不包含数据表的创建语句-d --no-data 不包含数据-T, --tabname 自动生成两个文件一个.sql文件创建表结构的语句一个.txt文件数据文件
示例:
A.备份db01数据库
mysqldump -uroot -p1234 db01 db01.sql 可以直接打开db01.sql来查看备份出来的数据到底什么样。
备份出来的数据包含
删除表的语句创建表的语句数据插入语句
如果在数据备份时不需要创建表或者不需要备份数据只需要备份表结构都可以通过对应的参数来实现。
B.备份db01数据库中的表数据不备份表结构(-t)
mysqldump -uroot -p1234 -t db01 db01.sql 打开db02.sql来查看备份的数据只有insert语句没有备份表结构。 C.将db01数据库的表的表结构与数据分开备份(-T)
mysqldump -uroot -p1234 -T /root db01 score 执行上述指令会出错数据不能完成备份原因是因为所指定的数据存放目录/rootMySQL认为是不安全的需要存储在MySQL信任的目录下。那么哪个目录才是MySQL信任的目录呢可以查看一下系统变量secure_file_priv。执行结果如下
上述的两个文件score.sql中记录的就是表结构文件而score.txt就是表数据文件但是需要注意表数据文件并不是记录一条条的insert语句而是按照一定的格式记录表结构中的数据。如下 7.2.6mysqlimport/source
1).mysqlimport
mysqlimport是客户端数据导入工具用来导入mysqldump加-T参数后导出的文本文件。
语法mysqlimport [options] db_name textfile1 [textfile2 ...]示例mysqlimport -uroot -p2143 test /tmp/city.txt 2).source
如果需要导入sql文件,可以使用mysql中的source指令:
语法source /root/xxxxx.sql
