【面试题】MySQL数据库

什么是视图，视图的作用是什么？

视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。
视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。
如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，此后查询此视图即可，简化复杂查询；
视图本质上就是一条SELECT语句，所以当访问视图时，只能访问到所对应的SELECT语句中涉及到的列，对基表中的其它列起到安全和保密的作用，可以限制数据访问

什么是索引？MySQL中有哪些类型的索引？

• 索引是一种数据结构，用于提高数据检索速度。
• MySQL中包括B-Tree索引（最常见）、Hash索引、全文索引、空间索引、位图索引等。解释每种索引的特点和适用情况。

简述索引设计原则？

• 选择唯一性索引；唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
• 为常作为查询条件的字段建立索引；如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
• 为经常需要排序、分组和联合操作的字段建立索引；经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
• 限制索引的数目；每个索引都需要占⽤用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。
• 小表不建议索引（如数量级在百万以内）；由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
• 尽量使用数据量少的索引；如果索引的值很长，那么查询的速度会受到影响。此时尽量使用前缀索引。
• 删除不再使用或者很少使用的索引。

简述索引的数据结构？

索引的数据结构和具体存储引擎的实现有关，MySQL 中常用的是 Hash 和 B+ 树索引。

• Hash 索引底层就是 Hash 表，进行查询时调用 Hash 函数获取到相应的键值（对应地址），然后回表查询获得实际数据.
• B+ 树索引底层实现原理是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可以获得所查键值，最后查询判断是否需要回表查询.

简述Hash 和 B+ 树索引的区别？

Hash

• 1）Hash 进行等值查询更快，但无法进行范围查询。因为经过 Hash 函数建立索引之后，索引的顺序与原顺序无法保持一致，故不能支持范围查询。同理，也不支持使用索引进行排序。
• 2）Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，如 AA 和 AB 的算出的值没有相关性。
• 3）Hash 任何时候都避免不了回表查询数据.
• 4）虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生 Hash 碰撞，此时查询效率反而可能降低。
  B+ Tree
• 1）B+ 树本质是一棵查找树，自然支持范围查询和排序。
• 2）在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表。
• 3）查询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。

列出MySQL中导致索引失效的情况？

• 模糊查询like使用“name%”索引可用，“%name”索引失效
• 组合索引包含从左到右的字段使用索引，不包含左边的字段索引失效
• 组合索引范围搜索，范围搜索后的字段不使用索引
• 条件字段数据类型不匹配，导致索引失效
• 联合查询时，字符集不匹配导致索引失效
• 不等于导致索引失效，不等于的情况包括（!= 、<、>、not in）
• 字段内容为null,导致索引失效
• or前后条件都包含索引则走索引，or前后有一个不包含索引索引失效
• 添加索引的字段上使用函数或者计算，导致索引失效，函数包括ABS,UPPER,DATE,DAY,YEAR等

简述数据库事务的特性？

• 原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做
• 一致性。事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。
• 隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
• 持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

简述事务的并发问题？

• 脏读：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。
• 不可重复读：一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。
• 幻读：一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。 解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

68.事务的隔离级别有哪些？

• 串行化的隔离级别最高，读未提交的级别最低，级别越高，则执行效率就越低，所以在选择隔离级别时应该结合实际情况。
• MySQL 支持以上四种隔离级别，默认为 Repeatable read (可重复读)；而 Oracle 只支持 Serializeble(串行化) 级别和 Read committed(读已提交) 两种，其中默认为读已提交。

简述MyISAM 和 InnoDB 的区别？

1）InnoDB 支持事务，而 MyISAM 不支持。
2）InnoDB 支持外键，而 MyISAM 不支持。因此将一个含有外键的 InnoDB 表 转为 MyISAM 表会失败。
3）InnoDB 和 MyISAM 均支持 B+ Tree 数据结构的索引。但 InnoDB 是聚集索引，而 MyISAM 是非聚集索引。
4）InnoDB 不保存表中数据行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量记录了整个表的行数，速度相当快（注意不能有 WHERE 子句）。
那为什么 InnoDB 没有使用这样的变量呢？因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的。
5）InnoDB 支持表、行（默认）级锁，而 MyISAM 支持表级锁。
InnoDB 的行锁是基于索引实现的，而不是物理行记录上。即访问如果没有命中索引，则也无法使用行锁，将要退化为表锁。
6）InnoDB 必须有唯一索引（如主键），如果没有指定，就会自动寻找或生产一个隐藏列 Row_id 来充当默认主键，而 Myisam 可以没有主键。
7）MySQL默认使用 InnoDB，MyISAM 适用以插入为主的程序，比如博客系统、新闻门户。

