MySQL学习记录

索引的本质

索引是帮助MySQL高效获取数据的排好序的数据结构；

比如现在一百个数，那么如何快速获取中间值，常见的可以用二叉树，来快速定位数据，减少查询的次数；

索引的选择

常见的数据结构有：

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 这个网页可以帮助理解各种树

• 二叉树

  • 如果次数有一万个数为（1,2,3....)，那么二叉树会有一万层，更像一个链表结构。这种结构很显然是不便于快速定位数据的。因为如果要查询10000那么需要10000次io才能检索到索引；

• 红黑树

  • 红黑树是一种平衡二叉树，较于二叉树会减少层次数量，但是随着数据量增加，红黑树的层级也会增加；

• Hash表

  • Hash表只能针对于单数据查询，如select * from user where id=888；这种查询会先hash(888)算出索引地址，然后拿到数据的地址。可以极快的查询到数据；
  • 但是如果是范围查询，那么hash便无法支持；

  以上，所以MySQL索引最好保证树的高度（1-4层），以此减少io的次数（1-4次），来达到快速查找的目的；

• B树

  

  • 比较矮胖，即缩小高度，将每层级的数据增多。
  • 每个节点都有data，这样查询的时候一次性获取一个层级的节点放到内存，在内存中可以快速查找；
  • 因为获取一个层级节点到内存是有大小限制的，即如果节点过大，那么获取的数量会减少；
  • innodb_page_size的默认值是16kb， 即一个层级的节点最好16kb.

MySQL最终选择的是B+树

• B+树（B树的变种）
  • 较于B树，B+树的非叶子节点不存储data。这样一次io可以获取更多的节点；
  • 较于B树，B+树非叶子节点会冗余索引，即每层会有重复的，这样做事为了查询更快；
  • 较于B树，B+树叶子节点中有指针连接，这样是为了范围查询的的时候可以直接往下查询，而不用从第一层索引开始重头开始；

假如一个节点是16KB，且主键是Bigint（占用内存8B) ，主键间存储的内存地址假设占用内存B，那么一层可以存储1170个节点；依次类推，这样的高度为3的B+树大概可以存储2000W数据；

存储引擎

MySQL存储引擎是形容表的；

MyISAM存储引擎

文件存储格式为三个：

• frm 表结构
• MYD 数据
• MYI 索引

采用B+树 树的子节点存储的是数据的地址，获取到数据地址后再去MYD文件查询具体数据；

这种查询方式的索引也叫做非聚集索引

INNODB存储引擎

文件存储格式为两个：

• frm 表结构
• IBD 数据+索引

采用B+树 树的子节点存储的就是数据，减少了回表。根据索引可以直接查询到数据；

这种查询方式的索引叫做聚集索引 也叫聚簇索引

为什么INNODB要求必须有主键且推荐采用自增主键，而MyISAM却不需要？

• 因为InnoDB要求数据有序，如果插入的是非自增主键，随机插入现有索引页中间，可能会造成数据移动，增加开销。
• MyISAM 插入数据就是，如果中间数据页有空位就直接插入，没有就在现有数据页后面插入，不涉及数据移动。

联合索引是怎么样的？

• 如上图，可以看到图是联合索引（col1,col2,col3)

• 因为不是主键索引，所以叶子节点不存储data, 那么在非聚集索引中，即使复合列索引，非叶节点也存储三个值

• 非叶子节点也是存储三列，不过是先按照第一字段排序，相同按第二字段排序，依次类推

  mysql(innodb)是索引组织表，叶子节点是存的表的主键

• 按照col1,col2,col3的顺序依次查找后可以找到主键id。再根据主键id进行索引查询；

• 如果where条件只有col2，那么很显然我们无法定位一个具体的索引区间，这种原则就是最左侧列匹配原则

理解以上联合索引后，我们可以很清晰的理解索引的匹配原则；

MySQL 索引匹配原则

