数据库整理复习

mysql存储引擎都有哪些，有什么区别？

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，比如只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！

MyISAM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyISAM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyISAM也是很好的选择。

InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyISAM

mysql索引结构都有哪些，有什么区别？

mysql一共有两种索引结构，一种是B+Tree索引，一种是Hash索引

B+Tree是mysql使用最频繁的一个索引数据结构，是Inodb和Myisam存储引擎模式的索引类型。相对Hash索引，B+Tree在查找单条记录的速度比不上Hash索引，但是因为更适合排序等操作，所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作。

Hash索引把数据以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为hash结构，每个键只对应一个值，而且是散列的方式分布。所以它并不支持范围查找和排序等功能。

B+tree索引结构和Btree区别？

在B树中,你可以将键和值存放在内部节点和叶子节点;但在B+树中,内部节点都是键,没有值,叶子节点同时存放键和值。B+树的叶子节点有一条链相连,而B树的叶子节点各自独立。

B树：B树可以在内部节点同时存储键和值,因此,把频繁访问的数据放在靠近根节点的地方将大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

B+树：B+树的内部节点只存放键,不存放值,因此,一次读取,可以在内存页中获取更多的键，有利于更快地缩小查找范围。B+树的叶节点由一条链相连,因此,当需要进行一次全数据遍历非常适合实的时候,B+树只需要使用O（logN）时间找到最小的一个节点,然后通过链进行O(N)的顺序遍历即可，而B树则需要对树的每一层进行遍历,这会需要更多的内存置换次数,因此也就需要花费更多的时间

mysql主从复制原理

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

• 从库会生成两个线程,一个I/O线程,一个SQL线程;
• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

mysql如何保证数据不丢失

binlog写入机制：

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。

redolog的写入机制：

事务在执行过程中，生成的redo log是要先写到redo log buffer的，并不是每次生成后都要持久化磁盘的

Mysql主从复制三种类型？

同步复制

同步复制指得是当主库提交事务后，binlog已经通过dump线程传到从库的中继日志，主库需要一直等待从库的提交确认，从库重放完成之后，回复一个ACK给主库，主库这才结束等待，执行后续操作，注意:如果这个时候有多个从节点，那么主库等待的时间就越久，所以需要设置一个超时等待时间。

mysql索引类型有哪些

1. 普通索引：最基本的索引，它没有任何限制 

2. 唯一索引：与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

3. 主键索引：它 是一种特殊的唯一索引，不允许有空，也是聚簇索引

4. 非聚簇索引：主键索引之外的就是非聚簇索引，非聚簇索引又叫辅助索引或者二级索引，和聚簇索引用的都是b+tree，不同点：主键索引的叶子节点存储的是一行完整的数据，非主键索引的叶子节点存储的是主键值

5.全文索引：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。

6.组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则，如果在查询中需要匹配多个字段的条件，可以把这几个字段做个联合索引，效率要比在每个字段上加索引高多了

InnoDB聚集索引：1、聚集索引在叶子节点存储的是表中的数据，而非聚集索引在叶子节点存储的是主键和索引列；2、聚集索引中表记录的排列顺序和索引的排列顺序一致，而非聚集索引的排列顺序不一致；3、聚集索引每张表只能有一个，而非聚集索引可以有多个。

InnoDB普通索引的叶子节点存储主键值。

• 脏读：指一个线程中的事务读取到了另外一个线程中未提交的数据。
• 不可重复读（虚读）：指一个线程中的事务读取到了另外一个线程中提交的update的数据。
• 幻读：指一个线程中的事务读取到了另外一个线程中提交的insert的数据。

什么是回表？

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

因此，可以通过索引先查询出id字段，再通过主键id字段，查询行中的字段数据，即通过再次查询提供MySQL查询速度。

通俗的来说就是非主键索引查找数据时需要先找到主键，再根据主键查找具体行数据，这种现象叫回表查询

避免回表：索引覆盖，即将查询sql中的字段添加到联合索引里面，只要保证查询语句里面的字段都在索引文件中就无需回表查询

mySql执行顺序？

(1)from (2) on (3) join(4) where (5)group by(6) avg,sum.... (7)having (8) select (9) distinct (10) order by 

当数据表中A、B字段做了组合索引，那么单独使用A或单独使用B会有索引效果吗？

看A、B两字段做组合索引的时候，谁在前面，谁在后面，如果A在前，那么单独使用A会有索引效果，单独使用B则没有，反之亦然。同理，使用like模糊查询时，如果只是使用前面%，那么有索引效果，如果使用双%号匹配，那么则无索引效果

