面试总结5——数据库

数据库三大范式

规范的来优化数据数据存储方式。

规范化目的是使结构更合理，消除存储异常，使数据冗余尽量小。便于插入、删除和更新。

 

1. 第一范式：列不可再分
2. 第二范式：非主属性完全依赖于主属性
3. 第三范式：非主属性不传递依赖于码。

 

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

 

事务

事物的4大特征（ACID）：

原子性：事物包含的所有操作要么全不执行，要么全部回滚

一致性：事物必须使数据库从一个一致性状态转换到另一个一致性状态（一种语义上的有意义且正确的状态）。

隔离性：多个并发事物之间应当相互隔离。

持久性：事物一旦提交，对数据库的改变是永久性的。

事务并发操作可能会发生的问题：

脏读：一个事物读取了另一个事物未提交的数据。读取到的数据并非是最终持久化之后的数据。

不可重复读：一个事物执行过程中多次查询返回不同的结果。即一个事物执行过程中，另一事物读取并修改了当前事物正在读取的数据。

幻读：一个事物执行过程中多次查询返回不同的结果。不可重复读指的是修改update，可以通过加行锁实现，幻读指的是插入（插入了新的数据导致返回结果集发生了变化），要通过锁住整张表实现。

针对并发操作中的问题，提出了四种隔离级别：

未提交读：所有事务都可以“看到”未提交事务的执行结果。

提交读：一个事务开始时，只能“看见”已经提交事务所做的改变。只解决了脏读问题。

可重复读：Mysql默认隔离级别。没有解决幻读问题（通过间隙锁和MVCC解决）。它保证同一事务的多个实例在并发读取事务时，会“看到同样的”数据行。

串行化：

如何解决幻读？

间隙锁（针对当前读，读到的是最新的行数据）或者MVCC（针对快照读，读取当前数据的副本，不能及时更新）

MVCC：是通过保存数据在某个时间点的快照来实现的。在很多情况下避免了加锁操作，因此开销更低。缺点：读取的数据不是及时更新的数据。

间隙锁：锁定一个范围的记录,但不包括记录本身.锁加在未使用的空闲空间上,可能是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

行锁

行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁。

1. 记录锁（record locks）：记录锁就是为某行记录加锁，它封锁该行的索引记录。
2. 间隙锁（gap locks）：间隙锁基于非唯一索引，它锁定一段范围内的索引记录。
3. 临键锁（next-key locks）：每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

ACID实现原理

1）undo log（回滚日志），原子性和隔离性实现的基础。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

2）redo log（重做日志），保证事物持久性。InnoDB提供了缓存(Buffer Pool), 大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；

3) InnoDB通过锁机制四种隔离级别中的RR实现隔离性，RR的实现主要靠锁机制、MVCC（隐藏列）+undo log和next-key lock来保证。

 

MySQL 的 binlog、redo log 和 undo log

1）binlog：binlog是二进制日志文件，用于记录mysql的数据更新或者潜在更新(比如DELETE语句执行删除而实际并没有符合条件的数据)，在mysql主从复制中就是依靠的binlog。

2）redo log通常是物理日志，记录的是 数据页 的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

3）undo log 用来回滚行记录到某个版本。undo log 一般是逻辑日志，根据每行记录进行记录。

主从复制模型

主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。

1）主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。

2）从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。

3）从服务器上面同时开启一个SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

Nginx

1）Nginx是一款自由的、开源的、高性能的HTTP服务器和反向代理服务器；同时也是一个IMAP、POP3、SMTP代理服务器；Nginx可以作为一个HTTP服务器进行网站的发布处理，另外Nginx可以作为反向代理进行负载均衡的实现。

正向代理，"它代理的是客户端，代客户端发出请求"，

通过上述的图解大家就可以看清楚了，多个客户端给服务器发送的请求，Nginx服务器接收到之后，按照一定的规则分发给了后端的业务处理服务器进行处理了。此时~请求的来源也就是客户端是明确的，但是请求具体由哪台服务器处理的并不明确了，Nginx扮演的就是一个反向代理角色。

客户端是无感知代理的存在的，反向代理对外都是透明的，访问者并不知道自己访问的是一个代理。因为客户端不需要任何配置就可以访问。

