彻底搞懂MySql及其底层原理（更新中...）

彻底搞懂MySql及其底层原理

背景

Mysql无疑是互联网公司用的最多的数据库了，它拥有开源、免费、学习成本低等优点，以至于被各大厂商青睐。理论上来说作为开发人员而不是专业的DB人员只需要掌握常用的增删改查命令以满足业务需求就行。不过，由于互联网行业的兴起，导致了公司的业务越来越复杂，数据量越来越庞大，再加上程序员这一职业越来越受到人们的青睐，许多人纷纷通过各大培训机构转行，就连考研学子也称计算机专业为“宇宙机”，纷纷选择跨专业考研。

无疑是这一越来越内卷的现象，催生了众多公司对开发人员Mysql越来越高的要求。如今，随便上某招聘网站搜索“Java开发工程师”职业要求，其中数据库一栏基本都是：熟练使用数据库，精通数据底层原理，索引数据结构，sql优化......

当我第一次看到这些，心里一万个草泥马，你这是在招DB还是Java开发？我就会个增删改查还不行吗？

如果你也有相似的经历，对Mysql还只停留在增删改查阶段，想要轻松通过Mysql相关面试或者单纯的想提升自己的sql认知水平。相信本文会带给你耳目一新的感觉。

Ps 本人虽然科班出身，却非常排斥满文专业术语让人一头雾水，所以全文我会尽量用大白话形式展现，在必要的时候会有自己的绘图以辅助让文字更容易理解

MySql系统结构如何？

img

如题可以看出，mysql主要有server层、存储引擎层、以及客户端层三大部分，我们主要了解前面两部分。

从Mysql的引擎说起

存储引擎是Mysql的核心，Mysql的存储引擎是以插件的形式运行的，所以诞生了许多存储引擎，如InnoDB、MyISAM、MEMORY、ARCHIVE、CSV等十多种。

不过从mysql5.5开始，默认存储引擎就已经是InnoDB了，在之前是MyISAM，而我们仅仅只需要掌握这两种就行（重点还是InnoDB）

MyISAM存储引擎：

​ 作为mysql5.5以及之前的默认引擎，它具有以下特点：

（1）不支持事务；
（2）不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用；
（3）对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存；
（4）默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁；
（5）支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等。
（6）数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复；

InnoDB存储引擎

随着软件行业的不断发展，尤其是互联网行业的兴起，以前的存储引擎完全无法满足业务需求，所以Mysql在5.5版本后就以此存储引擎作为默认。InnoDB作为一款经典的存储引擎，它能够适应绝大多数企业的用途。

主要特点有：

（1）灾难恢复性比较好；
（2）支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
（3）使用的锁粒度为行级锁，可以支持更高的并发；
（4）支持外键；
（5）配合一些热备工具可以支持在线热备份；
（6）在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
（7）对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

你真的会建表吗？

对于绝大多数程序员，每天的工作无疑都是对数据库进行“增删改查”。而表是我们存储数据的核心，如果能够根据业务建立一张合适的数据表，不仅能够提升我们的开发效率，还会一定程度使数据库查询效率更高。

关于建表规范，推荐：阿里巴巴MySQL数据库设计规范

彻底说透索引

到底什么是索引？

对于索引，我相信绝大多数人都知道，也都用过。而且一般都会认为索引就相当于是书籍的目录，可以方便我们快速查找数据。然而这样认为并不完全正确。

索引应该是帮助MySQL高效获取数据的排好序的数据结构。

没错，索引就是一种数据结构，包括：

  • 二叉树，一种子节点最多为两个的树形数据结构。
  • 红黑树，一种从根节点到任意尾节点的路径之差不超过1的平衡二叉树
  • 哈希表，一种通过哈希算法直接存储内存地址的数组
  • B树，一种在节点存储多条索引元素以及附带数据的树形结构
  • B+树，B树的变种，冗余存储了索引元素，在叶子节点存储了所有索引元素和附带数据，且含有双向指针

那么，索引到底是保存的什么呢？

一个完整的索引数据通常包含两部分：排序的值和对应的数据

通常索引会按照索引字段进行排序并存储

会根据编码类型和排序规则进行排序，在我们创建数据库时可以指定。

图

索引的数据具体保持的什么需要视情况而定，存储引擎的不同以及索引类型的不同都会导致索引数据存储的方式和结果不同

在MyISAM存储引擎中，主键索引对比非主键索引只有重复与否的区别，主键索引的值不可重复。所有索引存储的数据其实是磁盘文件地址（数据结构同样是B+树，这种索引也称为非聚族索引）。

