MySQL、MongoDB、PostgreSQL面试题

 2022-10-28 /  可可西里

数据库常见面试题、八股文

MySQL面试题

1. Mysql索引用的是什么算法

Mysql索引选用的是B+树，平衡二叉树的高度太高，查找可能需要较多的磁盘IO。B树索引占用内存较高(非叶子节点存储数据)
B+树，主要是查询效率高，O(logN)，可以充分利用磁盘预读的特性，多叉树，深度小，叶子结点有序且存储数据

2. Mysql事务的基本要素

原子性：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
一致性：事务开始前和结束后，数据库的完整性约束没有被破坏
隔离性：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰
持久性：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚

3. Mysql的存储引擎

InnoDB存储引擎
- InnoDB存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用
- 其特点是行锁设计，支持外键，并支持非锁定锁，即默认读取操作不会产生锁。从Mysql5.5.8版本开始，InnoDB存储引擎是默认的存储引擎
MyISAM存储引擎
- MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向一些OLAP数据库应用
- InnoDB的数据文件本身就是主索引文件，而MyISAM的主索引和数据是分开的
NDB存储引擎
- NDB存储引擎是一个集群存储引擎，其结构是share nothing的集群架构，能提供更高的可用性
- NDB的特点是数据全部放在内存中（从MySQL 5.1版本开始，可以将非索引数据放在磁盘上），因此主键查找的速度极快，并且通过添加NDB数据存储节点可以线性地提高数据库性能，是高可用、高性能的集群系统
- NDB存储引擎的连接操作是在MySQL数据库层完成的，而不是在存储引擎层完成的。这意味着，复杂的连接操作需要巨大的网络开销，因此查询速度很慢。如果解决了这个问题，NDB存储引擎的市场应该是非常巨大的
Memory存储引擎
- Memory存储引擎（之前称HEAP存储引擎）将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失
- 它非常适合用于存储临时数据的临时表，以及数据仓库中的纬度表。Memory存储引擎默认使用哈希索引，而不是我们熟悉的B+树索引
- 虽然Memory存储引擎速度非常快，但在使用上还是有一定的限制。比如，只支持表锁，并发性能较差，并且不支持TEXT和BLOB列类型。最重要的是，存储变长字段时是按照定常字段的方式进行的，因此会浪费内存
Archive存储引擎
- Archive存储引擎只支持INSERT和SELECT操作，从MySQL 5.1开始支持索引
- Archive存储引擎使用zlib算法将数据行（row）进行压缩后存储，压缩比一般可达1∶10。正如其名字所示，Archive存储引擎非常适合存储归档数据，如日志信息
- Archive存储引擎使用行锁来实现高并发的插入操作，但是其本身并不是事务安全的存储引擎，其设计目标主要是提供高速的插入和压缩功能
Maria存储引擎
- Maria存储引擎是新开发的引擎，设计目标主要是用来取代原有的MyISAM存储引擎，从而成为MySQL的默认存储引擎。它可以看做是MyISAM的后续版本
- Maria存储引擎的特点是：支持缓存数据和索引文件，应用了行锁设计，提供了MVCC功能，支持事务和非事务安全的选项，以及更好的BLOB字符类型的处理性能

4. Mysql事务隔离级别

Mysql有四种事务隔离级别，默认的是可重复读

事务隔离级别	脏读	不可重复读	幻读
读未提交	是	是	是
读已提交	否	是	是
可重复读	否	否	是
串行	否	否	否

读未提交(Read uncommitted)

一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证
- 所有事务都可以看到其他未提交事务的执行结果
- 本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少
- 该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据
读已提交(Read committed)

一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生
- 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）
- 它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变
- 这种隔离级别出现的问题是——不可重复读(Nonrepeatable Read)，不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果
导致这种情况的原因可能有：
- 有一个交叉的事务有新的commit，导致了数据的改变
- 一个数据库被多个实例操作时，同一事务的其他实例在该实例处理其间可能会有新的commit
可重复读(Repeatable read)

就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生
- 这是MySQL的默认事务隔离级别
- 它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
- 此级别可能出现的问题——幻读(Phantom Read)：当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行
- InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题.InnoDB采用MVCC来支持高并发，实现了四个标准隔离级别。默认基本是可重复读，并且提供间隙锁（next-key locks）策略防止幻读出现
串行(Serializable)

串行(Serializable)，是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。Mysql的默认隔离级别是Repeatable read
- 这是最高的隔离级别
- 它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁
- 在这个级别，可能导致大量的超时现象和锁竞争

5. Mysql高可用方案有哪些

主从复制方案
- 这是MySQL自身提供的一种高可用解决方案，数据同步方法采用的是MySQL replication技术。MySQL replication就是从服务器到主服务器拉取二进制日志文件，然后再将日志文件解析成相应的SQL在从服务器上重新执行一遍主服务器的操作，通过这种方式保证数据的一致性
- 为了达到更高的可用性，在实际的应用环境中，一般都是采用MySQL replication技术配合高可用集群软件keepalived来实现自动failover，这种方式可以实现95.000%的SLA
MMM/MHA高可用方案
- MMM提供了MySQL主主复制配置的监控、故障转移和管理的一套可伸缩的脚本套件。在MMM高可用方案中，典型的应用是双主多从架构，通过MySQL replication技术可以实现两个服务器互为主从，且在任何时候只有一个节点可以被写入，避免了多点写入的数据冲突
- 同时，当可写的主节点故障时，MMM套件可以立刻监控到，然后将服务自动切换到另一个主节点，继续提供服务，从而实现MySQL的高可用
Heartbeat/SAN高可用方案
- 在这个方案中，处理failover的方式是高可用集群软件Heartbeat，它监控和管理各个节点间连接的网络，并监控集群服务，当节点出现故障或者服务不可用时，自动在其他节点启动集群服务
- 在数据共享方面，通过SAN（Storage Area Network）存储来共享数据，这种方案可以实现99.990%的SLA
Heartbeat/DRBD高可用方案
- 这个方案处理failover的方式上依旧采用Heartbeat，不同的是，在数据共享方面，采用了基于块级别的数据同步软件DRBD来实现
- DRBD是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。和SAN网络不同，它并不共享存储，而是通过服务器之间的网络复制数据
NDB CLUSTER高可用方案
- 国内用NDB集群的公司非常少，貌似有些银行有用。NDB集群不需要依赖第三方组件，全部都使用官方组件，能保证数据的一致性，某个数据节点挂掉，其他数据节点依然可以提供服务，管理节点需要做冗余以防挂掉
- 缺点是：管理和配置都很复杂，而且某些SQL语句例如join语句需要避免

6. Mysql中utf8和utf8mb4区别

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就可以了
Mysql支持的utf8编码最大字符长度为3字节，如果遇到4字节的宽字符就会插入异常了。三个字节的UTF-8最大能编码的Unicode字符是0xffff，也就是Unicode中的基本多文种平面(BMP)。任何不在基本多文本平面的Unicode字符，都无法使用Mysql的utf8字符集存储
包括 Emoji 表情(Emoji是一种特殊的Unicode编码，常见于ios和android手机上)，和很多不常用的汉字，以及任何新增的Unicode字符等等
Mysql中保存4字节长度的UTF-8字符，需要使用utf8mb4字符集，但只有5.5.3版本以后的才支持(查看版本： select version();)。因此呢，为了获取更好的兼容性，应该总是使用utf8mb4而非utf8
对于CHAR类型数据，utf8mb4会多消耗一些空间，根据Mysql官方建议，使用VARCHAR替代CHAR

7. Mysql中乐观锁和悲观锁区别

悲观锁(Pessimistic Lock)
- 悲观锁顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁
- 传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁
乐观锁(Optimistic Lock)
- 乐观锁顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制
- 乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁
- 乐观锁的特点先进行业务操作，不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好
两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适

8. Mysql索引主要是哪些

索引的目的在于提高查询效率

UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
INDEX(普通索引)：允许出现相同的索引内容
PROMARY KEY(主键索引)：不允许出现相同的值
fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维
组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

8.1 索引的缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT，UPDATE和DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在要给大表上建了多种组合索引，索引文件会膨胀很宽，索引只是提高效率的一个方式，如果mysql有大数据量的表，就要花时间研究建立最优的索引，或优化查询语句

8.2 索引使用技巧

索引不会包含有NULL的列： 只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的
使用短索引： 对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作
索引列排序：mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引
like语句操作：一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引
不要在列上进行运算
不使用NOT IN 、<>、！=操作，但<，<=，=，>，>=，BETWEEN，IN是可以用到索引的
索引要建立在经常进行select操作的字段上。这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求
索引要建立在值比较唯一的字段上。
对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少
在where和join中出现的列需要建立索引。
where的查询条件里有不等号(where column != …)，mysql将无法使用索引。
如果where字句的查询条件里使用了函数(如：where DAY(column)=…)，mysql将无法使用索引
在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用

8.3 组合索引的作用

