SQL优化

最新推荐文章于 2024-10-14 17:36:50 发布

代码代码快快显灵

最新推荐文章于 2024-10-14 17:36:50 发布

阅读量854

点赞数 21

文章标签： sql 数据库

本文链接：https://blog.csdn.net/gege_0606/article/details/142499543

版权

order by的优化

在数据库中，ORDER BY 是一个常用的语句，用于对查询结果进行排序。优化 ORDER BY 查询性能的一个重要手段是利用索引。下面详细讲解与索引优化相关的知识点。

1. 基本概念

索引：在数据库中，索引是一种数据结构，用于提高数据检索速度。它类似于书籍的目录，可以快速定位到特定的记录。
文件排序 (filesort)：当查询使用 ORDER BY 但没有合适的索引时，数据库需要对结果集进行排序，通常使用文件排序，这会导致性能下降。

2. 单列排序

如果创建一个表

create table workers(
    id int primary key auto_increment,
    name varchar(255),
    age int,
    sal int
);

insert into workers values(null, '孙悟空', 500, 50000);
insert into workers values(null, '猪八戒', 300, 40000);
insert into workers values(null, '沙和尚', 600, 40000);
insert into workers values(null, '白骨精', 600, 10000);

创建单列索引可以提高单列排序的效率。例如，在表 workers 上创建名字的索引：

CREATE INDEX idx_workers_name ON workers(name);

此时，执行查询：

EXPLAIN SELECT id, name FROM workers ORDER BY name;

会显示 Using index，表明索引被有效利用。

3. 多列排序

对于多个字段排序的情况，最好创建复合索引。例如，如果要根据 age 和 sal 排序，创建如下索引：

create index idx_workers_age_sal on workers(age, sal);

此时，执行查询：

explain select id,age,sal from workers order by age,sal;

效率就是提高~

4. 排序顺序与索引

降序与升序的组合：如果需要对 age 降序和 sal 降序排序，查询如下：

EXPLAIN SELECT id, age, sal FROM workers ORDER BY age DESC, sal DESC;

若没有合适的索引，数据库可能会选择文件排序。

升序与降序混合：若一个字段是升序，另一个是降序，如：

EXPLAIN SELECT id, age, sal FROM workers ORDER BY age ASC, sal DESC;

这时，可能会导致一个使用索引，一个不使用索引的情况。针对这种情况，可以创建如下复合索引：

CREATE INDEX idx_workers_ageasc_saldesc ON workers(age ASC, sal DESC);

完整的示例

/*
order by 优化
*/

drop table if exists workers;

create table workers(
    id int primary key auto_increment,
    name varchar(255),
    age int,
    sal int
);

insert into workers values(null, '孙悟空', 500, 50000);
insert into workers values(null, '猪八戒', 300, 40000);
insert into workers values(null, '沙和尚', 600, 40000);
insert into workers values(null, '白骨精', 600, 10000);


explain select id,name  from workers order by name; #Using filesort效率低
select id,name  from workers order by name; #Using filesort效率低
# 添加索引
create index idx_workers_name on workers(name); # Extra为Using index，效率加快


/*
如果要通过age和sal两个字段进行排序，最好给age和sal两个字段添加复合索引，
不添加复合索引时，效率会低效
 */

explain select id,age,sal from workers order by age,sal;

# 创建索引
create index idx_workers_age_sal on workers(age, sal);

drop index idx_workers_age_sal on workers;
/*
 如果按照age降序，如果age相同则按照sal降序，会走索引吗？
会，会按照倒置索引，Backward index scan
 */
explain select id,age,sal from workers order by age desc,sal desc;
/*
 如果一个升序，一个降序会怎样呢？
 会出现一个使用了索引，一个没有使用索引
 */
explain select id,age,sal from workers order by age asc, sal desc;
# 可以针对这种排序情况创建对应的索引来解决
create index idx_workers_ageasc_saldesc on workers(age asc, sal desc);

explain select * from workers order by age,sal;

测试order by是否符合最左前缀法则

假设有一个表 employees，结构如下：

CREATE TABLE employees (
    id INT,
    last_name VARCHAR(255),
    first_name VARCHAR(255),
    department_id INT,
    salary DECIMAL(10, 2)
);

