mysql-note-2

optional
128 阅读14分钟

mysql_booker

key

select_update

  • select || update语句执行过程 developer.aliyun.com/article/942…
  • select: 尽量使用长链接、select-cache(链接断开时清空)
  • update: change_buffer
    • mysql_reset_connection 初始化长连接
    • query_cache_type=DEMAND根据需要使用查询缓存;缓存一致性实现=>cache_aside
    • WAL => Write ahead log，
    • InnoDB的redolog和Server的binlog的对比, Server能对外提供其他服务
    • cash-safe的解决=>redolog
    • 2pc=>2阶段提交的过程： redolog(prepare) -> binlog -> redolog(commit)
    • innodb_flush_log_at_trx_commit=1;表示每次事务的redolog都直接持久化到磁盘
    • sync_binlog=1;表示每次事务的binlog都持久化到磁盘

redolog-binlog对比

  1. redolog是InnoDB引擎特有的；binlog是MySQL的Server层实现的
  2. redolog是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是原生语句
  3. redolog是循环写的，空间固定会用完；binlog是可以追加写入的
  4. redolog的状体变化：redolog in prepare=>binlog=> redolog in commit

transaction

  • ACID
  • 脏读（dirtyread）、不可重复读（non-repeatableread）、幻读（phantomread）
  • 事务与MVCC 和 next-key-lock的关系
  • undolog日志，MVCC，事务视图(read-view)
  • setautocommit=1避免长事务(事务启动后长时间不提交);
  • 一致性读(可重复性读问题怎么解决)？ MVCC

长事务解决

  1. 应用端: 开发框架启动自提交:set autocommit =1
  2. 应用端: 拆分语句,减少执行时间
  3. mysql: 监控information_schema.Innodb_trx表
  4. mysql: 监控information_schema.Innodb_trx表

index

  • index数据结构对比:hash,btree,btree+; 跳表(不方便动态的增加复合索引)，LSM树
    • 聚簇索引（clusteredindex);二级索引（secondaryindex）
    • 回表查询,缩影覆盖
    • 页分裂&页合并=>要求建表语句里一定要有自增主键(因为索引是有序结构)
    • 最左前缀=>最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符；例如like 'x%'
    • 索引下推=>MySQL5.6引入的索引下推优化（indexconditionpushdown)

lock

  • 全局锁/表锁/行锁
  • 全局锁: 全局锁主要用在逻辑备份过程中
    • MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock(FTWRL); client断开连接就解锁
    • 数据备份工具mysqldump –single-transaction,
    • 设置从库只读 set global readonly=true
  • 表锁
    • 手动加的语法是lock tables...read/write->lock tables t1 read and unlock tables,
    • 另一类表级的锁是MDL(meta data lock);在MySQL5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁;系统自动加锁(你不用管...)
  • 行锁:
    • 两阶段锁协议: InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放
    • 死锁 & 死锁检测
    • 一个是view。它是一个用查询语句定义的虚拟表
    • 一致性读视图 consistent read view, InnoDB在实现MVCC时用到的一致性读视图(包括当前已经启动但是还没有提交事务id的数组)
    • 更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读当前读”(current read)。
    • sql加共享锁 lock in share mode=>一致读
    • sql加排他锁:for update=>当前读

死锁检测的办法

  1. 设置innodb_lock_wait_timeout=50s锁超时避免死锁
  2. 开启死锁检测:innodb_deadlock_detect=on;发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行.

