浅谈MySQL数据库面试必要掌握知识点

概述

**本人博客网站 **IT小神 www.itxiaoshen.com

定义

MySQL官方地址 //www.mysql.com/ MySQL 8系列最新版本为8.0.27，5系列的最新版本5.7.35

MySQL发展至今仍然是世界上最流行的关系型数据库管理系统,使用C和C++语言编写，并且为很多种编程语言提供API.能够处理千万级别的数据，市场占有量还是老大哥地位。

安装

MySQL安装有多种方式，可使用yum安装，也可以从官网下载指定版本解压安装既可，比较简单，安装完完成配置文件，linux下配置文件为my.cnf，windows配置文件为my.ini。

image-20211025143345414

当然还可以使用更为简单docker容器化的安装方式

image-20211025143711516

连接客户端

我们日常连接MySQL客户端工具可以选择使用Navicat For MySQL、SQLyog Community 、MySQL Workbench和phpMyAdmin(web界面操作)，具体就按个人习惯喜好去选择，而我们本篇主要是MySQL面试部分，这里就不太展开了，关于MySQL实战应用部分后续我们再单独来剖析。

MySQL版本

MySQL创立于1996年,最初是由瑞典 MySQL AB公司开发所有,在2008年被Sun公司收购,而Sun公司又在2009年被Oracle公司收购，目前MySQL属于Oracle旗下的产品。MySQL官网提供了多种版本，我们来了解一些，但我们常用的还是社区版本。

  • MySQL Community Server：开源免费的社区版本，不提供技术支持。
  • MySQL Enterprise Edition：收费企业版本或商业版本，可试用，提供技术支持。
  • MySQL Cluster：Cluster集群版，这也是开源免费的，但相对应工具集是收费的，它由一组计算机构成，适合于分布式计算环境的高实用、高冗余版本。它采用了NDB Cluster 存储引擎，允许在1个 Cluster 中运行多个MySQL服务器，也即是可将几个MySQL Server封装成一个Server。
    • 优点
      • MySQL Cluster 自动将表分片（或分区）到不同节点上，使数据库可以在低成本的商用硬件上横向扩展，同时保持对应用程序完全应用透明。
      • 凭借其分布式、无共享架构，MySQL Cluster 可提供 99.999% 的可用性，确保了较强的故障恢复能力和在不停机的情况下执行预定维护的能力。
      • MySQL Cluster 让用户可以在解决方案中整合关系数据库技术和 NoSQL 技术中的最佳部分，从而降低成本、风险和复杂性。
      • MySQL Cluster 提供实时的响应时间和吞吐量，能满足最苛刻的 Web、电信及企业应用程序的需求。 具有跨地域复制功能的多站点集群。
      • 跨地域复制使多个集群可以分布在不同的地点，从而提高了灾难恢复能力和全球 Web 服务的扩展能力。
      • 为支持持续运营，MySQL Cluster 允许向正在运行的数据库模式中联机添加节点和更新内容，因而能支持快速变化和高度动态的负载。
    • 缺点
      • 随数据库容量增加，每个数据数据节点需要添加更多的内存，增加使用成本。
      • 牺牲部分sql语言特性。
  • MySQL Cluster CGE：Cluster高级集群版，需付费

而官方提供另外一个工具MySQL Workbench（GUI TOOL）是Workbench专为MySQL设计的ER/数据库建模工具，它是著名的数据库设计工具DBDesigner4的继任者，MySQL Workbench又分为两个版本，分别是社区版（MySQL Workbench OSS）和商用版（MySQL Workbench SE）。

image-20211025135600397

image-20211025135542655

MySQL其他分支

