MySQL学习笔记 - 我有点酷

概述

索引的数据结构

二叉树
红黑树
Hash表
B-Tree

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

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

B和B+树的区别

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

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数据存储格式

file

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

目录页
一张表中的数据会存储在多个页中，那么查找数据时如何知道在第几页？
在这些页之上又抽象出一种目录页的结果（即B+树中的非叶子节点），目录页中存储了每一页的最小索引的位置
如果建表时既没有定义主键，也没有定义唯一列，则InnoDB会自动创建一个名为 rowID的自增字段
MySql在创建表时会自动创建一个空页，当向表中存数据时就会向该页存，当该页存满时，会复制当前页到新的一页，然后再创建一个新页存储新的数据，并将第一页数据清空，升级为这两个新页的目录页，这样做的好处是保持查找开始内存位置不变，将第一页常驻内存，加快查找速度
创建多个字段联合索引
会将这些字段拼接起来进行排序（建立索引的本质就是排序），并在该索引的B+树的叶子节点只保存数据行的主键（即每创建一个索引都会创建一个B+树，但是除主键索引外，其他的都不保存行数据），然后根据该主键再去主键索引查找行数据
如果建立的索引列的数据都相似或者相同，则创建出的索引意义不大，Mysql会自动在这种索引中加上主键索引，保证索引唯一

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

注：

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

例如，以_bin结尾的就是转换为二进制比较

事务

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

隐式提交
使用ddl时会自动隐式提交数据
保存点
创建事务后，提交前，可以创建多个保存点。当需要回滚时可以指定回滚到某个保存点

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

读已提交（Read Committed）
必须提交之后其他地方才能读到数据，但是其他地方的事务中，即使没有提交，但是表发生了变动，同一个事务中也会查出不同的结构
会出现幻读和不可重复读（两者本质相同）
可重复读（Repeatable Read）
默认方式，同一个事务中，即时还没提交，表发生了变化，查出来的数据也是一样的
串行化
多个事务同时读没影响，但是如果有读写的话，读就会排队等待写完成。

实现

读已提交
假如两个事务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一条新记录

行锁

分为：

LOCK_REC_NOT_GAP: 单行记录上的锁
LOCK_GAP: 间隙锁，锁定一个范围（修改的所有行，包括行的间隙，即不允许在锁住的两行记录之中插入数据），但不包括记录本身。GAP锁的目的时为了防止同一事务的两次读操作出现幻读
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;

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

id

如果id相同，则表示sql执行时是从上往下执行的
如果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

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

possible_key/key

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

key_len

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

ref

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

rows

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

extra

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

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

索引优化

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

索引分析

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

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

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

索引失效（应避免）

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

最左前缀法则：如果建立了多列的联合索引（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，但是其会产生中间表，极其影响性能

是否使用索引总结

file

sql性能分析

查询优化

最好使用小表驱动大表：例如 left join 左边应该是小表。小表驱动大表时，使用 exists 代替 in 效率更高
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:单路排序最大排序数量，否则会使用双路排序
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锁机制

锁的分类

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

主从复制

复制的基本原理（类比redis使用rdb进行主从复制）
slave会从master读取binlog（即mysql的日志文件）来进行同步数据
- 基本步骤
  1. master将改变记录到二进制日志 binlog 中。这些记录过程叫做二进制日志时间（binary log events）；
  2. slave将master的binary log events拷贝到它的中继日志（relay log）
  3. slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且是串行化的
复制的基本原则
1. 每个slave只有一个master
2. 每个slave只能有一个唯一的服务器ID
3. 每个master可以有多个salve
一主一从配置
修改主从配置
- 主：修改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 查看主机状态
file
解释：从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 2PC
分两个阶段：
准备阶段：TM将事务请求发送给各个数据库执行，但不提交，数据库返回执行状态给TM
提交阶段：若TM收到的状态全部为成功，则向所有数据库发送commit命令。否则发送回滚指令。
优点：解决了分布式事务的问题，Mysql自己支持
缺点：属于阻塞式的，即多数据库向TM反馈准备阶段的结果时，所有的数据库都是阻塞加锁状态的。且单TM节点容易发生故障
注：由此衍生出3PC
TCC（try-commit-cancel）
分两步：
2.1. TM向数据库请求预留资源（例如：要减少某个库存值，则可以在该表中添加一个字段即被冻结库存数量，try阶段就是设置冻结数量的值）
2.2. TM根据try阶段的执行结果进行让数据库执行commit或者cancel操作

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

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

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

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

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

概述