你真的知道如何优化SQL么？

最新推荐文章于 2022-02-18 16:57:25 发布
原创 最新推荐文章于 2022-02-18 16:57:25 发布 · 207 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文深入探讨MySQL的Explain命令，解析其如何帮助我们理解SQL执行计划，包括查询类型、表读取顺序、索引使用情况及性能评估，是数据库性能优化的必备工具。

本文涉及：MySQL性能优化神器Explain的使用

简介

虽然使用Explain不能够马上调优我们的SQL，它也不能给予我们一些调整建议，但是它能够让我们了解MySQL 优化器是如何执行SQL 语句的

通过Explain，我们可以分析出以下结果：

  • 表的读取顺序

  • 数据读取操作的操作类型

  • 哪些索引可以使用

  • 哪些索引被实际使用

  • 表之间的引用

  • 每张表有多少行被优化器查询

Explain命令的用法十分简单, 在 select语句前加上 Explain 就可以了, 例如:


 

 

 
id、select_type、table、partitions、type、possible_keys、key、ref、rows、filtered、extra

 
接下来我们来看一下各个字段的含义

 

 
id 查询序列号

 

 
加载表的顺序

 
640?wx_fmt=png

 
连接查询各个表的加载顺序是相同的，所以都为1

 

 
640?wx_fmt=png

 
包含子查询的时候，先执行子查询，所以user表的id值最大

 

 
select_type 查询类型

 

 
常用取值有:

 
  • SIMPLE：简单的select查询，不包含子查询和索引
  • PRIMARY：查询中若包含任何子查询，最外层查询则为记为PRIMARY
  • SUBQUERY：在SELECT或WHERE列表中包含了子查询
  • DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
  • UNION：若第二个SELECT出现在索引之后，则被标记为UNION：若索引包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  • UNION RESULT：从索引表获取结果的查询

 

 
table查询涉及的表或衍生表

 

 
type查询类型

 
通过 type 字段, 我们可以判断此次查询是全表扫描还是索引扫描等，type 常用的取值有:

 

 
system：表只有一条数据

 
const：针对主键或唯一索引的等值查询扫描, 简单理解为一次读取就获取到了数据，例如下面这个主键索引的查询

 
640?wx_fmt=png

 

 
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

 
ref：非唯一性索引扫描，返回匹配某个单独值的所有行

 
range：表示使用索引范围查询，例如=、<>、>、>=、<、<=、IS、 NULL、<=>、BETWEEN、IN等

 
index: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据，例如:

 
640?wx_fmt=png

 
ALL: 表示全表扫描, 这个类型的查询是性能最差的查询

 

 
type 类型的性能比较

 
通常来说, 不同的 type 类型的性能关系如下:

 
possible_keys查询时能够使用到的索引.

 
possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意并不是一定用，实际使用是由 由 key 字段决定

 


 
key 查询使用的索引

 
此字段是 MySQL 在当前查询时所真正使用到的索引.

 


 
key_len使用索引的字节数

 
这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到.

 
rows MySQL估算要查找到结果集需要扫描读取的数据行数

 

 
Extra额外的信息

 

 
常见的有以下几种内容:

 
  • Using filesort：MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
  • Using index：表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
  • Using temporary：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.
  • Using where：表明使用了where过滤
  • Using join buffer：表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些
  • impossible where：where子句的值总是false，不能用来获取任何元组
  • select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
  • distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

 

 
 
640?wx_fmt=jpeg

 

  

   
万水千山总是情，点个 “在看” 行不行！！！

  

  

   640?wx_fmt=png  
   

  

 

 
 


                

        

    


  



    

      

        

            

              
                
                  javaxuexilu
                
              
            

            

                关注
              
            

        

        

          
      

      










                



	

		

			

				
					
有哪些SQL优化的手段？

				
			

			

				

					观于沧海的博客
				

				

					08-16
					
					8316
					
				

			

		

		

			
				
1、SQL的性能分析；2、SQL的优化手段；

			
		

	



                

            
              



	

		

			

				
					
SQL如何进行优化，看这篇就好了

				
			

			

				

					m0_58479954的博客
				

				

					06-23
					
					1203
					
				

			

		

		

			
				
在应用开发的早期，数据量少，开发人员开发功能时更重视功能上的实现，随着生产数据的增长，很多 SQL语句开始暴露出性能问题，对生产的影响也越来越大，有时可能这些有问题的 SQL 就是整个系统性能的瓶颈。...

			
		

	



              


  
              

                


	

		

			

				
					
MySQL 性能优化神器 Explain 使用分析

				
			

			

				

					chouyan7019的博客
				

				

					03-12
					
					250
					
				

			

		

		

			
				