如果我们创建一个复合索引：

CREATE INDEX idx_last_first ON employees(last_name, first_name);

示例

符合最左前缀法则的查询：
```
SELECT * FROM employees ORDER BY last_name, first_name;
```
这里，使用了复合索引。
不符合最左前缀法则的查询：
```
SELECT * FROM employees ORDER BY first_name, last_name;
```
由于 first_name 不是最左前缀，索引无法被有效利用，可能导致性能下降。

order by 优化原则总结：

排序也要遵循最左前缀法则。
使用覆盖索引（确保查询的所有列都在索引中，直接从索引中获取数据，避免回表）
针对不同的排序规则，创建不同索引。（如果所有字段都是升序，或者所有字段都是降序，则不需要创建新的索引）
如果无法避免filesort，要注意排序缓存的大小，默认缓存大小256KB，可以修改系统变量 sort_buffer_size ：
```
show variables like 'sort_buffer_size';
```

group by的优化

1. 无索引的情况下使用 `GROUP BY`

在 job 列没有索引时，进行 GROUP BY 操作：

SELECT job, COUNT(*) FROM empx GROUP BY job;
explain SELECT job, COUNT(*) FROM empx GROUP BY job; #查看Extra，判断效率如何

通过 EXPLAIN 分析，查询使用了临时表 (Using temporary)，这意味着效率较低，MySQL 必须创建一个临时表来处理分组。

2. 添加索引以提升性能

为了优化查询，可以为 job 列创建索引：

CREATE INDEX idx_empx_job ON empx(job);

添加索引后，再次执行相同的查询：

SELECT job, COUNT(*) FROM empx GROUP BY job;
explain SELECT job, COUNT(*) FROM empx GROUP BY job;

通过 EXPLAIN，可以看到查询不再使用临时表，而是直接使用了索引 (Using index)，这显著提升了查询效率。

3. 复合索引优化

在需要对多个字段进行分组时，例如按 deptno 和 sal 进行分组，创建复合索引进一步优化查询性能：

explain SELECT job, COUNT(*) FROM empx GROUP BY job,sal;

CREATE INDEX idx_empx_job_sal ON empx(job, sal);

这种复合索引可以加速基于 deptno 和 sal 列的分组操作。

最左前缀法则

定义：最左前缀法则是指，索引在创建时，按照列的顺序排列，查询时只有当使用的列顺序符合索引的最左部分时，索引才能被有效利用。
例子 1：当按 deptno 分组时，符合最左前缀法则，索引能够被使用，（此时已经创建了第三点的复合索引）查询效率提高：
```
EXPLAIN SELECT job, COUNT(*) FROM empx GROUP BY job;
```
查询结果显示 Using index，表明使用了索引。
例子 2：当仅按 sal 分组时，由于 sal 不是复合索引中的最左部分，索引无法完全被使用，需要临时表来处理：
```
EXPLAIN SELECT sal, COUNT(*) FROM empx GROUP BY sal;
```
查询结果显示 Using index; Using temporary，表明尽管使用了索引，但还是使用了临时表，效率有所下降。

3. WHERE 子句和复合索引的结合

当在 GROUP BY 查询中同时使用 WHERE 条件和复合索引时，如果 WHERE 中使用了复合索引的最左列，性能会提高。

例如，按 sal 分组并使用 deptno 的 WHERE 条件（deptno 是复合索引的最左列）：
```
EXPLAIN SELECT sal, COUNT(*) FROM empx WHERE deptno = 10 GROUP BY sal;
```
结果显示 Using index，表明索引被完全使用，查询效率提升。

优化总结：

无索引时 GROUP BY 性能较差，会使用临时表，导致效率低。
为 GROUP BY 列添加索引：可以避免使用临时表，直接通过索引优化查询。
复合索引：当涉及多个列时，创建复合索引以加速多列分组。
最左前缀法则：索引使用时，列顺序必须符合复合索引的最左部分，才能有效利用索引。
WHERE 子句与索引结合：如果 WHERE 中使用复合索引的最左列，查询效率将进一步提高

通过这种方法，可以显著提升 GROUP BY 查询的性能，避免不必要的临时表创建。

limit优化

