Java面试--数据库知识点

存储引擎

MyISAM和InnoDB的区别
1.是否支持行级锁：MyISAM只有表级锁，InnoDB支持行级锁和表级锁，默认为行级锁。
2.是否支持事务和奔溃后的安全恢复：MyISAM每次查询具有原子性，不支持事务。InnoDB支持事务，外部键等高级数据库功能。具有事务、回滚和奔溃修复能力的事务安全型表。
3.是否支持外键：MyISAM不支持，InnoDB支持。
4.是否支持MVCC：InnoDB支持，应对高并发事务，MVCC比单纯加锁更高效；MVCC只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作；MVCC可以使用乐观锁和悲观锁来实现。

索引

MyISAM：MyISAM使用B+树构建索引树时，叶子节点data域存放的是数据记录的地址。在索引检索的时候，首先按照B+树搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB：数据文件本身就是索引文件。叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键。其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址。所以根据主键索引搜索时，直接找到key所在的节点即可取出数据；根据辅助索引查找时，需要先取出主键的值，再走一遍主索引。

查询缓存的使用

执行查询语句的时候，会先查询缓存。8.0后移除，因为不实用。

什么是事务

事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务的四大特性

1.原子性:事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用。
2.一致性：执行事务后，数据库从一个正确状态变化到另一个正确状态。
3.隔离性：并发访问数据库时，一个用户的事务不被其他事务干扰。
4.持久性：一个事务被提交之后，它对数据库中数据的改变是持久的，即使数据库发生故障也不该对其有任何影响。

并发事务带来哪些问题

脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
丢失修改：指在一个事务读取一个数据时，另一个事务也访问了该数据，那么在第一个事务修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，所以叫丢失修改。
不可重复读：指在一个事务内多次读同一数据。在这个事务还未结束时，另一个事务也访问该数据。那么，在第一个事务的两次读数据之间，由于第二个事务的修改导致第一个事务两次读的数据可能不太一样。
幻读：和不可重复读类似。发生在一个事物读取了几行数据，接着另一个并发事务插入了一些数据时，在随后的查询中，第一个事务发现多了些原本不存在的记录。
不可重复读重点是修改，多次读一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除，比如多次读取一条记录发现记录增多或者减少了。

事务的隔离级别有哪些？MySQL的默认级别是什么

四个隔离级别：
READ-UNCOMMITTED（读取未提交）：最低的隔离级别，允许读取没有提交的数据变更，可能会导致脏读、幻读或者不可重复读。
READ-COMMITTED（读取已提交）：允许读取并发实物已经提交的数据，可以阻止脏读，但是幻读和不可重复读仍有可能发生。
REPEATABLE-READ（可重复读）：对同一字段的多次读取结果是一致的，除非数据是被本身事务自己修改的，可以阻止脏读和不可重复读，幻读仍有可能发生。
SERIALIZABLE（可串行化）：最高的隔离级别，完全服从事务的四大特性。事物之间依次逐个执行，不会产生干扰。
MySQL InnoDB存储引擎的默认支持的隔离级别是REPEATABLE-READ（可重复读），使用Next-key Lock锁算法，可以避免幻读，可以保证事务的隔离性要求。

锁机制与InnoDB锁算法

表级锁：对当前操作的整张表加锁，实现简单，资源消耗少，加锁快，不会出现死锁。触发锁冲突的概率最高，并发度最低，两种引擎都支持表级锁。
行级锁：只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，并发度高，但是锁的开销大，加锁慢，会出现死锁。
InnoDB存储引擎的三种锁算法：
Record lock:单个行记录上的锁
Gap lock:间隙锁，锁定一个范围，不包括记录本身
Next-key lock:锁定一个范围，包含记录本身

大表优化

1.限定数据的范围
禁止不带任何限制数据范围条件的查询语句。
2.读/写分离
主库负责写，从库负责读。
3.垂直分区：
指数据表列的拆分，把一张列比较多的表拆分为多张表。
优点：使列数据变小，在查询时减少读取的block数，减少I/O次数。
缺点：主键出现冗余，引起jion操作。
4.水平分区：数据表行的拆分。水平拆分能支持很大的数据量存储，但是分片事务难以解决。数据库分片两种常见方案：
客户端代理：分片逻辑在应用端，封装在jar包中，通过封装或者修改JDBC层来实现。
中间件代理：在应用和数据之间加了一个代理层。分片逻辑统一维护在代理层中。

