吊打面试官系列——万字长文MySQL篇面试题总结（二）

mysql不同存储引擎的特点及它们各自的应用场景是什么？

• MyISAM

  • MyISAM 引擎读取速度较快，占用资源相对较少，不支持事务，不支持外键约束，但支持全文索引；

  • 使用表级锁，读写互相阻塞，也就是说读数据的时候你就不能写数据，写数据的时候你就不能读数据；

  • MyISAM 引擎只能缓存索引，而不能缓存数据。

  • 适用场景：

  • 不需要事务支持的业务

  • 适用于读数据比较多的业务，不适用于读写频繁的业务

  • 并发相对较低、数据修改相对较少的业务

  • 硬件资源比较差的机器可以考虑使用 MyISAM 引擎

• InnoDB

  • 事务型数据库的首选引擎，支持事务安全表，支持行锁定和外键

  • 具有提交、回滚和崩溃恢复能力的事务安全存储引擎，能处理巨大数据量，性能及效率高，完全支持外键完整性约束;

  • 具有非常高效的缓存特性，能缓存索引也能缓存数据，对硬件要求比较高;

  • 使用场景：

  • 需要事务支持的业务、高并发的业务

  • 数据更新较为频繁的场景，比如 BBS、SNS、微博等

  • 数据一致性要求较高的业务，比如充值转账、银行卡转账

• Innodb是如何实现事务的

数据库join的底层实现是什么？

• 个人理解

  • mysql的join算法是Nested-Loop Join（嵌套循环连接）

  • 要连接的两张表可以分为驱动表和被驱动表

  • 在连接的时候会对驱动表进行全表扫描，然后根据join的列在被驱动表中进行匹配

• 因此在进行多表关联的时候，建议把小的表作为驱动表，然后大的表作为被驱动表，而且保证被驱动表的关联字段应该建立索引，加快匹配

为什么用 B+ 树做索引而不用哈希表做索引?

• 哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而B+树则可以通过最左前缀原则快速找到对应的数据。

• 如果我们要进行范围查找，例如查找ID为100 ~ 400的人，哈希表同样不支持，只能遍历全表。

• 索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的 链表 ，这样的话，查找的时间就会大大增加。

• 哈希表中存储记录行的物理位置

• 哈希索引在进行等值查询的时候速度较快，但是无法进行范围查询，不支持使用索引排序，不支持模糊查询等

• 适用哈希表作为索引的时候，需要将整个哈希表加载进内存中，一次性占用的内存空间较大，但是B+树可以按照节点来进行加载，按序加载，不用一次性占用较大的内存

为什么索引不使用红黑树？

• 在大规模数据存储的时候，红黑树由于树的深度过大，查询效率较低而且磁盘IO读写比较频繁，进而导致效率较低

• 通过B+树，可以有效地提高查询效率和磁盘读写效率，查询效率更加稳定

为什么建议使用主键自增的索引？

• 如果我们的主键是自增的，每次插入的 ID 都会比前面的大，那么我们每次只需要在后面插入就行， 不需要对叶子节点进行移动或者页分裂等操作，这样可以提高性能。也就是为什么建议使用主键自增的索引。

redo log是什么？作用和实现介绍一下？

• 重做日志

• 用来记录数据页上进行了什么修改

• 在 MySQL 中，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就采用了日志（redo log）来提升更新效率

• 而日志和磁盘配合的整个过程，其实就是 MySQL 里的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

• 在同一个事务中，每当数据库进行修改数据操作时，将修改结果更新到内存后，会在redo log添加一行记录记录“需要在哪个数据页上做什么修改”，并将该记录状态置为prepare，等到commit提交事务后，会将此次事务中在redo log添加的记录的状态都置为commit状态，之后在适当的时候（如系统空闲时）将修改落盘时，会将redo log中状态为commit的记录的修改都写入磁盘

• redolog采用循环写的方式记录，当写到结尾时，会回到开头循环写日志

• redolog的大小是固定的，在mysql中可以通过修改配置参数innodb_log_files_in_group和innodb_log_file_size配置日志文件数量和每个日志文件大小

• 参数

  • innodb_log_file_size ：指定每个redo日志大小，默认值48MB

  • innodb_log_files_in_group ：指定日志文件组中redo日志文件数量，默认为2

  • innodb_log_group_home_dir ：指定日志文件组所在路劲，默认值 ./ ，指mysql的数据目录 datadir

  • innodb_mirrored_log_groups ：指定日志镜像文件组的数量，默认为1，此功能属于未实现的功能，在5.6版本中废弃，在5.7版本中删除了。

• redo log也是需要写入磁盘的，但是它是顺序IO，比起直接将内存的脏页写到磁盘的随机IO要快很多

• write pos表示日志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1从头开始记录；check point表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦除掉，即write pos->checkpoint之间的部分是redo log空着的部分，用于记录新的记录，checkpoint->write pos之间是redo log待落盘的数据修改记录。当writepos追上checkpoint时，得先停下记录，先推动checkpoint向前移动，空出位置记录新的日志。

• 有了redo log，当数据库发生宕机重启后，可通过redo log将未落盘的数据恢复，即保证已经提交的事务记录不会丢失。

