MySQL数据库面试题汇总

MySQL数据库

1.什么是数据库？

数据库（Database）是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。（简而言之，用来存储大量数据的）

2.为什么使用数据库？

使用数据库可以方便地存储和管理大量数据，并且可以通过查询等方式快速地获取和操作数据，提高数据的可靠性和安全性。

3.什么是MySQL？

MySQL是一种关系型数据库管理系统，它使用SQL语言进行查询和管理数据。它是一种开源软件，可以在多个操作系统上运行。

4.数据库的三大范式是什么？

• 第一范式（1NF）：确保每个列都是原子性的，即每个列都不能再分解为更小的数据项。这个范式基本上是关于如何定义关系模式的，它要求关系模式中的所有属性都是原子性的，不可再分解。
• 第二范式（2NF）：确保表中的每个非主属性都完全依赖于主键，而不是依赖于主键的一部分。也就是说，非主属性不能只依赖于主键的部分属性。
• 第三范式（3NF）：确保表中的每个非主属性都不传递依赖于主键。也就是说，如果一个非主属性依赖于另一个非主属性，那么这个非主属性应该被拆分到另一个表中，以避免数据冗余。

这些范式旨在规范数据库设计，以确保数据的一致性、完整性和有效性。

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

• mysql.user: 存储MySQL用户账号信息，比如用户名、密码和主机名。
• mysql.db: 存储数据库级别的权限信息，比如哪些用户有哪些数据库的权限。
• mysql.tables_priv: 存储表级别的权限信息，比如哪些用户可以访问哪些表的哪些操作权限。
• mysql.columns_priv: 存储列级别的权限信息，比如哪些用户可以访问哪些列的哪些操作权限。

6.MySQL的binlog有几种录入格式？分别是什么？

binlog 有三种格式

• Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
• Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
• Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。

7.MySQL有哪些数据类型？

• 数值类型
• 字符串类型
• 时间日期类型

8.什么是索引？

在数据中，索引相当于字典的目录一下，通过索引可以快速的检索数据。

9.索引有哪些优缺点？

优点：

• 提高数据的搜索速度
• 加快表与表之间的连接速度
• 在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。

缺点:

• 只能提高查询速度，相反会降低新增，修改，删除的效率
• 浪费存储空间 索引不是越多越好，根据实际情况添加不同的索引

10.索引有哪几种类型？

• 唯一索引：在创建唯一索引时要不能给具有相同的索引值。
• 主键索引：在我们给一个字段设置主键的时候，它就会自动创建主键索引，用来确保每一个值都是唯一的。
• 聚集索引：我们在表中添加数据的顺序，与我们创建的索引键值相同，而且一个表中只能有一个聚集索引。
• 普通索引：它的结构主要以B+树和哈希索引为主，主要是对数据表中的数据进行精确查找。
• 全文索引：它的作用是搜索数据表中的字段是不是包含我们搜索的关键字，就像搜索引擎中的模糊查询。

11.MylSAM索引与InnoDB索引的区别？

• InnoDB支持事务，MylSAM不支持事务。这是MySQL 将默认存储引擎从MylSAM变成InnoDB的重要原因之一;
• InnoDB支持外键，而MylSAM不支持。对一个包含外键的lnnoDB表转为MYISAM会失败;
• InnoDB是聚集索引，MylSAM是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
• InnoDB 不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MylSAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快;
• InnoDB最小的锁粒度是行锁，MyISAM最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是MySQL将默认存储引擎从 MylSAM变成InnoDB的重要原因之一;

12.索引的基本原理？

就是把无序的数据变成有序的查询.

• 把创建了索引的列的内容进行排序
• 对排序结果生成倒排表
• 在倒排表内容上拼上数据地址链
• 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

13.索引设计原则

查询更快、占用空间更小

• 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
• 基数较小的表(就是表的数据比较少，因为会单独维护一个索引表)，索引效果较差，没有必要在此列建立索引
• 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
• 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
• 定义有外键的数据列一定要建立索引。
• 更新频繁字段不适合创建索引
• 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
• 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
• 对于定义为text、image和bit的数据类型的列不要建立索引。

14.创建索引的原则？

• 对于经常需要搜索、排序或分组的列，应该创建索引。这样可以加快查询速度。

• 对于表中唯一值比较多的列，也应该创建索引。这些列通常是主键、外键和唯一约束列。

