mysql学习笔记

一、查询语句执行过程：

二、更新语句执行过程

1、跟查询执行过程基本相似，增加了redo log(重做日志)、binlog(归档日志)

2、redo log:innob独有的日志，更新操作先放在redo log，等系统空闲的时候再写入磁盘，redo log大小固定，可以配置1组4个文件，每个文件大小1G，从头->尾->头循环写入，write_pos写入点，check_point擦除位置,当写入位置赶上擦除位置，擦除位置就会推进，给写入操作腾出空间，crash-safe 概念，redo log保证了crash-safe即数据库异常，也可以保证数据的完整

binlog:mysql服务层提供的日志

写操作首先依次写入内存->写入binlog（prepare阶段）->写入binlog-> 提交事务(commit),两段式提交保证binlog和redo_log的一致性

配置 innodb_flush_log_at_trx_commit=1每次redo_log写入都自己持久化到磁盘

配置 sync_binlog=1 每次事务的binlog都直接持久化到磁盘

三、事务隔离级别

1、事务的ACID特点：Atomicity/Consistency/Isolation/Durability

2、隔离级别：读未提交(read uncommitted)/读已提交(read committed)/可重复度(repeatable read)和串行化(serializable)

3、数据库多版本并发控制(MVCC)，回滚日志:每一条记录对应多个回滚操作，通过回滚操作可以去的不同版本的结果，不影响其他事务的执行，回滚日志会在系统判断没有更早的read-view是被删除

3、autocommit=0代表关闭自动提交，任何操作事务会自动开启，不需要begin，但是每次都需要手动commit或rollback，否则事务一直开启

autocommit=1代表打开自动提交，任何操作都会开启事务并自动提交，除非现实begin开启事务，才需要手动commit或rollback，建议使用autocommit=1,避免事务无法关闭的问题

4、可以通过commit work and chain在提交事务的同时，开启下一个事务，少一次begin操作

5、重建主键索引，那么其它第二索引都会删除，等于白做了

6、通过 informationschema.innodb_trx表查看长事务占用

四、索引

1、B+树，数据（关键码）存在叶子节点；非叶子节点存放索引；每个节点1200个分叉，3层可以存储17亿条数据，很少的磁盘读写次数；相比b数，数据层级更低；

2、自增主键空间占用更小，写入性能更好，不存在页分裂造成的空间浪费和影响写性能问题；在只有一个索引，且唯一的情况下可以使用普通字段做主键，其它都是用自增主键；

3、覆盖索引，指索引已经覆盖了要查询的结果，不需要回表

4、联合索引，多个字段组合起来的索引，最左原则；普通索引默认会跟主键建立联合索引

5、索引下推：mysql5.6后，在索引内部就会对条件进行过滤而不是挨个对条件进行回表多虑

6、undo log,存储在系统表空间或者undo表空间的undo slot内，用所事务回滚；将innodbundotablespaces设置成2或者大于 2，可以增加undo slot数量，事务回滚更快

7、max_*execution_*time设置查询的最大时间 ，innodb*_lock_wait_timeout死锁等待超时时间*

五、锁

全局锁：

1、全局锁 flush tables with read lock; 用在myisam备份 整个数据库只读

2、single-transaction;用在全部表都是innodb的库做备份，异常会自动释放全局锁

3、set global readonly=true 不建议，原因1：readonly被用在判断主从，影响主从功能，2：无法自动释放

表级锁：

1、语法 lock tables t1 read/write,t2 read/write; 释放unlock tables

2、MDL(元数据锁)隐式加锁 主键查询锁行，非主键查询DML锁表

行级锁：

1、两阶段锁：update自动加写锁，后面对同一行更新出现锁等待；两个事务对同一张表的不同行，进行不同顺序的更新操作，可能出现死锁

2、避免锁争：尽量将可能出现锁争的操作放在失误的最后，减少加锁的时间

3、避免死锁：对同一张表的不同行执行操作，按照相同的顺序；

