Back

MySQL

记录MySQL从基础架构、常用sql、索引、优化、引擎、事务、锁、主从架构等原理

[TOC]

基础架构

MySQL逻辑架构图

  • 连接器:负责跟客户端建立连接、获取权限、维持和管理连接。登录进去后修改权限,默认是将在下一次登录后生效
  • 查询缓存:MySQL接收到查询请求后会先查询缓存,key是查询语句,value是查询结果,之后经过执行器的权限判断再返回,如果查不到则往后走。不建议使用,因为若有更新操作,会删除对应表的缓存,可能导致缓存命中低,可以设置query_cache_type=demand,默认不使用缓存,需要在查询时显示指定。MySQL8.0删除此功能
  • 分析器:对SQL语句进行分析,词法分析判断各个字符串代表的含义(包括列是否存在),语法分析判断SQL的语法是否正确,这一层操作之后,MySQL就知道你要做什么了
  • 优化器:决定是否要使用索引,使用哪个索引,决定表的连接顺序
  • 执行器:先判断是否有对该表的操作权限,之后判断要使用哪个引擎提供的接口
  • 引擎:对数据进行具体操作的执行者,事务和索引都是在这层做的,但具体需要引擎支持,例如MyISAM不支持事务,InnoDB支持

日志系统

关于物理日志和逻辑日志:物理日志记录每一个page具体存储的值,在这个数据页上做了什么修改,比如redo log;而逻辑日志记录每一个page中数据的变动过程,比如undo log、binlog、relay log;

比如一个page页中一个数据从 1 改到 2 ,再改到 3,物理日志记录最后一个值是 3 ,逻辑日志记录 1 -> 2, 2->3 的过程。

  • redo log重做日志:InnoDB独有,物理日志,记录这个页做了什么改动,使用二阶段提交保证两份日志逻辑一致。当有日志要写入时,先写到redo log buffer后状态是prepare,开始写bin log cache,bin log 写完后,事务提交,redo log 改为commit状态,redo log写完,此时事务就算完成;这里描述的写redo log和bin log都只写在了缓冲区,何时写进磁盘,是根据innodb_flush_log_at_trx_commitsync_binlog配置决定。用于实现数据持久化,以及宕机恢复数据。

    redo log 本质上记录了对某个表空间的某个数据页的某个偏移量修改了哪几个字节的值,具体修改的值是什么,一条redo log也就几个字节到十几个字节,格式是 日志类型,表空间ID,数据页号,数据页偏移量,具体修改的数据。redo log buffer默认是16MB,redo log 数据内容是记在 redo log block 里的,写满一个就存到redo log buffer里,然后写下一个,直到 buffer 满了,此时会强制刷盘。

    redo log 是固定大小的,比如有一组4个文件组成的“环形队列”,环形写入,一个文件写完就写下一个,4个轮转,作用是redo log刷盘完成之后这部分内存就能重新利用;首位指针表示当前记录的位置和当前擦除位置。当前擦除位置是一个叫 LSN(log sequence number)的检查点,表示在LSN之前的数据(缓冲池里的数据页、索引页)都已经持久化到磁盘了,redo log文件里在LSN检查点之前的内容才允许被覆盖,擦除或覆盖之前一定会刷到磁盘。

  • WAL机制:执行事务时,将表数据写入内存和日志(redo log),事务就完成了,此时表数据可能还没写入磁盘,InnoDB会在合适的时机将内存里的数据刷入磁盘。WAL机制主要是解决表数据写入时,CPU和磁盘速度的差异问题,也因为是先把数据写进内存,再写入磁盘,才需要redo log + 二阶段提交来解决崩溃数据丢失的问题。

  • bin log归档日志:属于server层的日志,逻辑日志,记录所有逻辑操作,追加写入,不会覆盖以前的日志,bin log有两种模式,statement 格式的话是记sql语句, row格式会记录行的内容,一般使用row,记录行变化前和变化后的数据,缺点是日志变大。用于主从复制和数据备份恢复

    bin log也是先写buff再写磁盘,建议设置sync_binlog=1,表示每次事务提交后bin log都会持久化到磁盘;

    其他参数:=0,表示由系统判断何时刷盘,=N,表示每次事务提交都会write到磁盘page cahce,但是会累计提交 N 个事务后才把bin log的数据持久化到磁盘,一般设置范围是100~1000,对应的风险是,如果机器宕机,会丢失最近 N 个事务的bin log日志;

    redo log prepare、commit 的XID与bin log的XID实现关联,通过XID的关联,就能知道两份日志是否完整,从而实现crash-safe。

只有当innodb_flush_log_at_trx_commit=1sync_binlog=1,数据库才具备crash-safe的能力。

结合redo log的二阶段提交和WAL机制,整个流程就是:

  1. 当有记录要更新时,数据修改前,先把老版本的数据写入undo log,然后把新数据先写进redo log buffer,状态是prepare,然后是写入 bin log cache,事务提交,redo log更新为commit状态,并更新内存里的数据后,整个更新操作就算完成。当两个参数都 =1 时,事务完整提交前,需要刷两次盘,一次 redo log,一次 bin log。

    建议设置innodb_flush_log_at_trx_commit=1,表示每次事务完成后,立即调一次 fsync 方法将redo log buffer 中的数据持久化到磁盘的cache,再调用一次flush 方法持久化到硬盘。

    其他参数:=0,表示每秒调用 fsync 方法将 redo log buffer 中的数据持久化到磁盘cache,再调用 flush 刷盘,机器停电或崩溃可能会丢失一秒的数据;=2,表示每次事务完成后,立即调一次 fsync 方法将 redo log buffer 中的数据写到磁盘page cache,然后每秒调一次 flush 方法持久化到硬盘,机器停电或操作系统崩溃可能会丢失一秒的数据。

    除此之后还有几种场景会强制写磁盘:redo log buffer满了、正常关闭服务器、redo log环形写入到达check point时。

  2. InnoDB在空闲的时候才真正的将内存中已更新的表数据刷新到对应的page页,写入磁盘中,此时数据才真正落盘。

    因为redo log格式固定,可以通过redo log buffer实现顺序写入磁盘,顺序IO写入速度快,而将表数据写入磁盘,需要更新redo log日志中的内容到表数据对应的page页,涉及到分页或合并等操作,属于随机IO写入,比较费时,所以才先写redo log日志,再把表数据写入磁盘。

可以只使用redo log来实现崩溃恢复,保证恢复后不会出现主从不一致的情况,由MySQL内部实现,但无法只使用bin log,原因是 InnoDB使用WAL机制,如果此时数据库崩溃,要依赖日志来恢复数据页,但是bin log并没有记录数据页的更新细节,而redo log因为环形写入的问题,无法对所有记录进行归档,仅仅只能实现崩溃恢复;InnoDB通过redo log的状态和比较与bin log的数据,来判断哪些数据需要进行崩溃恢复。

正常运行的情况下,数据页被修改到落盘,从头到尾操作的都是内存中的数据,操作记录只是顺带记录在redo log文件中,只有在崩溃恢复的场景下,如果InnoDB判断数据丢失了更新,才会从redo log文件中将数据恢复到内存,再从内存中将数据刷盘。redo log只是用于记录,真正刷盘的数据来自其他buffer。

备份时间的长短会影响日志文件的大小,文件的完整性,从而影响到恢复速度和恢复效果;

  • undo log回滚日志:InnoDB独有,逻辑日志,主要用于事务失败时的回滚,以及MVCC中版本数据查看。当事务被提交后,并不会马上被删除,而是放到待清理链中,等到没有事务用到该版本信息时才可以清理。

参考:MySQL中的日志机制

常用SQL

更新

update 表名 set 列名1=值1, 列名2=值2... where 列名3=值3

插入

insert into 表名 (列名1, 列名2, ...) values (值1, 值2, ...)

Count(*)、Count(1)、Count([列])区别

在count(*)不带条件在MyISAM里查询比较快,因为MyISAM会存储总条数,不带条件查询的时候直接用就行,而InnoDB带了事务,支持MVCC,因此每次count(*)时都会扫表

以下归纳基于InnoDB,count会返回满足条件的结果集的总行数,它会使用存储引擎进行全表扫描获取结果,比如count(1)会直接返回1,count(主键)会获取主键,返回给server层,由server层进行计数,因此按效率排序是:count(字段) < count( 主键id) < count(1) ≈ count(*)

  • count(列)会计算列或这列的组合不为空的计数
  • count(*) 跟 count(1) 的结果一样,都包括对NULL的统计,而count([列名]) 是不包括NULL的统计
  • 对于计数,也可以通过创建列为表名、total的表进行计数,利用事务能力,一般是先insert再update,理由是并发进行total值的更新时,是会上行锁的,如果先update total值可能会导致事务处理时间过长

having的使用

  • having一般需要搭配 group by 使用,在group by之后,order by之前

  • having一般配合聚合函数使用,而where后面不能加聚合函数

  • where是对表的字段进行条件过滤,having是对select出来的字段进行条件过滤

    可以想成 查询一些字段,先通过where进行一次过滤,group by进行一次分组,having对分组后的结果再过滤一次,having后的字段必须出现在select中

常见一点的sql,比如有如下表,这里为了方便理解以中文的形式表示字段

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
+-----+--------Log------+------------+
|  id | 网站名称 | 点击数 | date        |
+-----+---------+-------+------------+
|   1 |       A |    10 | 2016-05-10 |
|   2 |       C |    60 | 2016-05-13 |
|   3 |       A |   230 | 2016-05-14 |
|   4 |       B |    45 | 2016-05-14 |
|   5 |       E |   545 | 2016-05-14 |
|   6 |       D |    13 | 2016-05-15 |
|   7 |       C |   105 | 2016-05-15 |
|   8 |       E |   660 | 2016-05-16 |
|   9 |       C |   301 | 2016-05-17 |
+-----+---------+-------+------------+