当数据量特别庞大时，使用 LIMIT 分页查询的性能会随着页数增加而下降，尤其是当偏移量（OFFSET）很大时，MySQL 需要扫描大量数据。因此，使用覆盖索引 + 子查询是一种有效的优化方法。

优化 `LIMIT` 的方式

MySQL 官方推荐的优化 LIMIT 查询的方法是通过覆盖索引 + 子查询。以下是具体步骤：

1. 覆盖索引

覆盖索引是指查询的列完全由索引提供，这样可以避免回表，提高查询效率。

2. 子查询优化

通过子查询先获取要查询的主键，然后再通过主键进行查询，避免大量的数据扫描。

具体优化步骤

示例表结构：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    department_id INT,
    hiredate DATE,
    salary DECIMAL(10, 2)
);

普通的 `LIMIT` 查询：

当你使用 LIMIT 进行分页时，例如：

SELECT * FROM employees ORDER BY hiredate LIMIT 10000, 10;

随着偏移量 10000 增加，查询效率会越来越低，因为 MySQL 需要扫描前面的 10000 行数据。

优化后的查询：

为了提升效率，我们可以使用子查询来先获取要查询的主键，再用主键进行查询。

第一步：用子查询获取主键的范围

可以为id创建覆盖索引然后在进行子查询，效率更好

SELECT id FROM employees ORDER BY hiredate LIMIT 10000, 10;

第二步：根据主键进行查询

SELECT * FROM employees WHERE id IN (
    SELECT id FROM employees ORDER BY hiredate LIMIT 10000, 10
) ORDER BY hiredate;

通过这种方式，MySQL 可以通过索引直接找到需要的数据，而不是扫描大量数据后再返回结果。

总结

问题：当数据量非常庞大时，LIMIT 查询的性能会随着偏移量增加而显著下降。
解决方案：通过覆盖索引和子查询的方式，先获取主键，再通过主键查询对应的数据，避免扫描大量无关数据，从而提升效率。

这种方式能显著优化分页查询的性能，特别是在处理大数据集时。

忘记了limit分页查询？

MySQL基础——DQL_dql sql-CSDN博客

主键设计原则：

主键值不要太长：
- 二级索引的叶子节点存储主键值，主键值太长会导致索引占用的空间增大，影响性能。因此，保持主键值简短可以节省空间，提高索引查找效率。
优先使用 AUTO_INCREMENT 生成主键：
- 自增主键（AUTO_INCREMENT）是顺序插入的，性能较高，能减少 B+ 树在插入时的调整操作。
- 避免使用 UUID 作为主键：UUID 由于是随机生成的，插入时不是顺序的，可能导致频繁的页分裂和页合并操作，从而降低性能。
避免使用业务主键：
- 业务主键（如订单号、身份证号等）可能会因为业务变化而频繁修改，而主键修改会导致 B+ 树重新排序，增加不必要的开销。
- 主键应该保持稳定，不应该随着业务逻辑变动，因为主键的修改会引发聚集索引的重新排序，影响性能。
- 业务逐渐表示与业务逻辑相关的、在业务系统中具有实际含义的字段。
主键顺序插入：
- 顺序插入主键能最大限度减少 B+ 树叶子节点的页分裂与页合并，保持索引结构的稳定。
- 如果主键是乱序插入的，B+ 树的叶子节点将频繁重新排序，导致频繁的页分裂和页合并操作，降低系统性能。
- 页分裂：当一个页满时，插入新主键值会触发页分裂操作，将原页的部分数据移到新页中，这是一种较为耗时的操作。
- 页合并：当页中的数据量下降到某个阈值时，两个相邻的页会合并成一个新页，这也是耗时的。
- 顺序插入的优势：主键值顺序插入可以减少页分裂和合并的次数，提升 B+ 树的性能。
B+ 树与页分裂、页合并的影响：
- B+ 树的节点存储在数据库的页中，每个页的大小通常为 16KB。
- 随着数据量的增加，如果主键是乱序插入的，页的利用率会下降，触发频繁的页分裂和页合并操作，这会降低数据库系统的整体性能。
优化技术：
- 虽然乱序插入主键会引发性能问题，但可以通过一些技术手段进行优化：
  - 延迟分裂：B+ 树的分裂操作可以延迟到确实需要的时候，减少不必要的分裂。
  - 调整页大小：根据实际情况调整 B+ 树中页的大小和节点的大小，减少页分裂和页合并的次数。

