02_SQL优化

最新推荐文章于 2024-09-25 14:30:16 发布
最新推荐文章于 2024-09-25 14:30:16 发布
阅读量99 收藏
点赞数
分类专栏： # mysql查询优化 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44390164/article/details/119974511
版权

1. 大批量插入数据

1. 按主键顺序插入

  • InnoDB类型的表是按照主键的顺序保存的（聚簇索引B+树）。
  • 将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率

2. 导入数据时关闭唯一性校验

SET UNIQUE_CHECKS=0;
  • 导入数据前关闭唯一性校验
  • 导入完成后再开启唯一性校验

3. 导入数据前设置为手动提交事务

SET AUTOCOMMIT=0;
  • 导入数据前关闭自动提交事务
  • 导入数据后再打开自动提交事务

2. 优化insert语句

1. 对某张表插入多条数据应合并为一条语句

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

// 优化后
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

2. 插入数据有序插入

3. 优化order by语句

// 建表语句
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
create index idx_emp_age_salary on emp(age,salary);

1. filesort排序

explain select * from emp order by age;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
  • 全表扫描，然后对返回的数据进行排序，集filesort排序。
  • 所有不是通过索引直接返回排序结果的排序都加Filesort排序
  • 未使用到索引，因为需要全表扫描数据，再对age排序

2. 通过有序索引顺序扫描直接返回有序数据

explain select id from emp order by age asc;

+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

explain select id,age from emp order by age desc;

+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

explain select id,age,salary from emp order by age desc;

+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • 满足复合索引（age,salary）的最左前缀原则，而且不需要回表操作

3. 多字段排序

explain select id,age,salary from emp order by age asc,salary asc;

+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

explain select id,age,salary from emp order by age desc,salary desc;

+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  •  多字段排序中同升序同降序可以走索引排序
explain select id,age,salary from emp order by age desc,salary asc;

+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
explain select id,age,salary from emp order by salary asc,age asc;

+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | emp   | NULL       | index | NULL          | idx_emp_age_salary | 9       | NULL |   12 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
  • 多字段排序中不满足同升序同降序或者不满足最左前缀原则，则不走索引排序。
  • 但是因为查找的三个字段都在索引上，所以还是用到了索引。

4. 优化group by语句

  • group by实际上也会进行排序操作
  • 与order by相比，group by主要多了排序之后的分组操作。
  • 所以group by过程中一样可以使用索引甚至复合索引
  • 但是还是一样要满足索引的最左前缀原则

1. 删除索引后会发现进行了fileSort

drop index idx_emp_age_salary on emp;

explain select age,count(*) from emp group by age;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

2. 创建索引后发现走索引排序了。

create index idx_emp_age_salary on emp(age,salary);

explain select age,count(*) from emp group by age;

+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys      | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | index | idx_emp_age_salary | idx_emp_age_salary | 9       | NULL |   12 |   100.00 | Using index |
+----+-------------+-------+------------+-------+--------------------+--------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

5. 优化嵌套查询

1. 将有些子查询优化为连接查询

  • 因为连接查询不需要再内存中创建一个临时表，所以效率会高一些
explain select * from t_user where id in (select user_id from user_role);

+----+--------------+-------------+------------+--------+---------------+---------------+---------+-----------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key           | key_len | ref             | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+---------------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE       | t_user      | NULL       | ALL    | PRIMARY       | NULL          | NULL    | NULL            |    6 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key>    | 99      | test1.t_user.id |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | user_role   | NULL       | index  | fk_ur_user_id | fk_ur_user_id | 99      | NULL            |    6 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------+---------------+---------+-----------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

优化后

explain select * from t_user u,user_role ur where u.id=ur.user_id;

+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | ur    | NULL       | ALL    | fk_ur_user_id | NULL    | NULL    | NULL             |    6 |   100.00 | Using where |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY       | PRIMARY | 98      | test1.ur.user_id |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

6. 优化or条件

  • 建议将union替换or条件

7. 优化分页查询

  • limit 2000000,10 这样的情况需要先排序前2000010条记录，仅仅返回2000000-2000010的记录
  • 所以优化的思路是最好再所以上确定第2000000条的记录，然后返回之后的十条记录。
  • 即思想还是建立有序索引。先查索引，再回表（对于复合索引甚至不需要回表）

