优化SQL的一般步骤

最新推荐文章于 2024-03-08 09:24:31 发布
最新推荐文章于 2024-03-08 09:24:31 发布
阅读量323 收藏
点赞数
分类专栏： # MySQL SQL优化 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33033819/article/details/106484112
版权

文章目录

优化SQL的一般步骤

一、分析执行计划

​ 1、通过 EXPLAIN 分析低效 SQL 的执行计划

mysql> explain select sum(amount) from customer a, payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANE.BENNETT@sakilacustomer.org' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

输出项说明

# select_type ：表示 SELECT 的类型，常见的取值有 
							SIMPLE（简单表，即不使用表连接或子查询）、
							PRIMARY（主查询，即外层的查询）、
							UNION（UNION 中的第二个或者后面的查询语句）、
							SUBQUERY（子查询中的第一个 SELECT ）
							
# table ：输出结果集的表
# type : 表示 MySQL 在表中找到所需行的方式，或者叫访问类型，常见的类型如下,从左至右，性能由最差到最好
					ALL < index < range < ref < eq_ref < const,system < NULL
					
					1） ALL ：全表扫描， MySQL 遍历全表来找到匹配的行。
					2） index：索引全扫描， MySQL 遍历整个索引来查询匹配的行。
					3） range：索引范围扫描，常见于 < 、 <= 、 > 、 >= 、 between 等操作符。
					4） ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值得记录行。
					5） eq_ref：类似 ref ，区别在于使用的索引是唯一索引，也就是在多表连接中使用 primary key 或者 unique index 作为关联条件。
					6） const,system：单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理。
					7） NULL：MySQL 不用访问表或者索引，直接就能够得到结果。
					
					8） ref_of_null：与 ref 类似，区别在于条件中包含对 NULL 的查询。
					9） index_merge：索引合并优化。
					10）unique_subquery：in 的后面是一个查询主键字段的子查询。
					11）index_subquery：与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询
					
# possible_keys ：表示查询是可能使用的索引。
# key ：表示实际使用的索引。
# key_len ：使用到索引字段的长度。
# rows ：扫描行的数量。
# Extra ：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
          1) Using where：表示优化器除了利用索引来加快访问之外，还需要根据索引回表查询数据。
          2) Using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表，也就是平时说的覆盖索引扫描。
          3) Using index condition：表示 MySQL 使用了 ICP（Index Condition Pushdown）来进一步优化查询。Pushdown 表示操作下放，某些情况下的条件过滤操作下放到存储引擎。
          4) Using filesort：不是通过索引直接返回排序结果的排序都叫 Filesort 排序
          
# filtered ：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例

2、分区表执行计划

​ 使用 explain partitions 的输出结果中有一列 partitions ，其中显示 SQL 所需要访问的分区名字 p2

mysql> explain partitions select * from customer_part where customer_id = 130 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer_part
   partitions: p2
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)
二、查看执行过程中线程的每个状态和消耗时间

​ 方法 1：通过 show profile 分析

​ 1) 分析 SQL 执行过程中线程的每个状态和消耗时间

​ 注意：在 MySQL 5.7 中，profile 已经不建议使用，而使用 performance schema 中的一系列性能视图来代替。

#1) 查看是否支持 profile
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set, 1 warning (0.00 sec)

#2) 默认 profiling 是关闭的，可以通过 set 语句在 Session 级别开启 profiling
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)


#3) 首先，在一个 InnoDB 引擎的付款表 payment 上，执行一个 count(*) 查询。
mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
|    16049 |
+----------+
1 row in set (0.32 sec)


#4) 执行完毕后，通过 show profiles 语句，看到当前 SQL 的 Query_ID 为 2。
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00026275 | select @@profiling           |
|        2 | 0.32845575 | select count(*) from payment |
+----------+------------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

#5) 通过 show profile for query 语句能够看到执行过程中线程的每个状态和消耗时间。
mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000174 |
| checking permissions | 0.000016 |
| Opening tables       | 0.000024 |
| init                 | 0.000019 |
| System lock          | 0.000012 |
| optimizing           | 0.000007 |
| statistics           | 0.000017 |
| preparing            | 0.000015 |
| executing            | 0.000004 |
| Sending data         | 0.328066 |
| end                  | 0.000020 |
| query end            | 0.000016 |
| closing tables       | 0.000012 |
| freeing items        | 0.000029 |
| cleaning up          | 0.000026 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

