mysql学习：三大范式，存储引擎和索引

三大范式是什么

第一范式，表中的所有字段都得是原子的不可再分的，比如不能有一个人的“特征”，必须细分为“爱好”，“性格”这样

第二范式，要求非主属性必须完全依赖于候选关键字，比如我现在有一个表：学号，课程号，姓名，学分

学号→姓名，学分→课程号，不符合第二范式，应该拆成三张表【学生：姓名，学号】，【课程：课程号，学分】【选课关系：学号，课程号】

第三范式，要求不存在依赖传递关系，比如我有一个表a，b，c

c依赖于a+b，这就导致a字段修改c必须也修改

三大范式目的是解决：数据冗余，数据不一致，数据更新和擦汗如异常

但是比如第二范式会导致多张分表，所以没必要完全遵守

exists和in的区别

exists一旦找到符合条件的第一行数据就立马返回true，in是子查询先找到所有结果然后逐一扫描

存储引擎

mysql内部查询语句是怎么执行的

先经过连接器，建立mysql链接，同时检验用户以及权限

缓存空间，之前有，用于缓存查找，但是因为数据库内容一旦有写和更新就会清空缓存空间，利用率不高，所以后面取消了

解析器对mysql语法解析生成语法树

优化器考虑索引的执行成本

优化器如何优化的？

是通过各种信息，比如表和索引的信息，以及预估的成本量进行数学计算

执行器执行语句

mysql的存储引擎有哪些

innodb，能实现事务，行锁，是现在的默认引擎

MyISAM之前的默认引擎，实现不了事务，被抛弃了

Memory完全基于内存的存储引擎

NULL是如何存储的

mysql存储每行数据的时候有一个专门的null值列表，每列对应一个二进制位，如果有null就为1

也是因此尽量避免null的出现，因为null会影响查找，也会占用空间

索引

1. b+树，前提是b树，b树是每到五个节点长度就向上分裂，b+树则是分裂的同时分裂值多一份+叶子节点双向链表
2. 为什么用b+树

首先b+树高度低，同时树本身平衡

叶子节点双向链表连接，支持范围查询和排序查询

缺点就是会产生大量随机IO，插入数据导致页分裂，而且物理上叶子节点不连续

3. 聚簇索引和非聚簇索引的区别

聚簇索引存的是主键+完整记录，也就是直接走聚簇索引就能找到记录

非聚簇索引是索引+主键，通过二级索引的B+树找到主键值后，还要回表才能找到记录

索引应用

1. 有那些索引

主键索引

唯一索引

前缀索引

联合索引

普通索引

2. 如果没设置主键会怎么样

没有就默认选择第一个不包含NULL的唯一列作为聚簇索引的索引值

如果上面那个也没有就自动生成一个隐式rowid作为索引键

3. 外键的劣势

外键约束，会导致每次更新都要看一下是否一致

每次修改外键同步的时候需要获取锁，高并发可能导致死锁

4. mysql新版本解决了哪些索引失效情况

函数索引，可以针对函数计算建立索引

索引跳跃扫描机制，解决的是最左匹配原则

本质就是把所有情况都列出来然后找

本质还是完成了最左匹配的查找

5. 为什么得是最左，最左在数据结构上有什么体现

最左是因为建立联合索引的时候，是按照先后顺序来排序的

比如联合索引a,b,c

先给a排序

对不同的a里对b排序

对不同的b里给c排序

也就是说全局来说a是有序的，但是b和c不一定有序

6. 索引下推是什么，什么情况发生

   假设有一个表 users，包含 id、name 和 city 三个字段，并且在 (city, name) 上创建了一个联合索引。现在执行以下查询：

   SQL

   SELECT * FROM users WHERE city = 'London' AND name LIKE '%son%';

   没有 ICP 的情况下：

   1. 存储引擎会根据索引 (city, name) 找到所有 city 为 ‘London’ 的索引记录。
   2. 存储引擎会将这些索引记录对应的完整数据行都返回给数据库服务器。
   3. 数据库服务器接收到所有 city 为 ‘London’ 的数据行后，再根据 name LIKE '%son%' 这个条件进行过滤。

   有 ICP 的情况下：

   1. 存储引擎会根据索引 (city, name) 找到所有 city 为 ‘London’ 的索引记录。
   2. 存储引擎会进一步检查这些索引记录中的 name 是否满足 LIKE '%son%' 这个条件（这个条件只涉及索引中的 name 列）。
   3. 只有同时满足 city = 'London' 和 name LIKE '%son%' 的索引记录，存储引擎才会回表查询其对应的完整数据行。
   4. 数据库服务器接收到的数据行就是最终过滤后的结果，无需再进行额外的过滤。