mysql理论技能_MySQL 高阶技能

MySQL 高阶技能

一、底层原理

1、数据组织方式

B+Tree

2、逻辑架构

C - S 架构

客户端

查询处理器

数据存储引擎

3、客户端使用

增、删、改、查

二、索引

MySQL的InnoDB索引数据结构是B+树，主键索引叶子节点的值存储的就是MySQL的数据行，普通索引的叶子节点的值存储的是主键值

MySQL索引用的是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

1、B 树和 B+树有什么不同呢？

第一，B 树一个节点里存的是数据，而 B+树存储的是索引(地址)，所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。

第二，B+树的叶子节点是数据阶段用了一个链表串联起来，便于范围查找。

2、B+树优势

首先，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。

其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。

3、聚集索引、非聚集索引、组合索引、唯一索引

聚集索引：主键索引，B+树 叶子节点上保存的为 数据行 信息。其他节点上的值为主键的值。

非聚集索引：普通索引，B+树 叶子节点上保存的为 对应数据行主键的值，其他节点上的值为索引字段对应的值。

组合索引： 叶节点中除了有主键的值以外，还有对应组合字段的值，可直接返回。省去了通过主键查找聚集索引获取数据的步骤。最左匹配原则

唯一索引：数据唯一，可存在多个唯一索引。

问题：

1、为什么创建主键？

答：将表数据的存储结构转换为树格式，将给整张表按主键创建了一个聚集索引。

2、为什么加索引后会使查询变快？

答：因为，加索引后，查询由逐行顺序查找，转换为逐层平衡树查找，减少了磁盘IO次数。

3、为什么加索引后会使写入、修改、删除变慢？

答：因为索引也是一份数据，每次创建一个索引，都会将对应字段的数据复制一份出来，用于生成索引。这样会增加表的体积，增加磁盘空间占用量。

并且会导致索引数据的变更，为了维护索引的正确性，会导致索引的调整。

4、什么情况下要同时在两个字段上建索引？

答：需要快速通过一个字段，查询出组合字段的值的时候。

三、数据存储引擎

InnoDB 引擎的底层实现(聚集索引方式)(默认)

MyISAM 引擎的底层实现(非聚集索引方式)

四、事务

刚性事务：ACID：原子性，一致性，隔离性，持久性。

五、隔离问题

脏读：一个事务在处理过程中读取了其他事务未提交的数据。

不可重复读：不可重复读是读取了其他事务已提交的数据。

幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

说明：幻读和不可重复读都是读取了其他事务已经提交的数据(这点就脏读不同)，

所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体(比如数据的个数)。

六、隔离级别

隔离程度越强，事务的执行效率越低

1、Serializable(串行化)：花费最高代价但最可靠的事务隔离级别。

“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

事务 100% 隔离，可避免脏读、不可重复读、幻读的发生。

2、Repeatable read(可重复读，默认级别)：多次读取同一范围的数据会返回第一次查询的快照，即使其他事务对该数据做了更新修改。

事务在执行期间看到的数据前后必须是一致的。

但如果这个事务在读取某个范围内的记录时，其他事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行，这就是幻读。

可避免脏读、不可重复读的发生。但是可能会出现幻读。

3、Read committed (读已提交)：保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该事物未提交的数据。

可避免脏读的发生，但是可能会造成不可重复读。

大多数数据库的默认级别就是 Read committed，比如 Sql Server , Oracle。

4、 Read uncommitted (读未提交)：最低的事务隔离级别，一个事务还没提交时，它做的变更就能被别的事务看到。

任何情况都无法保证。

七、锁

1、记录锁(Record Locks)

对单条索引记录进行加锁，锁住的是索引记录而非记录本身，即使表中没有任何索引，MySQL会自动创建一个隐式的row_id作为聚集索引来进行加锁。

2、共享锁/排他锁(Shared and Exclusive Locks)

事务拿到某一行记录的共享S锁，才可以读取这一行，并阻止别的事物对其添加X锁

事务拿到某一行记录的排它X锁，才可以修改或者删除这一行

共享锁的目的是提高读并发

排他锁的目的是为了保证数据的一致性

3、意向共享锁/意向排他锁(Intention Shared and Exclusive Locks)

意向共享锁：预示事务有意向对表中的某些行加共享S锁

意向排他锁：预示着事务有意向对表中的某些行加排他X锁

4、间隙锁(Gap Locks)

封锁记录中的间隔，防止间隔中被其他事务插入。

间隙锁主要出现在RR隔离级别，避免出现幻读。

5、临键锁(Next-key Locks)

临键锁是记录锁和间隙锁的组合，既锁住了记录也锁住了范围。

临键锁的主要目的，也是为了避免幻读。

6、插入意向锁(Insert Intention Locks)

插入意向锁是间隙锁的一种，针对insert操作产生。

目的是提高插入并发。

7、自增锁(Auto-inc Locks)

自增长锁是一种表级锁，专门针对auto_increment类型的列。

八、树

二叉搜索树：二叉树，每个结点只存储一个关键字，等于则命中，小于走左结点，大于走右结点；

B(B-)树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；

B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；

B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；

九、乐观锁

乐观锁，大多是基于数据版本(Version)记录机制实现。

何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。

读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。

此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

1、使用版本号实现乐观锁

UPDATEt_goodsSET status=2,version=version+1

WHERE id=#{id} AND version=#{version};

2、使用限制条件实现乐观锁

UPDATEt_goodsSET num = num -#{buyNum}WHEREid=#{id}AND num - #{buyNum} >= 0

AND STATUS = 1

注意：乐观锁的更新操作，最好用主键或者唯一索引来更新,这样是行锁，否则更新时会锁表

十、悲观锁

悲观并发控制(又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”)是一种并发控制的方法。

它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作读某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

select * from tbl_user where id=1 for update;

参考资料