总结：

主键应该简短且稳定，尽量使用顺序插入的自增主键。
避免使用业务主键和 UUID 等随机主键，减少 B+ 树结构的频繁调整。
顺序插入主键可以优化 B+ 树的性能，减少页分裂和页合并操作。

`INSERT` 优化原则总结：

批量插入：
- 对于大批量的数据插入，不要一条一条地插入，而是使用批量插入，这样可以显著提高插入效率。
- 推荐批量插入语法：
```
INSERT INTO t_user(id, name, age) 
VALUES (1, 'jack', 20), (2, 'lucy', 30), (3, 'timi', 22);
```
- 建议：一次批量插入的条数不要超过 1000 条，以防止占用过多内存或锁资源。
手动控制事务：
- MySQL 默认的自动提交模式：MySQL 在每次执行一条 DML 语句（如 INSERT、UPDATE、DELETE）后会自动提交事务。这种方式在处理大量插入时效率较低，因为每条语句都会导致数据库的磁盘写操作。
- 优化建议：当插入大量数据时，建议手动开启和提交事务，减少磁盘 I/O 操作。
```
START TRANSACTION;
INSERT INTO t_user(id, name, age) VALUES (1, 'jack', 20), (2, 'lucy', 30);
INSERT INTO t_user(id, name, age) VALUES (3, 'timi', 22), (4, 'tom', 25);
COMMIT;
```
- 这样可以显著提高大量数据插入时的性能。
顺序插入主键：
- 主键值最好采用顺序插入，而不是随机插入。顺序插入能有效减少 B+ 树在插入时的页分裂和页合并操作，从而提高插入性能。
- 例如，使用 AUTO_INCREMENT 自增主键可以确保主键的顺序性。

使用 LOAD DATA 导入大数据量：

LOAD DATA 是 MySQL 提供的一种高效的批量数据导入方式，适用于将大量数据从文件（如 CSV）导入到数据库表中，速度比普通的 INSERT 要快得多。

典型流程如下：

登录 MySQL 时启用本地数据导入功能
```
mysql --local-infile -uroot -p1234
```
开启 local_infile 设置：
```
SET GLOBAL local_infile = 1;
```

创建目标表：

USE powernode;

CREATE TABLE t_temp(
  id INT PRIMARY KEY,
  name VARCHAR(255),
  password VARCHAR(255),
  birth CHAR(10),
  email VARCHAR(255)
);

执行 LOAD DATA 指令导入 CSV 文件

LOAD DATA LOCAL INFILE 'E:\\powernode\\05-MySQL高级\\resources\\t_temp-100W.csv' 
INTO TABLE t_temp 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

总结：

批量插入：建议一次插入多条数据，以减少插入操作的开销。
手动事务管理：批量插入数据时，手动控制事务能提高效率，避免自动提交带来的性能损耗。
顺序插入主键：主键应尽量顺序插入，减少 B+ 树的页分裂和合并，提升数据库性能。
LOAD DATA 批量导入：对于超大数据量，可以使用 LOAD DATA 指令直接导入数据文件，速度显著优于普通插入。

`COUNT(*)` 优化总结

分组函数 `COUNT` 的使用方式及原理

COUNT(主键)：
- 原理：取出每个主键值，累加主键的非 NULL 值。
- 性能：主键通常是唯一且不为 NULL，效率较高，但需要从索引中取出主键值进行累加。
COUNT(常量值)：
- 原理：MySQL 会为每一行返回相同的常量值，因此只需要对常量值进行累加。
- 性能：这种方式也能快速计算，因为常量值不依赖于表的内容。
COUNT(字段)：
- 原理：取出指定字段的每个值，判断是否为 NULL，只有非 NULL 的值才会被计入结果。
- 性能：性能相对较低，因为 MySQL 需要逐行检查该字段是否为 NULL。
COUNT(*)：
- 原理：MySQL 不取出具体的字段值，而是直接统计表中的行数，这种方式 MySQL 已经在底层做了优化。
- 性能：效率最高，特别是当仅需要统计总行数时，COUNT(*) 比其他方式性能要好。

