一. Mysq组成部分

1.1 Server层包括连接器、查询缓存、分析器、优化器、执行器等,内置函数(如日期、时间等),所有跨存储引摩的功能比如存储过程、触发昌存储引摩层负责数据的存储和提取。其架构模式是插件式的,支持InnoDB、MySAM、Memory等多个存储引摩。

1.2查询缓存

适用于数据极少改动的表该表任何数据修改缓存失效此功能emysql8.0版本取1.3分析器

对sql语句进行词法分析语法分析识别关键字语法错误报告
1.4 优化器
执行计划生成比如索引选择多表连接顺序等1.5执行器调用存储引掌接口,读取修改数据等返回结果
2.1存储引擎
InnoDB和Myisam区别: InoDB支持事务:支持行锁:有redoLog
3.1文件存储格式
两种引擎都有.frm文件用于存储表结构等元数据信息
InnoDB引擎:.ibd文件.用于存储表数据和索引系统表空间:ibdata1, bdata2
MyISAM引警: myd:表数据文件.myi索引文件; log日志文件

二. RedolLog和binlog

Page页
innodb引擎的最小存储单元为Page页
默认16K在MYSQL5.6版本之后可以修改Page数据结构类似双向链表,存储了上一个和下一个page页的地址数据被存储在UserRecords中,数据结构为链表一个B+树节点就是一个Page页,因此控制page页的大小可以间接控制B+Tree每个节点的子节点数量一个数据页满了,按照B+Tree算法,新增加一个数据页,叫做页分裂,会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并,合并的过程是分裂过程的逆过程

保证 crash-safe能力
WAL技术思想 Write-Ahead Logging,它的关键点就是先写日志,再写磁盘
RedoLog:重做日志.循环写可配置为一组四个文件,每个文件1GB可视为一个环
write-position:写点 redolog写入点 check-point:检查点会将此点位置之后的内容写入磁盘并删除
归档日志有row和 stament mixed三种格式
与 redolog不同点
binlog是 server层所有引擎可用 redolog是 innodb特有
2 binlog追加写, redolog循环写
3 binlog是逻辑日志记录的是对某数据作了什么修改 redolog是物理日志记录的是page页的修改
Redolog和 binlog的两阶段提交
更新数据时先写 Redology,处于 prepare状态再写入数据WA技术再写入 bingo,最后提交事务 commit.
为什么要两阶段提交?
保证 redolog和 binlog的致性,如果发生断电崩溃,若只有 redology, binlog未记录改动会导致从库、备份均缺失数据。若只有 binlog,则崩溃时无法恢复主库数据

两个配置参数:

1. innodb flush log at trx commit: innodb引擎刷入 redolog落盘策略
   为0时,每次提交写入 redolog buffer,每秒将 redolog buffer刷入 os buffer(inux系
   统缓冲区)并调用 fsync真实落盘到磁盘。分析:mysq崩溃则丢失一秒数据
   为1时,每次提交写入 redologbuffer,并写入 os buffer,并调用 fsync真实落盘到磁盘。
   分析:速度最慢,最安全
   为2时,每次提交不写入 redologbuffer,直接写入 os buffer,每秒调用 fsync真实落盘。
   分析:服务器崩渍,丢失一秒数据。安全性高于0
2. sync binlog: binlog日志落盘策略
   为0时,每次提交写入 binlog cache,并写入lnux文件系统 page cache。由lnxu操作系
   统自行决定 fsync时机。
   为1时,每次提交写入 binlog cache,并写入lnux文件系统 page cache,并调用 fsync方
   法落盘
   为N时,每次提交写入 binlog cache,并写入lnux文件系统 page cache,累积N个事务后调用 fsync方法落盘

事务
四个隔离级别:读未提交读已提交( oracle默认),可重复读(mysq默认)串行化
实现方法视图MvCC
读未提交:直接返回最新值无视图概念
读已提交:每个sql执行前创建一个视图
可重复读:事务开启时创建一个视图,整个事务中都读取该视图
串行化:通过全局加锁避免并行访问
脏读:当前事务读取到了其他事务未提交的数据
幻读:同一事务前后两次读取相同数据出现了新的行.可重复读下,使用 for update当前读已经提交的数据会出现幻读

