MySQL的基础原理

MySQL结构

一句SQL做了什么

1.首先MySQL数据库启动，阻塞等待连接
2.客户端连接，经过服务管理模块进行用户信息确认，进行登录
3.客户端发送SQL语句，数据库SQL API接收SQL语句，交给SQL 解析器
4.经过SQL解析器，检查语句（类似于编译的前3步），然后交给SQL 优化器
5.SQL优化器 生成多条执行计划，选择最合适的执行计划进行执行
6.优先从数据库缓存查询数据，如果存在，直接返回到SQL API，如果没有再去数据库磁盘获取数据
7.通过SQL API 将数据发送到客户端

存储引擎

存储引擎就是对数据的存取方式，是基于表建立的。

常用的存储引擎有4种

MyISAM	不支持外键	不支持事务	全文索引	B+树	表锁
InnoDB	支持外键	支持事务	支持全文索引	B+树	行锁

MEMORY
内存上，适合存储临时数据，varchar 默认按照char存储
默认是哈希索引
不支持text、BOLB类型的字段，如果数据有，就会将这些数据存储到磁盘

ARCHIVE
只支持insert与select操作，使用压缩算法将数据进行压缩后存储，一般是1：10比例

存储日志
	记录整个执行过程中每一个操作
	数据量很大，查询少

MyISAM与InnoDB

索引

索引就是一种可以提高数据查询效率的数据结构。索引是基于字段建立的。

如果创建表不指定索引，系统默认创建索引

1.查询是否存在     主键    主键索引
2.查询是否存在唯一键     唯一索引
3.添加一个隐藏字段 6个字节的大小，会设置一个auto_increment字段

索引分类

1.普通索引

创建

create index index_name on table_name(field_name);

alter table tablename add index index_name on (field_name);

creat table tablename(index index_name(field_name));

2.唯一索引

特点：索引列的值必须唯一，但允许有空值（和主键不同），如果是组合索引，则列值的组合必须唯一。

create unique index index_name on table_name(field_name);

3.全文索引

从char,varchar 或者 text 提取一部分作为索引。数据量很大，很消耗硬盘空间。

4.单列和多列索引

每次查询，MySQL都只会使用一个索引，从众多索引选择一个限制最为严格的索引

在多个单列索引的情况下，有可能会发生索引合并

1.where条件的索引列使用or连接时，会触发索引合并
2.where条件的索引列使用and连接时，通常会选择限制最严格的索引，这个时候联合索引（多列索引）比较合适

5.组合索引

id  age  sex

多个字段设置索引，组合查询，但是需要遵循最左前缀原则，由左到右依次组合

id
id age
id age sex

是可以查询的，其他都不可以。

聚集索引与非聚集索引

1.MyISAM

数据和索引分离设计     非聚集索引

2.InnoDB

索引当成数据的一部分存储    聚集索引

1.非聚集索引

B+树叶子节点存储索引值与数据地址，叶子节点也会有指针域，指向下一个叶子节点，形成链表。数据是存储在磁盘的。

2.聚集索引
B+树叶子节点存储索引值与数据，叶子节点也会有指针域，指向下一个叶子节点，形成链表。数据是存储在内存的。

辅助索引

主索引

存储数据的索引
不允许关键字重复

辅助索引

允许关键字重复
叶子节点存储主索引值

辅助索引的B+树中，叶子节点存储的是主索引的值，在依赖辅助索引查询时，依旧需要依赖主索引，即O（2*long2(n))。

索引的优化

索引其实就是用空间换时间。所以

1.哪种情况应该建立索引？

	1.经常用于查询的字段
	2.主键字段
	3.用于连接的字段（多表查询）
	4.范围查询
	5.排序查询
	6.经常需要where查询的字段

2.哪种情况不应该建立索引？

	1.不经常查询的字段
	2.数值较少的字段，比如性别
	3.数据量大的字段，比如某个字段数据量很大
	4.修改操作远远大于查询

索引的注意事项

1.索引项不会包含NULL，如果列中有NULL值，也不会包含在索引中，复合索引只要有一列为NULL，那么这一列对于次复合索引是无效的。

2.使用短索引
使用的索引要尽量短，可以提高IO效率

3.不要索引列排序
索引本来就是排序的

4.使用like要注意
like “%aaa%” no
like “aaa%” yes
第一个字母是通匹 不会用到索引

5.不要再索引项上进行运算
会使索引失效

InnoDB事务

ACID

事务
事务就是一组SQL语句的集合，需要满足ACID四个条件

原子性（Atomicity），可以理解为一个事务内的所有操作要么都执行，要么都不执行。强调于结果。

一致性（Consistency），可以理解为数据是满足完整性约束的，也就是不会存在中间状态的数据。强调于过程。

隔离性（Isolation），指的是多个事务并发执行的时候不会互相干扰，即一个事务内部的数据对于其他事务来说是隔离的。

持久性（Durability），指的是一个事务完成了之后数据就被永远保存下来，之后的其他操作或故障都不会对事务的结果产生影响。

隔离性

没有隔离性，一般会发生3种情况
1.脏读
一个事务在执行过程中，读取了另一个事务未提交的数据。

比如有这么一个表

name	age
张三疯	24

A事务启动，将age改为20，然后未提交。
B事务启动，读取age，age = 20。
这时A事务发生回滚，age变为24。
这时我们把B读到的数据称为脏读

2.不可重复读
事务执行过程中，读取到了其他事务不同阶段的数据。（强调数据的update）

还是上面那个表
B事务启动，读取age为24，
A启动事务，将age改为20，
B事务重复读取age，读取age为20，

不管A事务最后有没有提交事务，B“连续”读到的数据不一致，这就发生了不可重复读。

3.幻读
事务执行过程中，读取了其他事务不同阶段的过程（强调数据的delete与insert)

