总结一期mysql面试题

• mysql

  • 使用场景

    • 分表怎么做的?

      • 原来项目中使用过Mycat做的分表,表做的是水平拆分,分表策略用的是hash策略,我知道还有一个是根据主键id值达到一定的值,比如说500万,进行的分表.第二种这个没用过.

    • 数据库三大范式是什么

      • 第一范式：每个列都不可以再拆分。第二范式：非主键列完全依赖于主键，第三范式：非主键列只依赖于主键，不依赖于其他非主键,具体实际项目中还是得根据实际需求来定.

  • sql优化

    • sql优化

      • 第一点呢，就是SQL语句优化，这个是最常见的优化方式。主要是基于慢查询日志，找到慢查询SQL，然后呢，咱们可以基于explain去分析这个SQL语句的执行情况，让每次查询都可以命中索引，避免全盘扫描，从而提升咱们的查询效率。 而第二点呢，是在架构层面，咱们可以做一些表设计层面，以及分库分表的一些事情，那比如字段，咱们都尽量做一些非空约束，一些字段呢，占用资源比较少的数据类型，或者咱们可以做一些反范式的设计，那么其次呢，就是单表数据量如果达到了500万条左右，甚至更高，那么即便这次查询命中了索引，它的效率也是特别慢的。那么这个时候呢，咱们可以利用MyCat中间件，对数据库做水平分表操作，将500万条数据拆分成两张表甚至更多，从而提升咱们的查询效率。那当然也可以做一些垂直分表，咱们可以将一张表基于业务进行拆分，其次单台服务器呢，也是存在性能瓶颈的。那咱们可以做读写分离，将mysql搭建主从的架构，然后呢，主写从读，从而充分发挥咱们服务器硬件的性能。 那么第三点就是可以将一些热点数据做JVM缓存或者是Redis缓存，那么如果查询条件比较复杂，并不适合做缓存，数据量也特别庞大的数据，那咱们也可以将mysql中的数据同步到ElasticSearch中，做一些全文检索的操作。

    • explain分析结果的Type字段参数

      • system： 表只有一行（等于const）。 const： 表有一个匹配行，它仅在读取一次的情况下进行。因为仅有一行，在这行中的列的值可以被优化器剩余部分认为是常数。const用于用主键或唯一索引进行查找的情况。 eq_ref： 每个连接键列都有唯一匹配的行。 通常是在连接中使用主键或唯一索引时发生。 ref： 非唯一性索引扫描，返回匹配某个单独值的所有行。 fulltext： 使用FULLTEXT索引的搜索。 ref_or_null： 类似ref，但是MySQL会额外搜索包含NULL值的行。 index_merge： 表示使用了索引合并优化方法。 unique_subquery： 用于IN子查询中，查找唯一匹配的索引值。 index_subquery： 与unique_subquery类似，但可以用于非唯一索引。 range： 只检索给定范围的行，使用一个索引来选择行。当使用BETWEEN、<、>、>=、<=、IS NULL、<=>、LIKE或IN()操作时，MySQL可以使用这种类型的访问。 index： 全索引扫描，扫描整个索引以查找匹配的行。 ALL： 全表扫描，即遍历全表以找到匹配的行。

  • 索引

    • 索引底层实现原理

      • 底层实现是一个B+树,B+树的叶子节点才会存储数据，非叶子节点不存放数据, 我们创建索引的时候也得考虑到我们这张表的更新频率，如果表里索引比较多的话是比较影响更新速度的，为什么会影响更新速度？因为创建索引的过程其实就是构建一个二叉树，而每次更新完数据都得重新计算二叉树，

    • Mysql使用索引的有什么好处？为什么使用索引会快?

      • 索引就是为了提高数据查询的效率，就像书的目录一样,在数据量比较大的表中,where条件后经常出现的字段就可以创建索引,看创建单列还是联合还是唯一索引,看具体需求,其实索引底层就是给在表里加了一个B+树,加快查询速度,但是加索引还得注意一些问题,比如说经常更新的表不能加索引,因为会影响更新速度,还有就是删除表里数据的时候记得先删除索引,再删除表里数据,再重新创建索引,因为这样可以不破坏索引的B+树结构.

    • B树和B+树有什么区别

      • B+树其实是在B树的基础上做的增强,和B树有两个比较大的区别是,B树的数据存储到每个节点上,而B+树的数据只存储到叶子节点上,非叶子节点只存储索引,

    • 创建索引的原则? 是不是表越多越创建索引?

      • 这个是得看表里的数据多少,还要看表的更新频率去创建索引,比如说表里数据量几十万条，经常出现的where条件后的字段，需要创建索引的话我们才会去创建索引，还有表的更新频率不是特别高的，查询频率比较高的表我们去创建索引。更新频率比较高的情况下没有必要创建索引，因为在增删改的时候会对索引二叉树造成破坏，起不到索引的作用.

    • 索引的话只能作用于单列吗

      • 不是的, 还有联合索引，得看在where后面出现的字段，如果是多个字段那我们就可以创建联合索引,如果单个字段那就创建唯一索引或者普通索引, 看需求情况.

    • 哪些场景不适用索引

      • 第一种情况是:做查询的时候很少用到的列、某个列中包含的数据很少 第二种情况是:数据类型的字段是TEXT、BLOB、BIT等数据类型的字段、 第三种情况是:当在数据表中修改数据的频率大于查询数据频率时等，这些场景不适合创建索引，还有查询字段不会做为 where 条件或者 order by 字段时也不适合创建索引。

    • 解释一下索引什么情况会失效

      • 1.在索引上进行运算,比如说使用sum或者avg什么的会失效. 2.在使用联合索引的时候,没有按照顺序使用也会失效,因为不符合索引的最左匹配原则 3.在索引列使用不等号,或者not查询的时候会失效 4.使用like, %xxx在前面的时候会失效,因为不符合索引的最左匹配原则. 5.使用or关联查询的时候,两个字段没有同时使用索引页会失效.

    • Mysql索引之间有什么区别

      • 我记得大概有五个类型的索引, 主键索引：也叫聚集索引, 普通索引：最基本的索引，没有什么特殊的限制。 唯一索引：与普通索引之间的区别就在于索引列的值必须是唯一的，但是可以有空值。 全文索引：仅可在使用了MyIsam存储引擎的表中使用，针对较大的数据列。 组合索引：也有叫联合索引指多个字段上创建的索引，使用组合索引时遵循最左原则,顺序不能乱,例如A,B,C三个字段是联合索引,如果单独使用BC两个字段索引是生效的,如果使用CB就不会生效,因为打乱了索引的最左匹配原则.

    • 既要删除又要加索引

      • 先删除索引,再删除数据,再加上索引 ,避免破坏索引原理

  • 区别/函数

    • 20、什么是通用SQL函数？

      • 1、CONCAT(A, B)– 连接两个字符串值以创建单个字符串输出。通常用于将两个 或多个字段合并为一个字段。 第 140 页 共 485 页 2、FORMAT(X, D)- 格式化数字 X 到 D有效数字。 3、CURRDATE(), CURRTIME()- 返回当前日期或时间。 4、NOW（）– 将当前日期和时间作为一个值返回。 5、MONTH（），DAY（），YEAR（），WEEK（），WEEKDAY（）– 从日期 值中提取给定数据。 6、HOUR（），MINUTE（），SECOND（）– 从时间值中提取给定数据。 7、DATEDIFF（A，B）– 确定两个日期之间的差异，通常用于计算年龄 8、SUBTIMES（A，B）– 确定两次之间的差异。 9、FROMDAYS（INT）– 将整数天数转换为日期值。

    • MySQL和Oracle区别

      • Mysql相比Oracle更简单更轻量,在开发中mysql用的更多一些。但是Oracle的一些基本点也都知道，Oracle的分页和mysql不一样，用的是伪列，mysql用的是limit。SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21 [控制分页查询的每页的范围]。Mysql主键可以设置自增，Oracle中并没有主键自增，我们可以用sequence(CkunSi)方法来实现主键自增。

  • 锁机制

    • mysql数据库的锁有哪几种

      • 表级锁 isam 当一个事务正在修改某个表的时候，其他事务无法同时修改该表 行级锁 innoDb 行级锁允许多个事务同时修改同一张表，但是只有在修改同一行数据时，才会阻塞其他事务的操作。 数据库准备-数据库自身解决并发两种策略 悲观锁(Pessimistic Lock) 每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁. 乐观锁(Optimistic Lock) 每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，通常使用版本号去控制。

    • 悲观锁乐观锁区别

      • 悲观锁数据库中的悲观锁和乐观锁都是为了保证数据库操作的并发安全性的机制,悲观锁：悲观锁是一种较为保守的并发控制策略。它假设在事务开始之前，其他事务会对数据进行修改，所在在访问数据时会加锁，阻止其他事务对数据的修改或读取，直到当前事务完成。悲观锁主要通过数据库的锁机制实现，如行级锁或表级锁。悲观锁适用场景是并发写入比较频繁的场景，可以有效地防止并发冲突。 乐观锁：乐观锁是一种更为乐观的并发控制策略。它认为在事务开始之前，其他事务不会对数据进行修改，因此不会主动加锁，而是在提交事务时检查数据是否发生了变化。乐观锁通常会使用一个版本号或时间戳来标记数据的版本，每个事务读取数据时都会记录下当前的版本号或时间戳。在提交事务时，会比较当前的版本号或时间戳与事务开始时记录的版本号或时间戳是否一致，如果一致则提交成功，否则需要进行回滚或重试。乐观锁适用于读操作比较频繁，冲突较少的场景，可以提高系统的并发性能。 悲观锁和乐观锁各有优势和适用场景。悲观锁保证了数据的一致性和并发安全性，但在并发较高的情况下会影响系统的性能。乐观锁则通过版本号或时间戳进行乐观的假设，在大部分情况下可以提高系统的并发性能，但需要处理并发冲突的情况。

    • 锁的优化策略

      • 1、读写分离 2、分段加锁 3、减少锁持有的时间 4.多个线程尽量以相同的顺序去获取资源 不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效 率不如一次加一把大锁。

  • 日志

    • 日志分类

      • MySQL日志在数据库管理、故障排查、性能优化等方面发挥着重要的作用。以下是MySQL中常见的几种日志类型： 错误日志（Error Log）： 记录MySQL服务器启动、运行或停止时出现的错误和问题，也包括警告信息。 它是最重要的日志之一，对于检测和解决服务器故障非常关键。 查询日志（Query Log）： 记录所有执行的查询语句，包括客户端的连接信息。 这对于调试查询和追踪查询问题非常有用。 慢查询日志（Slow Query Log）： 记录执行时间超过预设阈值（如long_query_time）的查询语句，或者不使用索引的查询。 这有助于发现性能瓶颈，从而进行优化。 二进制日志（Binary Log，通常称为binlog）： 记录所有对数据库的更改操作，包括插入、更新和删除，但不记录select、show等不修改数据库的SQL。 它主要用于数据库恢复、主从复制等场景。 中继日志（Relay Log）： 在主从复制架构中，从服务器上的中继日志保存了从主服务器的二进制日志中复制而来的事件。 它与二进制日志在结构和内容上几乎相同。 记录支持事务的存储引擎（如InnoDB）执行事务时产生的日志。 它包括重做日志（redo log）和撤销日志（undo log）。重做日志确保事务的持久性，而撤销日志用于事务失败后的回滚。 事务日志： binlog主要就是用来 主从复制和数据恢复的 主从复制就是Master(马斯特)开启binlog 将binlog发送给各个Slave(死嘞) 然后Slave(死嘞)重放binlog达到主从数据一致 数据恢复就是使用mysqlbinlog工具恢复的 就是通过这个工具找到最近误删的时间节点的binlog重新放到临时数据库 然后选择误删的节点 恢复一下 redolog是InnoDB存储引擎的日志主要就是记录事务的变化的 不管事务是否提交都会记录下来 他就是保证事务的持久性 防止发生故障的时候 还有没用的写到磁盘 重启mysql的时候 根据redolog进行重做 达到事务的持久性 主要是由于系统故障，重启数据库的时候能够基于redolog去补全数据 undolog其实就是要么全成功 要么全失败 不会出现部分成功的 原子性的底层其实就是通过undolog实现的 主要就用来记录逻辑变化的 默认情况下，MySQL只会启动错误日志文件，其他日志文件可能需要手动配置才能启用。通过查看和分析这些日志，管理员和开发者可以更好地理解数据库的运行状态，从而进行相应的优化和故障排查。

    • 如果数据库误操作, 如何执行数据恢复?

      • 看你mysql有没有开启那个binlog，然后用mysql自带的mysqlbinlog工具找到最近误操作时间节点的bin log，重放到临时数据库里，然后选择误删的数据节点，恢复一下。

    • MySQL 是如何保证数据不丢失的？

      • 1.只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后恢复数据 2.在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。

  • 事务

    • MySQL支持事务吗？

      • 在缺省模式下，MySQL是 autocommit 模式的，所有的数据库更新操作都会即时 提交，所以在缺省情况下，MySQL是不支持事务的。 但是如果你的MySQL表类型是使用InnoDB Tables 或 BDB tables 的话，你的 MySQL 就可以使用事务处理,使用 SET AUTOCOMMIT=0 就可以使 MySQL 允许在非 autocommit 模式，在非 autocommit 模式下，你必须使用 COMMIT 来提交你的更改，或者用 ROLLBACK 来回滚你的更改

    • 说一下什么事务?

      • 我知道事务有那四个特性,原子性,一致性,隔离性,还有一个是持久性, 一般我们在同时执行两条SQL语句的时候,在service的方法上会加上@Transanal注解来启动事务,保证要么同时成功要么同时失败

    • 说一下事务的隔离级别

      • 事务隔离级别： 读未提交（Read Uncommitted）： 这是最低的隔离级别。 一个事务可以读取另一个未提交事务的修改。 存在的问题：脏读、不可重复读和幻读。 适用于对数据一致性要求不高的场景，例如读取非敏感信息的查询操作。 读已提交（Read Committed）： 大多数数据库系统的默认隔离级别（但不是MySQL的默认）。 一个事务只能读取已经提交的事务的修改。 存在的问题：不可重复读和幻读。 适用于对数据一致性要求较高的场景，例如需要读取其他事务已提交的数据。 可重复读（Repeatable Read）： 这是MySQL的默认隔离级别。 在同一事务内多次读取同一数据，结果都是一致的。 通过多版本并发控制（行级锁+MVCC）实现。 存在的问题：幻读。 适用于对数据一致性要求非常高的场景，例如订单处理、库存管理等需要保证数据一致性的操作。 串行化（Serializable）： 最高的隔离级别。 通过强制事务串行执行，解决了脏读、不可重复读和幻读的问题。 但并发性能最低，因为事务只能一个接一个地执行。 适用于对数据一致性要求极高的场景，例如银行转账操作、库存扣减等需要严格保证数据一致性的操作。

    • MySQL和Oracle的默认事务隔离级别

      • MySQL的默认事务隔离级别是“可重复读”。 在这个级别下，同一个事务内多次读取同一数据的结果是一致的，即使其他事务在此期间修改了这些数据并提交了更改。这确保了在一个事务中执行相同的查询多次时，将看到相同的数据行。 Oracle的默认事务隔离级别是“读已提交”。 在这个级别下，一个事务只能读取到已经提交的数据。换句话说，一个事务不会看到其他未提交事务的修改。这确保了每次读取数据时，都是最新且已经确认的数据。

    • mysql的存储引擎用过哪些,有什么区别？

      • 我们默认用的是innodb支持行级别事务的， 还有一种是ISAM的，这种是支持表级别事务的。