不走索引场景的一次分析优化

最新推荐文章于 2024-07-18 19:36:34 发布
转载 最新推荐文章于 2024-07-18 19:36:34 发布 · 254 阅读 · 0

本文探讨了在SQL查询中遇到大量数据时如何通过并行处理加速查询速度，并在必要时利用索引来优化执行计划，通过案例分析展示了如何正确配置HINT以引导优化器采用更高效的查询策略。

 

一般一个SQL查询数据量很大，且优化余地不大时，我们必然想开启并行，用并行的方式提高数据的查询速度，然后不是任何情况下开启并行都可以达到最佳运行效果，
有时原本使用索引的执行计划，因为使用并行反而该走全表扫描，因此必须通过hint方式引导优化器采取正确的执行计划，对于如下SQL
SELECT   
                     T1.RPO_NO
                    ,T_LGIN.LGIN_DT
                    ,T_LGIN.USER_ID  
                    ,T_LGIN.USER_IP
                    ,T_LGIN.CLNT_IP
                    ,T_LGIN.MAC_ADDR
                    ,T_LGIN.MENU_SYS_CD
                    ,ROW_NUMBER() OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK
                    FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1
                            ,MCS_HQ.HI_USER_LGIN T_LGIN
                    WHERE   T_LGIN.USER_ID = T1.REQ_ID
                    AND     T_LGIN.LGIN_DT >= TRUNC(T1.REQ_DT)
                    AND     T_LGIN.LGIN_DT <= T1.REQ_DT;
     
     
不采取任何方式人工干预，优化器生成的执行计划将按索引查询表HI_USER_LGIN，但因HI_USER_LGIN表内数据量很大，查询非常消耗资源，因此开启并行，提高查询速度。
开始引入并行的hint如下所示

SQL_ID  7f2gdrbqzv7d8, child number 0
-------------------------------------
SELECT    /*+ PARALLEL(T_LGIN,8) PARALLEL(T1,8) ALL_ROWS */            
                 T1.RPO_NO /*-testNL0000001*/                    
,T_LGIN.LGIN_DT                     ,T_LGIN.USER_ID                    
,T_LGIN.USER_IP                     ,T_LGIN.CLNT_IP                    
,T_LGIN.MAC_ADDR                     ,T_LGIN.MENU_SYS_CD               
           ,ROW_NUMBER() OVER(PARTITION BY T1.RPO_NO ORDER BY
T_LGIN.LGIN_DT DESC) RNK                     FROM   
MCS_HQ_READ.UP_RPO_TRACE_0602 T1                            
,MCS_HQ.HI_USER_LGIN T_LGIN                     WHERE   T_LGIN.USER_ID
= T1.REQ_ID                     AND     T_LGIN.LGIN_DT >=
TRUNC(T1.REQ_DT)                     AND     T_LGIN.LGIN_DT <= T1.REQ_DT
 
Plan hash value: 3061441924
 
