1.mysql

1.mysql架构

1.1mysql语句执行流程

select * from user where name=‘张三’；这句话的执行流程 :

• A. 建立链接 连接mysql 3306端口，可以使用同步(默认)，也可以用异步，可以长连接(默认)，也可以短链接, 客户端每开启一个链接, 服务端就会创建一个线程,这个跟tomcat bio模型一样.
  show global status like ‘Thread%';可以看到mysql服务器创建的线程
  show global status like ‘wait_timeout';
  一个链接经过timeout时间后，没有使用，服务端会断开他
• B. 服务端会做什么:
  • 1、 如果开启query cache: show variables like 'query_cache%';那么就会走cache， 但是5.7开始就默认关闭了，因为这个很鸡肋，sql多了一个空格，大小写变了（sql语义没变），以及这个表任意数据任何改变，整张表query cache都会失效，浪费时间，浪费内存。 8.0版本这个功能已经被移除了
  • 2.解析器： （1）词法分析：将sql打碎成一个个单词 （2）语法分析：将打碎的单词解析成一个解析树，并进行语法检查，（abc这样的sql完全不符合语法，就会被识别出来)
  • 3.预处理：预处理（预编译）一次编译，多次共用，还有select * from asdf123321；报错这个table不存在，虽然语法正确，但是语义不对，预处理会处理这样的情况，得到一个新的解析树
  • 4.优化器 optimizer：a. sql优化,包括: 子查询优化, 条件简化, 语义优化,等等等等 b. 生成和选择执行计划. 一个sql可能有不同的执行计划，他是根据时间和cpu成本综合考虑的,基于成本(cost)优化，当然, 选择哪个作为索引, 选择哪个表作为基准表,都是他说了算. 现在经过优化器得到了执行计划, 他可以直接被存储引擎执行
  • 6.执行器 根据执行计划调用存储引擎执行sql, 得到结果 ，写入query cache，返回客户端
  • 7.存储引擎： 存储数据, 提供读写数据的接口

1.2.mysql架构和内部模块

1.2.1存储引擎

• innodb: 默认引擎: 事物安全, 保证acid, 失败回滚, 崩溃恢复, 行锁, mvcc, 聚集索引(降低io), 支持外键.
• mysiam: 表锁级别限制读写并发, 用于只读,或绝大部分只读场景
• memory : 数据放内存, 读写很快, 但是数据会丢失
• csv: 纯文本,无索引, 仅供导入导出(备份,迁移数据使用)
• archive: 归档数据, 经过压缩, 没有索引

innodb架构： Innodb 分为内存和磁盘两块

双写缓冲

先回顾下InnoDB的记录更新流程：先在Buffer Pool中更新，并将更新记录到 Redo Log 文件中，Buffer Pool中的记录会标记为脏数据并定期刷到磁盘。由于InnoDB默认Page大小是16KB，而磁盘通常以扇区为单位写入，每次默认只能写入512个字节，无法保证16K数据可以原子的写入。

如果写入过程发生故障(比如机器掉电或者操作系统崩溃)，会出现页的部分写入(partial page writes)，导致难以恢复。因为 MySQL 的重做日志采用的是物理逻辑日志，即页间是物理信息，而页内是逻辑信息，在发生页部分写入时，无法确认数据页的具体修改而导致难以恢复。

MySQL 的数据页在真正写入到表空间文件前，会先写到系统表空间文件的一段连续区域双写缓冲(Double-Write Buffer，默认大小为 2MB，128个页)并 fsync 落盘，等双写缓冲写入成功后才会将数据页写到实际表空间的位置。

因为双写缓冲和数据页的写入时机不一致，如果在写入双写缓冲出错，可以直接丢弃该缓冲页，而如果是写入数据页时出错，则可以根据双写缓冲区数据恢复表空间文件。

A.buffer pool

在内存中缓存了表和索引的数据，可以对表和索引进行高速访问，加快速度。一些专用服务器上缓冲池可以配置为机器内存80%以上。

缓冲池的数据单位是页， 数据结构是双向链表， 采用LRU算法淘汰不使用的数据，如下图所示，为了防止一次加载数据过大，淘汰其他数据，弄了两个head 两个tail，新加载的数据放在middle，再次访问这个数据就将他移动到头部 ,参考链接：buffer-pool

SHOW ENGINE INNODB STATUS可以查看缓冲区的使用情况。
show variables like '%innodb_buffer_pool%';查看缓冲区配置。

change buffer：
不同于主键索引， 二级索引通常不是唯一的。并且二级索引通常无序。
我们来看一个update 例子

update user set name=‘张三’ where name=‘李四’; -- name是非唯一索引

你需要将name索引加载到buffer pool，然后变更索引，然后将索引刷盘。这肯定快不起来，刷盘很慢。
那么，这个刷盘操作可以以后再做吗？

当然可以，mysql将这个update缓存在change buffer，等到某一个操作（select update insert）将name索引加载到buffer pool的时候，再将这个update和索引合并（合并缓冲），然后当系统处于空闲期间会定时将这个name脏索引刷到磁盘。
这样做避免了大量随机io，因为name不是有序索引，加载这个name=‘李四’索引page会是随机io，所以会慢。

如果是唯一索引就不行了，因为需要唯一校验，必须要立即加载索引。

应用
当大量的insert update时，二级无序索引如果要保持最新状态需要大量随机io。将update放到change buffer定期合并会显著提高性能。
当然如果insert update很频繁，这个缓存合并过程可能要点时间。。。
如果你没有二级索引或者都是查询 操作，那么change buffer应当调小，否则占用buffer pool又没什么用。