反向代理，"它代理的是服务端，代服务端接收请求"，主要用于服务器集群分布式部署的情况下，反向代理隐藏了服务器的信息。

 

2）反向代理的作用：

保证内网的安全，通常将反向代理作为公网访问地址，Web服务器是内网

负载均衡，通过反向代理服务器来优化网站的负载

3）Nginx支持的负载均衡调度算法方式如下：

weight轮询(默认，常用)：接收到的请求按照权重分配到不同的后端服务器

ip_hash（常用）：每个请求按照发起客户端的ip的hash结果进行匹配，这样的算法下一个固定ip地址的客户端总会访问到同一个后端服务器，

fair：智能调整调度算法，动态的根据后端服务器的请求处理到响应的时间进行均衡分配，响应时间短处理效率高的服务器分配到请求的概率高，

url_hash：按照访问的url的hash结果分配请求，每个请求的url会指向后端固定的某个服务器，可以在Nginx作为静态服务器的情况下提高缓存效率。

索引

什么是索引，优缺点

1）索引是一种数据结构，索引是对数据库表中一个或多个列的值进行排序的数据结构。

2）优点：可以提高查询速度；加速表与表之间的连接；

3）缺点：维护需要额外的时间和空间。

创立索引但无法使用

1. 以%开头的like模糊匹配。
2. Or语句前后没有同时使用索引
3. 数据类型出现隐式转换。
4. 对于多列索引，必须满足最左匹配原则。

mysql 存在索引但不能使用索引的情况

1. 单键值的b树索引列上存在null值，导致COUNT(*)不能走索引
2. 索引列上有函数运算或者是表达式的一部分，导致不走索引
3. 数据类型隐式转换导致不走索引
4. 表的数据库小或者需要选择大部分数据，不走索引 （Mysql估计使用索引比全表扫描慢）
5. 负向查询（not , not in , not like , <>, != , !>, !< ） 不会使用索引
6. 组合索引最左前缀
7. like '%liu' 百分号在前
8. 查询条件中使用or的每个列都必须有索引才能走索引，否则考虑使用union替代or

 

什么样的字段适合创建索引

1. 经常查询的字段。
2. 经常做表连接的字段。
3. 经常出现在order by，group by，和distinct后面的字段。

创建索引时需要注意什么

1. 非空字段：应该指定列为NOT NULL。（可空列需要更多的存储空间，mysql内部进行特殊处理。可空列被索引后，每条记录都需要一个额外的字节，还能导致MYisam 中固定大小的索引变成可变大小的索引。）
2. 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面
3. 索引字段越小越好：

创建索引

1）ALTER TABLE修改表结构(添加索引)

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX [index_name] (列名1，列名2，…)

ALTER TABLE table_name ADD UNIQUE (列名1，列名2，…)

ALTER TABLE table_name ADD PRIMARY KEY (列名1，列名2，…)

索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

2)  CREATE INDEX创建索引

CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

索引名不可选,不能用CREATE INDEX语句创建PRIMARY KEY索引。

索引类型

在创建索引时，可以规定索引能否包含重复值。如果不包含，则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。

删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

查看索引

mysql> show index from table_name;

mysql> show index_name from table_name;

什么情况下应不建或少建索引

1）表记录太少

2）经常插入、删除、修改的表

3）数据重复且分布平均的表字段

4）经常和主字段一块查询但主字段索引值比较多的表字段

索引的分类

1. 聚集索引和非聚集索引：聚簇索引按照数据的物理存储进行划分的；非聚集索引是把一个很大的范围，转换成一个小的地图，然后你需要在这个小地图中找你要寻找的信息的位置
2. 普通索引：没有任何限制
3. 唯一索引：索引列的值必须唯一，允许空值
4. 主键索引：唯一索引的一种，不允许有空值
5. 全文索引：尽可以用在MyISAM中，用在一篇文章中检索文本信息
6. 组合索引：多列组合，最左匹配原则。

 

索引的底层实现原理

二叉搜索树、AVL、B树，hash。

B树

1. 根节点最少有两个节点
2. 其他节点有（m/2，m）个子节点
3. 节点关键字为子节点个数-1
4. 叶结点都在同一层

