Mysql培训第二天

争取第一个内存块：

instance（实例）的作用：

instance的作用： 参考阿里一般设置为16或32

instance 的数量应该参考CPU数量 在单CPU的情况下，并发就会很少。 instance代表的是同时有多少个人能一块干活

innodb buffer pool 怎么存数据：

LRU污染： 对于一个经常不读的表，某一天突然读取，它会从磁盘中读取出来，然后放在热端，这样就会导致我经常读取的数据滞后，当我再正常读取我经常读取的数据时，就会造成大量的IO，这就是LRU污染，造成数据库飙升。

对LRU的优化： 添加了midpoint，第一次读取的数据会先放在midpoint位置，当再读取时，我会觉得你是真的使用它，再把它放在热端。

sql语句的排序不是在innodb buffer pool 中做的。

当一个用户连接的时候，mysql db会给它创建一个线程，除此之外，一个用户还会给它创建很多内存区域，这些内存区域就是这些用户私密的小空间。内存缓存做后面的事。

如果有排序，会多一步，会多分出一个缓存，先将数据无排序的从硬盘中调取到innodb buffer pool，然后有要求排序的时候，直接从innodb buffer pool放到每个用户私密的空间sort buffer进行排序，排完之后才能真正的返回给用户。

对select做一个总结： 首先用户进程连上来，我要先看看innodb buffer pool 中有没有我要的数据，如果有我就直接从内存拿了，如果没有我就只能磁盘IO了，磁盘IO不是直接会给用户，而是给innodb buffer pool（我为人人，人人为我，只要有人和我差不多的读取数据，我就可以直接从内存中读取数据了）

以上讲的都是全表扫描的例子，后面讲一讲索引

我有一个表，这个表有一千万行，里面有一列，这一列是男和女，当这个时候我where sex =“男”，即要找出2500万的行数
**全表扫面怎么做：**这个表是有很多个数据页组成，我一次最少也要读一页，实际上我可以多个数据页多个数据页的往里面读
一个数据页的读：

**实际多个数据页：**把这些数据放在内存里做过滤，找出我想要的

索引： .ibd中存有数据和索引，数据是按照主键索引放的
考量索引的性能就是按照树高（深度）来考量的。

索引读取方式：
假如索引是这种结构的，索引我每读一行，都要有3次IO


以上是mysql的查询在数据库中怎么流转的。
以下写更新修改的。

内存中修改的是脏数据（灰数据）。

io_write_thread*n 取决于CPU的数量，有多少个CPU就可以开通多少个并行的线程（重点是可并行的线程），即使线程很多，但不一定都在同时工作。一般的互联网公司设置为16或32。可以设置为8个CPU对1个io_write_thread.

插入一条数据：
Mysql是有自动提交机制的，如果这边一个用户插入进去信息，另一个连接上的用户就会查询到刚刚插入的信息。
假如将数据库的自动提交关闭，然后进行插入
insert into T select 5,‘andy’； 此时即使还没commit，但是自己查是可以查询到的，这时候如果再输入：commit；（相当于给别人转账时候的确认），另一个用户连接上数据库的时候进行查询时就可以查询到了，如果不commit，另外一个用户是查询不到的，如果输入：rollback（相当于支付时的取消付款），此时就会撤销。
以上就是Mysql的事务。
但是将脏数据写入磁盘与commit无关
比方：双十一的时候，有几千万同时commit，那不就是磁盘要同时进行磁盘IO，可是我们的磁盘怎么可能经受住那么的同时IO。

写脏数据完全看心情，当IO压力小的时候就多写一点，当IO压力大的时候就少写一点，和commit没关系

事务的特点：
原子性：（事务里的每一部要么都成功要么都失败）
一致性：（当我修改提交了，那么其他人就可以看到我修改后的值，当我没提交就看不到我修改后的值）
隔离性：
持久性：

硬盘里面还有一部分为 ibdata1.
当另一个用户查询这个信息的时候，会根据下图中右边那一行来做，先看这个数据是否在事务中，如果Y就是在，假如第一个人没有commit，此时内存中会修改为JACK，自己会查询到也修改了，而且由于可能此时thread很空闲，就会进行磁盘IO，将磁盘中的数据也改掉，这时候的另一个用户，先在内存中查询，但不会返回JACK，而是会根据右边那个进入UNDO（撤销）的地址去查询（没commit之前）。当commit的之后，UNDO里面的数据就无效了。
**UNDO的第一个作用：**没有提交没有回滚的时候，做事务的一致性。里面有一个前进项，满足一致性读。

UNDO的第二个作用： 当我用户rollback（回滚）的时候，支持事务的回滚。

**但我删一条数据的时候：**本来想这样写删除一条数据, DELETE FROM EMP WHWER…;
但是此时我写错了，写成删除整张表了(假如这张表有世亿行数据)，DELETE FROM EMP;此时危害最大的就是UNDO;（DELETE 的时候，会先将整张表先复制到UNDO中，UNDO就会撑的很大，就会产生大量的IO，ibdata1是一个文件，撑大了就回不来了）