减少开销
- 建一个组合索引(col1，col2，col3)，实际相当于建了(col1)，(col1，col2)，(col1，col2，col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用组合索引会大大的减少开销
覆盖索引
- 通常指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖
- 对组合索引(col1，col2，col3)，如果有如下的sql: select col1，col2，col3 from test where col1=1 and col2=2
- 那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
效率高
- 索引列越多，通过索引筛选出的数据越快

9. Mysql联合索引最左匹配原则

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先
- 在检索数据时从联合索引的最左边开始匹配，Mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配了
- 就比如 a=3 and b=4 and c>5 and d=6如果建立(abcd)顺序的索引，d就用不到索引了，如果建立(abdc)的索引则都可以用到索引，其中abd的顺序可以任意调整，因为查询优化器会重新编排（即使是c>5 and b=4 and d=6 and a=3也会全部用到 abdc索引）
=和in可以乱序，比如a=1 and b=2 and c=3 建立(abc)索引可以任意顺序，mysql查询优化器会优化顺序
- 这里需要注意下，比如abc索引那么只要查询条件有a即可用到abc索引（如abc ab ac a），没有a就用不到
- 最左前缀匹配成因：Mysql是创建复合索引的规则是根据索引最左边的字段进行排序，在第一个字段排序的基础上再进行第二个字段排序，类似于order by col1，col2… 所以第一个字段是绝对有序的第二个字段就是无序的了，所以Mysql强调最左前缀匹配

10. 聚簇索引和非聚簇索引区别

聚簇索引与非聚簇索引的区别是：叶子节点是否存放一整行记录
InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引
对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)
对于非聚簇索引表来说（右图），表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+索引对应的记录的数据
因此，聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针

11. 如何查询一个字段是否命中了索引

通过explain sql可看下SQL是否走了索引，很快对比出来
当一个sql中索引字段为int类型时，例如搜索条件where num="111"与where num=111都可以使用该字段的索引。当一个中索引字段为varchar类型时，例如搜索条件where num="111"可以使用索引，where num=111不可以使用索引

12. Mysql不走索引的情况

查询条件使用函数或表达式：如果在查询条件中使用函数或表达式，MySQL 无法使用索引进行优化，例如WHERE YEAR(date) = 2022
查询条件使用了 LIKE 语句：如果在查询条件中使用了 LIKE 语句并且模式以通配符开头，例如WHERE name LIKE '%xxx'
查询条件使用了 OR：如果查询条件使用了 OR，例如WHERE a = 1 OR b = 2，MySQL 无法同时使用多个索引，OR操作有至少一个字段没有索引
查询条件对列进行了运算或类型转换：如果在查询条件中对列进行了运算或类型转换，例如WHERE CONCAT(firstname, lastname) = 'xxx'
查询条件中使用了 NULL 值：如果在查询条件中使用了 NULL 值，例如WHERE name IS NULL
查询的数据量很大：如果查询的数据量很大，超过了索引的选择性，MySQL可能会选择全表扫描而不是使用索引

13. Mysql中的MVCC是什么

数据库并发控制——锁:Multiversion (version) concurrency control (MCC or MVCC) 多版本并发控制，它是数据库管理系统一种常见的并发控制

并发控制常用的是锁，当线程要对一个共享资源进行操作的时候，加锁是一种非常简单粗暴的方法(事务开始时给 DQL 加读锁，给 DML 加写锁)，这种锁是一种悲观的实现方式，也就是说这会给其他事务造成堵塞，从而影响数据库性能

其中在数据库中最常见的就是悲观锁和乐观锁：

悲观锁：当一个线程需要对共享资源进行操作的时候，首先对共享资源进行加锁，当该线程持有该资源的锁的时候，其他线程对该资源进行操作的时候会被阻塞.
乐观锁：当一个线程需要对一个共享资源进行操作的时候，不对它进行加锁，而是在操作完成之后进行判断。比如乐观锁会通过一个版本号控制，如果操作完成后通过版本号进行判断在该线程操作过程中是否有其他线程已经对该共享资源进行操作了，如果有则通知操作失败，如果没有则操作成功，当然除了版本号还有CAS，如果不了解的可以去学习一下，这里不做过多涉及

MVCC的两种读形式：

快照读：读取的只是当前事务的可见版本，不用加锁。而你只要记住简单的 select操作就是快照读(select * from table where id = xxx)
当前读：读取的是当前版本，比如特殊的读操作，更新/插入/删除操作

MVCC的实现原理：

MVCC 使用了“三个隐藏字段”来实现版本并发控制，MySQL在创建建表的时候 innoDB 创建的真正的三个隐藏列吧

RowID	DB_TRX_ID	DB_ROLL_PTR	id	name	password
自动创建的id	事务id	回滚指针	id	name	password

RowID：隐藏的自增ID，当建表没有指定主键，InnoDB会使用该RowID创建一个聚簇索引
DB_TRX_ID：最近修改（更新/删除/插入）该记录的事务ID
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本

其实还有一个删除的flag字段，用来判断该行记录是否已经被删除。而 MVCC 使用的是其中的事务字段，回滚指针字段，是否删除字段

14. Mvcc和Redolog和Undolog以及Binlog有什么不同

Mvcc
- MVCC多版本并发控制是MySQL中基于乐观锁理论实现隔离级别的方式，用于读已提交和可重复读取隔离级别的实现
- 在MySQL中，会在表中每一条数据后面添加两个字段，最近修改该行数据的事务ID，指向该行（undolog表中）回滚段的指针
- Read View判断行的可见性，创建一个新事务时，copy一份当前系统中的活跃事务列表。意思是，当前不应该被本事务看到的其他事务id列表
UndoLog
- UndoLog也就是我们常说的回滚日志文件主要用于事务中执行失败，进行回滚，以及MVCC中对于数据历史版本的查看
- 由引擎层的InnoDB引擎实现，是逻辑日志，记录数据修改被修改前的值，比如”把id=’B’ 修改为id = ‘B2’ ，那么undo日志就会用来存放id =’B’的记录”
- 当一条数据需要更新前，会先把修改前的记录存储在undolog中，如果这个修改出现异常，则会使用undo日志来实现回滚操作，保证事务的一致性
- 当事务提交之后，undo log并不能立马被删除，而是会被放到待清理链表中，待判断没有事物用到该版本的信息时才可以清理相应undolog。它保存了事务发生之前的数据的一个版本，用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
Redolog
- Redolog是重做日志文件是记录数据修改之后的值，用于持久化到磁盘中
- Redolog包括两部分：一：是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二：是磁盘上的重做日志文件(redo log file)，该部分日志是持久的
- 由引擎层的InnoDB引擎实现，是物理日志，记录的是物理数据页修改的信息，比如“某个数据页上内容发生了哪些改动”。当一条数据需要更新时，InnoDB会先将数据更新，然后记录redoLog 在内存中，然后找个时间将redoLog的操作执行到磁盘上的文件上
- 不管是否提交成功我都记录，你要是回滚了，那我连回滚的修改也记录。它确保了事务的持久性
Binlog
- Binlog由Mysql的Server层实现，是逻辑日志，记录的是sql语句的原始逻辑，比如”把id=’B’ 修改为id = ‘B2’
- Binlog会写入指定大小的物理文件中，是追加写入的，当前文件写满则会创建新的文件写入
- 产生:事务提交的时候，一次性将事务中的sql语句，按照一定的格式记录到binlog中
- 用于复制和恢复在主从复制中，从库利用主库上的binlog进行重播(执行日志中记录的修改逻辑)，实现主从同步。业务数据不一致或者错了，用binlog恢复

15. Mysql读写分离以及主从同步

原理：主库将变更写binlog日志，然后从库连接到主库后，从库有一个IO线程，将主库的binlog日志拷贝到自己本地，写入一个中继日志中，接着从库中有一个sql线程会从中继日志读取binlog，然后执行binlog日志中的内容，也就是在自己本地再执行一遍sql，这样就可以保证自己跟主库的数据一致
问题：这里有很重要一点，就是从库同步主库数据的过程是串行化的，也就是说主库上并行操作，在从库上会串行化执行，由于从库从主库拷贝日志以及串行化执行sql特点，在高并发情况下，从库数据一定比主库慢一点，是有延时的，所以经常出现，刚写入主库的数据可能读不到了，要过几十毫秒，甚至几百毫秒才能读取到。还有一个问题，如果突然主库宕机了，然后恰巧数据还没有同步到从库，那么有些数据可能在从库上是没有的，有些数据可能就丢失了。所以mysql实际上有两个机制，一个是半同步复制，用来解决主库数据丢失问题，一个是并行复制，用来解决主从同步延时问题
半同步复制：semi-sync复制，指的就是主库写入binlog日志后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的relay log之后，接着会返回一个ack给主库，主库接收到至少一个从库ack之后才会认为写完成
并发复制：指的是从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这样库级别的并行。（将主库分库也可缓解延迟问题）

16. InnoDB的关键特性

插入缓冲：对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能
两次写：两次写带给InnoDB存储引擎的是数据页的可靠性，有经验的DBA也许会想，如果发生写失效，可以通过重做日志进行恢复。这是一个办法。但是必须清楚地认识到，如果这个页本身已经发生了损坏（物理到page页的物理日志成功页内逻辑日志失败），再对其进行重做是没有意义的。这就是说，在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，这就是doublewrite
自适应哈希索引：InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引
异步IO：为了提高磁盘操作性能，当前的数据库系统都采用异步IO（AIO）的方式来处理磁盘操作。AIO的另一个优势是可以进行IO Merge操作，也就是将多个IO合并为1个IO，这样可以提高IOPS的性能
刷新邻接页：当刷新一个脏页时，InnoDB存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过AIO可以将多个IO写入操作合并为一个IO操作，故该工作机制在传统机械磁盘下有着显著的优势

