MySQL基础知识

数据库范式

第一范式：数据库的每一列不能分割，最小单元，例如，不能将一个地址字段拆分成街道、城市、国家等多个字段

第二范式：所有非主键字段完全依赖于主键，不存在部分依赖，例如主键ab，字段c部分依赖于字段a，满足2NF应该是字段c依赖于主键ab

第三范式：所有非主键字段不能依赖于其他非主键字段。不存在传递依赖，例如主键a，字段b，c依赖于主键a，但存在c依赖b

第四范式：不存在多值依赖，例如主键a对应多个b，对应多个c，拆分多个一对一的表

事务的四大特性

原子性：事务包含的所有操作要么全部成功，要么全部失败回滚

一致性：要求事务执行前后数据库的状态保持一致。那转账来说，假设用户A和用户B两者的钱加起来是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱加起来应该还得是5000

隔离性：多个事务要互相隔离

持久性：事务提交，数据的修改会永久保存在数据库中

MySQL为什么采用b+树作为索引（树高度低磁盘IO少）

B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围查询

**B+Tree vs 二叉树：**对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

**B+Tree vs Hash：**Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因

1.  B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
2. B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
3. B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

MySQL死锁是什么

MySQL死锁指的是多个事务相互等待对方所持有的资源

解决方案

设计事务的执行顺序，假设有两个事务（事务A和事务B）和两个资源（资源1和资源2）。

事务A首先锁定了资源1。同时，事务B锁定了资源2。

接着，事务A尝试锁定资源2，但由于资源2已被事务B锁定，因此事务A必须等待。

同时，事务B尝试锁定资源1，但由于资源1已被事务A锁定，因此事务B也必须等待。

减少资源的锁定时间

使用合适的索引可以减少锁定的范围，从而减少死锁的概率。

调整事务隔离级别:如将隔离级别调整为读已提交，可以减少死锁的发生。

SQL语句的执行顺序

from join no where group by having select distinct order by limit

聚合函数：count max min avg sum

日期：now，curdate（返回当前日期）

数值函数：abs，power（x的y次方）

字符串函数：concat（连接），length，substring，replace

三大日志

undolog：是Innodb存储引擎生成的日志。用于事务的回滚和MVCC，保证了事务的原子性。事务开始前，MySQL会把更新前的数据都记录到undolog里面，

redolog：是Innodb存储引擎生成的日志。用于崩溃后修复数据，保证了事务的持久性。物理日志，事务完成后，记录更新之后的数据

binlog：记录每一条修改数据的 SQL，逻辑日志，用于备份恢复、主从复制，MySQL 的 Server 层实现的日志，所有存储引擎都可以使用

MySQL两阶段提交原理

保证了binlog和redolog数据一致性

准备阶段：将事务操作记录在redolog中，状态标记为prepare

提交阶段：将事务操作记录在binlog中，redolog状态改为commit

两阶段提交（分布式系统事务一致性）

准备阶段：协调者发送准备请求，参与者（引用了超时机制）执行事务但不提交，协调者等待参与者的响应

提交阶段：协调者根据响应决定是否提交事务，都成功，协调者发送提交请求，参与者执行提交操作；失败，协调者发送回滚请求，参与者执行回滚操作

三阶段（避免协调者故障导致的阻塞问题）

比两阶段引入了：预准备阶段和协调者超时机制

预准备阶段：协调者先咨询参与者是否能参与事务处理

准备阶段：协调者发送准备请求，参与者执行事务但不提交 ，协调者等待响应(都引用了超时机制)

提交阶段：协调者发送提交请求，参与者执行提交操作，协调者等待结果

如何定位慢查询

开启慢查询日志记录慢SQL，接着执行explain(12个属性)，通过key查看是否命中索引，通过extra判断是否回表。

开启慢查询日志：找到MySQL 安装目录下的 my.ini 文件，添加配置

保存并重启mysql服务

聚簇和非聚簇索引，回表，覆盖索引，索引跳跃，索引下推，最左匹配

索引分类：主键索引，普通索引，唯一索引，复合索引

主键索引为聚簇索引，b+树，叶子节点保存了整行数据以及对应的主键值且通过指针形成链表，范围查询，非叶子节点保存主键值和指向子节点的指针

其他索引为非聚簇索引，b+树，叶子节点保存索引列的值和主键值，非叶子节点保存索引列的值和指向子节点的指针

回表指的是先从非聚簇索引中找到对应的主键值，然后根据这个主键值从聚簇索引中找到对应的整行数据

覆盖索引指的是查询的列包含在索引中，而不需要再次访问数据行，避免回表

索引跳跃：跳过不必要的索引行来避免不必要的磁盘I/O操作，有name 和 age列的联合索引，只查询 age时，它就会跳过name索引，直接走age索引，不必去匹配最左原则。

索引下推：联合索引( name,age)，现在我们要查询姓名以张开头并且年龄等于10岁的记录。

没有开启索引下推：先查询所有姓张的数据行得到主键索引id，然后根据id去聚簇索引中找到匹配的数据行，接着在mysql的服务层使用age =10这个条件进行过滤。

