Mysql - SQL 优化|电子爱好者

admin管理员组
文章数量:1547197

👀为什么查询速度会慢

🚀查询的生命周期：

从客户端到服务器: 数据传输时间。
解析查询: 将SQL文本解析为执行结构。
生成执行计划: 根据表和索引的统计信息，优化器决定如何检索数据。
执行查询: 包括检索存储引擎的数据、排序、分组等。
返回结果给客户端: 结果集的传输。

在这个过程中，查询可能会在以下方面花费时间：

网络延迟: 客户端和服务器之间的数据传输。
CPU计算: 执行查询、排序或其他操作。
统计信息和执行计划的生成: 对查询进行优化。
锁等待: 由于并发查询而导致的延迟。
I/O操作: 读取磁盘上的数据。

根据存储引擎的不同，还可能出现大量的上下文切换和系统调用。

🔎常见的查询性能瓶颈：

不必要的操作: 如检索不需要的列或行。
重复的操作: 例如，多次执行相同的数据检索。
操作执行得太慢: 如没有使用索引或使用了低效的算法。

📘 数据库优化步骤

🎯 1. 选择适合的DBMS

🔹 商业DBMS：如 SQL Server 和 Oracle，适用于大数据量和高事务性要求。
🔹 MySQL：开源，多种存储引擎选择，适用于多种业务场景。
🔹 NoSQL：包括键值型、文档型、搜索引擎、列式存储和图形数据库。根据业务选择适当的NoSQL类型。

📊 2. 优化表设计

🔹 三范式：数据结构更清晰，减少冗余。
🔹 反范式：提高查询效率。

🔹 优化数据类型

优化数据类型是为了提高存储效率、减少IO操作，并增加查询性能。

整数类型数据优化
示例：
如果我们有一个用户表，其中的用户ID是一个非负数，我们可以选择 UNSIGNED INT 作为数据类型。
```
CREATE TABLE users (
    user_id UNSIGNED INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);
```
优先使用整数类型
示例：
如果我们记录用户的IP地址，而不是存储字符串形式的IP，我们可以将其转换为整数。
```
CREATE TABLE user_logins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    ip_address BIGINT UNSIGNED
);
```
避免使用text、blob数据类型
示例：
如果我们有一个博客文章表，而且我们知道文章长度不会太长，我们可以使用 VARCHAR 而不是 TEXT。
```
CREATE TABLE articles (
    article_id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(5000)
);
```

避免使用ENUM类型
示例：
如果我们记录用户的性别，我们可以使用 TINYINT 而不是 ENUM。

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    gender TINYINT COMMENT '1 for Male, 2 for Female'
);

使用TIMESTAMP存储时间
示例：
如果我们记录用户的注册时间，我们可以使用 TIMESTAMP。

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    registration_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

使用decimal存储精确浮点数
示例：
如果我们记录商品的价格，我们可以使用 DECIMAL。

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10, 2)
);

🔹 优化插入记录的速度

优化插入记录的速度可以大大提高数据导入的效率。

MyISAM引擎示例：

-- 禁用索引
ALTER TABLE large_table DISABLE KEYS;

-- 插入大量数据
INSERT INTO large_table (...) VALUES (...), (...), (...);

-- 重新启用索引
ALTER TABLE large_table ENABLE KEYS;

InnoDB引擎示例：

-- 禁止自动提交和外键检查
SET autocommit=0;
SET foreign_key_checks=0;
SET unique_checks=0;

-- 插入大量数据
INSERT INTO large_table_innodb (...) VALUES (...), (...), (...);

-- 恢复设置
SET unique_checks=1;
SET foreign_key_checks=1;
COMMIT;
SET autocommit=1;

📈 3. 优化逻辑查询

🔹 子查询优化

子查询可以嵌套在其他查询中。为了提高效率，我们应该注意子查询的写法和执行次序，特别是当它们涉及到大数据表时。

示例：

假设我们有一个订单表 orders 和一个产品表 products。我们想找出哪些产品从未被订购过。

不推荐：

SELECT product_name 
FROM products 
WHERE product_id NOT IN (SELECT product_id FROM orders);

这种写法会对每一个 products 表中的产品执行子查询。如果订单表很大，这会非常低效。

🔹 避免字段运算

在 WHERE 子句中进行字段运算会导致该字段上的索引失效，从而降低查询效率。我们应该尽量避免字段运算，并使用其他方式来实现相同的需求。

示例：

假设我们有一个评论表 product_comment，我们想要找出评论内容开头为 “abc” 的所有评论。

不推荐：

SELECT comment_id, comment_text, comment_time 
FROM product_comment 
WHERE substring(comment_text,1,3)='abc';

这种写法会使 comment_text 上的索引失效。

🛠️ 4. 优化物理查询

🔹 使用索引

索引是数据库中用于加速查询速度的数据结构。正确地使用索引可以使查询速度大大加快。

示例：
假设我们有一个用户表，并且经常基于邮箱查询用户。为了加速这种查询，我们可以为 email 字段创建一个索引。

CREATE INDEX idx_email ON users(email);

但要记住，索引不是万能的。过度的索引会导致插入和更新变慢，同时占用更多的存储空间。

🔹 单表扫描

单表扫描是指对一个表进行的查询，它可以是全表扫描或局部扫描。

全表扫描：查询没有使用任何索引，数据库需要扫描整个表来查找匹配的记录。

示例：
```
SELECT * FROM users WHERE age > 30;
```
如果表中没有关于 age 的索引，那么上述查询可能会执行全表扫描。
局部扫描：当查询使用索引时，数据库只需要扫描表的一部分来查找匹配的记录。

示例：
```
SELECT * FROM users WHERE email = 'test@example';
```
如果为 email 字段创建了索引，上述查询会使用索引进行局部扫描。

🔹 多表连接

当从多个表中查询数据时，连接顺序很重要。不同的连接顺序可能导致查询性能的大幅度差异。

示例：
假设我们有两个表：一个是 users 表，另一个是 orders 表。我们想要查询所有用户及其订单。

SELECT * FROM users 
JOIN orders ON users.id = orders.user_id;

在这种情况下，选择先扫描哪个表（即驱动表）并基于这个表来连接另一个表会影响查询的性能。优化器通常会选择记录数较少的表作为驱动表，但这也取决于查询条件和索引。

为了提高效率，我们还需要确保连接条件中的字段（在上述示例中是 users.id 和 orders.user_id）都有索引。

🚀 5. 使用缓存

🔹 Redis：支持持久化，多种数据结构。
🔹 Memcached：纯内存存储，速度快。

🌐 6. 库级优化

🔹 读写分离

读写分离策略是数据库架构中的一种常见优化方法，通过将读和写操作分开，将读操作指向多个从数据库，而将写操作指向单个主数据库，从而实现负载均衡，提高系统的吞吐量和可用性。

示例：

假设你有一个电子商务网站，随着用户量的增长，主数据库开始面临压力。为了解决这个问题，你决定实施读写分离策略。

主数据库 (Master): 所有的写操作（如插入、更新和删除）都会发送到这里。
从数据库 (Slave): 多个只读数据库，用于处理查询请求。当主数据库写入数据后，这些更改会被复制到从数据库。

当用户浏览商品或查看订单时，查询会发送到从数据库。但是，当用户下订单或修改账户信息时，写请求会发送到主数据库。

🔹 数据分片

数据分片是一种数据管理策略，通过将数据分散到多个数据库或表中，从而提高性能、可扩展性和可管理性。

垂直拆分 (分库): 根据业务功能将一个大的数据库分解为多个小的数据库。例如，将用户表、订单表和产品表放在不同的数据库中。

示例：

一个大型电商网站可能将用户信息存储在一个名为user_db的数据库中，而商品信息存放在product_db中，订单数据存放在order_db中。
水平拆分 (分表): 将一个大的表按某种逻辑（如ID范围或日期）分解为多个小的相似结构的表。

示例：

对于一个具有数百万用户的应用，你可能会根据用户ID将用户表分成多个表。例如，ID 1-10000 的用户在users_1表，ID 10001-20000 的用户在users_2表，以此类推。

通过正确的数据分片策略，你可以确保每个数据库或表的大小保持在一个可管理的范围内，同时提高查询性能和系统的整体可扩展性。

🏗️ 7. 优化数据库结构

🔹 冷热数据分离

冷热数据分离是一种常见的数据库优化策略，目的是将经常访问的数据（热数据）和不经常访问的数据（冷数据）分开存储，以提高查询性能。

示例：
假设我们有一个日志表，其中只有近一个月的日志会被频繁查询（热数据），而更早的日志查询得很少（冷数据）。

为此，我们可以创建两个表：logs_recent 和 logs_archive。每天，我们可以将 logs_recent 中超过一个月的数据迁移到 logs_archive 中。

INSERT INTO logs_archive SELECT * FROM logs_recent WHERE log_date < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
DELETE FROM logs_recent WHERE log_date < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

🔹 增加中间表

当需要从多个表中联合查询数据时，可以考虑创建中间表来存储经常要查询的数据，以避免频繁的多表连接。

示例：
假设我们经常需要查询用户及其最近的订单。为此，我们可以创建一个 user_recent_orders 的中间表，其中包含用户ID和其最近的订单信息。

CREATE TABLE user_recent_orders AS
SELECT users.id as user_id, orders.id as order_id, orders.date as order_date
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.date > DATE_SUB(CURDATE(), INTERVAL 7 DAY);

🔹 冗余字段

虽然冗余字段可能导致数据更新更复杂，但它们可以大大提高查询性能。

示例：
假设我们有一个 orders 表，其中包含 user_id，并且经常需要查询用户的名字。为了避免与 users 表的频繁连接，我们可以在 orders 表中添加一个 user_name 的冗余字段。

🔹 数据类型优化

优化数据类型可以减少存储空间，提高查询速度。

示例：
考虑使用 TIMESTAMP 而非 DATETIME。
TIMESTAMP 类型占用 4 个字节，而 DATETIME 类型占用 8 个字节。如果我们只关心日期和时间（不关心时区），则可以使用 TIMESTAMP 以节省空间。

ALTER TABLE events CHANGE COLUMN event_time event_time TIMESTAMP;

这种转换可以节省存储空间，并可能提高与此列相关的查询性能。

📝 8. 优化插入速度

🔹 禁用索引

在进行大量数据的批量插入时，建议先禁用索引，因为索引的维护会降低数据的插入速度。完成数据插入后再重新启用索引。

示例：
假设我们要在 users 表中进行大量数据插入。

