后端开发面试题---数据库篇

事务的四个特性：

A：原子性，事务是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

C：一致性，一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。这是说数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。

I：隔离性，多个事务并发访问时，事务之间是隔离的，一个事务不应该影响其它事务运行效果。

D：持久性，意味着在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

事务的隔离级别：

读已提交：（脏读、不可重复读、幻读）

读未提交：（不可重复读、幻读）

可重复读：（幻读）

串行化

幻读是什么，数据库如何处理幻读，如何解决幻读？

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

innodb引擎使用临键锁了幻读问题，也称为Next-Key Locking

主键索引和唯一索引有什么区别

主键索引是唯一性索引，唯一性索引并不一定是主键

主键列在创建时，默认 空值 + 唯一索引

一个表最多只能创建一个主键，可以创建多个唯一索引

主键索引上存储数据信息，唯一索引存储主键信息

慢查询优化处理

1.sql是否走索引，如果没有走索引，考虑加入索引，使用explain查看sql执行过程。

explain指令各个字段含义：

1.id

sql执行序列号，表明sql中各个语句的执行顺序。

2.select_type

select类型，它有以下几种值

2.1 simple 它表示简单的select,没有union和子查询

2.2 primary 最外面的select,在有子查询的语句中，最外面的select查询就是primary,上图中就是这样

2.3 union union语句的第二个或者说是后面那一个.现执行一条语句，explain
select  *  from uchome_space limit 10 union select * from uchome_space limit 10,10

会有如下结果

第二条语句使用了union

2.4 dependent union    UNION中的第二个或后面的SELECT语句，取决于外面的查询

2.5 union result        UNION的结果,如上面所示

还有几个参数，这里就不说了，不重要

3 table

输出的行所用的表，这个参数显而易见，容易理解

4 type

连接类型。有多个参数，先从最佳类型到最差类型介绍 重要且困难

4.1 system

表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计

4.2 const

表最多有一个匹配行，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快

记住一定是用到primary key 或者unique，并且只检索出两条数据的 情况下才会是const,看下面这条语句

explain SELECT * FROM `asj_admin_log` limit 1,结果是

虽然只搜索一条数据，但是因为没有用到指定的索引，所以不会使用const.继续看下面这个

explain SELECT * FROM `asj_admin_log` where log_id = 111

log_id是主键，所以使用了const。所以说可以理解为const是最优化的

4.3 eq_ref

对于eq_ref的解释，mysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。看下面的语句

explain select * from uchome_spacefield,uchome_space where uchome_spacefield.uid = uchome_space.uid

得到的结果是下图所示。很明显，mysql使用eq_ref联接来处理uchome_space表。

目前的疑问：

       4.3.1 为什么是只有uchome_space一个表用到了eq_ref,并且sql语句如果变成

       explain select * from uchome_space,uchome_spacefield where uchome_space.uid = uchome_spacefield.uid

       结果还是一样，需要说明的是uid在这两个表中都是primary

4.4 ref 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

看下面这条语句 explain select * from uchome_space where uchome_space.friendnum = 0，得到结果如下，这条语句能搜出1w条数据

4.5 ref_or_null 该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

上面这五种情况都是很理想的索引使用情况

4.6 index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

4.7 unique_subquery 

4.8 index_subquery

4.9 range 给定范围内的检索，使用一个索引来检查行。看下面两条语句

explain select * from uchome_space where uid in (1,2)

explain select * from uchome_space where groupid in (1,2)

uid有索引，groupid没有索引，结果是第一条语句的联接类型是range,第二个是ALL.以为是一定范围所以说像 between也可以这种联接,很明显

explain select * from uchome_space where friendnum = 17

这样的语句是不会使用range的，它会使用更好的联接类型就是上面介绍的ref

4.10 index     该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

4.11  ALL  对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记 const的表，这通常不好，并且通常在它情况下 很差。通常可以增加更多的索引而不要使用 ALL，使得行能基于前面的表中的常数值或列值被检索出。

5 possible_keys 提示使用哪个索引会在该表中找到行，不太重要

6 keys MYSQL使用的索引，简单且重要

7 key_len MYSQL使用的索引长度

8 ref   ref列显示使用哪个列或常数与key一起从表中选择行。

9 rows 显示MYSQL执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引

10 Extra  该列包含MySQL解决查询的详细信息。

Using index	mysql将使用覆盖索引，以避免访问表
Using where	mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引
Using filesort	mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成
using temporary	为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时
range checked for each record	没有找到合适的索引