上面说到MySQL属于Oracle旗下的产品，由于Oracle已经有了一个商业数据库，他们担心MySQL作为领先的免费开源数据库提供的功能可能太少、发布周期太慢并且可能需要支付更昂贵费用，因此许多基于MySQL其他分支也就应运而生，我们也简单了解一下。

  • Percona Server：是MySQL重要的分支之一，它基于InnoDB存储引擎的基础上，提升了性能和易管理性，Percona XtraDB 是 InnoDB 存储引擎的增强版,被设计用来更好的使用更新计算机硬件系统的性能,同时还包含有一些在高性能环境下的新特，可以用来更好地发挥服务器硬件上的性能,XtraDB 设计的主要目的是用以替代现在的 InnoDB。所以Percona Server也可以称为增强的MySQL与开源的插件（plugin）的结合。
    • 由于官方版本的MySQL在一些特性的使用上有一定的局限性，需要收费。所以Percona Server就有了一定的市场占有比例，也比较受大家的欢迎。像一些常用的工具包xtrabackup、percona-toolkit等，在生产环境中是DBA的必备武器。还有像XtraDB-Cluster这种支持多点写入的强同步高可用集群架构，真正实现实时同步的过程，解决了MySQL主从复制之间经常出现并让人头疼的延迟问题。
    • 而且Percona还收购了TokuDB公司，TokuDB存储引擎非常优秀，淘宝网、阿里云上大量在使用这款存储引擎。它支持数据压缩，支持hot scheme modification，具有高扩展性和优秀的查询插入性能。
  • MariaDB：Mariadb是由MySQL创始人Monty创建的，主要由开源社区维护，采用GPL授权许可，是一款高度兼容的MySQL产品，MariaDB提供了MySQL提供的标准存储引擎，即MyISAM和InnoDB；因此实际上可以将它视为MySQL的扩展集，它不仅提供MySQL提供的所有功能还提供其他功能，因此从MySQL切换到MariaDB时无需更改任何基本代码即可。它不仅仅是MySQL的一个替代品，还创新与提高了MySQL原有的技术。它与Percona产品非常类似，既包含了Percona的XtraDB存储引擎，还包含TokuDB存储引擎、Spider水平分片存储引擎等多种存储引擎，并且还有一些复制功能上的新特性，比如基于表的并行复制、Multi-source Replication多源复制、Galera Cluster集群。MariaDB有一套Java的管理系统，可以通过投票机制来决定哪些特性和参数是我们需要的。

HeatWave

我们都知道MySQL主要是为OLTP场景设计的，OLAP一直是MySQL的短板，内置 MySQL 数据库服务实时查询加速器，在HeatWave出现之前，我们通常不能直接使用MySQL数据库进行OLAP分析，因为其性能不足。于是我们往往是先将MySQL数据同步到HDFS/Hbase/Kudu等存储系统上，再使用MR/Spark/Impala等计算引擎做计算,而HeatWave的到来意味着我们可以“少此一举”。

image-20211025103239525

image-20211025140417835

HeatWave 是一种用于 Oracle MySQL 数据库服务的大规模并行、高性能、内存中查询加速器，可将分析和混合工作负载的 MySQL 性能提高几个数量级。HeatWave 以一半的成本比 Amazon Redshift 快 6.5 倍，以五分之一的成本比 Snowflake 快 7 倍，以一半的成本比 Amazon Aurora 快 1400 倍。带有 HeatWave 的 MySQL 数据库服务是唯一使客户能够直接从他们的 MySQL 数据库运行 OLTP 和 OLAP 工作负载的服务。这消除了复杂、耗时且昂贵的数据移动以及与单独分析数据库的集成的需要。新的 MySQL Autopilot 使用先进的机器学习技术来自动化 HeatWave，使其更易于使用并进一步提高性能和可扩展性。

image-20211025140357795

HeatWave是Oracle云上的架构，实现了分布式计算框架，HeatWave 是为 Oracle MySQL 数据库服务开发的内存查询加速器。它是一个大规模并行、混合、列式、查询处理引擎，具有用于分布式查询处理的最先进算法，可为查询提供非常高的性能。与其他OLAP计算框架的区别是，HeatWave与MySQL结合封装得更好。从运维角度看，首先不再需要做数据同步操作，因为数据存储还是MySQL的InnoDB。在计算的时候把InnoDB的数据读到节点的内存中进行计算，此时InnoDB的更新也会即时同步。HeatWave可以智能地自动配置集群大小，而不需要人为配置，不会浪费资源也不会资源不足。从使用角度来看，使用者应该是无感知的，仍然是通过原有方式(JDBC/ODBC)连接和使用，能感知到的只是OLAP查询速度快了很多。

MySQL面试必备

将原理与实际操作经验相结合，本章不讲sql用法，主要针对的是开发人员需掌握的一些MySQL知识点，涉及索引、事务、优化等方面

说说MySQL存储引擎？

image-20211025150915144