• 特点

  • redo log的大小是固定的，日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作。

  • redo log是innodb引擎层实现的，并不是所有引擎都有。

  • 重做日志都是以512字节进行存储的，称之为重做日志块，与磁盘扇区大小一致，这意味着重做日志的写入可以保证原子性，不需要doublewrite技术

• innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

binlog是什么？作用和实现介绍一下？

• 特点

  • binlog 记录了数据库表结构和表数据变更

  • binlog是server层实现的，意味着所有引擎都可以使用binlog日志

  • binlog通过追加的方式写入的，可通过配置参数max_binlog_size设置每个binlog文件的大小，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上。

  • binlog有两种记录模式，statement格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

  • 备注: 每个事务 binlog 的末尾，会记录一个 XID event，标志着事务是否提交成功，也就是说，recovery 过程中，binlog 最后一个 XID event 之后的内容都应该被 放弃。

  • sync_binlog：设置为1，表示每次事务的binlog都直接持久化到磁盘（注意是这里指的是binlog日志本身落盘），保证mysql重启后binlog记录是完整的。

    • show variables like ‘sync_binlog’;

• 对于事务引擎，每次事务提交的时候，都会写binlog

• 对于非事务引擎，每条SQL语句都会写到binlog

• 作用

  • 数据复制：数据库的数据出问题以后，可以通过binlog来对数据进行恢复

  • 数据恢复： 在主服务器和从服务器间进行数据复制，保持数据的一致性

• 区别 ：

  • redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

  • redo log 是物理日志，记录的是在某个数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑。

  • redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

  • redo log 事务开始 的时候，就开始记录每次的变更信息，而 binlog 是在 事务提交 的时候才记录。

  • MySQL需要保证 redo log 和 binlog 的 数据是一致 的，如果不一致，主从服务器间的数据就会不一致了

• 录入格式：

  • statement：综合了上述两者，没有函数时，使用statement，有函数时使用row，但是对于系统变量仍然会出现主从不一致

  • row： 用来记录发生改变的行以及对应的改变，但是存在效率问题，可能一条SQL能设置完的数据，需要一条一条遍历整个表才能做完

  • mixed： 用来记录执行的语句，该模式的话对于函数无法保证数据一致性，如now函数

• 有了对这两个日志的概念性理解后，再来看执行器和 InnoDB 引擎在执行这个 update 语句时的内部流程。

  1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

  2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

  3. 引擎将这行新数据更新到内存（InnoDB Buffer Pool）中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

日志相关问题

1 怎么进行数据恢复？

• binlog 会记录所有的逻辑操作，并且是采用追加写的形式。当需要恢复到指定的某一秒时，比如今天下午二点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

  • 首先，找到最近的一次全量备份，从这个备份恢复到临时库

  • 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

• 这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

2 数据库崩溃恢复，是从 redo log 更新过来的还是从 buffer pool 更新过来的呢？

• 实际上，redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在由 redo log 更新过去数据最终落盘的情况。

  • checkpoint->write pos之间是redo log待落盘的数据修改记录，因此崩溃恢复就是将这部分的数据页读取到内存中，然后使用redo日志更新内存内容，最终将脏页落盘。

  1. 数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程与 redo log 毫无关系。

  2. 在 崩溃恢复场景 中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

3 redo log 和 binlog 是怎么关联起来的?

• redo log 和 binlog 有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

  • 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；

  • 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

为什么建议单表的数据量不宜过大？

• 应该与IO次数有关

• 单表数据量过大，索引也会增多，需要的磁盘IO次数就多了

SQL注入问题介绍一下？

• 问题描述

  • 未将代码与数据进行严格隔离，导致在读取用户数据时，错误地将数据作为代码的一部分执行

• 解决方法

  • 过滤用户输入参数的特殊字符

  • 禁止通过字符串拼接的SQL语句，使用参数绑定传入的SQL参数，参数化绑定SQL变量

MySQL事务相关语句介绍一下？

• 操作

  • 开启事务：start transaction;

  • 回滚：rollback;

  • 提交：commit;

数据库连接池是什么？

• 将数据库的连接交给连接池来管理

  • 可以更好地管理连接资源

  • 当请求到来时，可以直接从池中获取连接使用，效率更高

  • 当不需要使用连接时，归还连接给连接池，不用关闭销毁，重复创建

• 分类

  • C3P0

  • Druid

• 流程：

  • 导入jar包

  • 定义配置文件

  • 加载配置文件

  • 创建连接池对象

  • 获取连接

SQL选用索引的执行成本如何分析？

• 在有多个索引的情况下， 在查询数据前，MySQL 会选择成本最小原则来选择使用对应的索引

• 成本：

  • IO 成本：MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理。一页的成本就是 1， IO 的成本主要和页的大小有关

  • CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2

• 在MySQL5.6以后，对于无where条件的 select count(*) from table ，会进行相应的优化

• MySQL会选择成本最小的辅助索引查询方式来计数

• 但是，如果select count(*)中包含了where语句的话，最终还是会使用全表扫描

MySQL的全局参数如何查询和设置？

