MySQL

mysql
存储引擎：不同的数据组织形式
innodb
.frm存放表结构或者元数据
.ibd存放表数据和表索引
索引文件和数据文件是放在一起的
有事务
默认支持行锁
锁一般是锁数据，但是innodb是锁的索引
如果没有索引，也只能是表锁
支持外键

undolog可以保证事务的原子性，innodb还可以用undolog保证多版本并发控制mvcc


myisam
.myd 存放表数据
.myi 存放表索引
.frm 存放表结构等
没有事务
默认支持表锁
不支持外键


不同引擎意味着 不同文件的组织形式 。


mysql架构：三层
客户端
server：
连接器 ：管理客户端的数据连接（数据库连接池，解决频繁创建连接的问题 dbcp c3p0 阿里druid ，springboot的h……ry

》show processlist

分析器：词法分析，语法分析，最终将你提交的sql字符串 生成抽象语法树 

优化器：抽象语法树 ️n多种解析方式，每种方式的执行过程是不一样的，性能差距也是很大 。主要有两种方式：1.cbo 基于成本的优化 2.rbo基于规则的优化

其实我们提交的sql，mysql底层是会自己帮我们优化的。从而选择一个它认为最优的执行过程

执行器：选择一个最优的执行过程到存储引擎里面读取数据，返还给客户端。

存储引擎：innodb myiasm memory

——分割线—
mysql日志 

默认是在 /var/lib/mysql，消耗性能，数据丢失可以找回
binlog 归属于server层，默认关闭
redolog 只归属于innodb存储引擎层
undolog 也只归属于innodb存储引擎层

binlog和undolog都是append方式 
redolog是循环写状态，写满茶掉

show variables like “%log_bin%”


mysql原子性和持久性如何保证？
不得不提到数据库的acid
a：原子性
c：一致性
i：隔离性
d：持久性

这四个特点底层实现的原理是啥？
其实底层是各种日志保证的
原子性 实现原理：undolog
持久性 实现原理：redolog
隔离性 读写锁➕mvcc
一致性 上面三个共同
undolog：记录上一个一致性的状态，和你的语句相反，
insert delete
delete insert
update update

undolog日志文件是二进制文件。
文件是有自己的格式的。

行式存储 ：每一行数据放在一起
列式存储：每一列数据放在一起

快照：某一时刻的数据

原子性：一个事务包含n多条sql执行语句。要么执行成功，要么全部失败。
任务执行失败就要回滚到之前的状态。

—分割线……
数据库是用来持久化数据存储
持久性：事务一旦提交，对数据库的更新就是持久的。底层通过redolog保证。

写数据的时候，并不能直接写到磁盘里面去，先是放在内存中。redolog记录新数据状态。

隔离性
本质上是通过锁来实现的
理论上事务之间的执行不应该相互影响，对数据的执行应该和串行化执行一致

完全的隔离会降低系统并发性，但是一般会放宽隔离，这也一定程度上造成对数据库的一致性降低

即：

隔离级别越高 数据越安全，并发行越低
级别越低 越不安全，并发行高

sql标准为事务定义了不同的隔离级别，从低到高：
1.读未提交，没有任何限制🚫，不推荐
产生脏读，一个事务未提交对数据的更改，居然能够在另一个事务里面查到更改后的数据
-》产生脏读 不可重复读 幻读

2.读已提交 oracle默认隔离级别
产生不可重复读，一个事务对数据更改
不手动提交另一个事务数据查询不了更改后数据的（解决了脏读问题），手动commit之后就变成了新数据
-》产生不可重复读 幻读


3.可重复读 mysql默认隔离级别
解决了不可重复读的问题、会产生幻读，
一个事务对数据更改，不手动提交另一个事务数据查询不了更改后数据的（解决了脏读问题），手动commit之后 ，还是无法查询到更改后的数据（解决了不可重复读）。

这个时候在一个事务里面insert数据，接着commit。这条数据是在另一个事务是查询不了的。同时，你在另一个事务插入同一条数据 会发生报错。


4.串行化


脏读 幻读 不可重复读 
1.关闭事务自动提交，默认开启的
select @@autocommit；
set =0；

