0404 - MySQL

Oracle 功能强大，主要缺点就是贵

MySQL 是互联网行业中最流行的数据库，这不仅仅是因为 MySQL 免费，可以说关系数据库场景中你需要的功能，MySQL 都能很好得满足。后面的详解部分会详细介绍 MySQL 的一些知识点。

MariaDB 是 MySQL 的分支，由开源社区维护，MariaDB 虽然被看作 MySQL 的替代品，但与 MySQL 相比，它在扩展功能、存储引擎上都有非常好的改进，后续可以关注。

PostgreSQL也叫 PGSQL，PGSQL 类似于 Oracle 的多进程框架，可以支持高并发的应用场景。PG 几乎支持所有的 SQL 标准，支持类型相当丰富。PG 更加适合严格的企业应用场景，而 MySQL 更适合业务逻辑相对简单、数据可靠性要求较低的互联网场景。

MongoDB 是一个基于分布式文件存储的数据库，将数据存储为一个文档，数据结构由键值对组成。MongoDB 比较适合表结构不明确，且数据结构可能不断变化的场景，不适合有事务和复杂查询的场景。

HBase 是建立在 HDFS，也就是 Hadoop 文件系统之上的分布式面向列的数据库，类似于谷歌的大表设计，HBase 可以快速随机访问海量结构化数据。在表中它由行排序，一个表有多个列族以及每一个列族可以有任意数量的列。 HBase 依赖 HDFS 可以实现海量数据的可靠存储，适用于数据量大，写多读少，不需要复杂查询的场景。

Cassandra 是一个高可靠的大规模分布式存储系统。支持分布式的结构化 key-value 存储，以高可用性为主要目标。适合写多的场景，适合做一些简单查询，不适合用来做数据分析统计。

Pika 是一个可持久化的大容量类 Redis 存储服务， 兼容五种主要数据结构的大部分命令。Pika 使用磁盘存储，主要解决 Redis 大容量存储的成本问题。

TiDB 是开源的分布式关系数据库，几乎完全兼容 MySQL，能够支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和 MySQL 协议，具有数据强一致的高可用特性。既适合在线事务处理，也适合在线分析处理。

另外一个比较著名的 NewSQL 是蚂蚁金服的 OceanBase。OB 是可以满足金融级的可靠性和数据一致性要求的数据库系统。需要使用事务，并且数据量比较大的时候，就比较适合使用 OB。不过目前 OB 已经商业化，不再开源。

第一个原子性，指事务由原子的操作序列组成，所有操作要么全部成功，要么全部失败回滚。

第二个事务的一致性，指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。比如在做多表操作时，多个表要么都是事务后新的值，要么都是事务前的旧值。

第三个事务的隔离性，指多个用户并发访问数据库时，数据库为每个用户执行的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。事务的隔离级别在后文中介绍。

第四个事务的持久性，指一个事务一旦提交并执行成功，那么对数据库中数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

脏读 是指在一个事务处理过程里读取了另一个未提交的事务中的数据，例如，账户 A 转帐给 B 500元，B 余额增加后但事务还没有提交完成，此时如果另外的请求中获取的是 B 增加后的余额，这就发生了脏读，因为事务如果失败回滚时，B 的余额就不应该增加。

不可重复读 是指对于数据库中某个数据，一个事务范围内多次查询返回了不同的数据值，这是由于在多次查询之间，有其他事务修改了数据并进行了提交。

幻读 是指一个事务中执行两次完全相同的查询时，第二次查询所返回的结果集跟第一个查询不相同。与不可重复读的区别在于，不可重复读是对同一条记录，两次读取的值不同。而幻读是记录的增加或删除，导致两次相同条件获取的结果记录数不同。

第一个隔离级别是读未提交，也就是可以读取到其他事务未提交的内容，这是最低的隔离级别，这个隔离级别下，前面提到的三种并发问题都有可能发生。

第二个隔离级别是读已提交，就是只能读取到其他事务已经提交的数据。这个隔离级别可以解决脏读问题。

第三个隔离级别是可重复读，可以保证整个事务过程中，对同数据的多次读取结果是相同的。这个级别可以解决脏读和不可重复读的问题。MySQL 默认的隔离级别就是可重复读。

最后一个隔离级别是串行化，这是最高的隔离级别，所有事务操作都依次顺序执行。这个级别会导致并发度下降，性能最差。不过这个级别可以解决前面提到的所有并发问题。

第一个是扁平化事务，在扁平事务中，所有的操作都在同一层次，这也是我们平时使用最多的一种事务。它的主要限制是不能提交或者回滚事务的某一部分，要么都成功，要么都回滚。

