数据库知识

数据库

PostgreSQL相对于MySQL的优势

1. 不仅仅是关系型数据库

除了存储正常的数据类型外，还支持存储：

array，不管是一位数组还是多为数组均支持

json(hStore)和jsonb，相比使用text存储接送要高效很多，json存储的是文本类型，类似于mysql中的text类型

• json与jsonb之间的区别：jsonb是json的二进制形式
• json格式写入快，但读取慢；
• jsonb格式写入慢，但读取快。

json和jsonb之间的区别

jsonb和json在更高的层面上看起来几乎是一样的，但在存储实现上是不同的。

json存储完的文本，json列会每次都解析存储的值，它不支持索引，但你可以为查询创建表达式索引。

jsonb存储的二进制格式，避免了重新解析数据结构。它支持索引，这意味着你可以不使用指定的索引就能查询任何路径。

当我们比较写入数据速度时，由于数据存储的方式的原因，jsonb会比json稍微的慢一点。json列会每次都解析存储的值，这意味着键的顺序要和输入的时候一样。但jsonb不同，以二进制格式存储且不保证键的顺序。因此，如果你有软件需要依赖键的顺序，jsonb可能不是你的应用的最佳选择。使用jsonb的优势还在于你可以轻易的整合关系型数据和非关系型数据， PostgreSQL对于mongodb这类的基于文档的数据库是个不小的威胁，毕竟如果一个表中只有一列数据的类型是半结构化的，没有必要为了迁就它而整个表的设计采用schemaless的结构。

2)支持地理信息处理扩展

PostGIS 为PostgreSQL提供了存储空间地理数据的支持，使PostgreSQL成为了一个空间数据库，能够进行空间数据管理、数量测量与几何拓扑分析。在功能上，和MYSQL对比，PostGIS具有下列优势：

O2O业务场景中的LBS业务使用PostgreSQL + PostGIS有无法比拟的优势。

3)可以快速构建REST API

PostgREST 可以方便的为任何 PostgreSQL 数据库提供完全的 RESTful API 服务。

4)支持树状结构

支持R-trees这样可扩展的索引类型，可以更方便地处理一些特殊数据。MySQL 处理树状的设计会很复杂, 而且需要写很多代码, 而 PostgreSQL 可以高效处理树结构。

5)有极其强悍的 SQL 编程能力

支持递归，有非常丰富的统计函数和统计语法支持。

MySQL：支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。

PostgreSQL：没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL(专用的过程语言)、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。

6)外部数据源支持

可以把 70 种外部数据源 (包括 Mysql, Oracle, CSV, hadoop …) 当成自己数据库中的表来查询。Postgres有一个针对这一难题的解决方案：一个名为“外部数据封装器(Foreign Data Wrapper，FDW)”的特性。该特性最初由PostgreSQL社区领袖Dave Page四年前根据SQL标准SQL/MED(SQL Management of External Data)开发。FDW提供了一个SQL接口，用于访问远程数据存储中的远程大数据对象，使DBA可以整合来自不相关数据源的数据，将它们存入Postgres数据库中的一个公共模型。这样，DBA就可以访问和操作其它系统管理的数据，就像在本地Postgres表中一样。例如，使用FDW for MongoDB，数据库管理员可以查询来自文档数据库的数据，并使用SQL将它与来自本地Postgres表的数据相关联。借助这种方法，用户可以将数据作为行、列或JSON文档进行查看、排序和分组。他们甚至可以直接从Postgres向源文档数据库写入(插入、更细或删除)数据，就像一个一体的无缝部署。也可以对Hadoop集群或MySQL部署做同样的事。FDW使Postgres可以充当企业的中央联合数据库或“Hub”。

7)没有字符串长度限制

一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而PostgreSQL的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。MySQL 的各种text字段有不同的限制，要手动区分 small text, middle text, large text… PostgreSQL 没有这个限制，text 能支持各种大小。

8)支持图结构数据存储

没有具体使用过，具体可以自己搜索下。参考链接：https://mp.weixin.qq.com/s/cjor82wgDu5gzDvTYpLDWw

9)支持窗口函数

