Java全栈面试题(二)--数据库

慢速SQL优化

        1.WHERE子句中：where表之间的连接必须写在其他Where条件之前，哪些可以过滤掉最大数量记录的条件必须写在Where子句的末尾，HAVING最后。

        2.应尽量避免在where子句中对字段进行null值判断和表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

        3.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。

        4.避免在索引列上使用计算和使用IS NULL和IS NOT NULL

        5.用EXISTS替代IN、用NOT EXISTS替代NOT IN。

JDBC的使用步骤

        1.注册驱动：使用Class.forName("com.mysql.jdbc.Driver")注册该数据库的驱动程序

        2.创建连接：使用DriverManger.getConnection(url,user,password)方法建立与数据库的连接，并得到表示该连接的Connection对象

        3.创建Statement对象：使用Connection对象的createStatement()方法创建Statement对象，用于执行SQL语句

        4.执行SQL语句：使用Statement对象的executeQuery()方法执行查询SQL语句，使用executeUpdate()方法执行插入，更新，删除等操作的SQL语句

        5.处理SQL结果：当执行查询SQL语句时，会返回一个ResultSet对象，其中包含了查询结果数据集。可以使用ResultSet对象的next()方法逐步读取结果数据

        6.关闭连接：使用Connection对象的close()方法关闭与数据库的连接

在JDBC编程中处理事务的步骤

        1.获取连接：首先需要获取与数据库的连接

        2.设置自动提交事务为false：通过Connection对象的setAutoCommit(false)方法设置自动提交事务为false

        3.执行事务处理SQL

        4.如果没有异常，就提交事务：调用Connection对象的commit()方法

        5.如果有异常，就回滚事务：调用Connection对象的rollback()方法

        6.在finally中关闭连接：调用Connection对象的close()方法关闭与数据库的连接

什么是事务

        事务是指数据中心一组操作单元，这些操作要么全部执行成功，要么全部不执行。在关系型数据库中，事务作为访问和更新数据库的一个逻辑单元，可以确保数据的一致性、完整性和持久性。

事务可能发生哪些问题？

        脏读：一个事务读取了另一个事务未提交的数据，然后进行了操作，而另一个事务来回滚了操作，导致第一个事务读取到的数据是无效的。

        不可重复读：一个事务在读取某些数据后，另一个事务修改了这些数据并提交，导致第一个事务再次读取同样的数据时，得到了不同的结果

        幻读：一个事物在读取某个范围内的数据记录时，另一个事务插入了一些新的记录，导致第一个事务再次读取同样范围的数据时，得到了不同的结果

事务的隔离级别

        读未提交(Read uncommitted)：最低的隔离级别，允许一个事务读取另一个事务未提交的数据修改，该隔离级别最低，会产生脏读、不可重复读和幻读等问题

        读已提交(Read committed)：允许一个事务读取另一个事务已经提交的数据修改，该隔离级别可以避免脏读问题，但是可能会出现不可重复读和幻读问题

        可重复读(Repeatable red)：在事务执行期间，多次读取同一数据会得到相同的结果，除非该数据被该事务本身所修改。该隔离级别可以避免脏读和不可重复读问题，但是可能会出现幻读问题

        串行化(Serializable)：最高隔离级别，保证一个事务多次读取同一数据的结果都是一致的，除非被改事务本身改变。从而避免了脏读、不可重复读和幻读等问题。但是这个隔离级别会导致并发性能降低，因为同一时间只允许一个事务访问数据库。

事务的四大特征

        原子性：指事务是一个不可分割的工作单位，事务中的所有操作要么全部成功执行，要么全部失败回滚。

        一致性：事务前后数据的完整性必须保持一致

        隔离性：多个事务并发执行时，每个事物都应该被隔离开来，互不干扰。每个事务对数据库的操作应该与其他事务隔离开来，保证数据的完整性和一致性。

        持久性：事务一旦提交后，它对数据库中数据的修改就是永久性的，并且不能被回滚。

分库分表分片策略有哪些

        数据切片：通过某种特定的条件，将存放在同一个数据库中的数据拆分存放到多个数据库中，从而达到分散单台机器负载的情况，即分库分表。根据数据切分规则的不同，主要有两种模式:

        垂直切分，对不同的表进行切分，存储到不用的数据库之上

        水平切分，对同一个表中的数据进行切分，存储到不用的数据库之上。

        规则是根据表中数据的逻辑关系，按照某种条件拆分