B+树（InnoDB搜索引擎的索引实现）

1. 节点关键字与子节点个数相同。
2. 为所有叶子节点增加了一个链指针。
3. 所有关键字信息只在叶结点出现。

红黑树

1. 节点为黑色或者红色
2. 叶结点为黑色的null
3. 根节点为黑色
4. 红节点的子节点为黑色
5. 每个节点到子孙节点路径上包含相同数量的黑色节点

 

插入、删除、旋转次数少于平衡二叉树；

TreeMap实现原理；

近似平衡。

B+树优于B树、红黑树的原因

1. 磁盘读写代价更低：所有关键字信息只在叶结点出现，一次性读入节点数更多
2. 查询效率更稳定：任何关键字的查找必须走一条从根结点到叶子结点的路
3. 可以基于范围查询：为所有叶子节点增加了一个链指针

B+树优于hash的原因

1. hash索引等值查询，无法进行范围查找。
2. hash索引不支持索引排序
3. hash索引不支持模糊查询以及多列索引的最左匹配。
4. hash索引任何情况下都避免不了回表查询，而b+树在符合某些条件下（聚集索引，覆盖索引）可以只通过索引完成查询。
5. hash索引不稳定，还可能发生hash碰撞。

聚集索引和非聚集索引

表记录的排列顺序与索引的逻辑顺序是否一致。

1. 数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。叶子节点存放的整行数据。
2. 该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。叶子节点存放的主键的值。

非聚簇索引一定会回表查询吗？

不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.

联合索引是什么?为什么需要注意联合索引中的顺序?

MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.

MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序.

联合索引在B+树中是怎么存储的

联合索引(col1, col2,col3)也是一棵B+Tree，其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。

创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度.

MySql的搜索引擎

MyISAM和InnoDB

区别：

1. 存储结构：MyISAM在磁盘上存储成三个文件（表，表定义，索引文件），InnoDB所有的表都在同一个数据文件中。
2. 存储空间：MyISAM可以被压缩，占据的存储空间小，InnoDB需要更多的内存和存储。
3. MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。
4. 在MyISAM中，可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。InnoDB中必须包含只有该字段的索引，并且引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
5. MyISAM只支持表级锁，InnoDB支持事务和行级锁。
6. MyISAM支持 FULLTEXT类型的全文索引；InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
7. 表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
8. 表的具体行数：MyISAM保存表的总行数，select count() from table;会直接取出出该值；而InnoDB没有保存表的总行数。
9. 外键：MyISAM不支持外键，而InnoDB支持外键。

NOSQL数据库：Redis

Redis是什么

Redis是一款基于内存的且支持持久化、高性能的Key-Value NoSQL 数据库，其支持丰富数据类型(string，list，set，sorted set，hash)，常被用作缓存的解决方案。Redis具有以下显著特点：

1. 速度快
2. 支持丰富的数据类型
3. 支持事务，操作都是原子性
4. 丰富的特性，可用于缓存，消息，按key设置过期时间，过期后将会自动删除。

redis相比memcached有哪些优势

1. memcached所有值均是简单的字符串，redis作为其替代者，支持更丰富的数据类型。
2. redis的速度比memcached快很多
3. redis可以持久化其数据

Redis主要消耗什么物理资源？

内存。

Redis有哪几种数据淘汰策略？

1）noeviction:返回错误当内存限制达到并且客户端尝试执行会让更多内存被使用的命令（大部分的写入指令，但DEL和几个例外）

2）allkeys-lru: 尝试回收最少使用的键（LRU），使得新添加的数据有空间存放。

3）volatile-lru: 尝试回收最少使用的键（LRU），但仅限于在过期集合的键,使得新添加的数据有空间存放。

4）allkeys-random: 回收随机的键使得新添加的数据有空间存放。

5）volatile-random: 回收随机的键使得新添加的数据有空间存放，但仅限于在过期集合的键。

6）volatile-ttl: 回收在过期集合的键，并且优先回收存活时间（TTL）较短的键,使得新添加的数据有空间存放。

一个字符串类型的值能存储最大容量是多少？

512M

Redis有哪些适合的场景？