在我们了解到MySQL底层数据结构B+树后，这些规则其实很好理解；

索引匹配原则如下：

• 等值匹配
• 最左侧列匹配
• 最左前缀匹配
• 范围查找
• 等值匹配+范围查找

假设现在有一张表student_score，表中有5个字段，id，class, name，course，score，其中id为主键，有四条数据

• id：1 class：1班 name：张三 course：数学 score 90
• id：2 class：1班 name：张三 course：语文 score 50
• id：3 class：3班 name：李四 course：数学 score 50
• id：4 class：3班 name：李四 course：语文 score 20

并且我们以class、name、course建立了联合索引；

等值匹配

假设现在有一条sql如下

select * from student_score where class = '1班' and name = '张三' and course = '数学'

这条语句中就会用到所有的索引字段，首先找class，然后查找name，最后查找course

最左侧列匹配

假设有一条sql如下

select * from student_score where course = '数学'

该SQL语句就不会用到索引，因为B+树中，索引的使用是从左到右的，不能跳过左边的，直接查找右边的，我们可以用explain来看一下

确定这里是没有用到索引的，这条SQL可以改成

select * from student_score where class = '1班' and name = '张三'

这样就可以用到索引了

因此在建立索引的时候，我们需要考虑表中的字段，到底哪些字段是最常被用于查找的

最左前缀匹配

这条原则适用于模糊匹配的时候，也就是需要用到like的时候，假设有一条SQL如下

select * from student_score where class like '1%'

这里是可以用到索引的，印在B+索引树中，数据都是按照字段来排序的，比如这里有联合索引 key(class、name、course) ，那么数据会按照class排序

但是如果你把SQL写成了下面这样，就用不到索引了，因为匹配不到最左前缀

select * from student_score where class like '%班'

范围查找

我们可以通过> 、 <这种范围比较来查找数据

select * from student_score where class > '1班' and class < '3班'

通过explain，可以看到，这条sql是用到了索引的
但是如果你把sql改成下面这样的，就不会用到索引了

select * from student_score where name > '张三'

因为该联合索引的B+树中只能根据class字段来进行范围查找，也就是联合索引中最左侧的字段

等值匹配+范围查找

假设有一条SQL如下

select * from student_score where class = '1班' and name > '张三' and course > '数学'

通过explain分析后，可以看到，这里也会用到索引

一些问题

is null, is not null会不会走索引？

• 我们还是以上面的表来举例，此时我们新增age字段（允许为Null），并将age设置为索引；

select * from student_score  where age is null;

通过explain分析，我们可以看到是走索引的。但是如果数据量足够大，其中只有一个Null值时又不走索引了；

• is not null同理，有情况会走索引，有情况会不走；

in 会不会走索引？

select * from student_score where id in (1)

很显然，我们通过执行计划可以看到是走索引的。
但是如果我们in的条件增加变为in(1,2,3,4,5,6,7,8,9)，我们又看到不走索引了；这又是为什么呢？

以上，我们其实仔细思考下索引的本质，他是通过B+树来更快速的达到结果查询的目的；那么如果走索引查询比全表扫描更慢的时候呢？此时MySQL的查询优化器会考虑查询成本，来自动选择走不走索引；

并发事务带来的问题

• 脏读：事务A查询数据后进行了一次修改且未提交，而事务B这个时候去查询，然后使用了这个数据，因为这个数据还没有被事务A 提交到数据库中，所以事务B的得到数据就是脏数据，对脏数据进行操作可能是不正确的。
• 不可重复读: 事务A访问了两次数据，但是这访问第二次之间事务B进行一次并进行了修改，导致事务A访问第二次的时候得到的数据与第一次不同，导致一个事务访问两次数据得到的数据不相同。因此叫做不可重复读。
• 幻读： 与不可重复读都点相似，只是这次是事务B在事务A访问第二次的之前做了一个新增，导致事务A第二次读取的时候发现了多的记录，这就是幻读。
• 丢失修改：事务A访问该数据，事务B也访问该数据，事务A修改了该数据，事务B也修改了该数据，这样导致事务A的修改被丢失，因此称为丢失修改；

