Java面试题-数据库

数据库

慢速SQL优化

• 在where子句中，将过滤数据多的条件放在前面，最快速度形成记过集
• 尽量避免在where子句中使用表达式操作，可能或导致引擎放弃使用索引而进行全表扫描
• 应尽量避免在where子句中对字段进行 NULL 值的判断，这可能导致引擎放弃使用索引而进行全表扫描
• 用 EXSITS 替代 IN，用 NOT EXSITS 替代 NOT IN
• 不要在索引列上进行 IS NULL 和 IS NOT NULL 的判断
• 不要在索引列进行计算
• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 和 order by 字段列添加索引
• 避免使用大表做 JION，使用 group by 分组、自动排序
• 一次查询数据量不宜过大，可以采用分页查询
• 尽量避免在查询语句中是 LIKE 进行模糊查询
• 返回必要的字段，用具体的字段代替 select * 语句

JDBC

JDBC 的使用步骤

1. 注册驱动（有些新的版本会自动注册驱动）
2. 创建连接
3. 创建 Statement 对象
4. 执行 SQL
5. 处理 SQL 结果
6. 关闭连接

在 JDBC 编程处理事务的步骤

1. 设置自动提交为 false
2. 执行事务处理SQL
3. 如果没有出现异常，提交事务
4. 如果出现异常，回退事务
5. 在finally里关闭连接

事务

什么是事务

保证在一个事务中多次操作，要么全部成功，要么全部失败

事务可能发生问题？隔离级别有哪些

• 隔离级别
  • Read-UnCommmitted：已读未提交
  • Read-Committed：已读已提交
  • Repeatable-Read：可重复
  • Seriablizable：可串行
• 可能发生的问题
  • 脏读：一个事务读取到了其他事务未提交的数据，将隔离级别设置为 Read-Committed（已读已提交）
  • 不可重复读：一个事务多次对相同的 sql 的查询结果不一致， 将隔离级别设置为 Repeatable-Read（可重复读）
  • 幻读/影读：对表中数据进行统计时，统计出来的结果与实际表中的数据不一致，将隔离级别设置为 Seriablizable（可串行）
• 注意：隔离级别越高，事务并发就越低

事务的四大特征

• 原子性：事务的操作要么全部成功，要么全部不执行
• 隔离性：多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间相互隔离
• 持久性：事务一旦提交，对数据库里的数据的改变是永久的，即使数据库发生故障，也不会对其产生任何影响
• 一致性：事务前后数据的完整性必须保持一致

分库分表分片的策略有哪些？

• 怎么分库分表
  

• 分库

  • 垂直分库：根据业务的耦合性，将关联度低的不同表储存在不同的数据库里，按业务分类进行独立划分，例如
    

  • 水平分库：根据表数据规则，将一个表（商品表）得数据分到不同得库中，每个库只有这个表的部分数据，例如：上面虽然已经把商品库分成3个库，但是随着业务的增加一个订单库也出现QPS过高，数据库响应速度来不及,一般mysql单机也就1000左右的QPS，如果超过1000就要考虑分库。
    

• 分表

  • 垂直分表：按照字段拆开，储存到不同的表中
    

  • 水平分表：数据量大，储存到不同的表中，例如：一般我们一张表的数据不要超过1千万,如果表数据超过1千万，并且还在不断增加数据，那就可以考虑分表。
    

索引

什么是索引

官方定义是数据结构，例如书本的目录

为什么使用索引

提高查询效率

索引应用时有什么弊端

• 索引会额外占用一定的储存空间
• 对更新操作带来一定的复杂度（更新数据时也会更新索引）

索引的分类

• 主键索引：数据列不允许重复，不允许为null，一个表中只能有一个主键
• 唯一索引：数据列不允许重复，允许为null，一个表允许创建多个唯一索引
• 普通索引：基本的索引类型，没有唯一性的限制，允许为null
• 全文索引：是目前搜索引擎使用的一种关键技术，对文本内容进行分词，搜索
• 组合索引：多个列组成一个索引，多用于组合查询，效率大于索引合并

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

• 主键索引：是一种特殊的唯一索引，不允许有空值。
• 普通索引：MySQL 中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值 和空值，纯粹为了查询数据更快一点。

如何查看表中的索引

show index from 表名

怎么查看一个sql语句中，索引是否生效

• 使用 explain，使用索引列时索引才会生效

explain select * from 表名 where 列名(索引列) = 条件

索引为什么快（索引使用了那种数据结构）

常见的MySQL主要有两种结构：hash索引和 B+Tree 索引，MySQL 默认使用 innodb 引擎，索引默认使用 B+树 结构

哪些情况下不会触发索引

