0404 - MySQL

数据库

基础知识

常用的数据库

关系型数据库

Oracle

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

MySQL

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

MariaDB

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

PostgreSQL

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

NoSQL

MongoDB

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

HBase

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

Cassandra

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

Pika

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

NewSQL

TiDB

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

OceanBase

另外一个比较著名的 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 的实现目前还存在一些局限，比如事务隔离级别最高支持到读已提交级别。

SQL语法

数据查询语言DQL：数据查询语言，顾名思义就是从数据库中查询数据。包括select、where、order by、group by、having等动词数据定义语言DDL：数据定义语言，用于数据库表结构相关操作，如创建表CREATE、删除DROP、添加表索引等数据操作语言DML：通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问数据操纵语言DCL：实现对数据库表数据的查询、增加、删除和修改操作

SQL执行顺序

语法顺序：SELECT->DISTINCT->FROM->JOIN IN->WHERE->GROUP BY->HAVING->UNION->ORDER BY执行顺序：FROM->WHERE->GROUP BY->HAVING->SELECT->DISTINCT->UNION->ORDER BY

数据库设计

三大范式

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

完整性约束

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

数据库模型

概念模型

E-R图，即Entity Relationship，实体关系

逻辑模型

关系：二维表

物理模型

表结构，包括字段类型、长度、主外键、是否非空等信息

游标

相当于一个指针，用于对数据库中数据进行遍历操作，通常以行为单位进行遍历

存储过程

存储过程能够实现更复杂的功能，而函数一般用来实现针对性比较强的功能，例如特殊策略求和等。存储过程可以执行包括修改表等一系列数据库操作，而用户定义函数不能用于执行修改全局数据库状态的操作。存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用。SQL 语句中不能使用存储过程，但可以使用函数。存储过程一般与数据库实现绑定，使用存储过程会降低程序的可移植性，应谨慎使用。

触发器

类似于存储过程，唯一的区别时触发器不能执行EXECUTE语句调用，而是由用户执行T-SQL语句时自动触发执行

视图

基于SQL语句的结果集构成的表（虚拟表）

存储引擎

MYISAM：不支持外键，默认表锁，插入数据时，锁定整个表，查表总行数时，不需要全表扫描

INNODB：5.5版本后成为MySQL的默认存储引擎，特点是支持ACID事务，支持外键，默认行锁，查表总行数时，全表扫描

索引详解

分类

唯一索引

就是索引列中的值必须是唯一的，但是允许出现空值。这种索引一般用来保证数据的唯一性，比如保存账户信息的表，每个账户的 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-Tree

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

R-Tree

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

Hash

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

FullText

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

索引失效的场景

全值匹配

最佳左前缀法则

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

不在索引列上做任何操作（计算、函数、（手动或自动）类型转换），会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引

MySQL在使用不等于（!=或<>)的时候无法使用索引会导致全表扫描

is null，is not null也无法使用索引

使用LIKE关键字查询的语句：如果匹配字符串的第一个字符为“%”，索引不会起作用。MySQL索引失效会变成全表扫描的操作

字符串不加单引号索引失效（自动类型转换）

or左边有索引、右边没索引也会失效

锁机制与事务隔离级别

MySQL锁

性能

乐观锁

悲观锁

操作

读锁

写锁

粒度

表锁

行锁

死锁以及优化解决方案

总结

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

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

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

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

ACID四大特性

原子性Atomicity

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

一致性Consistency

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

隔离性Isolation

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

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

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

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

持久性Durability

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

事务隔离级别

READ_UNCOMMITTED（未提交读）

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

READ_COMMITTED（读已提交）

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

REPEATABLE_READ（可重复读）【默认】

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

SERIALIZABLE（串行）

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

并发事务一般有哪些问题？

更新丢失：两次事务读同一行，更新的时候不知道别的数据存在，导致丢失更新问题。

脏读：事务B读取事务A还没有提交的数据

不可重复读：两次事务读的数据不一致

幻读：事务A修改了数据，事务B也修改了数据，这时在事务A看来，明明修改了数据，咋不一样

多版本并发控制MVCC机制详解

常见事务问题的解决方案

版本检查

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

锁

共享锁与排他锁

加共享锁的对象只允许被当前事务和其他事务读。也称读锁。能给未加锁和添加了S锁的对象添加S锁。对象可以接受添加多把S锁。

加排它锁的对象只允许被当前事务读和写。也称独占锁，写锁。只能给未加锁的对象添加X锁。对象只能接受一把X锁。加X锁的对象不能再加任何锁。