还是上面的那个表
B事务启动，读取表的人员个数，count = 1,
A事务启动，insert一个人员信息，count = 2，
B事务重复读取人员个数，count = 2，

多次读取的数据不一致，好像发生了幻觉，和不可重复读有些类似，幻读是强调增删

隔离级别
为了解决以上3种情况，设置了多种隔离级别

隔离级别	脏读	可重复读	幻读
未提交读	yes	yes	yes
已提交读	no	yes	yes
可重复读	no	no	yes
可序列化	no	no	no

事实上，MySQL就是采用的可重复读，但是他解决了幻读，是因为使用了间隙锁。
1.未提交读
事务可以读取另一事务未提交的数据，所以容易发生这3个问题。
2.已提交读
事务只能读取另一事务提交的数据，因此事务看不到其他事务执行的过程，所以解决了脏读。
3.可重复读
事务在执行的过程中，其他事务与他是透明的，即他看不到其他事务，仿佛只有他一个在执行，所以数据只有他能“改变”（他自己认为）。

比如
B事务启动，读取age为24
A事务启动，修改age为20
B事务再次读取age，依旧为24

至于怎么实现的，我猜是有一份拷贝？待续

4.可序列化
强制将所有的事务串行化，效率太低。

原子性

特点：事务的操作要么全部执行成功，要么全部失败
那么要怎么实现呢？

redo log -------- 日志先行

一个事务提交，不会先执行，会先记录到redo log 日志里，然后再从内存刷到磁盘里（妈妈再也不怕我断电了，但是分布式应该怎么解决呢？），然后再执行事务，
如果事务执行成功，ok。
如果执行失败，那么就根据日志回滚。

那么如何回滚呢？

undo log ----回滚
undo log 记录了事务执行过程中数据的每个状态与执行语句，因此回滚就是undo log的一个逆序操作。

一致性与持久性

一致性
主要依靠锁的机制，来保证一致性

持久性
也是应用了日志先行，只要有日志，数据就得到了保障

（所以万一日志存储满了怎么办？）

分布式事务

2PC

3PC

TCC

本地消息表

本地事务

锁机制

锁是保证一致性的法宝，也是保证并发安全的法宝。

读写锁（表锁与行锁）

表锁： 即为锁住一张表的锁
行锁：即为锁住一行数据的锁
表锁，行锁强调锁住资源的范围级别，读写锁才是真正具有“锁“功能的锁

MyISAM 表锁
读锁 共享读锁
写锁 独占写锁
其实只要稍微了解过并发，就知道对于共享资源，只要涉及到增删改，就必须上锁，所以锁其实就很好理解。
所以对于A与B2个事务，下面的表也很容易明白

A\B	读锁	写锁
读锁	yes	no
写锁	no	no

唯一注意的是，MyISAM是表锁，他会锁住整个表，比较浪费。

InnoDB 行锁（也支持表锁）
读锁 共享锁
写锁 排他锁
InnoDB与MyISAM的锁还是有区别的，因为InnoDB支持事务。

行锁的优点显而易见，锁住的资源少（对于保证安全的情况下，锁住的资源越少，锁的效率就越高，但是任务需要更细化），行锁只会锁住一行，从而解放一个表的其他资源。

同时在InnoDB里select是不需要上锁的，因为有隔离性啊，还记得可重复读吗？

但是 行锁需要索引，如果不用索引，就会是表锁 为什么呢？
首先要明白一个道理，写锁必须是先锁后写（悲观锁）。如果我们先全表搜索某一行，再找的过程，这一行数据发生了改变，我们再去锁住这个表修改，事实上，已经违背了锁的本意，即先查询后加锁。
如果用索引，即为先加锁，后查询，这样符合设置锁的本意。

意向锁

主要为了解决InnoDB中，行锁会发生的死锁

意向读锁(IS) 意向写锁（IX) 表级锁

意向锁之间是兼容的，与读写锁需要区分

A\B	意向读锁	意向写锁
意向读锁	yes	yes
意向写锁	yes	yes
读锁	yes	no
写锁	no	no

那么为什么需要意向锁呢？

那么来看这么一个情况，InnoDB中
A事务启动，给某一行添加一个行锁
B事务启动，想添加表锁，此时发生冲突，B阻塞
系统会不断全表扫描是否有行锁的存在，直到某次扫描，再也没有一个行锁
系统就会让B得到一个表锁

全表扫描太费时间，所以引入了意向锁

A事务启动，给某一行添加一个行锁
B事务启动，想添加表锁，此时B发现有意向锁，B阻塞
当A事务执行结束，释放行锁与意向锁
B事务检测到意向锁消失，添加锁
减少了多次全表扫描
效率大大大大提高。

乐观锁与悲观锁

悲观锁
先加锁，锁住资源，再执行操作
MyISAM InnoDB 的读写锁都是悲观锁。
乐观锁
遇上问题才”加锁“，锁住资源

版本id
一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。

A读取数据，同时读取版本号，在提交事务时，若当时提交的版本号与当前版本号是相同的，则提交成功，否则发生回滚。

版本id用CAS实现

应用场景
加锁是需要开销的，就像多线程一定比多线程块吗？所以乐观锁一定比悲观锁好吗？
不一定，对于多写的情况下，用悲观锁会比较好，反之对于多读的情况，用乐观锁会比较好。

间隙锁

可以解决幻读的问题
比如
数据 ：1 2 3 4 5 6 7 8 9
select * from chat where id > 3;

并发情况下，如果插入数据，会发生幻读

而间隙锁会锁住4 5 6 7 8 9的数据，所以查询的过程中，这一段数据对于其他事务是不可操作的，所以也就解决了幻读。

触发器

一个事件到来，触发器被触发，去执行其他操作