java面试---mysql

java连接数据库过程

1、加载驱动类，注册驱动

2、通过DrivrManager获取数据库连接

3、通过connection创建statement

4、通过statement执行sql语句，返回结果集

5、操作resultSet结果集

6、回收数据库资源，晚声明的先关

 

Statement和PreparedStatement的比较

1、statement需要进程字符串拼接，可读性和维护性比较差；另一个使用参数设置，可读性好，不易犯错。

2、PreparedStatement有预编译模式，性能更好

3、PreparedStatement可以防止sql注入攻击

 

存储过程

是一组为了完成特定功能的sql语句集，经编译后存储在数据库中，用户通过制定存储过程的名字并给定参数来执行；

1）存储过程可以用流控制语言编写，可以完成复杂的判断和运算，灵活性好；

2）存储过程被创建后，可以程序中多次调用，而不必重新编写该存储过程的sql语句；

3）存储过程能实现比较快的执行速度；

4）存储过程减少网络流量

5）存储过程可以被用作一种安全机制来充分利用

 

数据库的锁

1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度高。

3、悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

4、乐观锁：假设不会发生并发冲突，只是在提交操作时检查是否违反数据完整性，不能解决脏读问题。

5、共享锁：读取操作创建的锁。其他用户可以并发读取数据，任何事物都不能获取数据上的排它锁，知道已释放所有的共享锁。

6、排它锁：对数据A加上排他锁，其他事物不能再对A加锁，此时既能读数据也能写数据

 

锁的优化策略

1、读写分离

2、分段加锁

3、减少锁的持有时间

4、多个线程尽量以相同的顺序去获取资源

 

设置了索引无法使用

1、已%开头的like语句，模糊匹配

2、or语句前后没有同时使用索引

3、数据类型出现隐式转化

 

sql优化

1、用exists代替in

2、尽量避免在索引列上使用计算

3、避免在索引列上使用is null和is not null

4、当只要一行数据时使用limit1

5、为搜索字段建立索引

6、避免select *

7、尽可能使用not null

8、数据区分度不大的字段不宜使用索引

9、把计算放到业务层而不是数据库层

 

数据库的隔离级别

1）可读取未确认

写事务阻止其他事务，避免更新遗失，没有其他阻止读事务

存在问题脏读；另一个事务还没有提交最终数据，这个读事务读取了中途数据。

2）可读取确认

写事务会阻止其他读写事务，读事务不会阻止其他事务

存在不可重复读问题；一次事务之间进行两次读取，两次结果不一样

3）可重复读

读事务会阻止其他写事务，不会阻止其他读事务

存在幻读；阻止update和delete操作但无法阻止insert，两次读取数量有差别

4）可串行化

读加共享锁，写加排它锁。读取事务可以并发，读写，写写事务之间互斥。

mysql的默认隔离级别是可重复读

 

mysql存储引擎

InnoDB：这种类型事务安全，支持外键；表格速度快，具有事务，回滚和崩溃修复能力；提供行锁。

 

高并发下如何安全的修改同一行数据

要想安全修改，需要保证一个线程在修改时其他线程无法更新这个记录

这种情况下可以使用悲观锁

推荐使用select 。。。 for update，执行时一般的查询不受影响

 

数据库死锁以及解决方法

当两个事物需要有一组有冲突的锁，不能讲事务继续下去，出现死锁；

1）用户A访问表A又要访问表B，用户B访问表B又要访问表A，互相占有锁，没有释放锁导致死锁；程序BUG导致，对于多表操作时，尽量按照相同的顺序处理，避免同时锁定两个资源。

2）用户多次快速点击同一按钮，一段代码对数据库同一条记录进行多次操作，容易出现死锁；

尽量避免用户重复点击，使用乐观锁控制，使用悲观锁控制

3）多次执行全表扫描，导致阻塞和死锁

优化sql，不要使用太复杂的关联表查询；使用执行计划对sql语句进行分析

 

mysql避免全表扫描

1）尽量避免where子句中对字段进行null判断

2）尽量避免在where条件中使用!=或者<>操作符

3）尽量避免where条件中使用or

4）in和not in慎用

5）模糊查询金使用全文索引，就是%放在字段后面

6）尽量避免在where条件中进行计算

7）使用exists 代替 in

 

mysql exists in

1）外层查询小于子查询，使用exists

2）外层查询大于子查询，使用in

3）两者相同，随便使用

 

mysql主从延迟

1）多线程去操作数据库，单线程读取binlog，当某个sql执行时间长或是锁表就会导致sql积压，未被同步到从服务器中

解决方法：

1）半同步复制；写主库时，完成主从复制再返回，此时读取从库就是最新数据

优点：简单；缺点：吞吐量降低

2）强制读主库；

3）数据库中间件；读写全部经过中间件，由它分配到主从库；记录写表的key，在主从同步时间内，访问该表，则将请求路由到主库

优点：一致性好 缺点：中间件成本高

4）缓存记录写key；将库上的某个key要发生写操作，记录在cache，并将超时时间设置为主从同步时间；在请求时，首先读取cache，如果key命中，则读取主库，否则读取从库

优点：保持功能，成本较低 缺点：引入cache组件，读写时先读取cache

 

数据库的水平拆分和垂直拆分

1、水平拆分

以某个字段为依据，按照一定规则，将一个库上的数据拆分到多个库上，降低单库的大小

1）每个库的结构都一样

2）每个库的数据都不一样

3）所有库的并集是全量数据

2、垂直拆分

将一个属性较多，一行数据较多的表，拆分到不同的表中

1）每个库的结构都不一样

2）一般来说，每个表都有一列交集，一般是主键

3）所有表的并集是全量数据

3、垂直拆分的依据

1）将长度较短，访问频率较高的属性放到一个表中，一般为主表

2）将字段较长，访问频率较低的属性放到一个表中，一般为扩展表

3）经常一起访问的属性

4）需要同时访问主表和扩展表时，服务层不要用join连接，应该分两次查询