MySQL数据库面试大全

缺乏索引或索引未被充分利用：没有正确地创建索引或者查询语句中没有使用到索引，导致数据库需要进行全表扫描来查找所需数据，从而降低查询速度。
I/O吞吐量小：磁盘读写速度慢、网络带宽不足等问题都会导致I/O吞吐量小，形成了瓶颈效应，进而影响查询速度。
缺少计算列导致查询不优化：计算列是一种预先计算并存储在数据库中的列，可以提高查询性能。如果没有适当地创建计算列，查询语句可能需要对大量数据进行计算，导致查询速度变慢。
内存不足：当数据库所需的内存超出了可用内存的限制时，数据库可能会将一部分数据存储在磁盘上，从而导致查询速度下降。

解决MySQL查询速度慢的方法包括：

创建合适的索引：分析查询语句和数据表结构，确定合适的索引策略，创建适当的索引，以加快查询速度。
优化查询语句：通过优化查询语句的编写方式，避免不必要的计算和重复操作，从而提高查询效率。
增加硬件资源：增加服务器的内存、磁盘和网络带宽等硬件资源，以提高整体系统的性能。
使用缓存机制：利用缓存机制，将频繁查询的结果缓存起来，减少对数据库的访问次数，提高查询速度。
数据库分区：将大表分成多个分区，可以提高查询效率，减少查询范围。

总结来说，解决MySQL查询速度慢的问题需要综合考虑索引优化、查询语句优化、硬件资源增加、缓存机制和数据库分区等方面的方法。根据具体情况选择合适的解决方案，可以提高MySQL的查询性能。

三、数据库的事务是什么?怎么使用

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

四、主键、外键、索引的各自的含义以及区别

1、主键

在MySQL中，主键（Primary Key）是指一张表中用来唯一标识每条记录的字段或者字段集合。一个表只能有一个主键，且主键必须满足以下三个条件：

唯一性：每个主键的取值必须唯一，即不允许出现两条记录的主键取值相同的情况。

非空性：主键列不能包含NULL值，否则将无法区分记录。

稳定性：主键列的值在任何时候都不能被修改，这是为了保证表中记录始终能够被唯一标识。

在MySQL中，通常可以通过在定义表的时候设置主键实现。可以把一个列定义为主键并用“PRIMARY KEY”关键字声明它，也可以在多个列上定义复合主键。。如果一个表没有主键，那么在某些查询中可能会影响性能，并且可能会导致数据不一致。因此，在设计数据库时建议为每个表定义一个主键

2、外键

在MySQL数据库中，外键（Foreign Key）是指一个表中的字段，在另一个表中作为主键使用。换言之，外键是通过一个或多个列来定义两个表之间关系的约束。它描述了两个表之间的关系，确保引用表中的数据符合相应的完整性要求。
在MySQL中，定义外键约束可以使用“FOREIGN KEY”语句，必须满足以下两个条件：
1. 外键列值必须与其引用表中的主键或唯一键值匹配，保证数据的正确性和可靠性。
2. 在更新和删除操作时，必须遵循特定的行为，比如：当引用表的某一行被删除时，相关的行应该被删除或将其外键列置为NULL值等。
有了外键约束后，可以保证两个表之间的数据始终保持同步，同时也防止了数据的矛盾和冲突，增加了数据的安全性和可维护性。但是，由于外键的检索需要占用更多的系统资源，所以在设计数据库结构时不应该过度使用外键

3、索引

MySQL索引是一种数据结构，用于加速MySQL数据库中的查询操作。它允许快速查找特定值或一组值对应的行。索引可以在一个表上定义一个或多个列（字段），每个索引都有一个或多个列（字段）的值按照特定的方式排序，并保存在数据结构中以提升查询效率。

MySQL索引通常具有以下几个特点：

索引可以加速查询：通过使用索引，我们可以在数据集合中快速定位到满足特定条件的记录，从而加速查询的速度。
索引会占用空间：索引需要占用磁盘空间来存储索引信息，因此在设计索引时需要权衡空间和性能之间的平衡。
索引可以降低写入性能：由于索引需要在更新、插入和删除操作时维护，所以过多的索引可能会拖慢写入操作的性能。
索引可以提高表的安全性：通过创建唯一索引或主键索引等约束，可以增强表的数据完整性和安全性。