窗口函数提供跨行相关的当前查询行集执行计算的能力。仅当调用跟着OVER子句的聚集函数，作为窗口函数;否则它们作为常规的聚合函数。窗口也是一种分组，但和 group by 的分组不同。窗口，可以提供分组之外，还可以执行对每个窗口进行计算。可以想象成是group by 后，然后对每个分组进行计算，而不像Group by ，只是单纯地分组。MySQL 不支持 OVER 子句, 而PostgreSQL支持。OVER 子句能简单的解决 “每组取 top 5” 的这类问题。MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式(Oracle的with 语句)或者窗口函数(分析函数)。

10)对索引的支持更强

PostgreSQL 的可以使用函数和条件索引，这使得PostgreSQL数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。对于索引类型：

MySQL：取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。

PostgreSQL：支持 B-树、哈希、R-树和 Gist 索引。

InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。PostgreSQL不存在这个问题。

索引类型方面，MySQL取决于存储引擎。MyISAM：BTREE，InnoDB：B+TREE。PostgreSQL支持 B-树、哈希、R-树和 Gist 索引。

11)集群支持更好

Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。复制(Replication)功能是异步的并且有很大的局限性。例如，它是单线程的(single-threaded)，因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master。

PostgreSQL有丰富的开源cluster软件支持。plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。

另外，PostgreSQL的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。对于WEB应用来说，复制的特性很重要，mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。

12)事务隔离做的更好

MySQL 的事务隔离级别 repeatable read 并不能阻止常见的并发更新, 得加锁才可以, 但悲观锁会影响性能, 手动实现乐观锁又复杂. 而 PostgreSQL 的列里有隐藏的乐观锁 version 字段, 默认的 repeatable read 级别就能保证并发更新的正确性, 并且又有乐观锁的性能。

13)对于字符支持更好一些

MySQL 里需要 utf8mb4 才能显示 emoji 的坑, PostgreSQL 没这个坑。

14)对表连接支持较完整

对表连接支持较完整，MySQL只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。PostgreSQL都支持。

15)存储方式支持更大的数据量

PostgreSQL主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。

16)时间精度更高

MySQL对于时间、日期、间隔等时间类型没有秒以下级别的存储类型，而PostgreSQL可以精确到秒以下。

17)优化器的功能较完整

MySQL对复杂查询的处理较弱，查询优化器不够成熟，explain看执行计划的结果简单。性能优化工具与度量信息不足。

PostgreSQL很强大的查询优化器，支持很复杂的查询处理。explain返回丰富的信息。提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。

18)序列支持更好

MySQL 不支持多个表从同一个序列中取 id, 而 PostgreSQL 可以。

19)对子查询支持更好

对子查询的支持。虽然在很多情况下在SQL语句中使用子查询效率低下，而且绝大多数情况下可以使用带条件的多表连接来替代子查询，但是子查询的存在在很多时候仍然不可避免。而且使用子查询的SQL语句与使用带条件的多表连接相比具有更高的程序可读性。几乎任何数据库的子查询 (subquery) 性能都比 MySQL 好。

20)增加列更加简单

MySQL表增加列，基本上是重建表和索引，会花很长时间。PostgreSQL表增加列，只是在数据字典中增加表定义，不会重建表.

MySQL相对于PostgreSQL的优势

1)MySQL比PostgreSQL更流行

流行对于一个商业软件来说，也是一个很重要的指标，流行意味着更多的用户，意味着经受了更多的考验，意味着更好的商业支持、意味着更多、更完善的文档资料。易用，很容易安装。第三方工具，包括可视化工具，让用户能够很容易入门。

2)回滚实现更优

innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。

3)在Windows上运行更可靠

与PostgreSQL相比，MySQL更适宜在Windows环境下运行。MySQL作为一个本地的Windows应用程序运行(在 NT/Win2000/WinXP下，是一个服务)，而PostgreSQL是运行在Cygwin模拟环境下。PostgreSQL在Windows下运行没有MySQL稳定，应该是可以想象的。

4)线程模式相比进程模式的优势

MySQL使用了线程，而PostgreSQL使用的是进程。在不同线程之间的环境转换和访问公用的存储区域显然要比在不同的进程之间要快得多。

进程模式对多CPU利用率比较高。进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。

