MySql总结

最新推荐文章于 2024-09-25 14:34:16 发布

Olge

最新推荐文章于 2024-09-25 14:34:16 发布

阅读量136

点赞数

分类专栏： Code-MySql 文章标签： mysql 面试

本文链接：https://blog.csdn.net/zhangdong2012/article/details/100716519

版权

Code-MySql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

总结一下mysql吧。

mysql逻辑架构

mysql在逻辑上可以分为以下几个部分：

连接管理器：负责与客户的建立建立，并负责进行权限的校验；
查询缓存（8.0中以及去掉了）；
解析器：用于解析sql，明确用户的意图；
优化器：对sql的执行进行优化，例如使用哪些索引；
执行器：负责驱动sql语句执行；
存储引擎：负责存储数据；

sql的类型

对于我们程序员来说，写sql语句是日常工作的最主要内容之一，sql语句主要可以分为四种：

DDL：数据定义语言，包括create table、create index等；
DCL：数据控制语言，包括一些与权限控制相关的sql语句；
DML：数据修改语言，包括delete/update/insert；
DQL：数据定义语言，就是我们写的最大的select；

mysql锁

锁可以分为乐观锁和悲观锁两大类，对于乐观锁，mysql并没有提供直接的实现，我们可以通过数据表来实现，利用版本号来实现乐观锁的语义。

mysql中提供的锁基本都属于悲观锁，主要有全局锁、表锁、行锁。

全局锁共享模式：flush tables read lock; unlock tables;
全局锁排他模式：flush table write lock; unlock tables;
表锁共享模式：lock table t_name read; unlock tables;
表锁排他模式：lock table t_name write; unlock tables;
行锁共享模式：sql lock in share mode；
行锁排他模式：sql for update；

另外还有一种元数据锁，当对表数据进行修改时，就会加表的元数据写锁，进行crud的时候，会加元数据读向，相当于表锁的另外一种模式。需要注意一点的是对表结构的修改要极其小心，如果对于表的业务量比较大的话，由于修改表结构会加元数据写锁，此时所有的crud操作都会被阻塞，如果请求量非常大，会瞬间造成大量的业务请求抛异常，造成业务请求堆积，如果上层业务再有重试机制，那就更麻烦了，这些堆积的请求很可能把mysql直接压死，造成整个系统的雪崩，这个坑小编在真实场景中踩过，只不过没有那么严重，所以，修改表结构一定要找DBA来操作。

事务隔离级别

事务的特点：

原子性：在一个事务中的所有操作要么全部成功要么全部失败；
一致性：在事务执行前后，数据应该保持一致，例如转账，综合不能有变化；
隔离性：为解决事务并发问题，应该将不同的事务进行隔离处理，以保证逻辑的正确性；
持久性：事务结束后，数据应该被持久化处理；

并发事务存在的问题：

脏读：一个事务读到了另一个事务中未提交的数据；
不可重复读：在一个事务中多次读取同一条数据得到的结果不一致；
幻读：在一个事务中多次读取同一组数据时，返回的数据总数不一致；

为解决并发事务存在的问题，mysql提供的几种事务隔离级别：

read uncommited：读未提交，不能解决任何问题，一般不用；
read commited：读已提交，能够读到其他事务中已经提交的数据；
repeatable read：可重复读，能够解决不可重复读问题；
serializable：串行化，能解决所有问题；

在深入事务原理之前，我们先来明确结构与事务实现相关的概念：

MVCC：全称是Multi Version Concurrent Control，即多本版并发控制，mysql通过MVCC机制实现了各种不同的事务隔离级别；
当前读：读取最新版本的数据；
快照读：读取快照数据，不一定是最新版本；

下面再简单说说上面四种事务隔离级别的实现原理。

对于read Uncommited这种隔离级别，因为逻辑比较简单，就是读取最新的数据（注意并不是读取最新版本的数据），所以底层并没有使用到MVCC机制，而就是简单的读取磁盘上最新的数据。Read Commited 和 Repeatable Read这两种隔离级别都用到了MVCC机制，在事务开始时，会创建当前数据的快照（这里的快照并不是复制一份，每个事务都有唯一的一个事务id，通过事务id+版本号的方式来实现，底层数据实际上只有一份，而这里的快照相当于一个指针，指向了数据的不同位置），对于Read Commited，在读取数据的时候，是当前读，也就是读取其他事务以及提交了的最新的数据，而对于Repeatable Read，在整个过程中，都只会从事务开始时的快照中读取数据（但这里只针对以及存在的数据，对于新增的数据无效，也就是幻读），来保证多次读取数据的一致性。Serializable底层也没有使用MVCC机制，而是粗暴的将所有事务串行化处理，虽然最能保证数据的一致，但性能最差，一般也不会使用，在mysql中，默认的事务隔离级别是Repeatable Read。