在MySQL中，可根据不同的需求创建不同类型的索引。最常见的索引包括主键索引、唯一索引、普通索引、全文索引等。利用索引可以提高查询的速度，但是需要合理地设计与使用索引，否则可能会影响查询性能并占用过多的存储空间。

4、三者的区别

主键、外键和索引是MySQL数据库中常见的概念，它们的区别如下：

主键（Primary Key）：主键用于唯一标识一张表中每条记录，通常由一个或多个列组成。主键要求每条记录都必须具有唯一标识，且不允许为空。主键可以作为其他表中的外键参考。通过主键可以方便地定位、更新以及删除表中的某条记录。
外键（Foreign Key）：外键是指一个表中的字段，在另一个表中作为主键使用。外键约束保证了数据在表之间的完整性，并使得表之间具有联系。外键定义了两个表之间的关系，要求在外键所指向的表中存在对应的行。在 MySQL 中，外键是用于约束数据完整性的重要工具。
索引（Index）：索引用于加速 SQL 查询语句的执行速度。索引在数据库的某个列上进行构建，它可以帮助 MySQL 更快地定位和提取查询语句所需要的数据。索引可分为普通索引和唯一索引两种，除此之外还有全文搜索索引等其他类型的索引。
总体来说，主键是用于表示一张表中每条记录的唯一标识，外键是用于表示关联两张表之间的字段，而索引用于提高 SQL 查询语句的执行速度。三者之间具有不同的作用和用途，但都可以提高数据库操作的效率与安全性。

五、ACID是指事务的四个关键特性，其英文全称为Atomicity、Consistency、Isolation和Durability，分别对应事务的原子性、一致性、隔离性和持久性。

原子性（Atomicity）：事务是一个不可分割的操作单位，要么全部执行成功，要么全部执行失败。即使在系统发生故障时，也必须保证事务的原子性。
一致性（Consistency）：事务执行前后，数据的完整性约束没有被破坏，如数据的唯一性、实体完整性、参照完整性等。
隔离性（Isolation）：多个事务并发执行时，每个事务都应该彼此独立，互不干扰。每个事务看到的数据视图应该与其他事务的数据视图相互隔离，这样可以避免数据不一致的问题。
持久性（Durability）：事务一旦提交，对数据的修改就是永久性的，即使系统崩溃，修改的数据也能够恢复。
这四大特性是保证数据库管理系统（DBMS）中事务处理正确和可靠的基础。如果一个事务满足ACID特性，那么就可以保证数据的一致性和可靠性，从而使数据库系统更加稳定和安全。

六、索引的数据结构是什么

索引可能有三种数据结构哈希表、有序数组和N叉树。MySQL使用了B+树。

1.哈希表(散列表)

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。

因为是使用hash算法对key求值取余得到其在数组的存储位置。

所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。在范围查询中需要全表扫描，是很慢的。

2.有序数组

有序数组在范围查询中优势非常明显，可以采用二分法，能大大缩短查询时间，尤其是数据量比较大时。时间复杂度是时间复杂度是 O(log(N))。如果往中间插入一条数据，就需要把后续数组都往后移,这个时候有序数组的成本就很高了.

所以，有序数组索引只适用于静态存储引擎，

3.二叉树(N叉树)

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

主键索引的叶子节点存的是整行数据。如果查询条件是主键就能获取整行数据。非主键索引的value是主键Id，如果查询条件是非主键，那么先查非主键索引得到主键id，根据主键Id再查主键索引得到整行数据。

从性能和存储空间方面考量，自增主键往往是比业务字段更合理的选择。可以参考：优化 | InnoDB表一定要用自增列做主键

七、什么是前缀索引

所谓前缀索引，说白了就是对文本的前几个字符建立索引（具体是几个字符在建立索引时去指定），比如以产品名称的前 10 位来建索引，这样建立起来的索引更小，查询效率更快！

有点类似于 Oracle 中对字段使用 Left 函数来建立函数索引，只不过 MySQL 的这个前缀索引在查询时是内部自动完成匹配的，并不需要使用 Left 函数。

