MySQL

作者: 紫色红色黑色 | 来源:发表于2019-10-27 15:52 被阅读0次

组成

MySQL结构是分为:连接器、分析器、优化器、执行器
连接器负责连接控制、用户认证。分析器负责语法分析、表字段分析。优化器负责优化SQL,确定执行计划。执行器负责执行执行计划。

日志

事务日志

更新语句写日志流程:先查询出该记录,然后插入redo log,更新redo log该记录为prepare状态。再插入binary log,最后更新redo log状态为commit。更新结束。
插入binary log前数据库宕机,数据恢复时会判断该数据没有执行完,不予恢复。插入binary log后数据库宕机,数据恢复时会判断该事务再binary log已写完,会将redo log中prepare状态的事务继续执行,更新为commit状态。

redo log

重做日志,是循环写,记录数据页做了什么改动。由内存中redo log buffer和硬盘上redo log file组成。配置中innodb_flush_log_at_trx_commit设置为1,表示每次事务提交更新buffer后再刷新数据到硬盘。保证数据不丢失。

undo log

回滚日志,记录每个事务版本下的数据做了什么修改。用于实现MVCC和事务回滚。

binary log

二进制日志,记录数据操作逻辑。查看binlog格式

show variables like 'log_%';
show variables like '%binlog_format%';
show binary logs;
  • binlog格式
    • statement,5.7之前默认,记录记录执行sql,但是对uuid()这种函数不能记录结果
    • row,5.7默认,记录行数据变化sql,可以修改参数binlog_row_image
      • full,记录一行所有列的内容
      • minimal,记录改动的列内容
      • noblob,
    • mixed,根据sql语句由系统决定是使用statment or row

当前读和快照读

  • 当前读:读取最新事务中的数据。例如DDL、加锁的查询
  • 快照读:读取undo log中对应的事务版本的记录数据,例如普通查询

事务

  • 事务特点
    • 原子性,每个事务都是最小不可拆分的动作
    • 一致性,事务中的数据,从开始到结束都应该一致
    • 隔离性,每个事务不应该影响其他事务
    • 持久性,事务结束后数据应该永久保存
  • 事务隔离级别

幻读:当前事务再读取某个范围内记录时,另一个事务在这个范围内插入了新的记录。当之前的事务再次读取该范围的记录时,会产生幻行。InnoDB通过MVCC解决了幻读。

隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
读未提交
读已提交
可重复读
串行化

MVCC

Mutiversion Concurrency Control。InnoDB的MVCC是通过在每行记录上保存两个隐藏列实现的。一个列保存行的创建时间、另一个列保存行的过期时间(删除时间)。列值存储不是真实的时间,而是系统版本号(System version number)。每开始一个新的事务,系统版本号会递增。事务开始时的系统版本号作为事务的版本号。用来和查询到的记录的版本号进行比较。下面看下可重复读隔离级别下的MVCC具体操作。

  • select
    InnoDB会根据以下两个条件检查每行记录:

    • InnoDB只查找版本号小于等于事务版本号的数据行。这样可以确保事务读取的行,是在事务开始前存在或者是事务自身插入或修改的。
    • 行删除版本要么不存在,要么大于当前事务版本号。这样可以确保事务读取到的行,是在事务开始前未被删除的。

    只有符合上述两个条件,才能返回作为查询结果。

  • insert
    InnoDB为新插入的行保存当前事务版本号为行版本号

  • delete
    InnoDB为删除的行保存当前事务版本号为行删除标识

  • update
    InnoDB为插入一行新记录,当前事务版本号为行版本号,当前事务版本号为原来数据行的删除标识

MVCC只在可重复读和读已提交隔离级别下工作,其他两个隔离级别和MVCC不兼容。因为读未提交总是读最新的行,而不是符合当前事务版本的行,而串行化是对所有读取的行加锁。

锁是保护多线程或多进程同时访问某一资源的机制

  • 按类型
    • 排他锁,x锁
    • 共享锁,s锁
  • 按粒度
    • 表锁,table-level,myisam默认
    • 行锁,row-level,innodb默认,加在主键索引上,如果索引失效则行锁变表锁
      • 行锁(record lock):锁定该条记录
      • 间隙锁(gap):范围查询中,锁定记录行间隙,使用左闭右开原则
      • 临界锁(next key):行锁+间隙锁
  • 死锁

查看最近的死锁日志

show engine innodb status;

索引

是为了配合高效查询算法的一种数据结构,innodb中是b+tree。这个tree上的叶子节点key为主键,value是行数据。普通节点只存储key和next。又称索引组织表。

  • 普通索引,value是primary key,如果查询主键或索引外的数据,需要回表到主键索引上
  • 唯一索引

优化步骤

  • 开启慢查询,捕获慢查询sql
  • explain优化sql
  • show profile查询sql在mysql中执行细节和生命周期情况
  • 数据库服务器参数调优

慢查询

查看慢查询是否开启

show variables like '%slow_query_log%';
show variables like '%long_query_time%';
show global status like '%Slow_queries%';

开启

set global show_query_log=1;

当前session休息

select sleep(4);

mysqldumpslow慢查询工具

explain

  • id相同,从上往下顺序执行。id不同,id越大优先级越大。
  • select_type
    • simple,简单查询,不包含子查询或union查询
    • primary,查询中包含复杂的子查询,最外层查询标记为primary
    • subquery,在select或where中包含的子查询
    • derived,在from列表中包含的子查询被标记为derived,mysql会递归执行这些子查询,将结果放到临时表中。table列中的显示<derived2>,数字就是id
    • union,第二个select放在union后,则被标记为union
    • union result,合并union结果集
  • table
  • type
    • system->const->eq_ref->ref->range->index->all
    • system,表只有一行记录,等于系统表,是const中的特例
    • const,表示通过索引一次就找到,const用于primary key和unique key。如将主键至于where中,mysql能将该查询转换为一个常量
    • eq_ref,唯一性索引扫描,对于每个索引键,只有一条记录与之匹配
    • ref,非唯一性索引,对于每个索引键,有多条记录与之匹配
    • range,只扫描给定的范围,一般where后有between、>、<、in等查询
    • index,只遍历索引树,从索引读取,all是从硬盘读取
    • all,全表扫描
  • possible_keys,可能用到的索引,一个或多个
  • key,实际用到的索引,为null表示没用到索引
  • key_len,表示索引中使用的字节数,为最大可能长度,并非实际长度
  • ref,显示索引的哪一列被使用,可能是常量,
  • rows,大致估算找到记录所需扫描的行数
  • extra
    • using filesort,无法利用索引进行排序,需要用到文件排序
    • using temporary,使用临时表保存中间结果,常见于order by或者group by
    • using index,表示select使用了覆盖索引,如果同时出现using where表明用来执行索引键值查找。如果没有using where表明索引用来执行读取数据而非执行索引查找动作
    • using where
    • using join buffer,多表连接join buffer小了
    • impossible where,where子句的值总是false

order by

  • 避免使用using filesort
    • order by 子句满足最左前缀原则
    • where子句和order by子句满足最左前缀原则,最左前缀定义为常量
  • 无法避免时,会尽量使用sort_buffer_size来进行文件排序

show profile

查看profile是否开启

show variables like '%profiling%';

查看profile状态

show profiles;
show profile cpu,block io for query 3;

相关文章

网友评论

      本文标题:MySQL

      本文链接:https://www.haomeiwen.com/subject/nvgpvctx.html