17. Mysql如何保证一致性和持久性

Mysql为了保证ACID中的一致性和持久性，使用了WAL(Write-Ahead Logging，先写日志再写磁盘)。Redo log就是一种WAL的应用
当数据库忽然掉电，再重新启动时，Mysql可以通过Redo log还原数据。也就是说，每次事务提交时，不用同步刷新磁盘数据文件，只需要同步刷新Redo log就足够了

18. 为什么选择B+树作为索引结构

Hash索引：Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描
二叉查找树：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表
平衡二叉树：通过旋转解决了平衡的问题，但是旋转操作效率太低
红黑树：通过舍弃严格的平衡和引入红黑节点，解决了AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多
B+树：在B树的基础上，将非叶节点改造为不存储数据纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效。此外，B+树，主要是查询效率高，O(logN)，可以充分利用磁盘预读的特性，多叉树，深度小，叶子结点有序且存储数据

19. InnoDB的行锁模式

共享锁(S)：用法lock in share mode，又称读锁，允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
- 若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改
排他锁(X)：用法for update，又称写锁，允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁
- 若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。在没有索引的情况下，InnoDB只能使用表锁

20. 哈希(hash)比树(tree)更快，索引结构为什么要设计成树型

加速查找速度的数据结构，常见的有两类：

哈希，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是O(1)
树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是O(lg(n))

哈希只能满足等值查询，不满足范围和大小查询，其次哈希不可以排序。Mysql是用等值查询，用树的话，等值查询只需要顺序遍历即可。但是对于排序查询的sql需求：分组：group by ，排序：order by ，比较：<、>等，哈希型的索引，时间复杂度会退化为O(n)，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率

21. 为什么索引的key长度不能太长

key 太长会导致一个页当中能够存放的 key 的数目变少，间接导致索引树的页数目变多，索引层次增加，从而影响整体查询变更的效率

22. Mysql的数据如何恢复到任意时间点

恢复到任意时间点以定时的做全量备份，以及备份增量的 binlog 日志为前提。恢复到任意时间点首先将全量备份恢复之后，再此基础上回放增加的 binlog 直至指定的时间点

23. Mysql为什么加了索引可以加快查询

在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序
劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表

24. Explain命令有什么用

在开发的过程中，我们有时会用慢查询去记录一些执行时间比较久的Sql语句，找出这些Sql语句并不意味着完事了，这个时候我们就需要用到explain这个命令来查看一个这些Sql语句的执行计划，查看该Sql语句有没有使用上了索引，有没有做全表扫描，这些都可以通过explain命令来查看

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

- id：select选择标识符
- select_type：表示查询的类型
- table：输出结果集的表
- partitions：匹配的分区
- type：表示表的连接类型
- possible_keys：表示查询时，可能使用的索引
- key：表示实际使用的索引
- key_len：索引字段的长度
- ref：列与索引的比较
- rows：扫描出的行数(估算的行数)
- filtered：按表条件过滤的行百分比
- Extra：执行情况的描述和说明

id：select选择标识符

id是Sql执行的顺序的标识，Sql从大到小的执行
- id相同时，执行顺序由上至下
- 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
select_type：表示查询的类型

查询中每个select子句的类型
- SIMPLE(简单SELECT，不使用UNION或子查询等)
- PRIMARY(查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
- UNION(UNION中的第二个或后面的SELECT语句)
- DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
- UNION RESULT(UNION的结果)
- SUBQUERY(子查询中的第一个SELECT)
- DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
- DERIVED(派生表的SELECT，FROM子句的子查询)
- UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table：输出结果集的表
- table显示这一行的数据是关于哪张表的，有时不是真实的表名字，看到的是derivedx
partitions：匹配的分区
type：表示表的连接类型

type表示Mysql在表中找到所需行的方式，又称“访问类型”。常用的类型有： ALL， index， range， ref， eq_ref， const， system， NULL（从左到右，性能从差到好）
- ALL：Full Table Scan， Mysql将遍历全表以找到匹配的行
- index：Full Index Scan，index与ALL区别为index类型只遍历索引树
- range：只检索给定范围的行，使用一个索引来选择行
- ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
- eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
- const、system：当Mysql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，Mysql就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
- NULL：Mysql在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
possible_keys：表示查询时，可能使用的索引
- possible_keys指出Mysql能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
- 该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用
- 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
key：表示实际使用的索引
- key列显示MySql实际决定使用的键（索引）
- 如果没有选择索引，键是NULL。要想强制Mysql使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
key_len：索引字段的长度
- key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）不损失精确性的情况下，长度越短越好
ref：列与索引的比较
- ref表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows：扫描出的行数(估算的行数)
- rows表示Mysql根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
filtered：按表条件过滤的行百分比
Extra：执行情况的描述和说明

该列包含Mysql解决查询的详细信息，有以下几种情况：
- Using where：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
- Using temporary：表示Mysql需要使用临时表来存储结果集，常见于排序和分组查询
- Using filesort：Mysql中无法利用索引完成的排序操作称为“文件排序”
- Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能
- Impossible where：这个值强调了where语句会导致没有符合条件的行
- Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

25. MySQL优化

优化查询语句：使用索引、避免全表扫描、合理使用 JOIN 语句、避免使用子查询等方式可以提高查询性能
优化表结构：选择合适的数据类型、避免使用过多的 NULL 值、避免使用过多的索引等方式可以提高表的读写效率
优化服务器配置：调整缓存大小、调整线程数、调整网络连接等可以提高 MySQL 的性能
使用缓存：使用 MySQL 自带的缓存机制或者使用外部缓存工具，如Redis等，可以提高读取效率
定期维护：定期备份、优化表、清理无用数据、修复数据等操作可以保持数据库的健康运行状态
使用连接池：连接池可以避免频繁地建立和关闭数据库连接，提高数据库连接的效率
合理分布数据：将数据分布在多个物理磁盘上，可以避免磁盘的瓶颈，提高数据访问的效率
避免使用外部函数：外部函数通常比 MySQL 自带的函数慢很多，可以避免使用外部函数来提高 MySQL 的性能
使用慢查询日志：使用慢查询日志可以帮助发现查询语句的性能瓶颈，及时进行优化
读写分离：通过将读操作和写操作分开处理，提高系统的性能和可靠性

25.1 优化查询语句

使用索引：确保查询涉及的列都有合适的索引
1
SELECT * FROM table WHERE indexed_column = 'value';
避免使用SELECT * ：只选择需要的列，避免不必要的数据传输
1
SELECT column1, column2 FROM table WHERE column3 = 'value';

使用JOIN时，避免在被连接的表上使用函数或表达式

1	SELECT table1.column1, table2.column2 FROM table1 JOIN table2 ON table1.column1 = CONCAT('prefix', table2.column2);

避免使用子查询：使用 JOIN 或者其他更好的方式重构查询

示例：

1	SELECT column1 FROM table WHERE column2 = (SELECT column3 FROM table2 WHERE column4 = 'value');

改为：

1	SELECT t1.column1 FROM table t1 JOIN table2 t2 ON t1.column2 = t2.column3 WHERE t2.column4 = 'value';

使用EXPLAIN命令分析查询计划，以便确定查询是否使用了索引
1
EXPLAIN SELECT * FROM table WHERE indexed_column = 'value';

尽量减少使用OR和IN等操作符

示例：

1	SELECT column1 FROM table WHERE column2 = 'value' OR column3 = 'value';

改为：

1	SELECT column1 FROM table WHERE column2 = 'value' UNION SELECT column1 FROM table WHERE column3 = 'value';

避免使用不必要的 GROUP BY 操作符

示例：

1	SELECT column1 FROM table WHERE column2 = 'value' GROUP BY column1;

改为：

1	SELECT DISTINCT column1 FROM table WHERE column2 = 'value';

对于大表，可以使用分区表进行优化

1	CREATE TABLE table PARTITION BY RANGE(column1) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200));

对于大表，可以使用水平分片或垂直分片

水平分片可以根据时间或者ID等条件对表进行分割；垂直分片可以根据列的类型和频率进行划分

避免在查询中使用通配符（如%），它会使索引无效

示例：

1	SELECT column1 FROM table WHERE column2 LIKE '%value%';

改为：

1	SELECT column1 FROM table WHERE column2 LIKE 'value%';

使用合适的数据类型，例如使用整型而不是字符串型
1
SELECT column1 FROM table WHERE column2 = 10;

在创建表时设置合适的数据类型和字段长度

1	CREATE TABLE table (column1 INT(10), column2 VARCHAR(50));

对于需要重复多次查询的数据，可以考虑使用缓存

使用Redis或Memcached等缓存技术，将查询结果缓存到内存中，以提高查询速度

如果查询有重复的子查询，可以使用公共表表达式（CTE）进行优化

1	WITH subquery AS (SELECT column1 FROM table WHERE column2 = 'value') SELECT * FROM subquery JOIN table2 ON subquery.column1 = table2.column3;

