事务介绍
简单来说, 事务就是指逻辑上的一组 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 特点: 面试必答题
- Row-level locking #行级锁
- Full-text search indexs #全文索引
- Data caches #缓存数据
- Index caches #索引缓存
- 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, 避免主键更新, 因为这会带来大量的数据移动
来源: http://www.bubuko.com/infodetail-2970667.html