Mysql的数据结构

  • 数字型：整型（tinyint,smallint,int,bigint），浮点型（float,double），定点型（decimal）
  • 字符串：char，varchar，tinytext，text，mediumtext，longtext，tinyblob，blob，mediumblob，longblob
  • 日期：date，timestamp

定点型是指定小数点后几位的。char是定长的，varchar是可变的，一般设计的是最大长度。如果超过长度char是需要修改表结构的。而且相同字符串在存储层面两者是相同的。但是varchar内存占用稍高，某些高级操作是需要遍历。

Mysql查询流程

  1. 连接器：连接客户端的http请求，校验用户名密码
  2. 查询缓存，如果缓存里面有就直接返回（这里在8版本就废弃了，因为命中率实在太低了）
  3. 词法分析语法分析
  4. 执行器：包括预处理，例如将*换成表的字段；优化器，选择效率较高的执行计划执行，能用索引就不用全表扫描；执行器，执行sql然后返回结果给客户端

InnoDB和MyISAM

  1. 事务
  2. 行级锁
  3. 索引
  4. 支持外键
  5. 崩溃恢复redolog

InnoDB的存储形式

众所周知InnoDB存储数据的数据结构是B+树，其中树的节点就是数据页。

数据页内部：

一个数据页上会有多条记录，对应着实际的记录。在数据页内部是按照主键id顺序排列的。除此之外还有一个页目录，将顺序排列的某些记录归为一个槽。例如一个数据页有12345，可能12是一个槽，345是另一个，槽只保存最大的那个节点。当搜索的时候，比如我要搜索4，由于4大于12这个页的最大值2，所以会在345里面搜索。这个时候需要遍历的成本就低很多了。一般来说槽只会包含不超过8条记录。使用二分法找到这个槽，然后在在这个槽进行遍历。页目录只是为了加快在页内的查找速度。实际上每一个页里面记录也是通过链表串起来的。对外只会暴露当前页主键最大值和最小值。方便b+树的查找。

B+树：

b+树与b树的不同点在于，b+只有底层真正存储数据，b树在非叶子节点也会存储数据。而且b+底层是一个双向链表，可以方便进行范围查询。对于innoDB而言，为索引节点还是数据节点根据页头的某个字段决定。索引节点中存储着下一个表对应的最小主键。我们可以把查找一次当作一次io，由于索引节点能存储的数据是很多的，千万级别的数据最多也不过三四层，也就是说io次数会很小。b+树的缺点是会由于添加数据导致分裂，影响性能。

InnoDB默认得要一个索引，可以不显式的给出，但是背后隐藏列会默认出一个索引作为数据页的排序参照

为什么用B+树而不是B树，红黑树或者跳表

  • b树：由于非叶子节点也会存储数据，那么就没有b+树存储数据多。查询的效率也不稳定，可能在非底层就查询到数据了。而且不是双向链表，范围查询做不到
  • 红黑树：也是二叉树，io次数肯定比b+树多，跳表同理
  • 哈希表：不支持范围查询，这同样是redis为什么用跳表而不是哈希表的理由

索引

索引是为了加快搜索而建立的数据结构

  • 按照数据结构划分：B+树索引，Hash索引
  • 物理存储划分：聚簇索引（叶子节点是数据），非聚簇索引（叶子节点存的是主键id，后续还要回表去聚簇索引找）
  • 字段特性：主键索引，唯一索引（建立在唯一字段上的索引），普通索引，前缀索引（查电话号码）

唯一索引：create unique index index_name on table(index_column_1)

普通索引：create index index_name on table(index_column_1)

前缀索引：create index index_name on table(length(index_column_1))

  • 字段个数：单列索引，联合索引

联合索引：create index index_name on table(index_column_1,index_column_2)

联合索引范围查询

select * from t_table where a > 1 and b = 1

这里只会用到a一个索引，因为只有a能缩小范围，b是无序的，无法缩小范围。如果没有索引下推只能一个个回表通过b进行筛选

select * from t_table where a >= 1 and b = 1

这里会用到两个索引，因为a=1的时候b是有序的，b能够缩小范围

select * from t_table where a between 1 and 2 and b = 1

mysql中between是左闭右闭区间，所以两个都能用

select * from t_table where a like ‘m%’ and b = 1

