基础架构
组件
mysql由Server层和储存引擎层组成。
server层包括连接器，分析器，优化器和执行器等组件，server层涵盖Mysql的大多数核心服务功能以及内置函数，存储过程，触发器，视图等。
存储引擎负责数据的储存和提取。架构模式是插件式的，支持InnoDB，MyISAM，Memory等储存引擎。
连接器
管理连接，权限校验
分析器
词法分析，语法分析
优化器
执行计划生成，索引选择
执行器
操作引擎，返回结果
存储引擎
储存数据，提供读写接口
运行过程
1.客户端使用数据库驱动发起请求
2.服务端连接器负责与客户端建立连接，进行权限校验
建立连接时会从权限表读取用户的权限，后续这条连接的业务执行的权限判断都依赖这次的读取
3.如果是查询请求会先查询缓存，缓存有值就直接返回结果，没有就需要进行真正的查询操作
4.分析器对执行语句做词法分析和语法分析
5.优化器计算执行成本，决定要使用的索引，确定执行方案
6.执行器校验表的执行权限，调用存储引擎的接口获取数据
索引
概念
索引是提高查询效率的数据结构
数据和索引的关系类比与书籍和目录的关系
索引模型
哈希表
等值查询效率很高，不能做范围查找
有序数组
等值和范围查找效率很高，增删元素需要进行内存移动，适合静态数据
搜索树
查询和范围查找效率很高，增删元素需要保持树平衡。
B+tree
多叉平衡查找树，每个节点拥有多个子节点可以大大降低树的深度。
每个节点存储的信息较多，占据的储存空间也较大。
非叶子节点不储存data数据，只存储关键字key。
叶子节点含有指向相邻节点的指针。
B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数
聚簇索引
也称主键索引，索引的叶子结点存储的是整行数据。
非主键索引，称为二级索引，叶子节点储存的是主键的值。
二级索引需要先从索引本身查询主键，再通过主键到主键索引查询数据行，多扫描一颗索引树。
回表
先从普通索引搜索主键值，再拿到主键值回到主键索引搜索的过程
覆盖索引
在二级索引上搜索的结果满足查询要求，无需回表搜索的情况
最左前缀
索引项会按照索引定义里出现的字段顺序排序
多维度排序思想，排序优先级就是索引定义的字段顺序
索引下推
zhuanlan.zhihu.com/p/351446942
索引条件下推ICP就是尽可量利用二级索引筛除不符合where条件的记录，如此一来减少需要回表继续判断的次数
Explain sql 语句时的输出项的Extra会显示Using index condition
字符串索引
#整个字符串建索引
alter table SUser add index index1(email);
#字符串前缀建索引
alter table SUser add index index2(email(6));
字符串前缀建索引
相当于截取一部分作为索引的key，节省了索引空间。
截取字符串的区分度影响查询效率，截取长度应该考虑尽量增大区分度。
字符串前缀索引不能使用覆盖索引。
增大区分度手段：
1.根据数据特征截取前缀长度
2.如果区分度主要取决于字符串的尾部，字符串反转储存
3.增加新字段，使用哈希函数如CRC，缩短字符串长度，使用新字段做索引。
Order by
1.在order by 字段没有使用索引情况下，需要进行额外的排序工作。
2.初始化sort_buffer,确定要放入的字段
如果数据单行的长度太大，存入的字段是排序字段+rowid，否则存入select * 查询的字段。
3.在二级索引上查询一条符合条件的主键值，回表找到数据行，提取需要的字段并存入sort_buffer内存中。
4.如此往复取出所有符合条件的数据行存到内存中，如果sort_buffer内存不足以保存所有数据，就创建临时文件来保存数据。
5.对数据根据排序字段进行快速排序
6.如果rowid 排序，需要回表查询数据行
7.按照排序的顺序取出（如果有limit，取出要求的数量）
事务
隔离级别
读未提交（read uncommitted）
读提交(read committed)
可重复读（repeatable read）
串行化(serializable)
事务实现
mysql通过MVCC（多版本控制）实现事务
每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值可以通过回滚操作得到前一个状态的值。
而实现上数据库会创建一个视图，这个视图决定查看什么版本的记录，访问的时候以视图的逻辑结果为准，视图类似一个数据快照。
一个事务通过视图查看到的数据版本是创建视图时已经提交的或者本事务更新的版本。
【读未提交】直接返回记录上的最新值
【读提交】视图在每个sql语句执行时开始创建的
【可重复读】事务启动时创建，整个事务都用这个视图
【串行化】直接加锁避免并发访问
避免长事务
原因
每条记录数据通过保存多个回滚操作进行版本的控制，而回滚操作记录的删除前提时，没有视图再需要通过对应的回滚操作记录查看旧版数据。长事务会导致回滚日志不能快速删除，导致回滚操作占用储存空间。
措施
尽量使用自动提交
当前读
- 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）
- lock in share mode （查询添加共享锁）
select * from T where id =x lock in share mode;
- for update(查询添加排他锁)
select * from T where id =x for update;
锁[待续]
间隙锁
左开右闭
可以解决幻读
两阶段锁协议
锁在需要的时候时候才加上，在事务结束时才释放。
锁类型
| 类型 | 使用语法 | 现象和用途 |
|---|---|---|
| 全局锁 | 加锁：Flush tables with read lock (FTWRL)释放：unlock tablesFlush tables t with read lock代表的是只关闭表t | 1.整个库加读锁，不允许数据更新（增删改）和DDL执行。2.适合全库逻辑备份。3.Innodb备份使用其他方案，一致性读视图，mysqldump 使用参数–single-transaction4.客户端断开自动释放 |
| 表级锁 | 表锁表锁加锁：lock tables … read/write释放锁：unlock tables元数据锁（MDL）元数据锁自动添加和释放：执行DML语句添加读锁，执行DDL语句添加写锁意向锁避免在表中已经存在行锁的情况下，另一个事务去申请表锁时去扫描表中的每一行来判断是否存在行锁。需要对数据记录加行锁时，需要先获取表的意向锁，如果行锁为共享锁，获取意向共享锁，行锁为拍他锁，则需要获取意向排他锁。 | MDL的作用是防止DDL和DML并发冲突 |
| 行级锁 | 共享读：select ... in share mode排他读：select ... for update写锁：update语句 |
日志和主备
change buffer
一块位于mysql数据库内存bufferPool中的用来储存页更新操作的内存块。
当需要更新一个数据页时，如数据页不在内存中，在不影响数据一致性前提下，将更新页的操作缓存在change buffer中，避免数据页读入内存。
在下次需要访问这个数据页时，将数据页读入内存，然后执行change buffer与这个页有关的操作，保证了数据的完整一致性。
更新操作先记录在change buffer，减少读磁盘，提高语句执行效率。
实战优化
尽量采用自增主键
自增主键是顺序插入，降低了新记录插入导致的索引数据的页分裂和页合并的概率。
自增主键一般比业务字段做主键长度较小，索引树上的主键越小，每个节点可以存储的子节点就越多，索引所占用的空间就越小。
从性能和存储空间方面考量，自增主键往往是更合理的选择。
主键索引重建
索引会因为删除，插入记录导致页分裂，从而导致数据页有空洞,重建索引会创建新的索引，数据按数据插入，页面的利用率最高，是的索引更紧凑和更省空间。
做法：alter table T engine=InnoDB
覆盖索引
减少树的搜索次数，显著提升查询性能
e.g. 使用联合索引，身份证和姓名
普通索引和唯一索引的选择
查询：
普通索引在索引上遍历到一个不匹配键值才停止。
唯一索引在索引上遍历到一个匹配的就停止。
性能差别不大。
更新：
唯一索引有唯一性约束，在更新时候需要做判断，如果更新的数据不在内存，需要将数据页读入内存，判断后再更新。
普通索引在数据不在内存情况下，将数据更新操作写入changeBuffer就完成更新，避免数据页从磁盘读入内存的耗时操作，性能很高。
在业务能保障唯一性的前提下，推荐使用普通索引。
避免索引失效
总结：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能
1.对字段进行函数计算
select * from T where month(create_date)=7;
select * from T where id+1=1000;
2.隐式类型转换
#tradeid为字符串类型字段，输入类型为整型
select * from tradelog where tradeid=110717;
# 相当于转函数转换
select * from tradelog where CAST(tradid AS signed int) = 110717;
# 结论：索引无效
#如tradeid是int类型
select * from tradelog where tradeid='110717';
#相当于
select * from tradelog where tradeid=CAST('110717' AS signed int);
#结论：索引有效
3.隐式字符编码转换
#有个表使用utf8编码 一个使用utf8mb4编码
#utf8编码》utf8mb4编码
select * from trade_detail where tradeid=$L2.tradeid.value;
#等于
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;
死锁
begin;
select * from t where id=N for update;
/* 如果行不存在 */
insert into t values(N,N,N);
/* 如果行存在 */
update t set d=N set id=N;
commit;
由于查询不存在的记录，导致出现间隙锁，并发情况下会导致多个session都对同一个区域加上间隙锁。
各自的insert操作无法执行，互相等待锁的释放，导致死锁的发生。
解决方案：
1.insert … on duplicate key update
2.RC隔离级别
explain
考点
truncate和delete区别
delete可以进行选择性删除，truncate是整表数据删除
delete支持事务回滚，truncate是DDL，不回滚
delete一条条遍历删除，记录事务日志，效率慢，truncate效率高
truncate重置自增主键值
