SQL優化策略

IT那活兒發布于2023-01-11 13:20 / 678人閱讀

SQL優化策略

點擊上方“IT那活兒”，關注后了解更多內容，不管IT什么活兒，干就完了！！！

優化原則

1. 嚴禁使用SELECT *方式查詢語句，必須明確查詢字段，INSERT語句必須明確要插入的字段。

2. 嚴禁單條SQL關聯表超過3張，關聯字段必須有索引且數據類型一致。

3. 嚴禁單條SQL子查詢超過2層。

4. 嚴禁在SQL中進行計算或嵌套判斷邏輯。

5. 嚴禁查詢條件中字段無索引。

6. 嚴禁在where條件中字段使用函數或者表達式(例如where col/3>=100)。

7. 嚴禁負向查詢條件(!=、<>、not ...)、單表行數大于5萬的禁止左模糊、全模糊查詢(例如：colA like ‘%服務’)。

8. 嚴禁傳入變量類型與查詢條件中字段類型不匹配。

9. 嚴禁表無主鍵或使用復合索引作為主鍵，嚴禁使用無序數據作為主鍵內容。

10. 嚴禁使用外鍵、視圖、觸發器、存儲過程、自定義函數和分區表。

innodb索引組織表

索引組織表的典型特征：

表記錄通過聚集索引組織；
表有且僅有一條聚集索引；
所有列數據存儲在葉子幾點上。

Innodb存儲引擎中，表都是根據主鍵順序組織存放，以這種存儲方式的表稱為索引組織表。

InnoDB的數據文件本身就是索引文件。

InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址。

如果在創建表時沒有顯示地定義主鍵，則InnoDB存儲引擎會按如下方式選擇或創建主鍵：

首先判斷表是否有非空的唯一索引(Unique not null),如果有，則該列即為主鍵。

如果不符合上述條件，InnoDB存儲引擎自動創建一個6字節大小的指針。

如下：

查詢的優化

1. 隱式轉換

禁止隱式轉換，保持變量類型與字段類型一致。

SQL1：

select emp_no,from_date from dept_emp1
where dept_no=404838;

SQL2：

select emp_no,from_date from dept_emp1
where dept_no=404838;

2. WHERE子查詢

1）使用連接方式改寫子查詢。

示例1:

SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN ( SELECT column1 FROM t2);

改寫:

SELECT DISTINCT t1.column1 FROM t1, t2 WHERE t1.column1 = t2.column1;

示例2:

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);

改寫:

SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

還可以改寫成如下LEFT JOIN：

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

2）對于只返回一行的無關聯子查詢用‘=’代替‘in’。

示例:

SELECT * FROM t1 WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

改寫:

SELECT * FROM t1 WHERE t1.col_name= (SELECT a FROM t2 WHERE b = some_const);

對于數據庫來說，在絕大部分情況下，連接會比子查詢更快。使用連接的方式， MySQL優化器一般可以生成更佳的執行計劃，更高效地處理查詢。而子查詢往往需要運行重復的查詢，子查詢生成的臨時表上也沒有索引，因此效率會更低。

3. OR子句

避免使用子查詢、or，將子查詢轉化為表連接方式，or轉化為in。

優化or子句：

1）or子句全部相同，則改為in。

示例：

select * from t1 where a=1 or a=3;

改為：

select * from t1 where a in(1,3);

2）or子句具有公共子序列前綴的，請在or公共部分建立索引。

示例：（如下需要在a列上創建索引）

select * from t1 where (a=1 and b=2) or (a=3 and c=4);

3）若無公共，則建議改為union all，并為每部分建立索引。

示例：

select * from t1 where a=1 or b=2;

可以使用 Index merge。

或者轉換（效率更高）：

select * from t1 where a=1
union all
select * from t1 where b=2;

4. GROUP/ORDER BY優化

order by子句，盡量使用Index方式排序，在索引列上遵循索引的最佳左前綴原則。

如下：

Key (a,b,c)

Order by 能使用索引情況：

--order by a；

--order by a, b；

--order by a, b,c；

----order by a desc ,b desc ,c desc。

如果where 使用索引的最左前綴定義為常量，則order by能使用索引：

--where a=const order by b,c；

--where a=const and b=const order by c；

--where a=const and b>const order by b,c。

group by與order by的索引優化基本一樣，group by實質是先排序后分組，也就是分組之前必排序，遵照索引的最佳左前綴原則可以大大提高group by的效率。

5. LIMIT偏移量過大

禁止分頁查詢偏移量過大，如limit 10000,10。

1）盡量使用索引排序完成文件排序。

2）限制用戶翻頁。

3）利用自增主鍵，避免offset使用。

idx_test1 (gender,hire_date);

優化案例

案例一：模糊查詢

1）慢sql：

2）慢sql執行計劃：

問題點：

1）dsps_staff_id like concat(‘%’,’HE12065’,’%’) 全模糊匹配導致索引失效。

2）總量統計沒必要實時統計，建議降低統計頻率。

