MySQL之原理

一、索引和存储引擎(MyISAM和InnoDB)以及B+树的结构

1.1 MyISAM

- 是MySQL5.5版本之前默认的存储引擎、速度比Innode更快。只有非聚簇索引

• B+树：MyISAM
  

1.2 InnoDB

- 是MySQL5.5版本及之后默认的存储引擎，存储数据更加安全，支持自适应哈希(自己决定是hash还是B+)
- 一定有聚簇索引，但是其他索引都是非聚簇索引

• B+树：InnoDB

- 因为B+树是有序的，修改和插入数据效率会更高，所以自增索引比较好。

• hash树：InnoDB
  

1.3 联合索引

1.4 索引的一些特点

• 最左前缀法则：查询从索引的最左前列开始并且不跳过索引中的列
• 不在索引上做任何操作(计算、函数、改变数据类型)，会导致索引失败而扫描全表。

二、事务

• 事务：一组操作要么全部成功，要么全部失败，目的是为了保证数据最终的一致性。mysql默认情况下，自动提交事务

• 特性：

  • 原子性(Atomicity)：当前事务的操作要么同时成功，要么同时失败，是由我们的undo log日志保证。
  • 一致性(Consistency)：使用事物的最终目的，由业务代码正确逻辑保证。
  • 隔离性(Isolation)：在事物并发执行时，他们内部的操作不能互相干扰。
  • 持久性(Durability)：一个事务一旦被提交，在数据库中的改变就是永久的，提交后就不能再回滚。由redo 日志记录的。

• 隔离性/隔离级别: innodb有四种隔离级别，级别越高事务隔离性越好，但性能越低，而隔离机制是由mysql的各种锁以及MVCC机制来实现

  • read uncommit(读未提交)：有脏读问题 ： 未提交的更改数据，读的时候会读到。就是脏读问题
  • read commit(读已提交)：有不可重复读问题 ：只能读取已经提交的数据， 但是一个事务中多次读取同一个数据可能不同，就是不可重复读问题。（高并发的用rc）
  • repeatable read(可重复读)：有脏写问题： 读取提交数据，在一个事务中，多次读取同一条数据的时候，都是和第一次读取是一样的，即使数据改变在这个事务中还是不变的。就是脏写问题。原理：MVCC，有一个绑定关系，还有事务的号。见下面**（传统公司经常出报表的）**
  • serializable(串行)：解决上面全部问题。 原理：读的时候加上一把锁。
    – 串行原理详解：读的时候加上一把读锁。
    

• MVCC（类似于copy on write）
  

• 脏写如何规避缺点

  • 用乐观锁：乐观锁就是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是 不采用数据库自身的锁机制，而是通过 程序来实现 。在程序上，我们可以采用 版本号机制 或者 CAS(时间戳) 机制 实现。 乐观锁适用于多读的应用类型， 这样可以提高吞吐量 。

三、MySQL执行过程与BufferPool缓存机制

• WAL机制
  

四、主从复制

• 为什么要主从同步？
  • 在复杂的业务系统中，如果有一条sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让从库负责读，就可以保证业务的正常运行了。
  • 做数据的热备（主备：当业务激增的时候，可以正常访问）
  • 架构的扩展，业务量越大，I/O访问频率越高，单机无法满足，此时做多库的存储，降低磁盘I/O的频率，提高单个机器的I/O性能。（当数据库超过千万级别的时候就要做到分库分表）

• 步骤描述：
  • master服务器将数据改变记录到二进制binlog里面，当master上的数据发生改变的时候，则将其改变写入二进制的日志中
  • slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变则开始一个I/OThread请求master二进制事件。
  • 同时主节点为每个I/O线程启动一个dump线程，发送二进制事件并保存到本地的relay日志中，从节点将启动SQL线程读取二进制文件，在本地重放，使得其数据与主节点保持一致
  • 最后I/OThread和SQLThread将进入休眠状态，等待下一次被唤醒。

五、慢查询

• 怎么处理mysql的慢查询？
  • 开启慢查询日志，准确定位到哪个sql语句出现了问题
  • 分析sql语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
  • 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽能的命中索引
  • 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表

六、锁

锁的理解

七、补充概念

• 索引的作用： 提高查询效率，索引和实际的数据都是存储在磁盘上的，只不过在进行数据读取的时候会优先把索引加载到内存中。
• 回表： 非聚簇索引跳转到聚簇索引的过程称之为回表。先根据b+树比配到对应叶子节点的id， 然后id再去整个b+树去检索的过程。
• 索引覆盖： 当非聚簇索引的的叶子节点中包含了查询需要的字段的时候，不需要回表，这个过程称为索引覆盖
• 索引下推：有了索引下推之后，执行的过程是根据name、age整体的从存储引擎中做数据检索，返回对应的记录，不在server层做任何操作。 将检索数据从server层推到了存储引擎层叫索引下推。
• 索引失效的几种情况： 索引失效