MySQL支持存储引擎很多种，默认已支持上面9种，5.7.35版本也是一样，但我们常用存储引擎主要是InnoDB和MyISAM这两种。

MySQL InnoDB和MyISAM两大引擎对比？

  • InnoDB存储引擎：从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB了，他的主要特点有：

    • InnoDB是MySQL5.5之后默认的数据库存储引擎，支持事务，支持外键
      • 使用共享表空间存储：所有的表和索引存放在同一个表空间中。
      • 使用多表空间存储：表结构放在frm文件，数据和索引放在IBD文件中。分区表的话，每个分区对应单独的IBD文件，分区表的定义可以查看我的其他文章。使用分区表的好处在于提升查询效率。
    • 灾难恢复性比较好；
    • 支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
    • 使用的锁粒度为行级锁，可以支持更高的并发；
    • 支持外键；
    • 配合一些热备工具可以支持在线热备份，有很成熟的在线热备解决方案；
    • 在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
    • 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；

  • MyISAM存储引擎：在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少，主要特点为：

    • MyISAM是MySQL5.5之前的默认存储引擎，MyISAM既不支持事务，也不支持外键
      • 每个MyISAM在磁盘上存储成3个文件索引的结构是B+树结构其文件名都和表名相同，但拓展名分别是 ：
        • frm文件：存储表的定义数据,可以存放在不同的目录,平均分布IO，获得更快的速度
        • MYD文件：存放表具体记录的数据,可以存放在不同的目录,平均分布IO，获得更快的速度
        • MYI文件：存储索引,仅保存记录所在页的指针
      • 通过MYI的B+树结构来查找记录页，再根据记录页查找记录。并且支持全文索引、B树索引和数据压缩,支持数据的类型有以下三种
        • 静态固定长度表
        • 动态可变长表
        • 压缩表
    • 不支持事务；
    • 不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用；
    • 对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存；
    • 默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁；
    • 支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch，Solr，Sphinx等。
    • 数据库所在主机如果宕机，MyISAM的数据文件容易损坏，而且难恢复；

  • 查询性能上，MyISAM的查询效率高于InnoDB，因为InnoDB在查询过程中，是需要维护数据缓存，而且查询过程是先定位到行所在的数据块，然后在从数据块中定位到要查找的行；而MyISAM可以直接定位到数据所在的内存地址，可以直接找到数据；

  • SELECT COUNT(*)语句，如果行数在千万级别以上，MyISAM可以快速查出，而InnoDB查询的特别慢，因为MyISAM将行数单独存储了，而InnoDB需要朱行去统计行数；所以如果使用InnoDB，而且需要查询行数，则需要对行数进行特殊处理，如：离线查询并缓存；

MySQL常用存储引擎的底层原理？

这里小编先推荐一个数据结构可视化的网站，可以帮助我们学习各种数据结构的底层原理，常见的查询算法,顺序查找,二分查找,二叉排序树查找,哈希散列法,分块查找,平衡多路搜索树 B 树（B-tree）

数据结构可视化学习网站 //www.cs.usfca.edu/~galles/visualization/Algorithms.html

image-20211025153718960

InnoDB和MyISAM这两种引擎底层都是采用B+树的数据结构来构建索引。

B+树是B树的变种，B树是一棵多路平衡查找树，简单来说，B树可以看做平衡二叉树的进阶版，它与平衡二叉树的不同点主要在B树的一个节点可以存放多个关键字，并且B树的每个节点可以有两个以上的子节点，而这些都取决于B树的阶数，当B树的阶数为2时，它就是一个普通的平衡二叉树。单纯从数据结构来讲，B树和平衡二叉树在查找的时间复杂度上并没有什么区别，但数据结构比平衡二叉树复杂一些，计算机读取数据的操作中最耗时的是从磁盘中读取数据，在大多数linux系统中，每次磁盘io会取出4k的连续数据；平衡二叉树因为每个关键字都存放在独立的节点，无法保证在磁盘中的物理存储地址是连续的，因此，在最坏的情况下，每个关键字的读取都需要进行一次磁盘IO。而B树的每个节点可以存放多个关键字，每个节点的关键字在磁盘中的物理存储地址都是连续的，使得每次磁盘IO都可以读取多个关键字，大大减少了磁盘IO次数，使得查找时间更快。我们描述一棵B树是需要定义它的阶数，阶数定义了它最多会有多少个子节点。下面是一棵3阶的B树示例图，一棵m阶的B树简单定义如下：

  • 每个节点最多可以有m个子节点

  • 每个节点最多可以存放m-1个关键字

