sql 优化步骤

下面知识点原文章：https://developer.aliyun.com/article/973568

1 目标sql

SELECT 
C.CONTRACT_CODE AS ContractCode, 
D.DECLARATION_NO AS declarationNo,
D.DECLARATION_CODE AS declarationCode,
C.CCN AS SOCCN,
'1' AS `Declare`,
S1.SECTION_ID AS Section,
CASE WHEN S1.`STATUS` = '1' THEN S1.STAFF_ID ELSE S1.STAFF_NAME END AS Applicant,
CASE WHEN S2.`STATUS` = '1' THEN S2.STAFF_ID ELSE S2.STAFF_NAME END AS Responsible_Officer,
'' AS staffCode,
CASE WHEN D.URGENCY = '1' THEN '-8136175497130980501' ELSE '8616459757770788697' END AS Urgency,
CONCAT(COALESCE(D.DECLARATION_NAME, ''),'/', COALESCE(CC2.COMPANY_NAME, ''),'(',SUBSTRING(D.CREATE_DATE, 1, 4),')-', CASE WHEN D.DECLARATION_TYPE = '0' THEN 'NEW' WHEN D.DECLARATION_TYPE = '1' THEN 'CHANGE' ELSE 'TERMINATION' END) AS Subject,
CASE WHEN D.DECLARATION_TYPE = '0' THEN '1395341742952695992' WHEN D.DECLARATION_TYPE = '1' THEN '5554482302239525360' ELSE '4008668884422379081' END AS Operation_Of_Contract,
D.BACK_GROUND AS Background_Info,
DC.CONTRACT_VALUE AS Estimated_Contract_Value,
'' AS Contract_Type,
CASE WHEN DC.SIGNIFICANT_CONTRACT = '1' THEN '1260554007226566010' WHEN DC.SIGNIFICANT_CONTRACT = '2' THEN '8722973336077618215' ELSE '-4138754645967091409' END AS Significant,
CASE WHEN DC.AUTO_RENEW = '1' THEN '6946411676681456370' WHEN DC.TERM_TYPE = '2' THEN '6619427055234793886' ELSE '-2455786127955115412' END AS Term_Type,
DC.NUM_SIGNED_CONTRACT AS No_of_originals_of_signed_contract_needed,
DC.ESTIMATED_COMMEN_DATE AS Estimated_Effective_Date,
DC.EXPIRY_DATE AS Estimated_Terminantion_Date,
CRR.SIGNING_DATE AS Signing_Date,
CRR.EFFECTION_DATE AS Effective_Date,
CRR.TERMINATION_DATE AS Expiry_Date,
'8472449952866845352' AS Termination_Notice_Period_Unit,
'90' AS Termination_Notice_Period,
DC.GOVEMING_LAW AS Governing_Law,
CASE WHEN DC.ANY_ARBITRATION = '0' THEN '-6679378182927464933' ELSE '5947946289657628735' END AS Arbitration_Clause,
DC.COURT_JRRISDICTION AS Court_Jurisdiction,
CASE WHEN DC.ANY_EXCLUSIVITY = '0' THEN '-6679378182927464933' ELSE '5947946289657628735' END AS Exclusive_Agent,
CC.COMPANY_NAME AS Name_Of_HKA_Entity,
CONCAT(COALESCE(DC.NOTE_TERM, ''), COALESCE(CONCAT('\n',D.REMARK), ''), COALESCE(CONCAT('\n',DT.REMARK), '')) AS Special_notes_Other_unusual_terms,
'' AS Other_Supplement_File,
D.SO_DOC_CODE AS soDocCode,
D.SO_DOC_NO AS soDocNo,
CASE WHEN D.DECLARATION_STATUS IN ('2','1','5','6','3','7') THEN '2950458168167318169' WHEN D.REFERENCE_STATUS = '1' THEN '-6013631347856355871' ELSE '-9176650534254591873' END AS Archive_Status,
'' AS Replenish_No,
'' AS User_Organization,
'' AS Contract_Approval_Attachment,
'' AS Replenish_Attachment,
CASE WHEN SC.CONFIDENTIALITY = '02' THEN '2829909847111389072' WHEN SC.CONFIDENTIALITY = '03' THEN '-4579811989845160133' ELSE '-7657278420850163186' END AS Confidentiality,
CC2.COMPANY_NAME AS Name_Of_Contract_Counterparties,
D.DECLARATION_NAME AS Name_Of_Contract,
CASE WHEN CC1.COMPANY_TYPE = '1' THEN '1375932225006814690' ELSE '5431022286405126496' END AS Contract_Drafter,
D.SHORT_DESC AS Scope_Of_Service,
'' AS Attach_Contract,
'' AS SubContract_Type,
CASE WHEN C.STATUS = '4' THEN '1783012442868369467' WHEN C.STATUS = '1' THEN '4801899542179759787' WHEN C.STATUS = '7' THEN '2815518161343106644' 
WHEN C.STATUS = '8' THEN '-7391779020705607697' WHEN C.STATUS = '2' THEN '6540816724978189615' WHEN C.STATUS = '6' THEN '-4350861002212423160' 
WHEN C.STATUS = '3' THEN '3057032933114268085' WHEN C.STATUS = '5' THEN '-1609758532644943923' END AS Contract_Status,
CASE WHEN C.STATUS = '2' OR C.STATUS = '8' THEN DETI.TERMINATION_DATE END AS Expexted_Termination_Date,
CASE WHEN C.STATUS = '2' OR C.STATUS = '8' THEN DETI.TERMINATION_REASON END AS Termination_Reason,
'' AS Termination_Attachment,
'' AS originalDeclarationNo,
'' AS noticeDays,
'' AS estimatedNoticeDate,
'' AS Contract_No,
DT.SO_DOC_NO AS TerminationSONo,
DT.DECLARATION_NO AS TerminationPreNo,
S1.DEPARTMENT_ID AS Department,
'' AS Termination_Replenish_Attachment,
CASE WHEN DC.AUTO_RENEW = '1' THEN DC.AUTO_RENEW_INTERVALS_MONTH END AS AutoRenewMonth,
CASE WHEN DC.AUTO_RENEW = '1' THEN DC.AUTO_RENEW_INTERVALS_YEAR END AS AutoRenewYear,
SUBSTRING(D.CREATE_DATE, 1, 4) AS createYear