间隙锁：为避免RR幻读，查询时，锁定索引间的空闲部分，也就是next-key锁

六、普通索引和唯一索引的选择

大部分情况下应该选择使用普通索引，因为change_buffer的机制，会通过内存写入，延迟写入磁盘的方式，在下次需要读取的时候再入库，提高写入的响应速度；唯一索引因为唯一性的判断，需要每次写入都从磁盘读取，增加了I/O操作，性能不高

七：索引选择偏差

1、索引的选择是优化器根据预估统计评估选择的，不一定准确，比如事务过程中，其它会话进行删除，删除数据并未真正的删除，只是添加的删除但未提交的标记，所以预估行数=数据行+新增行+回表行，会大于实际查询行数；预估偏差也是一种错误，可以通过重新评估进行纠正，analyze table t进行修正

2、表的索引统计不准，可以通过analyze table t进行修正

3、优化器选择索引的时候会考量多个维度，比如扫描行数，排序，查询数据量等，所以并不一定能选择最优解；有解决方法，比如force index,order by调整(增加新的排序字段)，删除误用的索引

4、物理日志记录某一页某一个位置前后的数值，更底层；逻辑日志：记录了某个表，某字段update/insert/delete的操作；日志区别是物理日志有page Id

八、字符串字段加索引的方式

1、前缀索引：字符串加索引整段加会很长，可以考虑前缀索引，前缀索引无法使用索引自带的覆盖索引功能，

2、倒序存储：大小根据区分度确定比如6-7个字节长度

3、hash存储:大小(4字节)，函数crc32()

4、相同点：倒序存储和hash存储都因为数据经过处理而无法使用范围查询，只能使用等值查询；

不同点：倒序查询+前缀索引：存在重复可能倒序不增加新的字段

hash查询：hash需要增加额外的hash字段用于存储hash值；hash值存在重复的可能很低，1条查询扫描行数基本等于1

九、mysql刷脏页

1、当内存不足或者redo log满的时候，会刷新脏页数据到磁盘持久化，决定刷新性能的是innodb_io_capacity参数，可以通过fio查看磁盘的iops保持一致；

fio 磁盘命令：fio -filename=/dev/vda1 -direct=1 -iodepth 16 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -allow_mounted_write=1 -group_reporting -name=mytest

十、数据删掉，表文件大小不变

1、数据删掉，表空间没有删掉；如果采用drop，可以实现完全删除；

2、表重建(recreate)：alert table t engine=InnoDB

optimize table t = recreate + analyze

3、change_buffer 默认设置25(%)，如果更新很频繁，可以改为50%；

4、重建表后，页空间并不是最紧凑的，会留1/16的空间给更新操作

十一、排序

1、要排序的数据放在sortbuffer中，进行全字段排序，如果行长度过大，如将直接参与排序的字段放入sort_buffer,然后回表获取所有需要的字段数据

十一、间隙锁

加锁原则：

1、加锁是以next-key lock加的锁

2、是有访问到的锁，才会加锁

3、唯一索引等值查询，退化为行锁

4、索引上的等值，向右查找到第一个不符合的边界，并退化为gap-lock

5、索引范围判断不退化为gap_lock

6、唯一索引范围判断，出现与索引等值情况，会退化为行锁

7、唯一索引范围锁，向右访问到不满足条件的第一个值而且不退化为gap_lock

注意：两个事务同时申请间隙锁，并出现相互等待，会死锁，事务检测到，会主动释放其中1个事务

十二、锁的动态性

锁是根据右边的边界确定的，如果前边的记录行被删除，那么锁的范围会被扩大

show engine innodb status: 查看innodb引擎状态（事务和锁详情）

十三、数据库恢复

预防误删：

1 、账号分离：开发只给DML权限；DBA平时只用readonly账号，需要的时候切换最大权限

2、操作规范：删表前先改名，观察一段时间；改名采用*_to_delete类似的后缀，DBA删表只删除指定后缀的表*

误删数据：

预防：上线前，代码审计