但寻找到相应的索引后，就会根据磁盘文件地址寻找相应的真实数据并加载到内存中。

而在InnoDB存储引擎中，如果是主键索引，那么在索引的数据区就是存储这一行对应表中完整的数据。如果是非主键索引，那么数据区就是存储的主键索引地址。

显而易见，InnoDB的主键索引虽然冗余了大量的数据，但减少了磁盘IO操作，也就提升了查询效率，而这种索引也被称为聚族索引。当我们根据主键索引查询数据时，很显然直接就从磁盘查询到了，而根据非主键索引查询数据时，需要额外查询一次主键索引。

索引类型有哪些？它们之前又有什么区别？

示例表结构如下：

image-20220608165531833

当我们对表中某个字段创建索引的时候，可以选择以下索引：

  • FULLTEXT：全文检索
  • NORMAL：普通索引
  • SPATIAL：空间索引
  • UNIQUE：唯一索引

image-20220608165041764

其中，全文索引只适用于字符串类型（char、varchar、text等），他提供了全文检索功能，效率比like更好，但是对于中文分词不太友好，需要第三方插件。如果需要简单的搜索还好，否则还是建议使用专业的第三方组件比如es。

值得一提的是，如果在非字符串类型字段建立FULLTEXT类型索引，则会报错：

image-20220608165146890

空间索引：一般不会用，有关空间计算的。

剩下就是普通索引和唯一索引了，它们的唯一区别其实就是可不可重复。除此之外，本质上没有任何区别。

那么我们应该如何选择呢？

我的建议是尽量选择普通索引，除非在业务上面明确会保证该字段唯一

其次，mysql中还存在组合索引，它是基于多个字段的，你可以把它理解为在B+树里面储存的data是由多个字段的值组成了，然后按照值依次排序。下面，我们首先建立一个组合索引。

image-20220608165838867

常见疑问🤔️：

  • 聚族索引既然冗余了这么多数据，那么进行增删改操作岂不是不好维护？

    得确，聚族索引在维护时需要进行额外的操作。但通常来说，一个表的数据查询的次数是要远远高于修改的次数，综合考虑，维护成本带来的收益还是相当可观的。

  • 为什么InnoDB引擎推荐每个表都需要有主键，并且最好时整型的自增主键？如果不建主键怎么样？

    首先，InnoDB并不强制需要用户建立主键，但它得确是必须存在主键的，当你没创建主键时，mysql会选择表中唯一的字段作为主键；若当你没有唯一字段时，mysql会根据你的插入顺序（俗称行数）来作为主键。

    因为索引是根据索引字段来排序插入的，作为一颗B+树来维护（关于B+树的维护可以参见【链接】），既然排序，肯定整型自增的类型是天然排序的；如果不是整型，比如字符型，那么mysql就会根据字符编码和排序规则来排序，对比整型可以直接排序相当于是多了一个步骤。

  • 主键索引是必须的吗？

    主键索引是必须的，它是跟主键相与相存的，并且一般由mysql自动创建。

  • 聚族索引相比非聚族索引谁快，为什么？

    聚族索引要更快，因为聚族索引可以直接在一个磁盘文件中寻找到索引数据，而非聚族索引需要跨文件寻找完整数据（俗称“回表”）。

  • 在建立索引时我们还可以选择hash索引，为什么一般很少用，它有使用场景吗？

    hash索引数据结果类似于Java的HashMap，也会存在哈希碰撞。查询效率通常来说比B+树更高，但是只适合查找“=”，范围类查找无法使用该类型索引。如果一张表的需求只会有等值查询可以考虑哈希索引（这种需求应该很少吧）。

  • 为什么InnoDB的非主键索引不用聚族索引而保存的是主键索引地址？

    首先是节约存储空间；而更重要的是平衡维护的成本，如果所有索引都是聚族索引，每次的修改增加操作都会维护所有索引，这会涉及到数据的一致性问题（有的索引维护成功有的失败。。。）。

  • 知道联合索引吗，它又有什么不同？

    其实一般来说不推荐针对一个字段建立单值索引，更推荐建立联合索引。

    联合索引同样是B+树来存储，会按照索引字段顺序依次排序放入。

  • B+树类索引是否支持范围查找，原理如何？

    B+树支持范围查找，原理见下图，我相信你会一目了然。。。

为什么需要索引？

通常来说在没有缓存和索引的情况下，Mysql查找任何数据都是读取磁盘文件进行IO操作，并进行逐行扫描，直到查找到目标数据。在数据量很大或关联查询较复杂的情况下会极大的影响查询效率。

