首页  思维导图  详情

数据库，数据结构 - mysql, mongodb, redis, es

2024-12-05 17:40:33   13  举报





AI智能生成

数据库，键值数据库，检索数据库，数据结构，数据排序，索引；数据库实例分析 - mysql, mongodb, redis, elasticsearch (es) 涵盖各类关系/非关系型数据库。

数据结构

数据库

redis

ElasticSearch

MySQL

作者其他创作

大纲/内容

存储问题

数据结构

一维线性结构

顺序表

顺序表可以实现下标的快速访问

顺序表在中间或者头部插入节点和删除节点必须依次挪动节点

顺序表每次增容固定大小的空间有可能造成空间浪费，但不用每次插入时都动态开辟空间

队列

先进先出

应用

排队问题

事件驱动

堆栈

先进后出

应用

历史记录

括号匹配问题

链表

单项链表

单链表必须从头依次遍历查找，时间复杂度为O(n)

单链表插入、删除节点快，时间复杂度为O(1)

单链表每次插入节点时都必须动态开辟空间、删除节点时必须释放掉动态开辟的空间

应用

升级循环单项链表

花名册录入

循环单项链表

双向链表

回顾回述问题

节点可以向前查找，向后查找

应用

升级循环双向链表

回顾回述问题

循环双向链表

二维树状结构

hash结构

结构

哈希表首先定义链结点Node。其中Node有三个属性，一个是key，一个value，还有一个是对应链表的point

连续空间中顺序键值对-->链表

特点

信息压缩

插入快速

hash碰撞

范围查询难

排序难

平衡二叉树

结构

左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树

特点

插入，查找，删除的时间复杂度最好情况和最坏情况都维持在O(logN)

树的深度高了以后，回述和查询效率大大下降

AVL树

结构

由于二叉搜索树在最坏的情况下（顺序写入）会退化成链表，搜索时的时间复杂度高

这里AVL树在节点进行插入、删除、修改的时候进行了自平衡，让整棵树不至于过于倾斜

树的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树

某结点的左子树与右子树的高度（深度）差即为该结点的平衡因子（BF、Balance Factor）

图示

右边二叉树中节点值为10的左右子树高度差为2

自平衡手段

如果插入新节点时发现左右子树的平衡因子的绝对值大于2，通过LL、LR、RR、RL的操作保证平衡因子的绝对值小于等于1

特点

AVL树由于自平衡的存在，使得树的高度不会太高，平均搜索的时间复杂度为O(logN)

树的高度较高，需要多次IO操作

B树

结构

B树属于多叉树又名平衡多路查找树

排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则

子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M路,当M=2则是2叉树,M=3则是3叉树）

特点

每个节点包含的关键字增多了，在B树应用到数据库中的时候，数据库充分利用了磁盘块的原理

树的节点关键字增多后，树的层级比原来的二叉树少了，减少数据查找的时间复杂度

B+树

结构

B+树是B树的一个升级版

B+树的非叶子节点不保存关键字记录的指针，只进行数据索引

B+树叶子节点保存了父节点的所有关键字记录的指针

B+树叶子节点的关键字从小到大有序排列，相互双向指针

特点

B+树的层级更少：相较于B树B+每个非叶子节点存储的关键字数更多

B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表

B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可

B*树

结构

B+树初始化的关键字初始化个数是cei(m/2)，b*树的初始化个数为（cei(2/3*m)）

每个节点都有指向兄弟的指针

特点

节点空间使用率更高，而又存有兄弟节点的指针，可以向兄弟节点转移关键字的特性使得B*树额分解次数变得更少

总结各种数据结构

前面讲述了大量的数据结构，最后发现B树和B+树是较为合理的，可以作为索引底层的数据结构

评价数据结构是否适合作为索引的标准就是查询数据时磁盘IO的次数，因为磁盘IO的速度比起内存IO要慢上几个数量级

由于B树在非叶子节点同时存储数据和关键字，造成一个节点能够存储的数据个数不会太多，那么B树的高度就会比较高，磁盘IO的次数就会较多

B+树非叶子节点只存储关键字，因此能够存储的关键字更多，B+树的高度就不会太高，一般为3 ~ 4层。因此B+树的高度比B树低，磁盘IO次数更少

综上所述B+树更适合作为索引底层的数据结构

高维图结构

子主题

HNSW (Hierarchical Navigable Small World) 索引是一种高效的近似最近邻 (Approximate Nearest Neighbor, ANN) 搜索算法，它主要用于大规模数据集中的高效查询。HNSW 索引的核心原理基于图论和小世界网络（Small World Network）的概念。

构建过程：

初始化：首先，对数据点进行排序，然后选择一个固定大小的邻居窗口，将每个数据点与其邻居连接起来，形成一个初始的邻接列表。
构建层次结构：递归地将这个邻接列表扩展为多层，每一层包含更远的邻居。相邻层之间的节点通过随机重采样连接，形成小世界结构。
添加新节点：当有新数据加入时，会根据其与现有节点的距离插入到适当的层次中，并调整邻接关系。
搜索过程：

查询：对于一个查询点，从根节点开始，沿着层次结构向下搜索，找到最接近的邻居节点。搜索过程中，如果遇到分支点，会优先考虑距离更近的分支。
扩散策略：为了提高搜索精度，HNSW 使用扩散策略，即从当前节点开始，不仅查看直接邻居，还会查看其邻居的邻居，直到达到预设的深度或找到足够数量的候选结果。
优点：

高效：HNSW 在处理大规模数据时非常高效，因为它使用了局部性原理，即相似的数据点在图中通常很接近。
可扩展性：可以动态添加和删除数据，而无需重建整个索引。
内存效率：由于采用了小世界结构，HNSW 可以在内存有限的情况下处理大量数据。
缺点：

精度与空间复杂度：随着图的层数增加，精度提高但空间复杂度也增大。需要权衡搜索精度和内存使用。
不适合高维数据：对于高维数据，HNSW 的性能可能会下降，因为高维空间中的“近似”可能不直观。

排序问题

冒泡排序

选择排序

快速排序

希尔排序

堆排序

检索问题

顺序查找

二分法查找

聊聊精排

“精排”或精细化排序，在数据库查询和搜索场景下，通常表示对搜索结果的一种高级筛选和排序策略

条件限制

权重计算（依据用户喜好、评分等因素）

更加复杂的算法以提供满足用户特定偏好结果的排序方式，动态调整结果权重、相关度排名等。

“索引能力”在此过程中至关重要，因为良好的索引能够显著提高查找的速度，减少搜索结果的计算成本

使用适当的索引能提升“最近用户评价”“按价格区间过滤产品”或是类似基于某种属性或关系的快速过滤需求

添加额外的索引以跟踪和聚合用户行为，可以在推荐系统或个性化服务上发挥巨大作用，实现精确用户偏好和内容推荐

关系型数据库 RDB

Mysql

优势应用场景

适合快速开发应用，与其他大型数据库的设置和管理相比，其复杂程度较低，易于学习。开源免费。

Web 网站系统

日志记录系统

嵌入式系统

MySQL架构

架构图

MySQL分为Server和Engine两大部分

服务层（Server）

连接数据库，校验权限

查询缓存，解析分析

优化、执行计划生成，索引选择

内置函数、存储过程、触发器、视图等

存储引擎层（Storage Engine）

Server层通过API和存储引擎通信，不同的存储引擎之间不会通信

存储引擎层负责数据的存储和提取，支持InnoDB、MyISAM、Memory等存储引擎

不同的存储引擎共用同一个Server层，存储引擎设置在表上

注意：存储引擎是不会解析SQL的

客户端

连接到MySQL服务器终端。例如JDBC的Java程序、Navicat、命令行终端等

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接

查询缓存

查询数据时，先去缓存找。如果缓存命中，直接返回；如果没有命中，走分析器 -> 优化器 -> 存储引擎

分析器

检查SQL语法是否正确

词法分析

MySQL需要识别出里面的字符串分别是什么，代表什么

MySQL从你输入的"SELECT"这个关键字识别出来，这是一个查询语句

它也要把字符串"T"识别成"表名"T"，把字符串"ID"识别成"列ID"

语法分析

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法

经过词法分析和语法分析形成抽象语法树

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引

或者在一个语句有多表关联（JOIN）的时候，决定各个表的连接顺序

优化器阶段完成后，这个语句的执行方案就确定了

执行器

进行权限检查，判断是否具有查询表的权限

调用存储引擎的接口，获取数据

Mysql的集群架构

主从架构

1 master - 1 salve

方案

Master-Slave 架构一般用于备份或者做读写分离，一般是一主一从设计。

优点

Master 主机会自动将数据同步到从机，可以进行读写分离

缺点

1）主库宕机后，数据可能丢失

2）从库只有一个SQL Thread，主库写压力大，复制很可能延时

双主架构

2 master - 1 slave - 1monitor

方案

当 Master 挂掉后，Monitor 可以切换新的Master节点为主写节点

优点

Monitor 可以感知集群的整体状态。

提升了主库的可用性

缺点

水平扩容仍然比较困难，只能通过增加集群物理性能来垂直扩容

推荐使用双主单写，因为双主双写存在ID冲突和双主更新覆盖丢失问题。

分片集群

(master - slave) * n

方案

MHA是一款优秀的故障切换和主从提升的高可用软件。能30秒之内自动完成数据库的故障切换并最大保证数据一致性

优点

高可用：可用性提升，故障转移快

高并发：扩展性提升，数据的查询，存储问题可以得到解决

一致性：主库崩溃不存在数据不一致情况

其他思考

分库分表

分配策略

集群扩容

Mysql数据类型

日期和时间类型

date：3字节，日期，格式：2014-09-18

time：3字节，时间，格式：08:42:30

datetime：8字节，日期时间，格式：2014-09-18 08:42:30，范围为1000-01-01 00:00:00/9999-12-31 23:59:59

timestamp：4字节，自动存储记录修改的时间，时间戳，范围为1970-01-01 00:00:00/2038

year：1字节，年份

数值类型

整型（有符号或者无符号）

tinyint：1字节，范围（-128 ~ 127）

smallint：2字节，范围（-32768 ~ 32767）

mediumint：3字节，范围（-8388608 ~ 8388607）

int或integer：4字节，范围（-2147483648 ~ 2147483647）

bigint：8字节，范围（+|-9.22 * 10的18次方）

注意：加上unsigned关键字，定义成无符号的类型，那么对应的取值范围就要少一倍了。例如tinyint unsigned的取值范围为0 ~ 255

数据范围

浮点型

float(m, d)：4字节，单精度浮点型，m总个数，d小数位

double(m, d)：8字节，双精度浮点型，m总个数，d小数位

decimal(m, d)：m + 2字节，decimal是存储为字符串的浮点数，numeric和decimal类似，可以近似等价于decimal

DECIMAL和NUMERIC 类型的存储精确的数值数据。这些类型用于保持精确精度很重要的情况，例如货币数据

在MySQL中建立了一个表，有一列为float(5, 3)，那么该列的最大值就是99.999，做了以下试验

插入123.45678，最后查询得到的结果为99.999。高版本MySQL直接报错

插入123.456，最后查询结果为99.999。高版本MySQL直接报错

插入12.34567，最后查询结果为12.346

在使用浮点型的时候，还是要注意陷阱的，要以插入数据库中的实际结果为准

字符串类型

char(n)：固定长度，最多255个字符

varchar(n)：可变长度，最多65535个字符

tinytext：可变长度，最多255个字符

text：可变长度，最多65535个字符

mediumtext：可变长度，最多2的24次方-1个字符

longtext：可变长度，最多2的32次方-1个字符

注意

char(n)和varchar(n)中括号中n代表字符的个数，并不代表字节个数。使用了中文的时候（UTF8）意味着可以插入n个中文，但是实际会占用n * 3个字节

同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n

对于char类型的字段不要设置的太大，如果不确定字段是否有值，建议设置成varchar(255)

基本SQL语句

DCL（Data Control Language）

