mysql笔记 ｜ 青训营笔记

张Gavei
95 阅读1小时+

MySQL基本问题

  1. 什么是MySQL？

    • MySQL是一种开放源代码的关系型数据库管理系统(RDBMS)，因为开源任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。

  2. MySQL常用的存储引擎有什么？他们有什么区别？

    • InnoDB： InnoDB存储引擎支持事务、行锁、外键、聚集索引、MVCC，一致性非锁定读。
    • MyISAM： MyISAM存储引擎不支持事务、支持全文索引、表锁设计，查询比较快（适合用在读写分离的从机上）。
    InnoDBMyISAMMemory
    事务支持不支持不支持
    锁行锁表锁表锁
    外键支持不支持不支持
    B+树索引支持支持支持
    哈希索引支持不支持支持
    聚簇索引支持不支持不支持
    可恢复性重做日志无重做日志
    查询性能强
    文件格式数据和索引集中存储.ibd.frm文件存储表定义；数据和索引分开存储.MYD .MYI

  3. 数据库的三大范式？

    • 第一范式： 表的列不可再分，数据库中表的每一列都是不可分割的基本数据项。
    • 第二范式： 在第一范式的基础上，非主键列完全依赖于主键列，而不能是依赖于主键的一部分。确保表中的每列都和主键相关
    • 第三范式： 在第二范式的基础上，非主键列只能依赖于主键列，不能依赖于其他非主键，确保每列都和主键列直接相关而不是间接相关

    在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能，事实上我们经常会为了性能而妥协数据库的设计。

  4. MySQL的数据类型有哪些？

    • 整数

      TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分别占用8、16、24、32、64位存储空间。值得注意的是，INT(10)中的10只是表示显示字符的个数，并无实际意义。一般和UNSIGNED ZEROFILL配合使用才有实际意义，例如，数据类型INT(3)，属性为UNSIGNED ZEROFILL，如果插入的数据为3的话，实际存储的数据为003。

    • 浮点数

      FLOAT、DOUBLE及DECIMAL为浮点数类型，DECIMAL是利用字符串进行处理的，能存储精确的小数。相比于FLOAT和DOUBLE，DECIMAL的效率更低些。FLOAT、DOUBLE及DECIMAL都可以指定列宽，例如FLOAT(5,2)表示一共5位，两位存储小数部分，三位存储整数部分。

    • 字符串

      字符串常用的主要有CHAR和VARCHAR，VARCHAR主要用于存储可变长字符串，相比于定长的CHAR更节省空间。CHAR是定长的，根据定义的字符串长度分配空间。

      应用场景：对于经常变更的数据使用CHAR更好，CHAR不容易产生碎片。对于非常短的列也是使用CHAR更好些，CHAR相比于VARCHAR在效率上更高些。一般避免使用TEXT/BLOB等类型，因为查询时会使用临时表，造成严重的性能开销。

    • 日期

      比较常用的有year、time、date、datetime、timestamp等，datetime保存从1000年到9999年的时间，精度位秒，使用8字节的存储空间，与时区无关。timestamp和UNIX的时间戳相同，保存从1970年1月1日午夜到2038年的时间，精度到秒，使用四个字节的存储空间，并且与时区相关。

      应用场景：尽量使用timestamp，相比于datetime它有着更高的空间效率。

    • 枚举类型(ENUM) ，把不重复的数据存储为一个预定义的集合

      有时可以使用ENUM代替常用的字符串类型，存储紧凑，会将列表值压缩到一个或两个字节。

  5. 数据库有哪些连接？默认情况是什么连接？

    连接MySQL操作是一个连接进程和MySQL数据库实例进行通信，本质上是进程通信。

    • TCP/IP： TCP/IP套接字方式是MySQL数据库在任何平台下都提供的连接方式。
    • 命名管道和共享内存
    • UNIX域套接字

    默认是使用TCP/IP套接字进行连接。

  6. 什么是存储过程？有哪些优缺点？

    存储过程是一些预编译的SQL语句。

    1、更加直白的理解：存储过程可以说是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

    2、存储过程是一个预编译的代码块，执行效率比较高，一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率，可以一定程度上确保数据安全。

    但是，在互联网项目中，其实是不太推荐存储过程的，比较出名的就是阿里的《Java开发手册》中禁止使用存储过程，我个人的理解是，在互联网项目中，迭代太快，项目的生命周期也比较短，人员流动相比于传统的项目也更加频繁，在这样的情况下，存储过程的管理确实是没有那么方便，同时，复用性也没有写在服务层那么好。

  7. 视图？

    视图是从一个或者多个表（或视图）导出的表，其内容由查询定义。视图是一个虚拟表，数据库中只存储视图的定义，不存储视图对应的数据，在对视图的数据进行操作时，系统根据视图的定义去操作相应的基本表。可以说，视图是在基本表之上建立的表，它的结构和内容都来自基本表，依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

    1. 视图的优点：

    • 简化了操作，把经常使用的数据定义为视图

    • 安全性，用户只能查询和修改能看到的数据

    • 逻辑上的独立性，屏蔽了真实表的结构带来的影响

    1. 视图的缺点：

      性能差，数据库必须把对视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。

  8. MySQL的架构和执行流程？

    img

    SELECT：

    1. 客户端通过TCP连接发送连接请求到mysql连接器，连接器会对该请求进行权限验证及连接资源分配。

    2. 查缓存。（当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。）

    3. 语法分析（SQL语法是否写错了）。 如何把语句给到预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义。

    4. 优化。是否使用索引，生成执行计划。（注意，这一步仅仅是生成执行计划，还没真正执行SQL）

    5. 交给执行器，根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据，完成SQL的执行。

    （1）首先 MySQL 执行器根据执行计划调用存储引擎的API查询数据 （2）存储引擎先从缓存池 buffer pool 中查询数据，如果没有就会去磁盘中查询，如果查询到了就将其放到缓存池中 （3）在数据加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo log 日志文件中 （4）innodb 会在 Buffer Pool 中执行更新操作 （5）更新后的数据会记录在 redo log buffer 中 （6）提交事务在提交的同时会做以下三件事 （7）（第一件事）将 redo log buffer 中的数据刷入到redo log文件中 （8）（第二件事）将本次操作记录写入到 bin log 文件中 （9）（第三件事）将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记 （10）使用一个后台线程，它会在某个时机将我们Buffer Pool中的更新后的数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了

MySQL索引