索引

索引的作用是提高查询效率，那么如果数据数据结构的话，就会知道，能够提高查询效率的数据结构有很多，比如最简单的二叉树，复杂一点的红黑树、哈希表，还有B-Tree，那为什么mysql最终选择了B+Tree来实现索引结构呢？这个问题其实还挺有意思的。

我们都知道，目前在使用mysql的时候，一张表的数据量一般在500万~2000万之间，那么如何在这个数据量下面以最短的时间找到需要的数据呢？数据最终都是被存储在磁盘上的，那么读取数据的时候就会涉及到磁盘IO，磁盘IO可以说是计算机世界里最耗时的操作之一，cup的运算速度和磁盘IO速度做对比的话，可以用天上一天地上一年来形容，所以，向提高数据的检索效率，就必须减少磁盘IO次数，而从磁盘读取数据的时候，都是以磁盘页为单位来存储，基于以上逻辑，我们来分析一下上面的几种数据结构是否适合实现mysql的索引结构。

首先说二叉树，二叉树的检索效率O(logn)，看起来很快，但是二叉树有两个致命的问题，一个是在极端情况下会退化成链表，另外，在大数据量下，树的高度不可控，如果把一次节点搜索看做一次磁盘IO的话，那么当N很大时，磁盘IO次数太多，所以二叉树被pass了。

红黑树结构实际上和二叉树差不多，虽然它解决了退化成链表的问题，但是树的高度仍然不可控，同时调整树平衡的自旋过程也会造成一定的性能开销，所以红黑树也被pass了。

哈希表的检索效率理论上可以达到O(1)，但是由于哈希表是基于hashcode来存储数据的，对顺序查询不友好，例如要进行一个范围查询，哈希表就只能做全表扫描，另外哈希表对于排序也不友好，而这些操作都是mysql的核心功能，不能因为要提高查询性能就把这些核心功能给删掉，所以哈希表也被pass了。

最后来看看B-Tree，B-Tree与B+Tree最大的区别在于，B-Tree中的节点处理粗出key值外，还会存储key对应值，在mysql中，一个节点对应了一个磁盘页，默认大小为16kb，如果把value值也放进去的话，就会造成一个节点能够存储的key的数量变少，造成树的高度增加，所以基于性能考虑，B-Tree最后也不合格。

那我们在来看看B+Tree，B+Tree由于在非叶子节点上只存储key值不存储value，所以一个节点（磁盘页）可以存储更多的key。同时mysql在原始B+Tree的基础上做了一些优化，叶子节点构造成了一个双向链表，很方便进行范围查找。

mysql中的索引主要分为两种，一种是聚簇所以，一种是非聚簇索引。聚簇索引也叫主键索引，非聚簇索引也叫二级索引，聚簇索引的叶节点存储了具体的行数据，而非聚簇索引的叶子节点存储了行的主键值，当通过非聚簇索引查询数据的时候，一般首先会找到行数据对应的主键值，然后再在聚簇索引里查询具体的行数据，这个过程叫做回表。

在我们常用的两种存储引擎InnoDB和MyIsam中，索引的存储结构有一点区别，在MyIsam的聚簇索引中，叶节点存储的不是具体的行数据，而是数据才磁盘上的物理地址。

一般在创建表的时候都需要制定主键字段，同时最好使用int类型的自增字段，那为什么呢？因为如果不指定主键的话，mysql内部也会为表生成主键值，目的是用来构建主键索引，因为数据表本身其实就是一个主键索引，如果不指定主键字段的话会影响一部分性能，另外，主键字段类型不建议使用其他类型，比如字符串，因为字符串占用的空间比较多，造成B+Tree中的节点能存储的key的数量变少，树的高度增加，增加查找数据是磁盘IO次数。

MySql中的日志

慢查询日志；
bin log；
redo log；
undo log；
relay log；

慢查询日志记录执行时间比配置的时间长的慢sql，redo log是InnoDB存储引擎的日志，用来提高写数据的性能，bin log是server层的日志，用来进行数据同步和数据恢复，undo log主要用来实现事务的回滚，同时mvcc机制也是基于undo log来实现的，relay log用来实现数据同步，存在于slave节点上。