面试中mysql数据库问题_MySql数据库面试常见问题

1.数据库索引实现原理

数据库的索引是在存储引擎层实现的，不同的存储引擎有不同的实现方法，索引一般指的是B-Tree这种数据结构，InnoDB使用的是B+树，B树指的是多路自平衡搜索树，所有键值分布在树的所有节点中，搜索有可能在非叶子节点结束，性能逼近二分搜索。B+树是B-树的变体，所有的键值都存储在叶子节点中，为所有节点增加了链指针。

为什么会使用B/B+树：

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。MySQL 是基于磁盘的数据库系统,索引往往以索引文件的形式存储的磁盘上,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取，I/O存取的消耗要高几个数量级,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。为什么使用B-/+Tree，还跟磁盘存取原理有关。

InnoDB存储引擎，B+树索引+自适应哈希索引。

B+树和B树的优缺点

B树每个索引节点都含有信息，B+数除了叶子节点外，其余的都是存储的索引，因此一次性读入内存的数据更多了，降低io次数，而且所有数据都存储在叶子节点上，查询效率更加稳定，因为叶子节点之间用过指针相连，所以范围查找更加快速，B树要实现范围查找只能进行中序遍历。

2.什么数据库索引

索引就是用来提高数据库表的访问速度的，和我们查字典的过程很象，

A)索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页，而不是遍历所有数据页。

B)对于非聚集索引，有些查询甚至可以不访问数据页。

C)聚集索引可以避免数据插入操作集中于表的最后一个数据页。

D)一些情况下，索引还可用于避免排序操作。

3.索引的存储

一个索引包含索引项+指针，就是你指定的那个索引记录和指向数据的指针，键值+逻辑指针。

当你为一张空表创建索引时，数据库系统将为你分配一个索引页，该索引页在你插入数据前一直是空的。此页此时既是根结点，也是叶结点。每当你往表中插入一行数据，数据库系统即向此根结点中插入一行索引记录。当根结点满时，数据库系统大抵按以下步骤进行分裂：

A)创建两个儿子结点

B)将原根结点中的数据近似地拆成两半，分别写入新的两个儿子结点

C)根结点中加上指向两个儿子结点的指针

通常状况下，由于索引记录仅包含索引字段值(以及4-9字节的指针)，索引实体比真实的数据行要小许多，索引页相较数据页来说要密集许多。一个索引页可以存储数量更多的索引记录，这意味着在索引中查找时在I/O上占很大的优势。

4.索引的类型

http://www.cnblogs.com/aspnethot/articles/1504082.html

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

1.聚集索引：在聚集索引中，叶结点也即数据结点，所有数据行的存储顺序与索引的存储顺序一致。

2.非聚集索引：聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。而对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。

3.覆盖索引：索引覆盖是这样一种索引策略：当某一查询中包含的所需字段皆包含于一个索引中，此时索引将大大提高查询性能。

包含多个字段的索引，称为复合索引。索引最多可以包含31个字段，索引记录最大长度为600B。如果你在若干个字段上创建了一个复合的非聚集索引，且你的查询中所需Select字段及Where,Order By,Group By,Having子句中所涉及的字段都包含在索引中，则只搜索索引页即可满足查询，而不需要访问数据页。由于非聚集索引的叶结点包含所有数据行中的索引列值，使用这些结点即可返回真正的数据，这种情况称之为“索引覆盖”。

5.索引的优缺点

索引加快数据库的检索速度

索引降低了插入、删除、修改等维护任务的速度

唯一索引可以确保每一行数据的唯一性

通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

索引需要占物理和数据空间

6.数据库连接

连接就是将数据库表中的其他列添加进来，进行添加列的集合运算，内连接和外连接。

inner join:只能选取出同时存在两张表中的数据。

outer join:只要数据存储在某一张表中，就能读取出来。选取出单张表中的全部信息。

outer join:还涉及一个概念，就是以哪个表作为连接的主表，

left outer join, right outer join. 外连接中使用left outer join, right outer join 得到的结果完全相同。

7.数据库左连接，右连接

就是在外连接中，可以指定哪个表作为主表。left outer join right outer join.

8.什么是事务

事务(Transaction)是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务的四个特性：ACID

8.1.原子性：是指事务是不可分割的最小单元，整个事务的所有操作要么执行成功，要么全部失败回滚。

8.2.一致性：是指事务总是从一个一致性的状态转移到另外一个一致性的状态。

