java数据库面试总结

一，SQL优化和准则

1. 如何优化mysql

①使用索引和优化sql语句

比如：通过explain查看sql执行效果。尽量不要使用select * 用具体字段代表*。

②优化数据库对象：优化数据类型。对表进行拆分（垂直拆分，水平拆分）。使用中间表转移要统计的数据到中间表。

③系统配置优化：主要在配置文件my.cnf。使用数据库连接池。使用查询缓存。

④硬件优化：CPU，内存，磁盘IO等等。

1. 特大访问到数据库上，如何优化

主从复制，读写分离，负载均衡

主从复制原理：master将数据改变记录到二进制日志中，slave监听二进制日志文件是否修改，如果修改则将其拷贝到中继日志中，将改变反应到自己的数据库中。

1. Sql注入问题

说明：是在SQL语句拼接的情况下发送的。导致用户查询的数据超出了角色权限范围

解决：①不要用拼接SQL字符串方法编写SQL语句②检查变量的数据类型和格式，不要有特殊字符。③所以sql语句都封装在存储过程中

1. SQL语句的5种连接概念

1. 交叉连接（cross join）

它返回被连接的两个表所有数据行的笛卡尔积，结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数

1. 内连接（inner join）：只显示符号连接条件的记录

内连接使用比较运算符来比较被连接列的列值，列出与连接条件匹配的数据行。

1. 外连接（outer join）

左外连接：返回符合连接条件的行，而且还包括左表中的所有数据行，左表中不符合连接条件的行的其余字段为空。

右外连接：返回符合连接条件的行，而且还包括右表中的所有数据行，右表中不符合连接条件的行的其余字段为空。

1. 联合连接（union join）/全外连接（full join）

返回符合连接条件的行，而且还包括左、右两个表中的所有数据行，两个表中不符合连接条件的行的其余字段为空。

1. 自然连接（natural inner join）

要求有同名同数据类型的列名，隐含了这样列的相等连接，并删除连接表中的重复列。联接中使用的列不能有限定词。

1. 在MySQL中如何找到查询效率较慢的SQL语句？

在MySQL中如何找到查询效率较慢的SQL语句？

（1）慢查询日志。MySQL中--log-slow-queries来记录执行时间超过long_query_time秒的sql。而mysql 自带 slow log 的分析工具 mysqldumpslow。

（2）explain命令。用法：explain+SQL语句。 explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

1. 存储过程的概念以及优缺点

给一遍好的SQL语句起一个名字，供外界调用

优点：①预编译过了，提高运行速度 ②模块化设计可以重复调用 ③提高安全性，直接调用而并不知道内部结构。④减少网络流量，只要传存储名称和参数

缺点：可移植性不高，调试麻烦

1. 三种范式

第一范式：字段不再分隔  第二范式：一个表只能说明一个事物（不能把学生表和课程表放在一张表上） 第三范式：每列与主键直接相关，不存在传递性。

1. 说一下数据库事务的四个特性，为什么mysql事务能保证回滚

事务的四大特性ACID

A~原子性：事务的一组操作是原子的不可再分割的，这组操作要么同时完成要么同时不完成。类似于一个CAS（compare and swap）（有时间会讲解cas）

C~一致性: 事务在执行前后数据的完整性保持不变。（例：原本的外键约束在进行事务成功后不会损坏）

D~隔离性：当多个事务同时操作一个数据库时，可能存在并发问题，此时应保证各个事务要进行隔离，事务之间不能互相干扰。这个用来解决脏读、不可重复度和幻影读问题

I~持久性：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，不能再回滚。

原子性、一致性和持久性都是通过redo和undo日志文件实现的。是redo还是undo文件都会有一个缓存我们称之为redo_buf和undo_buf。同样，数据库文件也会有缓存称之为data_buf。undo记录了数据在事务开始之前的值，当事务执行失败或者ROLLBACK时可以通过undo记录的值来恢复数据。redo日志记录数据修改后的值，可以避免数据在事务提交之前必须写入到磁盘的需求，减少I/O

1. 并发事务带来的问题

并发事务带来的问题：更新丢失。脏读。不可重复读。幻读等

①更新丢失：两个事务读入同一数据并修改，事务2提交的结果覆盖了事物1提交的结果，导致事务1的更新丢失

②脏读：事务1修改某一数据，并将其写回磁盘，事物2读取同一数据后，事务1由于某种原因被撤销，被事务1修改过的数据恢复原值。事务2读到的数据与数据库中的不一致，出现“脏读”。

③不可重复读：事务1多次访问同一数据，事务2对该数据进行修改，导致事务1两次访问的结果不一样。（重点在修改）

④幻读：同样的条件，第一次读的数据和第二次读的不一样（重点在新增或者删除）

1. 事物的隔离级别

数据库4种隔离级别（由低到高）：

①读未提交数据：允许事务读取未被其他事务提交的变更

②读已提交数据：允许事务读取已经被其他事务提交的变更

③可重复读：确保事务可以多次从一个字段中读取相同的值，在事务持续期间，禁止其他事务对这个字段进行更新。

④可串行化：所有事务都一个接一个的串行执行

• 锁问题

1. 数据库锁机制，数据库都有哪些锁

说明：锁是一种并发控制技术，锁用来多个用户同时访问数据的时候保护数据

①两种基本锁类型：

共享锁(S)：表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。（如果试衣间的门还没被锁上，顾客都能够同时进去参观）

排他锁也叫写锁(X)：排他锁表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面给打开了)。

 