驱动表的定义

wwh999 在 2006年总结说，当进行多表连接查询时， [驱动表] 的定义为：
1）指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；
2）未指定联接条件时，行数少的表为[驱动表]（Important!）。

永远用小结果集驱动大结果集

今天学到了一个很重要的一点：当不确定是用哪种类型的join时，让mysql优化器自动去判断，我们只需写select * from t1,t2 where t1.field = t2.field

通俗解释：select  * from table order by field

其中filed建普通索引，这种情况会使用到using temporary,因为虽然这时候使用到了索引，但因为扫描的是全表，mysql优化器会判断：反正是搜索全表而且要排序，因为这时候要回行，我还不如不沿着索引找数据，直接全部检索出所有数据来在排序。如果语句这么写 select * from table where field > 1 order by field.mysql优化器就会这么判断：这时候不是搜全表，我需要先根据where条件,沿着索引树搜出想要的相应索引数据，在回行（一边找索引一边回行）。这时候就不需要临时表了

10.7 using where

WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。（这个说明不是很理解，因为很多很多语句都会有where条件，而type为all或index只能说明检索的数据多，并不能说明错误，useing where不是很重要，但是很常见）

如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。

10.8 Using sort_union(...) , Using union(...) , Using intersect(...)

这些函数说明如何为index_merge联接类型合并索引扫描

10.9 Using index for group-by

类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

实例讲解

通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。

联合索引，避免回表

众所周知，联合索引的叶子节点存储了索引字段的值，因此，合理的设计联合索引，可以避免回表操作。比如：我的表中有四十个字段，但是查询频率比较高的只有a，b，c三个字段，这时候，如果我想进一步提升效率，我可以将abc三个字段设计一个联合索引，查询的速度就会有进一步提升。

特殊优化，深度分页

深度分页： 指的是在分页查询的时候，页数标记的特别高（如查询第10000页的数据），这样，如果正常查询的sql会出现 select * from table_name limit 10000,10 的sql出现，会造成极大的性能问题。因此深度分页问题需要有一定的优化。

方案一：从业务方面搞定，因为很多业务根本用不到那么多分页，那么就可以限制最大分页的页数，比如百度的最大显示页码就只有76页。
业务方面如果搞不定，只能通过技术手段搞定，那么我们就分析一下问题出在哪？

我们能够想到，性能瓶颈出在了limit页码过大的问题上，我们知道，索引的结构（B+树）有一个特性，就是叶子节点之间依靠双向链表连接，这个特性主要是针对范围查询做的优化，因此在进行分页查询的时候，我们会直接通过链表进行查询，问题就出在了这里，由于页码过大，而且查询字段过多，每次查询时候需要回表，所以链表在遍历的时候时间过长，造成了性能瓶颈。因此引申出了下面两个优化方案。

方案二：分页查询时将id作为查询条件传过来，然后往后查十条，这样可以先通过索引锁定到第一条数据，然后通过链表往后遍历十条数据，从而达到优化的效果。
如果你的已有的方案不是那么方便改，可以考虑下边的方案。

方案三：在sql中先分页查询到id（不需回表查询速度较快），然后在进行表关联进行分页查询，具体sql如下（类似）：

select table.* from table_name as table inner join ( select id from table limit 3000000,10 ) as tmp on tmp.id=table.id;
1

突破瓶颈，分库分表

分库
每个数据库是有一个最大连接数的，超过这个连接数会查询超时，所以，在你的项目的并发量超过一定量级之后，你就需要考虑分库了，至于具体分多少个库，看实际QPS量有多少，一个库默认的连接数是100，按照具体QPS推算需要分出多少个库。

分表
分表又分为水平分表和垂直分表。
水平分表： 是将一张表的字段拆分到两张表上，从而达到单表数据存储量降低的目的。一般应用的场景都是按照业务含义拆分，可以拆分成多张一对一的表，各个表通过主键关联，比如将sku表拆分成sku基础信息表，sku扩展表，sku状态表，等等。
垂直分表： 是单表数据过多后，会导致数据查询时候读盘次数增加，从而查询效率降低，这时候就考虑将数据分到多张表上，通常做法是确定一个拆分键，将拆分键进行一定的算法（比如：hash运算后按照分表数取模），最终落到某张表中。
基本上单表量级超过两千万就可以考虑分表了，分多少表按照 最大量级除以500-1000万 左右数据计算。至于为什么最大阈值是两千万，可以看下 mysql B+树 索引机制 及 InnoDB一棵B+树可以存放多少行数据？ 深入了解一下。

