江苏龙网

  1. 主页 > 生活百科 > >

SQL优化的七个方法，你会哪个？

sql优化

一、插入数据优化 
普通插入：在平时我们执行insert语句的时候 ， 可能都是一条一条数据插入进去的 ， 就像下面这样 。
INSERT INTO `department` VALUES(1, '研发部(RD)', '2层'),INSERT INTO `department` VALUES(2, '人事部(RD)', '1层'),INSERT INTO `department` VALUES(3, '后勤部(RD)', '4层'),INSERT INTO `department` VALUES(3, '财务部(RD)', '4层'), 
现在我们考虑以下三个方面对insert操作进行优化 。
1、采用批量插入（一次插入的数据不建议超过1000条）,
执行批量插入，一次性插入的数据不建议超过1000条，如果要插入上万条数据的话，可以将其分割为多条insert语句进行插入 。
INSERT INTO `department` (`id`, `deptName`, `address`)VALUES (1, '研发部(RD)', '2层'), (2, '人事部(HR)', '3层'), (3, '市场部(MK)', '4层'), (4, '后勤部(MIS)', '5层'), (5, '财务部(FD)', '6层'); 
2、手动提交事务
因为一条一条insert插入的时候，如果是自动提交事务，我们的MySQL会频繁的开启、执行事务；
所以我们可以考虑在在大段insert单条插入语句执行的时候，用手动提交事务的方式来执行 。
begin;INSERT INTO `department` (`deptName`, `address`)VALUES('研发部(RD)', '2层'),('人事部(HR)', '3层'),('市场部(MK)', '4层'),('后勤部(MIS)', '5层');INSERT INTO `department` (`deptName`, `address`)VALUES('研发部(RD)', '2层'),('人事部(HR)', '3层'),('市场部(MK)', '4层'),('后勤部(MIS)', '5层');INSERT INTO `department` (`deptName`, `address`)VALUES('研发部(RD)', '2层'),('人事部(HR)', '3层'),('市场部(MK)', '4层'),('后勤部(MIS)', '5层');commit;

SQL优化的七个方法，你会哪个？

文章插图
图片
3、大批量插入
如果一次性需要插入大批量数据 ， 使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入 。
-- 1、首先，检查一个全局系统变量 'local_infile' 的状态 ，  如果得到如下显示 Value=https://www.isolves.com/it/sjk/bk/2024-03-07/OFF，则说明这是不可用的show global variables like 'local_infile';-- 2、修改local_infile值为on，开启local_infileset global local_infile=1;-- 3、加载数据 /*脚本文件介绍 :每一列数据用","分割",每一行数据用 n'回车分割*/load data local infile 'D:/sql_data/sql1.log' into table tb_user fields terminated by ',' lines terminated by 'n';经过测试，导入100万行数据，仅仅耗时16.84s
SQL优化的七个方法，你会哪个？

文章插图
注意事项：使用load的时候要按主键顺序插入，主键顺序插入的性能要高于乱序插入的性能 。
二、主键优化在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 。
我们的InnoDB存储引擎的聚集索引结果中，B+Tree的叶子结点下存储的是row，行数据，并且是根据主键顺序存放 。所有的数据都会出现在叶子结点，而非叶子结点仅仅起到了索引的作用 。
SQL优化的七个方法，你会哪个？

文章插图
图片
主键设计原则：1、满足业务需求的情况下，尽量降低主键的长度
2、插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
3、尽量不要使用UUID做主键或者是其他自然主键，如身份证号
4、业务操作时 ， 避免对主键的修改
三、order by优化 
我们先了解两个概念 ， 前面我们在Explatin详解文章中提到过：SQL性能分析工具ExplAIn详解
【SQL优化的七个方法，你会哪个？】Using filesort：通过表的索引或全表扫描 ， 读取满足条件的数据行 ， 然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序 。
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高
我们对order by的优化就是尽可能优化为Using index 。
新建表：employee
CREATE TABLE `employee` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) DEFAULT NULL,`dep_id` int(11) DEFAULT NULL,`age` int(11) DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`cus_id` int(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name_dep_id_age` (`name`,`dep_id`,`age`)) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;


推荐阅读


上一篇：两年前端经验还不会手写Promise？

下一篇：准线上事故之MySQL优化器索引选错