1. 会话缓存（Session Cache）：用Redis缓存会话比其他存储（如Memcached）的优势在于：Redis提供持久化。
2. 全页缓存：
3. 队列：Reids在内存存储引擎领域的一大优点是提供 list 和 set 操作，这使得Redis能作为一个很好的消息队列平台来使用
4. 排行榜/计数器：Redis在内存中对数字进行递增或递减的操作实现的非常好。集合（Set）和有序集合（Sorted Set）也使得我们在执行这些操作的时候变的非常简单，Redis只是正好提供了这两种数据结构。
5. 发布/订阅：

Redis和Redisson有什么关系？

Redisson是一个高级的分布式协调Redis客服端，能帮助用户在分布式环境中轻松实现一些Java的对象。

Jedis与Redisson对比有什么优缺点？

1）Jedis是Redis的Java实现的客户端，其API提供了比较全面的Redis命令的支持；

2）Redisson实现了分布式和可扩展的Java数据结构，和Jedis相比，功能较为简单，不支持字符串操作，不支持排序、事务、管道、分区等Redis特性。Redisson的宗旨是促进使用者对Redis的关注分离，从而让使用者能够将精力更集中地放在处理业务逻辑上。

Redis哈希槽

Redis集群没有使用一致性hash,而是引入了哈希槽的概念，Redis集群有16384个哈希槽，每个key通过CRC16校验后对16384取模来决定放置哪个槽，集群的每个节点负责一部分hash槽。

Redis集群的主从复制模型

为了使在部分节点失败或者大部分节点无法通信的情况下集群仍然可用，所以集群使用了主从复制模型,每个节点都会有N-1个复制品.

Redis 的哨兵机制

为了达到redis的高可用，有两种部署方式：主从复制（redis里面只支持一个主，不像Mysql、Nginx主从复制可以多主多从。）+哨兵机制

哨兵机制存在的意义：

为了实现redis故障转移的自动化。自动发现，自动转移。不需要人工参与（如果主节点down调）。

该系统执行以下三个任务:

1）监控(Monitoring): 哨兵(sentinel) 会不断地检查你的Master和Slave是否运作正常。

2）提醒(Notification):当被监控的某个Redis出现问题时, 哨兵(sentinel) 可以通过 API 向管理员或者其他应用程序发送通知。

3）自动故障迁移(Automatic failover):当一个Master不能正常工作时，哨兵(sentinel) 会开始一次自动故障迁移操作,它会将失效Master的其中一个Slave升级为新的Master, 并让失效Master的其他Slave改为复制新的Master; 当客户端试图连接失效的Master时,集群也会向客户端返回新Master的地址,使得集群可以使用Master代替失效Master。

Redis集群

1）Redis并不能保证数据的强一致性，这意味这在实际中集群在特定的条件下可能会丢失写操作。

2）Redis集群之间异步复制

3）Redis集群目前无法做数据库选择，默认在0数据库。

Redis分布式锁么，它是怎么实现的？

先拿setnx来争抢锁，抢到之后，再用expire给锁加一个过期时间防止锁忘记了释放。

如果在setnx之后执行expire之前进程意外crash或者要重启维护了，那会怎么样？

set指令有非常复杂的参数，这个应该是可以同时把setnx和expire合成一条指令来用的

使用过Redis做异步队列么，你是怎么用的？有什么缺点？

一般使用list结构作为队列，rpush生产消息，lpop消费消息。当lpop没有消息的时候，要适当sleep一会再重试。

缺点：

在消费者下线的情况下，生产的消息会丢失，得使用专业的消息队列如rabbitmq等。

能不能生产一次消费多次呢？

使用pub/sub主题订阅者模式，可以实现1:N的消息队列。

redis持久化如何实现

两种方式：RDB（默认）和AOF

1） RDB持久化原理：通过bgsave命令触发，然后父进程执行fork操作创建子进程，子进程创建RDB文件，根据父进程内存生成临时快照文件，完成后对原有文件进行原子替换（定时一次性将所有数据进行快照生成一份副本存储在硬盘中）

优点：是一个紧凑压缩的二进制文件，Redis加载RDB恢复数据远远快于AOF的方式。

