MySQL学习笔记

概述

索引的数据结构

1. 二叉树
2. 红黑树
3. Hash表
4. B-Tree

• 每个索引都可以选择不同的数据结构

使用红黑树保存索引数据，仍然会因为树的高度增加带来的查询效率的衰减，解决方案是每个节点多放几个索引数据，即B树，为了更高的查询效率，在B树的基础上衍生出B+树

B和B+树的区别

1. 非叶子节点不保存数据，只保存树索引
2. 叶子节点使用指针链接

B+树每个节点默认是16kb，每个索引14字节，所以一个节点可以存1170个索引元素

• myISAM 和 InnoDB是形容表的，不是形容库的，所以每个表都能有不同的存储引擎

• MyISAM中B+树索引的叶子节点的data元素保存的是数据库某一行的索引（在文件系统中索引和数据分两个文件存储），而InnoDB叶子节点中的data保存的就是某一行的数据（索引和文件保存在同一个文件中，即聚集索引）

• InnoDB表必须要有主键，并且推荐使用整型自增 每个节点中保存了很多的索引数据，如果没有使用UUID作为主键则需要先转换成为acsii进行比较。并且整型更节约空间 使用自增的原因是因为查找可能是范围查找，就可以直接遍历叶子节点 方便插入和删除（树的插入和删除复习）

• 联合索引 按照联合索引顺序将多个索引统一保存在B+树的每一个节点中，查找时，如果节点中的第一个元素相同，则比较下一个，以此类推

• 每次查询的时候，文件系统最少会向内存中加载一页的数据（即一个节点16kb，类比操作系统的局部性原理）

• 表中的每一行都会有一种行格式，比如COMPACT、Dynamic、Compress等。每一种行格式所记录的每一行的数据格式不尽相同，例如COMPACT格式记录的一行数据为： 变长字段长度列表+NULL标志位+记录头信息+列1数据+列2数据。。。

  • 变长字段长度列表： 按可变长字段顺序保存该字段长度，例如 name varchar(20) 是该列第3个元素，则在该变长字段长度列表的第三个位置保存实际存储的name的实际长度 注：innoDB每一行元素占用总内存不得超过65535字节，blobs类型字段除外。但是一页只有16k的数据，故超过一页大小的数据就需要分页，不同的数据库引擎分页方式不同，例如InnoDB中，分页数据的第一页不存储数据，只存储下一页的地址，方便索引
  • NULL标志位 表中的某些字段允许为空，则该字段中按照允许为空的字段顺序存储01，其中0代表该值为空，1代表不为空，例如00101

存储结构/索引

InnoDB

• InnoDB查询出来的数据会自动根据主键进行排序，原因是其主键默认也是索引。MySIAM查询出来的顺序是插入时的顺序

InnoDB数据存储格式

表中的数据以数据页为单位在磁盘中进行存储，每一页有16KB，每一页数据都会携带除用户数据以外的数据，例如下一页的数据，和该页的分组数据。例如：如果表中每一行元素只需要存很少数据，则一页数据就可以存很多行数据，所有的行数据形成一个链表，如果一页数据量过大，则查询效率会减小。 此时就会将这一页的数据分成n个组，例如(1-4)(5-9)…，每个组保存最小数据的指针，保存在页的头信息中，即分组数据。 这样的话，如果查询某一个数据则会先去分组数据里面找，然后直接在命中的分组中进行查找。

• 目录页 一张表中的数据会存储在多个页中，那么查找数据时如何知道在第几页？ 在这些页之上又抽象出一种目录页的结果（即B+树中的非叶子节点），目录页中存储了每一页的最小索引的位置

• 如果建表时既没有定义主键，也没有定义唯一列，则InnoDB会自动创建一个名为 rowID的自增字段

• MySql在创建表时会自动创建一个空页，当向表中存数据时就会向该页存，当该页存满时，会复制当前页到新的一页，然后再创建一个新页存储新的数据，并将第一页数据清空，升级为这两个新页的目录页，这样做的好处是保持查找开始内存位置不变，将第一页常驻内存，加快查找速度

• 创建多个字段联合索引 会将这些字段拼接起来进行排序（建立索引的本质就是排序），并在该索引的B+树的叶子节点只保存数据行的主键（即每创建一个索引都会创建一个B+树，但是除主键索引外，其他的都不保存行数据），然后根据该主键再去主键索引查找行数据 如果建立的索引列的数据都相似或者相同，则创建出的索引意义不大，Mysql会自动在这种索引中加上主键索引，保证索引唯一

