InnoDB 的数据是按数据页为单位来读写的。也就是说,当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在 InnoDB 中,每个数据页的大小默认是 16KB。
一、MySQL查询执行过程
1.MySQL分层结构
- MySQL8.0没有查询缓存的功能了,如果频繁修改缓存,将会损耗性能
- 查询流程就按照分层结构就可以清楚,只要了解各个组件的各自功能就行
- 分析器主要分析语法和词法是否正确
- 优化器主要优化SQL语句
二、MySQL更新执行过程
- 更新主要涉及两个重要的日志,redolog(重做日志)和binlog(归档日志)
1.redolog
- 如果没有日志记录的话,每一条更新语句将会写进磁盘,再从磁盘找到对应的数据进行更新,将会非常损耗性能
- InnonDB引擎会先更新内存,再写redolog日志,等系统空闲的时候,再将入磁盘.即使数据库宕机,恢复后也能根据redolog将未写入磁盘的更新数据写入磁盘,保证数据更新这就是InnoDB提供的crash-safe能力
- redolog是固定大小的文件,如果日志文件不够写,将会先擦除一部分再写
2.binlog
- binlog是server层提供的归档日志,会记录下更新的逻辑,比如将1变为2
3.两种日志区别
- redolog是InnoDB提供的,支持crash-safe能力;binlog是server层提供的
- redolog是循环写的,日志文件会用完;binlog是追加写的
4.更新过程
示例:给ID=2的C字段加1
- 查找ID=2的数据,如果内存有直接返回,没有则从磁盘查找,再返回给内存
- 将ID=2的C字段+1,调用执行引擎接口更新数据,并将数据更新到内存
- 将记录写入redolog,此时redolog处于prepare状态
- 告知执行器可以提交事务,写binlog日志
- redolog提交事务,更新完成
- 两阶段提交
为什么会两阶段提交redolog和binlog呢,原因就是为了误删数据后,恢复数据能与历史数据达成一致.我们可以进行反证
- 假设先提交redolog,当发生了一次crash-safe,恢复后redolog记录的日志可以更新数据,但是binlog没有记录.之后使用binlog恢复删除的数据,会造成该条数据不一致
- 假设先提交binlog后crash,但是redolog并没有记录该条数据的更新,所以机器恢复后redolog不会更新该条数据,但是使用binlog恢复删除的数据则会更新该条数据,也会造成数据不一致
三、事务隔离
- 事务特性
- 原子性
一条事务是原子的,不可分割,要么全部执行,要么都不执行 - 一致性
事务开始前状态一致,事务执行过程中可能不一致,事务执行后状态一致 - 隔离性
多个事务之间互相隔离,不依赖不影响 - 持久性
一旦事务被提交,所做的变更是持久的
- 事务并发带来的问题
- 脏读
A事务读取到B事务没有提交的结果,读取到脏数据 - 不可重复读
前后多次读取,数据内容不一致。举个例子:A事务第一次查询name为"X",这期间B事务将name改为"Y",A事务第二次查询name变为了"Y"。A事务中两次读取的数据内容不一致,就不可重复读。针对update操作。 - 幻读
前后多次读取,数据总量不一致。举个例子:A事务第一次读取数据总量为100条,这期间B事务新增了一条数据,A事务再次读取的数据总量为101条。A事务两次读取的数据总量不一致,称为幻读。针对insert和delete操作。
- 事务隔离级别
为了形象的解释隔离级别,画个图展示一下.
- 读未提交
读取没有提交的数据,基本没啥作用.v1和v2和v3都是19 - 读已提交
可以解决脏读的问题,A事务只能读取其他事务提交后的结果.因为A事务只能读取其他事务提交后的结果,所以v1是18,v2和v3都是19.通过视图保证读已提交,视图在事务的每条sql语句执行时生成. - 可重复读
事务在执行的过程中,看到的结果是一样的.v1和v2都在A事务执行过程当中,所以v1和v2都是18,v3是19.通过视图达到可重复读,视图在事务开始的时候创建,整个事务过程都会用到. - 串行化
读的时候加读锁,写的时候加写锁.使用synchronize锁保证.v1和v2都是18,v3是19
- 事务隔离级别实现
以下以可重复读举例
- 视图是保证事务执行过程中能查询的数据,回滚日志只是为了回滚事务到之前的版本
- 在每条记录更新的时候都会同时记录一条回滚操作,通过回滚操作可以回滚到上一个版本的值.
- 因此在数据库中,一条数据可能有多个版本的值,又称作多版本.使用多版本并发控制(MVCC)保证事务隔离级别
- 当没有事务使用回滚日志时,回滚日志会被删除
不建议使用长事务,原因如下:
- 长事务占用锁,非常影响性能
- 长事务可能保存比较老的视图,要想回到视图之前的版本,就需要保存多个版本的回滚日志,将会非常占用资源
四、索引
1.索引数据结构
- hash表
通过hash函数把key换算成对应的位置,再把value放在这个位置.需要查询的时候通过hash计算就能找到对应的位置,取出该位置的值了.如果碰到hash碰撞,该下标所在位置会转换成链表.hash索引适合等值查询,如果范围查询就需要遍历全表了. - 有序数组
有序数组经过排序后,利用二分法查询速度也比较快,并且非常适合范围查询.如果有新增索引,为了保证有序,新增位置之后的索引全部后移,非常损耗性能. - 二叉搜索树
左节点的值小于根节点,右节点的值大于根节点.利用二叉搜索树,查询速度也比较快.但实际上,大多数的索引结构不选用二叉树,理由是索引不止存在内存中,还可能写到磁盘上.索引树的每个节点都是一个页,一页保存多行记录,不论读一行,还是读多行,都是将这些行所在的页进行加载。也就是说存储空间的基本单位是页。如果二叉树比较高,每次查询都需要访问过多节点,即访问多个页,页存储在数据块中,所以访问数据块过多,而从磁盘随机读取数据块过于耗时。所以更多选用B+树做索引结构,B+树有N叉,保证树尽量矮
2.innodb索引模型
- innodb索引结构为B+树,每一个索引就是一棵B+树
- innodb索引分为主键索引(又叫聚簇索引),非主键索引(又叫二级索引)
- 主键索引保存数据整行记录
- 非主键索引保存主键的值
- 如果使用非主键索引查询,先要查询到主键的值,再通过主键的值回表查询到该行记录.
3.索引维护
假设现在插入一条为400的数据,那么400后面的ID索引将会后移,如果数据页满后,还会进行页分裂,当然页的数据太少也会进行页合并.所以为了避免页分裂,尽量插入的索引是有序的,这就是为什么推荐使用自增ID做主键索引的原因.
4.覆盖索引
上面提到,如果是二级索引,可能会进行回表查询,使用覆盖索引将会避免回表查询,提升查询速度.
- 使用sql语句: select ID from table where k = 3 .这条sql会根据k的索引树查询,但是不需要回表,因为只需要查找ID的值,所以就避免了回表.
- 有的情况下,建立联合索引避免回表查询,会提升查询速度,但是索引过大也会占用内存和磁盘,需要根据场景选择.比如身份证和姓名,有一个需求需要根据身份证查询姓名,那么就可以将身份证和姓名建立联合索引.
5.最左前缀原则
- 这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。
- 利用索引最左前缀匹配索引的最左N个字段的规律,可以尽量少维护索引.比如有根据name和age查询的需求,有根据name查询的需求,那么只需要建立一个联合索引(name,age)就可以了.
6.索引下推
- 以联合索引(name,age)为例,sql语句: select name,age from table where name like ‘张%’ and age = 10
- 如果姓张的人有10个,姓张并且年龄为10的只有三个
- 当查出来姓张的人后,会继续使用age索引查询满足age=10的数据
MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
7.索引失效
模型数空运最快
- 模:模糊查询的意思。like的模糊查询以%开头,索引失效。比如:
SELECT * FROMuser
WHEREname
LIKE ‘%老猿’; - 型:代表数据类型。类型错误,如字段类型为varchar,where条件用number,索引也会失效。比如:
SELECT * FROMuser
WHERE height= 180;
height为varchar类型导致索引失效。 - 数:是函数的意思。对索引的字段使用内部函数,索引也会失效。这种情况下应该建立基于函数的索引。比如:
SELECT * FROMuser
WHERE DATE(create_time) = ‘2020-09-03’;
create_time字段设置索引,那就无法使用函数,否则索引失效。 - 空:是Null的意思。索引不存储空值,如果不限制索引列是not null,数据库会认为索引列有可能存在空值,所以不会按照索引进行计算。比如:
SELECT * FROMuser
WHERE address IS NULL不走索引。
SELECT * FROMuser
WHERE address IS NOT NULL;走索引。
建议大家这设计字段的时候,如果没有必要的要求必须为NULL,那么最好给个默认值空字符串,这可以解决很多后续的麻烦(切记)。 - 运:是运算的意思。对索引列进行(+,-,*,/,!, !=, <>)等运算,会导致索引失效。比如:
SELECT * FROMuser
WHERE age - 1 = 20; - 最:是最左原则。在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找,则无法使用索引。
- 快:全表扫描更快的意思。如果数据库预计使用全表扫描要比使用索引快,则不使用索引。
五、锁
Mysql分为全局锁,表锁,MDL锁,行锁
1. 全局锁
全局锁是针对整个数据库的,一般全库逻辑备份使用,使用全局锁会让整个库处于只读状态.命令是 Flush table with read lock
- 如果对主库使用全局锁,业务更新指令将会被阻塞.
- 对从库使用全局锁,将会不能执行主库同步过来的binlog,导致主从延迟.
- 只读为什么不使用set global readonly=true
- 有时候readonly用在业务逻辑
- 当使用readonly时,如果客户端异常,就会一直保持只读的状态.使用FTWLK遇到异常会释放锁
2.表级锁
Mysql的表级锁分为两种,分别是表锁、MDL(meta data lock)
(1)表锁
表锁的语法是 lock tables … read/write。与 FTWRL 类似,可以用 unlock tables 主动释放锁,也可以在客户端断开的时候自动释放。需要注意,lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。
- 举个例子:如果某线程A执行: lock tables t1 read,t2 read,write,后面其他线程写t1,读写t2都会被阻塞,.线程 A 在执行 unlock tables 之前,也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许,自然也不能访问其他表。
(2)MDL锁
MDL锁不需要显示使用,在访问某一个表时会自动加上
- 在对数据进行CRUD时,也就是DML时会加上MDL读锁;在对表结构进行修改时,也就是DDL会加上MDL写锁.
- 读锁之间不互斥,读写、写写之间互斥
- MDL互斥阻塞
有时候修改一个小表结构,可能会将整个数据库压垮,下面看一下这个问题
- 在这个例子当中,A事务查询和B事务查询不会冲突,正常执行.
- 当B事务查询没结束的时候,带上的是MDL读锁,此时C事务修改表结构需要MDL写锁,会被阻塞
- C事务阻塞可以等待,但是D事务需要MDL读锁,需要等待C执行完毕.如果D事务后面还有很多事务等待执行,那么MySQL线程很快就会爆满
- 给表安全加字段
- 如果有长事务执行,则需要kill掉这个长事务
- 设置DDL的超时时间