InnoDB 为何推荐使用自增主键？

自增 ID 可以保证每次插入时 B+ 树索引是从右边扩展的，因此相比自定义 ID （如 UUID）可以避免 B+ 树的频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

说说在 MySQL 中一条查询 SQL 是如何执行的？

select name from t_user where id=1为例：

1. 取得链接，使用使用到 MySQL 中的连接器。
2. 查询缓存，key 为 SQL 语句，value 为查询结果，如果查到就直接返回。不建议使用次缓存，在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。
3. 分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。
4. 优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。
5. 执行器，通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取到这个表的最后一行，最后返回

简述CHAR，VARCHAR 和 Text 的区别？

长度区别
Char 范围是 0～255。
Varchar 最长是 64k（注意这里的 64k 是整个 row 的长度，要考虑到其它的 column，还有如果存在 not null 的时候也会占用一位，对不同的字符集，有效长度还不一样，比如 utf-8 的，最多 21845，还要除去别的column），但 Varchar 在一般情况下存储都够用了。
如果遇到了大文本，考虑使用 Text，最大能到 4G（其中 TEXT 长度 65,535 bytes，约 64kb；MEDIUMTEXT 长度 16,777,215 bytes，约 16 Mb；而 LONGTEXT 长度 4,294,967,295 bytes，约 4Gb）。
效率区别
效率来说基本是 Char > Varchar > Text，但是如果使用的是 Innodb 引擎的话，推荐使用 Varchar 代替 Char。
默认值区别
Char 和 Varchar 支持设置默认值，而 Text 不能指定默认值。

如何优化一个慢查询？

• 首先使用EXPLAIN分析查询计划，检查是否使用了正确的索引；
• 优化查询语句，减少不必要的数据返回（避免SELECT *），减少JOIN操作；
• 检查数据库表的索引设计、数据量大小、锁定情况等；
• 调整MySQL配置，如增加缓冲池大小等。

什么是索引覆盖？如何实现？

• 索引覆盖是指查询所需的所有数据都从索引中直接获得，无需回到主键索引查找，从而减少磁盘I/O。
• 实现方式是确保索引包含了查询中所有字段，即建立包含查询列的复合索引。

如何设计数据库表的分库分表？

• 可以根据业务需求（如读写比例、数据量大小）选择垂直拆分（按功能模块拆分表到不同数据库）或水平拆分（根据某个字段如用户ID范围拆分表）。
• 还需考虑使用代理层如MyCAT进行路由管理，以及数据一致性和复杂查询的处理策略。

如何保证MySQL数据库的安全？

• 使用强密码策略，限制远程访问；
• 开启SSL连接；
• 定期更新MySQL以修复安全漏洞；使用最小权限原则为用户分配权限；
• 定期备份数据；
• 考虑使用防火墙和安全组等。

MySQL的备份与恢复有哪些方法？

• 物理备份工具如mysqldump（适合中小型数据库），可以全备+增量/差异备份；
• 逻辑备份工具如Percona XtraBackup（支持在线热备份，适合大型数据库）；
• 使用MySQL Enterprise Backup等。恢复时根据备份类型选择适当的命令，如使用mysql命令导入.sql文件或利用XtraBackup的恢复功能。

70.数据库锁的作用以及有哪些锁？

• 当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。即锁的作用是解决并发问题。

从锁的粒度划分，可以将锁分为表锁、行锁以及页锁。

• 行级锁：是锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。
  行级锁开销大，加锁慢，且会出现死锁。但锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 表级锁：是粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。
• 页级锁：是粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折中的页级，一次锁定相邻的一组记录。

开销和加锁时间界于表锁和行锁之间，会出现死锁。锁定粒度界于表锁和行锁之间，并发度一般。

从使用性质划分，可以分为共享锁、排它锁以及更新锁。

• 共享锁（Share Lock）：S 锁，又称读锁，用于所有的只读数据操作。
  S 锁并非独占，允许多个并发事务对同一资源加锁，但加 S 锁的同时不允许加 X 锁，即资源不能被修改。S 锁通常读取结束后立即释放，无需等待事务结束。
