AI 助理
备案控制台
开发者社区 数据库 文章 正文

【MySQL高级】SQL优化

2023-02-10 112
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 【MySQL高级】SQL优化

5. SQL优化


5.1 大批量插入数据


环境准备 ：


CREATE TABLE `tb_user_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `qq` varchar(32) DEFAULT NULL,
  `status` varchar(32) NOT NULL COMMENT '用户状态',
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;


当使用load 命令导入数据的时候，适当的设置可以提高导入的效率。

ce56c38c04a54ab7b3ecf787f61ab148.png


load data local infile 'E:/sql1.log' into table tb_user_2 fields terminated by ',' lines terminated by '\n';
load data local infile 'E:/sql2.log' into table tb_user_2 fields terminated by ',' lines terminated by '\n';

对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：


1） 主键顺序插入


因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。


脚本文件介绍 :

   sql1.log  ----> 主键有序

   sql2.log  ----> 主键无序

插入ID顺序排列数据：


db9af0b1dac640e08e3f953ecad620f5.png


插入ID无序排列数据：


a7f2cbad16b5474f9d2a179e9c2f120f.png


注意出现：【LOAD DATA语法上传数据】


具体解法见 【Mysql中出现的问题】 这个笔记


2） 关闭唯一性校验


在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。


2c8dc63f15574749b49eb57250ff8ec1.png


3） 手动提交事务


如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

60a501e1cefb46b88b6302d706b46e6b.png


5.2 优化insert语句


当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。


如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。


示例， 原始方式为：

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

60a501e1cefb46b88b6302d706b46e6b.png优化后的方案为 ：


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

在事务中进行数据插入。


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

数据有序插入


insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');

优化后


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(4,'Tim');
insert into tb_test values(5,'Rose');

5.3 优化order by语句


5.3.1 环境准备

CREATE TABLE `emp2` (
  `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 `emp2` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp2` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
create index idx_emp_age_salary on emp2(age,salary);


5.3.2 两种排序方式


1). 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。


80255d514e5740f7b008b15cd058b9d0.png


2). 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。


850a76ed6cd64a8bb8c9a3d4973560ff.png


多字段排序


ae67efe02dee477689ce4966d7262fbb.png



了解了MySQL的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同， 并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现FileSort。


5.3.3 Filesort 的优化


通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。对于Filesort ， MySQL 有两种排序算法：


1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。


2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。


MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。


可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。


d0fa32145b3a41ab876ff2b51cbfb2b2.png


5.4 优化group by 语句


由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。


如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ：


drop index idx_emp_age_salary on emp2;
explain select age,count(*) from emp2 group by age;

d0fa32145b3a41ab876ff2b51cbfb2b2.png

9a3c7269a974422a8e9ab9305fab8030.png


优化后


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


9d1afc89c2d746cc967752de0e081b19.png


从上面的例子可以看出，第一个SQL语句需要进行"filesort"，而第二个SQL由于order by null 不需要进行 "filesort"， 而上文提过Filesort往往非常耗费时间。


创建索引 ：


create index idx_emp_age_salary on emp(age,salary)；

9d1afc89c2d746cc967752de0e081b19.png

72d82bbec3e94099aea346532e040220.png


5.5 优化嵌套查询


Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。


示例 ，查找有角色的所有的用户信息 :


explain select * from t_user where id in (select user_id from user_role );

72d82bbec3e94099aea346532e040220.png

执行计划为 :


f8f9c130456d4dbeb03b6315da21124a.png


优化后 :


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


c48de9be2d5e482a9b8bf04507d21719.png


连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。


5.6 优化OR条件


对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。


获取 emp 表中的所有的索引 ：


e381df78b86b457fa39b7623eec377fd.png


示例 ：

explain select * from emp where id = 1 or age = 30;


6ac554d490d44857ba961ca2e57bf0e4.png


建议使用 union 替换 or ：


fe6b5584a6fa437b867d1440afc39e78.png


我们来比较下重要指标，发现主要差别是 type 和 ref 这两项


type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：


system > const > eq_ref > ref > fulltext > ref_or_null  > index_merge > unique_subquery > index_subquery > range > index > ALL


UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距


UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快


这两项的差距就说明了 UNION 要优于 OR 。


5.7 优化分页查询


一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。


37be4f2ce9cc4cf59f1e3281f05a89ac.png


5.7.1 优化思路一


在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。


9d8f2f5704714622890fbd356ec937d8.png


5.7.2 优化思路二


该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。


917eb9a55b2d409e81e1c7882960a572.png


5.8 使用SQL提示


SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。


5.8.1 USE INDEX


在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。


create index idx_seller_name on tb_seller(name);

917eb9a55b2d409e81e1c7882960a572.png

a4028fad7b0c4b028b0a51e78dc430b6.png


5.8.2 IGNORE INDEX


如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。


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

4ad55f4e5d114fb8aeef52f9248fe6b8.png


5.8.3 FORCE INDEX


为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。


create index idx_seller_address on tb_seller(address);


e0ee6dcba3044c0ebbcd36b9b7e63575.png


文章标签：
云数据库 RDS MySQL 版
搜索推荐
SQL
关系型数据库
索引
MySQL
算法
数据库
存储
关键词：
云数据库 RDS MySQL 版优化
SQL优化
云数据库 RDS MySQL 版sql
SQL MySQL
云数据库 RDS MySQL 版高级
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
陶然同学
目录
相关文章
互联网课堂
|
1月前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN：指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询，帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标，如 `id`、`select_type`、`table`、`type`、`key` 等，并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例，展示了如何通过创建合适索引和调整查询语句来提升查询性能。
互联网课堂
216 9
蓝易云
|
16天前
|
SQL 关系型数据库 MySQL
MySQL派生表合并优化的原理和实现
通过本文的详细介绍，希望能帮助您理解和实现MySQL中派生表合并优化，提高数据库查询性能。
蓝易云
56 16
蓝染-惣右介
|
30天前
|
SQL 存储 关系型数据库
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法，包括数据定义（DDL）、数据操作（DML）、数据查询（DQL）和数据控制（DCL）四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作，以及通过图形化工具DataGrip进行数据库管理和查询。此外，还讲解了数据的增、删、改、查操作，以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
蓝染-惣右介
105 2
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
蓝易云
|
17天前
|
SQL 关系型数据库 MySQL
MySQL派生表合并优化的原理和实现
通过本文的详细介绍，希望能帮助您理解和实现MySQL中派生表合并优化，提高数据库查询性能。
蓝易云
33 7
潜意识Java
|
4天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧，涵盖基础优化、高级技巧及性能监控。
潜意识Java
58 0
蓝易云
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍，希望您能够深入理解MySQL索引优化和慢查询优化的方法，并在实际应用中灵活运用这些技术，提升数据库的整体性能。
蓝易云
82 18
云深知何处
|
1月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导（SQL Access Advisor）的应用场景及其使用方法。访问指导通过分析给定的工作负载，提供索引、物化视图和分区等方面的优化建议，帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句，还能综合考虑多条SQL语句的优化效果，为DBA提供全面的决策支持。
云深知何处
83 11
技术小达人
|
7天前
|
SQL 分布式计算 Java
Spark SQL向量化执行引擎框架Gluten-Velox在AArch64使能和优化
本文摘自 Arm China的工程师顾煜祺关于“在 Arm 平台上使用 Native 算子库加速 Spark”的分享，主要内容包括以下四个部分： 1.技术背景 2.算子库构成 3.算子操作优化 4.未来工作
技术小达人
32 0
蓝易云
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍，希望您能够深入理解MySQL索引优化和慢查询优化的方法，并在实际应用中灵活运用这些技术，提升数据库的整体性能。
蓝易云
84 7
蓝易云
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化：原理与实践
通过本文的介绍，希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法，并在实际项目中灵活运用这些技术，提升数据库的整体性能。
蓝易云
107 5

热门文章

最新文章

  • 1
    预编译为什么能防止SQL注入?
  • 2
    日志服务 SQL 引擎全新升级
  • 3
    如何在 Java 代码中使用 JSqlParser 解析复杂的 SQL 语句？
  • 4
    如何在Django中正确使用参数化查询或ORM来避免SQL注入漏洞？
  • 5
    SQL优化有绝招，使用DAS提升工作效率！完成任务可领取保暖手套！
  • 6
    Java使用sql查询mongodb
  • 7
    Flink SQL 详解：流批一体处理的强大工具
  • 8
    【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
  • 9
    除了使用Django的ORM，还能通过什么方式在Django中避免SQL注入漏洞？
  • 10
    Java使用sql查询mongodb
  • 1
    Linux下安装MySQL
    71
  • 2
    XtraBackup支持哪些MySQL数据库版本？
    830
  • 3
    如何将数据从MySQL迁移到PolarDB？
    604
  • 4
    Java医院绩效考核系统源码B/S+avue+MySQL助力医院实现精细化管理
    93
  • 5
    Linux对MySQL语法校验
    110
  • 6
    【MySQL系列笔记】分库分表
    270
  • 7
    mysql 设置环境变量与未设置环境变量连接数据库的区别
    186
  • 8
    服务器Linux系统配置mysql数据库主从自动备份
    240
  • 9
    mysqlTools 一分钟部署安装本mysql多个版本，解锁繁琐部署过程
    440
  • 10
    golang使用gorm操作mysql3，数据查询
    116

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • 如何快速连接云数据库RDS MySQL
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 5分钟上手Flink MySQL连接器

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mariadb
    • 下一篇
    阿里云oss简介和如何对接使用