避免在 WHERE 子句中对列进行函数计算

示例：

1	SELECT column1 FROM table WHERE YEAR(column2) = 2023;

改为：

1	SELECT column1 FROM table WHERE column2 >= '2023-01-01' AND column2 < '2024-01-01';

避免使用 UNION：UNION 会将多个查询合并在一起，会增加查询的执行时间和资源消耗，应该尽量避免使用

示例：

1
2
3

SELECT column1 FROM table1 WHERE column2 = 'value1'
UNION
SELECT column1 FROM table2 WHERE column2 = 'value2';

改为：

1	SELECT table1.column1 FROM table1 INNER JOIN table2 ON table1.column2 = 'value1' AND table2.column2 = 'value2';

25.2 优化表结构

合理设计表结构：在设计表结构时，应该尽量避免冗余字段、过多的索引、过多的数据类型等问题。应该根据具体业务需求设计合理的表结构，避免无谓的字段和表关系
优化数据类型：在设计表结构时，应该根据数据类型的实际需求选择合适的数据类型。应该尽量避免使用过大或过小的数据类型，可以使用 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 等整型数据类型来优化表结构，减少数据存储空间的占用
创建合适的索引：索引是提高查询性能的一个重要因素。在 WHERE、ORDER BY、GROUP BY、JOIN 等操作中经常用到的列应该加索引。可以使用 EXPLAIN 命令来查看查询执行计划，分析索引是否被正确地使用
分区表：对于大型数据表，可以使用分区表来将表分成多个小表，根据数据的范围或者其他条件来分区，提高查询效率
使用合适的存储引擎：MySQL 支持多种存储引擎，不同的存储引擎有不同的优缺点。可以根据实际情况选择合适的存储引擎来优化表结构。例如，InnoDB 存储引擎支持事务和行级锁，可以提高并发性能，MyISAM 存储引擎不支持事务但具有更高的查询速度
分离大字段：对于包含大字段（如 BLOB 或 TEXT）的表，可以考虑将这些字段独立出来，单独存储在一个表中，避免影响整个表的查询性能

25.3 优化服务器配置

调整缓存设置：MySQL 有多种缓存设置，包括 key_buffer_size、query_cache_size、innodb_buffer_pool_size、sort_buffer_size 等。这些缓存可以缓存查询结果、表索引、数据行等。根据实际情况，可以调整缓存大小，以便让 MySQL 更好地使用内存，提高性能
调整线程设置：MySQL 中的线程池可以控制连接数，防止过多连接导致服务器负载过高。可以根据实际情况调整连接数的大小，避免因为连接数过多而导致的性能问题
调整存储引擎设置：MySQL 支持多种存储引擎，不同的存储引擎对性能和稳定性的影响是不同的。可以根据实际情况选择合适的存储引擎，并进行相应的配置优化
配置查询缓存：MySQL 中的查询缓存可以缓存查询结果，避免重复查询。可以根据实际情况调整查询缓存的大小和使用情况，以便让 MySQL 更好地使用缓存，提高性能
优化操作系统设置：MySQL 运行在操作系统之上，操作系统的设置也会影响 MySQL 的性能和稳定性。可以根据实际情况调整操作系统的内存、磁盘、网络等设置，以提高 MySQL 的性能和稳定性

25.4 使用缓存

查询缓存：查询缓存可以缓存查询语句及其结果，如果再次执行相同的查询，就可以直接从缓存中获取结果，避免了重复查询数据库。开启查询缓存需要设置 query_cache_type=1 和 query_cache_size，具体可以在 MySQL 配置文件中设置
表缓存：表缓存可以缓存表的定义，避免每次查询时都需要重新解析表结构。开启表缓存需要设置 table_definition_cache 和 table_open_cache，具体可以在 MySQL 配置文件中设置

除了 MySQL 自带的缓存机制，还可以使用外部缓存工具，如 Memcached 和 Redis 等，将常用的数据缓存到内存中，以提高查询性能和响应速度。具体方法如下：

安装和配置 Memcached 或 Redis 服务器，并在应用程序中使用相应的客户端库连接到服务器
在应用程序中定义需要缓存的数据，并设置过期时间等参数，然后将数据存储到 Memcached 或 Redis 服务器中
在查询数据时，首先检查缓存中是否有相应的数据，如果有，则直接从缓存中获取数据，避免了查询数据库的过程

25.5 定期维护

备份数据库：备份数据库是保证数据安全的重要手段，可以通过 MySQL 自带的备份工具或者第三方备份工具进行备份。建议定期备份数据库，并将备份文件存储在安全的位置
优化表结构：定期检查表结构是否存在冗余字段和索引，删除不必要的字段和索引，可以提高查询性能和减少存储空间占用
清理无用数据：对于一些已经过期或者不再使用的数据，应该定期进行清理，以避免数据量过大导致查询性能下降
重建索引：定期重建索引可以保证索引的完整性和准确性，避免出现索引失效等问题
优化查询语句：定期检查慢查询日志，找出一些性能较差的查询语句，并进行优化，以提高查询性能
检查服务器资源：定期检查服务器资源的使用情况，如 CPU、内存、磁盘空间等，如果资源使用率过高，可以考虑升级服务器硬件或者调整数据库配置，以提高性能和稳定性
更新 MySQL 版本：定期更新 MySQL 版本可以获取最新的功能和安全修复，避免已知的安全漏洞和性能问题

25.6 使用连接池

使用连接池可以有效地提高数据库访问性能和减少连接的创建和销毁开销。以下是使用连接池的一般步骤：

导入连接池库：Java 中有许多第三方连接池库，如 c3p0、Druid、HikariCP 等，可以选择其中一个导入到项目中
配置连接池参数：在配置文件中设置连接池参数，如最大连接数、最小连接数、连接超时时间、空闲连接超时时间等参数
获取连接：在需要访问数据库时，从连接池中获取一个可用连接
执行 SQL：使用获取的连接执行 SQL 操作
释放连接：完成 SQL 操作后，将连接释放回连接池，以便其他线程使用

使用连接池时需要注意以下几点：

最大连接数：设置最大连接数时需要根据服务器硬件和应用负载情况进行调整，过高的最大连接数可能会导致服务器负载过高，而过低的最大连接数可能会导致连接池中的连接不足
连接超时时间：设置连接超时时间可以防止连接被长时间占用而无法释放，导致连接池中的连接耗尽
空闲连接超时时间：设置空闲连接超时时间可以定期清理空闲连接，避免连接池中的连接长时间闲置而浪费资源
异常处理：使用连接池时需要处理连接异常，如连接超时、数据库宕机等异常情况，避免应用程序崩溃

25.7 合理分布数据

将数据分布在多个物理磁盘上，可以避免磁盘的瓶颈，提高数据访问的效率。MySQL 可以通过以下两种方式来实现数据的分布式存储：

分区（Partitioning）：MySQL 提供了分区功能，可以将一个大的表分割成多个小的表，每个小表存储不同的数据，以便更好地控制数据的存储和访问

分区可以按照一定的规则（如按照时间、按照地区、按照数据类型等）将表分割成多个分区，每个分区存储不同的数据。分区可以根据需求选择不同的分区类型，如 RANGE、LIST、HASH 等，以实现数据的分布式存储

使用分区可以有效地避免磁盘的瓶颈，提高数据访问的效率。同时，也可以利用多个磁盘提高数据的可靠性，避免数据丢失。
垂直分割（Vertical Partitioning）：将一个大的表拆分成多个小的表，每个小表存储不同的列，以便更好地控制数据的存储和访问

垂直分割可以根据列的相关性将表拆分成多个小表，每个小表只包含相关的列，从而减少了数据的冗余，提高了数据的存储效率。同时，也可以利用多个磁盘提高数据的可靠性，避免数据丢

25.8 避免使用外部函数

在 MySQL 中，可以通过避免使用外部函数来提高查询的效率。外部函数是指不属于 MySQL 内部函数库的函数，如自定义函数或其他程序库中的函数。由于外部函数需要调用外部程序或动态链接库，因此会增加额外的开销和延迟，从而降低查询的效率

以下是一些避免使用外部函数的方法：

尽量使用 MySQL 内置函数：MySQL 内置函数已经被优化过，可以获得更好的性能。因此，尽量使用 MySQL 内置函数而不是外部函数，以提高查询的效率
使用存储过程和触发器：存储过程和触发器可以在 MySQL 中实现一些自定义的逻辑，避免使用外部函数。可以使用存储过程和触发器来代替一些复杂的查询，以提高查询的效率
将一些计算操作放到应用程序中完成：一些简单的计算操作，如字符串拼接、日期格式化等，可以在应用程序中完成，而不是在 MySQL 中使用外部函数。这样可以避免增加额外的开销和延迟，提高查询的效率
避免使用 UDF（User Defined Functions）：UDF 是 MySQL 中的自定义函数，虽然可以实现一些定制化的功能，但是也会增加额外的开销和延迟。因此，除非必要，应避免使用 UDF

25.9 使用慢查询日志

MySQL 的慢查询日志是一种记录慢查询语句的机制，可以记录执行时间超过一定阈值的查询语句，以帮助开发人员和 DBA 找出慢查询的原因，并进行优化。下面是使用慢查询日志的步骤：