线程模式对资源消耗比较少。所以MySQL能支持远比PostgreSQL多的更多的连接。但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。

5)权限设置上更加完善

MySQL在权限系统上比PostgreSQL某些方面更为完善。PostgreSQL只支持对于每一个用户在一个数据库上或一个数据表上的 INSERT、SELECT和UPDATE/DELETE的授权，而MySQL允许你定义一整套的不同的数据级、表级和列级的权限。对于列级的权限， PostgreSQL可以通过建立视图，并确定视图的权限来弥补。MySQL还允许你指定基于主机的权限，这对于目前的PostgreSQL是无法实现的，但是在很多时候，这是有用的。

6)存储引擎插件化机制

MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

7)适应24/7运行

MySQL可以适应24/7运行。在绝大多数情况下，你不需要为MySQL运行任何清除程序。PostgreSQL目前仍不完全适应24/7运行，这是因为你必须每隔一段时间运行一次VACUUM。

8)更加试用于简单的场景

PostgreSQL只支持堆表，不支持索引组织表，Innodb只支持索引组织表。

索引组织表的优势：表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。

索引组织表的劣势：索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。

对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。

由于索引组织表是按一个索引树，一般它访问数据块必须按数据块之间的关系进行访问，而不是按物理块的访问数据的，所以当做全表扫描时要比堆表慢很多，这可能在OLTP中不明显，但在数据仓库的应用中可能是一个问题。

存储引擎

概念

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、
更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同
的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引
擎。存储引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Archive, 5. Federated

InnoDB（ （ B+树 ）

InnoDB 底层存储结构为B+树， B树的每个节点对应innodb的一个page，page大小是固定的，
一般设为 16k。其中非叶子节点只有键值，叶子节点包含完成数据。

适用场景：
1）经常更新的表，适合处理多重并发的更新请求。
2）支持事务。
3）可以从灾难中恢复（通过 bin-log 日志等）。
4）外键约束。只有他支持外键。
5）支持自动增加列属性 auto_increment。

MyIASM

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，
因此当 INSERT(插入)或 UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
ISAM 执行读取操作的速度很快，而且不占用大量的内存和存储资源。在设计之初就预想数据组织
成有固定长度的记录，按顺序存储的。—ISAM 是一种静态索引结构。
缺点是它不 支持事务处理。

Memory

Memory（也叫 HEAP）堆内存：使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应
一个磁盘文件。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用
HASH 索引。但是一旦服务关闭，表中的数据就会丢失掉。 Memory 同时支持散列索引和 B 树索
引，B树索引可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘，散列索
引相等的比较快但是对于范围的比较慢很多。

索引

索引（Index）是帮助 MySQL 高效获取数据的数据结构。常见的查询算法,顺序查找,二分查找,二
叉排序树查找,哈希散列法,分块查找,平衡多路搜索树 B 树（B-tree

• 常见索引原则有

1. 选择唯一性索引
   1． 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
2. 为经常需要排序、分组和联合操作的字段建立索引 ：
   3 ．为常作为查询条件的字段建立 索引 。
   4 ．限制索引的数目：
   越多的索引，会使更新表变得很浪费时间。
   尽量使用数据量少的索引
   6． 如果索引的值很长，那么查询的速度会受到影响。
   尽量使用前缀来索引
   7． 如果索引字段的值很长，最好使用值的前缀来索引。
   7 ．删除不再使用或者很少使用的索引
   8 . 最左前缀匹配原则，非常重要的原则。
   10 . 尽量选择区分度高的列作为索引
   区分度的公式是表示字段不重复的比例
   11 . 索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。
   12 . 尽量的扩展索引，不要新建索引。

数据库三范式

第一范式(1st NF －列都是不可再分)
第一范式的目标是确保每列的原子性:如果每列都是不可再分的最小数据单元（也称为最小的原子
单元），则满足第一范式（1NF）

第二范式(2nd NF －每个表只描述一件事情)
首先满足第一范式，并且表中非主键列不存在对主键的部分依赖。 第二范式要求每个表只描述一
件事情

第三范式(3rd NF － 不存在对非主键列的传递依赖)
第三范式定义是，满足第二范式，并且表中的列不存在对非主键列的传递依赖。除了主键订单编
号外，顾客姓名依赖于非主键顾客编号。

