5 万字 124 道MySQL经典面试题总结（2024修订版）_网站优化分享

您的位置：上海毫米网络优化公司 > 网站优化分享 >

1、数据库的三范式是什么？

第一范式：列不可再分

第二范式：在第一范式的基础上，要求数据库表中的所有非主键列完全依赖于主键，而不是仅依赖于主键的一部分

第三范式：满足第二范式的基础上，所有非主键列之间不存在传递依赖

这些范式是构建关系型数据库时的重要指导原则，帮助设计者避免数据异常和维护成本的增加，同时也能提高查询效率。在实际应用中，根据不同的业务需求和性能考虑，有时也需要适当地做出权衡，比如在某些情况下可能会选择适当的冗余来提升查询性能。

2、内连接、左连接、右连接有什么区别？

内连接返回两个表中有联系的所有数据；
左连接会返回左表中的所有行，以及它在右表中找到的匹配行。

右连接会返回右表中的所有行，以及它在左表中找到的匹配行。

3、inner join 、left join、right join，优先使用inner join，为什么？

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。

inner join 内连接，只保留两张表中完全匹配的结果集；
left join会返回左表所有的行，即使在右表中没有匹配的记录；

right join会返回右表所有的行，即使在左表中没有匹配的记录；

为什么？

如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；
使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；

这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

4、表连接不宜太多，一般3个以内？

关联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高
如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

阿里规范中，建议多表联查三张表以下

5、union 和 union all 有什么不同?

（1）处理重复行

Union会对两个结果集进行并集操作，并去掉重复行，也就是说，如果两个结果集中有相同的行，Union只会显示一次。而Union All则不同，它会包括或显示所有结果集中的行。

（2）排序操作

Union不仅会对结果进行去重，还会对获取的结果进行默认的排序操作。而Union All则不会对结果进行排序。

（3）执行效率

由于Union All只是简单地将两个结果集合并，并不会进行去重和排序操作，所以在没有重复数据或不需要排序的情况下，使用Union All的执行效率通常会比Union高。

6、count(1)、count(*) 与 count(列名) 的区别？

count(1)：会统计表中的所有记录数，包括那些字段为null的记录。也就是说，无论列值是否为空，只要该行存在，就会被count(1)计算在内。

count(*)：同样会统计所有记录的条数，它相当于行数，在统计结果时不会忽略任何一个列值为NULL的记录。

count(列名)：只会统计指定列中非null值的个数。如果某行的指定列值为NULL，则不会被count(列名)计入总数。

7、MySQL中有哪些主要的数据类型？请列举并简要描述每种数据类型的基本特征和适用场景。

（1）整数类型：

TINYINT：非常小的整数值，范围从-128到127或0到255，适用于存储有限范围内的整数值。

SMALLINT：较小的整数值，范围从-32768到32767或0到65535，适用于较小数值的存储。

MEDIUMINT：中等大小的整数值，范围从-8388608到8388607或0到16777215，适用于中等大小数值的存储。

INT：标准整数，范围从-2147483648到2147483647或0到4294967295，适用于大多数整数需求。

BIGINT：大整数，范围从-9223372036854775808到9223372036854775807，适用于非常大的整数值。

（2）浮点数类型：

FLOAT：单精度浮点数，适用于需要精确小数点数值的存储，但可能会有精度损失。

DOUBLE：双精度浮点数，提供更高的精度，适用于需要高精度小数点数值的存储。

DECIMAL：固定精度的小数，适用于财务计算等需要精确小数点数值的存储。

（3）日期和时间类型：

DATE：日期，只包含年月日，适用于只需要日期信息的场景。

TIME：时间，只包含时分秒，适用于只需要时间信息的场景。

DATETIME：日期和时间，包含年月日时分秒，适用于需要完整日期和时间信息的场景。

TIMESTAMP：时间戳，包含年月日时分秒，适用于需要自动更新时间戳的场景。

（4）字符串类型：

CHAR：定长字符串，适用于存储固定长度的字符串。

VARCHAR：可变长字符串，适用于存储长度可变的字符串。

TEXT：长文本，适用于存储大量文本数据。

BLOB：二进制大对象，适用于存储二进制数据如图片或文件。

（5）二进制类型：

BINARY：二进制字符串，适用于存储二进制数据。

VARBINARY：可变长二进制字符串，适用于存储长度可变的二进制数据。

TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB：不同大小的二进制大对象，适用于存储不同大小的二进制数据。

每种数据类型都有其特定的应用场景和优势，选择合适的数据类型可以优化数据库的性能和存储效率。例如，如果需要存储用户的电话号码，可以选择VARCHAR类型，因为它可以有效地存储可变长度的字符串。而对于存储价格信息，DECIMAL类型会是一个更好的选择，因为它可以提供精确的小数点数值。在设计数据库时，应根据实际需求和数据特性来选择最合适的数据类型。

8、在MySQL数据库中，如何将整数类型从INT更改为BIGINT以支持更大的数值？

备份数据：在进行任何修改之前，建议先对当前数据库或表进行备份，以防在更改过程中发生数据丢失。
确定更改的字段和表：明确哪些字段需要从INT更改为BIGINT，以及这些字段位于哪个表中。可以使用DESCRIBE table_name;命令来查看表结构。
执行ALTER TABLE语句：使用ALTER TABLE命令结合ALTER COLUMN子句来更改字段的数据类型。例如，如果要将名为students的表中名为age的字段从INT更改为BIGINT，可以执行以下SQL语句：ALTER TABLE students MODIFY COLUMN age BIGINT;。这会将age列的数据类型更改为BIGINT。
检查数据类型转换：在执行了ALTER TABLE语句后，再次使用DESCRIBE命令来确认字段的数据类型已经更改为BIGINT。
测试和验证：确保更改后，应用程序中与该字段相关的所有功能仍然正常工作。

如果需要将其他类型（如VARCHAR）转换为BIGINT，可能需要先将字段的值转换为数字类型，再将其转换为BIGINT。这可以通过CAST操作符或CONV()函数来实现。

通过以上步骤，应该可以安全地将MySQL中的整数类型从INT更改为BIGINT，以便支持更大的数值存储需求。

9、float 和 double 的区别是什么？

在MySQL数据库中，FLOAT和DOUBLE都是用来存储近似数值的浮点数数据类型，但它们在精度、存储空间和计算速度方面存在差异。具体来说：

（1）精度

DOUBLE类型的精度高于FLOAT。DOUBLE类型提供双精度，尾数可以有16位，而FLOAT类型提供的是单精度，尾数精度大约只有7位。这意味着在处理需要更高精度的数据时，应优先考虑使用DOUBLE类型。

（2）存储空间

由于精度的不同，DOUBLE类型占用的存储空间是FLOAT的两倍。在MySQL中，FLOAT使用4个字节，而DOUBLE使用8个字节。因此，如果对内存使用敏感或需要存储大量数据，考虑使用FLOAT可能更为合适。

（3）计算速度

由于DOUBLE的精度更高，其计算过程也相对复杂，导致运算速度比FLOAT慢。如果对计算性能有较高要求，可以考虑在适用的情况下使用FLOAT以加快处理速度。

总的来说，当需要较高的数值精度时，应该使用DOUBLE，而在对精度要求不高且希望节省存储空间或提高计算效率时，则可以选择FLOAT。

10、Blob 和text 有什么区别？

BLOB用于存储二进制数据，如图像、音频、视频等。

TEXT用于存储文本数据，如文档、日志等。

Text类型需要指定字符集，并根据该字符集进行校对和排序。Blob类型没有字符集，因此不进行字符集的校验，它的内容以原始的字节形式存储。

在比较和排序时，Text类型不区分大小写，而Blob类型区分大小写。这是因为Text被视为不区分大小写的字符串，而Blob则按字节的数值直接比较。

11、char 与 varchar2 有什么区别？

（1）char的长度是固定的，而varchar2的长度是可以变化的。

比如，存储字符串“101”，对于char(10)，表示你存储的字符将占10个字节（包括7个空字符），在数据库中它是以空格占位的，而同样的varchar2(10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。

（2）char的效率比varchar2的效率稍高。

（3）何时用char，何时用varchar2?

char和varchar2是一对矛盾的统一体，两者是互补的关系，varchar2比char节省空间，在效率上比char会稍微差一点，既想获取效率，就必须牺牲一点空间，这就是我们在数据库设计上常说的“以空间换效率”。

varchar2虽然比char节省空间，但是假如一个varchar2列经常被修改，而且每次被修改的数据的长度不同，这会引起“行迁移”现象，而这造成多余的I/O，是数据库设计中要尽力避免的，这种情况下用char代替varchar2会更好一些。char中还会自动补齐空格，因为你insert到一个char字段自动补充了空格的,但是select后空格没有删除，因此char类型查询的时候一定要记得使用trim，这是写本文章的原因。

如果开发人员细化使用rpad()技巧将绑定变量转换为某种能与char字段相比较的类型（当然，与截断trim数据库列相比，填充绑定变量的做法更好一些，因为对列应用函数trim很容易导致无法使用该列上现有的索引），可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化，应用就会受到影响，因为它必须修改字段宽度。

正是因为以上原因，定宽的存储空间可能导致表和相关索引比平常大出许多，还伴随着绑定变量问题，所以无论什么场合都要避免使用char类型。

12、MySQL中用什么类型存储货币？

存储货币的最佳数据类型是DECIMAL。DECIMAL类型用于存储精确的定点数值，可以指定总共的位数和小数点后的位数，这使得它非常适合用于存储货币金额，因为货币金额通常需要精确到小数点后几位。

例如，你可以使用DECIMAL(10, 2)来存储货币值，其中10表示总共的位数，2表示小数点后的位数。这意味着你可以存储最大为99999999.99的货币值。

DECIMAL类型可以确保货币金额的精确性，避免由于浮点数运算带来的精度问题。

13、为什么财务、银行相关的金额字段必须使用decimal类型

非精准浮点：float,double

精准浮点：decimal

Decimal类型为精准浮点数，在计算时不会丢失精度；
占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节；
可用于存储比bigint更大的整型数据；

14、为什么说尽量使用数值替代字符串类型

因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
而对于数字型而言只需要比较一次就够了；
字符会降低查询和连接的性能，并会增加存储开销；

15、为什么建议把BLOB或是TEXT列分离到单独的扩展表中

Mysql内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，Mysql还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型。

如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。

16、如何选择高校的数据类型？

MySQL支持的数据类型非常多，选择正确的数据类型对于获得高性能至关重要。

（1）更小的

一般情况下，应该尽量使用较小的数据类型，更小的数据类型通常更快，因为占用更少的磁盘、内存和CPU缓存，处理时需要的CPU周期更短。

（2）更简单的

简单的数据类型通常需要更少的CPU周期，整形比字符串类型代价更低，因为字符集和校验规则使字符比较比整形比较更复杂。

（3）尽量避免NULL

很多表都包含可为NULL的列，即使应用程序并不需要保存NULL也是如此，因为可为NULL是列的默认属性，通常情况下，最好指定列为NOT NULL。

如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使索引、索引统计和值的比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理，可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引变成可变大小的索引。

17、Oracle中有哪些主要的数据类型？请列举并简要描述每种数据类型的基本特征和适用场景。

字符串类型：这类数据类型用于存储字符串信息。它们包括固定长度的CHAR类型和可变长度的VARCHAR2以及支持国家语言的NCHAR和NVARCHAR2等。这些类型根据存储空间的需求和字符编码的不同，适用于不同的场景，如CHAR适合存储固定长度的字符串，而VARCHAR2则适用于长度可变的字符串。
数字类型：NUMBER是Oracle中常用的数字类型，它既可以存储整数也可以存储小数，具有很大的灵活性。NUMBER类型可以指定精度（有效位数）和小数位数，适用于需要精确数值表示的场景。例如，NUMBER(5,2)可以存储从-999.99到999.99的数值。
日期类型：Oracle提供了多种日期和时间相关的数据类型，如DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE、TIMESTAMP WITH LOCAL TIME ZONE等，它们用于存储日期和时间信息，适用于需要记录日期和时间的场景。
LOB类型：包括BLOB、CLOB和NCLOB，用于存储大型对象，如文本文档、图片或音视频内容。这些类型适用于处理大量非结构化数据的场景。
RAW类型：RAW类型用于存储二进制数据，如图像或文件的二进制内容。
ROWID & UROWID类型：ROWID是一个伪列，它包含每行在数据库中的唯一标识。UROWID则是对ROWID的扩展，提供了更多的信息。

18、为什么建议where中使用默认值代替null

并不是说使用了is null或者 is not null就会不走索引了，这个跟mysql版本以及查询成本都有关；
如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效；
其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；
如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；

19、为什么避免在where子句中使用!=或<>操作符

使用!=和<>很可能会让索引失效
应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
实现业务优先，实在没办法，就只能使用，并不是不能使用

20、MySQL中有哪些字符集？

字符集规定了字符在数据库中的存储格式，比如占多少空间，支持哪些字符等等。不同的字符集有不同的编码规则，在有些情况下，甚至还有校对规则的存，校对规则是指一个字符集的排序，在运维和使用MySQL数据库中，选取合适的字符集非常重要，如果选择不恰当，轻则影响数据库性能，严重的可能导致数据存储乱码。

常见的MySQl字符集主要有以下四种：

字符集	长度	说明
GBK	2	支持中文，但不是国际通用字符集
UTF-8	3	支持中英文混合场景，是国际通用字符集
latin1	1	MySQL默认字符集
utf8mb4	4	完全兼容UTF-8，用四个字节存储更多的字符

MySQL数据库在开发运维中，字符集选用规则如下：

如果系统开发面向国外业务，需要处理不同国家、不同语言，则应该选择utf-8或者utf8mb4；
如果只需要支持中文，没有国外业务，则为了性能考虑，可以采用GBK；

21、MySQL中有哪些基础函数？

LEFT(s,n)，返回字符串 s 的前 n 个字符
LOWER(s)，将字符串 s 的所有字母变成小写字母
LPAD(s1,len,s2)，在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len
LTRIM(s)，去掉字符串 s 开始处的空格
MID(s,n,len)，从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)
POSITION(s1 IN s)，从字符串 s 中获取 s1 的开始位置
REPEAT(s,n)，将字符串 s 重复 n 次
REPLACE(s,s1,s2)，将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s)，将字符串s的顺序反过来
RIGHT(s,n) ，返回字符串 s 的后 n 个字符
RPAD(s1,len,s2)，在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len
RTRIM(s)，去掉字符串 s 结尾处的空格
SPACE(n)，返回 n 个空格
STRCMP(s1,s2)，比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1
SUBSTR(s, start, length)，从字符串 s 的 start 位置截取长度为 length 的子字符串
SUBSTRING(s, start, length)，从字符串 s 的 start 位置截取长度为 length 的子字符串，等同于 SUBSTR(s, start, length)
SUBSTRING_INDEX(s, delimiter, number)，返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
TRIM(s)，去掉字符串 s 开始和结尾处的空格
UCASE(s)，将字符串转换为大写
UPPER(s)，将字符串转换为大写