**推荐使用 `COUNT(*)`**

结论：如果你想统计表中的总行数，建议使用 COUNT(*)，因为 MySQL 针对这种场景做了底层优化，效率通常高于 COUNT(主键) 和 COUNT(字段)。

注意事项

InnoDB 引擎：
- 对于 InnoDB 存储引擎，COUNT(*) 的实现原理是遍历表中的每一条记录并进行累加。这是因为 InnoDB 没有维护单独的行数统计，COUNT(*) 需要逐条扫描表中的每一行。
- 优化建议：如果数据量很大，且需要频繁统计总行数，可以考虑通过缓存机制（如 Redis）维护总行数。每次插入或删除记录时，同步更新缓存，这样在查询总行数时可以直接从缓存中读取，大幅提升查询效率。
MyISAM 引擎：
- MyISAM 存储引擎在表中维护了一个总行数计数器，因此在没有 WHERE 条件的情况下，COUNT(*) 查询可以直接返回总行数，效率极高。
- 这种优化使得 MyISAM 在统计总行数时，比 InnoDB 要快得多，但由于 MyISAM 在并发写入等其他场景下的劣势，目前一般更推荐使用 InnoDB。

总结：

对于大多数查询，COUNT(*) 是统计总行数时效率最高的方式，特别是当你不关心具体字段的值时。
InnoDB 没有维护总行数，因此 COUNT(*) 会进行全表扫描。如果性能是瓶颈，可以通过缓存（如 Redis）来优化频繁的行数查询。
MyISAM 可以直接读取总行数，效率非常高，但由于 MyISAM 的并发性能较差，现代应用更多使用 InnoDB。

Update优化

什么是行级锁？

行级锁是 MySQL InnoDB 存储引擎中常用的一种锁机制，它允许不同事务操作同一张表中的不同行。当多个事务尝试同时修改不同的行时，行级锁能保证更高的并发性。

行级锁的示例：如果事务 A 正在修改某一行（例如 id = 1 的记录），其他事务（例如事务 B）在该事务提交之前无法修改同一行。此时，事务 B 会等待，直到事务 A 提交或回滚。

表级锁

表级锁会锁定整个表，使得其他事务无法对该表进行任何数据修改操作，直到持有表锁的事务完成。

行锁与表锁的关系

**行锁（InnoDB）**是通过索引来实现的。如果 UPDATE 或 DELETE 操作的 WHERE 条件字段上有索引，那么 InnoDB 会锁定符合条件的特定行。
表锁（MySQL 的 MyISAM）：如果没有索引，InnoDB 会将锁提升为表级锁，即锁住整个表，阻塞其他操作，导致并发性能下降。

示例：演示行锁和表锁

我们有一张表 t_fruit，其结构和初始数据如下：

CREATE TABLE t_fruit (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
);

INSERT INTO t_fruit VALUES (NULL, '苹果');
INSERT INTO t_fruit VALUES (NULL, '香蕉');
INSERT INTO t_fruit VALUES (NULL, '橘子');

行级锁的例子：

开启事务 A，更新 id = 1 的记录：

BEGIN; UPDATE t_fruit SET name = '苹果改名' WHERE id = 1; 
-- A事务未提交，保持此状态

事务 B 试图更新 id = 1 的同一条记录：

BEGIN; UPDATE t_fruit SET name = '苹果被B事务改名' WHERE id = 1;
 -- 此操作将会等待，直到A事务提交或回滚

提交事务 A：

COMMIT; -- 此时，B事务会继续执行

同一行进行操作，会被锁住，指到A事务提交。

事务 A：

BEGIN; UPDATE t_fruit SET name = '苹果改名' WHERE id = 1; 
-- 锁住了id = 1的这一行，其他行未受影响

事务 B：

BEGIN; UPDATE t_fruit SET name = '香蕉改名' WHERE id = 2; 
-- 可以正常执行，因为id = 2与id = 1不冲突

在这种情况下，A 和 B 可以并行操作，因为它们修改的是不同的行，并且没有相互影响。

表级锁的例子：

事务 A（没有使用索引）：

BEGIN; UPDATE t_fruit SET name = '水果改名' WHERE name = '苹果'; 
-- 锁住了整个表，因为name列没有索引，导致升级为表级锁