数据库删除数据的速度和创建索引的数量是成正比的，如果需要删除大量的数据，可以选择先删除索引然后再删除数据。

  1. 什么是索引？

    索引是对数据库表的一列或者多列的值进行排序一种结构，索引的目的是为了提升数据库查询的效率，相当于书的目录。

  2. 索引的优缺点有哪些？

    优点：

    • 通过创建唯一性索引，保持数据库每行数据的唯一性。
    • 可以大大加快数据检索的速度。
    • 将随机I/O变成顺序I/O(逻辑上) 。Multi Range Read优化。
    • 加速表与表之间的连接 。
    • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统性能。

    缺点：

    • 从空间角度考虑，建立索引需要占用物理空间 。
    • 从时间角度考虑，创建和维护索引都需要花费时间，例如对数据进行增删改的时候都需要维护索引。

  3. 索引的数据结构？

    • B+树索引

    • 哈希索引

      InnoDB存储引擎根据表的使用情况自动生成哈希索引，无法人工干预。适合单条记录的查询、不适合范围查询。

  4. Hash索引和B+树的区别？

    因为两者数据结构上的差异导致它们的使用场景也不同，哈希索引一般多用于精确的等值查找，B+索引则多用于除了精确的等值查找外的其他查找。在大多数情况下，会选择使用B+树索引。

    • 哈希索引不支持排序，因为哈希表是无序的。
    • 哈希索引不支持范围查找。
    • 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
    • 哈希索引任何时候都避免不了回表查询。
    • 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。

  5. 索引的类型有哪些？

    MySQL主要的索引类型主要有FULLTEXT，HASH，BTREE，RTREE。【数据结构划分】

    • FULLTEXT

      FULLTEXT即全文索引，MyISAM存储引擎和InnoDB存储引擎在MySQL5.6.4以上版本支持全文索引，一般用于查找文本中的关键字，而不是直接比较是否相等，多在CHAR，VARCHAR，TEXT等数据类型上创建全文索引。全文索引主要是用来解决WHERE name LIKE "%zhang%"等针对文本的模糊查询效率低的问题。

    • HASH

      HASH即哈希索引，哈希索引多用于等值查询，时间复杂度为o(1)，效率非常高，但不支持排序、范围查询及模糊查询等。

    • BTREE

      BTREE即B+树索引，INnoDB存储引擎默认的索引，支持排序、分组、范围查询、模糊查询等，并且性能稳定。

    • RTREE

      RTREE即空间数据索引，多用于地理数据的存储，相比于其他索引，空间数据索引的优势在于范围查找。

  6. 索引的种类有哪些？

    主键索引： 数据列不允许重复，不能为NULL，一个表只能有一个主键索引 。

    联合索引： 由多个列值组成的索引。表上的多个列进行索引，联合索引也是一颗B+树，不同的是联合索引的键值数量不是1，而是大于等于2。(最左匹配原则)

    唯一索引： 数据列不允许重复，可以为NULL，索引列的值必须唯一的，如果是组合索引，则列值的组合必须唯一。

    全文索引： 对文本的内容进行搜索。 现在只有char，varchar，text上可以创建全文索引。类似于一个搜索引擎，在大量数据中寻找。

    普通索引： 基本的索引类型，可以为NULL。

  7. 索引和主键的区别？

    • 主键一定是唯一性的索引，唯一性的索引不一定就是主键。
    • 一个表中可以有多个唯一索引，但是主键只能有一个。
    • 主键列不允许为空值，而唯一性索引列允许空值。
    • 唯一索引表示索引值唯一，可以由一个或者几个字段组成，一个表可以有唯一索引；所以主键也可以由多个字段组成，组成复合主键，同时主键也是唯一索引。

  8. B树和B+树的区别？

    B树和B+树最主要的区别主要有两点：

    • B树中的内部节点和叶子节点均存放键和值，而B+树的内部节点只有键没有值，叶子节点存放所有的键和值。
    • B＋树的叶子节点是通过双向链表相连在一起的，方便顺序检索。

  9. 数据库为什么使用B+树而不是B树？为什么不用红黑树？

    B+树优点

    • 可以减少磁盘IO次数：非叶子节点只存索引值，叶子节点存储数据（和物理页对应，内存与磁盘以页为单位交换数据），适合文件系统，一次IO即可载入更多的索引key信息，可以减少磁盘IO次数。(空间利用率高)

      按B树和B+树来说, B+树的索引页中全部是都是索引,这样一个数据页中能查询到很多索引降低了下一次去磁盘再拿索引页的可能性, 这样就降低了磁盘的IO了。

    • 适合范围查询：logN时间复杂度定位到页，利用页之间的双向链表可以直接找到指定范围的数据。B树进行范围查询必须进行中序遍历。范围查询在数据库中是高频操作！

    • 查询效率稳定：对所有关键词的查询次数（B+树的高度）都是相同的。

    B+树缺点
    • B+树最大的性能问题是会产生大量的随机IO，随着新数据的插入，叶子节点会慢慢分裂，逻辑上连续的叶子节点在物理上往往不连续，甚至分离的很远，做范围查询时，会产生大量读随机IO。对于大量的随机写也一样，举一个插入key跨度很大的例子，如7->1000->3->2000 ... 新插入的数据存储在磁盘上相隔很远，会产生大量的随机写IO。
    • 相对于B树，如果热点数据距离根节点比较近，则B树效率更高。
    B树优点/缺点
    • 离根近的数据查询速度快，可以把热点数据放在这里；在特定数据重复多次查询的场景中更加高效。
    • 查询效率不稳定，非叶子节点数据查询快，叶子节点慢。
    • IO次数相对较多。
    • 不适合范围查询（需要对树进行中序遍历）。
    AVL、红黑树、二叉树的缺点
    • AVL/红黑树是一种二叉搜索树，深度相对较大，磁盘IO次数多（适合采用矮胖的结构）。
    • 不适合范围查找。【中序遍历】
    • 二叉树每个节点只存储一个键值和数据的，如果是B+树，可以存储更多的节点数据，适合磁盘读写，降低IO次数。

    AVL 树和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。 为什么会出现这样的情况，我们知道要获取盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定。

  10. 什么是聚簇索引，什么是非聚簇索引？

聚簇索引和非聚簇索引最主要的区别叶子结点是否都包含完整的行数据信息。

  • 聚簇索引： 聚集索引是根据表的主键构造的一颗B+树，每张表只有一个聚集索引。聚集索引的叶子节点是一个数据页，里面存放了完整的行记录数据（即每一行的所有字段信息），叶子节点/页之间通过双向链表进行连接且按照主键排序，页和页之间逻辑上是连续的、但是物理上可以不连续。

    InnoDB索引组织表，聚簇索引的顺序与表中的记录物理顺序一致，一张表中最多一个聚簇索引。

    优势：根据主键查询条目比较少时，不用回表查询，效率很高。

    缺点：如果碰到不规则的数据插入，则会造成频繁的页分裂；所以聚簇索引的主键值尽量使用连续增长的值，而不是随机值和UUID，否则会造成大量的也分裂与页移动，严重影响性能。

  • 非聚簇索引： 一张表可以有多个非聚集索引（不同的字段都可以设置非聚集索引），它的叶子节点不存储完整的行数据（InnoDB中存储列值+主键；MyISAM中存储行数据的物理地址）。

在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，也是聚簇索引，在主索引之上创建的索引为辅助索引，也是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢，因为辅助索引中的叶子节点存储的是主键。

在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。

image-20210126175248822

  1. 非聚簇索引一定会进行回表查询吗？

    不一定！索引覆盖可以解决非聚集索引回表查询的问题。

    覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

    从非聚簇索引中即可查到记录，可以减小大量的IO操作。

    (username,age)联合索引
select username , age from user where username = 'Java' and age = 22

    explain 中的extra字段显示了是否使用覆盖索引。

  2. 索引下推？【用于联合索引】

    • 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
    • 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
    • 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
    • 根据explain解析结果可以看出Extra的值为Using index condition，表示使用了索引下推。

  3. 索引的使用场景有哪些？

    • 对于中大型表建立索引非常有效，对于非常小的表，一般全部表扫描速度更快些。
    • 对于超大型的表，建立和维护索引的代价也会变高，这时可以考虑分区技术。
    • 如果表的增删改非常多，而查询需求非常少的话，那就没有必要建立索引了，因为维护索引也是需要代价的。
    • 一般不会出现再where条件中的字段就没有必要建立索引了。
    • 多个字段经常被查询的话可以考虑联合索引。
    • 字段多且字段值没有重复的时候考虑唯一索引。
    • 字段多且有重复的时候考虑普通索引。

  4. 什么类型的字段适合作为索引？

    1．选择唯一性索引，字符的区别度很高。 2．为经常需要排序、分组和联合操作的字段建立索引。 3．为常作为查询条件的字段建立索引。 4．限制索引的数目。 5．尽量使用数据量少的索引。 6．尽量使用前缀来索引。 7．删除不再使用或者很少使用的索引。

  5. 索引的设计原则？

    • 最适合索引的列是在where后面出现的列或者连接句子中指定的列，而不是出现在SELECT关键字后面的选择列表中的列。

    • 索引列的基数越大，索引的效果越好，换句话说就是索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差，因为列的基数最多也就是三种，大多不是男性就是女性。

      show index 查看Cardinality的值