（1）CONCAT(a,b…n)

合并字符串。

select concat("和哪吒编程","一起","学习Java");

（2）CONCAT_WS(x, a,b…n)

合并字符串，每个字符串中间添加分隔符x。

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第1张

（3）ASCII(s)

返回字符串 s 的第一个字符的 ASCII 码。

SELECT name,ASCII(name) as ascName FROM student;

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第2张

（4）CHAR_LENGTH(s)、CHARACTER_LENGTH(s)

这两个函数的效果是一样的，返回字符串 s 的字符数。

SELECT CHAR_LENGTH("哪吒编程") as length;

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第3张

（5）FIELD(s,s1,s2…)

返回第一个字符串s在字符串中的位置。

select FIELD("一起","和哪吒编程","一起","学习Java");

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第4张

注意：前面的字符串，只能是后面的一个分串，不能是一部分。

（6）FIND_IN_SET(s1,s2)

返回在字符串s2中与s1匹配的字符串的位置。

SELECT FIND_IN_SET("c", "a,b,c,d,e");

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第5张

（7）FORMAT(x,n)

将数字x进行格式化，保留n位小数，最后进行四舍五入返回。

SELECT FORMAT(3.141592657, 2);

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第6张

（8）INSERT(s1,x,len,s2)

字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串。

SELECT INSERT("和哪吒编程一起学习Java", 2, 3, "美杜莎");

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第7张

（9）LOCATE(s1,s)

从字符串 s 中获取 s1 的开始位置。

（10）LCASE(s)

将字符串 s 的所有字母变成小写字母

22、MySQL 有哪些常见的数字函数？

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第8张

ABS(x)：返回 x 的绝对值;
ACOS(x)：返回 x 的余弦值（弧度制）;
ASIN(x)：返回 x 的反正弦值（弧度制）;
ATAN(x)：返回 x 的反余弦值（弧度制）;
CEIL(x)：返回 x 向上取整;
EXP(x)：返回 x 的指数;
FLOOR(x)：返回 x 向下取整;
ROUND(x, y)：返回 x 四舍五入到 y 位;
SIGN(x)：返回 x 的符号（正数为正，负数为负，0为 0）;
SIN(x)：返回 x 的正弦值（弧度制）;
TAN(x)：返回 x 的余弦值（弧度制）;
POW(x, y)：返回 x 的 y 次方;

23、MySQL 有哪些常见的日期函数？

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第9张

日期格式化函数：DATE_FORMAT()和STR_TO_DATE()；
日期加减函数：DATE_ADD()和DATE_SUB()；
时间函数：NOW()和SYSDATE()；
时间比较函数：DATE_CMP()和STR_CMP()；
日期和时间函数：CURDATE()、CURTIME()和CURTIMESTAMP()；
数学函数：ABS()、CEIL()、EXP()、FLOOR()、LOG()、LOG10()、SIN()、COS()、TAN()、TRUNCATE()和CEILING()；

24、MySQL 有哪些常见的运算符？

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第10张

（1）算术运算符：

加法：+
减法：-
乘法：*
除法：/
取模：%

（2）比较运算符：

大于：>
小于：<
等于：=
不等于：<>
大于等于：>=
小于等于：<=

（3）逻辑运算符：

与（AND）：AND
或（OR）：OR
非（NOT）：NOT

（4）位运算符：

按位与（AND）：&
按位或（OR）：|
按位异或（XOR）：^
左移（<<）：<<
右移（>>）：>>
位移（>>>）：>>>

25、说说MySQL的执行过程？

了解了MySQL的执行过程，我们才知道如何进行sql优化。

客户端发送一条查询语句到服务器；
服务器先查询缓存，如果命中缓存，则立即返回存储在缓存中的数据；
未命中缓存后，MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树，MySQL解析器将使用MySQL语法进行验证和解析。例如，验证是否使用了错误的关键字，或者关键字的使用是否正确；
预处理是根据一些MySQL规则检查解析树是否合理，比如检查表和列是否存在，还会解析名字和别名，然后预处理器会验证权限；
根据执行计划查询执行引擎，调用API接口调用存储引擎来查询数据；
将结果返回客户端，并进行缓存；

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第11张

26、数据库常见规范都有什么？

所有数据库对象名称必须使用小写字母并用下划线分割；
所有数据库对象名称禁止使用mysql保留关键字；
数据库对象的命名要能做到见名识意，并且最后不要超过32个字符；
临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀；
所有存储相同数据的列名和列类型必须一致；

27、说说MySQL三层逻辑架构？

MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图：

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第12张

（1）第一层负责连接管理、授权认证、安全等等。

每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名和密码的方式进行认证，也可以通过SSL证书进行认证。登录认证通过后，服务器还会验证该客户端是否有执行某个查询的权限。

（2）第二层负责解析查询

编译SQL，并对其进行优化(如调整表的读取顺序，选择合适的索引等)。对于SELECT语句，在解析查询前，服务器会先检查查询缓存，如果能在其中找到对应的查询结果，则无需再进行查询解析、优化等过程，直接返回查询结果。存储过程、触发器、视图等都在这一层实现。

（3）第三层是存储引擎

存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信，这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明。存储引擎不会去解析SQL。

28、MyISAM和InnoDB的区别

	MyISAM	InnoDB
事务	不支持	支持
锁机制	表级锁	行级锁
外键	不支持	支持
表主键	不必须有	必须有
全文搜索功能	支持	不直接支持，但可通过插件或第三方工具实现
崩溃恢复	不支持	支持，通过日志文件恢复数据
读写优化	无缓冲合并，写操作时全表锁定	无缓冲合并，写操作时全表锁定
缓存	依赖MySQL的key buffer	有自己的缓冲池
行数统计	需要全表扫描来计算行数	存储了每张表的具体行数，无需全表扫描
适用场景	适合读密集型应用，对事务要求不高的场景	适合写密集型应用，特别是需要事务支持和数据完整性保证的场景

对于只涉及简单读取的应用，MyISAM可能是一个好选择；而对于需要事务处理和高并发写入的应用，InnoDB通常是更合适的选项。

29、InnoDB存储引擎，有哪些特性？

事务支持：InnoDB支持ACID（原子性、一致性、隔离性和持久性）事务，保证数据的完整性和一致性。它使用START TRANSACTION语句开启事务，COMMIT语句提交事务，以及ROLLBACK语句回滚事务。
外键约束：InnoDB支持外键约束，可以在数据库层面保证数据的完整性，防止出现无效或不一致的数据。
行级锁定：InnoDB使用行级锁定机制，只锁定需要修改的行，而不是整个表，从而提高了并发性能。这种锁定策略使得多个事务可以同时访问数据库的不同部分，而不会相互阻塞。
多版本并发控制（MVCC）：InnoDB通过MVCC实现高并发读写操作，允许多个读者和写者同时访问数据，而不会相互干扰。这有助于提高数据库的并发性能和数据一致性。
缓冲池：InnoDB使用缓冲池来缓存数据和索引，减少了磁盘I/O操作，从而提高了查询性能。缓冲池是InnoDB存储引擎的关键组件之一，它有效地管理内存和磁盘之间的数据交换。
热备份：InnoDB支持在线热备份，可以在不停止数据库服务的情况下进行备份操作，这对于需要持续运行的应用来说是非常有用的。
支持大容量：InnoDB支持非常大的数据集，并且可以在64位操作系统上使用更大的内存来提高性能。这使得它成为处理大规模数据的理想选择。

存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。存储引擎API包含几十个底层函数，用于执行诸如“开始一个事务”或“根据主键查询数据”等操作，但存储引擎不会去解析SQL，不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第13张

30、了解InnoDB的底层数据分布吗？

因为InnoDB是聚簇索引，所以使用非常不同的方式存储数据。

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第14张

聚簇索引的每一个叶子节点都包含主键值、事务ID、用于事务和MVCC的回滚指针、其它列。

InnoDB的二级索引和聚簇索引不同，InnoDB的二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的指针。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当做指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。

在InnoDB中，最好按照主键顺序插入行，对于根据主键做关联操作的性能会更好。最好避免随机的聚簇索引，特别是IO密集型的应用，从性能的角度考虑，使用UUID来作为聚簇索引会很糟糕，它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

用UUID作为主键索引的缺点：

写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO。
因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行为分配空间，页分裂会导致移动大量数据，一次插入最少需要修改三个页面而不是一个页。
由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

31、了解MyISAM的底层数据分布吗？

MyISAM按照数据数据插入的顺序存储在磁盘上。

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第15张

在行的旁边显示了行号，从0开始递增。

下图隐藏了页的物理细节，只显示索引中的节点，索引中的每个叶子节点包含行号。

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第16张

在MyISAM存储引擎中主键索引和其它索引在结构上没有什么区别，主键索引就是一个名为primary的唯一非空索引。

32、Oracle是如何存储数据的？

程序通过mybatis等持久层框架访问Oracle数据库，指定表空间，表空间内包含若干张表，表中存有行数据，行数据以行片段的形式存储在数据库块中，① 当插入的行太大，无法装入单个块时；② 或因为更新的缘故，导致现有行超出了当前空间时 -> 就会发生整个行不存储在一个位置的情况。

Oracle在逻辑上将数据存储在表空间中，在物理上将数据存储在数据文件中。

表空间包括若干个数据文件，这些表空间使用与运行Oracle软件的操作系统一致的物理结构。数据库的数据存储在构成数据库表空间的数据文件中。

临时文件是一个临时表空间的文件；它是通过TEMPFILE选项创建的。临时表空间不包含表，通常用于排序。

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第17张

进一步分析它们之间的关系

数据库包含若干个表空间（逻辑存储单元）；
每一个表空间包含很多的Oracle 逻辑数据块，逻辑数据块的大小一般在2 KB 至32 KB，默认8 KB；
Oracle 数据块是逻辑I/O的最小单位；
特定数目的相邻逻辑块构成了“区”；
特定逻辑结构分配的一组区构成了一个段;

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第18张

33、Oracle逻辑数据块是什么？由哪几部分组成？

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第19张

数据库块包含块头、行数据、可用空间。

（1）块头

块头包含段类型（如表或索引）、数据块地址、表目录、行目录和事务处理插槽。

每个插槽的大小为24 字节，修改块中的行时会使用这些插槽。

（2）行数据

块中行的实际数据。

（3）可用空间

可用空间位于块的中部，允许头和行数据空间在必要时进行增长。当插入新行或用更大的值更新现有行的列时，行数据会占用可用空间。

（4）致块头增长的原因有：

行目录需要更多的行条目；
需要的事务处理插槽数多于最初配置的数目；

块中的可用空间最初是相邻的。但是，删除和更新操作可能会使块中的可用空间变成碎片，需要时Oracle 服务器会接合块中的空闲空间。

34、一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

一般情况下，我们创建的表类型是InnoDB。

不重启MySQL，如果新增一条记录，id是8；

重启，ID是6；因为InnoDB表只把自增主键的最大ID记录在内存中，如果重启，已删除的最大ID会丢失。

如果表类型是MyISAM，重启之后，最大ID也不会丢失，ID是8；

InnoDB必须有主键（建议使用自增主键，不用UUID，自增主键索引查询效率高）、支持外键、支持事务、支持行级锁。

系统崩溃后，MyISAM很难恢复；

综合考虑，优先选择InnoDB，MySQL默认也是InnoDB。

35、说一下 ACID 是什么？

ACID是数据库事务执行的四大基本要素，包括原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

（1）原子性（Atomicity）

整个事务中的所有操作，要么全部完成，要不全部不完成。

（2）一致性（Consistency）

事务在开始之前和结束之后，数据库的完整性约束没有被破坏，即事务使数据库从一个一致性状态转变到另一个一致性状态。

（3）隔离性（Isolation）