image-20211025153854021

B+树是B树的变种，在B+树中，所有的关键字都会保存在叶子节点中，叶子节点之间也会有指针进行连接，形成一个链表的形式，和B树相比，这样的结构方便范围查找。比如要查询大于3的关键字，我们从根节点往下遍历，找到关键字为3的叶子节点之后，直接读取3之后的叶子节点就可以了，而不用一次次的从根节点去遍历大于3的关键字。当我们进行的范围查找进行倒序操作的时候，凭借叶子节点的单向链表是无法实现的，因此MySQL中的B+树结构做了一些调整，MySQL将B+树叶子节点的单向链表改为双向链表。而相同节点的B+树则如下，红色的箭头线是MySQL特殊改造实现的。

image-20211025155643394

B+降低树的高度又能支持范围索引，MySQL在使用Innodb引擎的时候页大小默认是16K，一个三层B+树如果以十几个字节作为一个索引节点，叶子节点为800字节，那么也可存储两千万级别数据（1024*1024*20）

image-20211025160355489

基于B+为底层实现，我们也明白MySQL Innodb为何推荐使用自增ID作为主键，不要使用UUID这种作为主键，因为如果是自增主键增加节点就能顺序依次追加到后面即可，避免B+的结构出现分裂带来性能开销，在InnoDB存储引擎中，主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降.总之,在数据量大- -些的情况下,用自增主键性能会好一-些。

Hash索引和B+树所有有什么区别？

索引是一种数据结构,可以帮助我们快速的进行数据的查找。索引的数据结构和具体存储引擎的实现有关,在MySQL中使用较多的索引有B+树、Hash索引。Hash索引和B+树相比如下：

  • hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是 从根节点出发,查找到叶子节点方可以获得所查键值然后根据查询判断是否需要回表查询数据.
  • hash索引进行等值查询更快(-般情况下),但是却无法进行范围查询.因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持-致,不能支持 范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点多叉树也类似,天然支持范围.
  • hash索引不支持使用索引进行排序,原理同上.
  • hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测AAAA和AAAB的索引没有相关性.
  • hash索 引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
  • hash索引虽然在等 值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定，对于

如何选择合适的存储引擎？

image-20211025154214233

  • 使用场景是否需要事务支持；是否需要支持高并发，InnoDB的并发度远高于MyISAM；是否需要支持外键；是否需要支持在线热备；
  • 高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引；
  • 索引，不同存储引擎的索引并不太一样；在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。
    • InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
    • MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
    • MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
    • MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

什么是聚簇索引和非聚簇索引？

在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引.在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引.如果没有唯一键，则隐式的生成一个键来建立聚簇索引.

当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询.

非聚簇索引一定会回表询吗?
不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,，那么就不必再进行回表查询.

举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.

说说MySQL的联合索引？

MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要 命中索引,需要按照建立索引时的字段顺序挨个使用，否则无法命中索引.MySQL联合索引底层数据结构也还是B+树，遵循索引最左前缀原则，单列索引其实也可以看做索引列为1的联合索引，联合索引的底层存储跟单列索引时类似的，联合索引是每个树节点中包含多个索引值，在通过索引查找记录时，会先将联合索引中第一个索引列与节点中第一个索引值进行匹配，匹配成功接着匹配第二个索引列和索引值，直到联合索引的所有索引列都匹配完；如果过程中出现某一个索引列与节点相应位置的索引值不匹配的情况，则无需再匹配节点中剩余索引列。

image-20211025170426533

比如：索引包含表中每一行的last_name、first_name和dob列，即key(last_name, first_name, dob)，以下情况可以用到索引：

  • 匹配全值：对索引中的所有列都指定具体的值。
  • 匹配最左前缀：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。
  • 匹配列前缀：你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。
  • 匹配值的范围查询：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。
  • 匹配部分精确而其它部分进行范围匹配：可以利用索引查找last name为Allen，而first name以字母K开始的人。
  • 仅对索引进行查询：如果查询的列都位于索引中，则不需要读取元组的值。

MySQL的binlog有几种格式?

