mysql学习笔记
数据库事务的四大特性
原子性：事务是不可分割的最小单元，一个事务的操作要么完全成功，要么全部失败。
一致性：事务对同一数据的读取结果是相同的。
隔离性：一个事务的修改在最终提交之前，对其他事务是不可见的。
持久性：事务一旦提交，其所做的修改会永久保存在数据库中。
隔离级别
未提交读：事务的修改即使没有提交，对其他事务也是可见的
提交读：事务所做的修改在提交之前，对其他事务是不可见的（防止脏读）
可重复读：保证同一事务中多次读取同一数据的结果是相同的。（防止脏读、不可重复读）
可串行化：强制串行执行，事务之间互不干扰。（防止脏读，不可重复读，幻读）
InnoDB存储引擎和MylSAM存储引擎的区别
MylSAM存储引擎
- 不支持事务，最小的锁粒度是表锁
- 缓冲池只缓存索引文件，不缓冲数据文件
- 由MYD和MYI文件组成，MYD用来存放数据文件，MYI用来存放索引文件
InnoDB存储引擎
- 支持事务
- 独立表空间，支持MVCC，行锁设计，提供一致性非锁定读
- 支持外键，插入缓冲，二次写，自适应哈希索引，预读
- 使用聚集的方式存储数据，每张表的存储都是按主键顺序存放
InnoDB日志文件
日志文件是用于记录系统操作时间的记录文件或文件集合
类型：
错误日志：遇到问题应该查看该文件以便定位问题
慢查询日志：记录执行时间超过某一阈值的所有sql
查询日志：记录所有对mysql数据库的请求信息
二进制日志：记录对MySql数据库执行更改的所有操作，可用于恢复，复制，审计
锁机制
锁是一种保护机制，在多线程的情况下，保证操作数据的正确性，一致性。
行锁和表锁的概念和区别
1.主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁
（1）行锁：访问数据库的时候，锁定整个行数据，防止并发错误。
（2）表锁：访问数据库的时候，锁定整个表数据，防止并发错误。
2.各种锁：
表锁：开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低
行锁：开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
悲观锁：悲观的认为每次去拿数据的时候别人都会修改，所以每次在拿数据的时候都会上锁，比如行锁，表锁等都是悲观锁。
乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量。
共享锁：对于多个不同的事物，对同一个资源共享一把锁。是悲观锁的一种。
独占锁：该锁一次只能被一个线程所持有。
MVCC:是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，是乐观锁的一种实现方式。
乐观锁是快照读。
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。写写冲突由乐观锁去解决。
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。
所以MVCC可以为数据库解决以下问题：
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。
索引的优缺点：
1.创建唯一性索引，保证数据库表中每一行数据的唯一性
2.大大加快数据的检索速度，这也是创建索引的最主要的原因
3.减少磁盘IO（向字典一样可以直接定位）
索引的缺点
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2.索引需要占用额外的物理空间
3.当对表中的数据进行增加、删除和修改的时候
索引也要动态的维护，降低了数据的维护速度
索引的数据结构（B+树）
目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构
B+树的特点：
- 平衡树，每个结点到叶子结点的高度都是相同的
- 层数比较小
- 非叶子结点的子树指针与关键字个数相同
- 非叶子结点相当于是叶子结点的索引，叶子结点相当于树存储数据的数据层
- 为所有的叶子结点增加一个链指针
应用
在文件系统以及数据库中做索引
什么说B+树比B树更适合数据库索引？
- B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
- B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当
- 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
总结 数据库索引采用B+树的主要原因是：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。
聚集索引
mySQL主键就是聚集索引
数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
我们的sql数据库是行数据库，数据是一行一行存储的，而聚集索引是个特殊的索引，相当于这一行行记录的物理编号，描述这一行行数据的物理存储顺序。所以，一张表只会有一个聚集索引。
最左前缀匹配原则在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
创建索引的几大原则
- 索引不是越多越好。 每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构。因此，索引越多，所花的时间越长。
- 不要对经常变动的数据加索引。
- 小数据量的表没必要加索引。
- 索引一般加在常用来查询的字段上。 最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。