这些语句定义了数据库、表、字段、用户的访问权限和安全级别

创建用户

给用户授权

撤销授权

查看权限

删除用户

DDL（Data Definition Language）

数据库操作

创建数据库

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

查询数据库

查询所有数据库

SHOW DATABASES

查询创建数据库时的sql

SHOW CREATE DATABASE db_name;

选择数据库

USE db_name;

查询当前正在使用的数据库

SELECT DATABASE();

删除数据库

DROP DATABASE db_name;

修改数据库的字符编码和排序方式

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

命令行设置之后操作的编码格式

SET NAMES UTF8

数据库表操作

创建表

SQL : CREATE TABLE tb_name (字段名、类型、长度、约束、默认、注释)

约束

主键

PRIMARY KEY

自增

AUTO_INCREMENT

唯一

UNIQUE

唯一约束，取值不允许重复，可以为NULL

并不是指只有一行字段为NULL，可以有多个为NULL

非空

NOT NULL

非负

UNSIGNED

只能用于数值

对于库存或者是余额意义的字段，建议加上该约束，如果程序出现错误新增或者修改后的值为负数，直接报错

外键

FOREIGN KEY

现在很少使用物理外键，一般使用逻辑外键

可以在开发环境或者测试环境加上外键，在正式环境去除外键

默认

DEFAULT

注释

COMMENT

表字段索引

唯一索引

添加

ALTER TABLE table_name ADD UNIQUE INDEX index_name(field_name);

删除

DROP INDEX index_name ON table_name

普通索引

添加

ALTER TABLE table_name ADD INDEX index_name(field_name);

删除

DROP INDEX index_name ON table_name

主键

添加

ALTER TABLE table_name ADD PRIMARY KEY (field_name)

删除

ALTER TABLE table_name DROP PRIMARY KEY;

联合索引

添加

ALTER TABLE table_name ADD index_name (field_name1, field_name2);

删除

DROP INDEX index_name ON table_name

修改表

表字段的增删改查

字段添加

ALTER TABLE tb_name ADD address VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';

ALERT TABLE tb_name ADD 添加字段字段类型非空约束默认注释

字段类型修改

ALTER TABLE tb_name MODIFY address VARCHAR (50) NOT NULL DEFAULT '' COMMENT '地址';

ALERT TABLE tb_name MODIFY 字段名称新的字段类型非负非空默认注释

字段名称类型修改

ALTER TABLE tb_name CHANGE address addr VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';

ALTER TABLE tb_name CHANGE 旧的字段名新的字段名新的类型约束默认注释

字段类型查询

DESC tb_name;

字段删除

ALTER TABLE tb_name DROP field_name;

表修改

表名修改

ALTER TABLE tb_name RENAME TO new_tb_name;

引擎修改

ALTER TABLE tb_name ENGINE = InnoDB;

删除表

DROP TABLE tb_name;

查询表

查询所有表

SHOW TABLES;

查询建表时的sql

SHOW CREATE TABLE tb_name;

DML（Data Manipulation Language）（重点）

增

添加单条

INSERT INTO tb_name(`field1`,`field2`,....) VALUES('value1','value2',.....);

添加多条

INSERT INTO tb_name(`field1`,`field2`,....) VALUES('value1','value2',.....), ('value1','value2',.....),('value1','value2',.....),....;

删

sql

DELETE FROM tb_name WHERE ...

注意

删除时必须加WHERE条件

改

sql

UPDATE tb_name SET field1 = value1, field2 = value2, ..... WHERE ....

注意

修改时必须加WHERE条件

DQL（Data Query Language）（重点）

基础的查询

SELECT * FROM tb_name

WHERE子句

比较运算符

大于、小于、等于、不等于、大于等于、小于等于

SELECT * FROM tb_name WHERE user_id >10;

逻辑运算符

逻辑运算符是用来拼接其他条件的。用and或者or来连接两个条件，如果用or来连接的时候必须使用小括号

SELECT * FROM tb_name WHERE user_id > 10 AND sex = '男'

LIKE模糊查询

通配符

%（百分号）匹配零个或者多个任意字符

_（下划线）匹配一个任意字符

sql

SELECT * FROM tb_name WHERE username LIKE '张%';查找username开头是张的数据

SELECT * FROM tb_name WHERE username LIKE '%张%';查询username中含有张的数据

SELECT * FROM tb_name WHERE username LIKE '%张';查询username字段的数据以张结尾的

SELECT * FROM tb_name WHERE username LIKE '张_';查询username以张开头后边有一个字符的数据

IN字段指定多个值查询

IN (value1, value2, value3, ....)

SELECT * FROM tb_name WHERE user_id IN (1, 3, 5, 7, 9, 11)。查询user_id是1, 3, 5, 7, 9, 11的所有数据

BETWEEN AND 区间查询

field BETWEEN value1 AND value2

字段的值大于等于value1同时小于等于value2

SELECT * FROM user WHERE user_id BETWEEN 2 AND 9。查询user表中user_id大于等于2小于等于9的所有值

GROUP BY分组查询

聚合函数

COUNT(field)：获取符合条件出现的非NULL值的次数

COUNT(*)：统计所有行

SUM(field)：获取所有符合条件的数据的总和

AVG(field)，取平均值

MAX(field)，取最大值

MIN(field)，取最小值

GROUP_CONCAT(field)，可以将分组的字符串以", "连接起来

SELECT sex, COUNT(*) count FROM class GROUP BY sex;获取class表中男生和女生的数量

HAVING对聚合值进行过滤

对聚合值或者是字段进行过滤

WHERE不能对聚合值进行过滤

ORDER BY查询排序

查询顺序

ORDER BY field DESC;降序查询

ORDER BY field ASC;升序查询

SELECT * FROM tb_name ORDER BY id DESC; 查询tb_name表中所有数据，按id的降序来查找

ORDER BY是否使用索引的严格要求

通过有序索引顺序扫描直接返回有序数据，通过explain分析显示Using Index，不需要额外的排序，操作效率比较高

索引的顺序和ORDER BY子句的顺序完全一致

索引中所有列的方向（升序、降序）和ORDER BY子句完全一致

当多表连接查询时ORDER BY中的字段必须在关联表中的第一张表中

通过对返回数据进行排序，也就是FileSort排序，所有不是通过索引直接返回排序结果的都叫FileSort排序

FileSort是通过相应的排序算法将取得的数据在sort_buffer_size系统变量设置的内存排序中进行排序

如果内存装载不下，就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集

可以理解为归并排序

LIMIT查询结果截取

参数

LIMIT后边可以跟两个参数，如果只写一个表示从零开始查询指定长度，如果两个参数就是从第一个参数开始查询查询长度是第二个参数的值，俩个参数必须是整形

SELECT * FROM tb_name LIMIT 5;查询tb_name表中的所有数据，只要前边的5条数据

SELECT * FROM tb_name LIMIT 5, 5;查询tb_name中所有的数据，返回的结果是从第五条开始截取五条数据

分页查询一般会全表扫描，优化的目的应尽可能减少扫描；

第一种思路：在索引上完成排序分页的操作，最后根据主键关联回原表查询原来所需要的其他列。这种思路是使用覆盖索引尽快定位出需要的记录的id，覆盖索引效率高些

第二中思路：limit m,n 转换为 n

之前分页查询是传pageNo页码, pageSize分页数量，
当前页的最后一行对应的id即last_row_id，以及pageSize，这样先根据条件过滤掉last_row_id之前的数据，然后再去n挑记录,此种方式只能用于排序字段不重复唯一的列，如果用于重复的列，那么分页数据将不准确

JOIN连接查询

JOIN连接查询总共有7种（内连接、左连接（全A）、右连接（全B）、左连接（只A）、右连接（只B）、全外连接、交叉外连接）

内连接：SELECT <select...list> FROM TableA A INNER JOIN TableB B ON A.Key=B.Key;

左连接（全A）：SELECT <select...list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key;

右连接（全B）：SELECT <select...list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;

左连接（只A）：SELECT <select…list> FROM TableA a LEFT JOIN TableB b ON A.Key=B.Key WHERE B.Key IS NULL;

右连接（只B）：SELECT <select…list> FROM TableA a RIGHT JOIN TableB b ON A.Key=B.Key WHERE A.Key IS NULL;

全外连接：SELECT <select…list> FROM TableA a FULL OUTER JOIN TableB b ON A.Key=B.Key;

由于MySQL不支持FULL JOIN这种语法（在orcale可行），所以使用UNION关键字拼接左连接（全A）和右连接（全B）结果并去重来达到效果

SELECT <select...list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key
UNION
SELECT <select...list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;

交叉外连接

同理，使用UNION关键字拼接左连接（只A）和右连接（只B）达到效果

SELECT <select…list> FROM TableA a LEFT JOIN TableB b ON A.Key=B.Key WHERE B.Key IS NULL
UNION
SELECT <select…list> FROM TableA a RIGHT JOIN TableB b ON A.Key=B.Key WHERE A.Key IS NULL;

JOIN时使用ON和WHERE过滤的区别

INNER JOIN

如果是内连接将过滤条件写在ON和WHERE的效果一样

LEFT JOIN

RIGHT JOIN

FULL JOIN

事务控制语言

关键词

BEGIN：开启事务

ROLLBACK：事务回滚

COMMIT：事务提交

必备条件

表的存储引擎为InnoDB

索引（重点）

索引是什么

索引本质上是一种排好序的数据结构，是真实存在的，存储到物理磁盘文件中

对指定的列或者多列添加额外的数据结构，让查找变得更快，可能降低新增、修改、删除的时间

没有特别说明，一般说的索引都是指B树或者B+树

使用索引的优点和缺点

优点

可以大大提高查询速度

可以显著提高查询中分组和排序的速度

可以加速表与表的连接

可以通过创建唯一索引，可以保证每一行数据的唯一性

缺点

创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作

索引需要磁盘的空间进行存储，如果针对单表创建了大量的索引，数据文件大小上限问题

当对表中的数据进行新增、修改或者删除时，也会触发索引的维护，增加执行时间

索引的分类（重点）

按照使用字段的个数

单列索引

主键索引、辅助索引

使用主键字段的索引就是主键索引，其他都是辅助索引

辅助索引以非主键字段生成的称为辅助索引，又称为次级索引

聚簇索引、非聚簇索引

聚簇索引只有Innodb存储引擎支持，使用主键生成的索引

除了聚簇索引之外的索引，其他都是非聚簇索引

唯一索引（添加了唯一约束）

一个或者多个字段添加了唯一约束，形成唯一索引

全文索引

较少使用。一般使用ElasticSearch、Solr等搜索引擎代替

复合索引（联合索引、组合索引）

多个字段组成形成复合索引

按照索引和数据是否存储在一起（重点）

聚簇索引（聚集索引）

表的存储引擎为InnoDB，且使用主键或者非空唯一键或者默认row_id作为索引

非聚簇索引（非聚集索引）

表的存储引擎为InnoDB，除了主键或者非空唯一键或者row_id，之外的其他字段，作为索引列都是非聚簇索引

表的存储引擎为MyISAM，使用到的索引都为非聚簇索引

按照底层数据结构

B树索引（B树和B+树）

Hash索引

R-索引（空间索引）

全文索引（倒排索引）

扇区、磁盘块、内存页、局部性原理、磁盘预读、InnoDB数据页

扇区

硬盘的最小读写单元，一般是4KB

磁盘块

操作系统对硬盘读取的最小单元，一般是扇区的2的N次方

内存页

操作系统对内存操作的最小单元，一般是4KB

局部性原理

当一个数据被用到时，其附近的数据也通常会马上被使用

磁盘预读

程序运行期间所需要的数据通常比较集中

通常程序读取数据，并不是只读取需要的，而是将附近的数据都读取出来

通常是读取一整个磁盘块

InnoDB数据页

和操作系统读取磁盘类似，InnoDB读取数据是以页为单位进行读取的

页（Page）是Innodb存储引擎用于管理数据的最小磁盘单位

页大小默认为16KB