• 避免在大型表上创建过多的索引。因为索引会占用磁盘空间，并且会影响插入、更新和删除操作的性能。

• 对于经常进行范围查询的列，如日期或价格，应该使用复合索引。复合索引可以包含多个列，可以提高查询性能。

• 在创建索引时，应该选择合适的数据类型和长度。选择较小的数据类型和长度可以减少索引占用的磁盘空间，并提高查询性能。

• 定期对索引进行优化和维护。可以使用数据库管理工具或脚本来分析索引使用情况，并进行必要的优化和维护操作。

15.使用索引查询一定能提高查询的性能吗？为什么

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4 5次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询（INDEX RANGE SCAN）适用于两种情况：

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%。
• 基于非唯一索引的检索。

16.百万级别或以上的数据如何删除？

• 分批次删除：将要删除的数据分成多个批次，每次删除一部分数据。这样可以避免一次性删除过多数据导致数据库性能下降或者系统崩溃。

• 使用TRUNCATE命令：TRUNCATE命令可以快速删除表中的所有数据，比使用DELETE命令要快得多。但是，TRUNCATE命令不能回滚数据，所以在使用之前需要谨慎考虑。

• 使用DROP命令：DROP命令可以删除整个表，包括表结构和所有数据。但是，这个方法需要谨慎使用，因为它会永久删除数据，而且无法恢复。

• 使用存储过程：可以编写存储过程来删除数据。存储过程可以对数据进行预处理和批量删除，可以提高删除效率。

• 使用分区表：如果表中的数据可以按照某个条件进行分区，可以使用分区表来删除数据。这样可以只删除指定分区中的数据，而不会影响其他分区的数据。

无论采用哪种方法，都需要在删除之前备份数据，并在删除之后进行必要的优化和维护操作。

17.什么是最左前缀原则？什么是最左匹配原则？

• 顾名思义，最左优先，以最左边为起点任何连续的索引都能匹配上。同时遇到范围查询（>、<、between、like）就会停止匹配。

• 如建立 (a,b,c,d) 索引，查询条件 b = 2 是匹配不到索引的，但是如果查询条件是 a = 1 and b = 2 或 a=1 又或 b = 2 and a = 1 就可以，因为优化器会自动调整 a,b 的顺序。

• 再比如 a = 1 and b = 2 and c > 3 and d = 4，其中 d 是用不到索引的，因为 c 是一个范围查询，它之后的字段会停止匹配。

18.什么是数据库事务？

数据库事务是指一组数据库操作，这组操作要么全部执行成功，要么全部执行失败。在数据库中，事务通常用于保证数据的一致性和完整性。

事务通常具有以下四个特性，也称为ACID特性：

• 原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部执行失败。如果其中任何一个操作失败，整个事务都会被回滚到之前的状态。

• 一致性（Consistency）：事务执行前后，数据库的状态应该保持一致。也就是说，事务执行后，数据库应该满足所有的约束和规则。

• 隔离性（Isolation）：事务应该在独立的隔离环境中运行，不受其他事务的干扰。这可以避免数据冲突和并发问题。

• 持久性（Durability）：事务执行成功后，对数据库的修改应该被永久保存。即使系统崩溃或者重启，数据也应该能够恢复。

通过使用事务，可以确保数据库操作的正确性和可靠性。如果一个操作失败，整个事务都会被回滚到之前的状态，这可以避免数据损坏和数据不一致的问题。

19.什么是关系型数据库，主键，外键索引分别是什么？

• 关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。
• 主关键字(primary key)是表中的一个或多个字段，它的值用于唯一的标识表中的某一条记录
• 外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字
• 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

20.常见的关系型数据库有哪些？

• MySQL-MySQL AB公司开发(开源)
• Oracle-公司开发
• DB2-IBM公司开发DB2
• Microsoft Access-Microsoft公司开发
• Microsoft SQL Server-Microsoft公司开发
• Sybase-Sybase公司开发

21.SQL的select语句完整的执行顺序是什么？

• FROM子句：从指定的表中获取数据。

• WHERE子句：筛选满足条件的数据行。

• GROUP BY子句：将数据按照指定的列进行分组。

• HAVING子句：筛选满足条件的分组。

• SELECT子句：选择需要查询的列。

• DISTINCT关键字：去除重复的行。

• ORDER BY子句：对结果集进行排序。

• LIMIT子句：限制结果集的数量。

