mysql学习收获

1一个查询的过程

 

2 一个update的过程

 

3 order的执行过程

 

4事务的隔离

      事务的特性

 

     事务的隔离级别：

           查询数据库的默认隔离级别：

           show VARIABLES LIKE 'transaction_isolation'

           show VARIABLES LIKE 'tx_isolation'

           读未提交 read uncommit

           读提交： read commit

          可重复读：repeatable read

          串行化   serializable

         设置autocommit=0的长连接会导致长事务，长事务会导致记录事务回滚的文件ibdata逐渐增大，例如你的数据文件可能只有1个G，但是ibdata的文件可能已经100个G了，可能会需要重新建库（为什么尽量不要使用长事务。长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库）。

        DML语句执行的时候会在redolog写入执行过程，同时也会在undo log中写入回滚的语句，等到这个事务的回滚视图的时候已经不被使用并且，没有比它时间更早的视图的时候，这个回滚视图就会被清理，即使被清理了（这个“清理”的意思是 “逻辑上这些文件位置可以复用”，但是并没有删除文件，也没有把文件变小），ibdata文件也不会变小

       （回滚日志什么时候删除？系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删）

       多事务同时执行的时候，可能会出现的问题：脏读、不可重复读、幻读

 

     在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。那么在读未提交里面，另一个事务也没有commit，这个是怎么查询到插入或修改的数据的？是从buffer_pool中读到的

    数据库的索引

                可以使用的索引模型：数组、hash、搜索树

                索引的维护-页分裂（不仅降低效率，还会降低空间使用率，大概50%，自增主键可以防止页分裂，删除是逻辑删除而非物理删除会避免页合并）

               B+树的叶子节点是page （页），一个页里面可以存多个行，索引只能定位到page，page内部怎么去定位行数据？

              答：内部有个有序数组，二分法

           你可以想象一下一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。这里有点不理解，为什么树高20就是20个数据块？
          答：  每个叶子结点就是一个块，每个块包含两个数据，块之间通过链式方式链接。树高20的话，就要遍历20个块。因为是二叉树结构，每次指针查找很大概率是触发随机磁盘读（比如很难刚好碰上一个节点和他的左右儿子刚好相邻）

 

       访问磁盘和内存索引涉及磁盘(sata，ssd，nvm)读写性能，以及内存读写性能，可否给一些数值方便直观认识?

  L1 cache reference 0.5 ns
Branch mispredict 5 ns
L2 cache reference 7 ns
Mutex lock/unlock 100 ns
Main memory reference 100 ns
Compress 1K bytes with Zippy 10,000 ns
Send 2K bytes over 1 Gbps network 20,000 ns
Read 1 MB sequentially from memory 250,000 ns
Round trip within same datacenter 500,000 ns
Disk seek 10,000,000 ns
Read 1 MB sequentially from network 10,000,000 ns
Read 1 MB sequentially from disk 30,000,000 ns
Send packet CA->Netherlands->CA 150,000,000 ns

 

 

   “N叉树”的N值在Mysql是否可以被调整？

 

左侧索引：

索引下推：

 

 

 

mysql innodb中的行锁：

        A 在执行完两条 update 语句后，持有哪些锁，以及在什么时候释放。你可以验证一下：实际上事务 B 的 update 语句会被阻塞，直到事务 A 执行 commit 之后，事务 B 才能继续执行

  经过测试，行锁应该是把匹配条件的行进行锁住，执行完DML后不会立即释放锁，commit后才会释放。假如事务B中是这样的 update t set k=k_2 where id = 13,这个update就不需要等待事务A 的commit，因为他们锁的的是不同行的数据，假如是update t set k=1 这个也不算表锁，是全部行的锁。

 

 

    多线程高并发情况下，会使cpu升高，假设有100的并发，检查死锁的复杂度就会是O（100*100）=10000，假如说两个事务出现相互等待锁的情况就会出现死锁，例如：

        事务A

begin;
update t2 set c = c+1 where id=1;


update t2 set c = c+1 where id=2;

commit;

     事务B：

       

begin;
update t2 set c = c+1 where id=1;


update t2 set c = c+1 where id=2;

commit;

 两个事务类似这种执行顺序

在navicat客户端会报死锁

update t2 set c = c+1 where id=1
> 1213 - Deadlock found when trying to get lock; try restarting transaction
> 时间: 0.107s

      

    

       .innodb支持RC和RR隔离级别实现是用的一致性视图(consistent read view)       

     事务的启动

     读当前 current read

     高低水位

  
共享锁  select k from t where id=1 lock in share mode;
写锁/排它锁     select k from t where id=1 for update;

 

 

事务的可重复读的能力是怎么实现的？

 

既然可重复读的隔离级别是读当前，那跟读提交有什么区别？

答，1假如在一个事务A中没有做update，只有查询，那么最后的查询结果就是这个快照的开始查询结果。

       2 如果在这个事务A中有update，并且在update之前其他事务B已经进行了update的事务提交，那么A得先读取Bupdate的结果再进行事务A的update

      对于可重复读，查询只承认在事务启动前就已经提交完成的数据；

      对于读提交，查询只承认在语句启动前就已经提交完成的数据；

 

事务是如何实现的MVCC？

  (1)每个事务都有一个事务ID,叫做transaction id(严格递增)
(2)事务在启动时,找到已提交的最大事务ID记为up_limit_id。
(3)事务在更新一条语句时,比如id=1改为了id=2.会把id=1和该行之前的row trx_id写到undo log里,
并且在数据页上把id的值改为2,并且把修改这条语句的transaction id记在该行行头
(4)再定一个规矩,一个事务要查看一条数据时,必须先用该事务的up_limit_id与该行的transaction id做比对,
如果up_limit_id>=transaction id,那么可以看.如果up_limit_id<transaction id,则只能去undo log里去取。去undo log查找数据的时候,也需要做比对,必须up_limit_id>transaction id,才返回数据

 

 

.为什么rr能实现可重复读而rc不能,分两种情况
(1)快照读的情况下,rr不能更新事务内的up_limit_id,
    而rc每次会把up_limit_id更新为快照读之前最新已提交事务的transaction id,则rc不能可重复读
(2)当前读的情况下,rr是利用record lock+gap lock来实现的,而rc没有gap,所以rc不能可重复读