成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

SQL優(yōu)化策略

IT那活兒 / 690人閱讀
SQL優(yōu)化策略
點擊上方“IT那活兒”，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！??！





優(yōu)化原則



1. 嚴禁使用SELECT *方式查詢語句， 必須明確查詢字段，INSERT語句必須明確要插入的字段。
2. 嚴禁單條SQL關(guān)聯(lián)表超過3張，關(guān)聯(lián)字段必須有索引且數(shù)據(jù)類型一致。
3. 嚴禁單條SQL子查詢超過2層。
4. 嚴禁在SQL中進行計算或嵌套判斷邏輯。
5. 嚴禁查詢條件中字段無索引。
6. 嚴禁在where條件中字段使用函數(shù)或者表達式(例如where col/3>=100)。
7. 嚴禁負向查詢條件(!=、<>、not ...)、單表行數(shù)大于5萬的禁止左模糊、全模糊查詢(例如：colA like ‘%服務(wù)’)。
8. 嚴禁傳入變量類型與查詢條件中字段類型不匹配。
9. 嚴禁表無主鍵或使用復(fù)合索引作為主鍵，嚴禁使用無序數(shù)據(jù)作為主鍵內(nèi)容。
10. 嚴禁使用外鍵、視圖、觸發(fā)器、存儲過程、自定義函數(shù)和分區(qū)表。





innodb索引組織表



索引組織表的典型特征：

  • 表記錄通過聚集索引組織；

  • 表有且僅有一條聚集索引；

  • 所有列數(shù)據(jù)存儲在葉子幾點上。

Innodb存儲引擎中，表都是根據(jù)主鍵順序組織存放，以這種存儲方式的表稱為索引組織表。
InnoDB的數(shù)據(jù)文件本身就是索引文件。
InnoDB的輔助索引data域存儲相應(yīng)記錄主鍵的值而不是地址。
如果在創(chuàng)建表時沒有顯示地定義主鍵，則InnoDB存儲引擎會按如下方式選擇或創(chuàng)建主鍵：
首先判斷表是否有非空的唯一索引(Unique not null),如果有，則該列即為主鍵。
如果不符合上述條件，InnoDB存儲引擎自動創(chuàng)建一個6字節(jié)大小的指針。
如下：




查詢的優(yōu)化



1. 隱式轉(zhuǎn)換
禁止隱式轉(zhuǎn)換，保持變量類型與字段類型一致。
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）對于只返回一行的無關(guān)聯(lián)子查詢用‘=’代替‘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);
對于數(shù)據(jù)庫來說， 在絕大部分情況下， 連接會比子查詢更快。使用連接的方式， MySQL優(yōu)化器一般可以生成更佳的執(zhí)行計劃， 更高效地處理查詢。而子查詢往往需要運行重復(fù)的查詢， 子查詢生成的臨時表上也沒有索引， 因此效率會更低。
3. OR子句
避免使用子查詢、or，將子查詢轉(zhuǎn)化為表連接方式，or轉(zhuǎn)化為in。
優(yōu)化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列上創(chuàng)建索引）
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。
或者轉(zhuǎn)換（效率更高）：
select * from t1 where a=1
union all
select * from t1 where b=2;
4. GROUP/ORDER BY優(yōu)化
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的索引優(yōu)化基本一樣，group by實質(zhì)是先排序后分組，也就是分組之前必排序，遵照索引的最佳左前綴原則可以大大提高group by的效率。
5. LIMIT偏移量過大
禁止分頁查詢偏移量過大，如limit 10000,10。
1）盡量使用索引排序完成文件排序。
2）限制用戶翻頁。
3）利用自增主鍵，避免offset使用。
idx_test1 (gender,hire_date);




優(yōu)化案例



案例一：模糊查詢
1）慢sql：
2）慢sql執(zhí)行計劃：
問題點：
1）dsps_staff_id like concat(‘%’,’HE12065’,’%’) 全模糊匹配導(dǎo)致索引失效。
2）總量統(tǒng)計沒必要實時統(tǒng)計，建議降低統(tǒng)計頻率。
全模糊查詢改為 dsps_staff_id like ’HE12065%’，優(yōu)化后效率提升百倍。
案例二：索引覆蓋
包含所有滿足查詢需要的數(shù)據(jù)的索引成為覆蓋索引，也就是平時所說的不需要回表操作,對于一個索引覆蓋查詢，顯示為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這個索引就足已獲取到所需要的數(shù)據(jù)，不需要再通過索引回表查詢了。

例2：

可以看到，我們這次查詢了id和film_id兩個字段，但條件只用了film_id這個二級索引，Extra的值卻也是為Using index。
原因是二級索引的葉子結(jié)點中會有主鍵索引(id)值，因此使用了覆蓋索引。
可以通過索引來實現(xiàn)索引覆蓋查詢，但前提條件是，查詢返回的字段數(shù)足夠少，select * 類不可以。
案例三：驅(qū)動表、別名
1）慢sql：
2）慢sql執(zhí)行計劃：
問題點：
1）原sql基表是t5，大量使用臨時表、排序，效率低下。
2）order by crttime 使用別名。
第一步：刪除channel_id索引。
第二步：order by crt_time 字段名代替別名。
案例四：or
1）慢sql：
2）慢sql執(zhí)行計劃：
問題點：
1）全表掃描。
2）or條件，索引失效。
第一步：改寫union 上部分or。
第二步：改寫union下部分子查詢。
第三步：數(shù)據(jù)合并由應(yīng)用層實現(xiàn)。
經(jīng)過分步執(zhí)行后總執(zhí)行時間為0.00s,效率提升百倍。
案例五：拆分大sql
1）慢sql：
2）慢sql執(zhí)行計劃：
問題點：
1）關(guān)聯(lián)表過多，共關(guān)聯(lián)9張表。
2）or條件，索引失效。
3）子查詢過多。
第一步：改寫union 上部分or。
第二步：改寫union下部分子查詢。
第三步：改寫union下部分子查詢，利用第二部分數(shù)據(jù)。
第四步：數(shù)據(jù)合并由應(yīng)用層實現(xiàn)。
通過redis等NoSQL緩存字典類信息，減少多表關(guān)聯(lián)。
案例六
1）慢sql：
2）執(zhí)行計劃：
問題點：
1）rec表全表掃描。
2）sql邏輯問題。
執(zhí)行計劃詳細信息中發(fā)現(xiàn)問題：
第一步：改寫sql。
第二步：改寫后sql執(zhí)行計劃。



本文作者：李博文

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

文章版權(quán)歸作者所有，未經(jīng)允許請勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請注明本文地址：http://systransis.cn/yun/129541.html

相關(guān)文章

  • Hibernate最全面試題

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

    張利勇 評論0 收藏0

  • 第三代DRDS分布式SQL引擎全新發(fā)布

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

    xinhaip 評論0 收藏0

  • SparkSQL 在有贊的實踐

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

    hzx 評論0 收藏0

  • SparkSQL 在有贊的實踐

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

    Xufc 評論0 收藏0

發(fā)表評論

0條評論

IT那活兒

男|高級講師

TA的文章

閱讀更多
最新活動
閱讀需要支付1元查看
<