简介 
MySQL 提供了一个 EXPLAIN 命令, 它可以对SELECT语句进行分析, 并输出SELECT执行的详细信息, 以供开发人员针对性优化. EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了, 例如: 
EXPLAINSELEC...

			
		

	





	

		

			

				
					
Mysql 性能优化神器Explain详解

				
			

			

				

					
				

				

					02-18
					
					890
					
				

			

		

		

			
				
文章目录Explain的作用博文背景Explain 使用方法Explain之ID说明1. ID值相同2.ID值不同3.ID值相同，ID值不同共存Explain的select_type详解SIMPLE 简单查询PRIMARY 主查询DERIVED 衍生UNION 联合SUBQUERY 子查询UNION RESULT 联合结果Explain的table详解Explain的type域详解1.system2.const3.eq_ref4.ref5.range6.index7.allExplain之possible_

			
		

	



		

			
		



	

		

			

				
					
MySQL性能优化神器—explain

				
			

			

				

					风过留声
				

				

					04-29
					
					343
					
				

			

		

		

			
				
MySQL性能优化神器—explain
		
		
		
			一、explain是什么？
简单来讲就是官方给的一个优化工具，直接在你的SQL语句前加上explain，执行整条语句，之后你就可以根据执行结果优化你的SQL啦，废话不多说，直接刚实例
测试实例
1、创建测试表