MySQL的binlog有三种格式,分别是statement、row、mixed。

  • statement模式下，记录单元为语句.即每- 个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一-些使用了函数之类的语句无法被记录复制。
  • row级别下,记录单元为每一-行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多，日志量太大。
  • mixed. -种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row.此外,新版的MySQL中对row级别也做了- -些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

MySQL如何做调优？

  • 核心理念
    • 选择合适的存储引擎
    • 平衡范式与冗余，为提高效率可以牺牲范式设计，冗余数据。
    • 最大化利用索引，尽可能避免全表扫描，减少无效数据的查询；
    • 不在数据库做计算，cpu计算务必移至业务层。
    • 控制单表数据量，单表记录控制在千万级。控制列数量，字段数控制在20以内。
    • 拒绝3B(big)，也即是大sql，大事务，大批量。
  • 基本原则
    • 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO；
    • 返回更少的数据：只返回需要的字段和数据分页处理 减少磁盘io及网络io；
    • 减少交互次数：批量DML操作，函数存储等减少数据连接次数；
    • 减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少cpu 内存占用；
    • 利用更多资源：使用表分区，可以增加并行操作，更大限度利用cpu资源
  • 字段类优化
    • 用好数值类型：
      • tinyint(1Byte)/smallint(2Byte)/mediumint(3Byte)/int(4Byte)/bigint(8Byte) bad case：int(1)/int(11)
      • 有些字符转化为数字|：用int而不是char(15)存储ip
      • 优先使用enum或set，例如：sex enum (‘F’, ‘M’)
    • 避免使用NULL字段：
      • NULL字段很难查询优化
      • NULL字段的索引需要额外空间
      • NULL字段的复合索引无效
  • 索引原则
    • 选择唯一性索引，唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
    • 为经常需要排序、分组和联合操作的字段建立索引。
    • 为常用作为查询条件的字段建立索引。
    • 限制索引的数目：越多的索引，会使更新表变得很浪费时间。尽量使用数据量少的索引，如果索引的值很长，那么查询的速度会受到影响。尽量使用前缀来索引，如果索引字段的值很长，最好使用值的前缀来索引。
    • 删除不再使用或者很少使用的索引。
    • 最左前缀匹配原则，非常重要的原则。
    • 尽量选择区分度高的列作为索引区分度的公式是表示字段不重复的比例。
    • 索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。
    • 尽量的扩展索引，不要新建索引
  • 索引类优化，例如：
    • 谨慎合理使用索引
    • 改善查询、减慢更新
    • 索引一定不是越多越好（能不加就不加，要加的一定得加）
    • 覆盖记录条数过多不适合建索引，例如“性别”
    • 字符字段必须建前缀索引
    • 不在索引做列运算
    • innodb主键合理使用自增列，主键建立聚簇索引，主键不应该被修改，字符串不应该做主键，如果不指定主键，innodb会使用唯一且非空值索引代替
    • 不用外键，请由程序保证约束
  • sql类优化如，例如：
    • sql语句尽可能简单
    • 一条sql只能在一个cpu运算
    • 大语句拆小语句，减少锁时间
    • 一条大sql可以堵死整个库
    • 简单的事务
    • 事务时间尽可能短
  • 调优方法
    • 通过慢查询找到sql语句
    • MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一-遍查询优化器,之后会拿到对语句的分析,也就是执行计划，其中包含了许多信息.
    • 可以通过其中和索引有关的信息来分析是否命中了索引,例如，possilbe_ key,key,key_ len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度

MySQL如何应对数据量快速增大？

这里先介绍下Apache Sharding Sphere ，当当网开源贡献,已于2020年4月16日成为 Apache 软件基金会的顶级项目

是一套开源的分布式数据库解决方案组成的生态圈，它由 JDBC、Proxy 和 Sidecar（规划中）这 3 款既能够独立部署，又支持混合部署配合使用的产品组成。 它们均提供标准化的数据水平扩展、分布式事务和分布式治理等功能，可适用于如 Java 同构、异构语言、云原生等各种多样化的应用场景。

Apache ShardingSphere 旨在充分合理地在分布式的场景下利用关系型数据库的计算和存储能力，而并非实现一个全新的关系型数据库。 关系型数据库当今依然占有巨大市场份额，是企业核心系统的基石，未来也难于撼动，我们更加注重在原有基础上提供增量，而非颠覆。

