sql

发表于 更新于 分类于 阅读次数：

sql…

数据库编码格式

数据库编码是指用于存储和检索数据库中数据的字符集。不同的编码使用不同的字节数来表示不同的字符，这会影响数据存储效率和排序规则。

常见的数据库编码包括：

  • ASCII：仅支持英文字母、数字和一些特殊符号，每个字符使用 1 个字节。
  • Latin1：是 ASCII 的扩展，支持更多欧洲语言的字符，每个字符使用 1 个字节。
  • UTF-8：是一种可变长度编码，支持所有 Unicode 字符，每个字符使用 1-4 个字节。
  • GBK：是中文国家标准编码，支持所有中文字符，每个字符使用 2 个字节。

MySQL 字符序命名规则是：以字符序对应的字符集名称开头，以国家名居中(或以 general 居中)，以 ci、cs、或 bin 结尾。以 ci 结尾的字符序表示大小写不敏感，以 cs 结尾的字符序表示大小写敏感，以 bin 结尾的字符序表示按二进制编码值比较。

utf8 是 Mysql 中的一种字符集，只支持最长三个字节的 UTF-8 字符，也就是 Unicode 中的基本多文本平面。
要在 Mysql 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持。
对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。

utf8mb4 已成为默认字符集，在 MySQL 8.0.1 及更高版本中将 utf8mb4_0900_ai_ci 作为默认排序规则。以前，utf8mb4_general_ci 是默认排序规则。由于 utf8mb4_0900_ai_ci 排序规则现在是默认排序规则，因此默认情况下新表格可以存储基本多语言平面之外的字符。现在可以默认存储表情符号。如果需要重音灵敏度和区分大小写，则可以使用 utf8mb4_0900_as_cs 代替

IndexedDB

IndexedDB 是类 NoSQL 类型数据库，可以说是没有结构的。通过预设索引，可以快速的根据索引值进行筛选查询；并且可以将任意 JavaScript 变量类型或对象直接存入数据库中，而不需要手动转换。一个数据库中可以包含多种对象集合，相对于 SQL 数据库来说就是多个表；在一个域（名）下，还可以有多个数据库。但是不能跨域访问别的域名之下的数据库。

幂等性

用户对于同一操作发起的一次请求或者多次请求的结果是一致的，不会因为多次点击而产生了副作用。
接口幂等性: 以相同的参数，对一个接口进行多次调用，所产生的结果和一次调用是完全相同的。
幂等性是分布式环境下常见的问题；幂等性指的是多次操作，结果是一致的。（多次操作数据库数据是一致的。）
常见的解决幂等性的方式有以下：

  1. 唯一索引；保证插入的数据只有一条；
  2. token 机制；每次接口请求前先获取一个 token，然后再下次请求的时候在请求的 header 体中加上这个 token，后台进行验证，如果验证通过删除 token，下次请求再次判断 token
  3. 悲观锁或者乐观锁，悲观锁可以保证每次 for update 的时候其他 sql 无法 update 数据(在数据库引擎是 innodb 的时候,select 的条件必须是唯一索引,防止锁全表)【分布锁思路】
  4. 先查询后判断，首先通过查询数据库是否存在数据，如果存在证明已经请求过了，直接拒绝该请求，如果没有存在，就证明是第一次进来，直接放行。

InnoDB 和 MyISAM

mysql5.5 之前的默认存储引擎是 MyISAM，而从 mysql 5.5 开始，默认的存储引擎是 InnoDB。