启用慢查询日志：在 MySQL 配置文件中设置 slow_query_log 参数为 ON，设置 slow_query_log_file 参数为日志文件的路径。可以通过以下命令查看当前参数的值：
1
show variables like '%slow_query%';
设置慢查询阈值：通过设置 long_query_time 参数来确定查询执行时间的阈值，单位为秒。默认值为 10 秒，可以根据实际情况进行调整
查看慢查询日志：通过查看慢查询日志文件，可以找到执行时间超过阈值的查询语句。可以使用以下命令查看最近一段时间的慢查询日志：
1
mysqldumpslow -s t /var/log/mysql/mysql-slow.log
该命令将会按照查询执行时间排序，以 t 表示时间为单位进行汇总
分析慢查询日志：通过分析慢查询日志，可以找到慢查询的原因，并进行优化。可以根据查询语句的执行时间、执行计划等信息，找到查询中存在的瓶颈，进行优化
关闭慢查询日志：在优化完慢查询后，可以将慢查询日志关闭，以避免对系统性能产生影响
1
set global slow_query_log=0;

注意：慢查询日志可能会对系统性能产生一定的影响，因此应该在优化完毕后及时关闭。同时，慢查询日志记录的是执行时间超过阈值的查询语句，而并不是所有查询语句，因此需要合理设置阈值，以避免记录过多的查询语句

25.10 读写分离

MySQL 实现读写分离可以将读操作和写操作分开处理，提高系统的性能和可靠性。具体实现方式如下：

建立主从复制：将一个 MySQL 数据库作为主库，其他 MySQL 数据库作为从库，在主库上进行写操作，从库复制主库的数据，并提供读操作的服务
配置主库和从库：在主库和从库上配置相应的参数，以建立主从复制。主库需要开启 binlog，从库需要配置主库的 IP 地址和日志文件名等信息，以便从主库复制数据
配置读写分离：在应用服务器中配置读写分离的规则，使得应用程序在读取数据时连接从库，写入数据时连接主库。可以使用多种方式实现读写分离，如使用中间件、使用路由规则等
监控和管理：在实现读写分离后，需要对主从复制进行监控和管理。可以通过慢查询日志、系统监控工具等方式对数据库进行监控，及时发现并处理故障

注意：读写分离可以提高系统的性能和可靠性，但同时也可能会带来数据一致性的问题。因为从库的数据是通过复制主库的数据实现的，主从复制的延迟可能会导致从库中的数据与主库中的数据不一致。因此，在实现读写分离时，需要考虑数据一致性问题，采取相应的措施，如设置合理的复制延迟时间、使用全局锁等

26. SQL注入

Sql注入攻击是通过将恶意的Sql查询或添加语句插入到应用的输入参数中，再在后台Sql服务器上解析执行进行的攻击，它目前黑客对数据库进行攻击的最常用手段之一。

避免SQL注入：

参数校验，校验数据的类型，长度，特殊字符，数据库关键词等
代码层面防止SQL注入攻击的最佳方案是SQL预编译，尽量不写SQL原生语句，使用ORM
严格限制数据库权限
避免直接响应一些SQL异常信息，SQL发生异常后，自定义异常进行响应

27. MySQL、MongoDB、Redis、PostgreSQL区别，适用场景

27.1 MySQL

MySQL是一种关系型数据库管理系统，采用SQL语言进行操作，支持多种数据类型和事务处理。MySQL的应用场景非常广泛，尤其适用于需要处理大量结构化数据的应用程序。例如，金融数据、电子商务网站数据、在线预订系统和搜索引擎数据等。MySQL支持大多数主要操作系统，包括Linux、Windows和Mac OS X

27.2 MongoDB

MongoDB是一种面向文档的数据库管理系统，适用于需要处理非结构化和半结构化数据的应用程序，例如日志、用户信息和地理位置数据等。MongoDB的数据模型非常灵活，能够轻松处理可变模式和复杂层次结构数据。MongoDB还提供了自动分片和自动故障转移等高级功能，使其非常适合大型分布式系统

27.3 Redis

Redis是一种内存数据结构存储系统，支持各种数据结构，包括字符串、哈希、列表、集合和有序集合等。Redis的数据存储在内存中，因此速度非常快，但存储容量受限于可用内存大小。Redis的高性能和低延迟使其非常适合缓存和消息队列等应用场景。例如，网站缓存、会话存储、排行榜和实时数据分析等

27.4 PostgreSQL

PostgreSQL是一种功能丰富的关系型数据库管理系统，提供了广泛的数据类型、完整的事务支持、高级的并发控制和复杂的查询优化功能。PostgreSQL适用于需要处理大量复杂数据的应用程序，例如地理信息系统、社交网络和日志分析等

总结：MySQL适用于处理大量结构化数据，MongoDB适用于处理非结构化和半结构化数据，Redis适用于缓存和消息队列等应用场景，PostgreSQL适用于处理大量复杂数据的应用程序。当然，具体应该根据具体的应用场景和数据需求来选择最合适的数据库管理系统

28. MongoDB为什么比MySQL快

MongoDB和MySQL是两种不同类型的数据库，MongoDB是文档型数据库，而MySQL是关系型数据库

数据模型的差异：MongoDB使用文档型数据库模型，其中数据被组织为类似于JSON的文档，而MySQL使用关系型数据库模型，其中数据被组织为表。由于文档数据库模型具有更好的数据局部性，因此在某些查询和更新方面可能比关系型数据库更快
索引和查询优化：MongoDB具有基于内存的查询优化器和多种类型的索引，例如哈希、文本和地理空间索引。这些功能使MongoDB在某些查询方面具有更高的性能
高可用性和可伸缩性：MongoDB具有内置的复制和分片功能，这使得它可以更好地扩展到大型分布式系统中，并且在可用性方面更加强大
没有外键和事务的支持：MongoDB不支持外键和事务，这使得它可以更快地执行操作，并且在某些情况下可以避免锁定操作

注意：性能不仅取决于数据库的类型，还取决于许多其他因素，例如服务器硬件、网络带宽和数据库配置等。因此，在比较两个不同类型的数据库时，必须仔细考虑不同方面的性能需求，并根据具体情况做出选择

MongoDB面试题

1. 什么是MongoDB？它与关系型数据库有什么不同之处

MongoDB是一个开源的、面向文档的NoSQL数据库系统，采用了分布式文件存储的方式来存储数据。它是一款非常灵活和可扩展的数据库，具有高可用性、高性能和高扩展性，被广泛应用于Web、移动、物联网等应用场景

与关系型数据库相比，MongoDB有以下不同之处：

数据模型：MongoDB采用文档数据模型，数据以文档的形式存储，而关系型数据库则采用表格模型，数据以行和列的形式存储
事务处理：MongoDB在早期版本中不支持事务处理，但在最新版本中已经支持了多文档的事务处理，而关系型数据库则一直支持事务处理
数据查询：MongoDB采用基于文档的查询语言，支持丰富的查询方式，包括嵌套查询、范围查询、正则表达式查询等，而关系型数据库则采用SQL查询语言
扩展性：MongoDB可以很容易地进行横向扩展，通过添加更多的节点来扩展系统性能，而关系型数据库则通常需要通过垂直扩展来提高性能，即增加更强大的硬件
存储结构：MongoDB采用BSON（二进制JSON）格式来存储数据，而关系型数据库则采用基于行的存储结构

总结：MongoDB的文档数据模型和分布式架构使得它更适合于处理大量结构化和非结构化的数据，特别是在大数据、实时应用等方面有着明显的优势

2. 如何在MongoDB中进行聚合操作

在MongoDB中，聚合操作是指在一个或多个集合中执行多个操作，从而返回一个结果集合的过程。聚合操作可以用于数据分析和数据挖掘等领域，通常包括以下几个步骤：

创建聚合管道：使用聚合管道可以将多个操作连接起来，构成聚合操作的流程。聚合管道是一个由多个阶段组成的数组，每个阶段表示一个操作
筛选数据：使用 $match 操作符可以筛选符合条件的文档
分组数据：使用 $group 操作符可以将数据按照指定的字段进行分组，并对每个组进行聚合操作
过滤数据：使用 $project 操作符可以筛选需要输出的字段，并对输出结果进行格式化
排序数据：使用 $sort 操作符可以对输出结果进行排序
聚合数据：使用 $sum、$avg、$min、$max、$count 等聚合操作符可以对分组后的数据进行聚合操作
输出结果：使用 $out 操作符将聚合结果保存到一个集合中，或者使用 $merge 操作符将聚合结果合并到一个已存在的集合中

下面是一个简单的聚合操作示例，以 orders 集合为例：

db.orders.aggregate([
   { $match: { status: "complete" } },
   { $group: { _id: "$customer", total: { $sum: "$amount" } } },
   { $sort: { total: -1 } },
   { $project: { _id: 0, customer: "$_id", total: 1 } },
   { $out: "customer_total" }
])

这个聚合操作将 orders 集合中状态为“complete”的订单按照客户进行分组，并计算每个客户的订单总金额，然后按照总金额从高到低排序，只输出客户和总金额字段，并将结果保存到 customer_total 集合中