缺点：由于每次生成RDB开销较大，非实时持久化，

2） AOF持久化原理：开启后，Redis每执行一个修改数据的命令，都会把这个命令添加到AOF文件中。查询不会记录下来。

优点：实时持久化。

缺点：所以AOF文件体积逐渐变大，需要定期执行重写操作来降低文件体积，加载慢。

Redis的缓存穿透

1）一般的缓存系统，都是按照key值去缓存查询，如果不存在对应的value，就应该去DB中查找 。这个时候，如果请求的并发量很大，就会对后端的DB系统造成很大的压力。这就叫做缓存穿透。关键词：缓存value为空；并发量很大去访问DB。

2）造成的原因

1.业务自身代码或数据出现问题；2.一些恶意攻击、爬虫造成大量空的命中，此时会对数据库造成很大压力。

3）解决方法

1.设置布隆过滤器，将所有可能存在的数据哈希到一个足够大的bitmap中，一个一定不存在的数据会被这个bitmap拦截掉，从避免了对底层存储系统的查询压力。

2. 如果一个查询返回的数据为空，不管是数据不存在还是系统故障，我们仍然把这个结果进行缓存，但是它的过期时间会很短最长不超过5分钟。

Redis的雪崩

1）因为缓存层承载了大量的请求，有效的保护了存储层，但是如果缓存由于某些原因，整体不能够提供服务，于是所有的请求，就会到达存储层，存储层的调用量就会暴增，造成存储层也会挂掉的情况。存在这种问题的一个场景是：当缓存服务器重启或者大量缓存集中在某一个时间段失效，这样在失效的时候，大量数据会去直接访问DB，此时给DB很大的压力。

2）解决方法

设置redis集群和DB集群的高可用，如果redis出现宕机情况，可以立即由别的机器顶替上来。这样可以防止一部分的风险。

使用互斥锁：在缓存失效后，通过加锁或者队列来控制读和写数据库的线程数量。比如：对某个key只允许一个线程查询数据和写缓存，其他线程等待。单机的话，可以使用synchronized或者lock来解决，如果是分布式环境，可以是用redis的setnx命令来解决。

不同的key,可以设置不同的过期时间，让缓存失效的时间点不一致，尽量达到平均分布。

永远不过期：redis中设置永久不过期，这样就保证了，不会出现热点问题，也就是物理上不过期。

资源保护：使用netflix的hystrix，可以做各种资源的线程池隔离，从而保护主线程池。

其他。

SQL语句

drop、delete与truncate的区别

1. drop：删除表，删除索引和权限，不可回滚
2. delete：删除表的全部数据行或者一部分数据行，可以回滚。
3. truncate：删除删除表中所有数据，保留表，不能回滚。

存储过程？有哪些优缺点？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。

1. 就编译一次，提高数据库执行效率。
2. 减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小。
3. 通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

什么是触发器？

触发器是一种特殊的存储结构，通过事件触发，保证数据库的完整性。触发器是与表相关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。

MySQL中的悲观锁与乐观锁的实现

1. 悲观锁的特点是先获取锁，再进行业务操作，即“悲观”的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功再进行业务操作。需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。
2. 乐观锁的特点先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过，若未被更新过，则更新成功；否则，失败重试。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号或者时间戳。其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发生并发的修改。如果更新失败，即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。
3. 悲观锁与乐观锁的应用场景

　　一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

如何优化MySQL数据库查询

1. 加索引
2. 索引失效
3. 分表
4. 优化数据类型：避免使用null，尽可能使用更小的字段。

 

Sql执行很慢的原因

一，大多数正常，偶尔很慢；

Sql语句没问题

1，数据库在刷脏页：因为插入，更新是要先修改内存，等到空闲才通过redo同步到磁盘，所以会造成内存数据页和磁盘内容不一致，这时候内存称为脏页。内存数据写入磁盘后，成为干净页；

刷脏页的四种情况：redolog写满了；内存不够用了（淘汰换入数据页）；mysql认为系统空闲的时候；mysql正常关闭的时候。

2，查询的表或者行被加锁了，当前查询要等待锁：用show processlist查看当前状态。

数据量不变的情况下，一直很慢；

