Java知识回忆录（五）——数据库

1.数据库隔离级别有哪些，各自的含义是什么，Mysql默认的隔离级别是什么。

读未提交（Read uncommitted）一个事务可以读取另一个未提交事务的数据。

（Oracle默认）读提交（Read committed）一个事务要等另一个事务提交后才能读取数据。一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

（Mysql默认）重复读（Repeatable read）在开始读取数据（事务开启）时，不再允许修改操作。不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

串行读（Serializable 序列化）Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

serializable完全锁定字段，若一个事务来查询同一份数据就必须等待，直到前一个事务完成并解除锁定为止。是完整的隔离级别，会锁定对应的数据表格，因而会有效率的问题

2.什么是幻读。

不可重复读的重点是修改 :
       同样的条件 ,   你读取过的数据 ,   再次读取出来发现值不一样了
       幻读的重点在于新增或者删除
       同样的条件 ,   第 1 次和第 2 次读出来的记录数不一样 

3.Mysql有哪些存储引擎，各自优缺点。

Myisam：拥有较高的插入和查询速度，但是不支持事务，灾难恢复性差  5.5.5之前的默认引擎

InnoDB：支持事务，支持ACID，支持行级锁定，灾难恢复性高  5.5.5之后的默认引擎

Memory ：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失 。

Archive ：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive拥有高效的插入速度，但其对查询的支持相对较差 

CSV、Merge等

4.高并发下，如何做到安全的修改同一行数据。

使用悲观锁 悲观锁本质是当前只有一个线程执行操作，结束了唤醒其他线程进行处理。 
       也可以缓存队列中锁定主键。

5.乐观锁和悲观锁是什么，INNODB的标准行级锁有哪2种，解释其含义。

乐观锁是设定每次修改都不会冲突，只在提交的时候去检查（CAS），悲观锁设定每次修改都会冲突，持有排他锁。 
       行级锁分为共享锁和排他锁两种 共享锁又称读锁 排他锁又称写锁 

共享读锁，排他写锁

参看：https://blog.csdn.net/riemann_/article/details/90276432

6.SQL优化的一般步骤是什么，怎么看执行计划，如何理解其中各个字段的含义。

查看慢日志（show [session|gobal] status ），定位慢查询，查看慢查询执行计划根据执行计划确认优化方案 
Explain sql 
select_type:表示select类型。常见的取值有SIMPLE（简单表，即不使用连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（union中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。 
talbe：输出结果集的表。 
type:    表的连接类型。性能由高到底：system（表中仅有一行）、const（表中最多有一个匹配行）、eq_ref、ref、ref_null、index_merge、unique_subquery、index_subquery、range、idnex等 
possible_keys:查询时，可能使用的索引 
key:实际使用的索引 
key_len:索引字段的长度 
rows：扫描行的数量 
Extra：执行情况的说明和描述 

7.数据库会死锁吗。举一个死锁的例子，mysql怎么解决死锁。

产生死锁的原因主要是：

（1）系统资源不足。 
（2） 进程运行推进的顺序不合适。 
（3）资源分配不当等。

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。

产生死锁的四个必要条件：

（1） 互斥条件：一个资源每次只能被一个进程使用。 
（2） 请求与保持条件：一个线程因请求资源而阻塞时，对已获得的资源保持不放。 
（3） 不剥夺条件:   线程程已获得的资源，在末使用完之前，不能强行剥夺。 
（4） 循环等待条件:   若干线程之间形成一种头尾相接的循环等待资源关系。

这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。 
这里提供两个解决数据库死锁的方法：

1）重启数据库 
2）杀掉抢资源的进程： 
先查哪些进程在抢资源：SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 
杀掉它们：Kill trx_mysql_thread_id；

8.Mysql的索引原理，索引类型有哪些，如何创建合理的索引，索引如何优化。

索引是通过复杂的算法，提高数据查询性能的手段。从磁盘io到内存io的转变 
普通索引，主键，唯一，单列/多列索引建索引的几大原则 
1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式 
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录 
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
http://www.cnblogs.com/cq-home/p/3482101.html

多列索引只有在where条件中含有索引中的首列字段时才有效 

9.聚集索引和非聚集索引的区别。

“聚簇”就是索引和记录紧密在一起。 
         非聚簇索引 索引文件和数据文件分开存放，索引文件的叶子页只保存了主键值，要定位记录还要去查找相应的数据块。

• 聚集还是非聚集指的是 B+Tree 叶节点存的是指针还是数据记录
• MyISAM 索引和数据分离，使用的是非聚集索引
• InnoDB 数据文件就是索引文件，主键索引就是聚集索引

10.select for update是什么含义，会锁表还是锁行或是其他。

• 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。（只读，不可写，其他线程可以获得共享锁。）
• 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。（可读写，其他线程不可以获得任何锁）
• InnoDB的标准行级锁。