可以使用 explain SQL 查看是否使用索引

注：

1. mysql中的utf8格式其实只是真正的utf8的子集，它认为你不会存一些非常少见的字符，真正的utf8应该是utf8mb4
2. 定义字段时也可以指定排序规则，比如对于字符串的比较，可以将其转换为ascii比较，也可以转换为二进制比较，使用以下方式设置 例如，以_bin结尾的就是转换为二进制比较

事务

使用begin/commit/rollback开启/提交/回滚事务，可以开启或关闭Mysql的自动提交（默认开启使用begin/commit/rollback开启/提交/回滚事务，可以开启或关闭Mysql的自动提交（默开）

• 隐式提交 使用ddl时会自动隐式提交数据

• 保存点 创建事务后，提交前，可以创建多个保存点。当需要回滚时可以指定回滚到某个保存点

  四种事务隔离

  1. 读未提交（Read Uncommitted） 事务即使没有提交，其他地方也能读到该未提交的数据 会出现脏读

2. 读已提交（Read Committed） 必须提交之后其他地方才能读到数据，但是其他地方的事务中，即使没有提交，但是表发生了变动，同一个事务中也会查出不同的结构 会出现幻读和不可重复读（两者本质相同）

3. 可重复读（Repeatable Read） 默认方式，同一个事务中，即时还没提交，表发生了变化，查出来的数据也是一样的

4. 串行化 多个事务同时读没影响，但是如果有读写的话，读就会排队等待写完成。

实现

• 读已提交 假如两个事务A、B，及一条记录r=1，此时B创建事务修改r=2但还未提交，与此同时，A读取事务。依照隔离级别，此时A读取的数据应该是r=1，但是此时如果B提交了数据，则A在其事务内再次读取的值为r=2 每个事务都会有一个事务ID 每一行数据都会有一些除数据本身之外的其他数据，如之前说的rowID，当然也包括事务相关的transaction_id（6个字节的事务ID）及roll_pointer（7个字节的回滚指针）。transaction_id保存着最近修改该行的事务ID。

  版本链

  表中的每一行数据都有一个版本链，每次创建事务（还没有提交）修改数据时都会在版本链头部添加一个新的版本，并将其transaction_id记录为新的事务ID，将roll_pointer设置为上个版本的地址 多版本回滚机制，关键字 undolog

  ReadView

  Select语句默认会隐式地自动查询一个ReadView属性，该属性中又存在一个 m_id 的属性，m_id是一个列表，保存了当前行中所有未提交的事务ID，当查询某行数据时，从版本链中检索，如果节点的transaction_id存在于m_id中，则依照roll_pointer向下寻找 因此，当事务B提交了修改，事务A再次读取r，此时Select出来的ReadView中m_id 已经不包含事务B的事务ID了，故查出来的是新提交的数据，造成了不可重复读

  多版本并发控制（MVCC）

  使用了 版本链+ReadView 机制即实现了MVCC

• 可重复读 同 读已提交 ，但是在事务A中再次查询的时候，其ReadView中m_id保持不变

锁

读写锁

锁分为 读锁（共享锁、S锁）和 写锁（排他锁、X锁） 一个资源可以加多个读锁，只能由一个写锁。 但是Select语句会忽略锁，且不会加任何锁（区别于 select … for update） 注：

• select ... for update会将查找到的数据加上一个X锁

• delete 语句会在删除记录前对其加X锁

• insert插入一条数据时，会先加隐式锁（可理解为X锁）来保护这条新插入的数据在提交前不被其他事务访问到

  隐式锁：一个事务插入一条记录时，会向本版链中插入一个新节点，如果还没有提交，则该记录会保存本次事务id，而其他事务如果想对该记录加锁而发现事务ID不对应时，则才会产生一个X锁

• update如果修改前后没有导致存储空间的变化，则先会给记录加X锁再进行修改，如果由存储空间变化，则会先给记录加X锁，然后删除该记录，再insert一条新记录

行锁

分为：

1. LOCK_REC_NOT_GAP: 单行记录上的锁
2. LOCK_GAP: 间隙锁，锁定一个范围（修改的所有行，包括行的间隙，即不允许在锁住的两行记录之中插入数据），但不包括记录本身。GAP锁的目的时为了防止同一事务的两次读操作出现幻读
3. LOCK_ORDINARY: 锁定一个范围，包含本身。对于行的查询，都是采用该方法，主要目的时解决幻读