log buffer（redo log 16M）
每次写，都是写内存(buffer pool)和redo（事物还要写undo），redo在内存有缓冲就是这个log buffer, 他不是立即刷盘的, 有三个策略：
=0：每秒从buffer直接到文件
=1：每次commit直接写到刷到文件
=2：每次commit就os.write(写到os内核缓冲)

注意大型事物,需要加大这个缓冲,不然他会直接写文件的

B.磁盘架构

表空间, redo undo log, 索引

redo log
mysql的写入不直接到文件, 因为innodb的文件写入是随机io, 所以他是后台线程定期刷盘(redo 满了), redo log是一个append 顺序写入, 所以很快, 首先myql会将增删改,写入redo, 立即返回成功, redo满了,刷盘. 如果系统宕机, 从redo恢复

redo log buffer: redo 也不是直接刷盘的, 内存也有缓存, 这个有策略, 默认每个事物commit就将redo刷到磁盘.

undo log
事物之前的备份,用于回滚

比如更新一个数据 name=‘张三’ 变成李四
a. 委托存储引擎从磁盘或buffer pool取出这个数据页,返回给server
b. 记录undo(张三)
c. 写入buffer pool
d.记录redo(李四) prepare
e. 记录binlog
f. ,然后 commit, 然后将redo prepar改为commit
事物以binlog为准,他有的就有, 他没有的就算没有

连接层: 管理连接, 权限认证
服务层: 词法,语法解析, 生成执行计划, 调用存储引擎返回结果，内存计算
存储引擎层: 存储数据, 提供读写接口

2.mysql 索引

2.1 B+树

想要快速搜索一个数字, 基础算法就是有序数组利用二分查找. 但是如果是数组, 插入删除很慢, 那么将数组替换成链表, 那么此时, 二叉树就来了.

二叉树当数据顺序递增时候, 退化成线性查找. 平衡二叉树解决了这个问题.

b树：mysql数据存储单位是page, 一个page 16k, 一个节点如果存一个数据,就太浪费了, 所以一个节点(page)存尽可能多的数据作为索引, 相应的, 他的子节点也会变多. 这样可以大大降低树的高度, 大大减少io的次数

b+树: 1.关键字数量=度数（路数）。2.非叶子节点不存磁盘地址，只存key+子节点引用。3.叶子节点才存储数据。
为什么这么做？ a.没有磁盘地址，增加了路数。 b.叶子节点的每个page与相邻page之间具有双向指针，当访问一个page，可以轻易的访问相邻page， c.全表扫描能力强，只需要遍历叶子节点 d.排序能力更强，基于叶子节点双向指针

跟b树区别，b树如果根节点找到了，直接返回，b+树只有叶子存数据，所以，每一次查找，io次数都一样，就是一个是部分查询更快，一个是每次查询都要这么久，更稳定

为什么建议要用数字自增作为主键
因为b+树是自平衡的, 插入删除数据会发送page 分裂, 合并, 所以为什么建议主键要有序, 首先，聚簇索引用主键，其次索引必须有序（二叉树），再次，数据插入是按照主键索引的顺序的。当用自增主键时，数据的插入就相当于顺序io一个页一个页的顺序写，如果用UUID做主键，插入数据可能会挤到其他页，发生page 分裂，并且是随机io

b+树的数据存储能力
叶子节点： 一行数据16k，一个page可以存10条数据
非叶子节点：假设一个key+一个子节点指针 = 16byte，那么他的路数 = 1000
假设树的高度=2， 三层
那么可以存储数据 = 1000100010=1000万
b+树将1000w次的io，简化成了3次。所以索引性能提升是巨大的

2.2 索引的落地

2.2.1 mysiam引擎

MYI（i代表index）：索引文件，n个索引n个文件，查询数据是找索引的叶子结点得到磁盘地址，再去myd文件加载数据
MYD（d代表data）：数据文件，存储具体数据

2.2.2 innodb引擎

idb文件：n个索引和数据都在这个文件，索引即数据，数据即索引。为什么这么说，因为相对于mysiam引擎，innodb叶子节点存了具体的数据行，而不是磁盘索引

1.(聚簇索引)[https://dev.mysql.com/doc/refman/5.7/en/innodb-indexes.html]： innodb无论如何都会创建一个聚集索引，用于优化增删查改以及dml语句。如果有主键主键就是聚集索引，如果没有，用第一个唯一非空索引做聚集索引，如果还没有，就创建一个6字节的影藏列row_id（如果存在主键row_id是主键的别名），用这个列建立聚集索引；
聚簇索引的主键顺序, 跟数据的物理顺序一致,主键是什么顺序, 数据存储就是什么数据

2.二级索引
非聚集索引都叫二级索引。
只有聚集索引的叶子节点存储数据, 其他索引都叫二级索引, 二级索引叶子存的是主键的值. 为什么? 因为二级索引如果也存数据,那么主键也存了, 数据有很多份, 浪费空间, update的时候会比较麻烦.

3.为什么二级索引叶子不存数据磁盘地址而是存主键?
磁盘地址会变, 当insert时候, 可能insert到某个page页,满了,就会分裂,此时某个id=n的数据他的物理磁盘地址就变化了,所以还不如存主键

4.回表
通过二级索引查询数据,会使用聚簇索引再查一次,这个过程叫回表

5.没有主键索引怎么办?

1. 找unique(not null)索引作为主键索引
2. 创建一个隐藏的列作为聚集索引 _rowid, 以下情况不能查到这个rowid的,但是他存在只不过是隐藏的:
   • 主键或unique不是数字类型
   • 联合主键
   • unique不是非空

2.2.3 创建索引的原则

1. 在where group by, order by, join 的字段建立索引
2. 索引个数不要太多
3. 频繁更新值