2.设置事务不同隔离级别
select session transaction ioolaction level read uncommitted

begin；开启事务

脏读：在一个事务里面对 数据的变更 没有提交，这个时候居然能够读到这个未正常事务提交的数据。
读未提交隔离级别出现这个情况
隔离界别提升到 读已提交 就能解决这个脏读问题

默认
不可重复读：一个事务里面先后查询同一份数据，结果不一致。
读已提交还是会出现不可重复读的问题
……分割线……🧵

锁  下去搜索资料💾
在谈及锁的时候要与存储引擎挂钩
myiasm 共享锁 独占锁
innodb 共享锁 排它锁
cs compare and sweep 比较和交换

转账义务需求
一致性：事务的执行结果必须使数据库从一个一致性状态 转移到 另一个一致性状态

反映真实世界数据库状态的变更


【索引】

innodb数据结构b➕树🌲
什么叫回表
最左匹配
索引覆盖
索引下推

如何根据索引来优化查询

b树可能造成树的深度过深 ，从而造成io次数增多 意味着查询性能低

b➕树里面只有叶子节点存放数据
b树每一个节点都会存放数据

索引：加快数据的访问 
字典 图书馆等都是索引的应用

mysql数据存放在磁盘
查询慢 主要是卡在io 瓶颈，想想笔记本机械硬盘换成ssd。

提高io效率：1.减少io次数 2.减少io的量
满足当前系统需求的前提下，尽量减少读取的数据量

磁盘预读：内存和磁盘发生数据交互的时候，一般会有一个最小的逻辑单元，称之为页。即datapage。一般是由操作系统决定，一般大小是4k或8k。而我们在于数据交互的时候可以取datastage的整数倍来进行读取。

innodb存储引擎每次读取16k

局部性原理：数据和程序都有聚集成群的倾向。物以类聚人以群分……之前被访问的数据 很可能会被再次访问到
️空间局部性和时间局部性

索引系统是和io挂钩
如何设计索引系统？

索引存放在磁盘，查询数据库的时候优先将索引加载到内存中

1.索引真正存放的是什么，具体是些什么值？
key：实际数据行中存储的值
文件地址
文件内的offset

2.上面这些存储的索引值应该选用啥数据结构来存放呢？
可以发现数据是k-v格式
这样我们可以使用hash表
树：二叉树 红黑树 avl树 b树 b➕树🌲
mysql的索引系统中不是按照上面格式存储的，

olap：联机分析处理，侧重于对海量历史数据分析，产生决策分析。数据仓库，
hive的索引就是key值
oltp：联机事务处理，侧重于在短时间内返回对应的数据结果。关系型数据库

引出 mysql的索引系统—b➕树
为啥是b➕树

哈希表：存在hash冲突的问题
哈希冲突会导致数据散列相当不均匀，会产生大量的线性查询，比较浪费时间。当进行范围查询的时候，需要挨个进行便利遍历
hash表对于内存空间要求比较高
优点：如果是等值查询，非常快。
在mysql中是有hash索引的，memory存储引擎使用的是hash索引，innodb存储索引支持自适应hash


现在5.1？之后默认存储引擎是innodb，之前是myisam

插入数据的时候，左子树必须小于根节点，右子树必须大于根节点。 这样就可以使用二分查找来提高效率

O（n）到O（logn）

如果插入的数据是递增或递减 应该咋办？
退化成链表，查询效率又会变成O（n）

经过左旋 或者 右旋 让树🌲平衡起来，avl：即平衡二叉树。最短子树和最长子树的高度之差不能超过1。


avl树：为了保证平衡，在插入数据的时候必须要旋转。旋转需要耗时，这样通过插入数据性能损失来弥补查询数据性能的提升，
写少读多的场景还是适合

3读写请求一样多，怎么办？
引入红黑树，左旋右旋让树平衡起来。接着，还有变色的行为
红黑树：最长子树只要不超过最短子树的两倍即可。

查询性能和插入性能接近一致。

随着数据的插入，发现树的深度会变深，深度约深，io次数增多。此时影响数据读取的效率。（本质我们应该减少磁盘io次数和io量大小）

二叉树：每一个节点有且仅有两个分支