• 读已提交：不管什么情况下，只会对查询出来的数据行使用行锁
• 可重复读：对行的修改操作会使得其在要修改记录行内插入间隙锁，锁住这些行和其间隙，用于解决幻读现象，此时，如果其他事务想在这个范围中修改数据，则会发生阻塞。 为什么要这样：假如在事务A中执行语句 update where age=12,假设会修改3条数据，并且age是一个索引。如果在没加间隙锁的情况下另一个事务提交insert一条新的age=12的记录，由于age是索引，则该记录会插入在事务A查询的结果之中，则此时事务A会修改4条数据，出现了幻读

表锁

分为读写锁 如果当前表已经有行写锁了，则无法加表锁 因为加表锁需要遍历所有的行，查看是否存在行锁，开销极大，效率极低，所以在向表中加行锁时，会同时在表级别加意向锁（IS锁/IX锁），则如果需要向表加锁时，会出现锁冲突

mysql优化

• SQL执行慢可能的原因：

  1. 查询语句写的不好
  2. 索引失效
  3. 关联查询太对join
  4. MySQL服务器调优及各个参数设置不合理

• MySQL常见瓶颈

  1. CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
  2. IO，磁盘IO瓶颈发生在装入数据远大于内存容量的时候
  3. 服务器硬件限制

Explain关键字

sql提交给MySQL之后，其会通过执行优化器进行优化，优化的过程可以使用explain进行查看

explain select * from user;

注：上图为两张表关联查询的情况，可以发现执行计划中有两个执行计划，分别代表两张表的执行过程，对执行计划的解释如下：

id

1. 如果id相同，则表示sql执行时是从上往下执行的
2. 如果id不同，则id值越大，越先执行

select_type

主要用于区别普通查询、联合查询、子查询等复杂查询 可取值： SIMPLE：简单的select查询，查询中不包含子查询或UNION PRIMARY：查询中若包含复杂的子部分，则最外层查询被标记为主查询（见上图） SUMQUERY：在SELECT或WHERER列表中包含了子查询（见上图） DERIVED：相当于一张查询出来的临时表（类似视图） UNION：若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED UNION_RESULT：两个UNION查询之后出现的结果集被标记为该类型

type

查询方式类型 最好到最差依次为：system > const > eq_ref > ref > range > index > ALL

1. system：表中只有一行记录，平时几乎用不到
2. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。MySQL会将其优化为根据常量查询
3. eq_ref：唯一性索引扫描，对于每个索引只有一条与之相匹配的数据。常见于主键或唯一索引
4. ref：非唯一索引扫描，返回匹配某个单独值的所有行，可能找到多个符合条件的行
5. range：只检索给定范围的行，使用一个索引来选择行，比如使用了 between/and in，一般需优化到该层面
6. index：全索引扫描，会遍历所有的索引树
7. all：全表扫描，百万级别的一般不建议用

possible_key/key

possible_key：可能用到的索引，但实际不一定用得到，比如给主键另建了一个索引后根据主键查询（主键本身已经是索引） key：实际用到的索引，如果为NULL，则没有使用索引。如果出现了覆盖索引（select的字段和建立的索引正好匹配），则只会出现在key中而不会出现在possible_key中。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好 key_len的值为索引最大可能长度，并非实际使用长度

ref

显示索引的哪一列被用到了

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要的行数

extra

除上述信息外其他非常重要的信息

1. Using filesort：当使用sortby时，且被排序的列没有建立索引，则会导致其使用文件系统先进行排序，再查询，非常影响性能
2. Using temporary：说明内部产生了临时表，常见用group by。极大影响性能。一般情况下，使用group by要根据其分组所用列建索引
3. Using index：使用了覆盖索引，可以提高效率
4. Using Where：使用了where 。。。（重要的就是前三个）

索引优化

索引的主要作用是 排序和查找

索引分析

1. 单表查询 例如：某表有a、b、c、d 4列，a 为主键，根据条件 where b=1 and c > 1 order by d limit 1 进行查询 情况1：没有索引 解释：由于没有任何索引，导致使用where时使用了全表扫描，并且，由于使用了sortby，导致了Using filesort

情况2：创建了（b,c,d）联合索引 解释：由于查询条件中存在索引列，所以搜索方式由全表扫描变成了范围查找。但是，由于orderby后于where c>1 执行，故没有用到索引，进而使用了Using filesort 注：联合索引的使用顺序是：先根据b排序，如果b相同，则根据c排序，如果c相同，则根据d排序。 上述查询中，由于使用了c>1的范围条件，导致其无法继续根据d进行排序，该索引失效（见下：索引失效第4条）

