扫一扫下方二维码,关注本站官方公众号
获取永久解锁本站全部文章的验证码
还能不定期领现金红包

mysql相关知识点总结-Go语言中文社区

mysql相关知识点总结


mysql结构划分

mysql可以划分为客户端、server端、存储引擎三部分,server端包括了连接器、分析器、优化器、执行器4部分

  1. 客户端通过navicat、jdbc等方式与服务端连接器建立连接,发送sql语句
  2. 如果是查询语句,且mysql缓存中有查询结果则立即返回,否则server利用分析器对sql进行词法和语法分析,看sql语句是否错误
  3. 之后优化器对sql语句进行优化,有CBO(基于成本)和RBO(基于规则)两种方式,但大部分使用的是CBO,基于查询成本的方式进行优化
  4. 将优化完成的sql交给执行器,执行器通过数据库引擎执行sql,最后将执行结果返回

mysql调优

性能监控

  • show profiles可以查询sql语句各步骤的执行时间
  • show precesslist 命令查询数据库连接数量,command字段可以查看该线程状态,正在做什么
  • show engine innodb status;可以查询数据库引擎运行的详情信息,里面包括连接线程等待锁的信息

schema与数据结构优化

在数据库和表的创建之初就采用更合理的设计,减少存储空间,提升查询效率。

  • 当数据类型有多种选择时,更小的通常更好,例如整型数字类型由tinyint、samllint、mediumint、int、bigint,它们分别占1、2、3、4、8个字节,当它们都满足取值范围时选取较小的数据类型进行数据存储,首先可以节省存储空间,并且处理时需要的CPU周期较小也可以增快查询效率
  • 当存储字符串类型的数据时,如果我们已经知道了它的长度固定格式,例如手机号11位、身份证,那么就使用char类型,不要用varchar,这样可以减少一个字节的存储空间
  • 合理使用范式(范式是为了解决数据冗余的问题)和反范式,使用范式可以减少冗余,但会出现大量外键和关联查询,查询效率较低,因此在实际工作中允许出现一些反范式,即存在冗余
  • mysql 字符集用utf-8mb4,因为utf8存储的时候某些中文是乱码
  • 在innodb中索引和数据是一起存储的,myisam中是分开存储的;innodb才有redo log和undo log,它是用来支持事务的

项目中慢查询

1.mysql需要手动开启慢查询,项目中一般把慢查询时间设置为1S。当出现慢查询时利用explain命令分析SQL的执行计划,看它有没有使用到索引,有没有做全表扫描。当执行explain命令时有两个字段比较重要

  • type 表示对表的访问类型,常见的有ALL、index、range(索引范围扫描)、 ref、eq_ref、const(单条匹配 如主键查询)、system(从左到右,查询性能从差到好),一般需要保证type至少要到range这个级别
  • key 即查询时真实用到的索引,如果没有使用索引,则该字段为NULL

2.当sql语句语句没有使用索引时,可以为它添加合适的索引,提高查询效率,如果已经使用了索引但是用explain命令发现索引并未生效时,就需要检查一下是不是SQL查询条件where部分写的有问题导致索引没有生效,例如(下列sql假设key字段有索引)

3.当使用了索引,并且索引也已生效,就需要考虑下是不是SQL太过复杂,考虑对SQL进行拆分等优化,并且对相关业务代码进行调整
实际保险项目中包含有保单管理等模块,保单表的数据量大概有个几百万,且保单管理模块查询条件做的比较丰富,一开始我们做了很多次联表查询,join许多其他表,例如投保人、被保人、规划师、支付流水表(有分期付款)表等,sql写的很复杂,且偶尔会有慢查询的情况出现,后来发现大部分是出现在利用投保人和被保人姓名做查询的时候,在实际表中并没有对姓名加索引,之后考虑到尽量压缩关联查询的数据量,所以把投保人和被保人相关的查询单独拆出来不再做join查询,分两步先利用投被保人信息查询出具体的保单id,再利用保单id去做二次查询

索引

  • 什么是聚集索引和非聚集索引?
    • 聚集索引就是指索引与数据一起存储,非聚集索引就是说索引与数据分开存储;
    • 在MyISAM存储引擎底层它的索引文件和数据文件是分开存储的所以它里面都是非聚集索引;
    • Innodb的索引和数据存放在一个文件中,主键索引B+树的叶子节点中存放了其所在数据行的完整数据信息,它是聚集索引,但是非主键索引树叶子节点只存储了索引所在行的主键信息,所有非主键索引为非聚集索引

  • 索引是怎么实现的?
    • 在mysql中大部分索引是使用B+树来实现的,还有一小部分使用hash(哈希表,但不支持范围查询)
      B+树
      这个是B树

    • 在myisam 无论主键索引还是非主键索引叶子节点存储的是索引对应数据行在磁盘存储位置的指针
      在这里插入图片描述

    • innodb 非主键索引叶子节点存储的是索引对应行的主键值,主键叶子节点存储了其数据行的完整数据信息
      在这里插入图片描述