CREATE TABLE `sql_explain_student` (


...

			
		

	





	

		

			

				
					
SQL优化|Java面试题

				
			

			

				

					
				

				

					12-13
					
					556
					
				

			

		

		

			
				
看到一篇非常全面的SQL优化文章，在开发的工作中往往不考虑性能上的缺失（在一开始的时候数据量不大也看不出速度上的区别）。但写的越多越应该规范一下写法。
原文链接：http://www.jfox.info/SQL-you-hua.html
ByLee-Last updated:星期五, 五月 17, 2013

数据库的优化问题
一、问题的提出
　在应用系统开发初期，由...

			
		

	





	

		

			

				
					
你真的知道怎么优化SQL吗

				
			

			

				

					09-09
				

			

		

		

			
				
本文将深入探讨如何优化SQL语句，提高查询效率，减少资源消耗。  首先，了解SQL语句的执行过程至关重要，Explain是MySQL提供的一种工具，用于分析查询执行计划。通过Explain，我们可以看到SQL语句在执行时的详细步骤...

			
		

	





	

		

			

				
					
你的sql该优化了

				
			

			

				

					01-19
				

			

		

		

			
				
1.查询SQL尽量不要使用select * ，而是select具体字段 2.如果知道查询结果只有一条，或者只要最大/最小一条记录，建议用limit 1 3.尽量避免在where子句中使用or来连接条件 4.优化limit分页 5.优化like语句 6.使用...

			
		

	





	

		

			

				
					
“数据分析师”需要知道的SQL优化问题

				
			

			

				

					12-14
				

			

		

		

			
				
   3）SQL优化—主要就是优化索引    4）索引的弊端    5）索引的优势    6）B+树图示说明   3、索引的分类与创建    1）索引分类    2）创建索引的两种方式    3）索引删除和索引查询   4...

			
		

	





	

		

			

				
					
SqlServer 执行计划及Sql查询优化初探

				
			

			

				

					01-19
				

			

		

		

			
				
网上的SQL优化的文章实在是很多，说实在的，我也曾经到处找这样的文章，什么不要使用IN了，什么OR了，什么AND了，很多很多，还有很多人拿出仅几S甚至几MS的时间差的例子来证明着什么(有点可笑)，让许多人不知道其是...

			
		

	





	

		

			

				
					
手把手教你给 SQL 做个优化

				
			

			

				

					JNPF快速开发平台的博客
				

				

					08-12
					
					818
					
				

			

		

		

			
				
在开始之前，咱们要知道：如果我的 SQL 语句执行的足够快，还有没有必要去做优化？

完全没有必要对吧

所以我们一般说，要给 SQL 做个优化，那肯定就是这条 SQL 语句执行的比较慢了

那么，为什么它会执行比较慢呢？

SQL 语句执行较慢的 3 个原因

没有建立索引，或者索引失效导致了 SQL 语句执行较慢

这个应该是比较好理解的，如果数据比较多，在千万级别以上，然后呢又没有建立索引，在这千万级别的数据中查找你想要的内容，简直就是在肉搏啊（哎呦，可了不得，竟然敢肉搏



索引失效这块内容说起来

			
		

	





	

		

			

				
					
Java教程之SQL性能优化提升

				
			

			

				

					想当大佬的咸鱼双的博客
				

				

					07-19
					
					1635
					
				

			

		

		

			
				
Ø 简单的性能优化

　　Sql的性能优化是数据库工程师在实际工作中必须面对的重要课题之一。对于某些数据库工程师来说，它几乎唯一的命题。实际上，像WEB服务这样需要快速响应的应用场景中，SQL的性能直接决定了系统是否可以使用。这里主要介绍一些使用SQL执行速度更快，消耗内存更少的优化技巧，今天的文章只介绍其中的一种，后续会继续更新一些其它的优化方式。

　　严格地优化查询性能时，必须要了解所使用的...

			
		

	





	

		

			

				
					
MySQL数据库优化的八种方式(经典必看)

				
			

			

				

					weixin_30521161的博客
				

				

					12-21
					
					1万+
					
				

			

		

		

			
				
引言：
      关于数据库优化，网上有不少资料和方法，但是不少质量参差不齐，有些总结的不够到位，内容冗杂
偶尔发现了这篇文章，总结得很经典，文章流量也很大，所以拿到自己的总结文集中，积累优质文章，提升个人能力，希望对大家今后开发中也有帮助

1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表...

			
		

	





	

		

			

				
					
SQL 优化必懂知识点

				
			

			

				

					技术杂谈
				

				

					06-20
					
					1万+
					
				

			

		

		

			
				

  
    
                    

                    

                    
                    
                    本文来自作者 奋斗 在 GitChat 上分享 「SQL 优化必懂知识点」1. 基数单个列唯一键（distict_keys）的数量叫做基数。比如性别列，该列只有男女之分，...

			
		

	





	

		

			

				
					
真·mysql中的SQL优化

					
热门推荐

				
			

			

				

					Wildpen的成长之路
				

				

					08-01
					
					2万+
					
				

			

		

		

			
				
1. 优化SQL语句中的一般步骤





通过show status命令了解各种SQL的执行频率

 
  
 



定位执行效率较低的SQL语句


可以通过以下两种方式定位执行效率较低的SQL语句。 通过慢查询日志定位那些执行效率较低的SQL语句，用-log-slow-queries[=file_name]选 项启动时，mysqld写一个包含所有执行时间超过long_query_time秒...

			
		

	





	

		

			

				
					
elasticsearch-7.17.22-linux-x86-64.tar.gz分享给需要的同学

					
最新发布

				
			

			

				

					12-08
				

			

		

		

			
				
elasticsearch-7.17.22-linux-x86_64.tar.gz分享给需要的同学

			
		

	





	

		

			

				
					
sqlserver 中有for循环么？

				
			

			

				

					06-17
				

			

		

		

			
				
<think>我们正在处理用户的问题：用户想了解SQLServer中是否支持for循环以及如何使用，或者替代方案。根据引用内容，我们知道：1.游标（Cursor）通常用于逐行处理数据，但性能较差（引用[1]和[4]）。2.引用[4]展示了使用WHILE循环来替代游标的例子，说明SQLServer中可以使用WHILE循环来实现类似循环的功能。3.引用[3]解释了@@FETCH_STATUS的用法，这是在游标循环中常用的状态变量。但是用户问的是“for循环”。在SQLServer中，并没有像其他编程语言（如C#、Python）那样的FOR循环结构。但是，我们可以通过WHILE循环来模拟类似的功能。因此，回答应该包括：-说明SQLServer没有直接的FOR循环，但可以使用WHILE循环。-给出WHILE循环的基本语法和使用示例。-强调在数据库操作中，通常应避免使用循环（无论是游标还是WHILE循环），因为集合操作（基于集合的查询）通常更高效。-提供替代方案，如使用集合操作（JOIN、GROUPBY等）或窗口函数等。根据引用[2]中的优化建议，我们应该尽量避免使用循环（游标或WHILE），而使用基于集合的操作。此外，引用[1]提到可以使用WHILE循环、SET操作、ROW_NUMBER()、CTE、MERGE、表变量、APPLY等作为游标的替代方案，这些同样适用于替代循环操作。因此，回答结构如下：1.直接回答用户：SQLServer没有FOR循环，但可以使用WHILE循环。2.展示WHILE循环的基本语法和示例。3.说明循环在数据库中的性能问题，建议优先使用基于集合的操作。4.提供替代循环的基于集合操作的例子（如引用[2]中的优化例子）。5.最后，生成相关问题。注意：我们引用的内容主要是关于游标和优化，但用户问的是FOR循环，因此我们要将两者联系起来：游标和WHILE循环都是循环操作，而FOR循环在SQLServer中不存在，但WHILE循环可以替代。示例代码：我们可以用WHILE循环来实现一个简单的计数循环，或者遍历一个表（但遍历表通常用游标，而游标性能差，所以我们用WHILE循环时也要注意）。但是，请注意引用[4]中就是用WHILE循环配合游标的状态来更新数据，实际上还是游标。我们这里要展示的是不使用游标的WHILE循环，例如使用一个计数器。重要：在数据库中，循环操作（无论是游标还是WHILE）通常性能较差，应优先考虑集合操作。因此，我们将提供两种方式的例子：a.使用WHILE循环进行简单的计数（模拟FOR循环）。b.使用基于集合的操作替代循环（如更新整个表，而不是逐行更新）。同时，我们可以提供使用ROW_NUMBER()和CTE等方法来避免循环。最后，生成相关问题。</think>### SQL Server 中的循环实现与替代方案

SQL Server **没有直接的 `FOR` 循环语法**，但可通过 `WHILE` 循环实现类似功能。在数据库操作中，优先推荐基于集合的替代方案以提高性能。以下是具体实现方法和优化建议：

---

### 一、`WHILE` 循环替代方案（模拟 FOR 循环）
#### 语法结构
```sql
DECLARE @counter INT = 1;  -- 初始化计数器
DECLARE @max INT = 10;     -- 设置循环上限

WHILE @counter <= @max
BEGIN
    -- 循环内操作（示例：打印计数）
    PRINT '当前计数: ' + CAST(@counter AS VARCHAR);
    
    -- 更新计数器
    SET @counter = @counter + 1;
END
```

#### 实际应用示例：批量更新订单数据
```sql
DECLARE @BatchSize INT = 1000;  -- 每批处理量
DECLARE @MinID INT = (SELECT MIN(OrderID) FROM Orders);
DECLARE @MaxID INT = (SELECT MAX(OrderID) FROM Orders);

WHILE @MinID <= @MaxID
BEGIN
    UPDATE Orders
    SET CostValue = OrderID + 100
    WHERE OrderID BETWEEN @MinID AND @MinID + @BatchSize;
    
    SET @MinID += @BatchSize + 1;
END
```
> **优势**：分批次处理减少事务锁竞争，性能显著优于游标[^4]。

---

### 二、高性能替代方案（推荐优先使用）
#### 1. 基于集合的批量操作
```sql
-- 直接更新整表（无需循环）
UPDATE Orders
SET CostValue = OrderID + 100
WHERE OrderDate >= '2024-01-01';
```

#### 2. 窗口函数 `ROW_NUMBER()` 分组处理
```sql
;WITH CTE AS (
    SELECT *,
        GroupID = (ROW_NUMBER() OVER (ORDER BY OrderID) - 1) / 1000
    FROM Orders
)
UPDATE CTE
SET CostValue = OrderID + 100
WHERE GroupID = 5;  -- 处理第6组（每组1000条）
```

#### 3. `APPLY` 操作符关联计算
```sql
SELECT c.CustomerID, o.TotalSpent
FROM Customers c
CROSS APPLY (
    SELECT SUM(TotalAmount) AS TotalSpent
    FROM Orders 
    WHERE CustomerID = c.CustomerID
        AND OrderDate BETWEEN '2024-01-01' AND '2024-12-31'
) o;
```
> **性能提示**：此方案避免子查询嵌套，效率提升 3-5 倍[^2]。

---

### 三、关键性能对比
| 方案             | 10万条数据耗时 | 锁竞争 | 适用场景               |
|------------------|---------------|--------|------------------------|
| 游标             | 44+ 秒[^4]    | 高     | 单行复杂逻辑           |
| WHILE 循环       | 5-8 秒        | 中     | 分批次更新             |
| 集合操作         | <1 秒         | 低     | 批量数据处理（推荐）   |
| 窗口函数         | 1-2 秒        | 低     | 分组计算               |

> **最佳实践**：  
> - 90% 的循环需求可通过集合操作实现[^1]  
> - 必须循环时优先用 `WHILE` 分批次处理  
> - 避免在 `WHERE` 中使用子查询[^2]

---

### 相关问题
1. SQL Server 中 `WHILE` 循环和游标的性能差异具体如何量化？  
2. 哪些场景下必须使用循环操作而无法用集合操作替代？  
3. 如何监控和优化 SQL Server 中的批量数据操作性能？  
4. 窗口函数 `ROW_NUMBER()` 在处理大数据集时有哪些性能陷阱？  

[^1]: 游标虽然功能强大，但在处理大量数据时可能会导致性能问题。通过使用 WHILE 循环、SET 操作、ROW_NUMBER() 和 CTE、MERGE 语句、表变量以及 APPLY 操作符等替代方案，可以提高查询性能和代码的可维护性。选择合适的替代方案取决于具体的应用场景和需求。
[^2]: 避免使用子查询：尽量避免在 SELECT 语句中使用子查询，可以改为 JOIN 或 GROUP BY 来提高效率。
[^3]: @@FETCH_STATUS是一个系统变量，用于表示最近一次 FETCH 操作的状态。
[^4]: SQL Server中用While循环替代游标(Cursor)的解决方案。我们用游标的方式给每一条订单添加一个CostValue,耗时44s。

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

  
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

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

              

            

          

      

      余额充值