②锁的粒度：MySQL有三种锁的级别：页级、表级、行级。

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

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

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。

 

③MySQL的锁机制比较简单，不同的存储引擎支持不同的锁机制。

比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

1. 参数死锁的四个必要条件（重要）

①互斥条件：一个资源每次只能被一个进程使用

②请求与保持条件：一个进程因请求资源而阻塞时，对获得的资源保持不放

③不可剥夺条件：进程以及获得资源，在未使用完成之前，不能被强行剥夺

④环路等待条件：若干进程循环等待资源

预防死锁：主要破坏四个条件之一即可

1. 悲观锁和乐观锁的概念，实现方法

锁的两种机制：悲观锁和乐观锁

悲观锁：数据库更改开始就将其锁住，知道更改完成才被释放

乐观锁：基于数据版本编号实现，基于时间戳实现。提交的版本必须大于记录当前的版本才能进行更新

• 索引问题

1. 索引失效的几种情况

①组合索引，不是使用第一部分

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

③以“%”开头的LIKE语句，模糊匹配

④数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

⑤对索引列进行运算和使用函数

1. 索引底层实现原理

B+树，经过优化的B+树

主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此InnoDB建议为大部分表使用默认自增的主键作为主索引

MyISAM B+树叶子节点存放的是数据地址

InnoDB b+树叶子节点包含完整的数据记录，要求表必须有主键，索引使用自增字段作为主键

1. 索引的理解

1. 什么是索引？

索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。

1. 什么情况下使用索引？

于创建索引需要额外的磁盘空间，以及太多的索引会导致文件系统大小限制所产生的问题，所以对哪些字段建立索引，什么情况下使用索引，需要审慎考虑。①较频繁地作为查询条件的字段。②唯一性太差的字段（基数小的字段）不适合建立索引。③更新太频繁地字段不适合创建索引。④不出现在where条件中的字段不该建立索引

1. 索引的优缺点：

优点：①加快数据检索的速度 ②加快表和表之间的连接 ③减少分组和排序的检索时间

缺点：占用物理空间，维护索引表

1. 索引的类型

①唯一索引：索引字段不能重复

②非唯一索引：索引字段可以重复

③主键索引：对主键创建索引

④聚簇索引：表中记录的物理顺序和键值的索引顺序相同

⑤组合索引：基于多个字段创建的索引

1. 组合索引注意事项（索引的几大原则）

①最左前缀匹配原则：范围最右，索引列排序相同，查询条件包含组合索引首列字段

②字段区分度高

③尽量扩展索引，不新建索引

④不在索引字段使用函数和计算

⑤不要在索引字段用like模糊匹配

⑥where语句经常出现的字段简历索引，分组字段和排序字段，链表链接字段建索引

1. Mysql引擎

Mysql引擎有很多种：ISAM、MyISAM、HEAP（也称为MEMORY）、CSV、BLACKHOLE、ARCHIVE、PERFORMANCE_SCHEMA、InnoDB、 Berkeley、Merge、Federated和Cluster/NDB等。这里主要讨论四个存储引擎：

（1）InnoDB存储引擎

MySQL存储引擎可以分为官方存储引擎和第三方存储引擎，InnoDB就是强大的第三方存储引擎，具备较好的性能和自动崩溃恢复特性，目前应用极为广泛，是当前MySQL存储引擎中的主流，它在事务型存储和非事务型存储中都很流行。InnoDB存储引擎支持事务、支持行锁、支持非锁定读、支持外键。

（2）MyISAM存储引擎

MyISAM存储引擎是MySQL官方提供的存储引擎，它在InnoDB出现并完善之前是MySQL存储引擎的主流，但目前逐渐被淘汰主要因为其不支持事务，这或许源于MySQL的开发者认为不是所有的应用都需要事务，所以便存在了这种不支持事务的存储引擎。

MyISAM不支持事务，不支持行级锁，支持表锁，支持全文索引，最大的缺陷是崩溃后无法安全恢复。

（3）3. Memory存储引擎

Memory存储引擎将表中数据放在内存中，因此速度非常快，但因其支持表锁，所以并发性能较差，最糟糕的是这个存储引擎在数据库重启或崩溃之后表中的数据将全部丢失，它只适用于存储临时数据的临时表，MySQL中一般使用这个存储引擎来存放查询的中间结果集，如MySQL自带的默认的information_schema库中就存在较多使用Memory存储引擎的表。

（4）Archive存储引擎

   Archive存储引擎置只支持INSERT和SELECT操作，支持行锁，但本身并不是事务安全的存储引擎，其最大的优点是其具有较好的压缩比，压缩比一般可达到1:10，可以将同样的数据以更小的磁盘空间占用来存储。

   Archive存储引擎非常适合存储归档数据，如历史数据、日志信息数据等等，这类数据往往数据量非常大，并且基本只有INSERT和SELECT操作，使用这个存储引擎可以非常节约磁盘空间。

1. Mysql中MyIsam与InnoDB的区别

①事物上：MyIsam强调性能，查询速度比InnoDB更快，但是不提供事物支持。InnoDB提供事物支持。

②外键：MyIsam不支持外键，InnoDB支持外键

③锁：MyIsam只支持表级锁，InnoDB支持行级锁和表级锁，默认行级锁

④全文索引：MyIsam支持全文索引，InnoDB不支持全文索引

⑤表主键：MyIsam允许没有主键，InnoDB如果没有主键会自动生成主键