高性能MySQL第一章笔记

1.1MySQL逻辑架构

最上层的服务：客户端。
第二层架构：MySQL的核心服务功能，包括查询解析、分析、优化、缓存一级所有的内置函数。实现存储过程、触发器、视图等。
第三次：包含眊存储引擎，负责MySQL中数据的存储和提取。

优化：优化器并不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。

1.2并发控制

并发控制：通过实现一个由两种类型的锁组成的锁心痛来解决问题。这两种类型的锁通常被称为共享锁和排他锁，也叫读锁和写锁。
读锁：是共享的，多个用户在同一时刻读取同一个资源，而互不干扰。
写锁：是排他的，一个写锁会阻塞其他的写锁和读锁，能确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

使用锁更理想的方式就是只对会修改的数据片进行精确的锁定。但各种锁的操作也会消耗资源，因此锁不能太多。
锁策略：在锁的开销和数据的安全性之间寻求平衡。
每种MySQL存储引擎都可以实现自己的锁策略和锁力度。
两种重要的锁策略：表锁和行级锁。
表锁：是MySQL中最基本的锁策略，并且是开销最小的策略。进行写操作时获得写锁，读时获得读锁，写锁比读锁有更高的优先级，写锁可以插入到锁队列中读锁的前面，读锁不能插入到写锁的前面。
行级锁：可以最大程度地支持并发处理（同时也带来了最大的锁开销）。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。

1.3事务

事务就是一组原子性的SQL查询。事务内的语句，要么全部执行成功，要么全部执行失败。
可以用STARTTRANSACTION语句开始一个事务，然后要么使用COMMIT提交事务将修改的数据持久保留，要么使用ROLLBACK撤销所有的修改。
ACID表示原子性、一致性、隔离性和持久性。
对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎，可以获得更高的性能。即使存储引擎不支持事务，也可以通过LOCKTABLES语句为应用提供一定程度的保护。

隔离级别：四种隔离级别，较低级别的隔离通常可以执行更高的并发，系统的开销也更低。
READ UNCOMMITTED：即使没有提交，对其他事务也都是可见的。
READ COMMITTED：一个事务开始时，只能“看见”已经提交的事务所做的修改。不可重复读。
REPEATABLEREAD：该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。可重复读，是MySQL默认事务隔离级别。
SERIALIZABLE：是最高的隔离级别，会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

死锁：是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶劣循环的现象。两个事务都等待对方释放锁，同时又持有对方锁需要的锁，则陷入死循环。
InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。
死锁的产生有双重原因：有些因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的。
只有部分或者完全回滚其中一个事务，才能打破死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

事务日志：可以帮助提高事务的效率。

MySQL提供了两种事务型的存储引擎：InnoDB和NDB Cluster。
MySQL默认采用自动提交（AUTOCOMMIT）模式，设置AUTOCOMMIT变量来启用或者禁用自动提交模式。1或者ON表示启用，0或者OFF表示禁用。
MySQL可以通过执行SET TRANSACTION ISOLATIONLEVEL命令来设置隔离级别，会在下一个事务开始的时候生效。
MySQL能够识别所有的4个ANSI隔离级别，InnoDB引擎也支持所有的隔离级别。
在同一个事务中，使用多种存储引擎是不可靠的。
InnoDB采用的是两阶段锁定协议：隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁；InnoDB也支持通过特定的语句进行显式锁定。
MySQL也支持LOCK TABLES和UNLOCKTABLES语句，这是在服务器层实现的，和存储引擎无关，不能代替事务处理。

1.4多版本并发控制

多版本并发控制（MVCC）：可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。MVCC的实现是通过保存数据在某个时间点的快照来实现的。

1.5MySQL的存储引擎

每个数据库保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。MySQL使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。表的定义是在MySQL服务层统一处理的。

