存储引擎
mysql表有几种类型/存储引擎？
INNODB,MYISAM,MEMORY,CSV
INNODB
设计
存储结构=内存区域+磁盘区域
刷盘策略：
存储方式
特点
innodb VS myisam
INNODB特点：
1.支持ACID的事务，支持四种隔离级别。
2.支持行级锁及外检约束，支持写并发。
3.不存储总行数。
4.一个innodb引擎可能存储在一个文件空间，也有可能多个，取决于表空间是共享还是独立。
5.主键为聚簇索引，其他索引为非聚簇索引。主键数据域存储数据文件本身，其他索引数据域存储主键。索引主键最好用自增索引，否则为了维持B+树平衡可能会发生大调整。
MYISAM：
1.不支持事务
2.不支持行级锁，仅支持表级锁。
3.存储总行数。
4.一个muisam表有三个文件：索引文件、表结构文件、数据文件。
5.采用非聚簇索引。
索引
什么是索引
索引是可以用来快速查询数据记录的一种数据结构
索引类型
按照索引类型可以分为：
主键索引：primary 每张表有且只有一个，不允许为NULL，唯一标识表中的每一行数据，也是聚簇索引
唯一索引：unique 索引列的所有值都是唯一的，不能重复，允许为NULL
全文索引：fulltext 用于海量文本的快速查询，innodb不支持
普通索引：没有什么限制，单独维护一份数据结构，来提升检索效率
按照物理存储方式可以分为：
聚簇索引：数据行的存储位置按照索引的顺序存放。INNODB 主键。
非聚簇索引：索引存储的位置与数据行的存储位置不同。
根据实现的数据结构可以分为：
B-Tree索引：大多数存储引擎都用的B-Tree,INNODB ——B+ Tree
Hash索引：MEMORY使用。
根据索引的列数量可以分为：
单列索引：一列组成
复合/联合索引：多列组成
事务
数据库事务
事务，有一个有限的数据库操作序列构成，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。
事务四大特性
A Atomicity, C Consistency, I Isolation, D Durability
隔离级别有哪些？怎么实现的？默认使用？怎么查看？设置？
隔离级别及存在的并发问题
为了解决并发事务存在的脏读、不可重复读、幻读等问题，数据库设计了四大隔离级别。分别是：
读未提交:RU 脏读、不可重复读、幻读 啥也不做
读已提交：不可重复读、幻读 （Oracle默认） MVCC实现。每次读都会新产生一个readview，新的readview里面已提交的事务已经不在活跃列表，所以可以查看。
可重复读：幻读 （MYSQL默认）MVCC实现 解决幻读可以加锁，共享读锁或者互斥写锁，锁住间隙，主要是组织其他事务新增或者删除数据。
串行化：没有并发隔离问题，但是效率很低SHOW VARIABLES LIKE '%isolation%'；
分布式事务
XA协议 innodb支持，提供api AP,TM,RM角色，两阶段提交
XA协议
xa协议是一种管理分布式事务的规范，提供了一系列API，包括：
xa start, xa end, xa prepare, xa commit, xa rollback
包含3大角色：AP（应用程序）,TM（事务管理器，协调者）,RM（资源管理器eg.mysql 服务）
mysql提供了对xa协议的支持。
分布式事务常见处理模式
1.基于XA协议的2PC/3PC
2pc分布式事务处理流程
其中 xid是全局事务id,由客户端生成或者mysql服务器生成。
2pc中RM没有超时功能，如果和TM失去联系会长期挂起阻塞。单点TM可用性差，存在数据不一致情况
基于XA协议的3PC
特点：
3PC对于协调者（Coordinator）和参与者（Partcipant）都设置了超时，超时后，自动进行本地commit从而进行释放资源。时间避免了RM与TM失联无法释放资源的问题，降低了整个事务的阻塞时间和范围。
3PC多设置了一个缓冲阶段保证了在最后提交阶段之前各参与节点的状态是一致的。
3PC依然没有完全解决数据不一致的问题。
3阶段canCommit, preCommit, doCommit/abort
2PC和3PC是数据库层面的操作，对于长事务，会长时间锁RM。
而TCC是业务层面实现的分布式事务。
2、事务补偿TCC
特点：
TCC本质上就是一个应用层面的2PC，需要通过业务逻辑来实现。这种分布式事务的实现方式的优势在于，可以让应用自己定义数据库操作的粒度，使得降低锁冲突、提高吞吐量成为可能。
而不足之处则在于对应用的侵入性非常强
针对每个操作都要注册一个与其对应的确认和补偿（撤销操作）
操作
try, confirm, cancel
另在还有AT模式，saga模式
因为2pc 3pc存在的不足，seata分布式事务解决方案应运而生，它实现了多种常见的分布式事务解决方案。
seata-分布式事务解决方案
锁
有哪些锁/锁分类？
怎么产生？和隔离级别的关系？
Mysql的锁按照锁粒度可以分为：（INNODB只支持表级锁和行级锁）
行级锁：record lock,最小粒度的锁，锁定资源的争用概率最小，并发度更高。
表级锁：一种是表锁，例如自增锁，或者显示加锁，lock tables 。一种是元数据锁，比如执行alter table DDL语句。
页面锁：并发度和锁粒度都解决行级锁和表级锁之间。
全局锁：给整个数据库实例加锁，使得数据库处于只读状态。使用场景：数据库备份。
按照锁的兼容性可以分为：
共享锁（S）：select ..... in share mode。允许其他事务读取持有锁的记录，但是不允许修改。如果不加锁是可以修改的。
排它锁（X）：select ..... for update。不允许其他事务加共享锁。
按照锁的模式可以分为：
记录锁：record lock.
间隙锁：gap lock. 左开右开。RC隔离级别下不会使用，RR及以上才会用到。
临键锁：next key lock ,左开右闭。
意向锁：表级锁，在获取共享锁或者排它锁之前获取表的意向锁，包括共享意向锁，和共享排它锁。
插入意向锁：行级锁，间隙锁的一种特例，间隙锁里插入的不同数据，先获取插入意向锁，可以插入成功。
自增锁：在有自增主键的数据插入时，获取自增锁的才可以插入，一条一条来。
数据库死锁
死锁产生的必要条件：
1、互斥条件：一个资源每次只能被一个进程使用；
2、请求与保持条件：一个进程因请求资源而阻塞时，对以获得的一员保持不放；
3、不可剥夺条件：进程已获得资源，在未使用完成之前，不能强行剥夺；
4、循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系。
数据库死锁产生的原因：
事务并发控制：事务同时请求多个资源，并以不同的顺序获得这些资源
锁粒度不当：锁粒度范围太大，导致事务等待对方释放锁的时间过长
事务持锁时间过长：事务持有锁的时间过长，其他事务无法访问相同资源
数据库死锁的解决
1发现 ：show processlist, ->state : waiting for lock 找到死锁的sql,SHOW ENGINE INNODB STATUS
2.解决： kill [进程ID]
优化事务逻辑：减少事务持有锁时间、调整事务提交的顺序
锁粒度调整
事务重试机制：在应用层实现事务失败后的重试机制，减少死锁的影响
调整InnoDB参数：innodb_deadlock_detect_interval,innodb_lock_wait_timeout
查询操作流程
流程图，各个组件的作用？设计原理？用到临时表的情况？
查询sql语句用到临时表的情况：
1.order by排序 的字段不是id也没有查询出来
2.group by
写入操作流程
MVCC
MVCC是什么？
MVCC是多版本并发控制的意思，是一种并发控制的方法，在mysql中，用mvcc实现数据库的并发访问，是mysql中用来实现RC,RR的方式。
数据库存在多个版本的数据，在某个事务对其进行操的时候，需要查看这一条记录的隐藏列事务版本id,比对事务id并根据事务隔离级别去判断读取哪个版本的数据。
MVCC的引入？
数据库通过加锁，来实现事物的隔离性。串行化隔离级别就是加锁实现的。但是频繁地加锁，导致读数据时没法修改，修改数据时，没办法读取，大大降低了数据库性能。因此，mysql 引入了MVCC机制，它实现读取数据不用加锁，可以让读取数据同时修改。修改数据同时可以读取。
实现？
trx_id,roll_point,undolog 版本链记录不同版本的数据； readview用来做可见性判断，判断当前事务可见哪个版本的数据.
当前读？快照读？
当前读读取的是记录数据的最新版本，显式加锁的都是当前读。lock in share mode, for update.
快照读读取的是记录数据的可见版本（由旧的版本）。不加锁。普通的select语句都是快照读。select * for t where id =1;
加锁？key上，基本单位临建锁，可以退化为记录锁或者间隙锁
在读已提交只支持行锁，可重复读以上才支持临建锁、间隙锁
Read View
执行SQL语句时产生的读视图。
read view的重要属性
m_ids:活跃事务的id list
min_limit_id:生成read view前最小的活跃事务id,m_ids最小值。
max_limit_id:创建read view时，系统中应该分配给下一个事物的id值。
creator_trx_id:创建当前read view的事务id.
在什么时候生成read view？
读已提交RC隔离级别下每次执行sql时都会生成一个readview.同一个事务可能有多个readview.
可重复读RR隔离级别下在事务开启后，第一次执行SQL时会生成一个readview.事务结束前不会再生成。同一个事务都是副本公用的，所以可以保证每次查询的数据都是一样的。
read view-事务版本链匹配规则
事务里的任何SQL查询结果需要从对应的版本链的最新数据逐条开始与readview比对从而得到最终的快照结果。
只有两种情况不可见，版本链记录的事务id>下一个要分配的事务id,且！=当前事务id
版本连事务id在活跃视图组中，且！=当前事务id，、
这两种情况的版本连记录对于当前查询是不可见的。
MVCC执行一次查询的流程
1.获取当前事务id/版本号
2.获取read view
3.查询得到的数据，然后与read view中的事务版本号进行比较。
4.如果不符合read view的可见性规则。需要读取undo_log中历史快照。
5.最后返回符合规则的数据。
MYSQL数据丢失的情况
mysql数据丢失有两种情况，
一种是本地数据丢失，也就是redo log日志没有及时刷新到磁盘，发生断电宕机等引起的数据丢失
另一种是mysql多节点数据复制时造成的数据丢失
redo_log
数据时写入时是先写入内存的，为了防止数据丢失，引入了redolog机制，但是redolog的数据也是先写入redo_log_buffer,但是redo_log_buffer刷盘前宕机也会导致数据丢失。这就要看redo_log的刷盘时机如何配置：
innodb_flush_log_at_commit. 0每秒 ，1 每次刷盘，2每次commit都write chache但是不flush(1秒一次)，
可以根据需要设置。
bin_log
sync=0 mysql不控制，OS处理, >0 每次刷新
sql语句优化与explain分析
sql语句优化
explain重要字段？
id : 序列号，从上往下执行，越大越早执行
type: sysrem>const>eq_ref>ref>range>index>all 至少达到range,最好达到ref
key: 实际用到的索引
row: 估计要读取的行数
extra: 额外信息
Using index:使用覆盖索引
Using where、Using index condition、
Using temporary(用了临时表，需要优化)
Using filesort, 用外部排序，而不是索引排序，数据小时可以在内存完成，大的话需要在磁盘完成排序。需要优化
Using temporary的原因和优化
MySQL在处理复杂查询时，如果不能有效地使用索引来排序或分组数据，就可能会创建临时表来临时保存中间结果，以便继续处理。这样做可以避免在每个步骤中对大量数据进行排序或分组，从而提高查询效率。
解决方法:
-
优化查询语句：检查查询的复杂度，并尝试简化查询逻辑，比如合并多个简单查询为一个，或者重写查询以减少需要的临时表。
-
增加/优化索引：确保查询中涉及的列有有效的索引，这样MySQL可以直接从索引中检索数据，而无需物理排序或分组。
-
调整MySQL配置：增加
tmp_table_size和max_heap_table_size参数的值，以允许更大的内存临时表。
Using filesort 使用外部排序的优化
表示mysql需要进行额外的排序操作，而不是直接从索引中读取已排序的数据，通常发生在排序操作无法通过索引直接完成时。
产生的几种情况：
1.查询不使用索引的排序。
2.符合排序和非索引排序列
3.排序数量大
4.排序列不在同一个索引
5.不必要的排序
6.mysql优化器判断不知用索引排序更加高效
强制使用索引，force index
解决方案：
1.优化查询语句，确保查询可以利用索引，减少无必要的排序操作。
2.增加或优化索引，为经常需要排序的列添加索引，或者优化现在有的索引结构。
3.调整服务器配置，增加可用于排序的内存（sort_buffer_size）,或者调整其他与排序操作有关的配置参数。
4.分析和调整查询：对于复杂查询，可能需要重写查询逻辑，分解为多个简单的查询以减少filesort的需求。
表设计
设计原则遵循三大范式：
1NF：字段的原子性
2NF：1NF前提下，非主键列完全依赖于主键，不能只依赖于主键的一部分。
3NF：在2NF的前提下，非主键列之间不存在传递依赖。
但是实际项目中有时候也要打破3大范式
表设计经验：
选择合适的字段类型
尽可能选择存储空间效的字段类型，tinyint、samllint、int、bigint从左往右开始选择
小数类型如金额选择decimal,禁止使用float和double
如果存储的字符串长度几乎相等，使用char定长字符串类型。
varchar是可变长字符串，不预先分配存储空间，长度不要超过5000
如果存储值太大，建议字段类型修改为text,同时到哪都售出一张表，用主键与之对应。
同一张表中，所有varchar长度加起来不能大于65535.如果有这样的需求请使用text/Longtext。
时间的类型选择，datetime存储范围更大，且跟时区无关
主键设计要合理
主键设计最好不要与业务逻辑关联。有些业务上的字段，比如身份证虽然是唯一的，但是不建议用来做主键。主键最好是一串数字，或则是自增主键，或者是雪花算法生成的主键。
选择合适的字段长度
varchar和char 表示字符长度，而其他类型都是字节长度。char(10)表示字符长度是10，bingint(4)表示4字节，但是一位内bigint实际长度是8字节，bigint(4)的实际长度是8字节 。
字段长度一般设置为2的幂
优先考虑逻辑删除，而不是物理删除
物理删除恢复数据困难。会使自增主键不再连续。核心业务表只做状态变更不进行物理删除。
表必备字段
一张表的字段不宜过多，尽量不超过20个。
尽可能使用not null字段
防止空指针，null需要额外空间存储增加运算难度。可能会导致索引失效
设计表时，评估哪些字段需要加索引
数据少时不需要加。
有查询条件时一般需要建立索引，但不能滥用，
每张表不超过5个，区分度不高的字段不建，注意避免索引失效的情况，索引过多的话可以通过联合索引优化。
不需要严格遵守3NF，通过业务荣誉来减少连表查询
避免mysql保留字：eg:select interval desc
不搞外键关联，一般都在外码维护
性能、并发死锁、分库分表问题
选择存储引擎，一般innodb
选择字符集，utf8中英文混用场景，一个字符3个字节，emoji会用到utf8mb4
不建议使用存储过程、触发器
存储过程：已预编译为一个可执行过程的一个或多个sql语句
触发器：值一段代码，当触发某个事件时，自动执行这些代码。
没有完善的出错记录处理，不建议使用
大字段 可以保存到mongodb中
考虑是否需要分库分表
分库分表
为何要拆分？如何分？什么组件？实现原理？处理流程？
垂直拆分、水平拆分
拆分策略：
range：易扩容，不均匀，存在热点问题
hash：难扩容，均匀分布，不易出现热点问题
解决方案：group概念，先按照range分group,再按照hash分库分表
项目实战分库分表过程？数据迁移？分布式事务？分页查询？
分库分表引发的问题及解决方案：
1.分布式事务
2.跨库join关联查询
3.跨库排序，分页查询
4.全局id去重问题
5.数据迁移问题
高可用架构
单机->主从->主主->集群
架构演变与设计？
如何解决高可用问题？引入的问题如何解决？
负载均衡？
mysql设计优化
innodb索引下推
在执行查询语句时，MySQL优化器将WHERE条件中可下推到存储引擎层的部分下推到存储引擎进行过滤，只将满足条件的数据返回给MySQL服务器，从而减少MySQL服务器的工作量，提高查询效率。通常情况下，只有使用索引的查询才能利用索引下推功能。
异常处理（sql注入）
如何防止sql注入？
-
使用PreparedStatement。
-
使用参数占位符（?）。
-
使用set方法设置参数值。
mybatis二级缓存会引发的问题？默认开启一级缓存
1. 数据不一致性
2. 内存占用
3. 延迟问题
4. 不适用于复杂查询
线上服务异常处理？
重启后缓存载入？
排查过程？
标签：事务,查询,索引,mysql,id,数据,主键 From： https://www.cnblogs.com/jyzyz/p/18138634