为了解决第一种事务的弊端，就有了第二种带保存点的扁平事务。它允许事务在执行过程中回滚到较早的状态，而不是全部回滚。通过在事务中插入保存点，当操作失败后，可以选择回滚到最近的保存点处。

第三种事务是链事务，可以看做是第二种事务的变种。它在事务提交时，会将必要的上下文隐式传递给下一个事务，当事务失败时就可以回滚到最近的事务。不过，链事务只能回滚到最近的保存点，而带保存点的扁平化事务是可以回滚到任意的保存点。

第四种事务是嵌套事务，由顶层事务和子事务构成，类似于树的结构。一般顶层事务负责逻辑管理，子事务负责具体的工作，子事务可以提交，但真正提交要等到父事务提交，如果上层事务回滚，那么所有的子事务都会回滚。

最后一种类型是分布式事务。是指分布式环境中的扁平化事务。

常用的分布式事务解决方案如上图右侧所示，下面进行简要介绍。

第一个分布式事务解决方案是 XA 协议，是保证强一致性的刚性事务。实现方式有两段式提交和三段式提交。两段式提交需要有一个事务协调者来保证所有的事务参与者都完成了第一阶段的准备工作。如果协调者收到所有参与者都准备好的消息，就会通知所有的事务执行第二阶段提交。一般场景下两段式提交已经能够很好得解决分布式事务了，然而两阶段在即使只有一个进程发生故障时，也会导致整个系统存在较长时间的阻塞。三段式提交通过增加 pre-commit 阶段来减少前面提到的系统阻塞的时间。三段式提交很少在实际中使用，简单了解就可以了。

第二个分布式解决方案是 TCC，是满足最终一致性的柔性事务方案。TCC 采用补偿机制，核心思想是对每个操作，都要注册对应的确认和补偿操作。它分为三个阶段：Try 阶段主要对业务系统进行检测及资源预留；Confirm 阶段对业务系统做确认提交；Cancel 阶段是在业务执行错误，执行回滚，释放预留的资源。

第三种方案是消息一致性方案。基本思路是将本地操作和发送消息放在一个事务中，保证本地操作和消息发送要么都成功要么都失败。下游应用订阅消息，收到消息后执行对应操作。

第四种方案可以了解一下阿里云中的全局事务服务 GTS，对应的开源版本是 Fescar。Fescar 基于两段式提交进行改良，剥离了分布式事务方案对数据库在协议支持上的要求。使用 Fescar 的前提是分支事务中涉及的资源，必须是支持 ACID 事务的关系型数据库。分支的提交和回滚机制，都依赖于本地事务来保障。 Fescar 的实现目前还存在一些局限，比如事务隔离级别最高支持到读已提交级别。

第一范式1NF：列的原子性
第二范式2NF：满足第一范式，必须要有主键列
第三范式3NF：满足第二范式，主外键引用

1、域（列）完整性：包括取值范围、类型、默认值、非空等
2、实体（行）完整性：主关键字即不能重复，也不能为空值
3、参照完整性：外键约束
4、用户自定义完整性：范围约束

概念模型

逻辑模型

物理模型

就是索引列中的值必须是唯一的，但是允许出现空值。这种索引一般用来保证数据的唯一性，比如保存账户信息的表，每个账户的 ID 必须保证唯一，如果重复插入相同的账户ID时MySQL返回异常。

主键索引是一种特殊的唯一索引，但是它不允许出现空值。

普通索引，与唯一索引不同，它允许索引列中存在相同的值。例如学生的成绩表，各个学科的分数是允许重复的，就可以使用普通索引。

联合索引，就是由多个列共同组成的索引。一个表中含有多个单列的索引并不是联合索引，联合索引是对多个列字段按顺序共同组成一个索引。应用联合索引时需要注意最左原则，就是 where 查询条件中的字段必须与索引字段从左到右进行匹配。比如，一个用户信息表，用姓名和年龄组成了联合索引，如果查询条件是“姓名等于张三“，那么满足最左原则；如果查询条件是“年龄大于 20“，由于索引中最左的字段是姓名不是年龄，所以不能使用这个索引。

全文索引，前面提到了，MyISAM 引擎中实现了这个索引，在 5.6 版本后 InnoDB 引擎也支持了全文索引，并且在 5.7.6 版本后支持了中文索引。全文索引只能在 CHAR、VARCHAR、TEXT 类型字段上使用，底层使用倒排索引实现。要注意对于大数据量的表，生成全文索引会非常消耗时间也非常消耗磁盘空间。