undolog:回滚日志
地点 记录在表空间ibd文件中
时间:更新数据时将保存当前数据到 undolog,并将当前行的回滚指针(行数据隐藏字段之一)指向该 undolog
undolo是逻辑日志新增一条数据时, undolog记入一条dete日志,删除一条数据时(数据实际并未删除而是打上了删除tag, undolog计入一条新增日志 update非主键内容时,直
接计入相反的 update内容(如将某行某列数据1改为2,则 undolog中记入的是将该行该列数据从2改为1) update主键内容时会记录删除该行再插入新行
插入操作的 undolog可以在事务提交后删除而删除和 update操作在事务提交后仍不能删除 undolog(其他事务可能还需要用该 undolog做数据视图,只有等到其他事务读取完成提交后,才可以删除)

表数据隐藏字段
DB_TRX_ID:数据事务id
DB_ROLL_PTR:回滚指针指向该数据的上一个版本在 undolog中的位置
DB_ROW_ID:隐藏主键仅未指定主键时存在该字段
数据可见性是根据要读取的数据事务id和当前活跃事务id列表(也就是 read view列表)进行判断
可重复读数据可见性判断:
在事务启动时,copy一份当前系统活跃事务id列表即未提交的事务,取出事务id最小值和最大值
若要读取的行数据事务id小于最小值说明当前事务开启时该行数据已提交则可见
如要读取的行数据事务id大于最大值说明当前事务开启时该行数据事务还未开启则不可见，则根据回滚指针找到版本可见的数据为止
如果要读取的数据事务介于最小值和最大值之间则判断该事务id是否处在活跃事务id列表中如果在则说明当前事务开启时未提交不可见如果不在说明已提交可见
读已提交可见性判断规则和可重复读相同区别在于创建视图的时间是每个sq语句执行时重新创建视图

四 Innodb缓冲池 buffer pool

缓冲池目的:存放热数据(数据页和索引页)避免每次进行磁盘IO加速访问
预读磁盘读写按页读取把一整页数据全部加入内存未来可能用到
普通LRU算法:缓存数据的淘汰算法数据结构为定长的链表,新加入的数据放入表头链表满后,如访问的数据在链表内则将该数据移到表头如不在表内,则放入表头并淘汰最后一个数据
普通LRU算法问题:预读失败(预读数据未被真实使用到)缓冲池污染(一次查询访问大量数据行导致缓存数据全部被更新真实热数据被淘汰)
MYSQL改良后的LRU算法将链表分为新生代长度占7成)和老生代(长度占3成)新加入数据先进入老生代头部被真实访问之后且经过T毫秒仍未被淘汰才放入新生代头部
参数: innodb buffer_pool_ size
介绍:配置缓冲池的大小,在内存允许的情况下,DBA往往会建议调大这个参数,越多数据和索引放到内存里,数据库的性能会越好。
参数: innodb_old blocks pct
介绍:老生代占整个LRU链长度的比例,默认是37,即整个LRU中新生代与老生代长度比例是63:37。
画外音:如果把这个参数设为100,就退化为普通LRU了。
参数: innodb_old_blocks_time 老生代停留时间窗口,单位是毫秒,默认是1000