-- 禁用索引
ALTER TABLE users DISABLE KEYS;

-- 执行插入操作
INSERT INTO users (...) VALUES (...), (...), (...);

-- 重新启用索引
ALTER TABLE users ENABLE KEYS;

🔹 禁用唯一性检查

禁用唯一性检查可以在插入大量数据时提高速度，但需要确保插入的数据不违反唯一性约束。

示例：

-- 禁用唯一性检查
SET unique_checks=0;

-- 执行插入操作
INSERT INTO users (...) VALUES (...), (...), (...);

-- 重新启用唯一性检查
SET unique_checks=1;

🔹 批量插入

批量插入是一次插入多条记录，这比单独插入每条记录要快得多。

示例：

-- 批量插入
INSERT INTO users (name, email) VALUES 
('Alice', 'alice@example'), 
('Bob', 'bob@example'), 
('Charlie', 'charlie@example');

此外，对于非常大的数据集，建议使用 LOAD DATA INFILE 语句，这是MySQL提供的一种高效批量导入数据的方法。

🌟 其他注意事项

🔹 使用非空约束

使用非空约束（NOT NULL）确保字段总是包含值，从而避免了空值检查的开销，提高查询和存储的效率。此外，非空字段也更容易创建和维护索引，因为索引NULL列需要额外的空间来保存。

示例：

考虑一个场景，我们正在设计一个 employees 表，其中员工的 name 和 email 应始终存在。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    hire_date DATE
);

在上面的示例中：

name 和 email 字段都有 NOT NULL 约束，确保每个员工必须有名称和电子邮件。
这样做有助于提高查询效率，因为数据库不需要检查这些字段的空值。
同时，这也确保了数据的完整性，因为每个员工都应该有名称和电子邮件。

当插入记录时，如果尝试插入没有 name 或 email 的记录，数据库将引发错误。这确保了数据的质量和完整性。

👀常见优化策略

查询性能低下的根本原因是过多的数据访问。大多数效率不佳的查询可以通过减少访问的数据量进行优化。分析低效查询时，主要考虑两个方面：

应用程序是否检索了超过需要的数据：这可能是因为访问了过多的行或列。
MySQL服务器是否分析了超过需要的数据行。

🔥 常见场景

🎯 优化查询过程中的数据访问

🔹 问题：访问数据太多导致查询性能下降。

📝 原因：可能是查询了太多的行或列，导致检索了超过需要的数据。

🔍 解决办法：

避免查询不需要的数据
示例：
❌ SELECT * FROM users;
✅ SELECT * FROM users LIMIT 100;
多表关联时避免返回全部列
示例：
❌ SELECT * FROM users JOIN orders ON users.id = orders.user_id;
✅ SELECT users.name, orders.order_id FROM users JOIN orders ON users.id = orders.user_id;

🛠 优化长的查询语句

🔹 建议：考虑使用多个简单查询而不是一个复杂查询。

📝 原因：MySQL可以每秒扫描内存中上百万行数据，但响应数据给客户端会更慢。

🔍 解决办法：

切分查询
示例：
❌ SELECT * FROM orders WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';
✅ SELECT * FROM orders WHERE order_date BETWEEN '2021-01-01' AND '2021-06-30';
✅ SELECT * FROM orders WHERE order_date BETWEEN '2021-07-01' AND '2021-12-31';

📊 优化关联查询

🔹 建议：优化ON或USING子句，使用索引。

📝 原因：确保GROUP BY和ORDER BY只使用一个表中的列。

🔍 解决办法：

使用UNION ALL替代UNION
示例：
❌ SELECT name FROM users WHERE age < 20 UNION SELECT name FROM users WHERE age > 50;
✅ SELECT name FROM users WHERE age < 20 UNION ALL SELECT name FROM users WHERE age > 50;

💾 优化子查询

📝 原因：子查询可能导致效率问题。

🔍 解决办法：

使用关联查询替代子查询
示例：
❌ SELECT id FROM users WHERE age IN (SELECT age FROM ages WHERE age < 20);
✅ SELECT users.id FROM users JOIN ages ON users.age = ages.age WHERE ages.age < 20;

🗂 优化LIMIT分页

🔹 建议：优化大偏移量的LIMIT查询。

📝 原因：LIMIT偏移量大时，查询效率较低。

🔍 解决办法：

优化大偏移量的查询
示例：
❌ SELECT * FROM users LIMIT 1000000, 10;
✅ SELECT * FROM users WHERE id > 1000000 LIMIT 10;

🕐 优化WHERE子句

🔹 建议：优化WHERE子句的内容和结构。

📝 原因：某些WHERE子句的写法可能导致全表扫描或低效率。

🔍 解决办法：

避免在WHERE子句中使用函数
示例：
❌ SELECT id FROM users WHERE YEAR(birthdate) = 1990;
✅ SELECT id FROM users WHERE birthdate BETWEEN '1990-01-01' AND '1990-12-31';

🌐 数据结构的优化

🔹 建议：优化数据库的结构和设计。

📝 原因：一个好的数据库设计可以大大提高查询性能。

🔍 解决办法：

使用中间表
示例：
❌ SELECT users.name, orders.order_date FROM users JOIN orders ON users.id = orders.user_id WHERE orders.order_date BETWEEN '2021-01-01' AND '2021-12-31';
✅ 先创建一个中间表user_orders_summary，然后查询：
SELECT name, order_date FROM user_orders_summary WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';

📊 查询SQL优化

🔹 慢查询日志
可以开启慢查询日志来定位低效的SQL。
示例：
✅ SET GLOBAL slow_query_log = 'ON';

🔹 避免列运算
不要在列上执行运算，这会导致全表扫描。
示例：
❌ SELECT id FROM users WHERE age + 1 = 30
✅ SELECT id FROM users WHERE age = 29

🔹 简化SQL
SQL语句应尽可能简单。
示例：
❌ 一个超长的SQL查询
✅ 分解为多个简单的SQL查询

🔹 避免SELECT
尽量指定所需的字段而不是使用 SELECT *。使用 SELECT * 会导致数据库查找所有列名，可能会导致全表扫描，增加数据传输时间和I/O，内存和CPU的消耗。

示例：
❌ SELECT * FROM users
✅ SELECT id, name, email FROM users

🔹 OR与IN
使用 IN 替代多个 OR 条件。
示例：
❌ SELECT id FROM books WHERE author = 'A' OR author = 'B'
✅ SELECT id FROM books WHERE author IN ('A', 'B')

🔹 避免函数和触发器
尽量在应用程序中处理逻辑，减少数据库的函数和触发器使用。
示例：
❌ 在数据库中处理字符串操作
✅ 在应用程序中处理字符串操作

🔹 避免%xxx查询
尽量不要以%开头的LIKE查询。
示例：
❌ SELECT id FROM users WHERE name LIKE '%john%'
✅ SELECT id FROM users WHERE name LIKE 'john%'

🔹 少用JOIN
尽量减少联接查询，尤其是多表联接。
示例：
❌ SELECT * FROM users JOIN orders JOIN products
✅ 分成几个查询或优化查询结构

🔹 类型匹配
查询时确保数据类型匹配，以利用索引。
示例：
❌ SELECT id FROM users WHERE age = '30'
✅ SELECT id FROM users WHERE age = 30

🔹 BETWEEN vs IN
虽然 BETWEEN 和 IN 在某些情况下执行时间上可能差不多，但从整体成本上看，BETWEEN 通常更优。
对于连续的数值，使用 BETWEEN 而不是 IN。
示例：
❌ SELECT id FROM users WHERE age IN (25, 26, 27, 28, 29, 30)
✅ SELECT id FROM users WHERE age BETWEEN 25 AND 30

🔹 LIMIT用于分页
使用 LIMIT 进行分页，并确保每页的数据量不是很大。
示例：
✅ SELECT name FROM users LIMIT 10 OFFSET 20

🔹 多使用commit
经常使用 commit 可以提高程序性能，并释放资源，减少数据库的锁定时间。

🛠️ 其他调优策略

🔹 服务器语句超时处理

为了确保资源不会被长时间运行的低效率查询占用，可以设置服务器语句超时。这可以帮助自动中止运行时间超过指定时间限制的查询。

示例：
设置查询的最大执行时间为2000毫秒。

SET GLOBAL MAX_EXECUTION_TIME = 2000;

当一个查询的执行时间超过2000毫秒时，MySQL将自动中止该查询并返回一个超时错误。

🔹 创建全局通用表空间

InnoDB引擎允许创建全局通用表空间，这样的表空间可以被所有数据库和表共享，有助于节省元数据内存。

示例：

创建名为janus的全局通用表空间。

CREATE TABLESPACE janus ADD DATAFILE 'janus.ibd' FILE_BLOCK_SIZE=16K;

创建一个新表并指定它使用janus表空间。