​ Sending data 状态表示 MySQL 线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端，由于 Sending data 状态下，MySQL 线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

​ 2) 获取到最耗时的线程状态后，MySQL 支持进一步选择 all、cpu、block io、context、switch、page faults 等明细类型来查看 MySQL 在使用什么资源上耗费过高的时间

mysql> show profile cpu for query 2;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000174 | 0.000037 |   0.000133 |
| checking permissions | 0.000016 | 0.000003 |   0.000010 |
| Opening tables       | 0.000024 | 0.000005 |   0.000019 |
| init                 | 0.000019 | 0.000004 |   0.000014 |
| System lock          | 0.000012 | 0.000003 |   0.000009 |
| optimizing           | 0.000007 | 0.000001 |   0.000006 |
| statistics           | 0.000017 | 0.000004 |   0.000014 |
| preparing            | 0.000015 | 0.000003 |   0.000011 |
| executing            | 0.000004 | 0.000001 |   0.000003 |
| Sending data         | 0.328066 | 0.134053 |   0.446006 |
| end                  | 0.000020 | 0.000002 |   0.000009 |
| query end            | 0.000016 | 0.000004 |   0.000012 |
| closing tables       | 0.000012 | 0.000002 |   0.000010 |
| freeing items        | 0.000029 | 0.000007 |   0.000022 |
| cleaning up          | 0.000026 | 0.000006 |   0.000020 |
+----------------------+----------+----------+------------+
15 rows in set, 1 warning (0.00 sec)

​ 方法 2 ：查询 information_schema.profiling 表

​ 可以更清晰的看到 SQL 执行过程中线程的每个状态和消耗时间和当前线程占总消耗时间的百分比。

mysql> select state,sum(duration) as total_r,round(100 * sum(duration)/(select sum(duration) from information_schema.profiling where query_id = 2),2) as pct_r,count(*) as calls,sum(duration)/count(*) as "R/Call" from information_schema.profiling where query_id = 2 group by state order by total_r desc;
+----------------------+----------+-------+-------+--------------+
| state                | total_r  | pct_r | calls | R/Call       |
+----------------------+----------+-------+-------+--------------+
| Sending data         | 0.328066 | 99.88 |     1 | 0.3280660000 |
| starting             | 0.000174 |  0.05 |     1 | 0.0001740000 |
| freeing items        | 0.000029 |  0.01 |     1 | 0.0000290000 |
| cleaning up          | 0.000026 |  0.01 |     1 | 0.0000260000 |
| Opening tables       | 0.000024 |  0.01 |     1 | 0.0000240000 |
| end                  | 0.000020 |  0.01 |     1 | 0.0000200000 |
| init                 | 0.000019 |  0.01 |     1 | 0.0000190000 |
| statistics           | 0.000017 |  0.01 |     1 | 0.0000170000 |
| checking permissions | 0.000016 |  0.00 |     1 | 0.0000160000 |
| query end            | 0.000016 |  0.00 |     1 | 0.0000160000 |
| preparing            | 0.000015 |  0.00 |     1 | 0.0000150000 |
| System lock          | 0.000012 |  0.00 |     1 | 0.0000120000 |
| closing tables       | 0.000012 |  0.00 |     1 | 0.0000120000 |
| optimizing           | 0.000007 |  0.00 |     1 | 0.0000070000 |
| executing            | 0.000004 |  0.00 |     1 | 0.0000040000 |
+----------------------+----------+-------+-------+--------------+
15 rows in set, 2 warnings (0.00 sec)
三、分析优化器如何选择执行计划

​ 使用 trace 分析优化器如何选择执行计划

#1) 打开 trace ，设置格式为 JSON 。
mysql> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

#2) 设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。
mysql> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)

