MYSQL常见知识总结
InnoDB与MyISAM的区别
show engines查看当前支持引擎;mysql5.5之后默认为InnoDB
事务
行表锁
外键
InnoDB是聚族索引（数据文件即索引本身）
数据崩溃后恢复
是否支持MVCC，InnoDB的MVCC只在read-commit和repeatable-read下工作，可以使用乐观锁或者悲观锁来实现
索引结构
大多数情况下建议使用B+Tree
支持范围查询
适用于单条记录查询
不支持范围查询
Hash索引
B+Tree索引
并发带来的问题
脏读
丢失修改
不可重复读
幻读
事务隔离级别
read-uncommit😻脏读、不可重复读、幻读
read-commit 不可重复读、幻读
repeatable-read 幻读 （默认）通过
java SELECT @@tx_isolation;来查看serializable
隔离级别 脏读 不可重复读 幻读 read-uncommit ✔️ ✔️ ✔️ read-commit ❌ ✔️ ✔️ repeatable-read ❌ ❌ ✔️ serializable ❌ ❌ ❌ 分布式id生成方案
数据库自增id
redis生成
雪花算法
数据库三大范式
第一范式：列不可分割（原子性）
第二范式：行具备唯一性（主键）
第三范：非主属性不可依赖与其他表的非主属性（外键）
SQL优化手段有哪些
不要使用select *
不要使用子查询，使用关联查询替换（join、left join等）
or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)
减少使用in、not in，使用exit、not exit代替
where子句不要使用<> 、！=，否则将引擎放弃使用索引而进行全表扫描
应尽量避免在 where 子句中对字段进行 null 值判断
char与varchar的区别
char与varchar在存储与检索的方式不同
char的长度范围是0-255，列的长度固定为创建表时声明的长度
当char值被存储时，它们被用空格填充到指定长度，检索char值时需要删除尾随的空格
Mysql架构
连接层：认证、授权、连接池
服务层：缓存、解析、优化、内置函数
引擎层：执行、插件式选择，存储引擎真正的负责了MySQL中数据的存储和提取
存储层：主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互
查看MySQL 数据保存在哪里
show variables like 'data%'在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的
.frm文件，.frm文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为 数据表名.frm，如user.frm。B-Tree 和B+Tree索引
B+Tree相对于B-Tree有几点不同：
1) 非叶子节点只存储键值信息；
2) 所有叶子节点之间都有一个链指针；
3) 数据记录都存放在叶子节点中从上图中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的（默认16KB），如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小
B+Tree
B-Tree
那为什么推荐使用整型自增主键而不是选择UUID
UUID占用空间大，相同数据下会使树的高度增加，查询速度变慢
在B+树查找时，需要进行值比较，整型比较比字符比较更快
UUID不适用范围查询，比如查 where id>5 && id<20的数据记录
在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。
Hash索引
检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。目前使用Hash索引的数据库并不多，主要有Memory等。
MySQL目前有Memory引擎和NDB引擎支持Hash索引。
主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。
Full-text索引
全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。
它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。
R-Tree索引
空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型
mysql有哪些锁
行锁
表锁
分页锁
间隙锁
区间锁，左开右闭，如id 为（1，4】
临建锁
其他角度看
共享锁
排他锁
还可以分为
乐观锁
悲观锁
explain各字段意思
列 名 描述 id 查询语句每出现一个Select关键字，Mysql就会为它分配一个唯一的id值，某些子查询会被优化为join查询，那么出现ID会一样 select_type select关键字对应的查询类型 table 表名 partitions 匹配的分区信息 type 针对表单的查询方式（全表扫描、索引） possible_keys 可能用到的索引 key 实际上使用的索引 key_len 实际上使用到的索引长度 ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 rows 预估需要读取的记录条数 filtered 某个表经过搜索条件过滤后剩余记录条数的百分比 extra 一些额外的信息，比如排序等 mysql如何保证ACID
原子性
通过undo log（回滚日志）来保证
一致性
通过数据其他三大特性来保证一致性，程序代码要保证业务上的一致
隔离性
通过MVCC来保证
持久性
redolog,当死机时通过redolog来恢复
in 与exit区别
in 相当于多个or查询的叠加
exit相当于bool
如果查询的两个表大小相当，那么用in和exists差别不大。如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
UNION与UNION ALL区别
首先，它们都要求彼此的结果集类型和字段名相同
UNION会去重，效率低，会按照字段顺序进行排序
UNION ALL不会去重，效率高，只是简单将结果集合并
JOIN
查看当前事务隔离级别
show variables like 'tx_isolation';
MVCC(Multi Version Concurrency Control)多版本控制
MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。
MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事务看到的数据都是一致的。
典型的MVCC实现方式，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。下边通过 InnoDB的简化版行为来说明 MVCC 是如何工作的。
InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。
group by having
优先级 from-->where-->group by-->select-->having-->order by