数据库是事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向
系统提交，要么都执行、要么都不执行 。事务是一个不可分割的工作逻辑单元
事务必须具备以下四个属性，简称 ACID 属性：
原子性（ Atomicity ）

1. 事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执
   行。
   一致性（ Consistency ）
2. 当事务完成时，数据必须处于一致状态。
   隔离性（ Isolation ）
3. 对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方
   式依赖于或影响其他事务。
   永久性（ Durability ）
4. 事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。

存储过程 存储过程( 特定功能的 SQL

一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次
编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过
程是数据库中的一个重要对象。
存储过程优化思路： 存储过程优化思路：

1. 尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
2. 中间结果存放于临时表，加索引。
3. 少使用游标。sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如
   对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次
   读取。
4. 事务越短越好。sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成
   并发操作的阻塞，死锁。导致查询极慢，cpu 占用率极地。
5. 使用 try-catch 处理错误异常。
6. 查找语句尽量不要放在循环内。

触发器( 一段能自动执行的程序)

触发器是一段能自动执行的程序，是一种特殊的存储过程，触发器和普通的存储过程的区别是：
触发器是当对某一个表进行操作时触发。诸如：update、insert、delete 这些操作的时候，系统
会自动调用执行该表上对应的触发器。SQL Server 2005 中触发器可以分为两类：DML 触发器和
DDL 触发器，其中 DDL 触发器它们会影响多种数据定义语言语句而激发，这些语句有 create、
alter、drop 语句。

数据库并发策略

并发控制一般采用三种方法，分别是乐观锁和悲观锁以及时间戳.

• 乐观锁
  乐观锁认为一个用户读数据的时候，别人不会去写自己所读的数据；悲观锁就刚好相反，觉得自
  己读数据库的时候，别人可能刚好在写自己刚读的数据，其实就是持一种比较保守的态度；时间
  戳就是不加锁，通过时间戳来控制并发出现的问题
• 悲观锁
  悲观锁就是在读取数据的时候，为了不让别人修改自己读取的数据，就会先对自己读取的数据加
  锁，只有自己把数据读完了，才允许别人修改那部分数据，或者反过来说，就是自己修改某条数
  据的时候，不允许别人读取该数据，只有等自己的整个事务提交了，才释放自己加上的锁，才允
  许其他用户访问那部分数据
• 时间戳
  时间戳就是在数据库表中单独加一列时间戳，比如“TimeStamp”，每次读出来的时候，把该字
  段也读出来，当写回去的时候，把该字段加1，提交之前 ，跟数据库的该字段比较一次，如果比数
  据库的值大的话，就允许保存，否则不允许保存，这种处理方法虽然不使用数据库系统提供的锁
  机制，但是这种方法可以大大提高数据库处理的并发量，
  以上悲观锁所说的加“锁”，其实分为几种锁，分别是：排它锁（写锁）和共享锁（读锁）。

数据库锁

• 行级锁
  行级锁是一种排他锁，防止其他事务修改此行；在使用以下语句时，Oracle 会自动应用行级锁：

1. INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];
2. SELECT … FOR UPDATE 语句允许用户一次锁定多条记录进行更新
3. 使用 COMMIT 或 ROLLBACK 语句释放锁。

• 表级锁
  表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使
  用的 MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁
  （排他锁）。

• 页级锁
  页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级
  冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB 支持页级锁

• 基于 基于 Redis 分布式锁

1. 获取锁的时候，使用 setnx（SETNX key val：当且仅当 key 不存在时，set 一个 key
   为 val 的字符串，返回 1；若 key 存在，则什么都不做，返回 0）加锁，锁的 value
   值为一个随机生成的 UUID，在释放锁的时候进行判断。并使用 expire 命令为锁添
   加一个超时时间，超过该时间则自动释放锁。
2. 获取锁的时候调用 setnx，如果返回 0，则该锁正在被别人使用，返回 1 则成功获取
   锁。 还设置一个获取的超时时间，若超过这个时间则放弃获取锁。
3. 释放锁的时候，通过 UUID 判断是不是该锁，若是该锁，则执行 delete 进行锁释放

分区分表

