1 mysql架构和历史

1.3 事务 ACID

原子性：一个事务被视为不可分割的最小工作单元
一致性：数据库总是从一个一致性的状态转为另一个一致性的状态
隔离性：事务之间的隔离，后面会写到隔离级别
持久性：一旦事务提交，所做的修改会永久保存到数据库中

1.3.1 隔离级别

READ UNCOMMITTED
事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，被称为脏读。（写的时候别的事务可以读）
READ COMMITED
一个事务开始时，只能看见已经提交事务所做的修改。也叫不可重复读。（写的时候别人不能读了，但是读的时候别的事务可以写，在多次读的时候，其他事务修改了数据会导致2次读的结果不一致）
REPEATABLE READ
解决了上面问题，但可能会出现幻读（指的是某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，之前事务再读取该记录范围内时，会出现幻行。InnoDB通过多版本并发控制mvcc解决了幻读）
SERIALIZABLE
强制事务串行执行。他会给读取的每一行都加上锁，会导致大量超时和锁争用问题。

1.3.2 死锁

死锁是指2个或以上事务在同一资源相互占用，并请求锁定对方占用的资源，从而导致恶性循环。

#事务1
START TRANSACTION;
UPDATE Stockprice SET close=45.50 WHERE stock_id=4 and date='2002-0501';
UPDATE Stockprice SET close=19.80 WHERE stock_id=3 and date='2002-0502';
COMMIT;
#事务2
START TRANSACTION;
UPDATE Stockprice SET high=15.50 WHERE stock_id=3 and date='2002-0502';
UPDATE Stockprice SET high=47.50 WHERE stock_id=4 and date='2002-0501';
COMMIT;

碰巧2个事务都执行了第一条update语句，锁定了行数据，2个事务都尝试执行第二条update语句，却发现该行都被对方锁定了，陷入死循环。InnoDB目前处理方式是将持有最少行级排他锁的事务进行回滚。

1.4 多版本并发控制 MVCC

InnoDB是通过在每行记录的后面保存2个隐藏的列来实现。这2个列一个保存了行的创建时间，一个保存行的过期时间（删除时间）。存储的不是真的时间还是系统版本号。每开始一个新的事务，系统版本号就会自动递增。下面详解在REPEATABLE READ隔离级别下 MVCC的具体操作

SELECT
InnoDB会根据下面2个条件检查每行记录
- InnoDB只查找早于当前事务版本的数据行（行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前就存在的，要么是事务自身插入或修改过的
- 行的删除版本要么未定义，要么大于当前事务版本号。可以确保事务读取到的行在事务开始前还没有被删除。
INSERT
InnoDB为插入的每一行保存当前系统版本号作为行版本号
DELETE
InnoDB为删除的每一行保存当前系统版本号作为删除标识
UPDATE
InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识
MVCC只能在REPEATABLE READ和READ COMMITED下工作。缺点是每行记录需要额外的存储空间，需要做更多的行检查，以及额外一些维护工作。

1.5 存储引擎

1.5.1 InnoDB

InnoDB是mysql默认事务型引擎。
InnoDB数据存储在表空间中。mysql4.1之后InnoDB将每个表的数据和索引放在单独的文件中（意思也可以放在一个文件）
InnoDB用MVCC支持高并发，并实现了四个标准的隔离级别，并通过MVCC和间隙锁(next-key locking)策略解决幻读。
InnoDB表基于聚簇索引，后面章节会详解。

1.5.2 MyISAM

MyISAM不支持事务和行级锁（用的是表锁），崩溃后也无法恢复。

存储

MyISAM将表存储在2个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名
MyISAM的索引都是保存行的地址

5 创建高性能索引

B-Tree索引的抽象表示

当一个索引包含多个列时key(last_name, first_name)
索引只对下面类型查询有效：

全值匹配
全值匹配指的是和索引中的所有列进行匹配。比如查找last_name是ZHUANG,first_name是ALAN的人
匹配最左前缀
只使用索引的第一列，查找last_name是ZHUANG的人
匹配列前缀
匹配某一列的值的开头部分，查找last_name是ZH开头的人，要使用索引的第一列，不能匹配first_name是ZH开头的人
匹配范围值
第一列范围，查找last_name从ZHANG到ZHUANG的人。
精确匹配第一列并范围匹配另一列
查找last_name是ZHUANG，first_name是A开头的人。第一列必须全匹配第二列才可以范围匹配
只访问索引无需访问数据行

哈希索引

基于哈希表实现。对于每一行数据，存储引擎会对所有的索引列计算一个哈希码。哈希索引将所有的哈希码存储在引擎中，同时在哈希表中保存指向每个数据行的指针

