00.总结
覆盖索引：通过创建覆盖索引，可以显著提高查询性能
数据量控制：通过条件过滤和分表策略，减少查询的数据量
索引优化：根据查询特点选择合适的索引策略，包括普通索引、覆盖索引和强制索引
查询优化：结合业务场景，选择合适的查询优化策略，如小表驱动大表
01.数据生成
a.表结构
CREATE TABLE `orders` (
`order_id` int NOT NULL AUTO_INCREMENT,
`user_id` int DEFAULT NULL,
`order_date` date NOT NULL,
`total_amount` decimal(10,2) NOT NULL,
PRIMARY KEY (`order_id`),
KEY `idx_user_id` (`user_id`) USING BTREE,
KEY `idx_user_amount` (`user_id`,`total_amount`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-----------------------------------------------------------------------------------------------------
CREATE TABLE `users` (
`user_id` int NOT NULL AUTO_INCREMENT,
`username` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
`email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`),
KEY `idx_user_id` (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
b.数据生成存储过程
a.用户数据生成
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_users`()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE total_users INT DEFAULT 1000;
DECLARE rnd_username VARCHAR(50);
DECLARE rnd_email VARCHAR(100);
WHILE i < total_users DO
SET rnd_username = CONCAT('User', FLOOR(1 + RAND() * 10000000));
SET rnd_email = CONCAT(rnd_username, '@example.com');
INSERT INTO users (username, email) VALUES (rnd_username, rnd_email);
SET i = i + 1;
END WHILE;
END
b.订单数据生成
CREATE DEFINER=`root`@`localhost` PROCEDURE `generate_orders`()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE total_users INT DEFAULT 1000;
DECLARE total_orders_per_user INT DEFAULT 1000;
DECLARE rnd_user_id INT;
DECLARE rnd_order_date DATE;
DECLARE rnd_total_amount DECIMAL(10, 2);
DECLARE j INT DEFAULT 0;
WHILE i < total_users DO
SELECT user_id INTO rnd_user_id FROM users LIMIT i, 1;
WHILE j < total_orders_per_user DO
SET rnd_order_date = DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * 1096) DAY);
SET rnd_total_amount = ROUND(RAND() * 1000, 2);
INSERT INTO orders (user_id, order_date, total_amount) VALUES (rnd_user_id, rnd_order_date, rnd_total_amount);
SET j = j + 1;
END WHILE;
SET j = 0;
SET i = i + 1;
END WHILE;
END
02.查询优化
a.原始查询
a.代码
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN orders b ON a.user_id = b.user_id
GROUP BY a.user_id;
b.问题
在没有索引的情况下，查询需要全表扫描，耗时较长
b.优化策略
a.普通索引
为查询条件创建索引
-------------------------------------------------------------------------------------------------
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_total_amount ON orders (total_amount);
CREATE INDEX idx_users_user_id ON users (user_id);
-------------------------------------------------------------------------------------------------
结果：查询速度未显著提升，可能由于回表机制导致
b.覆盖索引
创建包含所有查询列的索引
-------------------------------------------------------------------------------------------------
CREATE INDEX idx_orders_user_id_total_amount ON orders (user_id, total_amount);
-------------------------------------------------------------------------------------------------
结果：查询时间显著降低，因为避免了回表操作
c.减少数据量
通过条件过滤减少查询的数据量
-------------------------------------------------------------------------------------------------
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN orders b ON a.user_id = b.user_id
WHERE a.user_id > 1033
GROUP BY a.user_id;
-------------------------------------------------------------------------------------------------
结果：进一步降低查询时间
d.小表驱动大表
尝试使用小表驱动大表的策略
-------------------------------------------------------------------------------------------------
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN (SELECT user_id, total_amount FROM orders WHERE user_id > 1033) b
ON a.user_id = b.user_id
GROUP BY a.user_id;
-------------------------------------------------------------------------------------------------
结果：在此场景中效果不明显，但在特定业务场景中可能有效
e.强制索引
在特定情况下使用强制索引
-------------------------------------------------------------------------------------------------
SELECT a.*, sum(b.total_amount) as total
FROM users a
LEFT JOIN orders b FORCE INDEX (idx_orders_user_id_total_amount)
ON a.user_id = b.user_id
WHERE b.user_id IN (1033, 1034, 1035, 1036, 1037, 1038)
GROUP BY a.user_id;
-------------------------------------------------------------------------------------------------
结果：在某些复杂查询中可能有效
03.总结
a.覆盖索引
通过创建覆盖索引，可以显著提高查询性能
b.数据量控制
通过条件过滤和分表策略，减少查询的数据量
c.索引优化
根据查询特点选择合适的索引策略，包括普通索引、覆盖索引和强制索引
d.查询优化
结合业务场景，选择合适的查询优化策略，如小表驱动大表
01.定义
覆盖索引（Covering Index）是指一个索引包含了查询所需的所有列的数据
因此查询可以直接从索引中获取结果，而不需要访问表中的实际数据行
这种索引可以显著提高查询性能，因为它减少了磁盘I/O操作
02.原理
a.说明
在MySQL中，当执行查询时，数据库通常需要访问表中的数据行来获取所需的列
然而，如果一个索引包含了查询所需的所有列，MySQL可以直接从索引中获取数据，而不需要访问表
这种情况称为覆盖索引
b.覆盖索引的优点
减少I/O操作：因为不需要访问表中的数据行，减少了磁盘I/O
提高查询速度：索引通常比表小，访问索引比访问表更快
03.常用API
在MySQL中，覆盖索引不需要特殊的API来创建
它是通过创建合适的复合索引来实现的
可以使用`CREATE INDEX`语句来创建包含查询所需列的索引
04.使用步骤
a.识别查询列
确定查询中需要的所有列
b.创建复合索引
使用`CREATE INDEX`语句创建包含所有查询列的索引
c.执行查询
MySQL会自动使用覆盖索引来优化查询
05.场景及代码示例
a.场景1：简单查询
a.说明
假设有一个表`employees`，包含以下列：`id`、`name`、`department`、`salary`
我们需要查询某个部门的员工姓名
b.代码
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(100),
salary DECIMAL(10, 2)
);
-- 创建覆盖索引
CREATE INDEX idx_department_name ON employees(department, name);
-- 查询使用覆盖索引
SELECT name FROM employees WHERE department = 'Sales';
---
c.说明
索引`idx_department_name`包含了查询所需的所有列（`department`和`name`）
因此查询可以直接从索引中获取数据
b.场景2：复杂查询
a.说明
假设我们需要查询某个部门的员工姓名和工资
b.代码
-- 创建覆盖索引
CREATE INDEX idx_department_name_salary ON employees(department, name, salary);
-- 查询使用覆盖索引
SELECT name, salary FROM employees WHERE department = 'Sales';
c.说明
索引`idx_department_name_salary`包含了查询所需的所有列（`department`、`name`和`salary`）
因此查询可以直接从索引中获取数据
05.注意事项
a.索引大小
覆盖索引可能会增加索引的大小，因此需要权衡索引大小和查询性能
b.更新开销
更多的索引可能会增加插入、更新和删除操作的开销
c.选择性
确保索引的选择性高，以便充分利用覆盖索引的优势
1.12 [3]深度分页
01.普通分页
a.优化1：通过增加主键排序
order by id
b.优化2：如果需要根据时间排序，就给常用的字段增加索引，包括时间字段
order by create_time
02.深度分页
a.定义
查询偏移量过大的分页
SELECT * FROM t_xxx ORDER BY id LIMIT 1000000, 20
b.优化方案
a.强制索引force index（不推荐）
不建议添加强制索引来进行sql优化，主要有以下几种缺点：
影响选择性最佳的索引：强制使用索引可能会影响数据库引擎选择性最佳的索引，导致查询性能下降
增加更新操作的时间：强制使用索引后，数据库更新操作的时间会增加，因为索引文件需要被更新
降低查询的灵活性：如果强制使用索引过于固定，会降低查询的灵活性，不方便后期维护
b.ID范围查询
如果那种不需要页码的场景下，比如滑动加载（消息列表这种）
还有那种只有上下页按钮点击的网站分页，我们可以通过where id > #{上次查询的最后一条记录的id} 进行优化
# 查询指定 ID 范围的数据
SELECT * FROM t_xxx WHERE id > 1000000 AND id <= 1000020 ORDER BY id
# 也可以通过记录上次查询结果的最后一条记录的ID进行下一页的查询
SELECT * FROM t_xxx WHERE id > 1000000 LIMIT 20
c.子查询+INNER JOIN
先根据时间字段（create_time）或者id排序查询到id
SELECT id FROM t_xxx ORDER BY create_time DESC LIMIT 1000000,20
这个子查询先查出来，作为临时表，然后再让主表join这个临时表去联表查询需要的t_xxx对应的信息字段
SELECT * FROM t_xxx INNER JOIN (SELECT id FROM t_xxx WHERE name = 'xxx' ORDER BY id LIMIT 1000000,20) AS t_temp ON t_xxx.id = t_temp.id
d.子查询+ID过滤
通过子查询+ID过滤优化的方式进行优化
SELECT * FROM t_xxx WHERE name = 'xxx' AND id >(SELECT id FROM t_xxx WHERE name = 'xxx' ORDER BY id LIMIT 1000000,1) ORDER BY id LIMIT 20
c.总结
a.产品方向
参考谷歌/百度搜索分页，每次只能跳转到当前页前后10页，也就是最多可以跳10页，要想达到深分页情况需要耐心
前端不支持跳页，只能通过上下页这种
b.技术方向
1.即使没有排序条件，也最好带上主键或者时间排序
2.对排序字段添加索引
3.通过子查询+INNER JOIN的方式
4.通过子查询+ID过滤的方式（这其实是3的变种，要求id是自增的）
5.每次查询记录上一次查询的id，然后基于这个id查询下一次（类似4）
6.调整mysql相关配置，比如sort_buffer_size大小（注意并不是越大越好，根据情况设置）
7.如果是基于内容搜索的话，可以使用Elasticsearch这种全文搜索引擎来进行优化
1.13 [3]索引、约束
00.索引、约束
索引：查询效率
约束：数据完整性
01.六种索引：查询效率
a.普通索引（Index）
最常用的索引类型，用于加速查询，没有特殊的约束
示例：CREATE INDEX idx_name ON users(name);
b.唯一索引（Unique Index）
确保索引列中的值是唯一的，类似于 UNIQUE 约束
示例：CREATE UNIQUE INDEX idx_email ON users(email);
c.主键索引（Primary Key Index）
主键自动创建一个唯一索引，值必须唯一且非空
示例：PRIMARY KEY (id)
d.复合索引（Composite Index 或 Multi-Column Index）
在多个列上创建的索引，用于优化多列条件的查询
示例：CREATE INDEX idx_name_age ON users(name, age);
e.全文索引（Full-Text Index）
用于全文搜索，支持 MATCH 和 AGAINST 操作。适合处理大文本数据（如文章或评论）
示例：CREATE FULLTEXT INDEX idx_content ON articles(content);
f.空间索引（Spatial Index）
用于存储和查询地理数据，适用于 GIS（地理信息系统）
必须基于 MyISAM 表或 InnoDB 的 SPATIAL 列类型
示例：CREATE SPATIAL INDEX idx_location ON geo_data(location);
02.六大约束：数据完整性
a.NOT NULL（非空约束）
保证字段值不能为 NULL，即要求字段必须有值
示例：name VARCHAR(50) NOT NULL
b.UNIQUE（唯一约束）
保证字段的值在该列中唯一，不能重复，但可以有 NULL 值（NULL 被认为是唯一的）
示例：email VARCHAR(100) UNIQUE
c.PRIMARY KEY（主键约束）
主键是表中的唯一标识，要求字段值唯一且不能为 NULL
一张表只能有一个主键。
示例：id INT PRIMARY KEY
d.FOREIGN KEY（外键约束）
用于建立表与表之间的关联关系，外键字段的值必须与主表对应字段的值匹配
示例：FOREIGN KEY (order_id) REFERENCES orders(id)
e.CHECK（检查约束）
检查字段值是否符合某些条件，MySQL 8.0 及以上版本支持
示例：age INT CHECK (age >= 18)
f.DEFAULT（默认值约束）
为字段设置一个默认值，如果插入数据时未指定值，则使用默认值
示例：status VARCHAR(20) DEFAULT 'active'
1.14 [3]索引、回表
00.索引、回表
a.回答
索引：加速查询
回表：为了获取索引中未包含的完整数据
通过辅助索引（二级索引）获取数据时，当索引中的字段无法覆盖选择列，才会出现所谓的“回表”现象
b.关系
关系：回表并不是因为通过索引找不到数据而触发的，而是因为索引中不包含查询所需的所有列数据时才需要进行的操作
01.定义
发生在非聚簇索引查询
当查询的列不完全包含在索引中时，MySQL需要通过索引找到主键值，然后回到表中获取完整的数据行，这一过程称为回表
在使用二级索引（非聚簇索引）作为条件进行查询时，由于二级索引中只存储了索引字段的值和对应的主键（聚簇索引）键值，因此需要根据主键中去聚簇索引查找实际的数据行，这个过程被称为回表
02.工作原理
1.索引查找：MySQL使用辅助索引查找满足条件的索引键值，辅助索引中存储的是索引列的值和对应的主键值
2.回表操作：找到索引键值后，MySQL需要通过主键值回到表中，获取完整的数据行，这是因为辅助索引中不包含所有列的数据
03.何时会发生回表
当查询的列不完全包含在索引中时，需要回表
使用覆盖索引（即查询的所有列都在索引中）可以避免回表
04.如何优化回表
使用覆盖索引：通过在查询中选择合适的索引列，确保查询的所有列都在索引中，从而避免回表
选择合适的索引：根据查询的实际需求，创建合适的索引以减少不必要的回表操作
05.代码示例
-- 查询需要回表，因为order_date不在索引中
SELECT order_date FROM orders WHERE customer_id = 123;
-- 使用覆盖索引避免回表
-- 假设创建了组合索引 (customer_id, order_date)
CREATE INDEX idx_customer_order ON orders (customer_id, order_date);
-- 现在查询可以通过覆盖索引避免回表
SELECT order_date FROM orders WHERE customer_id = 123;
1.15 [3]索引覆盖、索引下推
01.覆盖索引
a.定义
覆盖索引（Covering Index）指的是一个查询语句可以通过索引直接获取所需的数据，而无需访问数据表
这种情况下，索引包含了查询所需的所有列
b.优点
减少了回表操作，降低了 I/O 开销
提高了查询效率，因为所有数据都可以从索引中获取
c.使用条件
查询的列必须全部包含在索引中
查询条件必须符合索引的最左前缀匹配原则
d.示例
表 `covering_index_sample` 中有索引 `idx_key1_key2(key1, key2)`
查询 `SELECT key2 FROM covering_index_sample WHERE key1 = 'keytest';` 可以使用覆盖索引
查询 `SELECT key1 FROM covering_index_sample WHERE key2 = 'keytest';` 无法使用索引，因为不符合最左前缀匹配
查询 `SELECT key2, key3 FROM covering_index_sample WHERE key1 = 'keytest';` 无法使用覆盖索引，因为 `key3` 不在索引中
02.索引下推
a.定义
索引下推（Index Condition Pushdown）是 MySQL 5.6 引入的一种优化技术
允许在存储引擎层面进行更复杂的索引条件过滤，从而减少回表次数
b.优点
减少了回表次数，提高了查询效率
在有 `LIKE` 条件的查询中尤其有效
c.使用条件
联合索引中某个非前导列由于索引失效需要进行扫表时，可以使用索引下推优化
d.示例
表 `people` 中有索引 `(zipcode, lastname, firstname)`
查询 `SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';` 使用索引下推技术
联合索引 `(a, b)`，查询 `SELECT d FROM t2 WHERE a = "ni" AND b = 1;` 可以使用索引下推，因为 `b` 的类型不匹配导致索引失效，但索引下推可以减少回表次数
1.16 [4]groupby、orderby
00.汇总
操作 无索引时的开销 有索引优化策略 关键参数
GROUP BY 临时表+文件排序 松散/紧凑索引扫描，覆盖索引 tmp_table_size、sql_mode
ORDER BY 内存/磁盘排序 索引顺序扫描，延迟关联分页 sort_buffer_size、max_length_for_sort_data
联合使用 双重临时表+排序 索引履盖分组和排序字段 联合索引设计，查询重写
01.GROUP BY工作原理
a.概述
GROUP BY 用于按指定列对数据进行分组，通常结合聚合函数（如 SUM、COUNT、MAX 等）使用
b.执行流程
a.无索引时的处理
a.全表扫描
读取所有满足条件的行
b.构建临时表
将数据按 GROUP BY 列分组，存储分组键和中间聚合结果
c.排序与聚合
默认按分组字段隐式排序（类似 ORDER BY）
若未显式关闭排序（ORDER BY NULL），可能触发文件排序（Using filesort）
d.返回结果
遍历临时表输出分组后的数据
b.有索引时的优化
a.松散索引扫描（Loose Index Scan）
当 GROUP BY 列是索引的最左前缀且无范围查询时，直接跳过索引中的重复值，仅读取每组的第一行
示例：索引 (a, b)，查询 GROUP BY a
b.紧凑索引扫描（Tight Index Scan）
按索引顺序逐行读取数据，隐式利用索引有序性完成分组
示例：索引 (a, b)，查询 GROUP BY a, b
c.性能优化策略
a.索引设计
创建联合索引，确保 GROUP BY 列是索引的最左前缀
使用覆盖索引（包含所有查询字段）避免回表
示例：ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
b.避免隐式排序
SELECT category, COUNT(*) FROM products
GROUP BY category ORDER BY NULL; -- 禁用默认排序
c.监控临时表
SHOW STATUS LIKE 'Created_tmp%'; -- 查看临时表使用情况
02.ORDER BY工作原理
a.概述
ORDER BY 用于对结果集排序，其执行效率高度依赖索引和内存管理
b.执行流程
a.无索引时的处理
a.全表扫描
读取所有满足条件的行
b.排序缓冲区（sort_buffer）
若数据量小于 sort_buffer_size，在内存中排序（单路排序）
若数据量过大，使用磁盘临时文件进行多路归并排序（双路排序）
c.返回结果
按排序后的顺序输出数据
b.有索引时的优化
若排序字段与索引顺序一致，直接按索引顺序读取数据（避免排序）
支持正向或反向扫描索引（MySQL 8.0+ 支持降序索引）
c.性能优化策略
a.索引设计
排序字段与索引顺序一致（包括升降序）
示例：索引 (created_at DESC)，查询 ORDER BY created_at DESC
b.调整排序缓冲区
sort_buffer_size = 8M -- 增大内存缓冲区
max_length_for_sort_data = 8192 -- 控制单行数据长度，优先单路排序
c.避免深分页
-- 低效写法（扫描前100010行）
SELECT * FROM logs ORDER BY id LIMIT 100000, 10;
-- 高效写法（基于游标）
SELECT * FROM logs WHERE id > 100000 ORDER BY id LIMIT 10;
03.GROUP BY 与 ORDER BY 的联合使用
a.执行顺序
a.默认顺序
先执行 GROUP BY 分组，再对分组结果排序
b.优化思路
若 ORDER BY 字段与 GROUP BY 字段一致，可省略 ORDER BY（分组后默认有序）
若不一致，需确保索引覆盖分组和排序字段
b.优化示例
a.场景：统计每个用户的最新订单并按时间倒序输出
a.低效写法（临时表 + 排序）
SELECT user_id, MAX(created_at)
FROM orders
GROUP BY user_id
ORDER BY MAX(created_at) DESC;
b.优化方案（覆盖索引 + 延迟关联）
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
SELECT o.user_id, o.created_at
FROM orders o
INNER JOIN (
SELECT user_id, MAX(created_at) AS max_time
FROM orders
GROUP BY user_id
) AS tmp ON o.user_id = tmp.user_id AND o.created_at = tmp.max_time
ORDER BY o.created_at DESC;
00.不推荐子查询、JOIN
a.原因
a.性能问题
子查询在执行时，MySQL需要创建临时表来存储内层查询的结果，查询完毕后再删除这些临时表
这会增加CPU和IO资源的消耗，产生慢查询
JOIN操作本身效率也是硬伤，特别是当数据量很大时，性能难以保证
b.索引失效
子查询可能导致索引失效，因为MySQL会将查询强行转换为联接来执行
这使得子查询不能首先被执行，如果外表很大，性能上会出问题
c.查询优化器的复杂度
子查询会影响查询优化器的判断，导致不够优化的执行计划
相比之下，联表查询更容易被优化器理解和处理
d.数据传输开销
子查询可能导致大量不必要的数据传输，因为每个子查询都需要将结果返回给主查询
而联表查询则可以通过一次查询返回所需的所有数据，减少数据传输的开销
e.维护成本
使用JOIN写的SQL语句在修改表的schema时比较复杂，成本较大，尤其是在系统较大时，不易维护
b.解决
a.应用层关联
在业务层单表查询出数据后，作为条件给下一个单表查询，减少数据库层的负担
b.使用IN代替子查询
如果子查询结果集比较小，可以考虑使用“IN”操作符进行查询，这在数据量较小的情况下，查询效率更高
c.使用WHERE EXISTS
WHERE EXISTS是一种比“IN”更好的方案，它会检查子查询是否返回结果集，查询速度能够明显提高
d.改写为JOIN
使用JOIN查询来替代子查询，不需要建立临时表，速度更快，如果查询中使用索引，性能会更好
c.案例
01.查询所有有库存的商品信息
02.使用EXISTS优化子查询
03.使用JOIN代替子查询
04.优化子查询以减少数据量
05.使用索引覆盖
06.使用临时表优化复杂查询
07.使用窗口函数替代子查询
08.优化子查询以避免全表扫描
09.使用LIMIT子句限制子查询返回数据量
10.使用JOIN代替子查询以利用索引
01.查询所有有库存的商品信息
a.原始查询（使用子查询）
SELECT * FROM products WHERE id IN (SELECT product_id FROM inventory WHERE stock > 0);
b.优化方案（使用EXISTS）
SELECT * FROM products WHERE EXISTS (SELECT 1 FROM inventory WHERE inventory.product_id = products.id AND inventory.stock > 0);
c.说明
这个优化方案可以大幅提升查询速度，改善用户体验
02.使用EXISTS优化子查询
a.原始查询
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');
b.优化方案
SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM customers WHERE orders.customer_id = customers.customer_id AND customers.country = 'USA');
c.说明
使用EXISTS代替IN子查询可以减少回表查询的次数，提高查询效率
03.使用JOIN代替子查询
a.原始查询
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');
b.优化方案
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE c.country = 'USA';
c.说明
使用JOIN代替子查询可以减少子查询的开销，并且更容易利用索引
04.优化子查询以减少数据量
a.原始查询
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers);
b.优化方案
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE active = 1);
c.说明
限制子查询返回的数据量，减少主查询需要检查的行数，提高查询效率
05.使用索引覆盖
a.原始查询
SELECT customer_id FROM customers WHERE country = 'USA';
b.优化方案
CREATE INDEX idx_country ON customers(country);
SELECT customer_id FROM customers WHERE country = 'USA';
c.说明
为country字段创建索引，使得子查询可以直接在索引中找到数据，避免回表查询。
06.使用临时表优化复杂查询
a.原始查询
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE last_order_date > '2023-01-01');
b.优化方案
CREATE TEMPORARY TABLE temp_customers AS SELECT customer_id FROM customers WHERE last_order_date > '2023-01-01';
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM temp_customers);
c.说明
对于复杂的子查询，使用临时表存储中间结果，简化查询并提高性能。
07.使用窗口函数替代子查询
a.原始查询
SELECT employee_id, salary, (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS avg_salary FROM employees e;
b.优化方案
SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department_id) AS avg_salary FROM employees;
c.说明
使用窗口函数替代子查询，提高查询效率。
08.优化子查询以避免全表扫描
a.原始查询
SELECT * FROM users WHERE username IN (SELECT username FROM orders WHERE order_date = '2024-01-01');
b.优化方案
CREATE INDEX idx_order_date ON orders(order_date);
SELECT * FROM users WHERE username IN (SELECT username FROM orders WHERE order_date = '2024-01-01');
c.说明
为order_date字段创建索引，避免全表扫描，提高子查询效率。
09.使用LIMIT子句限制子查询返回数据量
a.原始查询
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA');
b.优化方案
SELECT * FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = 'USA' LIMIT 100);
c.说明
使用LIMIT子句限制子查询返回的数据量，减少主查询需要处理的数据量，提高查询效率。
10.使用JOIN代替子查询以利用索引
a.原始查询
SELECT * FROM transactions WHERE product_id IN (SELECT product_id FROM products WHERE category = 'Equity');
b.优化方案
SELECT t.* FROM transactions t JOIN products p ON t.product_id = p.product_id WHERE p.category = 'Equity';
c.说明
使用JOIN代替子查询，并且可以更容易地利用products表上的category索引
2.4 [1]失效：20个
00.汇总
01.违反最左前缀原则
02.索引例参与运算
03.隐式类型转换
04.OR连接非索引例
05.范围查询阻断索引
06.不等于(!= / <>)查询
07.LIKE左模糊匹配
08.索引选择性过低
09.ORDERBY排序方向混乱
10.使用NOTIN条件
11.多表JOIN字符集不一致
12.使用函数处理索引例
13.使用变量表达式
14.索引|列存在NULL值
15.分页深度过大
16.使用MATCH AGAINST全文索引
17.强制类型转换
18.统计信息不准确
19.使用派生表
20.索引合并效率低下
01.违反最左前缀原则
a.场景
联合索引(a,b,c)，但查询条件仅包含b和c
b.原理
B+树按a->b->c顺序构建，缺失最左列导致无法定位起始节点
c.案例
-- 失效
SELECT * FROM table WHERE b=2 AND c=3;
-- 有效
SELECT * FROM table WHERE a=1 AND b=2;
02.索引列参与运算
a.场景
WHERE a+1 = 5
b.原理
索引存储原始值，计算后的值无法匹配索引结构
c.案例
-- 失效
SELECT * FROM table WHERE YEAR(create_time) = 2023;
-- 有效
SELECT * FROM table WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
03.隐式类型转换
a.场景
字符串字段使用数字查询WHERE code = 123（code为VARCHAR）
b.原理
MySQL执行类型转换相当于调用CAST()函数
c.案例
-- 失效（code为VARCHAR）
SELECT * FROM table WHERE code = 123;
-- 有效
SELECT * FROM table WHERE code = '123';
04.OR连接非索引列
a.场景
WHERE a=1 OR b=2（仅a有索引）
b.原理
OR条件需要全表扫描验证非索引列
c.案例
-- 失效（假设b无索引）
SELECT * FROM table WHERE a=1 OR b=2;
-- 优化方案
SELECT * FROM table WHERE a=1
UNION
SELECT * FROM table WHERE b=2;
05.范围查询阻断索引
a.场景
联合索引(a,b,c)，条件a>1 AND b=2
b.原理
范围查询后，后续索引列无法保证有序性
c.案例
-- 仅使用a的索引
SELECT * FROM table WHERE a>1 AND b=2;
-- 有效使用索引
SELECT * FROM table WHERE a=1 AND b=2;
06.不等于(!= / <>)查询
a.场景
WHERE status != 1
b.原理
需要扫描大部分数据时，优化器放弃索引
c.例外
覆盖索引时可能仍使用索引
07.LIKE左模糊匹配
a.场景
WHERE name LIKE '%张'
b.原理
B+树无法反向匹配字符串后缀
c.优化
LIKE '张%'可使用索引
08.索引选择性过低
a.场景
性别字段索引（值分布集中）
b.原理
优化器认为全表扫描成本更低
c.判断标准
COUNT(DISTINCT col)/COUNT(*) < 0.2时慎用索引
09.ORDER BY排序方向混乱
a.场景
ORDER BY a ASC, b DESC（索引为a ASC, b ASC）
b.原理
排序方向不一致导致无法利用索引排序
10.使用NOT IN条件
a.场景
WHERE id NOT IN (1,2,3)
b.原理
等价于多个不等于条件，需全表验证
11.多表JOIN字符集不一致
a.场景
关联字段字符集不同（utf8 vs utf8mb4）
b.原理
字符集转换导致索引失效
12.使用函数处理索引列
a.场景
WHERE LOWER(name) = 'alice'
b.原理
函数处理后的值与索引存储值不匹配
13.使用变量表达式
a.场景
WHERE a = @var（变量类型与列类型不一致）
b.原理
隐式类型转换导致索引失效
14.索引列存在NULL值
a.场景
WHERE col IS NULL（允许NULL的列）
b.原理
NULL值在索引中特殊存储，可能不走索引
15.分页深度过大
a.场景
LIMIT 100000, 10
b.原理
优化器可能放弃使用索引进行偏移量计算
16.使用MATCH AGAINST全文索引
a.场景
混合使用全文索引和普通索引
b.原理
两种索引机制不兼容导致部分索引失效
17.强制类型转换
a.场景
WHERE CAST(id AS CHAR) = '123'
b.原理
显式类型转换破坏索引匹配
18.统计信息不准确
a.场景
数据分布变化后未执行ANALYZE TABLE
b.原理
优化器基于过时统计信息做出错误决策
19.使用派生表
a.场景
SELECT * FROM (SELECT * FROM t) WHERE id=1
b.原理
派生表可能破坏索引下推优化
20.索引合并效率低下
a.场景
同时使用多个单列索引
b.原理
索引合并的代价可能高于全表扫描
2.5 [1]优化：30个
00.汇总
01.慢查询
02.连接查询性能问题
03.子查询性能问题
04.过度使用通配符%的LIKE查询
05.大批量插入或更新
06.频繁的重复查询
07.过度使用分组和聚合函数
08.大量重复数据的查询
09.过度使用OR条件的查询
10.大型数据分页查询
11.使用不必要的列
12.频繁更新的表
13.未使用索引的外键约束
14.大型查询的分批处理
15.未使用存储过程的重复逻辑
16.未使用合适的数据类型
17.大量写操作导致的锁竞争
18.频繁使用数据库函数和表达式
19.未使用合适的索引策略
20.大量数据的联合操作
21.数据分布不均匀的索引
22.过度使用子查询
23.未使用批量操作
24.过度使用内存表
25.缺乏定期统计和优化
26.未使用合适的数据库引擎
27.使用强制类型转换
28.未优化的长事务
29.未优化的存储过程
30.未考虑DB服务器配置和硬件资源
01.慢查询
a.问题原因
通常是由于查询中涉及大量数据、缺乏索引或者SQL语句本身写得不够有效率所导致
b.优化策略
添加索引以加速检索
优化SQL查询语句，使用更有效率的方法
限制返回的行数
c.示例
-- 原始查询
SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-01';
-- 优化后的查询
CREATE INDEX idx_order_date ON orders (order_date);
SELECT COUNT(*) FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-01';
02.连接查询性能问题
a.问题原因
连接查询（特别是跨多个表的连接）可能会导致性能下降，尤其是在没有合适索引的情况下
b.优化策略
确保连接字段上存在索引
尽可能使用INNER JOIN替代其他类型的连接，因为INNER JOIN的性能通常更好
c.示例
-- 原始查询
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- 优化后的查询
CREATE INDEX idx_customer_id ON orders (customer_id);
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;
03.子查询性能问题
a.问题原因
子查询可能会执行多次，导致性能下降
b.优化策略
将子查询转换为连接查询或者联合查询
优化子查询本身，确保其尽可能高效
c.示例
-- 原始查询
SELECT product_id, product_name
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- 优化后的查询
SELECT p.product_id, p.product_name
FROM products p
JOIN (SELECT AVG(price) AS avg_price FROM products) AS avg_table
WHERE p.price > avg_table.avg_price;
04.过度使用通配符%的LIKE查询
a.问题原因
以%开头的 LIKE 查询会导致索引失效，因此性能较差
b.优化策略
尽可能避免在LIKE查询中使用%开头的通配符
如果不可避免，考虑全文搜索或者其他更适合的索引方法
c.示例
-- 原始查询
SELECT * FROM products WHERE product_name LIKE 'abc%';
-- 优化后的查询
SELECT * FROM products WHERE product_name >= 'abc' AND product_name < 'abd';
05.大批量插入或更新
a.问题原因
大量数据的插入或更新可能会导致性能下降，特别是在事务中进行时
b.优化策略
使用批量插入或更新操作，减少事务的开销
禁用或延迟索引更新，以减少插入或更新操作的负担
c.示例
-- 原始插入操作
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');
INSERT INTO orders (order_id, customer_id, order_date) VALUES (2, 1002, '2024-03-15');
-- 优化后的批量插入操作
INSERT INTO orders (order_id, customer_id, order_date) VALUES
(1, 1001, '2024-03-15'), (2, 1002, '2024-03-15'), ...;
06.频繁的重复查询
a.问题原因
重复执行相同的查询会造成资源浪费，尤其是在查询结果较大或复杂时。
b.优化策略
使用缓存技术，如数据库查询缓存或应用程序级别的缓存，避免重复查询相同的数据
考虑使用预编译语句或存储过程，以减少解析和编译的开销
c.示例
-- 原始查询
SELECT stock_quantity FROM products WHERE product_id = 123;
-- 优化后的查询（使用缓存）
SELECT stock_quantity FROM cached_stock WHERE product_id = 123;
07.过度使用分组和聚合函数
a.问题原因
对大数据集进行分组和聚合操作可能会导致性能下降，尤其是在没有适当索引或者内存不足的情况下
b.优化策略
确保使用合适的索引以加速分组和聚合操作
考虑使用汇总表（Summary Tables）或者其他预计算技术，避免在运行时进行大量聚合计算
c.示例
-- 原始查询
SELECT product_id, AVG(amount) AS avg_amount
FROM sales
GROUP BY product_id;
-- 优化后的查询（使用汇总表）
SELECT product_id, avg_amount
FROM product_sales_summary;
08.大量重复数据的查询
a.问题原因
当查询中存在大量重复的数据时，可能会导致不必要的资源消耗，降低查询效率
b.优化策略
使用 DISTINCT 关键字去除重复数据，但要注意使用得当，避免过多的内存消耗
考虑是否有更好的数据模型或者查询方式来避免重复数据的产生
c.示例
-- 原始查询
SELECT COUNT(customer_id) FROM orders;
-- 优化后的查询（使用DISTINCT）
SELECT COUNT(DISTINCT customer_id) FROM orders;
09.过度使用 OR 条件的查询
a.问题原因
当查询中存在多个OR条件时，数据库可能无法有效使用索引，导致全表扫描，降低性能
b.优化策略
将OR条件转换为使用IN或者UNION操作，以提高查询效率
确保在OR条件中的每个字段都有索引，以帮助数据库优化查询计划
c.示例
-- 原始查询
SELECT COUNT(*) FROM orders WHERE customer_id = 1001 OR product_id = 123;
-- 优化后的查询（使用UNION）
SELECT COUNT(*) FROM (
SELECT * FROM orders WHERE customer_id = 1001
UNION
SELECT * FROM orders WHERE product_id = 123
) AS combined_orders;
10.大型数据分页查询
a.问题原因
当需要获取大型数据集的分页结果时，传统的LIMIT OFFSET方法可能导致性能下降，特别是在偏移量较大时
b.优化策略
使用基于游标的分页（cursor-based pagination）来避免OFFSET的性能问题
考虑在应用层面或者缓存层面进行分页处理，减轻数据库的负担
c.示例
-- 原始分页查询
SELECT * FROM orders LIMIT 50 OFFSET 1000;
-- 优化后的查询（使用游标）
SELECT * FROM orders WHERE order_id > (SELECT order_id FROM orders ORDER BY order_id LIMIT 1 OFFSET 1000) LIMIT 50;
11.使用不必要的列
a.问题原因
当查询中包含不必要的列时，数据库引擎可能会浪费时间和资源来获取这些不需要的数据
b.优化策略
只选择查询中需要的列，避免选择不必要的列
如果存在大对象（如BLOB或CLOB），尽量避免在查询中选择这些对象，除非确实需要
c.示例
-- 原始查询
SELECT * FROM orders;
-- 优化后的查询（只选择需要的列）
SELECT order_id, order_date FROM orders;
12.频繁更新的表
a.问题原因
当表上有大量更新操作时，可能会导致表锁定和性能下降
b.优化策略
尽可能减少更新操作，考虑是否可以将数据操作转移到非热点表上
将更新操作批量处理，以减少事务开销
c.示例
-- 原始更新操作
UPDATE login_logs SET last_login = NOW() WHERE user_id = 123;
-- 优化后的更新操作（批量处理）
UPDATE login_logs
SET last_login = NOW()
WHERE user_id IN (123, 124, 125);
13.未使用索引的外键约束
a.问题原因
虽然外键约束可以确保数据完整性，但如果没有为外键字段创建索引，可能会导致性能下降
b.优化策略
确保为外键字段创建索引，以加速相关的数据操作
在数据库设计阶段考虑外键索引的需要性
c.示例
-- 创建外键约束
ALTER TABLE orders ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id);
-- 为外键字段创建索引
CREATE INDEX idx_customer_id ON orders (customer_id);
14.大型查询的分批处理
a.问题原因
当查询涉及大量数据时，一次性处理可能会导致内存和 CPU 资源过度消耗
b.优化策略
将大型查询分成多个较小的批次进行处理，以减少每个批次的负载
使用游标或者分页技术来处理大型数据集
c.示例
-- 原始查询
SELECT * FROM users;
-- 优化后的查询（使用游标）
DECLARE cursor_name CURSOR FOR SELECT * FROM users;
OPEN cursor_name;
FETCH NEXT FROM cursor_name;
-- 逐行处理数据...
CLOSE cursor_name;
15.未使用存储过程的重复逻辑
a.问题原因
当有重复的业务逻辑需要在多个地方执行时，未使用存储过程可能导致代码重复和维护困难
b.优化策略
将重复的逻辑封装到存储过程中，以便在需要时可以重复使用
使用存储过程可以减少网络通信开销，并且可以通过权限控制提高安全性
c.示例
-- 原始逻辑
SELECT SUM(total_amount) FROM orders WHERE customer_id = 123;
-- 优化后的存储过程
CREATE PROCEDURE CalculateOrderTotal(IN customer_id INT)
BEGIN
SELECT SUM(total_amount) FROM orders WHERE customer_id = customer_id;
END;
16.未使用合适的数据类型
a.问题原因
选择不恰当的数据类型可能会导致存储空间浪费和性能下降
b.优化策略
使用最合适的数据类型来存储数据，避免过大或过小的数据类型
尽量使用整型来存储整数数据，使用定点数或浮点数来存储小数数据
c.示例
-- 原始表定义
CREATE TABLE orders (order_id INT, quantity VARCHAR(10));
-- 优化后的表定义
CREATE TABLE orders (order_id INT, quantity INT);
17.大量写操作导致的锁竞争
a.问题原因
当有大量写操作时，可能会导致锁竞争，降低数据库的并发性能
b.优化策略
尽量减少长时间持有锁的操作，例如尽快提交或回滚事务
考虑分批处理写操作，以减少锁竞争的影响
c.示例
-- 原始下单操作
BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');
-- 更多写操作...
COMMIT;
-- 优化后的下单操作（分批处理）
BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1001, '2024-03-15');
COMMIT;
-- 分批处理更多下单操作...
18.频繁使用数据库函数和表达式
a.问题原因
频繁使用复杂的数据库函数和表达式可能会导致计算开销增加，影响查询性能
b.优化策略
尽量减少使用数据库函数和表达式，尽可能在应用层或缓存层进行计算
对于经常使用的表达式，考虑将其结果存储在列中，以减少重复计算
c.示例
-- 原始查询
SELECT SUM(total_amount * (1 + tax_rate)) FROM orders;
-- 优化后的查询（使用预计算结果）
ALTER TABLE orders ADD COLUMN total_with_tax DECIMAL(10, 2);
UPDATE orders SET total_with_tax = total_amount * (1 + tax_rate);
SELECT SUM(total_with_tax) FROM orders;
19.未使用合适的索引策略
a.问题原因
选择不当的索引策略可能会导致索引失效或不必要的索引开销
b.优化策略
定期检查和优化索引，确保其适应实际查询需求
根据查询模式选择合适的索引类型，例如B-Tree索引、哈希索引等
c.示例
-- 原始索引策略
CREATE INDEX idx_order_date ON orders (order_date);
-- 优化后的索引策略（创建覆盖索引）
CREATE INDEX idx_order_date_amount ON orders (order_date, total_amount);
20.大量数据的联合操作
a.问题原因
在执行大量数据的联合操作（UNION、UNION ALL）时，数据库可能需要对结果集进行排序和去重，导致性能下降
b.优化策略
尽量避免使用联合操作，如果必须使用，确保各个子查询的结果集尽量小且已经过滤，以减少排序和去重的开销
考虑使用其他方法代替联合操作，如JOIN、子查询等
c.示例
-- 原始查询
SELECT * FROM table1
UNION
SELECT * FROM table2;
-- 优化后的查询（使用JOIN）
SELECT DISTINCT t1.* FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id;
21.数据分布不均匀的索引
a.问题原因
如果索引数据分布不均匀，可能会导致部分索引页过度填充，而另一部分过度稀疏，影响查询性能
b.优化策略
定期重新组织索引或重新构建索引，以平衡数据分布
根据具体情况，考虑使用分区表或者其他分布均匀的数据结构
c.示例
-- 原始索引
CREATE INDEX idx_username ON users (username);
-- 优化后的索引（重新组织）
ALTER INDEX idx_username REORGANIZE;
22.过度使用子查询
a.问题原因
过度使用子查询可能会导致查询执行效率低下，尤其是嵌套子查询的情况下
b.优化策略
将子查询转换为JOIN操作，以减少查询的嵌套层次
尽量避免在SELECT子句中使用子查询
c.示例
-- 原始查询
SELECT user_id, (SELECT MAX(order_date) FROM orders WHERE orders.user_id = users.user_id) AS latest_order_date FROM users;
-- 优化后的查询（使用JOIN）
SELECT users.user_id, MAX(orders.order_date) AS latest_order_date
FROM users
LEFT JOIN orders ON users.user_id = orders.user_id
GROUP BY users.user_id;
23.未使用批量操作
a.问题原因
频繁执行单条数据操作可能会导致数据库连接开销增加，从而降低性能
b.优化策略
尽量使用批量操作来替代单条数据操作，以减少数据库连接开销
对于大批量数据的操作，可以考虑使用批量导入或批量更新的方式，减少交互次数
c.示例
-- 原始更新操作
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE orders SET status = 'shipped' WHERE order_id = 2;
-- 更多单条更新操作...
-- 优化后的批量更新操作
UPDATE orders SET status = 'shipped' WHERE order_id IN (1, 2, ...);
24.过度使用内存表
a.问题原因
虽然内存表在某些情况下可以提高查询速度，但过度使用内存表可能会导致内存消耗过大，甚至影响系统的稳定性
b.优化策略
评估内存表的使用情况，确保仅在适当的情况下使用内存表
对于大数据量或需要持久化存储的数据，避免使用内存表，而是选择合适的磁盘存储方式
c.示例
-- 原始查询（使用内存表进行排序）
SELECT * FROM orders ORDER BY order_date;
-- 优化后的查询（使用磁盘排序）
SELECT * FROM orders ORDER BY order_date;
25.缺乏定期统计和优化
a.问题原因
未定期统计表的数据分布和索引使用情况，未进行数据库性能优化可能导致查询性能逐渐下降
b.优化策略
定期执行统计分析，评估表的数据分布和索引使用情况
根据统计结果，定期优化数据库的索引、表结构或者查询语句
c.示例
-- 定期执行统计分析
ANALYZE TABLE orders;
-- 定期优化数据库索引
OPTIMIZE TABLE orders;
26.未使用合适的数据库引擎
a.问题原因
选择不合适的数据库引擎可能导致性能下降，无法充分发挥数据库的优势
b.优化策略
根据具体的业务需求和性能要求选择合适的数据库引擎，如InnoDB、MyISAM等
评估和比较不同数据库引擎的特性，选择最适合当前业务场景的引擎
c.示例
-- 使用InnoDB引擎存储事务性数据
CREATE TABLE orders (order_id INT, customer_id INT, order_date DATE) ENGINE=InnoDB;
-- 使用MyISAM引擎存储非事务性数据
CREATE TABLE logs (log_id INT, log_message TEXT) ENGINE=MyISAM;
27.使用强制类型转换
a.问题原因
频繁使用强制类型转换可能会导致查询执行效率低下，尤其是在大数据量的情况下
b.优化策略
尽量避免在查询中使用强制类型转换，而是尽量保持数据类型一致性
如果必须进行类型转换，尽可能在应用程序层面完成，而不是在数据库层面
c.示例
-- 原始查询（使用强制类型转换）
SELECT * FROM orders WHERE DATE(order_date) = '2024-03-15';
-- 优化后的查询（避免强制类型转换）
SELECT * FROM orders WHERE order_date = '2024-03-15';
28.未优化的长事务
a.问题原因
长时间运行的事务可能会阻塞其他事务，影响数据库的并发性能
b.优化策略
尽量减少事务的持续时间，尽早提交或者回滚事务
如果事务必须长时间运行，考虑是否可以使用行级锁而不是表级锁
c.示例
-- 原始事务
BEGIN TRANSACTION;
-- 处理订单数据...
COMMIT;
-- 优化后的事务（分批提交）
BEGIN TRANSACTION;
-- 处理部分订单数据...
COMMIT;
-- 继续处理剩余的订单数据...
29.未优化的存储过程
a.问题原因
存储过程中可能存在未优化的SQL语句，导致存储过程执行效率低下
b.优化策略
定期评估和优化存储过程中的SQL语句，确保其执行效率
使用合适的索引、避免不必要的循环或递归等方式优化存储过程
c.示例
-- 原始存储过程
CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)
BEGIN
DECLARE total DECIMAL(10, 2);
SELECT SUM(amount) INTO total FROM order_details WHERE order_id = order_id;
SELECT total;
END;
-- 优化后的存储过程（使用索引）
CREATE PROCEDURE CalculateOrderTotal(IN order_id INT)
BEGIN
DECLARE total DECIMAL(10, 2);
SELECT SUM(amount) INTO total FROM order_details WHERE order_id = order_id;
SELECT total;
END;
30.未考虑DB服务器配置和硬件资源
a.问题原因
数据库服务器配置不合理或硬件资源不足可能会导致数据库性能低下。
b.优化策略
根据数据库负载情况和性能需求合理配置数据库服务器的硬件资源，包括CPU、内存、磁盘等
定期监控数据库服务器的性能指标，进行性能调优和升级。
c.示例
-- 原始查询数据库服务器性能指标
SHOW STATUS;
-- 优化后的升级硬件资源
增加CPU核心数、内存容量或者使用更快的磁盘。
01.大小写
SQL不区分大小写
Linux区分大小写
02.MySQL语法顺序
SELECT
FROM
JOIN
ON
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
03.SQL执行顺序
(8) SELECT
(9) DISTINCT
(11) <Top Num> <select list>
(1) FROM [left_table]
(3) <join_type> JOIN <right_table>
(2) ON <join condition>
(4) WHERE <where condition>
(5) GROUP BY <group_by_list>
(6) WITH <CUBE | RollUP>
(7) HAVING <having_condition>
(10) ORDER BY <order by_list> ASC升序 | DESC降序
f o j w g w h s d o top
from on join where groupby with having select distinct orderby top
from 2张表生成一个笛卡尔积 生成“虚表1”
on 2张表进行一个关联匹配 生成“虚表2”
join 左/右关联，根据左/右表添加一个外部行 生成“虚表3”
where 行筛选 生成“虚表4”
groupby 列分组 生成“虚表5”
with 所选列中值的组合聚合 生成“虚表6”
having 符合分组条件进行筛选 生成“虚表7”
select 选择列 生成“虚表8”
distinct 删除重复数据行 生成“虚表9”
orderby 排序生成游标 生成“虚表10”
<Top 数字> 选择前几条 生成“虚表11”
<select list> 将执行list结果返回
2.9 [2]优化：优化汇总
00.总结优化
1.避免SELECT*,，只查询必要的字段
2.避免在SQL中进行函数计算等操作，使得无法命中索引
3.避免使用%LIKE
4.联合索引不满足最左匹配原则
5.无索引字段进行order by
6.连表查询但对应字段字符集不同
01.常见优化1
00.常见优化
临时表stat.*允许使用，速度加快
不使用||，使用CONCAT('你好', '世界')
前端筛选框，写到最小表的WHERE语句中，缩小筛选范围
前端筛选框，SELECT $radio/100.0 AS val, '$sDay' AS sDay, '$eDay' AS eDay FROM DUAL; 跟大表连接ON 1=1，速度会变慢
GROUP BY，尽可能分组字段变少，GROUP BY XL_ID, IMAXLONG_20 执行速度快，GROUP BY XL_ID, XL_NAME, IMAXLONG_20, VOLTAGE_TYPE 执行速度慢
WHERE 条件1 && 条件2 || 条件3，每个条件就是一个boolean，&&代表AND，||代表OR
01.常见优化1
where子句：【日期：BETWEEN走索引，>=<不走索引】、【in代替!=、!<>】、【exists代替in、not exists替代not in】、【INSTR替代like、not like】、【in代替or、union all代替or，between代替or】
NULL值：where中使用默认值代替null，查询时尽量不要对字段做空值判断
等号前：尽量避免在where条件中=前对字段做任何运算，避免在where中对字段进行表达式操作，避免在索引列上使用内置函数
等号后：where后面的字段，留意其数据类型的隐式转换。如NAME=110
where 1=1：当数据量大时，避免使用where 1=1的条件，默认使用该条件，数据库引擎会放弃索引进行全表扫描。
过滤数据多的条件往前放：调整Where字句中的连接顺序，最快速度缩小结果集
02.常见优化2
避免子查询（SELECT * FROM t1 WHERE id = (SELECT id FROM t2 WHERE name = 'chackca')），使用join代替
inner join 、left join、right join，优先使用inner join
避免使用HAVING字句，提高group by语句的效率
order by条件要与where中条件一致，否则order by不会利用索引进行排序
不建议UNION操作符，推荐UNION ALL操作符
去重distinct过滤字段要少
02.常见优化2
01.常见索引
建立索引：常用查询条件和连接条件的列上建立索引、利用覆盖索引、适当使用前缀索引、正确使用联合索引，避免过多的列使用复合索引、更新频繁的列慎用索引、避免范围查询数据量过多
关于临时表
表连接不宜太多，一般5个以内
索引不宜太多，一般5个以内
索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引
复合索引最左特性，where条件仅包含复合索引非前置列
复合索引最左特性
SQL语句没有匹配到索引时使用表锁、行级锁增删改查匹配到索引时使用行锁
02.常见建表
尽量使用数值替代字符串类型
使用varchar代替char
尽量把所有列定义为NOT
批量插入性能提升
清空表时优先使用truncate
操作delete或者update语句，加个limit或者循环分批次删除
03.多表优化
多表关联查询时，小表在前，大表在后
先2、3个小表组建成1个表，再将这个表distinct去重，最后将这个表与大表连接
在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。例如：表1有50条数据，表2有30亿条数据
使用表的别名：当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。
04.SQL务必要写完整，不要使用缩写法
select user_name "姓名" from zz_users; --为字段取别名的简单写法
select user_name as "姓名" from zz_users; --为字段取别名的完整写法
select * from 表1,表2... where 表1.字段 = 表2.字段 ...; --内连表查询的简单写法
select * from 表1 别名1 inner join 表2 别名2 on 别名1.字段 = 别名2.字段; --内连表查询的完整写法
因为隐式的这种写法，在MySQL底层都需要做一次转换，将其转换为完整的写法，因此简写的SQL会比完整的SQL多一步转化过程，如果你考虑极致程度的优化，也切记将SQL写成完整的语法。
2.10 [2]优化：优化手段1
01.避免在where子句中使用【日期：BETWEEN走索引，>=<不走索引】、【!=、!<>】、【exists代替in、not exists替代not in】、【INSTR替代like、not like】、【in代替or、union all代替or，between代替or】
日期 Hutools的DateUtil.parse()，代替Java的sdf
日期 SQL尽可能使用RQ时，BETWEEN走索引，>=<不走索引
日期 SQL尽可能存本来类型，不使用CAST转换，Java去做类型转换
日期 DATE_FORMAT是函数，TO_CHAR、TO_DATE、TO_TIMESTAMP涉及到类型转换
日期 SQL存在隐式转换，有3种精确度为日、分、秒；使用MONTH(SYSDATE)、YEAR(SYSDATE)函数控制精度
---------------------------------------------------------------------------------------------------------
反例 SELECT * FROM user WHERE salary!=5000
反例 SELECT * FROM user WHERE salary<>5000
理由 使用!=和<>很可能会让索引失效
应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
实现业务优先，实在没办法，就只能使用，并不是不能使用
---------------------------------------------------------------------------------------------------------
示例 SELECT * FROM t WHERE id IN (2,3)
优化 如果是连续数值，可以用between代替。如下：SELECT * FROM t WHERE id BETWEEN 2 AND 3
优化 如果是子查询，可以用exists代替。如下：
select * from A where A.id in (select id from B); --不走索引
select * from A where exists (select * from B where B.id = A.id); --走索引
---------------------------------------------------------------------------------------------------------
反例 select * from citys where name like '%大连' (不使用索引)
select * from citys where name like '%大连%' (不使用索引)
正例 select * from citys where name like '大连%' (使用索引)
正例 NAME LIKE '%A%' 等于 INSTR('A',NAME)
正例 NAME NOT LIKE '%A%' 等于 INSTR('A',NAME)=0
理由 首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询， 即like ‘…%’，是会使用索引的；
左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成 like ‘…%’；
全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。
---------------------------------------------------------------------------------------------------------
反例 SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30;
反例 SELECT * FROM t WHERE id IN (10,20,30);
理由 另外，MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：
select id from table_name where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。
反例 SELECT * FROM user WHERE id=1 OR salary=5000
正例 使用union all
SELECT * FROM user WHERE id=1
UNION ALL
SELECT * FROM user WHERE salary=5000
正例 分开两条sql写
SELECT * FROM user WHERE id=1
SELECT * FROM user WHERE salary=5000
理由 使用or可能会使索引失效，从而全表扫描；
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描；
也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；
虽然mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引还是可能失效的；
02.NULL值：where中使用默认值代替null，查询时尽量不要对字段做空值判断
反例 SELECT * FROM user WHERE age IS NOT NULL
优化 可以给字段添加默认值0，对0值进行判断
正例 SELECT * FROM user WHERE age>0
理由 并不是说使用了is null或者 is not null就会不走索引了，这个跟mysql版本以及查询成本都有关；
如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效；
其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；
如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；
---------------------------------------------------------------------------------------------------------
反例 select * from xxx where yyy is null;
反例 select * from xxx where yyy not is null;
理由 当出现基于字段做空值判断的情况时，会导致索引失效，因为判断null的情况不会走索引，
因此切记要避免这样的情况，一般在设计字段结构的时候，请使用not null来定义字段，
同时如果想为空的字段，可以设计一个0、""这类空字符代替，一方面要查询空值时可通过查询空字符的方式走索引检索，
同时也能避免MyBatis注入对象属性时触发空指针异常。
03.等号前：尽量避免在where条件中=前对字段做任何运算，避免在where中对字段进行表达式操作，避免在索引列上使用内置函数
反例 SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();
正例 SELECT * FROM user WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);
理由 SQL解析时，如果字段相关的是表达式就进行全表扫描
使用索引列上内置函数，索引失效
字段干净无表达式，索引生效
注意在SELECT SUBSTR(NAME, 0, 2) 使用内置函数，然后再SELECT该子表，NAME索引仍会失效
---------------------------------------------------------------------------------------------------------
可以将表达式、函数操作移动到等号右侧。如下：
SELECT * FROM T WHERE score/10 = 9 --全表扫描
SELECT * FROM T WHERE score = 10*9` --走索引
---------------------------------------------------------------------------------------------------------
select * from zz_users where user_id * 2 = 8;
select * from zz_users where trim(user_name) = "熊猫";
zz_users用户表中user_id、user_name字段上都创建了索引，但上述这类情况都不会走索引，因为MySQL优化器在生成执行计划时，
发现这些=前面涉及到了逻辑运算，因此就不会继续往下走了，会将具体的运算工作留到执行时完成，也正是由于优化器没有继续往下走，
因此不会为运算完成后的字段选择索引，最终导致索引失效走全表查询。
04.等号后：where后面的字段，留意其数据类型的隐式转换。
使用索引 SELECT * FROM user WHERE NAME='110'
未使用索引 SELECT * FROM user WHERE NAME=110
使用索引 SELECT * FROM user WHERE NAME IN ('110','111','112')
未使用索引 SELECT * FROM user WHERE NAME IN (110,111,112)
因为不加单引号时，是字符串跟数字的比较，它们类型不匹配
MySQL会做隐式的类型转换，把它们转换为数值类型再做比较
---------------------------------------------------------------------------------------------------------
CREATE TABLE test (
id INT NOT NULL PRIMARY KEY,
var VARCHAR(50) NOT NULL
num INT NOT NULL,
INDEX `idx var`(`var`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
UPDATE test SET num = 10 WHERE var = 1; 将左侧val从'1'变成1，索引失效，行锁变为表锁
UPDATE test SET num = 10 WHERE id = '1'； 将id为int类型，将右侧'1'变成1，索引正常
---------------------------------------------------------------------------------------------------------
如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。
select col1 from table where col_varchar=123;
05.where 1=1：当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：
SELECT username, age, sex FROM T WHERE 1=1
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。
06.过滤数据多的条件往前放：调整Where字句中的连接顺序，最快速度缩小结果集
MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。
2.11 [2]优化：优化手段2
01.尽量避免使用子查询
SELECT * FROM t1 WHERE id = (SELECT id FROM t2 WHERE name = 'chackca');
其子查询在Mysql5.5版本里，内部执行计划是这样：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。
在MariaDB10/Mysql5.6版本里，采用join关联方式对其进行了优化，这条SQL语句会自动转换为：
SELECT t1.* FROM t1 JOIN t2 on t1.id = t2.id
但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询
由于MySQL的优化器对于子查询的处理能力比较弱，所以不建议使用子查询，可以改写成Inner Join，
之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表
02.inner join 、left join、right join，优先使用inner join
三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。
inner join 内连接，只保留两张表中完全匹配的结果集；
left join会返回左表所有的行，即使在右表中没有匹配的记录；
right join会返回右表所有的行，即使在左表中没有匹配的记录；
如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；
使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；
这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；
03.避免使用HAVING字句，提高group by语句的效率
反例 先分组，再过滤 select job, avg（salary） from employee group by job having job ='develop' or job = 'test';
正例 先过滤，后分组 select job，avg（salary） from employee where job ='develop' or job = 'test' group by job;
理由 可以在执行到该语句前，把不需要的记录过滤掉
理由 避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。
理由 where和having的区别：where后面不能使用组函数
04.order by条件要与where中条件一致，否则order by不会利用索引进行排序
SELECT * FROM ALL_INDEXES WHERE 1=1 AND TABLE_NAME='DSS_JZZT_MX'; 查看索引
ALTER TABLE "OMS_DATACENTER"."DSS_JZZT_MX" ADD CONSTRAINT "CONS134307167" PRIMARY KEY("JZID", "RQ") 增加索引
SELECT JZID, STATE, RQ FROM OMS_DATACENTER.DSS_JZZ_MX WHERE 1=1 AND JZID IN ('1', '2', '3') AND RQ BETWEEN '2024-04-18 00:00:00' AND '2024-04-18 23:59:59' ORDER BY JZID, RQ; 1=1全表检索
SELECT JZID, STATE, RQ FROM OMS_DATACENTER.DSS_JZZ_MX WHERE RQ BETWEEN '2024-04-18 00:00:00' AND '2024-04-18 23:59:59' ORDER BY JZID, RQ; WHERE没有JZID，违背复合索引最左特性 5m
SELECT JZID, STATE, RQ FROM OMS_DATACENTER.DSS_JZZ_MX WHERE JZID IN ('1', '2', '3', ..., '189') AND RQ BETWEEN '2024-04-18 00:00:00' AND '2024-04-18 23:59:59' ORDER BY JZID, RQ; WHERE使用JZID IN，符合复合索引最左特性 23s 937ms
SELECT JZID, STATE, RQ FROM OMS_DATACENTER.DSS_JZZ_MX WHERE JZID BETWEEN '11011400' AND '11011900' AND RQ BETWEEN '2024-04-18 00:00:00' AND '2024-04-18 23:59:59' ORDER BY JZID, RQ; WHERE使用JZID BETWEEN，符合复合索引最左特性，但VARCHAR比较数字，出现隐形转换 3m 16s 501ms
---------------------------------------------------------------------------------------------------------
SELECT * FROM t order by age; --不走age索引
SELECT * FROM t where age > 0 order by age; --走age索引
对于上面的语句，数据库的处理顺序是：
第一步：根据where条件和统计信息生成执行计划，得到数据。
第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
第三步：返回排序后的数据。
当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。
这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。
05.不建议UNION操作符，推荐UNION ALL操作符
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：
select username,tel from user
union
select departmentname from department
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，
如果表数据量大的话可能会导致用磁盘进行排序。推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
06.去重distinct过滤字段要少
带distinct的语句占用cpu时间高于不带distinct的语句
当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据
然而这个比较、过滤的过程会占用系统资源，如cpu时间
2.12 [2]优化：创建原则
00.索引创建原则
a.选择合适的列
最适合索引的列是出现在WHERE子句中的列，或连接子句中的列，而不是出现在SELECT关键字后的列
b.基数大
索引列的基数（即不同值的数量）越大，索引效果越好，因为高基数意味着更好的选择性
c.复合索引
根据情况创建复合索引，复合索引可以提高查询效率，尤其是在多个条件一起使用时
d.避免过多索引
创建过多的索引会额外占用磁盘空间，并降低写操作效率
e.主键选择
主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用，提高查询效率
f.字符串索引
对字符串进行索引时，应该定制一个前缀长度，可以节省大量的索引空间
01.建立索引
常用查询条件和连接条件的列上建立索引
利用覆盖索引
适当使用前缀索引
正确使用联合索引，避免过多的列使用复合索引
更新频繁的列慎用索引
避免范围查询数据量过多
02.关于临时表
避免频繁创建和删除临时表，以减少系统表资源的消耗；
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log；
如果数据量不大，为了缓和系统表的资源，应先create table，然后insert；
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除。先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定；
03.表连接不宜太多，一般5个以内
关联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高
如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
阿里规范中，建议多表联查三张表以下
04.索引不宜太多，一般5个以内
索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；
索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；
索引表的数据是排序的，排序也是要花时间的；
insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；
一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；
05.索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引
06.复合索引最左特性，where条件仅包含复合索引非前置列
复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。
select col1 from table where key_part2=1 and key_part3=2
07.复合索引最左特性
创建复合索引 ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)
满足复合索引的最左特性，哪怕只是部分，复合索引生效 SELECT * FROM employee WHERE NAME='哪吒编程'
没有出现左边的字段，则不满足最左特性，索引失效 SELECT * FROM employee WHERE salary=5000
复合索引全使用，按左侧顺序出现 name,salary，索引生效 SELECT * FROM employee WHERE NAME='哪吒编程' AND salary=5000
虽然违背了最左特性，但MySQL执行SQL时会进行优化，底层进行颠倒优化 SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒编程'
复合索引也称为联合索引，当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。
联合索引不满足最左原则，索引一般会失效。
01.示例1
WITH stat AS (
--基础表
SELECT XL_ID,XL_NAME,IMAXLONG_20,VOLTAGE_TYPE,V00,V01,V02,V03,V04,V05,V06,V07,V08,V09,V10,V11,V12,V13,V14,V15,V16,V17,V18,V19,V20,V21,V22,V23,V24,V25,V26,V27,V28,V29,V30,V31,V32,V33,V34,V35,V36,V37,V38,V39,V40,V41,V42,V43,V44,V45,V46,V47,V48,V49,V50,V51,V52,V53,V54,V55,V56,V57,V58,V59
FROM OMS_DATACENTER.SG_DEV_ACLINE_H1_MEA_2024
WHERE MEAS_TYPE='00002401'
AND CREATE_TIME BETWEEN '2024-04-09' AND '2024-04-09 23:00:00'
), nj AS (
--一天中的最大电流
SELECT line.ID, ROUND(MAX(pow.max_value),2) AS ZDDL
FROM SG_DATACENTER.SG_DEV_ACLINE_B line
INNER JOIN SG_DATACENTER.SG_DEV_ACLINE_H_STA_POW_2024 pow ON pow.ID = line.ID AND pow.MEAS_TYPE = '00002401'
WHERE pow.CREATE_DATE BETWEEN '2024-04-09' AND '2024-04-09 23:00:00'
GROUP BY line.ID
), lj AS (
--地区信息
SELECT line.ID, power.NAME AS POWER_NAME
FROM SG_DATACENTER.SG_DEV_ACLINE_B line
INNER JOIN SG_DATACENTER.SG_CON_PWRGRID_B power ON power.ID = line.GRID_ID
), sj AS (
--重载时长
SELECT XL_ID,V00+V01+V02+V03+V04+V05+V06+V07+V08+V09+V10+V11+V12+V13+V14+V15+V16+V17+V18+V19+V20+V21+V22+V23+V24+V25+V26+V27+V28+V29+V30+V31+V32+V33+V34+V35+V36+V37+V38+V39+V40+V41+V42+V43+V44+V45+V46+V47+V48+V49+V50+V51+V52+V53+V54+V55+V56+V57+V58+V59 AS ZZSC FROM (
SELECT XL_ID
,SUM(CASE WHEN ABS(V00)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V00,SUM(CASE WHEN ABS(V01)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V01,SUM(CASE WHEN ABS(V02)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V02,SUM(CASE WHEN ABS(V03)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V03,SUM(CASE WHEN ABS(V04)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V04,SUM(CASE WHEN ABS(V05)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V05
,SUM(CASE WHEN ABS(V06)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V06,SUM(CASE WHEN ABS(V07)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V07,SUM(CASE WHEN ABS(V08)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V08,SUM(CASE WHEN ABS(V09)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V09,SUM(CASE WHEN ABS(V10)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V10,SUM(CASE WHEN ABS(V11)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V11
,SUM(CASE WHEN ABS(V12)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V12,SUM(CASE WHEN ABS(V13)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V13,SUM(CASE WHEN ABS(V14)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V14,SUM(CASE WHEN ABS(V15)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V15,SUM(CASE WHEN ABS(V16)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V16,SUM(CASE WHEN ABS(V17)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V17
,SUM(CASE WHEN ABS(V18)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V18,SUM(CASE WHEN ABS(V19)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V19,SUM(CASE WHEN ABS(V20)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V20,SUM(CASE WHEN ABS(V21)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V21,SUM(CASE WHEN ABS(V22)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V22,SUM(CASE WHEN ABS(V23)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V23
,SUM(CASE WHEN ABS(V24)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V24,SUM(CASE WHEN ABS(V25)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V25,SUM(CASE WHEN ABS(V26)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V26,SUM(CASE WHEN ABS(V27)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V27,SUM(CASE WHEN ABS(V28)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V28,SUM(CASE WHEN ABS(V29)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V29
,SUM(CASE WHEN ABS(V30)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V30,SUM(CASE WHEN ABS(V31)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V31,SUM(CASE WHEN ABS(V32)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V32,SUM(CASE WHEN ABS(V33)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V33,SUM(CASE WHEN ABS(V34)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V34,SUM(CASE WHEN ABS(V35)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V35
,SUM(CASE WHEN ABS(V36)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V36,SUM(CASE WHEN ABS(V37)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V37,SUM(CASE WHEN ABS(V38)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V38,SUM(CASE WHEN ABS(V39)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V39,SUM(CASE WHEN ABS(V40)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V40,SUM(CASE WHEN ABS(V41)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V41
,SUM(CASE WHEN ABS(V42)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V42,SUM(CASE WHEN ABS(V43)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V43,SUM(CASE WHEN ABS(V44)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V44,SUM(CASE WHEN ABS(V45)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V45,SUM(CASE WHEN ABS(V46)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V46,SUM(CASE WHEN ABS(V47)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V47
,SUM(CASE WHEN ABS(V48)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V48,SUM(CASE WHEN ABS(V49)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V49,SUM(CASE WHEN ABS(V50)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V50,SUM(CASE WHEN ABS(V51)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V51,SUM(CASE WHEN ABS(V52)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V52,SUM(CASE WHEN ABS(V53)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V53
,SUM(CASE WHEN ABS(V54)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V54,SUM(CASE WHEN ABS(V55)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V55,SUM(CASE WHEN ABS(V56)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V56,SUM(CASE WHEN ABS(V57)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V57,SUM(CASE WHEN ABS(V58)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V58,SUM(CASE WHEN ABS(V59)>=ROUND((65/100),2) THEN 1 ELSE 0 END) AS V59
FROM stat GROUP BY XL_ID, IMAXLONG_20
)
), hj AS (
-- 小表：基础表+一天中的最大电流+地区信息
SELECT DISTINCT stat.XL_ID
,stat.XL_NAME
,lj.POWER_NAME
,DECODE(stat.VOLTAGE_TYPE, '1003', '500kV', '1005', '220kV') AS VOLTAGE_NAME
,stat.IMAXLONG_20
,nj.ZDDL
,CONCAT(ROUND(ROUND(nj.ZDDL/stat.IMAXLONG_20,2)*100), '%') AS FZL
FROM stat
INNER JOIN lj ON stat.XL_ID = lj.ID
INNER JOIN nj ON stat.XL_ID = nj.ID
-- WHERE VOLTAGE_TYPE in (1003, 1005)
-- AND INSTR('山西电网', POWER_NAME)
-- AND INSTR(XL_NAME,'风运二线')
)
SELECT hj.*, sj.ZZSC
FROM hj INNER JOIN sj ON hj.XL_ID = sj.XL_ID
ORDER BY XL_NAME;
02.示例2
SELECT DISTINCT lineEnd.NAME, CONCAT(LEFT(lineEnd.OWNER,4),'00') AS OWNER, line.LOAD_TYPE
FROM SG_DATACENTER.SG_DEV_ACLINEEND_B lineEnd
LEFT JOIN SG_DATACENTER.SG_DEV_ACLINE_B line
ON lineEnd.LINE_ID = line.ID
WHERE line.LOAD_TYPE IS NOT NULL;
在这段SQL中，LEFT JOIN会保留lineEnd表中所有的记录，即使line表中没有匹配的记录。
这是LEFT JOIN的特性：会保留左表（lineEnd）中的所有记录，并在右表（line）中找不到匹配记录时用NULL填充。
但是，WHERE子句中的line.LOAD_TYPE IS NOT NULL会在最终结果集中过滤掉line.LOAD_TYPE为NULL的记录。
SELECT DISTINCT lineEnd.NAME, CONCAT(LEFT(lineEnd.OWNER,4),'00') AS OWNER, line.LOAD_TYPE
FROM SG_DATACENTER.SG_DEV_ACLINEEND_B lineEnd
LEFT JOIN SG_DATACENTER.SG_DEV_ACLINE_B line
ON lineEnd.LINE_ID = line.ID AND line.LOAD_TYPE IS NOT NULL
WHERE line.LOAD_TYPE IS NOT NULL;
在这段SQL中，LEFT JOIN的ON子句中添加了line.LOAD_TYPE IS NOT NULL的条件。
这意味着只有在line.LOAD_TYPE不为NULL时，才会进行连接。
这样做的结果是，如果line.LOAD_TYPE为NULL，那么即使在lineEnd.LINE_ID = line.ID条件满足的情况下，也不会进行连接。
总结
第一段SQL：LEFT JOIN之后，WHERE子句过滤掉line.LOAD_TYPE为NULL的记录。
第二段SQL：LEFT JOIN过程中，如果line.LOAD_TYPE为NULL，则不会进行连接。
这两种写法最终的结果集可能是一样的，因为WHERE子句和LEFT JOIN的ON条件都确保了line.LOAD_TYPE不为NULL。然而，处理的方式不同：
第一段SQL会先进行LEFT JOIN，然后再过滤。
第二段SQL在JOIN时已经排除了line.LOAD_TYPE为NULL的记录。
在某些数据库管理系统中，第二种方式可能会更高效，因为它减少了需要进行的连接操作。
2.15 [3]创建：失效场景
01.索引失效的场景
01.查询条件包含or，可能导致索引失效
02.如何字段类型是字符串，where时一定用引号括起来，否则索引失效
03.like通配符可能导致索引失效
04.联合索引，查询时的条件列不是联合索引中的第一个列，索引失效
05.在索引列上使用mysql的内置函数，索引失效
06.对索引列运算（如，+、-、*、/），索引失效
07.索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效
08.索引字段上使用is null， is not null，可能导致索引失效
09.左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效
10.mysql估计使用全表扫描要比使用索引快,则不使用索引
02.索引失效的场景
a.在索引列上使用函数或表达式
如果在查询中对索引列使用了函数或表达式，索引可能无法使用。例如：SELECT * FROM table WHERE YEAR(date_column) = 2021
b.使用不等于（<>）或者 NOT 操作符
这些操作符通常会导致索引失效，因为它们会导致全表扫描
c.使用 LIKE 操作符，但通配符在最前面
如果 LIKE 的模式串是以“%”或“_”开头，索引无法使用。例如：SELECT * FROM table WHERE column LIKE '%abc'
d.OR操作符
如果查询条件中使用了 OR，并且 OR 两边的条件涉及不同的索引，这些索引可能无法使用
e.查询条件中列类型是字符串，没有使用引号
可能会因为类型不同发生隐式转换，使索引失效
f.对于组合索引，不是使用组合索引最左边的字段
如果没有使用组合索引的最左边字段，索引不会被使用
2.16 [3]创建：适合场景
01.索引适合的场景
a.对中大型表，索引非常有效
索引可以显著提高查询性能
b.经常用于查询的字段
在 WHERE 子句中经常使用的字段适合建立索引
c.经常用于连接的字段
建立索引可以加快连接速度
d.经常需要排序的字段
索引已经排好序，可以加快排序查询速度
e.对经常在 ORDER BY、GROUP BY、DISTINCT 后面的字段建立索引
索引可以帮助加快这些操作的速度
01.常用函数
a.ROW_NUMBER/RANK/DENSE_RANK - 排序
SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
RANK() OVER (ORDER BY salary DESC) as rank_num,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank_num
FROM employees;
b.FIRST_VALUE/LAST_VALUE - 首尾值
SELECT
name,
department,
salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as highest_salary,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_salary
FROM employees;
c.LAG/LEAD - 前后行
SELECT
name,
department,
salary,
LAG(salary) OVER (PARTITION BY department ORDER BY salary) as prev_salary,
LEAD(salary) OVER (PARTITION BY department ORDER BY salary) as next_salary
FROM employees;
d.NTILE - 分组
SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;
01.常用函数
a.MD5 - MD5加密
-- MySQL & SQL Server
SELECT MD5('password');
b.SHA1/SHA2 - SHA加密
-- MySQL
SELECT SHA1('password');
SELECT SHA2('password', 256);
c.ENCRYPT/DECRYPT - 加密解密
-- MySQL
SET @key = 'secret_key';
SET @encrypted = AES_ENCRYPT('text', @key);
SELECT AES_DECRYPT(@encrypted, @key);
3.8 XML函数(SQL Server)
01.常用函数
a.FOR XML PATH - 生成XML
SELECT name, age
FROM employees
FOR XML PATH('employee'), ROOT('employees')
b.XML数据类型方法
DECLARE @xml XML
SET @xml = '<root><child>value</child></root>'
SELECT @xml.value('(/root/child)[1]', 'varchar(50)')
01.常见函数
a.VERSION - 数据库版本
-- MySQL
SELECT VERSION();
-- SQL Server
SELECT @@VERSION;
-- Oracle
SELECT * FROM V$VERSION;
b.USER/CURRENT_USER - 当前用户
-- 所有数据库
SELECT USER;
SELECT CURRENT_USER;
c.DATABASE/DB_NAME - 当前数据库
-- MySQL
SELECT DATABASE();
-- SQL Server
SELECT DB_NAME();
3.11 高级聚合函数
01.常见函数
a.GROUPING SETS - 多维度聚合
SELECT department, location, COUNT(*)
FROM employees
GROUP BY GROUPING SETS (
(department, location),
(department),
(location),
()
);
b.CUBE - 所有可能的组合
SELECT department, location, COUNT(*)
FROM employees
GROUP BY CUBE (department, location);
c.ROLLUP - 层次聚合
SELECT
COALESCE(department, 'Total') as department,
COALESCE(location, 'Subtotal') as location,
COUNT(*) as employee_count,
AVG(salary) as avg_salary
FROM employees
GROUP BY ROLLUP (department, location);
d.PIVOT - 行转列
-- SQL Server
SELECT *
FROM (
SELECT department, location, salary
FROM employees
) AS SourceTable
PIVOT (
AVG(salary)
FOR location IN ([New York], [London], [Tokyo])
) AS PivotTable;
3.12 统计和数学函数
01.常见函数
a.PERCENTILE_CONT/PERCENTILE_DISC - 百分位数
SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median_salary,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) as discrete_median
FROM employees;
b.CORR - 相关系数
SELECT CORR(salary, performance_score)
FROM employees;
c.STDDEV/VARIANCE - 标准差和方差
SELECT
department,
AVG(salary) as avg_salary,
STDDEV(salary) as salary_stddev,
VARIANCE(salary) as salary_variance
FROM employees
GROUP BY department;
d.FIRST/LAST - 组内第一个/最后一个值
-- Oracle
SELECT
department,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) as first_salary,
LAST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY hire_date
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as last_salary
FROM employees;
3.13 字符串模式匹配函数
01.常见函数
a.LIKE模式匹配增强
-- 复杂LIKE模式
SELECT * FROM employees
WHERE
name LIKE '[A-M]%' -- SQL Server, 以A到M开头的名字
AND email LIKE '%@__%.__%'; -- 标准email模式
3.14 条件和流程控制增强
01.常见函数
a.CHOOSE - 索引选择
-- SQL Server
SELECT CHOOSE(2, 'First', 'Second', 'Third'); -- 返回 'Second'
b.复杂CASE表达式
SELECT
employee_name,
salary,
CASE
WHEN salary <= (SELECT AVG(salary) FROM employees) THEN 'Below Average'
WHEN salary <= (SELECT AVG(salary) + STDDEV(salary) FROM employees) THEN 'Average'
WHEN salary <= (SELECT AVG(salary) + 2*STDDEV(salary) FROM employees) THEN 'Above Average'
ELSE 'Exceptional'
END as salary_category
FROM employees;
3.15 表分析函数
01.常见函数
a.PERCENT_RANK - 百分比排名
SELECT
name,
salary,
PERCENT_RANK() OVER (ORDER BY salary) as salary_percentile
FROM employees;
b.CUME_DIST - 累积分布
SELECT
name,
salary,
CUME_DIST() OVER (ORDER BY salary) as salary_distribution
FROM employees;
3.16 实用复合函数示例
01.常见函数
a.年龄计算
-- MySQL
SELECT
name,
birthdate,
TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) as age,
DATE_ADD(birthdate,
INTERVAL TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) YEAR) as last_birthday,
DATE_ADD(birthdate,
INTERVAL TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) + 1 YEAR) as next_birthday
FROM employees;
b.工龄分析
SELECT
name,
hire_date,
CASE
WHEN DATEDIFF(YEAR, hire_date, GETDATE()) < 2 THEN 'Junior'
WHEN DATEDIFF(YEAR, hire_date, GETDATE()) < 5 THEN 'Intermediate'
WHEN DATEDIFF(YEAR, hire_date, GETDATE()) < 10 THEN 'Senior'
ELSE 'Expert'
END as experience_level
FROM employees;
c.薪资分析
WITH salary_stats AS (
SELECT
department,
AVG(salary) as avg_salary,
STDDEV(salary) as salary_stddev
FROM employees
GROUP BY department
)
SELECT
e.name,
e.department,
e.salary,
s.avg_salary,
(e.salary - s.avg_salary) / s.salary_stddev as z_score,
PERCENT_RANK() OVER (PARTITION BY e.department ORDER BY e.salary) as dept_percentile
FROM employees e
JOIN salary_stats s ON e.department = s.department;
d.考勤分析
WITH daily_attendance AS (
SELECT
employee_id,
attendance_date,
check_in_time,
check_out_time,
CASE
WHEN check_in_time > '09:00:00' THEN 'Late'
WHEN check_out_time < '17:00:00' THEN 'Early Leave'
ELSE 'Normal'
END as attendance_status
FROM attendance
)
SELECT
e.name,
COUNT(*) as total_days,
SUM(CASE WHEN a.attendance_status = 'Late' THEN 1 ELSE 0 END) as late_days,
SUM(CASE WHEN a.attendance_status = 'Early Leave' THEN 1 ELSE 0 END) as early_leave_days,
FORMAT(COUNT(*) * 1.0 /
(SELECT COUNT(DISTINCT attendance_date) FROM attendance), 'P') as attendance_rate
FROM employees e
JOIN daily_attendance a ON e.id = a.employee_id
GROUP BY e.name;
e.销售分析
WITH monthly_sales AS (
SELECT
YEAR(sale_date) as year,
MONTH(sale_date) as month,
SUM(amount) as total_sales,
COUNT(DISTINCT customer_id) as customer_count
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date)
)
SELECT
year,
month,
total_sales,
customer_count,
total_sales / customer_count as avg_customer_value,
LAG(total_sales) OVER (ORDER BY year, month) as prev_month_sales,
total_sales - LAG(total_sales) OVER (ORDER BY year, month) as sales_growth,
FORMAT((total_sales - LAG(total_sales) OVER (ORDER BY year, month)) /
LAG(total_sales) OVER (ORDER BY year, month), 'P') as growth_rate
FROM monthly_sales;