如何優(yōu)化SQL查詢

本篇文章給大家分享的是有關(guān)如何優(yōu)化SQL查詢，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

我們致力于打造能夠較好運行并延續(xù)較長一段時間的query(查詢)。小編將給出關(guān)于優(yōu)化SQL語句的幾點建議。

1. 嘗試不去用select *來查詢SQL，而是選擇專用字段。

反例：

select * from employee;

正例：

select id，name fromemployee;

理由：

• 通過只用必要字段進行查詢，能夠節(jié)省資源并減少網(wǎng)絡(luò)開銷。

• 這樣做可能不會使用覆蓋索引，會導致一個查詢返回到表中。

2. 如果已知只有一個查詢結(jié)果，推薦使用limit 1

假設(shè)有一張員工表格，想在其中找到一名叫jay的員工。

CREATE TABLE employee ( id int(11) NOT NULL, name varchar(255) DEFAULT NULL, age int(11) DEFAULT NULL, date datetime DEFAULT NULL, sex int(1) DEFAULT NULL, PRIMARY KEY (`id`) );

反例：

select id，name from employeewhere name='jay';

正例：

select id，name from employeewhere name='jay' limit 1;

理由：添加limit 1后，查找到相應的記錄時，便不會繼續(xù)查找下去，效率會大大提高。

3. 嘗試避免在 where 子句中使用or來連接條件

創(chuàng)建一個新的用戶表格，其有一個常規(guī)索引userId，表格結(jié)構(gòu)如下：

CREATE TABLE `user` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `userId` int(11) NOT NULL,   `age` int(11) NOT NULL,   `name` varchar(255) NOT NULL,   PRIMARY KEY (`id`),   KEY `idx_userId` (`userId`) )

現(xiàn)在假設(shè)需要查詢userid為1或為18歲的用戶，使用如下的SQL就會很簡單。

反例：

select * from user where userid = 1 or age = 18;

正例：

//se union all select * from user where userid=1 union all select * from user where age = 18;//Or write two separate SQL select * from user where userid=1;

理由：or 的使用可能會使索引無效，因此需要進行全表掃描。

在or 無索引的情況下，假設(shè)已采用userId索引，但是當涉及到  age(年齡)查詢條件時，必須執(zhí)行全表掃描，其過程分為三步：全表掃描+索引掃描+合并。

4. 盡可能避免在where子句中使用!=或<>運算符，否則，引擎將放棄使用索引并執(zhí)行全表掃描。

反例：

select age,name from user where age<>18;

正例：

//You can consider separate two sql writeselect age,name from user where age <18; select age,name from user where age>18;

理由：使用!=和<>可能使索引無效。

5. 優(yōu)化limit分頁

通常用limits來實現(xiàn)日常分頁，但當偏移量特別大時，查詢效率便會降低。因為Mysql不會跳過偏移量，而是直接獲取數(shù)據(jù)。

反例：

select id，name，age from employeelimit 10000，10;

正例：

//Solution 1: Return the largest record (offset) of the last query select id，name from employeewhere id>10000 limit 10;//Solution 2: order by + index select id，name from employeeorder by id limit 10000，10;

理由：

• 如果使用了優(yōu)化方案1，則會返回最末的查詢記錄(偏移量)，因此可以跳過該偏移量，效率自然會大幅提高。

• 選項二：使用+索引排序，也可以提高查詢效率。

6. 優(yōu)化like語句

在日常開發(fā)中，如果使用模糊關(guān)鍵字查詢，我們很容易想到like，但like可能會使索引無效。

反例：

select userId，name from user where userId like '%123';

正例：

select userId，name from user where userId like '123%';

理由：https://medium.com/@pawanjain.432/hey-thanks-dovid-for-pointing-out-a-typo-in-13-1000a4103fe6

7. 使用where條件限制將要查詢的數(shù)據(jù)來避免返回額外行

假設(shè)要查詢一名用戶是否為會員，老式執(zhí)行代碼會這樣做。

反例：

List<Long> userIds = sqlMap.queryList("select userId from userwhere isVip=1");boolean isVip = userIds.contains(userId);

正例：

Long userId = sqlMap.queryObject("select userId from user whereuserId='userId' and isVip='1' ")boolean isVip = userId！=null;

理由：能夠檢查需要的數(shù)據(jù)，避免返回非必要數(shù)據(jù)，并能節(jié)省費用和計算機開銷。

8. 考慮在where子句中使用默認值而不是null

反例：

select * from user where age is not null;

正例：

