java面试之数据库

1. MySQL:
   MySQL常见的两种存储引擎：MyISAM与InnoDB
   表和数据库的区别:
   表是一种表示数据库中数据划分的方式，而数据库是表和数据的集合。
   表用于将相互关联的数据分组并创建数据集。
   该数据将在数据库中使用。以任何形式存储在表中的数据都是数据库的一部分.
   数据库是有组织数据的集合，也是用于访问这些数据的功能，
   而表是用于存储数据的行和列的集合。
   从这几个方面考虑优化:
   a.SQL与索引; b.存储引擎与表结构;
   c.数据库架构; d.MySQL配置; e.硬件与操作系统.

1.查询很慢可以从以下几个方面分析:
先通过慢查询日志找到对应的SQL; 然后explain SQL查看对应的执行计划;
1）如果是由于没有命中索引导致全表扫描可以建立索引
2）如果是由于数据量太大，可以根据业务场景看是否需要分表(比方说查询历史
订单，可以按季度划分等)
3）如果优化后还是很慢，还可以根据业务场景看看这个数据是否可以做缓存
4）如果是连接数的问题，可以加大服务器端的连接数，并且在客户端使用连接池
(建议连接池大小=2*CPU核数+1)

2.对于数据库日常的调优，应该怎么做？
1）从数据库连接环节解决：数据库服务端可以适度扩大最大连接数或者及时释放不活跃的连接；数据库客户端可以使用连接池，实现连接的重复使用，连接池不是越大越好，太大了反而影响查询效率，因为每个连接都需要一个线程维护，增加线程数，反而增加了CPU压力
2）从查询缓存环节解决：可以使用第三方缓存技术如Redis非关系型数据库来提高查询效率；
如果读写压力比较大的时候，
可以考虑使用集群技术实现主从复制来分担读写压力
在数据库层面，可以使用GTID 复制的方式解决主从复制延迟的问题
在应用层面，可以使用分库分表的方式减少主从同步延迟的方法
垂直分库：可以减少并发压力
水平分库：可以解决存储瓶颈
3）从查询优化器环节解决：
打开慢查询日志，该日志会记录SQL执行情况
使用SHOW PROFILE可以查看SQL语句执行的时候使用的资源，比如CPU、IO的消耗情况。
使用SHOW STATUS 查看 MySQL 服务器运行状态信息
使用show engine显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；buffer pool统计信息
通过EXPLAIN 我们可以模拟优化器执行SQL 查询语句的过程，来知道 MySQL 是怎么处理一条SQL语句的。通过这种方式我们可以分析语句或者表的性能瓶颈
创建合适的索引优化
4）从存储引擎环节解决：
为不同的业务表选择不同的存储引擎，例如：查询插入操作多的业务表，用MyISAM。临时数据用Memeroy。常规的并发大更新多的表用InnoDB
在定义字段的时候，使用可以正确存储数据的最小数据类型，为每一列选择合适的字段类型。
5)数据库架构方面: 主从复制, 读写分离, 在主服务器Master只负责读操作, 在从库Slave上操作增删改; 分摊访问压力;
垂直分库分表: 把一个数据库按照业务拆分成多个数据库;
水平分库分表: 表结构相同数据量不同, 把数据拆分成多个库存储.
表结构优化: 字段长度的预估, 文件图片存储相对路径