-----------------------------------------------------------------
| Id  | Operation                  | Name              | E-Rows |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT           |                   |        |
|   1 |  PX COORDINATOR            |                   |        |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002          |    307K|
|   3 |    WINDOW SORT             |                   |    307K|
|   4 |     PX RECEIVE             |                   |    307K|
|   5 |      PX SEND HASH          | :TQ10001          |    307K|
|*  6 |       HASH JOIN            |                   |    307K|
|   7 |        PX RECEIVE          |                   |    396K|
|   8 |         PX SEND BROADCAST  | :TQ10000          |    396K|
|   9 |          PX BLOCK ITERATOR |                   |    396K|
|* 10 |           TABLE ACCESS FULL| UP_RPO_TRACE_0602 |    396K|
|  11 |        PX BLOCK ITERATOR   |                   |     35M|
|* 12 |         TABLE ACCESS FULL  | HI_USER_LGIN      |     35M|
-----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("T_LGIN"."USER_ID"="T1"."REQ_ID")
       filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT"
              )) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
  10 - access(:Z>=:Z AND :Z<=:Z)
  12 - access(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - dynamic sampling used for this statement (level=5)
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 

从执行计划看，说明优化器并没有按之前的计划采用索引方式扫描HI_USER_LOGIN，而是直接采用并行全表扫描的方式，说明并行不是在原来计划的基础上添加并行，但全表扫描如此大量的数据，
会降低查询速度，因此这里尝试人工干预执行计划，令其查询HI_USER_LOGIN时走索引，查看该表上对应的连接字段上有索引X_HI_USER_LGIN_2(LGIN_DT,USER_ID),因此引导优化器，
令连接的连个表采用嵌套循环连接方式，时扫描大表时，优化器能使用大表上的索引查询连接字段的值，修改hint后的执行计划


 SQL_ID  g1thg0qgnk745, child number 0
-------------------------------------
SELECT  /*+ LEADING(T1) USE_NL(T_LGIN) PARALLEL(T_LGIN,8)
PARALLEL(T1,8) ALL_ROWS */                              T1.RPO_NO
/*-testNL*/                     ,T_LGIN.LGIN_DT                    
,T_LGIN.USER_ID                     ,T_LGIN.USER_IP                    
,T_LGIN.CLNT_IP                     ,T_LGIN.MAC_ADDR                   
 ,T_LGIN.MENU_SYS_CD                           ,ROW_NUMBER()
OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK          
          FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1                     
       ,MCS_HQ.HI_USER_LGIN T_LGIN                     WHERE  
T_LGIN.USER_ID = T1.REQ_ID                     AND     T_LGIN.LGIN_DT
>= TRUNC(T1.REQ_DT)                     AND     T_LGIN.LGIN_DT <=
T1.REQ_DT
 
Plan hash value: 3231175795
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   1 |  PX COORDINATOR                   |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   2 |   PX SEND QC (RANDOM)             | :TQ10001          |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   3 |    WINDOW SORT                    |                   |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   4 |     PX RECEIVE                    |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   5 |      PX SEND HASH                 | :TQ10000          |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   6 |       NESTED LOOPS                |                   |      1 |        |    185K|00:01:06.73 |     720K|  25072 |
|   7 |        NESTED LOOPS               |                   |      1 |    307K|    185K|00:00:44.02 |     538K|   6995 |
|   8 |         PX BLOCK ITERATOR         |                   |      1 |        |  67750 |00:00:01.00 |    2338 |   2129 |
|*  9 |          TABLE ACCESS FULL        | UP_RPO_TRACE_0602 |     15 |    396K|  67750 |00:00:00.97 |    2338 |   2129 |
|* 10 |         INDEX RANGE SCAN          | X_HI_USER_LGIN_2  |  67750 |     16 |    185K|00:00:42.87 |     536K|   4866 |
|  11 |        TABLE ACCESS BY INDEX ROWID| HI_USER_LGIN      |    185K|      1 |    185K|00:00:22.49 |     181K|  18077 |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   9 - access(:Z>=:Z AND :Z<=:Z)
  10 - access("T_LGIN"."USER_ID"="T1"."REQ_ID" AND "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT"))
              AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT")
       filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
 
Note
-----
   - dynamic sampling used for this statement (level=5)
 
 
 显然，使用并行后，需要认为使用hint干预执行计划，使其使用合理的索引，提高查询速度
 
 

SQL_ID  gyn8zxr8uchgs, child number 0
-------------------------------------
SELECT  /*+ LEADING(T1) USE_HASH(T_LGIN) PARALLEL(T_LGIN,8)
PARALLEL(T1,8) ALL_ROWS */                              T1.RPO_NO
/*-test01*/                     ,T_LGIN.LGIN_DT                    
,T_LGIN.USER_ID                     ,T_LGIN.USER_IP                    
,T_LGIN.CLNT_IP                     ,T_LGIN.MAC_ADDR                   
 ,T_LGIN.MENU_SYS_CD                           ,ROW_NUMBER()
OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK          
          FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1                     
       ,MCS_HQ.HI_USER_LGIN T_LGIN                     WHERE  
T_LGIN.USER_ID = T1.REQ_ID                     AND     T_LGIN.LGIN_DT
>= TRUNC(T1.REQ_DT)                     AND     T_LGIN.LGIN_DT <=
T1.REQ_DT
 
