mysq学习总结

 

mysql逻辑架构

理解了数据库逻辑架构图就能很好的理解数据库和存储引擎的关系，
客户端通过tcp发送请求到服务层，服务层提供缓存数据，解析sql，sql优化的功能，优化器生成执行计划后，向存储引擎请求数据。
由于数据的多样性和业务使用数据场景的多样性造成数据存储的多样性，因此这种接口似的架构，方便针对不同的数据用不同的存储算法，因此发展到现在，就有很多种存储引擎

InnoDB引擎

简介

InnoDB是MySQL下使用最广泛的引擎，它是基于MySQL的高可扩展性和高性能存储引擎，从5.5版本开始，它已经成为了默认引擎。
InnODB引擎支持众多特性：
a) 支持ACID，简单地说就是支持事务完整性、一致性； 
b) 支持行锁，以及类似ORACLE的一致性读，多用户并发；
c) 独有的聚集索引主键设计方式，可大幅提升并发读写性能；
d) 支持外键；
e) 支持崩溃数据自修复；

事务

概念

事务（transaction）是数据库管理系统的执行单位，可以是一个数据库操作（如Select操作）或者是一组操作序列。事务ACID属性，即原子性（Atomicity）、一致性(Consistency)、隔离性（Isolation）、持久性（Durability）。

 

原子性：保证事务中的所有操作全部执行或全部不执行。例如执行转账事务，要么转账成功，要么失败。成功，则金额从转出帐户转入到目的帐户，并且两个帐户金额将发生相应的变化；失败，则两个账户的金额都不变。不会出现转出帐户扣了钱，而目的帐户没有收到钱的情况。

一致性：保证数据库始终保持数据的一致性——事务操作之前是一致的，事务操作之后也是一致的，不管事务成功与否。如上面的例子，转账之前和之后数据库都保持数据上的一致性。

隔 离性：多个事务并发执行的话，结果应该与多个事务串行执行效果是一样的。显然最简单的隔离就是将所有事务都串行执行：先来先执行，一个事务执行完了才允许 执行下一个。但这样数据库的效率低下，如：两个不同的事务只是读取同一批数据，这样完全可以并发进行。为了控制并发执行的效果就有了不同的隔离级别。下面 将详细介绍。

持久性：持久性表示事物操作完成之后，对数据库的影响是持久的，即使数据库因故障而受到破坏，数据库也应该能够恢复。通常的实现方式是采用日志。

事务隔离级别（transaction isolation levels）：隔离级别就是对对事务并发控制的等级。ANSI/ ISO SQL将其分为串行化（SERIALIZABLE）、可重复读（REPEATABLE READ）、读已提交（READ COMMITED）、读未提交（READ UNCOMMITED）四个等级。为了实现隔离级别通常数据库采用锁（Lock）。一般在编程的时候只需要设置隔离等级，至于具体采用什么锁则由数据库来设置。首先介绍四种等级，然后举例解释后面三个等级（可重复读、读已提交、读未提交）中会出现的并发问题。

串行化（SERIALIZABLE）：所有事务都一个接一个地串行执行，这样可以避免幻读（phantom reads）。对于基于锁来实现并发控制的数据库来说，串行化要求在执行范围查询（如选取年龄在10到30之间的用户）的时候，需要获取范围锁（range lock）。如果不是基于锁实现并发控制的数据库，则检查到有违反串行操作的事务时，需要滚回该事务。

可重复读（REPEATABLE READ）：所有被Select获取的数据都不能被修改，这样就可以避免一个事务前后读取数据不一致的情况。但是却没有办法控制幻读，因为这个时候其他事务不能更改所选的数据，但是可以增加数据，因为前一个事务没有范围锁。

读已提交（READ COMMITED）：被读取的数据可以被其他事务修改。这样就可能导致不可重复读。也就是说，事务的读取数据的时候获取读锁，但是读完之后立即释放（不需要等到事务结束），而写锁则是事务提交之后才释放。释放读锁之后，就可能被其他事物修改数据。该等级也是SQL Server默认的隔离等级。

读未提交（READ UNCOMMITED）：这是最低的隔离等级，允许其他事务看到没有提交的数据。这种等级会导致脏读（Dirty Read）。

InnoDB默认隔离级别

InnoDB默认隔离级别是可重复读，InnoDB利用MVCC，即版本控制器来避免幻读。
InnoDB实现MVCC的方法是，它存储了每一行的两个额外的隐藏列，这两个隐藏列分别记录了行的创建的时间和删除的时间。在每个事件发生的时候，每行存储版本号，而不是存储事件实际发生的时间。每次事物的开始这个版本号都会增加。自记录时间开始，每个事物都会保存记录的系统版本号。依照事物的 版本来检查每行的版本号。在事物隔离级别为可重复读的情况下，来看看怎样应用它。
SELECT
Innodb检查没行数据，确保他们符合两个标准：
　　   1、InnoDB只查找版本早于当前事务版本的数据行（也就是数据行的版本必须小于等于事务的版本），这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行
　　   2、行的删除操作的版本一定是未定义的或者大于当前事务的版本号。确定了当前事务开始之前，行没有被删除
　　符合了以上两点则返回查询结果。
　　INSERT
　　   InnoDB为每个新增行记录当前系统版本号作为创建ID。
　　DELETE
　   　InnoDB为每个删除行的记录当前系统版本号作为行的删除ID。
　　UPDATE
InnoDB复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。
MVCC只在可重复读和读已提交起作用，因为，在串行化中，不存在2个事务同时进行。而在读未提交中总是读取最新数据。

InnoDB索引

分类

从索引的存储算法上分类，InnoDB为用户只提供B+树的存储方式。
从索引上数据的完整程度可以分为聚集和非聚集。innoDB只会为主键自动建立一个聚集索引。不支持用户自定义聚集索引。
从索引查找条件上分，可以分为单列和多列和唯一索引。唯一，表示索引列的值是唯一的，如果是多列唯一索引就是，这个组合出的值是唯一的

存储算法

InnoDB索引主要用b+树存储，还有一种hash索引，这种索引一般是用户用逻辑实现，InnoDB用户建的索引都是用b+树存储。

hash索引的应用场景：有时，一个字段的内容很长很杂，不利与比较，可先用hash算法把它散列到一些数字上，然后在这些数字上建一个索引。这样就逻辑上实现了一个hash索引

聚集索引和非聚集索引

聚集索引和非聚集索引是指数据的在索引上的存储方式。他们都是用B+树来存储，区别是在叶子节点聚集索引会存储整行数据，而非聚集只有索引列和主键的值。
显然这样，聚集索引的好处是，索引上就可以直接返回数据，无需再表上拿，缺点就是，占用空间。
而非聚集索引就是一次索引后找到主键，然后根据主键的聚集索引在找到需要的数据，优点是空间小，缺点是要2此索引，当然，当需要的数据刚好是主键和索引列时就没必要第2次索引了，我想这也是dba不提倡用SELECT * 的原因之一吧。