可以通过SQL：SHOW GLOBAL STATUS LIKE 'innodb_page_size';进行查看

综上所述，将B+树一个节点就设置成16KB，就是一个数据页大小。读取节点数据时将整个数据页一次性加载到内存，减少IO操作的次数

B+树根节点常驻内存，搜索时，遍历整个B+树需要进行磁盘IO的次数为h - 1（h为B+树的高度），一般B+树的高度一般为3 ~ 4层，那么只需要2 ~ 3次磁盘IO就可以获取数据。通常存储引擎会缓存索引，因此查询速度会更快

索引的基本操作

索引命名

普通索引：idx_字段1_字段2_...字段N

唯一索引：ux_字段1_字段2_...字段N

新增

建表时

INDEX `索引名`(字段1, 字段2(使用的长度)) USING BTREE

建表后

ALTER TABLE db_name.tbl_name ADD INDEX idx_name(filed_name(20));

删除

ALTER TABLE db_name.tbl_name DROP INDEX idx_name;

查询

一般将索引列在WHERE或者是ON的后面，同时注意索引不要失效

InnoDB和MyISAM存储引擎如何使用B+树索引

MyISAM

MyISAM所有的索引都是非聚簇索引

数据的存储不是按主键顺序存放的，按写入的顺序存放

主键索引和辅助索引

主键索引

图示

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意

可以看出MyISAM的索引文件非叶子节点存放主键，叶子结点存放主键和数据地址

辅助索引

图示

主键索引和辅助索引的区别在于主键不能重复，辅助索引的值可以重复

MyISAM存储引擎的索引文件和数据文件分开存放，因此也叫作非聚簇索引

如果表使用的存储引擎为MyISAM，存在三个文件.frm（表定义文件）、.MYD（数据文件）、.MYI（索引文件）

图示

InnoDB

只有InnoDB支持聚簇索引，只有InnoDB的主键索引是聚簇索引，除此之外的所有索引都是非聚簇索引

数据写入的顺序是按照主键的大小升序写入

主键索引和辅助索引

主键索引

图示

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果没有主键会去寻找第一个唯一非空键，如果也没有默认生成6字节的row_id作为主键。索引数据和真实数据放置在一起，在同一个文件中

非叶子节点存放关键字，叶子节点存放关键字以及实际数据

同时叶子节点键增加指向前后节点的顺序指针，对于范围查找非常方便

对于查找如果找到主键，那么就直接找到了整行数据，不需要进行额外IO操作

如果使用InnoDB存储引擎，存在两个文件.frm（表定义文件）和.idb（数据和索引文件）

图示

辅助索引

图示

关键字可以重复，非叶子节点存放关键字，叶子节点存放关键字和主键索引的值

辅助索引查找整行数据，需要先根据辅助索引的值进行定位到主键索引的值，然后去主键索引树查找整行数据，这里需要进行两次索引树的查找，因此整个过程叫回表

MySQL单表能够存储的数据量计算

这里以InnoDB的主键索引为例

B+树的一个节点大小为一个数据页，默认为16KB

B+树非叶子节点存储的数据为关键字和指针，主键字段的数据类型为bigint（8字节），同时存在着指向下一个主键的指针（6字节）

一个节点中主键的个数为16 * 1024 / (8 + 6) ≈ 1170个

叶子节点中存储主键和实际数据，一般而言实际数据大小远远大于主键大小，主键大小可以忽略不计。实际数据大小假定为1KB，那么叶子节点可以存储16KB / 1KB = 16条数据

通常B+树的高度为3层，整颗B+树能够存储的数据就是1170 * 1170 * 16 ≈ 2200 0000，大约等于2000万

也就是说MySQL单表能够存储的数据量大概在2000万左右

为什么MySQL推荐使用自增主键，而不推荐使用UUID或者雪花ID

这里主要都是InnoDB的主键索引，也就是聚簇索引

InnoDB存储引擎在写入数据时，按照主键的值进行顺序写入，也就是说如果主键的值如果不是自增（例如UUID杂乱无序），可能会将后面的数据，写入到前面的数据页中，可能造成数据页重建以及分裂

雪花ID的插入顺序略低于自增主键

使用自增ID的缺点

由于主键自增，容易被猜出业务增长量

在高并发写入情况下，可能造成对锁的争抢严重，造成写入性能下降

联合索引（复合索引）（重要）

联合索引指多个字段共同建立索引

对于经常在一起查询的字段例如A、B、C，通常建立(A, B, C)的联合索引，而不是为每一个字段都建立单独的索引

为什么使用联合索引

减少索引开销

例如建立联合索引(A, B, C)实际上会建立索引(A)、(A, B)、(A, B, C)三个索引

最左匹配原则（重要）

如果使用了联合索引在进行WHERE过滤时，需要注意MySQL会一直向右进行匹配，遇到范围查找就停止（>、<、BETWEEN、LIKE）

比如WHERE a = 1 AND b = 2 c > 3 AND d = 4如果建立(a, b, c, d)顺序的索引，d是用不到索引的