3. MongoDB聚合优化

聚合是一种强大的数据处理工具，可以用于处理大量的数据并生成复杂的结果。以下是一些MongoDB聚合优化的技巧：

索引优化：使用适当的索引可以极大地提高聚合查询的性能。在聚合查询中，尤其是在需要使用 $match、$sort、$group 等聚合管道操作的时候，索引可以帮助 MongoDB 避免全集合扫描，提高查询效率
使用尽可能少的管道：在聚合查询中，每个管道操作都会创建一个临时的中间文档，这些中间文档会在管道操作之间传递。因此，使用尽可能少的管道可以减少中间文档的数量，从而提高查询效率
使用合适的数据类型：在聚合查询中，使用合适的数据类型可以减少数据的转换和处理，提高查询效率。例如，将字符串类型的字段转换为数字类型，可以加快比较和排序的速度
使用投影操作符：在聚合查询中，可以使用投影操作符将结果集缩小到仅包含需要的字段，从而减少文档的大小，提高查询效率
限制结果集大小：在聚合查询中，可以使用 $limit 操作符限制结果集大小，从而减少文档的数量，提高查询效率
使用聚合管道的优化操作符：在 MongoDB 中，有许多聚合管道操作符可以帮助优化聚合查询，例如 $lookup、$unwind、$project、$match、$sort、$group 等
使用复合索引：如果需要对多个字段进行聚合查询，可以使用复合索引来提高查询效率。复合索引可以在多个字段上创建索引，从而提高查询效率

4. 什么是索引，如何在MongoDB中创建索引

在MongoDB中，索引是一种用于快速查找和排序文档的数据结构，可以提高查询性能和效率。MongoDB支持多种类型的索引，包括单字段索引、复合索引、地理空间索引等

创建索引可以通过 MongoDB shell 或驱动程序进行，具体步骤如下：

选择要创建索引的集合：使用 use 命令选择要创建索引的数据库，然后使用 db.collection 命令选择要创建索引的集合
选择要创建索引的字段：使用 createIndex 命令指定要创建索引的字段，可以是单个字段或多个字段组合
配置索引选项：可以通过指定不同的选项来配置索引的行为，例如设置唯一索引、设置部分索引、设置文本索引等

下面是一个创建单字段索引的示例，假设有一个名为 users 的集合，包含了用户的信息：

1	db.users.createIndex({ username: 1 })

这个命令将为 users 集合的 username 字段创建一个升序的索引。在创建索引后，可以使用 explain 命令来查看查询执行计划，以确定查询是否使用了索引

除了单字段索引之外，还可以创建复合索引，用于按照多个字段进行排序和查询，例如：

1	db.users.createIndex({ username: 1, age: -1 })

这个命令将为 users 集合的 username 和 age 字段创建一个复合索引，其中 username 字段按升序排序，age 字段按降序排序。复合索引可以提高查询性能和效率，但也需要注意索引的大小和维护成本等问题

总结：在 MongoDB 中创建索引可以提高查询性能和效率，但也需要根据实际情况选择适当的索引类型和配置选项，并进行维护和管理，以确保索引的有效性和可靠性

5. 如何在MongoDB中实现数据复制和故障转移

在MongoDB中，数据复制和故障转移是通过复制集（Replica Set）来实现的。复制集是一组相互关联的 MongoDB 实例，其中包括一个主节点和一个或多个副本节点。主节点用于处理所有写操作，并将写入操作同步到所有副本节点中。副本节点用于处理读操作，并在主节点失效时自动接管主节点的角色，实现故障转移

要创建一个复制集，可以按照以下步骤进行：

启动 MongoDB 实例：在每个要添加到复制集中的 MongoDB 实例上启动 MongoDB
配置 MongoDB 实例：对于每个 MongoDB 实例，可以配置其 replication.replSetName 属性来指定复制集的名称
初始化复制集：将其中一个 MongoDB 实例指定为主节点，然后将其他 MongoDB 实例添加为副本节点
监视复制集状态：可以使用 rs.status() 命令来监视复制集的状态，包括主节点和副本节点的状态、同步进度和故障转移情况等

下面是一个创建一个名为 myReplicaSet 的复制集的示例：

启动 MongoDB 实例：

1
2
3

mongod --replSet myReplicaSet --dbpath /data/db1 --port 27017
mongod --replSet myReplicaSet --dbpath /data/db2 --port 27018
mongod --replSet myReplicaSet --dbpath /data/db3 --port 27019

这个命令将在三个不同的端口上启动三个 MongoDB 实例，并将它们分别配置为 myReplicaSet 复制集的成员

初始化复制集：

在其中一个 MongoDB 实例上启动 MongoDB shell，并执行以下命令：

rs.initiate({
   _id: "myReplicaSet",
   members: [
      { _id: 0, host: "localhost:27017" },
      { _id: 1, host: "localhost:27018" },
      { _id: 2, host: "localhost:27019" }
   ]
})

这个命令将初始化一个名为 myReplicaSet 的复制集，并将三个 MongoDB 实例分别配置为成员

监视复制集状态：

执行以下命令可以查看复制集的状态：
1
rs.status()
这个命令将显示复制集中每个成员的状态、同步进度和故障转移情况等信息

6. MongoDB如何处理数据的一致性

在MongoDB中，数据一致性是通过使用复制集（Replica Set）来保证的。复制集是一组相互关联的MongoDB实例，其中包括一个主节点和一个或多个副本节点。主节点用于处理所有写操作，并将写入操作同步到所有副本节点中。副本节点用于处理读操作，并在主节点失效时自动接管主节点的角色，实现故障转移

在MongoDB中，写操作会首先在主节点上执行，主节点会将写操作的结果同步到所有副本节点中，确保数据的一致性。当读操作发生时，客户端可以选择在主节点或副本节点上进行读取。如果在副本节点上进行读取，则副本节点会从主节点同步最新的数据，并提供客户端读取

在MongoDB中，如果主节点失效，副本节点会自动接管主节点的角色，并继续提供写操作和读操作服务。在这个过程中，MongoDB使用Raft算法来保证数据的一致性。Raft算法是一种分布式一致性算法，它可以确保数据的一致性，并在主节点失效时进行选举，选择新的主节点来继续提供服务

除了复制集外，MongoDB还提供了其他一些功能来处理数据的一致性，例如事务和读写关注点分离。通过使用事务，可以将多个操作组合在一起，以确保数据的原子性、一致性、隔离性和持久性。通过读写关注点分离，可以将读操作和写操作分别路由到不同的MongoDB实例上，以提高读写性能和减少写操作的锁竞争

总结：在MongoDB中，通过复制集、事务和读写关注点分离等功能，可以确保数据的一致性和可靠性，提高数据库的可用性和可靠性

7. MongoDB支持哪些数据模型？如何选择适当的数据模型

MongoDB支持多种数据模型，包括关系型数据模型、文档数据模型和图形数据模型

关系型数据模型是传统的数据库模型，通过表格和行来表示数据，并使用SQL进行查询和操作。MongoDB支持使用SQL进行查询，但并不是主要的查询语言

文档数据模型是MongoDB的主要数据模型，它使用JSON格式表示数据，每个文档可以包含任意数量的字段和嵌套文档。文档数据模型非常适合表示复杂的、半结构化的数据，例如日志、产品目录和用户配置

图形数据模型是用于表示复杂关系的数据模型，它使用节点和边来表示数据，并提供了一些特殊的查询和操作语言。MongoDB支持图形数据模型，但这并不是它的主要特点

当选择数据模型时，需要考虑以下因素：

数据的结构：如果数据有一个固定的结构，可以考虑使用关系型数据库。如果数据具有动态或半结构化的特性，可以考虑使用文档数据模型
数据的关系：如果数据包含多个表格或实体之间的关系，可以考虑使用关系型数据库。如果数据具有复杂的关系，例如一对多或多对多关系，可以考虑使用文档数据模型或图形数据模型
数据的可扩展性：如果需要处理大量数据或需要横向扩展，可以考虑使用文档数据模型或图形数据模型，因为它们支持自动分片和横向扩展
数据的查询和分析：如果需要高效地查询和分析数据，可以考虑使用文档数据模型或图形数据模型。因为它们支持索引和聚合操作

总结：MongoDB的文档数据模型非常适合表示动态或半结构化的数据，并具有很好的可扩展性和灵活性。在选择数据模型时，需要根据实际需求考虑不同的因素，并选择最适合的数据模型

8. 如何在MongoDB中进行备份和还原操作

MongoDB中备份和还原数据可以通过mongodump和mongorestore工具来实现

备份数据：

使用mongodump工具来备份数据。在终端输入以下命令：mongodump –host –port –db –out 。其中，hostname为MongoDB主机名或IP地址，port为MongoDB端口号，database为需要备份的数据库名称，directory_path为备份文件的目录
执行上述命令后，mongodump会将指定的数据库备份到指定的目录中。备份文件的格式为BSON格式，包含了指定数据库的所有数据和索引

还原数据：

使用mongorestore工具来还原数据。在终端输入以下命令：mongorestore –host –port –db 。其中，hostname为MongoDB主机名或IP地址，port为MongoDB端口号，database为需要还原的数据库名称，directory_path为备份文件的目录
执行上述命令后，mongorestore会将指定目录中的备份文件还原到指定的数据库中

