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

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

為元寶山等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及元寶山網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、外貿(mào)網(wǎng)站建設(shè)、元寶山網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專(zhuān)業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！

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

1. 嘗試不去用select *來(lái)查詢SQL，而是選擇專(zhuān)用字段。

反例：

select * from employee;

正例：

select id，name fromemployee;

理由：

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

  • 這樣做可能不會(huì)使用覆蓋索引，會(huì)導(dǎo)致一個(gè)查詢返回到表中。

2. 如果已知只有一個(gè)查詢結(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后，查找到相應(yīng)的記錄時(shí)，便不會(huì)繼續(xù)查找下去，效率會(huì)大大提高。

3. 嘗試避免在 where 子句中使用or來(lái)連接條件

創(chuàng)建一個(gè)新的用戶表格，其有一個(gè)常規(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或?yàn)?8歲的用戶，使用如下的SQL就會(huì)很簡(jiǎn)單。

反例：

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 的使用可能會(huì)使索引無(wú)效，因此需要進(jìn)行全表掃描。

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

4. 盡可能避免在where子句中使用!=或<>運(yùn)算符，否則，引擎將放棄使用索引并執(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;

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

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

通常用limits來(lái)實(shí)現(xiàn)日常分頁(yè)，但當(dāng)偏移量特別大時(shí)，查詢效率便會(huì)降低。因?yàn)镸ySQL不會(huì)跳過(guò)偏移量，而是直接獲取數(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，則會(huì)返回最末的查詢記錄(偏移量)，因此可以跳過(guò)該偏移量，效率自然會(huì)大幅提高。

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

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

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

反例：

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ù)來(lái)避免返回額外行

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

反例：

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é)省費(fèi)用和計(jì)算機(jī)開(kāi)銷(xiāo)。

8. 考慮在where子句中使用默認(rèn)值而不是null

反例：

select * from user where age is not null;

正例：

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

理由：如果用默認(rèn)值取代null值，則通?？梢越⑺饕?，與此同時(shí)，表達(dá)式將相對(duì)清晰。

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

反例：

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>

理由：批量插入性能良好且省時(shí)。

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

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

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

反例：

SELECT DISTINCT * from user;

正例：

select DISTINCT name from user;

理由：帶有“distinct”語(yǔ)句的CPU時(shí)間和占用時(shí)間高于沒(méi)有“ distinct”的語(yǔ)句。

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

11. 刪除多余和重復(fù)的索引

反例：

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`)

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

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

避免同時(shí)修改或刪除過(guò)多數(shù)據(jù)，因其將導(dǎo)致CPU利用率過(guò)高，從而影響他人對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)。

反例：

//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；

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

13. 使用explain分析SQL方案

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

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

14. 嘗試用union all代替union

如果搜索結(jié)果里沒(méi)有重復(fù)的記錄，我推薦用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，則無(wú)論有沒(méi)有重復(fù)的搜索結(jié)果，它都會(huì)嘗試對(duì)其進(jìn)行合并、排序，然后輸出最終結(jié)果。

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

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

反例：

`king_id` varchar（20） NOT NULL;

正例：

`king_id` int(11) NOT NULL;

理由：與數(shù)字字段相比，字符類(lèi)型將降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)。

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

反例：

`deptName` char(100) DEFAULT NULL

正例：

`deptName` varchar(100) DEFAULT NULL

理由：

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

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

以上就是如何優(yōu)化SQL查詢，小編相信有部分知識(shí)點(diǎn)可能是我們?nèi)粘９ぷ鲿?huì)見(jiàn)到或用到的。希望你能通過(guò)這篇文章學(xué)到更多知識(shí)。更多詳情敬請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。

分享標(biāo)題：如何優(yōu)化SQL查詢
文章源于：http://bm7419.com/article4/goshie.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供軟件開(kāi)發(fā)、營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、商城網(wǎng)站、微信小程序、小程序開(kāi)發(fā)、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)

成都網(wǎng)站建設(shè)