哈希索引查找特别快，因为它自身只需要存储对应的哈希值，所以索引的结构十分紧凑。不过他也有以下限制：

哈希索引只包含索引和行指针，而不存储字段值。所以不能使用索引的值来避免读取行
哈希索引数据并不是按照索引值顺序排序的所以不能用于排序
哈希索引不支持索引列部分匹配，必须索引列全部内容来计算哈希码。
只支持等值查询不支持范围查询
访问哈希索引速度非常快除非很多哈希冲突
哈希冲突很多的时候，索引维护代价也很高

高性能的索引策略

5.3.1 独立的列

select actor_id FROM sakila.actor where actor_id+1= 5
这句查询语句会导致索引失效。我们应养成简化WHERE条件的习惯，始终将索引单独放在比较符号的一侧

5.3.2 前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。一个策略是前面提到过的模拟哈希索引。但有时候这样还不够，还可以做些什么呢？
通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。
SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt Desc LIMIT 10;

5.3.5 聚簇索引

当表有聚簇索引时，他的数据实际上存放在索引的叶子页中。聚簇表示数据行和相邻的键值紧凑的存储在一起。因为无法把数据存放在两个不同的地方，所以一个表只能有一个聚簇索引。

聚簇索引数据分布

InnoDB通过主键聚集数据，这也就是说图中被索引的列是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引，InnoDB只能聚集在用一个页面中的记录。包含相邻键值的页面可能会相距甚远。
聚簇索引会加快数据访问。不过也会带来问题：会导致二级索引比想象的大，因为在二级索引的叶子节点包含了引用行的主键列。二级索引访问需要两次索引查找。
二级索引中叶子结点保存的不是指向行的物理位置的指针而是行的主键值。这意味着通过二级索引查找行会先获得对应的主键值，然后再去聚簇索引找到对应的行。进行了2次B-tree查找。自适应哈希能够减少这样的重复工作（InnoDB会自动生成。）

InnoDB和MyISAM的数据分布对比

测试表数据.png

MyiISAM

MyISAM的主键索引和非主键索引长得一模一样。（最下面最黑的其实是数据的行号）

InnoDB

在InnoDB中，聚簇就是表，所以不像MyISAM那样需要独立的行存储。
聚簇索引叶子结点包含了主键值，事务id，用于事务和MVCC的回滚指针以及剩余列。
InnoDB二级索引叶子结点存储的是key（查找的二级索引的具体值）+主键值

二级索引.png

5.3.6 覆盖索引

如果一个索引包含或者是覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”（比如有一个表有二级索引key(name,age),且查询语句为SELECT name,age FROM t1;那么二级索引中叶子节点就包含了查询所需要的数据，就不需要拿着主键值再去聚簇索引中查找行数据了）

7 MySQL高级特性

7.2 视图

视图本身是一个虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，它返回的数据是从其他表生成的。
实现视图最简单的方法就是将select语句的结果存放到临时表中。当需要访问视图的时候，直接访问这个临时表就可以了。

CREATE VIEW Oceania AS SELECT * FROM Country WHERE Continent = 'Oceania' WITH CHECK OPTION;

SELECT Code FROM Oceania WHERE Name = 'Australia';

实现视图更好的方式是重写含有视图的查询，将视图定义的SQL直接包含进查询的SQL中。下面展现的是将视图定义的sql合并进查询sql后的样子：
SELECT Code From Country WHERE Continent = 'Oceania' AND Name ='Australia';
Mysql可以使用这两种方法中的任何一种来处理视图。这两种算法分别叫做临时表算法（TEMPTABLE）和合并算法（MERGE），最好使用合并算法。

视图的两种实现

如果视图中包含GROUP BY, DISTINCT, UNION，聚合函数或子查询等无法使原纪录和视图记录建立一一映射关系的话就会使用临时表算法。
创建视图的时候可以指定用临时表算法
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;

视图主要作用

简化复杂查询
可能要经常使用具有多个连接的复杂的SQL语句，创建视图以后只要在视图上进行简单查询就可以
提高安全性
可以将你想展现的数据通过视图給用户看，同时隐藏了原来表里面的敏感数据
向后兼容
假设原来有一个表要被拆分成2个不同的表，那么之前对这个表的操作可能会失败，现在只需要创建和原来表名字相同的视图就行了

7.2.3 视图限制

mysql视图不支持物化视图（物化视图将视图结果数据存放在一个可以查看的表中，并定期从原始表刷新数据到这个表）。
也不支持视图中创建索引

高性能mysql