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

file

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

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

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

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

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

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

注:

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

事务

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

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

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

    四种事务隔离

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

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

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

实现


  • 读已提交
    假如两个事务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;

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

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:没有索引
    file
    解释:由于没有任何索引,导致使用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

  1. 两表查询
    例子: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或联合索引中的值,则会使用该索引
    file
  • 字符串查询需要加引号:例如表中数据name=’2000’,使用查询 where name=2000也可以查到,mysql底层会隐式地将2000转换为’2000’,此时就违反了索引列不能使用函数的原则

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

是否使用索引总结

file

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:
      file
    2. 查询sql的每一步消耗时间(查看cpu和block io为例):
      file
  • 如何根据上述数据判断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 查看主机状态
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.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的阻塞问题
缺点:三个步骤都需要代码进行控制,增大了开发难度

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

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

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

  2. saga模式

Leave a Comment