开启了索引下推：会将过滤的场景下推到存储引擎层上，查询过程是先根据name,age这个索引找到匹配的数据行，然后再根据id进行回表查询，减少了回表的次数。

最左匹配：1.联合索引 (a,b,c)，查询条件使用a/ab/abc都会走索引，而ac/bc不会走索引。

2.当使用了范围查询，范围查询后面的字段不会用到索引，假如查询条件为where a = 1 and b > 2 and c = 3，那么字段a和b会用到索引，c不会用到索引。

3．使用了模糊查询，在最左面加上%后，也会违反最左匹配原则，因为无法得知查找的具体范围。

查询时又是怎么走索引的，索引加在表的列（字段）上

1.解析查询：MySQL首先解析查询语句，确定需要哪些表和列。

2.选择索引：MySQL会检查查询条件，并尝试找到能够最有效满足这些条件的索引。如果查询条件直接匹配某个索引（如WHERE子句中的列是索引列），MySQL可能会选择该索引。

3.执行索引查找：

全索引扫描：如果查询条件不能有效减少索引中需要检查的行数（如WHERE子句中的条件不严格），MySQL可能会进行全索引扫描。

索引范围扫描：如果查询条件能够限制索引中需要检查的行的范围（如使用BETWEEN、<、>等操作符），MySQL会进行索引范围扫描。

索引查找：对于等值查询（如=），MySQL可以直接在索引中查找特定的值。

4.回表查询：如果查询的列不完全包含在索引中（即查询的列不是索引的覆盖列），MySQL需要从索引中找到对应的行指针，然后回表查询实际的数据行。

5.合并结果：MySQL将索引查找或全表扫描的结果合并，以生成最终的查询结果。

InnoDB和MyISAM的区别

Memory引擎将数据存储在内存中，适用于对性能要求较高的读操作，但是在服务器重启或崩溃时数据会丢失。它不支持事务、行级锁和外键约束

脏读，不可重复读，幻读

脏读：读未提交的数据

不可重复读：一个事务读时被另一个事务修改了数据

幻读：一个事务范围查询时被另一个事务添加了数据

读未提交，读已提交，可重复读，串行化，mvcc(多版本并发控制)

Readview（读视图）：事务id列表，最小事务id，最大事务id（当前最大事务id+1），创建事务id

读已提交每次查询都生成一个readview

可重复读一次事务内的多次查询共用一个readview

Undolog链行数据三个隐藏字段：事务id，回滚指针，行id（隐藏主键id）

MVCC的优缺点

优点：1.提高并发性能，允许事务在不加锁的情况下读取数据

           2.数据一致性，MVCC通过保存数据的多个版本来实现可重复读

缺点：1.幻读问题，加临键锁（记录锁和间隙锁的组合）

           2.写写冲突，占存储空间

快照读和当前读

快照读：读快照数据，不存在幻读，不加锁的select就是快照

当前读：读最新数据，并且当前读返回的记录都会上锁，存在幻读，update、insert、delete、select for update（排他锁）、select lock in share mode（共享锁） 都是当前读

间隙锁解决幻读：对某一范围加锁

建索引

查询频繁的字段，外键，排序字段，统计字段，分组字段

SQL优化

1 避免使用select *

2 用union all代替union（它的作用是将多个查询的结果集合并为一个结果集，并去除重复的行）

使用union all关键字，可以获取所有数据，包含重复的数据。

排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。

3 小表驱动大表，减少表连接创建的次数

4 插入时批量操作，

5 用连接查询代替子查询，MySQL执行子查询时，需要创建临时表，查询完成后再删除临时表，有一些额外开销。

6 join的表不宜过多，join表不宜超过3个，如果join太多，MySQL在选择索引时会非常复杂，很容易选错索引。

7 控制索引的数量，阿里巴巴开发者手册中规定，单表索引数量尽量控制在5个以内，且单个索引字段数量控制在5个以内。

MySQL锁（加在索引上，next-key lock 临键锁：行锁和间隙锁组合，会退化）

粒度：页锁、表锁、行锁

select ... lock in share mode（读锁）

select ... for update（写锁）加行锁，默认查询不加锁

update和delete操作都会默认加写锁

思想：乐观锁、悲观锁

行锁类别：共享锁、排它锁（读写锁是行锁）

行锁算法：记录锁、间隙锁、临键锁（记录锁+间隙锁）、插入意向锁

意向表锁（不锁数据，只是表示意向）：意向共享锁、意向排它锁

临键锁：锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录

插入意向锁是特殊的间隙锁：在insert操作时产生。假设存在两条值分别为4和7的记录，两个不同的事务分别试图插入值为5和6的两条记录，每个事务在获取插入行上独占的(排他)锁前，都会获取(4，7)之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突(阻塞等待)。

1. 1. MySQL主从复制原理

主库将修改记录到二进制日志(binlog)

从库将主库的binlog拷贝到它的中继日志

从库重做中继日志中的事件，将改变应用到自己的数据库中

1. 1. 如果数据表中查询的数据访问是1万到1万零10，如何优化sql只查询10条数据（子查询）

SELECT *

FROM your_table

WHERE id > (SELECT id FROM your_table ORDER BY id LIMIT 10000, 1)

ORDER BY id

LIMIT 10;