InnoDB是MySQL的默认事务型引擎，用来处理大量的短期事务，短期事务大部分情况是正常提交的，很少会被回滚。
InnoDB的改进：可测量性、可扩展性、可配置化、性能、各种新特性和对Windows的支持等。
InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是REPEATABLEREAD（可重复读），并且通过间隙锁策略防止幻读的出现。
InnoDB表是基于聚簇索引建立的，聚簇索引对主键查询有很高的性能。不过它的二级索引中必须包含主键列，如果主键列很大的话，其他的所有索引都会很大。若表上的索引较多的话，主键应当尽可能的小。

MyISAM存储引擎不支持事务和行级锁，崩溃后无法安全恢复。
MyISAM会将表存储在两个文件中：数据文件和索引文件，以.MYD和.MYI为扩展名。
MyISAM表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。

MySQL内建的其他存储引擎：Archive引擎、Blackhole引擎、CSV引擎、Federated引擎、Memory引擎、Merge引擎、NDB集群引擎。

第三方引擎中，Percona的XtraDB存储引擎是基于InnoDB引擎的一个改进版本，可以作为InnoDB的一个完全的替代产品。
Infobright是最有名的面向列的存储引擎。

在选择合适的引擎时，除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎。
除非万不得已，否则建议不要混合使用多种引擎。
如果应用需要事务支持，那么选择InnoDB（或者XtraDB），不需要事务并且主要是SELECT和INSERT操作，那么MyISAM是不错的选择。一般日志型的应用比较符合。
如果可以定期地关闭服务器来执行备份，那么备份的因素可以忽略。如果需要在线热备份，那么选择InnoDB就是基本的要求。
MyISAM崩溃后发生损坏的概率比InnoDB要高很多，而且恢复速度也要慢。
如果一个存储引擎拥有一些关键的特性，同时却又缺乏一些必要的特性，那么有时候不得不做折中的考虑，或者在架构设计上做一些取舍。某些存储引擎无法直接支持的特性，有时候通过变通也可以满足需求。
日志型应用：这一类应用的插入速度有很高的要求，MyISAM或者Archive存储引擎对这类应用比较合适，因为他们开销低，而且插入速度非常快。
要对记录的日志做分析报表：①利用MySQL内置的复制方案将数据复制一份到备库。②在日志记录表的名字中包含年和月的信息，比如web_log_2012_01，这样可以在已经没有插入操作的历史表上做频繁的查询操作，而不会干扰到最新的当前表上的插入操作。
只读或者大部分情况下只读的表：建议采用InnoDB。
订单处理：支持事务是必要选择。另外一个重要的考虑点是存储引擎对外键的支持情况。InnoDB是订单处理类应用的最佳选择。
CD-ROM应用：可以考虑使用MyISAM表或者MyISAM压缩表，这样表之间可以隔离并且可以在不同介质上相互拷贝。压缩表比未压缩的表要节约很多空间，但压缩表是只读的。如果数据放到只读介质的场景下，就采用压缩表。
大数据量：采用InnoDB数据库。10TB以上的级别可能就需要建立数据仓库。Infobright是MySQL数据仓库最成功的解决方案。也有一些大数据库适合TokuDB。

转换表的引擎有3中方法。
1.ALTER TABLE：用语句ALTER TABLE mytable ENGINE = InnoDB;需要执行很长时间，在繁忙的表上执行此操作要特别小心。如果将一张InnoDB表转换为MyISAM，然后再转回InnoDB，原InnoDB表上所有的外键将丢失。
2.导入与导出：可以使用mysqldump工具将数据导出文件，然后修改文件中CREATETABLE语句的存储引擎选项，注意同时修改表名。
3.创建与查询：先创建一个新的存储引擎的表，然后利用INSERT…SELECT语法来导数据。数据量很大则可以考虑做分批处理，针对每一段数据执行事务提交操作。

1.6MySQL时间线

采用了InnoDBplugin的版本，在高并发的时候性能明显更好，可以说InnoDBplugin的扩展性更好。新的版本在单线程的时候比旧版本更差。