把原来的有序二叉树变成有序多叉树：每个节点存储多个数据值，即每个节点存储一个范围值 

b树所有节点都要存放数据
b➕树只有叶子节点存放数据，非叶子节点不存放

 三层mysql 的b➕树一般是3到4层，如果3到4层还存不下，就要考虑 分库分表 。

创建索引用int好还是varchar好？
平常建索引的子段空间尽量小一点

4.聚簇索引vs非聚簇索引 ？
聚簇索引：数据和索引文件放在一起
取决于数据和索引文件是否是放在一起的

frm
ibd ，innodb=只能有一个聚簇索引➕多个非聚簇索引。这是因为聚簇索引的数据和文件要放在一起，所以innodb也只能有一个聚簇索引，myisam因为是分开的，只能有非聚簇索引

向innodb插入数据的时候，必须要包含一个索引的key值！
1.索引的key值可以是主键，
2.没有主键那就是唯一键，
3.唯一键都没有，那就是自生成的6字节的rowid，rowid对用户不可见。

myisam 都是非聚簇索引。



5.mysql表中索引
至少一个。
什么叫
1回表？
2最左匹配
3索引下推
4索引覆盖

假设有一张表，
id 主键：索引key=主键id，value=整条数据➕key
name 索引列，索引key =zhangsan，value=主键id
age
gender
1.sql：select * from student where name=zhangsan
这条语句会先根据name去查询id，
再根据id去查询整条数据，走了2颗b➕树。
此时这种现象叫做回表：先根据普通索引查询到聚簇索引的key值之后，再根据key值在聚簇索引中获取整条记录

2.sql： select id，name from student where
name=zhangsan
可以直接根据name查询到id，name的值，不需要从聚簇索引查询任何数据。此时叫做索引覆盖

alter table test add index inx（name）；此语句表示在name列上创建索引。把name列的值当作key值。

说白了底层存储数据的时候，会按照聚簇索引（b➕树）来存放。这样查询效率更高。

主键 联合主键
索引 ：普通索引 联合索引（组合索引）可能包含到多个索引列

来聊聊组合索引？
假设有一张表
id 主键
name
age name和age是组合索引列
gender

最左匹配原则
tips：组合索引使用的时候，必须先匹配name，再匹配age


select * from student where name=zhangsan 用

select * from student where name =Zhangsna and age=18 用

select * from student where age=18 不用

select * from student where age=18 and name=zhangsan mysql优化器会先优化先去匹配name 

索引下推概念：5.7以后默认支持
select * from student where name=zhangsna and age=18

在没有索引下推之前：这条语句会先根据name从存储引擎中获取符合条件的数据，然后再在server层对age进行过滤

在️索引下推之后：会根据name和age两个条件从存储引擎直接获得符合条件的数据

即：原来在server层做的计算，放到存储引擎层来做。减少server层和存储引擎层的数据交互。从而减少数据磁盘io。

myisam和innodb区别：
索引存储结构
锁不一样
外键
事务

io 
aio nio bio direct io
等多种io



.......分割线……
mysql事务以及锁机制 揭秘
spring 里面有一个platformtransform ？的类
以aop的方式来帮我们进行事务的管理。
使用的时候，加个@transform 的注解
由spring帮我们管理，不用自己管理。

so？到底啥叫事务
一组sql语句全部成功，要么全部失败！
场景：转账场景 
大概需要3条sql
1.先查是否有那么多钱2.从我的账户减3.对方账户加

事务即：一系列操作要么全部成功，要么全部失败。本质其实最终就是为了保证数据一致性。

谈到事务就不得不提到事务的ACID。

那么acid每个特点底层实现原理是啥？


跨行转账：分布式事务
二阶段提交啥的


————分割线———
1.谈一谈你对mysql索引的理解？
本质：加快数据访问
数据结构：
innodb和myisam存储引擎是b➕树 
memory存储引擎默认是hash表

这里不同 存储引擎 底层是不同的文件组织形式

索引一般是根据某个key（某一列的值）去找到value（整条记录）