Plan hash value: 3061441924
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   1 |  PX COORDINATOR            |                   |      0 |        |      0 |00:00:00.01 |       0 |      0 |
|   2 |   PX SEND QC (RANDOM)      | :TQ10002          |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   3 |    WINDOW SORT             |                   |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   4 |     PX RECEIVE             |                   |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|   5 |      PX SEND HASH          | :TQ10001          |      0 |    307K|      0 |00:00:00.01 |       0 |      0 |
|*  6 |       HASH JOIN            |                   |      1 |    307K|    221K|00:00:46.56 |   66137 |  66027 |
|   7 |        PX RECEIVE          |                   |      1 |    396K|    513K|00:00:00.56 |       0 |      0 |
|   8 |         PX SEND BROADCAST  | :TQ10000          |      0 |    396K|      0 |00:00:00.01 |       0 |      0 |
|   9 |          PX BLOCK ITERATOR |                   |      0 |    396K|      0 |00:00:00.01 |       0 |      0 |
|* 10 |           TABLE ACCESS FULL| UP_RPO_TRACE_0602 |      0 |    396K|      0 |00:00:00.01 |       0 |      0 |
|  11 |        PX BLOCK ITERATOR   |                   |      1 |     35M|   4718K|00:00:06.03 |   66137 |  66027 |
|* 12 |         TABLE ACCESS FULL  | HI_USER_LGIN      |     22 |     35M|   4718K|00:00:04.62 |   66137 |  66027 |
-------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("T_LGIN"."USER_ID"="T1"."REQ_ID")
       filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND
              "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
  10 - access(:Z>=:Z AND :Z<=:Z)
  12 - access(:Z>=:Z AND :Z<=:Z)
 
Note
-----
   - dynamic sampling used for this statement (level=5)
 

 
 
 
 SQL_ID  db4n20k7nt2bh, child number 0
-------------------------------------
SELECT  /*+ LEADING(T1) USE_NL(T_LGIN) */                             
T1.RPO_NO /*-testNL1*/                     ,T_LGIN.LGIN_DT             
       ,T_LGIN.USER_ID                     ,T_LGIN.USER_IP             
       ,T_LGIN.CLNT_IP                     ,T_LGIN.MAC_ADDR            
        ,T_LGIN.MENU_SYS_CD                           ,ROW_NUMBER()
OVER(PARTITION BY T1.RPO_NO ORDER BY T_LGIN.LGIN_DT DESC) RNK          
          FROM    MCS_HQ_READ.UP_RPO_TRACE_0602 T1                     
       ,MCS_HQ.HI_USER_LGIN T_LGIN                     WHERE  
T_LGIN.USER_ID = T1.REQ_ID                     AND     T_LGIN.LGIN_DT
>= TRUNC(T1.REQ_DT)                     AND     T_LGIN.LGIN_DT <=
T1.REQ_DT
 
Plan hash value: 1850855573
 
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |    100 |00:03:53.23 |    5480K|  16439 |  21677 |
|   1 |  WINDOW SORT                  |                   |      1 |    307K|    100 |00:03:53.23 |    5480K|  16439 |  21677 |
|   2 |   NESTED LOOPS                |                   |      1 |        |   1396K|00:03:50.52 |    5480K|  16397 |      0 |
|   3 |    NESTED LOOPS               |                   |      1 |    307K|   1396K|00:03:42.31 |    4111K|  16379 |      0 |
|   4 |     TABLE ACCESS FULL         | UP_RPO_TRACE_0602 |      1 |    396K|    513K|00:00:05.64 |   16479 |  16378 |      0 |
|*  5 |     INDEX RANGE SCAN          | X_HI_USER_LGIN_2  |    513K|      1 |   1396K|00:03:35.82 |    4095K|      1 |      0 |
|   6 |    TABLE ACCESS BY INDEX ROWID| HI_USER_LGIN      |   1396K|      1 |   1396K|00:00:06.86 |    1368K|     18 |      0 |
-------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("T_LGIN"."USER_ID"="T1"."REQ_ID" AND "T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND
              "T_LGIN"."LGIN_DT"<="T1"."REQ_DT")
       filter(("T_LGIN"."LGIN_DT">=TRUNC(INTERNAL_FUNCTION("T1"."REQ_DT")) AND "T_LGIN"."LGIN_DT"<="T1"."REQ_DT"))
 