索引技术原理

        索引是数据库中的一种特殊数据结构，用于优化数据库查询操作的性能。索引技术原理主要包括索引的存储结构、索引的查找算法以及索引的维护方法

        索引的存储结构：常见的索引数据结构主要有B树、B+树、哈希索引等。B树和B+树索引是一种平衡树，它们可以高效地支持范围查询和排序，适用于大规模数据存储和高并发查询；哈希索引是一种键映射到哈希表中的结构，它适用于等值查询，不支持排序和范围查询

        索引的查询算法：根据不同的索引类型，查找算法也会有所不同。对于B树和B+树索引，通常采用平衡查找算法，即从根节点开始按照规定的查找路径向下进行。对于哈希索引，采用哈希函数对关键字进行计算，从而快速定位到目标数据块

        索引的维护方法：对于动态数据集合，索引需要进行维护，以保证索引的正确性和性能。常见的维护方式有插入、删除和更新等操作。对于B树和B+树索引，插入和删除操作需要调整树的平衡和结构；更新操作则需要先将旧记录删除，在插入新纪录。对于哈希索引，由于哈希冲突问题，需要采用开放地址法或链式方法进行解决。

        总体来说，索引技术的原理主要包括存储结构、查找算法以及维护方法，不同的实现方式会影响索引的效率和应用场景。在实际使用中，需要根据具体业务需求和数据规模等因素选择合适的索引类型和实现方式，以提高查询性能和效率。

MySQL索引为什么这么快

        减少数据扫描：使用索引查询数据时，数据库不需要扫描整个数据表，而是直接定位到索引对应的数据页，从而减少数据扫描的时间

        加快数据定位：索引可以将数据分组存储，并通过B+树的查找算法快速定位到需要查询的数据，从而加快了数据定位的速度

        避免排序和分组：对数据进行排序或者分组时，使用索引可以避免进行数据的全表扫描，从而提高排序和分组的效率

        总之，MySQL 索引通过使用 B+ 树数据结构，实现了快速定位和快速查询数据的功能，从而提高了查询效率。在实际开发中，应该根据数据的访问模式和查询需求来选择适当的索引策略，以达到最优的查询性能。

为什么使用B+树，B+树的磁盘IO次数为多少

        数据库在磁盘上存储数据，而磁盘IO性能相对较慢，提高数据检索性能的关键就是减少IO次数

        B+树从根节点到叶子节点层数只有3层，每次IO可以读取一个节点到内存中，然后使用2分查找定位，使用B+树检索数据每次只需要3次IO

主键索引和普通索引的区别

        主键索引和表格主键有关，普通索引可以是在任何列上创建的。主键索引是一种特殊的索引类型，它唯一标识每行数据，并保证每行数据在索引中只出现一次。表格中只能有一个主键索引，但是可以有多个普通索引

        主键索引是一种聚簇索引，普通索引是一种非聚簇索引，聚簇索引是将数据存储在同一位置，因此索引和数据在同一块中，可以提高查询性能，非聚簇索引是将数据和索引分开存储，索引指向数据的物理地址。具体来说，如果主键索引是表格的主键，则其按照主键值对表格进行排序，这样在使用主键索引进行查询时可以更快地定位到目标值。

        主键索引是唯一索引不能为NULL，普通索引可以包含NULL。

        在多表查询中，主键索引可以作为外键，以便快速查找相关记录，普通索引不能使用外键，需要额外的关联操作才能实现

索引失效情况有哪些

        like以开头索引无效，当like以&结果，索引有效

        or语句前后没有同时使用索引，当且仅当or语句查询条件的前后列均为索引时，索引生效

        组合索引，使用的不是第一列索引时候，索引失效，即最左匹配规则

        数据类型出现隐式转换，如varchar不加单引号的时候可能会自动转换为int类型，这个时候索引失效。

        在索引列上使用IS NULL或者IS NOT NULL时候，索引失效，因为索引是不索引空值的

        在索引字段上使用，NOT、<>、!=不会使用索引，只会进行全表扫描

        对索引字段进行计算操作，函数操作时不会使用索引

        全表扫描速度比索引速度快的时候不使用索引

如何判定SQL语句命中了索引

        在执行 SQL 查询时，数据库管理系统会根据查询条件选择最适合的执行计划。如果查询语句中包含了索引关键字，而且这些关键字对应着已经建立的索引，那么在执行查询时就会使用这些索引来加速查询，从而提高查询效率。

        可以使用EXPLAIN命令来查看SQL查询的执行计划和使用索引，EXPLAIN命令用于分析查询语句的执行计划，并且可以显示使用了哪些索引以及索引的类型。

        执行命令后，数据库会返回一张执行计划表，其中包含了查询语句的执行计划、使用的索引以及索引的类型等信息。可以查看该表中“key”列，如果该列的值为“NULL”，则表示查询没有使用索引；否则，该列的值就是使用的索引名称