八、怎么创建前缀索引？

建立前缀索引的方式，方法很简单，通过如下方式即可创建！

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

其中prefix_length这个参数，就是前缀长度的意思，通常通过如下方式进行确认，步骤如下：

第一步，先计算某字段全列的区分度。

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

第二步，然后再计算前缀长度为多少时和全列的区分度最相似

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

最后，不断地调整prefix_length的值，直到和全列计算出区分度相近，最相近的那个值，就是我们想要的值。

下面以某个测试表为例，数据体量在 100 万以上，表结构如下！

CREATE TABLE `tb_test` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

九、数据库有几种表之间的连接形式(左连接,右连接,内连接,完全连接)

1、内联接

（select a.*,b.* from a inner join b on a.id=b.aId）

内连接又包括等值连接，非等值连接，默认的是等值链接。

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

2、外联接。

左连接（select a.*,b.* from a left join b on a.id=b.aId）

右链接（select a.*,b.* from a right join b on a.id=b.aId）

外联接可以是左向外联接、右向外联接或完整外部联接。在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：

1）LEFT JOIN或LEFT OUTER JOIN

2）RIGHT JOIN 或 RIGHT OUTER JOIN

3）FULL JOIN 或 FULL OUTER JOIN

3、完全连接（ select a.*,b.* from a full join b on a.id=b.aId）

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积.

十、三大范式

第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

判断表是否符合第一范式，列是否可以再分，得看需求，如果将电话号和地址分开才能满足查询等需求时，那之前的表设计就是不满足1NF的，如果电话号和地址拼接作为一个字段也可以满足查询、存储等需求时，那它就满足1NF。

第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)

在满足1NF的前提下，表中不存在部分依赖，非主键列要完全依赖于主键。(主要是说在联合主键的情况下，非主键列不能只依赖于主键的一部分)

第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）
1. 非主键列越少的表。(1NF强调列不可再分；2NF和3NF强调非主属性列和主属性列之间的关系)
2. 如代码表(sexcode)，非主键列只有一个sex_desc；
3. 或者将学生表的主键设计为primary key(id,name,sex_code,phone)，这样非主键列只有address，更容易符合3NF。

十一、Hash 和 B+ 树索引的区别？

Hash

Hash 进行等值查询更快，但无法进行范围查询。因为经过 Hash 函数建立索引之后，索引的顺序与原顺序无法保持一致，故不能支持范围查询。同理，也不支持使用索引进行排序。
Hash 不支持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，如 AA 和 AB 的算出的值没有相关性。
Hash 任何时候都避免不了回表查询数据.

虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生 Hash 碰撞，此时查询效率反而可能降低。

B+ Tree

B+ 树本质是一棵查找树，自然支持范围查询和排序。

在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表。
查询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。
为何使用 B+ 树而非二叉查找树做索引？

我们知道二叉树的查找效率为 O(logn)，当树过高时，查找效率会下降。另外由于我们的索引文件并不小，所以是存储在磁盘上的。

文件系统需要从磁盘读取数据时，一般以页为单位进行读取，假设一个页内的数据过少，那么操作系统就需要读取更多的页，涉及磁盘随机 I/O 访问的次数就更多。将数据从磁盘读入内存涉及随机 I/O 的访问，是数据库里面成本最高的操作之一。

因而这种树高会随数据量增多急剧增加，每次更新数据又需要通过左旋和右旋维护平衡的二叉树，不太适合用于存储在磁盘上的索引文件。

十二、为何使用 B+ 树而非二叉查找树做索引？

我们知道二叉树的查找效率为 O(logn)，当树过高时，查找效率会下降。另外由于我们的索引文件并不小，所以是存储在磁盘上的。
文件系统需要从磁盘读取数据时，一般以页为单位进行读取，假设一个页内的数据过少，那么操作系统就需要读取更多的页，涉及磁盘随机 I/O 访问的次数就更多。将数据从磁盘读入内存涉及随机 I/O 的访问，是数据库里面成本最高的操作之一。
因而这种树高会随数据量增多急剧增加，每次更新数据又需要通过左旋和右旋维护平衡的二叉树，不太适合用于存储在磁盘上的索引文件。