正文

msql事务与引擎

IP云
IP云V管理员/0 阅读
09/27
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

事务介绍

  简单来说，事务就是指逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句，执行时要么全成功要么全失败。 
  MySQL5.5支持事务的引擎：Innodb/ndb
 一、事务四大特性（ACID）
1.原子性（Atomicity）
事务是一个不可分割的单位，事务中的所有SQL等操作要么全都发生，要么都不发生
2.一致性（Consistency）
事务发生前和发生后，数据的完整性必须保持一致
3.隔离性（Isolation）
当并发访问访问数据库时，一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据库是相互隔离的。
4.持久性（Durability）
一个事务一旦被提交，它对数据库中的数据改变就是永久性的。如果出了错误，事务也不允许撤销，只能通过“补偿性事务”
二、事务的开启
  数据库默认事务是自动提交的，也就是发一条sql它就执行一条。如果想多条sql放在一个事务中执行，则需要使用事务进行处理。当我们开启一个事务，并且没有提交，mysql会自动回滚事务，或者我们使用rolback命令回滚事务。
MySQL默认是自动提交的，也就是提交一个QUERY，它就直接执行！我们可以通过以下命令进行查询：show variables like '%commit%'
mysql>set global autocommit=OFF
mysql>set global autocommit=NO 开启自动提交
rolback回滚事务（oracle）
commit提交事务（oracle）

1.2 MySQL存储引擎架构

  MySQL的存储引擎是MySQL数据库的重要组成部分，MySQL常用的表的引擎为MyISAM和InnoDB两种。MySQL的美中存储引擎在MySQL里通过插件的方式使用的。MySQL可以同时支持多种存储引擎。

MyISAM引擎介绍

1.1什么是MyIASM引擎？

  MyIASM引擎是MySQL关系数据库管理系统的默认存储引擎（MySQL 5.5.5以前）这种MySQL表的存储结构从旧的IASM代码扩展出许多有用的功能。在新版本的MySQL中，InnoDB引擎由于对事物参考完成行，以及更好的并发性等优点开始逐渐的取代MyIASM引擎。
提示：MySQL5.5以前的数据库的默认存储引擎为MyIASM
  每一个MyIASM的表都对应与硬盘上的三个文件。这三个文件有一样的文件名，但是有不同的扩展名指示其类型用途：frm文件保存表的定义，这个文件并不是MyISAM引擎的一部分，而是服务器的一部分；MYD保存表的数据；MYI是表的索引文件。MYD和MYI是MyIASM的关键

1.2 MyISAM引擎特点（此处面试最好全说出来）

1.不支持事务
（事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败）
2.表级锁定，数据更新时锁定整个表：其锁表机制是表级锁定，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。
3.读写互相阻塞：不仅会在写入的时候阻塞读取，MyIASM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读。
4.只会缓存索引：MyIASM可以通过key_buffer_size缓存索引，以大大提高访问性能减少磁盘IO。但是这个缓存区只会缓存索引，而不会缓存数据。
5.读取速度较快，占用资源相对少
6.不支持外键约束，但支持全文索引
7.MyISAM引擎是MySQL5.5.5 前缺省的存储引擎

1.3 MyIASM引擎适用的生产业务场景

1）不需要事务支持的业务（例如转账就不行）
2）一般为读数据比较多的应用，读写都频繁场景不适合，读多或者写多的都适合。
3）读写并发访问相对较低的业务（纯读纯写高并发也可以）（锁定机制问题）
4）数据修改相对较少的业务（阻塞问题）
5）以读为主的业务，例如：数据库系统表、www、blog图片信息数据库，用户数据库。商品库等业务
6）对硬件一致性要求不是非常高的业务（不支持事务）
7）硬件资源比较差的机器可以用MySAM（占用资源少）
8）使用读写分离的MySQL从库可以使用MyIASM
小结：单一对数据库的操作都可以使用MyIASM，所以单一就是尽量纯读，或纯写（insert，update，delete）等

1.4 MyIASM引擎调优精要

1.设置合适的索引（缓存机制）
2.调整读写优先级，根据实际需求确保重要操作更优先执行。
3.启用延迟插入改善大批量写入性能（降低写入频率，尽可能多条数据一次性写入）
4.尽量顺序操作让insert数据都写入到尾部，减少阻塞
5.分解大的时间长的SQL操作，降低单个操作的阻塞时间。
6.降低并发数（减少对MySQL访问），某些高并发场景通过应用进行排队队列机制
7.对于相对静态（更改不频繁）的数据库数据，充分利用Query Cache或memcached缓存服务可以极大的提高访问效率，网站动态内容静态化，减少对数据库的访问。

InnoDB引擎

1.1 什么是InnoDB引擎？

  InnoDB引擎是MySQL数据库的另一个重要的存储引擎，整成为目前MySQL AB所发行新版的标准，被包含在所有二进制安装包里。和其他的存储引擎相比，InnoDB引擎的优点是支持兼容ACID的事务（类似于PostgerSQL）以及参数完整性（即对外键的支持）
  Oracle公司2005年10月收购了Innobase。Innobase采用双认证授权，它使用GUN发行，也允许其他想将InnoDB结合到商业软件的团体获得授权。
  MySQL5.5.5以后数据库的默认存储引擎为InnoDB

InnoDB引擎特点

1.支持事务：支持4个事务隔离级别，支持多版本读。
2.行级锁定（更新时一定是锁定当前行）：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
3.读写阻塞与事务隔离级别相关。
4.具有非常搞笑的缓存特性：能缓存索引，也能缓存数据。
5.整个表和主键以Cluster方式存储，组成一颗平衡树。
6.所有Secondary Index都会保存主键信息
7.支持分区，表空间，类似oracle数据库
8.支持外键约束 5.5 以后不支持全文索引，以后支持了。
9.和MyIASM引擎相比，InnoDB对硬件资源要求比较高。

InnoDB特点:面试必答题

1.Row-level locking        #行级锁
2.Full-text search indexs  #全文索引
3.Data caches              #缓存数据
4.Index caches            #索引缓存
5.Transactions            #支持事务
6.占用资源多
7.读写阻塞与事务隔离级别相关。
8.外键

InnoDB引擎使用的生产业务场景

1、需要事务支持的业务（具有较好的事务特性）
2、行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
3、数据读写及更新都较为频繁的场景，如：BBS。SNA。微博，微信等。
4、数据一致性要求较高的业务，例如：充值转账、银行卡转账。
5、硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO

InnoDB引擎调优精要

1、主键尽可能小，避免给Secondary index 带来过大的空间负担
2、建立有索引避免全表扫描，因为会使用表锁。
3、尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO消耗
4、在大批量小插入的视乎，尽量自己控制事务而不要使用autocommit自动提交，有开关可以控制提交方式：
5、合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。
  如果Innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不会任何操作。
6、避免主键更新，因为这会带来大量的数据移动