2024.10月22日- MySql的 补充知识点

1、什么是数据库事务？

数据库事务: 是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

2、Mysql事务的四大特性是什么？

• 原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。
• 隔离性： 多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。
• 持久性： 表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

3、事务ACID特性的实现原理？

• 原子性：是使用 undo log 来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。
• 持久性：使用 redo log 来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
• 隔离性：通过锁以及 MVCC,使事务相互隔离开。
• 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

有关MVCC相关知识可以参考:看一遍就懂：MVCC原理详解

4、事务的隔离级别有哪些？

• 读未提交（Read Uncommitted）最低级别，任何情况都无法保证
• 读已提交（Read Committed）可避免脏读的发生
• 可重复读（Repeatable Read）可避免脏读、不可重复读的发生
• 串行化（Serializable）可避免脏读、不可重复读、幻读的发生

Mysql默认的事务隔离级别是可重复读(Repeatable Read)

5、什么是脏读、不可重复读、幻读呢？

• 脏读: 脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并不一定最终存在的数据，这就是脏读。
• 不可重复读: 不可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况。
• 幻读: 幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

具体看之前写的一篇文章一文详解脏读、不可重复读、幻读

6、datetime和timestamp的区别？

它们和date的区别在于: date存储精度到天，它们存储精度都为秒。

它们的区别在于：

• datetime 的日期范围是 1001——9999 年；timestamp 的时间范围是 1970——2038 年
• datetime 存储时间与时区无关；timestamp 存储时间与时区有关，显示的值也依赖于时区
• datetime 的存储空间为 8 字节；timestamp 的存储空间为 4 字节
• datetime 的默认值为 null；timestamp 的字段默认不为空(not null)，默认值为当前时间(current_timestamp)

7、varchar和char有什么区别？

char是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间。

在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char。

8、count(1)、count(*) 与 count(列名) 的区别？

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空表示null）的计数，即某个字段值为NULL时，不统计。

阿里巴巴Java开发手册有一条强制建议：不要使用count(列名)或count(常量)来代替count(*)。count(*)就是SQL92定义的标准统计行数语法，跟数据库无关。

9、exist和in的区别？

-- in
  select * from a where id in (select id from b);
  -- exists
 select * from A where exists(select 1 from B where B.id = A.id);

使用in时,sql语句是先执行子查询，也就是先查询子表b，再查主表a。而使用exists是先查主表a ,再查询子表b。

根据小表驱动大表(即小的数据集驱动大的数据集)的原则,如果主查询中的表较大且又有索引时应该用in。 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

还有一点注意的是用 exists 该子查询实际上并不返回任何数据，而是返回值True或False。

not in 和not exists

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

10、truncate、delete与drop区别？

| | delete | truncate | drop | | -------- | ---------------------------------------- | ------------------------------ | -------------------------------------------------- | | 类型 | DML | DDL | DDL | | 回滚 | 可回滚 | 不可回滚 | 不可回滚 | | 删除内容 | 表结构还在，删除表的全部或者一部分数据行 | 表结构还在，删除表中的所有数据 | 表结构也会删除，所有的数据行，索引和权限也会被删除 | | 删除速度 | 删除速度慢，逐行删除 | 删除速度快 | 删除速度最快 |

11、union与union all的区别？

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

从效率上说，union all 要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all

12、group by 和 distinct 的区别？

它们本质语言逻辑上的数据处理动作先后是不一样，distinct 是先获取结果集，再去重复记录。group by 是基于KEY先分组，再返回计算结果。

从效率上来讲 group by和distinct都能使用索引,在相同语义下，从执行效率上也看不到明显的差异;

那为什么，大家都更推崇使用group by？

• group by语义更为清晰
• group by可对数据进行更为复杂的一些处理

由于distinct关键字会对所有字段生效，在进行复合业务处理时，group by的使用灵活性更高，group by能根据分组情况，对数据进行更为复杂的处理，例如通过having对数据进行过滤，或通过聚合函数对数据进行运算。

13、Blob和text有什么区别？

• Blob用于存储二进制数据，而Text用于存储大字符串。
• Blob值被视为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基于列值中的字节的数值。
• text值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较。

14、常见的存储引擎有哪些？

Mysql中常用的四种存储引擎分别是：MyISAM、InnoDB、MEMORY、ARCHIVE。Mysql 5.5版本后默认的存储引擎为InnoDB。

15 说一说InnoDB与MyISAM的区别？

MyISAM和InnoDB两者之间还是有着明显区别

1) 事务支持

MyISAM不支持事务，而InnoDB支持。

2) 表锁差异

MyISAM：只支持表级锁。

InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只有在索引上才可能是行锁，否则还是表锁。

3）索引结构

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，即：MyISAM索引文件和数据文件是分离的，MyISAM的索引文件仅仅保存数据记录的地址。MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的。

