mysql总结

分页查询

1. 采用limit 子句 。limit x , y 表示 偏移 x行 后选择其后的y行数据

2. 当分页查询数据量特别大时，优化查询的方式

   select * from table order by id limit 10000 , 100 
   

   1. 采用子查询优化查询

      select * from table where id >= (select * from table order by id limit 10000 , 1)  limit 1002
      

   2. 存在id 自增的情况，使用id限定

      select name from table where (id >= 10000) limit 10
      

索引失效的场景

1. 索引列参与计算就不会走索引

   select name from tb where 'age' = 30 - 10;
   

2. 索引列使用函数

   select name from tb where concat(name , 'abc') = 'iuyabc';
   

3. 索引列使用like “%xxx”

4. 存在隐式转换（字符串列 与数字比较 等情况）

5. 避免or操作（只有or操作的所有字段都见了索引。才走索引）

6. 使用 ! 和 <>

如何判断数据查询是否使用索引

1. 查询语句前 加 explain ，查询type字段的值只要不是all 就是使用了索引

sql约束

1. not null 约束 ，控制字段的内容一定不为空
2. unique 字段唯一性约束
3. primary 可以 主键约束
4. foregin key 外键约束
5. check 约束

数据库三大范式

1. 第一范式
   1. 数据属性不可以再分
   2. 假如以商品作为属性，就不可以，商品本身还具有很多属性
2. 第二范式
   1. 每一个非主属性完全函数依赖于任何一个候选码
3. 第三范式
   1. 非主属性既不传递依赖于码，也不部分依赖于码。

where ， having 区别

1. where 在group by 和 聚合函数之前过滤
2. having 在 group by 和聚合之后过滤

where和 on的区别

1. 内连接中 on 与 where 效果相同
2. 外连接中 on 是生成临时表的条件 ， where是生成临时表后的进行过滤的条件

in 与 exist 的区别

1. in 先进行子查询 ， 然后将内表和外表做一个笛卡尔积，然后按照条件筛选
2. exist 指定期中一个子查询，循环遍历外表，查看是否有相同的存在

ACID 特性的实现

1. 原子性 ， 靠undo 日志

2. 持久性 ，redo日志 ， InnoDB提供了缓存Buffer Pool 作为数据页的缓存 ， buffer pool , 当mysql宕机时，buffer pool 中的数据没有来得及刷的磁盘，事务就无法实现持久性。 引入redo日志后， 先将修改写入redo日志， 然后再写入redo日志。宕机时，使用redo日志，对数据库进行恢复

3. 隔离性

   1. 写写隔离（通过锁机制保证隔离性）

   2. 读写隔离（MVCC 机制保证）

      1. 脏读（读到另一个事务未提交的数据）
      2. 不可重复读（一次事务中读到的数据俩次不一样， 第一次读到的数据没有被修改，第二次被另一个事务修改了）
      3. 幻读（俩次查询到的数据数量不一样 ， 另一个事务进行的删除或者增加操作）

   3. mvcc 机制的实现 ，

      1. 隐藏列（包含事务id , 指向undo 日志的指针）

      2. undo的版本链

      3. ReadView 储存此时活跃的事务

      4. low_limit_id , ReadView 时刻，应该分配的下一个事务id

      5. up_limit_id， 活跃的最小的id

      6. rw_trx_ids 活跃的事务id列表

      7. mvcc 机制的规则 ，

      8. 查询时生成ReadView

      9. 若trx_id < min_id ， 说明事务在ReadView前已经提交则可以访问最新版本

      10. 若 trx_id >= max_id 说明事务在ReadView ，还未提交，不能访问最新版本，只能按照undo日志版本链访问

      11. 若 min_id < trx_id < max_id ，此时判断trx_id 是否属于活跃日志，若在活跃日志中，还未提交，则不能访问， 若不在活跃日志中 ， 则可以访问

      12. 读已提交与课重复读隔离级别的区别是

      13. 可重复读只有在开始读的时候创建一个ReadView

      14. 读已提交是 每次select前都创建一个ReadView

B+ 树索引

1. 磁盘IO角度， 非叶子结点不储存信息，数据更紧密，更好的利用局部性原理
2. 叶子结点相连，对整棵树的遍历，只需要遍历一遍叶子结点即可（便于查找和搜索）
3. 数据都储存在叶子结点 时间复杂度固定为logn， B树时间复杂度不固定
4. B+树叶子结点相连，范围查找和区间访问性更好

聚簇索引与非聚簇索引

1. Innodb 聚簇索引将将数据和索引放到一起 ， 非聚簇索引叶子结点存放的是主键值，再通过回表来查询数据，聚簇索引文件本身就是数据文件
2. mylsam data域存放的都是地址

mysql 执行一条sql 的步骤

1. mysql 分为俩层 ，服务层和引擎层
2. 执行步骤
   1. 客户端请求
   2. 连接器（验证身份 ， 权限）
   3. 查询缓存（查询语句，缓存命中直接返回）
   4. 分析器（对sql进行语法分析和词法分析）
   5. 优化器（对sql语句进行优化）
   6. 执行器（查看用户是否有权限）
   7. 存储引擎返回结果

InnoDB 为什么用自增ID作为主键

1. 使用自增id ，每次插入新的记录就会添加到当前索引的后续位置，当一页写满，就会自动开辟一个新页
2. 若使用非递增主键，每次插入的值不规律，插入的位置随机，随机的位置造成分页，产生大量的碎片

Mysql 主从同步的实现

1. 主服务器将数据更改记录到二进制日志中
2. 从服务器将二进制日志复制到自己的中继日志
3. 从服务器将中继日志中的更改到自己的数据库中

数据库的锁

1. 按粒度分， 共享锁和排他锁
2. 按锁的策略 : 表锁， 行级锁