二，Sql数据量没有变化，sql语句一直执行很慢；

1，没有用上索引：字段没有索引，字段没有用上（左边有索引的字段做了运算，函数操作导致没有用上索引）；索引失效；

Where c-1=1000;  //没有用上索引

Where c = 1000+1;  // 用上了索引

Where pow(c,2) = 1000;  // 没有用上索引

2，统计失误，数据库没有走索引走了全表扫描：系统执行sql语句的时候，会进行预测：直接走索引扫描次数少还是扫描全表扫描行数少。因为直接走索引扫描的时候涉及到如果是非主键索引（非聚集索引），需要回表，再通过主键索引查找，走了两遍索引；查询语句是否需要使用临时表；是否需要排序等。

数据库怎么预测判断呢？

根据索引的区分度，一个索引上不同的值越多，意味着出现相同数值的索引越少，索引的区分值越高，区分度称之为基数，区分度越高，基数越大，意味着走索引查询越有优势（符合索引条件的行数少）。

怎么知道索引的基数呢？

索引通过遍历部分数据，即采样来预测索引的基数。所以既然是采样就可能出现失误。若采样的数据的基数很小，系统可能就直接走全局扫描了。

 

解决：

强制走索引：select * from t force index(a) where c<100 and c<100000;

查询索引的基数和实际是否符合：show index from t;

不符合的话，可以重现统计索引的基数： analyze table t;

 

MVCC

1）多版本并发控制机制，相对于传统的基于锁的并发控制主要特点是读不上锁

2）锁机制可以控制并发操作,但是其系统开销较大,而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销

3）InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)

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

Select：InnoDB只会查找版本早于当前事务版本的数据行；行的删除版本要么未定义,要么大于当前事务版本号。

Delete：会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.

Insert：实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间.

数据库分区、分表

分区：就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的

分表：就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。

水平分表：把一个表拆分按某个字段拆分称多个表 （用户表）

垂直分表：把表的大字段，并且不是经常查询，单独的放入到一张表去（文章表）

结构化，非结构化

结构化和非结构化，半结构化数据

结构化数据

结构化数据，是指由二维表结构来逻辑表达和实现的数据，严格地遵循数据格式与长度规范，主要通过关系型数据库进行存储和管理。

 

非结构化数据

非结构化数据，是数据结构不规则或不完整，没有预定义的数据模型，不方便用数据库二维逻辑表来表现的数据。包括所有格式的办公文档、文本、图片、HTML、各类报表、图像和音频/视频信息等等。一般直接整体进行存储，而且一般存储为二进制的数据格式

 

半结构化数据

半结构化数据，是结构化数据的一种形式，虽不符合关系型数据库或其他数据表的形式关联起来的数据模型结构，但包含相关标记，用来分隔语义元素以及对记录和字段进行分层。因此，也被称为自描述的结构。常见的半结构数据有XML和JSON。

关系型数据库和非关系型数据库

关系型数据库：oracle，mysql，SQLServer，DB2

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织

优点：

1、易于维护：都是使用表结构，格式一致；

2、使用方便：SQL语言通用，可用于复杂查询；

3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。

缺点：

1、读写性能比较差，尤其是海量数据的高效率读写；

2、固定的表结构，灵活度稍欠；

3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

非关系型数据库：Mongodb，redis

非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。

2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；

3、高扩展性；

4、成本低：nosql数据库部署简单，基本都是开源软件。

 

缺点：

1、不提供sql支持，学习和使用成本较高；

2、无事务处理；

3、数据结构相对复杂，复杂查询方面稍欠。

 

非关系型数据库的分类和比较：

1、文档型

2、key-value型

3、列式数据库

4、图形数据库

redis

1） NoSQL技术，这是一种基于内存的数据库，并且提供一定的持久化功能。Redis和MongoDB是当前使用最广泛的NoSQL。

2）定义：Redis 是完全开源免费的，遵守BSD协议，是一个高性能的key-value数据库。

3）优点：

高性能：基于内存，因为数据存在内存中

Redis支持数据的持久化，异步的方式从内存写回硬盘

支持存储的value类型相对更多，支持string，list，set，sorted set，hash；