单一索引

组合索引

B树索引

B+树索引

哈希索引

数据结构演示地址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

聚集索引

非聚集索引

B+ 树实现，B+ 树比较适合用作 > 或 < 这样的范围查询，是 MySQL 中最常使用的一种索引实现。

R-Tree 是一种用于处理多维数据的数据结构，可以对地理数据进行空间索引。不过实际业务场景中使用的比较少。

Hash 是使用散列表来对数据进行索引，Hash 方式不像 B-Tree 那样需要多次查询才能定位到记录，因此 Hash 索引的效率高于 B-Tree，但是不支持范围查找和排序等功能。实际使用的也比较少。

FullText 就是前面提到的全文索引，是一种记录关键字与对应文档关系的倒排索引。

使用多列索引的查询语句：只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

乐观锁

悲观锁

读锁

写锁

表锁

行锁

表锁开销小，加锁快，不会出现死锁；但是锁的粒度大，发生锁冲突的概率高，并发访问效率比较低。

行级锁开销大，加锁慢，有可能会出现死锁，不过因为锁定粒度最小，发生锁冲突的概率低，并发访问效率比较高。

共享锁也就是读锁，其他事务可以读，但不能写。MySQL 可以通过 lock in share mode 语句显示使用共享锁。

排他锁就是写锁，其他事务不能读取，也不能写。对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及的数据集加排他锁，或者使用 select for update 显示使用排他锁。

事务时原子性操作，要么执行，要么都不执行

事务在执行前后数据完整性必须保持一致

并发的事务之间时相互隔离的，事务内部的操作对于其他事务而言都是相互不可见的。如果不考虑隔离性，就可能发生脏读、不可重复读、幻读问题

脏读：一个事务读取了另一个事务改写但还未提交的数据，如果这些数据回滚，则读到的数据无效

不可重复读：在同一个事务中，多次读取同一个数据返回的结果不同

幻读：一个事务读取了几行记录后，另一个事务插入了一些记录，后来的查询中前一个事务就会发现有些原来没有的记录

事务一旦完成提交，引起的数据变化将是永久性的，即使数据库发生故障也不应该对其有任何影响

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读

允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生

最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、
不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。

在数据库中保留“版本”字段，跟随数据同时读写，以此判断数据版本。版本可能是时间戳或状态字段。
UPDATE table SET status = 1 WHERE id=1 AND status = 0;
版本检查能够作为“乐观锁”，解决更新丢失的问题。

共享锁与排他锁

临时锁与持续锁

表级锁与行级锁

悲观锁与乐观锁

三级加锁协议

两段锁协议（2-phase locking）

show variables like 'slow_query%';

方式一（临时，重启失效）：
------------------------------------------------------------
mysql>set global slow_query_log=ON
mysql>set global long_query_time=3600
mysql>set global log_querise_not_using_indexes=ON

show status like 'slow_queries';

show variables like 'profiling%';

set profiling=1; 或者 set profiling=ON;

set profiling=0; 或者 set profiling=OFF;

显示当前执行的语句和时间

id代表执行select子句或操作表的顺序

simple：简单的select查询,查询中不包含子查询或union查询
primary：查询中若包含任何复杂的子部分,最外层查询则被标记为primary
subquery：在select 或where 列表中包含了子查询
derived：在from列表中包含的子查询被标记为derived,mysql会递归这些子查询,把结果放在临时表里
union：做第二个select出现在union之后,则被标记为union,若union包含在from子句的子查询中,外层select将被标记为derived
union result：从union表获取结果的select

显示一行的数据时关于哪张表的

system：表只有一行记录,这是const类型的特例,平时不会出现
const：表示通过索引一次就找到了,const即常量,它用于比较primary key或unique索引,因为只匹配一行数据,所以效率很快,如将主键置于where条件中,mysql就能将该查询转换为一个常量 
eq_ref：唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
ref：非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
range：检索给定范围的行,使用一个索引来选择行,如where语句中出现了between,<,>,in等查询,这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index：index类型只遍历索引树,这通常比All快,因为索引文件通常比数据文件小,index是从索引中读取,all从硬盘中读取
all：全表扫描,是最差的一种查询类型

显示可能应用在这张表中的索引,一个或多个,查询到的索引不一定是真正被用到的

实际使用的索引,如果为null,则没有使用索引,因此会出现possible_keys列有可能被用到的索引,但是key列为null,表示实际没用索引

表示索引中使用的字节数,而通过该列计算查询中使用的索引长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即,key_len是根据表定义计算而得么不是通过表内检索出的