mysql默认事务隔离级别

1、未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

2、提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

3、可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读，但是innoDB解决了幻读

4、串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

mysql优化具体怎么优化

查看SQL执行频率可以通过使用show status命令

show status like 'uptime'查看服务工作时间

show status like 'slow_queries'查看慢查询次数

EXPLAIN：

select_type:表示SELECT的类型，table:输出结果集的表（表别名），type:表示MySQL在表中找到所需行的方式，或者叫访问类型，possible_keys: 表示查询可能使用的索引，key: 实际使用的索引，key_len: 使用索引字段的长度，ref: 使用哪个列或常数与key一起从表中选择行，rows: 扫描行的数量，filtered: 存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比)，Extra: 执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息

 分析语句，是否加载了不必要的字段/数据。

 分析 SQL 执行计划（

explain extended），思考可能的优化点，是否命中索引等。

 查看 SQL 涉及的表结构和索引信息。  如果 SQL 很复杂，优化 SQL 结构。

 按照可能的优化点执行表结构变更、增加索引、SQL 改写等操作。

 查看优化后的执行时间和执行计划。

 如果表数据量太大，考虑分表。

 利用缓存，减少查询次数。

MongoDB里面存储什么数据，在实际项目中哪里应用到

主要场景:

1）网站实时数据处理。它非常适合实时的插入、更新与查询，并具备网站实时数据存储所需的复制及高度伸缩性。

2）缓存。由于性能很高，它适合作为信息基础设施的缓存层。在系统重启之后，由它搭建的持久化缓存层可以避免下层的数据源过载。

3）高伸缩性的场景。非常适合由数十或数百台服务器组成的数据库，它的路线图中已经包含对MapReduce引擎的内置支持。

数据库里面常用锁都有哪些？

一、数据库为什么需要锁？

为了保证数据的一致性。mysql数据库存在多种数据引擎，MySQL各存储引擎使用了三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

数据库锁出现的目的：处理并发问题

锁分类

乐观锁：当线程获取不到锁时，会进行自旋重试，不会阻塞用户线程，但牺牲cpu的资源。

悲观锁：当线程获取不到锁时，阻塞用户线程，直到锁释放，重新获取。阻塞线程会造成计算机从用户空间切换至内核空间再切换回用户空间这种耗时操作，故效率较低。

共享锁（读锁）：只对数据进行读取。多个共享锁可以共存。

排它锁（写锁）：对数据进行修改。不允许其它锁共同访问数据。

重入锁：同一线程对锁多次访问，不会多次加锁，而是进行重入，减少加锁解锁的时间开销。

公平锁：多个线程获取一把锁，获取不到的线程排队等待获取。

非公平锁：多个线程获取一把锁，锁释放时，抢夺锁的线程不一定是按照访问顺序而来的线程。

表锁：对整张表进行锁定，表不存在事务。

行锁：对指定行进行加锁，存在事务。

间隙锁：

首先要明确一个概念，所谓行锁，并不是将这行记录锁定，而是将记录对应的索引锁定。即上锁，是对索引上锁。

索引又是按照顺序排列存放，所谓间隙锁，即索引区间的锁定。

举个例子:
Update user set age = 18 where id > 18 and id < 50;

那么，在执行sql时，主键索引(18,50)区间的索引值都会被锁定，就算可能当前id最大为30，但30~50的索引值还是会被索引。此时其它事务想要insert一条ID=31的数据，会发现阻塞，等待update的commit /rollback。

并非主键索引/唯一索引一定会使用间隙锁。当where条件命中具体行数据时，使用记录的行锁；当where条件命中一个区间时，才会使用间隙锁。

MVCC

MVCC，多版本并发控制,它是通过读取历史版本的数据，来降低并发事务冲突，从而提高并

发性能的一种机制。

MVCC可以实现可重复读

在MVCC协议下，每个读操作会看到一个一致性的snapshot，并且可以实现非阻塞的读。MVCC允许数据具有多个版本，这个版本可以是时间戳或者是全局递增的事务ID，在同一个时间点，不同的事务看到的数据是不同的

看一下MySQL的innodb引擎是如何实现MVCC的。innodb会为每一行添加两个字段，分别表示该行创建的版本和删除的版本，填入的是事务的版本号，这个版本号随着事务的创建不断递增。

如何避免死锁？

数据库锁升级的场景？