《高性能MySQL》 夯实之路(一) MySQL架构与历史

---

《高性能MySQL》 夯实之路(一) MySQL架构与历史

---

一.MySQL逻辑架构

三层逻辑架构:
最上层：服务器
中间层： 线程连接，查询缓存，解析器，优化器
最底层：存储引擎

1. 连接管理与安全性：

• 每个客户端连接都会在服务器进程中拥有一个线程
• 当客户端连接到MySQL时，需要通过用户名，密码进行验证，使用SSL连接

2. 优化与执行:

• MySQL会解析查询，然后根据查询建立解析树数据结构，然后对其优化。
• 对于select语句，在解析查询前，服务器会先检查缓存，优先使用缓存

---

二.并发控制

服务器层和存储引擎层

1.读写锁

• 读锁，也称Shared Lock共享锁
• 写锁，也称Exclusive Lock排他锁

读写锁是一对互斥锁，分为读锁和写锁。读锁和写锁互斥，让一个线程在进行读操作时，不允许其他线程的写操作，但是不影响其他线程的读操作；当一个线程在进行写操作时，不允许任何线程进行读操作或者写操作。读写锁用的是同一个 Sycn 同步器，因此等待队列、state 等也是同一个

2. 锁粒度

让锁定对象更有选择性，尽量只锁住需要修改的部分

• 表锁：最基本的锁策略，开销也最小。
• 行级锁：可以最大程度支持并发，但开销也最大。

---

三.事务

1. 隔离级别

级别越低，可支持的并发越高

• Read UnCommited：未提交的修改，也对其他事务可见，发生了就被称为脏读。
• Read Commited：只要提交，就可以读取，会发生不可重复读，即两次读取同一个数据却不一样。
• Repeatable Read：MySQL默认隔离级别，解决了脏读不可重复读。但不能解决幻读：同样的select出现额外的数据行，需要通过MVCC解决。
• Serializable：每一行都加上锁，资源消耗非常严重。只有需要确保一致性，且没有并发时使用。

2. 死锁

• 两个事务互相用锁占用对方的资源，就会导致双方都不释放锁。
• InnoDB解决：立即返回一个错误，将持有最少行数排他锁的事务进行回滚

3. 事务日志

存储引擎在修改数据时，只用修改其内存拷贝，再把该行为记录到事务日志去。

4. MySQL中的事务

• 自动提交：默认自动采用
• 事务型存储引擎：InnoDB
• 非事务型存储引擎：MyISAM

---

四.多版本并发控制 MVCC

1. 可以看作行级锁的一个变种，但很多情况下避免了加锁操作，大多实现为非阻塞的读锁，以及锁定必要行的行级锁
2. InnoDB的MVCC，通过每行记录后面保存两个隐藏的列，即行的创建时间和行的过期/删除时间(这里是版本号)
3. MVCC只在Repeatable Read和Read Commited两个隔离级别下工作。

---

五.MySQL的存储引擎

1. InnoDB存储引擎

• 将数据和索引都放在.ibd文件里，为聚集索引(主键构建)，速度快于非聚集索引MyISAM，不需要回表
• 采用间隙锁，不仅锁定需要查询的行，还锁定之间的间隙，防止幻行插入，出现幻读
• 因为基于聚集索引，所以主键查询有很高的性能，但二级索引查询中必须包含主键，查找到后再通过主键查询找到数据。
• 能在内存中创建哈希索引，做到自适应哈希。

2. MyISAM存储引擎

• MySQL5.1之前的默认存储引擎，支持全文索引，压缩，空间函数
• 不支持事务，不支持崩溃后的安全恢复
• 适合只读，可以接受repair的表
• 会将数据存储在.MYD文件里，将索引存储在.MYI里，为非聚集索引(非主键构建)
• MyISAM表可以包含动态行和静态行，但只会对整张表加锁
• 可以延迟更新索引键
• 如果数据在导入之后不需要再修改，那么可以使用压缩表，可以极大地减少磁盘空间占用，减少磁盘IO，可以使用索引，但不能修改索引

3.MySQL内建的其他存储引擎

• Archive引擎：只支持select和insert操作
• Blackhole引擎：没有任何存储机制，不做任何保存。
• CSV引擎：可以将CSV表作为MySQL表来处理
• Federated引擎：将sql语句传入到远程服务器执行，再将结果传回
• Memory引擎：适合需要快速访问数据，且数据不会更改，丢失也无所谓，所有数据都在内存中，不会消耗磁盘资源
• Merge引擎：由多个MyISAM合并而来的虚拟表
• NDB引擎：集群引擎

4.选择合适的存储引擎

大部分情况下用InnoDB，除非要用到InnoDB没有的特性

5.转换表的引擎

ALTER TABLE mytable ENGINE=InnoDB;