全模糊查詢改為 dsps_staff_id like ’HE12065%’，優化后效率提升百倍。

案例二：索引覆蓋

包含所有滿足查詢需要的數據的索引成為覆蓋索引，也就是平時所說的不需要回表操作,對于一個索引覆蓋查詢，顯示為using index。

CREATE TABLE test (
id int(8) unsigned NOT NULL ,
film_id smallint(5) unsigned NOT NULL,
store_id tinyint(3) unsigned NOT NULL,
PRIMARY KEY (id),
KEY idx_film_id (film_id)
) ENGINE=InnoDB ;

例1：

這里最主要看Extra，它的值為Using index，它在這句查詢中含義就是直接訪問film_id這個索引就足已獲取到所需要的數據，不需要再通過索引回表查詢了。

例2：

可以看到，我們這次查詢了id和film_id兩個字段，但條件只用了film_id這個二級索引，Extra的值卻也是為Using index。

原因是二級索引的葉子結點中會有主鍵索引(id)值，因此使用了覆蓋索引。

可以通過索引來實現索引覆蓋查詢，但前提條件是，查詢返回的字段數足夠少，select * 類不可以。

案例三：驅動表、別名

1）慢sql：

2）慢sql執行計劃：

問題點：

1）原sql基表是t5，大量使用臨時表、排序，效率低下。

2）order by crttime 使用別名。

第一步：刪除channel_id索引。

第二步：order by crt_time 字段名代替別名。

案例四：or

1）慢sql：

2）慢sql執行計劃：

問題點：

1）全表掃描。

2）or條件，索引失效。

第一步：改寫union 上部分or。

第二步：改寫union下部分子查詢。

第三步：數據合并由應用層實現。

經過分步執行后總執行時間為0.00s,效率提升百倍。

案例五：拆分大sql

1）慢sql：

2）慢sql執行計劃：

問題點：

1）關聯表過多，共關聯9張表。

2）or條件，索引失效。

3）子查詢過多。

第一步：改寫union 上部分or。

第二步：改寫union下部分子查詢。

第三步：改寫union下部分子查詢，利用第二部分數據。

第四步：數據合并由應用層實現。

通過redis等NoSQL緩存字典類信息，減少多表關聯。

案例六

1）慢sql：

2）執行計劃：

問題點：

1）rec表全表掃描。

2）sql邏輯問題。

執行計劃詳細信息中發現問題：

第一步：改寫sql。

第二步：改寫后sql執行計劃。

本文作者：李博文

本文來源：IT那活兒（上海新炬王翦團隊）

云服務器 GPU云服務器優化SQL sql優化 SQL命令優化 SQL優化方法

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/129541.html

Hibernate最全面試題

摘要：中怎樣實現類之間的關系如一對多多對多的關系中怎樣實現類之間的關系如一對多多對多的關系它們通過配置文件中的來實現類之間的關聯關系的。 Hibernate常見面試題 Hibernate工作原理及為什么要用？ Hibernate工作原理及為什么要用？讀取并解析配置文件讀取并解析映射信息，創建SessionFactory 打開Sesssion 創建事務Transation 持久化操作提...

張利勇 2019-08-15 14:39 評論0 收藏0
第三代DRDS分布式SQL引擎全新發布

摘要：阿里云分布式關系型數據庫服務，于月號發布了版本，這是一個年度大更新。無需額外付費或者開通，不依賴第三方組件，即可執行分布式事務。確保分布式執行代價的最小化。柔性事務提供的最終一致方式執行的分布式事務稱為柔性事務。摘要： DRDS （阿里云分布式關系型數據庫服務，https://www.aliyun.com/produc...）于 4 月 30 號發布了 5.3 版本，年度更新，具備眾...

xinhaip 2019-06-25 18:39 評論0 收藏0
SparkSQL 在有贊的實踐

摘要：在有贊的技術演進。業務數據量正在不斷增大，這些任務會影響業務對外服務的承諾。監控需要收集上執行的的審計信息，包括提交者執行的具體，開始結束時間，執行完成狀態。還有一點是詳細介紹了的原理，實踐中設置了的比默認的減少了以上的時間。前言有贊數據平臺從2017年上半年開始，逐步使用 SparkSQL 替代 Hive 執行離線任務，目前 SparkSQL 每天的運行作業數量5000個，占離線...

hzx 2019-06-26 18:45 評論0 收藏0
SparkSQL 在有贊的實踐

摘要：在有贊的技術演進。業務數據量正在不斷增大，這些任務會影響業務對外服務的承諾。監控需要收集上執行的的審計信息，包括提交者執行的具體，開始結束時間，執行完成狀態。還有一點是詳細介紹了的原理，實踐中設置了的比默認的減少了以上的時間。前言有贊數據平臺從2017年上半年開始，逐步使用 SparkSQL 替代 Hive 執行離線任務，目前 SparkSQL 每天的運行作業數量5000個，占離線...

Xufc 2019-06-21 16:51 評論0 收藏0