分库分表有垂直切分和水平切分两种。

• 垂直切分 ( 按照功能模块 )
   将表按照功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立定义数
  据库 workDB、商品数据库 payDB、用户数据库 userDB、日志数据库 logDB 等，分别用于
  存储项目数据定义表、商品定义表、用户数据表、日志数据表等。
  
• 水平切分 ( 按照规则划分存储 )
   当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，例如 userID 散列，进行
  划分，然后存储到多个结构相同的表，和不同的库上。
  

两阶段提交协议

分布式事务是指会涉及到操作多个数据库的事务,在分布式系统中，各个节点之间在物理上相互独
立，通过网络进行沟通和协调。
XA 就是 X/Open DTP 定义的交易中间件与数据库之间的接口规范（即接口函数），交易中间件
用它来通知数据库事务的开始、结束以及提交、回滚等。 XA 接口函数由数据库厂商提供。
二阶段提交(Two-phaseCommit)是指，在计算机网络以及数据库领域内，为了使基于分布式系统
架构下的所有节点在进行事务提交时保持一致性而设计的一种算法(Algorithm)。通常，二阶段提
交也被称为是一种协议(Protocol))。在分布式系统中，每个节点虽然可以知晓自己的操作时成功
或者失败，却无法知道其他节点的操作的成功或失败。当一个事务跨越多个节点时，为了保持事
务的 ACID 特性，需要引入一个作为协调者的组件来统一掌控所有节点(称作参与者)的操作结果并
最终指示这些节点是否要把操作结果进行真正的提交(比如将更新后的数据写入磁盘等等)。因此，
二阶段提交的算法思路可以概括为：参与者将操作成败通知协调者，再由协调者根据所有参与者
的反馈情报决定各参与者是否要提交操作还是中止操作。

索引的基本原理

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理：就是把无序的数据变成有序的查询

1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

mysql聚簇和非聚簇索引的区别

都是B+树的数据结构
聚簇索引：将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数
据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是
相邻地存放在磁盘上的
非聚簇索引：叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置
再取磁盘查找数据，这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个
目录里面找，找到对应的页码后再去对应的页码看文章。

优势：
1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率
要高
2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的
3、聚簇索引适合用在排序的场合，非聚簇索引不适合
劣势：
1、维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插
入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成
碎片。使用独享表空间可以弱化碎片
2、表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面
更慢，所以建议使用int的auto_increment作为主键
3、如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键
值，会导致非叶子节点占用占用更多的物理空间

InnoDB中一定有主键，主键一定是聚簇索引，不手动设置、则会使用unique索引，没有unique索引，
则会使用数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索引称之为辅助索引，
辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，
辅助索引叶子节点存储的不再是行的物理位置，而是主键值
MyISM使用的是非聚簇索引，没有聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构
完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助
键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据
来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所
占空间小，这些操作是需要在内存中完成的。

mysql索引的数据结构，各自优劣

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，
InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因
此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议
选择BTree索引。
B+树：
B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针
相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且
基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用
于数据库、文件系统等场景。

哈希索引：
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到
叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；前提
是键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直
到找到相应的数据；
如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，
有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实
本质上也是范围查询）；
哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引
的效率也是极低的，因为存在哈希碰撞问题。

锁的类型有哪些

基于锁的属性分类：共享锁、排他锁。
基于锁的粒度分类：行级锁(INNODB)、表级锁(INNODB、MYISAM)、页级锁(BDB引擎 )、记录锁、间
隙锁、临键锁。
基于锁的状态分类：意向共享锁、意向排它锁。

• 共享锁(Share Lock)

共享锁又称读锁，简称S锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对
数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持
并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

• 排他锁（eXclusive Lock）

排他锁又称写锁，简称X锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该
锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修
改，也不允许其他人读取。避免了出现脏数据和脏读的问题。

• 表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能
进行对表进行访问；
特点： 粒度大，加锁简单，容易冲突；

• 行锁

行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不
能访问，其他的记录可正常访问；
特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高；

• 记录锁(Record Lock)

记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁
住的只是表的某一条记录。
精准条件命中，并且命中的条件字段是唯一索引
加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前
被其他事务读取的脏读问题。

• 页锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突
少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

• 间隙锁(Gap Lock)