key

  • InnoDB的数据是按数据页为单位来读写的;在InnoDB中，每个数据页的大小默认是16KB;(索引节点包含多个数据页,数据页包含多个记录)
  • change buffer
  • 普通索引与唯一索引的区别(select,update2个方面)
  • analyze table t命令，可以用来重新统计索引信息
  • 采用force index(x)强行选择一个索引
  • MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引
  • InnoDB用缓冲池(buffer pool）管理内存
  • InnoDB刷脏页的控制策略
      1. innodb_io_capacity建议设置成磁盘的IOPS,它会告诉InnoDB你的磁盘能力。
      1. innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%
  • alter table A engine=InnoDB命令来重建表??? rename
  • analyze table t其实不是重建表，只是对表的索引信息做重新统计，没有修改数据
  • optimize table t等于recreate+analyze;

changebuffer

  1. InooDB会将更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作.
  2. changebuffer,系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中,也会执行merge操作
  3. innodb_change_buffer_max_size=50=>表示change buffer的大小最多只能占用buffer pool的50%
  4. 唯一索引的更新不能使用changebuffer，实际上也只有普通索引可以使用;
  5. 普通索引和changebuffer的配合使用，对于数据量大的表的更新优化还是很明显的
  6. 如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭changebuffer

  • count_: 对于count()这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。(=>使用count_*统计,执行的过程涉及索引的选择,执行时的操作)

  • order-by

    • 内存排序:sort_buffer_size内存排序的缓存大小
    • 文件排序:
    • 全字段排序-rowId排序(<orderKey,rowid>=>需要回表查询需要的数据)

不使用索引的情况

  1. 索引使用了函数
  2. 索引被隐式类型转换
  3. 索引编码的字符集不一致
  • lock算法
    • record_lock,gap_lock,next_key_lock
    • record_lock(行锁)=>锁之间同步;[某一行]
    • gap_lock(间隙锁)=>保护这个间隙，不操作间隙的值,(前开后开)
    • next_key_lock(gap_lock + record_lock合称next_key_lock):前开后闭区间(前开后闭];(定义gao的边界)

mysql加锁理解

  1. 原则1：加锁的基本单位是next-key lock。希望你还记得，next-keylock是前开后闭区间。
  2. 原则2：查找过程中访问到的对象才会加锁。
  3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-keylock退化为行锁。(等值查询+唯一索引)
  4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-keylock退化为间隙锁。(等值查询+普通索引)
  5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。(范围查询=>(],包含后边界)。
  1. 加锁的基本单位next_key_lock,是(]; gap_lock是();行锁是等值；gap_lock + 行锁和合称next_key_lock;
  2. select 的对象都加锁
  3. 等值查询可以退化为行锁或gap_lock，主要看是唯一索引还是普通索引
  4. 范围查询都是(] => 这样是为了锁定边界。不是bug...不然边界可能被修改....
  5. 加锁的前提是:事务隔离级别=重复读; 读提交没有gap_lock

可重复读与读提交的那些区别

  1. RR支持gap_lock; 加锁的级别不一样..
  2. 都是只能读到已经提交的数据; RR是MVCC读; RC是可以读到所有提交
  3. gap_lock要时刻注意死锁的场景...

死锁的场景

insert/delete/update高并发操作; gap_lock导致..

session-ASession-B
begin;
select * from t where id =9;		..
..begin;
select * from t where id =9;
insert into t values(9,9);..
..insert into t values(9,9);

id是普通索引,session-A和Session-B都对9的间隙加锁,例如(5,10)加2个gap_lock; 加锁不冲突;但是更新时竞争对方锁造成死锁.

  1. id存在但不是唯一索引;或者不存在,总之加锁是间隙锁.
  2. sessionA,sessionB是不同的连接;高并发导致同时操作
  3. 竞争死锁..

有的公司的配置RC+bin_log_format=row

  1. RC读提交=>关闭间隙锁,防止死锁出现
  2. 读提交可能出现幻读;同时为了解决数据和日志不一致的情况bin_log设置为row; statement只会记录语句

幻读场景...

数据表t(id,d)=>(1,5)

session-Asession-B
begin; select * from t where d=5 for update.
.insert into t (100,5);
select * from t where d=5 for update.

两次读到的数据不一样...(没有间隙锁,插入就能读到).

binlog的三种日志格式:Statement,row,mixed

show variables like '%binlog_format%';

  • log

    • 2阶段提交:redolog-prepare;binlog;redolog-commit

  • 数据库删库的结局方案...

    • binlog_format=row,binlog_row_image=FULL => 数据恢复使用
    • sql_safe_updates=on,自动检查delete/update里面有没有where+索引;
    • sql审计=>第三方工具...
    • 开发是有DML权限...不具有DDL权限...
    • 删库的解决数据库层的解决方案...

删除数据表

  1. delete from t where id >0; bin_log=row
  2. truncate/drop table=>数据不能恢复;因为即使bin_log=row,但是记录的是sql语句statement(..反复确认啊)

mysql-binlog命令的基本使用

mysql数据恢复做成自动化工具研发设计.....(这个挑战牛逼....)

  • kill
    • kill query id 杀死语句
    • kill connection id 杀死连接
    • mysql_store_result 和 mysql_use_result对比
      • mysql_strore_result:查询结果使用本地缓存,sending to client=服务端网络栈满了
      • mysql_use_result: client读一行处理一行
    • show engine innodb statue 查看BufferPool命中率=>Buffer pool hit rate=99/100

select大数据不会OOM

  1. server不会缓存全部的结果集,更具网路栈大小边读边处理
  2. LRU内存算法的分区更新(..有点偏了...)
  • join的2中查询算法
    • nested-loop-join(NLJ;嵌套loop的join):驱动表全表扫描,被驱表使用索引查询(像是嵌套使用了loop)
    • Block-nested-loop-join(BNL):使用join_buffer分块操作; 查询流程对比
    • 小表做驱动表=>怎么理解小表
    • MRR(multi range read)算法
    • BKA(batched key access)算法(BNL转BKA=>被驱动表的关联字端增加索引)

join的2个查询算法的对比

  1. index-nested-loop-join:使用index实现的子查询
  2. block-nested-loop-join:使用了join_buffer分区操作
  • 临时表
    • create temporary table t like tx;临时表只能被当前session访问，会自动删除
    • binlog_format=row,binlog不记录临时表的的所有操作
    • union / union all 流程对比; union需要使用临时表去重
    • group by 流程; 使用索引优化(能够实现直接排序...)
    • 解释直接排序...
    • generated clolumn机制

group by优化

  1. 如果对group by语句的结果没有排序要求，要在语句后面加order by null;(group by默认有排序要求)
  2. 尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary和Using filesort => 索引是有顺序的,可以直接排序,查询只需要使用sortbuffer;
  3. 如果groupby需要统计的数据量不大，尽量只使用内存临时表;也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表;
  4. 如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果

(1)group by 默认要求排序，使用order by null禁止排序; (2) group by 需要使用use temporary临时存储统计数据, 再使用use filesort 对临时表排序; 再返回结果; (3) 如果group by 使用了索引,索引以已经有序..只需要使用sort buffer就可以....

临时表优化查询的方法...

  1. create temprary t
  2. alter t add index(x)
  3. insert t selec * tt where x>0
  4. 使用临时表查询....

sortbuffer, 内存临时表，join_buffer 对比

  • 数据库自增id有哪些?
    • PK:unsigned_int(32),big_int(64)
    • row_id:InnoDB系统自增row_id;
      • InnoDB没有主键时插入
      • 计算方式row_id(48),循环利用
      • row_id重复则覆盖....(还是要有自己的PK)
    • Xid,binlog/redolog里面标记事务的一个id,xid通过内存变量获取重启清零,mysql重启生成新的binlog,对于同一个binlog,xid是唯一的
    • trx_id
    • thread_id

xid,trx_id对比

  1. xid是Server维护,Innodb使用;是为了redoLog,binlog之间的配合使用
  2. trx_id是InnoDB自己维护的
  3. trx_id是为了支持MVCC,通过一致性视图与当前行的Trx_id对比
  4. trx_id(48和row_id一样大小会循环利用); 持久化存储,重启不清零

auto_increment(id);

  1. insert into t (id) value(0)
  2. 持久化存储的redolog文件
  3. 不连续的原因 1) 不是连续插入 2) 唯一索引插入失败 3) 事务回滚 4)防止高并发重复
  • insert 语句加锁的情况
    • insert 并发/insert+delete

