[八股大全]Mysql数据库篇
1.Mysql基础
1.什么是关系型数据库？
关系型数据库（RDB，Relational Database）就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。
关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行就存放着一条数据（比如一个用户的信息）。
大部分关系型数据库都支持事务的四大特性(ACID)。
2.关系型和非关系型数据库的区别？
关系型数据库的优点
- 容易理解，因为它采用了关系模型来组织数据。
- 完全支持事务的ACID
- 可以保持数据的一致性。
- 数据更新的开销比较小。
- 支持复杂查询（带 where 子句的查询）
非关系型数据库（NOSQL）的优点
- 无需经过 SQL 层的解析，读写效率高。
- 基于键值对，读写性能很高，易于扩展
- 可以支持多种类型数据的存储，如图片，文档等等。
- 扩展（可分为内存性数据库以及文档型数据库，比如 Redis，MongoDB，HBase 等，适合场景：数据量大高可用的日志系统/地理位置存储系统）。
2.什么是MySQL
MySQL是一个关系型数据库，它采用表的形式来存储数据,有表结构（行和列）。行代表每一行数据，列代表该行中的每个值。列上的值是有数据类型的，比如：整数、字符串、日期等等。
3.数据库的三大范式
第一范式1NF(保证原子性)
确保数据库表字段的原子性。如果一个表中的“地址”列包含了省、市和具体地址，这不符合1NF，因为“地址”可以继续拆分成更细致的部分。按1NF要求，应该将“地址”拆分为“省份”、“城市”和“具体地址”等列。
第二范式2NF(消除部分依赖)
首先要满足第一范式，另外包含两部分内容，一是表必须有一个主键；二是非主键列必须完全依赖于主键，而不能只依赖于主键的一部分。例如，在学生成绩表中，如果学号和课程编号组成联合主键，那么成绩这一非主键列应同时依赖于学号和课程编号，而不是仅依赖于其中之一。
第三范式3NF（消除传递依赖）
首先要满足第二范式，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。例如，如果一个表中包含学生信息和院校信息，其中学生所在院校依赖于学号，而院校地址和电话又依赖于所在院校，这就形成了传递依赖。按照3NF的要求，需要将表拆分为学生表和院校表，使每个属性都直接依赖于主键。
2NF和3NF的区别？
- 2NF解决主键部分依赖问题
- 3NF解决主键传递依赖问题
4.SQL语句完整的执行顺序
FROM 子句组装来自不同数据源的数据；WHERE 子句基于指定的条件对记录行进行筛选；GROUP BY 子句将数据划分为多个分组；使用聚集函数进行计算；使用 HAVING 子句筛选分组；计算所有的表达式；SELECT 的字段；使用 ORDER BY 对结果集进行排序。
5.having和where区别？
- 二者作用的对象不同，
where子句作用于表和视图，having作用于组。 where在数据分组前进行过滤，having在数据分组后进行过滤。- 另一方面，
HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段 - 先执行where,然后group by 然后having
5.Mysql的架构/组成
MySQL主要分为 Server 层和存储引擎层：
- Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
- 存储引擎： 实现数据的存储和提取，不同的存储引擎如InnoDB、MyISAM通过这些API与Server层交互。存储引擎层提供数据存储和提取的具体实现，而Server层通过API调用这些功能，使得不同存储引擎能够与Server层无缝对接。
Server 层基本组件
- 连接器： 负责与客户端建立连接，并管理连接的权限验证和维持。当客户端连接 MySQL 时，server层会对其进行身份认证和权限校验。
- 查询缓存: 执行查询语句的时候，会先查询缓存，先校验这个 select语句 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。
- 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析。首先通过词法分析识别出各个字符串的意义，然后进行语法分析，检查语句是否符合语法结构。如有错误，会提示错误位置。
- 优化器： 优化器对查询进行优化，选择最有效的查询路径，包括索引的选择和表的读取顺序，生成执行计划，包括选择合适的索引、表扫描顺序等。
- 执行器： 根据优化后的执行计划调用存储引擎的API来实际执行SQL语句。在执行前会再次检查权限，确认当前用户有权执行该操作，然后通过存储引擎接口访问数据。
6.sql语句在Mysql中的执行过程
其实我们的 SQL 可以分为两种，一种是查询，一种是更新（增加，修改，删除）
1.查询语句执行流程
大概有4步：
- 先连接器检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，会先查询缓存，以这条 SQL 语句为 key 在内存中查询是否有结果，如果有直接返回缓存结果，如果没有，执行下一步。
- 通过分析器进行词法分析，提取 SQL 语句的关键元素。然后判断这个 SQL 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
- 接下来就是优化器进行确定执行方案，优化器根据自己的优化算法进行选择执行效率最好的一个方案
- 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。
2.更新语句执行流程
举个例子，更新语句是这样的：
update user set name = 'name' where id = 1;
1.先查询到 id 为1的记录，有缓存会使用缓存。
2.拿到查询结果，将 name 更新为张三，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录redo log，此时redo log进入 准备状态。
3.执行器收到通知后记录binlog，然后调用引擎接口，提交redo log为提交状态。
4.更新完成。
为什么记录完redo log，不直接提交，而是先进入准备状态？
假设先写redo log直接提交，然后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
7.inner join,left join和right join的区别
类型 作用1 Join(Inner Join)内连接 查出两表完全匹配的部分。（交集）2 Left Join左连接 返回左表所有的行，右表返回匹配行，不匹配的返回NULL3 Right Join右连接 返回由表所有的行，左表返回匹配行，不匹配的返回NULL4 Full Join全连接 只要其中一个表存在匹配，则返回行
8.MySQL 是如何保证数据不丢失的？
- 只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据
- 在恢复数据时，redolog 状态为 commit 则说明 binlog 也成功，直接恢复数据；如果 redolog 是 prepare，则需要查询对应的 binlog事务是否成功，决定是回滚还是执行。
9.为什么 MySQL 自增主键 ID 不连续？
MySQL 自增主键 ID 不连续的原因可能有以下几点：
- 插入数据失败：当插入数据时，如果某些原因导致插入失败，那么自增 ID 会跳过这个值，导致不连续。
- 删除数据：当删除某条记录时，自增 ID 不会回退，因此会导致不连续。
- 事务回滚：在事务中插入数据时，如果发生错误导致事务回滚，那么已经分配的自增 ID 也会被跳过，导致不连续。
- 数据库备份和恢复：在进行数据库备份和恢复时，可能会涉及到数据的重新分配，导致自增 ID 不连续。
- 分布式系统：在分布式系统中，多个节点可能同时分配自增 ID，虽然每个节点内部是连续的，但在整个系统中可能会出现不连续的情况。
- 手动设置自增 ID：如果在插入数据时手动设置了自增 ID，可能会导致不连续。
为了避免自增 ID 不连续的问题，可以采取以下措施：
- 使用 UUID 作为主键，而不是自增 ID。
- 在不影响性能的前提下，尽量减少事务回滚和数据删除操作。
- 在分布式系统中，使用全局唯一 ID 生成策略，如雪花算法（Snowflake）等。
10.orderby 排序内部原理
在 MySQL 中，ORDER BY 用于对查询结果进行排序。它有两种排序方式：
- 全字段排序：按照查询语句中 ORDER BY 后面的字段进行排序。这种方式比较直观，直接按照指定的字段排序即可。
- RowNumber 排序：当 ORDER BY 后面没有指定具体的字段时，MySQL 会采用 RowNumber 排序方式。这种方式是 MySQL 内部实现的排序机制，具体实现过程如下：首先，MySQL 会根据表的大小，分配一块内存空间（称为 Sort Buffer），用于存放需要排序的数据。然后，MySQL 会将表中的数据按照 RowNumber 的顺序，依次读取到 Sort Buffer 中。这个过程是通过多路归并排序算法实现的，即将表中的数据分成多个小的子集，每个子集内部按照 RowNumber 排序，然后再将子集合并成一个有序的大集合。最后，MySQL 会按照 Sort Buffer 中的数据顺序，依次输出结果。
需要注意的是，由于 Sort Buffer 的大小是有限的，因此在处理大表时，RowNumber 排序可能会导致内存不足的问题。此时，可以考虑使用外部排序的方式，即通过磁盘文件进行排序操作。
11.什么是sql注入？怎么解决？
SQL注入的原理是将SQL代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法。也就是说，在一些对SERVER端发起的请求参数中植入一些SQL代码，SERVER端在执行SQL操作时，会拼接对应参数，同时也将一些SQL注入攻击的“SQL”拼接起来，导致会执行一些预期之外的操作
如何解决SQL注入
- 严格的参数校验
- 参数校验就没得说了，在一些不该有特殊字符的参数中提前进行特殊字符校验即可。
- PreparedStatement预编译防止SQL注入
- PreparedStatement具有预编译功能，以上述SQL为例使用PreparedStatement预编译后的SQL为：此时SQL语句结构已固定，无论"?"被替换为任何参数，SQL语句只认为where后面只有一个条件，当再传入 1001 or 1 = 1时当作一个整体，语句会报错，从而达到防止SQL注入效果
- mybatis中#{}防止SQL注入
- mybatis中#{}表达式防止SQL注入与PreparedStatement类似，都是对SQL语句进行预编译处理注意：#{} ：参数占位符，可防sql注入
- ${} ：拼接替换符，不能防止SQL注入，一般用于传入数据库对象（如：数据库名称、表名）order by 后的条件
2.MySQL优化
-1.对数据库优化的理解
MySQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如，通过优化文件系统，提高磁盘I\O的读写速度；通过优化操作系统调度策略，提高MySQL在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。
- 针对查询，我们可以通过使用索引、使用连接代替子查询的方式来提高查询速度。
- 针对慢查询，我们可以通过分析慢查询日志，来发现引起慢查询的原因，从而有针对性的进行优化。
- 针对插入，我们可以通过禁用索引、禁用检查等方式来提高插入速度，在插入之后再启用索引和检查。
- 针对数据库结构，我们可以通过将字段很多的表拆分成多张表、增加中间表、增加冗余字段等方式进行优化。
-1.数据表结构设计优化
- 不能完全按照数据库三大范式设计。数据库范式就是让一个字段干一个字段的事，一个表干一个表的事，拒绝冗余。但如果完全没有冗余的话，很多时候会引起多表join降低性能，适当增加冗余可以提高查询性能。比如要查询班级里学生的姓名，如果在班级表里冗余学生字段的话就不用去join查询学生表了，一般我们冗余的事不太容易发生变化的字段比如姓名性别等；
- 当不可避免需要join的时候，可以设计个中间表去存储所有数据，用了中间表就要考虑到这张表的更新机制避免数据不一致的情况（最好定期轮询检查）
- 要设计一个游标字段（自增主键和时间戳很适合当游标字段），减少limit查询(limit n,m)尽量依靠游标来查，比如说你用主键，下次查询就>或<上次查询的最后一个id就可以了
0.如何分析sql的性能(EXPLAIN 命令)
我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 。
1.什么是执行计划
执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后，具体的执行方式。
通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。
2.执行计划常用字段
MySQL 为我们提供了 EXPLAIN 命令，来获取执行计划的相关信息。
需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。
执行计划有12个字段组成，常用的有：
1.type表示查询表连接类型**，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- system：这种类型要求数据库表中只有一条数据，是
const类型的一个特例，一般情况下是不会出现的。 - **const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。**基于主键或唯一索引唯一值查询，最多返回一条结果，比eq_ref略好。
- eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询
- ref : 常用于非主键和唯一索引扫描。
- ref_or_null：这种连接类型类似于
ref，区别在于MySQL会额外搜索包含NULL值的行 - index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
- unique_subquery：类似于
eq_ref，条件用了in子查询 - index_subquery：区别于
unique_subquery，用于非唯一索引，可以返回重复值。 - range：常用于范围查询，比如：between ... and 或 In 等操作
- index：全索引扫描（通过遍历整个索引树来获取数据行，而不是直接扫描整张表的数据。）
- ALL：全表扫描
2.possible_keys:表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。
3.key:表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。
4.key_len:表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。
5.rows:估算要找到所需的记录，需要读取的行数，这是一个估计值。
**6.extra:**这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：
- Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
- Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
- Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
- Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
- Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
- Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。
3.explain具体怎么分析一条慢sql?
如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，
比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，
第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，
第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复
1.慢查询问题
0.接口响应慢怎么办？
您可以尝试以下方法来优化Java接口的性能：
- 代码优化：审查接口代码，确保它们没有不必要的循环、嵌套或递归，尽可能减少资源消耗和时间复杂度。
- 数据库优化：如果接口涉及数据库操作，可以优化SQL查询，添加索引以提高检索速度，或者考虑使用缓存来减少数据库访问次数。
- 并发处理：使用多线程或异步处理来处理并发请求，提高系统吞吐量和响应速度。
- 资源管理：及时释放资源，如关闭数据库连接、IO流等，以避免资源泄露导致性能下降。
- 缓存：考虑将频繁使用的数据缓存起来，减少重复计算或查询的开销。
- 网络优化：确保网络连接稳定，并尽量减少网络通信的延迟。
- 代码审查和性能测试：定期进行代码审查和性能测试，及时发现并解决潜在的性能问题。
以上是一些常见的优化方法，根据具体情况，您可能需要结合实际场景进行调整和优化。
1.怎么定位慢查询
2种方法·：
- 运维监控工具Slywalking
我们当时做压测的时候有的接口非常的慢，接口的响应时间超过了2秒以上，因为我们当时的系统部署了运维的监控系统Skywalking ，在展示的报表中可以看到是哪一个接口比较慢，并且可以分析这个接口哪部分比较慢，这里可以看到SQL的具体的执行时间，所以可以定位是哪个sql出了问题
- mysql慢查询日志 开启慢查询日志功能：慢查询日志是MySQL内置的一项功能，可以记录执行超过指定时间的SQL语句。在MySQL配置文件中设置slow_query_log=1以启用该功能，并设定long_query_time=2来规定只有执行时间超过2秒的SQL才会被记录。分析慢查询日志文件：通过查看日志文件中记录的SQL语句和相应的执行时间、扫描行数等指标，可以直观地识别出慢查询。一般情况下，慢查询日志会记录查询执行的时间、返回的行数以及检查的行数等重要信息。
2.导致MySQL慢查询有哪些原因？
- 索引使用不当或缺失： 索引未覆盖查询中使用的字段，或者索引被查询中的函数调用、范围查询或非等值条件所避开。索引选择性差，即索引不能有效地过滤掉大量行。
- 单表数据量太大
- 查询使用了临时表
- 表结构设计不佳： 过度规范化或反规范化可能导致额外的JOIN操作，增加查询复杂度。冗余数据或设计不良的外键关系。
- limit深度分页问题
- 复杂的查询语句： 包含大量的连接（JOINs）、子查询或者复杂的嵌套表达式，这些都会增加CPU和I/O负载。
3.大表查询慢常见优化措施？
当MySQL单表记录数过大时，数据库的性能会明显下降，一些常见的优化措施如下：
- 创建适当的索引：可根据EXPLAIN来查看是否用了索引还是全表扫描 索引类型选择：根据查询的需要创建有针对性的索引，比如在WHERE和ORDER BY命令上涉及的列建立索引。避免索引过多：索引并不是越多越好，需要根据实际查询有针对性地创建，同时删除不必要的索引，避免维护索引的额外开销。合理设置索引顺序：使用多列索引时，注意索引的顺序与查询条件保持一致，可以提高索引的使用效率。避免索引失效
- 优化查询语句：避免全表扫描：应尽量减少在查询语句中使用全表扫描的操作，比如避免使用 SELECT ，尽量在 WHERE 子句中使用已有索引的字段。优化子查询和连接：尽量减少子查询的使用，将其转化为连接（JOIN）方式；如果必须使用子查询，尽量使其返回更少的记录数。同样地，避免多个表的连接，特别是大数据表之间的连接。使用具体的列名：在SELECT语句中，指定所需的具体列名而不是使用 * ，可以避免回表查询
- 利用缓存：利用Redis等缓存热点数据，提高查询效率
- 提升硬件配置性能更高的硬件设备：采用更快的存储介质（如固态硬盘）和更大的内存容量，以提升查询的IO性能。均衡系统资源：合理分配系统资源，确保MySQL的服务模型能够充分利用服务器的CPU资源。
- 读写分离。经典的数据库拆分方案，主库负责写，从库负责读
- 通过分库分表的方式进行优化，主要有垂直拆分和水平拆分
4.慢sql的优化分析思路？
步骤如下：
1.查看慢查询日志记录，分析慢SQL
通过慢查询日志slow log，定位那些执行效率较低的SQL语句，重点关注分析
2.explain查看分析SQL的执行计划
当定位出查询效率低的SQL后，可以使用explain查看SQL的执行计划。
比如在这里面可以通过key和key_len检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，
第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，
第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复
3.profile 分析执行耗时
explain只是看到SQL的预估执行计划，如果要了解SQL真正的执行线程状态及消耗的时间，需要使用profiling。开启profiling参数后，后续执行的SQL语句都会记录其资源开销，包括IO，上下文切换，CPU，内存等等，我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。
4.Optimizer Trace分析详情
profile只能查看到SQL的执行耗时，但是无法看到SQL真正执行的过程信息，即不知道MySQL优化器是如何选择执行计划。这时候，我们可以使用Optimizer Trace，它可以跟踪执行语句的解析优化执行的全过程。
大家可以查看分析其执行树，会包括三个阶段：
- join_preparation：准备阶段
- join_optimization：分析阶段
- join_execution：执行阶段
5.确定问题并采用相应的措施
最后确认问题，就采取对应的措施。
- 多数慢SQL都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，我们可以优化索引。
- 我们还可以优化SQL语句，比如一些in元素过多问题（分批），深分页问题（基于上一次数据过滤等），进行时间分段查询
- SQl没办法很好优化，可以改用ES的方式，或者数仓。
- 如果单表数据量过大导致慢查询，则可以考虑分库分表
- 如果数据库在刷脏页导致慢查询，考虑是否可以优化一些参数，跟DBA讨论优化方案
- 如果存量数据量太大，考虑是否可以让部分数据归档
2.sql优化经验
sql语句优化小技巧
SQL语句优化
- 查询时只返回必要的列，用具体的字段列表代替 select * 语句,因为要尽量用聚集索引防止回表查询
- SQL语句要避免造成索引失效的写法
- 尽量用union all代替union UNION 会进行去重处理，这会增加排序和比较的计算成本
- 避免在where子句中对字段进行表达式操作
- Join优化 能用inner join 就不用left join right join，如必须使用 一定要以小表为驱动，（ 内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序）inner join 内连接，只保留两张表中完全匹配的结果集；left join会返回左表所有的行，即使在右表中没有匹配的记录；right join会返回右表所有的行，即使在左表中没有匹配的记录；
- 使用varchar代替char.(因为可变常字段存储空间小，可节省空间)
- 将多次插入换成批量Insert插入：例如，使用 INSERT INTO order (id, code, user_id) VALUES (123, '001', 100), (124, '002', 100), (125, '003', 101);
- 避免在where子句中使用!=或<>操作符使用!=和<>很可能会让索引失效应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
- 多次插入改成批量插入默认新增SQL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来。
sql优化的真实场景
1.正确建立索引（联合索引）
应对场景：在实际工作中，对于一个承载着业务核心的表，它除了数据条数多之外，而且会有很多列。 对于这样核心的表，我们又频繁的有查询修改操作。查询的时候 往往是多个列在一起构成筛选条件,所以针对这种情况我们就需要创建合理的 联合索引。
这里通过创建一个用户信息表来讲解，表名为userbase 表信息如下：
创建一个联合索引 ，由 age,score,sex 组成注意他们的前后顺序 ：age 是1 ，score是2 ，sex是3
执行不同条件查询及结果：语句：select * from userbase where age =10 and score=98 and sex=‘男’ (顺序：1、2、3)结果：使用了索引
语句：select * from userbase where age =10 and score=98 (顺序：1、2)结果：使用了索引语句：
select * from userbase where score=98 and sex=‘男’ (顺序：2、3)结果：未使用索引
语句：select * from userbase where score=98 (顺序：2)结果：未使用索引
联合索引的最左原则： 以最左边的那列为中心，只要它参与了，就可以让索引起到作用，否则就不起作用。
我们知道了最左原则，就可以避免创建过多而无用的索引。索引的创建，会占用硬盘大量的空间，所以合理的创建索引，不但可以让我们查询效率提高，也能减少硬盘空间。
2.建表时选择合适的数据类型
使用varchar代替char.(因为可变常字段存储空间小，可节省空间)
3.不滥用事务修改事务级别
不同的数据库，有自己默认的事务类型。我们在做开发时，因为某些关键业务比如涉及到金钱的转账等，必须用事务防止出现脏数据(或脏读现象)，保证数据的准确性。 但是这样的情况下，必定有损执行性能，在一些不必要的业务中，建议取消事务。如我要查询某个结果，这个结果即使出现脏读，也不影响我们业务。就可以取消事务，来提高整体效率。
4.避免不必要的数据库连接
如果要执行100行sql语句，如果可以一次执行，那就在一次数据库连接，一起执行100条。而不是通过100次数据库连接，每次连接执行一条。较少数据库连接时产生的性能消耗。
在Java中，我们可以使用JDBC（Java Database Connectivity）来连接和操作数据库。为了一次性执行多条SQL语句，我们可以使用Statement或PreparedStatement对象的executeBatch()方
import java.sql.*;
public class Main {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydatabase";
String user = "username";
String password = "password";
try {
Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
for (int i = 1; i <= 100; i++) {
stmt.addBatch("INSERT INTO mytable VALUES (" + i + ", 'value" + i + "')");
}
int[] results = stmt.executeBatch();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
在这个例子中，我们首先建立了一个到数据库的连接，然后创建了一个Statement对象。然后，我们在循环中添加了100个INSERT语句到批处理中。最后，我们调用executeBatch()方法一次执行所有的插入操作，然后关闭连接。
3.分页问题
1.MySQL查询 limit 1000,10 和limit 10 速度一样快吗？
结论：mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大。
分析背后原理：
limit 1000,10 和limit 10,对应 limit offset, size 和 limit size 两种方式。
而其实 limit size ，相当于 limit 0, size。也就是从0开始取size条数据。
也就是说，两种方式的区别在于offset是否为0。
先来看下limit sql的内部执行逻辑。
MySQL内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。
执行器可以通过调用存储引擎提供的接口，将一行行数据取出，当这些数据完全符合要求（比如满足其他where条件），则会放到结果集中，最后返回给调用mysql的客户端。
以主键索引的limit执行过程为例：
执行select * from xxx order by id limit 0, 10;，select后面带的是星号，也就是要求获得行数据的所有字段信息。
server层会调用innodb的接口，在innodb里的主键索引中获取到第0到10条完整行数据，依次返回给server层，并放到server层的结果集中，返回给客户端。
把offset搞大点，比如执行的是：select * from xxx order by id limit 500000, 10;
server层会调用引擎层innodb的接口，由于这次的offset=500000，会在innodb里的主键索引中获取到第0到（500000 + 10）条完整行数据，返回给server层之后根据offset的值挨个抛弃，最后只留下最后面的size条，也就是10条数据，放到server层的结果集中，返回给客户端。
可以看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而获取的这些无用数据都是要耗时的。
因此，mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大。
2.深度分页怎么优化？
(什么是深度分页问题？)
select * from xxx order by id limit 500000, 10;
当offset非常大时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，拷贝完整数据相比只拷贝行数据里的其中一两个列字段更耗费时间。这就是深度分页问题。
优化方法：
- 子查询优化
我们可以采用覆盖索引和子查询来解决
先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了
因为查询id的时候，走的覆盖索引，所以效率可以提升很多
select * from xxx where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;
- 延迟关联尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。
4.列举一下，常用的数据库设计优化技巧？
- 字段尽量避免使用NULL
- 合理选择数据类型
- 字段选择合适的长度
- 正确使用索引
- 尽量少定义text类型
- 合理的数据表结构设计
- 适当的冗余设计
- 优化SQL查询语句
- 一张表的字段不宜过多
5.group by查询效率慢,如何优化
group by详解链接：看一遍就理解：group by详解 (qq.com)
group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。日常开发中，我们使用得比较频繁。如果不注意，很容易产生慢SQL。
group by可能会慢在哪里？因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。
- 如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是
tmp_table_size），会把内存临时表转成磁盘临时表。 - 如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。
如何优化group by呢?
- group by 后面的字段加索引
- order by null 不用排序
- 尽量只使用内存临时表
- 使用SQL_BIG_RESULT
6.order by查询效率慢,如何优化.
order by详解链接：看一遍就理解：order by详解 (qq.com)
在 MySQL 中，ORDER BY 用于对查询结果进行排序。它有两种排序方式：
- 全字段排序：按照查询语句中 ORDER BY 后面的字段进行排序。这种方式比较直观，直接按照指定的字段排序即可。
- RowNumber 排序：当 ORDER BY 后面没有指定具体的字段时，MySQL 会采用 RowNumber 排序方式。这种方式是 MySQL 内部实现的排序机制，具体实现过程如下：首先，MySQL 会根据表的大小，分配一块内存空间（称为 Sort Buffer），用于存放需要排序的数据。然后，MySQL 会将表中的数据按照 RowNumber 的顺序，依次读取到 Sort Buffer 中。这个过程是通过多路归并排序算法实现的，即将表中的数据分成多个小的子集，每个子集内部按照 RowNumber 排序，然后再将子集合并成一个有序的大集合。最后，MySQL 会按照 Sort Buffer 中的数据顺序，依次输出结果。
order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。
rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点.如果是order by排序,可能会借助磁盘文件排序的话，效率就更慢一点.
如何优化order by的文件排序?
- 因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序啦。而索引数据本身是有序的，我们通过建立索引来优化
order by语句。 - 我们还可以通过调整
max_length_for_sort_data、sort_buffer_size等参数优化；
7.原本可以执行得很快的 SQL 语句，执行速度却比预期的慢很多，原因是什么？如何解决？
原因：从大到小可分为四种情况
- MySQL 数据库本身被堵住了，比如：系统或网络资源不够。
- SQL 语句被堵住了，比如：表锁，行锁等，导致存储引擎不执行对应的 SQL 语句。
- 确实是索引使用不当，没有走索引。
- 表中数据的特点导致的，走了索引，但回表次数庞大。
解决：
- 考虑采用 force index 强行选择一个索引
- 考虑修改语句，引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。
- 第三种方法是，在有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
- 如果确定是索引根本没必要，可以考虑删除索引。
2.索引
-1.对索引的理解？
索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关。MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
0.如何创建及保存MySQL的索引？
- 在创建表的时候创建索引可以按照如下方式，在id字段上使用UNIQUE关键字创建唯一索引
- 在已存在的表上创建索引
例如，可以按照如下方式，在bookId字段上建立名称为UniqidIdx的唯一索引：
CREATE UNIQUE INDEX UniqidIdx ON book (bookId);
-2.全表扫描比遍历索引更高效的场景
在某些情况下，全表扫描可能比遍历索引更高效。这是因为数据库优化器会基于成本估算和统计数据来决定是否使用索引。具体来说，如果查询需要返回表中大部分数据，或者查询条件不足以显著减少需要检索的数据量，全表扫描可能会更快。
以一个例子来说明：假设有一张包含10万行数据的表，每个数据块可以存放100行数据，那么整张表大约有1000个数据块。如果一个查询需要返回20%的数据，也就是2万行，那么即使使用了索引，数据库仍然需要读取大量的数据块。在这种情况下，如果索引的选择性不高，即索引列中的值重复率较高，那么索引的优势就会减弱。因为索引本身也占用空间，且在索引中查找到每一行后，还需要回到表中获取完整的行数据，这个过程称为“回表”。如果回表的次数非常多，那么这个额外的开销就可能导致索引扫描不如全表扫描高效。
此外，全表扫描在某些情况下可以避免。例如，如果查询可以通过仅访问索引而不需要访问表数据来完成，那么全索引扫描（也称为快速全索引扫描）可能会更高效。这通常发生在查询只需要返回索引列的情况下。
总的来说，数据库优化器会根据多种因素决定是否使用索引，包括表的大小、索引的选择性、查询的复杂性以及数据分布等。在实际应用中，理解这些原理有助于编写更高效的SQL查询，并在必要时对索引策略进行调整。
-1.Hash索引和B+树索引的区别
hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是b+树，
哈希索引不支持排序，因为哈希表是无序的。
哈希索引不支持范围查找。
哈希索引不支持模糊查询及多列索引的最左前缀匹配。
因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的，每次查询都是从根节点到叶子节点。
1.什么是索引
**索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。**它可以比作一本字典的目录，可以帮你快速找到对应的记录。
2.索引的优缺点
索引的优点主要有以下几条： 1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。 2. 可以大大加快数据的查询速度，这也是创建索引的主要原因。 3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。 4. 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。 增加索引也有许多不利的方面，主要表现在如下几个方面： 1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。 2. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大 量的索引，索引文件可能比数据文件更快达到最大文件尺寸。 3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速 度。
3.索引的作用/为什么用索引
数据是存储在磁盘上的，查询数据时，如果没有索引，会加载所有的数据到内存，依次进行检索，读取磁盘次数较多。有了索引，就不需要加载所有数据，因为B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，查询速度大大提升。
4.索引数据结构
索引的数据结构主要有B+树和哈希表。MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引。
-1.b+树的叶子节点和非叶子节点分别存储的什么？
聚簇索引：
- 叶子节点：存储的是整行数据。
- 非叶子节点：存储的是主键值，这些值用于指导查询过程中的搜索方向。
非聚簇索引：
- 叶子节点：存储的是主键值，而不是整行数据。这是因为非聚簇索引的目的是加速查询，而不是直接提供数据。当通过非聚簇索引找到主键值后，MySQL会进行所谓的“回表”操作，即根据主键值去聚簇索引中查找相应的整行数据。
- 非叶子节点：存储的是非主键索引列的值，这些值也用于指导查询过程中的搜索方向。
B+树是一种平衡多路查找树，广泛应用于数据库和文件系统的索引结构。具体到MySQL的B+树索引结构：
- 非叶子节点：这些节点不存储实际的数据记录，只保存索引字段（也就是键值）以及指向子节点的指针。这样做可以使得每个非叶子节点能够索引大量的数据，而不需要占用过多的空间。由于非叶子节点仅包含键值和指针，这减少了树的高度，从而加快了搜索速度。
- 叶子节点：所有实际的数据都存储在叶子节点中。每个叶子节点不仅包含了索引字段，还包含了完整的数据记录。此外，叶子节点之间通过指针相连，形成了一个链表，这个特性优化了范围查询的性能。
总的来说，B+树索引结构在MySQL中的应用主要是为了提高数据检索的效率，特别是对于大量数据的查询操作。通过减少I/O操作的次数和利用磁盘预读特性，B+树能够有效地提升数据库系统的性能。
0.什么是b+树?
B+树是一种常用的数据结构，通常用于数据库索引和文件系统中。它是一种多路搜索树，具有以下特点：
- 内部节点存储的是索引字段（也就是键值）以及指向子节点的指针，叶子节点存储的是数据记录；
- 所有叶子节点之间通过指针连接成一个有序链表，便于范围查询；
- 内部节点通常不存储数据，只存储键值和指向子节点的指针；
- B+树的高度通常比较低，查找效率高；
- B+树的叶子节点都在同一层，便于范围查询和顺序遍历。
1.b+树和 b树的区别
B树（B-树）和B+树都是数据库和文件系统中常用的数据结构，它们在索引优化和存储管理方面具有重要作用。B+树相对于B树进行了优化，使其更加适合作为外存储的索引结构。具体分析如下：
- 节点结构 B树：每个节点既包含键也包含数据值。每个非叶子节点中的键和数据都紧密存储在一起，这可能导致在数据量较大时树的高度增加，从而影响查询效率。B+树：非叶节点只包含键，不包含实际数据，所有数据项都存储在叶子节点上。叶子节点之间通过指针相连，形成链表，便于区间查找和遍历。
- 磁盘IO次数 B树：因为数据和键都存储在每一个节点中，当数据量大时树的高度可能较高，导致更多的磁盘IO操作。B+树：由于非叶节点仅存储键，能够存放更多的键，从而降低树的高度，减少磁盘IO次数，提高查询性能。
- 查询性能 B树：查询性能不稳定，最好情况下时间复杂度为O(1)，最差情况下为O(log n)，这取决于要查找的数据是否在根节点或叶节点上。B+树：查询效率稳定为O(log n)，因为所
1.为什么选择b+树而不是b树当索引数据结构？
B+树：非叶节点只包含键，不包含实际数据，所有数据项都存储在叶子节点
- IO次数较少：B+树的非叶节点只包含键，而不包含真实数据，因此每个节点能指向的子节点个数比B树多很多，即阶数m更大。这样，B+树的高度更低，访问时所需的IO次数更少
- 查询性能稳定：由于B+树的所有叶子节点在同一层，并且通过指针连接成链表，这意味着每次查找操作的路径长度都相同，提供了稳定的查找效率。
- 范围查询高效：B+树的叶子节点之间的链表结构使得范围查询更加高效。可以顺序地访问叶子节点来快速检索一定范围内的所有记录。
2.b+树索引的特点
B+树索引是一种数据库中常用的索引结构，它是B-Tree的改进版，被广泛应用于数据库和文件系统中。
B+树索引的特点如下：
- 所有数据记录都是存储在叶子节点上：这一特性使得B+树能够保持数据的有序性，并且便于范围查找和顺序访问。
- 非叶子节点仅存储关键字信息（即索引）：这样做可以减少每个非叶子节点的大小，从而降低树的高度，提高查询效率。
- 叶子节点之间通过指针连接：这种设计允许对数据进行高效的顺序访问，对于范围查询特别有用。
- 平衡性：与B-Tree一样，B+树是一种平衡多叉树，这意味着树的所有分支都保持大致相同的高度，这有助于保证查询效率的稳定性。
- 支持动态插入和删除：B+树的结构允许在不影响整体树结构的情况下，对数据进行动态的插入和删除操作。
- 适合磁盘存储：由于磁盘的IO操作相对较慢，B+树的结构减少了必要的磁盘读取次数，因为它能够让查询路径更加紧凑。
- 支持左闭合区间搜索：B+树的搜索采用左闭合区间，这意味着搜索某个关键字时，会包含等于该关键字的最低值的记录，这对于支持自增ID等场景非常有利。
综上所述，B+树索引因其高效的查询性能、良好的数据有序性和适宜磁盘存储的特性，在数据库系统中得到了广泛的应用。它不仅适用于等值查询，也非常适合处理范围查询和顺序访问的场景。
3.b+树为什么矮胖？
B+树：非叶节点只包含键，不包含实际数据，所有数据项都存储在叶子节点
- 节点存储信息多：B+树的非叶子节点可以存储更多的键值对，这意味着每个节点可以指向更多的子节点，从而增加了树的分叉数。所以树的每一层都可以存储更多的数据，减少了整体的层数。
- 减少磁盘I/O操作：在数据库系统中，数据的查找通常涉及到磁盘的读取操作。B+树的“矮胖”特性意味着在进行数据查找时，需要遍历的树的层数更少，这样可以减少磁盘的I/O操作次数，提高数据检索的效率。
剩余60%内容，订阅专栏后可继续查看/也可单篇购买
我的笔记专栏，内有自己整理的八股知识笔记和算法刷题笔记，我会不断通过他人和自己的面经来更新和完善自己的八股笔记。专栏每增加一篇文章费用就会上涨一点，如果你喜欢的话建议你尽早订阅。内有超详细苍穹外卖话术！后续还会更新其他项目和我的实习经历的话术！敬请期待！
投递OPPO等公司9个岗位 >
查看6道真题和解析