MYSQL.md

Posted by lizhao on 07-09,2019

MYSQL

通用

  1. 如果内存够,就要多利用内存,尽量减少磁盘访问。
  2. 在保证逻辑 正确的前提下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一。

第一

  1. 连接器
  2. 查询缓存
  3. 分析,词法分析
  4. 优化器
  5. 执行器
  6. 底层引擎

更新操作

redolog、binlog

老板赊账白板的概念--中间层

物理日志、逻辑日志

循环写(checkpos-writepos)、追加写

crash-safe,异常关闭的恢复

二阶段提交--防止二份日志不对

取数据--判断在内存中--获取数据--修改值并写入--更新日志(prepare)--写入binlog--commit

备份时间,影响了回滚数据的时间

三、事务隔离

ACID 原子、一致、隔离、持久性

脏度、不可重复度、幻读

读未提交、读提交、可重复度、串行化

事务隔离实现、mvcc视图、带上版本号

避免长事务,

  1. set autocommit=0
  2. 只读情况,不加事务
  3. 长事务监控报警
  4. 分析日志

四、索引

数据结构,hash、数组、b+树

主键索引(聚簇索引)和非主键索引(二级索引 )

根据非主键索引查询数据,只能拿到主键的值,根据值在回表,进行查询

索引维护、没有排序,有可能是插入情况,导致页冲突(分裂)

最好是自增索引、最好是int或者长度小的数据

最好不要重建主键索引

五、索引下

覆盖索引:普通索引中包含了主键、这样就不能回表查询,比如主键是id,身份证.名字为联合索引,此时根据身份证查询名字和id就不用回表

最左前缀,ab作为一个联合索引,会匹配a的数据,并且以a的数据为主进行排序

索引下推,当ab为索引,where语句中,只要包含a、b的部分都可以在ab索引树下判断,不满足条件就不回表

问题:ab已是联合索引,是否有必要创建ca,cb索引 ca没必要,cb有必要

六、全局锁、表级锁和mdl

全局锁就是对整个数据库实例加锁,典型使用场景是,做全库逻辑备份

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时 候,导数据之前就会启动一个事务,来确保拿到一致性视图。而由于 MVCC 的支持,这个过程 中数据是可以正常更新的。

一种是表锁,表锁的语法是 lock tables … read/write

一种是元数据锁(meta data lock,MDL)。 当要对表做结构变更操作的时候,加 MDL 写锁

