首页  思维导图  详情

mysql

2020-10-09 16:54:46   48  举报





AI智能生成

mysql

作者其他创作

大纲/内容

事务

事务的隔离级别

Read uncommitted

会出现脏读，不可重复读，幻读

脏读：一个事务读取到另外一个事务未提交的数据

Read committed

会出现不可重复读，幻读

不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改

Repeatable read

会出现幻读(但在Mysql实现的Repeatable read配合gap锁不会出现幻读！)

幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

Serializable

串行，避免以上的情况！

实现隔离级别的两种方式

1.加读写锁
2.MVCC一致性快读

MVCC

基本概念

事务ID

事务ID是一个递增的整数，唯一的标识一个事务。ID的大小可以用来表示事务的串行化顺序，用于事务可见性的判断。大小6个字节

回滚指针

表示指向该行回滚段（rollback segment）的指针，大小为 7 个字节， InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本，在 undo 中都通过链表的形式组织。

DB_ROW_ID

行标识（隐藏单调自增 ID ），大小为 6 字节，如果表没有主键， InnoDB 会自动生成一个隐藏主键，因此会出现这个列。另外，每条记录的头信息（ record header ）里都有一个专门的 bit （ deleted_flag ）来表示当前记录是否已经被删除。

原理：
InnoDB的MVCC，是通过在每行纪录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或删除时间），当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行纪录的版本号进行比较。

操作过程

select

InnoDB会根据以下两个条件检查每行纪录：
1.InnoDB只查找版本早于当前事务版本的数据行，即，行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
2.行的删除版本，要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。

insert

InnoDB为插入的每一行保存当前系统版本号作为行版本号。

delete

InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

update

InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时，保存当前系统版本号到原来的行作为行删除标识。

优点：
多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

缺点：
每行纪录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

参考资料

锁模块

MyISAM

MyISAM默认使用表级锁，不支持行级锁

InnoDB

InnoDB默认用的行级锁，也支持表级锁

InnoDB为了提高性能，在read uncommited,read committed和repeatable read三种隔离级别下select操作是非阻塞的。即使使用了select语句还是没有上读锁。

InnoDB在sql没有用到索引的时候会锁表而不是锁行，索引（一般是主键）不明确时也是锁表。

读锁（又称共享锁）

当加上读锁后，此时只能对表执行读操作，其他操作会被阻塞

写锁（又称排他锁）

当加上写锁后，所有操作都会被阻塞

总结

1.共享锁排他锁在两个存储引擎中都存在,只不过MyISAM是作用在表中,InnoDB是作用在行中.InnoDB也可以作用于表
2.在MyISAM引擎中增删查改都会自动上锁,在InnoDB中,select要主动上锁才行,增删改InnoDB会主动上锁.
3.MyISAM适用于频繁执行查询语句，对数据增删改的频率不高，没有事务的场景
4.InnoDB适用于增删改查相当频繁，可靠性要求高，需要支持事务的场景。
5.InnoDB在sql没有用到索引的时候，用的表级锁，sql用到索引的时候，用的行级锁

备份

mysqldump

MySQL数据库自带的一个很好用的备份命令。是逻辑备份，导出的是SQL语句。也就是把数据从MySQL库中以逻辑的SQL语句的形式直接输出或生成备份的文件的过程,是全量备份

备份例子：
mysqldump -uroot -h192.168.1.146 --flush-logs --master-data=2 --single-transaction --routines --triggers --events --databases zsythink -p > zsythink.sql

属性

--flush-logs
mysqldump备份时，立刻滚动二进制Binlog文件，也就是生成新的binlog文件开始记录日志，那么我们在恢复的时候，就不用从一个binlog文件的中间开始恢复，高效！

--master-data
指定mysqldump备份时，记录对应二进制日志Binlog文件的位置
=0 不记录位置，与不使用此命令没有区别，没用
=1 默认值，也就是不指定值就是1，记录具体的Binlog文件的位置，会在备份文件中生成“CHANGE MASTER TO”语句，标明备份开始二进制文件的前缀名，和对应的position位置
=2 与1不同的时，它会注释掉备份文件中生成的“CHANGE MASTER TO”语句，如果只是单纯的记录position的位置，建议使用值2

--routines
表示备份时，存储过程和存储函数也会被备份

--triggers
表示备份时，触发器会被备份

--events
表示备份时，事件表会被备份