属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空
隙则会形成一个区间，遵循左开右闭原则。
范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复
读)的事务级别中。
触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务
里，A事务的两次查询出的结果会不一样。
比如表里面的数据ID 为 1,4,5,7,10 ,那么会形成以下几个间隙区间，-n-1区间，1-4区间，7-10
区间，10-n区间 （-n代表负无穷大，n代表正无穷大）

• 临建锁(Next-Key Lock)

也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁
会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一
个区间也会锁住
触发条件：范围查询并命中，查询命中了索引。
结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之
后，在范围区间内数据不允许被修改和插
入。

如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁
了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态
就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是
意向锁

• 意向共享锁

当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

• 意向排他锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

ACID靠什么保证的

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性由其他三大特性保证、程序代码要保证业务上的一致性
I隔离性由MVCC来保证(MVCC：多版本并发控制：读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了)
D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可
以从redo log恢复

InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态。
如果前面 prepare 成功，binlog 写盘，再继续将事务日志持久化到 binlog，如果持久化成功，那么
InnoDB 事务则进入 commit 状态(在 redo log 里面写一个 commit 记录)

redolog的刷盘会在系统空闲时进行

mysql主从同步原理

mysql主从同步的过程：Mysql的主从复制中主要有三个线程： master（binlog dump thread）、slave（I/O thread 、SQL
thread） ，Master一条线程和Slave中的两条线程。

• 主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服
  务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
• 主节点 log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
• 从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。
• 从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性

注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的
偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。
由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生
一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个
概念。

• 全同步复制
  主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方
  式的话性能会受到严重影响。
• 半同步复制
  和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至
  少一个从库的确认就认为写操作完成。

mysql执行计划怎么看

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数
EXPLAIN SELECT * from A where X=? and Y=?

1。id ：是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现
的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为
NULL最后执行。
2。selectType 表示查询中每个select子句的类型
SIMPLE： 表示此查询不包含 UNION 查询或子查询
PRIMARY： 表示此查询是最外层的查询（包含子查询）
SUBQUERY： 子查询中的第一个 SELECT
UNION： 表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT, UNION 的结果
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查
询的结果.
DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）
3.table：表示该语句查询的表
4.type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围：
const：通过索引一次命中，匹配一行数据
system: 表中只有一行记录，相当于系统表；
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref: 非唯一性索引扫描,返回匹配某个值的所有
range: 只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；
index: 只遍历索引树；
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。 那么基本就是随着表的数量增多，
执行效率越慢。
执行效率：
ALL < index < range< ref < eq_ref < const < system。最好是避免ALL和index
5.possible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一
定会用到。
6.key：此字段是 mysql 在当前查询时所真正使用到的索引。 他是possible_keys的子集
7.key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是
我们优化sql时，评估索引的重要指标
9.rows：mysql 查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重
要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明
要优化空间越大
10.filtered：返回结果的行占需要读到的行(rows列的值)的百分比，就是百分比越高，说明需要查询到
数据越准确， 百分比越小，说明查询到的数据量大，而结果集很少
11.extra
using filesort ：表示 mysql 对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有
using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大，延时大。
using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往
往说明性能不错。
using temporary：查询有使用临时表, 一般出现于排序， 分组和多表 join 的情况， 查询效率不
高，建议优化。
using where ：sql使用了where过滤,效率较高。

SQL优化

1、在表中建立索引，优先考虑where、group by使用到的字段。

2、尽量避免使用select *，返回无用的字段会降低查询效率。如下：

SELECT * FROM t

优化方式：使用具体的字段代替*，只返回使用到的字段。

3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)

SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1

UNION

SELECT * FROM t WHERE id = 3

（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，union扫描的是索引，or扫描的是全表）

5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE username LIKE ‘%li%’

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE ‘li%’

6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t2 WHERE score/10 = 9

SELECT * FROM t2 WHERE SUBSTR(username,1,2) = ‘li’

优化方式：可以将表达式、函数操作移动到等号右侧。如下：

SELECT * FROM t2 WHERE score = 10*9

SELECT * FROM t2 WHERE username LIKE ‘li%’

8、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE 1=1

优化方式：用代码拼装sql时进行判断，没where加where，有where加and。