情况3：删除原索引，创建（b,d）联合索引 解释：去除了索引中的c，所以其执行顺序为：根据索引找到b=1的行，并筛选出当中的c>1的行，然后再根据d进行排序。故不存在Using filesort

2. 两表查询 例子：A left join B on A.key=B.key 结论：应该在B表的key上建立索引 分析：因为左连接的特性是左表全都有，然后根据连接条件去右表中查询。故左表可以使用全表扫描，而右表则需要使用索引加快查询速度。右连接同理。

索引失效（应避免）

基本解决方法及注意要点： 解释：

• 最左前缀法则：如果建立了多列的联合索引（a，b，c），查询时，必须有a作为条件才会使用该索引，即必须包含首个索引列，例如a=1 and b=2 或者 a=1 and c=2 均能使用索引，但是类似 b=1 and c=2 就不会使用索引。但是 a=1 and c=2 只会使用a作为索引，跳过索引列只会使用跳过之前的列作为索引，即不能跳过索引的中间列
• 对联合索引中间列使用函数会使得索引失效，例如对联合索引（a，b，c）使用 where max(b) = 1，则b后面的索引c失效
• 对联合索引的某列做范围查询，其后续索引列失效
• 如果查询中，只select索引字段，则extra信息中会多出一个 Using index 字段，如果是select了索引以外的列，则没有该属性。即最好只select索引的列（覆盖索引）
• 如何解决不能使用 like ‘%abc%’ 的问题？ 解：使用覆盖索引，例如表中有一个 name 字段，则可以创建一个name索引，或以name开头的联合索引，select 只查询name或联合索引中的值，则会使用该索引
• 字符串查询需要加引号：例如表中数据name=‘2000’，使用查询 where name=2000也可以查到，mysql底层会隐式地将2000转换为'2000’，此时就违反了索引列不能使用函数的原则

注：group by内部会需要排序，使用索引效果等同于orderby，但是其会产生中间表，极其影响性能

是否使用索引总结

sql性能分析

查询优化

1. 最好使用小表驱动大表：例如 left join 左边应该是小表。小表驱动大表时，使用 exists 代替 in 效率更高
2. orderby排序优化：尽量使用索引进行排序（即使用 Using index）而非MySQL内部排序（即 Using filesort）
   • orderby使用多索引进行排序必须保持索引本身的顺序，不同于where。因为orderby使用不同列进行联合排序得到的结果是不同的
   • 混用desc/asc会使用filesort而忽略索引
   • 如果orderby的列不在索引上：其内部会使用filesort进行排序，分两种算法
     • 双路排序（早期算法）：进行两次磁盘扫描，第一遍扫描只读取指针和orderby列，对他们进行排序，再根据该排序顺序去磁盘一个一个取
     • 单路排序：直接从磁盘读取所有的sekect的列，然后在排序buffer中对他们进行排序，避免了一次磁盘io。但是它会使用更多的空间，因为它把每一行所有列都放进内存buffer中了。而且，如果所需的行较大，buffer中放不下，则会执行多路希尔排序，将会产生比双路排序更多的磁盘io
     • 为了解决单路排序的问题，可以调整MySQL的排序buffer相关的参数，sort_buffer_size:buffer容量，max_length_for_sort_data:单路排序最大排序数量，否则会使用双路排序
3. groupby优化：由于内部会首先进行排序，故其优化方式同orderby。另：优先使用where而不是having进行条件查询

慢查询

查询时间超过MySQL配置的long_query_time的sql会被记录到慢查询日志。默认为10秒 其会产生一个log文件，可以直接使用打开查看，也可以使用工具查看慢查询，例如mysqldumpslow工具 例如，使用mysqldumpslow查询访问次数最多的sql：mysqldumpslow -s c -t 10 /var/…/mydb-slow.log

函数和存储过程

函数有返回值，存储过程没有 使用 create function 创建函数，使用 create procedure 创建存储过程。使用call myprocedure调用存储过程

show profile

比explain更强大的sql性能分析工具，可以用来分析当前会话中语句执行的资源详细消耗情况

• 如何使用？

  1. 查询最近使用的sql：
  2. 查询sql的每一步消耗时间（查看cpu和block io为例）：