--single-transaction
在备份的过程中使用--single-transaction选项，它会让备份基于独立的事务进行

恢复：
执行生成的sql文件就可以了

mysql binlog

基本概念：
二进制日志，记录对数据发生或潜在发生更改的sql语句，并以二进制的形式保存在磁盘中

作用

复制:Mysql的master-slave协议，让slave可以通过监听binlog文件实现数据复制，达到数据一致的目的
一般是在master端开启binlog，然后再slave端通过读取master端的binlog文件来实现主从数据一致性
恢复：通过mysqlbinlog工具恢复数据
一般我们使用mysqldump备份是全量备份，它备份某一个时间点的所有数据。如果在下一次备份前数据库故障，那么上次备份时间点到数据库故障时间点之间的数据库变化就会丢失，这就需要我们的binlog记录这些增量数据了。

binlog日志格式

1.ROW 仅保存记录被修改细节，不记录sql语句上下文相关信息
2.STATEMENT 每一个会修改数据的sql语句都会记录在binlog中
3.MIXED 以上两种level的混合使用
执行show variables like 'binlog_format'查看

管理binlog

show variables like ‘log_bin’查看是否开启binlog

管理binlog的sql语句

show master logs；查看所有Binlog的日志列表
show master status；查看最后一个Binlog日志的编号名称，以及最后一个事件结束的位置
flush logs；刷新Binlog 此刻开始产生一个新编号的Binlog日志文件
reset master；清空所有的Binlog日志（慎用）

查看Binlog相关的sql语句

show binlog events in ‘L1WWITAFW5IVTNG-bin.000001’ from 500 limit 1，2；从指定的位置，带有偏移，开始查看binlog日志，限制查询的条件

恢复

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码数据库名

常用选项：
--start-position=953 起始pos点
--stop-position=1437 结束pos点
--start-datetime="2013-11-29 13:18:54" 起始时间点
--stop-datetime="2013-11-29 13:21:53" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

redo log

redolog记录着物理变化，即什么页修改了什么数据。每当我们在内存中修改数据时，会在内存中写redo log，假如修改未落到磁盘，可以通过redolog恢复这部分修改

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

日志的记录过程

这能解释mysql如何保证redolog和binlog日志一致
1.一条更新语句请求
2.在内存中更新
3.将修改结果更新到内存
4.在n数据页的m处修改了x，并把这行记录记为preprea
5.修改好了，可以提交事务
6.写入binlog
7.commit 提交事务
8.将redolog的这条记录置为commit

redo log的记录方式

redolog的大小是固定的，在mysql中可以通过修改配置参数innodb_log_files_in_group和innodb_log_file_size配置日志文件数量和每个日志文件大小，redolog采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。

write pos表示日志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1从头开始记录；check point表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦除掉，即write pos->checkpoint之间的部分是redo log空着的部分，用于记录新的记录，checkpoint->write pos之间是redo log待落盘的数据修改记录。当writepos追上checkpoint时，得先停下记录，先推动checkpoint向前移动，空出位置记录新的日志。

解决的问题

有了redo log，当数据库发生宕机重启后，可通过redo log将未落盘的数据恢复，即保证已经提交的事务记录不会丢失。

它和binlog的区别

1、redo log的大小是固定的，日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作。
2、redo log是innodb引擎层实现的，并不是所有引擎都有。
3、binlog是server层实现的，意味着所有引擎都可以使用binlog日志
4、binlog通过追加的方式写入的，可通过配置参数max_binlog_size设置每个binlog文件的大小，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上。

undo log

undolog是回滚日志，主要有两个作用：回滚和多版本控制(MVCC)

undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

undo log和redo log的区别

Undo 记录某数据被修改前的值，可以用来在事务失败时进行 rollback；
Redo 记录某数据块被修改后的值，可以用来恢复未写入 data file 的已成功事务更新的数据。
Redo Log 保证事务的持久性
Undo Log 保证事务的原子性

调优思路

1.定位到执行慢的sql语句

执行show VARIABLES LIKE '%quer%'

第一个表示慢日志是否开启，默认关闭，第二表示慢日志的文件的位置。
一般执行时间超过10秒的sql语句就会被放进这个文件里面（10秒是默认值，可以修改），这样就可以定位到执行慢的sql语句。

2.用explain分析执行慢的语句

