1.概念
(1).索引组织表
由索引组织起来的表,索引组织表中的数据,被主键的索引组织起来,InnoDB中,表都是根据主键顺序组织的。
(2).索引
索引是数据库中对某一列或多个列的值进行预排序的数据结构,InnoDB中,主键是一个特殊的索引字段。
(3).主键
InnoDB中,每一张表都有一个主键。
- 若表中有一个非空唯一索引,那么该索引就是主键。
- 若有多个非空唯一索引,选择第一个定义的索引作为主键。
- 若没有非空唯一索引,InnoDB自动创建一个6字节的指针作为隐式主键。
2.索引的底层数据结构
(1).主流索引查找算法
- 线性查找
- 二分查找
- 二叉查找树
- 平衡二叉树
- B树
- B+树
- 数据可视化工具
(2).线性查找
- 时间复杂度为O(N)
- 效率低
(3).二分查找
- 时间复杂度为O(logN)
- 磁盘不连续,有时不知道中间节点在哪里
(4).二叉查找树
- 时间复杂度为O(logN)
- 不平衡,可能退化为链表
(5).平衡二叉树
- 时间复杂度为O(logN)
- 一个节点只能存储一个数据,磁盘利用率低(B树的每个节点的元素可以视为一次I/O读取,在相同数量的总元素个数下,每个节点的元素个数越多,查询所需的I/O次数越少)
(6).B树
- 时间复杂度为O(logN)
- 范围查找效率低
(7).B+树
- 时间复杂度为O(logN)
- 特点
- 索引和数据分开,所有数据都存在叶子结点
- 叶子结点之间通过指针连接,范围查找时,顺着指针找即可
3.InnoDB数据表存储
(1).逻辑结构
(2).表空间tableSpace
指的是数据表在硬盘上的存储空间,就是通常的ibd文件。
(3).段segment
- 索引段:B+树的非叶子结点
- 数据段:B+树的叶子结点
(4).区extent
一个区由64个页组成。
(5).页page
一个page就是B+树的一个结点,页是InnoDB中磁盘读写的最小逻辑单位,默认16KB。不能太大,因为考虑到查找效率和加载利用率,不能太小,太小导致IO读写数据过少。
(6).行row
数据真正存储的空间,包含主键字段和其它字段。如果某个字段数据长度大于40B,那么就会另外开辟一段磁盘空间存储,然后字段只记录BLOB页指针。
4.日志
(1).日志类型
数据库在更新时,会产生binlog、redo log和undo log。
(2).binlog
server层产生的逻辑日志,用来进行数据复制和数据恢复。
(3).redo log
InnoDB自身产生的逻辑日志,用来记录数据页的变化,保证持久化。
类似于代码review,有问题时,先记录,review后根据记录点进行修改,修改完一条就删除一条。
(4).undo log
InnoDB自身产生的逻辑日志,用来进行事务回滚和展示旧版本,保证隔离性和原子性 。
(5).数据更新流程
然后客户端提交事务,MySQL Server将binlog写入内存,InnoDB提交事务。
redo log需要在binlog之前,是因为redo log是系统的关键节点,redo log写入成功之后,数据就会更新至磁盘。此外,binlog如果在redo log之前写入,如果redo log写入失败,那么binlog可能传送至备库,导致主从数据不一致。
(5).日志刷盘
如果断电,那么内存中的binlog,redo log日志可能丢失,丢失不可避免,只能通过参数控制刷盘减少损失。
- innodb_flush_log_at_trx_commit参数控制redo log刷盘
- 0表示异步每秒刷盘
- 1表示每个事务刷盘
- N表示每N个事务刷盘
- sync_binlog参数控制binlog刷盘
- 0表示自动刷盘
- 1表示每个事务刷盘
- N表示每N个事务刷盘
5.锁
(1).分类
按照粒度分为全局锁、表级锁(表锁和元数据锁)、行锁。
(2).全局锁
全局锁,整个库无法修改,使用很少。最常见的是FTWRL(命令flush tables with read lock),此命令使整个库处于只读状态,主要用途是备份。
(3).表锁
表锁,顾名思义对表上锁,表锁粒度也比较大,使用也很少,命令lock table tableName read/write。
(4).元数据锁
元数据锁锁的是表的元数据,元数据指的是表的结构、字段、数据类型和索引等。事务访问数据时,会自动给表加metadata lock读锁,事务修改数据时,会自动给表加metadata lock写锁。
(5).行锁
行锁会锁住数据行,分为读锁(S锁)和写锁(X锁)。S锁不是不让读,而是自己读,不让别人写。X锁不是不让写,而是自己写,不让别人写。只有读读可以兼容,读写、写读、写写都互斥。
#X锁
UPDATE
DELETE
SELECT FOR UPDATE
#S锁
SELECT LOCK IN SHARE MODE
6.事务
(1).简介
事务的ACID特性,即Atomicity(原子性)、 Consistency(一致性)、Isolation(隔离性)和Durability(持久性)。
(2).原子性
事务中的操作要么全部成功,要么全部失败。
MySQL的两阶段提交保证了事务的原子性。
使用undo log用来回滚事务的更改。
(3).一致性
事务在执行完成时,必须使所有的数据都保持一致状态。
MySQL的锁和两阶段提交保证了事务的一致性。
比如小明有100元,小红有0元,小明向小红转账100元,那么转账前,总额为100,转账后总额还是100,那么数据就是一致的。
(4).隔离性
事务独立运行,多个事务之间相互隔离,互不干扰。
MySQL的锁和undo log保证了事务的隔离性。
(5).持久性
事务执行完成之后,它对MySQL的影响是永久性的。
redo log保证了事务的持久性。
7.事务的隔离级别
(1).读未提交(READ UNCOMMITED)
可以读到其它事务未提交的数据。
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
(2).读提交(READ COMMITED)
读取此时已经提交的数据,未提交的读取不到。
show variables like 'tx_isolation';
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
如果,第二个客户端在第一个客户端未commit的情况下,也尝试修改数据,则会出现阻塞状态,因为第一个客户端在修改的时候,加了x锁,只有commit后才会释放。
(3).可重复读(REPEATABLE READ)
读取本事务开始时的数据状态,MySQL的默认隔离级别。
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
(4).串行化(SERIALIZABLE)
读加S锁,写加X锁,提交时释放。对于同一条数据,同时只能有一个事务进行写操作。安全最高,但是性能最差。
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
8.MVCC
(1).行记录版本控制
由于redo log的存在,可以根据trx_id从最新版本推算之前的版本。
(2).快照读(一致性非锁定读)
不锁定数据的情况下,读取数据的特定历史版本,版本由事务的具体需求确定。如读已提交,根据每次select时,其它事务的提交情况。可持续读,根据事务开始时,其它事务的提交情况。
(3).当前读(一致性锁定读)
读取数据的当前版本,并加行锁。如当前版本已经被加X锁且不兼容,则阻塞等待。
9.隔离问题
(1).脏读
读到了其它事务未提交的数据。
(2).不可重复读
同样的查询,读取到的数据内容不一样。
(3).幻读
同样的查询查询到了更多的数据。
(4).MySQL不同隔离级别的问题
通过间隙锁解决了部分幻读问题。
10.间隙锁把全表锁住了
11.死锁
(1).行级锁长时间无法释放
当前读会对数据行加锁,事务提交前无法释放,其它事务更新相同数据会阻塞,导致更新性能变差。
解决方法是将innodb_lock_wait_timeout缩短,如20秒或者30秒,让SQL语句报错返回。默认为50秒,等待50秒还未获取到锁,当前语句就会报错。
(2).死锁
解决方法,主动死锁检测innodb_deadlock_detect,默认开启,发现死锁时,回滚代价较小的事务,比如回滚5条就比回滚100条的代价小。
事务量大的情况下,会影响性能。
12.元数据锁导致数据库崩溃
事务访问数据时,会自动给表加metadata lock读锁,事务修改数据时,会自动给表加metadata lock写锁。
遇到锁不兼容时,即先加metadata lock读锁,后面有事务申请加metadata lock写锁,就会形成等待队列。
解决方法
alter table之前,查看是否有长事务还未提交,通过information_schema库innodb_trx表查看长事务。
如何查看影响性能的锁
通过information_schema库innodb_locks查看锁。
通过information_schema库innodb_lock_waits查看阻塞的事务。