注意：在进行备份和还原操作时，需要使用相同版本的MongoDB工具。此外，备份和还原操作可能会影响数据库的性能，因此需要在合适的时间进行操作，并确保备份文件的存储和管理安全可靠

9. 如何在MongoDB中实现数据分片

MongoDB使用数据分片来横向扩展数据存储能力，通过将数据分散到多个节点上，从而提高数据库的性能和可扩展性。以下是在MongoDB中实现数据分片的一般步骤：

部署分片集群：首先需要部署一个分片集群，该集群由多个节点组成。一个分片集群通常包含3个配置节点、3个查询路由节点和多个数据分片节点
创建分片键：在MongoDB中，分片键用于确定数据如何被分片和分布到各个节点上。通常可以选择一个唯一或频繁查询的字段作为分片键。例如，在一个电子商务网站中，可以选择商品ID作为分片键，从而将同一商品的数据存储在同一个节点上
启用分片功能：在分片集群中启用分片功能，执行sh.enableSharding(dbname)命令，其中dbname是需要分片的数据库名称。这将启用分片功能，并为该数据库创建一个空的chunks集合
配置分片：执行sh.shardCollection命令来配置分片，将需要分片的集合和分片键传递给该命令。例如，执行sh.shardCollection(“testDB.testCollection”, { “shardingKey” : 1 })，将testDB数据库中的testCollection集合分片，并使用shardingKey字段作为分片键
向分片集群中添加节点：可以在分片集群中添加更多的节点，通过在节点中添加新的数据分片来扩展集群的存储能力。可以通过执行sh.addShard命令来添加新的节点
监控集群状态：可以使用sh.status命令来监视分片集群的状态，包括各个节点的负载情况和分片数据的分布情况

注意：数据分片是一项复杂的操作，需要考虑多个因素，包括数据量、查询负载、数据分布情况等。在实现数据分片之前，需要仔细评估和规划，确保分片集群能够满足业务需求，并具有良好的可扩展性和可靠性

10. 如何在MongoDB中进行事务处理

MongoDB从版本4.0开始支持多文档事务处理，提供了在多个文档之间执行原子操作的能力，从而确保数据的一致性和完整性。以下是在MongoDB中进行事务处理的一般步骤：

启用事务功能：在MongoDB中启用事务功能，可以通过在会话级别打开事务，或者在集合级别打开事务。对于会话级别的事务，可以使用session.startTransaction()方法来启用事务。对于集合级别的事务，可以在执行任何涉及多个集合的操作之前，使用db.getCollection(collectionName).startSession()方法创建一个会话对象，并在该对象上调用session.startTransaction()方法来启用事务
执行事务操作：一旦启用了事务，可以在一个事务中执行多个操作，包括插入、更新、删除等操作。在MongoDB中，可以将多个操作放在一个事务中执行，如果其中一个操作失败，则整个事务将被回滚，所有操作都将被撤销
提交或回滚事务：一旦执行完所有操作，可以通过调用session.commitTransaction()方法来提交事务，或者调用session.abortTransaction()方法来回滚事务。提交事务将永久保存所有操作的结果，而回滚事务将撤销所有操作的结果

注意：MongoDB的事务处理功能在一些情况下可能会有性能上的损失，因此应该仔细评估业务需求，选择是否使用事务处理功能。另外，在使用事务处理功能时，还需要注意遵守MongoDB的事务处理规则和限制，例如，事务不能跨越分片，事务不能涉及多个数据库等

11. MongoDB的数据存储格式是什么？它与JSON有什么不同之处

MongoDB的数据存储格式是BSON（Binary JSON），它是一种二进制的JSON格式，由C++编写的BSON库将JSON对象序列化为二进制形式，从而提供了更高的效率和更好的可扩展性。BSON格式支持多种数据类型，包括字符串、数字、日期、布尔、数组、嵌套文档等，因此可以在MongoDB中存储非常灵活的数据结构

与JSON相比，BSON格式有以下不同之处：

BSON是二进制格式，而JSON是文本格式。由于BSON是二进制格式，因此比JSON更加紧凑和高效，可以更快地进行序列化和反序列化操作。另外，BSON支持存储二进制数据、日期和时间等数据类型，而JSON不支持
BSON支持嵌套文档和数组。BSON格式支持嵌套文档和数组，因此可以更灵活地存储数据，而JSON只支持数组和键值对
BSON支持数据类型的扩展。BSON支持数据类型的扩展，因此可以在不破坏现有应用程序的情况下添加新的数据类型。而JSON是一种固定的数据格式，不支持扩展

总结：BSON是MongoDB专门为存储和检索数据而设计的二进制格式，相对于JSON来说更加高效、灵活和可扩展，因此可以更好地满足MongoDB的数据存储和查询需求

PostgreSQL面试题

1. 什么是PostgreSQL？请介绍它的特点和优势

PostgreSQL是一种开源的关系型数据库管理系统（RDBMS），它支持大部分SQL标准，同时还提供了许多先进的功能和扩展性。以下是PostgreSQL的一些特点和优势：

可扩展性：PostgreSQL可以在各种规模的应用程序中使用，从小型单用户应用程序到大型企业级数据库应用程序，支持高并发、高吞吐量和大数据量
可靠性：PostgreSQL具有高度的稳定性和可靠性，支持事务、崩溃恢复和备份等功能，以确保数据的完整性和持久性
安全性：PostgreSQL提供了高级的安全性和访问控制机制，包括强制访问控制和行级别安全性等功能，以保护敏感数据
可定制性：PostgreSQL具有可定制性高的特点，可以根据应用程序的需要添加或删除功能
支持多种数据类型：除了标准的数据类型之外，PostgreSQL还支持许多特定的数据类型，如几何数据、网络地址、JSON等
免费开源：PostgreSQL是一个免费的开源项目，任何人都可以自由使用、修改和分发它的代码
社区支持：PostgreSQL有一个庞大的社区支持，用户可以从社区中获取帮助、学习和分享知识

2. 请解释一下PostgreSQL中的MVCC是什么

MVCC是PostgreSQL中的一种多版本并发控制技术，它可以实现高并发的事务处理，同时保证数据的一致性和完整性

在MVCC中，每个数据库事务在执行之前会获得一个唯一的事务ID，这个ID被称为XID。当一个事务更新数据时，PostgreSQL会将数据的旧版本存储在一个叫做Undo Buffer的地方，并在新版本的数据中记录对旧版本数据的修改。因此，在一个事务中，数据有多个版本存在，并且这些版本可以被同时访问和修改，而不会影响其他事务的操作

MVCC的核心思想是使用版本控制来管理事务的并发性，每个事务都看到自己的视图，因此可以避免锁的使用，减少锁等待的时间，提高并发性能。同时，由于每个事务看到的是一个一致性的数据视图，因此不会出现脏读、不可重复读等数据一致性问题

3. 什么是索引？PostgreSQL中有哪些类型的索引

索引是数据库中的一种数据结构，用于提高数据的检索速度。索引通常是在一列或多列上创建的，并按照一定的规则进行排序和存储，以支持快速的数据查找和访问。索引可以大大提高数据库查询的性能和效率

PostgreSQL中支持多种类型的索引，包括以下几种：

B-tree索引：B-tree是一种常用的索引结构，可以用于支持等值、范围和模糊查询。在PostgreSQL中，B-tree索引是默认的索引类型
哈希索引：哈希索引是一种基于哈希表的索引结构，可以用于支持等值查询。在PostgreSQL中，哈希索引通常用于处理大型数据集的等值查询，因为哈希索引可以在O(1)的时间复杂度内进行查找
GiST索引：GiST（通用搜索树）索引是一种通用的索引结构，可以用于支持各种数据类型的查询，包括几何数据和全文搜索等
SP-GiST索引：SP-GiST（空间分割通用搜索树）索引是一种空间索引结构，用于支持空间数据类型的查询
GIN索引：GIN（通用倒排索引）索引是一种倒排索引结构，用于支持多值数据类型的查询，例如数组和JSON等
BRIN索引：BRIN（块范围索引）索引是一种块范围索引结构，用于支持大型数据集的范围查询。BRIN索引可以有效地减少磁盘空间的使用，并提高范围查询的性能

除了以上几种索引类型，PostgreSQL还支持自定义索引类型，可以根据具体的应用场景进行自定义的索引设计和实现

4. 如何在PostgreSQL中备份和还原数据库

使用pg_dump和pg_restore命令进行备份和还原：
- 备份数据库：
  1
  pg_dump -U username -h hostname -p port databasename > backup.sql
  其中，username是数据库用户名，hostname是数据库服务器地址，port是数据库服务器端口号，databasename是需要备份的数据库名称，backup.sql是备份文件名
- 还原数据库：
  1
  psql -U username -h hostname -p port -d databasename < backup.sql
  其中，username是数据库用户名，hostname是数据库服务器地址，port是数据库服务器端口号，databasename是需要还原的数据库名称，backup.sql是备份文件名