削峰操作，临时缓存

有一种慢查询的情况是出在了一种峰值查询的时候，就是有时候会出现某个时间段有些业务几种出，但是定时任务过于集中，导致某一个短暂的时间段QPS暴增，从而数据库扛不住压力，出现了慢查询甚至于数据库崩溃，由于是峰值的问题，扩容机器又很浪费，不扩容又扛不住压力，为了避免这类问题，可以考虑引入缓存机制进行削峰操作，通过缓存机制短暂的保存数据库信息，让峰值的请求都压在缓存上，从而间接的避免了慢查询的情况。

当然，无论是使用缓存中间件Redis，或者内存中间件MemoryCache都可以解决这类问题。

以慢打快，异步处理

同样是慢查询出现定时任务批量处理数据的情况，但是定时任务过于集中，导致某一个短暂的时间段QPS暴增，这时候更好的方案是通过mq异步处理数据，通过控制mq的消费密度来减轻qps，从而达到减轻数据库的压力，以时间换空间，以慢打快，效果奇好。

更换思路，曲线救国

如果是在查询效率提升不上去，就考虑更换查询中间件吧，使用大数据的处理方式，比如，落ElasticSearch查询。

mysql中一条sql是如何执行的？

mysql架构上主要分为三层

client层，提供给外部服务进行连接，比如java服务可通过jdbc连接来执行sql语句。

server层，解析sql语句，对sql进行优化，生成sql执行计划。

引擎层，根据sql执行计划查询实际存储的数据，返回给server层。

sql语句的执行过程：

1.sql语句通过客户端来提交到server层

2.server层会通过连接器来建立连接，进行权限校验等操作

3.拿到sql后，server层会通过解析器来进行sql解析，主要有词法解析和语法解析，词法解析会将sql语句进行分词，将每个单词解析成mysql可识别的词法单元，语法解析则会将词法单元构建成一个便于执行的解析树。

4.解析完成后，server层会针对sql语句通过优化器来优化sql执行的成本， 通过比较不同的执行方案，来选择一个最低成本的执行方案，生成对应的执行计划。比如存在多个索引，会选择出一个最优的索引来执行。

5.优化完成后，会通过执行器来执行sql，执行器会判断用户对表有没有对应的权限，如果验证通过，执行器会调用存储引擎提供的接口来执行sql执行计划。

6.存储引擎执行sql执行计划，返回对应的结果数据。

索引优化思路和方向

索引失效
多表join
查询字段太多
表中数据量太大
索引区分度不高
数据库的表结构不合理
数据库io或者cpu比较高
数据库参数不合理
事务比较长
锁竞争导致的等待

InnoDB和MyIsam的区别？

InnoDB支持事务，MyIsam不支持事务。

Innodb支持表级锁和行级锁，myisam只支持表级锁。 

InnoDb支持外键，Myisam不支持外键。

数据库三大范式？

1.列不可分，数据库表中每个字段都是表示唯一属性的列，不能再分，如表中有一列为成绩，如果有多门课程，则该字段是可以被分割为语文成绩、数学成绩等，不满足列不可分。

2.不存在部分依赖，表中所有字段都依赖于主键字段，如主键为学号，确定了学号则知道该学号代表的姓名，但是如果有一列为课程名称，则表明存在部分依赖，因为课程名称依赖于课程id字段。

3.不存在传递依赖，传递依赖简单来说就是 b依赖于a，c依赖于b，则c传递依赖于a，不存在传递依赖表明数据表中所有字段都直接依赖于主键。

数据库表中记录的隐式字段

1. db_row_id，隐式主键id，当主键不存在时，会自动生成一个隐式主键。

2.db_trx_id，对当前记录修改/插入的事务id

3.db_roll_ptr，回滚指针，指向这条记录的上一个版本的事务

mysql一次事务更新的流程？

1.从buffer pool读取数据，当buffer pool不存在该数据时，则从磁盘中读取该页到buffer pool内存中。

2.在undolog中记录修改前的数据，用于数据库的原子性和一致性保证，当事务发送回滚时，则通过该日志将数据更新到修改前的状态。

3.更新操作记录到buffer pool中，并写入到redolog buffer中。

4.将redolog buffer写入磁盘，用于数据库持久性保证，并将更新操作写入buffer pool，设置该页为脏页，在合适的时机将该页写入磁盘。

5.事务提交后，会将本次提交的信息写入binlog，binlog是mysql实现主从同步的一种机制。