Apache ShardingSphere 5.x 版本开始致力于可插拔架构，项目的功能组件能够灵活的以可插拔的方式进行扩展。 目前，数据分片、读写分离、数据加密、影子库压测等功能，以及 MySQL、PostgreSQL、SQLServer、Oracle 等 SQL 与协议的支持，均通过插件的方式织入项目。 开发者能够像使用积木一样定制属于自己的独特系统。Apache ShardingSphere 目前已提供数十个 SPI 作为系统的扩展点，仍在不断增加中。

如果数据量较大基本优化已不能解决，MySQL主从架构开始包括主主、一主多从等，可以逐步考虑优化如下，这里就不展开偏度，后续有时间我们针对这几个单独

  • 读写分离（适合读请求较多，写请求较少场景）
    • Mycat 代理层方案
    • Apache ShardingSphere
  • 分库分表
    • Mycat 代理层方案
    • Apache ShardingSphere
  • NewSql数据库（新一代分布式关系型数据库解决方案）
    • TiDB

如何设计数据库？

  • 需求分析阶段：梳理出数据流程图DFD
  • 概念设计阶段：抽象出具体DBMS的模型E-R图、概念数据模型、逻辑数据模型、物理数据模型
  • 数据库设计三范式、外键的考虑
  • 逻辑设计阶段：外键、索引、视图，数据库选型：数据存储、检索、安全、读写分离、分库分表、数据归档、接入数据仓库
  • 物理设计阶段：存储过程、触发器、用户自定义函数、表空间

阿里Mysql索引规约

  • 【强制】业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
    说明:不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明 显的;另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必 然有脏数据产生。
  • 【强制】超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致;多表关联查询时， 保证被关联的字段需要有索引。
    说明:即使双表 join 也要注意表索引、SQL 性能。
  • 【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
    说明:索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索 引。
  • 【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合 索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。 正例:where a=? and b=? order by c; 索引:a_b_c 反例:索引中有范围查找，那么索引有序性无法利用，如:WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
  • 【推荐】利用覆盖索引来进行查询操作，避免回表。
    说明:如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗?目录浏览 一下就好，这个目录就是起到覆盖索引的作用。 正例:能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查 询的一种效果，用explain的结果，extra列会出现:using index。
  • 【推荐】利用延迟关联或者子查询优化超多分页场景。
    说明:MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过 特定阈值的页数进行 SQL 改写。
    正例:先快速定位需要获取的 id 段，然后再关联:
    SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
  • 【推荐】SQL 性能优化的目标:至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
    说明:
    1)consts 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。 2)ref 指的是使用普通的索引(normal index)。
    3)range 对索引进行范围检索。
    反例:explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级 别比较 range 还低，与全表扫描是小巫见大巫。
  • 【推荐】建组合索引的时候，区分度最高的在最左边。
    正例:如果 where a=? and b=? ，如果 a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。 说明:存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如:where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即索引 idx_d_c。
  • 【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。
  • 【参考】创建索引时避免有如下极端误解:
    1)宁滥勿缺。认为一个查询就需要建一个索引。
    2)宁缺勿滥。认为索引会消耗空间、严重拖慢更新和新增速度。
    3)抵制惟一索引。认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

MySQL同时有多个事务可能会产生什么问题？

多事务的并发进行一般会造成以下几个问题

  • 脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.
  • 不可重复读:当设置A事务只能读取B事务已经提交的部分，会造成在A事务内的两次查询,结果竟然不-样,因为在此期间B事务进行了提交操作.
  • 幻读: A事务读取了一一个范围的内容,而同时B事务在此期间插入了-条数据造成”幻觉”

说说MySQL事务隔离级别?

MySQL和其他关系型数据库一样有四种隔离级别，InnoDB默认使用的是可重复读隔离级别，四种隔离级别如下:

  • 未提交读(READ UNCOMMITTED)
    • 这就是.上面所说的例外情况了,这个隔离级别下，其他事务可以看到本事务没有提交的部分修改因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用.
  • 已提交读(READ COMMITTED)
    • 其他事务只能读取到本事务已经提交的部分.这个隔离级别有不可重复读的问题,在同-一个事务内的两次读取,拿到的结果竟然不一样,因为另外- -个事务对数据进行了修改.
  • REPEATABLE READ(可重复读)
    • 可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有-个新问题,就是幻读,当你读取id> 10的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了-条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥,那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而 上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题.
  • SERIALIZABLE(可串行化)
    • 这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用.