需要注意的是，这些步骤并不是严格按照顺序执行的，而是根据具体情况进行优化和调整的。例如，如果查询中使用了聚合函数，那么GROUP BY子句会在WHERE子句之后执行。同时，数据库系统也会根据索引和其他优化策略来调整执行顺序，以提高查询性能。

因此，虽然SELECT语句的执行顺序是按照上述步骤进行的，但是实际执行过程中可能会有所不同。

22.事务的特性？

事务通常具有以下四个特性，也称为ACID特性：

• 原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部执行失败。如果其中任何一个操作失败，整个事务都会被回滚到之前的状态。

• 一致性（Consistency）：事务执行前后，数据库的状态应该保持一致。也就是说，事务执行后，数据库应该满足所有的约束和规则。

• 隔离性（Isolation）：事务应该在独立的隔离环境中运行，不受其他事务的干扰。这可以避免数据冲突和并发问题。

• 持久性（Durability）：事务执行成功后，对数据库的修改应该被永久保存。即使系统崩溃或者重启，数据也应该能够恢复。

23.数据库怎么优化查询效率？

• 储存引擎选择，如果数据表需要事务处理，应该考虑使用InnoDB, 因为它完成兼容 ACID 特性，如果不需要事务处理，使用默认储存引擎 MyISQM 比较明智
• 对查询进行优化，尽量避免全表扫描，可以考虑再 where 及 order by 涉及的列上建立索引
• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导则引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中使用 ！= 或 < > 操作符，否则将引擎放弃使用索引而进行全表扫描
• 应尽量避免在 where 子句中使用 or 来连接条件， 如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
• 使用 Update 语句， 如果只更改 1、2 个字段，不要 update 全部字段，否则频繁调用会引起明显的性能小号，同时带来大量日志
• 对于多张大数据量的表 JINO，要先分页再 JOIN, 否则逻辑读会很高，性能差
• 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）
• 避免频繁创建和删除临时表，以减少系统表资源的消耗。

24.什么是脏读、幻读和不可重复度？

• 脏读：一个事务读取到另一个事务尚未提交的数据。 事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。

• 不可重复读：一个事务中两次读取的数据的内容不一致。 事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

• 幻读：一个事务中两次读取的数据量不一致。 系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

25.SQL注入是如何产生的，如何防止？

程序开发过程中不注意书写规范写SQL语句和关键字未进行过滤，导致客户端可以通过全局变量get或者post提交sql语句到服务器端正常运行;

防止方法：

• 过滤掉一些常见的数据库操作关键字，或者通过系统函数来进行过滤。
• 在PHP配置文件中将register_globals=off；设置为关闭状态
• SQL语句书写的时候尽量不要省略小引号（tab键上面那个）和单引号
• 提高数据库命名技巧，对于一些重要的字段根据程序的特点命名，去不易被猜到的
• 对于常用的方法加以封装，避免直接暴漏SQL语句
• 开启PHP安全模式：Safe_mode = on;
• 打开magic_quotes_gpc 来防止SQL注入、
• 控制错误信息：关闭错误提示信息，将错误信息写到系统日志。
• 使用mysqli或pdo预处理。

26.MySQL与MongoDB本质之间最基本的差别是什么？

​​

• 数据库模型：mongodb-非关系型;mysql-关系型;
• 存储方式：mongodb-以类JSON的文档的格式存储;mysql-不同引擎有不同的存储方式;
• 查询语句：mongodb-MongoDB查询方式(类似JavaScript的函数);mysql-SQL语句;
• 数据处理方式：mongodb-基于内存，将热数据存放在物理内存中，从而达到高速读写;mysql-不同引擎有自己的特点;
• 成熟度：mongodb-新兴数据库，成熟度较低;mysql-成熟度高;
• 广泛度：mongodb-NoSQL数据库中，比较完善且开源，使用人数在不断增长;mysql-开源数据库，市场份额不断增长;
• 事务性：mongodb-仅支持单文档事务操作，弱一致性;mysql-支持事务操作;
• 占用空间：mongodb-占用空间大;mysql-占用空间小;
• join操作：mongodb-MongoDB没有join;mysql-MySQL支持join;

27.为什么要使用视图？什么是视图？

视图是一种虚拟表，它是由 SELECT 语句定义的。视图不像表那样实际存在于数据库中，而是在需要时动态生成的结果集。视图可以看作是一张虚拟表，它可以从一个或多个基本表中选择、过滤和排序数据，生成一个新的逻辑表。