InnoDB 和 MyISAM 对比

  • 事务支持
    InnoDB 支持事务（ACID 属性），允许你回滚未提交的更改，并确保数据的一致性。
    MyISAM 不支持事务，因此一旦数据被插入或更新，就不能回滚。
  • 行级锁定与表级锁定
    InnoDB 使用行级锁定，这意味着在高并发情况下，可以同时对不同的行进行读写操作，从而提高效率。
    MyISAM 使用表级锁定，当一个查询执行时，整个表会被锁定，这可能会导致其他需要访问该表的操作等待。
  • 外键支持
    InnoDB 支持外键约束，可以帮助保持数据完整性。
    MyISAM 不支持外键。
  • 数据和索引存储
    InnoDB 将数据和索引存储在同一表空间中，这有助于减少磁盘 I/O 操作。
    MyISAM 分别存储数据文件（.MYD）和索引文件（.MYI），这可能在某些情况下提供更快的索引处理速度。
  • 故障恢复
    InnoDB 提供了更强大的故障恢复机制，通过使用重做日志（Redo Logs）和回滚日志（Undo Logs）来确保数据一致性。
    MyISAM 没有内置的故障恢复机制，如果遇到崩溃，可能需要使用操作系统级别的工具来恢复数据。
  • 性能
    InnoDB 通常更适合写密集型应用，因为它提供了更好的并发控制。
    MyISAM 在读密集型应用中表现更好，特别是在不需要事务支持的情况下。
  • 存储大小
    InnoDB 支持更大的表尺寸，因为它的设计考虑到了可扩展性。
    MyISAM 对于较小的数据集来说可能足够用，但对于非常大的数据量可能会遇到限制。

数据库索引

基本介绍

MySQL 官方对索引的定义为：索引（index）是帮助 MySQL 高效获取数据的一种数据结构，本质是排好序的快速查找数据结构。在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样

索引使用：一张数据表，用于保存数据；一个索引配置文件，用于保存索引；每个索引都指向了某一个数据

索引类型

  • 主键索引 alter table table_name add primary key(column);

  • 唯一键索引 alter table table_name add unique(column);

  • 普通索引 alter table table_name add index index_name(column);

  • 全文索引 alter table table_name add fulltext(column);

  • 多列索引 alter table table_name add index index_name(column1,column2,column3);
    什么时候需要创建索引

  • 主键自动建立唯一索引

  • 频繁作为查询条件的字段应该创建索引

  • 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找

  • 查询中统计或者分组的字段；

什么时候不需要创建索引

  • 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件

  • where 条件里用不到的字段，不创建索引；

  • 表记录太少，不需要创建索引；

  • 经常增删改的表；

  • 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

索引结构 B+树

向量数据库和矢量

矢量与向量意思相同,没有区别。
矢量(vector)是一种既有大小又有方向的量,又称为向量。
向量数据库专门用于存储、索引和查询嵌入向量，这些向量是通过机器学习模型传递非结构化数据而生成的。
Milvus 是一个开源矢量数据库，是该领域的领导者。Milvus 提供了许多演示，您可以使用它们来评估向量数据库的功能和用例。随着 Milvus 2.0 GA 的发布，Milvus 现在是一个云原生的容错系统，能够扩展到数十亿个向量甚至更多。

mysql5.7 和 8.0 的区别

mysql 高可用

  1. 集群部署

    Mysql Cluster 是官方的实现的高可用方案

  2. 主从复制
    能实现读写分离，配置好数据库后用
    sharding-jdbc-spring-boot-starter–>yml 配置主从数据库 shardingsphere–>allow-bean-definition-overriding=true

  3. 分库分表

sql 调优

事务

子查询

存储过程

redolog 和 binlog

表的设计规范(数据库三范式)

表的拆分

  1. 垂直分表

将一个表的字段分散到多个表中，每个表存储其中一部分字段

拆分规则:

  1. 不常用的字段单独一张表
  2. text，blob 类型的数据单独一张表
  3. 经常组合查询的字段单独一张表

sql 语句

  • limit 语法
1
2
3
4
-- #默认从第 0 行开始，取 10 条数据
select * from table limit 10;
-- #默认从第 10 行开始，取 20 条数据
select * from table limit 10,20;

其他

“Where 1=1” 是一种常用的 SQL 语句，它不会过滤任何结果。它通常用作动态 SQL 查询中的占位符，在这种情况下，可以根据某些条件添加其他过滤条件。

mysql 隐式转换

1
2
3
4
5
6
7
8
update db_name set selling_price = xx,sell_type = xx where id = 0;
# 字符串和数字的比较就按照浮点数规则进行比较。
# 也就是id会首先被转换成浮点数，然后再跟0进行比较。
# 1.如果字符串的第一个字符就是非数字的字符，那么转换结果就是0；
# 2.如果字符串以数字开头：
# (1)如果字符串都是数字，转换结果就是整个字符串对应的数字；
# (2)如果字符串中存在非数字，转换结果就是开头的那些数字对应的值；
update db_name set selling_price = xx,sell_type = xx where 0 = 0;