事务查看的数据是在事务开始前存在的数据，事务执行期间看到的数据不应受其他并发事务的影响。这通常是通过锁或其他并发控制技术来实现的。

（4）持久性（Durability）

持久性指的是一旦事务提交，则其结果就是永久性的，即使系统崩溃或重启也不会丢失。

36、MVCC是什么？有什么用？

MVCC，即多版本并发控制（Multi-Version Concurrency Control），是数据库管理系统中用于提高数据读取效率和并发性的一种技术。

MVCC的主要作用是在数据库系统中，特别是在面对高并发场景时，提供一种机制来避免读写操作之间的冲突。具体来说，它允许多个事务同时对同一数据进行读操作，而不会互相干扰。这是因为每个事务都会看到一个数据的“快照”，这个快照是事务开始时的数据状态，而不是实时的数据状态。这样，即使在事务执行期间其他事务对数据进行了修改，当前事务也能保持对数据的一致性视图。

MVCC通过维护数据的不同版本，实现了在不牺牲数据一致性的前提下，提高了数据库的并发性能和读取效率。这在现代数据库管理系统中，尤其是在处理大量并发读操作的场景下，是非常重要的一个特性。

37、清空表时优先使用truncate

truncate table在功能上与不带 where子句的 delete语句相同：二者均删除表中的全部行。但 truncate table比 delete速度快，且使用的系统和事务日志资源少。

delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。 truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

truncate table删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 drop table语句。

对于由 foreign key约束引用的表，不能使用 truncate table，而应使用不带 where子句的 DELETE 语句。由于 truncate table不记录在日志中，所以它不能激活触发器。

truncate table不能用于参与了索引视图的表。

38、操作delete或者update语句，为什么要加个limit或者循环分批次删除

（1）降低写错SQL的代价

清空表数据可不是小事情，一个手抖全没了，删库跑路？如果加limit，删错也只是丢失部分数据，可以通过binlog日志快速恢复的。

（2）SQL效率很可能更高

SQL中加了limit 1，如果第一条就命中目标return，没有limit的话，还会继续执行扫描表。

（3）避免长事务

delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。

（4）数据量大的话，容易把CPU打满

如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢。

（5）锁表

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。

39、MySQL 中 in 和exists 的区别？

IN：当使用IN时，MySQL首先执行子查询并将结果存储起来，然后主查询根据这个结果集来进行匹配操作。**如果子查询返回的结果集较小，那么IN的效率较高。**此外，如果使用了NOT IN，通常会导致全表扫描，且不会利用索引，因此其效率较低。

EXISTS：使用EXISTS时，会对主查询的每一行数据执行一次子查询，只要子查询返回至少一行数据，EXISTS条件就视为真。EXISTS通常会利用到索引，特别是当与NOT EXISTS搭配使用时，可以在子查询中利用索引优化查询效率。如果子查询的表较大，则使用EXISTS可能更高效。

40、MySQL中如何编写分页SQL？

在MySQL中，可以使用LIMIT和OFFSET子句来实现分页。以下是一个简单的例子：

SELECT * FROM user LIMIT 10 OFFSET 0;  -- 获取第一页，每页10条记录
SELECT * FROM user LIMIT 10 OFFSET 10;  -- 获取第二页，每页10条记录

上面的例子中，LIMIT用来限制返回的行数，OFFSET用来指定从结果集中的第几行开始返回记录。

另外，还可以使用LIMIT子句的简化语法，可以用LIMIT 10, 10表示从第11行开始的连续10行，以此类推。

41、Oracle 中如何编写分页SQL？

在Oracle 11g及更早的版本中，可以使用ROWNUM伪列来实现分页。但是，由于ROWNUM是在查询结果返回之前分配的，因此你需要使用子查询来正确地实现分页。

例如，假设我们有一个名为user的表，我们想要查询第2页的数据，每页显示10条记录：

SELECT * FROM (  
    SELECT u.*, ROWNUM r  
    FROM (SELECT * FROM user ORDER BY user_id) u  
    WHERE ROWNUM <= 20 -- 这里是第二页的结束位置（页码*每页记录数）  
)  
WHERE r > 10; -- 这里是第二页的起始位置（(页码-1)*每页记录数 + 1）

在Oracle 12c及更高版本中，你可以使用FETCH FIRST … OFFSET …子句来更直接地实现分页。这个语法更加直观，并且容易理解。

SELECT * FROM employees  
ORDER BY employee_id  
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

在这个例子中：

OFFSET 10 ROWS 表示跳过前10条记录。

FETCH NEXT 10 ROWS ONLY 表示接下来获取10条记录。

你可以将OFFSET和FETCH子句中的数字替换为变量，以便在应用程序中动态地设置分页参数。

42、分页查询的优缺点是什么

（1）优点：

减少数据传输：分页查询可以减少需要传输到客户端的数据量，提高数据检索的效率和减少网络流量。
提升用户体验：通过分页使得页面加载更快，用户可以快速浏览和定位所需的数据。
节省内存：通过分页查询，可以分批次地从数据库中检索数据，减少内存占用。
提升查询性能：对于大型数据集，分页查询可以降低查询的复杂度，提升性能。

（2）缺点：

数据不一致：当分页查询涉及到实时数据时，可能会因为数据的修改而导致分页数据的不一致性。
数据排序：如果分页数据需要按照特定的顺序排序，可能需要额外的查询和处理，增加了数据库的负担。
对数据库性能的影响：如果数据库中的数据量非常大，频繁的分页查询可能会影响数据库的性能。
逻辑复杂性：在编写代码和逻辑上进行分页可能会增加一定的复杂性，尤其是在处理边界情况时。

43、如何提高分页查询的效率？

（1）使用索引

在分页查询中，如果需要根据某个字段进行排序或筛选，那么在该字段上建立索引可以大大提高查询效率。

（2）避免全表扫描

在进行分页查询时，应该尽量避免全表扫描的情况。全表扫描会消耗大量的系统资源和时间，导致查询速度变慢。因此，在编写SQL语句时，应该尽量使用WHERE子句来限制查询的范围，从而减少查询的数据量。

（3）优化SQL语句

在编写SQL语句时，应该尽量减少不必要的操作，如避免使用子查询、避免使用JOIN等。此外，还可以通过调整查询的顺序、使用LIMIT关键字等方式来优化SQL语句。

（4）使用缓存

对于一些经常被访问的分页查询结果，可以使用缓存技术将其存储起来，以便下次访问时直接从缓存中获取数据，从而提高查询效率。

（5）使用数据库分区

对于大型数据库，可以考虑使用数据库分区技术将数据分成多个部分，每个部分存储在不同的物理磁盘上。这样可以减少查询时需要扫描的数据量，从而提高查询效率。

（6）应用层面的优化

在应用层面实现分页的懒加载或异步加载，减少一次性加载的数据量。

（7）使用分页插件

很多数据库和编程语言都提供了分页插件或库，它们通常已经对分页查询进行了优化。

44、MySQL中有缓存吗？有什么作用？

查询缓存：MySQL中用来缓存查询结果的一种机制，它能够存储SELECT查询的结果集，当同样的查询再次执行时，可以直接从缓存中获取结果，而不需要再次解析查询或访问数据库。
表缓存：用来缓存表的元数据，如表的结构定义、索引信息等。当一个表被频繁地查询时，它的元数据会被加载到缓存中，这样可以减少对磁盘的访问次数，提高查询效率。
Key Buffer 缓存：Key Buffer 缓存主要用于 MyISAM 存储引擎，用于缓存索引数据。它可以加快索引的访问速度，提高查询性能。
InnoDB 缓存池：InnoDB 缓存池主要用于存储 InnoDB 存储引擎的数据和索引。通过将热数据存储在内存中，减少了磁盘 IO 的次数，提高了数据的访问速度和查询性能。

具体应用场景包括：

当系统中有一些查询需求是重复性的，且数据不经常变动时，可以开启查询缓存来提高查询性能。
当需要大量使用 MyISAM 存储引擎的表时，可以通过调整 Key Buffer 缓存来提高索引的访问速度。
当需要使用 InnoDB 存储引擎时，通过调整 InnoDB 缓存池的大小，可以提高数据的访问速度和查询性能。

45、MySQL如何判断缓存命中？

判断是否命中时，MySQL不会解析，而是直接使用SQL语句和客户端发送过来的其它原始信息。任何字符上的不同，例如空格、注释，丢回导致缓存的不命中。通常使用统一的编码规则是一个好的习惯，会让你的系统运行的更快。

当查询语句中有一些不确定的数据时，不会被缓存，比如函数now()。实际上，如果缓存中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL系统表、或者任何包含列级别权限的表，都不会被缓存。

5 万字 124 道MySQL经典面试题总结（2024修订版）,在这里插入图片描述,第20张

46、使用查询缓存时，要注意什么？

打开查询缓存对读和写操作都会带来额外的消耗：

读查询在执行之前要先检查是否命中缓存；
如果读查询可以被缓存，那么当完成执行后，MySQL如果发现缓存中没有这个查询，会将其结果存入查询缓存，这会带来额外的系统消耗；
对写操作也有影响，因为当向某个表写入数据的时候，MySQL必须将对应表的所有缓存设置失效。如果查询缓存非常大或者碎片很多，这个操作就可能会带来很大的系统消耗；

虽然如此，查询缓存仍然会给系统带来性能的提升。但是，上述的额外消耗也可能不断增加，再加上对查询缓存操作是一个加锁排它操作，这个消耗也不小。

对InnoDB用户来说，事务的一些特性会限制查询缓存的使用。当一个语句在事务中修改了某个表，在事务提交前，MySQL都会将这个表对应的查询缓存设置失效，因此，长时间运行的事务，会大大降低查询缓存的命中率。

47、说说InnoDB和查询缓存的关系？

InnoDB会控制在一个事务中是否可以使用查询缓存，InnoDB会同时控制对查询缓存的读写操作。事务是否可以访问查询缓存取决于当前事务的ID，以及对应的数据表上是否有锁。每一个InnoDB表的内存数据字典都保存了一个事务ID号，如果当前事务ID小于该事务ID，则无法访问查询缓存。

如果表上有任何的锁，那么对这个表的任何查询语句都是无法被缓存的。例如，某个事务执行了select for update语句，那么在这个锁释放之前，任何其它的事务都无法从查询缓存中读取与这个表相关的缓存结果。

当事务提交时，InnoDB持有锁，并使用当前的一个系统事务ID更新当前表的计数器。InnoDB将每个表的计数器设置成某个事务ID，而这个事务ID就代表了当前存在的且修改了该表的最大的事务ID。

48、说说缓存表和汇总表有什么区别？

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据，有时候还需要创建一张完全独立的汇总表或缓存表。

缓存表用来存储那些获取很简单，但速度较慢的数据；
汇总表用来保存使用group by语句聚合查询的数据；
对于缓存表，如果主表使用InnoDB，用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文检索。

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引。

当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用，这就需要通过使用影子表来实现，影子表指的是一张在真实表背后创建的表，当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。

为了提升读的速度，经常建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表，这些方法会增加写的负担妈也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧，虽然写操作变慢了，但更显著地提高了读的性能。

49、在数据库设计中，主键（Primary Key）的唯一性是确保数据一致性和完整性的关键因素。请问，如何在数据库中实现主键的唯一性？有哪些不同的方法可以保证主键的唯一性，并且，这些方法分别有什么优缺点？

（1）自增长字段

自增长字段通常与主键结合使用，每次插入新的记录时，该字段的值自动递增，从而保证每个记录有一个唯一的标识。自增长字段从1开始，每次递增1，它不允许重复值也不允许为空，适合用于生成唯一ID。

实现简单，效率高，且与数据库表紧密集成，但依赖于数据库的自增长机制，可能在分布式环境下产生同步问题。

（2）UUID

UUID全称是通用唯一识别码，它是通过一定的算法生成的，在全球范围内具有唯一性。UUID可以由数据库生成，也可以由程序生成，其优点在于全球唯一性、生成性能高，但缺点包括无法保证趋势递增、查询效率较低、存储空间需求大等问题。

提供了跨数据库、分布式系统的唯一性保障，不受存储位置的限制，但在存储和索引方面可能需要更多的空间和处理时间。

（3）唯一性约束（UNIQUE）

除了主键约束以外，可以为数据库表中的其他列定义UNIQUE约束，确保这些列中的值也是唯一的。唯一性约束可以在一个或多个列上定义，包括复合UNIQUE约束，适用于需要确保特定列或列组合值唯一的场景。

灵活应用于非主键列的唯一性要求，易于定义和使用，但需要针对每一列或列组合单独设置。

50、每个Innodb表必须有个主键吗？

Innodb是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。

Innodb是按照主键索引的顺序来组织表的
1. 不要使用更新频繁的列作为主键，不适用多列主键；
2. 不要使用UUID、MD5、HASH、字符串列作为主键（无法保证数据的顺序增长）；
3. 主键建议使用自增ID值；
51、在分布式环境下如何处理主键唯一性问题？

在分布式环境中，确保主键的唯一性是一项挑战，但有多种策略可以应对这一挑战。具体内容如下：

（1）数据库自增长序列或字段

这是最常见的方法，利用数据库的特性来生成全数据库唯一的ID。这种方法简单且性能可接受，数字ID天然排序，有助于分页或排序。然而，它的缺点在于不同数据库的语法和实现不同，迁移时需要额外处理。此外，如果只有一个主库可以生成ID，可能存在单点故障的风险，并且在性能达不到要求时难以扩展。

