mysql学习笔记

图片

具体流程

连接方式：长连接或者短连接

MySQL使用了半双工的通信方式

缓冲池 Buffer Pool

redo Log Buffer

undo log

bin log

1、原子性：数据库事务不可分割的单位，我们对数据库的一系列的操作，要么都是成功

2、一致性：事务的操作不会改变数据库的状态，比方说唯一约束

3、隔离性：事务是相互不可见的

4、持久性：事务一旦提交，即使宕机也是能恢复的

1、扁平事务：使用最频繁的事务，要么都成功提交，要么都失败回滚

2、带有扁平点的扁平事务：允许事务回滚到同一个事务中比较早的一个状态

3、链事务：回滚到最近的一个保存点，在所有的事务都提交之后才会释放锁，并且下一个事务的开始需要上一个事务来进行通知

4、嵌套事务：树结构，只有当父级事务提交之后子级事务才会提交，任意一个父级事务的回滚都会导致下面的子级事务回滚

5、分布式事务：操作两个不同的数据库，使其实现数据的同步，例如将中国银行的钱转到工商银行，这个不同银行的不同数据库，为分布式事务

1.脏读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新，然后B回滚操作，那么A读取到的数据是脏数据。

2.不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3.幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

1.读未提交（READ UNCOMMITTED）：未提交读隔离级别也叫读脏，就是事务可以读取其它事务未提交的数据。

2.读已提交（READ COMMITTED）：在其它数据库系统比如 SQL Server 默认的隔离级别就是提交读，已提交读
隔离级别就是在事务未提交之前所做的修改其它事务是不可见的。

3.可重复读（REPEATABLE READ）：保证同一个事务中的多次相同的查询的结果是一致的，比如一个事务一开始
查询了一条记录然后过了几秒钟又执行了相同的查询，保证两次查询的结果是相同的，可重复读也是 mysql 的默认隔
离级别。

4.可串行化（SERIALIZABLE）：可串行化就是保证读取的范围内没有新的数据插入，比如事务第一次查询得到某个
范围的数据，第二次查询也同样得到了相同范围的数据，中间没有新的数据插入到该范围中。

图片

自动开启

手动开启事务

还有一种情况，客户端的连接断开的时候，事务也会结束。

图片

两大实现方案

InnoDB

1,Normal:普通的索引;允许一个索引值后面关联多个行值;

2,UNIQUE:唯一索引;允许一个索引值后面只能有一个行值;之前对列添加唯一约束其实就是为这列添加了一个unique索引;当我们为一个表添加一个主键的时候,其实就是为这个表主键列(设置了非空约束),并为主键列添加了一个唯一索引;

3,Fulltext:全文检索,mysql的全文检索只能用myisam引擎,并且性能较低,不建议使用;

1,b-tree:是一颗树(二叉树,平衡二叉树,平衡树(B-TREE);
使用平衡树实现索引，是mysql中使用最多的索引类型；在innodb中，存在两种索引类型，第一种是主键索引（primary key），在索引内容中直接保存数据的地址；第二种是其他索引，在索引内容中保存的是指向主键索引的引用；所以在使用innodb的时候，要尽量的使用主键索引，速度非常快；

2,hash:把索引的值做hash运算，并存放到hash表中，使用较少，一般是memory引擎使用；优点:因为使用hash表存储，按照常理，hash的性能比B-TREE效率高很多。
hash索引的缺点：
1，hash索引只能适用于精确的值比较，=，in，或者；无法使用范围查询;
2，无法使用索引排序；
3，组合hash索引无法使用部分索引；
4，如果大量索引hash值相同，性能较低；

B+tree

1，较频繁的作为查询条件的字段应该创建索引；

2，唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；
作为索引的列,如果不能有效的区分数据,那么这个列就不适合作为索引列;比如(性别,状态不多的状态列) 
举例:SELECT sum(amount) FROM accountflow WHERE accountType = 0;
假如把accountType作为索引列,因为accountType只有14种,所以,如果根据accountType来创建索引,最多只能按照1/14的比例过滤掉数据;但是,如果可能出现,只按照该条件查询,那我们就要考虑到其他的提升性能的方式了;

3，更新非常频繁的字段不适合创建索引；原因,索引有维护成本;

4，不会出现在WHERE 子句中的字段不该创建索引；

5, 索引不是越多越好;(只为必要的列创建索引)
1,不管你有多少个索引,一次查询至多采用一个索引;(索引和索引之间是独立的)
2,因为索引和索引之间是独立的,所以说每一个索引都应该是单独维护的;数据的增/改/删,会导致所有的索引都要单独维护;

索引的创建原则

MyISAM索引文件和数据文件是分离的(非聚集)

InnoDB索引实现(聚集)

1.冗余数据的处理（可以提高系统的整体查询性能,三范式）

2.垂直分库

3.水平分表

4.数据分片规则

1,JOIN的原理:
在mysql中使用Nested Loop Join来实现join;
A JOIN B：通过A表的结果集作为循环基础，一条一条的通过结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果；

2,JOIN的优化原则:
1，尽可能减少Join 语句中的Nested Loop 的循环总次数，用小结果集驱动大结果集
2，优先优化Nested Loop 的内层循环
3，保证Join 语句中被驱动表上Join 条件字段已经被索引
4，扩大join buffer的大小；

1.Explain:可以让我们查看MYSQL执行一条SQL所选择的执行计划

2.Profiling:可以用来准确定位一条SQL的性能瓶颈；

思路

原则