高性能MySQL - 读书笔记

最新推荐文章于 2024-08-11 21:13:17 发布

tptppp

最新推荐文章于 2024-08-11 21:13:17 发布

阅读量152

点赞数 1

分类专栏： MySQL 文章标签： MySQL 数据库性能优化

本文链接：https://blog.csdn.net/tptpppp/article/details/91425837

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

第1章 MySQL架构与历史
第4章 Schema与数据类型优化
- 范式和反范式
第5章创建高性能的索引

第1章 MySQL架构与历史

MySQL架构

MySQL采用的是分层架构：上层是Server层，下层是存储引擎层。

MySQL服务器逻辑架构图

这里讲一下一条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上的索引

三星索引

索引将相关记录放到一起则获得一星；
索引中的数据顺序和查找中的排列顺序一致则获得两星；
如果索引中的列包含了查询中需要的全部列则获得三星；

最左匹配原则

回想一下上文的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等字符串类型的变量，可以采用前缀索引，类似于最左匹配原则。

多列索引

一个常见的错误：为每个列创建独立的索引，或按照错误的顺序建立多列索引。参考：最左匹配原则。
不需要排序和分组时，将选择性高的放在索引的最前列。