• 排他锁（Exclusive Lock）：X 锁，又称写锁，表示对数据进行写操作。
  • X 锁仅允许一个事务对同一资源加锁，且直到事务结束才释放，其他任何事务必须等到 X 锁被释放才能对该页进行访问。
    使用 select * from table_name for update; 语句产生 X 锁。
• 更新锁：U 锁，用来预定要对资源施加 X 锁，允许其他事务读，但不允许再施加 U 锁或 X 锁。

从主观上划分，又可以分为乐观锁和悲观锁。

• 乐观锁（Optimistic Lock）：顾名思义，从主观上认定资源是不会被修改的，所以不加锁读取数据，仅当更新时用版本号机制等确认资源是否被修改。
  • 乐观锁适用于多读的应用类型，可以系统提高吞吐量。
• 悲观锁（Pessimistic Lock）：正如其名，具有强烈的独占和排它特性，每次读取数据时都会认为会被其它事务修改，所以每次操作都需要加上锁。

数据库三大范式分别是如何定义的？

• 第一范式：数据库表中的每一列都不可再分，也就是原子性
• 第二范式：在满足第一范式基础上要求每个字段都和主键完整相关，而不是仅和主键部分相关（主要针对联合主键而言)
• 第三范式：在满足第二范式基础上，要求表中的非主键字段和主键字段直接相关，不允许间接相关

我有一张表，既想让他建立索引，又不想修改表时受到索引影响而降低效率，怎么处理？

• 使用数据库中的视图（View）来实现这个需求。视图是一种虚拟表，它本身并不存储数据，而是通过查询语句从一个或多个基本表中获取数据生成的。
• 使用 CREATE VIEW 语句创建一个视图来查询原始表中的数据，并创建索引以提高检索效率，同时不会影响基本表的结构和性能

组合索引的使用场景

• 多个列共同作为查询条件：如果多个列同时参与查询条件，并且经常一起使用，可以选择将它们组合成一个索引，以提高查询效率。

• 范围查询：如果查询条件中包含范围查询，比如 “<”、“>”、“<=”、“>=”、“between” 等，就可以考虑将范围查询的列和其他查询条件的列一起组成一个索引，以避免全表扫描。

• 排序：如果查询结果需要排序，那么可以将排序的列和查询条件的列一起组成一个索引，以避免使用排序算法，提高查询效率。

简述SQL优化经验？

• 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，
• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
• 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致
• 很多时候用 exists 代替 in 是一个好的选择
• 尽量避免大事务操作，提高系统并发能力
• 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要
• where条件中，字段与参数的类型要保持一致，否则会导致索引失效

什么是回表

• 在MySQL数据库中，回表（Lookup）是指在执行查询操作时，由于某些列的值不在查询的索引中，无法直接通过索引获取所需的数据行，因此需要再次访问数据表以获取缺失的列信息。

• 当查询涉及到的列不存在于查询条件的索引中，MySQL无法满足只使用索引进行查询的需求。这时，MySQL会首先使用索引进行快速的范围定位，找到满足查询条件的数据行，然后再通过行ID回到原始表中进行回表操作，检索出其他列的值。

• 回表的过程需要访问磁盘检索数据表，因此会增加额外的I/O操作，对查询性能产生一定的影响。为了减少或避免回表的发生，一种常见的优化方法是使用覆盖索引（Covering Index）或联合索引，将查询所需的列包含在索引中，从而实现全覆盖的索引扫描，无需回表操作，提高查询性能。

• 在设计数据库表结构和索引时，需要根据实际的查询需求和业务特点，合理选择列的索引和索引的顺序，以减少回表操作，提升查询效率。在MySQL数据库中，回表（Lookup）是指在执行查询操作时，由于某些列的值不在查询的索引中，无法直接通过索引获取所需的数据行，因此需要再次访问数据表以获取缺失的列信息。

• 当查询涉及到的列不存在于查询条件的索引中，MySQL无法满足只使用索引进行查询的需求。这时，MySQL会首先使用索引进行快速的范围定位，找到满足查询条件的数据行，然后再通过行ID回到原始表中进行回表操作，检索出其他列的值。

• 回表的过程需要访问磁盘检索数据表，因此会增加额外的I/O操作，对查询性能产生一定的影响。为了减少或避免回表的发生，一种常见的优化方法是使用覆盖索引（Covering Index）或联合索引，将查询所需的列包含在索引中，从而实现全覆盖的索引扫描，无需回表操作，提高查询性能。

• 在设计数据库表结构和索引时，需要根据实际的查询需求和业务特点，合理选择列的索引和索引的顺序，以减少回表操作，提升查询效率。