• 使用左或左右模糊匹配时，也就是 like %xx 或 like %xx% 模糊查询时，索引失效
• 在 where 子句中，索引列使用表达式，索引失效
• 在 where 子句中使用函数时，索引失效
• 在 where 子句中，or 的前后都必须是索引列，否则索引失效
• 在索引列上使用 IS NULL 和 IS NOT NULL ，索引失效
• 在索引字段上使用 NULL、<>、!= 索引失效
• MySQL 在遇到字符串和数字进行比较时，会将字符串转换为数字再进行比较，如果字符串是索引列，传入的参数是数字，进行比较时，索引列会发生隐式类型转换，由于隐式类型转化是通过CAST函数实现的，所以索引失效
• 组合索引在使用时要遵循最左优先匹配原则，也就是按照最左优先的方式进行索引的匹配，否则索引失效
• 当全表扫描速度比索引速度快的时候不会使用索引

什么是B-树以及它的特点

B-树中每个非叶子节点允许有多个分叉，储存指针、数据、索引，每个叶子节点只储存索引、数据

缺陷：
1. 非叶子节点储存数据，导致磁盘块储存的索引就会少一些，分叉少，树的高度高
2. 叶子节点不支持范围查询，每次查询数据都要从根节点开始，这样可能会影响查询效率

什么是 B+树以及它的特点

B+树是一种多叉平衡树，主要用于索引的储存结构，提高查询效率

• 特点
  • 树中非叶子节点储存索引和指针，叶子节点储存索引和数据
  • 树中叶子节点在相同层并且有序，它们使用双向链表连接，所以更好的支持范围查询
    

B+树非叶子节点储存索引和指针，不储存数据，所以能储存更多的索引，分叉也越多，树的高度越低

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

• 左外连接：是左表和右表的交集再并上左表的所有数据，右表不符合条件的数据显示NULL（是A和B的交集再并上A的所有数据。）
• 右外连接：是左表和右表的交集再并上右表的所有数据，左表不符合条件的数据显示 NULL（是A和B的交集再并上B的所有数据。）
  

数据库建表的范式是什么

• 第一范式（1NF）：字段不可再分（原子性），例如姓名可再分为姓和名，这属于可再分
• 第二范式（2NF）：首先要满足1NF，然后不存在非主键字段对主键字段的部分依赖
• 第三范式（3NF）：首先要满足1NF，然后不存在非主键字段对主键字段的传递依赖

MySQL如何实现远程连接

• 授权法：使用GRANT命令，添加新的用户，授予远程范文的权限
• 改表法：在 localhost 登录 mysql 后，在 mysql 数据库里 user 表里的 host 项的 localhost 改为 %

MySQL 里的8小时未处理机制

MySQL在默认设置下，一个连接的空闲时间查过8小时，MySQL就会断开该连接，而c3p0/dbcp连接池则认为该断开的连接依然有效，在这种情况下，客户端向c3p0/dbcp连接池请求连接时，连接池就会把这个失效的连接返回给客服端，客服端在使用这个连接时就会抛出异常

• 解决方案
  • 第一种：对数据库做操作前，先对数据库连接做校验或判断

    #c3p0配置
    <!--最大空闲时间，60秒内未使用则连接被丢弃。若为0则永不丢弃。默认值: 0 -->   
    <property name="maxIdleTime">60</property>  
    <!-- 当连接池连接耗尽时，客户端调用getConnection()后等待获取新连接的时间，
    超时后将抛出SQLException，如设为0则无限期等待。单位毫秒。默认: 0 -->   
    <property name="checkoutTimeout" value="3000"/>  
    <!--c3p0将建一张名为Test的空表，并使用其自带的查询语句进行测试。
    如果定义了这个参数那么属性preferredTestQuery将被忽略。
    你不能在这张Test表上进行任何操作，它将只供c3p0测试使用。默认值: null -->   
     <property name="automaticTestTable">Test</property>  
    <!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的   
    　　时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable   
    　　等方法来提升连接测试的性能。Default: false -->   
    <property name="testConnectionOnCheckout">false</property>   
    <!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->   
    <property name="testConnectionOnCheckin">true</property> 
    <!--每60秒检查所有连接池中的空闲连接。Default: 0 -->   
    <property name="idleConnectionTestPeriod">60</property>
    

  • 第二种：加大等待时间

    #my.cnf
    wait_timeout=31536000  
    interactive_timeout=31536000
    

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

• Durid 数据库
• HikariCP
• 是维护数据库连接的缓存，在将来需要向数据库发请求时可以重用数据库，连接池用于提高数据库上执行命令的性能，维护数据库连接总数，避免连接过载

数据库中常见的聚合函数

count()、avg()、sum()、max()、min()

悲观锁与乐观锁

• 悲观锁：总是假设最坏的情况，它认为并发操作会导致数据不一致，数据不安全，一个线程一旦拿到悲观锁，其他线程拿到该数据就会阻塞，不能修改该数据了，直到锁被上一个持有释放了才可以执行
  • 数据库中的行锁，表锁，读锁，写锁以及syncronized实现的锁均是悲观锁
• 乐观锁：总是默认最好的情况，它认为并发操作不会导致数据不一致，所以线程可以不停的执行，也无需加锁，也无需等待，只是在提交修改的时候去验证对应的资源（也就是数据）是否被其他线程修改了

数据库表里有1亿条数据时,选择优化表,还是sql语句

优化表，进行水平分表