Mysql的功能笔记

1.mysql的核心功能基本都在第二层架构(处在存储引擎层上面)，包括查询解析、分析、优化、缓存以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、视图等。

mysql的第三层架构是存储引擎，它只负责数去的存储和提取，它不会解析sql，它只是简单的响应上层服务器的请求。

2.读写锁:用于并发控制，读锁是共享的，写锁是排他的，写锁会阻塞其他的写锁和读锁，通常写锁比读锁有更高的优先级，写锁可以插入到锁队列中读锁的前面。

3.锁粒度:注意，(1)在给定的资源上，锁定的数据越精确，则并发程度越高。

(2)关于锁的操作是需要耗资源的，包括获得锁、释放锁、检查锁等状态，都会增加开销。

4.表锁:开销缩小，并发程度比较低。

5.行级锁：行级锁是存储引擎相关的，mysql服务器层没有实现行级锁，innodb中实现了行级锁。行级锁能支持最大的并发程度，同时也带来的最大的锁开销。

6.事务:事务就是一组原子性的sql查询，事务内的语句，要么全部成功，要么全部失败，不存在部分成功部分失败的情况。

经典的例子就是银行的例子：银行转账例子

7.事务的acid特性：

(a),原子性(atomicity):一个事务必须被视为一个不可分割的最小工作单元，事务内的操作要么全部成功，要么全部失败回滚，这就是事务的原子性。

(b)一致性(consistency):总是从一个一致性的状态转换到另一个一致性的状态，银行转账例子中就是，它只能从starttransaction之前的状态转移到commit之后的状态，在事务中commit之前的状态操作不会改变数据库状态，即使在事务语句执行过程中系统崩溃，此时的状态仍然是starttransaction的状态。

(c)隔离性(isolation):通常来说，一个事务所做的修改在最终提交前对其他事务是不可见的， 即我们在事务a中将银行卡-200元，此时另一个事务b看这个银行卡的余额并不会发生改变。不过隔离性事务的隔离级别有关系。

(d)持久性(druability):一旦事务提交，所做的修改就会永久的保存到数据库中。

8.事务的代价:就像锁粒度的升级会增加系统开销一样，事务提供的sql语句的安全性，它也需要数据库做很多额外的工作，数据库因此需要更强的cpu处理能力，更大的内存空间和更多的磁盘空间。

9.事务是存储引擎相关的，可以选择不同的存储引擎来决定是否需要事务。

10.事务的隔离级别:较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

set transaction isolation level来设置隔离级别

a.)未提交读(read uncommitted):在事务中的修改，即使没有提交，对其他事务也都是可见的。 此时就形成了脏读：事务可以读取未提交的数据 ，依据脏数据做操作很可能是不正确的。这个级别会导致很多问题并且性能也没有明显优势，在实际中不会使用它。

b.)提交读(read committed)，又叫不可重复读:一个事务开始的时，只能”看见”已经提交的事务所做的修改，换句话说就是事务开始到提交前所做的修改对于其他事务是不可见的。在这个隔离级别的情况下，两次执行同样的查询可能会得到不一样的结果，因此这个级别又叫不可重复读。

c.)可重复读(repeatable read):mysql默认的事务隔离级别，保证同一事务中多次读取记录的结果是一致的。但是仍然存在幻读的问题。

d.)可串行化(serializable):是最高的隔离级别，强制事务串行执行，即在读取的每一行上加锁，解锁了”幻读”问题，但是会导致大量的超时和锁争用的问题。在实际中很少使用它。

脏读：一个事务读取到了另外一个事务没有提交的数据

事务1：更新一条数据

------------->事务2：读取事务1更新的记录

事务1：调用commit进行提交

***此时事务2读取到的数据是保存在数据库内存中的数据，称为脏读。

***读到的数据为脏数据

详细解释：

脏读就是指:当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，

另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个

事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

不可重复读：在同一事务中，两次读取同一数据，得到内容不同

事务1：查询一条记录

-------------->事务2：更新事务1查询的记录

-------------->事务2：调用commit进行提交

事务1：再次查询上次的记录

***此时事务1对同一数据查询了两次，可得到的内容不同，称为不可重复读

幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同

事务1：查询表中所有记录

-------------->事务2：插入一条记录

-------------->事务2：调用commit进行提交

事务1：再次查询表中所有记录

***此时事务1两次查询到的记录是不一样的，称为幻读

详细解释：

幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，

这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表

中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，

就好象发生了幻觉一样。

11.死锁:当多个事务视图以不同的锁顺序锁定资源的时候，就可能会产生死锁。多个事务同时锁定同一个资源也可能会产生死锁。数据库系统一般实现了各种死锁检测和死锁超时机制。innodb存储引擎能检测到死锁的循环依赖，并立即返回一个错误。innodb目前解决死锁的方式是将持有最少行级排它锁的事务进行回滚。

12.autocommit:mysql默认采用自动提交模式，即每个查询都被当作一个事务执行提交操作。

set autocommit = 1或者0。当autocommit=0的时候，所有的查询都在一个查询中，知道显示的执行commit.

13.在事务执行过程中，加锁的时机根据隔离级别自动加锁，但是锁的释放只在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻释放。

14.多版本并发控制:

什么是多版本并发控制呢 ?其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号，

而每一个事务在启动的时候，都有一个唯一的递增的版本号。

1、在插入操作时 ： 记录的创建版本号就是事务版本号。

比如我插入一条记录, 事务id 假设是1 ，那么记录如下：也就是说，创建版本号就是事务版本号。

id　　name　　create version　　delete version

1test　　1

2、在更新操作的时候，采用的是先标记旧的那行记录为已删除，并且删除版本号是事务版本号，然后插入一行新的记录的方式。

比如，针对上面那行记录，事务id为2 要把name字段更新

update table set name= 'new_value' where id=1;

id　　　　name　　create version　　delete version

1　　test　　12

1　　new_value　　2

3、删除操作的时候，就把事务版本号作为删除版本号。比如

delete from table where id=1;

id　　name　　create version　　delete version

1new_value23

4、查询操作：

从上面的描述可以看到，在查询时要符合以下两个条件的记录才能被事务查询出来：

1) 删除版本号 大于 当前事务版本号，就是说删除操作是在当前事务启动之后做的。

2) 创建版本号 小于或者等于 当前事务版本号 ，就是说记录创建是在事务中(等于的情况)或者事务启动之前。

这样就保证了各个事务互不影响。从这里也可以体会到一种提高系统性能的思路，就是：

通过版本号来减少锁的争用。

另外，只有read-committed和 repeatable-read 两种事务隔离级别才能使用mvcc

read-uncommited由于是读到未提交的，所以不存在版本的问题

而serializable 则会对所有读取的行加锁

多版本并发控制使得大部分读操作不用加锁，减少了锁的开销。不足之处是每行记录都需要额外的存储空间，需要更多的行检查工作和一些额外的维护工作。

15.innodb：

a.)mysql的默认事务性引擎，被设计用来处理大量的短期事务。

b.)自动崩溃恢复特性

c.)使用mvcc支持高并发，使用间隙锁策略防止幻读出现。

d.)使用聚簇索引.

e.)innodb的存储格式是平*立的，也就是说可以将数据文件和索引文件从intel平台复制到其他平台。

f.)innodb里面做了很多的优化,如可预测性预读，自适应哈希索引，以及加速插入操作的”插入缓冲区”

g.)innodb通过一些机制和工具支持热备份。

16.myisam:

a.)全文索引

b.)空间函数

c.)不支持事务和行级锁

d.)崩溃后无法安全恢复

e.)存储在数据文件和索引文件,动态行或者静态行

f.)压缩表:如果表在创建并导入数据后，不会再进修改操作，这样的表适合myisam压缩表，压缩表可以极大的减少磁盘空间占用，因此可以减少i/o，从而提升查询性能。压缩表也支持索引，但是索引也是只读的。

g.)myisam适用于：(1)只读数据(2)表比较下，可以忍受修复操作。

17.memory引擎，如果需要快速访问数据并其二数据不会被修改，重启后丢失也没关系，那么memory比较好，它的数据都保存在内存中，不需要进行磁盘i/o，比myisam快，memory表在很多场景可以发挥好的作用:

(1)用于查找或者映射表

(2)保存数据分析中产生的中间数据

(3)缓存周期性聚合数据的结果

18.选择合适的引擎：

大部分情况下,innodb都是很好的选择，除非你需要 innodb没有的特性，比如说全文索引。

选择存储引擎的时候有几个点可以帮助选择:

(1)是否需要事务:

当你的应用需要事务支持的时候(比如订单类的应用)，那么innodb无疑是你的首选，

如果不需要事务，并且你的数据大部分情况下是只读的，那么myisam也许是一个不错的选择。

(2)备份：我们知道innodb可以借助工具实现真正的热备份，那么如果你需要在线热备份，那么innodb无疑是你的选择。

(3)崩溃恢复：当数据量比较大的时候，系统崩溃后如何快速的恢复是一个你需要考虑的问题，myisam崩溃后发生损坏的概率比innodb要高很多并且恢复速度也很慢，所以即使你的应用不需要事务支持，innodb也是个不错的选择--------------------innodb崩溃修复特性。

(4)特有的特性：有一些应用可能依赖一些存储引擎特有的特性，比如innodb的聚簇索引，myisam的空间函数，压缩特性等，有时候你需要在一些应用需要好的特性上做一些思考。

以上概念比较抽象，我们还是举例来说明下吧

a.)日志型应用:如果你的应用是这个类型的，那么你们对插入速度会有很高的要求，myisam是个不错的选择，因为他们开销更低，插入速度快。

注意：如果需要对日志做分析报表，我们的解决办法如下:

(1)在系统负载较低的时候执行报表查询、分析操作，但是应用负载情况不断变化，你不能保证负载在你的查询报表过程中一直处于低水平状态。

(2)将数据复制一份到备库，然后再备库上执行比较消耗时间和cpu的查询，这样主库只用担心高效的插入操作，备库上的查询操作不会影响日志应用的插入操作。

b.)订单处理：无疑innodb是首选，这种类型的应用需要事务支持。

c.)只读或者大部分只读的表：如果你的应用不介意myisam崩溃修复的问题，选用myisam引擎是合适的。

d.)cd-rom 或者dvd-rom型应用：可以考虑采用myisam的压缩表特性，压缩表比未压缩的表节省很多空间并且压缩表可以在不同介质上相互拷贝。

f.)大数据量应用：一般innodb的数据量都在

-tb之间，在这样的数据量下，如果采用myisam，崩溃后的恢复就是一个噩梦。

19.innodb小总结:

a.)innodb的数据存储在表空间中，innodb可以将每个表的数据和索引文件存放在单独的文件中。

b.)innodb采用mvcc来支持高并发，实现了4个隔离级别，实现了更高的读性能，锁开销小。并且采用间隙锁策略防止幻读的出现(当前读)。

c.)innodb基于聚簇索引建立，聚簇索引对主键列查询有很高的性能，不过二级索引必须包含主键列，如果主键列特别大，其他的所有索引列都很大，空间浪费，主键应尽可能的小。

d.)innodb的存储格式是平*立的，也就是说可以将数据和索引从一个平台轻松的移动到不同的平台。

e.)很多的内部优化，比如可预测性预读，插入缓冲区，自适应哈希。

f.)支持热备份。

g.)支持行级锁和事务。

20.myisam小总结:

a.)不支持事务和行级锁，表锁。

b.)崩溃后无法安全修复。

c.)全文索引

d.)延迟更新索引键:myisam中的一个选项,delay_key_write,每次修改执行的时候，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区的时候或者关闭表的时候，才会将对应的索引快写入到磁盘，这会极大的提高性能(在内存中的操作当然快了，而且减少了将数据不同刷回磁盘的操作)。

e.)myisam压缩表:如果表在导入数据后不在进行修改操作(比如cd-rom或者dvd-rom应用)，这种情况下适合用myisam压缩表，压缩表减少了数据占用的磁盘空间，减少了磁盘i/o，从而提升了查询性能。压缩表也支持索引。

myisam适合只读的数据或者表比较小，可以忍受修复操作的应用。