MYSQL相关知识

一.存储引擎选择

>InnoDB：是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

>MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事物的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适。

>MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

 

二.遵循事务的四大特性ACID

A 原子性 保证sql要么全成功，要么全失败。通过undo log日志实现，在执行前进行快照保存，然后再利用undo log回滚。

C 一致性 保证sql执行完数据库的约束没有被破坏，主要通过主键、约束、数据类型、长度等来进行控制。

I 隔离性 保证sql执行的过程不受其他事务影响。通过锁和MVCC来进行控制，MyISAM只支持表锁，InnoDB支持行锁和表锁。InnoDB执行select语句是不加锁，在执行DML时会加排他锁。隔离级别分读未提交、读已提交、可重复读、串行化。mysql默认隔离级别是可重复读。

锁分为共享锁、排他锁、共享意向锁、排他意向锁、自增锁。

共享锁在语句后加lock in share mode

排他锁则在语句后加for update

 

读未提交无法避免脏读、不可重复读、幻读

读已提交无法避免不了重复读、幻读

可重复读无法避免幻读

MVCC-多版本并发控制机制:基于undo log版本链和read view来达成。undo log会在每次执行update或delete时生成一条记录，其中根据roll_pointer来进行串连。然后每次select的时候会进行read view将所有版本统计出来。①判断读取事务id=当前创建id，相等则意味着自己当然可以查看自己修改的。②判断读取事务Id小于最小事务id,则意味着该版本在read view生成之前已经提交，可以直接访问。③判断读取事务id大于最大事务id，则意味着该版本在read view生成之前才开启，肯定不能访问。④判断读取事务id在最大和最小事务id之间,并且不在活跃事务列表中，则意味着该版本已经被提交，可以被访问。

 

三.EXPLAIN

type列:

system 对主键等值查询

const 对唯一键等值查询

eq_ref 联表查询中关联的条件是主键或唯一键

ref 满足索引的最左原则，同时不走主键或唯一键

ref_or_null 在ref基础上增加null值查询

index_merge 多个搜索结果合并为一个，统一回表查询数据

range 范围查询，包括between、lile、in、大于、小于等

index 全表扫描，只查询索引树结果

all 全表查询

 

extra列：

using index 查找的数据都在索引中，不需要回表查询

using index condition 搜索时需要大量回表会先在存储引擎进行过滤

using where 所有过滤都在service层进行操作

using join buffer BNL 两个表关联，A表有索引，B表没有索引

using join buffer BKA 两个表关联，A表有索引，B表有索引

using union(indexs) 多个查询条件都是索引，就会对索引进行合并

using temporary 语句使用了临时表 group by、distinct、union

using filesort order by

 

 

每个表最多4096个字段，行数据限制64kb