首页 思维导图 详情

【数据库】 - MySQL, PG，MongoDB 关系数据库

2025-03-28 20:42:36 14 举报
AI智能生成
数据库，关系型数据库，半结构化数据库，检索数据库； 数据库实例分析 - mysql, mongodb, elasticsearch (es) 涵盖各类关系/非关系型数据库数据结构，数据排序，索引。
数据结构
数据库
ElasticSearch
MySQL
MongoDB
作者其他创作
大纲/内容
关系型数据库 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中的列（重点）
id
表示查询中执行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的权限
创建用户（自动拥有登录权限）
sql
CREATE USER username WITH PASSWORD 'your_password';
sql
CREATE ROLE username WITH LOGIN PASSWORD 'your_password';
删除用户
sql
DROP USER IF EXISTS username;
只读用户（仅查询特定表）
-- 创建用户
CREATE USER readonly_user WITH PASSWORD 'readonly123';
-- 授予数据库连接权限
GRANT CONNECT ON DATABASE testdb TO readonly_user;
-- 授予模式使用权限（如 public 模式）
GRANT USAGE ON SCHEMA public TO readonly_user;
-- 授予现有表的 SELECT 权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- 可选：自动授予未来表的 SELECT 权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;
读写用户（增删改查权限）
CREATE USER rw_user WITH PASSWORD 'rw123';
GRANT CONNECT ON DATABASE testdb TO rw_user;
GRANT USAGE, CREATE ON SCHEMA public TO rw_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rw_user;

-- 允许未来表的操作权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO rw_user;
模式管理员（管理特定模式）
CREATE USER schema_admin WITH PASSWORD 'admin123';
GRANT CONNECT ON DATABASE testdb TO schema_admin;
-- 授予模式所有权或完全控制权
GRANT USAGE, CREATE ON SCHEMA my_schema TO schema_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA my_schema TO schema_admin;

-- 允许修改未来对象
ALTER DEFAULT PRIVILEGES FOR ROLE schema_admin IN SCHEMA my_schema
GRANT ALL PRIVILEGES ON TABLES TO schema_admin;
超级用户（最高权限）
关键权限选项
SUPERUSER：超级用户权限。
CREATEDB：允许创建数据库。
CREATEROLE：允许创建和管理角色。
REPLICATION：复制权限。
INHERIT：自动继承所属角色的权限（默认启用）。
CREATE USER super_admin WITH PASSWORD 'super123'
SUPERUSER CREATEDB CREATEROLE REPLICATION;
效率
查询性能：PostgreSQL在大型事务的处理方面表现更好，尤其对于复杂查询和数据类型丰富的情况下有明显的性能优势。
并发支持：在特定场景下（比如大型金融交易或实时数据处理），PostgreSQL的并发优化和事务机制更加出色。
资源利用：PostgreSQL对内存的利用更为灵活，提供了更深层次的优化选项来调整内存使用策略。
优缺点
强大功能集合和优秀的稳定性；
更高程度的自定义灵活性，如用户定义的数据类型；
对大数据和复杂应用的优异支持。如高维向量数据；
学习曲线比MySQL陡峭，可能需要时间去理解其所有的特性；
在资源密集型任务中配置和管理可能更为复杂。
小技巧
navicat因为PG数据库的升级会出现不能使用的情况？
PG升级到15.x后，有些系统表的列名增加了，有些调整了一些字段。使得新版本出现兼容问题。
例如：
pg_database, pg_constrain
方案：
备份这个libcc.dll 文件；
进入网站 https://hexed.it/ 打开本地的libcc.dll 文件；
搜索关键词 SELECT DISTINCT datlastsysoid ；
找到之后，把 datlastsysoid 这几个字，改成 dattablespace ；
MSsql
Oracle
Sqlite
非关系型（键值）数据库
应用场景
缓存
会话缓存（Session Cache）
全页缓存（FPC）
队列
memcache 可以直接使用json对象 [ ] 去存储
redis更加有竞争力，可以使用list, set 来更好实现
数据存储
排行榜/计数器
发布/订阅
Redis
https://www.processon.com/view/6751753423bb5e5f8d18309f
MemCache
https://www.processon.com/view/6751753423bb5e5f8d18309f
非关系型（文档）数据库
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 Title" } },
        { "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文档类型的搜索结果。
docs 是类型（Type），但在 Elasticsearch 7.x 及以上版本中，类型的概念已经被废弃，默认只有一个类型 _doc
泛查询 所有字段 值为"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

这个URL将返回doc-202403索引中特定field字段的值为"SZ000001"的文档的搜索结果。
查询特定字段的值包含"SZ"的文档： 
http://es_ip_address:9200/doc-202403/docs/_search?q=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 应用提供大规模数据存储、实时查询和近似查找服务。
详细信息
https://www.processon.com/view/677b45913faeda177b462eda
存储问题
https://www.processon.com/view/67526732006d2a1ad40c0b40
数据结构
https://www.processon.com/view/67526732006d2a1ad40c0b40
排序问题
https://www.processon.com/view/67526732006d2a1ad40c0b40
检索问题
召回，精排
评论
0 条评论
下一页
为你推荐
查看更多
数据库基础
数据库基础
MySQL数据库
MySQL数据库
数据库(MySQL)
数据库(MySQL)
MySQL数据库项目式教程
MySQL数据库项目式教程
关系数据库
关系数据库
数据库系统与应用
数据库系统与应用
关系数据库
关系数据库
数据库设计
数据库设计
MySQL 数据库项目式教程
MySQL 数据库项目式教程
数据库总结
数据库总结