事务和锁的关系？ 事务是由锁来支持的 ## 事务的隔离级别 # read uncommitted 脏读 两个事务中，其中一个事务进行了add语句还没有commit,另一个事务就可以读取到，这是很危险的 # read committed 不可重复读 没有commit的是不能在另一个事务中读取出来的 # repeatable read 可重复读(默认) 一旦读取出来就锁定了，不影响其他事务的读取 不管提不提交 都不会读到不一样的数据 ## 晃读 但存在晃读，可能没查询出来，但是可以更新，另一个事务commit之后就可以看见 ## 死锁 同一个主键执行更新 相互在等待另外一把锁 #serializable 序列化 不存在死锁 一旦进行更新insert，那么另外的事务必须进行commit 否则会一直等待，会影响速度 for update是排他锁 不允许同时读select lock in share mode 允许其他事务读 索引是用奥查树存储的 6 5 8 1 2 8 11 select * from quanzhan order by uid limit 199,10; 化在搜索索引的时间多 select * from quanzhan where uid>199 order by uid limit 0,10; # 为什么有数据库 - 快速、永久、安全的存储大量数据 - 关系型数据库:结构化数据存储 - 常用数据库: - MySQL、PostgreSQL、Oracle、MS SQL SQLite、MongoDB # 关系型数据库约束 三范式: 1. 每一列都是不可分割的原子数据项(基本类型列) 2. 要求实体的属性完全依赖于主关键字(无重复行) 3. 数据表不包含其它表已有的非主属性(无数据冗余) 非关系型数据库特点,是不是也满足三范式? 更多范式: BC范式:非键字段确定主键的一部分 用户联系方式表:uid courseid mobile 主键(uid,courseid)mobile(唯一) 4范式:多字段键且没有非键字段,键的一部分确定另一部分的多个值 用户技能表:uid departmentid skill 1 1 PHP 1 2 PHP 2 3 Swift # SQL操作 ### DDL(Data Definition Language)数据定义语言 create、alter、drop、truncate、comment、grant、revoke ### DML(Data Manipulation Language)数据操作语言 select、insert、update、delete、call、explain、lock table ### DCL(Data Control Language)数据控制语言 commit、savepoint、rollback、set transaction **truncate与delete区别** # MySQL插件 NoSql 插件 HandlerSocket 中文全文索引插件 mysqlcft 新存储引擎XtraDB 可能成为InnoDB存储引擎的替代 # MySQL分支 Percona:MySQL咨询公司Percona发布。他们是XtraDB存储 引擎的原作者。 MariaD:MySQL的初始创建者的团队开发,提供MyISAM和 InnoDB以及XtraDB Drizzle:对MySQL进行一些重大更改 # 关系型数据库安全 事务与并发 事务的ACID: 1. 原子性(Atomicity ) 全部执行或全部不执行 2.一致性( Consistency)事务前后数据都是一致性状态,约束等 3.隔离性或独立性( Isolation) 4.事务之间是独立的,和级别相关 5. 持久性(Durabilily) 事务完成即持久化存储 事务与锁的关系? # 事务隔离级别 SQL的4种事务隔离模式: read uncommitted 脏读 read committed 不可重复读 repeatable read 可重复读(默认) serializable 串行 # MySQL事务试验 MySQL事务隔离模式: 查看: SELECT @@GLOBAL.tx_isolation, @@tx_isolation; 设置: set global transaction isolation level read uncommitted; 事务提交参数: set autocommit = 0; innodb_flush_log_at_trx_commit 0 每秒保存 1 每事务保存 2 操作系统决定 # MySQL锁表类型 1. 表级锁(MyISAM):开销小,加锁快;不会出现死锁;锁定粒 度大,发生锁冲突的概率最高,并发度最低 show status like 'table%'; 2. 行级锁(InnoDB):开销大,加锁慢;会出现死锁;锁定粒度最 小,发生锁冲突的概率最低,并发度也最高 showstatuslike 'innodb_row_lock%'; 3. 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定 粒度界于表锁和行锁之间,并发度一般 并发问题 任务: $numval = select num from table where id=1; update table set num=$numval+1 where id=1; # 悲观锁与乐观锁解决并发 ##悲观锁: 1)serializable; 2)select ... where id=1 for update; //不允许其他事务读 ##乐观锁: repeatable read: select ... where id=1 lock in share mode; //允许其他事务读 解决:update table set ...version=version+1 where ...version=@ver; 例:update table set num=num-1 where num>0; 锁实验: 1、lock in share mode 2、for update 从完整性考虑 a)外键、子查询、联合查询 b)发帖子: begin; insert into tiezi(tid,uid,content) values(100,1,'xxx'); update user set tcount=tcount+1 where uid=1; commit; c)依赖事务,适合场景? #从高负载考虑 a)分库分表 帖子可能在不同的表或数据库里 b)发帖子: 帖子表插入正确 用户表增加总数,不处理正确性 修正策略 c)用户金钱? # 选择何种多级分类方式 1 前端 2 后端多表 3 无限分类 数组展现、递归展现 分次展现 # 演示一个大数据量表的分页 取数据按主键排序,不加其它条件。索引已经是最优。 来看看分页效率如何 批量插入数据提示: max_allowed_packet #海量数据的分页优化 1 建立合适的索引 2 缓存count,根据访问热度缓存靠后的分页数据 MySQL查表行数方式 SQL_CALC_FOUND_ROWS select FOUND_ROWS() 3 数据表拆分(建索引表,按年归档等) 4 SQL转换 5 产品设计优化