CREATE TABLE test (
    id INT,
    name VARCHAR(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 TABLESPACE janus;

将现有的表移动到janus表空间。

ALTER TABLE existing_table TABLESPACE janus;

如果确定共享表空间内的所有数据都不再需要，且所有依赖该表空间的表都已被删除，可以删除该共享表空间以释放空间。
```
DROP TABLESPACE janus;
```

设置合理的超时和使用全局通用表空间都是数据库调优的有效策略。它们可以帮助确保资源的高效利用，同时提高整体系统的性能和可靠性。

👀SQL 执行流程

💡SQL执行流程

先尝试从查询缓存(8.0以后不再有查询缓存)中查询该sql是否已经有缓存的结果了；
分析器拿到sql之后会尝试对sql语句进行词法分析和语法分析，校验语法的正确性；
优化器拿到分析器的sql之后，开始继续解析sql，判断需要走什么索引，根据实际情况重写sql，最终生成执行计划；
执行器根据执行计划执行sql，执行之前会先进行操作权限校验；
然后根据表存储引擎调用对应接口进行查询数据，这里的扫描行数就是指的接口返回的记录数，执行器拿到返回记录之后进一步加工；
执行器根据sql条件依次筛选数据；从磁盘数据中读取数据到内存Buffer Pool（数据页，索引页）中；

💡SQL执行流程示例

SELECT column1, AVG(column2) as avg_value
FROM table1
JOIN table2 ON table1.id = table2.table1_id
WHERE table1.category = 'Category1'
GROUP BY table1.id, table1.column1
HAVING avg_value > 50
UNION
SELECT column3, MAX(column4) as max_value
FROM table3
WHERE table3.status = 'Active'
ORDER BY max_value DESC
LIMIT 10 OFFSET 20;

连接表操作：MySQL会首先评估table1和table2之间的连接。如果table1.id和table2.table1_id上有索引，MySQL将利用它来使连接操作更高效。如果没有，可能会导致更昂贵的操作，例如全表扫描。
过滤条件：接着，MySQL会对table1.category = 'Category1'进行过滤。如果table1.category上有索引，此操作会更快。
分组与筛选：然后，MySQL会按照table1.id和table1.column1进行分组，并计算每组的平均值。之后，它会筛选出平均值大于50的组。
第二部分查询：对于第二部分的查询，MySQL会从table3中筛选出状态为"Active"的记录。如果table3.status上有索引，这个操作将会更高效。
聚合与排序：接着，MySQL会计算每组的最大值，并根据最大值进行排序。
合并查询结果：上述两部分的查询结果会使用UNION操作合并。需要注意的是，UNION默认会删除重复的记录，如果不需要这样做，可以使用UNION ALL。
排序与分页：最后，整合的结果会按照max_value降序排序，并跳过前20条结果后，返回接下来的10条记录。

💡CUR执行流程

数据写入Buffer Pool的同时，先写入数据到redo log buffer中；
redo log buffer 依据 Force log at Commit(按时或事物提交的时候)写入 redo log日志中；
Buffer pool 依据check point 择时脏页数据落盘；（redo log日志满了也会触发该操作，成功之后会清理redo log日志）;
Double write保证脏页数据不会出现部分落盘失败的情况。

💡关联查询

关联查询是在两个或更多表之间执行的查询，MySQL使用多种策略来执行这些查询。永远使用小的结果集驱动大的结果集

🎯1. 嵌套循环连接 (Nested Loop Join)

示例：

考虑两个表A和B，使用嵌套循环连接进行连接。

SELECT A.*, B.* FROM A, B WHERE A.id = B.a_id;

🎯2. 块嵌套循环连接 (Block Nested Loop Join)

示例：

如果表B较小并可以完全装入内存，MySQL可能使用块嵌套循环连接。

🎯3. 散列连接 (Hash Join)

示例：

当两个表都很大并且没有合适的索引来加速连接时，MySQL可能使用散列连接。

🎯4. 排序合并连接 (Sort Merge Join)

示例：

如果两个表已经按连接键排序，MySQL可能使用排序合并连接。

💡关联查询示例

MySQL 中执行关联查询主要采用了嵌套循环关联的方法。

✍基本概念：

关联查询：在一个查询中涉及多个表的查询称为关联查询。
嵌套循环关联：对于每个主表中的行，MySQL 会在关联表中循环查找匹配的行。这是通过嵌套的两层循环完成的。

✍执行步骤：

单表查询：如果查询仅涉及一个表，MySQL 将仅执行这个表的查询。
多表关联查询：MySQL 会为主表中的每一行执行一个内部循环，以在关联表中找到匹配的行。

SQL语句分析过程：

考虑以下的SQL语句：

SELECT A.name, B.order_date
FROM Customers A
INNER JOIN Orders B ON A.customer_id = B.customer_id
WHERE A.country = 'USA' AND B.order_date > '2020-01-01';

分析过程：

解析查询：MySQL 解析器首先会检查查询的语法是否正确。
确定查询类型：确定这是一个关联查询，涉及两个表：Customers 和 Orders。
优化器决策：优化器会决定最佳的执行计划。它可能考虑哪个表应该是主表，是否存在可以利用的索引等。
执行主表查询：按优化器的决策，MySQL 可能首先从 Customers 表中选择国家为 ‘USA’ 的行。
嵌套循环关联：对于从 Customers 表中选出的每一行，MySQL 将在 Orders 表中查找具有相同 customer_id 且 order_date 大于 ‘2020-01-01’ 的行。
结果合成：MySQL 将两表匹配的行合并为结果集的一部分，并继续下一次循环，直到 Customers 表中的所有行都被处理。
返回结果：当所有匹配的行都被处理后，MySQL 将结果集返回给客户端。

此过程提供了关联查询是如何在MySQL中执行的一个高层次的概述。实际的执行可能会因表的大小、索引的存在、服务器配置和其他因素而有所不同。

💡数据和索引的统计信息

查询优化器依赖于关于数据和索引的统计信息来选择最佳的执行计划。

🎯1. 表的统计信息：

例如，使用SHOW TABLE STATUS LIKE 'students';可以获得有关"students"表的统计信息。

🎯2. 列的统计信息：

例如，使用SHOW INDEX FROM students;可以获得有关"students"表索引的统计信息。

🎯3. 索引的统计信息：

同上，使用SHOW INDEX FROM students;可以获得有关"students"表索引的统计信息。

👀SQL 查询剖析

SQL查询是一个动态的过程，从页加载的角度来看，我们可以得到以下两点结论：
- 位置决定效率。如果页就在数据库缓冲池中，那么效率是最高的，否则还需要从内测或者磁盘中进行读取。当然针对单个页的读取来说，如果页存在于内存中，会比在磁盘中读取效率高很多。
- 批量决定效率。如果我们从磁盘中对单一页进行随机度，那么效率是很低的。而采用顺序读取的方式，批量对页进行读取，平均一页的读取效率就会提升很多，甚至要快于单个页面在内存中的随机读取。

💡使用 SHOW PROFILE

🎯作用和注意事项

SHOW PROFILE 是 MySQL 提供的一种用于查询性能分析的工具。它可以展示查询执行中的各个阶段所消耗的时间。

注意事项:

不是所有的 MySQL 版本和配置都支持 SHOW PROFILE。
使用 SHOW PROFILE 可能会对性能产生轻微的影响。

🎯使用场景

当你需要深入了解一个查询在执行时在哪个阶段花费了最多的时间，这时 SHOW PROFILE 就非常有用。

🎯优缺点
优点:

提供了查询执行的详细分解，帮助定位性能瓶颈。

缺点:

对性能有轻微的影响。
不是所有版本的 MySQL 都支持。

🎯示例代码

SET profiling = 1;
SELECT * FROM your_table;
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;

SET @query_id = 1;
SELECT
	STATE,
	SUM( DURATION ) AS Total_R,
	ROUND( 100 * SUM( DURATION ) / ( SELECT SUM( DURATION ) FROM information_schema.PROFILING WHERE QUERY_ID = @query_id ), 2 ) AS Pct_R,
	SUM( DURATION ) / COUNT(*) AS "R/Call" 
FROM
	information_schema.PROFILING 
WHERE
	QUERY_ID = @query_id 
GROUP BY
	STATE 
ORDER BY
	Total_R DESC
	
-- information_schema.PROFILING 中的每一条记录都是一个步骤的记录，也就是说一个查询 id 在此表中对应了很多条数据
-- 上述功能就是在统计这些数据，并按顺序排列

💡使用 SHOW STATUS

🎯作用和注意事项

SHOW STATUS 提供了 MySQL 服务器的运行状态信息，这些信息可以帮助你诊断问题或优化性能。

注意事项:

提供的信息可能会根据 MySQL 的版本和配置有所不同。

🎯使用场景

当你需要获取关于 MySQL 服务器工作负载或操作的统计信息时。

🎯优缺点
优点:

提供了大量有关服务器状态的信息。
无性能开销。

缺点:

由于提供的信息量很大，可能需要一些经验才能解读和利用这些信息。

🎯示例代码

FLUSH STATUS;
-- 本机没有这个数据库，下面截图替代
SHOW STATUS WHERE VARIABLE_NAME LIKE 'Handler%' OR VARIABLE_NAME LIKE 'Created%';

💡使用慢查询日志

🎯作用和注意事项

慢查询日志记录了执行时间超过指定阈值的查询。

注意事项:

慢查询日志可能会对性能产生影响，特别是当日志量很大时。

🎯使用场景

当你需要找出哪些查询影响了数据库的性能时。

🎯优缺点
优点:

是优化数据库性能的重要工具。
可以精确地找到问题查询。

缺点:

如果不正确地配置，可能会对性能产生影响。

🎯示例代码

在 myf 或 my.ini 配置文件中启用慢查询日志并设置阈值:

slow_query_log=1
slow_query_log_file=/path/to/logfile.log
long_query_time=2

💡使用 Performance Schema

🎯作用和注意事项

Performance Schema 提供了关于 MySQL 服务器性能的详细信息。

注意事项:

使用 Performance Schema 可能会对性能产生影响，尽管这种影响通常很小。

🎯使用场景

当你需要详细的性能指标和统计信息来分析和优化 MySQL 服务器的性能时。

🎯优缺点
优点:

提供了大量的性能相关信息。
非常灵活，可以配置为收集特定的信息。

缺点:

配置和使用可能有些复杂。
可能会对性能产生轻微的影响。

🎯示例代码

启用 Performance Schema（通常在 myf 或 my.ini 中）:

performance_schema = ON

查询某些性能相关信息:

SELECT * FROM performance_schema.events_statements_summary_by_digest;
SELECT
	EVENT_NAME,
	COUNT_STAR,
	SUM_TIMER_WAIT 
FROM
	events_waits_summary_global_by_event_name 
ORDER BY
	SUM_TIMER_WAIT DESC 
	LIMIT 5;

👀SQL 优化

💡CTE 和 SARGable

主题	描述
CTE	- 定义: Common Table Expressions，临时结果集，可以在单个SQL语句中引用。 - 用途: 提高SQL的可读性，允许模块化。 - 优点: 增强查询可读性和维护性，有助于数据库优化。 - 语法: `WITH CTE_Name AS (SELECT ...)`
SARGable	- 定义: 表示查询能够充分利用索引。 - 重要性: 确保查询能够使用索引，避免全表扫描。 - 非SARGable的例子: 在索引列上使用函数、算术运算或前缀为通配符的`LIKE`。 - 建议: 在WHERE子句中避免对索引列使用函数或算术运算。

💡优化 SQL 的一般步骤

优化步骤号	🔍 优化步骤	说明及补充
1. 🔍	发现问题	使用慢查询日志、性能监控工具或其他数据库工具来识别性能问题或瓶颈。监控数据库的响应时间、资源使用情况等。
2. ⚙️	分析执行计划	使用`EXPLAIN`或其他数据库特定的工具来查看查询的执行计划。识别是否充分利用了索引，是否有全表扫描，联接的顺序等。
3. ⚙️	数据库参数配置	适当地配置MySQL参数可以显著提高性能。例如，调整`innodb_buffer_pool_size`以适应工作集，或增加`thread_cache_size`来减少线程创建的开销。
4. 🌐	选择合适的编码	使用正确的字符集可以防止字符转换的开销和可能的数据损坏。例如，使用`utf8mb4`而不是`utf8`来完整支持所有的Unicode字符。
5. 📊	选择合适的数据类型	使用最合适的数据类型可以节省存储空间和I/O开销。例如，如果一个字段只存储0到255的值，使用`TINYINT`而不是`INT`。
6. 🚀	优化索引	考虑添加、删除或修改索引以提高查询性能。考虑使用覆盖索引、联合索引等。
7. 💽	选择适合的存储引擎	不同的存储引擎有不同的性能特点和使用场景。例如，InnoDB支持事务，而MyISAM则在某些只读或大量插入的场景下更快。
8. ✏️	改写SQL	如果索引优化不能解决问题，考虑将查询改写为更高效的形式。例如，使用`JOIN`代替子查询，或者使用`CTE`等。
9. ↔️	数据库水平切分	也称为分表。它是将一个表的数据拆分成多个表，并将这些表存储在不同的数据库或服务器上。例如，将订单表按照日期或订单ID分成多个表。这通常是为了解决单一表中数据量过大的问题，提高查询性能。
10. ↕️	数据库垂直切分	也称为分库。它是将不同的表或列存储在不同的数据库或服务器上。例如，将不常用的数据或大型的数据存储在单独的数据库上。

💡常见问题发现渠道

问题发现方式	描述及补充
用户主动上报应用性能问题	- 性质: 被动 - 描述: 用户在使用应用时遇到性能问题或功能异常，并主动上报。 - 补充: 这种方式通常需要用户的积极参与，可能会因为用户的不满而导致应用的评价下降。
分析慢查询日志	- 性质: 主动 - 描述: 数据库管理员定期或自动化地分析慢查询日志，找出执行缓慢的SQL。 - 补充: 可以为慢查询日志设置时间阈值，例如，执行时间超过1秒的SQL会被记录。
数据库实时监控长时间运行的SQL	- 性质: 主动 - 描述: 使用数据库监控工具实时监控数据库活动，当发现某个SQL运行时间过长时进行警报。 - 补充: 这种方式可以快速发现并响应性能问题，尤其在高流量或关键业务场景中。
应用日志分析	- 性质: 主动 - 描述: 通过分析应用日志，找出可能的数据库问题或其他性能瓶颈。 - 补充: 应用日志可能包括错误、异常、警告等信息，可以帮助识别应用和数据库之间的交互问题。
第三方性能监控工具	- 性质: 主动 - 描述: 使用第三方工具，如New Relic、Datadog等，监控应用和数据库的性能。 - 补充: 这些工具提供了丰富的指标和可视化，可以帮助识别和解决性能问题。

💡什么情况会生成临时表

🔍 使用ORDER BY与GROUP BY的组合，且排序的列并不是分组的列
- 📝 示例:
```
SELECT empno, AVG(salary) FROM t_emp GROUP BY empno ORDER BY hiredate;
```
- 🛠️ 优化方法: 尽量使ORDER BY的列与GROUP BY的列相同
- ✅ 优化后的示例:
```
SELECT empno, AVG(salary) FROM t_emp GROUP BY empno ORDER BY empno;
```

🔍 子查询在FROM子句中

📝 示例:

SELECT empno FROM (SELECT empno FROM t_emp WHERE salary > 5000) as sub;

🛠️ 优化方法: 考虑使用JOIN或其它结构

✅ 优化后的示例:

SELECT empno FROM t_emp WHERE salary > 5000;

🔍 子查询在SELECT子句中

📝 示例:

SELECT empno, (SELECT deptname FROM t_dept WHERE t_dept.deptno = t_emp.deptno) as deptname FROM t_emp;

🛠️ 优化方法: 考虑使用JOIN代替子查询

✅ 优化后的示例:

SELECT t_emp.empno, t_dept.deptname FROM t_emp JOIN t_dept ON t_emp.deptno = t_dept.deptno;

🔍 IN子查询

📝 示例:

SELECT empno FROM t_emp WHERE deptno IN (SELECT deptno FROM t_dept WHERE loc = 'NEW YORK');

🛠️ 优化方法: 考虑使用EXISTS或JOIN

✅ 优化后的示例:

SELECT empno FROM t_emp e WHERE EXISTS (SELECT 1 FROM t_dept d WHERE d.deptno = e.deptno AND loc = 'NEW YORK');

🔍 子查询返回大量数据

📝 示例:

SELECT empno FROM t_emp WHERE empno IN (SELECT empno FROM t_bonus);

🛠️ 优化方法: 考虑使用JOIN或限制子查询结果

✅ 优化后的示例:

SELECT DISTINCT t_emp.empno FROM t_emp JOIN t_bonus ON t_emp.empno = t_bonus.empno;

🔍 使用JOIN操作中的列没有被索引或某些类型的JOIN操作
- 📝 示例:
```
SELECT * FROM t_emp JOIN t_dept ON t_emp.deptno = t_dept.deptno;
```
  (假设deptno没有索引)
- 🛠️ 优化方法: 为JOIN操作中的列建立索引
- ✅ 优化后的示例:
```
ALTER TABLE t_emp ADD INDEX (deptno);
```
🔍 使用了DISTINCT与ORDER BY的组合，且排序的列并不是使用DISTINCT的列
- 📝 示例:
```
SELECT DISTINCT empno FROM t_emp ORDER BY hiredate;
```
- 🛠️ 优化方法: 尝试改变查询结构或考虑是否真的需要DISTINCT和ORDER BY的组合
- ✅ 优化后的示例:
```
SELECT empno FROM t_emp GROUP BY empno ORDER BY empno;
```
🔍 使用了UNION操作
- 📝 示例:
```
SELECT empno FROM t_emp1 UNION SELECT empno FROM t_emp2;
```
- 🛠️ 优化方法: 如果可能，考虑使用UNION ALL（如果知道两个结果集不会有重复值）
- ✅ 优化后的示例:
```
SELECT empno FROM t_emp1 UNION ALL SELECT empno FROM t_emp2;
```
🔍 查询中包含BLOB或TEXT类型的列
- 📝 示例:
```
SELECT empname, bio FROM t_emp WHERE bio LIKE '%some_text%';
```
- 🛠️ 优化方法: 考虑是否真的需要全文搜索这些列，或者使用专门的全文搜索技术
- ✅ 优化后的示例:
```
使用MySQL的全文索引功能或外部工具如Elasticsearch
```

💡什么情况会查询磁盘而不查询内存

💡 大表全扫描
- 📝 示例:
```
SELECT * FROM t_emp;
```
- 🛠️ 描述: 当表非常大并且查询没有使用任何索引，它可能会导致大量的磁盘I/O。
- ✅ 优化方法:
```
SELECT * FROM t_emp WHERE deptno = 10;
```
  (假设deptno有索引)
💡 排序操作
- 📝 示例:
```
SELECT empno, ename FROM t_emp ORDER BY salary;
```
- 🛠️ 描述: 如果需要对大量数据进行排序，并且排序的数据不能完全放入内存中，MySQL可能会使用临时磁盘表。
- ✅ 优化方法: 优化查询结构或考虑增加sort_buffer_size。
💡 临时表
- 📝 示例:
```
SELECT DISTINCT salary FROM t_emp WHERE deptno = 10;
```
- 🛠️ 描述: 某些查询结构（如某些类型的JOIN或子查询）可能导致MySQL创建临时表。
- ✅ 优化方法: 优化查询结构或考虑增加tmp_table_size和max_heap_table_size配置参数。
💡 BLOB/TEXT字段
- 📝 示例:
```
SELECT bio FROM t_emp WHERE empno = 1001;
```
- 🛠️ 描述: 查询大型BLOB或TEXT字段可能会导致额外的磁盘I/O。
- ✅ 优化方法: 考虑是否真的需要在每次查询中都获取这些大字段，或考虑分割这些字段。
💡 不合适的索引
- 📝 示例:
```
SELECT * FROM t_emp WHERE ename LIKE '%JONES%';
```
- 🛠️ 描述: 使用不合适的索引或部分索引可能会导致更多的磁盘I/O。
- ✅ 优化方法: 使用EXPLAIN来分析查询，并根据需要优化或添加索引。

💡SQL 改写原则

SQL改写的原则	说明及注意事项
使用 outer join 代替 not in	- `WHERE`子句中的`NOT IN`和`<>`操作不能使用索引。 - 通过使用`OUTER JOIN`来实现相同的查询逻辑，可以帮助查询优化器更好地使用索引。
使用 CTE 代替子查询	- 公共表表达式（Common Table Expressions，CTE）可以提高SQL的可读性和维护性。 - 在某些情况下，使用CTE可以帮助查询优化器更好地优化查询，从而提高性能。
拆分复杂的大 SQL 为多个简单的小 SQL	- 由于一个SQL查询只能使用一个CPU核心，拆分复杂的大SQL为多个简单的小SQL可以更好地利用多核处理器。 - 这可以帮助并行处理和更快地返回结果，尤其是在处理大数据量时。
巧用计算列优化查询	- 计算列是基于其他列的值计算得到的列。 - 在某些情况下，为计算列创建索引并使用它来进行查询可以提高查询性能。 - 但要注意，计算列的索引可能需要在数据变动时更新，这可能导致额外的性能开销。
避免使用非SARGable表达式	- SARGable是指可以利用索引搜索的查询。 - 避免在索引列上使用函数、算术运算或其他非SARGable表达式，因为这会导致索引无法使用。 - 例如，避免使用`UPPER(column_name)`，而应直接查询大写或小写的值。

💡SQL 改写规则

🎯 全值匹配
- 📌 描述: 最好对索引字段进行全值的精确匹配。
- 📑 示例: WHERE column = 'value'
🚀 联合索引使用
- 📌 描述: 尽可能利用联合索引的所有字段。
- 📑 示例: WHERE col1 = 'value1' AND col2 = 'value2'
📊 最左匹配原则
- 📌 描述: 从联合索引的最左侧字段开始查询，避免跳过字段。
- 📑 示例: WHERE col1 = 'value'
🧱 索引字段操作
- 📌 描述: 避免对索引字段进行函数、计算或类型转换操作。
- 📑 示例: 错误 - WHERE UPPER(column) = 'VALUE'
📈 范围查询顺序
- 📌 描述: 在使用联合索引时，将范围条件放置在最后。
- 📑 示例: WHERE col1 = 'value' AND col2 > 10
🛡️ 覆盖索引
- 📌 描述: 使查询操作只涉及索引列，避免全表扫描。
- 📑 示例: SELECT indexed_column FROM table WHERE column = 'value'
🔎 等值查询
- 📌 描述: 少用不等于操作符，它们可能使索引失效。
- 📑 示例: 错误 - WHERE column != 'value'
🚫 NULL值处理
- 📌 描述: 注意NULL值对索引的影响。
- ⚠️ 注意: 若所有字段均有值，则避免使用IS NULL。
🔗 LIKE查询
- 📌 描述: 避免在LIKE查询中使用前置通配符，这可能会导致索引失效。
- 📑 示例: 错误 - WHERE column LIKE '%value'
✏️ 字符串查询
- 📌 描述: 保证字符串查询中的值被单引号包围。
- 📑 示例: 错误 - WHERE column = value
🔄 数据类型一致性
- 📌 描述: 确保查询条件与列的数据类型匹配，以维持索引效率。
- 📑 示例: 错误 - WHERE int_column = 'string_value'
🌐 OR与UNION
- 📌 描述: 使用UNION代替OR来提高查询效率。
- 📑 示例: SELECT column FROM table WHERE value1 UNION SELECT column FROM table WHERE value2

💡SQL 改写例子

🔍 问题: 返回所有列
- 📝 示例:
```
SELECT * FROM t_emp;
```
- 🛠️ 优化方法: 只返回所需的列
- ✅ 优化后的示例:
```
SELECT ename, deptno FROM t_emp;
```
🔍 问题: 只能从最左侧使用索引键
- 📝 示例:
```
WHERE c = 'value' (在`a_b_c`的联合索引中)
```
- 🛠️ 优化方法: 使用索引的前缀列
- ✅ 优化后的示例:
```
WHERE a = 'some_value' AND c = 'value';
```

🔍 问题: 这些操作可能导致 MySQL 跳过索引并进行全表查询

📝 示例:

SELECT ename FROM t_emp WHERE comm IS NOT NULL

🛠️ 优化方法: 避免使用与NULL值相关的判断，或者为列设置非空约束。

✅ 优化后的示例:

UPDATE t_emp SET comm = 0 WHERE comm IS NULL; 
ALTER TABLE t_emp MODIFY comm INT NOT NULL DEFAULT 0; 
SELECT ename FROM t_emp WHERE comm >= 0;

🔍 问题: 使用NOT IN 和 IN的查询条件无法利用索引

📝 示例:

WHERE column_name IN ('value1', 'value2')

🛠️ 优化方法: 使用UNION ALL 来替代IN

✅ 优化后的示例:

SELECT column_name FROM table_name WHERE column_name = 'value1' 
UNION ALL 
SELECT column_name FROM table_name WHERE column_name = 'value2';

🔍 问题: 使用 !=和<> 运算符

📝 示例:

SELECT ename FROM t_emp WHERE deptno != 20;

🛠️ 优化方法: 使用范围查询

✅ 优化后的示例:

SELECT ename FROM t_emp WHERE deptno > 20 OR deptno < 20;

🔍 问题: OR操作导致索引失效

📝 示例:

SELECT ename FROM t_emp WHERE deptno = 20 OR deptno = 30;

🛠️ 优化方法: 使用UNION将查询拆分

✅ 优化后的示例:

SELECT ename FROM t_emp WHERE deptno = 20 
UNION ALL 
SELECT ename FROM t_emp WHERE deptno = 30;

🔍 问题: 对索引列使用函数
- 📝 示例:
```
WHERE UPPER(column_name) = 'VALUE'
```
- 🛠️ 优化方法: 避免在查询中使用函数
- ✅ 优化后的示例:
```
WHERE column_name = 'value';
```
🔍 问题: %在模糊匹配的开头
- 📝 示例:
```
SELECT ename FROM t_emp WHERE ename LIKE '%S%';
```
- 🛠️ 优化方法: 避免使用以%开头的模糊查询
- ✅ 优化后的示例: (考虑使用全文搜索或其他方法)
🔍 问题: 数据类型转换导致性能下降
- 📝 示例:
```
SELECT ename FROM t_emp WHERE deptno='20';
```
- 🛠️ 优化方法: 保持查询条件的数据类型与列的数据类型一致
- ✅ 优化后的示例:
```
SELECT ename FROM t_emp WHERE deptno=20;
```
🔍 问题: 在表达式左侧使用运算符和函数
- 📝 示例:
```
SELECT ename FROM t_emp WHERE salary * 12 > 100000;
```
- 🛠️ 优化方法: 重新组织表达式
- ✅ 优化后的示例:
```
SELECT ename FROM t_emp WHERE salary > 8333;
```
🔍 问题: 不使用索引进行排序
- 📝 示例:
```
ORDER BY column_name;
```
- 🛠️ 优化方法: 为经常需要排序的列创建索引
- ✅ 优化后的示例: (此为策略建议，无具体的SQL示例)
🔍 问题: 修改已索引的列的数据
- 📝 示例:
```
UPDATE table_name SET indexed_column = 'new_value';
```
- 🛠️ 优化方法: 考虑更新操作前的影响
- ✅ 优化后的示例: (注意：此处优化方法主要是注意和策略，可能没有具体的SQL示例)
🔍 问题: 使用 IN 进行子查询
- 📝 示例:
```
SELECT ename FROM t_emp WHERE deptno IN (SELECT deptno FROM t_dept WHERE loc = 'NEW YORK');
```
- 🛠️ 优化方法: 考虑使用 EXISTS，尤其当子查询返回大量数据时
- ✅ 优化后的示例:
```
SELECT ename FROM t_emp WHERE EXISTS (SELECT 1 FROM t_dept WHERE t_dept.deptno = t_emp.deptno AND loc = 'NEW YORK');
```
- 💡 注意事项: EXISTS可以高效地使用索引，一旦找到匹配项即停止搜索。
🔍 问题: 使用 NOT IN 进行子查询，尤其当子查询中可能有NULL值
- 📝 示例:
```
SELECT ename FROM t_emp WHERE deptno NOT IN (SELECT deptno FROM t_dept WHERE loc = 'NEW YORK');
```
- 🛠️ 优化方法: 考虑使用 NOT EXISTS
- ✅ 优化后的示例:
```
SELECT ename FROM t_emp WHERE NOT EXISTS (SELECT 1 FROM t_dept WHERE t_dept.deptno = t_emp.deptno AND loc = 'NEW YORK');
```
- 💡 注意事项: NOT EXISTS可以利用索引来快速确定是否有满足条件的记录。
  1. 如果查询的两个表大小相当，使用 IN 和 EXISTS 差别不大；
  2. 如果两个表中一个较小，一个较大，则子查询表大的用 EXISTS，子查询小的使用 IN；
  3. 不论表的大小，NOT EXISTS 通常比 NOT IN 更快，因为 NOT IN 可能会导致全表扫描而没有使用索引，而 NOT EXISTS 的子查询依然能够利用表上的索引。

💡局限性及优化策略

✍ 策略/局限性

📝 示例:

SELECT department_name, 
       (SELECT COUNT(*) FROM employees WHERE department_id = departments.id) 
AS employee_count 
FROM departments;

🛠️ 优化策略: 转换为 JOIN 查询

✅ 优化后的示例:

SELECT departments.department_name, 
       COUNT(employees.id) AS employee_count 
FROM departments 
LEFT JOIN employees ON departments.id = employees.department_id 
GROUP BY departments.department_name;

🏗️ 索引优化子查询

📝 示例:

SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE name = 'John');

🛠️ 优化策略: 为子查询中的列添加索引

✅ 优化后的示例:

CREATE INDEX idx_customer_name ON customers(name);

🚀 UNION 与临时表

📝 示例:

SELECT name FROM customers WHERE state = 'CA' 
UNION 
SELECT name FROM suppliers WHERE state = 'CA';

🛠️ 优化策略: 使用 UNION ALL

✅ 优化后的示例:

SELECT name FROM customers WHERE state = 'CA' 
UNION ALL 
SELECT name FROM suppliers WHERE state = 'CA';

🗂️ 合并多个索引

📝 示例:

SELECT * FROM products WHERE category_id = 5 OR brand_id = 10;

🛠️ 优化策略: 为每个条件创建单独的索引

✅ 优化后的示例:

CREATE INDEX idx_category ON products(category_id); 
CREATE INDEX idx_brand ON products(brand_id);

📈 等值传递索引

📝 示例:

SELECT * FROM orders WHERE order_id = 100 AND customer_id = 10;

🛠️ 优化策略: 为每个等值条件创建索引

✅ 优化后的示例:

CREATE INDEX idx_order_id ON orders(order_id); 
CREATE INDEX idx_customer_id ON orders(customer_id);

🔄 查询并行执行
- 📝 示例:
```
SELECT * FROM products WHERE category_id BETWEEN 1 AND 10;
```
  和
```
SELECT * FROM products WHERE category_id BETWEEN 11 AND 20;
```
- 🛠️ 优化策略: 手动拆分查询成多个子查询，然后并行执行
- ✅ 优化后的示例: (这是策略性的建议，无具体的SQL示例)
🧮 哈希关联
- 📝 示例: (此为策略建议，无具体的SQL示例)
- 🛠️ 优化策略: 将哈希关联的逻辑移到应用层
- ✅ 优化后的示例: (这是策略性的建议，无具体的SQL示例)

📊 松散索引扫描

📝 示例:

SELECT category, COUNT(*) FROM products GROUP BY category;

🛠️ 优化策略: 确保使用了索引

✅ 优化后的示例:

CREATE INDEX idx_category ON products(category);

⏱️ 最大/最小值索引
- 📝 示例:
```
SELECT MIN(order_date) FROM orders;
```
- 🛠️ 优化策略: 为查询列创建索引
- ✅ 优化后的示例: (这是策略性的建议，无具体的SQL示例)

🔄 查询与更新同一表

📝 示例:

UPDATE orders SET total_price = (SELECT SUM(price) FROM orders WHERE id = 100) WHERE id = 100;

🛠️ 优化策略: 使用生成表的形式来绕过上面的限制

✅ 优化后的示例:

UPDATE orders, (SELECT SUM(price) AS sum_price FROM orders WHERE id = 100) AS derived_table 
SET orders.total_price = derived_table.sum_price 
WHERE orders.id = 100;

👀SQL 查询日志

💡配置慢查询日志

步骤号	操作	详细描述
1.	打开慢查询日志	在MySQL的配置文件中设置 `slow_query_log = 1` 以启用慢查询日志。
2.	指定慢查询日志文件	在配置文件中设置 `slow_query_log_file = /path/to/your/logfile.log` 来指定日志文件的保存位置。确保MySQL服务器进程有写该路径的权限。
3.	设置慢查询时间阈值	通过设置 `long_query_time = 2` (例如)，您可以定义一个查询被认为是“慢”的时间阈值。这意味着所有执行时间超过2秒的查询都会被记录。
4.	记录没有使用索引的查询	如果想记录那些即使执行时间很短但没有使用索引的查询，可以设置 `log_queries_not_using_indexes = 1`。
5.	重新启动MySQL服务器	在修改配置文件后，使用 `service mysql restart` 或适用于您的操作系统的其他命令来重新启动MySQL服务器。
6.	监视和分析慢查询日志	启用慢查询日志后，可以定期检查和分析慢查询日志文件，找出那些需要优化的查询。

💡分析 MySQL 慢查询日志

✍Percona toolkit 的使用

[root@study local]# yum install https://repo.percona/yum/percona-release-latest.noarch.rpm
[root@study local]# yum install percona-toolkit-3.2.0-1.el7.x86_64.rpm 
# 上述下载的包如果安装不了，就只能在线安装了
[root@study local]# yum install percona-toolkit

✍配置 MySQL 慢查询日志

-- 先查询目前的时间
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
-- 我们本机开发呢，设置为 0 记录是由的 sql
-- 工作中，一般设置为 0.001 秒
mysql> set global long_query_time = 0;
-- 查询慢查询日志位置
mysql> show variables like 'slow_query_log_file';
-- 查询和打开慢查询日志
mysql> show variables like 'slow_query_log';
mysql> set global slow_query_log = on;

✍mysqldumpslow

mysqldumpslow 是 MySQL 提供的一个工具，用于解析和摘要 MySQL 慢查询日志。它能帮助您更容易地找出慢查询的模式，从而优化它们。

以下是如何使用 mysqldumpslow 的基本步骤和常见选项：

🎯🎯使用方法：

基本命令格式为：

mysqldumpslow [OPTIONS] [LOGFILE...]

🎯🎯常见选项：

-s：排序的方式。可用的排序方式包括：
- t：按照查询时间排序（默认）。
- l：按照锁定时间排序。
- r：按照返回的记录数排序。
- c：按照查询出现的次数排序。
- al：按照平均锁定时间排序。
- ar：按照平均返回的记录数排序。
- at：按照平均查询时间排序。
-t：显示前N个查询。
-g：仅显示匹配给定正则表达式的查询。
-l：不解析锁定时间。
-h：仅显示来自给定主机的查询。

🎯🎯示例：

显示最慢的10个查询：

mysqldumpslow -s t -t 10 /path/to/your-slow-query.log

显示锁定时间最长的5个查询：

mysqldumpslow -s l -t 5 /path/to/your-slow-query.log

显示包含特定模式的查询：
```
mysqldumpslow -g "pattern" /path/to/your-slow-query.log
```
其中，pattern 是您要搜索的正则表达式。

🎯🎯注意：

如果您未指定日志文件路径，mysqldumpslow 将尝试从默认的慢查询日志位置读取。
为了有效地使用 mysqldumpslow，确保您已经启用了慢查询日志并正确配置了其路径。

使用 mysqldumpslow 可以帮助您更快地识别和解决慢查询问题。希望这些信息对您有所帮助！如果您有其他问题或需要进一步的解释，请告诉我。

✍pt-query-digest

pt-query-digest 是 Percona Toolkit 的一部分，它是一个更加先进的慢查询日志分析工具。与 mysqldumpslow 相比，pt-query-digest 提供了更丰富的功能、更详细的报告以及更多的过滤和分组选项。

以下是如何使用 pt-query-digest 的基本指南：

🎯🎯1. 安装：

首先，您需要安装 Percona Toolkit。对于大多数 Linux 发行版，您可以使用包管理器进行安装。例如，对于 Ubuntu/Debian：

sudo apt-get install percona-toolkit

对于 RedHat/CentOS：

sudo yum install percona-toolkit

🎯🎯2. 使用方法：

基本命令格式为：

pt-query-digest [OPTIONS] [LOGFILE]

🎯🎯3. 常见选项：

--limit：设置输出的查询数量。例如，--limit=10% 表示输出前10%的查询。
--since 和 --until：限制分析的时间范围。
--filter：过滤查询。您可以使用 Perl 代码作为过滤条件。例如，仅显示查询时间大于1秒的查询：--filter '$event->{Query_time} > 1'。
--order-by：指定排序方式。默认是按查询时间 (Query_time) 排序，但您可以选择其他的，如 Lock_time、Rows_sent 等。

🎯🎯4. 示例：

分析指定的慢查询日志：

pt-query-digest /path/to/your-slow-query.log

分析最近一天的查询：

pt-query-digest --since '24h ago' /path/to/your-slow-query.log

仅显示查询时间大于1秒的查询：

pt-query-digest --filter '$event->{Query_time} > 1' /path/to/your-slow-query.log

🎯🎯5. 输出：

pt-query-digest 的输出通常包括：

摘要：显示日志的时间范围、查询数量、不同的查询数量等。
每个查询的报告：显示查询的摘要、响应时间分布、表、索引等。
全局统计：显示所有查询的总体统计信息。

🎯🎯6. 注意：

pt-query-digest 可以分析多种来源的数据，不仅仅是慢查询日志。例如，它可以分析 tcpdump、general logs、binary logs 等。
该工具的输出相当详细，可能会很长，因此您可能希望将其重定向到一个文件进行进一步的分析。

pt-query-digest 是一个非常强大的工具，它为分析和优化MySQL查询提供了广泛的功能。如果您需要进一步的指导或有其他疑问，请告诉我。

✍详细的字段含义

🎯🎯1. 总体统计结果

Overall：总共有多少条查询
Time range：查询执行的时间范围
unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询
total：总计   min：最小   max：最大  avg：平均
95%：把所有值从小到大排列，位置位于 95% 的那个数，这个数一般最具有参考价值
median：中位数，把所有值从小到大排列，位置位于中间那个数
  
# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 340ms user time, 140ms system time, 23.99M rss, 203.11M vsz
# 工具执行时间
# Current date: Fri Nov 25 02:37:18 2016
# 运行分析工具的主机名
# Hostname: localhost.localdomain
# 被分析的文件名
# Files: slow.log
# 语句总数量，唯一的语句数量，QPS，并发数
# Overall: 2 total, 2 unique, 0.01 QPS, 0.01x concurrency ________________
# 日志记录的时间范围
# Time range: 2016-11-22 06:06:18 to 06:11:40
# 属性                总计      最小     最大    平均     95%  标准    中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time             3s   640ms      2s      1s      2s   999ms      1s
# 锁占用时间
# Lock time            1ms       0     1ms   723us     1ms     1ms   723us
# 发送到客户端的行数
# Rows sent              5       1       4    2.50       4    2.12    2.50
# select语句扫描行数
# Rows examine     186.17k       0 186.17k  93.09k 186.17k 131.64k  93.09k
# 查询的字符数
# Query size           455      15     440  227.50     440  300.52  227.50

🎯🎯2. 查询分组统计结果

Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定
Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
Response：总的响应时间
time：该查询在本次分析中总的时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：响应时间Variance-to-mean的比率
Item：查询对象

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0xF9A57DD5A41825CA  2.0529 76.2%     1 2.0529  0.00 SELECT
#    2 0x4194D8F83F4F9365  0.6401 23.8%     1 0.6401  0.00 SELECT wx_member_base

🎯🎯3. 每种查询的详细统计结果

# Query 1: 这是第一个查询的统计。它有0次每秒的查询(QPS)和0的并发度。
# ID 0xF9A57DD5A41825CA at byte 802: 这是查询的ID和它在日志文件中的位置。
# 这个项目被包含在报告中是因为它匹配了--limit参数。
# Scores: 这是查询的得分，与查询的性能有关。
# Time range: 这表示查询的时间范围。
# 接下来的表格显示了查询的各种统计数据。
# Count: 查询的执行次数。
# Exec time: 查询的执行时间。
# Lock time: 查询的锁定时间。
# Rows sent: 发送到客户端的行数。
# Rows examine: 查询扫描的行数。
# Query size: 查询的大小（以字符为单位）。
# 下面的字符串部分提供了查询的其他信息。
# Databases: 查询涉及的数据库。
# Hosts: 执行查询的主机。
# Users: 执行查询的用户。
# Query_time distribution: 这是查询时间的分布图。
# 最后，我们有实际的SQL查询语句。
# EXPLAIN: 这是SQL查询的解释。
select sleep(2)\G

# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 802 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-11-22 06:11:40
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         50       1
# Exec time     76      2s      2s      2s      2s      2s       0      2s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent     20       1       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size     3      15      15      15      15      15       0      15
# String:
# Databases    test
# Hosts        192.168.8.1
# Users        mysql
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(2)\G

👀SQL 的执行计划

🛠️ 能干什么？

📊 表的读取顺序 (id)
🔍 数据读取操作的操作类型 (select_type)
🗝️ 可使用的索引 (possible_keys)
🚀 实际使用的索引 (key)
🧮 查询优化器查询的行数 (rows)

关键点	描述	为什么重要
访问方法	描述SQL如何访问表中的数据	通过了解访问方法，我们可以确定查询是否有效地使用数据。例如，全表扫描可能比使用索引查找要慢得多。
索引使用	描述SQL如何使用表中的索引	如果查询没有使用正确的索引，或者完全没有使用索引，那么它可能会非常慢。了解索引的使用可以帮助我们调整查询或索引以提高性能。
查询类型	描述SQL使用的查询类型，如子查询、关联查询等	不同的查询类型可能有不同的性能特点。了解查询的具体类型可以帮助我们确定是否可以通过改变查询策略来提高性能。

📚 官方参考文档

官方文档提供详细指导和用法。

🎲 怎么玩？

📝 基本语法:
```
EXPLAIN [查询语句]
```

🌟 使用示例:

要分析的查询语句:

SELECT * FROM employees WHERE department_id = 10;

获取执行计划:

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

🔍 输出解释:
- id: 查询的唯一标识符。
- select_type: 查询类型，如 SIMPLE, SUBQUERY, UNION 等。
- table: 查询中涉及的表名。
- type: 访问类型，指明了查找表中行的方法，如 ALL, index, range 等。
- possible_keys: 表中可能用于执行此查询的索引。
- key: 实际使用的索引。
- key_len: 使用的索引的字节数。
- ref: 用于索引查找的列或常数。
- rows: 估计需要检查的行数。
- Extra: 查询的额外信息。
🛠️ 优化提示:
- type 列显示为 ALL 或 index 时可能需要优化，表示全表扫描或索引全扫描。
- possible_keys 和 key 列为空时，没有索引被使用，应考虑添加索引。
- rows 列的值很高时，查询或索引可能需要优化。
🧩 进阶使用:
- 🔎 使用 EXPLAIN extended 获取更多信息。
- 📐 使用 EXPLAIN partitions 来查看涉及的分区信息。

📝 构建SQL环境语句

构建好环境后，可详细解读 EXPLAIN 的各个字段。

-- 创建演员表，并为name字段创建索引
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `film`;  
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT, 
`name` varchar(10) DEFAULT NULL, 
PRIMARY KEY (`id`), 
KEY `idx_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2'); 

DROP TABLE IF EXISTS `film_actor`; 
CREATE TABLE `film_actor` (  
`id` int(11) NOT NULL,  
`film_id` int(11) NOT NULL,  
`actor_id` int(11) NOT NULL,  
`remark` varchar(255) DEFAULT NULL,  
PRIMARY KEY (`id`),  KEY `idx_film_actor_id` (`film_id`,`actor_id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

🆔 id字段

🔢 指示 SQL 操作的执行顺序。
🔄 相同 id 的多行意味着嵌套循环。
🔄 id相同：执行顺序由上到下，顺序执行。
🔢 id不同：子查询中 id 递增，值越大优先级越高。逆序执行。
🧐 id相同又不同：id 为 NULL 最后执行。

🗂️ `select_type、table`字段

🧐 select_type: 表示 SQL 查询中对应行的查询类型。
📘 具体类型:
- 🟢 SIMPLE: 简单查询，无子查询或 UNION。
- 🔵 PRIMARY: 包含子查询的最外层查询。
- 🟣 SUBQUERY: SELECT 列表中的子查询。
- 🟠 DEPENDENT SUBQUERY: 依赖外部结果的子查询。
- 🟡 UNION: UNION 的第二个或后续查询。
- ⚫️ DEPENDENT UNION: 作为子查询的 UNION 的依赖部分。
- 🟤 UNION RESULT: UNION 的结果。
- 🟢 DERIVED: 来自 FROM 子句的派生表查询。
📋 表的标识:
- table:
  - 🗂️ 指示哪个表输出了执行计划的数据。
  - 🚀 查询涉及多个表时，每个表都单独列出。

# 关闭mysql5.7新特性对衍生表的合并优化
mysql> set session optimizer_switch='derived_merge=off';
mysql> explain select (select 1 from actor where id = 1) from 
(select * from film where id = 1) der;
#还原默认配置
mysql> set session optimizer_switch='derived_merge=on';

🚦 `type`字段

📈 type 字段:
代表 MySQL 执行查询时的访问类型或关联类型，也就是 MySQL 如何查找表中的行。
🏆 性能从好到坏:
- system: 表中只有一行，是 const 类型的特例。
- const: 通过主键或唯一索引与常量比较来检索行，通常速度最快。
- eq_ref: 对于每个索引键，表中只有一行匹配，通常与 const 类型相似。
- ref: 使用非唯一索引或唯一索引的一部分来检索多行。
- range: 使用索引检索给定范围的多行，通常用于 BETWEEN、<、> 等操作。
- index: 扫描索引以避免扫描表的数据行，比 ALL 好，但通常不如 range 或 ref。
- ALL: 全表扫描，通常是性能最差的选择。
🛠 优化策略:
- 确保查询至少达到 range 级别，最好是 ref 或更好。
- 尝试避免 ALL 类型的查询，通过添加索引优化。
📝 不同类型解释:
- NULL: 在优化阶段解析的查询，不需要再访问表或索引。
- const, system: 主键或唯一索引完全匹配常量的查询，非常快速。
- eq_ref: 对主键或唯一索引的完整匹配进行连接查询，通常只返回一行数据。
- ref: 对非唯一索引的匹配进行连接查询，可能返回多行。
- range: 索引用于检索特定范围内的行。
- index: 使用覆盖索引，遍历索引而不是数据行。
- ALL: 全表扫描，应尽可能通过添加合适的索引来避免。

🔑 `possible_keys、key`列字段

🗝️ possible_keys 列显示可能使用的索引。
🚀 key 列显示实际使用的索引。

🔢 `key_len`字段

📐 key_len 字段:
显示 MySQL 在索引中使用的字节数。此值可以用来判断查询使用了索引中的哪些列。
🗝 索引使用示例:
- 假设 film_actor 表的 idx_film_actor_id 联合索引包含 film_id 和 actor_id 两个 int 类型列。
- 如果 key_len 为 4，意味着查询只使用了 film_id 列的索引。
🧮 key_len 计算规则:
- 字符串:
  - char(n) 和 varchar(n) 中的 n 表示字符数，不是字节数。
  - char(n): 存储汉字长度为 3n 字节（UTF-8 编码下）。
  - varchar(n): 存储汉字长度为 3n + 2 字节，额外的 2 字节用于存储字符串长度。
- 数值类型:
  - tinyint: 1 字节
  - smallint: 2 字节
  - int: 4 字节
  - bigint: 8 字节
- 时间类型:
  - date: 3 字节
  - timestamp: 4 字节
  - datetime: 8 字节
- 如果字段可为 NULL，则额外需要 1 字节来记录 NULL 状态。
📊 使用建议:
- 索引长度越短，查询通常越快。尽量避免索引过长的列，特别是在频繁查询的路径上。
- 设计索引时，应尽量前置过滤效果更好的列，以减少 key_len，提高查询效率。

📌 `ref、rows`字段

ref 字段显示用于索引中查找的列或常量。
rows 字段显示优化器估计的要读取的行数。

📉 `filtered`:

🔍 预估的过滤后的行数百分比。

📋 `Extra`字段

Using index:
- 📊 指使用了覆盖索引，即查询所需的所有数据都可以直接从索引中获取，无需访问表中的数据行。
- 🗂️ 适用于辅助索引，查询过程中不必访问数据文件，提高查询效率。
Using where:
- 🛠️ 意味着 MySQL 在索引检索后还需进行额外的过滤操作，因为索引未能覆盖 WHERE 子句中的所有条件。
Using index condition:
- 🎯 表明查询正在使用索引条件下推（Index Condition Pushdown），在索引层面过滤数据，减少数据到服务器层的传输。
Using temporary:
- 📝 指 MySQL 为了处理查询（如排序、分组）而创建了临时表。
- ⚠️ 注意: 这通常指出查询可能需要优化，因为临时表可能会影响性能。
Using filesort:
- 🔄 表示 MySQL 需要进行外部排序以满足查询的 ORDER BY 条件，可能在内存或磁盘上进行。
- ⚠️ 注意: 这通常不是使用索引排序，可能需要优化。
Select tables optimized away:
- 🚀 表明 MySQL 能够在优化阶段就解决查询，通常是使用索引来直接获取到 MIN 或 MAX 等聚合函数的结果，无需执行完整的表扫描。

⚠️ 注意: EXPLAIN 提供了查询优化的重要线索，但结果是基于估计，不是精确数值。实际执行可能与 EXPLAIN 输出有所差异。

💽 explain 列含义汇总整理

列名	描述
`id`	表示查询中的 SQL 数据对数据库对象操作的顺序。当 ID 相同时由上到下执行，当 ID 不同时，由大到小执行。
`select_type`	查询类型，如：SIMPLE、PRIMARY、SUBQUERY、DEPENDENT SUBQUERY、UNION、DEPENDENT UNION、UNION RESULT、DERIVED 等。
`table`	表示执行计划表中的数据是由哪个表输出的。
`type`	连接类型，如：system、const、eq_ref、ref、ref_or_null、index_merge、range、index、ALL 等。
`possible_keys`	可能使用的索引。
`key`	实际使用的索引。
`key_len`	实际使用索引的最大长度。
`ref`	指出哪些列或常量被用于索引查找。
`rows`	根据统计信息预估的扫描的行数。在关联查询中，也表示内嵌循环的次数。
`filtered`	表示返回结果的行数占需要读取行数（rows）的百分比。
`Extra`	包含了不适合在其他列中显示的一些额外信息，如：Distinct、Not exists、Using filesort、Using index、Using temporary、Using where、select tables optimizedaway 等。

👀诊断间歇性问题

💡单条查询问题还是服务器问题

当数据库性能出现问题时，首先要确定问题的范围。是整个数据库服务器都受到影响，还是只有某些特定的查询？确定这一点是解决问题的第一步。

如何确定问题的范围呢？可以使用以下技术：

✍使用 SHOW GLOBAL STATUS

SHOW GLOBAL STATUS 是一种强大的命令，可以为你提供MySQL服务器当前的各种状态信息。对于诊断问题，尤其是间歇性问题，这个命令非常有用。

如何使用:
为了捕获可能的尖刺或凹陷，你需要在短时间内多次运行这个命令，并观察某些关键指标的变化。

示例:
考虑以下脚本，它每秒查询一次状态，并输出你关心的那些指标：

while true; do
  mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_running';"
  mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';"
  mysql -e "SHOW GLOBAL STATUS LIKE 'Questions';"
  mysql -e "SHOW GLOBAL STATUS LIKE 'Queries';"
  sleep 1
done

解释:

Threads_running: 显示当前正在运行的非休眠线程的数量。这可以帮助你了解是否有很多查询在并发运行。
Threads_connected: 显示当前连接到MySQL服务器的线程数。一个突然的增加可能意味着有一个大量的并发连接请求。
Questions: 自服务器启动以来的查询数量。监视这个数字的快速增加可以帮助你识别查询率的突然增加。
Queries: 同上，但也包括服务器内部的查询。

注意:

在生产环境中，你可能不想每秒都运行这样的脚本，因为它可能会导致额外的开销。但是，对于短期的问题诊断，这是可以接受的。
如果你认为存在一个特定的问题模式，例如在每天的特定时间，你可以修改脚本，让它在这段时间内运行。

使用 SHOW GLOBAL STATUS 可以为你提供宝贵的信息，帮助你迅速定位问题。当然，解释这些指标并确定它们的正常范围需要一些经验，但这是一个很好的起点。

✍mysqladmin 的使用

mysqladmin 是MySQL服务器的命令行工具，可以用来显示服务器的状态和各种变量。

示例:

mysqladmin [option] command [command option] command ......

-c number 自动运行次数统计，必须和 -i 一起使用
-i number 间隔多长时间重复执行
每个两秒查看一次服务器的状态，总共重复5次。
./mysqladmin -uroot -p -i 2 -c 5 status
-h, --host=name Connect to host. 连接的主机名或iP
-p, --password[=name] 登录密码，如果不写于参数后，则会提示输入
-P, --port=# Port number to use for connection. 指定数据库端口
-s, --silent Silently exit if one can't connect to server.
-S, --socket=name Socket file to use for connection. 指定socket file
-i, --sleep=# Execute commands again and again with a sleep between. 间隔一段时间执行一次
-u, --user=name User for login if not current user.登录数据库用户名
-v, --verbose Write more information. 写更多的信息
-V, --version Output version information and exit. 显示版本

相关命令
mysqladmin password dadong123                  #<==设置密码，前文用过的。
mysqladmin -uroot -pdadong123 password dadong  #<==修改密码，前文用过的。
mysqladmin -uroot -pdadong123 status           #<==查看状态，相当于show status。
mysqladmin -uroot -pdadong123 -i 1 status      #<==每秒查看一次状态。
mysqladmin -uroot -pdadong123 extended-status   #<==等同show global status;。
mysqladmin -uroot -pdadong123 flush-logs        #<==切割日志。
mysqladmin -uroot -pdadong123 processlist       #<==查看执行的SQL语句信息。
mysqladmin -uroot -pdadong123 processlist -i 1  #<==每秒查看一次执行的SQL语句。
mysqladmin -uroot -p'dadong' shutdown           #<==关闭mysql服务，前文用过的。
mysqladmin -uroot -p'dadong' variables          #<==相当于show variables。

这会返回一行关于服务器状态的简要信息，如Uptime、Threads、Queries等。

✍使用 SHOW PROCESSLIST

SHOW PROCESSLIST 是一个在MySQL中经常使用的命令，它显示了MySQL服务器上当前正在运行的所有线程的信息。这可以帮助你了解当前服务器上发生了什么，尤其是当你试图找出导致性能下降的原因时。

如何使用:
使用 SHOW PROCESSLIST 来获取当前运行的线程列表。

示例:

SHOW PROCESSLIST;

解释:

每个线程都会显示其状态，这可以帮助你了解查询在做什么。例如，Sleep 状态表示线程正在等待新的命令，而 Locked 状态表示查询正在等待一个锁。
如果你看到大量的线程都在等待锁，这可能意味着你有一个锁争用问题，特别是在使用MyISAM存储引擎时。
SHOW PROCESSLIST 的输出还包括每个查询运行了多长时间，这可以帮助你找出那些运行时间过长的查询。

高级技巧:

你可以使用命令行工具和一些文本处理命令来分析 SHOW PROCESSLIST 的输出，例如:

mysql -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn

这个命令将显示每种状态的线程数，按数量降序排列。这可以帮助你迅速确定大多数线程正在做什么。

注意:

如果你看到大量的线程都在 freeing items 状态，这可能是一个指标，表明你有一些查询正在产生大量的临时数据。
SHOW PROCESSLIST 只显示当前的线程。如果你的服务器经常有性能问题，但你不能实时监控它，你可能需要使用其他工具或技术来长时间地收集数据。

总的来说，SHOW PROCESSLIST 是一个强大的工具，可以帮助你快速了解MySQL服务器的当前状态，并帮助你找出可能的性能瓶颈。但是，为了更深入地了解问题，你可能还需要使用其他工具和技术。

✍使用查询日志

MySQL的查询日志记录了所有执行的查询。如果启用了查询日志，可以查看它来找出运行缓慢的查询。

示例:

首先，确保查询日志已启用：

SET GLOBAL general_log = 'ON';

然后查看日志文件（文件的位置可以在myf或my.ini中找到）：

cat /path/to/query.log

在日志中，你可以查找运行时间长的查询，或在特定时间段内执行的查询。

结论:

通过上述技术，你可以收集大量关于服务器状态和运行的查询的信息。这些信息可以帮助你确定问题的范围，并找出可能的原因。当你收集了足够的信息后，可以开始进一步的分析和优化。

👀随便提一下

💡MySQL 查询状态简介

在MySQL中，连接或线程的状态描述了MySQL当前正在处理的操作。以下是一些常见状态的概述：

Sleep：线程处于空闲状态，等待客户端发出新请求。
Query：线程正在执行查询或向客户端发送结果。
Locked：线程在MySQL服务器层等待表锁。这在没有行锁的引擎（如MyISAM）中常见。
Analyzing and statistics：线程正在收集统计信息并为查询生成执行计划。
Copying to tmp table [on disk]：线程正在将查询结果复制到临时表中。如果带有on disk，则内存临时表正在转移到磁盘。
Sorting result：线程正在对查询结果排序。
Sending data：线程可能在多个状态间切换，生成结果集或向客户端返回数据。

💡MySQL 查询缓存简介

MySQL查询缓存用于存储查询结果集，以快速返回相同查询的结果，避免重新执行查询。

✍核心特性：

速度：对于不经常变化的数据，查询缓存提供了显著的性能提升。
匹配机制：仅完全匹配的查询才能使用缓存。
自动失效：相关数据表的任何更改都会使缓存失效。

✍优势：

提高读密集型应用的性能。
减少数据库的计算和磁盘I/O。

✍局限性：

对于频繁更改的数据，缓存效果不佳。
维护缓存带来的开销。

✍配置与使用：

query_cache_size定义缓存大小。
query_cache_type控制查询缓存的行为。
使用SQL_NO_CACHE指示查询不使用缓存。

✍备注：

从 MySQL 8.0 开始，MySQL 查询缓存已被移除，这是因为查询缓存在某些情况下可能导致性能问题，而且它不适用于现代的高并发数据库工作负载。取而代之，MySQL 提供了其他性能优化和缓存机制，以更好地满足不同类型的查询需求：
1. InnoDB Buffer Pool：对于 InnoDB 存储引擎，主要的数据和索引缓存是 InnoDB Buffer Pool。这个缓存池存储了磁盘上的数据页的拷贝，可以显著提高读取性能。你可以通过配置 innodb_buffer_pool_size 参数来调整它的大小。
2. Query Cache Removal and Caching SHA-256 Queries：MySQL 8.0 引入了新的查询缓存替代方案，使用 Caching SHA-256 Queries。这种机制会缓存查询的哈希值以及查询结果的哈希值，而不是实际的查询文本。这可以减少查询缓存带来的性能开销，并提供更好的灵活性。
3. Table and Index Statistics：MySQL 使用统计信息来优化查询执行计划。通过收集表和索引的统计信息，MySQL 查询优化器可以更好地选择有效的执行计划。你可以使用 ANALYZE TABLE 命令手动更新表的统计信息。
4. Query Optimization：MySQL 查询优化器本身也在不断改进，可以更好地处理各种查询，并选择更有效的执行计划。这包括了索引选择、连接算法等方面的改进。
5. 使用缓存层：在某些情况下，可以考虑使用缓存层，如 Memcached 或 Redis，来缓存常用查询的结果。这些缓存层可以显著减轻数据库负载，特别是对于读密集型应用。
总之，MySQL 8.0 引入了更现代和有效的查询优化和缓存机制，以取代旧的查询缓存机制。根据应用程序的需求和工作负载类型，可以选择合适的性能优化方法。在大多数情况下，合理配置 InnoDB Buffer Pool 和利用 MySQL 的查询优化器将有助于提高查询性能。

本文标签： mysql sql

版权声明：本文标题：Mysql - SQL 优化内容由热心网友自发贡献，该文观点仅代表作者本人，转载请联系作者并注明出处：https://m.elefans.com/dongtai/1727190853a1101416.html，本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。

电子爱好者 - 最新技术资讯及电子产品介绍！

Mysql - SQL 优化

👀为什么查询速度会慢

🚀查询的生命周期：

🔎常见的查询性能瓶颈：

📘 数据库优化步骤

🎯 1. 选择适合的DBMS

📊 2. 优化表设计

🔹 优化数据类型

🔹 优化插入记录的速度

📈 3. 优化逻辑查询

🔹 子查询优化

🔹 避免字段运算

🛠️ 4. 优化物理查询

🔹 使用索引

🔹 单表扫描

🔹 多表连接

🚀 5. 使用缓存

🌐 6. 库级优化

🔹 读写分离

🔹 数据分片

🏗️ 7. 优化数据库结构

🔹 冷热数据分离

🔹 增加中间表

🔹 冗余字段

🔹 数据类型优化

📝 8. 优化插入速度

🔹 禁用索引

🔹 禁用唯一性检查

🔹 批量插入

🌟 其他注意事项

🔹 使用非空约束

👀常见优化策略

🔥 常见场景

🎯 优化查询过程中的数据访问

🛠 优化长的查询语句

📊 优化关联查询

💾 优化子查询

🗂 优化LIMIT分页

🕐 优化WHERE子句

🌐 数据结构的优化

📊 查询SQL优化

🛠️ 其他调优策略

🔹 服务器语句超时处理

🔹 创建全局通用表空间

👀SQL 执行流程

💡SQL执行流程

💡SQL执行流程示例

💡CUR执行流程

💡关联查询

💡关联查询示例

💡数据和索引的统计信息

👀SQL 查询剖析

💡使用 SHOW PROFILE

💡使用 SHOW STATUS

💡使用慢查询日志

💡使用 Performance Schema

👀SQL 优化

💡CTE 和 SARGable

💡优化 SQL 的一般步骤

💡常见问题发现渠道

💡什么情况会生成临时表

💡什么情况会查询磁盘而不查询内存

💡SQL 改写 原则

💡SQL 改写 规则

💡SQL 改写 例子

💡局限性及优化策略

👀SQL 查询日志

💡配置慢查询日志

💡分析 MySQL 慢查询日志

✍Percona toolkit 的使用

✍配置 MySQL 慢查询日志

✍mysqldumpslow

✍pt-query-digest

✍详细的字段含义

👀SQL 的执行计划

🛠️ 能干什么？

📚 官方参考文档

🎲 怎么玩？

📝 构建SQL环境语句

💡SQL 改写原则

💡SQL 改写规则

💡SQL 改写例子

🗂️ `select_type、table`字段

🚦 `type`字段

🔑 `possible_keys、key`列字段

🔢 `key_len`字段

📌 `ref、rows`字段

📉 `filtered`:

📋 `Extra`字段

[mysql] root权限丢失恢复完全菜鸟教程

Win10电脑网络图标不见了解决办法