8. 使用sql提示

1. 查询时推荐mysql使用某个索引

explain select * from tb_seller use index(idx_seller_name) where name='小米科技';

2. 查询时忽略某个索引

explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';

3. 查询时强制使用某个索引

explain select * from tb_seller force index(idx_seller_name) where name='小米科技';

mysql sql优化的几种方法_sql优化常用的几种方法，19种最有效的sql优化技巧
weixin_28836507的博客
01-21 4933
本文我们来谈谈项目中常用的MySQL优化方法，共19条，具体如下：1、EXPLAIN做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例，标注(1、2、3、4、5)我们要重点关注的数据：type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。key_len列，索...
mysql dateformat语句优化_sql 优化-查询条件中有DATE_FORMAT的
weixin_42157556的博客
02-07 2030
查询sql where 后的条件中有时间比较的如果使用了DATE_FORMAT这种函数的话就不会走索引查询就会慢，优化方式：去掉了DATE_FORMAT函数，程序传过来的字符串“2017-07-01 14:02:19”可以直接和数据库中的时间字段值进行比较优化前： and TRADE_NO = #{tradeNo} and BATCH_ID = #{batchId} and DATE_FORMAT...
MySQL进阶篇之三：SQL优化
喜欢喝椰奶的博客
04-17 323
limit分页查询在大数据量的时候，查询效率同样会非常的慢，例如一个常见又非常头疼的问题就是limit 2000000,10 此时需要MySQL排序前200010条记录，仅仅返回200000-2000010的记录，其他记录丢弃，查询排序的代价非常大。①Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。（因为索引字段相当于上的行锁，非索引字段上的表锁）。
mysql插入需要顺序吗
youhuakongzhi的博客
11-19 7930
摘自：https://segmentfault.com/a/1190000007943698 增删改查是数据库最常用的4个命令，插入算是第二常用，第一是SELECT。插入数据的方式大概有4种 插入完整的行 插入行的一部分 插入多行数据（批量插入） 插入来自查询的数据 插入完整的行 使用Insert将一条数据插入数据库是最简单也是最基本的操作。插入操作有两种写法。 顺序插入 顺序插入指的是：给出数据库名和需要插入数据，按照数据库字段排列顺序将数据插入数据库。 .
插入记录的顺序问题！
terryhuang的专栏
04-11 1444
问题：   如何在任意位置插入记录？我们平时使用 ado的 recordset.addnew或者用 sql语句 insert into...来添加数据的时候，记录总是被添加在表的最后一行，如何才能把记录插入到其中某条记录的前面那？为什么我明明在最后一条添加记录，但是添加以后，我新增的那条记录却跑到中间去了？
Pandas数据库大揭秘：read_sql、to_sql 参数详解与实战篇
一键难忘的博客
02-15 8495
Pandas是Python中一流的数据处理库，而数据库则是数据存储和管理的核心。将两者结合使用，可以方便地实现数据的导入、导出和分析。本文将深入探讨Pandas中用于与数据库交互的两个关键方法：read_sql和to_sql。通过详细解析这两个方法的参数，我们将为读写数据库提供清晰的指导，并附带实际代码演示，以帮助读者更好地理解和运用这些功能。
sql优化的几种方式
热门推荐
freedom
11-05 42万+
一、为什么要对SQL进行优化 我们开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。 二、SQL优化的一些方法 1.对查询进行优化，应尽量避免全表扫描...
mysql distinct 优化_SQL优化终于干掉了“distinct”
weixin_42363388的博客
01-27 3058
一、优化目的在我提交了代码的时候，架构师给我指出我这个sql这样写会有问题。因为在分库分表的时候，是不支持子查询的。所以需要把多表的子查询的sql结构进行优化。二、优化之前的sql长这样是不是挺恐怖的；(此处为了脱敏，我把相关的sql关键词都给打码掉了)这个sql的执行步骤如下：1、查询出来d表中的某个id字段包含多个id值的所有的数据(因为此表是1-n的关系，所以需要去重，仅需要拿到不重复的id...
sql 优化，提高查询速度
我哎GIS博客
08-14 1万+
在系统开发中，SQL查询的优化是提高应用性能和响应速度的关键。SQL查询的优化都是相对的，要根据具体业务和库表数据量的大小选择合适的优化方案。
Oracle数据库SQL优化详解
靖节先生的博客
01-07 1万+
Oracle数据库SQL优化1. Oracle SQL优化概述2. Oracle SQL优化详解2.1 Oracle 查询阻塞2.2 Oracle 查询耗时 SQL2.3.Oracle 查看执行计划2.4.Oracle 查看收集统计信息2.5.Oracle 查询优化器 -- 改写查询语句2.6.Oracle 查询优化器 -- 访问路径2.7.Oracle 查询优化器 -- 表连接方法2.8.Oracle 索引2.9.Oracle 视图2.10.Oracle 减少数据库访问次数2.11 Oracle 面向对象
ORACLE_SQL_ALL.rar_oracle_oracle sql_sql优化
09-23
在Oracle数据库系统中，SQL的性能优化是至关重要的，因为它直接影响到系统的响应速度和整体效率。本资料"ORACLE_SQL性能优化(全)"提供了全面的指南，旨在帮助数据库管理员和开发人员提升SQL查询的性能。 1. **SQL...
SQL.rar_java sql_java sql查询_query optimization_sql_sql优化
09-24
以下是一些常见的SQL优化策略： 1. **查询语句优化**：避免使用全表扫描，尽可能利用索引。这通常意味着在WHERE子句中使用索引字段，而不是在SELECT或ORDER BY子句中。此外，减少JOIN操作的数量和复杂性也能提升...
sql.rar_SQL语句_hawk_sql优化
09-19
本资源“sql.rar”包含了“SQL语句_hawk_sql优化”主题，由名为“hawk”的专家或社区成员收集并分享，旨在帮助我们了解和提升Oracle SQL语句的优化技巧。 SQL语句优化是数据库性能调优的关键部分，尤其对于大型企业...
SQLyouhua_v1.0.rar_HB-SQLyou_oracle_sql you_sql优化
09-22
《SQL优化手册_v1.0.doc》是一份专门针对Oracle数据库SQL优化的详细指南，旨在帮助用户提升SQL语句的执行效率，进而提高整体数据库系统的性能。在Oracle数据库管理中，SQL优化是至关重要的，因为它直接影响到数据的...
Oracle_SQL优化脚本_完整实用资源
01-25
Oracle SQL优化是数据库管理员和开发人员提升系统性能的关键技能之一。这个"Oracle_SQL优化脚本_完整实用资源"压缩包包含了一系列工具和方法，旨在帮助你优化在Oracle数据库上运行的SQL查询，从而提高数据库的响应...
[OPEN SQL] SELECT语句
Hudas的博客
09-23 561
本文主要讲解OPEN SQL读取数据的SELECT语句相关知识点
SQL - 进阶语法（一）
qq_51222843的博客
09-21 644
关于SQL的进阶语法教程，包括简单合并数据库，修改数据库内容等操作
Transact-SQL概述（SQL Server 2022）
brucexia的专栏
09-23 1304
Transact-SQL是Microsoft公司在关系数据库管理系统SQL Server中的SQL3标准的实现，是微软对SQL的扩展。在SQL Server中，所有与服务器实例的通信都是通过发送Transact-SQL语句到服务器来实现的。根据其完成的具体功能，可以将Transact-SQL语句分为四大类，分别为数据操作语句、数据定义语句、数据控制语句和一些附加的语言元素。
【YashanDB知识库】yashandb执行包含带oracle dblink表的sql时性能差
最新发布
cod0410的博客
09-25 513
yashandb在访问远端oracle的数据时，拉取了表的所有列。并且每批次只拉取16条数据，在网络有时延时(现场网络时延0.4ms)，会有额外的网络传输耗时。使用oci编程可以验证yashandb访问oracle dblink远端表在不同情况下的性能表现。yashandb通过dblink访问oracle性能不佳，影响业务运行效率及客户体验。规避方式为在远端oracle侧创建视图，只查询需要的列。使用OCI驱动编程使用示例 中提供的例子可以分析oci的耗时。1、只从dblink拉取查询需要的列数据。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值