解释一下什么是池化设计思想？什么是数据库连接池？为什么需要数据库连接池？

我们常见的java线程池、jdbc连接池、redis连接池等就是池化设计思想的体现。池化设计会抵消每次获取资源的消耗，比如创建线程的开销，获取远程连接的开销等。池化设计还包括如下特征：池子的初始值、活跃值、最大值等，这些特征可以直接映射到Java线程池和数据库连接池的成员属性中。
数据库连接池可以看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，昂贵又浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。连接池还减少了用户必须等待建立与数据库的连接的时间。

分库分表之后，id主键如何处理

分成多个表之后，每个表都是从1开始累加，这样不对，需要一个全局唯一的id来支持。
生成全局id有以下几种方式：
UUID：不适合作为主键，太长而且无序不可读，查询效率低。适合用于生成唯一的名字标识比如文件的名字。
数据库自增id：两台数据库分别设置不同步长，生成不重复的id的策略实现高可用。这种方式生成的id有序，但是需要独立部署数据库实例，成本高。
利用Redis生成id：性能好，灵活方便不依赖于数据库。但是，引入新的组件造成系统更复杂，可用性降低，增加系统成本。

一条SQL语句在MySQL中如何执行的

MySQL主要分为Server层和引擎层，Server层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块(binlog)，这个日志模块所有执行引擎都可以共用，redolog只有InnoDB有。
引擎是插件式的，目前主要包括，MyISAM,InnoDB等。
SQL执行过程分为两类：
1.查询：权限检验–查询缓存–分析器–优化器–权限检验–执行器–引擎
2.更新：分析器–权限检验–执行器–引擎–redolog prepare–binlog–redolog commit

MySQL高性能优化规范建议

