【MySQL】索引和事务（B+树、聚簇非聚簇索引、ACID、隔离级别、MVCC）

秃头侠们好呀，今天来说 索引和事务

主键

primary key 用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表只能有一个主键，主键所在列通常为整数类型

唯一键

一张表中有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，唯一键就能解决需要唯一性约束的字段。

主键VS唯一键

他俩并不冲突，一张表中可以有主键也可以有唯一键。其实他都是对数据的一种约束。
比如一个人，它具有很多唯一性的属性，身份证号，指纹，QQ号，手机号等，它也有不是唯一性的比如身高体重生日。
主键的设置不是因为我们设置了它才具有唯一性，而是它本来就是唯一性，我们把它设为主键了。因为主键之能设置一个，所以没有被设为主键，但也是唯一性的数据就是唯一键。
他俩相互配合，约束数据库，满足自然界的业务逻辑。

外键和外键约束

外键用于定义主表和从表之间的关系，外键约束主要定义在从表上，主表则必须是有主键或unique约束，当定义外键后，要求外键列数据必须在主表的主键列存在或为null。从表的外键是主表的主键。
通过外键产生关联的表，除了在逻辑上要有关联，mysql在操作上也要维护这种逻辑关系的正确性，不光有外键就行，还要有外键约束，保证正确性。
外键是用来实现表和表之间关系的字段，其实是我们自己知道但是只要是人操作就会犯错，为了强调约束之间的关系，需要添加外键约束，有了约束，mysql就会为你维护表之间的逻辑关系。

create table class(
    id int primary key,
    class_name varchar(16)
);
create table stu(
    id int primary key,
    name char(10) not null,
    class_id int,
    foreign key(class_id) references class(id)
);

索引

说说 MySQL 索引，以及它们的好处和坏处?

提高数据库的搜索性能，它其实就是数据的目录，就跟一本书的目录一样，在mysql内，将数据以B+树的形式将所有page页进行组织，形成数据结构与其配套的查找算法，就是索引。查询效率的提高是以插入更新删除为代价的，毕竟你要维护B+树，以空间换时间。所以索引的价值在于提高海量数据的检索速度。常见的有主键索引，唯一键索引，普通index索引，全文索引。

为什么用B+不用其他的?

链表：线性遍历效率低。
二叉树：可能退化成单边树，与链表一样。
AVL红黑树：度最大为2，树的层数没有B+更矮胖，红黑树最坏要查找树的高度次，当数据量很大，就需要大量的page页IO次数，而B+一般为234层，需要更少的page页，更少的IO效率更高。查找的过程就是排除的过程，B+树一次可以排除更多。
哈希：哈希mysql支持但是存储引擎InnoDB，myISAM不支持，而且哈希需要全部加载到内存，当数据量很大，全部加载内存不现实，B+树可以分段加载需要的节点数据，可以在内存资源有限的前提下，极大提高查询效率。
B树：叶子结点不相连，除了叶子结点，其他结点也带有数据，B+树只有叶子结点有数据，且结点之间相连，相连的好处就是范围搜索，局部性原理。而且B+因为除了叶子节点的其他节点不存数据，就可以管理更多的page，就会更矮胖，IO更少经历的page越少。

聚簇索引，非聚簇索引?

InnoDB是聚簇索引，MYISAM是非聚簇索引
非聚簇索引的数据和索引page是分离的，MYISAM引擎底层也使用B+树，但是叶子节点没有存放数据，而是对应数据的地址，但InnoDB是放一起的
非聚簇的主键索引和普通索引没有差别。
聚簇索引如果是普通索引(辅助索引)，叶子节点不放数据，而是放对应数据的key主键值，聚簇索引第一次先找到主键，再用主键索引找到记录，需要二次查找，回表查询。
MYISAM更适合查找，但是它不支持事务。

事务

是应用层的概念，站在用户的角度，通过多条sql语句原子性的完成某项任务，就叫做事务。
当然一个完整的事务不是简单的sql集合，还需要满足四性：原子性、一致性、隔离性、持久性 ACID。

介绍一下数据库的 ACID？

事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。在事务中的操作，要么都执行修改，要么都不执行，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。 事务需遵循ACID四个特性： - A（atomicity），原子性。原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。 - C（consistency），一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。 - I（isolation），隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，这通常使用锁来实现。让同时启动，并发执行的各个事务，看到不同的数据修改，就叫做隔离性，我们作为一个事务，可以看到不同可见性的数据，程度不同，就叫做隔离级别 - D（durability） ，持久性。事务一旦提交，其结果就是永久性的，即使发生宕机等故障，数据库也能将数据恢复。持久性保证的是事务系统的高可靠性，而不是高可用性。

MySQL 的事务隔离级别

读未提交（READ UNCOMMITTED）； - 读提交 （READ COMMITTED）； - 可重复读 （REPEATABLE READ）； - 串行化 （SERIALIZABLE），事务隔离是为了解决脏读、不可重复读、幻读问题。
读未提交：一个事务可以读到另一个事务未提交的记录，不管你是否commit，这种相当于没有隔离性，存在脏读，幻读，不可重复读的问题。脏读是一个事务执行中读到另一个执行中事务的更新或其他操作但未commit的数据。
读提交：一个事务只能看到其他已经commit的事务的结果。但是没有解决不可重复读问题。不可重复读是指在一个事务执行中读到的数据有变化，多次读取会读到不一样的值。
可重复读：是mysql默认的级别，一个事务执行中，多次读取操作数据时会读到同样的数据，解决不可重复度，脏读问题。
串行化：就是串行执行，不在并发，效率不高。
并且InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ，但是与标准SQL不同的是，InnoDB存储引擎在REPEATABLE READ事务隔离级别下，使用Next-Key Lock的锁算法，因此避免了幻读的产生。

InnoDB 的 MVCC

多版本的并发控制协议，它是一种用来解决读-写冲突的无锁并发控制机制。在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能，还可以解决脏读、幻读、不可重复读等事务隔离问题。
实现原理主要是依赖记录中的 3个隐式字段、undo日志 、Read View。

1. 隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的自增ID为没有主键的数据添加主键，还有最近创建和修改这条记录事务id、还有最后一个回滚指针，指向undo
   log中的历史版本。
2. 基于undo log的版本链：每行数据的隐藏列中包含了指向历史版本的回滚指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。 当前读是指增删查改最新记录，快照读是指select历史记录。
3. ReadView：是事务进行快照读操作时产生的读视图，在该事务进行快照读那一时刻也就是第一次select的时候，会生成数据库当前情况的一个快照。将读到数据的事务id和快照读的ID进行可见性判断。决定你是能看到最新版本，还是undolog中的历史版本。

RR和RC的区别就是RR级别下，readview一旦形成不会更新，而RC每次select都会更新快照读readview。

C/C++连接数据库

MYSQL*my=mysql_init();创建mysql句柄
mysql_real_connect();连接数据库
mysql_set_character_set();设置编码格式
mysql_query();访问数据库
MYSQL_RES*result=mysql_store_result();解析数据
mysql_num_rows();获取几行
mysql_num_fields();获取几列
MYSQL_FIELD*fields=mysql_fetch_fields();获取表中列名
MYSQL_ROW line=mysql_fetch_row();获取完整一行数据
free(result);
mysql_close(my);关闭数据库

---

⭐感谢阅读，我们下期再见
如有错 欢迎提出一起交流