8.3.隔离性：一个事务在没有提交之前，对于其他事务是不可见的，事务的隔离有四个级别。务允许多个用户对同一个数据进行并发访问，而不破坏数据的正确性和完整性。同时，并行事务的修改必须与其他并行事务的修改相互独立。

a.未提交读：事务中的修改，即使没提交也是可以读到的，这种称为脏读。

b.提交读：一个事务从开始到提交之前，对其他事务是不可见的，这个级别有时候也称为不可重复读，因为两次执行同样的查询可能会出现不同的结果。

c.可重复读：该级别保证了多次读取同样的记录时是同一个值，但是这个级别无法解决幻读的问题，幻读是指，当一个事务在某个范围内读取值时，然后其他事务又在这个范围内插入新值，然后再次读取这个值的时候，就会出现新的记录，产生幻行，InooDB通过多版本并发控制MVCC解决了幻读问题。MySql的默认隔离级别

d.可串行化：通过每一行数据加锁，然后强制事务串行执行，最高的隔离级别。

8.4.持久性：一旦事务提交，则其所做的所有修改就会永久的保持在数据库中。

9.mysql存储引擎

MyISAM:在mysql 5.1之前，MyISAM是默认的存储引擎，但是MyISAM不支持事务和行级锁，崩溃后无法安全恢复，

10.mysql中的锁策略

当多个查询需要在同一时刻修改数据，就会产生并发控制问题，数据库管理系统中的并发控制是指当多个事务同时存取同一数据时，不破坏事务的隔离性和统一性及数据库的完整性。共享锁(读锁)，排它锁(写锁)。

表锁：是mysql中最基本的锁策略，并且是开销最小的所策略，就是对整张表进行锁定。

行级锁：锁定表中的某一行，最大程度的支持并发处理，但是也带来最大的锁开销，InnoDB支持行级锁。

多版本并发控制MVCC:MySQL中的存储引擎不仅仅是简单的行级锁，为了提高并发访问的性能，一般都实现了多版本并发控制，不同的存储引擎实现MVCC的方式是不同的，典型的有乐观的并发控制和悲观的并发控制。

MVCC是行级锁的一个变种，但是在很多情况下避免了加锁操作，因此开销更低，但是大多数都实现了非阻塞的读操作，写操作也是锁定必要的行。

InnoDB中MVCC的实现：InnoDB是通过在每行记录的后边添加两个隐藏列来实现的，一个是保存了行的创建时间，一个保存了行的过期时间，存储的不是时间值，而是系统的版本号，没开始一个新的事务，系统版本号都会自动递增。

可重复读隔离级别下，MVCC是具体如何操作的

SELECT

InnoDB会根据以下两个条件检查每行记录：

a.只查找版本号早于当前事务的版本号的行，这样可以保证事务读取的行，要么是事务开始之前存在的，要么是事务自身插入或修改过的。

b.行的删除版本号要么未定义，要么大于当前的事务版本号，这可以确保读取到事务的行，在读取之前不会被删除。

INSERT:

InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

保存这两个版本号，使得读操作都可以不用加锁，不足之处需要额外的存储空间，MVCC只在提交读和可重复读的隔离级别下工作。

11.数据库范式

第一范式：字段是最小的单元，不可再分。

第二范式：满足第一范式，而且表中的其他字段必须依赖于主键。

第三范式：满足第二范式，非主键外的所有字段必须互补依赖。

12.范式和反范式的优缺点

范式化的优点：

a.范式化的更新操作比反范式要快。

b.当数据较好的范式化时，没有重复数据。

c.范式化的表通常更小，更容易放在内存中，所以执行操作会更快。

d.很少有多余的数据意味着检索列表时更少需要DISTINCT和GROUP BY语句。

范式化的缺点：

范式化的数据库表，在查询时通常需要关联。

反范式化的优点：

a.避免关系表查询。

反范式化的缺点：

数据冗余。

13.连接池原理，乐观锁，悲观锁

http://www.open-open.com/lib/view/open1452046967245.html

a.数据库连接池和java线程池原理相同，都是预定义一些线程，等待连接，通过设置参数控制线程数量，这样避免频繁的启动和关闭线程和无限制的创建线程所带来的系统开销，实现原理，使用阻塞队列来实现。设置corePoolSize maxPoolSize。

b.乐观锁(乐观并发控制)：

它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

乐观锁( Optimistic Locking ) 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

c.悲观锁(悲观并发控制)：

如果一个事务执行的操作都某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁操作流程：先加锁，在操作，然后释放锁。