Mysql隔离级别

• READ-UNCOMMITTED(读取未提交): 最低的隔离级别，允许读取尚未提交的数据变更，可能会导 致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读 或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的，除非数据是被本身事务 自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化): 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个 执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及 幻读。

MVCC

MVCC多版本并发控制(Multi-Version Concurrency Control)是MySQL中基于乐观锁理论实现隔离级别的方式，用于实现读已提交和可重复读取隔离级别。

• Inndb中通过在每列后面都追加两行（写版本号与删除版本号）来实现MVCC。
• 在RC级别下，每次Select都生成新的ReadView，所以能看到不同事物间的提交
• 在RR级别下，只在第一次Select的时候生成ReadView，所以会产生幻读，因为快照读和真实读的结果不一致
• 以下出自《高性能MySQL》中对MVCC的解释：
  

OCC

• 乐观并发控制（OCC）是一种用来解决写-写冲突（即丢失修改）的无锁并发控制，可基于CAS比对版本号实现；

2PL

• 最简单的并发控制算法是2PL（2 Phase Locking），分为两阶段：
  1）获得锁阶段；
  2）释放锁阶段。
  一般2PL被称为是悲观并发控制。

MySQL 锁

表锁 & 行锁

• 只有明确（由普通索引间接查询也可以）指定主键，才会执行行锁，否则执行表锁；
• 上面意思就是只有可以找到具体的列才会执行行锁；
• 若匹配不到数据则无锁；
  • 如数据表有id=1,2,3三条数据，select * form user where id=4，此时是没有锁的；
• 表中的字段类型为整型时，无论查询用字符串类型的数字还是int类型的数字均能走索引
• 表中字段类型为字符类型时，查询的值为整型时，无法走索引

锁算法

行锁算法

• Record Lock 普通行锁

  • 键值在条件范围内
  • 记录存在

• Gap Lock 间隙所

  • 锁定一个范围，不包括记录本身
  • 对于键值不存在条件范围内，叫做间隙（GAP），引擎就会对这个间隙加锁；
  • 比如： select * from user where id > 49 for update 那么遵循左开右闭原则，会增加(49, max]的间隙锁

• Next-key Lock 行&间隙

  • 锁定一个范围，包含记录本身

  • 在键值范围条件内，同时键值又不存在条件范围内；

  • 在可重复读隔离级别下才会生效；

表锁算法

• 意向锁（升级机制）

  • 当一个事务带着表锁去访问一个加了行锁的资源，那么此时，这个行锁会升级成意向锁，将表锁住；

  此时有两条数据，Id=10, name=jjj和Id=20, name=kkk

  # 事务A(行锁)
  select * from user where id = 10 for update;

  # 事务B（表锁）
  select * from user where name = 'kkk' for update;

  此时执行事务B是拿不到锁的，因为在事务B是带着表锁去的，此时事务A的锁会升级成表锁（意向锁）；

• 自增锁

  • 如果一个事务正在往表里插入自增记录，其他事务都必须等待；

锁的实现

共享锁 & 排它锁

是行锁与表锁的一个具体实现

• 共享锁：
  • 允许一个事务去读一行，阻止其他事务去获取该行的排它锁；
  • 一般理解： 能读，不能写
• 排它锁（X）： 写锁
  • 允许持有排它锁的事务读写事务，阻止其他事务获取该资源的共享锁与排它锁；
  • 不能获取任何锁，不代表不能读

某个事务获取数据的排它锁，其他事务不能获取该数据的任何锁，并不代表其他事务不能无锁读取该数据；

MySQL offset问题

MySQL查询时，limit 1000,10，实际是查询1000+10条然后舍弃前1000条，所以当Offset变大时查询效率会降低；