做网站设计需要学什么,网站创建于,扁平化资讯网站模板,如何做平台软件文章目录 1. MySQL简介2. MySQL安装2.1 MySQL8新特性2.2 安装MySQL2.2.1 在docker中创建并启动MySQL容器#xff1a;2.2.2 修改mysql密码2.2.3 重启mysql容器2.2.4 常见问题解决 2.3 字符集问题2.4 远程访问MySQL(用户与权限管理)2.4.0 远程连接问题1、防火墙2、账号不支持远程… 文章目录 1. MySQL简介2. MySQL安装2.1 MySQL8新特性2.2 安装MySQL2.2.1 在docker中创建并启动MySQL容器2.2.2 修改mysql密码2.2.3 重启mysql容器2.2.4 常见问题解决 2.3 字符集问题2.4 远程访问MySQL(用户与权限管理)2.4.0 远程连接问题1、防火墙2、账号不支持远程连接 2.4.1. 创建新用户2.4.2. 修改root账户的host地址2.4.3 测试连接 2.5 SQL大小写规范2.5.1 Windows和Linux的区别2.5.2 Linux下大小写规则设置了解 2.6 导入数据2.7 sql_mode2.7.1 了解sql_mode2.7.2 设置sql_mode1、临时设置2、持久化设置 3. MySQL逻辑架构3.0 逻辑架构详解3.0.1 连接层3.0.2 服务层3.0.3 引擎层3.0.4 存储层 3.1. 查询SQL执行流程3.2. SQL执行计划 5. 索引5.1 索引简介5.1.1. 什么是索引5.1.2. 索引的优劣势 5.2. MySQL的索引结构5.2.1. BTree索引5.2.2. BTree索引1、数据结构2、BTree与B-Tree 的区别3、数据页大小 5.2.3. 聚簇索引与非聚簇索引5.2.4. 回表 5.3. MySQL索引分类5.3.1 索引创建删除5.3.2 联合索引结构 5.4. 索引的使用场景 6. 索引优化6.1. 性能分析explain6.1.1. explain是什么?6.1.2. explain能干什么6.1.3. explain怎么玩6.1.4. 各字段解释6.1.4.1. id(重要)6.1.4.2. select_type6.1.4.3. table6.1.4.4. partitions6.1.4.5. type(重要)6.1.4.6. possible_keys6.1.4.7. key6.1.4.8. key_len6.1.4.9. ref6.1.4.10. rows6.1.4.11. filtered6.1.4.12. extra 6.1.5. 小结 6.2. 数据准备6.2.1. 创建函数6.2.2. 存储过程6.2.3. 调用存储过程6.2.4. 批量删除表索引 6.3. 单表优化6.3.1. 索引优化原则6.3.2. 组合索引原则6.3.3. 小结 6.4. 关联查询优化6.4.1. 关联案例6.4.2. 优化建议6.4.3. 三种实现的比较 6.5. 子查询优化6.6. 排序优化6.6.1. 优化演示6.6.2. 了解filesort算法6.6.2.1. 双路排序6.6.2.2. 单路排序6.6.2.3. 优化策略 6.7. 分组优化6.8. 覆盖索引6.9. 索引无效说明6.10 优化总结 7. sql实战7.1 实现并优化8个SQL7.2 计算并指定索引长度7.3. 时间日期处理(了解)7.4 行转列7.5 group_concat7.6 删除重复行7.7 窗口函数 8. View视图9. Mysql日志和锁9.1 Mysql基础日志9.1.1 日志分类9.1.2 错误日志9.1.3 查询日志9.1.4 慢查询日志9.1.4.1 开启慢查询日志9.1.4.2 查看慢查询日志 9.2 Mysql事务日志9.2.1 事务回顾9.2.2 redo log日志(了解)9.2.2.1 innodb写数据过程9.2.2.2 redo log日志介绍重做日志的缓冲 (**redo log buffer**)重做日志文件 (**redo log file**) 9.2.2.3 redo log整体流程9.2.2.4 redo log的刷盘策略1. 介绍2. 刷盘策略 9.2.3 undo log日志(了解)9.2.3.1 简介9.2.3.2 undo log存储结构9.2.3.3 undo 页1、介绍2、回滚段与事务的关系 9.2.3.4 回滚段中的数据分类1、uncommitted undo information2、committed undo information3、expired undo information 9.2.3.5 undo log类型1、insert undo log2、update undo log 9.2.3.6 undo log的生命周期1、undo log简要生成过程2、详细生成过程3、回滚流程4、undo log的删除 9.3 并发事务问题9.3.1 写写并发问题9.3.2 读写并发问题 9.4 Mysql锁9.4.1 介绍9.4.2 并发事务9.4.2.1 问题9.4.2.2 解决方案9.4.2.3 并发事务分析1、 读-读情况2、 写-写情况3、 读-写情况 9.4.3 锁分类9.4.4 表锁9.4.5 行锁**Record Locks**9.4.6 间隙锁**Gap Locks**9.4.7 临键锁**Next-Key Locks**9.4.7 总结 10. 多版本并发控制10.1 MVCC概述10.2 快照读和当前读10.2.1 快照读10.2.2 当前读 10.3 隐藏字段以及Undo Log版本链10.4 MVCC之ReadView10.4.1 ReadView简介10.4.2 ReadView工作原理10.4.3 ReadView总结10.4.3.1 ReadView的规则10.4.3.2 MVCC整体操作流程 10.5 总结 1. MySQL简介
MySQL是一个关系型数据库管理系统由瑞典MySQL AB公司开发目前属于Oracle公司。
MySQL是一种关联数据库管理系统将数据保存在不同的表中而不是将所有数据放在一个大仓库内这样就增加了速度并提高了灵活性。
Mysql是开源的所以你不需要支付额外的费用。
Mysql是可以定制的采用了GPL协议你可以修改源码来开发自己的Mysql系统。
Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL使用标准的SQL数据语言形式。
Mysql可以允许安装于多个系统上并且支持多种语言。这些编程语言包括C、C、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
MySQL支持大型数据库支持5000万条记录的数据仓库32位系统表文件最大可支持4GB64位系统支持最大的表文件为8TB。 2. MySQL安装
官网下载地址http://dev.mysql.com/downloads/mysql/
2.1 MySQL8新特性
MySQL 8版本在功能上做了显著的改进与增强
1. 更简便的NoSQL支持
从5.6版本开始MySQL就开始支持简单的NoSQL存储功能。MySQL 8对这一功能做了优化以更灵活的方式实现NoSQL功能。
2. 更好的索引 在查询中正确地使用索引可以提高查询的效率。MySQL 8中新增了隐藏索引和降序索引。隐藏索引可以用来测试去掉索引对查询性能的影响。在查询中混合存在多列索引时使用降序索引可以提高查询的性能。
3.更完善的JSON支持 MySQL从5.7开始支持原生JSON数据的存储MySQL 8对这一功能做了优化增加了聚合函数JSON_ARRAYAGG()和JSON_OBJECTAGG()将参数聚合为JSON数组或对象新增了行内操作符 -是列路径运算符 -的增强对JSON排序做了提升并优化了JSON的更新操作。(开发中一般使用mdb操作json项目中使用频繁的比较复杂的多表联查数据如果数据改动不频繁可以直接以json的方式来保存优势查询速度快代码中不需要对象和json之间转换。 如果数据的字段频繁修改同一类数据字段不一定一样表不是特别好设计可以使用使用json存数据)
4.InnoDB的变化 InnoDB是MySQL默认的存储引擎是事务型数据库的首选引擎支持事务安全表ACID支持行锁定和外键。在MySQL 8 版本中InnoDB在自增、索引、加密、死锁、共享锁等方面做了大量的改进和优化并且支持原子数据定义语言DDL提高了数据安全性对事务提供更好的支持。
5.数据字典 在之前的MySQL版本中字典数据都存储在元数据文件和非事务表中。从MySQL 8开始新增了事务数据字典在这个字典里存储着数据库对象信息这些数据字典存储在内部事务表中。
6.字符集支持 MySQL 8中默认的字符集由latin1更改为utf8mb4并首次增加了日语所特定使用的集合utf8mb4_ja_0900_as_cs。
7.窗口函数 MySQL 8开始支持窗口函数。在之前的版本中已存在的大部分聚合函数在MySQL 8中也可以作为窗口函数来使用。 等等…
2.2 安装MySQL
2.2.1 在docker中创建并启动MySQL容器
docker run -d \
-p 3316:3306 \
-v mysql-master-conf:/etc/mysql/conf.d \
-v mysql-master-data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD123456 \
--name mysql-master \
mysql:82.2.2 修改mysql密码
如果使用的mysql客户端是新版本的 不需要修改密码
#进入容器env LANGC.UTF-8 避免容器中显示中文乱码
docker exec -it atguigu-mysql-master env LANGC.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p123456
#修改默认密码插件
ALTER USER root% IDENTIFIED WITH mysql_native_password BY 123456;
# 退出容器2.2.3 重启mysql容器
docker restart atguigu-mysql-master2.2.4 常见问题解决
如果mysql容器连接不上或者报错WARNING: IPv4 forwarding is disabled. Networking will not work.
先排除防火墙、网络问题
然后尝试如下方案
# 修改配置文件
vim /usr/lib/sysctl.d/00-system.conf
# 添加
net.ipv4.ip_forward1
# 保存退出 重启网络服务
systemctl restart network
# 重启mysql容器
docker restart atguigu-mysql-master2.3 字符集问题
查看mysql编码show variables like ‘%character%’发现默认编码是utf8无需修改 mysql8以前的版本默认编码是latin需要手动修改
2.4 远程访问MySQL(用户与权限管理)
2.4.0 远程连接问题
1、防火墙
接下来用宿主机sqlyog或者navicat客户端访问MySQL服务如果等待一会才连接失败一般是防火墙未关闭
# 关闭防火墙或者开放端口号
systemctl start firewalld.service
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl enable firewalld.service
systemctl disable firewalld.service
-- 查看开放的端口号
firewall-cmd --list-all
-- 设置开放的端口号
firewall-cmd --add-servicehttp --permanent
firewall-cmd --add-port3306/tcp --permanent
-- 重启防火墙
firewall-cmd --reload 2、账号不支持远程连接
默认情况下mysql不允许远程连接。只允许localhost连接 执行
select host,user,select_priv, plugin,authentication_string from mysql.user
ql.user;% 表示所有远程通过 TCP方式的连接
IP地址 如 (192.168.1.2,127.0.0.1) 通过制定ip地址进行的TCP方式的连接
机器名通过制定i网络中的机器名进行的TCP方式的连接
::1 IPv6的本地ip地址 等同于IPv4的 127.0.0.1
localhost 本地方式通过命令行方式的连接 比如mysql -u xxx -p 123xxx 方式的连接。
User: 表示用户名同一用户通过不同方式链接的权限是不一样的。
password密码所有密码串通过 password(明文字符串) 生成的密文字符串。加密算法为MYSQLSHA1 不可逆 。
mysql 5.7 的密码保存到 authentication_string 字段中不再使用password 字段。
plugin: 表示生成密码的插件
mysql5.7及之前使用mysql_native_password
mysql8使用caching_sha2_password
select_priv , insert_priv等为该用户所拥有的权限。
解决方案 创建新的用户 修改root用户访问权限
2.4.1. 创建新用户
创建新用户并指定密码create user liuyan identified by liuyan; 修改root用户密码set password password(123456);
修改某个用户的密码update mysql.user set authentication_stringpassword(123456) where userliuyan;
修改用户名update mysql.user set userli4 where userwang5;
删除用户drop user li4 ;
flush privileges; #所有通过user表的修改必须用该命令才能生效。
授权命令 **grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名用户地址 identified by ‘连接口令’;**该权限如果发现没有该用户则会直接新建一个用户。
授予新创建用户权限
grant select,insert,delete,drop on testdb.* to liuyan% ; # 给用户liuyan用本地命令行方式下授予testdb这个库下的所有表的插删改查的权限。
create user zhangsan identified by zhangsan;
grant all privileges on *.* to zhangsan%; #授予通过网络方式登录的的zhansan用户对所有库所有表的全部权限密码设为zhangsan.
flush privileges;#刷新权限扩展
查看当前用户权限show grants;
查看当前用户的全局权限select * from user ;
查看某用户的某个表的权限select * from tables_priv;
收回权限命令
revoke 权限1,权限2,…权限n on 数据库名称.表名称 from 用户名用户地址 ;
收回全库全表的所有权限REVOKE ALL PRIVILEGES ON mysql.* FROM li4localhost;
收回mysql库下的所有表的插删改查权限REVOKE select,insert,update,delete ON mysql.* FROM li4localhost;
用户必须重新登录后才能生效
2.4.2. 修改root账户的host地址
root用户的当前主机配置信息为localhost。只允许本地使用。
修改Host为通配符%
update user set host % where user root;2.4.3 测试连接
连接时出现如下问题错误号码 2058 mysql5.7之前密码加密使用的插件是mysql_native_passwordmysql8修改为caching_sha2_password
**解决方法**一种是升级SQLyog和Navicat新版SQLyog和Navicat不会出现此问题另一种是把MySQL用户登录密码加密规则还原成mysql_native_password。
Linux下 mysql -u root -p 登录你的 mysql 数据库然后 执行这条SQL
ALTER USER root% IDENTIFIED WITH mysql_native_password BY 123456;
flush privileges;重新测试连接
2.5 SQL大小写规范
2.5.1 Windows和Linux的区别
Windows环境
全部不区分大小写
Linux环境
1、数据库名、表名、表的别名、变量名严格区分大小写
2、列名与列的别名不区分大小写。
3、关键字、函数名称不区分大小写
2.5.2 Linux下大小写规则设置了解
在MySQL 8中设置的具体步骤为
1、停止MySQL服务
2、删除数据目录即删除 /var/lib/mysql 目录
3、在MySQL配置文件/etc/my.cnf 的 [mysqld] 中添加 lower_case_table_names1
4、启动MySQL服务注意不建议在开发过程中修改此参数将会丢失所有数据 2.6 导入数据
把课前资料中的《1.数据准备.sql》导入到mysql数据库
2.7 sql_mode
2.7.1 了解sql_mode
宽松模式
执行错误的SQL或插入不规范的数据也会被接受并且不报错。
严格模式
执行错误的SQL或插入不规范的数据会报错。MySQL5.7版本开始就将sql_mode默认值设置为了严格模式。
需求根据部门id查询每个部门的员工平均年龄。
sql_mode是个很容易被忽视的变量默认值是空值mysql5.5在这种设置下是可以允许一些非法操作的比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式所以开发、测试环境的数据库也必须要设置这样在开发测试阶段就可以发现问题。 查看show variables like ‘sql_mode’; sql_mode常用值如下
ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作如果在SELECT中的列没有在GROUP BY中出现那么这个SQL是不合法的因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0而该列又是自增长的那么这个选项就有用了。
STRICT_TRANS_TABLES
在该模式下如果一个值不能插入到一个事务表中则中断当前的操作对非事务表不做限制。提高性能
NO_ZERO_IN_DATE
在严格模式下不允许日期和月份为零
NO_ZERO_DATE
设置该值mysql数据库不允许插入零日期插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中如果数据被零除则产生错误而非警告。如果未给出该模式那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译那么抛出错误。不设置此值时用默认的存储引擎替代并抛出一个异常
PIPES_AS_CONCAT
将||视为字符串的连接操作符而非或运算符这和Oracle数据库是一样的也和字符串的拼接函数Concat相类似
ANSI_QUOTES
启用ANSI_QUOTES后不能用双引号来引用字符串因为它被解释为识别符
ORACLE
设置等同PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER
2.7.2 设置sql_mode
1、临时设置
查询sql_mode的值
SELECT session.sql_mode;
SELECT global.sql_mode;
-- 或者
SHOW VARIABLES LIKE sql_mode; --session级别临时设置sql_mode的值
SET GLOBAL sql_mode mode1,model2,...; --全局要重新启动客户端生效重启MySQL服务后失效
SET SESSION sql_mode mode1,model2,...; --当前会话生效效关闭当前会话就不生效了。可以省略SESSION关键字2、持久化设置
在 /etc/my.cnf 中配置永久生效
[mysqld]
sql-mode mode1,model2,...3. MySQL逻辑架构
和其它数据库相比MySQL有点与众不同它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
下图是MySQL的经典架构图 3.0 逻辑架构详解
3.0.1 连接层
最上层是一些客户端和连接服务包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
3.0.2 服务层
a) Management Serveices Utilities 系统管理和控制工具
b) SQL Interface: SQL接口
接受用户的SQL命令并且返回用户需要查询的结果。比如select from就是调用SQL Interface
c) Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。解析器中SQL 语句进行词法分析、语法分析、语义分析并为其创建语法树。 d) Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
用一个例子就可以理解 select uid,name from user where gender 1;
优化器来决定先投影还是先过滤。
这个select 查询先根据where 语句进行选取而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影而不是将属性全部取出以后再进行过滤
e) Cache和Buffer 查询缓存。
如果查询缓存有命中的查询结果查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存记录缓存key缓存权限缓存等
mysql8禁用记录缓存(查询缓存数据库表一旦修改数据了查询缓存需要删除)
3.0.3 引擎层
存储引擎层存储引擎真正的负责了MySQL中数据的存储和提取服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
3.0.4 存储层
数据存储层主要是将数据存储在运行于裸设备的文件系统之上并完成与存储引擎的交互。
3.1. 查询SQL执行流程 首先mysql的查询流程大致是
mysql客户端通过协议与mysql服务器建连接发送查询语句先检查查询缓存如果命中直接返回结果否则进行语句解析,也就是说在解析查询之前服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可这将大大提高系统的性能。
语法解析器和预处理首先mysql通过关键字将SQL语句进行解析并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询预处理器则根据一些mysql规则进一步检查解析树是否合法。
当解析树被认为是合法的了查询优化器将其转化成执行计划。一条查询可以有很多种执行方式最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
3.2. SQL执行计划
利用show profiles 可以查看sql的执行周期。需要先开启该功能查看参数表中profiling(性能分析工具) sql性能分析工具
show variables like %profiling%;
# 本次会话开启profiling
set profiling1;执行
select * from t_emp;select * from t_dept;显示最近几次查询
show profiles;查看程序的具体执行步骤
show profile cpu,block io for query 查询id show profile的常用查询参数。 ①ALL显示所有的开销信息。 ②BLOCK IO显示块IO开销。 ③CONTEXT SWITCHES上下文切换开销。 ④CPU显示CPU开销信息。 ⑤IPC显示发送和接收开销信息。 ⑥MEMORY显示内存开销信息。 ⑦PAGE FAULTS显示页面错误开销信息。 ⑧SOURCE显示和Source_functionSource_fileSource_line相关的开销信息。 ⑨SWAPS显示交换次数开销信息。**Mysql5.7使用了查询缓存执行计划如下****Mysql8: 取消了查询缓存执行计划如下**Executing hook on transaction: 启用事务checking permissions检查权限 Opening tables打开表
init 初始化
System lock 系统锁
optimizing 优化sql
statistics 统计
preparing 准备执行
executing 执行sql
Sending data 发送数据
Sorting result 排序
end 结束
query end 查询 结束
closing tables 关闭表 去除TMP 表
freeing items 释放物品
cleaning up 清理## 3.3. SQL语法顺序随着Mysql版本的更新换代其优化器也在不断的升级优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。需求查询每个部门年龄高于20岁的人数且高于20岁人数不能少于2人显示人数最多的第一名部门信息下面是经常出现的查询顺序## 3.4. MySQL存储引擎如何用命令查看show engines;也可以通过show variables like %storage_engine%;查看默认的存储引擎。### 3.4.1. 各引擎简介1. InnoDB存储引擎InnoDB是MySQL的默认事务型引擎它被设计用来font colorred处理大量的短期(short-lived)事务/font。除非有非常特别的原因需要使用其他的存储引擎否则font colorred应该优先考虑InnoDB引擎。/font 支持事务、回滚、崩溃恢复等功能支持行级锁定,实现高并发读写支持外键约束,保证数据完整性存储的数据有序,支持B树索引提供存储引擎级别的缓冲池,减少对磁盘IO的访问2. MyISAM存储引擎MyISAM提供了大量的特性包括全文索引、压缩、空间函数(GIS)等但font colorredMyISAM不支持事务和行级锁/font有一个毫无疑问的缺陷就是崩溃后无法安全恢复。3. Archive引擎font colorredArchive档案存储引擎只支持INSERT和SELECT操作/font在MySQL5.1之前不支持索引。Archive表适合日志和数据采集类应用。根据英文的测试结论来看Archive表比MyISAM表要小大约75%比支持事务处理的InnoDB表小大约83%。4. Blackhole引擎font colorredBlackhole引擎没有实现任何存储机制它会丢弃所有插入的数据不做任何保存。/font但服务器会记录Blackhole表的日志所以可以用于复制数据到备库或者简单地记录到日志。但这种应用方式会碰到很多问题因此并不推荐。 5. CSV引擎 font colorredCSV引擎可以将普通的CSV文件作为MySQL的表来处理但不支持索引。/fontCSV引擎可以作为一种数据交换的机制非常有用。CSV存储的数据直接可以在操作系统里用文本编辑器或者excel读取。6. Memory引擎如果需要快速地访问数据并且这些数据不会被修改重启以后丢失也没有关系那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。7. Federated引擎Federated引擎font colorred是访问其他MySQL服务器的一个代理/font尽管该引擎看起来提供了一种很好的跨服务器的灵活性但也经常带来问题因此默认是禁用的。8. PERFORMANCE_SCHEMAPERFORMANCE_SCHEMA是MySQL的一套性能监控与分析系统。它可以监控MySQL服务器上的资源消耗情况。### 3.4.2. MyISAM和InnoDB的区别(重点)| **对比项** | **MyISAM** | **InnoDB** |
| ------------------ | ------------------------------------------------------------ | ------------------------------------------------------------ |
| **外键** | 不支持 | 支持 |
| **事务** | 不支持 | 支持 |
| **行表锁** | 表锁即使操作一条记录也会锁住整个表 font colorred不适合高并发的操作 /font | 行锁,操作时只锁某一行不对其它行有影响 font colorred适合高并发的操作 /font |
| **缓存** | 只缓存索引不缓存真实数据 | 不仅缓存索引还要缓存真实数据对内存要求较高而且内存大小对性能有决定性的影响 |
| **关注点** | 节省资源、消耗少、简单业务 | 并发写、事务、更大资源 |
| **默认安装** | Y | Y |
| **用户表默认使用** | N | Y |
| **自带系统表使用** | N | Y |### 3.4.3. 阿里巴巴、淘宝用哪个Percona 为 MySQL 数据库服务器进行了改进在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具另外有更多的参数和命令来控制服务器行为。该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好,阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。# 4. SQL预热常见七种通用的Join查询练习#1. 所有有门派的人员以及部门信息 A、B两表共有#2. 列出所有用户并显示其部门信息 A的全集#3. 列出有部门的人员以及所有部门B的全集#4. 所有不入门派的人员 A的独有#5. 所有没有员工的部门B的独有#6. 列出所有人员和部门的对照关系(AB全有)#7. 列出所有没部门的人员和没人的部门A的独有B的独有sql
#1. 所有有门派的人员信息 A、B两表共有
SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptIdb.id;
#2. 列出所有用户并显示其机构信息 A的全集
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptIdb.id;
#3. 列出所有门派B的全集
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptIdb.id;
#4. 所有不入门派的人员 A的独有
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptIdb.id WHERE a.deptId IS NULL;
#5. 所有没人入的门派B的独有
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptIdb.id WHERE a.deptId IS NULL;
#6. 列出所有人员和机构的对照关系(AB全有)
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join union(可去除重复数据) right join
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptIdb.id
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptIdb.id WHERE a.deptId IS NULL;
#7. 列出所有没入派的人员和没人入的门派A的独有B的独有
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptIdb.id WHERE a.deptId IS NULL
UNION
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptIdb.id WHERE a.deptId IS NULL;练习 求各个门派对应的掌门人名称 求所有当上掌门人的平均年龄 求所有人物对应的掌门名称
# 求各个门派对应的掌门人名称
select * from t_dept a left join t_emp b on a.CEOb.id;# 求所有当上掌门人的平均年龄
select avg(a.age) from t_emp a inner join t_dept b on a.idb.CEO;# 求所有人物对应的掌门名称
# No1
select c.name,c.id,v.name from t_emp c LEFT JOIN (select a.deptName,a.id,b.name from t_dept a left join t_emp b on a.ceob.id) v on c.deptIdv.id
# No2
select c.name,c.id,v.name from (select a.deptName,a.id,b.name from t_dept a left join t_emp b on a.ceob.id) vLEFT JOIN t_emp c on c.deptIdv.id
# No3
select a.name,a.id,c.name from t_emp a LEFT JOIN t_dept b on a.deptIdb.idLEFT JOIN t_emp c on b.ceoc.id
# No4
select a.name,a.id,(select c.name from t_emp c where c.idb.ceo) from t_emp a LEFT JOIN t_dept b on a.deptIdb.id作业
课前资料中的《2.8个sql.txt》
5. 索引
5.1 索引简介
5.1.1. 什么是索引
MySQL官方对索引的定义为索引Index是帮助MySQL高效获取数据的数据结构。 可以得到索引的本质索引是数据结构。
索引的目的在于提高查询效率可以类比字典
如果要查“mysql”这个单词我们肯定需要定位到m字母然后从上往下找到y字母再找到剩下的sql。
如果没有索引那么你可能需要a----z如果我想找到Java开头的单词呢或者Oracle开头的单词呢
是不是觉得如果没有索引这个事情根本无法完成
你可以简单理解为“排好序的快速查找数据结构”。
在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。下图就是一种可能的索引方式示例 左边是数据表一共有两列七条记录最左边的是数据记录的物理地址 为了加快Col2的查找可以维护一个右边所示的二叉查找树每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针这样就可以运用二叉查找在一定的复杂度内获取到相应数据从而快速的检索出符合条件的记录。
结论
数据本身之外数据库还维护着一个满足特定查找算法的数据结构这些数据结构以某种方式指向数据这样就可以在这些数据结构的基础上实现高级查找算法这种数据结构就是索引。
一般来说索引本身也很大不可能全部存储在内存中因此索引往往以索引文件的形式存储的磁盘上
AVLTree 高度平衡树
增加和删除可能需要通过一次或多次树旋转来重新平衡这个树.
具有以下特点
它是一棵空树或它的左右两个子树的高度差的绝对值不超过1并且左右两个子树都是一棵平衡二叉树。 多叉树
多叉树multiway tree允许每个节点可以有更多的数据项和更多的子节点。2-3树2-3-4树就是多叉树多叉树通过重新组织节点减少节点数量增加分叉减少树的高度能对二叉树进行优化。 5.1.2. 索引的优劣势
优势 类似大学图书馆建书目索引提高数据检索的效率降低数据库的IO成本。 通过索引列对数据进行排序或分组降低数据排序的成本降低了CPU的消耗。
劣势 虽然索引大大提高了查询速度同时却会降低更新表的速度如对表进行INSERT、UPDATE和DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件。每次更新添加了索引列的字段都会调整因为更新所带来的键值变化后的索引信息 实际上索引也是一张表该表保存了主键与索引字段并指向实体表的记录所以索引列也是要占用空间的
5.2. MySQL的索引结构
5.2.1. BTree索引
B-Tree即B树Balance Tree多路平衡搜索树,它的高度远小于平衡二叉树的高度。2-3树是最简单的B树结构。B树的阶节点的最多子节点个数。比如2-3树的阶是32-3-4树的阶是4。 【初始化介绍】 一颗b树浅蓝色的块我们称之为一个磁盘块(innodb默认16kb一个磁盘块)可以看到每个磁盘块包含几个数据项深蓝色所示、指向关键字具体信息的指针红色和指向其他磁盘块的指针黄色所示
上图所表示的 B 树就是一棵 3 阶(叉)的 B 树。假设一个磁盘块可以存储一个节点的数据。我们可以看下磁盘块 2里面的关键字为812它有 3 个孩子 (35)(910) 和 (1315)你能看到 (35) 小于 8(910) 在 8 和 12 之间而 (1315)大于 12三节点的子树的值大小仍然遵守 BST 二叉排序树的规则。
【查找过程】假设我们想要 查找的数据项是 9 那么步骤可以分为以下几步
1、第一次磁盘IO找到根节点磁盘块1读入内存执行二分查找9 小于 17 得到指针 P1
2、第二次磁盘IO按照指针P1找到磁盘块 2读入内存执行二分查找 9 在 8 和 12 之间得到指针 P2
3、第三次磁盘IO按照指针P2找到磁盘块 6读入内存执行二分查找 找到了数据项 9。
B 树在搜索过程中虽然比较的次数并不少但把数据读取出来在内存中比较这个时间要远远小于 I/O操作查找磁盘块的时间。真实的情况是3层的b树可以表示上百万的数据如果上百万的数据查找只需要三次IO性能提高将是巨大的如果没有索引每个数据项都要发生一次IO那么总共需要百万次的IO显然成本非常非常高。
5.2.2. BTree索引
1、数据结构 2、BTree与B-Tree 的区别
1B-树的关键字和记录是放在一起的叶子节点可以看作外部节点不包含任何信息B树的非叶子节点中只有关键字和指向下一个节点的索引记录只放在叶子节点中。树的高度会更矮胖IO次数也会更少。 2在B-树中越靠近根节点的记录查找时间越快只要找到关键字即可确定记录的存在而B树中每个记录的查找时间基本是一样的都需要从根节点走到叶子节点而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B树好而在实际应用中却是B树的性能要好些。因为B树的非叶子节点不存放实际的数据这样每个节点可容纳的元素个数比B-树多树高比B-树小这样带来的好处是减少磁盘访问次数。尽管B树找到一个记录所需的比较次数要比B-树多但是一次磁盘访问的时间相当于成百上千次内存比较的时间因此实际中B树的性能可能还会好些而且B树的叶子节点使用指针连接在一起方便顺序遍历例如查看一个目录下的所有文件一个表中的所有记录等这也是很多数据库和文件系统使用B树的缘故。 思考为什么说B树比B-树更适合实际应用中操作系统的文件索引和数据库索引
B树的磁盘读写代价更低 B树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。B树的查询效率更加稳定 由于非终结点并不是最终指向文件内容的结点而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同导致每一个数据的查询效率相当。
3、数据页大小
查看mysql文件页大小16K
SHOW GLOBAL STATUS LIKE %page_size%为什么mysql页文件默认16K 假设我们一行数据大小为1K那么一页就能存16条数据也就是一个叶子节点能存16条数据再看非叶子节点假设主键ID为bigint类型那么长度为8Byte指针大小在Innodb源码中为6Byte一共就是14Byte那么一页里就可以存储16K/14B1170个(主键指针)
一颗高度为2的B树能存储的数据为:1170*11701,368,900
一颗高度为3的B树能存储的数据为1170*1170*1621902400千万级5.2.3. 聚簇索引与非聚簇索引
聚簇索引 叶子节点将数据存储与索引放到了一块找到索引也就找到了数据。 页内的记录是按照主键的大小顺序排成一个单向链表 。 页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表 。 非叶子节点存储的是记录的主键页号。叶子节点存储的是完整的用户记录。 非聚簇索引 叶子节点将数据存储与索引分开结构索引结构的叶子节点指向了数据的对应行myisam通过key_buffer把索引先缓存到内存中当需要访问数据时通过索引访问数据在内存中直接搜索索引然后通过索引找到磁盘相应数据这也就是为什么索引不在key buffer命中时速度慢的原因 澄清一个概念innodb中非聚簇索引又称辅助索引辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置而是主键值。 InnoDB使用聚簇索引将主键组织到一棵B树中而行数据就储存在叶子节点上若使用where id 14这样的条件查找主键则按照B树的检索算法即可查找到对应的叶节点之后获得行数据。
若对Name列进行条件搜索则需要两个步骤第一步在辅助索引B树中检索Name到达其叶子节点获取对应的主键。第二步使用主键在主索引B树种再执行一次B树检索操作最终到达叶子节点即可获取整行数据。重点在于通过其他键需要建立辅助索引
聚簇索引的好处 由于行数据和叶子节点存储在一起同一页中会有多条行数据访问同一数据页不同行记录时已经把页加载到了Buffer中再次访问的时候会在内存中完成访问不必访问磁盘。这样主键和行数据是一起被载入内存的找到叶子节点就可以立刻将行数据返回了如果按照主键Id来组织数据获得数据更快。 聚簇索引适合用在排序的场合非聚簇索引不适合 取出一定范围数据的时候使用聚簇索引 二级索引需要两次索引查找而不是一次才能取到数据因为存储引擎第一次需要通过二级索引找到索引的叶子节点从而找到数据的主键然后在聚簇索引中用主键再次查找索引再找到数据
聚簇索引的限制
对于mysql数据库目前只有innodb数据引擎支持聚簇索引而Myisam并不支持聚簇索引。由于数据物理存储排序方式只能有一种所以每个Mysql的表只能有一个聚簇索引。 一般情况下就是该表的主键。如果没有primary key,会以(not null unique key)非空的唯一索引保存数据内部自己生成一个字段保存数据 为了充分利用聚簇索引的聚簇的特性所以innodb表的主键列尽量选用有序非空的字段而不建议用无序的id比如uuid这种。
5.2.4. 回表
通过非聚簇索引查找到主键值之后仍然需要到聚簇索引中再查询一遍这个过程称为回表
**问题**为什么我们还需要一次回表操作呢直接把完整的用户记录放到叶子节点不OK吗
回答 如果把完整的用户记录放到叶子节点是可以不用回表。但是太占地方了相当于每建立一棵B树都需要把所有的用户记录再都拷贝一遍这就有点太浪费存储空间了。
5.3. MySQL索引分类
主键索引设定为主键后数据库会自动建立索引innodb为聚簇索引单值索引即一个索引只包含单个列一个表可以有多个单列索引唯一索引索引列的值必须唯一但允许有空值复合索引即一个索引包含多个列
5.3.1 索引创建删除
CREATE TABLE customer (id INT (10) UNSIGNED AUTO_INCREMENT,customer_no VARCHAR (200),customer_name VARCHAR (200),PRIMARY KEY (id), # 主键索引KEY idx_name (customer_name), # 单值索引UNIQUE KEY uk_name (customer_name), # 唯一索引KEY idx_no_name (customer_no, customer_name) # 复合索引
);单独建索引
# 使用CREATE语句CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
CREATE INDEX idx_customer_name ON customer(customer_name); # 单值索引
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); # 唯一索引
CREATE INDEX idx_no_name ON customer(customer_no,customer_name); # 复合索引
# 使用ALTER命令
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
# 该语句添加一个主键这意味着索引值必须是唯一的且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
# 这条语句创建索引的值必须是唯一的除了NULL外NULL可能会出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
# 添加普通索引索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
# 该语句指定了索引为 FULLTEXT 用于全文索引。删除索引
ALTER TABLE customer drop PRIMARY KEY; # 删除主键索引DROP INDEX idx_customer_name on customer; # 删除单值、唯一、复合索引查看索引
SHOW INDEX FROM table_name;修改主键索引必须先删除掉(drop)原索引再新建(add)索引
5.3.2 联合索引结构
基于多个字段创建的索引就是联合索引也称为复合索引比如我们创建索引create index idx on table(a,b,c) 我们称在字段a,b,c上创建了一个联合索引。同时以这三个列的大小作为排序规则。
记录先按照a列排序a列值相同时使用b列排序b列值相同时使用c列排序
然后将排好序的abc三列的值组织到非聚簇索引索引结构中。
联合索引结构 5.4. 索引的使用场景
哪些情况需要创建索引
主键自动建立唯一索引频繁作为查询条件的字段应该创建索引查询中与其它表关联的字段外键关系建立索引单键/组合索引的选择问题 组合索引性价比更高查询中排序的字段排序字段若通过索引去访问将大大提高排序速度查询中统计或者分组字段
哪些情况不要创建索引 表记录太少 经常增删改的表或者字段。 Why提高了查询速度同时却会降低更新表的速度如对表进行INSERT、UPDATE和DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件 Where条件里用不到的字段不创建索引 过滤性不好的、有大量重复数据的列不适合建索引
6. 索引优化
6.1. 性能分析explain
mysql5.6以后优化器做了很多改进执行时会自动进行大量的优化。
6.1.1. explain是什么?
模拟优化器查看执行计划
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
6.1.2. explain能干什么 表的读取顺序 那些索引可以使用 数据读取操作的操作类型 那些索引被实际使用 表之间的引用 每张表有多少行被物理查询
6.1.3. explain怎么玩
explain SQL语句
官方文档https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information
数据准备
CREATE TABLE t1(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));# 以下新增sql多执行几次以便演示
INSERT INTO t1(content) VALUES(CONCAT(t1_,FLOOR(1RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT(t2_,FLOOR(1RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT(t3_,FLOOR(1RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT(t4_,FLOOR(1RAND()*1000)));6.1.4. 各字段解释
6.1.4.1. id(重要)
select查询的序列号表示查询中执行select子句或操作表的顺序
三种情况
1、id相同执行顺序由上至下。
explain select * from t1, t2, t3 where t1.idt2.id and t2.idt3.id; 2、id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行
EXPLAIN SELECT *
FROM t1 WHERE t1.content (SELECT t2.contentFROM t2 WHERE t2.content(SELECT t3.contentFROM t3WHERE t3.content)
); 3、id既有相同又有不同 4、id为NULL最后执行。
EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;关注点每个id号码表示一趟独立的查询。一个sql 的查询趟数越少越好。
6.1.4.2. select_type
查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询
**SIMPLE**简单查询查询中不包含子查询或者UNION。
EXPLAIN SELECT * FROM t1;**PRIMARY**主查询查询中若包含子查询则最外层查询被标记为PRIMARY。
**SUBQUERY**子查询在SELECT或WHERE列表中包含了子查询。
EXPLAIN SELECT * FROM t3 WHERE id ( SELECT id FROM t2 WHERE content a);DEPENDENT SUBQUREY如果包含了子查询并且查询语句不能被优化器转换为连接查询并且子查询是相关子查询子查询基于外部数据列则子查询就是DEPENDENT SUBQUREY。
EXPLAIN SELECT * FROM t3 WHERE id ( SELECT id FROM t2 WHERE content t3.content);UNCACHEABLE SUBQUREY 表示一个子查询subquery被标记为无法缓存。在某些数据库管理系统中查询优化器会尝试将查询结果缓存起来以提高性能但对于被标记为UNCACHEABLE的子查询查询优化器将不会缓存其结果。
EXPLAIN SELECT * FROM t3 WHERE id ( SELECT id FROM t2 WHERE content character_set_server);UNION 对于包含UNION或者UNION ALL的查询语句除了最左边的查询是PRIMARY其余的查询都是UNION。
UNION RESULT UNION会对查询结果进行查询去重MYSQL会使用临时表来完成UNION查询的去重工作针对这个临时表的查询就
是UNION RESULT。
EXPLAIN
SELECT * FROM t3 WHERE id 1
UNION
SELECT * FROM t2 WHERE id 1;DEPENDENT UNION 子查询中的UNION或者UNION ALL除了最左边的查询是DEPENDENT SUBQUREY其余的查询都DEPENDENT UNION。 EXPLAIN SELECT * FROM t1 WHERE content IN(SELECT content FROM t2 UNION SELECT content FROM t3);DERIVED 在包含**派生表子查询在from子句中**的查询中MySQL会递归执行这些子查询把结果放在临时表里。
EXPLAIN SELECT * FROM (SELECT content, COUNT(*) AS c FROM t1 GROUP BY content
) AS derived_t1 WHERE c 1;6.1.4.3. table
显示这一行的数据是关于哪张表的
6.1.4.4. partitions
代表分区表中的命中情况非分区表该项为null
6.1.4.5. type(重要) type显示的是访问类型是较为重要的一个指标结果值从最好到最坏依次是
nullsystem const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL
常见system const eq_ref ref range index ALL
一般来说得保证查询至少达到range级别最好能达到ref。
allFull Table Scan将遍历全表以找到匹配的行。
explain select * from t1index出现index是sql使用了索引但是没用通过索引进行过滤一般是使用了覆盖索引或者是利用索引进行了排序分组。
explain select id from t1range只检索给定范围的行使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、、、in等的查询。这种范围扫描索引扫描比全表扫描要好因为它只需要开始于索引的某一点而结束语另一点不用扫描全部索引.
select * from t1 where t1.id10ref: 表示使用了非唯一索引进行的等值比较可能返回多个匹配的行。
-- t4表 content需要提前创建索引
EXPLAIN SELECT * FROM t4 WHERE content a;eq_ref唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
select * from t1,t2 where t1.idt2.id
-- 如果连接字段使用的不是唯一索引是什么效果system表只有一行记录等于系统表这是const类型的特列平时不会出现这个也可以忽略不计。
CREATE TABLE t(i int) EngineMyISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;const表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据所以很快。如将主键置于where列表中MySQL就能将该查询转换为一个常量。
select * from t1 where t1.id1null: MySQL不访问任何表或索引直接返回结果
select * from t1 where 12;index_merge: 在查询过程中需要多个索引组合使用通常出现在有 or 的关键字的sql中。
EXPLAIN SELECT * FROM t3 WHERE t3.content IS NULL OR t3.id10;ref_or_null: 对于某个字段既需要关联条件也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
EXPLAIN SELECT * FROM t3 WHERE t3.content IS NULL OR t3.contentaaaa;6.1.4.6. possible_keys
显示可能应用在这张表中的索引一个或多个。 查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用
6.1.4.7. key
实际使用的索引。如果为NULL则没有使用索引。
查询中若使用了覆盖索引则该索引和查询的select字段重叠 6.1.4.8. key_len
表示索引中使用的字节数可通过该列计算查询中使用的索引的长度。
key_len字段能够帮你检查是否充分的利用上了索引。 如何计算 1 、先看索引上字段的类型长度比如 int4 ; varchar(20) 20 ; char(20) 20
2 、如果是varchar或者char这种字符串字段视字符集要乘不同的值比如utf8mb3要乘 3(MySQL5.7)如果是utf8mb4要乘4,GBK要乘2
3 、varchar这种动态字符串要加2个字节
4、 允许为空的字段要加1个字节
索引字段最好不要为NULL因为NULL让统计更加复杂并且需要额外一个字节的存储空间。
6.1.4.9. ref
显示索引的哪一列被使用了如果可能的话是一个常数。哪些列或常量被用于查找索引列上的值
6.1.4.10. rows
rows列显示MySQL认为它执行查询时必须检查的行数。越少越好
6.1.4.11. filtered
最后查询出来的数据占所有服务器端server检查行数rows的百分比。值越大越好。
6.1.4.12. extra
包含不适合在其他列中显示但十分重要的额外信息通过这些额外信息来理解MySQL到底将如何执行当前的查询语句。MySQL提供的额外信息有好几十个这里只挑比较重要的介绍。
Using filesort说明mysql会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”
EXPLAIN SELECT * FROM t1 ORDER BY content;这类SQL语句性能极差需要进行优化。
在一个非索引列上进行了order by就会触发filesort常见的优化方案是在order by的列上添加索引避免每次查询都全量排序(只查询索引列的值)。
Using temporary使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
EXPLAIN SELECT * FROM t1 GROUP BY content;group by和order by同时存在且作用于不同的字段时就会建立临时表以便计算出最终的结果集。
USING index利用索引进行了排序或分组。表示相应的select操作中使用了覆盖索引(Covering Index)避免访问了表的数据行效率不错
EXPLAIN select * from t_emp where age30 ORDER BY name如果同时出现using where表明索引被用来执行索引键值的查找; 如果没有同时出现using where表明索引只是用来读取数据而非利用索引执行查找。
Using where表明使用了where过滤
using join buffer使用了连接缓存非主键关联(mysql8Using join buffer (hash join) 速度要好于 mysql5.7Using join buffer (Block Nested Loop) ) impossible wherewhere子句的值总是false不能用来获取任何元组。EXPLAIN select * from t_emp where false;
select tables optimized away在没有GROUPBY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作不必等到执行阶段再进行计算查询执行计划生成的阶段即完成优化。
在innodb中 Using index condition: Index Condition Pushdown Optimization 索引下推优化
-- 如果存在删除t_emp的deptId索引
ALTER TABLE t_emp DROP INDEX idx_dept_id;
-- 查询结果如下图
EXPLAIN SELECT * FROM t_emp WHERE deptId IN (1,2) AND deptId2;-- 添加索引查询测试
ALTER TABLE t_emp ADD INDEX idx_dept_id(deptId);
EXPLAIN SELECT * FROM t_emp WHERE deptId IN (1,2) AND deptId2;索引下推就是指在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数来提高查询效率。 1、如果没有索引下推ICP那么MySQL在存储引擎层找到满足deptId1或2的二级索引记录。使用主键值进行回表然后再使用条件deptId2对数据进行过滤最后返回完整的记录给server层。 2、如果使用了索引下推ICP那么MySQL在存储引擎层找到满足deptId1或2二级索引记录。先不执行回表而是判断后面的条件中使用了deptId字段索引的条件(deptId2)是否成立。如果这些条件不成立直接过滤如果条件成立则执行回表操作返回完整的记录给server层。 3、deptId字段条件只有一个时也会执行索引下推判断条件是否成立(mysql底层代码中的冗余判断) 6.1.5. 小结
表的读取顺序id
id趟数越少越好id相同执行顺序由上至下id不同 大的先执行
查询方式select_type
那些索引可以使用possible_keys
数据读取操作的操作类型type
range index all
那些索引被实际使用key
创建的索引是否能够被实际应用
使用索引的长度key_len
命中的索引匹配的长度(用来判断索引是否被完全利用)
计算索引长度
utf-8
varchar(len) 使用len*32 (如果字段可以为null再1)
char(len) 使用len*3 (如果字段可以为null再1)
int(len) 4 (如果字段可以为null再1)
表之间的引用table
每张表有多少行被物理查询rows
行数越少越好(多表联查时 被驱动表的rows如果使用索引了一般非常小)
额外优化信息extra
using join buffer(多表联查)、using filesort(排序)和 using temporary(分组) 需要考虑优化
其他情况性能都可以无需优化
6.2. 数据准备
在做优化之前要准备大量数据。接下来创建两张表并往员工表里插入50W数据部门表中插入1W条数据。
建表sql
CREATE TABLE dept (id INT(11) NOT NULL AUTO_INCREMENT,deptName VARCHAR(30) DEFAULT NULL,address VARCHAR(40) DEFAULT NULL,ceo INT NULL ,PRIMARY KEY (id)
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;CREATE TABLE emp (id INT(11) NOT NULL AUTO_INCREMENT,empno INT NOT NULL ,name VARCHAR(20) DEFAULT NULL,age INT(3) DEFAULT NULL,deptId INT(11) DEFAULT NULL,PRIMARY KEY (id)#CONSTRAINT fk_dept_id FOREIGN KEY (deptId) REFERENCES t_dept (id)
) ENGINEINNODB AUTO_INCREMENT1 DEFAULT CHARSETutf8;怎么快速插入50w条数据呢 存储过程
怎么保证插入的数据不重复函数
以部门表分析
id自增长
deptName随机字符串允许重复
address随机字符串允许重复
CEO1-50w之间的任意数字
以员工表分析
id自增长
empno可以使用随机数字或者从1开始的自增数字不允许重复
name随机生成允许姓名重复
age区间随机数
deptId1-1w之间随机数
总结需要产生随机字符串和区间随机数的函数。
6.2.1. 创建函数
set global log_bin_trust_function_creators1;
# 随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN DECLARE chars_str VARCHAR(100) DEFAULT abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ;DECLARE return_str VARCHAR(255) DEFAULT ;DECLARE i INT DEFAULT 0;WHILE i n DO SET return_str CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1)); SET i i 1;END WHILE;RETURN return_str;
END $$#用于随机产生区间数字
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN DECLARE i INT DEFAULT 0; SET i FLOOR(from_num RAND()*(to_num -from_num1));
RETURN i;
END$$#假如要删除
#drop function rand_string;
#drop function rand_num;6.2.2. 存储过程
# 插入员工存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp(START INT, max_num INT)
BEGIN DECLARE i INT DEFAULT 0; #set autocommit 0 把autocommit设置成0 SET autocommit 0; REPEAT SET i i 1; INSERT INTO emp (empno, NAME, age, deptid ) VALUES ((STARTi) ,rand_string(6), rand_num(30,50), rand_num(1,10000)); UNTIL i max_num END REPEAT; COMMIT;
END$$#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;#往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE insert_dept(max_num INT)
BEGIN DECLARE i INT DEFAULT 0; SET autocommit 0; REPEAT SET i i 1; INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000)); UNTIL i max_num END REPEAT; COMMIT;
END$$#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;6.2.3. 调用存储过程
#执行存储过程往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000); #执行存储过程往emp表添加50万条数据
DELIMITER ;
CALL insert_emp(100000,500000); 6.2.4. 批量删除表索引
批量删除某个表上的所有索引
DELIMITER $$
CREATE PROCEDURE proc_drop_index(dbname VARCHAR(200),tablename VARCHAR(200))
BEGINDECLARE done INT DEFAULT 0;DECLARE ct INT DEFAULT 0;DECLARE _index VARCHAR(200) DEFAULT ;DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schemadbname AND table_nametablename AND seq_in_index1 AND index_name PRIMARY ;DECLARE CONTINUE HANDLER FOR NOT FOUND set done2 ; OPEN _cur;FETCH _cur INTO _index;WHILE _index DO SET str CONCAT(drop index ,_index, on ,tablename ); PREPARE sql_str FROM str ;EXECUTE sql_str;DEALLOCATE PREPARE sql_str;SET _index; FETCH _cur INTO _index; END WHILE;CLOSE _cur;
END$$执行批量删除
CALL proc_drop_index(dbname,tablename); # 库名称和表名称6.3. 单表优化
6.3.1. 索引优化原则 不在索引列上做任何操作计算、函数、(自动or手动)类型转换会导致索引失效而转向全表扫描 like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作 mysql 在使用**不等于(!或者)**的时候无法使用索引会导致全表扫描 is not null 也无法使用索引但是is null是可以使用索引的 字符串不加单引号索引失效 以下两个sql那个写法更好 案例 #1.1 查找姓名以abc开头的员工信息 #1.2 查找姓名含有abc的员工信息 #1.3 查找年龄不等于25的员工 #1.4 查找姓名不为空的员工信息 #1.5 查找姓名等于123的员工信息
# 创建索引
create index idx_emp_age on emp(age);
create index idx_emp_name on emp(name);EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE abc%;EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3)abc;sql访问类型range ALL使用索引idx_emp_name NULL 使用索引长度63 NULL; 扫描行数25 498951 第一个sql用时0.00s 第二个sql用时0.37s 由此可见第一个sql优于第二个sql第一个走了索引第二个走了全表扫描。
把第一个sql的like查询条件改成‘%abc%’会怎样呢 可以发现改成’%abc%之后第一个sql失去了索引优势走了全表扫描。
再来看这两个sql不等于(!或者)
EXPLAIN SELECT * FROM emp WHERE emp.age30;
EXPLAIN SELECT * FROM emp WHERE emp.age!30;is not null和is null 字符串加引号 6.3.2. 组合索引原则
全值匹配我最爱符合最左原则不跳过索引中的列。如果where条件中是OR关系加索引不起作用存储引擎不能使用索引中范围条件右边的列
首先删除之前创建的索引
CALL proc_drop_index(mydb,emp);全值匹配我最爱
SELECT SQL_NO_CACHE * FROM emp WHERE age30 and deptId1 and nameabc;
create index idx_age_deptId_name on emp(age, deptId, name);
SELECT SQL_NO_CACHE * FROM emp WHERE age30 and deptId1 and nameabc;最左匹配原则 OR关联 范围条件右边的列 6.3.3. 小结
一般性建议
对于单键索引尽量选择针对当前query过滤性更好的索引在选择组合索引的时候当前Query中过滤性最好的字段在索引字段顺序中位置越靠前越好。在选择组合索引的时候尽量选择可以能够包含当前query中的where字句中更多字段的索引在选择组合索引的时候如果某个字段可能出现范围查询时尽量把这个字段放在索引次序的最后面书写sql语句时尽量避免造成索引失效的情况
假设index(a,b,c) 重要
Where语句索引是否被使用where a 3Y,使用到awhere a 3 and b 5Y,使用到abwhere a 3 and b 5 and c 4Y,使用到a,b,cwhere b 3 或者 where b 3 and c 4 或者 where c 4Nwhere a 3 and c 5使用到a 但是c不可以b中间断了where a 3 and b 4 and c 5使用到a和b c不能用在范围之后b断了where a is null and b is not nullis null 支持索引 is not null 类似范围查询ab能使用b右边的会失效where a 3不能使用索引where abs(a) 3不能使用 索引where a 3 and b like ‘kk%’ and c 4Y,使用到a,b,cwhere a 3 and b like ‘%kk’ and c 4Y,只用到awhere a 3 and b like ‘%kk%’ and c 4Y,只用到awhere a 3 and b like ‘k%kk%’ and c 4Y,使用到a,b,c
6.4. 关联查询优化
接下来再次创建两张表并分别导入20条数据
CREATE TABLE IF NOT EXISTS class (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,card INT(10) UNSIGNED NOT NULL,PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS book (bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,card INT(10) UNSIGNED NOT NULL,PRIMARY KEY (bookid)
);INSERT INTO class(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 (RAND() * 20)));INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 (RAND() * 20)));6.4.1. 关联案例
explain分析一下两个sql
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card book.card;EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card book.card;EXPLAIN SELECT * FROM class INNER JOIN book ON class.card book.card;给book.card创建索引
ALTER TABLE book ADD INDEX idx_card ( card);然后explain分析 删除旧索引添加新索引
# 删除旧索引 新建 第3次explain
drop index idx_card on book;
ALTER TABLE class ADD INDEX index_class_card (card);再次explain分析 同时给两张表的card字段添加索引class(card)索引已有index_class_card只需给bookcard添加索引
ALTER TABLE book ADD INDEX idx_card ( card);最后explain分析 6.4.2. 优化建议
保证被驱动表的join字段已经创建了索引left/right join 时为被驱动表连接字段创建索引。inner join 时mysql会自己帮你把小结果集的表选为驱动表,对被驱动表连接字段创建索引。(5.6已经优化掉了5.5需要手动编写)子查询尽量不要放在被驱动表有可能使用不到索引。能够直接多表关联的尽量直接关联不用子查询。(减少查询的趟数)
6.4.3. 三种实现的比较
6.5. 子查询优化
尽量不要使用not in 或者 not exists
尽量不要使用子查询
6.6. 排序优化
以下三种情况不走索引
无过滤不索引顺序错不索引方向反不索引
create index idx_age_deptid_name on emp (age,deptid,name)# 以下 是否能使用到索引能否去掉using filesort
explain select * from emp order by age,deptid;
-- 强制使用索引
EXPLAIN SELECT * FROM emp FORCE INDEX(idx_age_deptid_name) ORDER BY age,deptid;explain select * from emp order by age,deptid limit 10; # 无过滤 不索引 观察extra的值explain select * from emp where age45 order by deptid;explain select * from emp where age45 order by deptid,name; explain select * from emp where age45 order by deptid,empno;explain select * from emp where age45 order by name,deptid;explain select * from emp where deptid45 order by age;# 顺序错不索引
explain select * from emp where age45 order by deptid desc, name desc ;
# 方向反 不索引
explain select * from emp where age45 order by deptid asc, name desc ;6.6.1. 优化演示
ORDER BY子句尽量使用Index方式排序,避免使用FileSort方式排序
执行案例前先清除emp上的索引只留主键
# 查询 年龄为30岁的且员工编号小于101000的用户按用户名称排序SELECT SQL_NO_CACHE * FROM emp WHERE age 30 AND empno 101000 ORDER BY NAME;结论很显然执行时间为0.477stype 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
优化思路 尽量让where的过滤条件和排序使用上索引。
现在过滤条件使用了两个字段ageempno排序使用了name。
我们建一个三个字段的组合索引可否
CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);再次explain测试 我们发现using filesort 依然存在所以name 并没有用到索引。
原因是因为empno是一个范围过滤所以索引后面的字段不会再使用索引了。
所以我们建一个3值索引是没有意义的 那么我们先删掉这个索引DROP INDEX idx_age_empno_name ON emp
为了去掉filesort我们可以把索引建成
CREATE INDEX idx_age_name ON emp(age,NAME);也就是说empno 和name这个两个字段只能二选其一。 这样我们优化掉了 using filesort。 执行一下sql 速度果然提高了4倍。
假如选择创建age和empno会速度会怎样呢自己试试有惊喜 结果竟然有 filesort的 sql 运行速度超过了已经优化掉 filesort的 sql 而且快了好多倍。何故
原因是所有的排序都是在条件过滤之后才执行的所以如果条件过滤了大部分数据的话几百几千条数据进行排序其实并不是很消耗性能即使索引优化了排序但实际提升性能很有限。 相对的 empno101000 这个条件如果没有用到索引的话要对几万条的数据进行扫描这是非常消耗性能的所以索引放在这个字段上性价比最高是最优选择。
结论 当范围条件和group by 或者 order by 的字段出现二选一时 优先观察条件字段的过滤数量如果过滤的数据足够多而需要排序的数据并不多时优先把索引放在范围字段上。反之亦然。
6.6.2. 了解filesort算法
如果不在索引列上filesort有两种算法mysql就要启动双路排序和单路排序
6.6.2.1. 双路排序
MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘最终得到数据读取行指针和orderby列对他们进行排序然后扫描已经排序好的列表按照列表中的值重新从列表中读取对应的数据输出。
也就是从磁盘取排序字段再buffer进行排序再从磁盘取其他字段。
取一批数据要对磁盘进行了两次扫描众所周知I\O是很耗时的所以在mysql4.1之后出现了第二种改进的算法就是单路排序。
6.6.2.2. 单路排序
从磁盘读取查询需要的所有列按照order by列在buffer对它们进行排序然后扫描排序后的列表进行输出它的效率更快一些避免了第二次读取数据。并且把随机IO变成了顺序IO但是它会使用更多的空间因为它把每一行都保存在内存中了。
结论及引申出的问题由于单路是后出的总体而言好过双路。
但是用单路有问题在sort_buffer中比双路排序要多占用很多空间因为单路排序是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量导致每次只能取sort_buffer容量大小的数据进行排序创建tmp文件多路合并排完再取取sort_buffer容量大小再排……从而多次I/O。
本来想省一次I/O操作反而导致了大量的I/O操作反而得不偿失。
6.6.2.3. 优化策略
Order by时select * 是一个大忌只Query需要的字段 这点非常重要。在这里的影响是
当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时会用改进后的算法——单路排序 否则用老算法——多路排序。两种算法的数据都有可能超出sort_buffer的容量超出之后会创建tmp文件进行合并排序导致多次I/O但是用单路排序算法的风险会更大一些所以要提高sort_buffer_size。
尝试提高 sort_buffer_size
不管用哪种算法提高这个参数都会提高效率当然要根据系统的能力去提高因为这个参数是针对每个进程的 1M-8M之间调整
尝试提高 max_length_for_sort_data
提高这个参数 会增加用改进算法的概率。但是如果设的太高数据总容量超出sort_buffer_size的概率就增大明显症状是高的磁盘I/O活动和低的处理器使用率。1024-8192之间调整
6.7. 分组优化
group by 使用索引的原则几乎跟order by一致 唯一区别是groupby 即使没有过滤条件用到索引也可以直接使用索引。 group by 先排序再分组遵照索引建的最佳左前缀法则 当无法使用索引列增大max_length_for_sort_data和sort_buffer_size参数的设置 where高于having, 能写在where限定的条件就不要写在having中了
只要对分组列创建索引即可
6.8. 覆盖索引
最后使用索引的手段覆盖索引
什么是覆盖索引 简单说就是select 到 from 之间查询的列 使用的索引列主键 explain select * from emp where name like %abc;使用覆盖索引后 理解方式一索引是高效找到行的一个方法但是一般数据库也能使用索引找到一个列的数据因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据当能通过读取索引就可以得到想要的数据那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引
理解方式二非聚集复合索引的一种形式它包括在查询里的Select、Join和Where子句用到的所有列即建索引的字段正好是覆盖查询条件中所涉及的字段也即索引包含了查询正在查找的数据。
6.9. 索引无效说明
创建索引后用不用索引最终是优化器说了算。优化器会基于开销选择索引怎么开销小就怎么来。不是基于规则也不是基于语义。
另外SQL语句是否使用索引和数据库的版本、数据量、数据选择度查询中选择的列数运行环境都有关系。
所有创建索引后需要结合explain进行分析索引是否有效
6.10 优化总结
MySQL的性能调优主要集中在以下几个方面
1、查询优化
通过分析和优化查询语句包括使用合适的索引、避免全表扫描、优化JOIN操作等以提高查询性能。
2、索引优化
合理设计和使用索引包括选择合适的列作为索引、创建复合索引、删除不必要的索引等以加快数据检索速度。
3、配置优化
调整MySQL的配置参数如缓冲区大小、并发连接数、线程池大小等以适应不同的工作负载和硬件环境。
4、内存管理
合理配置MySQL的内存使用包括设置合适的缓冲池大小、排序缓冲区大小、临时表空间大小等以提高内存利用率和减少磁盘IO。
5、存储引擎选择
根据应用需求选择合适的存储引擎如InnoDB、MyISAM等并针对不同存储引擎进行相应的优化。
6、数据库设计优化
合理设计数据库表结构、字段类型和关系以减少数据冗余和提高查询效率。
以上只是MySQL性能调优的一些常见方面具体的调优策略和方法需要根据具体情况进行分析和优化。通过综合考虑硬件、操作系统、网络和应用程序等因素可以全面提升MySQL数据库的性能和可伸缩性。
7. sql实战
7.1 实现并优化8个SQL
#删除两个表的所有索引
#1、列出自己的掌门比自己年龄小的人员
EXPLAIN SELECT * FROM t_emp c INNER JOIN (SELECT a.id, a.deptName, b.age FROM t_dept a INNER JOIN t_emp b ON a.CEOb.id) v ON c.deptIdv.id
WHERE c.age v.age;EXPLAIN SELECT SQL_NO_CACHE * FROM dept a INNER JOIN t_emp b ON a.CEOb.id INNER JOIN t_emp c ON c.deptIda.id
WHERE c.age b.age;create index idx_deptId on t_emp(deptId);#2、列出所有年龄低于自己门派平均年龄的人员
EXPLAIN select SQL_NO_CACHE * from emp c LEFT JOIN (select b.deptId,AVG(b.age) avgage from emp b GROUP BY b.deptId) v on c.deptIdv.deptId
where c.age v.avgage;EXPLAIN select * from (select b.deptId, AVG(b.age) avgage from emp b GROUP BY b.deptId) v LEFT JOIN emp c on c.deptIdv.deptId
where c.age v.avgage;CREATE INDEX idx_deptId on emp(deptId);#3、列出至少有2个年龄大于40岁的成员的门派explain select SQL_NO_CACHE a.deptId, b.deptName, count(*) cou
from t_emp a INNER JOIN t_dept b on a.deptIdb.id
where a.age40
GROUP BY a.deptId
HAVING cou 2;
# 使用数据量少的表的字段分组
explain select SQL_NO_CACHE b.id, b.deptName, count(*) cou
from t_dept b STRAIGHT_JOIN t_emp a on a.deptIdb.id
where a.age40
GROUP BY b.id
HAVING cou 2;create INDEX idx_dept on t_emp(deptId, age);#4、至少有2位非掌门人成员的门派EXPLAIN select SQL_NO_CACHE a.deptId,b.deptName,count(*) cou
from t_emp a INNER JOIN t_dept b on a.deptIdb.id
where a.id!b.CEO
GROUP BY a.deptId
HAVING cou 2;explain select a.deptId,c.deptName,count(*) cou from t_emp a LEFT JOIN t_dept b on a.idb.ceo INNER JOIN t_dept c on a.deptIdc.id
where b.ceo is null
GROUP BY a.deptId
HAVING cou2CREATE INDEX idx_deptId on t_emp(deptId);
CREATE INDEX idx_ceo on t_dept(ceo);#5、列出全部人员并增加一列备注“是否为掌门”如果是掌门人显示是不是掌门人显示否
select a.id, a.name, CASE WHEN b.CEO IS NULL THEN 是 ELSE 否 END 是否掌门人
from t_emp a LEFT JOIN t_dept b on a.idb.CEO;#6、列出全部门派并增加一列备注“老鸟or菜鸟”若门派的平均值年龄50显示“老鸟”否则显示“菜鸟”
select a.id,a.deptName,AVG(b.age),IF(AVG(b.age)50, 老鸟, 菜鸟) 老鸟or菜鸟
from t_dept a INNER JOIN t_emp b on a.idb.deptId GROUP BY a.id;#7、显示每个门派年龄最大的人
CREATE INDEX idx_deptId on t_emp(deptId);
CREATE INDEX idx_age on t_emp(age);#8、显示每个门派年龄第二大的人·
SET last_deptid0;
SELECT a.id,a.deptid,a.name,a.age,a.rkFROM( SELECT t.*,IF(last_deptiddeptid,rank:rank1,rank:1) AS rk,last_deptid:deptid AS last_deptidFROM t_emp tORDER BY deptid,age DESC)a WHERE a.rk2;UPDATE t_emp SET age100 WHERE id 2SET rank0;
SET last_deptid0;
SET last_age0;
SELECT t.*,IF(last_deptiddeptid, IF(last_age age, rank, rank:rank1),rank:1) AS rk, last_deptid:deptid AS last_deptid,last_age :age AS last_age
FROM t_emp t
ORDER BY deptid,age DESCCALL proc_drop_index(mydb, t_emp);
CALL proc_drop_index(mydb, t_dept);
CALL proc_drop_index(mydb, emp);
CALL proc_drop_index(mydb, dept);7.2 计算并指定索引长度
阿里开发手册
【强制】在 varchar 字段上建立索引时必须指定索引长度没必要对全字段建立索引根据实际文本区
分度决定索引长度。
说明索引的长度与区分度是一对矛盾体一般对字符串类型数据长度为 20 的索引区分度会高达 90%以上可以使用 count(distinct left(列名索引长度)) / count(*) 的区分度来确定。测试
-- address长度为10,当截取到5的时候查询区分度高达0.9572(dept表是随机数据 根据自己的情况判断)
SELECT COUNT(DISTINCT LEFT(address,5)) / COUNT(*) FROM dept;
-- 创建address列的索引并指定长度为5(address可以为空 varchar类型字节数为5*33 18)
ALTER TABLE dept ADD INDEX idx_address(address(5));
-- 可以看到address使用的索引长度为18
EXPLAIN SELECT * FROM dept WHERE address IS NULL;7.3. 时间日期处理(了解)
SELECT DATE_FORMAT(NOW() , %Y年%m月%d日 %H时%i分%s秒);
SELECT NOW();
SELECT * FROM ucenter_member WHERE DATE(gmt_create) 2019-01-02;
SELECT * FROM ucenter_member WHERE DATE_FORMAT(gmt_create , %Y-%m-%d) 2019-01-02;7.4 行转列
测试表
CREATE TABLE t_score(id INT(11) NOT NULL auto_increment,stuid VARCHAR(20) NOT NULL COMMENT id,subject VARCHAR(20) COMMENT 科目,score DOUBLE COMMENT 成绩,PRIMARY KEY(id)
)测试数据
INSERT INTO t_score(stuid,subject,score) VALUES (001,Java基础,90);
INSERT INTO t_score(stuid,subject,score) VALUES (001,mysql,92);
INSERT INTO t_score(stuid,subject,score) VALUES (001,Javaweb,80);
INSERT INTO t_score(stuid,subject,score) VALUES (002,Java基础,88);
INSERT INTO t_score(stuid,subject,score) VALUES (002,mysql,90);
INSERT INTO t_score(stuid,subject,score) VALUES (002,Javaweb,75.5);
INSERT INTO t_score(stuid,subject,score) VALUES (002,ssm,100);
INSERT INTO t_score(stuid,subject,score) VALUES (003,Java基础,70);
INSERT INTO t_score(stuid,subject,score) VALUES (003,mysql,85);
INSERT INTO t_score(stuid,subject,score) VALUES (003,Javaweb,90);
INSERT INTO t_score(stuid,subject,score) VALUES (003,ssm,82);SELECT * FROM t_score;需求行转列显示学生直观显示学生各科成绩
SELECT stuid ,
SUM(IF(SUBJECT Java基础 , score , NULL)) Java基础,
SUM(IF(SUBJECT mysql , score , NULL)) mysql,
SUM(IF(SUBJECT Javaweb , score , NULL)) Javaweb,
SUM(IF(SUBJECT ssm , score , NULL)) ssm
FROM t_score
GROUP BY stuid;7.5 group_concat
聚合函数
group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator 分隔符] )-- 查询每个学科及格的学生的id集合
SELECT SUBJECT,GROUP_CONCAT(DISTINCT stuid ORDER BY score DESC separator :)
FROM t_score
WHERE score60
GROUP BY subject;7.6 删除重复行
插入重复数据
INSERT INTO t_score(stuid,SUBJECT,score) VALUES (001,Java基础,5);
INSERT INTO t_score(stuid,SUBJECT,score) VALUES (001,mysql,90);
INSERT INTO t_score(stuid,SUBJECT,score) VALUES (001,Javaweb,1);
INSERT INTO t_score(stuid,SUBJECT,score) VALUES (002,Java基础,22);
INSERT INTO t_score(stuid,SUBJECT,score) VALUES (002,mysql,55);
INSERT INTO t_score(stuid,SUBJECT,score) VALUES (002,Javaweb,1.5);
INSERT INTO t_score(stuid,SUBJECT,score) VALUES (002,ssm,2);SELECT * FROM t_score ORDER BY stuid,SUBJECT;需求每个学生同一学科有多个成绩的保留分数高的
DELETE FROM t_score WHERE id NOT IN(SELECT tmp.id FROM(SELECT id FROM t_score t1 JOIN (SELECT stuid , SUBJECT , MAX(score) m_score FROM t_score GROUP BY stuid , SUBJECT) t2ON t1.stuid t2.stuid AND t1.subject t2.subject AND t1.score t2.m_score)tmp
);SET stuid:0;
SET subject:;
SET rank: 1;
DELETE FROM t_score WHERE id IN(
SELECT id
FROM(
SELECT * , IF(stuid stuid , IF(subject SUBJECT , rank:rank1 ,rank:1) , rank:1) rank,stuid:stuid , subject:SUBJECT
FROM t_score
ORDER BY stuid , SUBJECT ,score DESC) tmp
WHERE tmp.rank !1);7.7 窗口函数
窗口函数和普通聚合函数很容易混淆二者区别如下
Ø 聚合函数是将多条记录聚合为一条
Ø 窗口函数是每条记录都会执行有几条记录执行完还是几条
按照功能划分可以把MySQL支持的窗口函数分为如下几类:
# 序号函数没有参数
row_number()/rank()/dense_rank()
# 分布函数没有参数
percent_rank()所在行索引/总行数的百分比
cume_dist()累积分布值
# 前后函数:参数有3个(expr列名n偏移量default_value超出记录窗口的默认值)
lag(): 从当前行开始往前获取第N行缺失则使用默认值
lead():从当前行开始往后获取第N行缺失则使用默认值
# 头尾函数 参数1个(expr列名)
first_value():返回分组内第一行的值
last_value():返回分组内最后一行的值
# 其他函数:
-- 参数有2个(expr列名n偏移量)
nth_value():返回分组内截止当前行的第N行
-- 参数有1个(n: 切片数)
ntile():返回当前行在分组内的分桶号
/*语法结构window_function ( expr ) OVER (PARTITION BY ...ORDER BY ...)其中window_function 是窗口函数的名称
expr 是参数有些函数不需要参数OVER子句包含三个选项
1、分区PARTITION BY
PARTITION BY选项用于将数据行拆分成多个分区组它的作用类似于GROUP BY分组。如果省略了 PARTITION BY所有的数据作为一个组进行计算
2、排序ORDER BY
OVER 子句中的ORDER BY选项用于指定分区内的排序方式与 ORDER BY 子句的作用类似OVER后面括号中的内容可以抽取WINDOW w AS ( PARTITION BY ...ORDER BY ...)
*/测试窗口函数的使用
-- 1、查询员工信息和他部门年龄升序排列前一名员工的年龄
SELECT * , lead(age , 1,-1) over(PARTITION BY deptId) last_emp_age
FROM t_emp;-- 2、查询每个员工在自己部门由大到小的年龄排名
select * ,row_number() over(PARTITION BY deptid ORDER BY age DESC) as row_num,
from t_emp;
# 或者
SELECT * ,row_number() over w AS row_num # w代表使用的
FROM t_emp
WINDOW w AS(PARTITION BY deptid ORDER BY age DESC);
-- 3、查询每个员工在自己部门年龄排序第一个的员工姓名
SELECT * ,first_value(name) over(PARTITION BY deptId ORDER BY age)
FROM t_emp;
8. View视图
视图是将一段查询sql封装为一个虚拟的表。 这个虚拟表只保存了sql逻辑不会保存任何查询结果。
主要作用:
封装复杂sql语句提高复用性逻辑放在数据库上面更新不需要发布程序面对频繁的需求变更更灵活
常用场景:
共用查询结果报表
语法:
创建
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition 使用
#查询
select * from view_name
#更新
CREATE OR REPLACE VIEW view_name
AS SELECT column_name(s) FROM table_name WHERE condition#删除
DROP VIEW view_name;9. Mysql日志和锁
9.1 Mysql基础日志
9.1.1 日志分类
MySQL可以通过各种各样的日志帮助数据库管理员追踪数据库发生过的各种事件。MySQL有多种类型的日志用于记录数据库的操作和状态。常见的MySQL日志有错误日志、查询日志、慢查询日志、二进制日志、事务日志
9.1.2 错误日志
错误日志(Error Log)是 MySQL 中最重要的日志之一用来记录MySQL启动、运行过程中发生的错误和异常情况如启动错误、语法错误等。
my.cnf中可以添加配置设置保存错误日志文件位置
vim /var/lib/docker/volumes/mysql-master-conf/_data/my.cnf[mysqld]
log_error /var/lib/mysql/mysql-master-error.err重启mysql
docker restart mysql-master**查看日志**建议view/tail查看
tail -f /var/lib/docker/volumes/mysql-master-data/_data/mysql-master-error.err重启mysql容器可以查看到日志
9.1.3 查询日志
查询日志(Query Log)记录所有执行的sql语句包括SELECT、INSERT、UPDATE、DELETE等操作。可以用于分析查询性能和调试问题但需要注意对于高负载的系统开启查询日志可能会对性能产生影响。
my.cnf中可以添加以下配置设置保存查询日志文件位置
vim /var/lib/docker/volumes/mysql-master-conf/_data/my.cnf[mysqld]
# 该选项用来开启查询日志 可选值 0 或者 1 0 代表关闭 1 代表开启
general_log1
# 设置日志的文件名 如果没有指定 默认的文件名为 host_name.log
general_log_file/var/lib/mysql/mysql-master-query.log重启mysql容器:
docker restart mysql-master查看查询日志
tail -f /var/lib/docker/volumes/mysql-master-data/_data/mysql-master-query.log执行CRUD sql后再看控制台查询日志 注意
1、开启查询日志会影响MySQL性能生产环境中慎用。
2、查询日志会记录大量的sql语句导致文件过大。可以通过定期清理或限制日志文件大小来解决。
3、查询日志可能会记录敏感信息如密码因此要确保只有授权的人员可以访问查询日志文件。
9.1.4 慢查询日志
慢查询日志(Slow Query Log)记录执行时间超过参数 long_query_time值 (默认10秒可以精确到微秒)的查询语句。分析慢查询日志可以找出执行时间较长的查询进行性能优化。
9.1.4.1 开启慢查询日志
MySQL数据库默认没有开启慢查询日志非调优不启用。
my.cnf中可以添加以下配置设置开启慢查询日志
vim /var/lib/docker/volumes/mysql-master-conf/_data/my.cnf# 该参数用来控制慢查询日志是否开启 可取值 1 和 0 1 代表开启 0 代表关闭
slow_query_log1
# 该参数用来指定慢查询日志的文件名
slow_query_log_file/var/lib/mysql/mysql-master-slow-query.log
# 该选项用来配置查询的时间限制 超过这个时间将认为值慢查询 将需要进行日志记录 默认10s
long_query_time1重启mysql容器
docker restart mysql-master9.1.4.2 查看慢查询日志
慢查询日志和上面的日志一样也是纯文本记录可以被直接读取。
1、查询慢查询是否开启以及日志文件位置
SHOW VARIABLES LIKE %slow_query_log%; 2、查询long_query_time 的值。
SHOW VARIABLES LIKE %long_query_time%; -- 查看值默认10秒 单位秒3、查看慢查询日志文件
tail -f /var/lib/docker/volumes/mysql-master-data/_data/mysql-master-slow-query.log4、执行sql再次查看日志
-- 由于在查询的执行时间小于配置的1s因此该查询不会记录到慢查询日志中。
select * from t_emp ;再次执行以下sql
-- sleep函数可以让查询休眠指定时间
SELECT * FROM t_emp WHERE id 1 AND SLEEP(1.1) ; 查看控制台慢查询日志 9.2 Mysql事务日志
事务有4种特性原子性、一致性、隔离性和持久性。那么事务的四种特性到底是基于什么机制实现呢 事务的隔离性由 锁机制 实现。 事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。 REDO LOG 称为 重做日志 提供再写入操作恢复提交事务修改的页操作用来保证事务的持久性。UNDO LOG 称为 回滚日志 回滚行记录到某个特定版本用来保证事务的原子性、一致性。
9.2.1 事务回顾
组成一个逻辑单元的多个操作要么都成功要么都失败保证数据一致性。
ACID四大特性
A原子性(Atomicity)
一个事务(transaction)中的所有操作要么全部完成要么全部不完成不会结束在中间某个环节。事务在执行过程中发生错误会被回滚Rollback到事务开始前的状态就像这个事务从来没有执行过一样。
C一致性(Consistency)
事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。
如果事务成功地完成那么系统中所有变化将正确地应用系统处于有效状态。
如果在事务中出现错误那么系统中的所有变化将自动地回滚系统返回到原始状态。
I隔离性(Isolation)
当不同的事务同时操纵相同的数据时每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。
事务查看数据更新时数据所处的状态要么是另一事务修改它之前的状态要么是另一事务修改它之后的状态不会查看到中间状态的数据。
D持久性(Durability)
保证事务提交后不会因为宕机等原因导致数据丢失。
事务的隔离性由 锁机制 实现事务的原子性、一致性和持久性由事务的 redo日志和undo日志来保证。
9.2.2 redo log日志(了解)
9.2.2.1 innodb写数据过程
innodb存储引擎是以页为单位来管理存储空间的。
查询记录时会从硬盘把记录所在的一整页数据加载出来缓存到内存Buffer Pool中后续都是先Buffer Pool中查找没有命中再去硬盘加载减少硬盘IO开销提升性能。
9.2.2.2 redo log日志介绍
Innodb引擎采用的是WAL技术(write-ahead logging)让MySQL拥有了崩溃恢复能力 , 也就是先写日志再写磁盘只有日志写入成功才算事务提交成功这里的日志就是redo log重做日志。
有了redo log就算MySQL实例崩溃宕机重启时InnoDB存储引擎也能使用redo log恢复数据保证数据的持久性与完整性。
原理
更新表数据的时候Buffer Pool里如果存在要更新的数据就直接在Buffer Pool里更新。然后将“某个数据页上做了哪些修改”记录到重做日志缓存redo log buffer里接着刷盘到redo log文件里。 重做日志的缓冲 (redo log buffer)
存在内存中是易失的。redo log buffer 大小默认 16M 最大值是4096M最小值为1M。
show variables like %innodb_log_buffer_size%;重做日志文件 (redo log file)
保存在硬盘中是持久的。
重做日志文件保存位置
ll /var/lib/docker/volumes/mysql-master-data/_data9.2.2.3 redo log整体流程
第1步先将原始数据从磁盘中读入内存中来修改数据的内存拷贝
第2步生成一条重做日志并写入redo log buffer记录的是数据被修改后的值 。(redo记录由“表空间号数据页号偏移量修改数据长度具体修改的数据”组成)
第3步当事务commit时将redo log buffer中的内容刷新到 redo log file对 redo log file采用追加写的方式
第4步定期将内存中修改的数据刷新到磁盘中
宕机且数据未刷新到磁盘的时候可以通过redo log来恢复保证持久化。 优点
1、redo日志降低了刷盘频率
2、redo日志占用的空间非常小
特点
1、redo日志是顺序写入磁盘的
2、事务执行过程中redo log不断记录
9.2.2.4 redo log的刷盘策略
1. 介绍
redo log的写入并不是直接写入磁盘的InnoDB引擎会在写redo log的时候先写redo log buffer之后按照刷盘策略配置将redo log buffer刷入到redo log file 中。 InnoDB可以通过 innodb_flush_log_at_trx_commit 参数控制 commit提交事务时如何将 redo log buffer 中的日志刷新到 redo log file 中。
-- 查看innodb_flush_log_at_trx_commit变量的值
SHOW VARIABLES LIKE %innodb_flush_log_at_trx_commit%;2. 刷盘策略
设置为0 表示每次事务提交时不进行刷盘操作。系统默认master thread每隔1s进行一次重做日志的同步。性能最佳、数据风险较高。 设置为1 表示每次事务提交时都将执行刷盘操作 默认值 。数据安全性较高、性能稍差。 设置为2 表示每次事务提交时都只把 redo log buffer 内容写入 page cache不进行同步。由系统自己决定什么时候同步到磁盘文件。**性能较高、数据安全性较高。**在发生数据库故障时可能会丢失最近提交的事务的数据因为尚未刷新到磁盘上的日志文件中。 修改方法my.cnf中添加配置
[mysqld]
...
innodb_flush_log_at_trx_commit29.2.3 undo log日志(了解)
9.2.3.1 简介
事务需要保证 原子性 也就是事务中的操作要么全部完成要么什么也不做。但有时候事务执行到一半 会出现一些情况比如
情况一事务执行过程中可能遇到各种错误比如 服务器本身的错误 操作系统错误甚至是突然断电导致的错误。
情况二程序员可以在事务执行过程中手动输入ROLLBACK 语句结束当前事务的执行。
以上情况出现需要把数据改回原先的样子保证原子性要求这个过程称之为回滚 。
MySQL为回滚记录了增删改操作的记录这些内容称为撤销日志或者回滚日志(undo log)
1、插入一条记录至少会记录主键值之后回滚时只需要把这个主键值对应的记录删除(对于INSERTINNODB存储引擎会完成一个DELETE)。
2、删除一条记录至少会记录整条数据之后回滚时再把这些内容插入到表中就好了(对于每个DELETEINNODB存储引擎会执行一个INSERT)。
3、修改一条记录至少要记录更新前的旧值之后回滚时把这条记录更新为旧值就好了(对于每个UPDATE,INNODB存储引擎会执行一个反向的UPDATE,将修改之前的行放回去)。
redo log是事务持久性的保证undo log是事务原子性的保证。在事务中更新数据的前置操作就是写入一个undo log日志。 9.2.3.2 undo log存储结构
InnoDB对undo log采用段的方式管理也叫回滚段rollback segment最多支持128个rollback segment每个回滚段记录了1024个undo log segment所以最大支持同时在线的事务为128*1024个。undo页(详解参考9.2.3.3)是在undo log segment段中申请。 # 通过如下的SQL语句查询回滚段的大小
SHOW VARIABLES LIKE innodb_rollback_segments;9.2.3.3 undo 页
1、介绍
Undo页是用于实现事务的回滚和MVCC多版本并发控制机制的关键组成部分。
在MySQL中undo页在事务提交或回滚后使用过的undo页可以被重新利用存储新事务的undo信息。这个过程称为undo页的重用。
当一个事务提交或回滚后其对应的undo页就不再需要了。为了节省空间和提高性能MySQL会将这些undo页标记为可重用状态然后添加到undo页的空闲链表中。当新的事务需要分配undo页时MySQL会首先尝试从空闲链表中获取可重用的undo页而不是分配新的页。
通过重用undo页可以减少对磁盘空间的需求提高系统性能。然而如果系统中存在长时间运行的读事务或长时间运行的只读事务可能会导致undo页无法及时重用从而增加了undo段的大小和磁盘空间的占用。
2、回滚段与事务的关系
1、每个事务只会使用一个回滚段一个回滚段在同一时刻可能会服务于多个事务。
2、当一个事务开始的时候会指定一个回滚段事务进行的过程中当数据被修改时原始的数据会被复制到回滚段。
3、当事务提交时InnoDB存储引擎会做以下两件事情
将undo log放入列表中以便之后的purge(清理)操作判断undo log所在的页是否可以重用若可以分配给下个事务使用
9.2.3.4 回滚段中的数据分类
1、uncommitted undo information
未提交的回滚数据uncommitted undo information是指在事务执行过程中所做的修改但尚未提交的数据。这些数据包括已插入、已更新或已删除的记录。在数据库中当一个事务开始时它可能会对数据库中的数据进行修改。这些修改操作会生成相应的undo信息用于在事务回滚时撤销这些修改。这些undo信息被存储在回滚段rollback segment中。
当一个事务还没有提交时其所做的修改被视为未提交的回滚数据。这意味着其他事务无法看到这些修改因为它们尚未被永久保存到数据库中。如果该事务被回滚那么这些未提交的回滚数据将被撤销数据库恢复到事务开始之前的状态。需要注意的是未提交的回滚数据只存在于回滚段中并且只对当前正在执行的事务可见。其他事务无法读取或修改这些未提交的数据。只有在事务成功提交后这些数据才会成为已提交的数据对其他事务可见。
2、committed undo information
已经提交但未过期的回滚数据committed undo information是指在事务执行过程中所做的修改并且已经成功提交到数据库中的数据。这些数据包括已插入、已更新或已删除的记录。在数据库中当一个事务成功提交后其所做的修改会被永久保存到数据库中。同时相应的undo信息也会保留在回滚段中以便在需要时进行回滚操作。
已经提交的回滚数据对其他事务可见可以被读取和修改。这意味着其他事务可以看到并访问这些已提交的数据而不仅仅局限于当前事务的范围内。需要注意的是已经提交的回滚数据只有在没有过期的情况下才能被保留。过期数据是指由于某些原因如长时间未使用而被标记为可回收的数据。
3、expired undo information
事务已经提交并过期的数据expired undo information是指在事务执行过程中所做的修改并且已经成功提交到数据库中但由于某些原因被标记为可回收的数据。这些数据包括已插入、已更新或已删除的记录。在数据库中当一个事务成功提交后其所做的修改会被永久保存到数据库中。同时相应的undo信息也会保留在回滚段中以便在需要时进行回滚操作。
然而由于一些策略或配置的原因一些已经提交的回滚数据可能会被标记为过期。过期数据通常是由于长时间未使用或其他管理策略而被认为是可以回收的数据。过期的回滚数据可能会被后续的事务覆盖或清理以释放存储空间或提高性能。这意味着虽然这些数据已经成功提交但它们可能不再对其他事务可见或可访问。
需要注意的是过期的回滚数据的处理方式可能因数据库管理系统而异。一些数据库系统会自动清理过期数据而另一些则需要手动管理和清理。
9.2.3.5 undo log类型
在InnoDB存储引擎中undo log分为insert undo log和update undo log
1、insert undo log
Insert undo log插入撤销日志是数据库中用于记录插入操作的一种撤销日志。因为insert操作的记录只对事务本身可见对其他事务不可见(这是事务的隔离性的要求)因此 undo log可以在事务提交之后删除。
2、update undo log
Update undo log更新撤销日志是数据库中用于记录**更新操作(delete、update)**的一种撤销日志。该undo log可能需要提供MVCC机制因此不能在事务提交时就进行删除。提交时放入undo log链表等待purge线程进行最后的删除。
9.2.3.6 undo log的生命周期
1、undo log简要生成过程
假设有2个数值分别为A1和B2, 然后将A修改为3B修改为4
1、start transaction ;
2、记录A1到undo log ;
3、update A 3 ;
4、记录A3到redo log ;
5、记录B2到undo log ;
6、update B 4 ;
7、记录B4到redo log ;
8、将redo log刷新到磁盘
9、commit异常情况分析
1、在1~8步骤的任意一步系统宕机事务未提交该事务就不会对磁盘上的数据做任何影响。
2、如果在8~9之间宕机恢复之后可以选择回滚也可以选择继续完成事务提交因为此时redo log已经持久化。
3、若在9之后系统宕机内存映射中变更的数据还来不及刷回磁盘那么系统恢复之后可以根据redo log把数据刷回磁盘。
只有Buffer Pool没有Redo Log和Undo log的流程 有了Redo Log和Undo Log之后 在更新Buffer Pool中数据之前我们需要先将该数据事务开始之前的状态写入Undo log中。假设更新到一半出错了我们就可以通过Undo log来回滚到事务开始前。
2、详细生成过程
对于InnoDB引擎来说每个行记录除了记录本身的数据之外还有几个隐藏的列
1、DB_ROW_ID: 如果没有为表显式的定义主键并且表中也没有定义唯一索引那么InnoDB会自动为表添加一个row_id的隐藏列作为主键。
2、DB_TRX_ID: 每个事务都会分配一个事务的ID当对某条记录发生变更时就会将这个事务的事务ID写入trx_id中。
3、DB_ROLL_PTR: 回滚指针本质上就是指向undo log的指针。 当我们执行INSERT时
begin;
INSERT INTO user (name) VALUES (tom);插入的数据都会生成一条insert undo log , 并且数据的回滚指针会指向它。undo log会记录undo log的序号插入主键的列和值…, 那么在进行rollback的时候通过主键直接把对应的数据删除即可。 当我们执行UPDATE时
对于更新的操作会产生update undo log并且会分更新主键的和不更新主键的假设现在执行
update user set name Sun where id 1 ;这时会把老的记录写入新的undo log让回滚指针指向定的undo log它的undo log是1并且新的undo log会指向老的undo log(undo no 0)。
假设现在执行
update user set id 2 where id 1 ;对于更新主键的操作会先把原来的数据deletemark标识打开这是并没有真正的删除数据真正的删除会交给清理线程去判断然后在后面插入一条新的数据新数据也会产生undo log并且undo log的序号会递增。
可以发现每次对数据的变更都会产生一个undo log当一条记录被变更多次时那么就会产生多条undo logundo log记录的是变更前的日志并且每个undo log的序号是递增的那么要当回滚的时候按照序号依次向前这样就会找到原始数据了。
3、回滚流程
以上面的例子来说假设执行rollback那么对应的流程应该是这样
1、通过undo no3的日志把id2的数据删除
2、通过undo no2的日志把id1的数据的deletemark还原成0
3、通过undo no1的日志把id1的数据的name还原成Tom
4、通过undo no0的日志把id1的数据删除
4、undo log的删除
1、针对于insert undo log 因为insert操作的记录只对事务本身可见对其他事务不可见。故该undo log可以在事务提交后直接删除不需要进行purge操作。
2、针对于update undo log 该undo log可能需要提供MVCC机制因此不能在事务提交时就进行删除。提交时放入undo log链表等待purge线程进行最后的删除。
9.3 并发事务问题
9.3.1 写写并发问题
第一类更新丢失回滚丢失
A事务和B事务同时执行操作同一条记录B事务先于A事务提交A事务回滚时导致B事务提交的数据丢失。第二类更新丢失提交覆盖
A事务和B事务同时执行操作同一条记录B事务先于A事务提交A事务提交时覆盖了B事务提交的数据。解决加锁
9.3.2 读写并发问题
**脏读**一个事务读取到另一个事务未提交的数据 **不可重复读**一个事务读取到另一个事务已提交(更新)的数据。 **虚读、幻读**一个事务读取到另一个事务已提交(新增、删除)的数据。
解决设置事务隔离级别
不可重复度Repeatable Read
幻读Serializable
脏读Read Committed
9.4 Mysql锁
9.4.1 介绍
锁是协调多个进程或线程并发访问某一资源的机制。数据库中的数据是需要用户共享的资源。我们需要保证数据并发访问的一致性可以合理使用mysql锁。
锁机制也为实现MySQL的各个隔离级别提供了保证。 锁冲突 也是影响数据库 并发访问性能 的一个重要因素。
9.4.2 并发事务
9.4.2.1 问题
多事务并发操作相同数据库相同记录时可能会导致以下问题
脏读 一个事务可以读取另一个事务未提交的数据
事务A读取了事务B更新的数据事务B未提交并回滚数据那么A读取到的数据是脏数据
丢失修改Lost to modify指在一个事务读取一个数据时另外一个事务也访问了该数据那么在第一个事务中修改了这个数据后第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失因此称为丢失修改。
**不可重复读**一个事务可以读取另一个事务已提交的数据 单条记录前后不匹配
事务 A 多次读取同一数据事务 B 在事务A多次读取的过程中对数据作了更新并提交导致事务A多次读取同一数据时结果 不一致。
幻读(虚读) 一个事务可以读取另一个事务已提交的数据 读取的数据前后多了点或者少了点
系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级但是系统管理员B就在这个时候插入了一条具体分数的记录当系统管理员A更改结束后发现还有一条记录没有改过来就好像发生了幻觉一样这就叫幻读。
9.4.2.2 解决方案
事务隔离MySQL支持四种隔离级别
读未提交(READ UNCOMMITTED)配置后其他事务可以看到本事务没有提交的部分修改会造成脏读问题。开发中不允许脏读出现所以不会使用此隔离级别。
sql演示
# 创建数据库表
create table goods_innodb(id int NOT NULL AUTO_INCREMENT,name varchar(20) NOT NULL,primary key(id)
)ENGINEinnodb DEFAULT CHARSETutf8;# 插入数据
insert into goods_innodb(name) values(华为);
insert into goods_innodb(name) values(小米);
# 查询事务隔离级别
SHOW VARIABLES LIKE %isolation%;
# 会话一
# 设置事务的隔离级别为read uncommitted
set session transaction isolation level read uncommitted ;
# 开启事务
start transaction ;
# 查询数据
select * from goods_innodb ;# 会话二
# 设置事务的隔离级别为read uncommitted;
set session transaction isolation level read uncommitted ; # 开启事务
start transaction ;
# 修改数据
update goods_innodb set name ZETC where id 2 ;
# 会话一
# 查询数据可以查询到会话2未提交的数据
select * from goods_innodb ;
# 会话二
# 回滚事务
rollback;
# 会话一
# 再次查询测试
# 提交事务
commit;读已提交(READ COMMITTED)其他事务只能读取到本事务已经提交的部分。这个隔离级别解决了脏读问题但是有不可重复读的问题。
sql演示
# 会话一
# 设置事务的隔离级别为read committed
set session transaction isolation level read committed ;
# 开启事务
start transaction ;
# 查询数据
select * from goods_innodb ; # 会话二
# 设置事务的隔离级别为read committed
set session transaction isolation level read committed ;
# 开启事务
start transaction ; # 修改数据
update goods_innodb set name 荣耀 where id 2 ; # 会话一
# 查询数据
select * from goods_innodb ; # 会话二
# 提交事务
commit; # 会话一
# 查询数据
select * from goods_innodb ;REPEATABLE READ(可重复读)可重复读隔离级别解决了上面不可重复读的问题但是不能完全解决幻读。MySql默认的事务隔离级别就是REPEATABLE READ
SELECT global.transaction_isolation ;sql演示(解决不可重复读)
# 会话一
set session transaction isolation level REPEATABLE READ ;
# 开启事务
start transaction ;
# 查询数据
select * from goods_innodb ; # 会话二
set session transaction isolation level REPEATABLE READ ;
# 开启事务
start transaction ;
# 修改数据
update goods_innodb set name 一加 where id 1 ; # 会话一
# 查询数据
select * from goods_innodb ;
# 会话二
# 提交事务
commit;
# 会话一
# 查询数据
select * from goods_innodb ; sql演示(测试不会出现幻读的情况)
# 会话一
set session transaction isolation level REPEATABLE READ ;
# 开启事务
start transaction ;
# 查询数据
select * from goods_innodb ; # 会话二
set session transaction isolation level REPEATABLE READ ;
# 开启事务
start transaction ;
# 插入数据
insert into goods_innodb(name) values(小米);
# 提交事务
commit; # 会话一
# 查询数据
select * from goods_innodb ; sql演示(测试出现幻读的情况)
# 表结构进行修改
ALTER TABLE goods_innodb ADD version int(10) NULL ;# 会话一
set session transaction isolation level REPEATABLE READ ;
# 开启事务
start transaction ;
# 查询一条不满足条件的数据
select * from goods_innodb where version 1; # 会话二
set session transaction isolation level REPEATABLE READ ;
# 开启事务
start transaction ;
# 插入一条满足条件的数据
insert into goods_innodb(name, version) values(vivo, 1);
# 提交事务
commit; # 会话一# 将version为1的数据更改为金立
update goods_innodb set name 金立 where version 1;
# 查询一条不满足条件的数据
select * from goods_innodb where version 1; SERIALIZABLE(可串行化)这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此不会使用到。
9.4.2.3 并发事务分析
并发事务访问相同记录的情况大致可以划分为3种读-读情况、写-写情况、读-写或写-读情况
1、 读-读情况
读-读情况即并发事务相继读取相同的记录 。读取操作本身不会对记录有任何影响并不会引起什么问题所以允许这种情况的发生。
2、 写-写情况
写-写 情况即并发事务相继对相同的记录做出改动。 在这种情况下会发生 脏写(脏写读取、脏写覆盖) 的问题任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务相继对一条记录做改动时需要让它们排队执行 这个排队的过程其实是通过锁来实现的。这个所谓 的锁其实是一个 内存中的结构 在事务执行前本来是没有锁的也就是说一开始是没有 锁结构 和记录进 行关联的如图所示 当一个事务想对这条记录做改动时首先会看看内存中有没有与这条记录关联的锁结构 当没有的时候 就会在内存中生成一个 锁结构 与之关联。比如事务 T1 要对这条记录做改动就需要生成一个 锁结构 与之关联 在锁结构中存在很多的信息为了简化理解只把两个比较重要的属性拿出来
1、trx信息代表这个锁结构是哪一个事务生成的
2、is_waiting: 代表当前事务是否在线等待
当事务T1改动了这条记录后就生成了一个锁结构与该条记录关联因为之前没有别的事务为这条记录加锁所以is_waiting属性就是false我们把这个场景就称之为获取锁成功或者加锁成功。然后就可以继续进行操作了。
在事务T1提交之前另外一个事务T2也想对该记录做更改那么先看看有没有锁结构与该条记录关联发现有一个锁结构与之关联然后也生成了一个锁结构与这条记录关联不过锁结构的is_waiting属性就是true表示当前事务需要等待我们把这个场景就称之为获取锁失败或者加锁失败。如下图所示 当事务T1提交之后就会把该事务生成的锁结构释放掉然后看看有没有别的事务在等待获取锁发现了事务T2还在等待获取锁所以把事务T2对应的锁结构的is_waiting属性设置为false然后把该事务对应的线程唤醒让他继续执行此时事务T2就算获取到了锁效果如下所示 小结
1、不加锁 意思就是不需要在内存中生成对应的 锁结构 可以直接执行操作。
2、获取锁成功或者加锁成功 意思就是在内存中生成了对应的 锁结构 而且锁结构的 is_waiting 属性为 false 也就是事务 可以继续执行操作。
3、获取锁失败或者加锁失败或者没有获取到锁 意思就是在内存中生成了对应的 锁结构 不过锁结构的 is_waiting 属性为 true 也就是事务 需要等待不可以继续执行操作。
3、 读-写情况
读-写 或 写-读 即一个事务进行读取操作另一个进行改动操作。这种情况下可能发生 脏读 、 不可重 复读 、 幻读 的问题。
要想解决这些问题就需要使用到到事务的隔离级别而事务的隔离性的实现原理有两种
1、使用MVCC读操作利用多版本并发控制 MVCC 写操作进行加锁 。 普通的SELECT语句在READ COMMITTED和REPEATABLE READ隔离级别下会使用到MVCC读取记录。 1、在 READ COMMITTED 隔离级别下一个事务在执行过程中每次执行SELECT操作时都会生成一 个ReadViewReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改 也就 是避免了脏读现象 2、在 REPEATABLE READ 隔离级别下一个事务在执行过程中只有 第一次执行SELECT操作 才会 生成一个ReadView之后的SELECT操作都复用 这个ReadView这样也就避免了不可重复读 和幻读的问题。 2、读、写操作都采用 加锁 的方式。
小结对比发现
1、采用 MVCC 方式的话 读-写 操作彼此并不冲突 性能更高 。
2、采用 加锁 方式的话 读-写 操作彼此需要 排队执行 影响性能。
一般情况下我们当然愿意采用 MVCC 来解决 读-写 操作并发执行的问题但是业务在某些特殊情况 下要求必须采用 加锁 的方式执行。
9.4.3 锁分类
从对数据操作的粒度分 开发中mysql锁慎用容易导致死锁(锁升级)
1 表锁操作时会锁定整个表。
2页面锁操作时会锁定某一页的数据。
3 行锁操作时会锁定当前操作行。
从对数据操作的类型分
1 读锁共享锁针对同一份数据多个读操作可以同时进行而不会互相影响。
2 写锁排它锁当前操作没有完成之前它会阻断其他写锁和读锁。
相对其他数据库而言MySQL的锁机制比较简单其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况
存储引擎表级锁行级锁页面锁MyISAM支持不支持不支持InnoDB支持支持不支持MEMORY支持不支持不支持BDB支持不支持支持
MySQL这3种锁的特性可大致归纳如下
锁类型特点表级锁偏向MyISAM 存储引擎开销小加锁快不会出现死锁锁定粒度大发生锁冲突的概率最高,并发度最低。行级锁偏向InnoDB 存储引擎开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低,并发度也最高。页面锁开销和加锁时间界于表锁和行锁之间会出现死锁锁定粒度界于表锁和行锁之间并发度一般。
9.4.4 表锁
每次操作锁住整张表。开销小加锁快不会出现死锁锁定粒度大发生锁冲突的概率最高并发度最低
一般用在整表数据迁移的场景。
-- 加锁
lock table 表1 read(write),表2 read(write);
-- 查看表锁
show open tables where in_use1;
-- 删除本次session的表锁
unlock tables;案例
一个session添加A表的写锁另一个session添加B表的写锁第一个session测试A、B表数据读取
-- Session_1执行
lock table t_emp write;
-- Session_2执行
select * from t_emp;
-- Session_1执行
unlock tables;9.4.5 行锁Record Locks
每次操作锁住一行数据锁定粒度最小发生锁冲突的概率最低并发能力强。但是开销大、加锁慢、可能会出现死锁。InnoDB存储引擎在执行查询语句SELECT时(非串行隔离级别)不会加锁。但是update、insert、delete操作会加行锁。
查看行锁
show status like innodb_row_lock%;对各个状态量的说明如下
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间(*)
Innodb_row_lock_time_max从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数(*)
当等待的次数很高而且每次等待的时长也不小的时候我们就需要分析系统中为什么会有如此多的等待然后根据分析结果着手制定优化计划。**测试行锁**必须在事务中使用
-- Session_1执行
start transaction;
select * from t_emp where id1 for update;
-- Session_2执行
start transaction;
select * from t_emp where id2 for update;
-- Session_1执行
select * from t_emp where id2 for update;
-- Session_2执行
select * from t_emp where id1 for update;注意如果不通过索引条件检索数据那么InnoDB将对表中的所有记录加锁实际效果跟表锁一样。
9.4.6 间隙锁Gap Locks
间隙锁 是 Innodb 为了解决在可重复读下的 幻读问题引入的锁机制。间隙锁是行锁的一种。
使用间隙锁锁住的是一个区间而不仅仅是这个区间中的每一条数据。
对于键值在条件范围内但并不存在的记录叫做 “间隙GAP” InnoDB也会对这个 “间隙” 加锁这种锁机制就是所谓的 间隙锁Gap Locks。
9.4.7 临键锁Next-Key Locks
Next-key锁是记录锁和间隙锁的组合它指的是加在某条记录以及这条记录前面间隙上的锁。
也可以理解为一种特殊的间隙锁。通过临键锁可以解决幻读的问题也是行锁
select * from t_emp where id 8 and id 3 for update;9.4.7 总结
尽可能让所有数据检索都通过索引来完成避免无索引行锁升级为表锁
合理设计索引尽量缩小锁的范围
尽可能减少索引条件及索引范围避免间隙锁
尽量控制事务大小减少锁定资源量和时间长度涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
10. 多版本并发控制
10.1 MVCC概述
MVCC Multiversion Concurrency Control多版本并发控制。顾名思义MVCC 是通过数据行的多个版本管理来实现数据库的并发控制 。这项技术使得在InnoDB的事务隔离级别下执行 一致性读 操作有了保证。换言之就是为了查询一些正在被另一个事务更新的行并且可以看到它们被更新之前的值这样在做查询的时候就不用等待另一个事务释放锁。
在数据库中对数据的操作主要有2种分别是读和写而在并发场景下就可能出现以下三种情况 读-读并发 写-写并发 读-写并发 我们都知道在没有写的情况下发读-读并是不会出现问题的而写-写并发这种情况比较常用的就是通过加锁的方式实现。那么读-写并发则可以通过MVCC的机制解决。
10.2 快照读和当前读
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能用更好的方式去处理 读-写冲突 做到即使有读写冲突时也能做到 不加锁 非阻塞并发读 而这个读指的就是快照读 , 而非当前读 。当前读实际上是一种加锁的操作是悲观锁的实现。而MVCC本质是采用乐观锁思想的一种方式。
10.2.1 快照读
快照读又叫一致性读读取的是快照数据。不加锁的简单的 SELECT 都属于快照读即不加锁的非阻塞 读比如这样
SELECT * FROM xx_table WHERE ...之所以出现快照读的情况是基于提高并发性能的考虑快照读的实现是基于MVCC它在很多情况下 避免了加锁操作降低了开销。 既然是基于多版本那么快照读可能读到的并不一定是数据的最新版本而有可能是之前的历史版本。 快照读的前提是隔离级别不是串行级别串行级别下的快照读会退化成当前读。
10.2.2 当前读
当前读要保证其他并发事务不能修改当前记录会对读取的记录进行加锁读取的是记录的最新版本最新数据而不是历史版本的数据。加锁的 SELECT或者对数据进行增删改都会进行当前读。比如
# 共享锁
SELECT * FROM xx_table LOCK IN SHARE MODE;
# 排它锁
SELECT * FROM xx_table FOR UPDATE;
# 排它锁
INSERT INTO xx_table ...
# 排它锁
DELETE FROM xx_table ...
# 排它锁
UPDATE xx_table ... 10.3 隐藏字段以及Undo Log版本链
对于使用 InnoDB 存储引擎的表来说它的聚簇索引记录中都包含两个必要的隐藏列。
1、trx_id 每次一个事务对某条聚簇索引记录进行改动时都会把该事务的 事务id 赋值给 trx_id 隐藏列。
2、roll_pointer 每次对某条聚簇索引记录进行改动时都会把旧的版本写入到 undo日志 中然 后这个隐藏列就相当于一个指针可以通过它来找到该记录修改前的信息。 insert undo只在事务回滚时起作用当事务提交后该类型的undo日志就没用了它占用的Undo Log Segment也会被系统回收也就是该undo日志占用的Undo页面链表要么被重用要么被释放。
假设 trx_id 100 的事务 A 插入一条行记录id 1, username “Jack”, age 18那么这行记录的两个隐藏字段 trx_id 100 和 roll_pointer指向一个空的 undo log因为在这之前并没有事务操作 id 1 的这行记录。如图所示 然后trx_id 200 的事务 B 修改了这条行记录把 age 从 18 修改成了 20于是这条行记录的 trx_id就变成了 200roll_pointer就指向事务 A 生成的 undo log 接着trx_id 300 的事务 C 再次修改了这条行记录把 age 从 20 修改成了 30如下图 可以看到每次修改行记录都会更新 trx_id 和 roll_pointer 这两个隐藏字段之前的多个数据快照对应的 undo log 会通过 roll_pointer 指针串联起来从而形成一个版本链。MVCC 这个机制其实就是靠 update undo log 实现的。
那么一条记录在同一时刻可能有多个事务在执行那么undo log会有一条记录的多个快照那么在这一时刻发生SELECT要进行快照读的时候要读哪个快照呢
10.4 MVCC之ReadView
MVCC 的实现依赖于Undo Log、隐藏字段、Read View。
10.4.1 ReadView简介
Read View 主要来帮我们解决可见性的问题的, 也就是他会来告诉我们本次事务应该看到哪个快照不应该看到哪个快照。
MVCC主要针对的是READ COMMITTED、REPEATABLE READ
1、READ UNCOMMITTED 隔离级别的事务由于可以读到未提交事务修改过的记录所以直接读取记录的最新版本就好了。
2、SERIALIZABLE 隔离级别的事务InnoDB规定使用加锁的方式来访问记录。
3、READ COMMITTED 和 REPEATABLE READ 隔离级别的事务都必须保证读到 已经提交了的 事务修改过的记录。
这个ReadView中主要包含4个比较重要的内容分别如下
1、creator_trx_id 创建这个 Read View 的事务 ID。 说明只有在对表中的记录做改动时执行INSERT、DELETE、UPDATE这些语句时才会为事务分配事务id否则在一个只读事务中的事务id值都默认为0。 2、m_ids生成 ReadView 时有哪些事务在执行但是还没提交的称为 ”活跃事务“这些活跃事务的 id 就存在这个字段里。
3、min_trx_idm_ids 里最小的值
4、max_trx_id生成 ReadView 时 InnoDB 将分配给下一个事务的 ID 的值事务 ID 是递增分配的越后面申请的事务 ID 越大max_trx_id是系统最大的事务id值这里要注意是系统中的事务id需要区别于正在活跃的事务ID。
**注意**max_trx_id并不是trx_ids中的最大值事务id是递增分配的。比如现在有id为1 23这三个事务之后id为3的事务提交了。那么一个新的读事务在生成ReadView时 m_ids就包括1和2min_trx_id的值就是1max_trx_id的值就是4。
10.4.2 ReadView工作原理
通过一个例子来理解下 ReaView 机制是如何做到判断当前事务能够看见哪些版本的
假设表中已经被之前的事务 Aid 100插入了一条行记录id 1, username “Jack”, age 18如图所示 接下来有两个事务 Bid 200 和 Cid 300过来并发执行事务C 想要更新update这行 id 1 的记录而事务 Bselect想要查询这行数据这两个事务都执行了相应的操作但是还没有进行提交 如果现在事务 B 开启了一个 ReadView在这个 ReadView 里面
1、m_ids 就包含了当前的活跃事务的 id即事务 B 和事务 C 这两个 id200 和 300
2、min_trx_id就是 200
3、max_trx_id是下一个能够分配的事务的 id那就是 301
4、creator_trx_id是当前创建 ReadView 事务 B 的 id 200 现在事务 B 进行第一次查询会使用行记录的隐藏字段 trx_id 和 ReadView 的 min_trx_id 进行判断此时发现 trx_id 是100小于 ReadView 里的 min_trx_id200这说明在事务 B 开始之前新增这行记录的事务 A 已经提交了所以开始于事务 A 提交之后的事务 B、是可以查到事务 A 对这行记录的更新的。
row.trx_id ReadView.min_trx_id接着事务 C 过来修改这行记录把 age 18 改成了 age 20所以这行记录的 trx_id就变成了 300同时 roll_pointer指向了事务 C 修改之前生成的 undo log 那这个时候事务 B 再次进行查询操作会发现这行记录的 trx_id300大于 ReadView 的 min_trx_id200并且小于 max_trx_id301。
row.trx_id ReadView.min_trx_id row.trx_id max_trx_id这说明一个问题就是更新这行记录的事务很有可能也存在于 ReadView 的 m_ids活跃事务中。所以事务 B 会去判断下 ReadView 的 m_ids 里面是否存在 trx_id 300的事务显然是存在的这就表示这个 id 300 的事务是跟自己事务 B在同一时间段并发执行的事务也就说明这行 age 20 的记录事务 B 是不能查询到的。 既然无法查询那该咋整事务 B 这次的查询操作能够查到啥呢 没错undo log 版本链
这时事务 B 就会顺着这行记录的 roll_pointer 指针往下找就会找到最近的一条trx_id 100 的 undo log而自己的 id 是 200即说明这个 trx_id 100 的 undo log 版本必然是在事务 B 开启之前就已经提交的了。所以事务 B 的这次查询操作读到的就是这个版本的数据即 age 18。
通过上述的例子我们得出的结论是通过 undo log 版本链和 ReadView 机制可以保证一个事务不会读到并发执行的另一个事务的更新。
那自己修改的值自己能不能读到呢肯定可以读到。不过上面的例子我们只涉及到了 ReadView 中的前三个字段而 creator_trx_id 就与自己读自己的修改有关所以这里还是图解出来让大家更进一步理解下 ReadView 机制
假设事务 C 的修改已经提交了然后事务 B 更新了这行记录把 age 20 改成了 age 66如下图所示 然后事务 B 再来查询这条记录发现 trx_id 200与 ReadView 里的 creator_trx_id 200 一样这就说明这是我自己刚刚修改的啊当然可以被查询到。
row.trx_id ReadView.creator_trx_id那如果在事务 B 的执行期间突然开了一个 id 500 的事务 D然后更新了这行记录的 age 88 并且还提交了然后事务 B 再去读这行记录能读到吗 答案是不能的。
因为这个时候事务 B 再去查询这行记录就会发现 trx_id 500大于 ReadView 中的 max_trx_id 301这说明事务 B 执行期间有另外一个事务更新了数据所以不能查询到另外一个事务的更新。
row.trx_id ReadView.max_trx_id那通过上述的例子我们得出的结论是通过 undo log 版本链和 ReadView 机制可以保证一个事务只可以读到该事务自己修改的数据或该事务开始之前的数据。
10.4.3 ReadView总结
10.4.3.1 ReadView的规则
有了这个ReadView这样在访问某条记录时只需要按照下边的步骤判断记录的某个版本是否可见。
1、如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同意味着当前事务在访问 它自己修改过的记录所以该版本可以被当前事务访问。
2、如果被访问版本的trx_id属性值小于ReadView中的 min_trx_id 值表明生成该版本的事务在当前事务生成ReadView前已经提交所以该版本可以被当前事务访问。
3、如果被访问版本的trx_id属性值大于或等于ReadView中的 max_trx_id 值表明生成该版本的事务在当前事务生成ReadView后才开启所以该版本不可以被当前事务访问。
4、如果被访问版本的trx_id属性值在ReadView的 min_trx_id 和 max_trx_id 之间那就需要判 断一下trx_id属性值是不是在 trx_ids 列表中。
如果在说明创建ReadView时生成该版本的事务还是活跃的该版本不可以被访问。如果不在说明创建ReadView时生成该版本的事务已经被提交该版本可以被访问。
10.4.3.2 MVCC整体操作流程
了解了这些概念之后我们来看下当查询一条记录的时候系统如何通过MVCC找到它
1、首先获取事务自己的版本号也就是事务 ID
2、获取 ReadView
3、查询得到的数据然后与 ReadView 中的事务版本号进行比较
4、如果不符合 ReadView 规则就需要从 Undo Log 中获取历史快照
最后返回符合规则的数据。 在隔离级别为读已提交Read Committed时一个事务中的每一次 SELECT 查询都会重新获取一次 Read View。 注意此时同样的查询语句都会重新获取一次 Read View这时如果 Read View 不同就可能产生不可重复读或者幻读的情况。
当隔离级别为可重复读的时候就避免了不可重复读这是因为一个事务只在第一次 SELECT 的时候会 获取一次 Read View而后面所有的 SELECT 都会复用这个 Read View如下表所示 10.5 总结 MVCC 在 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行快照读操作时访问记录的版本链的过程。这样使不同事务的 读-写 、 写-读 操作并发执行从而提升系统性能。核心点在于 ReadView 的原理 READ COMMITTD 、 REPEATABLE READ 这两个隔离级别的一个很大不同就是生成ReadView的时机不同 READ COMMITTD 在每一次进行普通SELECT操作前都会生成一个ReadView REPEATABLE READ 只在第一次进行普通SELECT操作前生成一个ReadView之后的查询操作都重复 使用这个ReadView就好了。