使用pg_basebackup和pg_receivexlog命令进行备份和还原：
- 备份数据库：
  1
  pg_basebackup -U username -h hostname -p port -D backup_directory -Ft -z -Xs -P
  其中，username是数据库用户名，hostname是数据库服务器地址，port是数据库服务器端口号，backup_directory是备份文件目录，-Ft选项表示使用tar格式进行备份，-z选项表示使用gzip进行压缩，-Xs选项表示备份时只备份WAL日志文件的切片，-P选项表示在屏幕上显示进度
- 还原数据库：
  1
  pg_receivexlog -U username -h hostname -p port -D backup_directory -S slotname
  其中，username是数据库用户名，hostname是数据库服务器地址，port是数据库服务器端口号，backup_directory是备份文件目录，slotname是复制槽的名称

5. PostgreSQL中的B-tree索引是如何工作的

PostgreSQL中，B-tree索引是默认的索引类型，在数据量较大时可以提供高效的查询性能。B-tree是一种自平衡的树形数据结构，可以用于快速的等值、范围和模糊查询。B-tree索引使用二叉树的结构，每个节点最多可以包含m个子节点，其中m称为B-tree的阶数。在PostgreSQL中，B-tree的默认阶数是4，可以通过修改参数进行调整

当创建一个B-tree索引时，PostgreSQL会在磁盘上创建一个B-tree数据结构，并将索引中的每个值存储在叶子节点上。每个非叶子节点都包含一组索引键，用于将索引值划分为更小的子集。在查询时，PostgreSQL会从根节点开始沿着B-tree逐级查找，直到找到匹配的叶子节点。由于B-tree的节点通常可以存储多个值，因此在一个节点上查找匹配值的时间复杂度是O(logm)，其中m是B-tree的阶数。因此，B-tree索引可以实现快速的数据检索和访问

B-tree索引还支持范围查询和模糊查询。对于范围查询，B-tree索引使用区间搜索技术，在索引键值之间的范围内搜索匹配的数据。对于模糊查询，B-tree索引使用通配符搜索技术，在索引键值中查找匹配的数据

6. PostgreSQL中的锁是什么？有哪些类型的锁

在PostgreSQL中，锁是一种用于管理并发访问的机制，可以确保多个会话之间的数据一致性和可靠性。PostgreSQL支持多种类型的锁，包括共享锁和排他锁等。下面是各种锁的类型及其用途：

共享锁（Shared Lock）：也称为读锁。当一个会话获取共享锁时，其他会话仍然可以读取数据，但不能修改数据。共享锁适用于在并发访问情况下防止数据读取冲突
排他锁（Exclusive Lock）：也称为写锁。当一个会话获取排他锁时，其他会话既不能读取数据，也不能修改数据。排他锁适用于在并发访问情况下防止数据写入冲突
行级锁（Row-level Lock）：也称为记录锁或行锁。行级锁用于锁定特定的数据行，只有持有该行锁的会话才能对该行进行修改。行级锁适用于高并发情况下防止多个会话同时修改同一行数据
表级锁（Table-level Lock）：也称为全局锁。表级锁用于锁定整个数据表，只有持有该表级锁的会话才能对该表进行修改。表级锁适用于执行DDL语句或在某些特定场景下防止其他会话对表进行修改
页级锁（Page-level Lock）：也称为块锁或区锁。页级锁用于锁定特定的数据页，只有持有该页锁的会话才能对该页进行修改。页级锁适用于在高并发访问情况下防止多个会话同时修改同一数据页
记录锁（Tuple-level Lock）：也称为元组锁。记录锁用于锁定特定的数据记录，只有持有该记录锁的会话才能对该记录进行修改。记录锁是行级锁的一种实现方式
兼容锁（Compatible Lock）：也称为兼容性锁。当一个会话获取兼容锁时，其他会话可以读取该数据，但不能修改该数据。兼容锁适用于在读写混合访问情况下，防止读操作和写操作之间的冲突

在PostgreSQL中，锁是由系统自动管理的，可以通过SQL命令和函数进行访问和管理。掌握锁的使用方法和技巧，可以帮助开发人员更好地处理并发访问和数据一致性问题

7. 如何在PostgreSQL中处理JSON数据类型

创建JSON类型列

CREATE TABLE mytable (
    id integer PRIMARY KEY,
    data json
);

插入JSON数据

1	INSERT INTO mytable (id, data) VALUES (1, '{"name": "John", "age": 30}');

查询JSON数据

1	SELECT data->>'name' as name, data->>'age' as age FROM mytable WHERE id = 1;

该语法使用->>运算符从JSON列中提取指定的JSON属性

更新JSON数据

1	UPDATE mytable SET data = '{"name": "Jack", "age": 35}' WHERE id = 1;

删除JSON数据
1
DELETE FROM mytable WHERE id = 1;
JSON函数

PostgreSQL提供了一些内置的JSON函数，可以帮助处理JSON数据。一些常用的JSON函数包括：
- json_object(text, ...): 创建一个JSON对象
- json_array(...)：创建一个JSON数组
- json_extract_path(json, VARIADIC text[])：从JSON数据中提取指定路径的JSON值
- jsonb_exists(jsonb, VARIADIC text[])：判断JSON数据是否包含指定路径的JSON值
JSON索引

PostgreSQL还支持使用GIN索引来加速JSON数据的查询。可以使用以下语法在JSON列上创建GIN索引：
1
CREATE INDEX idx_data ON mytable USING gin(data);
该语法创建一个GIN索引，可以在JSON数据中快速查询指定的JSON属性

8. 请介绍一下PostgreSQL中的继承

在PostgreSQL中，继承是一种用于实现表继承关系的机制。使用继承，可以创建一个父表并定义一组子表，子表会继承父表的所有列和约束。这种设计模式使得数据库表的设计更加灵活，可以避免数据冗余和代码重复

在继承关系中，父表被称为父级表或基表，子表被称为子级表或派生表。子表继承父表的所有属性，包括列、约束、索引和触发器。当在子表中插入一条记录时，这条记录会同时插入到父表中。同样，当在父表中更新一条记录时，所有子表中与该记录相关的数据也会相应更新

在继承关系中，还可以定义一些特殊的约束和属性，包括：

NOT NULL约束：可以在父表中定义NOT NULL约束，这个约束会自动继承到所有子表中
CHECK约束：可以在父表中定义CHECK约束，这个约束同样会自动继承到所有子表中
父子表之间的关系：父子表之间的关系可以通过在子表中定义FOREIGN KEY约束实现。子表中的FOREIGN KEY约束必须引用父表的主键约束
查询子表数据：可以使用UNION ALL操作符将父表和所有子表的数据组合在一起，也可以单独查询父表或子表中的数据

注意：在使用继承关系时，父表和子表必须使用相同的表结构和数据类型，否则可能会导致查询错误或数据不一致。此外，继承关系还可能对查询性能产生影响，需要根据具体情况进行优化

9. 如何在PostgreSQL中进行数据复制和高可用性配置

PostgreSQL提供了多种机制来进行数据复制和高可用性配置，下面介绍两种常用的方法：

数据复制

PostgreSQL支持两种方式的数据复制：流复制和逻辑复制。流复制是将主数据库上的事务日志传输到备份服务器上，然后在备份服务器上重放这些日志以达到同步主从数据库的目的。逻辑复制则是将指定表的更改信息通过逻辑复制插件传输到备份服务器，然后在备份服务器上重新执行这些更改以达到同步主从数据库的目的。使用复制，可以实现数据的备份、读写分离、负载均衡等功能。
高可用性配置

PostgreSQL支持多种高可用性配置方法，包括：
- 复制：通过流复制或逻辑复制实现主备切换，从而实现高可用性
- Slony-I：基于触发器的复制机制，可以实现多节点的复制
- Pgpool-II：支持连接池、查询路由、负载均衡和自动故障转移等功能，可以提高应用程序的可用性和性能
- Patroni：一个高可用性集群管理工具，使用Zookeeper、etcd或Consul进行协调，支持主从切换、自动故障转移和复制监控等功能
- Postgres-XL：一个分布式数据库系统，提供水平扩展和高可用性配置功能，支持全局表、多主复制和分片等功能

10. 如何在PostgreSQL中进行优化查询性能

创建索引：创建合适的索引可以大幅提高查询性能。可以根据查询语句的条件字段创建相应的索引，例如使用B-tree索引来加速排序和范围查询，使用哈希索引来加速等值查询，使用全文索引来进行文本搜索等
避免全表扫描：尽量避免对整个表进行扫描，可以使用索引或者分区表来减少扫描范围
使用EXPLAIN分析查询计划：可以使用EXPLAIN命令来分析查询语句的执行计划，找出可能的瓶颈并进行优化
优化数据模型：合理设计数据模型可以减少查询时的连接操作和聚合计算，提高查询效率
合理设置参数：根据具体的硬件配置和应用场景，合理设置PostgreSQL的参数可以提高查询性能。例如shared_buffers、work_mem、maintenance_work_mem等参数
使用表分区：将大表按照一定的规则分成多个小表，可以提高查询效率，减少锁的竞争
使用连接池：连接池可以减少连接的创建和销毁，降低数据库的负载，提高应用程序的响应速度

本文链接：
https://huajun-chen.github.io/2022/10/28/MySQL面试题/