回表产生的原因有哪些

回表是指在使用索引查询时，需要通过索引获取到行的主键值，然后再根据主键值返回到原始表中取得其他需要的列数据的操作。回表通常是由以下原因导致的：

1. 查询的列不在索引中：如果查询的列不在索引中，那么就需要通过索引找到对应的主键值，然后再通过主键回到原始表中获取其他列的数据。

2. 索引不覆盖查询的列：如果查询语句中的列在索引中存在，但是索引不包含所有查询语句需要的列，那么同样需要回表操作。

3. 使用了函数或表达式：如果查询语句中使用了函数或表达式，会导致索引失效，需要回到原始表中进行计算和比较。

4. 使用了某些特殊的操作：例如使用了GROUP BY、DISTINCT、ORDER BY等操作时，可能会导致索引失效而需要回表操作。

5. 存在虚拟列：虚拟列是通过表达式计算得到的列，如果查询语句中使用了虚拟列，并且该虚拟列没有被包含在索引中，那么同样会导致回表操作。

回表操作可能会增加额外的IO开销，降低查询性能。因此，通过合理的索引设计和优化查询语句，可以尽量减少或避免回表的产生，提高查询的效率。

如何避免回表查询

为了避免回表查询，可以采取以下几种方法：

1. 覆盖索引（Covering Index）：创建一个索引，包含所有查询语句中涉及的列，这样在查询时就可以直接通过索引获取所需的数据，无需再回到原始表中进行回表操作。

2. 聚簇索引（Clustered Index）：在InnoDB存储引擎中，表的数据行按照主键的顺序存储，当使用主键进行查询时，可以避免回表操作。

3. 合理选择索引列：根据实际查询的需求和业务特点，选择适合的索引列，尽可能涵盖查询语句中涉及的列，减少回表的需求。

4. 联合索引（Composite Index）：对多个列创建联合索引，使得查询可以直接利用联合索引进行范围定位和满足查询条件，避免回表操作。

5. 调整查询语句：优化查询语句的条件和列的选择，尽量让所有需要的列都能通过索引直接得到，避免不必要的回表操作。

6. 使用覆盖索引的子查询或连接查询：在某些情况下，可以使用覆盖索引的子查询或连接查询，将需要的列包含在索引中，避免回表查询。

7. 使用内存表（Memory Table）：对于一些小规模的表或者频繁查询的表，可以将其创建为内存表，将整个表加载到内存中，避免磁盘I/O，从而避免回表查询。

通过综合运用以上方法，可以有效减少回表查询的次数，提高查询性能。但需要根据具体情况进行权衡和选择，以满足业务需求和性能优化的要求。

SQL调优方面

在SQL调优过程中，有一些经验值得注意和尝试：

1. 确保正确的索引：使用适当的索引可以提高查询性能。在选择索引时，考虑查询条件和排序方式，并确保在列上创建索引，而不是函数或表达式上。
2. 优化查询语句：使用正确的查询语句可以提高性能。尽量避免使用通配符， 使用LIMIT限制结果集大小，避免不必要的子查询等等。
3. 注意JOIN操作：JOIN是数据库中常用的操作，但也可能影响性能。确保在JOIN操作中使用正确的连接条件，并避免使用不必要的JOIN操作。
4. 避免过度规范化：过度规范化会导致查询需要多个表进行JOIN操作，从而降低性能。在设计数据库时，要注意避免过度规范化，合理划分表和字段。
5. 使用合理的数据类型：使用合适的数据类型可以提高性能。例如，使用整数类型而不是字符类型来存储数字数据，使用DATE类型来存储日期等等。
6. 批量操作：尽量避免使用循环单一操作数据库，而是使用批量操作，减少与数据库的交互次数。
7. 适当分区：对于大型数据库，可以考虑将数据分区存储，以提高查询性能。分区可以根据日期、范围等条件进行划分。
8. 监控和优化查询计划：使用数据库管理工具来监控查询计划，并通过调整索引、统计信息等来优化查询。
9. 缓存查询结果：对于一些查询结果不经常变化的数据，可以将结果缓存起来，避免每次查询都去数据库查询。
10. 定期清理和优化数据库：定期清理无用数据，优化表结构，重新生成索引等，可以提高数据库的性能。

以上经验可以作为SQL调优的参考，但具体的优化策略还应根据具体的业务场景和数据库性能瓶颈来确定。