当你在存储k-v的时候 你可以选择二叉树 红黑树 avl树 b树 b➕树等
但是最根本的原因，选择树这种结构会导致树变高，从而影响io的次数，从而影响数据查询的性能。
使用b➕树这种结构可以在同一个数据节点里面尽可能的存放多的数据，让树变低，减少io次数，提高查询性能。

同时 mysql里面有 
主键索引 
普通索引
组合索引
全文索引等
 用得更多的是主键索引 和你 组合索引，在使用这些索引的过程中会出现 回表 最左匹配 索引下推 索引覆盖等细节点

️聊到索引一定要聊到存储引擎

小结：
存储引擎：数据在磁盘上的不同文件组织形式，
innodb 
frm 表结构
ibd 表数据和表索引
myisam  
frm表结构
myd 表数据
myi 表索引

innodb支持事务 ，myisam不支持事务
innodb支持外键，myisam不支持外键
innodb支持表锁和行锁myisam只支持表锁




不同存储引擎可能用同一种数据结构，但是在磁盘上数据存储的时候是不一样的。

数据结构：用啥具体的数据结构来存放你的数据
索引分类：

索引涉及到的常见名词：

执行计划：
索引优化：



2.聚簇索引vs非聚簇索引
聚簇索引：数据和索引放在一起的
非聚簇索引：数据和索引是分开的
innodb既存在聚簇索引也存在非聚簇索引，聚簇索引的key和value是放在一起的，非聚簇索引的value是存放的主键索引？

myisam中只存在非聚簇索引，索引value只存放数据行的索引地址，真实的数据是在另一个文件中的

分库分表常用技术：mycat  shardingsphere（推荐）.drds




回表 
索引覆盖 
索引下推 
最左匹配 

2

终极
mysql调优
1.排除缓存干扰
mysql8.0之前是支持缓存的，如果你开启了。那么就需要注意你的sql可能就会时快时慢了。缓存key-value：key你的查询语句value查询结果

经常失效：
只要你的表一旦更新了，那么该表所有的缓存会被清除。

2.explain
用执行计划去分析sql一般都要回答道
写业务sql的基本操作
你记得explain有哪些字段，分别是啥含义？

统计的行数只是一个接近的数字，索引不一定走最优，也可能会走错。

走错索引：a索引假如10w行，b索引假如1000行，但是发现b索引要走回表，这个回表过程也会被优化器考虑进去，最后执行器还是选择了走a索引。所以选择错了。
走错索引咋办：
1.生产中你发现explain得到行数和预估行数差距较大时候，你可以使用analyze table tablename 进行重新统计
2.还有可以使用force index 强制走正确索引（不推荐）

️索引覆盖
为了解决回表的问题，即走了普通索引之后，还要走聚簇索引找到所查数据。

索引覆盖：我们可以在需要的子段上创建索引，这样就可以直接从索引查到需要的数据不需要二次回表。减少树的查询次数，减少磁盘io次数。
可大大提升查询性能

️最左匹配原则
在联合索引的基础上来说，查询语句如果想要走此联合索引，那么你的where子句一定要包含 联合索引的第一个子段，并且最好放在最前面

️索引下推
简单来说就是把所有数据过滤的操作都放在了存储引擎层来做，避免了之前的先过滤一部分数据到server。再在server层二次过滤。这其中涉及到大量不必要的server与磁盘上的io操作。


️索引字段函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

这里注意隐是字段类型转换也是会放弃走索引，进行全表扫描的。还有就是字段编码也会影响索引的选择。


小结：

SQL规范性检查，
表结构检查，
索引检查
以及通过SQL改写 来优化查询

sql规范性检查：
1.1udf用户自定义函数：如果select 对后面字段套用了udf函数，那么sql返回多少行就会调用多少次udf函数，性能很低。

1.2 text类型字段 ：消耗大量网络和io带宽

1.3 内联子查询：在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。

where检查：
1.4索引列被运算
当一个字段被索引，同时出现在where条件内，是不能进行任何运算的。否则会导致索引失效。

1.5字段类型 转换问题
int传varchar可以走索引
varchar 传int 索引失效

表结构：
2.1 字段属性
not null default 某一值
尽量有默认值

2.2不建议text类型
传输消耗大

索引检查
3.1 复合索引 
注意满足最左匹配原则