当我们commit之后，但此时thread就是没有读取脏数据，而且此时的UNDO也已经不起作用了，那相当于这时候只有内存进行了修改，当这个人在此时再次读取这个数据的时候，此时突然断电了或者数据库突然宕机了，那么此时重启数据库，内存中已经没有JACK的数据，那怎么办呢？怎么保证是事务的持久性？

数据库软件第二大原则（WAL write ahead log）（日志先行），在内存中还有另外一个缓存区（log buffer）
Mysql是日志的（日志先行），即是在做任何修改之前都是先记在日志中，再做修改。

磁盘中还有两个文件是redo log，当commit的时候，它的作用就是落盘，即将log buffer 的日志复制在redo log 中，

Mysql在重启的时候，会进行一件事，就是实例恢复，就redo的第一行往下走。redo记的就是所有的脏数据。
log buffer 有几个原则：（不需要将log buffer 调的很大）
1.当log buffer 二分之一满的时候
2.达到1M
3.commit的时候
以上三个达到一个的时候就会刷到磁盘中。上面两个刷过去的时候，可能会把没有提交的数据也刷过去。

一种特殊的情况： 当log buffer 满足以上两种情况的时候，将log刷了过去，而且此时thread也捣乱，将磁盘也进行了IO修改，这时候出现了宕机，就会造成数据丢失。（类似，当你在把钱转进银行的时候，你还没有点确定，在犹豫的时候，银行系统内存已经将数据写入，而且由于thread也将数据进行了修改，也就是你的银行账户的钱已经加上去了，这时候银行系统出现宕机，就会造成你没有点确定，自己的钱少了，银行的增加了）
这时候银行系统重启，就会读取redo log 发现磁盘确实也做了修改。然将其他所有的redo log内容都进行了读取，却没发现commit，这时候Mysql会强制在日志中加上一个rollback，就会将数据在UNDO中补回去，然后磁盘就会直接读取UNDO数据进行改回来。（银行把钱退回来）

为什么要日志系统呢？
为什么不可以commit之后就将硬盘数据往磁盘里刷（将脏数据同步到磁盘中）。

硬盘的物理结构：
随机读： 硬盘是由很多个磁盘片组成的，不同的表会放在不同的磁盘片上，不同的表会做不同的更新，这个磁头就会在不同的磁盘片上做功耗非常大的物理移动。
以后在优化SQL或者结构的时候，我们要尽量避免随机读

redo log ： 磁头不用在盘片上来回动，在这个磁盘上磁头来回转就可以把这个redo 日志写完，就像记流水帐一样。

例如：在开发时，不能频繁提交，假如向一张表插入一千万行数据，不能插入一行提交一行。可以插入一万行提交一次。

redo就两张表，会循环写进去，当又重新写进第一张表时，此时系统宕机了，重启的时候redo遇到了还有做的commit，此时就无法覆盖了，用户的update、delete啥的就不能做了，就出现了hung住。
系统突然hung住（银行转帐的时候，用户在转账或者在做一些修改的时候，系统突然卡了，不能提交，前端压力就大了，前端业务已经反应了，咱们系统已经没反应了）
系统没反应的根本原因： redo已经不能写了。redo不能再覆盖了。

解决方案： 间接的将redo日志的一个文件内存调的大一点或着将文件个数调的多一点。

关系型数据库的瓶颈：在log buffer 这个地方，没法并行。

log thread只有一个，生产者和消费者模式。
比如现在有一千个人向我数据库里面插入数据，但是着一千个人都会产生redo 日志，那么这个redo日志产生的就是一千个人的日志。造成这些redo日志的线程就是生产者，负责将一千个人产生的日志写进磁盘里的消费者只有一个（log thread），实际上就像一个漏斗，这个时候就会出现漏斗溢出。
这是关系型数据库所共同面临的问题
解决思路： 1.减少提交的频率 （加大漏斗不行，再大也有满的时候）2.做分流，把事务分散开

这是关系型数据库不可避免的，因为要求要有持久性，必须就要有日志。
假如这么想：让log buffer 也多线程不就行了嘛。（不可以的）
假如有一个A用户先插入，B用户后删除。有两个log thread，但是由于并发很有可能导致先删除后插入。

假如编号，给insert编1号，delete编2号，1号先执行，2号后执行。那么这样不就和一个log thread一样了嘛，又变成了串行。
整个过程没法并发，这是关系型数据库性能的瓶颈，好在这只是日志记流水账的，所以还行

latin1 代表一个英文字符一个字节
UTF8 代表一个中文字符三个字节或者两个字节（两个字节是设置了为Unicode，它会是两个字节）
length 只要数中文字符占的字节数。
char_length只要用character_set_client=utf8;来算。 ”开心工作“应该是4个。
当客户端为character_set_client=utf8，连接数据库的时候，会把自己的编码方式也发过去。数据库会根据character_set_client来识别。就会三个三个字节的数，正好三个字节算一个，就数出来了四个。character_set_client=latin1的时候，一个”开“字就会数出来3个。