（2）UUID

UUID可以提供全球唯一的特性，理论上可以用作分布式ID。但是，UUID较长，可能导致存储和索引的空间需求增加，查询效率降低。此外，UUID不具有递增性，不利于分页或排序。

（3）全局唯一ID生成服务

使用独立的服务来生成全局唯一的ID，这通常涉及到复杂的算法和技术，如雪花算法（Snowflake），它能够在分布式系统中生成趋势递增的唯一ID。这种方法能够满足高可用性和有序增长的需求，但实现起来较为复杂。

（4）利用中间件

一些中间件提供了分布式ID生成的功能，例如Redis、Zookeeper等。这些中间件可以帮助生成全局唯一的ID，同时提供了高可用性和易于扩展的特点。

（5）利用业务逻辑
在某些情况下，可以通过业务逻辑来生成唯一ID，例如结合用户信息、时间戳等元素。这种方法依赖于业务的具体情况，可能需要定制化的解决方案。

（6）分库分表

在数据量巨大的情况下，可以通过分库分表来解决单一数据库的性能瓶颈。但这也会带来分布式系统中唯一主键ID生成的问题，需要考虑如何在不同数据库间协调生成唯一的主键。

每种方法都有其适用场景和优缺点，选择合适的策略需要综合考虑系统的具体需求、性能要求以及可维护性。在实际应用中，可能需要结合多种方法来满足不同的业务需求。

52、设计数据库时，有哪些原则？

① 数据库设计最起码要占用这个项目开发的40%以上的时间

② 数据库设计不仅仅停留在页面demo的表面

页面内容所需字段，在数据库设计中只是一部分，还有系统运转、模块交互、中转数据、表之间的联系等等所需要的字段，因此数据库设计绝对不是简单的基本数据存储，还有逻辑数据存储。

③ 数据库设计完成后，项目80%的设计开发都要存在你的脑海中

每个字段的设计都要有他存在的意义，要清楚的知道程序中如何去运用这些字段，多张表的联系在程序中是如何体现的。

④ 数据库设计时就要考虑效率和优化问题

数据量大的表示粗粒度的，会冗余一些必要字段，达到用最少的表，最弱的表关系去存储海量的数据。大数据的表要建立索引，方便查询。对于含有计算、数据交互、统计这类需求时，还有考虑是否有必要采用存储过程。

⑤ 添加必要的冗余字段

像创建时间、修改时间、操作用户IP、备注这些字段，在每张表中最好都有，一些冗余的字段便于日后维护、分析、拓展而添加。

⑥ 设计合理的表关联

若两张表之间的关系复杂，建议采用第三张映射表来关联维护两张表之间的关系，以降低表之间的直接耦合度。

⑦ 设计表时不加主外键等约束关联，系统编码阶段完成后再添加约束性关联

⑧ 选择合适的主键生成策略

数据库的设计难度其实比单纯的技术实现难很多，他充分体现了一个人的全局设计能力和掌控能力，最后说一句，数据库设计，很重要，很复杂。

53、数据库设计原则有哪些？

（1）业务逻辑和数据分离

设计时应将业务逻辑与数据存储分离，减少对数据库的依赖，例如尽量减少使用自定义函数、存储过程和视图。

（2）安全设计

应充分考虑数据库的整体安全性，包括管理和使用人员的权限分离，确保不同角色的合理权限控制。

（3）性能

根据数据对象的访问频度及性能需求，结合主机、存储等硬件需求，做好性能设计规划。

（4）数据增长和分布模式

针对数据量的增长趋势和业务模型，决策是否采用分布式模式（水平或垂直拆分）。

（5）备份和恢复策略

根据业务数据的安全等级，设计合适的数据备份和恢复策略。

（6）面向对象转过程

在设计初期先列出所有相关的对象及其属性，再考虑这些对象间的过程关系。

（7）明确表依赖和主外键关系

确保表之间的主外键关系清晰且正确反映实际业务逻辑，避免混乱或错误设置。

（8）中间表解决N:N关系

对于存在多对多关系的实体，应引入中间表以简化关系处理。

（9）数据库命名规范

数据库名称应具有明确的业务含义，便于理解和管理。

（10）按类型分开管理数据

不同类型的数据（如财务、业务等）应分开管理，以降低风险并提高维护效率。

（11）减少存储过程的使用

由于存储过程在不同数据库中支持差异较大，应避免过多使用复杂的存储过程，以减轻数据库服务器的压力。

54、视图是什么？有什么优势？

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。对多张表的复杂查询，使用视图可以简化查询，当视图使用临时表时，无法使用where条件，也不能使用索引。

单表视图一般用于查询和修改，会改变基本表的数据，多表视图一般用于查询，不会改变基本表的数据。

使用视图的目的是为了保障数据安全性，提高查询效率。

视图的优势：
1. 使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
2. 使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3. 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
55、视图与物化视图有什么区别？

（1）视图

视图可以理解为一张表或多张表的与计算，它可以将所需要查询的结果封装成一张虚拟表，基于它创建时指定的查询语句返回的结果集。

查询者并不知道使用了哪些表、哪些字段，只是将预编译好的SQL执行，返回结果集。每次查询视图都需要执行查询语句。

（2）物化视图

为了防止每次都查询，先将结果集存储起来，这种有真实数据的视图，称为物化视图。

MySQL并不原生支持物化视图，可以使用Justin Swanhart的开源工具Flexviews实现。

相对于传统的临时表和汇总表，Flexviews可以通过提取对源表的更改，增量地重新计算物化视图的内容。

56、缓存表和汇总表是什么，有什么区别？

有时提升性能最好的方法是在同一张表中保存衍生的冗余数据，有时候还需要创建一张完全独立的汇总表或缓存表。
- 缓存表用来存储那些获取很简单，但速度较慢的数据；
- 汇总表用来保存使用group by语句聚合查询的数据；
  对于缓存表，如果主表使用InnoDB，用MyISAM作为缓存表的引擎将会得到更小的索引占用空间，并且可以做全文检索。
  
  在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引。
  
  当重建汇总表和缓存表时，通常需要保证数据在操作时依然可用，这就需要通过使用影子表来实现，影子表指的是一张在真实表背后创建的表，当完成了建表操作后，可以通过一个原子的重命名操作切换影子表和原表。
  
  为了提升读的速度，经常建一些额外索引，增加冗余列，甚至是创建缓存表和汇总表，这些方法会增加写的负担妈也需要额外的维护任务，但在设计高性能数据库时，这些都是常见的技巧，虽然写操作变慢了，但更显著地提高了读的性能。
  
  57、什么是计数器表？如何使用？
  
  通常创建一张表来存储用户的点赞数、网站访问数等。
  
  create table like_count(num int unsigned not null) engine=InnoDB;
  
  每次点赞都会导致计数器进行更新：
  
  update like_count set num = num + 1;
  
  问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁mutex。这会使这些事务都只能串行执行，要获得更高的并发更新性能，可以将计数器保存在多行中，每次随机选择一行进行更新。
```
create table like_count(
	slot tinyint unsigned not null primary key,
	num int unsigned not null
) engine=InnoDB;
```
  预先在这张表中新增10条数据，然后选择一个随机的槽slot进行更新：
  
  注意：为了研究之后遇到的问题，后来又插入了一条~
```
update like_count set num = num + 1 where slot = floor(rand() * 10);
```
  更新了两行，这是为什么呢？
  select一下，查询结果，有的时候0条，有的时候1条，有的时候2条，有的时候3条，惊呆了，这么有趣的事情，我怎么能放过，让我们一起一探究竟。
  让我们一起一探究竟：
  - floor() 函数的作用：返回小于等于该值的最大整数；
  - rand()函数的作用：获得0到1之间的随机值；
    
    在ORDER BY或GROUP BY子句中使用带有RAND()值的列可能会产生意想不到的结果，因为对于这两个子句，RAND()表达式都可以对同一行计算多次，每次返回不同的结果。要从一组行中随机选择一个样本，将ORDER BY RAND()和LIMIT配合使用。
    
    在MySQL的官方手册里，针对RAND()的提示大概意思就是，在ORDER BY从句里面不能使用RAND()函数，因为这样会导致数据列被多次扫描。
    
    这就完了？
    
    58、阐述一下Oracle 存储过程的使用步骤？
    
    （1）创建存储过程：在 Oracle 数据库中创建存储过程使用 CREATE PROCEDURE 语句。
```
CREATE OR REPLACE PROCEDURE sp_GetEmployeeDetails
AS
BEGIN
  -- 存储过程的SQL语句
END;
```
    （2）调用存储过程：通过 EXECUTE 或 / 命令来执行存储过程。
```
EXECUTE sp_GetEmployeeDetails;
-- 或者
EXEC sp_GetEmployeeDetails;
```
    （3）传递参数：可以在存储过程定义时指定输入和输出参数。
    
    调用存储过程时需要传递参数：EXEC sp_GetEmployeeDetails(1001);