引入它是因为多数数据库在实现加X锁时是执行了如下流程：先加S锁，添加成功后尝试更换为X锁。这时如果有两个事务同时加了S锁，尝试换X锁，就会发生死锁。因此增加U锁，U锁代表有更新意向，只允许有一个事务拿到U锁，该事务在发生写后U锁变X锁，未写时看做S锁。

临时锁与持续锁

锁的时效性。指明了加锁生效期是到当前语句结束还是当前事务结束。

表级锁与行级锁

锁的粒度。指明了加锁的对象是当前表还是当前行。

悲观锁与乐观锁

悲观锁（Pessimistic Locking）

悲观锁假定当前事务操纵数据资源时，肯定还会有其他事务同时访问该数据资源，为了避免当前事务的操作受到干扰，先锁定资源。悲观锁需使用数据库的锁机制实现，如使用行级排他锁或表级排它锁。尽管悲观锁能够防止丢失更新和不可重复读这类问题，但是它非常影响并发性能，因此应该谨慎使用。

乐观锁（Optimistic Locking）

乐观锁假定当前事务操纵数据资源时，不会有其他事务同时访问该数据资源，因此不在数据库层次上的锁定。乐观锁使用由程序逻辑控制的技术来避免可能出现的并发问题。唯一能够同时保持高并发和高可伸缩性的方法就是使用带版本检查的乐观锁。乐观锁不能解决脏读的问题，因此仍需要数据库至少启用“读已提交”的事务隔离级别。

三级加锁协议

一级加锁协议

事务在修改数据前必须加X锁，直到事务结束（提交或终止）才可释放；如果仅仅是读数据，不需要加锁。

二级加锁协议

满足一级加锁协议，且事务在读取数据之前必须先加S锁，读完后即可释放S锁。

满足一级加锁协议，且事务在读取数据之前必须先加S锁，直到事务结束才释放。

两段锁协议（2-phase locking）

加锁阶段：事务在读数据前加S锁，写数据前加X锁，加锁不成功则等待。解锁阶段：一旦开始释放锁，就不允许再加锁了。

执行计划与优化实践

数据库中设置SQL慢查询日志

查看是否开发慢查询

show variables like 'slow_query%';

slow_query_log = off，表示没有开启慢查询slow_query_log_file 表示慢查询日志存放的目录

开启慢查询

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

方式二（永久性）：-------------------------------------------------------------在/etc/my.cfg文件中的[mysqld]中加入slow_query_log=ONslow_query_log_file=/var/lib/mysql/localhost-slow.log

查询慢查询次数

show status like 'slow_queries';

慢查询日志分析工具Mysqldumpslow

统计不同慢sql的出现次数(Count)，执行最长时间(Time)，累计总耗费时间(Time)，等待锁的时间(Lock)，发送给客户端的行总数(Rows)，扫描的行总数(Rows)

案例一：取出耗时最长的两条sql -------------->  mysqldumpslow -s t -t 2 /var/lib/mysql/localhost-slow.log

案例二：取出查询次数最多，且使用了in关键字的1条sql -------------->  mysqldumpslow -s c -t 1 -g 'in' /var/lib/mysql/localhost-slow.log

show profile性能分析方法

查看是否开启profile，mysql默认是不开启的，因为开启很耗性能

show variables like 'profiling%';

开启profile（会话级别的，关闭当前会话就会恢复原来的关闭状态）

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

关闭profile

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

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

显示当前查询语句执行的时间和系统资源消耗

执行计划explain（慢查询日志分析）

id

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

select_type

table

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

type

possible_keys

key

key_len

ref

rows

Extra

常见慢查询优化

索引优化最佳实践

order by关键字优化

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

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

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

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

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

group by关键字优化

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

优化数据库结构

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

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

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

MySQL调优

第一个纬度是针对数据库设计、表结构设计以及索引设置纬度进行的优化；第二个纬度是对我们业务中使用的 SQL 语句进行优化，例如调整 where 查询条件；第三个纬度是对 MySQL 服务的配置进行优化，例如对链接数的管理，对索引缓存、查询缓存、排序缓存等各种缓存大小进行优化；第四个纬度是对硬件设备和操作系统设置进行优化，例如调整操作系统参数、禁用 swap、增加内存、升级固态硬盘等等。