Cardinality是一个预估值，表示索引中不重复记录数量的预估值
根据Cardinality值的大小进行选择是否使用索引
// 为了提升性能，Cardinality值是通过随机采样计算得到的

    • 尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，并且索引高速缓存中的块可以容纳更多的键值，会使得查询速度更快。

    • 尽量利用最左前缀。

    • 不要过度索引，每个索引都需要额外的物理空间，维护也需要花费时间，所以索引不是越多越好。

  6. 如何删除、创建索引？

    创建索引：

    • 使用CREATE INDEX 语句

      CREATE INDEX index_name ON table_name (column_list);

    • 在CREATE TABLE时创建

      CREATE TABLE user(
    id INT PRIMARY KEY,
    information text,
    FULLTEXT KEY (information)
);

    • 使用ALTER TABLE创建索引

      ALTER TABLE table_name ADD INDEX index_name (column_list);

    删除索引：

    • 删除主键索引

      alter table 表名 drop primary key

    • 删除其他索引

      alter table 表名 drop key 索引名

    更新索引的信息：

    • analyze table

  7. 使用索引查询是性能一定会提升吗？

    • 不一定，前面在索引的使用场景和索引的设计原则中已经提到了如何合理地使用索引，因为创建和维护索引需要花费空间和时间上的代价，如果不合理地使用索引反而会使查询性能下降。

  8. 什么是前缀索引？

    前缀索引是指对文本或者字符串的前几个字符建立索引，这样索引的长度更短，查询速度更快。

    前缀索引也叫局部索引，比如身份证一共18位，给前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。

    使用场景：前缀的区分度比较高的情况下。

    建立前缀索引的方式

    ALTER TABLE table_name ADD KEY(column_name(prefix_length));

    这里面有个prefix_length参数很难确定，这个参数就是前缀长度的意思。通常可以使用以下方法进行确定，先计算全列的区分度

    SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

    然后在计算前缀长度为多少时和全列的区分度最相似。

    SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

    不断地调整prefix_length的值，直到和全列计算出区分度相近。

    mysql不支持后缀索引的

    解决方案：可以在表中添加一个新列，用于保存要被建立后缀索引的字段倒排值，然后建立前缀索引。

    例如查邮箱分类的后缀。

  9. 什么是最左匹配原则？

    最左匹配原则： 从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like） 后会停止匹配。

    例如建立索引(a,b,c)。

    • 第一种

      select * from table_name where a = 1 and b = 2 and c = 3
select * from table_name where b = 2 and a = 1 and c = 3

      上面两次查询过程中所有值都用到了索引，where后面字段调换不会影响查询结果，因为MySQL中的优化器会自动优化查询顺序。

    • 第二种

      select * from table_name where a = 1
select * from table_name where a = 1 and b = 2 
select * from table_name where a = 1 and b = 2 and c = 3

      答案是三个查询语句都用到了索引，因为三个语句都是从最左开始匹配的。

    • 第三种

      select * from table_name where  b = 1
select * from table_name where  b = 1 and c = 2

      答案是这两个查询语句都没有用到索引，因为不是从最左边开始匹配的

    • 第四种

      select * from table_name where a = 1 and c = 2

      这个查询语句只有a列用到了索引，c列没有用到索引，因为中间跳过了b列，不是从最左开始连续匹配的。

    • 第五种

      select * from table_name where  a = 1 and b < 3 and c < 1

      这个查询中只有a列和b列使用到了索引，而c列没有使用索引，因为根据最左匹配查询原则，遇到范围查询会停止。

    • 第六种

      select * from table_name where  a like 'ab%';
select * from table_name where  a like '%ab'
select * from table_name where  a like '%ab%'

      对于列为字符串的情况，只有前缀匹配可以使用索引，中缀匹配和后缀匹配只能进行全表扫描。

  10. 索引在什么情况下会失效？

    在上面介绍了几种不符合最左匹配原则的情况会导致索引失效，除此之外，以下这几种情况也会导致索引失效。

    • 条件中有or，例如select * from table_name where a = 1 or b = 3
    • 在索引上进行计算会导致索引失效，例如select * from table_name where a + 1 = 2
    • 在索引的类型上进行数据类型的隐形转换，会导致索引失效，例如字符串一定要加引号，假设 select * from table_name where a = '1'会使用到索引，如果写成select * from table_name where a = 1则会导致索引失效。
    • 在索引中使用函数会导致索引失效，例如select * from table_name where abs(a) = 1
    • 在使用like查询时以%开头会导致索引失效
    • 索引上使用!=、<>进行判断时会导致索引失效，例如select * from table_name where a != 1
    • 索引字段上使用 is null/is not null判断时会导致索引失效，例如select * from table_name where a is null

  11. 结合Explain分析Sql语句的索引使用情况

    EXPLAIN SELECT ...

    • id：在一个大的查询语句中每个select关键字都对应一个唯一的ID
    • select_type：select关键字对应的查询类型
    • table：表名
    • partitions：匹配的分区信息
    • type：针对单表的访问方法
    • possible_keys： 可能用到的索引
    • key： 实际上使用的索引
    • key_len： 实际使用到的索引长度，越小越好
    • ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息
    • rows：预估的需要读取的记录数据
    • filtered：某个表经过搜索条件过滤后剩余记录条数的百分比
    • Extra： 一些额外的信息，覆盖索引在这里显示，索引下推