interview

isolation-level

隔离级别-4

隔离级别脏读（Dirty Read）不可重复（NonRepeatable Read）幻读（Phantom Read）
Read uncommitted可能可能可能
Read committed不可能可能可能
Repeatable read不可能不可能可能
Serializable不可能不可能不可能

隔离级别与异常-3

  • 脏读: 读到没有commit的数据;
  • 重复读: 读到都是commited的数据,但是同一个事务中第二次读时被updated|delete(读到的数据值被修改)
  • 幻读: 读到都是commited的数据,但是同一个事务中第二次读时被insert(读到的数据量被修改)

那么RR级别下,幻读怎么解决...

幻读的解决方式: （RR+mvcc+gap_lock）

隔离级别与日志

  • RC+row,方便观察到重复读的情况,能查看到数据值的变更记录
  • RR+mixed(row+statement)

mvcc

optimistic-pessimistic

  1. 悲观锁:serializable,并行事务完全串行执行,读写都是排他属性
  2. 乐观锁:基于数据版本(version)做update

获取锁失败时的处理情况: dicard(丢弃),lock-list(排队); 大部分情况是乐观锁选择丢弃,悲观锁选择排队...

cas:compare and swap

SET session transaction isolation level read committed;
SET SESSION binlog_format = 'ROW';（或者是MIXED）

mvcc

多版本并发控制=>通过给数据多版本控制并发操作; 行记录增加了2个字端:trx_id,roll_ptr(事务ID,回滚指针)来标记不同的版本;通过mvcc控制每次能读(select)到的数据版本,从而减少读锁的使用;然后再锁住这些行进行下一步的操作(读/写).

RR + mvcc 一起解决的重复读的问题 再加gap-lock 解决幻读

lock

  • S: lock in share mode
  • X: for update

当前读-快照读

定义:

tech.meituan.com/2014/08/20/…

record-lock

  1. record-lock
  2. gap-lock
  3. next-key-lock

1解决的是事务update并发问题; 3,2解决的是事务insert/delete并发问题.

tech.meituan.com/2014/08/20/…

行锁是作用于有索引的字端,没有索引时则升级为表锁(所以update-where条件一定使用索引)....

总结+反思...

事务隔离级别定义了当前读的, mvcc通过多版本减少了读锁的添加; record-lock实现了事务隔离(update); next-key-lock解决的了幻读(insert); 美团文章很牛逼

index

索引类型

  1. 聚集索引
  2. 普通索引

最左匹配

联合索引(k1,k2,k3)=>建立索引时计算的顺序从左到右(先计算k1,再计算k2,最后计算k3); 所以最左匹配查询就是使用联合索引查询时,联合索引左边的key都尽量能等值查询; 如果不能则停止使用索引查询(一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配)

-- index(k1,k2,k3)
select * from tb where k1=2 and k2=2 and k3 >100;
-- 1. 索引只能用到k1,k2; k3开始遍历全部剩下索引
-- 2. and顺序不重要,会被mysql优化

类似场景:

  • like 'x%'

索引递推

索引递推和最左匹配都是基于联合索引的场景.

回表查询

slow-sql

explain

id

执行顺序： id不同自下而上执行,id相同则自上而下执行

rows

查询的结果集

sql优化

  1. order by limit
  2. group by 1,2 order by null

字端

tech.meituan.com/2014/06/30/…

mind-view

ps

avatar
文章
阅读
粉丝