比如WHERE a = 1 AND b = 2 AND c = 3建立(a, b, c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式

比如WHERE c = 2 建立(a, b, c)索引是无法走该索引的

索引覆盖（重要）

又称为覆盖索引，本质上是一种现象，并不是一种实际存在的索引

查找数据时，只查询索引的值，不查询其他数据，过滤时只通过索引列进行过滤，命中索引就会直接返回索引数据

A、B、C三个字段建立了联合索引，进行如下SQL的查询：SELECT A, B, C FROM tbl WHERE A = XXX AND B = XXX;

因此在写SQL时，不需要的字段没有必要查询出来，尤其要避免SELECT *的写法

索引失效

联合索引，没有遵守最左前缀原则

联合索引，范围（>、<、BETWEEN AND）之后的字段索引失效

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

在高版本的MySQL似乎进行了优化

使用不等于（!=或者<>），索引失效

使用LIKE '%XXX%'进行模糊查询

可以使用LIKE 'abc%'，避免全表扫描，前面必须要有字段

字符串不使用单引号索引失效

在高版本的MySQL似乎进行了优化

使用IS NULL或者IS NOT NULL进行WHERE过滤

InnoDB存储引擎允许字段的值为NULL，也可以添加索引

在MySQL不同版本，不同数据量的情况下，优化器会根据情况进行判断是否使用索引，因此存在着4不同的情况

一般而言MySQL的版本越高，基本上都会走索引

IN关键字

在不同版本的MySQL，情况不相同，可能会走，也可能不会走索引

OR关键字

如果想要OR也是用索引，过滤的每一列都需要添加索引

MySQL判断全表扫描比索引查找更快，索引失效

总结：上述的几种情况，在高版本的MySQL中都进行了优化，同时和数据量也有一定的关系，因此走不走索引并不是绝对的

索引使用事项

应该

对于经常WHERE、ORDER BY、GROUP BY、聚合函数的字段建立索引

外键字段建立索引

字段具有唯一性，建议生成唯一索引。在数据库的层面，保证数据正确性

对于经常一起出现的字段，推荐建立联合索引，需要注意最左前缀原则，将经常过滤的字段放在前面

不应该

数据量不大，没有必要建索引，全表扫描可能更快

对于数据区分度不高的字段，不要建立索引

对于频繁发生修改的字段，不要建立索引

参与计算的列，不要建立索引

常见面试题

B树和B+树有什么区别，为什么MySQL使用B+树作为索引底层的数据结构

MyISAM和InnoDB是如何使用B+树索引的

聚簇索引和非聚簇索引有什么区别

索引覆盖是什么？什么是回表？

哪些情况下索引会失效

EXPLAIN（重点）

EXPLAIN关键字查看MySQL对SELECT类型的SQL的执行计划。从而知道MySQL是如何处理查询SQL的

语法

在原来的SELECT的SQL上增加EXPLAIN关键字即可

作用

查看表的读取顺序

查询类型

哪些索引被使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

测试使用的表

subject（学科表）

建表语句

create table subject( id int(10) auto_increment, name varchar(20), teacher_id int(10), primary key (id), index idx_teacher_id (teacher_id));

INSERT语句

insert into subject(name,teacher_id) values('math',1),('Chinese',2), ('English',3),('history',4);

teacher（教师表）

建表语句

create table teacher( id int(10) auto_increment, name varchar(20), teacher_no varchar(20), primary key (id), unique index unx_teacher_no (teacher_no(20)));alter table teacher add index idx_name(name(20));

INSERT语句

insert into teacher(name,teacher_no) values('wangsi','T2010001'), ('sunsi','T2010002'),('jiangsi','T2010003'),('zhousi','T2010004');

student（学生表）

建表语句

create table student( id int(10) auto_increment, name varchar(20), student_no varchar(20), primary key (id), unique index unx_student_no (student_no(20)));

INSERT语句

insert into student(name,student_no) values ('zhangsan','20200001'), ('lisi','20200002'),('yan','20200003'),('dede','20200004');

student_score（学生成绩表）

建表语句

create table student_score( id int(10) auto_increment, student_id int(10), subject_id int(10), score int(10), primary key (id), index idx_student_id (student_id), index idx_subject_id (subject_id));

INSERT语句

insert into student_score(student_id,subject_id,score) values(1,1,90),(1,2,60), (1,3,80),(1,4,100),(2,4,60),(2,3,50),(2,2,80),(2,1,90),(3,1,90),(3,4,100), (4,1,40),(4,2,80),(4,3,80),(4,5,100);

EXPLAIN中的列（重点）

表示查询中执行SELECT子句或操作表的顺序

有几个SELECT就有几个id

执行顺序

id相同

执行顺序从上到下

SQL语句

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

执行计划

执行的顺序为teacher -> subject -> student_score，并不是按照书写的顺序查询的

id不同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行

SQL语句

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

执行计划

执行的顺序为teacher -> subject -> score

id相同又不同

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

执行的顺序为2.teacher -> 2.subject -> 1.subject -> 1.teacher

id的值为NULL

id为NULL的最后执行

union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL

select_type

表示查询的类型，主要用于区别普通查询，联合查询，子查询等复杂查询

SIMPLE

简单的SELECT查询，查询中不包括子查询或者UNION

SQL语句

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

执行计划

PRIMARY

查询中若包括任何复杂的子部分，最外层查询则被标记为PRIMARY

SQL语句

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

执行计划

SUBQUERY

在select或where列表中包含了子查询

SQL语句

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

执行计划

DERIVED

在FROM列表中，包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里

MySQL5.7+进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

当派生子查询存在以下操作时该特性无法生效：UNION 、GROUP BY、DISTINCT、LIMIT/OFFSET以及聚合操作

SQL语句

EXPLAIN SELECT t1.* FROM (SELECT * FROM subject WHERE id = 1 GROUP BY id) t1

执行计划

UNION

若第二个SELECT出现在UNION之后，则被标记为UNION

若UNION包含在FROM子句的子查询中，外侧SELECT将被标记为DERIVE

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

UNION RESULT

从UNION表获取结果的SELECT

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

table

显示数据来自于哪个表，有时不是真实的表的名字（对表取了别名，显示别名），虚拟表最后一位是数字，代表id为多少的查询

type（重点）

在表中找到所需行的方式NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > in dex_subquery > range > index > ALL。效率从高到低

掌握常见8种即可：NULL > system > const > eq_ref > ref > range > index > ALL

一般来说达到range级别就可以，最好达到ref级别

NULL

MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

SQL语句

explain select min(id) from subject;

执行计划

const、system

当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问

如将主键或者是唯一键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

SQL语句

explain select * from teacher where teacher_no = 'T2010001';

执行计划

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;

执行计划

ref

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行。然而可能会找到多个符合条件的行。

SQL语句

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id

执行计划

range

只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引一般就是在你的where语句中出现between、<>、in等的查询

SQL语句

explain select * from subject where id between 1 and 3;

执行计划

index

Full index Scan，Index与All区别：index只遍历索引树，通常比All快因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的

通常见于索引覆盖或者是JOIN连接查询中（外键设置了索引）

SQL语句

explain select id from subject;

执行计划

ALL

Full Table Scan，将遍历全表以找到匹配行

SQL语句

explain select * from subject;

执行计划

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示NULL）

可能出现possible_keys有列，而显示NULL的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询

key（重点）

实际使用到的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

key_len计算规则如下

按照生成索引字段的数据类型来计算

MySQL常用数据类型（日期|时间、数值、字符串）

日期和时间类型

date：3字节，日期，格式：2014-09-18

time：3字节，时间，格式：08:42:30

datetime：8字节，日期时间，格式：2014-09-18 08:42:30，范围为1000-01-01 00:00:00/9999-12-31 23:59:59

timestamp：4字节，自动存储记录修改的时间，时间戳，范围为1970-01-01 00:00:00/2038

year：1字节，年份

整型（有符号）

tinyint：1字节，范围（-128 ~ 127）

smallint：2字节，范围（-32768 ~ 32767）

mediumint：3字节，范围（-8388608 ~ 8388607）

int或integer：4字节，范围（-2147483648 ~ 2147483647）

bigint：8字节，范围（+|-9.22 * 10的18次方）

注意：上面定义的都是有符号的

加上unsigned关键字，定义成无符号的类型，那么对应的取值范围就要翻翻了。例如tinyint unsigned的取值范围为0 ~ 255

表示数量的字段建议加上unsigned关键字，例如库存、余额等，如果程序计算库存小于0了，在UPDATE或者是INSERT的时候直接报错，保证库存不超卖

浮点型

float(m, d)：4字节，单精度浮点型，m总个数，d小数位

double(m, d)：8字节，双精度浮点型，m总个数，d小数位

decimal(m, d)：m + 2字节，decimal是存储为字符串的浮点数

字符串数据类型

char(n)：固定长度，最多255个字符

varchar(n)：可变长度，最多65535个字符

tinytext：可变长度，最多255个字符

text：可变长度，最多65535个字符

mediumtext：可变长度，最多2的24次方-1个字符

longtext：可变长度，最多2的32次方-1个字符

注意

char(n)和varchar(n)中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候（UTF8）意味着可以插入n个中文，但是实际会占用n * 3个字节

同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n

超过char和varchar的n设置后，直接报错

对于char类型的字段不要设置的太大，如果不确定字段是否有值，建议设置成varchar(255)

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

生成索引的字段，建议设置非空约束。如果存在非空，建议使用其他值代替

不损失精确性的情况下，长度越短越好

对于字符类型的字段，如果没有必要使用全部数据，可以只是用字符前几位。例如INDEX(name(21))

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered

表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%

Extra（重要）

Using index

表示使用了覆盖索引，查询的列就是建立了索引的列，不需要进行回表查询

Using where

不能完全通过索引过滤数据，需要Server端进行"后过滤"

Using filesort

当查询中包含order by操作，而且无法利用索引完成的排序操作称为"文件排序"，这里的文件排序是指先在内存中进行排序，当内存排序无法完成时，使用临时文件帮助排序

出现了Using filesort就需要对SQL语句进行优化

SQL语句

explain select * from subject order by name;

执行计划

Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见group by、order by、distinct、union等关键字

出现Using temporary，说明使用了临时表，可能需要对SQL语句进行优化

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

关于Using where、Using index、Using index & Using where、Using index condition详细解释说明：https://www.cnblogs.com/kerrycode/p/9909093.html

Using join buffer

改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能

Impossible where

这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）

Select tables optimized away

这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used

Query语句中使用from dual 或不含任何from子句

EXPLAIN EXTENDED

会在EXPLAIN的基础上额外提供一些查询优化信息

紧随其后SHOW WARNINGS命令可以查看优化后的查询语句，从而看出优化器优化了什么

EXPLAIN EXTENDED SELECT * FROM film WHERE id = 1;SHOW WARNINGS;

结果

在未来版本的MySQL可能会删除这个关键字

EXPLAIN PARTITIONS

相比EXPLAIN多了个PARTITIONS字段，如果查询是基于分区表的话，会显示查询将访问的分区

高版本的MySQL已经默认带上了PARTITIONS字段

缺点

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

EXPLAIN不考虑各种Cache

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

部分统计信息是估算的，并非精确值

EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

MySQL常见优化手段（重点）

索引优化规则

步骤

使用EXPLAIN关键字去查看SQL的执行计划

SHOW WARNINGS 在explain执行后执行，查看翻译后的sql

禁止使用select *，需要什么字段就去取哪些字段

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 使用索引
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; 未使用索引

存储引擎不能使用索引中，范围条件右边的列（左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列）
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 使用索引
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'; 未使用索引

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列，减少回表）），减少select *语句
EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager'; 只查询索引不用查询具体的数据，效率更高
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

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

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

like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

字符串不加单引号索引失效
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的

in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内

union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表

order by如果根据多个值进行排序，那么排序方式必须保持一致，要么同时升续，要么同时降续，排序方式不一致不走索引

优化数据库表结构的设计

字段的数据类型

字段类型

字符类型

不同存储引擎对char和varchar的使用原则不同，myisam:建议使用国定长度的数据列代替可变长度。
innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高

数值类型

金额类型的字段尽量使用long用分表示，尽量不要使用bigdecimal，严谨使用float和double因为计算时会丢失经度
如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal
如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围
不建议使用ENUM、SET类型，使用TINYINT来代替

日期类型

根据实际需要选择能够满足应用的最小存储日期类型。

如果应用只需要记录年份，那么仅用一个字节的year类型。
如果记录年月日用date类型, date占用4个字节，存储范围1000-01-01到9999-12-31
如果记录时间时分秒使用它time类型
如果记录年月日并且记录的年份比较久远选择datetime，而不要使用timestamp,因为timestamp表示的日期范围要比datetime短很多
如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp, 因为日期类型值只有它能够和实际时区相对应

timestamp与datetime
两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。
都可以使用自动更新CURRENT_TIMESTAMP

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
而对于DATETIME，不做任何改变，基本上是原样输入和输出。

timestamp占用4个字节：timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’
datetime占用8个字节：datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’

总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。如果需要使用到时区就必须使用timestamp，如果不使用时区就使用datetime因为datetime存储的时间范围更大

注意：禁止使用字符串存储日期，一般来说日期类型比字符串类型占用的空间小，日期时间类型在进行查找过滤是可以利用日期进行对比，这比字符串对比高效多了，日期时间类型有丰富的处理函数
也尽量不要使用int来存储时间戳

是否为null

MySQL字段属性应该尽量设置为NOT NULL，除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL 。

在MySql中NULL其实是占用空间的，“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理”，而空值”“是不占用空间的。

含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替null。

联表查询的时候，例如SELECT user.username, info.introduction FROM tbl_user user LEFT JOIN tbl_userinfo info ON user.id = info.user_id; 如果tbl_userinfo.introduction设置的可以为null, 假如这条sql查询出了对应的记录，但是username有值，introduction没有值，那么就不是很清楚这个introduction是没有关联到对应的记录，还是关联上了而这个值为null，null意思表示不明确，有歧义

注意：NULL在数据库里是非常特殊的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。使用 ISNULL()来判断是否为 NULL 值，NULL 与任何值的直接比较都为 NULL。

1) NULL<>NULL的返回结果是NULL，而不是false。
2) NULL=NULL的返回结果是NULL，而不是true。
3) NULL<>1的返回结果是NULL，而不是true。

数据类型的长度

表的存储引擎

数据库参数配置优化

数据库编码: 采用utf8mb4而不使用utf8

缓冲池大小：根据数据库负载和数据量大小来调整缓冲池大小，以提高数据库性能。

查询缓存：根据业务需求和查询频率来决定是否开启查询缓存，以减少查询时间。

连接数限制：根据业务需求和数据库负载来调整最大连接数限制，以避免连接过多导致数据库性能下降。

日志记录级别：根据业务需求和调试需求来调整日志记录级别，以平衡性能和调试信息的需求。

其他注意

不要使用count(列名)或 count(常量)来替代 count(*)。
说明:count(*)会统计值为NULL 的行，而count(列名)不会统计此列为NULL值的行

禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
避免使用存储过程、触发器

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户（这些操作基本上都是 CPU 处理内存中的数据比较运算）。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标

MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。

锁（重点）

数据库中的锁是为了保证并发访问时数据的一致性，使各种共享资源在被访问时变得有序而设计的一种规则

MySQL中关于锁的知识和事务隔离级别、索引、MVCC杂合在一起，显得非常乱。且锁的各种名词让人眼花缭乱

锁的分类

加锁机制

乐观锁

本质上是无锁的方式

总是乐观地认为不会发生锁冲突，如果发现更新失败，则进行失败重试，直到达到最大重试次数，回滚事务

悲观锁

只要是加了锁都是悲观锁

锁定表或者行，让其他数据操作等待

读锁（共享锁）

针对同一份数据，多个读操作可以同时进行而不会互相影响（不能进行写操作）

写锁（排他锁）

当前写操作没有完成前，它会阻断其他写锁和读锁

锁粒度

表锁

表锁是指对一整张表加锁。表锁由MySQL Server层实现

行锁

行锁是锁定某行、某几行或者行之间的间隙，由存储引擎实现，不同存储引擎实现不同。目前只有InnoDB存储引擎支持行锁

存储引擎

InnoDB存储引擎

行锁

兼容性

共享锁（Share Lock，简称S锁）

加了锁的记录，所有事务都能去读取但不能修改，同时阻止其他事务获得相同数据集的排他锁

排它锁（Exclusive Lock，简称X锁）

允许已经获得排他锁的事务去更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

表锁

意向锁

读意向锁（IS锁）

写意向锁（IX锁）

获取共享锁、排它锁之前需要先获取意向共享锁、意向排它锁

MyISAM存储引擎

表共享读锁（Table Read Lock）

表独享写锁（Table Write Lock）

InnoDB锁详细解释

行锁

兼容性

共享锁（Share Lock，简称S锁）

普通的SELECT语句不会加共享锁

想要显式加共享锁，可以加SELECT ... LOCK IN SHARE MODE子句

为了确保自己查询的数据一定是最新的数据，不会被其他事务进行修改

被读取的行记录或者行范围其他SESSION可以读，也可以加共享锁，但是其他事务无法获取排它锁，也就是说S锁不冲突，S锁和X锁冲突

其他事务可以进行普通的SELECT、SELECT ... LOCK IN SHARE MODE，但是不能进行UPDATE、DELETE、INSERT操作

排它锁（Exclusive Lock，简称X锁）

普通的UPDATE、INSERT、DELETE语句都会加排它锁

想要对SELECT语句显式加排它锁，可以加SELECT ... FOR UPDATE子句，相当于UPDATE语句

保证读取到的数据一定是最新的，不允许其他事务进行修改

其他事务可以进行普通的SELECT，但是不能进行SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT操作

LOCK IN SHARE MODE和FOR UPDATE的相同和不同

相同

二者都可以读取到最新的数据，保证不让其他事务进行修改

可以让普通的SELECT读，UPDATE、DELETE和INSERT语句阻塞

不同

共享锁和共享锁不是互斥的，排它锁和排它锁、共享锁和排它锁是互斥的

两个事务同时进行LOCK IN SHARE MODE，且对锁住的数据执行UPDATE语句，会造成死锁

排它锁只有一个事务能进行FOR UPDATE，不会发生死锁

LOCK IN SHARE MODE适用于并发度低，且不会执行UPDATE锁住数据的场景，FOR UPDATE适用于并发度高，且执行UPDATE锁住数据的场景

锁模式

记录锁（Record Lock）

更新数据时根据索引进行更新。记录锁最简单的一种行锁形式，记录锁是加在索引上的

如果更新语句中WHERE过滤条件不走索引的话，那么它就会升级到表锁，最终造成效率低下，所以在写SQL语句时需要特别注意

间隙锁（Gap Lock）

当我们使用范围条件而不是相等条件去检索，并请求锁时，InnoDB就会给符合条件的记录的索引项加上锁