显示索引的哪一列被使用了,如果可能的话是一个常数,哪些列或常量被用于查找索引列上的值

根据表统计信息及索引选用情况,大只估算出找到所需的记录所需要读取的行数

Using filesort：说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为"文件排序"
Using temporary ：使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by
Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。 其中的覆盖索引含义是所查询的列是和建立的索引字段和个数是一一对应的
Using where：表明使用了where过滤
Using join buffer：表明使用了连接缓存,如在查询的时候会有多次join,则可能会产生临时表
impossible where：表示where子句的值总是false,不能用来获取任何元祖。如下例：select * from t1 where id='1' and id='2';
select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作，即一旦MySQL找到了与行相联合匹配的行，就不再搜索了

尽量使用index方式排序，避免使用filesort方式

order by满足两种情况会使用index排序：①、order by语句使用索引最左前列，②、使用where子句与order by子句条件列组合满足索引最左前列

双路排序：MySQL4.1之前，两次扫描磁盘

单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列进行输出，效率更高一点，但是它会使用更多的空间，
因为它把每一行都保存在内存中了

优化策略: 增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置

实质是先排序后进行分组，遵照索引键的最佳左前缀，当无法使用索引列时，增大sort_buffer_size+max_length_for_sort_data参数的设置

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

增加中间表

加载数据库驱动

获取链接对象

进行数据库操作（增删查改）

释放资源：关闭结果集ResultSet、语句对象Statement、链接对象Connection

DirverManager类

Connection接口

Statement接口

PreparedStatement接口

ResultSet接口

CallableStatement接口

DatabaseMetaData接口

ResultSetMetadata接口

德鲁伊，带有监控功能的数据库连接池

“光”，号称时性能最好的数据库连接池

MySQL优化概述

MySQL优化方案

优化数据库的方法

事务就是被绑定在一起作为一个逻辑工作单元的SQL 语句分组，如果任何一个语
句操作失败那么整个操作就被失败， 以后操作就会回滚到操作前状态，或者是上
有个节点。为了确保要么执行，要么不执行， 就可以使用事务。要将有组语句作
为事务考虑，就需要通过ACID 测试，即原子性， 一致性， 隔离性和持久性。

具体的应用场景：
1）社交场景，使用 MongoDB 存储存储用户信息，以及用户发表的朋友圈信息，通过地理位置索引实现附近的人、地点等功能。
2）游戏场景，使用 MongoDB 存储游戏用户信息，用户的装备、积分等直接以内嵌文档的形式存储，方便查询、高效率存储和访问。
3）物流场景，使用 MongoDB 存储订单信息，订单状态在运送过程中会不断更新，以 MongoDB 内嵌数组的形式来存储，一次查询就能将
订单所有的变更读取出来。
4）物联网场景，使用 MongoDB 存储所有接入的智能设备信息，以及设备汇报的日志信息，并对这些信息进行多维度的分析。
5）视频直播，使用 MongoDB 存储用户信息、点赞互动信息等。

use 数据库名称

show dbs 或 show databases

db

db.dropDatabase()

db.createCollection(集合名称)

当向一个集合中插入一个文档的时候，如果集合不存在，则自动创建集合

show tables 或者 show collections

db.collection.drop() 或 db.集合.drop()

单个文档的插入

批量插入

db.collection.find(<query>, [projection])

db.collection.update(query, update，options)

db.集合名称.remove(条件)

db.collection.count(query, options)

db.COLLECTION_NAME.find().limit(NUMBER).skip(NUMBER)

db.COLLECTION_NAME.find().sort({KEY:1})
或
db.集合名称.find().sort(排序方式)

db.collection.find({field:/正则表达式/})
或
db.集合.find({字段:/正则表达式/})

db.集合名称.find({ "field" : { $gt: value }}) // 大于: field > value
db.集合名称.find({ "field" : { $lt: value }}) // 小于: field < value
db.集合名称.find({ "field" : { $gte: value }}) // 大于等于: field >= value
db.集合名称.find({ "field" : { $lte: value }}) // 小于等于: field <= value
db.集合名称.find({ "field" : { $ne: value }}) // 不等于: field != value

db.comment.find({userid:{$in:["1003","1004"]}})

$and:[ { },{ },{ } ]

$or:[ { },{ },{ } ]

MongoDB支持在文档的单个字段上创建用户定义的升序/降序索引，称为单字段索引（Single Field Index）；
对于单个字段索引和排序操作，索引键的排序顺序（即升序或降序）并不重要，因为MongoDB可以在任何方向上遍历索引。