FROM CMS_CONTRACT C 
LEFT JOIN SYNC_PRESO_DATA_POSITION_MAPPING SP ON C.CONTRACT_CODE = SP.DATA_CMS_PRIMARY_VAL AND SP.DATA_PRESO_POSI_KEY = 'NEWSO-CODE'
LEFT JOIN DECLARATION D ON C.DECLARATION_CODE = D.DECLARATION_CODE AND D.DECLARATION_STATUS = '4'
LEFT JOIN SO_CONFIDENTIALITY SC ON D.SO_DOC_NO = SC.DOC_NO
LEFT JOIN CMS_STAFF CS ON D.APPLICANT = CS.STAFF_CODE LEFT JOIN NEWSO_STAFF S1 ON CS.STAFF_NO = S1.STAFF_NO
LEFT JOIN CMS_STAFF CR ON D.PERSON_LIABLE = CR.STAFF_CODE LEFT JOIN NEWSO_STAFF S2 ON CR.STAFF_NO = S2.STAFF_NO
LEFT JOIN DECL_CONTRACT_INFO DC ON D.DECLARATION_CODE = DC.DECALRATION_CODE
LEFT JOIN CONTRACT_COMPANY_RELATION CCR ON DC.CONTRACT_INFO_CODE = CCR.DECL_CONTRACT_CODE AND CCR.COMPANY_TYPE = '1' LEFT JOIN CMS_COMPANY CC ON CCR.COMPANY_CODE = CC.COMPANY_CODE
LEFT JOIN CONTRACT_COMPANY_RELATION CCR2 ON DC.CONTRACT_INFO_CODE = CCR2.DECL_CONTRACT_CODE AND CCR2.COMPANY_TYPE = '2' LEFT JOIN CMS_COMPANY CC2 ON CCR2.COMPANY_CODE = CC2.COMPANY_CODE
LEFT JOIN CMS_COMPANY CC1 ON DC.INITIAL_DRAFTER_CODE = CC1.COMPANY_CODE
LEFT JOIN CONTRACT_REPLENISH CRR ON C.CONTRACT_CODE = CRR.CONTRACT_CODE
LEFT JOIN DECLARATION DT ON C.CONTRACT_CODE = DT.ASSOCIATED_CONTRACT_CODE AND DT.DECLARATION_TYPE = '2' AND DT.DECLARATION_STATUS = '4' AND DT.SO_DOC_NO != ''
LEFT JOIN DECL_EARLY_TERMINATION_INFO DETI ON DT.DECLARATION_CODE = DETI.DECLARATION_CODE

WHERE SP.ID IS NULL  
LIMIT 0,1

执行计划

解决方案：

1）根据以下知识点type=all的表加上索引 此步使耗时21s到了3s

知识

1. id 列

id 列是一个编号，用于标识 SELECT 查询的序列号，表示执行 SQL 查询过程中 SELECT 子句或操作表的顺序。

如果在 SQL 中没有子查询或关联查询，那么 id 列都将显示一个 1。否则，内层的 SELECT 语句一般会顺序编号。

id 列分为三种情况：

1）id 相同

如下普通查询，没有子查询。

explain select f.* from film f,film_actor fa,actor a where f.film_id = fa.film_id and fa.actor_id = a.actor_id and a.first_name = 'NICK';

2）id 不同

如果存在子查询，id 的序号会递增，​id 值越大优先级越高，越先被执行​。

explain select * from film where film_id = (select film_id from film_actor where actor_id = 2 limit 1);

3）id 相同又不同