查询 除了D网站外 各个网站的点击数 大于100 的 网站名称 和 点击数 并 降序 表示

1
2
3
select 网站名称, SUM(点击数)
	from Log where 网站名称!='D'   
group by 网站名称 having SUM(点击数) >100 order by SUM(点击数)  

order by

  • 全字段排序:查询条件是索引,但是order by 条件不是,会先遍历索引,再回表取值,每次取到数据就丢sort_buffer,完了之后在sort_buffer里根据order by条件排序 (利用sort_buffer + 临时表),会根据数据量采用内存排序或者外部归并排序;
  • rowId排序:如果select的字段太多,超过设置的最大长度max_length_for_sort_data,就会只取主键和order by的条件丢进去sort_buffer里进行排序,最后再回表根据主键取出其他select的字段;
  • 如果order by的条件正好是索引顺序,就不需要使用sort_buffer进行排序了,直接使用索引顺序即可;
  • order by rand(),随机排序,使用内存临时表,使用rowId + 随机数进行排序;
  • 不带查询条件进行order by,就算order by条件是索引,是不一定会走索引进行排序,原因是如果MySQL优化器判断走索引后要去回表数量太大,就不会走;
  • 带limit的order by,mysql会采用堆排;
  • 默认的临时内存表是16M,由tmp_table_size设置;

group by

  • group by一般是使用在select + 聚合函数的情况,如果select 没有聚合函数,语义(即按照group by后的字段进行分组,相同的分组只返回一行)与distinct是类似的,性能也一样
  • SQL语句的执行顺序是 from > where > group by > having > order by > limit,join 和on和and的组合属于from范围
  • group by实际上会进行排序操作的,先根据group by后的字段进行排序,再聚合,最后select出需要的字段返回,这个过程会用到内存临时表(可能会退化为磁盘临时表)
  • 无法这样使用select * from table group by column

distinct

select distinct colA, colB from table

  • distinct 接多个列,会对多个列的不同组合都列出来
  • 无法这样使用select a, distinct b from table

join

  • 由于有时优化器会选择错误的驱动表,使用 straight_join 则可以让MySQL默认使用左边的表作为驱动表

  • NLJ算法进行join操作:当有A、B两表,是一个1对1的关系,逻辑外键在B表带有索引,使用join进行关联查询,小表驱动大表(这样扫描的行数较少),每扫一行,通过外键索引,在另一个表找对应的行数据,总共执行1条语句,扫描len(A) + len(B) 行,但是如果不使用join查,而是先查出A表所有数据,再根据A的id查回B的数据,虽然扫描的行数一样,但是却执行了len(B) + 1条SQL语句,显然是使用了join的方式性能强,前提是B上带了索引。

  • BNL算法进行join操作:如果B上关联键没有使用索引,则算法是这样的,先对A表进行全表扫描存进内存,再对B表进行全表扫描存进内存,然后在内存(join buffer)里进行匹配,如果内存太小,则分块加载,匹配后将结果集返回,清空内存,再分配加载这样循环处理。

    对BNL的优化,一种是在业务端查回两张表的数据,在通过hash匹配组合,另一种是查join查询前,先创建临时表,创建索引,查询被驱动表的数据,插入临时表中,与临时表进行join操作,将BNL转为NLJ;

    小表驱动,小表指的是行数相对少,或者select时表的数据量相对少的表;

    所以,NLJ算法是从表A每查一行就根据索引去表B匹配一行,MRR优化就是在NLJ基础上,回表的时候根据有序的索引一批一批查,这样就快一点;而BNL算法是同时去查表A和表B的所有行,再进行匹配关联

  • 驱动表走全表扫描,被驱动表最好走索引扫描,使用NLJ算法,如果被驱动表是全表扫描,则使用(S)BNL算法

  • left join,使用left join时,左边的表不一定是驱动表,如果要使用left join语义,不能把被驱动表的字段放在where条件里做等值和不等值判断,必须放在on里,原因是MySQL会先用on作为条件进行过滤,完了才使用where进行过滤,放在on里能让过滤出来的条数少,要注意两者表达的语义还是有些不同的;

drop、delete与truncate

  • delete和truncate只删除表数据不删除表结构
  • 速度上 drop > truncate > delete
  • drop和truncate是ddl语句,操作是立即生效,原数据不放到rollback segment中,不能回滚,而delete是dml语句,该操作会放在rollback segment中,事务提交后才生效
  • 不需要表时使用drop,删除某些行时使用delete,保留表但清空表的数据时使用truncate

Limit

  • limt M [offset N],从第N条记录开始,返回M条记录,比如limit 5, 10,表示返回6-15行
  • 当limit后面只跟一个参数时,表示返回最大的记录行数目,比如limit 5,表示只返回前5行
  • 初始偏移量是0

