文章目录
第1章 MySQL架构与历史
MySQL架构
MySQL采用的是分层架构:上层是Server层,下层是存储引擎层。
这里讲一下一条SQL的执行过程,来把各个模块串起来:
- 客户端与数据库建立连接,首先与连接器进行交互。
- 客户端发送SQL命令,MySQL先查询缓存。缓存是以key-value的形式存在内存中的,其中key为SQL语句,value为查询结果,任意对该表的修改都会清空缓存。
- 如果缓存未命中,则走到解析器,进行词法、语法分析,将字符串的SQL指令解析为树。
- 解析完成后走到优化器,根据索引、统计信息选择最优的执行计划。
- 然后执行器根据执行计划,调用存储引擎的接口,逐行将数据取出来。如果数据不满足条件,则取下一行,否则放入结果集中,知道取完所有数据。
事务隔离级别
SQL标准中定义了四种隔离级别:
隔离级别 | 定义 | 脏读 | 不可重复读 | 幻读 | 加锁读 |
---|---|---|---|---|---|
读未提交 READ UNCOMMITTED, RU | 可以读到其他事务未提交的修改 | 有 | 有 | 有 | 无 |
读提交 READ COMMITTED, RC | 只能读到其他事务已提交的修改 | 无 | 有 | 有 | 无 |
可重复读 REPEATABLE READ, RR | 通过MVCC,保证该事务过程中读到的数据总是一致的 | 无 | 无 | 有 | 无 |
串行化读 SERIALIZABLE | 事务串行执行 | 无 | 无 | 无 | 有 |
其中可重复读是默认隔离级别。
事务日志
MySQL采用预写式日志(Write-Ahead-Logging, WAL)机制,在修改数据时,只1)修改内存中的数据拷贝并2)记录事务日志并持久化到硬盘,从而提高效率。事务日志采用追加写的方式,顺序IO,效率高于直接修改数据的随机IO。
MySQL中的事务日志主要有:
- bin log,server层记录,分为Statement、Row两种方式。其中Statement格式记录了SQL原始语句、Row格式记录了每一行变化前后的值。Statement可能导致主从不一致(now()的结果可能不一样、生成的自增值可能不一样等),Row需要记录变化前后值因此日志量较大,因此可以采用Mixed方式,混合采用上述两种方式。
- redo log,InnoDB存储引擎生成的日志,主要用于崩溃后的恢复,环形队列,顺序追加写。
- undo log,InnoDB存储引擎生成的日志,主要用于MVCC。
多版本并发控制MVCC
InnoDB MVCC的实现,是通过保存数据在某个时间点的快照来实现的。InnoDB在每行记录后面都保存两个隐藏的列,其一保存了行的创建时间,另一保存了行的过期时间,其实际是系统的版本号。每开启一个事务,系统版本号就会自动递增。
MySQL存储引擎
MyISAM
MySQL亲儿子,5.1版本前是默认引擎,支持全文索引,不支持事务、行锁、崩溃恢复。
InnoDB
MySQL干儿子,支持MVCC,采用聚簇索引建表,5.1版本开始支持,5.5版本成为默认引擎。
可以通过如下命令查看表使用的存储引擎:
mysql> show table status like 'test' \G;
*************************** 1. row ***************************
Name: test
Engine: InnoDB
...
1 row in set (0.01 sec)
第4章 Schema与数据类型优化
主要原则:
- 更小的通常更好。跟小的数据类型占用更少的内存、磁盘、CPU缓存。
- 简单就好。使用整形而不是字符串,使用时间类型而不是字符串。
- 尽量避免NULL。NULL列不利于优化。
范式和反范式
- 第一范式:数据库表的每一列都是不可分割的基本数据项;
- 第二范式:在第一范式的基础上,要求必须有主键,且非主键必须完全依赖于主键,而不能只依赖主键的一部分;
- 第三范式:在第二范式的基础上,要求不存在传递依赖;
举例:
- 姓名,地址,家庭电话-公司电话,就不符合第一范式,需要改为如下:姓名,地址,家庭电话,公司电话。
- 学生,课程,分数,教师,就不符合第二范式,因为分数依赖学生-课程主键,但教师只依赖课程,需要拆分成两个表:学生,课程,分数、课程,教师。
- 商品ID,商品名,仓库ID,仓库地址,就不符合第三范式,因为主键为商品ID,仓库地址通过仓库ID依赖于商品ID。
范式的优点:
- 更新操作更快
- 没有重复数据,只需要修改更少的数据
- 范式表占用空间更小
反范式的优点:
- 查询时不需要关联
- 全表扫描时可以顺序IO,而无需随机IO
- 索引优化可能更有效
第5章 创建高性能的索引
索引的概念
B+Tree
B+Tree实际上是一个多叉搜索树,通过增加每个节点的扇出来降低数的高度。
B+Tree和B-Tree的区别在于:B+Tree的数据都存在叶子节点上,因此非叶子节点占用空间更少,可以将其放在内存中,多次磁盘IO。
三星索引
- 索引将相关记录放到一起则获得一星;
- 索引中的数据顺序和查找中的排列顺序一致则获得两星;
- 如果索引中的列包含了查询中需要的全部列则获得三星;
最左匹配原则
回想一下上文的B+Tree结构。
索引的搜索,是从上至下,从左至右的。对于多列索引,先根据左侧列排序,再根据右侧列排序。因此,索引(a)
、(a,b)
对如下查询是等价的:
select * from table_name where a = 0;
但索引(b,a)
对上述查询无效。
高性能索引策略
- 独立的列:索引不能是表达式的一部分,也不能是函数的参数。例如,下面的查询无法使用
actor_id
列的索引:
select actor_id from actor where actor_id + 1 = 5;
这个查询其实等价于actor_id = 4
,但MySQL无法理解用户行为。
- 索引选择性:不重复的索引值和表记录总数的比值。索引的选择性越高,MySQL查找时就能过滤掉更多的行。
- 前缀索引:对应VARCHAR等字符串类型的变量,可以采用前缀索引,类似于最左匹配原则。
多列索引
- 一个常见的错误:为每个列创建独立的索引,或按照错误的顺序建立多列索引。参考:最左匹配原则。
- 不需要排序和分组时,将选择性高的放在索引的最前列。