恢复：Flashback恢复数据：前提binlog*format=row和binlog_*row_image=FULL

误删表、库：

使用mysqlbinlog 命令：

1、show master status 查看当前使用中的binlog

2、show binlog events in ‘mysql-bin.000002’ 查看binlog事件日志，确定位置

3、方式一、mysqlbinlog --start-position=1572 --stop-position=1783 -v /Applications/phpstudy/Extensions/MySQL5.7.28/data/mysql-bin.000002 | mysql -uroot -p

方式二、mysqlbinlog --start-datetime=‘2022-09-23 11:45:00’ --stop-datetime=‘2022-09-23 11:46:00’ /Applications/phpstudy/Extensions/MySQL5.7.28/data/mysql-bin.000002 | mysql -uroot -proot

-v 可以row数据展示出来

通过位置或者日期恢复表数据或者行数据

十四、join查询

1、你使用应该用小表作为驱动表，对于被驱动表能用上索引的，建议用join，用不上的，不建议使用

2、不能使用索引的，只能使用block nested-loop，同样建议使用小表做驱动表（小表指的是需要返回的字段和行数少的表）主表查询字段放入内存扫描驱动表，匹配后返回

十五、临时表

1、语句 create temorary table user …

1、临时表跟普通表没有太大区别，存储在tmpdir目录

2、临时表属于一个会话，其它会话不可见，会话关闭后，临时表清空

3、需要用到临时表的情况：手动创建；union需要保证第一个字段唯一约束；group by需要统计聚合字段，并且排序字段没有索引；

在group by的select句段插入SQL*_BIT_RESULT*可以强制不适用临时表，使用sort_buffer+磁盘文件辅助来保存数据

十六、内存表

数据组织结构：采用堆组织，行等长，可以复用；inndodb索引采用索引组织结构，行不等长，不可以复用

1、内存表索引采用hash，范围查询慢，可以设置为tree add index using btree(f1)，增加范围查询速度

2、内存表不涉及磁盘io，访问速度块

3、内存表锁是表锁，并发更新操作性差，并发访问性差

4、 内存临时表可以满足访问速度块，读速度要求高，写要求不高的，并对数据持久化要求不高的场景

十七、主键为什么不连续

1、两个语句

create table t1 like t0;// 复制表结构和索引

create table t1 as select * from t0;//复制表结构和表数据，但是不复制索引

2、当自增锁申请后+1，语句执行失败，索引不会回退，索引就造成1个主键空缺

十八、复制表的方式

逻辑复制：

mysqldump -uroot -proot --single-transaction shop user --result-file=//Users/apple/Desktop/user.sql

物理复制：

1、复制表结构：create table t1 like t;

2、删除新表的表空间：alter table t1 discard tablespace

3、刷新原表锁表并导出：flush table t for export

4、去磁盘目录复制原表的: cp t.cfg t1.cfg,cp t.ibd t1.ibd

5、删除原表的锁：unlock tables;

6、重建表空间 alter table t1 import tablespace;

十九、grant 是否跟 flush privilege？

如果操作规范，是不需要flush的，因为grant的时候已经修改了内存和磁盘的权限配置；除非不规范的直接操作数据表（比如删除用户），造成内存和数据库配置不一样，才需要flush 内存中的权限；

修改密码：

1、建议alter user test@‘%’ IDENTIFIED by ‘123’

二十、分区策略

1、分区数量不应该过多，否则需要打开过多的文件

2、分区表在磁盘上是单独存放的，删除很快； server层维护MDL锁，单表，innodb认为是多表；分区表因为是server层维护1个MDL锁，对分区进行DDL会造成堵塞，对DDL不友好；

3、分区对于查询性能的提升有限，所以不建议使用，但是分区具有业务清晰的特征，可以考虑使用；

二十一、自增id最大值

1、自增主键：最大就不变，会出现主键重复，可以调整主键长度为bigint

2、row_id:6个字节，到最大后重置为0，基本可以不考虑重复