说说对于MySQL锁的理解

image-20211025182128987

  • 从锁的类别上来讲,有共享锁和排他锁

    • 共享锁:又叫做读锁.可以通过lock in share mode实现，这时候只能读不能写，当用户要进行数据的读取时，对数据加上共享锁共享锁可以同时加上多个。
      • select * from t_logs where id = 1 lock in share mode 相当于上了读锁（共享锁）
    • 排他锁:又叫做写锁.它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁、页锁。当用户要进行数据的写入时,对数据加上排他锁排他锁只可以加一-个 ,他和其他的排他锁,共享锁都相斥。
      • select * from t_logs where id = 1 for update 其中增删改操作自动上行锁，相当于上了写锁（排它锁）
    • 锁的粒度取决于具体的存储引擎,InnoDB实现了行级锁,页级锁,表级锁.他们的加锁开销从大大小,并发能力也是从大到小。
    • 表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。
    • 行锁又可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。
    • 间隙锁是可重复读级别下才会有的锁，MVCC(多版本并发控制，实际上就是保存了数据在某个时间节点的快照）和间隙锁,、mvcc+next-key锁、记录锁或者行锁+间隙锁）来解决幻读的问题,。

  • 锁的处理机制分为乐观锁和悲观锁

    • 加锁时机
      • 悲观锁，从数据开始修改时就将数据锁住，直到更改完成才释放锁。
      • 乐观锁，直到数据修改完准备提交时才上锁，完成后释放。
    • 并发性
      • 因为悲观锁是在事务执行中加锁，当并发量高时，就有可能会对其他事务进程造成影响，造成其他事务进程执行时间过程，导致事务超时。
      • 乐观锁是在对数据进行检查时才加锁，锁的时间会少很多，而只有锁住数据的时候会影响其它事务。
    • 两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

  • 活锁：使用优先级队列，先来一直获取不到锁。

  • 死锁：一次性封锁法、顺序封锁法，事先规定顺序。

  • mysql锁分为共享锁和排他锁，也叫做读锁和写锁。

    读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。

    写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁两种。

    表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。

    行锁又可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。

    间隙锁是可重复读级别下才会有的锁，结合MVCC和间隙锁可以解决幻读的问题

mysql怎么实现可重复读？

mysql可重复读主要通过undo log版本链+ReadView 机制实现，具体如下

image-20211025182600753

当 MySQL 执行写操作之前，会把即将被修改的数据记录到 undo log 日志里面。只有这样，事务要回滚的时候，即使 Buffer Pool 中的数据被修改了，依然可以从 undo log 日志中，读取到原插入、修改、删除之前的值，最终把值重新变回去，这就是回滚操作。

image-20211025182633274

日志与日志之间通过 roll_pointer 指针连接，就形成了 undo log 版本链。基于 undo log 版本链实现的 ReadView 机制：ReadView 说白了就是一种数据结构，它主要包含这样几部分：

  • m_ids，当前有哪些事务正在执行，且还没有提交，这些事务的 id 就会存在这里；
  • min_trx_id，是指 m_ids 里最小的值；
  • max_trx_id，是指下一个要生成的事务 id。下一个要生成的事务 id 肯定比现在所有事务的 id 都大；
  • creator_trx_id，每开启一个事务都会生成一个 ReadView，而 creator_trx_id 就是这个开启的事务的 id。

ACID是什么？

事务是一系列的操作,他们要符合ACID特性.最常见的理解就是:事务中的操作要么全部成功,要么全部失败。

  • A=Atomicity，原子性,就是上面说的,要么全部成功,要么全部失败.不可能只执行一部分操作。
  • C=Consistency，系统(数据库)总是从一个-致性的状态转移到另一 个一 致性的状态,不会存在中间状态。
  • I=Isolation，隔离性:通常来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色，意味着有例外情况.
  • D=Durability，持久性,一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果

简单说说MySQL三大日志？

  • binlog 用来保证可以将数据库状态恢复到任一时刻。
    • MySQL 整体来看，分为 Server 层和引擎层，而 binlog 是 Server 层面的 log 文件，也就是所有执行引擎都有 binlog。
  • redo log ：用来保证 crash-safe，InnoDB可以保证即使数据库发生异常重启，之前提交的记录都不会丢失。
    • 分为prepare 和 commit两个步骤也即是“两阶段提交”。
    • 将操作先更新到内存，再写入 redo log，此时标记为 prepare 状态，再写入 binlog，此时再提交事务，将 redo log 标记为 commit 状态。
  • undo log 是用来保证事务需要回滚时数据状态的回滚和 MVCC 时，记录各版本数据信息。
    • undo log 和 redo log 也是引擎层的 log 文件，undo log 提供了回滚和多个行版本控制（MVCC），在数据库修改操作时，不仅记录了 redo log，还记录了 undo log，如果因为某些原因导致事务执行失败回滚了，可以借助 undo log 进行回滚。

MySQL如何保证ACID？

  • Mysql怎么保证一致性的？

    • 从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。例如，原子性无法保证，显然一致性也无法保证。但是，如果你在事务里故意写出违反约束的代码，一致性还是无法保证的。例如，你在转账的例子中，你的代码里故意不给B账户加钱，那一致性还是无法保证。因此，还必须从应用层角度考虑。
    • 从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据。

  • Mysql怎么保证原子性的？

    • 利用Innodb的undo log。
      • undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。例如
        • 当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据。
        • 当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作。
        • 当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作。
      • undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

  • Mysql怎么保证持久性的？

    • 利用Innodb的redo log。
      • 正如之前说的，Mysql是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。
      • 那怎么解决上面这个问题，简单啊，事务提交前直接把数据写入磁盘就行啊。这么做有什么问题？
      • 只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16kb大小，你只改其中一点点东西，就要将16kb的内容刷入磁盘，听着也不合理。
      • 毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。显然操作随机IO，速度会比较慢。
    • 于是决定采用redo log解决上面的问题。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。
    • 采用redo log的好处？
      • 其实好处就是将redo log进行刷盘比对数据页刷盘效率高，具体表现如下
      • redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
      • redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

  • Mysql怎么保证隔离性的？

    image-20211025232810242

    • 利用的是锁和MVCC机制。还是拿转账例子来说明，有一个账户表如下：
      • 设立利用索引，减少锁的竞争。
      • 调整sql执行顺序，让长时间持有事务操作放在前面。
      • 避免大的事务，尽量拆分为多个子事务。
      • 以固定顺序访问表和行。

MySQL如何尽可能避免死锁

  • 以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。
  • 大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
  • 降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
  • 为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。
  • Innodb提供了wait-for graph算法来主动进行死锁检测，Innodb目前处理死锁的方法就是将持有最少行级排他锁的事务进行回滚。这也是相对比较简单的死锁回滚方式。

MySQL主从复制有几种复制方式？

  • 异步复制

    • MySQL主从异步复制是最常见的复制场景。数据的完整性依赖于主库BINLOG的不丢失，只要主库的BINLOG不丢失，那么就算主库宕机了，我们还可以通过BINLOG把丢失的部分数据通过手工同步到从库上去

  • 多线程复制

    • 在MySQL5.7中，带来了全新的多线程复制技术，解决了当master同一个schema下的数据发生了变更，从库不能并发应用的问题，同时也真正将binlog组提交的优势充分发挥出来，保障了从库并发应用Relay Log的能力。
    • 在MySQL8.0中，多线程复制又进行了技术更新，引入了writeset的概念，而在之前的版本中，如果主库的同一个会话顺序执行多个不同相关对象的事务，例如，先执行了Update A表的数据，又执行了Update B表的数据，那么BINLOG在复制到从库后，这两个事务是不能并行执行的，writeset的到来，突破了这个限制。

  • 增强半同步复制

    • 从MySQL5.5开始，引入了半同步复制，此时的技术暂且称之为传统的半同步复制，因该技术发展到MySQL5.7后，已经演变为增强半同步复制(也成为无损复制)。在异步复制时，主库执行Commit提交操作并写入BINLOG日志后即可成功返回客户端，无需等待BINLOG日志传送给从库。

    image-20211025234809060

  • 阿里开源Mysql数据同步工具有Otter、Canal，默认从服务器订阅bin log同步协议，实现数据增量同步。

  • 基于binlog主从复制原理和流程如下

image-20211025233936755

image-20211025234006550

Tags:

VirMach 便宜 VPS

QNews

QNews