ID：表示查询中执行select子句或操作表的顺序，当id相同时，按从上到下的顺序执行，id不同时，id数值大的先执行。

select_type：select_type代表的是查询的类型,主要是用于区别普通查询,联合查询,子查询。
SIMPLE ：简单的select查询，查询中不包含子查询或者UNION。
primary：查询中若包含任何的子查询,最外层查询则被标记为primary。
subquery：表示子查询。
dervied:zai from列表中包含的子查询标记为derived(衍生) mysql会递归执行浙西子查询,把结果放在临时表里。
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVE
union result :从union表获取结果的select。

table：表示执行的表

type：type所显示的是查询使用了哪种类型…
这个字段很重要，从好到坏的顺序是：system > const > eq_ref > ref > range > index > all
一般来说，至少要保证能达到range级别。
system:该类型表示表中只有一行记录
const:常量匹配,表示通过索引一次就可以找到数据
eq_ref:唯一性索引扫描,对于每个索引建,表中只有一条记录和它匹配
ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range:只查找范围内的数据,使用索引来匹配行,查询的范围缩小了,自己比前面的类型性能更好
index:(full index scan)全索引文件扫描比all要好不少,将直接从索引数据结构中找寻数据,比全表扫描理论上快不少
all: Full Table Scan 将遍历全表以找到匹配的行

possible_keys: 显示可能应用在这张表中的索引，一个或多个,但不一定会使用到

keys：显示查询语句中实际使用的索引,如果没有使用则为null

key_len: 显示索引中使用的字节数,通过key_len计算查询中使用的索引长度,在不损失精确性的情况下索引长度越短越好,key_len显示的值为索引字段最可能的长度,并非是实际使用长度

ref:显示索引的那一列被使用了，如果可能的话，最好是一个常数。

rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数,越少越好

extra:包含十分重要的额外信息
Usiing filesort:说明mysql读数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql无法利用索引完成的排序称为"文件排序".
Using temporary:使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index:表示相应的select操作中使用了覆盖索引,.
Using where : 表明使用了where过滤
Using join buffer:表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些
impossible where:where子句的值总是false，不能用来获取任何元组

3.对对应的sql语句进行优化

通过一些提高性能的经验来修改sql语句，或者让sql走索引。

数据库三大范式

第一范式：每个列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

基本架构

连接器

它负责和客户端建立连接，获得权限，维持和管理连接。我们查询时，第一步都是链接，这个时候就是连接器在起作用。链接的时候会经过TCP握手，然后身份验证，然后我们输入用户名密码就好了
验证ok后，我们就连上了这个MySQL服务了，但是这个时候我们处于空闲状态

分析器

在缓存没有命中的情况下，就开始执行语句了。
会先做词法分析，你的语句有这么多单词、空格，MySQL就需要识别每个字符串所代表的是什么，是关键字，还是表名，还是列名等等。
然后就开始语法分析，根据词法分析的结果，语法分析会判断你sql的对错，错了会提醒你的，并且会提示你哪里错了

优化器

它负责查询的优化。优化就比较简单了，因为我们建立表可能会建立很多索引，优化有一步就是要确认使用哪个索引，比如使用你的主键索引，联合索引还是什么索引更好。还有就是对执行顺序进行优化，条件那么多，先查哪个表，还是先关联，会出现很多方案，最后由优化器决定选用哪种方案

执行器

它负责执行语句，执行器会调用执行引擎去执行。执行的时候，就一行一行的去判断是否满足条件，有索引的执行起来可能就好点，一行行的判断就像是接口都提前在引擎定义好了，所以他比较快

流程图

缓存：
MySQL 拿到一个查询请求后，会先到查询缓存查询

缺点：
1.查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
2.查询缓存的命中率会非常低

在 MySQL 8.0 中，缓存功能彻底被废弃。

索引

为什么需要索引？

mysql的存储结构

首先Mysql的基本存储结构是页(记录都存在页里边)：各个数据页，能组成一个双向链表，而数据页中的记录又可以组成一个单向链表。

查找过程

1.定位到记录所在的页（需要遍历双向链表，找到所在的页）
2. 从所在的页内中查找相应的记录（由于不是根据主键查询，只能遍历所在页的单链表了）

所以我们做的是全表扫描，当数据量增大时，速度自然很慢

索引的底层结构

哈希索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快