日期类查询

  • curdate()函数:得到今天的日期,格式: 年-月-日

  • now()函数:得到今天的日期和时间,格式:年-月-日 时:分:秒

  • 两个datetime类型的字段相减,得到的单位跟日期的格式有关,如果格式有到秒,那减出来就是多少秒,如果格式只到日,那减出来就是多少日

  • UNIX_TIMESTAMP(datetime类型的字段) 将datetime类型的字段转换为时间戳,要注意时间戳是以1970 年 1 月 1 日开始算的

  • DATE_SUB(date, INTERVAL expr type) 函数:从日期减去指定的时间间隔

    date_format(date字段, ‘%Y%m%d %H:%i:%s') 函数:日期格式化函数,

    可以利用这些来查询最近多少天的数据如

    查询 近一小时的数据 where date字段 >= DATE_SUB(now(), INTERVAL 1 Hour) and date字段 < now()

    查询 昨天的数据 where date字段 >= DATE_SUB(CURDATE(), INTERVAL 1 Day) and date字段 < CURDATE()

    查询 近7天的数据 where date字段 >= DATE_SUB(CURDATE(), INTERVAL 7 Day)

    查询 本月的数据 where date_format(date字段, ‘%Y%m') = date_format(curdate() , ‘%Y%m')

    查询 上个月的数据 where period_diff(date_format(now() , ‘%Y%m') , date_format(date字段, ‘%Y%m')) =1

    查询 今年的数据 where YEAR(date字段)=YEAR(NOW())

    查询 去年的数据 where YEAR(date字段)=year(date_sub(now(),interval 1 year))

    查询本季和上一季的跟 查年的差不多 ,把 YEAR函数 换成 QUARTER函数 即可

数据类型

varchar和char

char是固定长度,varchar是可变长度,varchar(50)和varchar(200)存储字符串 “hello” 所占空间一样,但后者在排序时会消耗更多内存,因为order by采用fixed_length计算字段长度

int和int(20)

有符号的整型范围是-2147483648~2147483647;无符号的整型范围是0~4294967295;

int(20)表示能显示的宽度是20,比如id的值是10,那MySQL就会在前面加0,自动补全到20位,仍然占4个字节存储,存储范围也不变。

Datetime

  • 保存从 1001 年到 9999 年的日期和时间,精度为秒,使用 8 字节的存储空间
  • 与时区无关

TimeStamp

  • 和 UNIX 时间戳相同,保存从 1970 年 1 月 1 日午夜(格林威治时间)以来的秒数,使用 4 个字节,只能表示从 1970 年到 2038 年

  • 它和时区有关,每个时间戳在不同时区所代表的具体时间不同

  • 默认情况下,如果插入时没有指定 TIMESTAMP 列的值,会将这个值设置为当前时间

  • MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期,

    提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳

NULL

  • NULL跟任何值执行等值判断和不等值判断的结果都是NULL

索引

1.常见索引及概念

  • 聚集索引:InnoDB中的主键索引,每张表的主键构造一棵B+树,同时叶子节点中存放的即为整张表的行记录数据。一个表只能包含一个聚集索引,聚集索引通常提供更快的数据访问速度。

  • 非聚集索引:表中行的物理顺序与键值的逻辑顺序不匹配,查到记录对应的主键值 ,再使用主键的值通过聚集索引查找到需要的数据,这个过程也称为回表;即先通过普通索引找到主键,在通过主键找到数据。

    要细分的话可以分为普通索引,唯一索引,组合索引,全文索引这些。

    注:MyISAM无论主键索引还是二级索引都是非聚簇索引,而InnoDB的主键索引是聚簇索引,二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引。

  • 稠密索引:每个索引对应一个值

  • 稀疏索引:每个索引对应一个存储块

  • 覆盖索引:要查询的字段只需要去查询索引表就可以

  • 组合索引:最左匹配,建立一个组合索引等于建立多个索引,能达到覆盖索引的目的,效率高;例如有组合索引(a,b,c),则同时得到了索引(a),(a,b),(a,b,c), MySQL5.6 后有个索引下推,当查询条件带a,b的时候,会先查找索引树匹配a,再判断b,然后才回表找数据,从而减少回表次数,如果没有索引下推,MySQL是先查找索引树匹配a,拿到id回表查数据,判断是否匹配b,这样回表次数就太多了。

    组合索引之索引是最左匹配跟B+树有关,也是类似Order by的过滤,根据索引依次排列数据的,如Order by a,b,c 则先排a,a相同再排b,b相同再排c

索引可能因为删除,或者页分裂等原因,导致数据页有空洞,重建索引的过程会创建一个新的索引,把数据按顺序插入,这样页面的利用率最高,也就是索引更紧凑、更省空间,但主键索引的重建会导致整个表重建,一般可以使用alter table T engine=InnoDB来达到重建主键索引的效果

2.特点

需要建立的列:经常需要搜索的列、主键列、外键列、排序的列、经常在where后面出现的列

  • 避免进行数据库全表的扫描,大多数情况,只需要扫描较少的索引页和数据页,而不是查询所有数据页。而且对于非聚集索引,有时不需要访问数据页即可得到数据。
  • 聚集索引可以避免数据插入操作,集中于表的最后一个数据页面。
  • 在某些情况下,索引可以避免排序操作
  • 加速表与表之间的连接
  • 在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间
  • 增,删,改会带来不小性能开销

3.原理

B类树都是为了磁盘或其他辅助存储设备而设计的一种数据结构,目的是为了在查找数据的过程中减少磁盘I/O次数。

B+树:表的数据为叶子节点,非叶子节点为索引,有两条路径,一条是树,一条是各叶子相连

  • N叉树的N在MySQL5.6后可以通过page大小来间接控制,叶子节点是数据页(page),页与页之间组成双向链表
  • 一个数据页(page)可以包含多个行(记录),行按(记录)照主键顺序,行与行之间组成单向链表;每一个数据页中有一个页目录,方便按照主键查询行;
  • 页目录中通过槽把行(记录)分成不同小组,每个小组内包含多条行(记录),按照主键搜索页中行(记录)时,使用二分法查找,从槽开始依次往下找;
  • B+树的插入可能会引起数据页的分裂,删除可能会引起数据页的合并,二者都是比较重的IO消耗,所以比较好的方式是顺序插入数据,这也是我们一般使用自增主键的原因之一;
  • 每个结点就算一个page,数据以Page为单位在内存和磁盘间进行调度,每个Page的大小决定了相应结点的分支数量,每条索引记录会包含一个数据指针,指向一条数据记录所在文件的偏移量;

B+树
B+树

B-树:也称B树,不限制出度的个数,所有节点为表的数据,只有一条路,从根节点开始

B树
B树

为什么说B+树比B树更适合MySQL数据库索引

B+树的磁盘读写代价更低:B+树的内部节点并没有指向关键字具体信息的指针,因此其内部节点相对B树更小,如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多,一次性读入内存的需要查找的关键字也就越多,相对IO读写次数就降低了。

B+树的查询效率更加稳定:B+树深度比较平均,由于非叶子点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引,任何关键字的查找必须走一条从根结点到叶子结点的路,即所有关键字查询的路径长度相同,因此每一个数据的查询效率相当。而B树查询的深度比较随机,比较适合定点查询,单次查询效率高。

由于B+树的数据都存储在叶子结点中,分支结点均为索引,方便扫库,顺序IO,只需要扫一遍叶子结点即可;但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,带来更多的随机IO。所以B+树更加适合在区间查询的情况。

B树不适合遍历数据,B树比较合适查询单一记录,常用与NoSQL的索引结构,NoSQL一般是Key-value形式的存储,文档性一般是Json存储。

与二叉查找树、AVL树的比较

  • AVL树的出度为2,而且AVL树要严格保持平衡,但旋转保持平衡比较耗时,适合用于插入删除次数比较少,但查找多的情况
  • 二叉查找树在查找最大值或最小值的时候,二叉查找树就完全退化成了线性结构了
  • 其他缺点同下面

与红黑树的比较

  • 红黑树出度为2,B+树出度不止2,因此红黑树的高度会比B+树高,查找的次数也多了。(红黑树不是严格的平衡二叉树,旋转次数相对少,高度比平衡二叉树的低些)

  • B+树在降低磁盘I/O操作数方面占优势

    为了减少磁盘 I/O,磁盘往往不是严格按需读取,而是每次都会预读。预读过程中,磁盘进行顺序读取,顺序读取不需要进行磁盘寻道,并且只需要很短的旋转时间,速度会非常快。

    操作系统一般将内存和磁盘分割成固态大小的块,每一块称为一页,内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小,使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性,相邻的节点也能够被预先载入。

参考一步步分析为什么B+树适合作为索引的结构

4.失效情况

在使用到索引列的情况下

  • 对索引列作运算如 + - * / !
  • 索引类型出错,比如该索引列是字符串,但是写时候没有加``号,字符串和数字比较,MySQL会把字符串转为数字,无法转成数字的字符串都会被转换成0;或者是字段的字符编码格式不同,,比如有两张表,它们有外键关系,但是由于一张表是UTF-8另一张表是UTF-8mb4,也会导致索引失效;它们的原因都是MySQL在进行比较的时候,会使用函数对字段进行转换
  • 模糊查询 like ‘%keyword%` 查询不能有前置的%,如果是 like ‘keyword%’ 这样还是可以用到索引的
  • 索引列里有字段为null,null值不会加入到索引中
  • 使用or连接条件,如果or连接的条件中有一个不是索引,会失效,可以改成使用union all来连接两条sql语句
  • 组合索引没有体现最左匹配
  • is null / is not null 对索引作判断
  • 索引上使用 != 或者 <> 还有not in
  • 索引的值只有几种情况,如性别只有男和女,这种情况虽然也会用索引,只是意义不大
  • 表的量级较小,存储引擎判定使用全表扫描更快
  • 有一case:1.select * from T where k in(1,2,3,4,5);2.select * from T where k between 1 and 5,k为索引,但是推荐使用方法2,因为方法1会导致树查5次,而2是1次
  • 对索引字段使用了函数进行计算,可能会导致MySQL不使用该索引,或者进行了全索引扫描,无法用到索引进行快速定位
  • 如果查询的值的长度是否大于索引定义的长度,如果大于,虽然也会走索引,因为MySQL是先把查询值的长度截断成跟索引定义的长度一致去遍历索引,但是它还要再回表得到数据进行比较,所以查询会很慢

5.优化

  • 注意区分度,计算索引最优长度,使用这个计算 count(distinct left(列名, 索引长度)) / count(*) from table区分度越高越好,另外使用前缀索引虽然会减少索引存储空间,但是可能会增加扫描次数或者覆盖索引不生效

  • 当表的字符集编码或者属性不同时,需要想办法把函数加再索引对应的值上,而不是索引字段上,或者去掉函数,使用其他方法替代

  • 当要充当索引的字段在某些长度的区分度太小时,可以增加一个字段,采用索引字段的倒序存储或者hash的方法来充当索引,缺点是无法使用索引进行范围查询,而hash更是只能支持等值查询,查询时需要进行额外的计算,也是一种性能消耗

  • 由于MySQL在选择索引的时候会根据 索引区分度 和索引对应的预估扫描行数(包括回表),但是有可能预估的结果是不准的,如果通过explain命令发现rows的值与想象中的偏差较大,可以执行analyze table [tableName]来重新统计索引信息,或者使用force index([索引名称])来强制使用索引,或者重写SQL,引导MySQL使用正确的索引

  • 注意索引的最左前缀原则,如果在设置联合索引时,可以通过调整顺序来达到少维护一个索引,拿这个顺序就可以优先考虑,另外一个要考虑的就是索引的大小

  • 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

  • 启用MRR优化:在MySQL 5.6之后,使用MRR进行优化,需要设置set optimizer_switch =' mrr = on, mrr_cost_based = off, batched_key_access = on';

    在没使用MRR优化前,通过索引回表是一行一行去查的,每次通过普通索引查到后的主键id是无序的,多次回表查性能不太好,而MRR优化指的是,如果主键索引有序,记录递增插入,则会先根据索引找到所有的id,再对id进行排序,再回表查。

  • 关于普通索引和唯一索引

    在查询上,唯一索引和普通索引的区别是,唯一索引在查找到结果后就不会继续往下查了,但其实性能跟普通索引差别不会很大,但是更新的时候唯一索引由于用不上change buffer机制,更新的性能比较差

    在更新上,InnoDB会先判断更新的数据是否在内存,如果在就直接更新内存,如果不在,就把更新操作写到change buffer,等到数据加载到内存,在从change buffer里将更新操作更新到内存。change buff只适用普通索引上的更新操作,因为唯一索引因为需要先读取所有数据,判断索引是否重复后再插入,如果此时数据没有被读进内存,需要磁盘随机IO读取,最终导致更新变慢。

    另外,为了保证更新操作的稳定性,实际上在写内存的过程中还会把相关操作记录按顺序写进redo log(磁盘),才算真正完成更新操作。查询的时候其实可以直接查内存里的数据(内存已更新),或者先把磁盘里的数据读到内存,再配合change buffer就能得到更新后的数据了

  • 使用临时表,如果是InnoDB引擎,那创建的临时表是写磁盘的,如果是Memory引擎,则是写内存的。创建语句是 create temporary table ···engine=xxx,用法跟普通表类似,但仅在当前线程可见,show tables不显示临时表,当当前线程处理完成后,临时表会被清空,但会保留表结构,允许在不同线程间重名,如果bin log格式是statement或mix,操作临时表的记录也会记录在bin log,备库也会跟着操作。

    内部临时表常用于不带索引的 join、分库分表时的联合查询、union查询(对两个查询结果求并集并去重,union all则不去重)、group by查询(order by和group by子句不一样时也会)、distinct查询时加上order by、from 中的子句查询,explain查询指向计划结果的extra列

    当查询的数据量比较大时,默认会先使用内存临时表,发现太大后才转成磁盘表,因此如果数据量太大,可以使用SQL_BIG_RESULTselect SQL_BIG_RESULT 字段 from 表 group by xxx)告诉MySQL强制使用磁盘临时表

    一般情况下还是使用内存表快些,通过调大tmp_table_size来加大内存临时表的大小,默认是16M,这种临时表是由MySQL查询算法决定使用的

  • group by 或order by多个字段时,需要为这多个字段建组合索引,不然也是全表扫

  • 关于大数据量时的分页,优化的思路也是尽可能的使用索引,比如

    • select * from table where id > (页数*页面大小) order by id limit M,缺点是不太准确,会漏数据, order by 使得结果稳定
    • select * from table where id > (select ID from table order by id limit M, 1) order by id limit n,先利用子查询把id查出来,依靠id上的所有,外层效果跟上面的类似
    • select * from table where id in (select id from table limit M, N),同样也是利用id上的索引,覆盖索引,只查主键时,效率很高。

6.分析

  • explain + SQL语句,给出该SQL语句的分析结果,看看查询的类型,有没有用到索引,是不是全表扫描

    比较重要的字段:

    • select_type:查询类型,如 SIMPLE(简单查询)、PRIMARY(当存在子查询时,最外面的查询被标记为主查询)、UNION(联合查询,当一个查询在UNION关键字后就会出现)、SUBQUERY(子查询)等;

    • type:访问类型, 从左到右,查询性能依次减弱,一个好的SQL起码得达到range级;

      NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

      • NULL:MySQL在优化阶段分解查询语句,在执行阶段用不着再访问表和索引,比如explain SELECT 5*7这种单纯做计算的,一般用在已建立索引的情况下,查找索引的最大值和最小值,因为MySQL直接查找B+树最边上的叶子节点就能知道了,比如EXPLAIN SELECT MAX(id) FROM student

      • const:表示通过索引一次就找到了,一般是查询条件是主键或者唯一索引的场景下;

      • eq_ref:联表查询,按连表的主键或唯一键联合查询,当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型;

      • ref:表示单表扫描或者表间的连接匹配条件时用到普通索引或者唯一索引的前缀匹配;

      • index_merge:索引合并, 表示查询使用了两个以上的索引,最后取交集或者并集,常见and or的条件使用了不同的索引;

        5.0后才有的功能,使得MySQL可以在一次查询种使用多个索引,但是使用场景比较局限,多发生在查询条件涉及多个and和or的场景

      • range:索引范围查询,常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

      • index: 只遍历索引树,通常比 All 快。因为索引文件通常比数据文件小,即 all 和 index 都是读全表,但 index 是从索引中读取的,而 all 是从硬盘读的。

    • possible_keys:能使用哪个索引找到行,查询涉及到字段上若存在索引,则该索引将被列出,但不一定被查询使用;

    • key:索引列的名称,如果没有使用索引,显示为NULL;

    • ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

    • rows:扫描的行数

    • filtered:查询出的行数占表的百分比

    • extra:额外信息说明

      • using index:使用到了覆盖索引;
      • using temporary:MySQL对查询结果排序时使用了临时表,查询效率比filesort还低,常见于order by和group by
      • using filesort:使用外部排序,通过磁盘和内存交换数据来排序,查询效率不高;
      • using index condition:使用了索引下推;
      • using join buffer(block nested loop):使用join连表,并使用了缓冲区;
      • using union:使用索引并取并集;
      • using sort_union :先对取出的数据按rowid排序,然后再取并集;
      • using intersect :索引取交集;
  • show processlist,此命令用于查看目前执行的sql语句执行的状态,比如当CPU使用率飙升时,可通过该命令查看哪些SQL语句在执行

  • show status,查看数据库运行的实时状态,比如查询运行期间SQL的执行次数、连接数、缓存内的线程数量、连接数等,具体看 mysql SHOW STATUS 变量

  • show variables,查看系统参数,一些静态参数,比如开启慢查询,设置索引缓冲区大小,具体参考:Mysql show variables命令详解

  • performance_schema和sys系统库

    • MySQL启动前需要设置performance_schema=on,但是性能会比off少10%;

    • 查询sys.schema_table_lock_waitssys.innodb_lock_waits表可以知道那条语句在占用锁;

    • 查看 information_schema.innodb_trx表可以看到事务具体的状态;

  • 使用show engine innodb status查看数据库请求情况

  • 使用show status like 'innodb_row_lock%';查询行锁竞争情况

  • 使用show status like 'table%';查询表锁竞争情况

  • select trx_id,trx_state,trx_started,trx_wait_started,trx_operation_state,trx_tables_in_use,trx_rows_locked,trx_rows_modified,trx_query from information_schema.innodb_trx;查询当前事务情况

  • select * from information_schema.innodb_lock_waits;查看锁等待对应关系

  • select * from information_schema.innodb_locks;查看当前出现的锁

  • 当有语句执行过久或有语句一直被阻塞时,可以kill掉它

    使用kill query/connection + 线程id终止语句或连接,但kill不是直接终止线程,只是告诉该线程这条语句不用继续执行了,MySQL会在执行逻辑上打上断点标记,线程执行到该位置,才会判断状态是否是处于被kill状态,然后进行收尾工作,比如释放掉之前持有的锁

  • 慢查询分析

打开:set global slow_query_log='ON',临时开启,无需重启,永久开启则在my.cnf文件里设置

设置日志存放位置:set global slow_query_log_file='/usr/local/mysql/data/slow.log';

设置超过x秒就会记录到慢查询日志中:set global long_query_time=x;

查看慢查询相关设置:show variables like 'slow_query%';

慢查询日志分析工具:mysqlsla、mysqldumpslow

慢查询日志中,rows_examined字段,表示某个语句执行过程中扫描了多少行

MySQL索引原理及慢查询优化

存储引擎

1.MyISAM

  • 设计简单,数据以紧密格式存储。对于只读数据,或者表比较小、可以容忍修复操作,则依然可以使用它。

  • 提供了大量的特性,包括压缩表、空间数据索引等。

  • 不支持事务。

  • 不支持行级锁,只能对整张表加锁,读取时会对需要读到的所有表加共享锁,写入时则对表加排它锁。但在表有读取操作的同时,也可以往表中插入新的记录,这被称为并发插入(CONCURRENT INSERT)。

  • 可以手工或者自动执行检查和修复操作,但是和事务恢复以及崩溃恢复不同,可能导致一些数据丢失,而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE选项,在每次修改执行完成时,不会立即将修改的索引数据写入磁盘,而是会写到内存中的键缓冲区,只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能,但是在数据库或者主机崩溃时会造成索引损坏,需要执行修复操作。

2.InnoDB

  • 是 MySQL 默认的事务型存储引擎,只有在需要它不支持的特性时,才考虑使用其它存储引擎。

  • 实现了四个标准的隔离级别,默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下,通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻读。

  • 主索引是聚簇索引,在索引中保存了数据,从而避免直接读取磁盘,因此对查询性能有很大的提升。

  • 内部做了很多优化,包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

  • 支持真正的在线热备份。其它存储引擎不支持在线热备份,要获取一致性视图需要停止对所有表的写入,而在读写混合场景中,停止写入可能也意味着停止读取。

关于有时更新或者查询时突然变慢的原因:首先,InnoDB更新采用WAL机制,即更新时通过redo log记录更新操作,修改内存里的数据,然后再在恰当时间flush到磁盘(即先写日志,再写磁盘),原因就是出在flush磁盘上,当redo log满了,或者内存满了,脏页太多,都会将内存里的数据flush到磁盘,以腾出空间,如果要刷的数据特别多,那消耗的时间就长。

解决方法:1. 设置到 innodb_io_capacity 参数,该参数会告诉InnoDB机器的磁盘能力,可以使用fio工具测出 ;2. 控制脏页的比例,设置 innodb_max_dirty_pages_pct 的值,默认是75%,达到了就会刷; 3. 刷新脏页时是否会递归检测隔壁数据页是否也是脏页,如果是会连着一起刷,通过 innodb_flush_neighbors=1表示采用这种机制,=0表示只会刷自己,这个机制对机械硬盘关系比较大,SSD则不会

关于InnoDB的删除

innodb_file_per_table=OFF:表示表数据放在系统共享表空间,=on表示各个表空间放在独立文件下,后缀名是 .ibd ,一般设置=on,便于管理。

当我们使用delete删除数据时,InnoDB实际上是把数据页上的该数据标记为删除,表示该位置可以进行复用,此时磁盘上的文件并不会变小,当数据随机插入时会因为页分裂,分裂后的页可能存不满数据,就会标记某些位可复用,导致页的利用率不高,当有大量的增删时,会导致数据页存在大量空洞,为了压缩空间,此时的解决办法是重建表,一般使用alter table [tableName] enging=InnoDB达到重建的目的,MySQL会自动创建临时表,进行数据转存,交换表名,删除旧表,此时会阻塞,阻止增删改,5.56版本后使用onlineDDL机制,解决了这个问题,解决方法是使用redo log记录新插入的数据 + MDL读锁(写锁会退化) + IO + CPU

4大特性

  1. 插入缓冲:使用change buffer,对insert、delete、update、purge都有提升;
  2. 双写机制:InnoDB在将buffer pool中的Dirty Page脏页刷到磁盘时,会先将dirty page刷到InnoDB ;tablespace中的一个区域,这个区域称为 Double Write Buffer,该区域大小 2MB ,每次写入 1MB,128个页,每个页16k,其中120个页为后台线程批量刷Dirty Page,还有8个也是为了前台起的single page flash线程,用户可以主动请求使用,迅速提升空余的空间,在像Double Write Buffer写入成功后,第二步是将数据分别刷到一个共享空间和真正应该存在的位置;
  3. 自适应哈希索引:InnoDB会监控对表上辅助索引页的查询,如果发现建立hash索引可以提升性能,就会在缓冲池建立hash索引,提升查询性能;
  4. 预读:用异步将磁盘中的页读取到buffer pool中,预读请求的所有页集中在一个范围内;

3.Memory

  • menory引擎的内存表不同于InnoDB的内部临时表,内存表是库内全局可见,写内存的,而innodb的内部临时表是写磁盘的,同一线程内可见,线程结束就会清空
  • 默认是hash索引,数据单独存放,索引上保存数据的位置,这种形式称为推组织表,而InnoDB那种b+数结构的则是索引组织表
  • InnoDB数据存放是有顺序的,因此有可能会产生空洞,而memory则是有空位就可以存放,因此当数据位置发生变化时,innoDB只需要修改主键索引,而内存表需要修改所有索引(哈希表扩容导致)
  • 内存表不支持变长数据类型,只能固定字符串长度,即varchar(N)会退化为char(N)
  • 内存表的索引地位是相等的,而Innodb表分成了主键索引和普通索引
  • 由于hash索引并不适合索引范围查询,范围查询实际上是全表扫描,如果要支持索引范围查询,需要建立B树索引
  • 内存表仅支持表级锁,粒度较大,并发度低
  • 重启会清空内存表的特性会影响主备复制

4.MyISAM与InnoDB引擎区别

  • MyISAM是非事务安全;InnoDB是事务安全型

  • MyISAM的锁是表锁;InnoDB支持行锁

  • MyISAM支持全文索引;InnoDB不支持(5.6版本后才支持)

  • MyISAM适合 SELECT 密集型的表;InnoDB适合 INSERTUPDATE 密集型的表;MyISM可以直接定位到数据所在的内存地址,直接找到数据,而InnoDB需要维护数据缓存,查询过程中先定位到行所在的数据块,在从数据块中定位到要查找的行,所以查询性能上差于MyISM

  • MyISAM表是保存成文件形式,跨平台转移方便

  • InnoDB表比MyISAM表安全

  • MyISAM对于不会进行修改的表,支持压缩表,极大减少磁盘空间占用

  • MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多,而且恢复的速度也更慢;InnoDB因为有redo log,支持安全恢复

  • MyISAM不支持外键;InnoDB 支持外键,InnoDB 支持在线热备份

  • MyISAM是非聚集索引,使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针,所以还要再查一次才能得到数据;

    InnoDB是聚集索引,数据文件是和(主键)索引绑在一起的,即索引 + 数据 = 整个表数据文件,通过主键索引到整个记录,必须要有主键;辅助索引是以建索引的字段为关键字索引到主键,查询时先查到主键,再通过主键查询到数据;

    即MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针;InnoDB的B+树主键索引的叶子节点就是数据文件,辅助索引的叶子节点是主键的值;

事务

1.ACID原则

原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)

  • 原子性:事务中的所有操作要么全部提交成功,要么全部失败回滚

  • 一致性:数据库总是从一个一致性的状态转换到另一个一致性的状态

  • 隔离性:一个事务所做的修改在最终提交以前,对其他事务是不可见的

  • 持久性:一旦事务提交,则其所做的修改将永久保存到数据库

2.并发情况下带来的问题

  • 脏读:如有事务A和B,A读取了B未提交的数据

  • 丢失更新:如有事务A和B,AB均写入数据,A写入的数据被B覆盖

  • 不可重复读:如有事务A和B,A负责读取,B负责写入,A连续读的过程中B写入了一次,A前后两次读出来的数据不一样

  • 幻读:如有事务A和B,A修改表内数据的过程中,B向表内插入了一条数据,A修改完后发现数据并没有被全部修改完,或者是在RR隔离级别下,事务A内前后两条相同的SQL带“当前读”查询查回来的数据数不一致

不可重复读和幻读的区别:不可重复读侧重于update,而幻读侧重于insert和delete。不可重复读是在一个事务内前后两次读取的数据不一致,此时数据数量没有变化,重复读取得到的数据不一致,所以叫不可重复读;而幻读是在一个事务内前后两次读取的数据不一致,读的数据量变多或者变少了,这些多了的数据或少了的数据就像幻觉,所以叫幻读

3.事务隔离级别

隔离级别就是为了解决上述并发时候带来的问题

  • DEFAULT:默认隔离级别,即使用底层数据库默认的隔离级别;

  • READ_UNCOMMITTED:未提交读,一个事务未提交时,它的变更可以被其他事务看到;

    可能出现 脏读、不可重复读、丢失更新、幻读;

  • READ_COMMITTED:提交读,一个事务提交之后,它做的变更才会被其他事务看到,保证了一个事务不会 读 到另一个并行事务已修改但未提交的数据;

    避免了“脏读”,可能出现不可重复读、丢失更新;

    Oracle默认隔离级别;

  • REPEATABLE_READ:可重复读,一个事务在执行中看到的数据,总是跟这个事务在启动时看到的数据一致,保证了一个事务不会 修改 已经由另一个事务读取但未提交(回滚)的数据。

    避免了脏读、不可重复读取、丢失更新,可能存在幻读;

    MySQL默认是此隔离级别;但MySQL在此隔离级别下会使用MVCC和间隙锁来防止 幻读;

  • SERIALIZABLE:序列化,最严格的级别,事务串行执行,即一个事务要等待另一个事务完成才可进行

    效率最差,但也解决了并发带来的那4种问题;

例子:

事务A 事务B
启动事务,查询得到值1 启动事务
查询得到值1
将1改为2
查询得到的值v1
提交事务B
查询得到的值v2
提交事务A
查询得到值v3

在不同隔离级别下的答案

未提交读:v1=2,v2=2,v3=2

提交读:v1=1,v2=2,v3=2

可重复读:v1=1,v2=1,v3=2

串行:v1=1,v2=1,v3=2,且直到事务A提交后,事务B才可以继续执行

一般避免使用长事务,即在一个事务里做过多操作,长事务会导致回滚日志变大,也会占用锁资源

4.事务相关命令

  1. 显式启动事务,使用begin或strart transaction启动事务,commit提交事务,rollback回滚;
  2. set autocommit=0,关掉自动提交,任何语句执行都需要显式的提交(主动commit或rollback)才算执行完成;
  3. set autocommit=1,执行任意一条语句都会默认开启单次事务执行完成后隐式提交,事务也可以显式开启,直到显示使用commit、rollback或断开连接。一般是使用set autocommit=1,开启事务,再commit提交事务,执行commit work and chain则提交事务并开启下一次事务;

锁的分类

根据范围,可以分为全局锁、表级锁、行锁,当多种锁同时出现时,必须得所有锁不互斥,才能并行,否则就得等。

  • 全局锁:对整个数据库实例加锁,命令: Flush tables with read lock,让整个数据库变成只读,禁止任何ddl、dml语句

    一般用于全库逻辑备份,但有可能造成主从库数据延迟或者业务停摆,不用的话又会导致数据不一致问题,一般这种方式是给不支持 可重复读 事务的引擎使用的,像InnoDB可以在可重复读隔离级别下开启事务读数据,利用MVCC来保证在此期间数据一致

  • 表级锁

    一种需要显示启动,比如lock tables t1 read, t2 write; 表示线程在执行unlock tables之前,只能读t1,读写t2,其他操作做不了。

    另一种是 MDL(metadata lock),不需要显示使用,在访问一个表时自动加上,作用是保证读写正确性,当对一个表内数据做CRUD时,加MDL读锁,当对一个表做结构变更时,加MDL写锁。

    主要用于保护表的元数据,比如字段名等,比如查询和修改表字段的语句同时出现,如果没有MDL锁,就会导致查出来的数据列名有问题。

    MDL锁和表锁时可以同时出现的,比如MyISAM表上更新一行,会加上MDL读锁和表锁

    读锁间不互斥,读写、写写间互斥,MDL会在事务提交后释放。当需要对热点表做结构变更时,最好在变更语句上加等待时间,避免出现死锁导致整个表无法读写,

  • 行锁:在InnoDB事务中,采用二阶段锁协议,行锁是在事务结束后才释放,在事务过程中,即使一开始用了后面没用到也不会被释放,因此,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放,减少锁的影响时间;行锁是统称,主要可以细分为共享锁和排他锁这些

    记录锁:行锁,对索引进行锁定,如果是主键索引就会锁一条,但如果是二级索引,就会锁所有匹配的记录。

  • 共享锁 - S锁(SQL + lock in share mode):行锁,事务T对数据A加上共享锁,其他事务只能对A添加共享锁,不能加排他锁,获取共享锁的事务只能读不能写。

  • 排他锁 - X锁(SQL + for update):行锁,事务T对数据A加上排他锁,则其他事务不能再对A加任何类型的锁。获得排它锁的事务即能读数据又能修改数据。

  • 意向共享锁 - IS锁:表级锁,表示事务持有表中行的共享锁或者打算获取行的共享锁,仅表示意图,不阻塞其他操作,当事务在获取表中的共享行锁时,需要先获取表中的意向共享锁

  • 意向排他锁 - IX锁:表级锁,表示事务持有表中行的排他锁或者打算获取行的排他锁,仅表示意图,不阻塞其他操作,当事务在获取表中的排他行锁时,需要先获取表中的意向排他锁

    意向锁的作用主要为了解决遍历整个表来寻找行锁的情况,比如在alert语句修改表结构的时候使用,场景:

    事务A想修改表T的行R,A获得行R的排他锁,锁住了行R,事务B使用Alter Table语句修改表T的结构,此时需要获取表T的共享锁,由于它不知道表T是否存在行锁,只能去遍历,当表有行锁时,只能等行锁释放才能修改表结构,因为遍历很耗性能,所以需要意向锁来解决这个问题,事务A在获得行R的排他锁时,需要先上表T的共享排他锁,事务B在Alter 表T时就可以直接判断该表是否被上行锁了。

  • Auto-Inc 锁:自增锁,比较特殊,当设置键为自增时使用,比如自增主键,在生成自增id时,会先获取相关表的 Auto-Inc 锁,阻塞其他事务的插入操作,保证自增的唯一性。不遵循二阶段锁协议,因为它并不是在事务提交时释放,而是在inset语句执行后释放,即使是在回滚时,自增值也不会减一。

  • 多版本并发控制(MVCC):用来解决读-写冲突的无锁并发控制,为事务分配一个单向增长的时间戳,为每个修改保存一个版本,版本与事务时间戳关联,读操作只读该事务开始前的数据库的快照。MVCC 在语境中倾向于 “对多行数据打快照造平行宇宙”,而 CAS 一般只是保护单行数据而已

    MVCC只在提交读 和 可重复读隔离级别下有效,保证在读操作不用阻塞写操作,写操作不用阻塞读操作的同时,避免了脏读和不可重复读。 对于可重复读,查询只承认在事务启动前就已经提交完成的数据; 对于提交读,查询只承认在语句启动前就已经提交完成的数据;

    MVCC的版本快照,指的是什么呢,而且它是基于整个库的,总不能保存多个版本的库的所有数据吧?

    实际上MVCC得到的快照是逻辑上的数据,是推测出来的,通过当前值,利用事务Id和undo log日志,根据日志"回滚"得到各个版本的数据,事务id可以简单理解为对该行数据进行更改时产生的id,当然一个事务内可以对多条数据进行操作,这多条数据的事务id都是相同的

    例子:

    假如一个值1被按顺序改为2、3、4,每一次更改都会记录在回滚日志(undo log)里,如:将2改为1 -> 将3改为2 -> 将4改为3,当前的值是4,在查询这条记录的时候,不同时刻启动的事务会有不同的视图,比如有视图A(将2改成1)里看到的值是1,即同一条记录在数据库中存在多个版本

  • 间隙锁:当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做间隙锁。

    主要是为了解决幻读问题,只在可重复读隔离级别下有效,一般与行锁一同出现。

    间隙锁也会导致死锁,比如两个事务同时在一段范围内的数据加入间隙锁(事务间的间隙锁不会冲突,可以加成功),又insert或update在这个范围内的数据,就会导致死锁

    另外,“间隙” 是 由这个间隙右边的记录定义的,也就是说,如果 有索引 c的值为0,5,10,15,先有事务A查询select c from t where c > 5 lock in share mode,加锁范围是(5, 10], (10, 15], (15, max],然后有事务B更新update t set c = 1 where c = 5,执行成功,此时c的值有 0,1,10,15,再执行update t set c=5 where c=1;会执行不成功,被blocked住,原因是间隙锁的间隙变成了 [1, 10],所以更新失败

  • Next-Key 锁 = 间隙锁(gap lock) + 行锁(record lock),在可重复读隔离级别下,InnoDB在查找和扫描索引时,都会使用Next-Key锁来防止幻读的发生。

  • 插入意向锁(II Gap):特殊间隙锁,只有在插入时使用,表示插入的意向,属于行级锁,但不与行级锁冲突,而是与间隙锁和 Next-Key 锁冲突,当插入前需要获取插入意向锁,此时会与 Next-Key 锁冲突而阻塞,从而防止幻读。

可重复读隔离级别时的加锁规则

  1. 查询过程中扫描到的行才会加锁,锁的基本单位是next-key lock(左开右闭);

  2. 如果等值查询的对象不存在,会在该等值的 前后 遇到的第一个存在的数据的这段范围加上间隙锁(左开右开);

  3. 索引上的等值查询,如果是唯一索引,加的是行锁;如果非唯一索引,需要访问该等值的左边到右边第一个不满足条件的值,这个范围加上间隙锁(左开右开,中间包含该等值);

    可以理解为,等值查询的加锁范围是从该等值左边第一个不满足条件的值开始到该等值的右边第一个值不满足条件的值的左开右开区间,同时包含该等值,如果是唯一索引,则退化成行锁;

  4. 不带等值的范围查询上,无论是否是唯一索引,范围查询都需要访问到第一个不满足条件的值为止,在这个范围加间隙锁(左开右闭),带等值的范围查询时,规则还是同上;

  5. 锁是加在索引上的,先对where条件上的用到的索引加锁,再对该行上存在的索引依次加锁。比如有表A,索引为id、a、b,当update时的条件是where a=xx时,会对索引a、id、b的顺序加锁;间隙锁只会加在where条件中的索引上,对于该索引定位到的行加的是行锁;

  6. select加的锁,如果查询的列刚好是满足覆盖索引,且覆盖索引不包括其他索引,则只会锁where条件上的索引;

以上规则需要组合起来使用,InnoDB会对扫描过的行都加上行锁和间隙锁,所以如果查询条件不是索引,就会全表扫描,并对扫到的行主键上锁,表现出来就是锁表了

在InnoDB中,一般设置innodb_thread_concurrency的值在64~128之间,=0表示不限制并发线程数量,这里的并发线程数量指的是并发查询数量,并发连接数量可以有上千个,但是并发查询数量不能太多,否则太耗CPU资源,另外,在查询进入锁等待时,并发查询数量会减一,不计入并发查询数量里,select sleep(秒)则会计入。

关于死锁

当线程出现循环资源依赖,导致多个线程互相等待的状态称为死锁,解决方案:

  • 设置超时时间 innodb_lock_wait_timeout,在InnoDB中默认是50s
  • 死锁检测,当发现死锁后,主动回滚死锁链条中的某一个事务,设置innodb_deadlock_detect=on,默认值是on

一般使用第二种,但死锁检测会消耗大量CPU资源,主要发生在对同一行进行更新的检测上,其算法是O(n),虽然在同一行更新不会造成死锁,但是当并发很高时进行检测时就会消耗大量CPU资源,解决方案有两种,一种是在中间件或者MySQL server层上,增加对同一行更新的判断,进行排队,或者将那一行改为逻辑上的多行,来分散压力

因此对同一组资源,尽量以相同的顺序访问

关于对表进行DDL语句(加字段、索引)时是否锁表

以加字段为例子:

MySQL5.6以前的给表加字段时会锁表,在此期间只能读,加字段的步骤:

  1. 对原始表加MDL写锁,禁止读写
  2. 按照原始表和执行语句的定义,重新创建一个空的临时表;
  3. 为临时表添加索引;
  4. 将原始表种的数据逐条copy到临时表中;
  5. 当原始表的所有数据都被copy到临时表后,将原始表删除,临时表更名为原始表表名,允许读写

如果5.6之前的版本要加字段,可以使用pt-osc来实现,本质上也是创建临时表,然后copy数据的过程中通过触发器触发临时表上数据的修改,最后删除原始表并更名而已。

MySQL5.6之后新增Online DDL功能,使得表不可用的时间减少,或者说让表在被修改时仍然能正常操作,加字段时的步骤:

  1. 对原始表加MDL写锁,禁止读写
  2. 按照原始表和执行语句的定义,重新创建一个空的临时表,并申请rowlog空间,然后便允许读写
  3. 拷贝原始表的数据到临时表,此时表数据的CUD操作都会放在rowlog中,此时客户端仍然可以进行操作;
  4. 原始表数据全部拷贝完成后,会将rowlog中的改动同步到临时表中,此时客户端不可操作;
  5. 当原始表中的数据都被copy到临时表后,并且copy期间客户端的所有CUD操作都同步到临时表后(同步copy期间的操作禁止读写),原始表删除,临时表更名为原始表表名;

所以其实Online DDL也会锁表的,只是锁表的时间特别短,几乎可以全程允许读写。

要注意当表的数据量比较大时,不要设置default值,如果字段带有default值,MySQL会在执行完Online DDL后,对整个表该新增字段更新默认值,导致锁表。

在对索引进行添加和删除时也是类似的步骤,只是不是使用copy的方式,而是改成了in-place方式,不需要复制数据,而是将copy的操作改成按照聚簇索引的顺序查询数据,找到需要的索引列数据,排序后插入新的索引页中。

5.6版本之后copy和in-place就都对增删字段、索引时都可用。

参考:https://segmentfault.com/a/1190000041149544

关于事务可见性的疑问

在可重复读的隔离级别下,事务A开启,操作数据D,此时会创建一个当前数据D的视图(MVCC),但此时又刚好有事务B,已经操作到这条数据,并给这条数据加了行锁,对这条数据进行操作,事务B操作完成后,释放数据D的行锁,那之前的事务A在最终修改操作数据D时,数据D的值是什么呢?

假如一开始k的值是1,autocommit=1,即单独一条SQL执行本身就是一个事务,会自动提交

另外,事务中的begin / start transaction命令,是以执行它们之后的第一个sql语句为启动开始事务,而start with consisten snapshot是以这条命令为起点开启事务

事务A 事务B 事务C
start transaction with consisten snapshot;
start transaction with consisten snapshot;
update t set k = k + 1 where id = D
update t set k = k + 1 where id = D;
select K from t where id = D;
select k from t where id = D;
commit;
commit;

上面这道题的答案是 事务A查到k的值分别是1,事务B查到k的值是3

原因:事务A之所以查到的值是1,是因为事务A开启时,事务B和C还没开启,此时的快照k=1,因此得到的值是1。事务B查到k的值是3,虽然事务C是在事务B之后开启的,感觉看不到事务C修改后的值,但是由于更新操作是先读后写的,此时的读是当前读(当前读总是读该已提交的数据的最新版本),而当前读的值是2,因此更新后k的值是3,如果不这么做,就会导致事务C更新丢失,而在同一个事务内读值,是可以读到由当前事务修改的值的,所以事务B读到的值是3。普通select语句,在可重复读情况下,为了实现一致性读,是通过读undo log实现的,如果undo log太长(可能因为更新次数太多),会导致查的很慢

题外话,如果事务A读的时候加锁,就会变成当前读,例如将事务A的select语句后面加上lock in share mode(共享锁)或者for update(排他锁),那么查到的值就是3了。如果事务B在执行更新前先select了,查到的值也是1。如果事务C是显式启动事务,在事务B select后commit前才执行commit操作,就会触发二阶段锁协议,两条更新语句同时更新一行数据,先执行的语句会对这条数据加行锁,所以事务B需要等到事务C提交后,才能执行更新操作

这个问题的关键在于要理解 MVCC原理,更新操作前的当前读,事务的隔离级别,一致性读、行锁

参考:

Mysql加锁过程详解(1)-基本知识

Mysql中的锁机制

Innodb中的事务隔离级别和锁的关系

大表优化

MySQL大表优化方案

大表建立索引

当表的数据量很大,并且TPS也很高时,建立索引或者修改表结构会很慢,甚至出现死锁。

  • 方案1:创建一张与原表结构相同的新表,在新表上创建索引,将原表改名,新表改成原表的名字,让新表承担业务,然后为老表新增索引,完了之后再交换表名,再将新表产生的数据导入到原表中。

    但是这种方式有个问题是如果要对老表的数据进行删改,会产生数据丢失,此过程可以通过建立触发器解决,有Percona Toolkit工具就可以自动完成上述过程,并且保证数据删改不丢失。

  • 方案2:在业务空闲的时候,在从库上的表建索引,等待主库和从库同步位点一致,切换主从即可。

大量数据删除

大量删除时,会锁表,业务高峰时还会CPU暴涨,导致数据库性能下降

  • 方案1:删除大表的多行数据时,会超出InnoDB block table size的限制,要做的就算减少锁表的时间,可可以选择不需要删除的数据,存到一张同名的空表里,重新命名原始表,新表使用原表名,删除原始表
  • 方案2:将批量删除的任务拆分成N个小删除任务,异步执行
  • 方案3:删除表上的索引,再删除数据,提升删除效率

CPU或IO过高可能的原因

  • 慢查询导致CPU飙升,QPS不高,查询效率低,需要扫描大量数据才能出结果导致,这种时候一般是show processlist查看当前执行的查询,kill掉慢查询对应的id
  • 慢查询,磁盘故障,不合理的配置+大量增删改导致IO升高,一般结合performance_schema查看MySQL各项指标,查看刷盘相关的参数配置是否合理,比如sync_binlog之类的

分库分表

主要是为了解决数据量太大导致查询慢(一般可以分表,主从分离),还有高并发问题(一般是分库)。

分表时一般分为水平拆分(对行,即数量,比如根据id哈希分散到不同的表)和垂直拆分(对列,即拆字段,或者直接将某些表独立成库)

分片算法

  • 时间分片、范围分片,但容易产生热点问题

  • 哈希分片,针对某一列做哈希取模,平均分配到各个分表中,如果要扩容,由于模会变,导致数据要重新哈希,停机迁移数据,这样是不行的,因此一开始要设计好,例如使用一致性哈希算法,减少迁移的数据量;

    或者分表的时候取2的n次方,这样扩容的时候也以2的n次进行扩容,这样原来的key重新取模是在原来的位置或者原来的2倍;或者换个数据库了,像上面大表优化里面提到的那样。

  • 查表法,先通过分片映射表查到要查询的分片,再到对应的分片进行查询,但是要二次查询,即使上缓存,查询性能也是一般。

Sharding Key选择问题

一般是使用最常用的查询条件做分片key;

当分库分表遇到Sharding Key冲突时,只能选择冗余数据了,或者通过将多个列组合成一个新的列来标识,或者建立映射表;

分片之后,count之类的计数就只能用一张表单独存或者记录在Redis中了,不然就只能所有分片扫一次;

数据部分

MySQL一般的集群架构是 有两台MySQL实行双Master部署,进行主备复制,注意要把备用的master的主备复制关闭,避免循环复制,另外会部署一些机子作为从库,以其中一台master为主库进行部署

数据存储和恢复

只要redo log 和 bin log能够持久化到磁盘,就能确保MySQL异常重启后,数据就可以恢复

原理

  • bin log的写入机制:事务执行过程中,先把日志写进bin log cache,事务提交时,再把bin log cache写进bin log文件(先写到文件系统的page cache,再进行持久化)中,然后把bin log cache清空。

    bin log cache每个线程自己维护,bin log的写入是一个顺序操作;

    bin log cache的大小通过binlog_cache_size控制,如果超过就暂存到磁盘;

  • redo log写入机制:原理与bin log类似,但是它是二阶段提交,有状态,事务执行过程中,redo log先是prepare状态,写入redo log buffer,再写bin log,提交事务,变为redo log commit状态;

    redo log buffer全局共用,与bin log cache不同;

一般会把sync_binloginnodb_flush_log_at_trx_commit都设置为1,即一个事务完整提交前,会刷两次盘。另一种设置是让sync_binlog=1000innodb_flush_log_at_trx_commit=2,一般是在主备复制存在很大延迟时,为了让从库的备份速度跟上主库;

为了提高刷盘效率,MySQL一般会让多个事务在一段时间内完成,或尽量让page cache里的redo log和bin log组合在一起提交,减少刷盘次数;

主从复制

一般从库设置为read only,可以避免主从切换过程的双写,实现的是最终一致性。

原理

利用MySQL中的bin-log二进制文件,该文件记录了所有sql信息,主数据库会主动把bin-log文件发送给从数据库,在从数据库的replay-log重放日志文件中利用这些信息进行恢复。

bin log分为三种格式

  • statement:记录每次执行的SQL,但由于索引选择问题或者SQL语句使用聚合函数,有可能会导致主从不一致的问题
  • row:记录的是事件,表示每条SQL语句执行后的数据信息,比如delete操作后,会记录delete事件和delete删除的行的所有字段(可设置为记录所有字段或者只记录主键);update操作会记录行数据前后的记录;insert操作会记录insert的所有字段信息,缺点是占空间,但对数据的恢复有利
  • mix(混合上面两种):由MySQL自己判断,如果会出现主从不一致,就使用row,否则使用statement

bin log上会记录每台机子的server id,用于避免循环复制

具体步骤

每个MySQL数据库上都有这三个线程,默认是主服务器写完bin log后就算事务成功,bin log复制是异步执行。

  • binlog 线程 :负责将主库上的数据更改写入bin log。之后事务线程提交事务,响应成功给客户端。

  • I/O 线程 :负责从主库上读取bin log,并写入从库的重放日志(relay log)中,主库也会创建一个log dump线程来异步发送bin log给从库;

  • SQL 线程 :在从库里,负责将读取到的relay log日志并重放其中的 SQL 语句。MySQL 5.6前只支持单线程,5.6后改为多线程,SQL线程分为container对事务进行分发,调度不同的worker线程进行执行,分发策略:1、更新同一行的两个事务,必须被分发到同一个worker;2、同一个事务不能被拆开,必须在同一worker中执行

    从库在同步bin log时,必须保证bin log的顺序,才能确保数据一致性。

MySQL主从复制
MySQL主从复制

默认情况下,由于是异步复制,无法保证数据第一时间复制到从库上,但如果采用同步复制,即等从库复制完主库的bin log后才响应给客户端,性能就太差了。

在MySQL 5.7后的版本,增加半同步复制(Semisynchronous Replication),事务线程不用等到所有都复制成功才响应,只要一部分复制响应回来后即可响应给客户端,比如一主二从,等一从成功即可成功。配置rpl_semi_sync_master_wait_no_slave表示至少等待多少个从库复制成功才算成功。rpl_semi_sync_master_wait_point表示主库执行事务的线程是提交事务前等待复制(默认),还是提交事务之后等待复制

主备延迟

当备库重放日志的速度小于主库产生bin log的速度,会出现主备延迟,可能的原因:

  • 主备机器配置不一致,备库机器性能较差
  • 备库压力大,比如在备库上进行SQL分析、大量查询、大表的DDL,主库上的长事务操作等,消耗大量CPU资源导致

默认情况下,异步复制也会带来的读延时问题,可以采取 一主多从,主写从读,分散压力;利用好缓存中间件;持久化层的处理。

在MySQL 5.6后的版本,可以设置slave_parallel_workers来决定从库在进行重放时工作的线程数,一般设置在8~16,以通过并行重放的目的加快主从复制速度。

当MySQL集群搭建采用一主多从时,最好采用GTID模式来实现一主多从的切换。

主备切换策略

  • 可靠性优先:前提,备库是只读的,首先,备库持续判断与主库同步间的延迟时间,如果小于可接受的值,主库改为只读,主库等待备库同步延迟时间降为0,备库改为可读写,业务切换到备库,业务不可写的时间取决于主库等待备库同步数据的延迟时间
  • 可用性优先:步骤与上面的类似,只是主库改为只读后,不等待备库同步完数据,就切到备库,此时数据会不一致,后面再自己根据bin log手动调整更正

MySQL的高可用(通过主库发生故障时切到从库),是依赖主备复制的,主备延迟时间越小,可用性越高

库内表的复制

将一张表里的数据导出到文件,再写回原表,以下两种方式都可以跨引擎

  • 使用mysqldump,将表里的数据转成insert语句

    mysqldump -h$host -P$port -u$user --add-locks = 0 --no-create-info --single-transaction --set-gtid-purged = OFF db1 t --where ="a>900" --result-file =/client_tmp/t.sql
    
    – single-transaction:,在导出数据的时候不需要对表db1.t加表锁,而是使用STARTTRANSACTIONWITHCONSISTENTSNAPSHOT的方法;
    – add-locks:设置为0,表示在输出的文件结果里,不增加"LOCKTABLEStWRITE;";
    – no-create-info:不需要导出表结构;
    – set-gtid-purged=off:不输出跟GTID相关的信息;
    – result-file:指定了输出文件的路径,其中client表示生成的文件是在客户端机器上的。
    - skip-extended-insert:将每行数据输出成一条SQL语句
    
    使用mysql -h$host -P$port -u$user $db -e "source /client_tmp/ t.sql"
    
  • 导成CSV文件,在select语句后面加上into outfile `filepath` ,使用load data infile `filepath/filename` into table $db.$table,该语句也会被传到备库,如果备库没有该文件就会报错,因此在执行完该语句后,还要再执行load data local infile `filepath/filename` into table $db.$table,另外,该语句不会导出表结构,表结构需要另外导出

读写分离

主数据库负责写,从数据库负责读,从而缓解锁的争用、节约系统开销,提高并发量

读写分离常用代理方式来实现,应用层不需要感知后端的MySQL集群部署结构,直接访问代理层,代理层接收应用层传来的读写请求,然后决定转发到哪个MySQL

由于主从库之间可能发生主备延迟,导致在查从库的结果会慢于主库,解决方法

  • 将查询请求分类,对查询结果严格的请求直接发到主库上
  • 查询前先进行sleep操作(性能不好)
  • 等主库位点方案:在从库使用命令 select master_post_wait(file, pos[ , timeout]),参数file和pos指主库上的文件名和位置,timeout表示等待时间,正常的返回结果是一个正整数M,表示从命令开始执行到应用完file和pos表示的bin log位置,执行了多少事务,即如果M >=0 ,表示从库已与主库同步,可以接受查询,如果等待超过了timeout时间,就去查主库
  • GTID方案:与等主库位点方案类似,都是要知道命令执行过程中事务执行的数量,使用命令 select wait_for_executed_gtid_set(gtid_set, 1),参数gtid_set表示从库执行的事务是否包含该gtid_set,包含返回0,说明主从已同步过,可以执行查询,超时返回1,超时则查主库。该方案的难点在于gtid_set的获取,需要修改MySQL代码,让其在事务提交后返回gtid值

分区表

  • 建立分区表语句,demo是以范围做分区的,也可以使用hash分区、list分区

    1
    2
    3
    4
    5
    6
    7
    8
    
    CREATE TABLE `t` (
    	`ftime` datetime NOT NULL, 
    	`c` int(11) DEFAULT NULL, 
    	KEY (`ftime`)) ENGINE = InnoDB DEFAULT CHARSET = latin1 PARTITION BY RANGE (YEAR( ftime)) 
    	(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
        PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
        PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
        PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
    
  • 分区表会按分区存储在对应的文件里,第一次访问这张表时,MySQL会对所有分区表的文件进行打开操作,但是打开文件的个数是有限制的,如果分区表太多,会导致SQL语句无法执行

  • 分区表对于server层来说是一张表,DDL操作时会对所有分区表上锁,导致后面落到具体分区表的语句阻塞

  • 分区表对于引擎层来讲是多张表,因此在进行一些DML语句时,只会在对应分区表加间隙锁,不会影响其他分区表

  • alter table $tableName drop partition xxx用于删除分区表,与drop 整张普通表类似,但是速度快,因为只删除了部分数据

  • 在跨分区查询数据时,会比普通表慢

MySQL主从复制

MySQL健康状态检测

当MySQL的查询并发数满了之后,会导致不可用,后面的操作都会被阻塞

检测方法

  • 查询检测:在系统库里建一张表,比如叫health_check,里面只放一个数据,然后定期执行 select * from mysql.health_check; 判断执行的SQL语句是否不可用,当语句超时则表示不可用
  • 更新判断:由于当磁盘满了之后,MySQL仍然可读,但是bin log却写不进去,导致更新语句和事务 commit会被阻塞,在上面表的基础上,增加一个timestamp字段,每台MySQL的serverId作为主键插入表中,MySQL每次执行把当前时间更新到对应的行上,update mysql.health_check where server_id=xx set t_modified = now(); 主备都需要开启检测。当语句执行超时时表示不可用

由于每个执行请求都有可能获得IO资源,所以有时检测请求执行成功了,但是此时系统资源即将被耗尽了,已经可以进行主备切换了,但是仍然要在下次检测才能知道,因此需要判断多每次IO请求的时间,通常是检测(select)performance_schema表的信息

高可用方案MHA

MHA是一套保证MySQL实现故障切换和主从提升的高可用解决方案,故障切换时能在30s内自动完成,最大程度的保证数据一致,支持跨存储引擎,对原有MySQL库无性能影响,整个故障转移过程对应用程序完全透明。

MHA里有两个角色一个是MHA Node(数据节点)另一个是MHA Manager(管理节点)。

  • MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群,也可以部署在一台slave节点上。
  • MHA Node运行在每台MySQL服务器上,MHA Manager会定时探测集群中的master节点,当master出现故障时,它可以自动将最新数据的slave提升为新的master,然后将所有其他的slave重新指向新的master。

MHA架构
MHA架构

MHA方案采用半同步复制的方式,所以MySQL的版本要求至少是5.5,半同步复制机制保证master出问题的时候,至少有一台slave的数据是完整的,但在超时的情况下也会临时切换异步复制,保障业务的正常使用,直到一台slave追上同步进度,再继续切换成半同步复制模式。

MHA还可以修复多个slave之间的日志差异,使得所有salve的数据保证数据最终一致性,保证至少可以从中选出一个master。

故障转移流程

  1. MHA验证复制设置以及确认当前master状态
  2. 监控master,检测到master宕机
  3. 再次验证slave的配置
  4. 开始恢复一台新master
  5. 从宕机的master节点上通过SSH保存其bin log到Manager
  6. 根据配置文件来选举出新的master,或者将含有数据同步最新位点的slave提升为master
  7. 根据老master bin log生成差异日志,应用到新master上
  8. 将其他的slave连接到新的master进行复制

快速切换master流程

  1. MHA验证复制设置以及确认当前master状态
  2. 确认新的master
  3. 停止当前master的写操作
  4. 等待其他slave追上当前master,直到数据复制进度无延迟
  5. 确保新master可写
  6. 让其他slave指向新master

其他

自增主键

  • 自增id不一定是连续的,可能会产生空洞;比如 插入操作出现唯一键冲突,自增值也会+1;事务回滚时,自增值不会回滚;

  • MyISAM自增id的下一个值是存在表结构里的,InnoDB是放内存的,在MySQL 8.0以前,自增值并不会持久化到磁盘,每次重启后,自增值会被清空,在第一次读表的时候会把最大id给读出来+1,达到恢复原来的自增值;8.0后是记录在redo log里,重启后通过持久化的值来恢复

  • 自增的两个重要参数auto_increment_offsetauto_increment_increment都是系统参数,默认值为1

  • 设置 innodb_autoinc_lock_mode,=1时,普通insert语句会执行完之后释放,批量insert时会等到所有批量insert的SQL都结束的时候才释放锁,原因是如果bin log不是row,备库在复制时产生的行的id可能于主库的不一致问题;=2时,自增id锁每次获取完就会释放。

    如果选择=1,在批量insert时性能就会很差,MySQL的优化是让insert的语句不使用连续的自增id,不过这样就会让自增id不连续了。所以一般的操作是选择=2,bin log=row

  • 理论上自增id是无限的,但是因为字段的类型已经限定了最大的位数,比如如果id使用unsigned int是4个字节,上限就是2^32 -1,当达到上限后,自增值不变,就会导致报重复主键的错,所以如果表的上限需要比较大,需要设置成unsigned bigint

  • 如果没有明确设置主键,innoDB会默认给一个row_id,虽然实际上是一个unsigned bigint,但是只用到了6个字节,所以长度是0 ~ 2^48-1,子增值达到上限后的下一个值是0,此时在insert会覆盖原有的行

  • 当产生唯一键冲突时,除了会报错,还会加锁(主键+行锁,普通索引+间隙锁),然后在回滚时才释放,当出现唯一键冲突时,如果有多个事务同时插入,容易造成死锁(双方都在等待对方的间隙锁释放导致)

  • insert into ... on duplicate key update ... ,表示插入一行数据,如果出现唯一键冲突,就执行后面的update语句,该更新语句只会修改跟第一个索引冲突的行

Xid

  • Xid是 server层维护,表示一个事务id,存在bin log中,可以作为bin log和redo log中同一事务的关联id,让innoDB事务和server层做关联。

  • 由全局变量global_query_id赋给Query_id,Query_id+1后赋值给Xid,作为事务开始的第一条语句的id。

  • 由于global_query_id是内存变量,MySQL重启后会被清零,但是重启后会生成新的bin log,所以同一bin log不会出现两个相同的Xid

  • global_query_id达到上限2^64 - 1后,会从0开始计数,所以同一数据库里可能同时存在相同的Xid

trx_id

  • trx_id不同于Xid,trx_id是由InnoDB维护,用在事务可见性方面的,比如MVCC,视图一致性
  • 普通只读语句不分配trx_id(而是临时算的,会比较大,主要是为了区分读写语句),读写语句才会分配 trx_id,由max_trx_id + 2(至少,比如update,实际上要先当前读在update的,所以是+2)
  • max_trx_id会持久化,重启不会变0,上限是2^48-1,然后从0开始计数

thread_id

  • 系统保存全局变量thread_id_counter,每新键一个连接,thread_id_counter + 1后赋给thread_id,作为线程id
  • 上限是2^32-1,达到上限后重置为0

JDBC

SUN的 JDBC 是一套接口,而实现是各个数据库厂商的驱动包,因此使用了桥接模式

DriverManager注册驱动包,com.mysql.jdbc.Driver类中的static块会创建驱动实例,因此只需要把驱动字节码加载到JVM里即可,Class.forName(“com.mysql.jdbc.Driver”);

Connection conn = DriverManager.getConnection(url, username, password)获取连接

Statement stmt = con.createStatement(); 之后使用stmt的方法执行SQL语句即可,返回ResultSet

ResultSet下标从1开始

使用Connection类的setAutoCommit(false) 方法来实现事务,以这个开始,Connection类的commit()方法提交,Connection类的rollback()方法回滚

最后关闭ResultSet、Statement和Connection

数据库连接池

链表实现,在使用连接对象之前,先创建好一定数量的连接对象,以链表的形式连接,从端首取,用完回到段尾。

当池子中没有连接对象可取时,就让其先等待,如果等待超时还没有回获取到连接对象,就新建一个连接对象让其使用,用完后销毁该创建的对象

连接池负责管理、监控和维护这些连接对象

连接池单例

连接池需要保证线程安全

参考

MySQL ACID及四种隔离级别的解释

CyC2018/CS-Notes/MySQL

Innodb中的事务隔离级别和锁的关系

极客时间 - MySQL实战45讲

MySQL中的 redo 日志文件

MySQL的日志 - redo log

MySQL日志15问

MySQL 中的WAL机制

后记

极客时间 - MySQL实战45讲真的是质量很高的讲MySQL的课程,非常推荐

Built with Hugo
Theme Stack designed by Jimmy