共计 4839 个字符,预计需要花费 13 分钟才能阅读完成。
《高性能 Mysql》学习笔记(一)
前言
Mysql 的品质比拟好的书其实并不是很多,所以能够说是看一本少一本,这本书也算是学习 MYSQL 必看的一本书,当然非常厚,尽管版本很老然而讲述的内容都会非常实用的,对于学习 MYSQL 的人能够说是一本必读的进阶好书。
最初,这篇读书笔记是整顿集体以前自学的时候看书做的笔记,做的非常毛糙 =-=,当初来看很多笔记记得过于根底了。另外过后很多都是截图的,很多都是图片 HHHH(流量观看谨慎)。
MySQL 逻辑架构图
尽管看上去比拟复旧,然而挺经典的。
小贴士:
存储引擎不会去解析 sql,不同存储引擎不会互相通信,只是简略响应下层申请(InnoDB 引擎除外,会解析外键定义,因为 mysql 服务器自身没有实现该性能)
连贯治理和安全性
每一个连贯都是一个独自线程,服务器会对连贯 缓存 而不是创立或者销毁线程
优化与执行
执行流程:
- 解析查问
- 创立数据结构(解析树),优化(重写查问,读表程序优化,抉择索引等)
- 尝试查问缓存(SELECT)
- 返回后果
并发管制
目标:外部领有锁机制避免数据毁坏
共享锁和排他锁(读锁和写锁)
- 读锁是共享的,互相不阻塞
- 写锁是排他的,一个写锁会阻止其余读锁和写锁
锁粒度
进步共享资源并发性就是让锁更有选择性,让锁只监控局部数据
记住:任何时候,给定资源下,锁定数据量越少,并发水平越高,只有互相不发生冲突即可
mysql 提供多个存储引擎反对丰盛的锁策略
表锁 (table lock)
表锁是 msyql 中最根本的锁策略,开销最小的策略,这种形式相似邮箱的加锁机制:会锁定整张表,用户拜访时候,对表进行写操作,须要优先取得写锁,会阻塞其余用户读写操作,只有没有写锁时候,其余用户能力取得读锁,读锁之间是不互相阻塞的,特定场景表锁能够有良好性能。
注意事项:
- 写锁 比读锁 有更高的优先级,写锁有可能会插入到一个读锁的后面,然而读锁不能插入到写锁队列后面
- 服务器会为 alter table 等语句默认应用表锁而不是依据引擎决定(尽管存储引擎才是真正干活的)
行级锁 (row lock)
该锁能够 最大水平反对 并发解决(与此同时带来微小锁开销),InnoDB和XtraDB,实现了行级锁,行级锁只在存储层也就是存储引擎实现,而 <font color=’red’>mysql 服务层没有实现 </font>
事务
事务的 ACID
-
A(atomicity)原子性
- 一个事务是一个不可分割的单位,事务中的所有操作,要么全实现,要么全不实现,任何一个操作的失败,都会回滚到事务执行之前的状态。
-
C(consistency)一致性
- 事务完结后,零碎状态是统一的。即,在并发操作时,零碎的状态也要和串行执行事务时一样。
-
I(isolation)隔离性
- 并发执行的事务之间,无奈看到彼此的零碎状态。
-
D(durability)持续性
- 在事务实现后,事务对数据库的操作会被长久保留在数据库中,不会被回滚。
事务应用与否依据理论业务状况而定(甚至能够不应用事务,而是应用 sql 进行肯定的安全措施),如何抉择适合的 mysql 引擎来解决问题可能事务自身更加重要。
隔离级别
死锁
如果多个线程同时更改同一行数据,你们两个线程相互期待对面的锁,造成死锁
解决办法:
- 例如:InnoDB 检测死循环依赖,并且立刻返回一个谬误(死锁会造成慢查问)。
- 查问工夫达到锁期待超时设定工夫之后放弃锁申请。
- InnoDB 目前(5.1)解决办法:将持有 <font color=’red’> 起码行级排他锁 </font> 的事务进行回滚。
- 大多数时候因为数据抵触,有时候的确是因为存储引擎形式引起的!!!
只有局部或者齐全回滚一个事务能力突破死锁,事务性零碎无奈防止。大多数时候只须要从新执行死锁事务即可
事务日志
事务日志采纳追加形式,因而 I / O 的耗费比拟小,内存批改数据后盾缓缓刷会磁盘,目前大多数存储引擎都是这样实现被称为:预写式日志
mysql 中的事务
- innoDB
- NDB cluster
主动提交
默认状况下咱们所写的 SQL 默认都是主动提交的,也就是说在执行的时候 MYSQL 都会给咱们自定加上一条,COMMIT 语句,也就是主动提交事务,咱们能够应用 SHOW VARIABLES LIKE 'AUTOCOMMIT'
语句查看是否开启主动提交。
<font color=’red’> 切记:查找对应版本会产生 AUTO_COMMIT 所有语句 </font>
设置隔离级别
命令如下:
> SET SESSION TRASACTION ISOLATTION LEVEL READ COMMITTED
mysql 能够辨认 4 个 ansi 隔离级别,innodb 引擎也反对
混合应用存储引擎
mysql 服务层不治理事务,事务上层存储引擎实现,同一个事务应用多种存储引擎不牢靠
mysql 对非事务型表不会有提醒!!!!
mysql 对非事务型表不会有提醒!!!!
mysql 对非事务型表不会有提醒!!!!
暗藏和显式锁定
记住上面的两条特点:
- INNODB 应用两阶段锁定协定,锁只有在执行提交或者回滚才会开释
- INNODB 会依据隔离级别主动加锁
innodb 反对显示的加锁如下:
SELECT ... FROM IN SHARE MODE
SELECT ... FOR UPDATE
留神:这不是 sql 标准,而是 MYSQL 本人减少的语法反对
mysql 中的 lock 和 unlock tables 语句和存储引擎无关,而是在<font color=’red’> 服务层 </font> 实现,不能用来代替事务性存储引擎,有其余用处
倡议:除了事务中禁用 autocommit,能够应用 lock tables 之外,其余任何时候不要显式执行 lock tables,不论是什么存储引擎
多版本并发管制(mvcc)
mvcc 实现:保留数据在某个工夫点的快照实现,记住:依据事务开始工夫不同,每个事务对同一张表,同一时刻看到的数据可能是不一样的,这里能够引申:乐观锁 和乐观锁
innodb 的 mvvc
实现原理:通过在每行记录前面保留两个暗藏的列实现
- 一个列保留行创立工夫
- 另一个保留过期工夫(删除工夫)
事务开始时候零碎版本号(每个新事务都会递增版本号)作为事务版本号,和查问到记录的版本号比拟
REPEATABLE READ 隔离级别操作
MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别工作。
mysql 存储引擎
- 建表时候,会在
mytable.frm
中定义表定义。 - 表的定义是在 服务层。
- 不同零碎存储模式不一样(数据和索引)。
应用 show table status
命令 显示表的相干信息,例如 show table status like 'user' \G
,<font color=’red’>mysql5.1</font> 中的 innodb plugin
反对一些新个性(BLOB 存储形式应用),mysql5.1 肯定要应用 innodb plugin,比 旧 innodb 要好得多
mysql 5.5 之后 innodb plugin 才替换掉旧的 Innodb
Innodb 概览
mysql4.1 之后新个性
- innodb 能够将每个表的数据和索引放在独自文件当中
- innodb 能够将裸设施作为存储介质
Innodb 特点
- 应用 mvcc 反对高并发
- 实现了四个规范隔离级别(默认级别为 REPEATABLE READ(可反复读))。
- 应用间隙锁(next-key locking) 策略避免幻读的呈现。
- 间隙锁不仅锁定查问行,还对索引进行锁定。
- INNODB 基于 聚簇索引 建设。
- 存储格局是平台独立的,意味着能够跨平台应用。
- 外部进行优化,可预测性预读 ,能够主动在 内存 当中 创立 hash 索引 减速读操作 自适应哈希索引,减速操作的退出缓冲区。
- <font color=’red’> 浏览官网文档 ”InnoDB 事务模型和锁 ”</font> 理解更多内容。
- innodb 通过一些机制和工具实现真正的 热备份。
聚簇索引 对于主键查问有十分高的性能,不过二级索引中必须蕴含主键列,如果主键列很大,其余所有索引都会很大,
Myisam 存储引擎
mysql5.1 之前默认应用 MyISAM 作为存储引擎
特点:
- 全文索引,压缩,空间函数
- 不反对事务和行级锁
- 解体之后无奈平安复原
存储:
将表存储在两个文件当中
- 数据文件 (.MYD)
- 索引文件 (.MYI)
- 表反对蕴含动静或者动态(长度固定)行,mysql 依据表定义决定存储模式
- Mysql5 当中,如果是变长行,只能解决 256TB 数据
- 批改 Myisam 表指针长度,批改表 max_rows 和 avg_row_length 选项实现
myisam 个性
- 加锁与并发:对 整张表 加锁,而不是针对行。
- myisam 表,mysql 能够手工或者主动检查和修复操作(然而效率较低)。
- 索引个性:即便是 BLOB 和 TEXT 等长字段,也能够基于 500 个字符创立。
- myisam 反对 全文索引,基于分词创立索引。
- 提早更新索引键。
myisam 压缩表
如果表中数据不再批改,能够应用 myisam 压缩表,作用是缩小磁盘 i /o,进步查问性能。
myisam 性能问题
最典型的性能问题是 表锁 的问题
mysql 内建其余存储引擎
Archive 引擎
- 只反对 insert 和 sleect,mysql5.1 之前不反对索引。
- 适宜日志和数据采集类利用。
- 反对行级锁和专用缓冲区,实现高并发插入。
- 不是事务性引擎,只对通知插入和压缩做了优化的简略引擎。
Blackhole 引擎
- 无存储,抛弃所有插入数据。然而服务器会记录 blackhole 表的日志。
- 简略的日志引擎。
CVS 引擎
- 将一般 cvs 作为 mysql 表处理,不反对索引。
- cvs 引擎能够作为 数据交换的机制(excel 表格的转换)。
Federated 引擎
- 拜访其余 mysql 服务器代理,然而默认是 禁用 的。
Memory 引擎
- 能够快速访问数据 应用 Memory 表。
- 所有数据都是在内存当中。
- 每行长度固定。
- 并发性能较低。
-
作用:
- 查找或者映射表。
- 缓存周期性聚合数据。
- 保留数据产生的两头数据。
- 如果 mysql 查问应用长期表保留后果,你们外部应用就是 Memory 表,然而如果数据量较大,就会转为 myisam 表
Merga 引擎
- myisam 引擎变种,多个 myisam 表合并变种
- 分区性能实现后,被放弃
-
NDB 集群引擎
- 用于 mysql 集群
第三方存储引擎
OLTP 类引擎
- XtraDB 引擎
- PBXT 引擎
- TokuDB 引擎
如何抉择引擎
除非应用到了 innodb 不具备的个性,并且无奈代替,否则优先选择 innodb
次要对待四点
- 事务
- 备份
- 解体复原
- 特有个性
集体认识:
- 对于极高的插入数据要求时候,能够应用 myisam 或者 archieve
- 如果不晓得其余引擎的个性还是倡议 INNODB
测试解体数据恢复问题就是模仿电源断电!!!
数据表引擎转换
1. 间接转换
mysql> ALTER TABLE mytable ENGINE = InnoDB
这种形式性能很低,而且会加锁
2. 应用导入导出的办法
导入与导出:应用 msyql 工具导出 sql 语句而后手动批改引擎
3. 创立与查问
mysql > create table innodb_table like myisam table;
mysql > alter table innodb_table engine = InnoDB;
mysql > insert into innodb_table select * from myisam_table
4. 数据量很大的话能够分批解决
start transaction;
insert into innodb_table select * from myisam_table
where id between x and y;
insert into innodb_table select * from myisam_table
where id between x and y;
insert into innodb_table select * from myisam_table
where id between x and y;
.......
commit
总结
第一篇读书笔记次要介绍了和 MYSQL 的存储引擎的重点内容,以及简略介绍 MYSQL 的事务相干内容,在前言也说过,内容比拟根底并且因为以前偷懒很多都是截图书上的内容 =-=。