1）、2）两种情况同时存在。id 如果相同，认为是一组，从从上往下执行。在所有组中，id 值越大，优先级越高，越先执行。

2. select_type 列

select_type 列表示对应行的查询类型，是简单查询还是复杂查询，主要用于区分普通查询、联合查询、子查询等复杂的查询。

select_type 列有如下值：

select_type值	说明
SIMPLE	简单查询，意味着不包括子查询或UNION。
PRIMARY	查询中包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY	在select或where列表中包含了子查询
DERIVED	表示包含在from子句的子查询中的select，MySQL 会递归执行并将结果放到一个临时表中，称其为“派生表”，因为该临时表是从子查询中派生而来的。
UNION	第二个 select 出现在UNION之后，则被标记为UNION。
UNION RESULT	从UNION表获取结果的select。

3. table 列

table 列表示对应行正在执行的哪张表，指代对应表名，或者该表的别名(如果 SQL 中定义了别名)。

4. partitions 列

查询涉及到的分区。

5. type 列

type 列指代访问类型，是 MySQL 决定如何查找表中的行。

是 SQL 查询优化中一个很重要的指标，拥有很多值，依次从最差到最优：

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

1）ALL

众所周知的​全表扫描​，表示通过扫描整张表来找到匹配的行，很显然这样的方式查询速度很慢。

这种情况，性能最差，在写SQL时尽量避免此种情况的出现。

2）index

​全索引扫描​，和全表扫描 ALL 类似，扫描表时按索引次序进行，而不是按行扫描，即：只遍历索引树。

index 与 ALL 虽然都是读全表，但 index 是从索引中读取，而 ALL 是从硬盘读取。显然，index 性能上优于 ALL，​合理的添加索引将有助于性能的提升​。

举例如下：

explain select title from film;
explain select description from film;

通过 explain 结果来看，只查询表 film 中字段 title 时，是按照索引扫描的(type 列为 index)，倘若查询字段 description，却是按照全表扫描的(type 列为 ALL)。这是为何呢？

接下来，我们不妨看看表 film 的结构：

从 desc film 结果来看，字段 title 创建的有索引，而字段 description 没有，所以 select title from film 是按索引扫描，而 select description from film 按全表扫描。

从上面的举例对比中，也充分印证了索引的重要性。

3）range

​只检索给定范围的行​，使用一个索引来选择行。key 列显示使用了那个索引。一般就是在 where 语句中出现了 bettween、<、>、in 等的查询。这种索引列上的范围扫描比全索引扫描 index 要好。

举例如下：

4）ref

​非唯一性索引扫描​，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它属于查找和扫描的混合体。

此类型只有当使用非唯一索引或者唯一索引的非唯一性前缀时，才会发生。

举例如下：

show index from film;
explain select * from film where title = 'ACADEMY DINOSAUR';

5）eq_ref

唯一索引扫描。 常见于主键或唯一索引扫描。

6）const

通过索引一次就能找到，const 用于比较 primary key 或者 unique 索引。因为只需匹配一行数据，所有很快。如果将主键置于 where 列表中，MySQL 就能将该查询转换为一个 const。

举例如下：

show index from film;
explain select * from film where film_id = 1;

7）system

表只有一行记录，这是 const 类型的特例，比较少见，如：系统表。

6. possible_keys 列

显示在查询中使用了哪些索引。

7. key 列

实际使用的索引，如果为 NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在 key 列中。

possible_keys 列表明哪一个索引有助于更高效的查询，而 key 列表明实际优化采用了哪一个索引可以更加高效。

举例如下：

show index from film_actor;
explain select actor_id,film_id from film_actor;

8. key_len 列

表示索引中使用的字节数，查询中使用的索的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len 是根据表定义计算而得的，不是通过表内检索出的。

9. ref 列

表示在 key 列记录的索引中查找值，所用的列或常量 const。

10. rows 列

估算出找到所需行而要读取的行数。

这个数字是内嵌循环关联计划里的循环数，它并不是最终从表中读取出来的行数，而是 MySQL 为了找到符合查询的那些行而必须读取行的平均数，只能作为一个相对数来进行衡量。

11. filtered 列

返回结果的行数占读取行数的百分比，值越大越好。

举例如下：

表 film_actor中actor_id 为 1 的记录有 19 条，而 SQL 查询时扫描了 19 行(rows:19)，19 条符合条件(filtered: 100 19/19)

12. Extra 列

额外信息，但又十分重要。

常见的值如下：

1）Using index

表示 SQL 中使用了覆盖索引。

举例如下：

2）Using where

许多 where 条件里是涉及索引中的列，当它读取索引时，就能被存储引擎检验，因此不是所有带 where 子句的查询都会显示 Using where。

3）Using temporary

对查询结果排序时，使用了一个临时表，常见于 order by 和 group by。

4）Using filesort

对数据使用了一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说 MySQL 无法利用索引完成的排序操作成为“文件排序”。