MySQL事务

  1. 什么是数据库的事务？

    • 数据库事务( transaction)是访问并更新数据库中各种数据项的一个程序执行单元。在事务的操作中，要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

  2. 事务的分类？

    • 扁平事务
    • 带有保存点的扁平事务
    • 链事务
    • 嵌套事务 （MySQL数据库和InnoDB存储引擎不支持）
    • 分布式事务 (使用InnoDB存储引擎的事务隔离级别必须设置为SERIALIZABLE)

    长事务：对于长事务的问题，可以转化为小批量的事务来进行处理。当事务发生错误时，只需要回滚一部分数据，然后接着上次已完成的事务继续进行。

  3. 事务的实现原理？

    • 原子性：通过undo log实现。当事务对数据库进行修改时，InnoDB会生成对应的 undo log；如果事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。
    • 持久性：通过redo log+内存缓冲区+磁盘 实现。当有一条记录要更新时，InnoDB 引擎就会先把记录写到 redo log（循环写），并且变成prepared状态，等Service层记录完bin log并刷盘后，redo log变成commit状态，返回客户端。在系统空闲的时候，将redo log缓冲区数据更新到磁盘里面。当数据库崩溃时，可以利用redolog和binlog恢复数据。
    • 隔离性：通过 锁 + MVCC 实现。通过加锁来实现数据写写互斥，通过MVCC来实现可重复读（同一事务每读到的数据都是一致的）。
    • 一致性：数据库通过原子性、隔离性、持久性来保证一致性。

  4. 事务的四大特性是什么？

    • 原子性(atomicity)： 事务是不可分割的工作单位。原子性是指包含事务的操作要么全部执行成功，要么全部失败回滚。
    • 一致性(consistency)： 一致性指事务在执行前后状态是一致的。 数据库从一种状态转变为下一种一致的状态。
    • 隔离性(isolation)： （并发控制、可串行化、锁）一个事务所进行的修改在最终提交之前，对其他事务是不可见的。
    • 持久性(durability)： 事务一旦提交，其结果是永久性的。即使发生宕机等故障，数据库也能将数据恢复。

  5. 数据集的并发一致性问题？

    当多个事务并发执行时，可能会出现以下问题：

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

      事务A更新了数据，但还没有提交，这时事务B读取到事务A更新后的数据，然后事务A回滚了，事务B读取到的数据就成为脏数据了。

    • 不可重复读： 事务A对数据进行多次读取，事务B在事务A多次读取的过程中执行了更新操作并提交了，导致事务A多次读取到的数据并不一致。

    • 幻读： 事务A在读取数据后，事务B向事务A读取的数据中插入了几条数据，事务A再次读取数据时发现多了几条数据，和之前读取的数据不一致。

    • 丢失修改： 事务A和事务B都对同一个数据进行修改，事务A先修改，事务B随后修改，事务B的修改覆盖了事务A的修改。

    不可重复读和幻读看起来比较像，它们主要的区别是：在不可重复读中，发现数据不一致主要是数据被更新了。在幻读中，发现数据不一致主要是数据增多或者减少了。

  6. 数据库的隔离级别有哪些？

    • 读未提交(Read uncommitted)： 一个事务在提交前，它的修改对其他事务也是可见的。
    • 读已提交(Read committed)： 一个事务提交之后，它的修改才能被其他事务看到。
    • 可重复读(Repeatable read)： 在同一个事务中多次读取到的数据是一致的。
    • 串行化(Serializable)： 需要加锁实现，会强制事务串行执行。

    数据库的隔离级别分别可以解决数据库的脏读、不可重复读、幻读等问题。

    • 读未提交：会产生脏读、幻读和不可重复读。
    • 读已提交：可以阻止脏读，但仍会产生不可重复读和幻读。
    • 可重复读：可以阻止脏读和不可重复读，但仍会产生幻读。
    • 串行化：最高的隔离级别，可以完全阻止脏读、幻读和不可重复读。

    MySQL的默认隔离级别是可重复读, 但是通过 next-key lock 解决了幻读，保证了ACID

  7. 隔离级别是如何实现的？

    • 事务的隔离机制主要是依靠锁机制和MVCC(多版本并发控制)实现的，读已提交和可重复读可以通过MVCC实现，串行化可以通过锁机制实现。

  8. 什么是MVCC？

    MVCC(multiple version concurrent control)是一种控制并发的方法，主要用来提高数据库的并发性能。

    在了解MVCC时应该先了解当前读和快照读。

    • 当前读： 读取的是数据库的最新版本，并且在读取时要保证其他事务不会修该当前记录，所以会对读取的记录加锁。
    • 快照读： 不加锁读取操作即为快照读，使用MVCC来读取快照中的数据，避免加锁带来的性能损耗。

    MVCC的作用：在不加锁的情况下，解决数据库读写冲突问题，并且解决脏读、幻读、不可重复读等问题，但是不能解决丢失修改问题。

    MVCC的实现原理： 三个隐藏字段、undo log、read view

    1. 隐藏字段

      • DB_ROW_ID：所需空间6byte，隐含的自增ID，用来生成聚簇索引，如果数据表没有指定聚簇索引，InnoDB会利用这个隐藏ID创建聚簇索引。
      • DB_TRX_ID： 所需空间6byte，最近修改的事务ID，记录创建这条记录或最后一次修改这条记录的事务ID。
      • DB_ROLL_PTR： 所需空间7byte，回滚指针，指向这条记录的上一个版本。【回滚链】

    2. undo log

    3. read view

      Read View是事务进行快照读操作的时候生产的读视图，在该事务执行快照读的那一刻，会生成一个数据系统当前的快照，记录并维护系统当前活跃事务的id，事务的id值是递增的。Read View的最大作用是用来做可见性判断的，也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据。

      • trx_list：一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID。
      • up limit id：trx_list中最小的事务ID。
      • low limit id：Read View生成时刻系统尚未分配的下一个事务ID。

    MVCC流程：

    1. 获取当前DB_TRX_ID，跟Read View中的uplimitid相比较，判断是否小于；

    2. 不符合条件，继续判断是否大于等于lowlimitid；

    3. 不符合条件，判断是否处于trx_list列表中；

    4. 不符合条件，查询该记录的undo log，或继续查找undo log链表中下一个旧记录。

    5. 符合条件，读取数据。【可能是最新版本的记录，也可能是旧版本的记录】。

    在RC隔离级别下，是每个快照读都会生成并获取最新的Read View,而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View.

    MVCC的作用就是在避免加锁的情况下最大限度解决读写并发冲突的问题，它可以实现提交读和可重复度两个隔离级。

    MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

  9. 可重复读是否能解决幻读？

    在可重复读的情况下，MySQL引入MVCC，但MVCC并没有真正解决了幻读。

    InnoDB存储引擎在RR事务隔离级别下使用Next-Key Lock锁的算法，避免了幻读的产生。

    如何解决幻读？

    • 使用串行化读的隔离级别
  • MVCC+next-key locks：next-key locks由record locks(索引加锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)

MySQL锁

  1. 什么是数据库的锁？

    为了支持数据库系统对共享资源进行并发访问，提供数据的完整性和一致性。

    当数据库有并发事务的时候，保证数据访问顺序的机制称为锁机制。

  2. 数据库的锁与隔离级别的关系？

    隔离级别实现方式
    读未提交无需加锁
    读已提交读操作时需要加共享锁，读取操作后释放共享锁
    可重复读读操作时需要加共享锁，直到事务结束后才释放共享锁
    串行化锁定整个范围的键，一直持有锁到事务结束

  3. 数据库锁的类型有哪些？

    • 行锁： 锁的粒度最小，发生锁冲突的概率低，并发度最高。但开销大，加锁速度慢。会出现死锁。
    • 表锁： 锁的粒度最大，并发度最低。开销小，加锁速度快，不会出现死锁。
    • 页锁： 锁的粒度、并发度、资源开销、加锁速度都介于行锁和表锁之间。会出现死锁的情况。

    从锁的类别上区别可以分为共享锁和排他锁(行级锁)

    • 共享锁：共享锁又称读锁，简写为S锁，一个事务对一个数据对象加了S锁，可以对这个数据对象进行读取操作，但不能进行更新操作。并且在加锁期间其他事务只能对这个数据对象加S锁，不能加X锁。 共享锁可以加上多个。

    • 排他锁：排他锁又称为写锁，简写为X锁，一个事务对一个数据对象加了X锁，可以对这个对象进行读取和更新操作，加锁期间，其他事务不能对该数据对象进行加X锁或S锁。排它锁只能加一个，与其他的排它锁和共享锁都相斥。

      SELECT ... FOR UPDATE;			  # 加X行锁
