MySQL SQL性能优化方案（SQL优化 二）

插入数据优化

1. 多个值同时插入：多个值的插入语句可以减少与数据库的通信次数，从而提高插入性能

insert into 表名 (列1, 列2, 列3)
values
(值1, 值2, 值3),
(值4, 值5, 值6),
...

2. 命令批量插入：高效地将数据从文件导入到数据库中（要求文件格式要规范）

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

-- 导入操作

load data infile '文件路径' -- 相对路径和绝对路径都可以
into table 表名
fields terminated by ',' -- 指定字段分隔符
lines terminated by '\n' -- 指定行分隔符

如果是工具（比如Navicat的话），直接选择导入sql文件就行

3.禁用表索引(不建议，插入数据时，会导致索引不可用)

alter table 表名 disable keys;-- 禁用
-- 中间为插入操作
alter table 表名 enable keys;-- 打开

4.使用事务：将插入操作包装在事务中可以提高性能和数据一致性

start transaction;
insert into 表名 values(1,''),(2,''),(3,'');
insert into 表名 values(4,''),(5,''),(6,'');
insert into 表名 values(7,''),(8,''),(9,'');
commit;

5.主键顺序插入：性能高于乱序插入

主键乱序插入 : 5 1 4 3 2 7
主键顺序插入 : 1 2 3 4 5 7

--------------------------------

order by优化

MySQL查询执行计划中的两种不同的排序方式：

1. Using filesort：当MySQL无法使用索引完成排序时，会使用Using filesort，这意味着MySQL将在内存或磁盘上创建临时文件，并对结果集进行排序。这种操作可能会导致性能下降，特别是在处理大量数据时。

2. Using index：当MySQL能够使用索引完成排序时，会使用Using index。这意味着MySQL可以直接使用索引的有序性，而无需额外的排序操作，这通常是因为查询中的ORDER BY子句与已经存在的索引匹配。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序
操作时，尽量要优化为 Using index。

order by优化原则：如何使用 Using index？

1. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

create index 索引名 on employees (字段1, 字段2);-- 先对字段1排序，再对字段2排序

2. 尽量使用覆盖索引。

覆盖索引是指查询使用了索引，并且需要返回的列（包含数据），在该索引中已经全部能够找到。

-- 建立覆盖索引
create index idx_orders on orders (order_date, order_id, customer_id);

-- 查询（根据覆盖索引列）
select order_id, customer_id, order_date from orders where order_date >= '2020-01-01';

3. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。

create index idx_products on products (product_name asc, price desc);

--------------------------------

group by优化

在分组操作中，以提升性能方法：
1. 在分组操作时，可以通过索引来提高效率（分组的列为索引）
2. 分组操作时，索引的使用也是满足最左前缀法则的（分组为多个列）

--------------------------------

limit优化

经典问题：你如何优化SQL语句 limit 2000000或者 limit 2000000,10？

1. 建立临时表，减少扫描量和排序量

-- 建立

create temporary table temp_table as
select * from 表名
order by 列名
limit 2000000;

-- 查询

select * from temp_table;

 2. 通过覆盖索引加子查询形式进行优化

-- 例子

select * from 表1 t , (select id from 表1 order by id limit 2000000,10) a

where t.id = a.id;

--------------------------------

count优化

效率排序：count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)