使用视图的主要原因如下：

• 简化复杂查询：视图可以将多个表的数据组合成一个逻辑表，从而简化复杂查询。这样就不需要编写复杂的 SQL 查询语句，而是可以使用简单的 SELECT 语句查询视图。

• 限制数据访问：通过视图，可以限制用户对表中某些列的访问权限，从而保护敏感数据不被随意访问和修改。这种方式可以保证数据的安全性。

• 简化数据操作：通过视图，可以将多个表的数据组合起来，形成一个逻辑上的表，使得查询和操作数据更加方便。这样就可以避免在查询和操作数据时需要编写复杂的 SQL 查询语句。

• 提高数据一致性：通过视图，可以对数据进行约束和验证，保证数据的一致性和完整性。这种方式可以避免用户在操作数据时出现错误。

28.视图有哪些特点？

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
• 视图是由基本表(实表)产生的表(虚表)。
• 视图的建立和删除不影响基本表。
• 对视图内容的更新(添加，删除和修改)直接影响基本表。
• 当视图来自多个基本表时，不允许添加和删除数据。

29.视图的优缺点？

优点：

• 使用视图，可以定制用户数据，聚焦特定的数据。
• 使用视图，可以简化数据操作。
• 使用视图，基表中的数据就有了一定的安全性
• 可以合并分离的数据，创建分区视图

缺点：

• 性能差
• 修改限制

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

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

优点：

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

缺点：

• 调试麻烦（没有像开发程序那样容易）
• 可移植性不灵活（因为存储过程依赖于具体的数据库）

31.SQL语句主要分为哪几类？

• DQL：数据查询语言，用于对数据进行查询，如select
• DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
• TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
• DCL：数据控制语言，进行授权与权限回收，如grant、revoke
• DDL：数据定义语言，进行数据库、表的管理等，如create、drop
• CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

32.什么是死锁？怎么解决？

死锁是指两个或多个事务在执行过程中，因为互相持有对方需要的资源而相互等待，从而导致无法继续执行的情况。这种情况下，每个事务都在等待对方释放资源，而自己又无法释放自己持有的资源，从而导致整个系统陷入僵局。

解决死锁的方法主要有以下几种：

• 超时机制：在事务等待资源的过程中，设置一个超时时间。如果在规定时间内没有得到需要的资源，就放弃等待并回滚事务。

• 死锁检测和解除：系统可以定期检测是否存在死锁，并尝试解除死锁。一旦发现死锁，系统就会选择一个事务进行回滚，从而解除死锁。

• 加锁顺序：在进行资源加锁时，可以规定所有事务必须按照同一顺序进行加锁。这样可以避免不同事务之间的加锁顺序不一致，从而导致死锁的产生。

• 减少事务持有资源的时间：尽可能地减少事务持有资源的时间，可以减少死锁的发生概率。

• 使用读写锁：对于读多写少的应用场景，可以使用读写锁来提高并发性能。读写锁允许多个事务同时读取同一个资源，但只允许一个事务进行写操作。

33.左连接与右链接有什么区别？

• 左连接（LEFT JOIN）：左连接会读取左边数据表的全部数据，即使右边数据表没有对应数据；

左连接会读取左边数据表的全部数据，即使右边数据表没有对应数据。如果两个表中数据有相同部分，只显示一个数据

• 连接（RIGHT JOIN）：右连接会读取右边数据表的全部数据，即使左边数据表没有对应数据。

右连接会读取右边数据表的全部数据，即使左边数据表没有对应数据。如果两个表中数据有相同部分，只显示一个

34.where与having的区别？

• 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
• WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
• WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
• WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
• WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。

35.分组查看

select * from 表名 group by 列明 having 条件

36.查询数据库的前100行数据，你sq怎么写？

select top 100 * from 表名 order by 列名 desc (升序asc 降序desc)

37.如何进行多表查询？

多表联查：select * from 表1 join 表2 on 表1.列=表2.列（列里的字段一样）
select * from 表1 a join 表2 b on a.id=b.id（id相同）
内连接：select * from 表1 a inner join 表2 b on a.id=b.id
左连接：select * from 表1 a left join 表2 b on a.id=b.id
右链接：select * from 表1 a right join 表2 b on a.id=b.id

38.操作数据库命令