```
CREATE OR REPLACE PROCEDURE sp_GetEmployeeDetails
    (p_EmployeeID IN NUMBER)
AS
BEGIN
    -- 存储过程的SQL语句使用 p_EmployeeID 参数
END;
```
    （4）修改存储过程：使用 CREATE OR REPLACE PROCEDURE 语句来修改存储过程。
    
    （5）删除存储过程：使用 DROP PROCEDURE 语句来删除存储过程。
    
    59、Oracle 存储过程有哪些注意事项？
    1. 变量声明：在存储过程中需要使用变量时，必须先声明变量的数据类型。例如：DECLARE v_employee_name VARCHAR2(100);
    2. 异常处理：在存储过程中添加适当的异常处理代码，使用 EXCEPTION 来捕获和处理异常情况。
    3. 事务管理：在存储过程中可以使用 COMMIT 和 ROLLBACK 语句来管理事务，确保数据的一致性和可靠性。
    4. 性能优化：对存储过程的执行计划进行优化，可以通过索引、分区等技术提高存储过程的执行效率。
    5. 权限控制：确保用户拥有执行存储过程的必要权限，避免权限限制导致存储过程执行失败。
    6. 日志记录：在存储过程中加入日志记录功能，便于跟踪存储过程的执行情况和结果。
    60、如何优化存储过程？
    1. 尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
    2. 中间结果存放于临时表，加索引。
    3. 少使用游标。sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次读取。
    4. 事务越短越好。sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成
      并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极地。
    5. 使用 try-catch 处理错误异常。
    6. 查找语句尽量不要放在循环内。
    7. 减少数据访问量，避免在循环中频繁访问数据库，优化 SQL 查询语句，使用适当的条件过滤数据，减少不必要的数据交互和传输。
    8. 分批次处理：对于大量数据操作的场景，可以考虑将数据分批次处理，减少记录锁定的时间，提高并发性能。
    61、存储过程和函数有什么区别？
    
    存储过程（Stored Procedure）和函数（Function）是数据库中常用的两种可重复调用的程序单元，它们都包含了多条 SQL 语句来执行特定的任务。然而，存储过程和函数之间存在一些关键的区别：
    
    存储过程：
    1. **返回值：**存储过程可以包含输入参数、输出参数和返回参数，但一般情况下不返回结果集或值，而是通过输出参数或其他方式将结果传出。
    2. 事务管理：存储过程可以包含事务控制语句（如 COMMIT、ROLLBACK）来管理事务的范围，可以修改数据库的数据，保证数据的一致性。
    3. 可以包含 DML 语句：存储过程可以包含数据操纵语言（DML）语句（如 INSERT、UPDATE、DELETE），可以对数据库进行增删改操作。
    4. 可独立执行：存储过程可以单独执行，也可以被其他程序或代码调用执行，适用于处理业务逻辑和复杂查询等场景。
    5. 存储在数据库中：存储过程是存储在数据库中的，因此可以减少数据的传输量，提高性能。
    函数：
    1. 返回值：函数通常返回一个值（标量值或表），可作为查询的一部分来使用，具有明确的返回结果。
    2. 只读操作：函数通常用于执行只读操作，不能包含数据操纵语言（DML）语句，不能修改数据库中的数据。
    3. 不能包含事务控制语句：函数不能包含 COMMIT、ROLLBACK 等事务控制语句，因为函数在调用时处于当前事务的上下文中。
    4. 适用于数据查询：函数适用于在查询中计算和处理数据，可以像内置函数一样被调用。
    5. 可以嵌套调用：函数支持嵌套调用，一个函数内可以调用另一个函数，形成层层调用的逻辑。
    62、触发器是什么？有什么作用？具体有哪些使用场景？
    
    触发器是一种由事件驱动的特殊类型的存储过程。
    
    触发器是数据库中用来自动执行预定义的SQL代码块的一种对象。当特定的数据库事件，如插入（INSERT）、更新（UPDATE）或删除（DELETE）操作发生时，触发器会自动执行相关的SQL语句。
    
    触发器的使用场景：
    1. 数据一致性维护：在多个相关联的表之间插入、更新或删除数据时，触发器可以用来维护数据之间的一致性，确保数据的完整性。
    2. 审计跟踪：使用触发器可以记录数据表的变更历史，例如，每次数据发生变化时，自动在审计表中记录变化的内容和时间。
    3. 复杂业务规则实施：对于复杂的业务逻辑，触发器可以在数据变更时自动执行相应的逻辑，简化应用程序的处理流程。
    4. 数据验证：在数据输入到表中之前，触发器可以检查数据的有效性，拒绝不符合条件的数据输入。
    5. 自动化处理：触发器可以自动执行一些常规任务，比如在用户表中新增一条记录后自动发送欢迎邮件。
    6. 同步数据：当主表中的数据发生变化时，触发器可以用来同步更新从表中的相关数据，保持数据的同步性。
    63、索引不宜太多，一般5个以内？
    1. 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；
    2. 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；
    3. 索引表的数据是排序的，排序也是要花时间的；
    4. insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；
    5. 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；
    64、索引是什么，底层是什么数据结构？
    
    索引是存储引擎用于快速查找记录的一种数据结构。我觉得数据库中最重要的知识点，就是索引。
    
    存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。
    
    B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
    
    B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根结点开始进行搜索。根结点的槽中存放了指向子结点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。
    
    叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。B-Tree对索引列是顺序组织存储的，所有很适合查找范围数据。B-Tree适用于全键值、键值范围或键前缀查找。
    因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的order by操作。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。
    
    65、性别适合做索引吗？
    
    如果经常需要根据性别进行查询，那么为性别字段建立索引可能有助于提高查询性能。
    
    但是，性别字段通常只有几个可能的值（例如，男、女、其他）。这意味着它的选择性较低，因为大多数记录可能共享相同的性别值。低选择性的字段可能不是建立索引的好选择，因为索引可能不会带来显著的性能提升。
    
    66、什么情况不适合添加索引？
    
    （1）频繁更新的字段
    
    如果一个字段的数据经常发生变化，那么在该字段上创建索引可能会导致索引频繁更新，从而降低数据库的性能。
    
    （2）数据量小的表
    
    如果一个表只有很少的记录，那么即使创建了索引，也可能不会对查询性能有显著的提升，因此没有必要创建索引。
    
    （3）数据重复且分布平均的字段
    
    如果一个字段的数据重复率很高，且数据分布相对均匀，那么索引的效率会很低，因为索引的优势在于快速定位和排除不相关的数据。
    
    （4）BLOB或TEXT类型的字段
    
    由于这些字段的数据量通常很大，因此在这些字段上创建索引可能会占用大量的存储空间，并且在查询时效率也不高。
    
    （5）使用LIKE模糊查询的字段
    
    如果需要在一个字段上进行LIKE模糊查询，且查询模式不是从字符的开头开始（例如，‘%abc’），那么即使该字段上有索引，也无法有效地使用索引来加速查询。
    
    67、避免索引失效的一些原则
    1. 复合索引，不要跨列或无序使用（最佳左前缀）；
    2. 符合索引，尽量使用全索引匹配；
    3. 不要在索引上进行任何操作，例如对索引进行（计算、函数、类型转换），索引失效；
    4. 复合索引不能使用不等于（!=或<>）或 is null（is not null），否则索引失效；
    5. 尽量使用覆盖索引（using index）；
    6. like尽量以常量开头，不要以%开头，否则索引失效；如果必须使用%name%进行查询，可以使用覆盖索引挽救，不用回表查询时可以触发索引；
    7. 尽量不要使用类型转换，否则索引失效；
    8. 尽量不要使用or，否则索引失效；
    68、为什么不建议使用外键索引？
    
    外键通常都要求每次在修改数据时都要在另外一张表中进行一次额外的查询操作，虽然InnoDB强制外键使用索引，但还是无法消除这种约束检查的开销。如果外键的选择性很低，则会导致一个选择性很低的索引。
    
    不过在某些场景下，外键会提升一些性能，比如想确保两个相关表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高的多，此外。外键在相关数据的删除和更新上，也比在应用中维护要更高效，不过，外键维护操作时逐行进行的，这样的更新会比批量删除和更新要慢些。
    
    外键约束使查询时额外访问一些别的表，也就是需要额外的锁。如果向子表中写入一条记录，外键约束会让InnoDB检查对应的父表的记录，也就是需要对父表的对应记录进行加锁操作，来确保这条记录不会在这个事务完成之时就被删除了。这会导致额外的锁等待，甚至会导致一些死锁。因为没有直接访问这些表，所以这类死锁问题很难排查。
    
    所以，在目前的很多项目中，为了性能的考虑，已经不使用外键了。
    
    69、为什么避免在where子句中使用 or 来连接条件
    1. 使用or可能会使索引失效，从而全表扫描；
    2. 对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描；
    3. 也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；
    4. 虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的；
    70、遇到过MySQL死锁问题吗？你是如何解决的？
    
    （1）查看死锁信息
    
    使用 SHOW ENGINE INNODB STATUS 命令可以查看当前数据库的死锁信息。这个命令会返回一个包含大量信息的文本结果，你需要从中找到 “LATEST DETECTED DEADLOCK” 部分，它会详细列出导致死锁的SQL语句和事务信息。
    
    （2）避免长时间的事务
    
    尽量将大事务拆分成多个小事务。
    
    分析导致死锁的SQL语句，看是否可以优化。
    
    避免在事务中执行复杂的查询或长时间的操作。
    
    （3）调整事务隔离级别
    
    MySQL支持四种事务隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。默认的隔离级别是REPEATABLE READ。在某些情况下，降低事务隔离级别可以减少死锁的发生，但需要注意这可能会带来其他问题，如不可重复读或幻读。
    
    （4）锁定顺序
    
    确保所有事务以相同的顺序请求锁。这样可以大大减少死锁的可能性，因为事务将按照相同的顺序等待锁。
    
    （5）使用低锁等级的锁
    
    例如，如果可能的话，使用行级锁而不是表级锁，以减少锁的粒度，从而减少锁的争用。
    
    71、如何查询重复的数据？
```
SELECT column_name, COUNT(column_name)
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) > 1;
```
    72、索引怎么定义，分哪几种
    1. 普通索引：这是最基本的索引类型，没有任何限制，可以简单地通过CREATE INDEX语句创建。
    2. 唯一索引：这种类型的索引确保在列中的所有值都是唯一的，不允许出现重复的值。
    3. 主键索引：主键索引是唯一索引的一个特例，它确保每个表都有一个唯一的主键，通常在创建表时定义主键时自动创建。
    4. 全文索引：这种索引用于全文搜索，允许对文本字段进行复杂的搜索操作。
    5. 聚簇索引：在聚簇索引中，数据行存储在索引的叶级页上，这意味着整个表的行按照索引的顺序物理存储。
    6. 非聚簇索引：与聚簇索引相反，非聚簇索引的叶级页包含指向数据行的指针，而不是数据行本身。
    73、如何触发联合索引？
    
    （1）对user表建立联合索引username、password
    
    （2）使用联合索引的全部索引键可触发联合索引
    
    （3）使用联合索引的全部索引键，但是用or连接的，不可触发联合索引
    
    （4）单独使用联合索引的左边第一个字段时，可触发联合索引
    
    （5）单独使用联合索引的其它字段时，不可触发联合索引
    
    74、什么是全文索引，怎么创建，有哪些应用场景？
    
    全文索引是一种能够快速检索文本信息的数据结构，它适用于需要对大量文本数据进行搜索的场景，可以通过模糊查询like触发全文索引。
    
    可以在创建表的SQL语句中指定FULLTEXT KEY来创建全文索引列，也可以使用ALTER TABLE语句为表中的列添加全文索引。
    
    全文索引在处理大量文本数据的模糊查询时，尤其是当使用LIKE操作符进行搜索时，可以显著提高搜索效率。
    
    全文索引的应用场景：
    1. 当使用LIKE操作符进行模糊查询时，普通索引可能会失效，而全文索引可以提供更快的查询速度。
    2. 全文索引支持MATCH…AGAINST语句，这允许用户在特定列中搜索包含关键词的记录。
    75、什么是聚簇索引，怎么创建，有哪些应用场景？
    
    聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
    
    当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
    
    因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。
    
    叶子页包含了行的全部数据，节点页只包含索引列，索引列包含的是整数值。
    
    InnoDB通过主键聚集数据，也就是主键列，如果没有主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。
    
    聚簇索引的应用场景包括：
    1. 频繁地使用范围查询：由于数据行在磁盘上按照索引顺序进行物理存储，因此对于范围查询（如 BETWEEN、IN等）效果更好，因为这些操作更加高效地利用了索引的排序。
    2. 主键索引：通常，当在数据库表中定义主键时，数据库管理系统会自动为主键列创建聚簇索引。
    3. 数据的顺序化存储：当数据需要按照某一列进行有序的存储时，可以考虑创建聚簇索引。
    总的来说，聚簇索引适用于需要频繁使用范围查询和以特定顺序访问数据的场景。
    
    76、聚簇索引有哪些优缺点？
    
    （1）优点
    1. 可以把相关数据保存在一起，例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘IO;
    2. 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快;
    3. 使用覆盖索引扫描的查询可以直接使用节点页中的主键值。
    （2）缺点
    1. 聚簇索引最大限度的提高了IO密集型应用的性能，但如果数据全部都放在了内存中，则访问的顺序就没有那么重要了，聚簇索引也就没什么优势了；
    2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键的顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表；
    3. 更新聚簇索引的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；
    4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间；
    5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候；
    6. 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列；
    7. 二级索引访问需要两次索引查找，而不是一次；
    二级索引查找时，需要找到二级索引的叶子节点中存放的主键值，然后到聚簇索引中查找对应的行，所以是两次索引查找，在InnoDB中，可以通过自适应哈希索引减少这样的重复工作。
    
    77、什么是非聚簇索引，怎么创建，有哪些应用场景？
    
    非聚簇索引是数据库中的一种索引类型，它不会对数据行的物理存储顺序进行排序。相对于聚簇索引，非聚簇索引会将索引和数据行进行分开存储，索引指向实际数据所在的位置。在非聚簇索引中，叶子节点包含的是指向数据行的指针，而不是数据行本身。
    
    要创建非聚簇索引，需要选择一个或多个列作为索引键，然后对这些列创建非聚簇索引。
    
    非聚簇索引的应用场景包括：
    1. 频繁的等值查询：非聚簇索引在处理等值查询（如 WHERE column = value）和连接操作时效果更好。
    2. 覆盖索引：当需要查询的列都包含在索引中时，可以使用非聚簇索引来覆盖查询，从而避免访问实际数据行，提高查询性能。
    3. 多表连接：非聚簇索引在连接多个表的查询中效果更好，因为它能够更快速地定位到连接需要的数据行。
    总的来说，非聚簇索引适用于需要频繁进行等值查询和连接操作的场景，以及需要使用覆盖索引的情况。
    
    78、非聚簇索引一定会回表查询吗？
    
    是否需要回表查询取决于查询操作是否能够通过索引直接获得所有需要的数据，如果不能直接全部返回，则需要通过主键索引去再次获取其他列的值，就会导致回表查询。
    
    为了减少回表查询的次数，可以考虑使用覆盖索引。覆盖索引是指索引包含了查询语句中需要获取的所有列的值，这样数据库引擎就可以直接从索引中获取数据而不需要额外查找主键索引，避免回表查询，提高性能。
    
    79、说说对覆盖索引、回表的理解？
    
    覆盖索引避免了回表操作，提高了查询效率。
    
    覆盖索引指的是一个查询只需要通过索引就能获取所有需要的数据，而无需访问数据行本身。这通常发生在查询中所需的列全部包含在索引中的情况。
    
    回表指当索引不包含查询所需的所有列时，先通过索引找到对应的聚簇索引值（通常是主键），然后再通过这个聚簇索引值去查找完整的行记录。这个过程涉及到两次索引扫描，第一次是查找二级索引，第二次是查找聚簇索引，因此性能会比覆盖索引低。
    
    80、覆盖索引的优点
    1. 索引条目通常远小于数据行大小，如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于IO密集型的应用也有帮助，因为索引比数据更小，更容易放入内存中。
    2. 索引是按照列值顺序存储的，所以对IO密集型的范围查询会比随机从磁盘读取数据的IO少的多。
    3. 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题。
    4. 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用，InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。
    在发起一个覆盖索引查询时，在explain的Extra列可以看到“Using index”的信息。
    
    81、创建索引时有哪些基本原则应该遵循？它们各自的意义和作用是什么？
    
    基本原则：
    1. 最左前缀匹配原则：在使用联合索引时，会从联合索引的最左侧开始并一直向右匹配，直到遇到范围查询条件为止。所以需要将最常用的列放在前面，以确保索引可以被充分利用。
    2. 选择性原则：选择区分度高的列作为索引，这样的索引能更有效地缩小查询结果的范围，从而提高查询效率。
    3. 为搜索条件、排序和分组字段创建索引：出现在 WHERE 子句后面的字段、连接子句中指定的列、用于排序和分组的字段都是适合设置索引的列，这些情况下使用索引可以显著提高查询性能。
    数据库索引的意义和作用：
    1. 提高查询速度：通过创建唯一性索引，我们可以加快数据的检索速度。
    2. 唯一索引确保了数据库表中的每一行数据的唯一性，这对于保持数据的精确性和可靠性至关重要。
    3. 加速表连接：在进行多表连接查询时，如果关联字段上有索引，则可以显著减少连接操作的时间复杂度，进而提升整体的查询效率。
    82、举例说明一下复合索引最左特性
    
    复合索引也称为联合索引，当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
    
    联合索引不满足最左原则，索引一般会失效。
    
    （1）创建复合索引
```
ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)
```
    （2）满足复合索引的最左特性，哪怕只是部分，复合索引生效
```
SELECT * FROM employee WHERE NAME='哪吒编程'
```
    （3）没有出现左边的字段，则不满足最左特性，索引失效