索引设计规范：
1.限制每张表上的索引数量，建议单张表索引不超过5个
索引可以增加查询效率，同样也会降低插入和更新的效率。因为优化器在选择如何优化查询时，会对每一个可以用到的索引来进行评估，以生成一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加优化器生成执行计划的时间，同样会降低查询性能。
2.禁止给表中每一列都建立单独的索引
5.6之后虽然有了合并索引的优化方式，但是还远远没有使用一个联合索引的查询方式好。
3.每个Innodb表必须有个主键
Innodb是按照主键索引的顺序来组织表的：
不要用更新频繁的列作为主键，不适合用多列主键
不要用UUID,HASH，字符串列作为主键（无法保证数据的顺序增长）
主键建议使用自增Id值
4.常见索引列建议
出现在SELECT\UPDATE\DELETE语句的WHERE从句中的列
包含在ORDER BY\GROUP BY中的字段
不要讲符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引
多表join的关联列
5.怎么选择索引列的顺序
建立索引的目的是：通过索引进行数据查找，减少随机IO，增加查询性能，索引能过滤出越少的数据。
区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
字段长度小的列放在联合索引的最左端（字段长度越小，一页能存储的数据量越大）
使用最频繁的列放在联合索引的左端（可以比较少的建立一些索引）
6.避免建立冗余索引和重复索引，会增加优化器生成执行计划的时间
7.对于频繁的查询优先考虑使用覆盖索引
覆盖索引就是包含了所有查询字段（where，select，order by，group by包含的字段）的索引.
优点：
避免Innodb表进行索引的二次查询：Innodb是以聚集索引的顺序来存储的，对于
Innodb来说，二级索引在叶子结点中保存的是主键信息，二级索引查询数据的话，在查找到相应键值后，还要通过主键进行二次查询才能获得想要的数据。在覆盖索引中，二级索引的键值中就可以获取想要的鹅数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。
可以把随机IO变成顺序IO加快查询效率：由于覆盖索引是按照键值顺序存储的，对于IO密集型范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多。所以利用覆盖索引在访问时也可以把磁盘的随机访问的IO转变成索引查找的顺序IO。
8.索引SET规范
尽量避免使用外键约束
不建议使用外键约束，但一定要在表与表之间的关联键上建立索引
外键可用于保证数据的参照完整性，但建议在业务端实现
外键会影响父表和子表的写操作从而降低性能
数据库SQL开发规范
1.建议使用预编译语句进行数据库操作
预编译语句可以减少SQL编译使用的时间，还可以解决动态SQL带来的SQL注入问题。
只传参数，比传递SQL语句更高效。
相同语句可以一次解析，多次使用，提高处理效率。
2.避免数据类型的隐式转换
隐式转换会导致索引失效
3.充分利用表上已经存在的索引
避免使用双%的查询条件。
一个SQL只能用到复合索引中的一列进行范围查询。比如有a,b,c列的联合索引，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到。
在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧，使用left join或者not exists来优化Not in操作，因为not in也会使索引失效。
6.禁止使用SELECT*必须使用SELECT<字段列表>查询
会消耗更多的CPU和IO
无法使用覆盖索引
可以减少表结构变更带来的影响
8.避免使用子查询，可以把子查询优化为join操作
通常子查询在in子句中，而且子查询中为简单SQL时，才可以把子查询转换为关联查询进行优化。
子查询性能差的原因：子查询的结果无法使用索引，通常子查询的结果集会被存储到临时表中，无论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。特别是对于返回结果集比较大的子查询。
由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。
9.避免使用JOIN关联太多的表
对于MYSQL来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。
对于同一个SQL多关联一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，占用的内存也就越大。
多表关联join_buffer_size设置也不合理的话，就容易造成服务器内存溢出的情况，影响服务器数据库性能稳定性。
10.减少同数据库的交互次数
数据库更适合处理批量操作，合并多个相同操作在一起。
11.对应同一列进行or判断时，使用in代替or
in操作可以更有效的利用索引，or大多数情况下很少能利用索引。
12.禁止使用order by rand()进行随机排序
会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO以及内存资源。
13.WHERE从句中禁止对列进行函数转换和计算
14.在明显不会有重复值时使用UNION ALL而不是UNION
UNION会把两个结果集的所有数据放到放到临时表中后再进行去重操作。
UNION ALL不会再对结果集进行去重操作
15.拆分复杂的大SQL为多个小SQL
大SQL逻辑上比较复杂，需要占用大量的CPU进行计算的SQL
一个SQL只能用一个CPU进行计算
SQL拆分后可以通过并行执行来提高效率
数据库操作行为规范
1.超100万行的批量写（UPDATE,DELETE,INSERT）操作，要分批多次进行操作
大批量的操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况。
binlog日志为row格式时会产生大量的日志
大批量写操作会产生大量的日志，一次修改的数据越多，产生的日志量越多，日志的传输和恢复所需要的时间就越长，这也是造成主从延迟的原因。
避免产生大事务操作
大批量修改数据，一定是在一个事务中进行的，会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对数据库性能产生很大的影响。
特别是长时间的阻塞会占满所有数据库的可用连接，会使得生产环境中其他应用无法连接到数据库，因此一定要注意大批量写操作要分批。
2.对于大表使用pt-online-schema-change修改表结构
避免大表修改产生的主从延迟
避免在对表字段进行修改时进行锁表
pt-online-schema-change它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，再把原表中的数据复制到新表中，在原表中增加一些触发器。把原表中新增的数据也复制到新表中，在所有数据复制完成后，把新表命名为原表，把原来的表删除掉。把原来的一个DDL操作，分解成多个小的批次进行。
3.禁止为程序使用的账号赋予super权限。
•当达到最大连接数限制时，还运行 1 个有 super 权限的用户连接
•super 权限只能留给 DBA 处理问题的账号使用
4. 对于程序连接数据库账号,遵循权限最小原则
•程序使用数据库账号只能在一个 DB 下使用，不准跨库
•程序使用的账号原则上不准有 drop 权限

一条SQL语句执行很慢的原因是什么

分情况讨论：
1.大多数情况正常，偶尔很慢
(1)数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
(2)执行的时候，遇到锁，如表锁、行锁。
2、这条 SQL 语句一直执行的很慢，则有如下原因。
(1)没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
(2)数据库选错了索引。