select * from user where age>0; //Set 0 as default

理由：如果用默認值取代null值，則通?？梢越⑺饕c此同時，表達式將相對清晰。

9. 如果插入數(shù)據(jù)過多，可以考慮批量插入

反例：

for(User     u :list){ INSERT into user(name,age)values(#name#,#age#) }

正例：

//One batch of 500 inserts, carried out in batchesinsert intouser(name,age) values <foreach collection="list" item="item"index="index" separator=",">     (#{item.name},#{item.age}) </foreach>

理由：批量插入性能良好且省時。

打個比方，在有電梯的情況下，你需要將1萬塊磚移送到建筑物的頂部。電梯一次可以放置適當數(shù)量的磚塊(最多500塊)，你可以選擇一次運送一塊磚，也可以一次運送500塊。哪種方案更好?

10. 謹慎使用distinct關(guān)鍵詞

Distinct關(guān)鍵詞通常用于過濾重復記錄以返回唯一記錄。當其被用于查詢一個或幾個字段時，Distinct關(guān)鍵詞將為查詢帶來優(yōu)化效果。然而，在字段過多的情況下，Distinct關(guān)鍵詞將大大降低查詢效率。

反例：

SELECT DISTINCT * from user;

正例：

select DISTINCT name from user;

理由：帶有“distinct”語句的CPU時間和占用時間高于沒有“ distinct”的語句。

如果在查詢多字段時使用distinct，數(shù)據(jù)庫引擎將比較數(shù)據(jù)，并濾除重復的數(shù)據(jù)。然而，該比較和濾除過程將消耗系統(tǒng)資源和CPU時間。

11. 刪除多余和重復的索引

反例：

KEY `idx_userId` (`userId`) KEY `idx_userId_age` (`userId`,`age`)

正例：

//Delete the userId index, because the combined index (A, B) is equivalentto creating the (A) and (A, B) indexesKEY `idx_userId_age` (`userId`,`age`)

理由：若保留重復的索引，那么優(yōu)化程序在優(yōu)化查詢時也需要對其進行一一考量，這會影響性能。

12. 如果數(shù)據(jù)量很大，優(yōu)化 modify或delete語句

避免同時修改或刪除過多數(shù)據(jù)，因其將導致CPU利用率過高，從而影響他人對數(shù)據(jù)庫的訪問。

反例：

//Delete 100,000 or 1 million+ at a time? delete from user where id <100000;//Or use single cycle operation, lowefficiency and long time for（User user：list）{    delete from user；}

正例：

//Delete in batches, such as 500 each timedelete user where id<500; delete product where id>=500 and id<1000；

理由：一次刪除過多數(shù)據(jù)，可能會導致lock wait timeout exceed error(鎖定等待超時錯誤)，因此建議分批操作。

13. 使用explain分析SQL方案

在日常開發(fā)中編寫SQL時，嘗試養(yǎng)成習慣：使用explain來分析自己編寫的SQL，尤其是索引。

explain select * from user where userid = 10086 or age =18;

14. 嘗試用union all代替union

如果搜索結(jié)果里沒有重復的記錄，我推薦用union all代替union。

反例：

select * from user where userid=1 union select * from user where age = 10

正例：

select * from user where userid=1 union all select * from user where age = 10

理由：

• 如果使用union，則無論有沒有重復的搜索結(jié)果，它都會嘗試對其進行合并、排序，然后輸出最終結(jié)果。

• 若已知搜索結(jié)果中沒有重復記錄，用union all代替union將提高效率。

15. 盡可能使用數(shù)字字段。如果字段僅包含數(shù)字信息，嘗試不將其設(shè)置為字符類型。

反例：

`king_id` varchar（20） NOT NULL;

正例：

`king_id` int(11) NOT NULL;

理由：與數(shù)字字段相比，字符類型將降低查詢和連接的性能，并會增加存儲開銷。

16. 盡可能用varchar或nvarchar代替char或nchar

反例：

`deptName` char(100) DEFAULT NULL

正例：

`deptName` varchar(100) DEFAULT NULL

理由：

• 首先，由于可變長度字段的存儲空間很小，該方法可以節(jié)省存儲空間。

• 其次，對于查詢而言，在相對較小的字段中搜索會更有效率。

以上就是如何優(yōu)化SQL查詢，小編相信有部分知識點可能是我們?nèi)粘９ぷ鲿姷交蛴玫降摹ＯＭ隳芡ㄟ^這篇文章學到更多知識。更多詳情敬請關(guān)注億速云行業(yè)資訊頻道。