change buffer写缓冲
数据进行更新操作(包括增删改,先查看 buffer pool中是否有数据,如果有则立即修改内存内容，返回结果
如果没有,也并不会加载数据到内存，而是记录变更操作change buffer等数据将来被读取的时候再将数据合并 merge到缓冲池的技术
有何好处:可以将多个更新操作合并到一次进行更新,减少磁盘IO.对于一个page页修改越多, change buffer带来的收益就越大
change buffer除了在内存里,也会定期持久化到磁盘表空间ibd文件中
何时触发 change buffer数据合并merge到 buffer pool中数据
1.数据被访问时
2.后台有一个线程在系统空闲时
3 change buffer内存不够用时
4. redolog写满时
5.数据库正常关闭时
参数: innodb_change_buffer_max_size
介绍:配置写缓冲的大小,占整个缓冲池的比例,默认值是25%,最大值是50%
画外音:写多读少的业务,才需要调大这个值,读多写少的业务,25%其实也多了
参数: innodb_change_buffering
介绍:配置哪些写操作启用写缓冲,可以设置成a/none/ Inserts/ deletes等
注意:对于唯一索引, change buffer不生效.因为要确保唯一性所以 buffer pool中没有数据时必须读取数据,查看是否冲突这样就用不上 change_buffer了

有了 redolog为什么还需要 change buffer?

redolog是重做日志主要目的在于系统崩溃后保证数据安全性. change buffer目的在于减少更新操作时磁盘随机IO读取,加快更新速度实际上在事务提交的时候 change buffer也被写入了 redolog,因此崩溃恢复的时候, change buffer也会被恢复

刷脏页与MNSQL抖动
刷脏页是什么?在内存中存储的与磁盘上存储的数据不一致的数据页被称为脏页将内存中的数据写入磁盘,将脏页变为干净页的过程被称为刷脏页
何时会触发刷脏页?

1. redolog写满.当 redolog的 writepoint追上 checkpoint的时候,会将更新操作暂停将checkpoint往前推推
   2.内存不足当系统申请内存不足时,会选择清空一部分页数据.如果是脏页则需要先把脏页数据写入到磁盘才能淘汰
   3.系统认为空闲的时候会刷脏页
   4.系统正常关闭的时候
   如何优化?
   对于情况1:需要控制 InnoDB刷脏页的策略,告知Mysq实际的刷脏页能力,不然会导致刷脏页太慢 innodb io_ capacity:告知刷脏页能力
   对于情况2:控制脏页比例.不然脏页比例太高,会导致淘汰内存页的时候刷脏页概率变大 innodb max dirty pages pct默认75%

五.索引

索引的类型
哈希.同 hashmap,使用哈希算法,哈希碰撞时使用链表缺点:只适用于等值查询不能做模糊或范围查询
B+树:树结构每节点大约1200个子节点则一颗高度为3的B+树可存储约17亿数据.只在叶子节点存储实际数据,非叶子节点存储键值和下一节点的地址
为什么不用二叉树?
二叉树每个节点有两个子节点存储数据会导致索引树高度很高一次查询访问多个磁盘数据块会导致效率很低而一个10亿级的表用B+树最多只用访问3次磁盘
B+Tree和B-Tree对比
1、同等树高时,B-Tree查询效率比B+Tree高,由于B-Tree节点中存储了key+data的整个信息,找到key就等于是获取到了数据,而B+Tree的data只存储在叶子节点,因此必须要遍寻到叶子节点才能获取到data信息
2、同等数据量时,B+Tree的查询效率比?B-Tree高,由于B+Tree的非叶子节点只存储key信息,而B-Tree的节点存储了key+data,而每个page页(一个节点)的大小是固定的,所以B+Tree的树高会更低一些

聚簇索引和二级索引的概念。

1.对于B+树结构索引,表中每个索引对应一棵B+树 2.基于主键的索引树称为主键索引,又叫聚簇索引.其他索引被称为非主键索引也叫二级索引.
3.主键索引的叶子节点存储完整的行数据二级索引的叶子节点存储主键的值因此,根据根据主键查询只需要查询一棵索引树根据普通索引需要先查询二级索引拿到主键值再去查询主键索引这个过程叫做回表
4.最左前缀原则联合索引按照索引定义时字段排序因此只能通过最左前缀查询用上这个索引.联合索引先按照第一个字段排序再按照第二个字段排序其余字段不排序
5.覆盖索引:需要查询的列被索引覆盖,即通过一棵索引树即可获取无需回表比如根据主键旧d获取数据;根据二级索引获取主键id;根据最左前缀原则查询复联合索引包含的字段;
6.索引下推:对于联合索引会将匹配到的数据进行过滤,减少回表次数比如有 name, age的联合索引查询条件是name=张三 and age>18.如果没有索引下推则根据name索引查询后回表再过滤年龄符合条件的数据有了索引下推,就可以不用回表 MYSQL56版本有此功能

普通索引和唯一索引的选择
1.查询速度普通索引在查询到一条数据后继续查找下一条数据,直到不满足条件为止唯一索引在查询到满足条件的数据后停止.实际性能差距:很小因为mysq读取数据按照page为单位整体读入内存查找下一条数据的成本很小而且一个page可以存放近千条数据下一条数据正好在下一page的几率很小
2.唯一索引在进行新增更新操作时, change buffer机制失效,写入速度更慢

讯享网

字符串前缀索引

将字符串的前n个字符存储为索引

优点:节省空间减少索引树大小
缺点:区分度不高时会增加查询次数.影响使用覆盖索引,需要回表。
使用前缀索引时一定要注意索引的区分度.比如身份证同一个地区的人身份证前缀相同后几位不同则可以倒序存储身份证,再加入前缀索引.查询的时候使用 reverse函数,缺点是不支持范围查询且函数消耗额外CPU资源

索引基数:
索引基数是该索引上不同值的数量
基数计算方法:抽样统计N个page上不同值的平均值,乘以page数.因此是粗略值
基数重新计算时机:当数据修改量超过1M时
M和N的值当索引统计信息持久化时默认N为20M为10统计信息不持久化时默认N为8M为16

索引选择
优化器负责索引选择,一般基于以下条件综合考虑
1.预估扫描行数倾向扫描行数越少的索引扫描行数越少,磁盘IO及CPU资源消耗越少计算方法:索引选择性:基数/全部数据量
2.有无涉及临时表
3.有无排序.如有排序倾向选择排序字段索引

六.连表查询

inner join:内连接查询两表交集
lfet( outer)join:左(外)连接:两表交集加左表数据
right( outer)join:右(外)连接:两表交集加右表数据

为什么要用小表连接大表查询mysq采用 Nested Loop算法,先遍历驱动表,再到另一张表中匹配对应数据(走索引).因此驱动表越小,循环次数也越少.大表在join的字段上也必须要有索引
多表join:将前面表join的结果取join后表,因此尽量把大表放后面
七.排序
举例查询city在深圳的人按name排序,返回name,age字段city上有索引
1.全字段排序:根据city字段索引查询出所有符合条件的行数据,挑选出name, age, city字段,加载进内存在内存中根据name进行快速排序,将结果返回给客户端
2 rowid排序:与全字段排序的区别是不加载全部字段,内存中只加载主键和排序字段,完成排序后再回表查询数据获取所需字段后将结果返回给客户端
和全字段排序区别rowid排序多回表一次,速度更慢,优点是节省内存
系统选用: max lengthfor_sort_ data.如果全字段的所有字段长度加起来超过这个值,则采
用 rowid排序
排序内存不足怎么办
排序内存( sort buffer)是每一个线程单独拥有的一片内存空间如果一个线程排序需要的内存大于 sort buffer size(默认1MB)配置的值则需要使用磁盘临时文件根据数据量大小,可能会使用多个临时文件,多个文件间采用归并算法合并排序因此如果服务器配置较好,可适当调大该值以提升效率

八. limit offset偏移量很大优化
例子: select* from user limit
1.查询第条数据id,再往后取10条数据. select* from user where id>=( select
id from user order by id limit , 1) limit 10
2.适用于连续查看不能跳页.即记录每一页的最后一个id,下一页在此id基础上往后偏移
10条数据即可思考:往前后跳页数不多也可以比如第一次查询记录了第页最后
一条数据id现在查询第N页数据则可用此id往后偏移(N-1) pageSize条得到该
页第一条数据再取 page Size条即可

九锁
mysq锁大致可分为全局锁/表锁/行锁三类
9.1全局锁
使用方法:命令 FLUSH TABLES WITH READ LOCK( FTWRL让整库处于只读状态.阻塞
DDL, DML语句
使用场景:不支持事务的引擎做整库备份
缺点:备份主库导致全部写操作阻塞;备份从库会导致主从延迟

2. FTWRL在客户端断开连接后会自动释放全局锁,而 readonly=true是永久设置
   9.2表锁
   1.使用lock命令显式使用
   lock table table name read读锁(S锁)
   所有线程均可读取数据,加读锁线程写操作会报错;其余线程会等待读锁解除后进行写操作ock table table name write写锁(X锁)
   加锁线程可以读/写,其余线程会等待写锁解除后进行读/写操作
   2.元数据锁( metadata lock)MDL
   无需显式使用.进行增删改查操作,自动添加MDL读锁.进行DDL语句自动添加MDL写锁MDL读锁互相不互斥,因此各个线程可同时进行增删改查操作.
   MDL读锁和写锁,写锁和写锁之间互斥.需要等待锁释放比如在读取数据时,修改表结构的语句会被堵塞
   MDL锁是 mysql的 server层的锁,对所有存储引擎有效
   MDL锁释放时机:1.对于事务引擎,事务提交时释放;2.对于不支持事物的引擎,语句执行结束时;

MDL锁带来的隐患

长事务对表进行增删改查操作获取MDL读锁,此时进行DDL操作会被堵塞.更致命的是后续此表所有操作全部会被堵塞
原因:DDL操作等待获取MDL写锁,MDL写锁优先级高于读锁,所以会一直等待MDL写锁的获取,在此之前,一切操作(包括增删改查及DDL)都会被堵塞。
如何避免MDL锁带来的隐患?
1.调小锁等待时间.超时直接放弃获取MDL写锁
2.减少长事务操作
3.业务低峰期进行DDL操作,操作前可查看该表是否存在长事务
意向锁:
1.意向锁是InoDB引擎自动加的表级锁分为S(意向共享锁和意向排他锁
2.当表中有行数据被加S(共享锁)时,系统自动为该事务获取该表IS锁;当表中有行数据被
加Ⅹ锁时,系统自动为该事务获取该表Ⅸ锁
3.设计原因:为了兼容表锁和行锁,提升效率.例:事务A持有表中某行数据的S锁,事务B申请该表写锁.申请时会判断①该表是否存在表锁②该表是否有数据上有行锁. 如果没有意向锁的话,将会遍历索引上全部数据来判断是否存在行锁,效率很慢.而有了表级的意向锁,则可以很快判断
3.意向锁之间都是兼容的.意向锁和非意向锁之间和普通锁的兼容性相同也就是S锁和S
锁相容,X锁和任何锁都不兼容

乐观锁与悲观锁:

区别:并发修改数据时的态度.悲观锁认为我要修改的数据一定会被其他线程修改,因此修改前对数据上锁(S锁或X锁)
乐观锁认为我要修改的数据不一定会被其他线程修改.因此不会对数据加锁.需要手动通过版本号实现.获取数据同时获得版本号,更新数据 update table set.ver=ver+1 wherever=ver;更新失败则说明版本号已不存在已经更新则程序应再次获取最新值重复上述操作直到更新成功为止
使用场景:乐观锁适合读多写少的表.悲观锁适合写多的表

9.3.行锁
1.noDB引擎支持行锁, Myisam引擎不支持行锁。
2.实现方式:通过给索引上的索引项加锁实现行锁.因此,如果给没有索引的列加锁,将使用表锁
2.1 对没有索引的列查询加锁,为什么不仅锁住满足条件的数据?例如查询name=张三仅使用行锁会导致幻读,因为其他事务也可以插入name=张三的值,在使用当前读的情况下,会出现幻读
3.使用方法
共享锁: select* from table name lock in share mode
排他锁: Insert/ update/ delete/ select… for update;
共享锁:事务A加上共享锁后,事务B可以加上共享锁,加排他锁会进入等待;事务A加上排他锁后,其他事务加任何锁都会进入等待。

注意:事务A加上排他锁后,其他事务只是不能再加锁,可以进行普通读!(普通的select语句)
4.查询何时使用S锁,何时使用X锁?
查询数据不涉及修改使用S锁(为了保证获取到的数据未被修改;查询后要修改数据使用X锁,保证自己修改期间别人不会修改)
5.使用共享锁容易死锁.比如:事务A使用共享锁查询某行数据,事务B也使用共享锁查询该行数据;事务 A update此数据会因等待事务B释放锁而进入阻塞,若事务B此时 update此数据,则也会因为等待A释放锁而进入阻塞.死锁形成
解决办法:使用排他锁.不允许两个事务对同一行数据同时持有锁,死锁条件不具备
9.4间隙锁:( Gap Lock)
目的:解决幻读问题
行锁只能锁住已存在的数据,对于不存在的数据无法上锁.因此,即使使用行锁锁住了
name=张三的所有行数据,其余线程还是可以插入name=张三的数据这就造成了语义上的
冲突.即行锁并未真的把所有name=张三的数据都加锁,造成了幻读
间隙锁( gap lock)锁住的是数据与数据之间的间隙,可以阻止其他线程往间隙中插入数据
间隙锁和间隙锁是不冲突的因此对同一条不存在的数据加上S锁和X锁是不会冲突的
间隙锁导致的死锁.由于间隙锁互不冲突,因此可以给同一个间隙上锁如果这两个事务在
获得间隙锁之后都试图往间隙中插入数据,就会造成相互等待,形成死锁、

1. select* from t where num=7 for update.在num为5-10的索引范围上加了间隙锁如果插入(6,5),(9,10)会被锁住,加入(4,5),(11,10)则不会被锁
   原因:因为要注意主键的大小排序(4,5)在左边界(5,5)的左边,(1,10)在右边界(10,10)的右边

9.5 next-key-lock
next-key-lock就是行锁加间隙锁
几大原则

1. innodb引擎在RR隔离级别下加行锁,默认加next-key-lock,前开后闭区间
   2.仅给访问到的数据加锁
   3.唯一索引等值查询时,若存在该值,next-key-lock退化为行锁;若不存在该值,退化为间隙锁
   4.等值查询时,从第一个满足查询条件的值开始添加next-key-lock,向右遍历直到发现第一个不满足查询条件的值不满足条件的值上退化为间隙锁
   5.一个bug:唯一索引上的范围查询会遍历到第一个不满足条件的值为止
   6.有lmit的语句在取到了需要的数量后会立即停止,不会继续向后遍历
   注意:

1.对于原则二如果使用 lock in share mode加行锁,在使用二级索引的覆盖索引进行查
询的前提下,不会去访问主键聚簇索引.根据原则2,主键索引上的值不会被加上行锁,也就是说根据主键id去更新值,是可以成功的.如果使用的是 for update加行锁,mysq会无视覆盖索引,除了给二级索引加行锁,也会给主键索引加行锁
加锁示例一张表t有两个字段:id(主键)num(普通索引).插入数据(1,1),(5,5)
(6,5),(10,10)(15,15)(20.20)、(25,25)

1. select* from t where num=5 for update,
   根据原则一,找到第一个符合条件的值(5,5),所以给索引(1,5)加上左开右闭的next-key
   lock注意:此区间左节点为(1,1)右节点为(5,5);继续向右遍历,来到(6,5),加上(5,5)到(6,5)的左开右闭区间;继续向右遍历,发现(10,10)不满足条件,于是(6,5)到(10,10)的左开右闭区间退化为间隙锁,也就是左右都是开区间.所以next-key- lockd的区间加起来三段加起来
   就是(1,1)到(10,10)的左右开区间且在(55)和(6,5两行数据上有行锁
2. select * from t where id=5 for update:
   首先加上next- key-lock,(1,5],再根据原则2,id是唯一索引退化为行锁因此只锁了(5,5)这行数据
3. select* from t where id>=6 and id<10 for update
   拆开看,首先看id=6,再看d>6且<10.id=6,退化为行锁,锁住(6,5)继续往右遍历到10,不
   满足id<10,所以退化为间隙锁(6,10).综上6,10)
4. select from t where num >=5 and num<10 for update:
   首先看num=5,确认next-key-lock范围是(1,1)到(5,5)的左开右闭区间(5,5)上有行锁;继
   续遍历到(6,5),确认(5,5)到(6,5)的左开右闭区间;继续遍历到(10,10)不满足num<10,因
   此(6,5)到(10,10)退化为间隙锁.综上next-key-ock的范围是(1,1)到(10,10)的开区间,在
   (55)和(6,5)上有行锁

5一个bug举例: select* from t where id>7 and id<=10 for update;