mysql中between是左闭区间，所以b也能用

索引失效场景

什么时候无法使用索引？就是通过某些手段找不到原先的顺序了

  • 左或左右模糊匹配：例如%like和%like%，索引在这个字段上是根据最左匹配的，如果第一个字符都不知道，那后续的就是无序的了
  • 对索引使用函数：例如count()某个索引，这个时候也是走不了的，因为count不是一个索引
  • 对索引进行表达式计算：只要是在等号左边的也用不了，mysql没做这部分优化
  • 对索引隐式类型转换：在mysql中字符串会自动变成数字进行比较，索引当索引为字符串，而查询的时候用id=1来比较，会自动类型转换，相当于索引使用了隐式函数变成数字于查询条件进行比较
  • 联合索引非最左匹配：联合索引（a,b,c）如果查询条件只有b，c，就不会走索引。但是如果是ac还是可以走a的索引，如果有索引下推的话在二级索引可以通过判断c减少回表次数；如果没有就只能用a减少判断，最后回到server也就是索引是id的那个表中一个个判断c，这样会有很多回表
  • where子句中的or：如果只有一个条件是有索引，那就会失效，因为在or条件下只用满足一个就可以了

Mysql事务

首先是事务的ACID四个特性：

  • 原子性：事务内的东西是同时完成或者同时取消的。innodb的事务可以做到，redis的事务就没有原子性。
  • 一致性：事务完成前后，由一个一致的状态转移到另一个一致的状态。例如银行转账两个账户600+800，转账后总数是不能变的
  • 隔离性：并发导致的事务之间应该隔离，一个人购买商品的事务不应该影响另一个人的购买
  • 持久性：持久化下来，掉电也不会丢失

InnoDB的持久性是通过redolog完成的，原子性是undolog，隔离性是MVCC完成的，一致性是上面三者都完成后就会保证一致性。

事务隔离级别

  • 脏读：一个事务还没有提交，别的事务就能看到它的修改了。例如事务1修改了余额，事务2读取了这部分，但是事务1触发回滚，这就导致事务2读到了脏数据。
  • 不可重复读：在一个事务内读取两次数据，两次数据读取的不一样。这通常是由于另一个事务的提交，这个数据对于其他事务都是可见的（有点像volatile）
  • 幻读： 查询数量，事务内前后查询两次不一致，像发生了幻觉

事务隔离级别：

  • 读未提交：
  • 读提交：解决脏读
  • 可重复读：解决脏读+不可重复读
  • 串行化：解决幻读+不可重复读+幻读

MVCC（多版本并发控制）

在前面讲的记录中有两个隐藏字段：添加该条记录的事务id和指向上一个版本undolog指针。MVCC是依靠这两个字段完成的，更新数据时会在数据页中替换新的记录，然后将旧的记录作为指针保存下来，这时所有的版本都连成一条链，包含了当时进行更新的事务id。

MVCC提供了ReadView这个数据结构，由于事务id是顺序递增的，ReadView将事务id分为三个部分，已经提交的事务，正在进行的事务和没有发生的事务。已经提交的事务是有访问权的，正在运行的事务是不允许查看的，此时如果发生并发现象，查询数据的时候会根据这三个范围和当前全部的事务id进行过滤，这样就能避免读到还没有提交事务的数据。从而解决了脏读问题

MVCC是如何解决不可重复读的

主要是ReadView生成的时机问题，如果每一次快照读都生成一个ReadView，有可能在两次快照读期间有别的事务提交了，这两次的ReadView范围就会不一样，还是会发生不可重复读。但是如果在事务刚开始的时候就生成ReadView，就能保证执行下来的可见性，即使在第二次读之前，在readview中的那个事务提交了，还能保证接下来的读操作不可见这个数据。

MVCC解决了幻读吗

在默认隔离级别下的innoDB很大程度能够避免幻读，范围查询也需要看版本号。在某些情况下还是能出现幻读，例如当前没有s=5的数据，此时进行查询是没有的，另一个事务添加了这条数据，此时事务id在readview里面，是不允许访问的。但是原来事务又更新了这条数据，现在事务id是自己的了。下次查询的时候就会查询出来。（虽然更新一条不存在的数据逻辑上很诡异）