而对于键值在条件范围内但并不存在的记录，就叫做间隙，InnoDB在此时也会对间隙加锁，这种记录锁+间隙锁的机制叫Next-Key Lock

间隙锁是一个索引值的左开右开的区间

临键锁（Next-key Lock）

临键锁是记录锁与与间隙锁的结合，所以临键锁与间隙锁是一个同时存在的概念，并且临键锁是个左开右闭的区间

记录锁、间隙锁、临建锁之间的关系示意图

插入意向锁（Insert Intention Lock）

插入意图锁是一种间隙锁，在行执行INSERT之前的插入操作设置

如果多个事务INSERT到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突

锁模式下各种锁之间的兼容矩阵

第一行表示已有的锁，第一列表示要加的锁

插入意向锁不影响其他任何锁

间隙锁和Next-Key与插入意向锁冲突

间隙锁和除了插入意向锁之外的锁都不冲突

记录锁和记录锁冲突，记录锁和Next-key冲突，Next-key和Next-key冲突

表锁

读写意向锁

由于表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，必须要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突

意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）

当事务要在记录上加上行锁时，要首先在表上加上意向锁。这样判断表中是否有记录正在加锁就很简单了，只要看下表上是否有意向锁就行了，从而就能提高效率

意向锁是InnoDB自动加的，不需要用户干预

自增锁

AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTOINC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的

读意向锁、写意向锁、自增锁、共享锁、排它锁之间的兼容性

第一行是已存在的锁，第一列是想要获取的锁

总结

当不存在冲突时，InnoDB存储引擎并不会默认生成锁，而是当多个事务冲突后才会生成锁

表锁为意向锁，意向锁主要是为了简化表锁和行锁之间的逻辑，表锁是InooDB存储引擎自己加上的，一般不用关注

较为复杂的是行锁，行锁有两种模式，一种是S锁，一种是X锁。行锁的类型又可以细分成记录锁、间隙锁、临建锁等

如何描述一个行锁呢？现有锁的模式（共享、排他），然后有锁的类型。例如共享记录锁、排他记录锁

可以在information_schema.INNODB_LOCKS系统表中查看当前InnoDB存储引擎中存在的锁

lock_mode，表示锁模式，主要有S、X、IS、IX、GAP、AUTO_INC

lock_type，表示锁类型，主要有Record Lock、Next-key Lock、Insert Intention Lock

常见加锁场景分析

一般而言表使用的存储引擎都是InnoDB，下面所有案例都是InnoDB存储引擎，这里只描述行锁中的X锁

执行SQL后究竟加什么锁和事务隔离级别、索引、是否命中数据均存在关系

事务隔离剂级别取读已提交和可重复读

索引取主键索引、二级唯一索引、二级非唯一索引

具体场景分析

建表语句