```
SELECT * FROM employee WHERE salary=5000
```
    （4）复合索引全使用，按左侧顺序出现 name,salary，索引生效
```
SELECT * FROM employee WHERE NAME='哪吒编程' AND salary=5000
```
    （5）虽然违背了最左特性，但MySQL执行SQL时会进行优化，底层进行颠倒优化
```
SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒编程'
```
    83、使用联合索引时，为什么需要注意联合索引中的顺序？
    
    联合索引是针对多个字段创建的索引，它包含了这些字段的所有组合，按照指定的顺序排列。例如，一个由字段A、B、C组成的联合索引实际上相当于同时拥有A、AB、ABC三个单独的索引。这意味着，当你执行查询时，如果查询条件包含了这些字段的适当组合，数据库能够利用这个索引来加速查询过程。
    
    最左匹配原则指的是在使用联合索引时，查询条件必须从索引的最左侧开始匹配。
    
    84、MySQL索引底层是什么数据结构？
    
    MySQL索引的底层数据结构主要是B+树和哈希结构。
    
    B+树是更为常用的一种，因为它能够提供更高的查询性能和更好的稳定性。
    
    在某些特定的场景下，MySQL也会使用哈希结构来创建索引，尤其是在需要快速查找唯一值时。但相比之下，B+树提供了更全面的功能，因此在实际应用中使用得更为广泛。
    
    通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
    
    可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：
    
    InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^{3）。也就是说一个深度为3的B+Tree索引可以维护10}3 * 10^3 * 10^3 = 10亿条记录。
    
    实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2_{4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作。
    
    数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。
    
    85、MySQL为什么采用B+树作为底层数据结构？
    
    B+树作为索引的一些优点：
    1. 有序性：B+树的所有键值都是排序的，这对于范围查询非常高效。
    2. 分页查询：B+树的叶子节点包含了所有的关键字信息，以及指向记录的指针，且叶子节点之间是相连的，这使得区间访问变得简单高效。
    3. 减少磁盘I/O操作：由于非叶节点不存储数据，只存储索引，所以每个非叶节点可以拥有更多的子节点，这样树的高度就会降低，从而减少了磁盘I/O操作。
    4. 更好的稳定性：B+树由于其结构的特点，在数据库进行增删改操作时，能够保持较好的稳定性和性能。
    86、B+树在索引中如何处理数据的增删改操作？
    
    B+树是一种自平衡的多路搜索树，它在增删改操作中保持树的平衡性，这是通过节点的分裂和合并来实现的。当一个节点的数据项数量超过预设的最大值时，该节点会分裂为两个节点，以保证树的平衡。相反，当数据项数量过少，相邻的节点可能会合并以维持效率。
    
    B+树的每个非叶子节点只存储键值（key），而实际的数据值（value）则存储在叶子节点中。这样的设计使得非叶子节点可以存储更多的键值，从而降低树的高度，减少查找时的磁盘I/O操作次数。由于磁盘读写通常以页为单位，这种结构能够有效地利用局部性原理，即一次磁盘I/O操作可以读取多个相邻的键值，进一步提高了数据处理的效率。
    
    B+树叶子节点之间通过链表连接，这为范围查询提供了便利。在进行范围查询时，可以从一个叶子节点开始，沿着链表顺序访问其他叶子节点，直到达到查询范围的终点。
    
    B+树通过节点的分裂和合并、非叶节点只存储键值以及叶子节点之间的链表连接等机制，高效地处理数据的增删改操作，同时优化了数据的查询性能。这些特性使得B+树成为数据库索引中非常理想的数据结构。
    
    87、B+树的叶子节点之间是如何连接的？有什么好处？
    
    在B+树中，所有的数据记录都存储在叶子节点上，这些叶子节点通过指针形成了一个链表结构。
    
    这种设计有几个显著的优点：
    1. 范围查询效率：由于叶子节点形成了链表，所以进行范围查询时，可以从链表的一个节点开始，沿着链表顺序访问其他节点，直到达到查询范围的终点。这种方式避免了多次磁盘I/O操作，提高了查询效率。
    2. 数据插入性能：当需要插入新的数据记录时，如果对应的叶子节点已经满了，B+树会分裂该节点，并调整相邻节点的指针，以保持链表的完整性。这个过程虽然可能涉及到节点的分裂和合并，但由于叶子节点之间的链接关系，插入操作不会影响到非叶子节点的结构，因此插入性能较好。
    3. 数据删除性能：同样，当删除某个数据记录时，如果对应的叶子节点数据过少，可能会发生节点的合并，但这也不会影响非叶子节点的结构，因为叶子节点之间的链接关系可以保持不变。
    4. 数据更新性能：由于所有数据都存储在叶子节点上，更新操作可以直接在这些节点上进行，不需要修改非叶子节点中的索引信息。
    88、B+树的非叶子节点之间是如何连接的？有什么好处？
    
    B+树的非叶子节点通过指针与它们的子节点相连接。
    
    在B+树中，非叶子节点作为索引节点，它们存储的是键值对，这些键值对作为索引指向子节点。每个键值对的键是子节点中的最大（或最小）键，值是子节点的地址信息。这种结构使得非叶子节点可以快速定位到叶子节点，从而提高查询效率。
    
    89、用一条 SQL 语句查询出每门课都大于 80 分的学生姓名
```
SELECT student.name
FROM student
WHERE NOT EXISTS (
    SELECT course_name
    FROM score
    WHERE score.student_id = student.id AND score.score <= 80
)
```
```
SELECT student_name  
FROM scores  
GROUP BY student_name  
HAVING MIN(score) > 80;
```
    90、删除除了 id 号不同，其他都相同的学生冗余信息
```
SELECT name, age, class, COUNT(*) as count  
FROM students  
GROUP BY name, age, class  
HAVING COUNT(*) > 1;
```
    91、InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？
    
    MySQL中的InnoDB存储引擎支持四种事务隔离级别，它们分别是：READ UNCOMMITTED（读未提交）、READ COMMITTED（读已提交）、REPEATABLE READ（可重复读）和SERIALIZABLE（串行化）。这四种隔离级别在并发控制上有着不同的表现，主要影响的是事务的可见性和并发性能。
    
    （1）READ UNCOMMITTED（读未提交）
    
    在一个事务中可以读取到另一个未提交事务的修改。
    
    脏读：由于可以读取到其他未提交事务的修改，所以有可能读取到一些“脏”数据，即这些数据可能在后续被其他事务回滚。
    
    （2）READ COMMITTED（读已提交）
    
    只能读取到已经提交的数据。
    
    不可重复读：在一个事务内，多次读取同一数据可能会得到不同的结果，因为其他事务可能在此期间修改了数据。
    
    幻读：由于范围查询的结果集可能受到其他事务的插入或删除影响，因此可能产生幻读现象。
    
    （3）REPEATABLE READ（可重复读）
    
    在一个事务内多次读取同一数据会返回相同的结果。
    
    如何实现不可重复读的？
    
    通过多版本并发控制（MVCC）来实现。InnoDB为每个被读取的行保存一个版本，这样即使在事务进行期间其他事务修改了数据，当前事务也能看到它开始时的数据快照。
    
    幻读：虽然单个行的读取是可重复的，但由于范围查询可能受到其他事务插入或删除数据的影响，所以仍可能产生幻读。
    
    （4）SERIALIZABLE（串行化）
    
    强制事务串行执行，即不允许并发执行。通过对每一行数据加锁来实现。
    
    完全避免了脏读、不可重复读和幻读的问题。
    
    92、如何选择合适的事务隔离级别？
    
    （1）权衡并发性能与数据一致性
    
    如果系统对并发性能要求较高，且可以接受一定程度的数据不一致性（如不可重复读或幻读），那么可以选择较低的隔离级别，如READ COMMITTED。
    
    如果数据一致性至关重要，且系统对性能要求不是非常高，那么可以选择更高的隔离级别，如REPEATABLE READ或SERIALIZABLE。
    
    （2）不同的数据库系统可能对事务隔离级别的支持程度不同，需要根据数据库的需求来选择合适的事务隔离级别。
    
    （3）如果系统采用了读写分离架构，可以考虑根据操作类型使用不同的隔离级别。
    
    93、并发事务是什么？有哪些问题？如何解决？
    
    并发事务是指多个事务在同一时间内对数据库进行访问和修改的情况。并发控制是确保数据库在多用户同时访问时保持一致性和隔离性的重要机制。然而，如果没有适当的控制和管理，并发事务可能会导致以下问题：
    1. 丢失更新：当两个或多个事务试图同时更新同一数据项时，最后的更新可能会覆盖之前的更新，导致数据丢失。
    2. 脏读：一个事务读取了另一个未提交事务的修改结果，如果那个事务之后回滚，那么第一个事务读取的就是“脏”数据。
    3. 不可重复读：在一个事务内，多次读取同一数据集合，由于其他事务的修改导致前后读取的数据不一致。
    4. 幻读：一个事务在执行过程中，其他事务插入了新的数据行，导致原事务查询的结果出现了之前未出现过的“幻影”行。
    为了解决这些问题，数据库管理系统通常采用锁定机制和事务隔离级别来管理并发事务。
    
    MySQL支持多种事务隔离级别，包括：
    1. 读未提交：最低隔离级别，允许脏读、不可重复读和幻读。
    2. 读已提交：防止脏读，但仍然可能出现不可重复读和幻读。
    3. 可重复读：防止脏读和不可重复读，但幻读仍然可能发生。
    4. 串行化：最高隔离级别，防止脏读、不可重复读和幻读，完全隔离事务。
    选择合适的事务隔离级别对于平衡性能和数据一致性至关重要。高隔离级别可以提供更好的数据一致性保障，但可能会降低并发性能。因此，在实际应用中需要根据具体的业务需求和性能要求来选择适当的事务隔离级别。
    
    94、MySQL有哪些锁？
    1. 表级锁：MyISAM默认的锁；
    2. 行级锁：细粒度锁定，只锁定受影响的行而不是整张表，提高了并发处理能力，常用于InnoDB这类事务性存储引擎。
    3. 页级锁：介于表级锁和行级锁之间，锁定数据页而不是整张表或单独的行。
    根据锁的使用方式还可以分为乐观锁和悲观锁。乐观锁通常在数据读取时不加锁，而在数据更新时再进行检查和加锁；而悲观锁则是在读取时就加锁，以防其他事务修改数据。
    
    95、MySQL页级锁是什么？有哪些应用场景？
    
    MySQL中的页级锁是一种基于数据页的锁定机制，主要适用于大事务和数据库分区的场景。
    
    页级锁是MySQL中的一种重要锁机制，它介于行级锁和表级锁之间，以数据页为单位进行加锁。数据页是数据库中存储数据的最小单位，通常大小为16KB。当多个事务同时对同一数据页进行操作时，页级锁能够保证数据的一致性和并发性。
    
    页级锁的优势在于，当访问的数据量较大时，它可以减少锁的数量，从而提高并发性能。这在大事务的场景下尤为明显，即当一个事务需要修改大量数据时，使用页级锁可以有效地提高性能。此外，在数据库采用分区表的情况下，页级锁同样适用。分区表允许多个分区同时进行读写操作，使用页级锁可以进一步提高并发性能。
    
    96、InnoDb中有哪些锁？有什么特点？有哪些应用场景？
    
    （1）共享锁和排他锁
    
    共享锁：当事务要读取某行记录时，需要获得该行的共享锁。多个事务可以同时持有同一行的共享锁，但不允许其他事务对该行加排他锁。
    
    排他锁：当事务要修改某行记录时，需要获得该行的排他锁。排他锁是独占的，即同一时间只能有一个事务持有某行的排他锁，其他事务既不能获得该行的共享锁也不能获得排他锁。
    
    （2）意向锁
    
    一种表级锁，用于在将来某个时刻，事务可能要在某行上加上共享锁或排他锁时声明的一个意向。它不与行级锁冲突，而是用于在加行级锁之前，先判断表级的意向锁情况，从而避免锁冲突。
    
    （3）间隙锁
    
    锁定一个范围，但不包括记录本身。这主要用于防止幻读（Phantom Reads）。
    
    （4）自增锁
    
    自增锁是InnoDB存储引擎中一种特殊的锁，它用于保证在插入新记录时，自增主键的值是唯一且连续的。
    
    InnoDB的锁机制使其适用于多种应用场景：
    1. 事务性应用：由于支持事务和具有ACID特性，适用于要求高数据一致性和完整性的事务性应用，如金融系统、订单处理系统等。
    2. 高并发读写场景：行级锁定机制和多版本并发控制使其适用于高并发读写场景，如电子商务网站、社交媒体应用等。
    3. 需要外键约束的应用：如果应用程序需要使用外键约束来维护关联表之间的数据一致性，InnoDB是一个合适的选择。
    4. 需要热备份和灾难恢复的应用：InnoDB的热备份和恢复特性使其适用于要求快速备份和恢复的应用场景。
    5. 大型数据集：InnoDB的数据缓存机制适用于大型数据集，通过缓存数据提高了读取性能。
    97、MySQL中数据页和数据行有什么区别？
    
    数据页是数据库存储和管理数据的基本单元，而数据行则是实际存储数据记录的单元。
    
    （1）数据页
    
    数据页是MySQL中磁盘与内存交互的基本单位，通常大小为16KB。这种设计可以减少内存与磁盘的交互次数，从而提升数据库的性能。数据页内部包含多个数据行，它们在物理上是连续存储的。每个数据页都有页头信息，包括页的元数据、控制信息等。此外，数据页之间通过指针相连，形成了一个双向链表的结构。
    
    （2）数据行
    
    数据行是数据库中实际存储数据记录的单位。每条数据行包含了一行数据的所有列值。在InnoDB存储引擎中，数据行被组织成页的形式存储在磁盘上。当执行查询时，相关的数据行会被加载到内存中进行处理。
    
    98、MySQL中数据页的作用是什么？
    
    （1）减少磁盘I/O操作
    
    由于磁盘的读写速度远慢于内存，频繁的磁盘I/O会严重影响数据库性能。通过使用数据页，MySQL可以一次性读取或写入多个数据行，这样可以减少磁盘与内存之间的交互次数。
    
    （2）缓存设计思想
    
    数据页的设计体现了典型的缓存设计思想。从时间维度考虑，正在使用的数据可能在未来短时间内再次被访问；从空间维度考虑，正在使用的数据附近的数据也很可能会被用到。因此，将相关数据组织在一起可以提高缓存的效率。
    
    （3）数据页链表结构
    
    数据页之间通过指针相连，形成了一个双向链表的结构。这种设计方便了数据的管理和查找，因为可以通过链表快速定位到所需的数据页。
    
    99、sql语句执行慢，如何优化？
    1. 分析查询计划：使用EXPLAIN命令分析查询计划，了解MySQL如何执行SQL语句。根据结果调整索引或修改查询逻辑。
    2. 添加或优化索引：为经常用于搜索和排序的列创建索引，提高查询效率。同时，定期分析表数据，更新统计信息，以便优化器更好地选择索引。
    3. 选择合适的字段类型：使用合适的数据类型可以减小存储空间和提高查询效率。
    4. 优化子查询：将子查询转换为等价的JOIN操作，通常JOIN操作比子查询效率更高。
    5. 限制查询结果集：使用LIMIT语句限制返回的结果数量，减少数据传输量。
    6. 避免在WHERE子句中使用函数或表达式：这会阻止MySQL使用索引，降低查询效率。如果必须使用，考虑将计算结果存储为额外的列。
    7. 减少JOIN操作：尽量减少多表JOIN操作，特别是在大表上。如果必须使用，确保JOIN的字段上有合适的索引。
    8. 使用临时表：对于复杂的查询，可以考虑将中间结果存储到临时表中，分步执行查询。
    9. 避免使用LIKE操作符：尽量避免在开头使用通配符的LIKE查询，因为它无法使用索引。
    10. 批量操作：尽量使用批量插入、更新和删除操作，减少事务提交次数。
    11. 调整MySQL配置：根据服务器硬件资源和业务需求，调整MySQL的配置参数，如缓冲池大小、表缓存等。
    12. 分区表：对于大表，可以考虑使用分区表，将数据分散到不同的物理文件中，提高查询效率。
    13. 定期维护表：使用OPTIMIZE TABLE命令整理表碎片，提高查询效率。
    14. 使用概要统计：为经常用于聚合查询的列创建概要统计，提高聚合查询的效率。
    100、单表记录数过大时，如何优化？
    
    （1）表结构设计优化
    - 选择合适的数据类型：确保列的数据类型尽可能小且合适；
    - 规范化设计：合理拆分表，避免数据冗余，减少不必要的JOIN操作
      （2）索引优化
      - 创建有效的索引：针对查询中经常作为条件的列创建索引，以提高查询速度。
      - 避免过度索引：过多的索引会占用额外的存储空间，并可能降低写操作的性能。
      - 使用复合索引：当查询条件涉及多个列时，考虑使用复合索引。
      - 定期维护索引：重建或重新组织索引，以减少碎片并提高性能。
        （3）查询优化
        
        优化SQL语句：避免使用SELECT *，只选择需要的列；使用连接（JOIN）代替子查询；优化WHERE子句等。
        减少全表扫描：确保查询能够利用索引，避免不必要的全表扫描。
        （4）分区技术
        
        使用分区表：将大表拆分成多个逻辑上独立、物理上相关的分区，提高查询和管理效率。
        选择合适的分区键：根据查询和维护需求选择合适的分区键。
        （5）数据库配置和硬件升级
        
        调整内存参数：增加数据库缓存区大小，提高缓存命中率。
        优化I/O性能：使用高速磁盘、RAID阵列或SSD来提高I/O性能。
        增加CPU和内存：根据数据库负载情况，适当增加服务器硬件资源。
        （6）归档旧数据
        
        将不再频繁访问的旧数据归档到历史表或备份系统中。
        
        101、举例说明单表如何优化？
        
        （1）删除student表中的联合索引。
        
        （2）添加索引
        
        alter table student add index student_union_index(name,age,sex);
        
        优化一点，但效果不是很好，因为type是index类型，extra中依然存在using where。
        
        （3）更改索引顺序
        
        因为sql的编写过程
        
        select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
        
        解析过程
        
        from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
        
        因此我怀疑是联合索引建的顺序问题，导致触发索引的效果不好。are you sure？试一下就知道了。
        
        alter table student add index student_union_index2(age,sex,name);
        
        删除旧的不用的索引：
        
        drop index student_union_index on student
        
        索引改名
        
        ALTER TABLE student RENAME INDEX student_union_index2 TO student_union_index
        
        更改索引顺序之后，发现type级别发生了变化，由index变为了range。
        
        range：只检索给定范围的行，使用一个索引来选择行。
        
        备注：in会导致索引失效，所以触发using where，进而导致回表查询。
        
        （4）去掉in
        
        ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；
        
        index 提升为ref了，优化到此结束。
        
        （5）小结
        
        保持索引的定义和使用顺序一致性；
        索引需要逐步优化，不要总想着一口吃成胖子；
        将含in的范围查询，放到where条件的最后，防止索引失效；
        
        102、如何优化order by语句？
        
        using filesort有两种算法：双路排序、双路排序（根据IO的次数）
        
        MySQL4.1之前，默认使用双路排序；双路：扫描两次磁盘（①从磁盘读取排序字段，对排序字段进行排序；②获取其它字段）。
        
        MySQL4.1之后，默认使用单路排序；单路：只读取一次（全部字段），在buffer中进行排序。但单路排序会有一定的隐患（不一定真的是只有一次IO，有可能多次IO）。
        
        注意：单路排序会比双路排序占用更多的buffer。
        
        单路排序时，如果数据量较大，可以调大buffer的容量大小。
        
        set max_length_for_sort_data = 1024;单位是字节byte。
        
        如果max_length_for_sort_data值太低，MySQL底层会自动将单路切换到双路。
        
        太低指的是列的总大小超过了max_length_for_sort_data定义的字节数。
        
        提高order by查询的策略：
        
        选择使用单路或双路，调整buffer的容量大小；
        避免select * from student;（① MySQL底层需要对*进行翻译，消耗性能；② *永远不会触发索引覆盖 using index）；
        符合索引不要跨列使用，避免using filesort；
        保证全部的排序字段，排序的一致性（都是升序或降序）；
        
        103、联表查询如何优化？
        
        （1）左连接查询
        
        explain select s.name,t.name from student s left join teacher t on s.teacher_id = t.id where t.course = '数学'
        
        上一篇介绍过，联合查询时，小表驱动大表。小表也称为驱动表。其实就相当于双重for循环，小表就是外循环，第二张表（大表）就是内循环。
        
        虽然最终的循环结果都是一样的，都是循环一样的次数，但是对于双重循环来说，一般建议将数据量小的循环放外层，数据量大的放内层，这是编程语言的优化原则。
        
        再次代码测试：
        
        student数据：四条
        
        teacher数据：三条
        
        按照理论分析，teacher应该为驱动表。
        
        sql语句应该改为：
        
        explain select teacher.name,student.name from teacher left join student on teacher.id = student.id where teacher.course = '数学'
        
        优化一般是需要索引的，那么此时，索引应该怎么加呢？往哪个表上加索引？
        
        索引的基本理念是：索引要建在经常使用的字段上。
        
        由on teacher.id = student.id可知，teacher表的id字段使用较为频繁。
        
        left join on，一般给左表加索引；因为是驱动表嘛。
        
        alter table teacher add index teacher_index(id); alter table teacher add index teacher_course(course);
        
        备注：如果extra中出现using join buffer，表明mysql底层觉得sql写的太差了，mysql加了个缓存，进行优化了。
        
        （3）小结
        
        小表驱动大表
        索引建立在经常查询的字段上
        sql优化，是一种概率层面的优化，是否实际使用了我们的优化，需要通过explain推测。
        
        104、衡量查询开销的几个重要指标
        
        （1）响应时间
        
        响应时间可以分为服务时间和排序时间。
        
        服务时间指数据库处理这个查询真正花费的时间；
        排队时间指服务器因为等待某些资源而没有真正执行查询的时间，比如等待IO操作、等待行锁。
        （2）扫描的行数和返回的行数
        
        较短的行的访问速度更快，内存中的行比磁盘中的行的访问速度要快得多。
        
        理想情况下扫描的行数和返回的行数是相同的。但这种情况并不多见，比如关联查询的时候，服务器必须扫描更多的行才能得到结果，因此，越多的表关联，性能越低。
        
        （3）扫描的行数和访问类型
        
        MySQL可以通过多种方式查询并返回结果集，速度从慢到快，扫描的行数由多到少，依次为全表扫描、索引扫描、范围扫描、唯一索引扫描、常数引用。
        
        最常用的优化方式是为查询增加一个合适的索引，索引可以让MySQL以最高效、扫描行数最少的方式找到需要的记录。
        
        （4）一般可以通过explain的Extra列查看查询的优劣
        
        一般MySQL能够使用以下三种方式应用where条件，从好到坏依次为：
        
        在索引中使用where条件过滤不匹配的记录，这是在存储引擎层完成的；
        使用索引覆盖扫描，也就是Extra中出现Using index，直接从索引中过滤不需要的记录并返回命中的结果，这是在MySQL服务器层完成的，无须再回表查询记录；
        Extra中出现Using where，这是在MySQL服务器层完成的，MySQL需要先从数据表读取记录，然后过滤。
        
        Extra中出现Using where时，可以通过如下方式优化：
        
        使用索引覆盖扫描，把所有需要的列都放到索引中，这样就不用回表查询了；
        改变表结构，比如使用汇总表；
        重写sql，让MySQL优化器能够以更优化的方式执行这个sql；
        
        105、如何避免在WHERE子句中使用函数或表达式导致的查询效率降低？
        
        尽量避免在WHERE子句中使用函数或表达式，因为这样做会阻止MySQL使用索引。如果必须使用函数或表达式，可以考虑将计算结果存储为额外的列，然后在WHERE子句中直接使用该列进行条件判断。
        
        106、什么是数据库连接池?为什么需要数据库连接池?
        
        数据库连接池是一种管理数据库连接的技术，它允许应用程序重复使用现有的数据库连接，避免重新创建连接的开销。
        
        数据库连接池的核心概念在于提供一个存储和管理数据库连接的容器。当程序需要执行数据库操作时，可以直接从连接池中获取一个已经存在的连接，而不是每次都去创建一个新的连接。使用完毕后，这个连接不会立即关闭，而是被归还到连接池中，以供后续重用。这种机制类似于线程池，通过复用资源来提高效率和性能。
        
        107、分库分表之后，id 主键如何处理？
        
        UUID：UUID可以生成全局唯一的ID，缺点是它通常较长，会占用更多的存储空间，并且不易排序。
        数据库自增ID：配置不同库或表的起始值和增长步长来避免冲突。
        利⽤ redis ⽣成 id：性能⽐᫾好，灵活⽅便，不依赖于数据库。
        使用全局唯一的分布式主键（如雪花算法）：可以引入全局唯一的分布式主键生成算法，如雪花算法（Snowflake），确保生成的主键在整个系统中唯一。这样在分库分表后，不同库表的主键也能保持唯一性。
        分库分表后主键拆分：将原始主键拆分成两个部分，一个部分表示分库的编号，另一个部分表示分表的编号，这样可以保证每个库表的主键是唯一的。
        
        108、分库分表是什么？解决了哪些问题？
        
        分库分表是一种数据库架构设计方式，旨在解决大规模数据存储和处理的挑战。这种技术策略通过将数据分散到多个数据库实例（分库）和表格（分表）中，提高了数据库的性能、可扩展性和负载均衡。
        
        分库分表主要解决了以下问题：
        
        单个数据库的容量和性能瓶颈
        系统本身的IO、CPU瓶颈
        
        109、分库分表可能会遇到哪些问题？
        
        （1）联表查询问题
        
        分库分表后，数据分布在不同的数据库或表中，传统的JOIN操作不再适用。
        
        （2）事务问题
        
        分库分表引入了分布式事务的概念，这比传统的本地事务更加复杂。
        
        （3）排序和分页问题
        
        当数据分布在多个数据库或表中时，执行排序和分页查询变得更加困难。需要考虑如何有效地合并来自不同数据源的结果集，并确保结果的正确性和一致性。
        
        （4）数据一致性问题
        
        分库分表可能导致数据在不同数据库或表之间的同步延迟，从而影响数据的实时性和一致性。
        
        110、高并发时，如何修改同一行数据？
        
        在高并发场景下，确保同一行数据的安全修改通常采用悲观锁或乐观锁这两种策略。
        
        悲观锁，通过SELECT … FOR UPDATE语句在事务中锁定需要修改的行，这样其他事务在该行被解锁之前无法对其进行修改。
        
        乐观锁，在数据行中增加一个版本号字段，每次更新时版本号递增。当更新发生时，会检查版本号是否一致，如果不一致，则说明有其他事务已经修改了该行，当前事务的更新操作将失败。
        
        111、说说sql语句执行过程
        
        （1）编写过程
        
        select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
        
        （2）解析过程
        
        from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
        
        112、谈谈你对explain执行计划的理解？
        
        Explain执行计划是MySQL中用于分析SQL查询语句性能的一种工具。通过使用EXPLAIN关键字，我们可以查看查询语句在执行时的详细信息，包括表扫描方式、连接类型、索引使用情况等。这有助于我们找出查询语句中的性能瓶颈，从而优化SQL语句以提高查询效率。
        
        在Explain执行计划中，主要有以下几个关键部分：
        
        id：表示查询的标识符，相同的id表示在同一级别的查询。
        select_type：表示查询的类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
        table：表示查询涉及到的表。
        type：表示MySQL在表中找到所需行的方式，常见的类型有ALL（全表扫描）、index（全索引扫描）、range（范围扫描）等。一般来说，type的值越小，查询效率越高。
        possible_keys：表示可能使用的索引。
        key：表示实际使用的索引。
        key_len：表示使用的索引的长度。
        ref：表示索引的参考列。
        rows：表示MySQL预计需要扫描的行数。
        Extra：表示额外的信息，如Using index（使用覆盖索引）、Using filesort（使用文件排序）等。
        
        通过分析Explain执行计划的这些信息，我们可以针对性地优化SQL语句，例如增加或调整索引、改变查询条件等，从而提高查询性能。
        
        113、简述一下如何使用explain？
        
        （1）未触发索引
        
        （2）触发索引
        
        explain中第一行出现的表是驱动表。
        
        指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]
        未指定联接条件时，行数少的表为[驱动表]
        对驱动表直接进行排序就会触发索引，对非驱动表进行排序不会触发索引。
        
        114、如何根据Explain执行计划中的rows和key_len信息优化MySQL查询？
        
        在MySQL中，可以根据 EXPLAIN 执行计划中的 rows 和 key_len 信息来优化查询性能。这些信息可以提供有关查询执行的重要线索，帮助识别潜在的性能问题并进行优化。
        
        （1）优化 rows（行数）参数
        
        减少扫描行数：如果 rows 值较大，说明查询需要扫描的行数很多，可能需要考虑优化查询，比如添加索引、优化查询条件、调整表结构等，来减少需要扫描的行数。
        
        优化索引使用：确保查询使用了合适的索引，避免全表扫描，从而减少扫描的行数。
        
        （2）优化 key_len（索引键长度）参数
        
        理解索引键长度：key_len 表示索引键的长度，可以帮助了解MySQL使用的索引情况。通常，较小的 key_len 表示索引效率较高。
        
        调整索引设计：如果 key_len 较大，可能意味着使用的索引不够有效，可能需要考虑优化索引设计，添加合适的索引以减少索引键长度，提高查询效率。
        
        115、千万级数据的表，增删改查很慢，如何优化？
        
        （1）索引优化
        
        创建合适的索引是提高查询速度的关键。
        
        （2）分库分表
        
        分库分表可以显著减少单个表的数据量，提高查询和写入的速度。
        
        （3）读写分离
        
        通过将读操作和写操作分配给不同的数据库服务器来提高并发处理能力。读服务器可以处理查询请求，而写服务器则负责数据的插入、更新和删除。
        
        （4）缓存优化
        
        使用Redis缓存技术来缓存热点数据，以减少对数据库的直接访问。
        
        （5）SQL优化
        
        仔细审查和优化SQL语句，避免使用子查询、临时表等可能导致性能下降的操作。同时，可以使用批量操作来提高写入效率，例如批量插入、批量更新等。
        
        116、开发中，你都用过哪些分库分表数据库中间件？解决了哪些问题？
        
        （1）ShardingSphere
        
        当当网开源的一个数据中间件，主要用于数据分片。通过使用Sharding-JDBC，我能够更灵活地管理数据库，将大量数据分布到多个数据库或表中，从而提高了查询性能和系统稳定性。
        
        （2）MyCat
        
        一个开源的、基于MySQL协议开发的数据库中间件，主要用于分片、读写分离、负载均衡和故障切换等功能。在处理大规模数据时，MyCat帮助我有效地将多个MySQL数据库服务器组合成一个逻辑数据库，从而提高了系统的处理能力和可扩展性。
        
        117、MySQL的主从延迟怎么解决？
        
        （1）先查看主从延迟情况
        
        通过在从库执行SHOW SLAVE STATUS命令，查看Seconds_Behind_Master的值来判断主从之间的延迟情况。如果该值为0，则表示主备库之间无延迟。
        
        比较主从库的文件点位，包括Master_Log_File、Read_Master_Log_Pos、Relay_Master_Log_File和Exec_Master_Log_Pos等参数，确保它们之间是同步的。
        
        （2）优化SQL语句
        
        分析和优化主服务器上的慢查询，减少大事务的产生。
        使用批量插入、减少锁的竞争等策略来优化写入性能。
        
        （3）使用多线程复制
        
        MySQL 5.6版本引入了多线程复制，可以通过设置slave_parallel_workers参数来提高从库的复制速度。
        
        （4）读写分离
        
        在架构层面实施读写分离，将读操作分散到从库，减轻主库的压力。
        
        （5）使用半同步复制
        
        半同步复制确保至少一个从服务器已经接收并写入中继日志后，主服务器才提交事务。这增加了数据的一致性，但可能会稍微降低性能。
        
        118、MySQL如何做性能分析，有哪些常用命令？
        
        （1）explain
        
        通过解析查询语句的执行计划，了解MySQL如何执行查询，从而找到查询语句的性能瓶颈。
        
        （2）profiling
        
        SET profiling=1;：开启性能分析功能。
        SELECT @@profiling;：确认性能分析是否开启。
        SHOW PROFILES;：查询刚刚被执行的语句的Query_ID。
        SHOW PROFILE FOR QUERY {Query_ID};：分析指定SQL的性能。
        SET profiling=0;：关闭性能分析功能。
        （3）SHOW [SESSION|GLOBAL] STATUS
        
        SHOW [SESSION|GLOBAL] STATUS;：查看服务器状态信息，包括INSERT、UPDATE、DELETE、SELECT的执行频率。
        
        （4）慢查询日志
        
        SHOW VARIABLES LIKE ‘slow_query_log’;：查看慢查询日志的状态。
        SHOW FULL PROCESSLIST;：查看当前运行的查询，找出可能的慢查询。
        （5）工具分析法
        
        mysqldumpslow：分析慢查询日志，找出返回记录最多或平均访问次数最多的SQL。
        pt-query-digest：清晰地分析慢查询，提供详细的报告。
        119、说说MySQL的基础架构？
        
        （1）连接层
        
        负责与MySQL客户端之间的通信，提供如连接处理、身份验证等功能。当客户端发送SQL查询和命令到MySQL服务器时，连接层会处理这些请求，验证用户的身份，并分配一个线程专门与这个客户端进行交互。
        
        （2）核心服务层
        
        处理权限判断、SQL解析、行计划优化、查询缓存的处理以及所有内置的函数（如日期、时间、数学运算、加密等）。此外，存储过程、触发器、视图等功能也在这一层完成。
        
        服务层包括：
        
        连接器：管理客户端连接和用户身份认证。它使用TCP协议来建立与客户端的连接，并负责验证用户的身份。
        查询缓存：用于缓存执行过的查询结果，以提高查询效率。如果相同的查询再次发生，可以直接从缓存中获取结果，而不必重新执行查询。
        分析器：对客户端传来的SQL语句进行分析，解析出语法结构。
        优化器：对解析后的SQL语句进行优化，选择最有效的执行计划。
        执行器：根据优化器选择的执行计划，执行SQL语句并返回结果。
        
        （3）存储引擎层
        
        负责存储和获取所有存储在MySQL中的数据。
        
        120、Mysql 的binlog 有几种录入格式？有什么区别？
        
        Statement格式：每一条会修改数据的SQL语句都会记录在binlog中。这种方式简洁且可读性好，但可能会因为某些函数或存储引擎而导致不一致性。
        Row格式：记录每一行数据变更前后的具体值。它保证了最高的准确性和一致性，但生成的日志量较大，增加了存储和网络传输的开销。
        Mixed格式：是前两种格式的混合，MySQL会根据具体情况选择使用哪种格式。在对MyISAM表进行更改时，使用Statement格式；对InnoDB表进行更改时，使用Row格式。这种格式既保证了可读性，又能保证数据一致性。
        
        121、Oracle分区表是什么？有哪些优缺点？
        
        分区是指表和索引可以被分成若干个部分，它们拥有相同的逻辑属性和数据结构。所有分区的字段和索引都是一样的。
        
        分区表是将表数据分为若干个可以被单独管理的片，每个片就是一个分区，分一个分区都可以拥有自己的物理属性，比如表空间、事务槽、存储参数、最小区段数等，通过建分区语句指定，提升可用性和存储效率。
        
        每个分区可以被单独管理，降低管理成本和备份成本，提高容错率，避免“一荣既荣，一损俱损”的问题。
        
        （1）优点
        
        可以通过指定分区提高查询性能；
        提高容错率，避免“一荣既荣，一损俱损”的问题；
        降低管理成本；
        降低备份成本；
        
        （2）缺点
        
        普通表和分区表不能直接转换，可以通过数据迁移，再重命名的方式实现，需要重建约束、索引，在创建表时可以添加关键字“parallel compress”并行执行，提高效率，下面会通过SQL实例介绍。
        
        122、Oracle何时分区？
        
        单表的数据量如果过大，会影响SQL的读写性能，我们可以通过分库分表的方式解决表性能的问题，Oracle的分区表是将一张大表在物理上分成几个较小的表，从逻辑上看仍然是一张完整的表。这样，每次DML操作只考虑其中一张分区表即可。
        
        那么，临界点是多少呢？
        
        数量量超过500万且空间占用超过2GB的时候必须分区；
        数量量高于100万，低于500万时建议分区；
        
        注意：单个分区的数据可以超过500万，但存储空间不建议超过2GB。
        
        123、说说分区表的分类？
        
        （1）范围分区
        
        将数据基于范围映射到每一个分区，这个范围是由创建分区表时指定的分区键决定。
        
        一般选取id或者时间作为范围分区的分区键。
        
        （2）列表分区
        
        列表分区适用于一个字段只有固定的几个值，比如类型、月份、课程等。
        
        （3）哈希分区
        
        范围分区和列表分区都是使用某一个字段进行分区，此字段的分区度大才行，但也会产生诸多问题，比如上述的按技术列表分区，现阶段，Java开发人员明显高于C，此时就会导致分区不均匀的问题。
        
        此时，hash分区闪亮登场，hash分区的好处是让分区更均匀一些。
        
        （4）范围列表组合分区
        
        124、分区表会带来哪些问题？
        
        （1）分区列和索引列不匹配
        
        如果定义的分区列和索引列不匹配，会导致查询无法进行分区过滤。
        
        假设在列id上定义了索引，在列create_time上进行了分区，因为每个分区都有其独立的索引，所以扫描列create_time上的索引就需要扫描每一个分区内对应的索引。如果每个分区内对应索引的非叶子节点都在内存中，那么扫描的速度还是可以接受的。
        
        （2）选择分区的成本可能很高
        
        某一行属于哪个分区？这些符合条件的行在哪个分区？服务器需要扫描所有的分区来找到正确的分区，这样的线性查找的效率并不高，随着分区数的增长，成本会越来越高。
        
        （3）打开并锁住所有底层表的成本可能会很高
        
        当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的一个很大的开销。这个操作在分区过滤之前，所以无法通过分区过滤降低此开销，并且此开销也和分区类型无关，会影响所有的查询。这一点对一些本身查询速度非常快的查询会带来明显的额外开销。可以通过批量操作的方式来降低单个操作的此类开销。
        
        综上所述，分区会有很多隐患和问题。所以目前在进行分区的时候会加入一些限制。
        
        所有分区都必须使用相同的存储引擎；
        某些存储引擎不支持分区；
        对于MyISAM的分区表，不能使用LOAD INDEX INTO CACHE；
        
        3万字80道Java经典面试题总结（2024修订版）- Java基础篇
        
        2 万字 42 道Java经典面试题总结（2024修订版）- Java集合篇
        
        4 万字 102 道Java经典面试题总结（2024修订版）- 多线程篇
        
        10万字208道Java经典面试题总结（2024修订版）- SSM篇
        
        🏆文章收录于：100天精通Java从入门到就业
        
        全网最细Java零基础手把手入门教程，系列课程包括：Java基础、Java8新特性、Java集合、高并发、性能优化等，适合零基础和进阶提升的同学。
        
        🏆哪吒多年工作总结：Java学习路线总结，搬砖工逆袭Java架构师。
        
        华为OD机试 2023B卷题库疯狂收录中，刷题点这里
        
        刷的越多，抽中的概率越大，每一题都有详细的答题思路、详细的代码注释、样例测试，发现新题目，随时更新，全天CSDN在线答疑。
        点击下方名片，回复1024，获取《10万字208道Java经典面试题总结(2024修订版).pdf 》
        
        网络服务商电话注册公司网址怎样制作网页链接赣州人才网招聘找工作怎么注册自己的网站重庆专业网站建设公司