而索引作为一种有序并且查询效率极高的数据结构，可以快速的查找目标数据。

索引为何会失效？

索引最佳实践

本次示例数据表结构为：

除了自带的逐渐索引，我额外加了[load_time]字段普通索引

mysql自带工具explain的使用。explain是mysql自带的sql分析工具，使用很简单，只需在sql前面加上“explain”关键字就行。

EXPLAIN SELECT * from fan_painting

可以看到，mysql为我们打印出id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra等十个字段。

  • id列：该列为select语句执行的顺序，其中1为最先，null为最后。（主要针对包含子查询）。

  • select_type列：表示对应行是简单还是复杂的查询。

  • table列：表示该查询正在访问哪个表

  • type列（重点关注）：该查询的大概查询范围

    NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

    const&system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是 const的特例，表里只有一条元组匹配时为system。

    eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

    ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会 找到多个符合条件的行。

    range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

    index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接 对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这 种通常比ALL快一些。

    ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

Mysql的日志系统

日志是mysql很重要的组成部分，它记录着mysql运行的各种信息，主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

我们主要掌握二进制日志和事务日志就行了，这两种也是最重要的。

binlog日志

事务日志其实就是mysql自带的binlog日志，属于server层的。

它记录着所以的sql语句（除了查询）

理论上，只要磁盘够大，它可以一直写下去。

redolog日志

事务日志包括两个，分别是redolog和undolog

undolog日志

事务为什么重要？

什么是ACID？

原子性：一个操作不可分割（通常只业务上的一个操作，可能会涉及到多个sql操作）

一致性：事务开始和结束，对于数据来说结果是一致的（从数据层面的原子性）。

隔离性：当前事务不会受到其他事务的影响。

持久性：事务一旦提交，对数据的改变是永久的，即使出现宕机也能恢复。

高并发场景事务带来的问题

  1. 脏写：一个事务的操作覆盖了第二个事务的结果。

    如：事务A对字段a查询结果为1，然后进行了业务逻辑操作，使a=0，最后将a=0更新到数据库了。但是在更新之前，可能出现事务B已经将a更新成了2，这样就影响了其他事务的更新。

  2. 脏读：一个事务查询到了其他事务未提交的数据。

  3. 不可重复读：一个事务对一条或多条数据进行了多次查询，发现数据有删除或修改（强调的数据的删除和修改）。

  4. 幻读：一个事务对同一批数据进行了多次查询，发现数据有新增（强调数据的新增）。

锁的详解

为什么mysql需要锁？mysql和java一样，为了支持高并发操作，引入了锁的概念。

大体上我们可以将锁分为全局锁、表锁和行锁。

  • 从性能上可分为乐观锁和悲观锁。

    关于悲观锁：通过版本号来实现，每次查询时将版本号字段（整型数字）也查询出来，更新操作时对比版本号与之前是否一致。不一致代表已经被其他事务修改，放弃操作；一致代表没有其他事务操作，在修改数据时将版本号同时+1

  • 从数据库操作类型可分为读锁和写锁。（也成为共享锁和排他锁）

    读锁指多个事务可对同一条数据进行读取，修改时会阻塞直到超时或所有其他事务都提交

    写锁指同一时刻只有一个事务能对数据进行修改

  • 从对数据库操作的粒度可分为表锁和行锁

额外：间隙锁&临键锁

  • 间隙锁：slect * from table where id < 1 and id >10

    以上sql语句执行后，会将id在1~10的所有记录（即使可能范围包含不存在的id）加锁，其他事务无法修改。

  • 临键锁：slect * from table where id < 3 and id >10

    以上sql语句，若表中没有id为3的，会选取id为3所在的区间和10所在区间的合集。

    关于区间：对于上述sql来说，例如表中只有id为1和id为4的数据，范围就是14，那么区间就是(1,4]。或者有id为3和5的数据，范围就35，区间就是[3,5]。

事务隔离级别

事务隔离界别 脏读 不可重复读 幻读
读未提交 可能 可能 可能
读已提交 不可能 可能 可能
可重复读 不可能 不可能 可能
串行化 不可能 不可能 不可能

mysql默认隔离界别为可重复读，已经能满足绝大多数使用场景。

关于串行化：一般不推荐，因为此隔离界别会将所有sql语句串行执行，使性能大幅下降。

posted @ 2021-07-23 15:33  LovelyLM  阅读(4888)  评论(0)  编辑  收藏  举报