11.为什么要用Btree实现，它是怎么分裂的，什么时候分裂，为什么是平衡的。

B+是btree的变种，本质都是btree，btree+与B-Tree相比，B+Tree有以下不同点： 
       每个节点的指针上限为2d而不是2d+1。 
       内节点不存储data，只存储key；叶子节点不存储指针。

http://lcbk.net/9602.html 
       Btree 怎么分裂的，什么时候分裂，为什么是平衡的。 
       Key 超过1024才分裂B树为甚会分裂？ 因为随着数据的增多，一个结点的key满了，为了保持B树的特性，就会产生分裂，就向           红黑树和AVL树为了保持树的性质需要进行旋转一样！

12.数据库的ACID是什么。

数据库的4大特性。

原子性（ Atomicity ）事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

一致性（ Consistency ）

隔离性（ Isolation ）隔离性，并发事物间相互不影响，互不干扰。

持续性（ Durability ）已经提交的事物对数据库所做的更新必须永久保存。即便发生崩溃，也不能被回滚或数据丢失。

13.某个表有近千万数据，CRUD比较慢，如何优化。

分库分表操作。主要两种拆分 垂直拆分，水平拆分。

垂直拆分：数据表列的拆分。水平拆分：数据表行的拆分。

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

sharding jdbc   mycat

参看：https://blog.csdn.net/qq_37221991/article/details/87693639

14.Mysql怎么优化table scan的。

避免在where子句中对字段进行is null判断 
       应尽量避免在where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 
       避免在where 子句中使用or 来连接条件 
       in 和not in 也要慎用 
       Like查询（非左开头） 
       使用NUM=@num参数这种 
       where 子句中对字段进行表达式操作num/2=XX 
       在where子句中对字段进行函数操作

15.如何写sql能够有效的使用到复合索引。

由于复合索引的组合索引，类似多个木板拼接在一起，如果中间断了就无法用了，所以要能用到复合索引，首先开头(第一列)要用上，比如index(a,b) 这种，我们可以select table tname where a=XX 用到第一列索引 如果想用第二列 可以 and b=XX 或者and b like‘TTT%

第一个字段必须使用，坚持最左前缀匹配原则，尽量不要再前面使用范围查询

16.mysql中in和exists区别。

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。 
       如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： 
       not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。 
       1.EXISTS只返回TRUE或FALSE，不会返回UNKNOWN。

2.IN当遇到包含NULL的情况，那么就会返回UNKNOWN。

17.数据库自增主键可能的问题。

1.主键重复问题。导致数据库为单点，不可进行拆库。2.表锁导致阻塞的性能问题。3.自增主键不连续。当innodb_autoinc_lock_mode为0时候， 自增id都会连续，但是会出现表锁的情况，解决该问题可以把innodb_autoinc_lock_mode 设置为1，甚至是2。会提高性能，但是会在一定的条件下导致自增id不连续。

18.MVCC的含义，如何实现的。

MVCC(Multi Version Concurrency Control的简称)，代表多版本并发控制。与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。

MySQL从概念上可以分为四层，顶层是接入层，不同语言的客户端通过mysql的协议与mysql服务器进行连接通信，接入层进行权限验证、连接池管理、线程管理等。下面是mysql服务层，包括sql解析器、sql优化器、数据缓冲、缓存等。再下面是mysql中的存储引擎层，mysql中存储引擎是基于表的。最后是系统文件层，保存数据、索引、日志等。

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

参看：https://blog.csdn.net/riemann_/article/details/94838870

19.你做过的项目里遇到分库分表了吗，怎么做的，有用到中间件吗，比如sharding jdbc等，它的原理是什么。

20.Mysql的主从延迟怎么解决。

从库同步延迟情况出现的
● show slave status显示参数Seconds_Behind_Master不为0，这个数值可能会很大
● show slave status显示参数Relay_Master_Log_File和Master_Log_File显示bin-log的编号相差很大，说明bin-log在从库上没有及时同步，所以近期执行的bin-log和当前IO线程所读的bin-log相差很大
● mysql的从库数据目录下存在大量mysql-relay-log日志，该日志同步完成之后就会被系统自动删除，存在大量日志，说明主从同步延迟很厉害

MySQL数据库主从同步延迟是怎么产生的？当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，那么延时就产生了，当然还有就是可能与slave的大型query语句产生了锁等待。首要原因：数据库在业务上读写压力太大，CPU计算负荷大，网卡负荷大，硬盘随机IO太高次要原因：读写binlog带来的性能影响，网络传输延迟。

1)、架构方面

1.业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。

2.单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。

3.服务的基础架构在业务和mysql之间加入memcache或者redis的cache层。降低mysql的读压力。

4.不同业务的mysql物理上放在不同机器，分散压力。

5.使用比主库更好的硬件设备作为slave总结，mysql压力小，延迟自然会变小。