《高性能MySQL》

　　《高性能MySQL》（第3版）讲解MySQL如何工作，为什么如此工作？

　　MySQL系统架构、设计应用技巧、SQL语句优化、服务器性能调优、系统配置管理和安全设置、监控分析，以及复制、扩展和备份/还原等主题

　　schema设计

　　索引优化

　　查询优化

　　事务

　　【扩展应用】集群：复制-备份-恢复，负载均衡，高可用-高可扩展性，容灾技术

　　【知识点】

　　InnoDB存储引擎

　　备份和恢复的策略

　　云端的MySQL数据库

　　高可用的冗余系统

　　高可扩展性设计

　　第1 章 mysql 架构与历史 1

　　存储引擎架构：这种架构的设计将查询处理及其它系统任务 和 数据的存储/提取相分离。

　　处理和存储分离的设计：可以在使用时根据性能、特性、其它需求来选择数据存储方式。

　　——MySQL的服务器架构。

　　——各种存储引擎之间的主要区别，以及这些区别的重要性。

　　1.1 mysql 逻辑架构 1

　　

 

　　——查询解析，分析，优化，缓存，内置函数，跨存储引擎的功能：存储过程，触发器，视图等。

　　——存储引擎负责数据的存储和提取。

　　——存储引擎API包含几十个底层函数，用于执行开始一个事务，根据主键提取一行记录等。

　　——但存储引擎不会去解析SQL。存储引擎只是简单地响应上层服务器的请求。

　　1.1.1 连接管理与安全性2

　　——服务器负责缓存线程，用于创建连接。

　　——线程池：使用池中少量的线程来服务大量的连接。

　　1.1.2 优化与执行 3

　　——解析查询，创建内部数据结构：解析树。

　　优化：重写查询，决定表的读取顺序，选择合适的索引等。

　　——用户通过特殊的关键字提示（hint）优化器，影响它的决策过程。

　　——优化器可以解释优化过程中的各个因素。便于用户重构查询和schema，修改相关配置。（第6章）

　　——优化器并不关心表使用的是什么存储引擎。

　　但优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息。

　　1.2 并发控制 3

　　——服务器层 和 存储引擎层。

　　【MySQL如何控制并发读写】

　　1.2.1 读写锁 4

　　——两种类型的锁组成的锁系统。

　　共享锁：读锁【锁的具体实现】

　　排它锁：写锁

　　MySQL锁的内部管理都是透明的。

　　1.2.2 锁粒度 4

　　【让锁定对象更有选择性】

　　——尽量只锁定只需要修改的部分数据。只对会修改的数据进行精确的锁定。

　　锁定的数量越少，系统的并发程度越高。

　　——加锁也需要消耗资源。获得锁，检查锁是否已经解除，释放锁等都会增加系统的开销。

　　——锁策略：在锁的开销 和 数据的安全性 之间寻求平衡。

　　大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁，并以各种复杂的方式来实现。以便在锁比较多的情况下提供更好地性能。

　　——每种存储引擎实现自己的锁策略 和 锁粒度。

　　存储引擎的设计中，锁管理是个非常重要的决定。

　　【两种重要的锁策略】表锁+行级锁

　　table lock：开销最小的锁策略

　　行级锁：row lock，最大程度地支持并发处理，也带来最大的锁开销。

　　——行级锁只在存储引擎层实现，而MySQL服务器层没有实现。

　　服务器层完全不了解存储引擎中的锁实现。所有的存储引擎都以自己的方式显现了锁机制。

　　1.3 事务6

　　——一组原子性的SQL查询。一个独立的工作单元。

　　要么全部执行成功，否则全部失败。

　　【银行应用】

　　START TRANSACTION;

　　SELECT balance FORM checking WHERE  customer_id = 10233276;

　　UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;

　　UPDATE saving SET balance = balance + 200.00 WHERE customer_id = 10233276;

　　COMMIT;

　　原子性

　　一致性：从一个一致性状态转化到另一个一致性状态。

　　隔离性：最终提交前，所做的修改对其他事务是不可见。

　　持久性

　　——用户可以根据业务是否需要事务处理，来选择合适的存储引擎。

　　1.3.1 隔离级别 8

　　1）未提交读（Read Uncommitted）（很少使用）：SELECT语句以非锁定方式被执行，所以有可能读到脏数据，隔离级别最低。

　　脏读：事务中的修改，即使没有提交，对其他事务也是可见的。

　　2）提交读（Read Committed）：只能读取到已经提交的数据。即解决了脏读，但未解决不可重复读。

　　两次执行同样的查询，可能会得到不一样的结果。

　　3）可重复读（Repeated Read）（MySQL的默认事务隔离级别）：在同一个事务内的查询都是事务开始时刻一致的，InnoDB的默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读。

　　保证在同一个事务中多次读取同样的记录的结果是一致的。

　　幻读：另外一个事务插入了新的记录。

　　4）串行读（Serializable）：（强制事务串行执行）完全的串行化读，所有SELECT语句都被隐式的转换成SELECT ... LOCK IN SHARE MODE，即读取使用表级共享锁，读写相互都会阻塞。隔离级别最高。

　　

 

　　1.3.2 死锁 9

　　当多个事务尝试以不同的顺序锁定资源时，就可能会产生死锁。

　　死锁检测机制。死锁超时机制。

　　原始的方法：放弃锁请求。