#3) 接下来执行想做 trace 的 SQL 语句。
mysql> select rental_id from sakila.rental where 1=1 and rental_date >= '2005-05-25 04:00:00' and rental_date <= '2005-05-25 05:00:00' and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
|        39 |
+-----------+
1 row in set (0.34 sec)

#4) 然后，检查 information_schema.OPTIMIZER_TRACE 就可以知道 MySQL 是如何执行 SQL 语句的。
mysql> select * from information_schema.OPTIMIZER_TRACE \G;
四、确定问题并采取相应的优化措施
#1) 从执行计划可以确认对客户表 customer 的全表扫描导致不理想。
mysql> explain select sum(amount) from sakila.customer a,sakila.payment b where 1=1 and a.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

#2) 对 customer 的 email 字段创建索引
mysql> create index idx_email on sakila.customer(email);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

#创建索引后对客户表 customer 需要扫描的行数明显减少。
mysql> explain select sum(amount) from sakila.customer a,sakila.payment b where 1=1 and a.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ref
possible_keys: PRIMARY,idx_email
          key: idx_email
      key_len: 203
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)
sunset_ggz
关注
专栏目录
SQL取数步骤级常用优化参数修改说明.docx
12-19
"SQL取数步骤级常用优化参数修改说明" 在本文档中，我们将讨论 SQL 取数步骤级常用优化参数修改的相关知识点。 SQL 取数步骤级常用优化参数修改 SQL 取数步骤级常用优化参数修改是指在 SQL 取数过程中，对参数...
Oracle SQL tuning 数据库优化步骤分享(图文教程)
09-10
以下是优化SQL语句的基本步骤： 1. **建立连接**：在Quest Central的"Database"树中选择"SQL Server"，然后在"Tools"框中选择"SQL Tunning"，输入数据库连接信息并建立连接。 2. **分析原始SQL语句**：在连接成功...
SQL优化一般步骤
qq_43163507的博客
07-12 937
类型type还有其他值，如ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）、index_merge（索引合并优化）、unique_subquery（in 的后面是一个查询主键字段的子查询）、index_subquery（与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）等。对于含有or的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引。对索引所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。
百万数据查询优化技巧三十则,新增5条
weixin_34360651的博客
07-30 143
百万数据查询优化技巧三十则 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：...
SQL优化的一般步骤
yozzs的博客
10-23 2291
在MySQL数据库中，当面对一个有sql性能问题的数据库时，我们可以使用show status、explain、show profile等命令进行系统分析，尽快定位问题SQL并解决问题。 一、通过show status命令了解各种SQL的执行情况 mysql&gt; show status like 'Com_%'; +---------------------------+-------+ | ...
SQL优化的一般步骤（参考MySQL）
qq_70095877的博客
11-22 159
在不清楚为什么效率查询慢的时候 ，我们大多数都认为与数据库有关。那我们首先查询 MySQL 数据库的运行状态 ，通过跑了一天的生产环境 ， 我们能查到数据库执行的所有SQL的类型（CRUD）以及执行时间。
SQLServer 优化SQL语句 in 和not in的替代方案
09-11
在SQL Server中，优化SQL语句是提升数据库性能的关键环节。在处理大量数据时，尤其需要注意避免使用效率较低的操作符，如`IN`和`NOT IN`。这些操作符虽然在编写时提供了简洁和易读性，但在执行效率上往往不如其他...
SQL优化（一）、sql优化一般步骤
热门推荐
小浪与艳花
10-20 2万+
sql优化一般步骤概要： 1 通过 show status 命令了解各种sql的执行频率 2 定位执行效率较低的sql语句 3 通过explain分析低效sql的执行计划 4 通过 show profile 分析sql 5 通过trace分析 优化器 如何选择执行计划 6 确定问题并采取相应的优化措施 以下阐述每步具体操作，请详细跟进操作，会有不错的收货！ 1 通过 show status 命...
SQL语句的优化步骤（一）
abcdef_12a的博客
11-29 354
mysql数据库是常用的一种数据库处理语言，也是限制IT行业应用较广的一种数据库语言。数据库是用来处理数据，对数据进行增删改查（CRUD），对于大容量的数据处理来说，需要处理的事件可能有1天，2天，那对SQL语句的优化就至关重要了。 以下从5个方式来初步理解SQL优化的基本步骤 一、查询执行效率 1、查看数据库中各命令的使用频率 show global status like 'com_______' 2、查看innoDB在进行增删改查时的频率 sho...
优化SQl语句的十个重要步骤：
03-24
优化SQl语句的十个重要步骤：优化SQl语句的十个重要步骤：
Mysql优化的一般步骤
拂晓的专栏
04-17 1996
一、数据库设计要合理 表的设计要符合3NF，有时需要适当的逆范式 1NF为属性的原子性约束，只要是关系型数据库均满足1NF。2NF对记录的唯一性约束，要求记录有唯一的标识(主键)。3NF在满足2NF的基础上，对字段冗余性的约束，任何字段不能由其他字段派生出来。 没有冗余的数据库未必是好的数据库，有时为了提高运行效率适当降低范式标准。(如统计一个相册总的点击量时，增加一个点击量字段
优化SQL语句的一般步骤
zuimei_forver的博客
04-04 762
当面对一个有SQL性能的数据库时，我们可以从以下步骤来对数据库进行分析。1.通过show status命令了解各种SQL的执行频率    MySQL客户端连接成功后，通过show [session | global] status命令可以提供服务器状态信息。session为当前连接的统计结果；global为数据库启动至今的统计结果；默认为session。     以下为常用的统计参数：
Sql优化-1 优化 SQL 语句的一般步骤
ChinaMuZhe的博客
06-29 391
  当面临sql性能优问题时，可以通过以下步骤，定位到问题sql并解决问题。 第一步：通过show status命令来了解各种sql的执行频率   MySQL 客户端连接成功后，通过 show [session|global]status 命令可以提供服务器状态信息，也可以在操作系统上使用 mysqladmin extended-status 命令获得这些消息。show[session|glo...
SQL优化的步骤
weixin_30367873的博客
07-08 280
一、优化SQL的方法 SQL优化的一般步骤：先查询mysql数据库运行状况，然后定位慢查询，再分析sql的执行过程，然后进行优化 1.使用show status查询数据库的运行状况 //显示数据库运行状态 SHOW STATUS //显示数据库运行总时间 SHOW STATUS LIKE 'uptime' //显示连接的次数 SHOW STATUS LIKE 'co...
优化SQL语句的一般步骤②
RandolfJay的博客
10-03 492
1.通过慢查询日志分析数据，找出运行慢的sql语句 2.分析原因，看是否有必要添加索引，或者修改sql条件的顺序
SQL优化的步骤流程
王林冲的博客
09-10 1139
一、优化SQL的方法 SQL优化的一般步骤：先查询mysql数据库运行状况，然后定位慢查询，再分析sql的执行过程，然后进行优化 1.使用show status查询数据库的运行状况 //显示数据库运行状态 SHOW STATUS //显示数据库运行总时间 SHOW STATUS LIKE 'uptime' //显示连接的次数 SHOW STATUS LIKE 'connections' /...
SQL 优化步骤
qq_38560825的博客
10-26 622
注：SQL 优化能够让开发人员写出更加高效的SQL 语句，从而提高系统效率。 1. 优化SQL步骤 1.1 查看SQL执行效率 注：下述的命令能够得出数据库哪种操作使用更加频繁。 -- 展示以 Com 开头的数据库状态信息（本次连接） show status like 'Com_______'; -- 展示以 Com 开头的数据库状态信息（全局） show global status like 'Com_______'; 图2. 整个数据库的状态图1. 数据库一次连接的状态 ...
SQL优化的步骤和方法
最新发布
zyjzyjjyzjyz的博客
03-08 1178
这几天看到群里高手发的sql优化的基本思路步骤：增加过滤条件，创建合适索引，简化复杂sql，减少io扫描。下面将对sql优化的步骤和方法进行介绍。
Oracle SQL优化详解：步骤与策略
了解【Oracle优化器】的工作原理，如成本基优化器（CBO），是优化SQL的关键。 【Oracle的执行计划】部分，详细阐述了如何阅读和分析执行计划，以识别性能瓶颈。【注意事项】可能包括避免过度优化、考虑系统整体平衡...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值