mysql对B+树中一个节点的大小设置的是16K,B+树叶子节点间存在双向指针

mysql 存储引擎

MyISAMInnoDB
支持表锁即支持表锁也支持行锁
不支持事务支持事务
不支持外键支持外键
非聚集索引,无论主键还是非主键B+树叶子节点存储的都是对应数据存储地址的指针主键是聚集索引,索引和数据存储在一起;非主键B+树叶子节点存储的是索引对应的主键值,主键B+树叶子节点存储的是主键对应的完整数据
存储文件中索引和数据是两个文件分开存储的索引和数据用同一个文件存储
可以没有主键一定要有主键
  • Innodb中的事务
    事务可以理解是一组sql集合,他们的执行要满足ACID四个特性。一致性是事务的根本追求
事务特性实现原理
原子性undo_log由innodb引擎产生,当进行数据操作之前,首先将原数据备份到undo log之中,然后进行数据修改,如果出现的错误,或者用户执行了回滚rollback语句,系统可以利用undo log中的备份将数据恢复到事务开始之前到状态,undo log还用于实现多版本并发控制(MVCC);需要注意的是undo log是逻辑日志,可以理解为当delete一条记录时,undo log中会记录一条对应的insert语句,当insert一条语句时,undo log中记录的是一条delete语句,update时,会记录一条相反的update记录
一致性通过原子性 隔离性 持久性来保证
隔离性实现原理锁,共享锁S,排他锁X,事务根据自己对数据的操作类型申请相应的锁;申请锁的请求被发送到锁管理器,锁管理器根据当前数据项是否已经有锁以及事务申请和持有的锁是否冲突来决定是否为该请求授予锁;若授予锁则事务可以继续执行,若拒绝,则申请锁的事务将进行等待,直到锁被其他事务释放。
持久性redo log 记录了新数据的备份,在事务提交前,将要保证redo log持久化到磁盘,此时不需要将数据持久化。当系统奔溃时,虽然数据没有持久化,但是系统可以根据redo log内容,将数据恢复到最新状态,redo log刷入磁盘的频率可以由 innodb_flush_log_at_trx_commit参数控制 0每次commit时将rodo log写入log buffer,每秒将日志写入os buffer并调用fsync()刷入磁盘(这里的commit应该是指修改完数据页中的数据,生成对应的redo log);1每次commit时将rodo log写入os buffer并调用fsync()将日志写入磁盘;2每次commit时将rodo log 写入os buffer,每秒钟调用fsync()将日志刷入磁盘。默认是1,数据安全性高,但是会影响性能,0和2相比,他们预防的宕机级别不一样,当mysql宕机log buffer中数据就会丢失,os buffer是系统内存当mysql挂掉时,只要操作系统没有挂,它的数据就不受影响依然可以刷到磁盘

redolog提交时间点

mysql中的锁

  • MyISAM中有两种锁
    表共享读锁、表独占写锁,对MyISAM表的读操作,不会阻塞其他用户对同一个表的读操作,但是会阻塞对同一张表的写请求;对MyISAM表的写操作则会阻塞其他用户对同一张表的读和写请求;MyISAM表的读写操作之间、写操作之间是串行的。

  • Innodb

    • 共享锁(S):又称读锁,允许一个事务去读一行,阻止其他事务获取同一数据集的排他锁,若事务T对数据对象A加上S锁,则事务T可以读A但是不能修改A,其他事务只能对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但是在T释放A上S锁之前不能对A做任何修改
    • 排他锁(X):又称为写锁,允许获取排他锁的事务更新数据,阻止其他事务获取相同数据集的共享读锁和排他写锁。若事务T为数据对象A加上X锁,事务T既可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。
    • mysql innodb引擎默认为update、insert、delete语句自动加上排他锁,select语句默认不加任何类型的锁,如果想加排他锁可以使用 select … for update,加共享锁使用 select … lock in share mode语句。所以加过排他锁的数据行在其他任何事务中是不能修改的,也不能通过 for update和lock in share mode的方式查询数据,但可以通过select … from …普通查询语句查询数据,因为普通查询没有任何锁机制。
    • innodb行锁的实现方式是通过给索引上的索引项加锁来实现的,这种方式意味着只有通过索引条件来检索数据,innodb才能使用行级锁,否则将使用表锁

mysql中的事务隔离基本

在这里插入图片描述

  • 可重复读隔离级别的实现
    在InnoDB中,给每行增加两个隐藏字段来实现MVCC(多版本并发控制),一个用来记录数据行的创建时间,另一个用来记录行的过期时间(删除时间)。在实际操作中,存储的并不是时间,而是事务的版本号,每个事务都有一个版本号,每开启一个新事务,事务的版本号就会递增。
    于是乎,默认的隔离级别(REPEATABLE READ)下,增删查改变成了这样:
    SELECT
    读取创建版本小于或等于当前事务版本号,并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。
    INSERT
    将当前事务的版本号保存至行的创建版本号
    UPDATE
    新插入一行,并以当前事务的版本号作为新行的创建版本号,同时将原记录行的删除版本号设置为当前事务版本号
    DELETE
    将当前事务的版本号保存至行的删除版本号