SELECT ... LOCK IN SHARE MODE;	   # 加S行锁

  4. MySQL中InnoDB引擎和行锁模式及其是如何实现的？

    行锁模式

    在存在行锁和表锁的情况下，一个事务想对某个表加X锁时，需要先检查是否有其他事务对这个表加了锁或对这个表的某一行加了锁，对表的每一行都进行检测一次这是非常低效率的，为了解决这种问题，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁，两种意向锁都是表锁。

    • 意向共享锁： 简称IS锁，一个事务打算给数据行加共享锁前必须先获得该表的IS锁。
    • 意向排他锁： 简称IX锁，一个事务打算给数据行加排他锁前必须先获得该表的IX锁。

    有了意向锁，一个事务想对某个表加X锁，只需要检查是否有其他事务对这个表加了X/IX/S/IS锁即可。

    行锁实现方式： INnoDB的行锁是通过给索引上的索引项加锁实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录进行加锁。

    InnoDB行锁主要分三种情况：

    • Record lock： 单个行记录上的锁。
    • Gap lock： 间隙锁，锁定一个范围，但不包含记录本身。
    • Next-key lock： Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。

    InnoDB行锁的特性：如果不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，实际产生的效果和表锁是一样的。

    MVCC不能解决幻读问题，在可重复读隔离级别下，使用MVCC+Next-Key Locks可以解决幻读问题。

    InnoDB使用Next-Key Lock来避免幻读问题（RR级别下），利用next-key Lock将行记录和行记录之间的数据都锁住，其它事务将无法在这个范围内插入新的数据。

  5. 什么是数据库的乐观锁和悲观锁，如何实现？

    乐观锁： 系统假设数据的更新在大多数时候是不会产生冲突的，所以数据库只在更新操作提交的时候对数据检测冲突，如果存在冲突，则数据更新失败。

    乐观锁实现方式：一般通过版本号和CAS算法实现。

    悲观锁： 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。通俗讲就是每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁。

    悲观锁的实现方式：通过数据库的锁机制实现，对查询语句添加for updata。

    场景：

    • 乐观锁：多数用于数据争用不大、冲突较少的环境中，这种环境中，偶尔回滚事务的成本会低于读取数据时锁定数据的成本，因此可以获得比其他并发控制方法更高的吞吐量。【多读写少的情况】
    • 悲观锁：主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。【多写的情况】

  6. 什么是死锁，如何避免？

    死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力的作用，事务将无法推进下去。

    在MySQL中，MyISAM是一次获得所需的全部锁，要么全部满足，要么等待，所以不会出现死锁。在InnoDB存储引擎中，除了单个SQL组成的事务外，锁都是逐步获得的，所以存在死锁问题。

    解决死锁：超时，当两个事务互相等待时，当一个等待时间超过设置的某一个阈值，其中一个事物进行回滚，另一个等待的事务就可以继续运行了。

    死锁检测：wait for graph(等待图)，将锁的信息链表与事务的等待链表构造出一张图，图中若存在回路，则说明存在死锁。

    死锁概率：

    • 系统中事务的数量越多，发生死锁的概率越大；
    • 每个事物操作的数量越多，发生死锁的概率越大；
    • 操作数据的集合越小则发生死锁的概率越大；

    InnoDB存储引擎不存在锁升级的情况。

    如何避免MySQL发生死锁或锁冲突：

    • 如果不同的程序并发存取多个表，尽量以相同的顺序访问表。
    • 在程序以批量方式处理数据的时候，如果已经对数据排序，尽量保证每个线程按照固定的顺序来处理记录。
    • 在事务中，如果需要更新记录，应直接申请足够级别的排他锁，而不应该先申请共享锁，更新时在申请排他锁，因为在当前用户申请排他锁时，其他事务可能已经获得了相同记录的共享锁，从而造成锁冲突或者死锁。
    • 尽量使用较低的隔离级别。
    • 尽量使用索引访问数据，使加锁更加准确，从而减少锁冲突的机会。
    • 合理选择事务的大小，小事务发生锁冲突的概率更低。
    • 尽量用相等的条件访问数据，可以避免Next-Key锁对并发插入的影响。
    • 不要申请超过实际需要的锁级别，查询时尽量不要显示加锁。
    • 对于一些特定的事务，可以表锁来提高处理速度或减少死锁的概率。

  7. 一致性非锁定读、一致性锁定读

    • 一致性非锁定读：读取操作不会等待行上锁的释放，会读取行的一个快照数据。

    快照数据：快照数据是指该行的之前版本的数据，通过undo段来完成。

    • 一致性锁定读：显示的对数据库读取操作进行加锁保证数据逻辑的一致性。

      SELECT ... FOR UPDATE   // X锁
SELECT ... LOCK IN SHARE MODE  // S锁

必须在一个事务中，事务提交后，锁就释放了
BEGIN START TRANSACTION

SQL语句基础知识

  1. SQL语句主要分类？

    • 数据据定义语言DDL（Data Definition Language）： 主要有CREATE，DROP，ALTER等对逻辑结构有操作的，包括表结构、视图和索引。
    • 数据库查询语言DQL（Data Query Language）： 主要以SELECT为主 。
    • 数据操纵语言DML（Data Manipulation Language）： 主要包括INSERT，UPDATE，DELETE 。
    • 数据控制功能DCL（Data Control Language）： 主要是权限控制能操作，包括GRANT，REVOKE，COMMIT，ROLLBACK等。

  2. 为什么不推荐使用select *?

    • 增加不必要的磁盘I/O，TEXT、BLOB等类型最为明显。
    • 加重网络的时延
    • 无法使用覆盖索引

  3. SQL约束有哪些？

    • 主键约束： 主键为在表中存在一列或者多列的组合，能唯一标识表中的每一行。一个表只有一个主键，并且主键约束的列不能为空。
    • 外键约束： 外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列。只有主表的主键可以被从表用作外键，被约束的从表的列可以不是主键，所以创建外键约束需要先定义主表的主键，然后定义从表的外键。
    • 唯一约束： 确保表中的一列数据没有相同的值，一个表可以定义多个唯一约束。
    • 默认约束： 在插入新数据时，如果该行没有指定数据，系统将默认值赋给该行，如果没有设置没默认值，则为NULL。
    • Check约束： Check会通过逻辑表达式来判断数据的有效性，用来限制输入一列或者多列的值的范围。在列更新数据时，输入的内容必须满足Check约束的条件。

  4. 什么是子查询？

    子查询：把一个查询的结果在另一个查询中使用。

    子查询可以分为以下几类：

    • 标量子查询：指子查询返回的是一个值，可以使用 =,>,<,>=,<=,<>等操作符对子查询标量结果进行比较，一般子查询会放在比较式的右侧。

      SELECT * FROM user WHERE age = (SELECT max(age) from user)  //查询年纪最大的人

    • 列子查询：指子查询的结果是n行一列，一般应用于对表的某个字段进行查询返回。可以使用IN、ANY、SOME和ALL等操作符，不能直接使用。

      SELECT num1 FROM table1 WHERE num1 > ANY (SELECT num2 FROM table2)

    • 行子查询：指子查询返回的结果一行n列。

      SELECT * FROM user WHERE (age,sex) = (SELECT age,sex FROM user WHERE name="zhangsan")

    • 表子查询：指子查询是n行n列的一个数据表。

      SELECT * FROM student WHERE (name,age,sex) IN (SELECT name,age,sex FROM class1) //在学生表中找到班级在1班的学生

  5. 了解MySQL的几种连接查询吗？

    MySQl的连接查询主要可以分为外连接，内连接，交叉连接。

    • 外连接：

      外连接主要分为左外连接(LEFT JOIN)、右外连接(RIGHT JOIN)、全外连接。

      左外连接： 显示左表中所有的数据及右表中符合条件的数据，右表中不符合条件的数据为null。

      右外连接： 显示右表中所有的数据及左表中符合条件的数据，左表中不符合条件的数据为null。

      MySQL中不支持全外连接。【不管匹配不匹配，全部显示出来，左表在右边没有的显示NULL，右表在左边没有的显示NULL】

    • 内连接： 只显示符合条件的数据。【结果仅包含符合连接条件的两表中的行】

    • 交叉连接： 使用笛卡尔积的一种连接。【返回左表中所有行与右表中所有行的组合，也称笛卡尔积】

  6. MySQL中的in和exists的区别？

    in和exists一般用于子查询。

    • 使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件；使用in一般会先进行内表查询获取结果集，然后对外表查询匹配结果集，返回数据。
    • in在内表查询或者外表查询过程中都会用到索引。
    • exists仅在内表查询时会用到索引 。
    • 一般来说，当子查询的结果集比较大，外表较小使用exist效率更高；当子查询寻得结果集较小，外表较大时，使用in效率更高。
    • 对于not in和not exists，not exists效率比not in的效率高，与子查询的结果集无关，因为not in对于内外表都进行了全表扫描，没有使用到索引。not exists的子查询中可以用到表上的索引。

  7. varchar和char的区别？

    • varchar表示变长，char表示长度固定。当所插入的字符超过他们的长度时，在严格模式下，会拒绝插入并提示错误信息，在一般模式下，会截取后插入。如char(5)，无论插入的字符长度是多少，长度都是5，插入字符长度小于5，则用空格补充。对于varchar(5)，如果插入的字符长度小于5，则存储的字符长度就是插入字符的长度，不会填充。
    • 存储容量不同，对于char来说，最多能存放的字符个数为255。对于varchar，最多能存放的字符个数是65532。
    • 存储速度不同，char长度固定，存储速度会比varchar快一些，但在空间上会占用额外的空间，属于一种空间换时间的策略。而varchar空间利用率会高些，但存储速度慢，属于一种时间换空间的策略。

  8. MySQL中int(10)和char(10)和varchar(10)的区别？

    int(10)中的10表示的是显示数据的长度，而char(10)和varchar(10)表示的是存储数据的大小。

  9. drop、delete和truncate的区别？

    deletedroptruncate
    类型DMLDDLDDL
    回滚可回滚不可回滚不可回滚
    删除内容表结构还在，删除表的一部分或全部数据删除整个表、数据行、索引和权限表结构还在，删除表中的所有数据
    删除速度删除速度慢，需要逐行删除删除速度最快删除速度较快

    删除整个表，使用drop，删除表的部分数据使用delete，保留表结构删除表的全部数据使用truncate。

  10. UNION和 UNION ALL的区别？

