SQL性能优化
1.使用表连接JOIN 代替 子查询
2.避免在WHERE子句中使用函数
在where字句中使用函数可能会阻止索引发挥作用。
3.in中值太多
可以在sql中对数据用limit做限制。
不过我们更多的是要在业务代码中加限制，伪代码如下：
public List<Category> getCategory(List<Long> ids) {
if(CollectionUtils.isEmpty(ids)) {
return null;
}
if(ids.size() > 500) {
throw new BusinessException("一次最多允许查询500条记录")
}
return mapper.getCategoryList(ids);
}
还有一个方案就是：如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。
4.优化深度分页查询
查询偏移量过大的场景我们称为深度分页，这会导致查询性能较低，转javaguide
原因：当使用LIMIT和OFFSET进行分页时，随着页码的增加，查询性能会逐渐下降，因为数据库需要扫描越来越多的行来找到所需的起始点。
建议：使用基于索引的查询来优化分页，特别是当表很大时。例如，可以记录上一页最后一条记录的某个唯一标识符（如ID），并使用它作为下一页查询的起点。
例子：
优化前（随着页码增加性能下降）
SELECT * FROM user LIMIT 10 OFFSET 100000;优化后（使用上一页的最后一条记录的ID）
SELECT * FROM user WHERE id > LAST_SEEN_ID ORDER BY id LIMIT 10;5.不得使用外键与级联，一切外键概念必须在应用层解决。
说明：以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外
键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级
联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风
险；外键影响数据库的插入速度。 ——《阿里巴巴java开发手册》
6.建组合索引的时候，区分度最高的在最左边。
7.选用合适的字段类型
对于长度不固定的字段，选用
varchar比较好。对于长度较为固定的字段，应该选用
char。使用
VARCHAR时，存储的大小根据实际内容变动，这在处理较短字符串时能有效节省空间，但在性能上可能略逊色于CHAR，特别是在需要频繁进行字符串比较或连接操作时，因为CHAR是固定长度的，查找速度可能更快。
7.1 varchar
1. 可变长度
可变长：与固定长度的字符串类型（如
CHAR）不同，VARCHAR只占用实际存储的字符数加上额外的字节用于存储字符串的长度信息。这样可以节省存储空间，尤其是在存储长度不一的字符串时。例如，定义一个
VARCHAR(50)列，如果存储一个长度为 10 的字符串，只会占用 10 个字符的存储空间加上 1 或 2 字节来记录字符串的长度（具体取决于字符串最大长度）。
2. 长度限制
在定义
VARCHAR时，必须指定其最大长度，例如VARCHAR(n)，其中n是最大字符数。不同的数据库系统对n的限制可能有所不同。
表连接查询
参考链接
实战举例
数据表结构
假设数据表如下：
user 表:
dept 表:
内连接（INNER JOIN）
内连接只返回在两个表中都有匹配的行。
SQL 查询:
SELECT *
FROM user
INNER JOIN dept ON user.dept_id = dept.dept_id;查询结果:
左外连接（LEFT JOIN）
左外连接返回左表中的所有行，即使在右表中没有匹配的行。
SQL 查询:
SELECT *
FROM user
LEFT JOIN dept ON user.dept_id = dept.dept_id;查询结果:
右外连接（RIGHT JOIN）
右外连接返回右表中的所有行，即使在左表中没有匹配的行。
SQL 查询:
SELECT *
FROM user
RIGHT JOIN dept ON user.dept_id = dept.dept_id;查询结果:
全外连接（FULL JOIN）
全外连接返回两个表中的所有行。如果一行在一个表中没有匹配，在结果集中会有 NULL 值。
SQL 查询:
SELECT *
FROM user
FULL JOIN dept ON user.dept_id = dept.dept_id;查询结果:
总结
内连接（INNER JOIN）: 只返回在两个表中都有匹配的行。
左外连接（LEFT JOIN）: 返回左表的所有行，即使右表中没有匹配的行。
右外连接（RIGHT JOIN）: 返回右表的所有行，即使左表中没有匹配的行。
全外连接（FULL JOIN）: 返回两个表的所有行，如果一行在一个表中没有匹配，则包含
NULL值。
以上示例中，使用了 SELECT * 来选择所有列。查询结果中包含来自两个表的所有列。
sql语句操作json字符串数据：
参考链接
JSON_ARRAY_APPEND() 和 JSON_SET()
JSON_ARRAY_APPEND()用于在 JSON 数组中追加元素。JSON_SET()用于设置 JSON 文档中指定路径的值或创建新的键值对。
当然可以！JSON_ARRAY_APPEND() 和 JSON_SET() 是 MySQL 中处理 JSON 数据的两个函数。它们用于修改 JSON 格式的数据字段。下面是它们的详细解释和用法：
1. JSON_ARRAY_APPEND()
功能：JSON_ARRAY_APPEND() 函数用于向 JSON 数组中追加元素。如果 JSON 数据不是数组类型，该函数会自动将其转换为数组，并追加新元素。
语法：
JSON_ARRAY_APPEND(json_doc, path, val1[, val2, ...])json_doc：要操作的 JSON 文档。
path：指定要修改的 JSON 路径。
val1, val2, ...：要追加到数组中的值。可以追加多个值。
示例：
假设有一个表 example，其中有一列 data 存储 JSON 数据：
CREATE TABLE example (id INT PRIMARY KEY, data JSON);
INSERT INTO example (id, data) VALUES (1, '[]');要在 data 列的 JSON 数组中追加一个值 10：
UPDATE example
SET data = JSON_ARRAY_APPEND(data, '$', 10)
WHERE id = 1;执行后，data 列将变为 [10]。
2. JSON_SET()
功能：创建 or 更新
语法：
JSON_SET(json_doc, path, val[, path, val, ...])
UPDATE my_table
SET extra = JSON_SET(extra,
'$.name', 'Bob',
'$.age', 31,
'$.location', 'Los Angeles')
WHERE id = 1;
，json_doc：要操作的 JSON 文档。
path：指定要修改的 JSON 路径。
val：要设置的新值。
如果原本extra为空，可能无法插入成功，需要使用IFNULL进行处理：
UPDATE sys_dict
SET sys_dict.extra = json_set(
IFNULL(sys_dict.extra, '{}'), -- 如果 extra 为空，则使用 '{}' 作为空 JSON 对象
'$.template_code', sys_dict.remark->'$.template_code',
'$.print_type_classify_code', sys_dict.remark->'$.print_type_classify_code'
)
WHERE type_id = @print_type_dict_id;
示例：
假设表 example 中的 data 列存储 JSON 对象：
CREATE TABLE example (id INT PRIMARY KEY, data JSON);
INSERT INTO example (id, data) VALUES (1, '{}');要设置 data 列的 JSON 对象中的 name 键为 'John'，并且设置 age 键为 30：
UPDATE example
SET data = JSON_SET(data, '$.name', 'John', '$.age', 30)
WHERE id = 1;执行后，data 列将变为 {"name": "John", "age": 30}。
踩坑：
extra字段为null，是字符串null，转成JSONObject会是空，null。
sql语句直接操作json字符串里的对象：
(sd.extra -> '$.gridCode')
//sd为表名字，extra为json字符串字段名，'$.gridCode'为json内部某对象，as后为输出的java对象的字段名
(sd.extra -> '$.gridCode') as gridCode可以用在select、甚至模糊匹配中。
2.JSON_EXTRACT
在 MySQL 中，如果某个字段的类型是 JSON 类型，你可以使用 JSON_EXTRACT 函数来直接从 JSON 字段中提取特定属性的值。下面是一个示例 SQL 查询，演示如何从 JSON 字段中获取特定属性的值：
假设你有一个表名为 data，其中包含一个名为 json_data 的 JSON 类型字段，结构类似于 {"name": "Alice", "age": 30, "city": "New York"}。
示例 SQL 查询
SELECT JSON_EXTRACT(json_data, '$.name') AS name
FROM data;在上面的查询中，JSON_EXTRACT 函数用于从 json_data 字段中提取 name 属性的值。'$' 表示 JSON 根对象，$.name 指示要提取根对象下的 name 属性。
示例结果
假设数据如下：
执行上面的查询后，将会返回类似以下结果：
这样，你就可以直接从 JSON 字段中提取特定属性的值。你也可以结合其他 SQL 查询条件或函数来进一步筛选和处理 JSON 数据中的内容。
3.JSON_UNQUOTE
查出来的结果带着双引号，那么用这个，可以去掉双引号。例子：
SELECT JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.name')) AS name
FROM data;Sql转换类型
例如String类型转Long类型：
WHERE long_column = CAST(string_column AS BIGINT);数据库表不可缺少的五个列名
1.删除标识（delete_flag）
实际数据库运用中，对于数据的删除非常慎重，一般而言应该是以逻辑删除为主，而不是真正的物理删除。
对于驱动表，
delete_flag=1应该放在最后面的where之后，防止外联on条件筛选时失效每联一张表（非驱动表）都需要在紧跟着的
on后面添加该表的.delete_flag=0，而不能放在最后的where后（能生效但不规范）
2.添加记录的渠道（creator）
每一条数据添加进来应该都有对应的业务线或者渠道，这样才追踪数据来源的时候，排查问题的时候，有时候可以帮大忙
3.记录创建时间（create_time）
4.更新记录的渠道（updater）
5.更新时间（update_time）
最后还有个字段，也可以加上，就是remark，算是备注字段，万一字段不够了还是补上。
驱动表
在数据库查询优化中，驱动表（也称为主表或外层表）是指在连接（JOIN）操作中，首先被读取的表。它的选择对查询性能有着重要影响，尤其是在大数据量的场景下。
驱动表的选择
当你执行一个连接查询（比如 INNER JOIN、LEFT JOIN 或 RIGHT JOIN），数据库管理系统（DBMS）需要决定从哪个表开始读取数据，这个表就是驱动表。驱动表的选择通常基于以下几个因素：
表的数据量：通常，较小的数据集会被选择为驱动表，因为它可以减少扫描的行数。
索引的使用情况：如果表上有合适的索引，并且索引能够快速过滤出结果，这个表可能会被优先选择为驱动表。
查询条件的过滤性：如果查询条件能够极大地过滤驱动表中的数据，那么它更有可能被选择为驱动表。
统计信息和代价估算：数据库管理系统会根据表的统计信息来估算执行不同连接顺序的成本，并选择最低成本的方式进行查询。
驱动表与连接顺序
在连接操作中，驱动表的每一行数据都会与另一个表（通常称为被驱动表或从表）的数据进行匹配。因此，驱动表的选择直接影响到连接操作的性能。
INNER JOIN: DBMS 会选择一个表作为驱动表，然后对另一个表进行匹配操作。
LEFT JOIN: 左边的表通常是驱动表，因为这个连接类型要求左表的所有行都出现在结果集中。
RIGHT JOIN: 右边的表通常是驱动表，原理与
LEFT JOIN类似。
举例说明
假设你有两个表：employees 和 departments，它们的行数分别是 1000 和 10 行。你想查询每个部门的员工信息。
SELECT e.name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.id;在这个查询中，如果数据库选择 departments 作为驱动表，它只需扫描 10 行，然后对 employees 表进行匹配，这种情况下通常会更高效。如果反过来选择 employees 作为驱动表，那么会进行 1000 次匹配，效率较低。
数据库优化器的作用
大多数现代数据库系统都有一个优化器，它会自动选择驱动表并决定连接顺序，以最小化查询的执行时间。优化器会考虑上述因素，并根据统计信息和成本模型来决定最优的查询执行计划。
总结
驱动表的选择对 SQL 查询的性能优化至关重要。理解数据库如何选择驱动表，可以帮助你更好地编写高效的 SQL 查询，并进行手动优化。
sql语句的用户自定义变量：@xxx
在 SQL 中，变量名前的 @ 符号用于标识这是一个**用户定义的变量**。这些变量在 SQL 会话中用于存储数据，以便在后续的 SQL 语句中使用。以下是关于用户变量的更多细节：
用户变量的作用
存储数据: 用户变量可以用来存储查询结果、计算值或其他需要在后续 SQL 操作中引用的数据。
在同一会话中共享: 用户变量的作用范围是在创建它们的数据库会话内。一旦会话结束，变量会被销毁。
语法和使用
声明和赋值: 使用
SET语句来声明和赋值用户变量。例如：SET @my_variable = 10; SET @自定义变量名 = (SELECT 列名 FROM 表名 WHERE 条件);
使用变量: 变量可以在 SQL 查询中被引用。例如：
SELECT * FROM my_table WHERE column_name = @my_variable;这里
@my_variable用于作为查询的条件。
特点
前缀
**@**: 在 SQL 中，@符号用于区分用户定义的变量与其他标识符，如列名或表名。不需要声明类型: 与一些编程语言不同，SQL 不要求你在使用变量之前先声明其类型。变量的类型是动态的，根据赋给它的值来确定。
会话级别: 用户变量的作用范围是在创建它们的会话内。每个数据库会话可以有自己的用户变量，它们之间互不干扰。
示例
假设你有一个 SQL 查询需要在多个地方使用相同的值，你可以使用用户变量来存储这个值。举个例子：
-- 设置用户变量
SET @user_id = 42;
-- 使用变量查询
SELECT * FROM orders WHERE user_id = @user_id;
-- 使用变量进行更新
UPDATE users SET status = 'active' WHERE id = @user_id;在这个例子中，变量 @user_id 被赋值为 42，然后在后续的查询和更新操作中被使用。这种方法有助于避免在多个地方重复输入相同的值。
注意事项
确保唯一性: 在同一会话中，变量名必须唯一。如果使用了重复的变量名，后者会覆盖前者的值。
多行子查询: 如果你使用子查询来赋值，确保子查询返回的结果是一行（或单个值）。如果子查询返回多行，SQL 会抛出错误。例如：
-- 正确：单行单列 SET @variable = (SELECT value FROM table WHERE id = 1); -- 错误：多行返回 SET @variable = (SELECT value FROM table);
转义
踩坑记录：
写数据库插入就送数据，插入结果转义符号/
我有一个json对象，原来没有转义符号，使用JSON_ARRAY_APPEND 添加到一个json_array里面以后就出现了转移符号，这是为什么？
如果你在
JSON_ARRAY_APPEND中使用的 JSON 对象实际上是字符串形式（比如用单引号包裹的），MySQL 可能会将其视为普通字符串，而不是 JSON。
解决方案
确保 JSON 格式正确：
使用
JSON_OBJECT创建 JSON 对象，确保它的格式正确，而不是直接使用字符串。例如：
sql复制代码
UPDATE my_table
SET data = JSON_ARRAY_APPEND(data, '$', JSON_OBJECT('key1', 'value1', 'key2', 'value2'))
WHERE id = your_id;检查数据类型：
确保你要添加的 JSON 数据是
JSON类型，而不是VARCHAR或其他类型。你可以使用CAST来确保类型：
sql复制代码
UPDATE my_table
SET data = JSON_ARRAY_APPEND(data, '$', CAST('{"key1": "value1", "key2": "value2"}' AS JSON))
WHERE id = your_id;Navicat 查询json字符串的某个属性结果展示为空：
其实是查出来了，只是navicat的bug，他展示不出来
(sd.extra -> '$.gridCode') 取不出来里面的属性，结果为null
查询json字符串的某个属性，用(sd.extra -> '$.gridCode') 取不出来里面的属性，结果为null：
解决方法：
CONVERT((sd.extra -> '$.gridCode'), SIGNED)
->操作符获得的结果是没有解包的字符串
-> 操作符返回的是 JSON 类型的值，而不是普通的字符串。如果你希望获取可读的字符串结果，可以使用 ->> 操作符
使用->>解析json字符串中的某个属性，得到“null”
如果该属性为空，那么可能得到结果是字符串null,
可以使用以下sql直接获得null：
CASE
WHEN json_data->>'code' = 'null' THEN NULL
ELSE json_data->>'code'
END mysql数据库里存储的时间戳怎么转化为时间date
在 MySQL 数据库中，时间戳通常以 UNIX 时间戳的形式存储，这是一种表示自1970年1月1日（UTC）以来的秒数的方法。要将存储在 MySQL 数据库中的时间戳转换为日期格式（例如 DATETIME 或 DATE），您可以使用 MySQL 的内置函数。
以下是一些常见的方法来实现这个转换：
1. 使用 FROM_UNIXTIME 函数
如果您的时间戳是 UNIX 时间戳（以秒为单位），您可以使用 FROM_UNIXTIME() 函数将其转换为 DATETIME 格式。例如：
SELECT FROM_UNIXTIME(your_timestamp_column) AS converted_datetime
FROM your_table;如果您希望将结果格式化为特定的日期字符串，可以使用 DATE_FORMAT 函数。例如：
SELECT DATE_FORMAT(FROM_UNIXTIME(your_timestamp_column), '%Y-%m-%d %H:%i:%s') AS formatted_date
FROM your_table;2. 使用 DATE 函数
如果您只关心日期部分（不需要时间），可以使用 DATE() 函数：
SELECT DATE(FROM_UNIXTIME(your_timestamp_column)) AS converted_date
FROM your_table;这样可以得到 YYYY-MM-DD 格式的日期。
3. 时间戳存储为 DATETIME 类型
如果您的时间戳是以 DATETIME 类型存储（例如使用 CURRENT_TIMESTAMP），则不需要转换，您可以直接查询：
SELECT your_datetime_column
FROM your_table;4. 在 Java 中转换时间戳
如果您使用 Java 编程语言与 MySQL 交互，并想在 Java 中将时间戳转换为 Date 对象，可以使用如下方式：
import java.util.Date;
long timestamp = 1634232892; // 示例时间戳
Date date = new Date(timestamp * 1000); // 将秒转换为毫秒
System.out.println(date);5. 示例
假设您有一个名为 events 的表，其中有一个名为 event_timestamp 的列存储 UNIX 时间戳：
CREATE TABLE events (
id INT PRIMARY KEY,
event_name VARCHAR(50),
event_timestamp INT
);插入一些数据：
INSERT INTO events (id, event_name, event_timestamp) VALUES
(1, 'Event A', 1634232892),
(2, 'Event B', 1634236992);查询并转换时间戳：
SELECT
event_name,
FROM_UNIXTIME(event_timestamp) AS event_datetime,
DATE(FROM_UNIXTIME(event_timestamp)) AS event_date
FROM events;待看
https://www.cnblogs.com/hanease/p/15920509.html
对json字符串字段的某个属性建立索引：
是的，在 MySQL 中，可以对存储在 JSON 字段中的某些属性建立索引。这种功能从 MySQL 5.7 开始支持，通过以下几种方式实现：
1. 使用虚拟列 + 索引
MySQL 支持创建虚拟列，虚拟列可以从 JSON 字段中提取某个属性，然后为虚拟列建立索引。
如果 JSON 数据中的日期嵌套在对象中，可以通过 JSON 路径表达式 提取嵌套的属性值，并使用相同的逻辑为其建立虚拟列和索引。
示例场景
假设 data 字段存储如下 JSON 数据：
{
"user": {
"name": "Alice",
"info": {
"birth_date": "1990-05-15"
}
}
}需要从 data 中提取 birth_date 并将其转换为 DATE 类型，同时为其创建索引。
步骤
1. 创建表
CREATE TABLE example_table (
id INT PRIMARY KEY AUTO_INCREMENT,
data JSON
);2. 添加虚拟列
提取嵌套的 birth_date 属性并转换为 DATE 类型：
ALTER TABLE example_table
ADD COLUMN birth_date DATE GENERATED ALWAYS AS (
CAST(JSON_UNQUOTE(JSON_EXTRACT(data, '$.user.info.birth_date')) AS DATE)
) STORED;说明：
$.user.info.birth_date: JSON 路径，表示从user对象的info中提取birth_date属性。JSON_EXTRACT: 提取指定路径上的值。JSON_UNQUOTE: 去掉 JSON 提取值中的引号。CAST(... AS DATE): 将提取值转换为 MySQL 的DATE类型。STORED: 持久存储虚拟列值，支持索引。
3. 为虚拟列创建索引
CREATE INDEX idx_birth_date ON example_table (birth_date);验证索引
插入测试数据：
INSERT INTO example_table (data) VALUES
('{"user": {"name": "Alice", "info": {"birth_date": "1990-05-15"}}}'),
('{"user": {"name": "Bob", "info": {"birth_date": "1985-10-30"}}}'),
('{"user": {"name": "Charlie", "info": {"birth_date": "2000-01-01"}}}');查询数据：
EXPLAIN SELECT * FROM example_table WHERE birth_date = '1990-05-15';EXPLAIN 结果中的 key 字段应显示 idx_birth_date，表示查询使用了索引。
注意事项
JSON 路径语法
$表示 JSON 根节点。使用点号 (
.) 访问嵌套对象的属性，例如$.user.info.birth_date。
日期格式要求
确保 JSON 中的日期值格式符合 MySQL 的日期标准（如
YYYY-MM-DD或YYYY-MM-DD HH:MM:SS）。
NULL 值处理
如果某些 JSON 数据中缺少
birth_date属性，则虚拟列的值会是NULL，索引会忽略这些行。
通过这种方式，可以高效地提取嵌套 JSON 对象中的日期属性，并使用索引优化查询性能。
2. 直接使用全文索引（全文索引仅适用于 MySQL 5.7 或更高版本的 InnoDB 引擎）
如果目标字段是 JSON 文本，并且你需要对某些内容进行全文搜索，可以直接创建全文索引。
示例：
CREATE FULLTEXT INDEX idx_fulltext_data ON example_table (data);但注意，全文索引更适合于字符串搜索，而不适用于数值范围查询。
3. 原生 JSON 索引（MySQL 8.0 引入）
从 MySQL 8.0 开始，支持直接对 JSON 字段建立 函数索引。
示例：
-- 创建函数索引
CREATE INDEX idx_json_age ON example_table ((CAST(JSON_EXTRACT(data, '$.age') AS UNSIGNED)));这里：
使用
JSON_EXTRACT提取age。CAST用于将 JSON 中的数值转换为合适的类型（如UNSIGNED）。函数索引直接作用于 JSON 字段，无需创建虚拟列。
总结
MySQL 5.7：推荐使用虚拟列配合普通索引。
MySQL 8.0：优先使用函数索引，简化实现过程。
如果你已经在使用 MySQL 8.0 或更高版本，建议直接使用函数索引；否则，可以考虑虚拟列方案。