Note
-----
   - dynamic sampling used for this statement (level=2)
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/750077/viewspace-1815384/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/750077/viewspace-1815384/

csk83922
关注
ClickHouse数据库的索引优化与查询加速技巧
AI天才研究院
07-18 1353
随着企业数字化转型加速，秒级响应的实时报表、用户行为分析等需求激增。ClickHouse作为专为OLAP设计的数据库，理论上能处理亿级数据，但实际性能表现与索引设计强相关。本文聚焦索引优化这一核心环节，覆盖从索引原理到实战调优的全链路知识，帮助开发者解决“查询慢”的痛点。本文从“生活化场景”切入索引原理→拆解ClickHouse特有索引类型→通过代码实战演示优化过程→总结10条可落地的加速技巧→展望未来索引技术趋势，形成“原理-实践-经验”的完整知识闭环。稀疏索引。
mysql 不走索引的场景
weixin_30363509的博客
03-09 514
1.like 不走索引，模糊查询 2.where 中包含DATEFORMAT()函数(表达式的)的不会走索引 3.如果没有业务需求尽量不要添加order by 4.where 中判断字段是否为null 不走索引 5. 转载于:https://www.cnblogs.com/sailormoon/p/8534415.html...
不走索引的一些情形——替你们躺过的坑
qq_41700030的博客
05-07 987
参与一场面试，jvm、多线程、架构设计一一过关，最后死在了这看似简单，但却因为大意忽视了的sql优化上，这里把知道的一些情形都整理罗列出来，防止再次踩坑！ 列与列对比 某个表中，有两列（id和c_id）都建了单独索引，下面这种查询条件不会走索引 select * from test where id=c_id; 这种情况会被认为还不如走全表扫描。测试如下 explain select * from product where PRODUCT_ID =100101 ; 当有两个带索引的列比较时 存在NU
oracle numberover,row_number()over不使用索引问题.
weixin_42389770的博客
04-05 1530
直接查下面语句你就明白为什么不走索引了.selectrow_number()over(partition by f.XXFLRECID order by f.ISSUE_TIME desc) hrfromtest f你都全表数据进行扫描.数据库为什么要选择索引呢?selectrow_number()over(partition by f.XXFLRECID order by f.ISSUE_T...
mysql有索引但不走索引的典型场景
weixin_39723233的博客
07-14 599
1、数据类型发生隐式转换 如下sql 语句中last_name 是varchar类型 select * from student where last_name = 1; 改进方法，使用正确的数据类型 select * from student where last_name = '1'; 2、where 条件表达式等号左侧使用函数 select * from task_table where DATE_ADD(update_time,INTERVAL 2 DAY) < NOW(); 改进方
哪些情况下不走索引
关注系统性能调优
09-11 3788
1.   使用不等于操作符（<>   !=） 2.   NULL值。where字句中出现is null或is not null时，索引就不被使用。 a)     使用函数（索引不是基于函数的索引） 3.   比较不同的数据类型state 为number ,to_char(state) =’2’，这种情况最隐蔽 4.   当索引条件字段选择性低 5.   同一个表中的两列（有索引）
MySQL 索引分析和优化
09-11
唯一性索引和普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建： * 创建索引，例如 `CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);` *...
MySQL不走索引的情况分析
FirstMrRight的博客
08-11 1653
innodb表的统计信息并不是实时统计更新，如果统计信息和实际的索引信息差异很大，就会导致优化器计算各个索引成本后，做出非预期的选择。出现这种现象的场景是：当有大量数据在短时间内落库时，Innodb还没更新统计相关信息，此时来了一个查询，MySQL会基于历史数据做出错误的判断：当前表数据量少，不走索引更高效。回表操作可能会增加额外的磁盘访问和数据检索的开销，因此，在某些情况下，当MySQL判断回表所需的资源大于直接扫描全表时，它可能选择不走索引，而是执行全表扫描。查询频繁是数据表字段增加合适的索引。
MySql数据库中SQL与索引优化的实际案例分析及其应用技巧
03-25
内容概要：本文详述了一次对百万级别数据量下MySql数据库SQL语句的优化过程，重点强调了索引对性能的影响。主要内容涵盖：索引相关理论的基础知识、SQL本身的优化策略（如not in 和 exists的选择），利用EXPLAIN分析...
12种索引失效场景总结
程序员世杰
07-18 3135
联合索引不满足最左匹配原则使用了select *索引列参与运算索引列使用函数错误的like使用类型隐式转换使用OR操作两列做比较<>!不等于比较not innot exists条件order by普通索引优化器认为全表扫描 > 索引扫描。
oracle优化器和不走索引的原因
07-09 256
oracle的优化器(Optimizer) 作者：佚名　来源：dingl.com SQL优化器简介 基于规则的优化器 。总是使用索引 。总是从驱动表开始（from子句最右边的表） 。只有在不可避免的情况下，才使用全表扫描...
sql优化_不要让优化器放弃索引
不积跬步无以至千里，不积小流无以成江海
11-07 2494
一、问题的提出 　在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单
mysql 数据连续不走索引6_学习MySQL的优化方法，工作效率大大提高
weixin_31043675的博客
02-07 264
今天小编来和大家聊一聊项目中常用到的MySQL优化方法，有了这些方法，会让你的效率提升至少3倍。1、EXPLAIN做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。ltype列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。lkey列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。lkey_len列，索引长度。lrows列，扫描行数。...
索引优化(优化场景、失效场景、优化手法)
weixin_44569143的博客
07-31 432
索引可优化的场景 下图是该文章中例题使用表的结构和总行数 mysql> desc usertb; +-------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Ext...
parallel_index不生效
cszt85637的博客
09-17 330
select dealid, orderseqid, dealamount, dealstatus, dealcode, d.payeefee, ...
oracle跳过检查建索引,oracle - 使用PARALLEL提示时Oracle跳过索引 - 堆栈内存溢出
weixin_29164843的博客
04-03 321
我正在使用Oracle数据库“ Oracle Database 12c企业版12.1.0.2.0 64位”我正面临一种行为，我不知道这是对还是错。例如下面的查询SELECT *FROM (SELECT x, y, z, ROW_NUMBER() OVER (PARTITION BY x ORDER BY last_date DESC) ROW1FROM HHHWHERE s = 0AND v_...
oracle查询并行数据,Oracle并行操作之并行查询实例解析(2)
weixin_35796591的博客
04-14 810
SQL> select * from v$px_process;SERVER_NAME STATUSPID SPIDSID SERIAL#----------- --------- ---------- ------------------------ ---------- ----------P000 AVAILABLE25 5776P001 AVAILABLE26 5778...
pro oracle sql 中文,《Pro Oracle SQL》 读书笔记-Chapter 6-6.2 Execution Plans-之4
weixin_28949779的博客
04-14 151
《Pro Oracle SQL》 读书笔记--Chapter 6--6.2 Execution Plans--之四Understanding DBMS_XPLAN in Detail 深入理解DBMS_XPLAN(Page 174)The DBMS_XPLAN package is supplied by Oracle and can be used to simplify the retriev...
oracle使用并行踩过的坑
weixin_44258894的博客
11-19 3120
一、并行机制的简述 并行处理的机制实际上就是把一个要扫描的数据集分成很多小数据集，Oracle会根据初始化参数 PARALLEL_MIN_SERVERS=n的值启动几个并行服务进程同时处理这些小数据集，最后将这些结果汇总，作为最终的处理结果返回给用户。 二、并行使用场景 1、Parallel query（并行查询） 执行并行查询是需要符合以下条件： A、SQL语句中有Hint提示，比如Parallel或者 Parallel_index。 B、SQL语句中引用的对象被设置了并行属性。 C、多表关联中，至少有一
mysql不走索引怎么优化
最新发布
04-18
<think>好的，我现在要处理用户关于MySQL在不使用索引的情况下优化查询性能的问题。首先，我需要回顾之前的对话历史，确保这次回答与之前的建议保持一致，并且参考用户提供的引用内容。用户之前的问题是关于不建索引时的优化方法，而这次他们进一步询问不走索引的优化方法及替代方案，可能希望得到更详细的策略或者不同的角度。 用户提供的引用内容中提到了索引的基础知识、优化方法、执行计划分析等。例如，引用[1]讨论了索引类型、覆盖索引、分页处理，引用[2]提到了执行计划中的Using filesort和Using temporary等额外信息，引用[3]展示了EXPLAIN的输出示例。这些内容需要整合到当前的回答中，确保信息准确且有依据。 接下来，我需要确定用户的实际需求。他们可能因为某些原因无法添加索引，比如表结构不允许、数据量太大导致索引维护成本高，或者只是想在现有结构下寻求临时优化方案。因此，回答需要涵盖多种场景下的优化方法，而不仅仅是重复之前的建议。 首先，回顾之前的回答，已经提到了优化查询语句、分页优化、应用层缓存、表分区、调整服务器配置、数据归档、临时表、数据类型优化等方法。现在需要在这些基础上扩展，同时结合引用中的内容，比如覆盖索引、执行计划分析、避免索引失效的情况，即使在不使用索引的情况下，如何利用现有资源优化。 然后，分析用户当前的查询可能需要避免全表扫描，或者减少数据处理的负担。例如，在无法使用索引的情况下，优化查询语句的写法尤为重要，比如减少返回列、精确过滤条件、避免复杂运算等。此外，分页优化可以通过基于主键或有序字段的定位来减少偏移量的问题，这在之前的回答中已经提到，但可能需要更详细的例子。 引用[3]中的EXPLAIN输出示例显示，即使没有索引，MySQL可能仍然会使用某种类型的扫描，比如INDEX扫描，但需要明确在不使用索引时，如何通过其他手段优化。例如，调整服务器配置如增大缓冲池，可以减少磁盘I/O，这在无索引时尤为重要。 另外，用户可能对执行计划中的Using filesort和Using temporary有疑问，这些是性能瓶颈的常见指标。需要解释如何通过优化查询来避免这些临时表和文件排序，比如优化ORDER BY和GROUP BY子句，使用合适的聚合方式，或者在应用层处理排序。 还需要考虑数据归档和分区，将历史数据分离，减少活跃数据量，从而降低全表扫描的时间。同时，临时表的合理使用可以分解复杂查询，减少单次查询的负载。 最后，确保回答结构清晰，分点列出，每个方法都有实际例子和引用支持，例如引用[1]中的分页处理，引用[2]中的执行计划指标，引用[3]中的EXPLAIN分析。同时，总结关键原则，帮助用户快速理解不同方法的适用场景和原理。 需要避免重复之前的内容，而是补充新的角度，例如应用层缓存的具体实现、如何有效使用分区表、调整配置的具体参数建议，以及数据类型优化的具体案例。同时，强调即使没有索引，通过多方面的综合优化仍然可以提升性能，但长期来看，合理使用索引仍是更有效的解决方案。</think>### MySQL无索引查询优化方法与替代方案 即使无法使用索引，通过以下方法仍可提升查询性能。以下方案基于**查询优化**、**数据存储优化**、**架构调整**三个维度展开，结合执行计划分析和实际场景应用。 --- #### 一、**查询语句优化** 1. **精确过滤条件** - 添加更多筛选条件缩小结果集，减少全表扫描的数据量 ```sql -- 低效：全表扫描后筛选 SELECT * FROM orders WHERE status = 'pending'; -- 优化：添加时间范围缩小扫描量 SELECT order_id FROM orders WHERE status = 'pending' AND created_at > '2023-01-01'; ``` 2. **避免复杂运算与函数** - 将计算逻辑（如`DATE_FORMAT`、`CONCAT`）移至应用层，减少数据库负载 ```sql -- 低效：在WHERE中使用函数 SELECT * FROM users WHERE YEAR(created_at) = 2023; -- 优化：应用层计算时间范围 SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31'; ``` 3. **减少返回字段** - 仅选择必要字段，降低网络传输和内存占用 ```sql -- 低效：返回所有字段 SELECT * FROM products; -- 优化：仅返回必要字段 SELECT product_id, name FROM products; ``` --- #### 二、**分页优化技巧** 1. **主键定位法** 利用有序字段（如自增ID）跳过偏移量，避免扫描无效数据[^1]： ```sql -- 低效：大偏移量全表扫描 SELECT * FROM users LIMIT 100000, 20; -- 优化：通过ID定位起始点 SELECT * FROM users WHERE id > 100000 LIMIT 20; ``` 2. **预计算分页边界** 在应用层记录上一页的最大ID，直接定位下一页起始位置。 --- #### 三、**执行计划分析与优化** 通过`EXPLAIN`分析查询行为，重点关注以下指标[^2]： - **Using filesort**：优化`ORDER BY`子句，减少排序字段数量 - **Using temporary**：避免复杂`GROUP BY`，或拆分查询为多个步骤 - **Using where**：确保过滤条件尽早生效 **示例优化过程**： ```sql -- 原始查询（存在Using temporary和Using filesort） EXPLAIN SELECT department, COUNT(*) FROM employees GROUP BY department ORDER BY COUNT(*) DESC; -- 优化步骤1：拆分GROUP BY和排序到应用层 -- 步骤2：使用临时表存储中间结果 CREATE TEMPORARY TABLE temp_dept_count AS SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department; SELECT * FROM temp_dept_count ORDER BY cnt DESC; ``` --- #### 四、**数据存储与结构优化** 1. **表分区** 按时间或范围分区，缩小物理扫描范围： ```sql -- 按年份分区 CREATE TABLE logs ( id INT, log_time DATETIME ) PARTITION BY RANGE (YEAR(log_time)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024) ); ``` 2. **数据归档** 定期迁移历史数据到归档表，减少活跃表体积： ```sql -- 迁移一年前的订单数据 INSERT INTO archive_orders SELECT * FROM orders WHERE created_at < '2022-01-01'; DELETE FROM orders WHERE created_at < '2022-01-01'; ``` 3. **紧凑数据类型** - 用`INT`代替`VARCHAR`存储数字型ID - 使用`ENUM`替代低基数字符串字段 --- #### 五、**服务器与配置优化** 1. **内存配置调优** - 增加`innodb_buffer_pool_size`（建议为物理内存的70%~80%）[^3] - 调整`sort_buffer_size`和`join_buffer_size`，提升排序和连接性能 2. **临时表优化** - 增大`tmp_table_size`，减少磁盘临时表使用 --- #### 六、**架构层替代方案** 1. **读写分离** 通过主从复制分散读请求，避免单节点压力。 2. **应用层缓存** - 高频查询结果缓存到Redis，设置合理过期时间 - 示例：缓存每日活跃用户列表，每隔1小时更新 3. **异步批处理** 将实时性要求低的查询改为定时任务，如生成报表： ```python # 定时任务示例（每小时执行一次） def generate_daily_report(): data = db.query("SELECT COUNT(*) FROM orders WHERE created_at > CURDATE()") cache.set("daily_orders", data) ``` --- ### 关键方案对比 | 方法 | 适用场景 | 性能提升原理 | 实施复杂度 | |---------------------|-------------------------|--------------------------|----------| | 查询语句优化 | 所有无索引查询 | 减少计算与数据传输 | 低 | | 分页主键定位 | 有序数据分页 | 避免全表扫描 | 中 | | 表分区 | 时间序列或范围数据 | 物理缩小扫描范围 | 高 | | 应用层缓存 | 高频重复查询 | 减少数据库访问次数 | 中 | --- ### 总结 无索引优化的核心是**减少全表扫描数据量**和**降低单次查询复杂度**。但需注意： 1. 这些方法适用于**短期临时优化**，长期仍需通过索引或架构升级解决根本问题[^1] 2. 结合`EXPLAIN`分析执行计划，针对性优化高代价操作（如`Using filesort`）[^2] 3. 在应用层分担数据库压力（缓存、异步处理）是最有效的补充方案[^3]
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值