悲观锁的优缺点

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；另外，在只读型事务处理中由于不会产生冲突，也没必要使用锁，这样做只能增加系统负载；还有会降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数。

14.sql注入 如何预防

a.sql注入：用户输入的数据，未经检测，变成了sql语句的一部分，造成意外的结果输出。

b.避免sql注入：避免数据变成代码被执行，对sql语句进行预编译和查询参数绑定，在SQL语句中放置占位符'?'，然后将带有占位符的SQL语句传给数据库编译，执行的时候才将用户输入的数据作为执行的参数传给用户。这样的操作不仅使得SQL语句在书写的时候不再需要拼接，看起来也更直接，而且用户输入的数据也没有机会被送到数据库的SQL解释器被编译执行，也不会越权变成代码。

15.sql性能优化---查询性能优化

1.建立索引，优化查询，InnoDB---聚集索引(主键，叶子节点存储数据值) + 二级索引 (叶子节点存储主键值+需要二次查询) + 覆盖索引(当索引包含全部数据时)这样对于二级索引就只需要一次查询，就快了很多。

MyISAM 存储引擎和InnoDB存储数据结构不相同。

B-TREE索引适用于

2.高性能索引策略

独立的列：索引不能作为表达式的一部分

当索引很长时，可以考虑把一部分作为索引

尽量不要查询过多的数据，不要查询过多的行。

16.redis存储结构

17.慢查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

18.InnoDB间隙锁

间隙锁就是锁住索引记录之间的间隙的锁，MYSQL用REPEATABLE READ作为默认的事务隔离级别，所以它需要锁定索引记录和间隙记录，去解决幻读和基于语句的复制的一致性。如果你的应用能够处理幻读并且二进制登记为行格式，把隔离级别改成READ COMMITTED将帮助你避免所有的这些额外锁。最后一个建议，坚持使用短事务。

19.存储过程

存储过程就是预编译的sql语句，

1.存储过程就是预编译的sql代码块，执行效率高，给存储过程取个名字，在别处直接调用就好了

2.一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率

3.可以一定程度上确保数据安全

20.Mysql分布式事务

分布式事务提交分为两个阶段。

第一阶段：准备阶段，第二阶段：提交或者回滚。

第一阶段的准备阶段用来询问每个节点是否能提交，只有当第一阶段准备好后第二阶段的commit才能进行，

分布式事务需要解决的问题就是保证数据库之间的数据一致性问题

prepare预提交和commit确认提交

步骤如下：

分别获取到A、B、C数据库的连接并开启事物；

分别在A、B、C数据库上执行SQL；

分别在A、B、C数据库上执行事务预提交；

分别在A、B、C数据库上执行事务确认提交；

21.Mysql复制

1.复制解决的基本问题就是让一台服务器的数据和其他服务器数据保持一致，一台主库数据可以同步到多台备库上。

2.Mysql提供两种复制方式，一种是基于行的复制和另一种是基于语句的复制，都是通过在主库上记录二进制日志，然后在备库上重放日志的方式来实现异步数据的复制。

3.复制解决的问题

数据分布，负载均衡，备份，高可用性和故障切换，MySql升级测试

4.复制的步骤

1.在主库上把数据更改记录到二进制日志中。

2.备库将主库上的日志复制到自己的中继日志中。

3.备库读取中继日志事件，将其重放到备库上。

22.负载均衡

http请求负载均衡，mysql请求负载均衡，

负载均衡算法：

随机，轮询，最少连接数，哈希等算法。

23.分布式

1.Web服务器集群Session一致性的问题

第一种：Session Sticky

对于同一个会话的请求都发生在同一个web服务器，这种方案需要负载均衡器能够根据每次的会话来标识请求。

缺点：如果一个web服务器宕机或者重启，如果有登录状态，则需要用户重新登录。

对于负载均衡器内存的消耗也更大。

第二种：Session Replication

就是在web服务器之间同步会话数据，通过同步就会保证每台web服务器的会话一致。

缺点：同步session造成网络开销，只要session数据有变化，则会发生web服务器之间同步，seesion对于web服务器占用内存过多。

第三种：Session数据集中存储

把session数据集中存储起来，然后不同web服务器来这个地方取数据。

缺点：网络读取存在不稳性。

第四种：Cookie based

通过cookie来传递session数据。

缺点：cookie长度限制；安全性；带宽消耗；性能影响。

2.数据库读写压力大，读写分离

选择数据库作为读库，就涉及到数据库之间数据复制问题。

3.加速数据读取的利器----缓存

数据缓存和页面缓存。