哈希索引效率高，为什么mysql底层没有用它
缺点：
1.哈希索引也没办法利用索引完成排序
2.不支持最左匹配原则
3.在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
4.不支持范围查询
但是在实际的场景中这些都是经常需要用到的。

平衡二叉树

特点：节点含有关键字，每一个节点的左子树的值小于它本身，右子树的值大于它本身。
左右子树的树高差小于等于1.

缺点：
它开始可以达到O(logn)的效率，但是当对数据进行操作之后，如果之后添加的数据一直在树的一边，就会形成这种线性二叉树，使效率变成O(n).

B树

b树的特点：
1.根节点至少包括两个孩子。
2.每个节点至多有m个孩子（m>=2）。
3.除根节点和叶子节点以外，其他每个节点至少有m/2个孩子。
4.所有叶子节点都位于同一层。
5.关键字在树中，自左到右，升序排列。
6.b树将数据存放在非叶子节点中，检索时，可能在任一非叶子节点处结束

b树由于自身的特点，可以保证树高很低，在检索上，性能不错，是个很好的选择，但是我们有更优的选择B+树。

B+树（mysql底层使用）

B＋树的特点，它与B的特点差不多，下面是一些区别：
1.非叶子节点与根节点关键字数量相同。
2.指针的值，需要在其指向子树的关键字的区间内。
3.非叶子节点仅储存索引，数据都存储在叶子节点中（这样非叶子节点就可以比B树保存更多的关键字）。
4.所有叶子节点有一个指向下一叶子节点的指针。

根据这些特点，B+树作为索引的优势有：
1.非叶子节点仅仅保存索引，这样一次读入内存中查找的关键字更多，降低了I/O次数。
2.数据都保存在叶子节点中，所以每次查询都要走到叶子节点，也就是每次查询的效率几乎一样
3.每个叶子节点用指针链接起来，这样有利于进行范围查找，提高范围查找的效率

聚集索引和非聚集索引

聚集索引

基本概念：
它的B+树的叶子结点包含关键字和其他所有行数据。（InnoDB使用）

非聚集索引

基本概念：
它的B+树的叶子结点只包含关键字和指向行数据的地址。（Myisam使用）

选择聚集索引的过程

一个表只能创建一个聚集索引，InnoDB在选择聚集索引的过程：
1.如果有主键被定义，那么主键就是聚集索引。
2.若没有主键被定义那么，该表的第一个唯一非空索引作为聚集索引。
3若不满足上面两个条件，InnoDB内部会生成一个隐藏主键（聚集索引）

通过聚集索引查找数据的过程

一个表只能创建一个聚集索引，其他的都是非聚集索引，所有对于InnoDB支持的表的检索过程就是，先通过辅助键索引（非聚集索引）B+树找到主键索引值，再通过主键索引（聚集索引）B+树找到行数据。（这个过程实际查找了两次，也叫回表）

覆盖索引

在创建多列索引的时候，特殊情况-》覆盖索引
从上面的过程可知，我们在查询时候，实际上是要查询两次的。这样就会稍慢
而覆盖索引就是把要查询出的列和索引是对应的，就不需要再去查一次聚集索引了
比如：我们创建索引（a，b），然后select a,b from A ,那么a,b的值都在B+树的叶子节点，我们就不需要回表了

索引最左匹配原则

基本概念：
最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。

如有索引 (a,b,c,d)，查询条件 a=1andb=2andc>3andd=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

为什么最左匹配

什么字段时候做索引？

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；
建立索引，要根据数据的具体情况分析，要综合考虑，建立索引带来的会带来的储存开销，对增删，改操作，带来的处理开销与索引会增加查询效率这些因素结合起来。

B+树的每个节点为多大合适？

因为mysql的存储结构为页，所以每个节点为页或者页的倍数比较合适。
因为如果不为页的倍数的话，比如小于一页，读取出来就会造成空间的浪费。大于一页，比如1.1页，就会读出来两页，也会造成资源的浪费。

集群

分表

为什么要分表：
解决海量数据的查询效率问题

水平分表

通常是用户ID取模，如果不是整数，可以首先将其进行hash获取到整。

垂直分表

垂直拆分原则：
1. 把大字段独立存储到一张表中
2. 把不常用的字段单独拿出来存储到一张表
3. 把经常在一起使用的字段可以拿出来单独存储到一张表

分库

为什么要分库：
解决数据库并发性能