• 如何根据上述数据判断sql是否出现问题？

  1. 当查询过程中出现了 converting HEAP to MyISAM：表示查询结果太大，内存不够用了，开始往磁盘上搬了
  2. 出现creating tmp table：表示创建了临时表
  3. 出现copying to tmp table on disk：把内存中临时表复制到磁盘
  4. 出现locked

MySQL锁机制

锁的分类

1. 从数据操作类型分为：读写锁
2. 从锁的粒度分为：行表锁

主从复制

1. 复制的基本原理（类比redis使用rdb进行主从复制） slave会从master读取binlog（即mysql的日志文件）来进行同步数据

   • 基本步骤
     1. master将改变记录到二进制日志 binlog 中。这些记录过程叫做二进制日志时间（binary log events）；
     2. slave将master的binary log events拷贝到它的中继日志（relay log）
     3. slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且是串行化的

2. 复制的基本原则

   1. 每个slave只有一个master
   2. 每个slave只能有一个唯一的服务器ID
   3. 每个master可以有多个salve

3. 一主一从配置 修改主从配置

   • 主：修改my.conf配置文件 设置server-id=1（保证主服务器id唯一即可） 启用二进制日志binlog：log-bin=/…/mysqlbin

• 从：修改my.conf配置文件 设置server-id=2 启用二进制日志（可选）

在主机给从机创建账户授权复制

GRANT REPLICATION SLAVE ON *.* TO ‘username’@‘slave-ip’ IDENTIFIED BY ‘12345’

使用 show master status 查看主机状态 解释：从mysqlbin.0000035 文件的第341的位置开始复制

给从机配置主机，及binlog文件和复制位置

CHANGE MASTER TO MASTER_HOST=‘master-ip’, MASTER_USER=‘username’, MASTER_PASSWORD=‘12345’, MASTER_LOG_FILE=‘mysqlbin.0000035’, MASTER_LOG_POS=341

使用 show slave status 查看是否连接成功

此时在主机进行操作，从机就会同步

分布式事务

Mysql如何保证数据被持久化？ Undolog和Redolog undolog用于记录数据被修改前的值，redolog用于记录数据被修改后的新值 对数据的每一次修改都需要经理以下步骤： 将原数据保存在undolog —> 修改数据库的值(此时只是内存中修改) —> 将修改后的值存入redolog —> 持久化redolog（因为undolog也是记录在redolog中，所以只用持久化一次）—> 事务提交 注：事务提交就一定保证redolog已经被持久化了，即数据已经被持久化了 为什么不直接持久化数据而持久化redolog？ 持久化数据需要对磁盘进行随机读写，二redolog使用文件追加的方式，更快

分布式事务：

1. 分阶段提交 1.1 2PC 分两个阶段： 准备阶段：TM将事务请求发送给各个数据库执行，但不提交，数据库返回执行状态给TM 提交阶段：若TM收到的状态全部为成功，则向所有数据库发送commit命令。否则发送回滚指令。 优点：解决了分布式事务的问题，Mysql自己支持 缺点：属于阻塞式的，即多数据库向TM反馈准备阶段的结果时，所有的数据库都是阻塞加锁状态的。且单TM节点容易发生故障 注：由此衍生出3PC

2. TCC（try-commit-cancel） 分两步： 2.1. TM向数据库请求预留资源（例如：要减少某个库存值，则可以在该表中添加一个字段即被冻结库存数量，try阶段就是设置冻结数量的值） 2.2. TM根据try阶段的执行结果进行让数据库执行commit或者cancel操作

与2PC的区别： TCC的每个阶段都是独立commit的，避免了阻塞

优点：解决了2PC的阻塞问题 缺点：三个步骤都需要代码进行控制，增大了开发难度

3. 可靠消息队列 A服务执行本地事务并向MQ中发送一条需要B服务执行的事务信息（例如：下单—>减库存 两个服务），B从MQ中获取到信息之后执行本地事务操作，如果失败就不断重试

优点：实现简单 缺点：延时比较高，例如转账操作。且及其依赖MQ的稳定性

4. AT模式 即Seate实现的模式 结合了2PC和TCC的优势，每次执行sql都会被Seate拦截，分析sql中修改的数据，并select出原始数据记录下来，然后执行业务sql，并记录新值，如果分布式事务执行成功，则删除记录的原值，如果失败，则先对比表中数据是否和记录的新值相同，如果相同，则修改为原值，如果不同则需要人工干预

5. saga模式