mysql的秘密你知道吗?

一 亮瞎眼的重点(超级红线)

面向对象:mysql作为我们这些屌丝族的入门级学习研究的数据库,本文为那些入门求提升,小白求学习的同志们编写,大佬们就勿入了,篇幅字拙,恐怕难入眼

图片:图片来源互联网,只为说明问题,侵权请联系秒删

二 MySQL的数据库存储引擎

 myisam:

支持表锁,不支持行锁；不支持事务和外键；不支持mvcc并发读写；索引数据类型是B+tree,全部是非聚簇索引,即B+tree树中的节点中存储的是数据文件的地址,主打性能,查询的速度快
 innodb:

支持表锁,支持行锁,支持事务和外键,支持mvcc并发读写,索引的数据类型是B+tree,每个表都会根据主键建立聚簇索引，如果表没有主键,会自动创建,只是用户不可见,聚簇索引索引和数据是存储在一起的,都存储在叶子节点中,同时也对数据备份,主从复制做了支持,binlog文件其他索引是辅助索引,叶子结点存放的是对应行的主键值，获取主键值之后，需要再次去主键索引表中查询该主键对应的记录，获取其叶子结点存储的记录内容（mysql5.5版本以后的默认存储引擎）
 memory:

支持表锁,不支持行锁;不支持事务和外键;支持mvcc并发读写;索引是hash;数据是放在内存中的,查询速度超级快,由于内存有限制,要求存储数据比较小

 

三 索引

(1)概述

 目标:提高查询效率
 缺点:增删改的效率降低了,同时占用内存空间更大了（是一种牺牲空间换时间的操作）
(2) 索引分类 
(2.1)按照字段索引分类:

普通索引

唯一索引：它的特点是索引列的值不允许重复，这样可以防止插入重复的键值

主键索引：

联合索引

外键索引

全文索引 mysql5.5版本以后不支持全文索引
(2.2)按照数据结构划分:

     hash索引

     B+Tree索引
(2.3)按照数据存储方式:
非聚簇索引: myisam典型 索引和数据文件是分离的,使用 B+Tree 作为索引结构,索引叶子节点的 data 域存放的是数据记录的地址,主键索引和辅助索引是独立的,结构也是一致的

聚簇索引:   innnodb典型, B+tree树结构中主键索引的叶子节点中主键和数据存储在一起,B+tree树结构中辅助索引的叶子节点中存储的k是辅助索引v是主键索引, 主键索引和数据在B+tree的叶子节点中绑定,所以称为聚簇索引
检索数据: 通过主键索引查找数据直接可以得到数据,通过辅助索引查找数据会先查主键索引,然后在根据主键索引找数据,检索了两遍

 优点：

1 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

 2 聚簇索引对于主键的排序查找和范围查找速度非常快,(根据主键进行了排序)
 缺点：

 1 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式(根据索引排序)，否则将会出现页分裂(维护B+tree结构就很浪费性能)，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键,若是,这个主键对用没有建主键,则会默认生成一个主键户不可见,依赖这个主键构建B+tree,主键变更维护成本比较高

 2  二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

(3) 索引的数据结构:

 (3.1) b-tree(即btree)和b+tree的数据结构

   导学(这个视频讲的超级好) : https://www.bilibili.com/video/BV1x5411o7fg/?spm_id_from=333.337.search-card.all.click&vd_source=4ee7f19a5103a71f9eac5901fe201aef

   b-tree:

     内节点和叶子节点都存储的是kv对,且是排序的,看Btree图

     缺点: 内节点存储kv对,相比b+tree就会存储的k少,同量数据下树高于b+tree,检索效率低一点

     优点: 相对b+tree,不会重复k,省存储空间

   b+tree:

     内节点只存储k,也就是索引,叶子节点才存储kv对,所有节点都是排序了的

     在内节点中只存储k,在叶子节点也会将k和v以键值对的方式存储一下

     相邻节点之间有双向指针(即相邻节点形成双向链表)

     缺点: 内节点和叶子节点都会存储k,造成k会重复,浪费存储空间

     优点: 同量数据相对b-tree树高低,且相邻节点之间有双向指针,效率高,

     注意: b+tree检索数据时候都会检索到叶子节点,真实的数据就存储在叶子节点中

   b-tree和b+tree对比:

   相同: 都是二叉树的变异,b+tree又是b树的变异

   不同: b-tree所有节点存储的都是kv对,b+tree内节点存储的是k,只有叶子节点存储的是kv对

        b-tree相邻节点之间没有指针,b+tree相邻节点只有双向指针

   b+tree索引总结:

    b+tree的内节点存的是检索元素的k,叶子节点存储的是经过排序具体的数据,各个相邻节点通过双向指针形成链表,检索数据时候一次从内存中读取一页(16kb)的数据,在进行二分查找定位数据

 (3.2) 数据库存储使用b+tree不使用红黑树的原因:

  红黑树是单节点的,一个节点一个节点地存储数据,在数据量相同的情况下,红黑树的高度高,遍历查找效率低

  b+tree的节点是多树叉的,可以存放更多的数据,在相同数据量的情况下树的高度比红黑树低,遍历查找数据效率高很多

  添加元素的特点:

     红黑树是从上往下增加元素

     b+tree树是从下往上增加元素的

  红黑树的特性:

  （1）每个节点或者是黑色，或者是红色。

  （2）根节点是黑色。

  （3）每个叶子节点（NIL）是黑色。

  （4）如果一个节点是红色的，则它的子节点必须是黑色的。

  （5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。

(4)innodb 不使用hash索引的原因:

  hash要求数据全部加载进内存,数据库的数据量比较大,没有那么大的内存,或者有也是浪费内存,引入btree,一次读取一页16kb数据到内存,

  然后在排存放,后面根据条件进行检索,可以在有限的内存空间上完成较高的效率

 

四 锁分类

 排他锁: 写锁,当前线程在工作的时候不允许其他的线程操作当前的数据

 共享锁: 读锁,允许其他线程读取数据,但是不允许修改数据

 乐观锁:..

 悲观锁:..

 锁的体现:

   表锁: 对整张表进行加锁,加锁方便按,并发不好,容易阻塞,不会形成***锁

   行锁: 加锁比较慢,并发性相对较好,但是容易***锁

五 数据库约束

数据库约束即对于表中数据单元的要求:

  主键约束:

  唯一约束:

  非空约束:

  默认约束:

  外键约束:

六 事务:

(1)概念: 对数据的一次操作是一个完整的整体,要不全部成功,要不全部失败 

(2)事务的特征(acid):

    原子性:全部成功,或者全部失败

    一致性:事务前后，数据库的数据应该保持一致

    隔离性:各个事务不相互影响，为各个线程复制一份数据,各个线程相互独立工作,互不干扰

    持久性:事务完成,数据库的数据发生永久性改变,不可恢复

(3)事务的隔离级:

 读未提交