InnoDB引擎也使用B+Tree作为索引结构，但是InnoDB的数据文件本身就是索引文件，叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这种索引叫做“聚焦索引”。InnoDB的辅助索引的data域存储相应记录主键的值而不是地址。

4）表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。 InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，其它索引保存的是主索引的值。

5) 表的具体行数

MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。

InnoDB：没有保存表的总行数(只能遍历)，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了where条件后，myisam和innodb处理的方式都一样。

6) 外键

MyISAM：不支持

InnoDB：支持

16 bin log/redo log/undo log是什么？

bin log、redo log、undo log三种日志属于不同级别的日志，按照Mysql的划分可以分为服务层和引擎层两大层，bin log是在服务层实现的；redo log、undo log是在引擎层实现的，且是innodb引擎独有的，主要和事务相关。

1、bin log

bin log是Mysql数据库级别的文件，记录对Mysql数据库执行修改的所有操作，不会记录select和show语句。使用任何存储引擎的 Mysql 数据库都会记录 binlog 日志。

在实际应用中， binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复 。

主从复制 ：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， 从而达到主从数据一致。 数据恢复 ：通过使用 Mysqlbinlog 工具来恢复数据。

2、redo log

redo log中记录的是要更新的数据，比如一条数据已提交成功，并不会立即同步到磁盘，而是先记录到redo log中，等待合适的时机再刷盘，为了实现事务的持久性。

如果没有redo log,那么每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！

一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此 Mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

3、undo log

除了记录redo log外，当进行数据修改时还会记录undo log，undo log用于数据的撤回操作，它保留了记录修改前的内容。通过undo log可以实现事务回滚，并且可以根据undo log回溯到某个特定的版本的数据，实现MVCC。

17 bin log和redo log有什么区别？

• bin log会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；redo log只记录innoDB自身的事务日志。
• bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log不断写入磁盘。
• bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么修改。

18 说一下数据库的三大范式？

• 第一范式：数据表中的每一列（每个字段）都不可以再拆分。
• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
• 第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。

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

存储过程，就是一些编译好了的SQL语句，这些SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后给这些代码块取一个名字，在用到这个功能的时候调用即可。

优点：

• 存储过程是一个预编译的代码块，执行效率比较高
• 存储过程在服务器端运行，减少客户端的压力
• 允许模块化程序设计，只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用
• 一个存储过程替代大量SQL语句 ，可以降低网络通信量，提高通信速率
• 可以一定程度上确保数据安全

缺点：

• 调试麻烦
• 可移植性不灵活
• 重新编译问题

20 主键使用自增ID还是UUID?

推荐使用自增ID,不要使用UUID。

因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降。

21 超大分页怎么处理?

• 用id优化

先找到上次分页的最大ID,然后利用id上的索引来查询,类似于

select * from user where id>1000000 limit 100

这样的效率非常快,因为主键上是有索引的,但是这样有个缺点,就是ID必须是连续的,并且查询不能有where语句,因为where语句会造成过滤数据。

• 用覆盖索引优化

Mysql的查询完全命中索引的时候,称为覆盖索引,是非常快的,因为查询只需要在索引上进行查找,之后可以直接返回,而不用再回表拿数据.因此我们可以先查出索引的ID,然后根据Id拿数据.

select * from table where id in (select id from table where age > 20 limit 1000000,10)

• 在业务允许的情况下限制页数

建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

22 一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

这是一道腾讯的面试题，其实这个问题和上面是同一个问题，都是超大分页的问题,这就像读书的时候做数学题一样，上面是公式、定理，下面是题目，所以要学会举一反三。

1、如果A表TID是自增长,并且是连续的,B表的ID为索引

select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

23 日常开发中是怎么优化SQL的？

这个问题问的挺大的，可以也先从几个大的纬度来回答。

1. 添加合适索引
2. 优化表结构
3. 优化查询语句

然后针对每个大的纬度稍微讲几句。

1、添加合适索引

• 对作为查询条件和order by的字段建立索引。
• 对于多个查询字段的考虑建立组合索引，同时注意组合索引字段的顺序,将最常用作限制条件的列放在最左边，依次递减。
• 索引不宜太多，一般5个以内。

2、优化表结构

选择正确的数据类型，对于提高性能也是至关重要。下面给出几种原则:

• 数字型字段优于字符串类型

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

• 数据类型更小通常更好

使用最小的数据类型,会减少磁盘的空间，内存和CPU缓存。好比时间类型尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。

• 尽量使用 NOT NULL

NULL 类型比较特殊，SQL 难优化。如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。此外，NULL 在索引中的处理也是特殊的，也会占用额外的存放空间。

3、优化查询语句

• 分析语句，是否加载了不必要的字段/数据。
• 分析SQl执行计划，是否命中索引等。
• 如果SQL很复杂，优化SQL结构
• 如果表数据量太大，考虑分表