1.1.客户端连接到服务端: 并发情况下的连接数太多
客户端可以使用连接池, 不用太大一般CPU2+1即可;
服务端可以合理设置连接数show variables like ‘max_connections’和time_out设置超时时间;
1.2.缓存服务, 为减轻数据库压力, 可以缓存到第三方如Redis非关系型数据库;
1.3.主从复制, 读写分离, 在主服务器Master只负责读操作, 在从库Slave上操作增删改;
分摊访问压力, 主从复制-mysql异步复制, 解决延时可以全同步复制但事务时间增加,可以半同步复制只要 有一个slave写入即返回给客户端
1.4.分库分表:
垂直分库分表: 把一个数据库按照业务拆分成多个数据库;
水平分库分表: 表结构相同数据量不同, 把数据拆分成多个库存储;
1.5.slow_query和long_query默认10s, 慢日志查看SQL语句;
1.6.表结构优化: 字段长度的预估, 文件图片存储相对路径
2. 怎么优化数据库的查询？
查询中用到的关键词的顺序依次为 :
select–from–where–group by–having–order by;
select子句–少用号，尽量取字段名称:
使用列名意味着将减少消耗时间;
from 子句–执行顺序为从后往前、从右到左:
表名(最后面的那个表名为驱动表，执行顺序为从后往前, 所以数据量较少的表尽量放后, 你必须选择记录条数最少的表作为基础表。如果有3 个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表）;
where子句–执行顺序为自下而上、从右到左:
ORACLE 采用自下而上从右到左的顺序解析Where 子句,根据这个原理,表之间的连接必须写在其他Where 条件之前, 可以过滤掉最大数量记录的条件必须写在Where 子句的末尾;
group by–执行顺序从左往右分组:
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。即在GROUP BY前使用WHERE来过虑，而尽量避免GROUP BY后再HAVING过滤;
having 子句----很耗资源，尽量少用:
这个处理需要排序,总计等操作.
order by子句–执行顺序为从左到右排序,很耗资源
使用explain优化sql和索引:
使用方式如下：EXPLAIN +SQL语句
通过EXPLAIN，我们可以分析出以下结果：
• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询
查询返回的值说明:
table：显示这一行的数据是关于哪张表的;
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL; type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL: 一般来说，得保证查询至少达到range级别，最好能达到ref。
possible_keys：显示可能应用在这张表中的索引。
key： 实际使用的索引。
extra列返回的描述的意义: Using filesort: 看到这个的时候，查询就需要优化了。Using temporary 看到这个的时候，查询需要优化了。
对查询的SQL进行优化:

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 应尽量避免在 where 子句中使用!=或<>操作符。
3. 应尽量避免在 where 子句中对字段进行 null 值判断。
4. 应尽量避免在 where 子句中使用 or 来连接条件。
5. 下面的查询也将导致全表扫描：“select id from t where name like ‘%abc%’” 若要提高效率，可以考虑全文检索。
6. in 和 not in 也要慎用，否则会导致全表扫描，很多时候用 exists 代替 in 是一个好的选择。
7. 应尽量避免在 where 子句中对字段进行表达式操作。
8. 应尽量避免在where子句中对字段进行函数操作。

3. 在数据库中条件查询速度很慢的时候,如何优化?
4. 建索引(索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。)
5. 减少表之间的关联
6. 优化sql，尽量让sql利用索引定位数据，不要让sql做全表查询
7. 简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据
8. 利用分页查询减少返回的数据量
9. 数据库中事务是什么，有哪些特性？
   1.事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序单元(unit)。
   2.事务通常由SQL语言或编程语言发起并控制
   特性:
   1.事务是恢复和并发控制的基本单位
   2.事务应该具有4个属性：原子性、一致性、隔离性、持久性。
   ACID特性: Atomicity , Consistency , Isolation , Durability
   1、原子性：一个事务中的一系列操作要么全部成功，要么全部失败回滚。
   2、一致性：事务开始前和结束后，数据库的完整性约束没有被破坏。事务必须是使数据库从一个一致性状态变到另一个一致性状态。
   3、隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，多个并发事务之间要相互隔离。
   4持久性：持久性也称永久性，指一个事务一旦被提交，它对数据库中数据的改变就应该是永久性的。即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
   事务的并发问题:
   1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
   2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。
   3、幻读：幻读解决了不重复读，保证了同一个事务里，查询的结果都是事务开始时的状态（一致性）。
   例如：事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作 这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。 而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有跟没有修改一样，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
   小结：不可重复读侧重于修改，幻读侧重于新增或删除。
   解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。
   MySQL 标准定义的四个隔离级别为：

1. read_uncommited ：读到未提交数据数据脏读
2. read_committed：允许读取并发事务已经提交的数据, 对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。但是容易导致幻读
3. repeatable_read：MySQL默认的事务隔离级别. 在同一个事务中对同一字段的多次读取结果都是一致的,可重读会有幻读
4. serializable ：最高的隔离级别，完全服从ACID的隔离级别。串行化不会产生任何异常。
   Mysql 默认采用的 REPEATABLE_READ隔离级别
   Oracle 默认采用的 READ_COMMITTED隔离级别.
   嵌套事务:
   嵌套是子事务套在父事务中执行，子事务是父事务的一部分，在进入子事务之前，父事务建立一个回滚点，叫save point，然后执行子事务，这个子事务的执行也算是父事务的一部分，然后子事务执行结束，父事务继续执行。重点就在于那个save point。
   1. 如果子事务回滚，父事务会回滚到进入子事务前建立的save point;
   2. 如果父事务回滚，子事务也会跟着回滚！
   3. 事务的提交，子事务先提交，父事务再提交, 子事务是父事务的一部分!

5. 数据库有哪几种约束类型？
   主键约束 PRIMARY KEY, 外键约束 FOREIGN KEY,
   唯一约束 UNIQUE, 检查约束 CHECK, 非空约束 NOT NULL

6. 数据库建立索引常用的原则是什么？什么情况下不适合建立索引？
   定义: 索引是对数据库表中一列或者多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息
   索引的分类:主键索引,唯一索引,常规索引,全文索引
   对于有些列不应该创建索引：

7. 对于那些只有很少数据值的列也不应该增加索引，由于这些列的取值很少，例如人事表的性别列.

8. 很少或从不引用的字段和逻辑型的字段等不要建立索引;

9. 对于那些定义为text, image和bit数据类型的列不应该增加索引, 这些列的数据量要么相当大，要么取值很少。

10. 修改性能和检索性能是互相矛盾的。当修改性能远远大于检索性能时，不应该创建索引。
    一般来说，以下的栏位都必须记得加上索引：

    1. 唯一、不为空、经常被查询的字段.非常适合建立索引;
    2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
       3、在经常用在连接的列上，关联查询的外键字段要加索引(Foreign key)
       4、会被排序的栏位(被放在order方法中), 利用索引的排序，加快排序查询时间;
       5、在经常需要根据范围进行搜索的列上创建索引，，因为索引已经排序，其指定的范围是连续的；
       6、会被group的栏位(被放在group方法中). 会被查询的栏位(被放在where方法中)
       写少读多就多建索引, 写多读少就少建索引, 数据量少就不建索引。

索引的优点:
创建索引的目的就是加快检索表中的数据的速度,也就是查询数据的速度,提高数据库性能.
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

增加索引也有许多不利的方面(缺点)：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

7.与 Oracle 相比，Mysql 有什么优势？
1.Mysql 是开源软件，随时可用，无需付费; 2.Mysql 是便携式的, 可移植的, 在笔记本上使用不到1M的RAM; 3.带有命令提示符的 GUI; 4.使用 Mysql 查询浏览器支持管理
8.CHAR 和 VARCHAR 有什么区别？
1.CHAR 是可变长度的，而 VARCHAR 是固定长度的。
2.CHAR 和 VARCHAR 类型在存储和检索中不同。
3.CHAR 列的长度固定为创建表时声明的长度。长度范围是 1 到 255。
4.当使用空格将 CHAR 值右填充到特定长度时存储 CHAR 值。检索 CHAR 值时会删除尾随空格。
5.CHAR 使用静态内存分配，而 VARCHAR 使用动态内存分配。
6.CHAR 比 VARCHAR 快 50%。
9.MySQL 中的 TRUNCATE 和 DELETE 有什么区别？
1.TRUNCATE 是一个 DDL 命令，DELETE 是一个 DML 命令。
2.不能将 Where 命令与 TRUNCATE 一起使用，但可以将其与 DELETE 命令一起使用。
3.TRUNCATE 不能与索引视图一起使用，而 DELETE 可以与索引视图一起使用。
4.DELETE 命令用于从表中删除数据。它只删除表中的数据行，而 TRUNCATE 是非常危险的命令，应谨慎使用，因为它会永久删除表中的每一行。
10.内存表和临时表有什么区别
内存表：
内存中存在的表。
它们被临时用于高速存储。
它们不允许 BLOB 或文本字段。
不支持 AUTO_INCREMENT。索引不应为空。
临时表：
临时表用于保留临时数据。有时在某些情况下保存临时数据会很有用。当前客户会话终止后，将删除临时表。
主要区别：
内存表在客户端之间共享，而临时表不共享。
内存表只是一个存储引擎，而对于临时表，则需要特殊权限（创建临时表）。
11.什么是存储过程？用什么来调用？
存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。
调用：
1）可以用一个命令对象来调用存储过程。
2）可以供外部程序调用，比如：java程序。
12.存储过程的优缺点？
优点：
1）存储过程是预编译过的，执行效率高。
2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
3）安全性高，执行存储过程需要有一定权限的用户。
4）存储过程可以重复使用，可减少数据库开发人员的工作量。
缺点：移植性差
13.存储过程和函数的区别？
　　1、存储过程用户在数据库中完成特定操作或者任务（如插入，删除等），函数用于返回特定的数据。
　　2、存储过程声明用procedure，函数用function。
　　3、存储过程不需要返回类型，函数必须要返回类型。
　　4、存储过程可作为独立的pl-sql执行，函数不能作为独立的plsql执行，必须作为表达式的一部分。
　　5、存储过程只能通过out和in/out来返回值，函数除了可以使用out，in/out以外，还可以使用return返回值。
　　6、sql语句（DML或SELECT)中不可用调用存储过程，而函数可以。
相同点：1.创建语法结构相似，都可以携带多个传入参数和传出参数。
　　　　 2.都是一次编译，多次执行。
不同点：1.存储过程定义关键字用procedure，函数定义用function。
　　　　　　2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。
　　　　　　3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。
总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。
14.什么叫视图？游标是什么？
视图：
是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。
游标：
是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。
15.视图的优缺点
优点：
1对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
2)用户通过简单的查询可以从复杂查询中得到结果。
3)维护数据的独立性，试图可从多个表检索数据。
4)对于相同的数据可产生不同的视图。
缺点：
性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据
16.drop、truncate、 delete区别
最基本：
• drop直接删掉表。
• truncate删除表中数据，再插入时自增长id又从1开始。
• delete删除表中数据，可以加where字句。
17.什么是临时表，临时表什么时候删除?
临时表可以手动删除：
DROP TEMPORARY TABLE IF EXISTS temp_tb;
临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表，并且操作属于本连接的临时表。
创建临时表的语法与创建表语法类似，不同之处是增加关键字TEMPORARY
18.非关系型数据库和关系型数据库区别，优势比较?
非关系型数据库的优势：
• 性能：NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
• 可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
关系型数据库的优势：
• 复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
• 事务支持：使得对于安全性能很高的数据访问要求得以实现。
其他：
1.对于这两类数据库，对方的优势就是自己的弱势，反之亦然。
2.NOSQL数据库慢慢开始具备SQL数据库的一些复杂查询功能，比如MongoDB。
3.对于事务的支持也可以用一些系统级的原子操作来实现例如乐观锁之类的方法来曲线救国，比如Redis set nx。
19.什么是 内连接、外连接、交叉连接、笛卡尔积等?
内连接: 只连接匹配的行
左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行
例如1：
SELECT a.,b. FROM luntan LEFT JOIN usertable as b ON a.username=b.username
例如2：
SELECT a.,b. FROM city as a FULL OUTER JOIN user as b ON a.username=b.username
全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
交叉连接: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配
例如：
SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type
20.count()、count(1)、count(column)的区别
• count()对行的数目进行计算,包含NULL
• count(column)对特定的列的值具有的行数进行计算,不包含NULL值。
• count()还有一种使用方式,count(1)这个用法和count()的结果是一样的。
性能问题:
1.任何情况下SELECT COUNT() FROM tablename是最优选择;
2.尽量减少SELECT COUNT() FROM tablename WHERE COL = ‘value’ 这种查询;
3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
• 如果表没有主键,那么count(1)比count()快。
• 如果有主键,那么count(主键,联合主键)比count()快。
• 如果表只有一个字段,count()最快。
count(1)跟count(主键)一样,只扫描主键。count(*)跟count(非主键)一样,扫描整个表。明显前者更快一些。
21.mysql都有什么锁，死锁判定原理和具体场景，死锁怎么解决?
MySQL有三种锁的级别：页级、表级、行级。
什么是死锁？
死锁: 是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程。
表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。
死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。
那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。
死锁的解决办法?
1.查出的线程杀死 kill
SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;
2.设置锁的超时时间
Innodb 行锁的等待时间，单位秒。可在会话级别设置，RDS 实例该参数的默认值为 50（秒）。
生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值
该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：
set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒。
3.指定获取锁的顺序
22.mysql 高并发环境解决方案?
MySQL 高并发环境解决方案： 分库 分表 分布式 增加二级缓存。。。。。
需求分析：互联网单位 每天大量数据读取，写入，并发性高。
现有解决方式：水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力。
集群方案：解决DB宕机带来的单点DB不能访问问题。
读写分离策略：极大限度提高了应用中Read数据的速度和并发量。无法解决高写入压力。
23.Redis?