乐趣区

关于mysql:高性能Mysql学习笔记一

jiezi

《高性能 Mysql》学习笔记（一）

前言

​ Mysql 的品质比拟好的书其实并不是很多，所以能够说是看一本少一本，这本书也算是学习 MYSQL 必看的一本书，当然非常厚，尽管版本很老然而讲述的内容都会非常实用的，对于学习 MYSQL 的人能够说是一本必读的进阶好书。

​ 最初，这篇读书笔记是整顿集体以前自学的时候看书做的笔记，做的非常毛糙 =-=，当初来看很多笔记记得过于根底了。另外过后很多都是截图的，很多都是图片 HHHH（流量观看谨慎）。

MySQL 逻辑架构图

​ 尽管看上去比拟复旧，然而挺经典的。

小贴士：

存储引擎不会去解析 sql，不同存储引擎不会互相通信，只是简略响应下层申请（InnoDB 引擎除外，会解析外键定义，因为 mysql 服务器自身没有实现该性能）

连贯治理和安全性

​ 每一个连贯都是一个独自线程，服务器会对连贯 缓存 而不是创立或者销毁线程

优化与执行

执行流程：

  1. 解析查问
  2. 创立数据结构（解析树），优化（重写查问，读表程序优化，抉择索引等）
  3. 尝试查问缓存（SELECT）
  4. 返回后果

并发管制

​ 目标：外部领有锁机制避免数据毁坏

共享锁和排他锁（读锁和写锁）

  • 读锁是共享的，互相不阻塞
  • 写锁是排他的，一个写锁会阻止其余读锁和写锁

锁粒度

​ 进步共享资源并发性就是让锁更有选择性，让锁只监控局部数据

​ 记住：任何时候，给定资源下，锁定数据量越少，并发水平越高，只有互相不发生冲突即可

​ mysql 提供多个存储引擎反对丰盛的锁策略

表锁 (table lock)

​ 表锁是 msyql 中最根本的锁策略，开销最小的策略，这种形式相似邮箱的加锁机制：会锁定整张表，用户拜访时候，对表进行写操作，须要优先取得写锁，会阻塞其余用户读写操作，只有没有写锁时候，其余用户能力取得读锁，读锁之间是不互相阻塞的，特定场景表锁能够有良好性能。

​ 注意事项：

  1. 写锁 比读锁 有更高的优先级，写锁有可能会插入到一个读锁的后面，然而读锁不能插入到写锁队列后面
  2. 服务器会为 alter table 等语句默认应用表锁而不是依据引擎决定（尽管存储引擎才是真正干活的）

行级锁 (row lock)

​ 该锁能够 最大水平反对 并发解决（与此同时带来微小锁开销），InnoDB和XtraDB，实现了行级锁，行级锁只在存储层也就是存储引擎实现，而 <font color=’red’>mysql 服务层没有实现 </font>

事务

事务的 ACID

  • A（atomicity）原子性

    • 一个事务是一个不可分割的单位，事务中的所有操作，要么全实现，要么全不实现，任何一个操作的失败，都会回滚到事务执行之前的状态。

  • C（consistency）一致性

    • 事务完结后，零碎状态是统一的。即，在并发操作时，零碎的状态也要和串行执行事务时一样。

  • I（isolation）隔离性

    • 并发执行的事务之间，无奈看到彼此的零碎状态。

  • D（durability）持续性

    • 在事务实现后，事务对数据库的操作会被长久保留在数据库中，不会被回滚。

​ 事务应用与否依据理论业务状况而定（甚至能够不应用事务，而是应用 sql 进行肯定的安全措施），如何抉择适合的 mysql 引擎来解决问题可能事务自身更加重要。

隔离级别

死锁

​ 如果多个线程同时更改同一行数据，你们两个线程相互期待对面的锁，造成死锁

解决办法：

  1. 例如：InnoDB 检测死循环依赖，并且立刻返回一个谬误（死锁会造成慢查问）。
  2. 查问工夫达到锁期待超时设定工夫之后放弃锁申请。
  3. InnoDB 目前（5.1）解决办法：将持有 <font color=’red’> 起码行级排他锁 </font> 的事务进行回滚。
  4. 大多数时候因为数据抵触，有时候的确是因为存储引擎形式引起的！！！

只有局部或者齐全回滚一个事务能力突破死锁，事务性零碎无奈防止。大多数时候只须要从新执行死锁事务即可

事务日志

​ 事务日志采纳追加形式，因而 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 反对显示的加锁如下：

  1. SELECT ... FROM IN SHARE MODE
  2. 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 引擎

  1. 只反对 insert 和 sleect，mysql5.1 之前不反对索引。
  2. 适宜日志和数据采集类利用。
  3. 反对行级锁和专用缓冲区，实现高并发插入。
  4. 不是事务性引擎，只对通知插入和压缩做了优化的简略引擎。

Blackhole 引擎

  1. 无存储，抛弃所有插入数据。然而服务器会记录 blackhole 表的日志。
  2. 简略的日志引擎。

CVS 引擎

  1. 将一般 cvs 作为 mysql 表处理，不反对索引。
  2. cvs 引擎能够作为 数据交换的机制（excel 表格的转换）。

Federated 引擎

  1. 拜访其余 mysql 服务器代理，然而默认是 禁用 的。

Memory 引擎

  1. 能够快速访问数据 应用 Memory 表。
  2. 所有数据都是在内存当中。
  3. 每行长度固定。
  4. 并发性能较低。

  5. 作用：

    1. 查找或者映射表。
    2. 缓存周期性聚合数据。
    3. 保留数据产生的两头数据。
  6. 如果 mysql 查问应用长期表保留后果，你们外部应用就是 Memory 表，然而如果数据量较大，就会转为 myisam 表

Merga 引擎

  1. myisam 引擎变种，多个 myisam 表合并变种
  2. 分区性能实现后，被放弃

  3. NDB 集群引擎

    1. 用于 mysql 集群

第三方存储引擎

OLTP 类引擎

  1. XtraDB 引擎
  2. PBXT 引擎
  3. TokuDB 引擎

如何抉择引擎

​ 除非应用到了 innodb 不具备的个性，并且无奈代替，否则优先选择 innodb

次要对待四点

  1. 事务
  2. 备份
  3. 解体复原
  4. 特有个性

集体认识：

  1. 对于极高的插入数据要求时候，能够应用 myisam 或者 archieve
  2. 如果不晓得其余引擎的个性还是倡议 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 的事务相干内容，在前言也说过，内容比拟根底并且因为以前偷懒很多都是截图书上的内容 =-=。

​

退出移动版