企业网站建设浩森宇特,it运维,职称论文写作网站,游戏挂机赚钱一小时20目录
一、什么是事务
二、事务的特征
三、MySQL使用事务
3.1 实现流程#xff1a;
实现截图#xff1a;
3.2 实例演示#xff1a;
四、事务的隔离级别
幻读#xff1a;
如何解决#xff1a;
脏读#xff1a;
不可重复读#xff1a;
幻读和不可重复读两者区别…目录
一、什么是事务
二、事务的特征
三、MySQL使用事务
3.1 实现流程
实现截图
3.2 实例演示
四、事务的隔离级别
幻读
如何解决
脏读
不可重复读
幻读和不可重复读两者区别
事务的隔离级别
五、数据库优化
5.1 影响性能因素的优化
5.2 参与优化的对象
5.3 系统优化
5.4 服务优化
5.5 MyISAM配置项
5.6 INNODB配置项
5.7 应用优化
库表设计原则
索引建立原则一
索引建立原则二
编写高效的 SQL 一
编写高效的 SQL 二 一、什么是事务 事务Transaction是我们数据库内最小且不可再分的单元。通常一个事务对应一个完整的业务(例如银行账户转账业务该业务就是一个最小的工作单元)。一个完整的业务需要批量的DML(INSERT 、UPDATE、DELETE)语句共同联合完成。事务只和DML语句有关或者说DML语句才有事务。这个和业务逻辑有关业务逻辑不同DML语句的个数不同。
操作序列范畴这些序列共有的一个特征 要么全部执行要么全都不执行。这是一个不可分割的工作单元。事务是由事务开始和事务结束之间所执行的数据库操作组成。
#例如以银行转账需求
家长账户 扣款 -money
学生账户 收款 money
必要要求以上两台DML语句必须同时成功或者同时失败。
最小单元不可再分当第一条DML语句执行成功后并不能将底层数据库中的第一个账户的数据修改只是将操作进行了记录。这个记录实在内存中完成的当第二条DML语句执行成功后和底层数据库文件中的数据再进行完全的同步。反之如果第二条DML语句执行失败清空所有的历史操作记录以保证数据的统一。
事务处理事务操作保证所有事务都作为一个工作单元来执行即使出现了故障都不能改变这种执行方式。当在一个事务中执行多个操作时只有事务完成了提交行为。才意味着数据被永久的保存要么数据库管理系统将放弃所有的修改。使整个事务回滚到最初状态。
二、事务的特征
事务的本质是由一组SQL语句组成的逻辑处理单元。
A 原子性Atomicity原子性是指事务是一个不可分割的最小单元事务中的操作要么都发生要么都不发生
C 一致性Consistency事务必须使数据库从一个一致性转变到另一个一致性的状态
I 隔离性Isolation多个用户并发访问数据库时数据库为每一个用户开启的事务不能被其他的事务操作所干扰要求多个并发事务之间 要相互隔离
D持久性Durability持久性指一个事务被提交是它对于数据库内数据的改变就是永久性的接下来即便数据库发生故障也不应该对其有任何影响。 三、MySQL使用事务
3.1 实现流程
1、手动关闭掉一个操作自动提交改变成手动提交
SET AUTOCOMMIT 0;
2、标记事务的起点
START TRANSACTION;
3、编辑并执行 SQL语句 组
4、提交
COMMIT;5 、回滚
ROLLBACK;
6、手动开启掉一个操作手动提交改变成自动提交
SET AUTOCOMMIT 0;
实现截图 MySQL 开启事务回滚提交
begin
5.5 以上版本 不需要手动begin,只要你执行的是一个DML那么它就会自动在前面加入begin命令COMMIT 提交事务
完成了一个事务一旦事务提交成功就说明具备了ACID原则ROLLBACK 回滚职务
完成了一个事务将内存中已执行的操作撤销并还原成最初状态
3.2 实例演示
#需求 顾客A在线购买了一个商品 价格XXXX元 采用转账方式进行支付
#假设A 存款金额XXXX元且向卖家B支付购买商品费用
#卖家B 当前账户余额XXXX元#步骤1创建数据库 shop_db;
CREATE DATABASE shop_db;
#步骤2创建账户表 账户编号自增 账户人 当前账户金额
CREATE TABLE IF NOT EXISTS account (id int(11) not null auto_increment,name varchar(32) not null,cash decimal(9,2) not null,PRIMARY KEY (id)
) ENGINEInnoDB;
#步骤3通过事务完成转账业务
SET AUTOCOMMIT 0;START TRANSACTION;
UPDATE account set cash cash - 1000 WHERE name A;
UPDATE account set cash cash 1000 WHERE name B;COMMIT; #ROLLBACKSET AUTOCOMMIT 1;#那么当两个事务打开同一个数据库用户数据库原始余额为100第一个事务将余额改为0结束了
#当第二个事务又将余额-50那么最后结果是多少? #死锁 必须等待1事务结束后 方可执行2事务内的操作
四、事务的隔离级别
多个线程开启各自事务操作数据库中数据时数据库系统要负责隔离操作以保证各个线程在获取数据时的准确性。
如果不考虑隔离性会导致幻读、脏读以及不可重复读
幻读
在一个事务内读取到了别的事务插入的数据导致前后读取的信息不一致。
EG:事务A按照自身的约定在进行数据读取期间事务B插入了相同的搜索条件的新数据事务A再次按照原先约定条件进行读取时发现了事务B插入的新数据。
会造成事务中先产生的锁无法管理后加入满足条件的行。
如何解决
1、bin_log 产生数据一致性问题在一个事务中先对符合条件的目标行做变更而在事务提交前有新符合目标条件的加入。通过bin_log 恢复数据会将所有符合条件的目标行进行变更。
2、间隙锁在两行记录间的空隙加上锁防止新纪录的插入。
脏读
事务读取到另一个事务未提交的数据解决方案加入乐观锁。
不可重复读
不可重复读是指在数据库访问中一个事务范围内两个相同的查询却返回了不同数据这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据事务T2读取并修改了该数据T1为了对读取值进行检验而再次读取该数据便得到了不同的结果。
幻读和不可重复读两者区别
不可重复读指同一条SQL查询到了不同的结果幻读指查询的结果行数不同。
事务的隔离级别
描述脏读不可重复读幻读Read uncommitted是是是Read committed否是是Repeatable read否否是Serializable否否否
五、数据库优化
5.1 影响性能因素的优化
服务优化硬件操作系统网络数据库设计
应用优化应用程序查询事务管理数据分布
5.2 参与优化的对象
数据库管理员
业务部门代表
架构师
应用程序设计开发人员
硬件及系统管理员
存储管理员...
5.3 系统优化
软件优化开发系统操作系统MYSQL编译优化
硬件优化CPU 内存 硬盘 网卡
5.4 服务优化
Mysql配置配置合理的Mysql服务器尽量在应用本身达到一个合理的使用针对于不同的搜索引擎定制不同的配置针对于不同的情况和需求进行合理的配置my.cnf进行配置。
5.5 MyISAM配置项
选项缺省值推荐值说明key_buffer_size8M128M--256M用来存放索引区块的缓存值, 建议128M以上不要大于内存的30%read_buffer_size128k10-20M用来做MyISAM表全表扫描的缓冲大小.myisam_sort_buffer_size16M128M设置,恢复,修改表的时候使用的缓冲大小
5.6 INNODB配置项
选项缺省值推荐值说明innodb_buffer_pool_size32M1GInnoDB使用一个缓冲池来保存索引和原始数据, 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少一般是内存的一半不超过2G否则系统会崩溃这个参数非常重要innodb_additional_mem_pool_size2M128MInnoDB用来保存 metadata 信息,如果内存是4G最好本值超过200Minnodb_flush_log_at_trx_commit100 代表日志只大约每秒写入日志文件并且日志文件刷新到磁盘; 1 为执行完没执行一条SQL马上commit; 2代表日志写入日志文件在每次提交后,但是日志文件只有大约每秒才会刷新到磁盘上. 对速度影响比较大同时也关系数据完整性innodb_log_file_size8M128M在日志组中每个日志文件的大小, 一般是innodb_buffer_pool_size的25%官方推荐是innodb_buffer_pool_size 的 40-50%, 设置大一点来避免在日志文件覆写上不必要的缓冲池刷新行为innodb_log_buffer_size128K8M用来缓冲日志数据的缓冲区的大小.推荐是8M官方推荐该值小于16M最好是 1M-8M 之间
5.7 应用优化
库表设计原则
1、选择合适的数据类型如果能够定长尽量定长
2、使用 ENUM 而不是 VARCHARENUM类型是非常快和紧凑的在实际上其保存的是TINYINT类型但其显示为字符串。这样用这个字段来做一些选项列表变得相当的完美
3、不要使用无法加索引的类型作为关键字段比如TEXT类型
4、为了避免联表查询有时候可以适当的数据冗余比如邮箱、姓名这些不容易更改的数据
5、选择合适的表引擎有时候 MyISAM 适合有时候InnoDB适合
6、为保证查询性能最好每个表都建立有 auto_increment 字段 建立合适的数据库索引
7、最好给每个字段都设定 default 值。
索引建立原则一
1、一般针对数据分散的关键字进行建立索引比如ID、QQ像性别、状态值等等建立索引没有意义字段唯一最少不可为null
2、对大数据量表建立聚集索引避免更新操作带来的碎片
3、尽量使用短索引一般对int、char/varchar、date/time 等类型的字段建立索引
4、需要的时候建立联合索引但是要注意查询SQL语句的编写
5、谨慎建立 unique 类型的索引唯一索引
6、大文本字段不建立为索引如果要对大文本字段进行检索可以考虑全文索引引擎问题
7、频繁更新的列不适合建立索引。
索引建立原则二
1、order by 字句中的字段where 子句中字段最常用的sql语句中字段应建立索引
2、唯一性约束系统将默认为改字段建立索引
3、对于只是做查询用的数据库索引越多越好但对于在线实时系统建议控制在5个以内
4、索引不仅能提高查询SQL性能同时也可以提高带where字句的updateDelete SQL性能
5、Decimal 类型字段不要单独建立为索引但覆盖索引可以包含这些字段
6、只有建立索引以后表内的行才按照特地的顺序存储按照需要可以是asc或desc方式
7、如果索引由多个字段组成将最用来查询过滤的字段放在前面可能会有更好的性能。
编写高效的 SQL 一
1、能够快速缩小结果集的 WHERE 条件写在前面如果有恒量条件也尽量放在前面
2、尽量避免使用 GROUP BY、DISTINCT 、OR、IN 等语句的使用避免使用联表查询和子查询因为将使执行效率大大下降
3、能够使用索引的字段尽量进行有效的合理排列如果使用了联合索引请注意提取字段的前后顺序
4、针对索引字段使用 , , , , , IF NULL和BETWEEN 将会使用索引
5、如果对某个索引字段进行 LIKE 查询使用 LIKE ‘%abc%’不能使用索引使用 LIKE ‘abc%’将能够使用索引
6、如果在SQL里使用了MySQL部分自带函数索引将失效同时将无法使用 MySQL 的 Query Cache比如 LEFT(), SUBSTR(), TO_DAYS()DATE_FORMAT(), 等如果使用了 OR 或 IN索引也将失效
7、使用 Explain 语句来帮助改进我们的SQL语句。
编写高效的 SQL 二
1、不要在where 子句中的“”左边进行算术或表达式运算否则系统将可能无法正确使用索引
2、尽量不要在where条件中使用函数否则将不能使用索引
3、避免使用 select *, 只取需要的字段
4、对于大数据量的查询尽量避免在SQL语句中使用order by 字句避免额外的开销
5、如果插入的数据量很大用select into 替代 insert into 能带来更好的性能
6、采用连接操作避免过多的子查询产生的CPU和IO开销
7、只关心需要的表和满足条件的数据
8、适当使用临时表或表变量
9、对于连续的数值使用between代替in
10、where 字句中尽量不要使用CASE条件
11、尽量不用触发器特别是在大数据表上
12、更新触发器如果不是所有情况下都需要触发应根据业务需要加上必要判断条件
13、使用union all 操作代替OR操作注意此时需要注意一点查询条件可以使用聚集索引如果是非聚集索引将起到相反的结果
14、当只要一行数据时使用 LIMIT 1尽可能的使用 NOT NULL填充数据库
15、拆分大的 DELETE 或 INSERT 语句批量提交SQL语句。
