南山网站设计费用,自己做服务器和网站,wordpress portal,营销管理网站四、事务
一、概念 对数据库的一次执行中有多条sql语句执行。这多条sql在一次执行中#xff0c;要么都成功执行#xff0c;要么都不执行。保证了数据完整性。MySQL中只有innodb引擎支持事务。
二、特性 事务是必须满足 4 个条件#xff08;ACID#xff09;#x…四、事务
一、概念 对数据库的一次执行中有多条sql语句执行。这多条sql在一次执行中要么都成功执行要么都不执行。保证了数据完整性。MySQL中只有innodb引擎支持事务。
二、特性 事务是必须满足 4 个条件ACID原子性Atomicity或称不可分割性、一致性Consistency、隔离性Isolation又称独立性、持久性Durability。 原子性一个事务中多条sql要么都执行要么都不执行不执行的回滚到事务执行前状态。 隔离性事务可以多个同时执行要对多个事务进行隔离。 持久性事务正常提交后可以保证数据持久保存即使宕机也不丢失。 一致性原子性持久性隔离性都是为了保证一致性保证数据是完整可靠的。 三、设置 默认情况下, MySQL 启用自动提交模式变量 autocommit 为 ON。
MYSQL 事务处理主要有两种方法 1、用 BEGIN, ROLLBACK, COMMIT 来实现 BEGIN 开始一个事务 ROLLBACK 事务回滚 COMMIT 事务确认 2、直接用 SET 来改变 MySQL 的自动提交模式: SET GLOBAL / SESSION autocommit0; 禁止自动提交 SET GLOBAL / SESSION autocommit1;开启自动提交 查看 autocommit 模式 SHOW GLOBAL / SESSION VARIABLES LIKE autocommit; 四、事务隔离级别
一、读未提交(read uncommitted) 一个事务可以读取到另一个事务未提交的修改。这会带来脏读幻读不可重复读问题。 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 二、读已提交(read committed) 一个事务只能读取另一个事务已经提交的修改。其避免了脏读仍然存在不可以重复读和幻读问题。 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED 三、可重复读(repeatable read MySQL 默认隔离级别) 同一个事务中多次读取相同的数据返回的是一样的。其避免了脏读和不可重复读问题普通查询解决了幻读问题,如果在查询中添加 for update 语句, 会出现幻读问题. 幻读: 同一个事物中多次读取读到数据行数不同. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 四、串行化(serializable) 事务串行执行,避免了以上所有问题。 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 五、事务实现原理 InnoDB 存储引擎还提供了两种事务日志redolog(重做日志)和undolog(回滚日志)。其中 redolog 用于保证事务持久性undolog 则是事务原子性和隔离性实现的基础。 原子性实现 MySQL用undolog 日志文件来记录增删改操作的反向操作。当出现异常事务回流时执行对应操作的反向操作即可还原。
持久性实现(mysql 保证数据不丢失) 当数据库事务提交后保证数据是不能撤销的。当sql发送到MySQL后事务还未提交前如果发现断电或宕机先将sql保存到redolog日志文件中在MySQL更新启动时执行redolog中sql. 六、隔离级别实现原理MVCC
MVCC是什么 MVCC多版本并发控制 Multi-Version Concurrent Control是 MySQL 提高性能的一种方式配合 Undo log 和版本链,让不同事务的读-写、写-读操作同时进行提高并发访问每次事务对某条记录操作时生成一个操作的版本链。 如果隔离是读已提交那么在同一个事务中每次读取时都会从版本链上生成一个快照read view每次读到的时当前查询时最新的数据也称当前读。 如果事务隔离是可重复读在事务第一次读取数据时会从版本链上生成一个快照之后再次读取时仍从上次版本快照中读取也可实现重复读也称快照读。
五、锁机制
一、概述 事务在修改数据之前需要先获得相应的锁获得锁之后事务便可以修改数据该事务操作期间这部分数据是锁定的其他事务如果需要修改数据需要等待当前事务提交或回滚后释放锁。 二、行锁、表锁、间隙锁 行锁 一个事务对某行数据进行操作时其他事务不能对本行进行操作。分为共享锁排他锁。 特点粒度小开销大加锁慢并发度高。 表锁 表级锁操作时会给整张表加锁MYISAM 与 INNODB 都支持表级锁定。 特点锁定粒度最大实现简单资源消耗较少开销小加锁快开发度最低。 间隙锁 满足某条件获取某区间。
三、共享锁、排他锁
共享锁 又称读锁。为一个查询语句添加共享锁后其他事务读取但不能再添加排他锁。
排他锁 又称写锁。为一个查询语句添加排他锁后其他事务就不能为加锁的数据添加其他锁了共享锁也不可以。
六、SQL优化
一、为什么要优化 随业务数据量的增多SQL 的执行效率对程序的运行效率的影响逐渐增大此时对 SQL 的优化就很有必要。 二、优化方法
1.查询 SQL 尽量不要使用 select *而是具体字段 节省资源、减少开销。 2.避免在 where 子句中使用 or 来连接条件 反例:SELECT * FROM user WHERE id1 OR salary5000 正例:使用 union all 把两个两个 SQL 结果合并 使用 or 可能会使索引失效从而全表扫描对于or没有索引的salary这种情况假设它走了id的索引但是走到salary 查询条件时它还得全表扫描 3. 尽量使用数值替代字符串类型 正例 主键idprimary key 优先使用数值类型 int 性别gender0 代表女1 代表男数据库没有布尔类型mysql 推荐使用 tinyint 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符 而对于数字型而言只需要比较一次就够了字符会降低查询和连接的性能并会增加存储开销 4. 使用 varchar 代替 char varchar 变长字段按数据内容实际长度存储可以节省存储空间 char 按声明大小存储不足补空格 其次对于查询来说在一个相对较小的字段内搜索效率更高 5. 对查询进行优化应尽量避免全表扫描首先应考虑在 where 及 order by group by 涉及的列上建立索引 6. 应尽量避免索引失效 6.1 应尽量避免在 where 子句中使用 or 来连接条件否则将导致引擎放弃使用索引 而进行全表扫描如:select id from t where num10 or num20。 6.2 in 和 not in 也要慎用否则会导致全表扫描如select id from t where num in(1,2,3),对于连续的数值能用 between 就不要用 in ,select id from t where num between 1 and 3 6.3 模糊查询也将导致全表扫描 select id from t where name like %abc% 6.4 应尽量避免在 where 子句中对字段进行函数操作这将导致引擎放弃使用索引而进 行全表扫描。如: select id from t where substring(name,1,3)abc 7. 提高 group by 语句的效率 反例:先分组再过滤 正例:先过滤后分组 8. 清空表时优先使用 truncate truncate table 比 delete 速度快且使用的系统和事务日志资源少. delete 语句每次删除一行并在事务日志中为所删除的每行记录一项。 truncate table 通过释放存储表数据所用的数据页来删除数据. 9. 表连接不宜太多索引不宜太多一般 5 个以内 联的表个数越多编译的时间和开销也就越大每次关联内存中都生成一个临时表应该把连接表拆开成较小的几个执行可读性更高。 10. 深度分页问题 反例 select id,name from account limit 100000,10; 正例 select id,name FROM account where id 100000 order by id limit 10; 11. 使用 explain 分析 SQL 执行计划 三、执行计划
EXPLAIN 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句从而知道MySQL 是如何处理SQL 语句的。 EXPLAIN 作用 表的读取顺序 数据读取操作的操作类型 哪些索引可以使用 哪些索引被实际使用 表之间的引用 EXPLAIN 使用 在 select 语句之前增加 explain 关键字执行查询会返回执行计划的信息 而不是执行 SQL。 例如EXPLAIN SELECT * FROM USER WHERE id 1 expain 出来的信息有 12 列分别是: id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra 其中 id是选择标识符。是 SELECT 的查询序列号,id 如果相同可以认为是一组从上往下顺序执行, 在所有组中id 值越大优先级越高越先执行。 select_type表示查询的类型。 1.SIMPLE(简单 SELECT不使用 UNION 或子查询等) 2.PRIMARY(子查询中最外层查询查询中若包含任何复杂的子部分最外层的 select 被标记为 PRIMARY) 3.SUBQUERY(子查询中的第一个 SELECT结果不依赖于外部查询) 4.DERIVED(派生表的 SELECT, FROM 子句的子查询) 5.UNION(UNION 中的第二个或后面的 SELECT 语句) table是输出结果集的表。 type表示表的连接类型。又称“访问类型”。常用的类型有systemconsteq_refrefrangeindexALL从左到右性能从好到差. possible_keys表示查询时可能使用的索引。显示可能应用在这张表中的索引一个或多个。查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用。 system: 表只有一行记录等于系统表平时不会出现这个也可以忽略不计。 const: 表示通过索引一次就找到了const 用于比较 primary key 或者 unique索引。 eq_ref: 唯一性索引扫描对于每个索引键表中只有一条记录与之匹配。常见于主键或唯一索引扫描. ref: 非唯一性索引扫描返回匹配某个单独值的所有行.本质上也是一种索引访问它返回所有匹配某个单独值的行但它可能会找到多个符合条件的行所以他应该属于查找和扫描的混合体。 range: 只检索给定范围的行使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、、、in 等的查询这种范围扫描索引扫描比全表扫描要好因为它只需要开始于索引的某一点而结束语另一点不用扫描全部索引。 index: Full Index Scanindex 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快因为索引文件通常比数据文件小。也就是说虽然 all 和 Index 都是读全表但 index 是从索引中读取的而 all 是从硬盘中读的。 All: Full Table Scan将遍历全表以找到匹配的行。一般来说得保证查询至少达到 range 级别最好能达到 ref. key表示实际使用的索引。如果为 NULL则没有使用索引,或者索引失效。 key_len是索引字段的长度。在不损失精确性的情况下长度越短越好。 rows是扫描出的行数(估算的行数)。