CREATE TABLE `book` (
`id` int(11) NOT NULL,
`isbn` varchar(255) DEFAULT NULL,
`author` varchar(255) DEFAULT NULL,
`score` double(2,1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `isbn_unique_index` (`isbn`) USING BTREE,
KEY `author_index` (`author`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据SQL

INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (10, 'N0001', 'Bob', 3.4);
INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (18, 'N0002', 'Alice', 7.7);
INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (25, 'N0003', 'Jim', 5.0);
INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (30, 'N0004', 'Eric', 9.1);
INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (41, 'N0005', 'Tom', 2.2);
INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (49, 'N0006', 'Tom', 8.3);
INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (60, 'N0007', 'Rose', 8.1);

案例1：聚簇索引，查询命中

SQL执行流程图

UPDATE语句会获取X锁，同时根据主键id，命中会加记录锁

记录锁和记录锁之间是冲突的

加锁记录

由于Record Lock之间冲突，所以在information_schema.INNODB_LOCKS才能看到

在RC和RR隔离等级下的加锁，两种隔离等级下没有任何区别，都是对id = 10这个索引加排他记录锁

案例2：聚簇索引，查询未命中

SQL执行流程图

间隙锁和插入意向锁，如果插入的数据在间隙锁的区间内就冲突，否则不冲突

在RC隔离等级下，不需要加锁。而在RR隔离级别会在id = 16前后两个索引（(10, 18)）之间加上间隙锁

加锁记录

案例3：二级唯一索引，查询命中

SQL执行流程

在InnoDB存储引擎中，二级索引的叶子节点保存着主键索引的值，然后再拿主键索引去获取真正的数据行，所以在这种情况下，二级索引和主键索引都会加排他记录锁，无论是RC还是RR隔离级别

加锁记录

案例4：二级唯一索引，查询未命中

SQL执行流程

在RR隔离等级下未命中时的加锁情况，RC隔离等级下该语句未命中不会加锁。在 N0007 和 Suprenum Record 之间加了间隙锁

加锁记录

在SHOW EGINE INNODB STATUS的日志中出现了插入意向锁等待间隙锁

案例5：二级非唯一索引，查询命中

案例6：二级非唯一索引，查询未命中

案例7：无索引

案例8：聚簇索引，范围查询

案例9：二级索引，范围查询

案例10：修改索引值

案例11：DELETE语句加锁分析

案例12：INSERT语句加锁分析

行锁分析

show status like'innodb_row_lock%';

Innodb_row_ lock_current_wait

当前正在等待锁定的数量

Innodb_row_ lock_time

从系统启动到现在锁定总时间长度

Innodb_row_ lock_time_avg

每次等待所花平均时间

Innodb_row_ lock_time_max

从系统启动到现在等待最长的一次所花时间

Innodb_row_ lock_waits

系统启动后到现在总共等待的次数

死锁

Session _1执行：select *from account where i d= 1 for update;
Session _2执行：select *from account where i d= 2 for update;
Session _1执行：select *from account where i d= 2 for update;
Session _2执行：select *from account where i d= 1 for update;
查看近期死锁日志信息：show engine innodb status;

undo log, redo log, binlog

binlog

简介

binlog用于记录数据库执行的写入性操作（不包括查询）信息，以二进制的形式保存在磁盘中

binlog是MySQL的逻辑日志，并且由Server层进行记录，使用任何存储引擎的MySQL数据库都会记录binlog日志

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志

使用场景

在实际应用中， binlog的主要使用场景有三个，分别是主从复制、数据恢复、数据同步

主从复制

在Master端开启 binlog ，然后将binlog发送到各个Slave端， Slave端重放binlog从而达到主从数据一致

数据恢复

通过使用mysqlbinlog工具来恢复数据

数据同步

例如canal监听mysql的binlog，然后将数据同步数据源中，例如将mysql数据导入到hive中

binlog刷盘时机

对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢

mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N

0：不去强制要求，由系统自行判断何时写入磁盘

1：每次commit的时候都要将binlog写入磁盘

N：每N个事务，才会将binlog写入磁盘

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值

但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能

binlog日志格式

binlog日志有三种格式，分别为STATMENT、ROW和MIXED

在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format参数指定

STATMENT

基于SQL语句的复制（statement-based replication, SBR），每一条DML语句会记录到binlog中

优点

不需要记录每一行的变化，减少了binlog日志量，节约了IO , 从而提高了性能

缺点

在某些情况下会导致主从数据不一致，比如执行CURRENT_TIMESTAMP函数等

ROW

基于行的复制（row-based replication, RBR），不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了

优点

不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题

缺点

会产生大量的日志，尤其是`alter table` 的时候会让日志暴涨

MIXED

基于STATMENT和ROW两种模式的混合复制（mixed-based replication, MBR），一般的复制使用STATEMENT模式保存binlog ，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

redo log

为什么需要redo log

事务四大特性之一为持久性，只要事务提交成功，那么对数据库的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态

最简单的做法就是每次事务提交时，将事务涉及到修改的数据页全部刷新到磁盘中

上述做法存在着严重的性能问题

InnoDB是以页为单位与磁盘进行交互，一个数据页大小为16kb，一个事务可能只修改一个数据页里面几个字节。如果将完整的数据页刷新到磁盘太浪费资源

一个事务可能涉及到多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差

常见做法是修改数据时，先将数据读取到内存的缓冲池中，然后进行修改。数据在内存中被修改，与磁盘中相比就存在了差异，这种有差异的数据成为脏页

因此MySQL设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了（相对而言文件更小并且是顺序IO）

redo log基本概念

redo log包括两部分：一个是内存中的日志缓冲（redo log buffer），另一个是磁盘上的日志文件（redo log file）

MySQL每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file

这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL（Write-Ahead Logging）技术

redo log刷写时机

用户空间（user space）下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过内核空间（kernel space）缓冲区（OS Buffer）

redo log buffer写入redo logfile实际上是先写入OS Buffer，然后通过系统调用fsync()将其刷到redo log file中

MySQL支持三种将redo log buffer写入redo log file的时机，可以通过innodb_flush_log_at_trx_commit参数配置

0（延迟写）

1（实时写，实时刷）

2（实时写，延迟刷）

redo log和binlog的区别

文件大小

redo log的大小固定

binlog可通过参数max_binlog_size设置每个binlog文件的大小

实现方式

redo log是InnoDB引擎层实现的，并不是所有引擎都有

binlog是Server层实现，所有引擎都可以使用binlog日志

记录方式

redo log采用循环写的方式，当写到结尾时，会回到开头循环写日志

binlog通过追加的方式记录，当文件大小大于设定值后，后续日志会记录到新的文件上

使用场景

redo log适用于崩溃恢复（crash-safe）

binlog适用于主从复制和数据同步

undo log

一条UPDATE的SQL执行流程

PostgreSQL

PG数据库优势

强大的性能与扩展性

高效的数据处理能力

支持大规模并发访问

优化查询性能

丰富的数据类型与函数

支持地理空间数据

提供全文搜索功能

提供向量查询功能

高度可扩展性

支持垂直与水平扩展

易于集成其他系统

高度可靠的数据安全性

严格的数据完整性控制

事务ACID特性

多版本并发控制

强大的加密与认证机制

SSL/TLS加密传输

支持多种认证方式

细致的权限管理

基于角色的访问控制

细粒度的权限分配

丰富的生态系统与兼容性

广泛的编程语言支持

Python、Java、C++等

丰富的第三方工具与插件

pgAdmin、pgBadger等

良好的跨平台兼容性

支持Windows、Linux、macOS等

架构支持高可用

主从复制与读写分离

读写分离策略

提升读性能

减轻主库压力

主从同步机制

实时数据复制

故障自动切换

负载均衡与分片

数据分片策略

水平分片

垂直分片

分片实现技术

数据分片策略实施

分片键选择与设计

唯一性约束与分片键冲突处理

分片键对查询性能的影响

分片规则与算法

范围分片算法实现

一致性哈希分片算法

数据分片后的数据管理

数据迁移与同步

增量数据同步机制

数据迁移过程中的一致性保障

跨分片查询与聚合

分片间数据路由与合并

跨分片事务处理

数据分片后的运维管理

分片节点监控与告警

节点性能监控指标

告警策略与通知机制

数据分片扩容与缩容

扩容策略与数据迁移

缩容策略与数据合并

负载均衡机制

动态调整负载

故障节点剔除

负载均衡实现技术

基于硬件的负载均衡

F5负载均衡器配置

虚拟服务器与池管理

健康检查与会话保持

Cisco ACE负载均衡

策略与规则配置

日志与监控

基于软件的负载均衡

Nginx反向代理与负载均衡

upstream模块配置

负载均衡算法选择

HAProxy高性能负载均衡

前端与后端配置

健康检查与会话粘性

数据库内置负载均衡

PostgreSQL流复制与负载均衡

流复制配置与监控

负载均衡策略实现

Oracle RAC负载均衡

节点管理与资源分配

负载均衡与故障转移

故障恢复与数据容错

自动故障检测与恢复

快速故障切换

数据一致性校验

数据备份与恢复

定期全量备份

实时增量备份

PostgreSQL特点

丰富的功能集合：包括SQL完整性约束、ACID事务支持、SQL子查询、视图、事务完整性控制、存储过程及参数化查询等。

数据类型支持：除常规的日期时间、文本、数字、字符串等数据类型外，还包括JSON, XML和其他自定义数据类型的支持，使得处理各种复杂的结构化和非结构化数据变得简单。

支持多种索引方式，包括普通的B-tree、位图、hash和GiST/SP-GiST等空间索引，以应对复杂查询。

内存优化型设计：使用行级共享存储模式，提高了内存使用效率，并支持自动内存和磁盘缓存的调整，进一步优化了查询性能。

社区活跃和技术成熟：PostgreSQL由庞大的社区维护，拥有强大的文档资源和持续的技术支持，使得解决疑难问题较为方便。

高可用和高性能架构

主从复制：通过设置多个数据库服务器，并在其中一主多从的配置下进行读写分离。主服务器负责所有的写入操作，其他服务器作为只读服务。

多数据库分片：将大表划分为多个小的表或者在多个服务器上分散数据，可以提高单点的存储和读写速度，并能水平扩展以增加系统处理能力和吞吐量。

效率

查询性能：PostgreSQL在大型事务的处理方面表现更好，尤其对于复杂查询和数据类型丰富的情况下有明显的性能优势。

并发支持：在特定场景下（比如大型金融交易或实时数据处理），PostgreSQL的并发优化和事务机制更加出色。

资源利用：PostgreSQL对内存的利用更为灵活，提供了更深层次的优化选项来调整内存使用策略。

优缺点

强大功能集合和优秀的稳定性；
更高程度的自定义灵活性，如用户定义的数据类型；
对大数据和复杂应用的优异支持。如高维向量数据；

学习曲线比MySQL陡峭，可能需要时间去理解其所有的特性；
在资源密集型任务中配置和管理可能更为复杂。

MSsql

Oracle

Sqlite

非关系型（键值）数据库

Redis

Redis的应用场景

缓存

会话缓存（Session Cache）

全页缓存（FPC）

队列

数据存储

排行榜/计数器

发布/订阅

Redis的安装

ubuntu command

sudo apt update

sudo apt isntall -y redis

sudo apt install redis-tool

sudo apt install redis-server

Redis的集群架构

主从架构

1 master - n salve

方案

从服务器连接主服务器，发送SYNC命令；
主服务器接收到SYNC命名后，开始执行BGSAVE命令生成RDB文件，并使用缓冲区记录此后执行的所有写命令；
主服务器BGSAVE执行完后，向所有从服务器发送快照文件，并在发送期间继续记录被执行的写命令；
从服务器收到快照文件后丢弃所有旧数据，载入收到的快照；
从服务器完成对快照的载入，开始接收命令请求，并执行来自主服务器缓冲区的写命令；（从服务器初始化完成）

优点

Master 主机会自动将数据同步到从机，可以进行读写分离

Master Server是以非阻塞的方式为Slaves提供服务。所以在Master-Slave同步期间，客户端仍然可以提交查询或修改请求。

Slave Server同样是以非阻塞的方式完成数据同步。在同步期间，如果有客户端提交查询请求，Redis则返回同步之前的数据

缺点

Redis不具备自动容错和恢复功能，主机从机的宕机都会导致前端部分读写请求失败，需要等待机器重启或者手动切换

主机宕机，宕机前有部分数据未能及时同步到从机，切换IP后还会引入数据不一致的问题

哨兵架构

1 master - n slave - n sentinel

方案

哨兵的作用就是监控Redis系统的运行状况。它的功能包括以下两个。
（1）监控主服务器和从服务器是否正常运行。
（2）主服务器出现故障时自动将从服务器转换为主服务器。

优点

哨兵模式是基于主从模式的，所有主从的优点，读写分离，非阻塞数据同步。

可以自动切换，系统更健壮，可用性更高。

缺点

水平扩容仍然比较困难，只能通过增加集群物理性能来垂直扩容

分片集群

(master - slave) * n

方案

Redis-Cluster采用无中心结构,它的特点如下：
(1) 所有的redis节点彼此互联(PING-PONG机制),内部使用二进制协议优化传输速度和带宽。
(2) 节点的fail是通过集群中超过半数的节点检测失效时才生效。

优点

无中心架构；数据按照key的 hash slot 存储分布在多个节点，节点间数据共享实现水平扩容

可扩展性：可线性扩展到 1000 多个节点，节点可动态添加或删除;

高可用性：部分节点不可用时，集群仍可用。

缺点

Key 事务操作支持有限，只支持多 key 在同一节点上的事务操作，当多个 Key 分布于不同的节点上时使用事务困难。

Key 作为数据分区的最小粒度，不能将一个很大的键值对象如 hash、list 等映射到不同的节点。

集群管理

CLUSTER NODES：显示集群中所有节点的信息。
CLUSTER INFO：显示集群的信息，如节点数量、槽位分配等。
CLUSTER KEYSLOT <key>：返回指定键被分配到的槽位。
CLUSTER SLOTS：显示集群中所有槽位的分配情况。
CLUSTER COUNTKEYSINSLOT <slot>：返回指定槽位中的键数量。
CLUSTER MEET <ip> <port>：将一个新节点添加到集群中。
CLUSTER REPLICATE <node_id>：将一个节点设置为另一个节点的从节点。
CLUSTER FAILOVER：手动触发故障转移，将主节点切换为从节点。
CLUSTER FORGET <node_id>：从集群中移除指定节点。
CLUSTER FLUSHSLOTS：清空集群中所有槽位的分配信息。

Redis数据类型

String

存储的值

可以是字符串、整数或浮点，统称为元素

读写能力

对字符串操作，对整数类型加减

String类型操作　[key|value(string/int/float)]

set

设置置顶key的值

set key value

get

获取指定key的值

get key

incr

将key中储存的数字值增一

incr key

decr key

将key中储存的数字值减一

decr key

incrby

key 所储存的值增加给定的减量值(decrement)

incrby key decrement

decrby

key 所储存的值减去给定的减量值(decrement)

decrby key decrement

append

如果 key 已经存在并且是一个字符串， APPEND 命令将 value 追加到 key 原来的值的末尾。

append key value

setnx

只有在 key 不存在时设置 key 的值

setnx key value

mget

获取所有(一个或多个)给定 key 的值。

mget key [key...]

mset

同时设置一个或多个 key-value 对

mget key value [key value ...]

getset

将给定 key 的值设为 value ，并返回 key 的旧值(old value)。

getset key value

setex

将值 value 关联到 key ，并将 key 的过期时间设为 seconds (以秒为单位)。

setex key seconds value

strlen

返回 key 所储存的字符串值的长度

strlen key

del

删除键

del key

List

存储的值

一个有序序列集合且每个节点都包好了一个元素

读写能力

序列两端推入、或弹出元素、修剪、查改或移除元素

List类型操作
[key => value1 | 自
value2 | 左
value3 | 而
value4 | 右]

push

lpush

将一个或多个值插入到列表头部

lpush key value1 [value2....]

rpush

将一个或多个值插入到列表尾部

rpush key value1 [value2....]

pop

lpop

移出并获取列表的第一个元素

lpop key

rpop

移出并获取列表的最后一个元素

rpop key

lrange

获取列表指定范围内的元素

lrange key start stop

llen

获取列表长度

llen key

lindex

通过索引获取列表中的元素

lindex key index

lrem

移除列表元素

lrem key count value

count > 0

从表头开始向表尾搜索，移除与 VALUE 相等的元素，数量为 COUNT

count < 0

从表尾开始向表头搜索，移除与 VALUE 相等的元素，数量为 COUNT 的绝对值

count = 0

移除表中所有与 VALUE 相等的值

lset key index value

通过索引设置列表元素的值

lset key index value

Set

存储的值

无序的方式，各不相同的元素

读写能力

从集合中插入或删除元素

Set类型操作
key->[ value1
value2
value3
value4]

sadd

向集合添加一个或多个成员（存在则返回0）

sdd key member1 [member2]

scard

获取集合的成员数

scard key

sinter

返回给定所有集合的交集

sinter key1 [key2]

sismember

判断 member 元素是否是集合 key 的成员

sismember key member

smembers

返回集合中的所有成员

smembers key

srandmember

返回集合中一个或多个随机数

srandmember key [count]

srem

移除集合中一个或多个成员

srem key member1 [member2]

Hash

存储的值

有key-valued的散列组，其中key是字符串，value是元素

读写能力

按照key进行增加删除

Hash类型操作
key-> key1 value(string/int/float)
key2 value(string/int/float)
key3 value(string/int/float)
key4 value(string/int/float)

hset

将哈希表 key 中的字段 field 的值设为 value

srem key field value

hmset

同时将多个 field-value (域-值)对设置到哈希表 key 中

hmset key field1 value1 [field2 value2]

hsetnx

只有在字段 field 不存在时，设置哈希表字段的值

hsetnx key field value

hget

获取存储在哈希表中指定字段的值

hget key field

hmget

获取所有给定字段的值

hget key field1 [field2]

hgetall

获取在哈希表中指定 key 的所有字段和值

hgetall key

hvals

获取哈希表中所有值

hvals key

hlen

获取哈希表中字段的数量

hlen key

hkeys

获取所有哈希表中的字段

hkeys key

hdel

删除一个或多个哈希表字段

hdel key field1 [field2]

hexitst

查看哈希表 key 中，指定的字段是否存在

hexitst key field

Sort Set

存储能力

带分数的score-value有序集合，其中score为浮点，value为元素

读写能力

集合插入，按照分数范围查找

Sorc Set类型操作
key-> score(10.1) value(string/int/float) rank:1
score(9.1) value(string/int/float) rank:0
score(11.2) value(string/int/float) rank:2

zadd

向有序集合添加一个或多个成员，或者更新已存在成员的分数

zadd key score1 member1 [score2 member2....]

zcard

获取有序集合的成员数

zcard key

zcount

计算在有序集合中指定区间分数的成员数

zcount key min max

zincrby

有序集合中对指定成员的分数加上增量 increment

zincrby key increment member

zrange

通过索引区间返回有序集合成指定区间内的成员

zrange key start stop [withscores]

zrank

返回有序集合中指定成员的索引

zrank key member

zrem

移除有序集合中的一个或多个成员

zrem key member1 [member2....]

zrevrange

返回有序集中指定区间内的成员，通过索引，分数从高到底

zrevrange key start stop [withscores]

zscore

返回有序集中，成员的分数值

zscore key member

Redis数据持久化

RDB

RDB是Redis用来进行持久化的一种方式，是把当前内存中的数据集快照写入磁盘，也就是 Snapshot 快照（数据库中所有键值对数据）。

自动触发

在 redis.conf 配置文件中的 SNAPSHOTTING 下

手动触发

执行save、bgsave命令

优势

1.RDB是一个非常紧凑(compact)的文件，它保存了redis 在某个时间点上的数据集。这种文件非常适合用于进行备份和灾难恢复。
2.生成RDB文件的时候，redis主进程会fork()一个子进程来处理所有保存工作，主进程不需要进行任何磁盘IO操作。
3.RDB 在恢复大数据集时的速度比 AOF 的恢复速度要快。

缺点

1、RDB方式数据没办法做到实时持久化/秒级持久化。因为bgsave每次运行都要执行fork操作创建子进程，属于重量级操作，如果不采用压缩算法(内存中的数据被克隆了一份，大致2倍的膨胀性需要考虑。
2、在一定间隔时间做一次备份，所以如果redis意外down掉的话，就会丢失最后一次快照后的所有修改(数据有丢失)

AOF

AOF 则是通过保存Redis服务器所执行的写命令来记录数据库状态。

触发配置

在 redis.conf 配置文件的 APPEND ONLY MODE 下

优势

①、AOF 持久化的方法提供了多种的同步频率，即使使用默认的同步频率每秒同步一次，Redis 最多也就丢失 1 秒的数据而已。
②、AOF 文件使用 Redis 命令追加的形式来构造，因此即使 Redis 只能向 AOF 文件写入命令的片断，使用 redis-check-aof 也很容易修正 AOF 文件。
③、AOF 文件的格式可读性较强，这也为使用者提供了更灵活的处理方式。例如，如果我们不小心错用了 FLUSHALL 命令，在重写还没进行时，我们可以手工将最后的 FLUSHALL 命令去掉，然后再使用 AOF 来恢复数据。

缺点

①、对于具有相同数据的的 Redis，AOF 文件通常会比 RDF 文件体积更大。
②、虽然 AOF 提供了多种同步的频率，默认情况每秒同步一次的频率也具有较高的性能。但在 Redis 的负载较高时 RDB 比 AOF 具好更好的性能保证。
③、RDB 使用快照的形式来持久化整个 Redis 数据，而 AOF 只是将每次执行的命令追加到 AOF 文件中，因此从理论上说，RDB 比 AOF 方式更健壮。官方文档也指出，AOF 的确也存在一些 BUG

Redis中的数据查询

单节点中查询

KEYS key_name

KEYS key_name_pattern*

SCAN cursor [MATCH pattern] [COUNT count]

SCAN 0 MATCH "user:*" COUNT 10

集群节点查询

CLUSTER INFO：显示集群的信息，如节点数量、槽位分配等。

CLUSTER NODES：显示集群中所有节点的信息。

CLUSTER KEYSLOT key_name 命令来获取某个key所在的槽位

CLUSTER KEYSLOT baidu

return int

CLUSTER GETKEYSINSLOT slot_num key_nums 命令获取该槽位上的所有key

CLUSTER GETKEYSINSLOT 150 10

return list

CLUSTER COUNTKEYSINSLOT slot 返回指定槽位中的键数量。

MemCache

非关系型（文档）数据库

Mongodb

Mongodb的应用场景

千万级读写

支持多层数据结构

易于扩容和存储

Mongodb的集群架构

主从架构

1 master - n salve

方案

Master-Slave 架构一般用于备份或者做读写分离，一般是一主一从设计和一主多从设计。

优点

Master 主机会自动将数据同步到从机，可以进行读写分离

缺点

Master-Slave 的角色是静态配置的，不能自动切换角色，必须人为指定；

Slave 节点只和 Master 通信，Slave 之间相互不感知

读写分离的结构只适合特定场景，对于必须需要数据强一致的场景是不合适这种读写分离的。

哨兵架构

1 master - n slave - n arbiter

方案

Replica Set 只有一个 Primary 节点，当 Primary 挂掉后，其他 Secondary 或者 Arbiter 节点会重新选举出来一个 Primary 节点提供服务。

优点

节点直接互有心跳，可以感知集群的整体状态。

可以自动切换，系统更健壮，可用性更高。

缺点

水平扩容仍然比较困难，只能通过增加集群物理性能来垂直扩容

分片集群

(master - slave) * n

方案

首先，要选一个字段（或者多个字段组合也可以）用来做 Key，这个 Key 可以是你任意指定的一个字段。我们现在就是要使用这个 Key 来，通过某种策略算出发往哪个 Shard 上。这个策略叫做：Sharding Strategy ，也就是分片策略。
我们把 Sharding Key 作为输入，按照特点的 Sharding Strategy 计算出一个值，值的集合形成了一个值域，我们按照固定步长去切分这个值域，每一个片叫做 Chunk ，每个 Chunk 出生的时候就和某个 Shard 绑定起来，这个绑定关系存储在配置中心里。

优点

无中心架构；数据按照key的 hash 或者range 存取实现水平扩容

可扩展性：可线性扩展到多个节点（master-slave）

高可用性：部分节点不可用时，集群仍可用。

高并发的查询，存储问题可以得到解决

其他思考

写多数成功，才算成功；

读使用 strong 模式，也就是只从主节点读；

Mongodb的安装

ubuntu command

Mongodb的连接

mongo --host <hostname> --port <port> -u <username> -p <password> --authenticationDatabase <authDB> <dbname>

mongo --host example.com --port 27017 -u admin -p password --authenticationDatabase admin

mongo --host 107.1.1.1 --port 27017 -u admin -p password --authenticationDatabase admin mydb

基本的查询语句

增

insertOne()插入单个文档

insertMany()同时插入多个文档

删

delete()

db.users.delete({}) // 删除集合中所有文档

db.users.delete({ name: "John" }, { justOne: true })

deleteOne()

// 删除满足条件的第一个文档
db.users.deleteOne({ name: "John" })

deleteMany()

// 删除满足条件的所有文档
db.users.deleteMany({ name: "John" })

改

db.collection.updateOne()

db.collection.updateOne(
{ name: "John Doe" },
{ $set: { age: 30 } }
);

db.users.updateOne(
{ name: "John Doe" },
{ $inc: { score: 10 } }
)

db.users.updateOne(
{ name: "John Doe" },
{ $push: { hobbies: "reading" } },
{$currentDate: { lastModified: true }}
)

db.users.updateOne(
  { name: "Jane Doe" },
  { $set: { age: 25, hobbies: ["painting", "cycling"] } },
  { upsert: true }
)

db.collection.updateMany()

db.collection.updateMany(
{ isActive: false },
{ $set: { isActive: true } }
);

db.lp_merge_business_progress_str_rewrite_result.updateMany(
{ "desc": { $regex: "在、" } }, // 查询条件，查找所有包含"在、"的desc字段
[{ $set: { "desc": { $replaceAll: { input: "$desc", find: "在、", replacement: "在" } } } }]
// 使用 $replaceAll 来替换字符串
);

db.collection.update()

db.collection.update(
  { isActive: false },
  { $set: { isActive: true } },
  { multi: true } // 设置 multi 选项为 true
);

查

查询单个文档：
db.collection.findOne({ name: "Alice" }); // 查找名字为Alice的文档

查询符合特定条件的文档：
db.collection.find({ age: { $gte: 18, $lte: 30 } }); // 查找年龄在18到30之间的文档

可以结合使用投影（projection）来只返回所需的字段，如：
db.collection.find({"name": "John", "age": { "$gt": 30 }}, { name: 1, email: 1 }); // 只返回name和email字段

# $in 查找名称包含 "apple", "banana", "orange" 中任意一个词语的产品
query = { "name": { "$in": ["apple", "banana", "orange"] } }
result = collection.find(query)

# $regex 查找以字母 "J" 开头的用户名
query = { "name": { "$regex": "^J" } }
db.collection.find(query)

# 关于时间的查询：

#关于计数：

排序

db.users.find().sort({
"age": 1, // 升序排序
"name": -1 // 降序排序
});

聚合、关联

单字段聚合

db.products.aggregate([
    {
        $group: {
            _id: "$category",  // 以 category 字段作为分组键
            product_count: { $sum: 1 },  // 统计每个分类的产品数量
            total_sales: { $sum: "$sales" }  // 计算每个分类的产品销售额之和}
    },
    {
        $sort: {total_sales: -1  // 按照总销售额降序排列}
    }
]);

date案例

多字段聚合(条件过滤后)

db.lp_merge_business_progress_str_rewrite_result.aggregate([
            {"$match": {"company": {"$in": ["喜茶"]}}},
            {"$group": {"_id": {"company": "$company",
"result": "$after_merge_business_result"},
"count_num": {"$sum": 1}}},
            {"$sort": {"count_num": -1, "_id.company": 1}}
        ])

关联查询

db.orders.aggregate([
  {
    $lookup: {
      from: "users", // 要联接的另一个集合名称
      localField: "userId", // orders集合中连接字段
      foreignField: "_id", // users集合中连接字段
      as: "user_info" // 查询结果中新加入的字段名
    }
  }
])

关联条件

db.ac.aggregate([
    {
        $lookup: {
            from: "bc", // 关联的集合名
            localField: "company", // a表中用于关联的字段
            foreignField: "company", // b表中用于关联的字段
            as: "relatedB" // 关联后的数据存放的新字段名
        }
    },
  {
    $match: {
      $or: [
        { "relatedB.business_planning_deduplication_str": { $eq: "" } },
        { "relatedB.business_planning_deduplication_str": { $exists: false } }
      ]  //$or空字符串或者字段不存在的情况
    }
  },
    {
        $project: {
            company: 1, // 只返回a表的company字段
            relatedB: 1
        }
    }
]).pretty();

常见问题

docker启动mongodb后怎么修改root密码

docker启动的mongodb，怎么修改root用户的密码？给出2种方案，对应操作步骤

方案一：

进入运行中的mongodb容器：docker exec -it <container_id> bash
连接mongodb数据库：mongo
切换到admin数据库：use admin
修改root用户密码：db.changeUserPassword("root", "new_password")

方案二：

停止mongodb容器：docker stop <container_id>
以交互模式启动mongodb容器，并设置新密码：docker run -it --rm --entrypoint bash <image_name> -c "mongod --fork --logpath /var/log/mongod.log && mongo admin --eval 'db.changeUserPassword("root", "new_password")'"
重新启动mongodb容器：docker start <container_id>

创建新用户admin

创建新用户并授予admin权限：
db.createUser(
{
user: "admin",
pwd: "password",
roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
}
)

db.grantRolesToUser("username", [ { role: "readWrite", db: "dzh" } ])

db.grantRolesToUser("username", [ { role: "readWriteAnyDatabase", db: "admin" } ])

搜索型数据库

ElasticSearch

es的场景

Elasticsearch（简称ES）是一个开源的分布式搜索和分析引擎，它提供了强大的全文搜索功能，可以快速地对大规模数据进行搜索、分析和可视化。

Elasticsearch中，查询是非常重要的操作，通过查询可以从索引中检索出符合条件的文档数据。

倒排索引

ES中存储数据的基本单位是 index 索引，相当于mysql里的一张表。

ES中数据以 docs 文档 的形式存储在index中，相当于一行数据。

每个文档docs包含多个字段，字段是文档的基本单位。

传统的我们的检索是通过文章，逐个遍历找到对应关键词的位置。
倒排索引 是通过分词策略，形成了词-文章的映射关系表，这种 词典+映射表 即为倒排索引。

有了倒排索引，就能实现 O(1) 时间复杂度的效率检索文章了，极大的提高了检索效率。

es接收到一个文档后，进行字符过滤->分词->归一化（停用词，大小写，单数和复数，相近词（相似词））

BKD树（K-D数和B+树）

如何实现master选举

ZenDiscover模块负责

对所有可以成为master的节点（node.master: true）根据nodeId字典排序，每次选举每个节点都把自己所知道的节点排一次序，然后选出第一个节点，暂且认为它是master节点。

如果对某个节点的投票数达到一定值并且该节点自己也选举自己，那这个节点就是master

es的分布式架构原理

1.核心思想就是在多个机器上启动多个es进程实例，组成一个集群。
2.创建一个索引，这个索引可以被分成多个shard（分片），每个shard存储一部分数据
3.shared分片也会有主分片primary副分片replica
4.shared主副分片均匀分布在各个机器上
5.数据都是写入到主分片，然后主分片同步写入到副分片上。读数据则可读取主分片或者副分片的数据。
6.如果某台机器进程宕机，master进程宕机，选举其他进程作为master，并且将宕机的进程里的主分片的副分片转为主分片。
7.宕机的进程好了以后，便不再是master 节点，里面的主分片parimary shard转为副分片 replica shard。

写数据过程

基本写入流程
1.首先客户端随便选择一个节点node去写，此时这个节点称为协调节点
2.协调节点对写的数据进行hash，确定这个数据属于哪个shard（分片）
3.协调节点对数据进行路由，把请求发到所属主分片 pimary shard 的node上去
4.主节点同步数据到从节点，primary, replicate sharding 都写完了，那么协调节点会返回写成功的响应给客户端。

primary shard存储底层原理
（refresh，flush，translog，merge）

1.数据写入shard的时候，先写入内存buffer里，同时它会写入到translog日志文件里。
（此时如果客户端要查询数据是查不到的）

2.如果buffer快满了或者每隔一段（默认1s）时间，es会把内存buffer中的数据 refresh刷到到一个新的segment file，每隔1秒产生一个新的segment文件
但是如果buffer里面此时没有数据，就不会执行refresh。
数据在写入segment file之后，便存储好了这1s的数据，同时就建立好倒排索引了。

3.操作系统中，磁盘文件其实都有一个东西，叫os cache，操作系统缓存。
就是说数据写入磁盘文件之前，会先进入os cache。
只要buffer里的数据写入到了os cache里面，客户端就能搜索到这部分数据了。

为什么es是准实时的？

因为写入1s后才会刷到os cache里。写入到os cache里之后，buffer里的数据就会清空，translog会保留。

translog也是磁盘文件，所以也是先写入os cache里的，默认5秒刷新数据到磁盘中

4.当translog不断变大，大到一定阈值，或者30分钟就会触发commit（flush）操作。
（默认30分钟会自动执行）整个commit过程叫flush，手动根据es api也可以执行flush。

commit操作： 1.写commit point 2.将os cache fsync强刷到磁盘上去 3.清空translog日志文件

1.将buffer里的数据都写入os cache里面去，然后清空buffer。
2.将一个commit point文件写入到磁盘，里面标示着之前写入的所有segment file，但是数据还是在os cache中。
3.把os cache缓冲的所有的数据都fsync到磁盘上面的每个segment file中去。
4.刷完以后会删除并新建translog

translog日志作用：
数据一般都是存储在buffer或者os cache内存里，一旦服务器宕机重启，内存中的数据就会丢失。
所以将es操作日志存储在translog里，es重启时通过translog将数据恢复到buffer及os cache中。

删除数据写入.del文件中标识一下这个数据被删除了，里面某个doc标识为deleted状态
客户端搜索某条数据，一旦发现这条数据在.del文件中找到这条数据被标识成删除状态了，就不会搜索出来。

在新的文档被创建时，Elasticsearch会为该文档指定一个版本号，当执行更新时，旧版本的文档在.del文件中被标记为删除，新版本的文档被索引到一个新段。旧版本的文档依然能匹配查询，但是会在结果中被过滤掉。

由于每隔1s生成一个segment file，当文件多到一定程度的时候，es会merge成一个大的segment file，然后删除旧的文件
在merge的时候，会看一下如果某条数据在.del文件中标识为删除，那么merge后的新文件里这条数据就没了（物理删除）

丢失数据情况

默认5s才会将 translog 从os cache写入到磁盘文件中，所以会有5s数据丢失的可能

解决：可以设置个参数，官方文档。每次写入一条数据，都是写入buffer，同时写入磁盘上的translog。
但是会导致写性能，写入吞吐量下降一个数量级。本来1s可以写入2000条，现在1s钟可能只能写200条。

读数据过程

查询流程原理：
客户端发送一个请求到任意一个node，node成为协调节点。
请求转发到对应相关的所有shards，此时会使用随机轮询算法，在primary shard及所有replica shard中实现请求负载均衡。
请求节点 query phase 每个 shard 将自己的搜索结果（其实就是一些 doc id）返回给协调节点（coordinate node）
协调节点 fetch phase 进行数据的合并，排序，分页等操作。根据doc id去各个节点上拉取实际的document数据，返回 document 给客户端。

一条数据精准查询

数据写入了某个document，这个document会自动给你分配一个全局唯一的id （doc id）
同时也是根据doc id进行hash路由到对应的primary shard上去的。也可以手动指定doc id，比如用户id，订单id。

Match Query：匹配查询

用于在指定字段中搜索包含指定关键词的文档。

{
"query": {
"match": {
"content": "Elasticsearch"
}
}
}

Match Phrase Query：短语匹配查询

用于匹配包含指定短语的文档。

{
"query": {
"match_phrase": {
"content": "Elasticsearch tutorial"
}
}
}

Term Query：精确匹配

用于精确匹配指定字段中的值。

{
"query": {
"term": {
"category": "Technology"
}
}
}

Range Query：范围查询

用于匹配指定字段中符合范围条件的值。

{
"query": {
"range": {
"price": {
"gte": 100,
"lte": 500
}
}
}
}

案例

Bool Query：布尔查询

用于组合多个查询条件，支持must、should、must_not等逻辑操作符。

{
"query": {
"bool": {
"must": [
{ "match": { "title": "Elasticsearch" } },
{ "term": { "category": "Technology" } }
]
}
}
}

es查询（url方式）

目标 Index

/_search 在所有索引上检索

/search1/_search 在search1当前索引上检索

/search1,search2/_search 在search1,search2索引上检索

/search*/_search 在search开头的索引上检索

/g*,user*/_search 在g和user开头的索引上检索

查询所有文档：

GET http://localhost:9200/_search

查询 指定索引 指定类型 的文档：

GET http://localhost:9200/index/type/_search

http://es_ip_address:9200/doc-202403/docs/_search

这个URL将返回doc-202403索引中所有docs文档类型的搜索结果。

泛查询 所有字段 值为"SZ000001"的文档：

http://es_ip_address:9200/doc-202403/docs/_search?q=SZ000001

这个URL将返回 doc-202403 索引中特定字段的值为"SZ000001"的文档的搜索结果。

查询多字段字段的值为"XXX"的文档：

http://es_ip_address:9200/doc-202403/docs/_search?q=field1:(SZ000001 and 2024-03-01)

这个URL将返回doc-202403索引中特定字段的值为"SZ000001" | "2022-01-01"的文档的搜索结果。

查询特定字段的值包含"SZ"的文档：

http://es_ip_address:9200/doc-202403/docs/_search?q=field1:*SZ

{ "query": { "bool": { "must": { "match": { "field1": "SZ" } } } } }

这个URL将返回doc-202403索引中特定字段的值包含"SZ"的文档的搜索结果。通配符"*"表示匹配任意字符。

查询后排序

&sort=pubdate:desc

召回最大数量

http://10.15.108.88:9200/doc-202404/docs/_search?q=category:(%E6%9C%8B%E5%8F%8B%E5%9C%88%20and%20%E5%85%AC%E5%8F%B8%E5%85%AC%E5%91%8A)&sort=pubdate:desc&size=100

es高维数据检索

高维信息（如文本嵌入、图像或其他结构化高维数据），Elasticsearch 通过结合：向量相似度搜索，分布式索引，缓存

向量相似度搜索：利用 L2 距离、余弦相似性等算法计算对象在向量空间中的距离，从而评估数据点的相似度。

分布式索引：数据和查询任务可以通过自动路由分配到多个节点，这不仅可以并行执行操作，还提高了容错能力。

二级存储索引：为优化高维向量的搜索速度和内存消耗，Esper 提供了使用预计算或近似数据结构（如树状聚类）的二级索引来加速查询的过程。

es调优

es生产集群的部署架构是什么？

es生产集群我们部署了5台机器，每台机器是6核64G的，集群总内存是320G

es集群的日增量数据大概3000万条，每天日增量数据大概是1G

每个索引的数据量大概有多少？分片情况？

目前线上有5个索引（结合业务来），每个索引的数据量大概是20G。我们每个索引分配的是8个shard，比默认的5个shard多了3个shard。

每个月份有1个索引对应大智慧APP。每个索引在测试中3个shard，在生成中8给shard。

十亿数据，第一次5～10s，第二次就快了

es性能优化是没有什么银弹的。不要期待随手调一个参数，就可以万能的应对所有性能慢的场景。
有些场景换个参数，或者调整个语法就能搞定，但是绝对不是所有场景都是这样的。

1.性能优化杀手锏 filesystem cache

第一次从磁盘查出数据会存到内存的fileSystem Cache，es搜索引擎严重依赖底层的os cache。

如果走磁盘一般肯定上秒，但是如果走filesystem cache，走纯内存，那么基本上就是毫秒级的。从几毫秒到几百毫秒不等。

1.如果要es性能好，最佳情况下，机器的内存要容纳你总数据量的一半。

比如es中要存储1T数据，那么你多台机器留给filesystem cache的内存要加起来综合到512g。

2.往es里存少量的数据，比如30个字段只用到了三个就存三个。让内存留给filesystem cache的大小跟数据量一致。性能就会非常高，一般可以在1s以内

3.其他字段的数据可以存在mysql里面，建议采用es+hbase
hbase的特点就是适用于海量数据的在线存储，就是可以对hbase写入海量数据，不要做复杂的搜索，就是做很简单的一些根据id或者范围查询的操作

总结：最好写入es数据小于 fileSystem cache内存大小

2.缓存预热

假如说，按照上面的方案去做了，es集群中每个机器写入的数据量还是超过了filesystem cache的一倍，60g数据，filesystem cache就30g，还有30g在磁盘中

可以自己后台搞个系统，每隔一会就去搜索一下热数据，刷到filesystem cache中。后面用户搜索热数据就是直接去内存里查了

3.冷热分离

1.将大量不搜索的字段，拆分到别的存储引擎里去，这个类似于mysql分库分表的垂直拆分。

2.可以做类似mysql水平拆分，就是说将大量的访问很少，频率很低的数据，单独写一个索引，然后将访问很频繁的热数据单独写一个索引。

比如：6台机器，2个索引，一个放冷数据，一个放热数据，每个索引3个shard
3台放热数据index；3台放冷数据index；
这样的话，大量的时候是在访问热数据，热数据可能就占总数据的10%，此时数据量很少，几乎能确保数据全部保留在filesystem cache
对于冷数据而言，是在别的index里面，跟热数据都不在同一个index机器上，如果有人访问冷数据，在磁盘上，此时性能差点就差点了。

子主题

4.document模型设计

es里的复杂的关联查询，复杂的查询语法，尽量别用，一旦用了性能一般都不太好。
所以要好好设计es里的数据模型。

写入es的java系统里，就完成关联，将关联好的数据直接写入es中，搜索的时候就不需要利用es的搜索语法

比如 mysql两个表需要join
在写入es的时候java直接将join好的数据写入es，不用es的join语法查询

5.分页性能优化

es分页性能比较坑
假设每页10条数据，现在要查询第100页，实际上是会把每个shard上存储前1000条数据都查到一个协调节点上，如果你有5个shard，那么就有5000条数据，接着协调节点对这5000条数据进行一些合并，处理。再获取到最终第100页的10条数据。
翻页的时候，翻的越深，每个shard返回的数据就越多，协调节点处理数据时间越长，非常坑爹。

1.不允许深度分页/默认深度分页性能很差。

系统不允许翻那么深的页，或者告诉产品默认翻的越深性能越差

2.类似于app里的推荐商品或者微博，不断下拉出现一页一页的。
可以用scroll api来进行处理
scroll会一次性给你生成所有数据的快照，每次翻页通过游标移动，获取下一页这样子，性能会比上面说的那种分页性能高很多。
无论分多少页，性能基本上都是毫秒级的。
因为scroll api 只能一页一页往后翻，不允许先第十页再120页。

Milvus

Milvus 是一款高性能的向量数据库系统，旨在为 AI 应用提供大规模数据存储、实时查询和近似查找服务。

Milvus 检索召回策略

多种距离算法：支持余弦相似性、欧式距离等多种向量间相似度计算，以满足各种应用需求。

内存优化索引：Milvus 支持如 IVF（Induction of Vector）和 Faiss 索引，这些索引能够在保证搜索精度的同时尽量节省内存资源。

Milves 效率

并行计算能力：利用本地内存和计算资源进行高效的向量化查询和聚合计算，通过并行处理加速搜索过程。

存储与检索加速: Milvus利用了分块、近似近邻（ANNS）、空间索引等技术以减少查询响应时间，对数百万级/千万级的数据规模

对比ES

Elasticsearch 和 Milvus 在设计上有不同侧重点。Elasticsearch 更侧重于文本搜索和多样化的数据索引

Milvus 是针对大型向量数据设计的，特别强调高维数据的存储和查询。

实践操作中考虑：性能要求、数据复杂性、集成的现有系统等，确保最佳的检索召回效果