　　InnoDB处理方法：将持有最少 行级排它锁 的事务进行回滚。

　　锁的行为和顺序和存储引擎相关。

　　【部分或者完全回滚其中一个事务】

　　1.3.3 事务日志 10

　　使用事务日志，存储引擎在修改表的数据时，只需要修改其内存中的拷贝。再把该修改行为记录到持久在硬盘上的事务日志中。而不用每次都将修改的数据本身持久到磁盘。

　　预写式日志：修改数据需要写两次磁盘。

　　1.3.4 mysql 中的事务 10

　　两种事务型存储引擎：InnoDB和NDB Cluster。

　　MySQL默认采用自动提交：AUTOCOMMIT。

　　#SHOW VARIABLES LIKE 'AUTOCOMMIT';

　　

 

　　还有一些命令，在执行前会强制执行COMMIT，提交当前的活动事务。比如会导致大量数据改变的操作。ALTER TABLE,LOCK TABLES。

　　设置隔离级别：#SET TRANSACTION ISOLATION LEVEL。新的隔离级别在下一个事务开始时生效。

　　可以在配置文件设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别。#SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED.

　　【在事务中混合使用存储引擎】

　　MySQL服务器层不管理事务。事务是由下层的存储引擎实现的。所以在同一个事务中使用多种存储引擎是不可靠的。

　　为每张表选择合适的存储引擎很重要。

　　因为大多数情况，对非事务型表的操作都不会有提示。

　　【隐式和显式锁定】

　　InnoDB采用“两阶段锁定协议”。

　　InnoDB也支持显式锁。#SELECT ... LOCK IN SHARE MODE #SELECT ... FOR UPDATE

　　InnoDB的行级锁 比 LOCK TABLES工作地更好。尽量不要显示执行 LOCK TABLES。

　　1.4 多版本并发控制 12

　　除了行级锁，同时实现了多版本并发控制。行级锁的变种。很多情况下避免了加锁操作。

　　大都实现了非阻塞读，写操作也只锁定必要的行。

　　MVCC的实现，是通过保存数据在某个时间点的快照来实现的。

　　不管需要执行多长时间，每个事务看到的数据都是一致的。

　　根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

　　——乐观并发控制和悲观并发控制。

　　

 

　　

 

　　1.5 mysql 的存储引擎 13

　　文件系统中，数据库schema，保存为数据目录下的一个子目录。

　　创建表时，MySQL在数据库子目录下创建一个和表同名的frm文件。大小写敏感和具体的平台密切相关。

　　#SHOW TABLE STATUS LIKE 'TABLE_NAME';

　　查询表的信息：

　　表名，表的存储引擎类型，行的格式，表中的行数，平均每行包含的字节数，表数据的大小，表数据的最大容量，索引的大小，已分配但目前没有使用的空间，下一个AUTO_INCREAMENT的值。表的创建时间，表数据的最后修改时间。

　　1.5.1 innodb 存储引擎 16

　　被设计用来处理大量短期事务。

　　

 

　　InnoDB的数据存储在表空间（tablespace）中。可以将每个表的数据和索引放在单独的文件中。

　　InnoDB采用MVCC来支持高并发。并且实现四个标准的隔离级别。默认级别是可重复读（REPEATABLE READ），并且通过间隙锁（next-key locking）策略防止幻读的出现。

　　间隙锁不仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入。

　　

 

　　内部的优化：从磁盘读取数据时采用可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引，能够加速插入操作的插入缓冲区等。

　　【InnoDB事务模型和锁】

　　【InnoDB的MVCC架构带来的一些微妙和细节之处】

　　1.5.2 myisam 存储引擎 17

　　全文索引，压缩，空间函数GIS

　　不支持事务和行级锁。奔溃后无法安全恢复。

　　【MyISAM存储】数据文件.MYD和索引文件.MYI。

　　【加锁和并发】对整张表加锁，而不是针对行。（可以并发插入CONCURRENT INSERT）

　　【修复】执行表的恢复可能导致一些数据的丢失。

　　【索引特性】支持全文索引。基于分词创建的索引，可以支持复杂的查询。

　　【延迟更新索引键】每次修改执行完成后，不会立刻将修改的索引数据写入磁盘，而是会写到内存中键缓冲区。只有在清理键缓冲区或者关闭表时才会将对应的索引块写入到磁盘中。

　　【MyISAM压缩表】压缩表不能修改。

　　【性能问题】表锁的问题。

　　1.5.3 mysql 内建的其他存储引擎 19

　　Archive引擎：针对高速插入和压缩做了优化的简单引擎。

　　CVS引擎：可以作为一种数据交换的机制。

　　Memory引擎：需要快速访问数据。

　　1.5.4 第三方存储引擎 22

　　1.5.5 选择合适的引擎 24

　　建议不要混合使用多种存储引擎。（影响存储引擎层和服务器层的交互）混合存储对一致性备份和服务器参数配置都带来一些困难。

　　需要考虑的因素：

　　【事务】【备份】【奔溃恢复】

　　【各种类型的应用如何选择引擎】

　　1.5.6 转换表的引擎 27

　　ALTER TABLE mytable ENGINE = InnoDB;

　　1.6 mysql 时间线（timeline） 29

　　1.7 mysql 的开发模式 32

　　1.8 总结 33

　　分层架构：服务器层的服务和查询执行引擎 + 存储引擎。

　　存储引擎API最重要。