union和union all的作用都是将两个结果集合并到一起。

  • union会对结果去重并排序，union all直接直接返回合并后的结果，不去重也不进行排序。
  • union all的性能比union性能好。

  1. 主键和候选键有什么区别？

    • 表格的每一行都由主键唯一标识,一个表只有一个主键。
    • 主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

MySQL优化

  1. 什么是临时表，什么时候会使用到临时表，什么时候删除临时表？

    MySQL在执行SQL语句的时候会临时创建一些存储中间结果集的表，这种表被称为临时表，临时表只对当前连接可见，在连接关闭后，临时表会被删除并释放空间。

    临时表主要分为内存临时表和磁盘临时表两种。内存临时表使用的是MEMORY存储引擎，磁盘临时表使用的是MyISAM存储引擎。

    一般在以下几种情况中会使用到临时表：

    • FROM中的子查询
    • DISTINCT查询并加上ORDER BY
    • ORDER BY和GROUP BY的子句不一样时会产生临时表
    • 使用UNION查询会产生临时表

  2. 大表数据查询如何进行优化？

    • 索引优化
    • SQL语句优化
    • 主从复制，读写分离
    • 水平拆分
    • 垂直拆分
    • 建立中间表
    • 使用缓存技术
    • 固定长度的表访问起来更快
    • 越小的列访问越快

  3. 了解慢日志查询吗？统计过慢查询吗？对慢查询如何优化？

    慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志。

    相关参数：

    • slow_query_log：是否开启慢日志查询，1表示开启，0表示关闭。
    • slow_query_log_file：MySQL数据库慢查询日志存储路径。
    • long_query_time：慢查询阈值，当SQL语句查询时间大于阈值，会被记录在日志上。
    • log_queries_not_using_indexes：未使用索引的查询会被记录到慢查询日志中。
    • log_output：日志存储方式。“FILE”表示将日志存入文件。“TABLE”表示将日志存入数据库。

    如何对慢查询进行优化？

    • 分析语句的执行计划，查看SQL语句的索引是否命中。
    • 优化数据库的结构，将字段很多的表分解成多个表，或者考虑建立中间表。
    • 优化LIMIT分页。

  4. 为什么要设置主键？

    主键是唯一区分表中每一行的唯一标识，如果没有主键，更新或者删除表中特定的行会很困难，因为不能唯一准确地标识某一行。

  5. 主键一般用自增ID还是UUID？

    使用自增ID的好处：

    • 字段长度较uuid会小很多。
    • 数据库自动编号，按顺序存放，利于检索 。
    • 无需担心主键重复问题 。

    使用自增ID的缺点：

    • 因为是自增，在某些业务场景下，容易被其他人查到业务量。
    • 发生数据迁移时，或者表合并时会非常麻烦 。
    • 在高并发的场景下，竞争自增锁会降低数据库的吞吐能力 。

    UUID：通用唯一标识码，UUID是基于当前时间、计数器和硬件标识等数据计算生成的。

    使用UUID的优点：

    • 唯一标识，不会考虑重复问题，在数据拆分、合并时也能达到全局的唯一性。
    • 可以在应用层生成，提高数据库的吞吐能力。
    • 无需担心业务量泄露的问题。

    使用UUID的缺点：

    • 因为UUID是随机生成的，所以会发生随机IO，影响插入速度，并且会造成硬盘的使用率较低。
    • UUID占用空间较大，建立的索引越多，造成的影响越大。
    • UUID之间比较大小较自增ID慢不少，影响查询速度。

    一般情况MySQL推荐使用自增ID。因为在MySQL的InnoDB存储引擎中，主键索引是一种聚簇索引，主键索引的B+树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增ID，只需要按顺序往后排列即可，如果是UUID，ID是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降。

  6. 字段为什么要设置成not null？

    首先说一点，NULL和空值是不一样的，空值是不占用空间的，而NULL是占用空间的，所以字段设为NOT NULL后仍然可以插入空值。

    字段设置成not null主要有以下几点原因：

    • NULL值会影响一些函数的统计，如count，遇到NULL值，这条记录不会统计在内。
    • B树不存储NULL，所以索引用不到NULL，会造成第一点中说的统计不到的问题。
    • NOT IN子查询在有NULL值的情况下返回的结果都是空值。
    • MySQL在进行比较的时候，NULL会参与字段的比较，因为NULL是一种比较特殊的数据类型，数据库在处理时需要进行特数处理，增加了数据库处理记录的复杂性。

  7. 如何优化查询过程中的数据访问？

    从减少数据访问方面考虑：

    • 正确使用索引，尽量做到索引覆盖
    • 优化SQL执行计划

    从返回更少的数据方面考虑：

    • 数据分页处理
    • 只返回需要的字段

    从减少服务器CPU开销方面考虑：

    • 合理使用排序
    • 减少比较的操作
    • 复杂运算在客户端处理

    从增加资源方面考虑：

    • 客户端多进程并行访问
    • 数据库并行处理

  8. 如何优化长难的查询语句？

    • 将一个大的查询分解为多个小的查询
    • 分解关联查询，使缓存的效率更高

  9. 如何优化LIMIT分页？

    limit offset, length：需要先查到前offset条数据并舍弃掉后才能查到所需数据，当offset很大时，查询效率会非常慢。

    解决方法：先将符合的id查出来，再利用id进行查询数据，id会走索引，查询速度大大提升。

    • 子查询：

      SELECT * FROM product 
WHERE id > =(select id from product limit 866613, 1) limit 20

    • join：

      SELECT * FROM product a JOIN 