10.程序要尽量避免大事务操作，提高系统并发能力。

11.一个表的索引数最好不要超过6个，如果索引太多的话，就需要考虑一下那些不常使用到的列上建的索引是否有必要。

数据库优化

1. 为什么要优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上

随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢

数据是存放在磁盘上的，读写速度无法和内存相比

优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

3. MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

简述在MySQL数据库中MyISAM和InnoDB的区别

区别于其他数据库的最重要的特点就是其插件式的表存储引擎。存储引擎是基于表的，而不是数据库。

InnoDB与MyISAM的区别：

InnoDB存储引擎: 主要面向OLTP(Online Transaction Processing，在线事务处理)方面的应用，是第一个完整支持ACID事务的存储引擎(BDB第一个支持事务的存储引擎，已经停止开发)。

特点：行锁设计、支持外键,支持事务，支持并发，锁粒度是支持mvcc得行级锁；

MyISAM存储引擎: 是MySQL官方提供的存储引擎，主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。

特点：不支持事务，锁粒度是支持并发插入得表级锁，支持表所和全文索引。操作速度快，不能读写操作太频繁；

MyISAM 是非事务的存储引擎；适合用于频繁查询的应用；表锁，不会出现死锁；适合小数据，小并发
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引
不用保证唯一性

innodb是支持事务的存储引擎；合于插入和更新操作比较多的应用；设计合理的话是行锁（最大区别就在锁的级别上）；适合大数据，大并发。
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅
索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，
为维持B+树结构，文件的大调整。

   MyISAM：成熟、稳定、易于管理，快速读取。一些功能不支持（事务等），表级锁。

   InnoDB：支持事务、外键等特性、数据行锁定。空间占用大，不支持全文索引等。

5点不同

1.InnoDB支持事物，而MyISAM不支持事物 2.InnoDB支持行级锁，而MyISAM支持表级锁 3.InnoDB支持MVCC, 而MyISAM不支持 4.InnoDB支持外键，而MyISAM不支持 5.InnoDB不支持全文索引，而MyISAM支持。

6.InnoDB不能通过直接拷贝表文件的方法拷贝表到另外一台机器， myisam 支持

7.InnoDB表支持多种行格式， myisam 不支持

8.InnoDB是索引组织表， myisam 是堆表

(2)、innodb引擎的4大特性

1.插入缓冲（insert buffer) 2.二次写(double write) 3.自适应哈希索引(ahi) 4.预读(read ahead)

(3)、各种不同 mysql 版本的Innodb的改进

MySQL5.6 下 Innodb 引擎的主要改进： （ 1） online DDL （ 2） memcached NoSQL 接口 （ 3） transportable tablespace（ alter table discard/import tablespace） （ 4） MySQL 正常关闭时，可以 dump 出 buffer pool 的（ space， page_no），重启时 reload，加快预热速度 （ 5） 索引和表的统计信息持久化到 mysql.innodb_table_stats 和mysql.innodb_index_stats，可提供稳定的执行计划 （ 6） Compressed row format 支持压缩表

MySQL 5.7 innodb 引擎主要改进 （ 1） 修改 varchar 字段长度有时可以使用 online DDL （ 2） Buffer pool 支持在线改变大小 （ 3） Buffer pool 支持导出部分比例 （ 4） 支持新建 innodb tablespace，并可以在其中创建多张表 （ 5） 磁盘临时表采用 innodb 存储，并且存储在 innodb temp tablespace 里面，以前是 myisam 存储 （ 6） 透明表空间压缩功能

关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运
维在做，会定期将业务中的慢查询反馈给我们。
慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还
是数据量太大？
所以优化也是针对这三个方向来的，

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载
  了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽
  可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者
  纵向的分表

简述mysql中索引类型及对数据库的性能的影响

普通索引：允许被索引的数据列包含重复的值。
唯一索引：可以保证数据记录的唯一性。
主键：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用
关键字 PRIMARY KEY 来创建。
联合索引：索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。
全文索引：通过建立 倒排索引 ,可以极大的提升检索效率,解决判断字段是否包含的问题，是目前搜索引
擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引索引可以极大的提高数据的查询速度。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚
簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都
会跟着变。