支持事务，操作都是原子性，

Redis使用单线程

Redis支持数据的备份，即master-slave模式的数据备份。

缺点：Redis的主要缺点是数据库容量受到物理内存的限制，不能用作海量数据的高性能读写，因此Redis适合的场景主要局限在较小数据量的高性能操作和运算上。

4）常见命令：

String：set,get,decr,incr,mget   常规计数：微博数，粉丝数等

Hash: hget,hset,hgetall     hash特别适合用于存储对象

List: lpush, rpush, lpop, rpop, lrange等 Redis list的实现为一个双向链表

Set: sadd,spop,smembers,sunion 等  特殊之处在于set是可以自动排重的。

Sorted Set: zadd,zrange,zrem,zcard等 sorted set增加了一个权重参数score，使得集合中的元素能够按score进行有序排列

5）Redis的并发竞争问题如何解决?

Redis为单进程单线程模式，采用队列模式将并发访问变为串行访问。Redis本身没有锁的概念，

使用过Redis分布式锁么，它是怎么实现的？

先拿setnx来争抢锁，抢到之后，再用expire给锁加一个过期时间防止锁忘记了释放。

如果在setnx之后执行expire之前进程意外crash或者要重启维护了，那会怎么样？

set指令有非常复杂的参数，这个应该是可以同时把setnx和expire合成一条指令来用的！

数据库搜索引擎

引擎简介

1）数据库引擎是用于存储、处理和保护数据的核心服务。

2）你能用的数据库引擎取决于mysql在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译MYSQL。在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY（BDB），也常常可以使用。

3） ISAM

　　ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

MYISAM

　　MYISAM是MYSQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的功能，MYISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。

　　MYISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。

HEAP

　　HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。

INNODB和BERKLEYDB

INNODB和BERKLEYDB（BDB）数据库引擎都是造就MYSQL灵活性的技术的直接产品，这项技术就是MYSQL++ API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MYISAM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和MYISAM引擎慢很多，但是INNODB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

MyISAM和Innodb

区别：

存储结构：

MyISAM在磁盘上存储为三个表：存储表的定义，存储数据，存储索引；Innodb中所有表都保存在同一个数据文件中。

存储空间：

MyISAM可被压缩，存储空间小；Innodb需要更多的内存，他会在主内存中建立专门的缓存池用于高速缓存数据和索引。

         可移植性，备份和恢复：

前者数据是以文件存储的，跨平台转移很方便，在备份和恢复时可单独针对某个表进行操作；

后者可以拷贝数据文件，备份binlog。支持灾难恢复

         事物支持：

前者强调性能，每次查询具有原子性，执行速度快，但不支持事物；后者支持事物，并实现了四种隔离级别。

         Auto_increment:

MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

         表锁差异：

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB：支持表级锁和行级锁，行锁大幅度提高了多用户并发操作的新能。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

         全文索引：

MyISAM：支持 FULLTEXT类型的全文索引。InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

         表主键：

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

表的具体行数：

MyISAM：保存有表的总行数，如果select count( * ) from table;会直接取出出该值；

InnoDB：没有保存表的总行数，如果使用select count( * ) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

CURD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

外键：

MyISAM：不支持，InnoDB：支持

应用场景

MyISAM: MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

Innodb: InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。在大数据量，高并发量的互联网业务场景下，请使用InnoDB:行锁，对提高并发帮助很大。事务，对数据一致性帮助很大

索引

索引（Index）是帮助MySQL高效获取数据的数据结构。MyIASM和Innodb都使用了树这种数据结构做为索引。

1）MyIASM引擎的索引结构：（非聚集索引）

MyISAM引擎的索引结构为B+Tree，其中B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。

2）Innodb引擎的索引结构：（主键采用聚集索引）

与MyISAM引擎的索引结构同样也是B+Tree，但是Innodb的索引文件本身就是数据文件，即B+Tree的数据域存储的就是实际的数据，这种索引就是聚集索引。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

并且和MyISAM不同，InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址，所以当以辅助索引查找时，会先根据辅助索引找到主键，再根据主键索引找到实际的数据。所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。

建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率

3）所以InnoDB要求表必须有主键（MyISAM可以没有）