一个小知识点

UNION 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)

UNION ALL 语句:用于将不同表中相同列中查询的数据展示出来;(包括重复数据)

Bin Log

二进制日志 binlog,记录了数据库执行sql的写入性操作信息,如insert、update、delete操作,由mysql server层产生,以二进制数据的形式保存在磁盘上,无论使用何种数据库引擎,mysql都会产生bin log。
对于InnoDB引擎来说,只有在提交事务时才会产生bin log,但此时bin log还是存在内存中,最终需要被存储在磁盘中,它的刷盘时机,mysql通过sync_binlog参数来控制

  • 0 不强制要求,由系统自行判断何时写入磁盘
  • 1 每次commit的时候都要将bin log写入磁盘
  • n 每N个事务commit,才会将一批bin log写入磁盘

sync_binlog最安全的配置是1,这也是mysql 5.7.7之后版本的默认配置,但也可以设置一个较大的值,牺牲一些一致性提升数据库性能。

bin log的应用场景有两个

  1. mysql 集群主从复制,在master端开启binlog,然后将binlog发送给slave,slave重放binlog达到主从数据一致
  2. 数据恢复,只要保存有足够的binlog数据,可以把数据库状态恢复至任意时刻的状态

binlog包含三种模式

  1. statement 基于sql语句,每一条修改了DB中数据的sql都会被记录到binlog中,优点是:不必记录每条发生变化的数据,日志量较小,节约IO,提高性能;
    缺点是:在sql中使用函数有可能会导致主从数据不一致例如sysdate()等
  2. row 不需要记录sql,将每条发生改变的数据存储进binlog中,优点:不会出现存储过程、函数导致主从数据不一致的情况 缺点:数据量较大,尤其是alter table会让日志量暴增
  3. mixed statement和mix两种混合,一般情况下使用statement存储,对于statement无法保存的binlog使用row的形成存储binlog
    保险用的是row

redo log、undo log

crash-safe能力,在事务提交的任意阶段,数据库宕机,重启后保证事务完整性,已提交的事务不会丢失,未提交的事务自动回滚,使用redo_log、undo_log实现

mysql的数据存储在磁盘上,直接对磁盘进行访问操作涉及到磁盘的I/O访问,效率较低,因此在mysql的innodb引擎中将数据读取到内存中一个缓冲池buffer poll里以数据页的形式进行存储,当mysql对数据进行查询、修改等操作时实际是对数据页进行操作。mysql中有一个很重要的原则是日志先行,为了避免buffer poll中的数据丢失和保障mysql宕机恢复后数据的完整性,为了满足事务持久性提供了redo_log,为满足原子性提供了undo_log。

例如在执行 update order set status = 2 where id =1;

  1. 在开始时会先判断id为1的数据是否在buffer poll缓冲池中,如果没有将相关数据页其加载到内存中
  2. 记录在undo_log中记录id=1的status的初始值
  3. 对buffer poll中数据页id=1的记录进行操作,之后将相应的数据修改结果生成redo_log信息,存储进redo log buffer中,将redo log状态为prepare,在生成该条redo log时就可以根据innodb_flush_redo_log_at_trx_commit参数判断何时将其刷入磁盘;在mysql server层将数据操作记录存储进bin log buffer中,根据sync_binlog参数判断是否将其刷入磁盘
  4. 将redo_log中记录置为commit状态
  5. 返回操作结果

redo_log和bin_log的区别:

  1. redo_log是数据库引擎实现的,bin_log是数据库server层实现的
  2. redo_log采用循环写的方式来记录日志,当写到结尾时会回到开头重新写;bin_log是通过追加的方式,当文件大小不足时,则生成一个新的文件继续写
  3. redo_log一般用于mysql宕机后恢复保证数据完整性,bin_log一般用于mysql主从同步(slave获取master的bin_log,将其写入自己的中继日志relay log中,将操作重放),误操作后数据恢复

undo_log用于保证事务的原子性,当事务处理出现错误时,回滚到事务最初的状态

由于buffer poll和redo_log的大小并不能无限扩展,所有需要按照一定的节奏将buffer poll(数据库缓冲池)中的脏页(缓冲池中数据页的数据与磁盘中不一致)刷新至磁盘,这个刷脏页的时刻就被称作check_point

当数据库宕机恢复时,根据rodo log记录从check point点判断redo log是否完整且为prepare状态,然后从binlog中找相应的事务记录,如果可以找到则重新commit redolog,否则执行undolog

MySQL 的 crash-safe 原理解析

版权声明1:本文来源CSDN,感谢博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/swl1993831/article/details/115499382
站方申明:本站部分内容来自社区用户分享,若涉及侵权,请联系站方删除。
  • 发表于 2021-12-11 20:45
  • 阅读 ( 715 )
  • 分类:数据库

0 条评论

请先 登录 后评论

官方社群