个人视频网站怎么做,营销型网站跟云网站,小程序代理商怎么赚钱,北京网站备案号查询MySQL
1.事务
事务的4大特性
事务4大特性#xff1a;原子性、一致性、隔离性、持久性
原⼦性#xff1a; 事务是最⼩的执⾏单位#xff0c;不允许分割。事务的原⼦性确保动作要么全部完成#xff0c;要么全不执行一致性#xff1a; 执⾏事务前后#xff0c;数据保持⼀…MySQL
1.事务
事务的4大特性
事务4大特性原子性、一致性、隔离性、持久性
原⼦性 事务是最⼩的执⾏单位不允许分割。事务的原⼦性确保动作要么全部完成要么全不执行一致性 执⾏事务前后数据保持⼀致多个事务对同⼀个数据读取的结果是相同的隔离性 并发访问数据库时⼀个⽤户的事务不被其他事务所⼲扰各并发事务之间数据库是独⽴的持久性 ⼀个事务被提交之后。它对数据库中数据的改变是持久的即使数据库发⽣故障也不应该对其有任何影响。
实现保证MySQL的存储引擎InnoDB使用重做日志保证一致性与持久性回滚日志保证原子性使用各种锁来保证隔离性。
2.事务隔离级别
读未提交最低的隔离级别允许读取尚未提交的数据变更可能会导致脏读、幻读或不可重复读。读已提交允许读取并发事务已经提交的数据可以阻⽌脏读但是幻读或不可重复读仍有可能发⽣。可重复读同⼀字段的多次读取结果都是⼀致的除⾮数据是被本身事务⾃⼰所修改可以阻⽌脏读和不可重复读会有幻读。串行化最⾼的隔离级别完全服从ACID的隔离级别。所有的事务依次逐个执⾏这样事务之间就完全不可能产⽣⼲扰。
隔离级别并发问题问题读未提交可能会导致脏读、幻读或不可重复读读已提交可能会导致幻读或不可重复读可重复读可能会导致幻读可串行化不会产生干扰
3.默认隔离级别-RR
MySQL默认隔离级别可重复读
同⼀字段的多次读取结果都是⼀致的除⾮数据是被本身事务⾃⼰所修改
可能出现幻读解决方案
把隔离级别设置成SERIALIZABLE这样所有事务都只能顺序执行自然不会因为并发有什么影响了但是性能会下降许多使用MVCC解决快照读幻读问题如简单select读取的不是最新的数据。维护一个字段作为version这样可以控制到每次只能有一个人更新一个版本。如果需要读最新的数据可以通过GapLockNext-KeyLock可以解决当前读幻读问题。
4.行锁表锁意向锁
InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁
InnoDB按照不同的分类的锁
1. 共享/排它锁(Shared and Exclusive Locks)行级别锁
2. 意向锁(Intention Locks)表级别锁
3. 间隙锁(Gap Locks)锁定一个区间
4. 记录锁(Record Locks)锁定一个行记录表级锁串行化:Mysql中锁定 粒度最大的一种锁对当前操作的整张表加锁实现简单 资源消耗也比较少加锁快不会出现死锁 。其锁定粒度最大触发锁冲突的概率最高并发度最低MyISAM和 InnoDB引擎都支持表级锁。
行级锁RR、RC:Mysql中锁定 粒度最小 的一种锁只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小并发度高但加锁的开销也最大加锁慢会出现死锁。 InnoDB支持的行级锁包括如下几种
记录锁Record Lock: 对索引项加锁锁定符合条件的行。其他事务不能修改和删除加锁项间隙锁Gap Lock: 对索引项之间的“间隙”加锁锁定记录的范围不包含索引项本身其他事务不能在锁范围内插入数据。Next-key Lock 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。
5.索引
Innodb和Myisam引擎
Myisam支持表锁适合读密集的场景不支持外键不支持事务索引与数据在不同的文件
Innodb支持行、表锁默认为行锁适合并发场景支持外键支持事务索引与数据同一文件
哈希索引
哈希索引用索引列的值计算该值的hashCode然后在hashCode相应的位置存执该值所在行数据的物理位置因为使用散列算法因此访问速度非常快但是一个值只能对应一个hashCode而且是散列的分布方式因此哈希索引不支持范围查找和排序的功能。
B树索引
优点
B树的磁盘读写代价低更少的查询次数查询效率更加稳定有利于对数据库的扫描B树是B树的升级版B树只有叶节点存放数据其余节点用来索引。索引节点可以全部加入内存增加查询效率叶子节点可以做双向链表从而提高范围查找的效率增加的索引的范围。在大规模数据存储的时候红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁进而导致效率低下的情况。所以只要我们通过某种较好的树结构减少树的结构尽量减少树的高度B树与B树可以有多个子女从几十到上千可以降低树的高度。磁盘预读原理将一个节点的大小设为等于一个页这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的在实际实现B-Tree还需要使用如下技巧每次新建节点时直接申请一个页的空间这样就保证一个节点物理上也存储在一个页里加之计算机存储分配都是按页对齐的就实现了一个node只需一次I/O。
6. 聚簇索引和非聚簇索引
聚簇索引将数据存储与索引放到了一块索引结构的叶子节点保存了行数据主键索引
非聚簇索引将数据与索引分开存储索引结构的叶子节点指向了数据对应的位置辅助索引
聚簇索引的叶子节点就是数据节点而非聚簇索引的叶子节点仍然是索引节点只不过有指向对应数据块的指针。
7.数据库范式
第一范式1NF列不可分割
第二范式2NF属性完全依赖于主键 [ 消除部分子函数依赖 ]
第三范式3NF属性不依赖于其它非主属性 [ 消除传递依赖 ]
8.JOIN 查询
left join(左联接) 返回包括左表中的所有记录和右表中关联字段相等的记录 right join(右联接) 返回包括右表中的所有记录和左表中关联字段相等的记录 inner join(等值连接) 只返回两个表中关联字段相等的行
优化
Explain
索引优化
最左前缀索引like只用于’string%语句中的和in会动态调整顺序唯一索引唯一键区分度在0.1以上无法使用索引! 、is null 、 or、 、5.7以后根据数量自动判定in 、not in联合索引避免select * 查询列使用覆盖索引
语句优化
har固定长度查询效率高varchar第一个字节记录数据长度应该针对Explain中Rows增加索引group/order by字段均会涉及索引Limit中分页查询会随着start值增大而变缓慢通过子查询表连接解决count会进行全表扫描如果估算可以使用explaindelete删除表时会增加大量undo和redo日志 确定删除可使用trancate
表结构优化
单库不超过200张表单表不超过500w数据单表不超过40列单表索引不超过5个
集群
1.主从复制过程
MySQl主从复制
原理将主服务器的binlog日志复制到从服务器上执行一遍达到主从数据的一致状态。过程从库开启一个I/O线程向主库请求Binlog日志。主节点开启一个binlog dump线程检查自己的二进制日志并发送给从节点从库将接收到的数据保存到中继日志Relay log中另外开启一个SQL线程把Relay中的操作在自身机器上执行一遍优点 作为备用数据库并且不影响业务可做读写分离一个写库一个或多个读库在不同的服务器上充分发挥服务器和数据库的性能但要保证数据的一致性
2.数据一致性问题
“主从复制有延时”这个延时期间读取从库可能读到不一致的数据。
**缓存记录写key法**在cache里记录哪些记录发生过的写请求来路由读主库还是读从库
**异步复制**在异步复制中主库执行完操作后写入binlog日志后就返回客户端这一动作就结束了并不会验证从库有没有收到完不完整所以这样可能会造成数据的不一致。
**半同步复制**当主库每提交一个事务后不会立即返回而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端通过一份在主库的Binlog另一份在其中一个从库的Relay-log可以保证了数据的安全性和一致性。
**全同步复制**指当主库执行完一个事务所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回所以全同步复制的性能必然会收到严重的影响。