,我给你留一个问题吧。备份一般都会在备库上执行,你在用–single-transaction 方法做 逻辑备份的过程中,如果主库上的一个小表做了一个 DDL,比如给一个表上加了一列。这时 候,从备库上会看到什么现象呢?

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT
/* other tables */ 
Q3:SAVEPOINT sp; 
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */ 
Q5:SELECT * FROM `t1`;
/* 时刻 3 */ 
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */
在备份开始的时候,为了确保 RR(可重复读)隔离级别,再设置一次 RR 隔离级别 (Q1);
启动事务,这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致 性视图(Q2);
设置一个保存点,这个很重要(Q3);
show create 是为了拿到表结构 (Q4),然后正式导数据 (Q5),回滚到 SAVEPOINT sp,在 这里的作用是释放 t1 的 MDL 锁 (Q6。当然这部分属于“超纲”,上文正文里面都没提到。
DDL 从主库传过来的时间按照效果不同,我打了四个时刻。题目设定为小表,我们假定到达 后,如果开始执行,则很快能够执行完成。
参考答案如下:
1. 如果在 Q4 语句执行之前到达,现象:没有影响,备份拿到的是 DDL 后的表结构。
2. 如果在“时刻 2”到达,则表结构被改过,Q5 执行的时候,报 Table definition has changed, please retry transaction,现象:mysqldump 终止;
3. 如果在“时刻 2”和“时刻 3”之间到达,mysqldump 占着 t1 的 MDL 读锁,binlog 被 阻塞,现象:主从延迟,直到 Q6 执行完成。
4. 从“时刻 4”开始,mysqldump 释放了 MDL 读锁,现象:没有影响,备份拿到的是 DDL 前的表结构。

行锁

2阶段提交

一个事务中,在遇到某个更新语句的时候,就要获取锁,并在 commit之后释放锁

代码中,事务的顺序需要考虑,先扣钱、再出票

死锁和死锁检测

死锁

解决:

  1. 设置超时时间
  2. 发起超时检测
  3. 控制并发量
  4. 队列

###问题 我给你留下一个问题吧。如果你要删除一个表里面的前 10000 行数据,有以下三种方法 可以做到: 第一种,直接执行 delete from T limit 10000; 第二种,在一个连接中循环执行 20 次 delete from T limit 500; 第三种,在 20 个连接中同时执行 delete from T limit 500。

解答:第一种,长事务占据时间过长 第三种,会有争夺现象 第二种相对好点

事务到底是隔离的还是不隔离的?

快照

每个事务都会有自己的事务id(transaction id),trx_id,每一行都有多个版本,对应不同的trx_id,能通过undo log获取到各个版本的信息

读取数据

可重复读:当前事务id为x,读的数据版本id要小于x,一致性读

读提交:只要这句话之前,其他事务提交过了就能看到

对于可重复读,查询只承认在事务启动前就已经提交完成的数据; 
对于读提交,查询只承认在语句启动前就已经提交完成的数据;

更新数据

更新数据都需要拿到最新的版本,然后进行更新,叫做当前读

写操作有加锁,如果没释放,会阻塞

undo log(回滚日志)

想要获取到不同版本的行数据,就是根据现在的版本号,执行undo log,就能往回找到数据

九、普通索引和唯一索引

主键,唯一索引,普通索引(可以当成联合索引)

在读的操作中

唯一索引:命中索引上的唯一键,然后拿到主键,回表

普通索引:命中左前缀,然后拿到当前节点,继续往下匹配,拿到节点数组(不过是唯一,肯定就不处理),回表

时间差不多

写操作

唯一索引:需要判断唯一性,所以一定要加载到内存中进行更新

普通索引:在内存中就可以直接更新,不在的话,就先写到change buffer中

1. Page 1 在内存中,直接更新内存;
2. Page 2 没有在内存中,就在内存的 change buffer 区域,记录下“我要往 Page 2 插入一 行”这个信息
3. 将上述两个动作记入 redo log 中(图中 3 和 4)

redo log 主要节省的是随 机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消 耗。

change buffe

  1. 针对普通索引
  2. 如果数据在内存中,则直接更新
  3. 如果数据不在内存中,则先存到change buffe,
  4. 有查到这条数据、系统定时、正常关闭数据库的时候就会将这个更新记录写到数据库中(purge)
  5. 在读多写少的场景比较好,因为有查询到某个数据,说不定就要回写,比较慢

十、选错索引

优化器

会判断sql需要查找的行数,来选择索引

explain

explain select 分析语句

SHOW VARIABLES LIKE '%long_query%'
SET long_query_time=0; 

set sql_long_query=0; 
select * from t where a between 10000 and 20000; /*Q1*/ 
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

对于由于索引统计信息不准确导致的问题,你可以用 analyze table 来解决。

而对于其他优化器误判的情况,你可以在应用端用 force index 来强行指定索引,也可以通过修 改语句来引导优化器,还可以通过增加或者删除索引来绕过这个问题。

你可能会说,今天这篇文章后面的几个例子,怎么都没有展开说明其原理。我要告诉你的是,今 天的话题,我们面对的是 MySQL 的 bug,每一个展开都必须深入到一行行代码去量化,实在 不是我们在这里应该做的事情。

十一、字符串加索引

整个字符串

前缀索引

可以取字符串的前n个字符

会影响覆盖索引,肯定要回表

反序+前缀索引

hash值

使用crc32计算出hash值

十二、数据库抖动

没有规律的卡顿,大部分都是因为flush操作

flush

数据库的操作都是在内存中,出现一些特殊情况时,就要将这些操作同步到硬盘上,就叫flush

特殊情况

  1. redolog写满了,wirte追到checkpoint了
  2. 内存脏页过多
  3. 空闲时间
  4. 关机的时候

删除表数据

删除过程

删除过程没有把使用的内存删掉。而是将这个位置的数据标记为可用状态,你下次插入数据,可以往这里插

更新内存方法

在删除表的时候,将表重建(临时表,然后移动)

alter table A engine=InnoDB 命令来重建表

这个方法很屌,

1. 建立一个临时文件,扫描表 A 主键的所有数据页;
2. 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;
3. 生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中,对应的是 图中 state2 的状态;
4. 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同 的数据文件,对应的就是图中 state3 的状态;
5. 用临时文件替换表 A 的数据文件

十四、count

count(*)

MyISAM有存总数,所以很快

MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个 数,效率很高;
而 InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出 来,然后累积计数。

。在保证逻辑 正确的前提下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一。

不同的count

对于 count(主键 id) 来说,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。
对于 count(1) 来说,InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一 个数字“1”进去,判断是不可能为空的,按行累加。
单看这两个用法的差别的话,你能对比出来,count(1) 执行得要比 count(主键 id) 快。因为从 引擎返回 id 会涉及到解析数据行,以及拷贝字段值的操作。
对于 count(字段) 来说:
1. 如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能 为 null,按行累加;
2. 如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出 来再判断一下,不是 null 才累加。
也就是前面的第一条原则,server 层要什么字段,InnoDB 就返回什么字段。
但是 count(*) 是例外,并不会把全部字段取出来,而是专门做了优化,不取值。count(*) 肯定 不是 null,按行累加。

缓存优化,实时知道count值

在代码中使用其他中间件

redis,有可能不准

在数据库层面

用一张表,使用事务

十五

崩溃恢复

崩溃恢复时的判断规则。

  1. 如果 redo log 里面的事务是完整的,也就是已经有了 commit 标识,则直接提交;
  2. 如果 redo log 里面的事务只有完整的 prepare,则判断对应的事务 binlog 是否存在并完 整: a. 如果是,则提交事务; b. 否则,回滚事务。

如果碰到既有 prepare、又有 commit 的 redo log,就直接提交; 如果碰到只有 parepare、而没有 commit 的 redo log,就拿着 XID 去 binlog 找对应的事 务。

数据库的2阶段提交

graph TB
A(存到内存)-->|节点1|B(存到redologprepareing状态)
B-->|节点2时刻A|C(存到binlog)
C-->|节点3时刻B|D(comit)

时刻A出现问题,回滚

时刻B出现问题,完整的话,事务会提交

binlog

statement 格式的 binlog,后会有 COMMIT; row 格式的 binlog,后会有一个 XID event。

redolog和binlog的关联

根据XID

redolog_buffer

所以,redo log buffer 就是一块内存,用来先存 redo 日志的。也就是说,在执行第一个 insert 的时候,数据的内存被修改了,redo log buffer 也写入了日志。 但是,真正把日志写到 redo log 文件(文件名是 ib_logfile+ 数字),是在执行 commit 语句 的时候做的。

十六、order by

select city,name,age from t where city='杭州' order by name limit 1000 ;

索引为city

查询、回表、取数据、排序、返回

索引为city(设置排序行大小max_length_for_sort_dat)

查询、回表、取数据、排序、回表、返回

索引city+name

查询、回表、取数据、返回

索引city+name+age

查询、取数据、返回

十七、

十八、索引的使用

正常的使用索引字段进行查询会走b+树进行搜索,如果对函数进行运算/函数等操作,就不会走b+树搜索。因为破坏了有序性

隐式类型转换

where intval= '643' 字符串转数字,相当于使用了Cover(intval)

隐式字符编码转换

相当于走了函数Cover(code)

十九、查询一行很慢的原因

mdl锁

行锁

flush

不走索引

当前读

一致性读,需要根据undo log往前回滚

二十、幻读

概念

一次事务中,二次查询相同的sql,获取的结果行数不一致

一致性读不会有问题

当前读有问题

问题

语义不一致

数据不一致

间隙锁

在所有行数之间加入加入一个锁

这个锁和"要插入"冲突