需要通过间隙锁才能解决幻读，也就是锁住一段范围内的，其他事务添加这段范围内的数据就会被阻塞。

redolog

前面所讲的数据库事务的ACID中，持久性就是由redolog保证的。mysql在更新的时候先写redolog，然后再在合适的实际将redolog真正写在数据页上。首先读取操作的时候不会每次都从磁盘io，会缓存在buffer pool中，那么写操作也会在buffer pool中操作，此时就变成了一个脏页。redolog的左右就是把这个脏页的操作记录并持久化下来，这个过程是比刷数据页要快的，很多时候查询的并不是顺序读，有可能在很多数据页上。

刷盘时机:

redolog也是有缓冲区的，把buffer pool中的修改保存在redolog缓存区中，然后再在合适的时机刷盘，一般有以下几个时机：

  • mysql正常关闭
  • 后台线程会每隔1s刷盘
  • 当缓冲区满了一半就触发
  • 根据刷盘策略

刷盘策略有以下三种：

  • 每次事务提交只写在缓冲区中，但是如果在1s内掉电，redolog buffer就会没有了，这种策略可能会掉最多1s的数据
  • 每次事务提交直接写磁盘，效率较低，但是安全
  • 每次事务提交给操作系统的page cache，交给操作系统写。这种情况如果mysql停机了还是能写，因为操作系统没有停止，但是如果掉点了还是会丢1s的数据，算是上面两种的一个折中。

日志文件组：

redolog不是一个文件，而是多个文件，这些文件按照环形存储，会覆盖。头指针是checkpoint，尾指针是write pos。每次有缓冲区来的数据就移动pos。如果mysql进行了redolog到数据块的持久化，checkpoint就往后移动。

如果这个环形文件满了，就需要阻塞mysql，进行部分redolog到数据页的操作。

总结：

redolog保证了mysql的持久性，这种操作比直接修改数据页要高效，由于直接修改数据页可能会存在随机写的情况，而redolog是追加写，就好像每次找东西随机写就是看目录找书页，而追加就是往后翻一页。

然而redolog在某些策略下也不是万无一失的，因为redolog也是有缓存的，如果没有及时写在磁盘里就会丢失1s的数据。

由此可见redolog和真正存在数据页中还有一段时间差，那这个时候来了一个读请求，怎么保证一致性？
会在buffer pool找，而不是直接去磁盘找，本来redolog就是为了存buffer pool的脏页的，是内存同步到外存，

binlog

binlog是解决主从同步的一种日志形式，只记录写操作，select和show是不记录的。有三种格式形式：

  • statement：只记录更新的语句，不管前后状态，一般存储的数据量较小
  • row：不光记录更新的具体语句，更新前后的状态也保存，一般用在需要计算的sql语句，例如now()，用第一种方式就会造成数据不一致，第二种会保存在主机上面的结果。当然存储的数据量会更大
  • mix：根据具体sql的形式选取策略，例如正常sql就用statement，涉及到计算的就用row

刷盘方式：

与redolog类似，也有一个缓冲区，但是binlog的缓冲区线程之间是独立的，最终都要写进磁盘。

  • 0：不同线程的binlog buffer写到page cache里面，由操作系统写回
  • 1：直接写到磁盘
  • n：n个线程的binlog buffer写到page cache后触发一次写到磁盘。掉电就丢失n个线程的数据

binlog就够了为什么还需要redolog

binlog主要负责主从同步，redolog负责恢复数据库。一个是外部机制，一个是内部机制，两者独立但是互补，服务于不同的需求。

两阶段提交

如果在binlog和redolog之间出现问题就会导致数据不一致。所以需要两阶段提交

写redo的时候设置为prepare，等到binlog写完了才设置为commit状态。mysql认为binlog写完了就是一个事务结束，即便没有再设置redolog的状态。

例如在写完redolog时宕机，binlog没有写，这个时候就会触发回滚；但是写完了binlog宕机了，redolog没有被改变为commit，还是会被认为事务提交了

存在什么问题：

  • 多线程下需要原子性
  • 如果是“双1配置”，一个事务io两次

Mysql的锁

  1. 数据库锁：只有在进行数据库备份或者迁移的时候才会用
  2. 表级锁：表级读锁写锁，读写意向锁，自增锁，元数据锁