要在设计表结构时，考虑数据库的水平与垂直扩展能力，提前规划好未来1年的数据量、读写量的增长，规划好分库分表方案。比如设计用户信息表，预计 1 年后用户数据 10亿 条，写 QPS 约 5000，读 QPS 30000，可以设计按 UID 纬度进行散列，分为 4 个库每个库 32 张表，单表数据量控制在 KW 级别。要为字段选择合适的数据类型，在保留扩展能力的前提下，优先选用较小的数据结构。例如保存年龄的字段，要使用 TINYINT 而不要使用 INT。可以将字段多的表分解成多个表，必要时增加中间表进行关联。假如一张表有 40～50 个字段显然不是一个好的设计。一般来说，设计关系数据库时需要满足第三范式，但为了满足第三范式，我们可能会拆分出多张表。而在进行查询时需要对多张表进行关联查询，有时为了提高查询效率，会降低范式的要求，在表中保存一定的冗余信息，也叫做反范式。但要注意反范式一定要适度。要擅用索引，比如为经常作为查询条件的字段创建索引、创建联合索引时要根据最左原则考虑索引的复用能力，不要重复创建索引；要为保证数据不能重复的字段创建唯一索引等等。不过要注意索引对插入、更新等写操作是有代价的，不要滥用索引，比如像性别这样唯一很差的字段就不适合建立索引。列字段尽量设置为 not null。MySQL 难以对使用 null 的列进行查询优化，允许 null 会使索引、索引统计和值更加复杂，允许 null 值的列需要更多的存储空间，还需要 MySQL 内部进行特殊处理。再看到如图右边所示的模块，对 SQL 语句进行优化的原则。要找到最需要优化的 SQL 语句。要么是使用最频繁的语句，要么是优化后提高最明显的语句，可以通过查询 MySQL 的慢查询日志来发现需要进行优化的 SQL 语句；要学会利用 MySQL 提供的分析工具。例如使用 Explain 来分析语句的执行计划，看看是否使用了索引，使用了哪个索引，扫描了多少记录，是否使用文件排序等等。或者利用 Profile 命令来分析某个语句执行过程中各个分步的耗时。要注意使用查询语句是要避免使用 SELECT *，而是应该指定具体需要获取的字段。原因一是可以避免查询出不需要使用的字段，二是可以避免查询列字段的元信息。是尽量使用 prepared statements，一个是它性能更好，另一个是可以防止 SQL 注入。是尽量使用索引扫描来进行排序，也就是尽量在有索引的字段上进行排序操作。

新特性

默认UTF-8编码

隐藏索引

通用表达式

窗口函数

应用拓展

JDBC

操作步骤

加载数据库驱动

导入对应的数据的驱动jar包