• 查询：

  • show variables like ‘sql_mode’;

• 设置：

  • set sql_mode=‘ONLY_FULL_GROUP_BY’;

  • set global slow_query_log=1;

慢查询日志是什么？有什么作用？如何使用？

• 用来记录运行时间超过long_query_time的SQL

• 开启方法：

  • 默认不开启

  • SHOW VARIABLES LIKE ‘%slow_query_log%’;

  • set global slow_query_log=1;

• 修改超时时间

  • SHOW VARIABLES LIKE ‘long_query_time%’;

  • set long_query_time=1

  • 可以通过命令修改，也可以在my.cnf参数里面修改

  • 当时间大于该值时才会被记录下来

• 配置文件：

  my.cnf
  

  【mysqld】下配置：

  slow_query_log=1
  slow_query_log_file=/var/lib/mysql/atguigu-slow.log
  long_query_time=3
  log_output=FILE

• 日志分析工具mysqldumpslow

• 用来配合慢查询日志进行分析

• 对于找到的慢查询SQL，使用EXPLAIN进行分析

如何对慢查询语句进行优化？

• 优化思路：

  • 查询条件是否命中索引？

  • 是否加载了不需要的数据？

  • 数据量太大了？

• 优化方法：

  • explain分析语句执行计划，查看索引的使用情况，如果索引的使用不符合预期，则进行语句修改或者添加索引

  • 查看是否加载了多余的行或者列数据，能使用覆盖索引尽可能使用覆盖索引

  • 如果语句已经足够优化了，可以考虑是否表中的数据太大了，如果是可以考虑进行横向或者纵向分表

常用引擎有哪些？

• Innodb引擎 ：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。

• MyIASM引擎 (原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。

• MEMORY引擎 ：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MyISAM与InnoDB的区别？

MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。

• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。

• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

MyISAM的读取性能为什么更高？

• MyISAM是非聚集索引，索引跟数据是分开来的，在进行查询操作的时候，可以先加载索引文件，然后再根据从索引文件中得到的文件偏移来定位数据的位置

  • 由于它使用的是非聚集索引，因此索引大小要比innoDB的索引文件要小，IO次数要少

• InnoDB是事务型引擎，在默认的可重复读的情况下，它会通过MVCC来维护隔离级别

  • 在MVCC机制下，读取数据会判断数据行的版本号是否在当前事务版本号之前，如果是则可以使用，否则需要在undo log中寻找符合条件的数据

  • MyISAM不支持事务，因此不用进行此操作，所以能够更快一些

索引失效的情况介绍一下？

• 多列索引中，应该遵守最佳左前缀法则，即过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

• 不要对索引列进行任何操作，否则会导致索引失效

• 存储引擎不能使用索引中范围条件右边的列

• mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

• is not null 也无法使用索引,但是is null是可以使用索引的

• like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作

• 字符串不加单引号索引失效

  • 隐式转换的影响

  • 当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。下面的案例中，date_str 是字符串，然而匹配的是整数类型，从而发生隐式转换。

建立索引的原则有哪些？

• 尽量选择针对当前query过滤性更好的索引

• 当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好

• 尽量选择可以能够包含当前query中的where字句中更多字段的索引

• 如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

• 更新频繁字段不适合创建索引

• 尽量的扩展索引，不要新建索引

• 外键数据列一定建立索引

• 索引尽量非空

创建和删除索引的语句？

• 在执行CREATE TABLE时创建索引

  CREATE TABLE user_index2 (
  id INT auto_increment PRIMARY KEY,
  first_name VARCHAR (16),
  last_name VARCHAR (16),
  id_card VARCHAR (18),
  information text,
  KEY name (first_name, last_name),
  FULLTEXT KEY (information),
  UNIQUE KEY (id_card)
  );

• 使用ALTER TABLE命令去增加索引

  ALTER TABLE table_name ADD INDEX index_name (column_list);

• 使用CREATE INDEX命令创建

  CREATE INDEX index_name ON table_name (column_list);

• 删除索引

  alter table user_index drop INDEX name;

百万级数据如何删除？

• 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）

• 然后删除其中无用数据（此过程需要不到两分钟）

• 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。

• 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

前缀索引的作用？

• 目的：利用前缀索引来减少索引的大小

• 语法： index(field(10)) ，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引。

• 利用 select count(*)/count(distinct left(password,prefixLen)); 得到前缀的区分度，进而确定前缀的合适长度

MySQL中InnoDB引擎的行锁是怎么实现的？

• InnoDB是基于索引来完成行锁

• 例: select * from tab_with_index where id = 1 for update;

• for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将使用表锁，并发将无从谈起

InnoDB引擎的锁有哪几种？

• Record lock：单个行记录上的锁

• Gap lock：间隙锁，锁定一个范围，不包括记录本身

• Next-key lock：record+gap 锁定一个范围，包含记录本身

数据库的死锁解决方法有哪些？

• 解决方法

  • 尽可能一次锁定全部所需资源

  • 提升锁的粒度，升级为表级锁

SQL 约束有哪几种？

• 非空约束

• 唯一约束

• 主键约束

• 外键约束

• CHECK: 用于控制字段的值范围