X IX S IS
X 不兼容 不兼容 不兼容 不兼容
IX 不兼容 兼容 不兼容 兼容
S 不兼容 不兼容 兼容 兼容
IS 不兼容 兼容 兼容 兼容

解释一下：IX表示表内已经有行在进行X操作了，此时不允许全局上锁，所以全局X和S都不允许；全局X的条件是里面没有任何一个在进行读写操作，所以与所有都不兼容；IS和IX能兼容是因为都只是作为指示作用，IS和IX可能锁住的列不一样，他们的存在只能影响全局锁。
3. 行级锁：记录锁（锁住id=1这条记录），间隔锁（锁住（2，5）区间的），临键锁（锁住（2,5]）

Mysql什么时候上锁

执行select for update或者update的时候，在事务执行范围内会上一个对当前事务有效的锁，一般锁的单位是临键锁，在某些情况下会退化成记录锁或者间隔锁。这一部分的提出正是为了解决。

唯一索引等值查询

  1. select * from user where id = 1 for update;如果有id=1这条记录，临键锁就会退化为记录锁，只锁住当前这个id=1，因为这样就不会有别的事务来进行更改避免不可重复读
  2. select * from user where id = 1 for update；如果没有id=1这条记录，就会退化为间隔锁，找到最近的两个区间例如（0，3）锁住这段，不允许添加数据避免幻读

唯一索引范围查询

  1. select * from user where id > 15 for update;如果有id=20的记录，那就会加锁(15,20]，(20,INf]
  2. select * from user where id >= 15 for update;如果有id=15，加锁15，(15,20]，(20,INf]
  3. select * from user where id < 6 for update;如果有id=1,5,10的记录，加锁(-INF,1],(1,5],(5,10)
  4. select * from user where id <= 5 for update;(-INF,1],(1,5]

非唯一索引等值查找

只用记住一点，非唯一索引是按照大小排序的，索引值相同再按照id升序排序。{(15,14),(25,20)}，锁住非唯一索引（15，25），如果新增的值在其中就不允许插入，如果=15且id>14也是不允许的，右边同理。

Mysql死锁

由于间隙锁是可以重复的，两边都锁住了不允许添加。

慢sql

产生慢sql的原因

  1. 索引未生效或者根本没有索引，导致全表扫描
  2. 数据量太大了，加索引都不行。这个时候就要考虑分库分表了
  3. sql书写不当，过多的join操作或者子查询过多，子查询会产生一张新表；或者是深度分页；用了比较长的排序
  4. 数据量比较大的时候，redolog满了，在更新checkpoint的时候会阻塞
  5. 等待行级锁

如何发现慢sql

  1. mysql自己可以开启慢sql查询日志，将查询时间大于1s的记录下来
  2. 预防慢sql，做全局sql扫描。阿里的这篇文章提到了可以使用JVM的沙箱模型。相当于一个大的aop模型（值得一提的是gclib也是跟这个原理类似），通过在实例前后进行监听就可以无侵入式地录制HTTP/Java/Dubbo入参/返回值，业务系统无感知。基于这个能力，我们可以方便的采集和SQL执行相关的Java方法参数以及返回值。通过配置采集点，来采集执行sql的java代码的相关方法、参数和返回值，辅助实现sql采集功能。

如何识别高危sql

  • 不符合规范的sql：不允许使用外键，深度分页优化，子查找过多，有*
  • 用explain观察，row字段过多需要优化，type字段是否使用索引，extra是否有排序操作，以及是否索引下推，索引覆盖。

常见的sql优化手段

  1. 避免使用*：因为在查询的过程中还有一个预处理过程，可以自己写的就不要交给sql去查询，而且无法使用覆盖索引，也就是不用回表
  2. 深度分页优化：比如有1000000页，查询这个页的时候就是深度分页了。可以用子表查询这个页的第一条记录，然后再去查比这个大的10条记录作为范围。有两个缺点，首先是id必须自增，其次是子表查询不推荐
  3. 避免多join，避免使用外键
  4. 优化慢sql
  5. 正确使用索引：选择合适的字段创建索引（不为NULL的字段，频繁查找但是不是频繁增删的字段）；避免索引失效；考虑在字符串上建立前缀索引；避免冗余索引