Class.forName(数据库驱动全名）加载驱动

获取链接对象

获取语句对象Statement/PreparedStatement

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

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

API

DirverManager类

用于获取数据库链接对象

Connection接口

完成数据库操作的主对象，其中包括数据操作相关的方法

事务相关

设置手动提交事务：connection.setAutoCommit(false)；

提交事务：connection.commit()；

Statement接口

执行SQL语句

PreparedStatement接口

Statement的子接口

执行预编译SQL语句，防止SQL注入，及提高SQL执行效率

ResultSet接口

代表查询结果集

CallableStatement接口

PreparedStatement的子接口

处理存储过程的调用

DatabaseMetaData接口

包含对数据源的元数据（比如表结构、字段类型、函数、触发器、存储过程等）信息进行获取的API

通过Connection对象的getMetaData()方法获取

ResultSetMetadata接口

包含对查询结果集中的元数据（比如字段类型、字段值）等信息进行获取的API

通过ResultSet对象的getMetadata()方法获取

数据库连接池

C3P0

DBCP

Druid

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

HikariCP

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

面试总计

MySQL 中有哪几种锁

1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高， 并发度最低。2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低， 并发度也最高。3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MySQL 中有哪些不同的表格

共有5 种类型的表格：1、MyISAM2、Heap3、Merge4、INNODB5、ISAM

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

MyISAM：不支持事务，但是每次查询都是原子的；支持表级锁，即每次操作是对整个表加锁；存储表的总行数；一个MYISAM 表有三个文件：索引文件、表结构文件、数据文件；采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------InnoDb：支持ACID 的事务， 支持事务的四种隔离级别；支持行级锁及外键约束：因此可以支持写并发；不存储总行数：一个InnoDb 引擎存储在一个文件空间（ 共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G）， 受操作系统文件大小的限制；主键索引采用聚集索引（索引的数据域存储数据文件本身） ，辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时， 为维持B+树结构， 文件的大调整。

MySQL 中InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别

SQL 标准定义的四个隔离级别为：1、read uncommited ：读到未提交数据2、read committed：脏读， 不可重复读3、repeatable read：可重读4、serializable ：串行事物

CHAR 和VARCHAR 的区别

1、CHAR 和VARCHAR 类型在存储和检索方面有所不同2、CHAR 列长度固定为创建表时声明的长度， 长度值范围是1 到255 当CHAR值被存储时，它们被用空格填充到特定长度， 检索CHAR 值时需删除尾随空格。

主键和候选键有什么区别

myisamchk 是用来做什么的

它用来压缩MyISAM 表， 这减少了磁盘或内存使用。MyISAM Static 和MyISAM Dynamic 有什么区别？在MyISAM Static 上的所有字段有固定宽度。动态MyISAM 表将具有像TEXT，BLOB 等字段， 以适应不同长度的数据类型。MyISAM Static 在受损情况下更容易恢复。

如果一个表有一列定义为TIMESTAMP，将发生什么

每当行被更改时，时间戳字段将获取当前时间戳。列设置为AUTO INCREMENT 时， 如果在表中达到最大值，会发生什么情况？它会停止递增，任何进一步的插入都将产生错误， 因为密钥已被使用。怎样才能找出最后一次插入时分配了哪个自动增量？LAST_INSERT_ID 将返回由Auto_increment 分配的最后一个值，并且不需要指定表名称。

你怎么看到为表格定义的所有索引

索引是通过以下方式为表格定义的：SHOW INDEX FROM <tablename>;

LIKE 声明中的％和_是什么意思

％对应于0 个或更多字符，_只是LIKE 语句中的一个字符。如何在Unix 和MySQL 时间戳之间进行转换？UNIX_TIMESTAMP 是从MySQL 时间戳转换为Unix 时间戳的命令FROM_UNIXTIME 是从Unix 时间戳转换为MySQL 时间戳的命令

列对比运算符是什么

在SELECT 语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR 或LIKE 运算符。

BLOB 和TEXT 有什么区别

BLOB 是一个二进制对象，可以容纳可变数量的数据。TEXT 是一个不区分大小写的BLOB。BLOB 和TEXT 类型之间的唯一区别在于对BLOB 值进行排序和比较时区分大小写， 对TEXT 值不区分大小写。

MySQL_fetch_array 和MySQL_fetch_object 的区别是什么

以下是MySQL_fetch_array 和MySQL_fetch_object 的区别：MySQL_fetch_array（） – 将结果行作为关联数组或来自数据库的常规数组返回。MySQL_fetch_object – 从数据库返回结果行作为对象。

MyISAM 表格将在哪里存储，并且还提供其存储格式

每个MyISAM 表格以三种格式存储在磁盘上：“.frm”文件存储表定义数据文件具有“.MYD” （MYData）扩展名索引文件具有“.MYI” （MYIndex）扩展名

MySQL 如何优化DISTINCT

DISTINCT 在所有列上转换为GROUP BY，并与ORDER BY 子句结合使用。

NOW() 和CURRENT_DATE() 有什么区别

NOW() 命令用于显示当前年份，月份， 日期，小时， 分钟和秒。CURRENT_DATE() 仅显示当前年份， 月份和日期。

什么是非标准字符串类型

1、TINYTEXT2、TEXT3、MEDIUMTEXT4、LONGTEXT

什么是通用SQL 函数

MySQL 支持事务吗

MySQL 里记录货币用什么字段类型好

NUMERIC 和DECIMAL 类型被MySQL 实现为同样的类型，这在SQL92 标准允许。他们被用于保存值，该值的准确精度是极其重要的值， 例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。

MySQL 有关权限的表都有哪几个

MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL 数据库里，由MySQL_install_db 脚本初始化。这些权限表分别user，db，table_priv，columns_priv 和host。

列的字符串类型可以是什么

字符串类型是：1、SET2、BLOB3、ENUM4、CHAR5、TEXT

锁的优化策略

1、读写分离2、分段加锁3、减少锁持有的时间4、多个线程尽量以相同的顺序去获取资源不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多， 反而效率不如一次加一把大锁。

索引的底层实现原理和优化

B+树， 经过优化的B+树主要是在所有的叶子结点中增加了指向下一个叶子节点的指针， 因此InnoDB 建议为大部分表使用默认自增的主键作为主索引。

什么情况下设置了索引但无法使用

1、以“ %” 开头的LIKE 语句，模糊匹配2、OR 语句前后没有同时使用索引3、数据类型出现隐式转化（如varchar 不加单引号的话可能会自动转换为int 型）

实践中如何优化MySQL

最好是按照以下顺序优化：1、SQL 语句及索引的优化2、数据库表结构的优化3、系统配置的优化4、硬件的优化

MySQL优化概述

MySQL优化方案

Mysql的优化，大体可以分为三部分：索引的优化，sql语句的优化，表的优化

索引优化

索引

一般的应用系统，读写比例在10：1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的也是最容易出现问题的，还是一些复杂的查询操作，因此对查询语句的优化是重中之重，加速查询最好的方法就是索引。索引：简单的说，相当于图书的目录，可以帮助用户快速的找到需要的内容。在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。能够大大提高查询效率。特别是当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。总结：索引的目的在于提高查询效率，与我们查询图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小结，然后找到页数。相似的例子还有：查字典，查地图等。

索引类型

普通索引：是最基本的索引，它没有任何限制。 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

1、只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引2、尽量使用短索引，如果可以，应该制定一个前缀长度3、对于经常在where子句使用的列，最好设置索引，这样会加快查找速度4、对于有多个列where或者order by子句的，应该建立复合索引5、对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引6、尽量不要在列上进行运算（函数操作和表达式操作）7、尽量不要使用not in和<>操作

SQL慢查询优化

如何捕获低效sql

1、slow_query_log：这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。2、ong_query_time：当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。3、slow_query_log_file：记录日志的文件名。4、log_queries_not_using_indexes：这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

慢查询优化的基本步骤

1、先运行看看是否真的很慢，注意设置SQL_NO_CACHE2、where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高3、explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）4、order by limit 形式的sql语句让排序的表优先查5、了解业务方使用场景6、加索引时参照建索引的几大原则7、观察结果，不符合预期继续从1开始分析

优化原则

1、查询时，能不要*就不用*，尽量写全字段名2、大部分情况连接效率远大于子查询3、多使用explain和profile分析查询语句4、查看慢查询日志，找出执行时间长的sql语句优化5、多表连接时，尽量小表驱动大表，即小表 join 大表6、在千万级分页时使用limit7、对于经常使用的查询，可以开启缓存

数据库表优化

优化数据库的方法

1、选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’ 、’ 性别’最好适用ENUM2、使用连接(JOIN)来代替子查询3、适用联合(UNION)来代替手动创建的临时表4、事务处理5、锁定表、优化事务处理6、适用外键，优化锁定表7、建立索引8、优化查询语句

简单描述MySQL 中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

数据库中的事务是什么

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

数据库事务的特性

1、原子性：即不可分割性，事务要么全部被执行， 要么就全部不被执行；2、一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态；3、隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务；4、持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

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

SQL 注入漏洞产生的原因？如何防止

SQL 注入产生的原因：程序开发过程中不注意规范书写sql 语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST 和GET 提交一些sql 语句正常执行。防止SQL 注入的方式：开启配置文件中的magic_quotes_gpc 和magic_quotes_runtime 设置执行sql 语句时使用addslashes 进行sql 语句转换Sql 语句书写尽量不要省略双引号和单引号。过滤掉sql 语句中的一些关键词： update、insert、delete、select、* 。提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的。

为表中得字段选择合适得数据类型

存储时期

Datatime：以YYYY-MM-DD HH:MM:SS 格式存储时期时间，精确到秒，占用8 个字节得存储空间，datatime 类型与时区无关Timestamp：以时间戳格式存储，占用4 个字节，范围小1970-1-1 到2038-1-19，显示依赖于所指定得时区，默认在第一个列行的数据修改时可以自动得修改timestamp 列得值Date:（生日） 占用得字节数比使用字符串.datatime.int 储存要少，使用date 只需要3 个字节，存储日期月份，还可以利用日期时间函数进行日期间得计算Time：存储时间部分得数据注意：不要使用字符串类型来存储日期时间数据（ 通常比字符串占用得储存空间小，在进行查找过滤可以利用日期得函数）使用int 存储日期时间不如使用timestamp 类型

对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题

1、索引的目的是什么？快速访问数据表中的特定信息，提高检索速度创建唯一性索引，保证数据库表中每一行数据的唯一性。加速表和表之间的连接使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间2、索引对数据库系统的负面影响是什么？创建索引和维护索引需要耗费时间， 这个时间随着数据量的增加而增加； 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。3、为数据表建立索引的原则有哪些？在最频繁使用的、用以缩小查询范围的字段上建立索引。在频繁使用的、需要排序的字段上建立索引4、什么情况下不宜建立索引？对于查询中很少涉及的列或者重复值比较多的列， 不宜建立索引。对于一些特殊的数据类型，不宜建立索引，比如文本字段（ text）等

解释MySQL 外连接、内连接与自连接的区别

先说什么是交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。内连接则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。外连接其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，这三种情况依次称之为左外连接， 右外连接，和全外连接。左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL 来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换， MySQL 目前还不支持全外连接。

Myql 中的事务回滚机制概述

事务是用户定义的一个数据库操作序列， 这些操作要么全做要么全不做， 是一个不可分割的工作单位，事务回滚是指将该事务已经完成的对数据库的更新操作撤销。要同时修改数据库中两个不同表时， 如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改， 第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚。

SQL 语言包括哪几部分？每部分都有哪些操作关键字

完整性约束包括哪些

数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。分为以下四类：1、实体完整性：规定表的每一行在表中是惟一的实体。2、域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。3、参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。4、用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。与表有关的约束：包括列约束(NOT NULL（非空约束）)和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 。

什么是锁

数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制， 在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。

什么叫视图？游标是什么

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查， 操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易， 相比多表查询。游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候， 游标显得十分重要。

什么是存储过程？用什么来调用

存储过程是一个预编译的SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象来调用存储过程。

如何通俗地理解三个范式

第一范式：1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解；第二范式：2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；第三范式：3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。范式化设计优缺点：优点：可以尽量得减少数据冗余，使得更新快， 体积小缺点：对于查询需要多个表进行关联， 减少写得效率增加读得效率，更难进行索引优化反范式化：优点：可以减少表得关联，可以更好得进行索引优化缺点：数据冗余以及数据异常，数据得修改需要更多的成本

什么是基本表？什么是视图

基本表是本身独立存在的表，在SQL 中一个关系就对应一个表。视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中， 是一个虚表。

试述视图的优点

(1) 视图能够简化用户的操作(2) 视图使用户能以多种角度看待同一数据；(3) 视图为数据库提供了一定程度的逻辑独立性； (4) 视图能够对机密数据提供安全保护。

NULL 是什么意思

NULL 这个值表示UNKNOWN(未知):它不表示“” (空字符串)。对NULL 这个值的任何比较都会生产一个NULL 值。您不能把任何值与一个NULL 值进行比较， 并在逻辑上希望获得一个答案。

你可以用什么来确保表格里的字段只接受特定范围里的值

Check 限制，它在数据库表格里被定义，用来限制输入该列的值。触发器也可以被用来限制数据库表格里的字段能够接受的值，但是这种办法要求触发器在表格里被定义，这可能会在某些情况下影响到性能。

说说对SQL 语句优化有哪些方法

1、Where 子句中：where 表之间的连接必须写在其他Where 条件之前，那些可以过滤掉最大数量记录的条件必须写在Where 子句的末尾.HAVING 最后2、用EXISTS 替代IN、用NOT EXISTS 替代NOT IN3、避免在索引列上使用计算4、避免在索引列上使用IS NULL 和IS NOT NULL5、对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引6、应尽量避免在where 子句中对字段进行null 值判断， 否则将导致引擎放弃使用索引而进行全表扫描7、应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

应用场景

“三高”需求：High performance - 对数据库高并发读写的需求。Huge Storage - 对海量数据的高效率存储和访问的需求。High Scalability && High Availability- 对数据库的高可扩展性和高可用性的需求。

共同特点：（1）数据量大（2）写入操作频繁（3）价值较低的数据、对事务性要求不高

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

MongoDB简介

MongoDB是一个开源、高性能、无模式的文档型数据库，当初的设计就是用于简化开发和方便扩展，是NoSQL数据库产品中的一种。它支持的数据结构非常松散，是一种类似于 JSON 的 格式叫BSON，所以它既可以存储比较复杂的数据类型，又相当的灵活。MongoDB中的记录是一个文档，它是一个由字段和值对（field:value）组成的数据结构。MongoDB文档类似于JSON对象，即一个文档认为就是一个对象。字段的数据类型是字符型，它的值除了使用基本的一些类型外，还可以包括其他文档、普通数组和文档数组。

数据模型

MySQL与MongoDB对比

MongoDB的特点

高性能

高可用性

MongoDB的复制工具称为副本集（replica set），它可提供自动故障转移和数据冗余。

高扩展性

MongoDB提供了水平可扩展性作为其核心功能的一部分。分片将数据分布在一组集群的机器上。（海量数据存储，服务能力水平扩展）从3.4开始，MongoDB支持基于片键创建数据区域。在一个平衡的集群中，MongoDB将一个区域所覆盖的读写只定向到该区域内的那些片。

丰富的查询支持

MongoDB支持丰富的查询语言，支持读和写操作(CRUD)，比如数据聚合、文本搜索和地理空间查询等。

其他特点

如无模式（动态模式）、灵活的文档模型

基本常用命令

数据库操作

选择和创建数据库的语法格式

use 数据库名称

如果数据库不存在则自动创建

查看有权限查看的所有数据库命令

show dbs 或 show databases

查看当前正在使用的数据库命令

db

MongoDB默认的数据库为test，如果没有选择数据库，集合将存放在test数据库中

数据库的删除

db.dropDatabase()

集合操作

集合的显式创建

db.createCollection(集合名称)

集合的隐式创建

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

查看当前库中的表

show tables 或者 show collections

集合的删除

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

文档基本CRUD

文档的插入

单个文档的插入

db.collection.insert()

批量插入

db.collection.insertMany()

文档的基本查询

查询所有

db.comment.find() 或 db.comment.find({})

投影查询

>db.comment.find({userid:\"1003\

文档的更新

覆盖修改

db.comment.update({_id:\"1\

局部修改

db.comment.update({_id:\"2\

批量修改

//默认只修改第一条数据db.comment.update({userid:\"1003\

列值增长的修改

db.comment.update({_id:\"3\

文档的删除

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

db.comment.remove({})

统计查询

分页列表查询

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

//第一页db.comment.find().skip(0).limit(2)//第二页db.comment.find().skip(2).limit(2)//第三页db.comment.find().skip(4).limit(2)

排序查询

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

正则的复杂条件查询

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

db.comment.find({content:/^专家/})

比较查询

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

包含查询

db.comment.find({userid:{$in:[\"1003\

条件连接查询

db.comment.find({$or:[ {userid:\"1003\

常用命令小结

索引-Index

单字段索引

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

复合索引

其他索引

地理空间索引（Geospatial Index）

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

文本索引（Text Indexes）

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

哈希索引（Hashed Indexes）

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

索引的管理操作

索引的查看

db.collection.getIndexes()

默认_id索引：MongoDB在创建集合的过程中，在_id 字段上创建一个唯一的索引，默认名字为_id_ ，该索引可防止客户端插入两个具有相同值的文档，您不能在_id字段上删除此索引。注意：该索引是唯一索引，因此值不能重复，即_id 值不能重复的。在分片集群中，通常使用_id 作为片键。

索引的创建

索引的移除

db.collection.dropIndex(index)

index:指定要删除的索引。可以通过索引名称或索引规范文档指定索引。若要删除文本索引，请指定索引名称。

db.comment.dropIndexes()

删除spit集合中所有的索引

索引的使用

执行计划

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

涵盖的查询

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

MongoDB集群和安全

副本集-Replica Sets

简介

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

也可以说，副本集类似于有自动故障恢复功能的主从集群。通俗的讲就是用多台机器进行同一数据的异步同步，从而使多台机器拥有同一数据的多个副本，并且当主库当掉时在不需要用户干预的情况下自动切换其他备份服务器做主库。而且还可以利用副本服务器做只读服务器，实现读写分离，提高负载

冗余和数据可用性

复制提供冗余并提高数据可用性。 通过在不同数据库服务器上提供多个数据副本，复制可提供一定级别的容错功能，以防止丢失单个数据库服务器。在某些情况下，复制可以提供增加的读取性能，因为客户端可以将读取操作发送到不同的服务上， 在不同数据中心维护数据副本可以增加分布式应用程序的数据位置和可用性。 您还可以为专用目的维护其他副本，例如灾难恢复，报告或备份。

MongoDB中的复制

副本集是一组维护相同数据集的mongod实例。 副本集包含多个数据承载节点和可选的一个仲裁节点。在承载数据的节点中，一个且仅一个成员被视为主节点，而其他节点被视为次要（从）节点。主节点接收所有写操作。 副本集只能有一个主要能够确认具有{w：“most”}写入关注的写入; 虽然在某些情况下，另一个mongod实例可能暂时认为自己也是主要的。主要记录其操作日志中的数据集的所有更改，即oplog。辅助(副本)节点复制主节点的oplog并将操作应用于其数据集，以使辅助节点的数据集反映主节点的数据集。 如果主要人员不在，则符合条件的人员中将举行选举以选出新的主要人员。

主从复制和副本集区别

主从集群和副本集最大的区别就是副本集没有固定的“主节点”；整个集群会选出一个“主节点”，当其挂掉后，又在剩下的从节点中选中其他节点为“主节点”，副本集总有一个活跃点(主、primary)和一个或多个备份节点(从、secondary)。

副本集的三个角色

两种类型

主节点类型：数据操作的主要连接点，可读写

次要（辅助、从）节点类型：数据冗余备份节点，额可以读或选举

三种角色

主要成员（Primary）：主要接收所有写操作

副本成员（Replicate）：从主节点通过复制操作以维护相同的数据集，即备份数据，不可写操作，但可以读操作（但需要配置）。是默认的一种从节点类型。

仲裁者（Arbiter）：不保留任何数据的副本，只具有投票选举作用。当然也可以将仲裁服务器维护为副本集的一部分，即副本成员同时也可以是仲裁者。也是一种从节点类型。

主节点的选举原则

主节点选举的触发条件

1、主节点故障2、主节点网络不可达（默认心跳信息为10秒）3、人工干预（rs.stepDown(600)）

选举规则是根据票数来决定谁获胜：票数最高，且获得了“大多数”成员的投票支持的节点获胜。“大多数”的定义为：假设复制集内投票成员数量为N，则大多数为 N/2 + 1。例如：3个投票成员，则大多数的值是2。当复制集内存活成员数量不足大多数时，整个复制集将无法选举出Primary，复制集将无法提供写服务，处于只读状态。若票数相同，且都获得了“大多数”成员的投票支持的，数据新的节点获胜。数据的新旧是通过操作日志oplog来对比的。

分片集群-Sharded Cluster

分片概念

分片（sharding）是一种跨多台机器分布数据的方法， MongoDB使用分片来支持具有非常大的数据集和高吞吐量操作的部署。换句话说：分片(sharding)是指将数据拆分，将其分散存在不同的机器上的过程。有时也用分区(partitioning)来表示这个概念。将数据分散到不同的机器上，不需要功能强大的大型计算机就可以储存更多的数据，处理更多的负载。有两种解决系统增长的方法：垂直扩展和水平扩展。垂直扩展意味着增加单个服务器的容量，例如使用更强大的CPU，添加更多RAM或增加存储空间量。可用技术的局限性可能会限制单个机器对于给定工作负载而言足够强大。此外，基于云的提供商基于可用的硬件配置具有硬性上限。结果，垂直缩放有实际的最大值。

分片集群包含的组件

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

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

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

分片集群的架构目标

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

安全认证

默认情况下，MongoDB实例启动运行时是没有启用用户访问权限控制的，也就是说，在实例本机服务器上都可以随意连接到实例进行各种操作，MongoDB不会对连接客户端进行用户验证，这是非常危险的。

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

为了强制开启用户访问控制(用户验证)，则需要在MongoDB实例启动时使用选项--auth 或在指定启动配置文件中添加选项auth=true 。

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

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

MongoDB的用户和角色权限

关于角色权限的查看

示例：查看所有内置角色：// 查询当前数据库中的某角色的权限> db.runCommand({ rolesInfo: \"<rolename>\" })// 查询其它数据库中指定的角色权限> db.runCommand({ rolesInfo: { role: \"<rolename>\

常用的内置角色

数据库用户角色：read、readWrite;

read：可以读取指定数据库中任何数据；readWrite：可以读写指定数据库中任何数据，包括创建、重命名、删除集合。

所有数据库用户角色：readAnyDatabase、readWriteAnyDatabase、userAdminAnyDatabase、dbAdminAnyDatabase

readAnyDatabase：可以读取所有数据库中任何数据(除了数据库config和local之外)。readWriteAnyDatabase： 可以读写所有数据库中任何数据(除了数据库config和local之外)。userAdminAnyDatabase： 可以在指定数据库创建和修改用户(除了数据库config和local之外)。dbAdminAnyDatabase ： 可以读取任何数据库以及对数据库进行清理、修改、压缩、获取统计信息、执行检查等操作(除了数据库config和local之外)。

数据库管理角色：dbAdmin、dbOwner、userAdmin；

dbAdmin：可以读取指定数据库以及对数据库进行清理、修改、压缩、获取统计信息、执行检查等操作。userAdmin： 可以在指定数据库创建和修改用户。clusterAdmin： 可以对整个集群或数据库系统进行管理操作。

集群管理角色：clusterAdmin、clusterManager、clusterMonitor、hostManager；

备份恢复角色：backup、restore；

backup： 备份MongoDB数据最小的权限。restore：从备份文件中还原恢复MongoDB数据(除了system.profile集合)的权限。

超级用户角色：root

内部角色：system

分库分表

ShardingSphere

数据读写分离及分库分表场景详解

常见数据分片算法hash、list、range、tag详解

常见数据库中间件Mycat与ShardingSphere对比

解密Sharding-jdbc核心概念与快速开始

深入Sharding-jdbc特性详解与模块划分

实战订单交易中orders和ordersTtem分库分表开发

深入Sharding-jdbc源码之SQL解析、SQL路由、SQL改写、SQL执行、结构合并