mysql知识梳理

使用版本5.7

一、mysql执行过程

1. 链接器 权限验证；
2. 缓存 查询热数据缓存；
3. 解析器 解析当前SQL有没有语法等问题；
4. 优化器 优化SQL，匹配索引，如果联合索引abc，where b and c and a,依然走索引；
5. 执行器 API调用存储引擎；

二、SQL执行顺序

• from 找到关联主表；
• join on 找到表合并成临时表lt1后通过on条件产能临时表lt2；
• where 筛选过滤产生临时表lt3；
• group by 分组生成临时表lt4,数据条数不变，顺序变了
• having 过滤产生临时表lt5；
• select 取出相关的字段临时表lt6；
• order by 排序后生成临时表lt7;
• limit生成临时表lt8;

三、查询怎么优化？为什么？

• 1、尽量使每个SQL都用到索引

  • 索引是什么 类似书本的前面目录，将量大的数据按照某一种算法变小放在一个更小的容器中，增加访问速度；
  • 索引有哪些
    • 主键索引 存储主键和全量的数据
    • 唯一索引 可能存在全量的数据也可能是该数据对应的主键ID
    • 普通索引 存储该字段和主键ID
    • 组合索引 存储字段和主键ID

• 2、尽量减少回表

  • 回表是需要耗费时间的，如果在当前索引下就能把要查的数据拿到效率就高点；

• 3、聚集和非聚集

  • 索引中全量和非全量数据

• 4、索引下推

  • 组合索引下的概念

• 5、索引失效

  • 比如 a like ‘%x’ , a字段有索引也失效； type 类型是 all
  • 比如 b or c, b/c 字段有索引也失效; type 类型是all
  • 比如 intE = ‘1’ 查询条件隐式转换,索引失效;
  • 比如 b+1 = 3 查询条件有计算的;
  • 比如组合索引的没满足最左匹配原则的；

• 6、性能问题

  • 减少in的使用(有序数组)，可通过between 或者 join代替；

• 7、尽量减少索引层高

  • 跟表字段多少，大小，数据量有关，综合值越高，层高越高

• 8、数据量多考虑分库分表

  • 代理 mycat
  • 非代理 sharding jdbc

四、集群方式有哪些

• 一主一从

• 一主多从

• 多主多从

• 实现过程？

  • ？？？

五、乐观锁和悲观锁

• 悲观锁 for update
• 乐观锁 业务代码实现类似cas

六、事务四大特性

• 原子性 不可分割的操作单元，要么都成功，要么都失败；
• 一致性 对数据可见性的约束，保证在一个事务中的多次操作的数据中间状态对其他事务不可见的；
• 隔离性 事务操作之间彼此透明互不影响；
• 持久性 事务一旦提交，结果就是永久的；

七、数据库隔离级别

• 读未提 可以读到其他事务未提交的数据；
  • 问题：如果最终事务回滚了，产生脏读问题；
  • 方案：事务隔离级别，读已提；
• 读已提 只能读到其他事务已提交的问题
  • 问题：在同一事务中读到的数据不一致，不可重复读；
  • 方案：事务隔离级别，可重复读；
• 可重复读 不修改数据情况下，同一个事务内多次读的数据一样；
  • 问题：范围幻读行问题
  • 方案：MVCC 多版本并发控制===》select 快照数据
• 串行化 每次读都需要获得表级共享锁，读写相互都会阻塞

八、日志 bin log 、redo log、undo log

• bin log
  • DDL 和 DML 语句（除了数据查询语句select、show等）
  • 提交了事务才会记录binlog。
  • binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关
  • 如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；
  • 如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中
  • bin log 日志格式类型
    • Statement
      • 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。
      • 缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行的时候相同的结果。另外mysql的复制，像一些特定函数的功能，slave与master要保持一致会有很多相关问题。
    • Row

      • 优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以row的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题.

      • 缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

    • Mixed
      • 在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。
• redo log
  • 事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态
• undo log
  • 数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上， 原子性 底层就是通过 undo log 实现的。undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。