(select id from product limit 866613, 20) b ON a.id = b.id

  10. 如何优化UNION查询？

    如果不需要对结果集进行去重或者排序建议使用UNION ALL，会好一些。

  11. 如何优化WHERE子句？

    避免索引失效的情况发生

    • 不要在where子句中使用!=和<>进行不等于判断，这样会导致放弃索引进行全表扫描。
    • 不要在where子句中使用null或空值判断，尽量设置字段为not null。
    • 尽量使用union all代替or 。
    • 在where和order by涉及的列建立索引 。
    • 在where子句中使用参数会导致全表扫描 。
    • 避免在where子句中对字段及进行表达式或者函数操作会导致存储引擎放弃索引进而全表扫描。

  12. SQL语句执行的很慢的原因是什么？

    • 如果SQL语句只是偶尔执行很慢，可能是执行的时候遇到了锁，也可能是redo log日志写满了，要将redo log中的数据同步到磁盘中去。
    • 如果SQL语句一直都很慢，可能是字段上没有索引或者字段有索引但是没用上索引(explain)，单表数据量太大。

  13. SQL语句的执行顺序？

    • FROM：对SQL语句执行查询时，首先对关键字两边的表以笛卡尔积的形式执行连接，并产生一个虚表V1。虚表就是视图，数据会来自多张表的执行结果。
    • ON：对FROM连接的结果进行ON过滤,并创建虚表V2
    • JOIN：将ON过滤后的左表添加进来，并创建新的虚拟表V3
    • WHERE：对虚拟表V3进行WHERE筛选，创建虚拟表V4
    • GROUP BY：对V4中的记录进行分组操作，创建虚拟表V5
    • HAVING：对V5进行过滤，创建虚拟表V6
    • SELECT：将V6中的结果按照SELECT进行筛选，创建虚拟表V7
    • DISTINCT：对V7表中的结果进行去重操作，创建虚拟表V8，如果使用了GROUP BY子句则无需使用DISTINCT，因为分组的时候是将列中唯一的值分成一组，并且每组只返回一行记录，所以所有的记录都h是不同的。
    • ORDER BY：对V8表中的结果进行排序。

  14. 大表如何优化？

    • 限定数据的范围： 避免不带任何限制数据范围条件的查询语句。
    • 读写分离： 主库负责写，从库负责读。
    • 垂直分表： 将一个表按照字段分成多个表，每个表存储其中一部分字段。
    • 水平分表： 在同一个数据库内，把一个表的数据按照一定规则拆分到多个表中。
    • 对单表进行优化： 对表中的字段、索引、查询SQL进行优化。
    • 添加缓存。

  15. 分库分表？

    分表就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。当一张表的查询速度很慢、单表数据量巨大、高并发插入/查询时速度很慢时可以考虑分表。

    为什么要分表？

    • 单表数据量太大（如百万行），读写锁导致查询/插入效率下降（锁导致并发量变小）；
    • 索引维护开销大；
    • 大字段可能影响IO效率（大字段可能导致单条记录跨页）；

    怎么分表？

    • 垂直分表：将一个表按字段拆分成多个表。

      eg：商品表中：商品ID、名称、价格等信息访问频繁，商品描述信息可能访问频次更低（但是空间占用大）；可以将商品描述拆分到一个单独的表中。

      通常我们按以下原则进行垂直拆分:

      • 把不常用的字段单独放在一张表;
      • 把text，blob等大字段拆分出来放在附表中;
      • 经常组合查询的列放在一张表中;

    • 水平分表：水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。

      • Hash分表：对字段进行哈希计算然后取模进行分表（适合批量并行写入，不适合范围查询）
      • Range分表：对字段值范围大小进行分表（适合范围查询）

    分库就是把一个数据库按一定的规则分解成N个数据库（可能在不同机器上）。

    什么场景下需要分库？

    • 单台DataBase的存储空间不够；
    • 查询/写入量太大请求太多，单台数据库无法支撑这么多IO请求；

    怎么分库？

    • 垂直分库：将一个库中的多个表分别放到不同的库中。

      eg：把卖家库（店铺表、商品信息表、商品描述表、地理区域表）分为商品库（商品信息表、商品描述表）和店铺库（店铺表、地理区域表）。

      通常使用以下原则进行垂直分库：

      • 按照业务将表进行分类，专库专用

    • 水平分库：将同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

      eg：商品表随着发展存储数据超过预估且访问频繁，单台数据库容量和性能都无法支撑了；此时可以把商品id分为在x-y的负载数据库1中，其它放在数据库2中。

  16. 分库分表后，ID键如何处理？

    分库分表后会出现的问题：事务支持、跨库join、ID问题等。

    事务问题：

    ① 使用分布式事务：

    优点：由数据库管理，简单有效。 缺点：性能代价高，特别是shard越来越多。

    ② 程序与数据库共同控制实现，原理就是将一个跨多个数据库的分布式事务分解成多个仅存在于单一数据库上面的小事务，并交由应用程序来总体控制各个小事务。

    优点：性能上有优势； 缺点：需要在应用程序在事务上做灵活控制。如果使用了spring的事务管理，改动起来会面临一定的困难。

    跨节点Join的问题：

    解决该问题的普遍做法是分两次查询实现：在第一次查询的结果集中找出关联数据的id，根据这些id发起第二次请求得到关联数据。

    分库分表后不能每个表的ID都是从1开始，所以需要一个全局ID，设置全局ID主要有以下几种方法：

    • UUID：优点：本地生成ID，不需要远程调用；全局唯一不重复。缺点：占用空间大，不适合作为索引。

    • 数据库自增ID：在分库分表表后使用数据库自增ID，需要一个专门用于生成主键的库，每次服务接收到请求，先向这个库中插入一条没有意义的数据，获取一个数据库自增的ID，利用这个ID去分库分表中写数据。优点：简单易实现。缺点：在高并发下存在瓶颈。

    • Redis生成ID：优点：不依赖数据库，性能比较好。缺点：引入新的组件会使得系统复杂度增加

    • Twitter的snowflake算法：是一个64位的long型的ID，其中有1bit是不用的，41bit作为毫秒数，10bit作为工作机器ID，12bit作为序列号。

      1bit：第一个bit默认为0，因为二进制中第一个bit为1的话为负数，但是ID不能为负数.

      41bit：表示的是时间戳，单位是毫秒。

      10bit：记录工作机器ID，其中5个bit表示机房ID，5个bit表示机器ID。

      12bit：用来记录同一毫秒内产生的不同ID。

      美团的Leaf分布式ID生成系统，美团点评分布式ID生成系统

  17. MySQL的复制原理及流程？如何实现主从复制？

    MySQL复制：为保证主服务器和从服务器的数据一致性，在向主服务器插入数据后，从服务器会自动将主服务器中修改的数据同步过来。

    主从复制的原理：

    • 主服务器把数据更改记录到二进制日志中。
    • 从服务器把主服务器的二进制日志复制到自己的中继日志中。
    • 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

    主从复制主要有三个线程：binlog线程，I/O线程，SQL线程。

    • binlog线程： 负责将主服务器上的数据更改写入到二进制日志（bin log）中，并通知从库来读取新数据。

    • I/O线程： 负责从主服务器上读取二进制日志（bin log），并写入从服务器的中继日志（Relay log）中。

    • SQL线程： 负责读取中继日志，解析出主服务器中已经执行的数据更改并在从服务器中重放。

    1. Master在每个事务更新数据完成之前，将操作记录写入到binlog中。
    2. Slave从库连接Master主库，并且Master有多少个Slave就会创建多少个binlog dump线程。当Master节点的binlog发生变化时，binlog dump会通知所有的Slave，并将相应的binlog发送给Slave。
    3. I/O线程接收到binlog内容后，将其写入到中继日志（Relay log）中。
    4. SQL线程读取中继日志，并在从服务器中重放。

    主从复制的作用：

    • 高可用和故障转移： 帮助应用程序避免单点失败。
    • 负载均衡： 降低单个服务器的压力，写操作在主服务器，读操作分散在各个从服务器。
    • 数据备份。
    • 升级测试。

  18. 主从复制的方式？

    主从复制分为：异步复制，半同步复制和全同步复制。

    异步复制： 是MySQL默认的复制模式，主库在执行完客户端提交的事务之后会立刻将结果返回给客户端，并不关心从库接收的结果，这样就会导致当主数据库因为某些原因宕机之后从数据库可能没有同步到主数据库的数据，导致主从数据不一致，甚者如果将从数据库强制转换为主数据库，可能导致数据丢失

    优点：返回客户端无延迟 缺点：主从数据不一致，数据丢失

    半同步复制： 半同步复制解决了主从数据库不一致的问题，原理是只有当至少一个从数据库接收到并且写入到relay log 日志中之后才会返回给客户端接收结果，这样带来的问题是至少会带来一个 TCP/IP的往返时间的延迟

    优点：保证主从数据库的最终一致性 缺点：返回客户端有延时

    全同步复制： 当所有的从库接收到主数据库的数据并且执行完了其中的事务之后才会给客户端返回

    优点：主从数据库的数据强一致性 缺点：响应延迟高

    并行复制： 就是在半同步复制的基础上从数据库会在内部有多个SQL线程去将relay log中的数据写入到数据库中，因为5.6x版本之前一直是单线程的，因此产生主从延迟的几率会很大，现在多线程处理之后，能最大可能的减少主从延迟的几率。

  19. 了解读写分离吗？

    读写分离主要依赖于主从复制，主从复制为读写分离服务。主要是为了数据库读能力的水平扩展。

    读写分离的优势：

    • 增加数据冗余：主服务器宕机后从服务器可以继续提供服务。
    • 提升处理效率：写入比读耗费资源，写入大量数据将影响读取速度。
    • 缓解机器访问压力：可以对读操作进行分散，均衡处理。
    • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销。

    读写分离的实现：

    • 基于应用/client层程序代码实现：代码中根据select、insert等操作进行路由分类，这也是目前使用最广泛的方式。
    • 基于中间代理层/中间件实现：代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到后端数据库。如mysql_proxy，mysql_proxy是Mysql的一个开源项目，通过其自带的lua脚本进行sql判断。

    读写问题面临的问题：

    • 对sql类型进行判断。 如果是select等读请求，就走从库，如果是insert、update、delete等写请求，就走主库。

    • 主从数据同步延迟问题。 因为数据是从master节点通过网络同步给多个slave节点，因此必然存在延迟。因此有可能出现我们在master节点中已经插入了数据，但是从slave节点却读取不到的问题。对于一些强一致性的业务场景，要求插入后必须能读取到，要求读请求强制走主库。

    • 事务问题。 如果一个事务中同时包含了读请求(如select)和写请求(如insert)，如果读请求走从库，写请求走主库，由于跨了多个库，那么jdbc本地事务已经无法控制，属于分布式事务的范畴。而分布式事务非常复杂且效率较低。因此对于读写分离，目前主流的做法是，事务中的所有sql统一都走主库，由于只涉及到一个库，jdbc本地事务就可以搞定。

    • 高可用问题。 主要包括：

      • 新增slave节点： 如果新增slave节点，应用应该感知到，可以将读请求转发到新的slave节点上。
      • slave宕机或下线： 如果其中某个slave节点挂了/或者下线了，应该对其进行隔离，那么之后的读请求，应用将其转发到正常工作的slave节点上。
      • master宕机后切换： 需要进行主从切换，将其中某个slave提升为master，应用之后将写操作转到新的master节点上。

  20. MySQL的自增ID用完了会怎么样？

    当再次插入时，使用的自增ID还是 4294967295，并不会再自增，报主键冲突的错误。所以说，MySQL的自增ID用完了，会导致插入直接报错。原因就是设置的主`int的范围决定的。

    可以使用alter语句修改成bigint。但是alter会导致锁表的问题。

    • （1）首先创建新的临时表，表结构通过命令ALTAR TABLE新定义的结构。
    • （2）然后把原表中数据导入到临时表。
    • （3）删除原表。
    • （4）最后把临时表重命名为原来的表名。

  21. 影响数据库的并发量因素？

    • 服务器的CPU数量、性能。
    • 服务器的SOCKET连接数限制。
    • 请求是否落在一个表上或者多个表上，是查询请求还是修改请求。

日志相关

  1. 日志文件？

    • 错误日志： 对MySQL的启动、运行、关闭过程进行记录。
    • 二进制日志： 记录对MySQL数据库执行更改的所有操作。
    • 慢查询日志： 慢查询日志主要用来定位可能存在问题的SQL，从而进行SQL语句层面的优化。可以将运行时间超过设置阈值的所有SQL都记录到慢查询日志中。
    • 查询日志。

  2. MySQL的binlog有有几种录入格式?分别有什么区别?

    有三种格式，statement，row和mixed.

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

  3. binlog、redolog、undolog的区别和作用？

    • binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

    所以binlog日志并不是InnoDB独有的，它是server层的日志

    既然是server层的日志，它记录的都是事务操作内容，是一种逻辑日志。

    逻辑日志：可以简单理解为记录的就是sql语句 。

    binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

    使用场景：

    1. binlog可以作为恢复数据使用，通过使用 mysql binlog工具来恢复数据。
    2. 主从复制搭建（数据同步）。在 Master端开启 binlog，然后将 binlog发送到各个Slave端，Slave端重放 binlog从而达到主从数据一致。

    刷盘时机：

    MySQL数据库中的任何存储引擎对于数据库的更改都会产生binlog，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？

    • 0： 不去强制要求，由系统自行判断何时写入磁盘；
    • 1： 每次 commit的时候都要将 binlog写入磁盘；【默认】
    • N： 每N个事务，才会将 binlog 写入磁盘。
    • redo log是InnoDB引擎特有的。redo log作为异常宕机或者介质故障后的数据恢复使用。所以这也是持久性的依赖，只要事务提交成功，数据库对已经修改的数据就被永久保存下来了。

    有了binglog为什么还要有redolog？

    • 重做日志是在InnoDB存储引擎层中，而binlog是在数据库中的server层中产生，binlog不只针对InnoDB存储引擎，MySQL数据库中的任何存储引擎对于数据库的更改都会产生binlog。
    • 日志记录内容形式不同。 binlog是一种逻辑日志，其记录的是对应SQL语句；重做日志是物理格式日志，其记录的是对于每个页的修改。
    • 两种日志记录写入磁盘的时间点不同。binlog在事务提交完成后进行一次写入；重做日志在事务进行中不断地被写入，日志并不是随着事务的提交的顺序进行写入的。

redo log 工作过程：

  • MySQL每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。

binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。但只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

  • undo log：用户执行的事务或语句如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用undo log中的备份将数据回滚到修改之前的样子。

    为了不影响其他事务正在进行的工作，使用undo log进行回滚时，实际做的是与先前相反的工作。对于每个INSERT，对应DELETEDELETE 的undo log。而不是直接将页回滚到事务开始之前的样子。

    • undo log实现了MVCC。InnoDB存储引擎中的MVCC是通过undo log来完成的，当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo log读取之前的行版本信息，以此实现一致性非锁定读。

！！！undo log会产生redo log，也就是undo log的产生会伴随着redo log的产生，因为undo log也需要持久性的保护。

  1. redolog和binlog的对比？

    • 来源：redo log是(InnoDB)存储引擎层产生的；而binlog是MySQL服务层产生的，任何存储引擎对数据库的更改都会产生binlog。
    • 内容：redo log是一种物理日志，记录物理页（Page）的修改；binlog是一种逻辑日志，主要记录执行修改的SQL语句（不是简单的字符串，有固定格式的编码）
    • 写入时机：当数据发生更新时，InnoDB会先将数据更新，然后记录redoLog 在内存中（在事务执行时记录），然后定期将redoLog的操作刷新到磁盘上的文件上；binlog只在事务提交时记录，对每一个事务只记录一个日志；事物的提交顺序可能和redolog记录的顺序不一致，且一个事务可能产生多条redolog（可能修改多个页）.
    • 作用：binlog可以作为恢复数据或主从复制时使用；redolog可作为数据库异常之后的数据恢复。
    • 写入方式：binglog为追加写入到文件中，redolog为循环写入，满了则刷新到磁盘文件中（两阶段提交）。

  2. MySQL 有关权限的表都有哪几个？

    MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在 mysql 数据库里，由 mysql_install_db 脚本初始化。这些权限表分别 user，db，table_priv， columns_priv 和 host。下面分别介绍一下这些表的结构和内容：

    • user 权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
    • db 权限表：记录各个帐号在各个数据库上的操作权限。
    • table_priv 权限表：记录数据表级的操作权限。
    • columns_priv 权限表：记录数据列级的操作权限。
    • host 权限表：配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权 限表不受 GRANT 和 REVOKE 语句的影响。
avatar
文章
阅读
粉丝