MongoDB还支持多个字段的用户定义索引，即复合索引（Compound Index）。
复合索引中列出的字段顺序具有重要意义。例如，如果复合索引由{ userid: 1, score: -1 } 组成，则索引首先按userid正序排序，然后
在每个userid的值内，再在按score倒序排序。

为了支持对地理空间坐标数据的有效查询，MongoDB提供了两种特殊的索引：
返回结果时使用平面几何的二维索引和返回结果时使用球面几何的二维球面索引。

MongoDB提供了一种文本索引类型，支持在集合中搜索字符串内容。这些文本索引不存储特定于语言的停止词
（例如“the”、“a”、“or”），而将集合中的词作为词干，只存储根词。

为了支持基于散列的分片，MongoDB提供了散列索引类型，它对字段值的散列进行索引。
这些索引在其范围内的值分布更加随机，但只支持相等匹配，不支持基于范围的查询。

db.collection.getIndexes()

db.collection.createIndex(keys, options)

db.collection.dropIndex(index)

db.comment.dropIndexes()

分析查询性能（Analyze Query Performance）通常使用执行计划（解释计划、Explain Plan）来查看查询的情况，
如查询耗费的时间、是否基于索引查询等。

当查询条件和查询的投影仅包含索引字段时，MongoDB直接从索引返回结果，而不扫描任何文档或将文档带入内存。 这些覆盖的查询可以
非常有效。

MongoDB中的副本集（Replica Set）是一组维护相同数据集的mongod服务。
副本集可提供冗余和高可用性，是所有生产部署的基础。

冗余和数据可用性

MongoDB中的复制

主从复制和副本集区别

两种类型

三种角色

主节点选举的触发条件

分片（sharding）是一种跨多台机器分布数据的方法， MongoDB使用分片来支持具有非常大的数据集和高吞吐量操作的部署。

换句话说：分片(sharding)是指将数据拆分，将其分散存在不同的机器上的过程。有时也用分区(partitioning)来表示这个概念。
将数据分散到不同的机器上，不需要功能强大的大型计算机就可以储存更多的数据，处理更多的负载。

有两种解决系统增长的方法：垂直扩展和水平扩展。
垂直扩展意味着增加单个服务器的容量，例如使用更强大的CPU，添加更多RAM或增加存储空间量。可用技术的局限性可能会限制单个机器对于给定工作负载而言足够强大。此外，基于云的提供商基于可用的硬件配置具有硬性上限。结果，垂直缩放有实际的最大值。

分片（存储）：每个分片包含分片数据的子集。 每个分片都可以部署为副本集。

mongos（路由）：mongos充当查询路由器，在客户端应用程序和分片集群之间提供接口。

config servers（“调度”的配置）：配置服务器存储群集的元数据和配置设置。
从MongoDB 3.4开始，必须将配置服务器部署为副本集（CSRS）。

两个分片节点副本集（3+3）+ 一个配置节点副本集（3）+ 两个路由节点（2）

mongodb官网上说，为了能保障mongodb的安全可以做以下几个步骤：
1）使用新的端口，默认的27017端口如果一旦知道了ip就能连接上，不太安全。
2）设置mongodb的网络环境，最好将mongodb部署到公司服务器内网，这样外网是访问不到的。公司内部访问使用vpn等。
3）开启安全认证。认证要同时设置服务器之间的内部认证方式，同时要设置客户端连接到集群的账号密码认证方式。

1）启用访问控制：
MongoDB使用的是基于角色的访问控制(Role-Based Access Control,RBAC)来管理用户对实例的访问。
通过对用户授予一个或多个角色来控制用户访问数据库资源的权限和数据库操作的权限，在对用户分配
角色之前，用户无法访问实例。
在实例启动时添加选项--auth 或指定启动配置文件中添加选项auth=true 。

2）角色：
在MongoDB中通过角色对用户授予相应数据库资源的操作权限，每个角色当中的权限可以显式指定，
也可以通过继承其他角色的权限，或者两都都存在的权限。

3）权限：
权限由指定的数据库资源(resource)以及允许在指定资源上进行的操作(action)组成。
1. 资源(resource)包括：数据库、集合、部分集合和集群；
2. 操作(action)包括：对资源进行的增、删、改、查(CRUD)操作。
在角色定义时可以包含一个或多个已存在的角色，新创建的角色会继承包含的角色所有的权限。在同一
个数据库中，新创建角色可以继承其他角色的权限，在admin 数据库中创建的角色可以继承在其它任意
数据库中角色的权限。

关于角色权限的查看

常用的内置角色