mysql 总结

mysql 

引擎:
innodb  聚簇索引 (叶子节点存了所有的行数据)
myisam   非聚簇索引（叶子节点存行id）

事务的隔离级别
    ACID 
    原子性:事务作为最小执行单位，事务要么全部被执行，要么全部不执行。
    一致性:事务的执行使得数据库从一种正确状态转换成另外一种正确状态。
    隔离性:事务与事务之间互不影响。
    持久性:事务正确提交之后，其结果将永远保存在数据库之中，即使在事务提交之后有了其他故障，事务的处理结果也会得到保存。
事务的隔离级别：
    read uncommit:  读到未提交的事务数据。 （脏读，不可重复读，幻读）
    read commit  :  读到提交后事务的数据。  （不可重复读，幻读）
    repeat read  :  多次读取的数据都是一样的。 （幻读）mysql 默认
    searial      :  串行化，一个事物一个事物的执行。

事务的实现原理是 undolog 和redolog 一起来决定的。
1.数据库表行数据隐藏字段 DB_TRX_ID (当前事务ID),DB_ROLL_PTR(回滚指针,对应的是undolog),DB_ROW_ID(6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引)
2.undolog 保证了事务提交之前数据操作的反向日志，比如说：insert 操作，undolog 会保存delete 相反的语句，这样就可以实现事务的回滚。
3.redolog 物理日志，记录的是数据页的物理修改,用来恢复提交后的物理数据页。解决了mysql 宕机，可以恢复数据。
4.binlog 数据修改的操作日志，对slave 提供热备份功能。

索引B+tree 结构，DataPage

dataPage 默认大小为16k 

DataPage 里面包含了多个data (B+tree 的叶子节点)
这里要注意为什么要求id 最好自增，自增的话不会破坏老的datapage ,(3,5) 插入4 就会变成 (3,4)(5)这样就会对datapage 进行修改，还有移动数据的操作还会对索引树进行维护。

MVCC 多版本数据控制
多数据版本控制，实现原理undolog 来控制不同的数据版本，从而对于读和写互补影响。

索引分类：
1、主键索引
2、Hash
3、组合
4、唯一索引
5、全文索引

查询索引注意：

1.索引下推： mysql 5.6 之后 ，执行计划中Using index condition.表示使用索引下推 。 例如： （A,B） 为索引  C 是普通条件，查询ABC 没有索引下推会通过执行引擎查询AB 数据后在内存里面在对于C 进行筛选，开启索引下推C条件传给了引擎，直接返回结果。

2.索引覆盖： 索引覆盖的意思是查询的数据在索引中能够获取到值，不用在做回表操作

3.最左匹配原则: 组合索引，（A,B,C）where A= and B= and C= 命中，注意   where  B= and C= and where A= and C= 无法命中

4.回表：  根据索引查询主键ID ,再通过主键ID 查询主键b+tree 这样可以取到全数据的data

索引失效
1.in() 过多的数据，优化器会认为数据量过大，还不如走全表。
2.or ，like '%%'和'%'',函数等都会导致索引。
3.索引的长度，索引优先选取长度短的。
4.条件不一致，"1" 使用  1 查询 。
5.！= 不走索引

执行计划
explain select * from 

id  select_type table partition type prossiblekey key key_len ref rows filtered extra

1.select_type:
SIMPLE 简单查询
PRIMARY 主键最外层查询
SUBQUERY 映射为子查询
DERIVED 被驱动只查询
UNION 联合
UNION RESULT 使用联合结果

2.table: 表名

3.type 类型
ALL 全表
index  全索引查询
RANGE 范围索引查询
INDEX_MERGE 合并索引
CONST 常量

4.possible_keys : 可能使用的索引

5.key : 真实使用的索引

6.key_len : MySQL中使用索引字节长度

7.rows : mysql 预估为了找到所需的行而要读取的行数

8.filtered : 过滤多少条数据

9.extra:
Using index:    此值表示mysql将使用覆盖索引，以避免访问表
Using where: mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
Using temporary: mysql 对查询结果排序时会使用临时表
Using filesort： mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行

锁：
行锁，锁的是b+tree 叶子节点。
表锁，锁的是整个表。
排他锁，修改操作，insert delete update   共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁，只能一个修改 lock in share mode;（update,insert,delete）语句会自动加排它锁。
共享锁，查询操作 共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁，只能查询 for update。
乐观锁，update set A=1 where A=3 。
悲观锁，for update。
间隙锁，间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

索引结构:
b+tree 和 b tree 区别
b tree   
    每个节点存值。
b+tree
    叶子节点存值。分支节点存的是范围值。所有的叶子节点组成了一个双向的链表结构，      并且多个叶子节点存放在一个DataPage ,这样可以减少磁盘的I/O 读写。减少了b+tree 的层级，从而缩减了查询的平均时间。

主键索引：
1.设置主键索引，没设置主键则会采用唯一索引，如果也没有唯一索引，那么会自动以DB_ROW_ID 产生一个6bit 的聚簇索引。

组合索引和唯一索引：
叶子节点存的数据是主键ID.