1.MySQL的逻辑架构
(1)服务层
(2)解析优化,内置函数
(3)存储引擎
2.连接管理与安全性
线程池:新建一个连接不会重启一个线程
3.优化与执行
解析查询的内部数据结构:解析树
查询之前的优化:查询缓存
4.并发控制
锁的分类:
(1)读锁,共享锁(S锁)
(2)写锁,排他锁(X锁)
意向锁:不会阻塞全表扫描外的任何请求
(1)IS锁
(2)IX锁(IX锁之间是互相兼容的)
锁的粒度:
(1)表级锁
(2)行级锁
5.事务处理
事务的ACID特性:
(1)原子性(redo log,记录页的更改)
(2)一致性(undo log,记录每行的操作)
(3)隔离性(lock)
(4)持久性(redo log)
事务的隔离级别:
(1)read committed
(2)read uncommitted
(3)repeatable read
(4)serializable
set session transaction isolation level repeatable read;
6.死锁
解决死锁问题:
(1)死锁检测:等待图 {锁的信息链表,事务等待链表}
(2)死锁超时机制
InnoDB处理死锁的方法:回滚
7.事务日志
预写式
修改数据需要修改2次磁盘
undo log -> update -> redo log -> write to 磁盘
undo log存放在undo段,undo段位于共享表空间。
当InnoDB引擎进行回滚时,实际上是做与之前相反的操作。
undo log会产生redo log。
8.MYSQL的事务:自动提交
隐式锁定:
显式锁定:
select * lock in shape mode;(S锁)
select * for update; (X锁)
lock tables;
9.多版本并发控制(MVCC)
实现了可重复读,读取快照数据
InnoDB的MVCC实现:undo log(每行数据有两个隐藏列:创建版本号,删除版本号)
间隙锁策略(next-key locking)防止幻读
10.后台进程
Master Thread
IO Thread (Read, Write, Insert Buffer, Log IO Thread)
Purge Thread(删除无用的undo页)
Page Cleaner Thread(刷新脏页)
11.缓冲池技术
平衡CPU和磁盘的速度差异
redo日志:持久化(redo日志缓冲)
缓冲池对象:数据页,索引页,insert buffer,lock info,自适应hash索引,数据字典
缓冲池页的管理:LRU算法(优化版),midpoint位置;page made young,page not made young
页的列表:free列表和LRU列表,维护页的更新。
12.压缩页
16kB -> 8/4/2kB
压缩页的管理:unzip_LRU列表
13.checkpoint机制
只对checkpoint之后的redo log进行恢复,脏页刷新
缩短数据库的恢复时间
14.insert buffer
对于不是unique的非聚集索引,
插入或更新时,判断辅助索引页是否在缓冲池,若不在则放到一个insert buffer对象中
merge insert buffer:(1)辅助索引页读到缓冲区
(2)insert buffer bitmap查到该辅助索引页无可用空间
(3)master thread
insert buffer -> change buffer
实现:B+树(共享表空间),叶子结点,非叶子节点
非叶子节点的键值---[space(表空间id),marker,offset(页偏移)]
叶子结点的键值---[space,marker,offset,metadata(进入顺序),辅助索引记录]
若辅助索引不在缓冲区,构造一个键值插入到叶子结点
insert buffer bitmap:标记每个辅助索引页的可用空间
15.double write
解决了部分写失效的问题,提高了可靠性。
在宕机恢复数据库的时候,应用redo日志前,需要建立一个页的副本。
16.自适应hash索引
InnoDB引擎为热点页,自动建立hash索引。
17.异步IO(AIO)
AIO的优势:IO merge操作;
需要native AIO,操作系统的支持。
18.刷新邻接页
刷新脏页时,连同该页所在区的所有页一起刷新。
固态硬盘不需要。
19.数据库的日志文件(不是InnoDB引擎)
(1)错误日志(error log):数据库优化信息
(2)二进制日志(binlog):记录数据库的更改操作(格式:STATEMENT,ROW,MIXED)
(3)慢查询日志(slow query log):定位有问题的SQL语句
(4)查询日志(log):记录对数据库的请求信息
20.重做日志与binlog的区别
(1)记录的内容不同,redo log记录对页的更新情况;
(2)写入的时间不同,binlog提交前记录一次,redo log在事务过程中记录;
(3)记录的范围不同,redo log只是记录innodb引擎,binlog记录所有存储引擎;
重做日志还有一个重做日志缓冲,按扇区写的,一定成功,不需要double write。
21.表的逻辑存储结构
表空间(共享,独立),段(数据段,索引段,回滚/undo段)
区(1MB连续空间),页(磁盘管理最小单位)
行:行格式
Antelope(Compact[NULL值不占用空间],Redundant)
Barracuda(Compressed[zlib压缩,有效存储blob/text数据],Dynamic)
22.InnoDB数据页结构
[文件头,页头,Infinum和Suprenum记录,user record,空闲空间,页目录,文件尾]
B+树索引只能找到记录所在页,数据库载入内存,通过二叉查找。
23.分区表
InnoDB引擎支持分区,水平分区
range分区,list分区,hash分区,key分区
24.聚集索引和辅助索引
聚集索引:叶子结点存放行记录数据(数据页);
数据页存放完整行记录,非数据页的索引页存放键值和数据页偏移量。
辅助索引:叶子结点存放键值和书签,书签告诉如何查找对应行数据,对应数据的聚集索引键。
通过辅助索引页找到主键,再通过聚集索引页找到数据页。
25.覆盖索引
从辅助索引就可以查到的,不需要查询聚集索引。
explain select语句:显示Extra:Using Index表示使用了覆盖索引。
26.优化器不使用索引的情况
(1)全表扫描(范围查找,join操作)
(2)数据量比较大(辅助索引带来离散读,性能低下)
(3)固态硬盘(离散读性能较高)
force index 强制索引
use insex 索引提示
>select * from table_name force/use index() where ...
27.MRR优化(Multi_Range Read)
减少磁盘的随机访问,将离散读转化为较为顺序的数据访问。
MRR工作方式:将查询到的辅助索引键值放在缓存,将键值按照rowid排序
MRR还可以将范围查询拆分为键值,批量数据查询
28.ICP优化(Index Condition Pushdown)
在取出索引的同时,利用where条件过滤。
29.全文索引(Full-Text Search)
实现:倒排索引,在辅助表中存储了单词与单词所在文档位置之间的映射
inverted file index [单词,所在文档id]
full inverted index [单词,所在文档id,具体位置]
FTS Index Cache(全文检索索引缓存):红黑树结构,根据(word,ilist)进行排序
将(文档id,具体位置)视为一个ilist
InnoDB引擎对辅助表进行批量更新,批量更新前是对FTS Index Cache进行更新
语法:match (col1, col2) against (expr[])
>select * from table_name where match (body) against ('+please+hot' in boolean mode)
>select * from table_name where match (body) against ('prorridge' in natural language mode)