左外连接查询和右外连接查询的区别

        外连接分为三种，分别是左外连接(left join)、右外连接(right join)、全外连接(full join)

        左外连接：又称左连接，这种连接方式会显示左边不符合条件的数据行，右边不符合条件的数据行直接显示NULL

        右外连接：也称右连接，这种连接方式会显示右表不符合条件的数据行，左表不符合条件的数据行直接显示NULL 

数据库建表的范式是什么

        第一范式：每个列都不可再拆分

        第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分

        第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键

        在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须要有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计

MySQL如何实现远程连接

        授权法：使用GRANT命令，添加新的用户，授予远程访问权限

        改表法：在localhost登入mysql后，更改"mysql"数据库里的"user"表里的"host"项，将"localhost"改成"%"

MySQL里的八小时未处理的机制

        MySQL在默认情况下设有8小时的连接超时机制。如果某个MySQL连接在8小时内没有任何操作，则MySQL服务器会主动关闭该连接，防止长时间闲置的连接占用服务器资源

        对于"8"小时问题，有些连接池(如c3p0)会默认开启一个类似于心跳检测的机制，每隔一定时间就向MySQL服务器发送一次ping操作，以保持连接的有效性。如果某个连接在8小时内没有进行任何实际操作，但仍然在连接池中被保留，那么MySQL服务器会在8小时后强制关闭这个闲置的连接。当应用程序再次使用这个连接时，会收到一个异常提示，需要重新建立连接

使用过哪些数据连接池？数据库连接池有什么用

        Druid和HikariCP

        作用：连接池可以提高应用程序访问数据库的性能和效率。与每次请求都建立新连接相比，连接池可以复用已经存在的连接，避免了每次连接数据库的开销。同时，数据库连接池还可以控制连接的数量，防止因过多连接而导致数据库性能下降，提高应用程序的并发处理能力。

数据库中常见的聚合函数

        在关系型数据库中，聚合函数是用于计算数据集合中的统计值的函数

        COUNT：查询某字段的行数

        SUM：计算某字段值的总和

        AVG：计算某字段值的平均数

        MAX：查询某字段的最大值

        MIN：查询某字段的最小值

悲观锁乐观锁

        悲观锁和乐观锁是并发控制的两种不同策略，用于协调多个线程多共享数据的访问。它们的主要区别在于对并发冲突的处理方式。

        悲观锁：悲观锁的基本思想是，在数据被修改时，采用加锁的方式防止其他线程访问，即假定数据在大部分时间内都会发生冲突，因此采取一种悲观的策略，避免数据冲突。悲观锁适用于读写操作时间长、且经常发生冲突的场景。

        乐观锁：乐观锁的基本思想是，在数据被修改时，不加锁而是采用版本控制的方式，通过对比版本号来判断数据是否被修改。乐观锁适用于读写操作时间短、且很少发生冲突的场景。

        在实际应用中，需要根据具体情况选择合适的锁策略。悲观锁能够确保数据的一致性，但并发性能较差，乐观锁能够提高并发性能，但需要处理冲突问题。

悲观锁的实现方式

        数据库行锁：在数据库中对某一条数据进行修改时，先申请该数据所在的行锁，并将锁持有到事务结束。其他事务如果需要修改该数据，需要等待锁释放。MySQL中的UPDATE语句就是使用行锁来实现悲观锁。

        数据库表锁：在数据中对某一个表进行读或写操作时，先申请该表的锁并持有到事务结束。其他事务如果需要访问该表，则需要等待表锁释放。表锁通常使用场景不多，因为它会造成大量访问等待，影响系统的性能。

        分布式锁：在分布式系统中，需要处理多个节点同时对同一数据进行修改，此时可以使用分布式锁来控制并发访问。分布式锁的实现方式有很多。例如基于ZooKeeper的实现、Redis实现、基于数据库实现等。

乐观锁的实现方式

        版本号机制：在数据库中新增一个版本号字段，每次更新时将版本号加1。在提交数据更新时，先检查当前提交的版本号与数据表中的版本号是否相同。如果不同，则表示数据已被其他线程修改，需要进行回滚或者重新尝试更新。

        CAS机制：CAS是一种无锁算法，通过循环比较并交换的方式更新数据。在进行更新之前，先获取当前数据的值和版本号。然后比较当前数据的版本号与本地的版本号是否相同。如果相同，说明可以进行更新操作，否则需要重新获取数据进行重试。如果版本号相同，则使用新的数据值和版本号更新原有数据。