linux服務(wù)器開(kāi)發(fā)相關(guān)視頻解析：

騰訊往声、阿里等大廠面試，不了解這些MySQL, InnoDB技術(shù)，何以征服面試官

5種紅黑樹(shù)的場(chǎng)景作箍，從Linux內(nèi)核談到Nginx源碼，聽(tīng)完醍醐灌頂

c/c++ linux服務(wù)器開(kāi)發(fā)免費(fèi)學(xué)習(xí)地址：c/c++ linux后臺(tái)服務(wù)器高級(jí)架構(gòu)師

前言

BATJTMD等大廠的面試難度越來(lái)越高前硫，但無(wú)論從大廠還是到小公司胞得，一直未變的一個(gè)重點(diǎn)就是對(duì)SQL優(yōu)化經(jīng)驗(yàn)的考察。一提到數(shù)據(jù)庫(kù)屹电，先“說(shuō)一說(shuō)你對(duì)SQL優(yōu)化的見(jiàn)解吧阶剑？”跃巡。SQL優(yōu)化已經(jīng)成為衡量程序猿優(yōu)秀與否的硬性指標(biāo)，甚至在各大廠招聘崗位職能上都有明碼標(biāo)注牧愁，如果是你瓷炮，在這個(gè)問(wèn)題上能吊打面試官還是會(huì)被吊打呢？

有朋友疑問(wèn)到递宅，SQL優(yōu)化真的有這么重要么娘香？如下圖所示，SQL優(yōu)化在提升系統(tǒng)性能中是：（成本最低 && 優(yōu)化效果最明顯） 的途徑办龄。如果你的團(tuán)隊(duì)在SQL優(yōu)化這方面搞得很優(yōu)秀烘绽，對(duì)你們整個(gè)大型系統(tǒng)可用性方面無(wú)疑是一個(gè)質(zhì)的跨越，真的能讓你們老板省下不止幾沓子錢(qián)俐填。

1安接、優(yōu)化成本：硬件>系統(tǒng)配置>數(shù)據(jù)庫(kù)表結(jié)構(gòu)>SQL及索引。

2英融、優(yōu)化效果：硬件<系統(tǒng)配置<數(shù)據(jù)庫(kù)表結(jié)構(gòu)<SQL及索引盏檐。

首先，對(duì)于MySQL層優(yōu)化我一般遵從五個(gè)原則：

1驶悟、減少數(shù)據(jù)訪問(wèn)： 設(shè)置合理的字段類(lèi)型胡野，啟用壓縮，通過(guò)索引訪問(wèn)等減少磁盤(pán)IO

2痕鳍、返回更少的數(shù)據(jù)： 只返回需要的字段和數(shù)據(jù)分頁(yè)處理 減少磁盤(pán)io及網(wǎng)絡(luò)io

3硫豆、減少交互次數(shù)： 批量DML操作，函數(shù)存儲(chǔ)等減少數(shù)據(jù)連接次數(shù)

4笼呆、減少服務(wù)器CPU開(kāi)銷(xiāo)： 盡量減少數(shù)據(jù)庫(kù)排序操作以及全表查詢熊响，減少cpu 內(nèi)存占用

5、利用更多資源： 使用表分區(qū)诗赌，可以增加并行操作汗茄，更大限度利用cpu資源

總結(jié)到SQL優(yōu)化中，就三點(diǎn):

1铭若、最大化利用索引洪碳；

2、盡可能避免全表掃描奥喻；

3偶宫、減少無(wú)效數(shù)據(jù)的查詢；

理解SQL優(yōu)化原理 环鲤，首先要搞清楚SQL執(zhí)行順序：

SELECT語(yǔ)句 - 語(yǔ)法順序：

1.SELECT

2.DISTINCT

3.FROM

4.JOIN

5.ON

6.WHERE

7.GROUPBY

8.HAVING

9.ORDERBY

10.LIMIT

SELECT語(yǔ)句 - 執(zhí)行順序：

FROM

<表名> # 選取表纯趋，將多個(gè)表數(shù)據(jù)通過(guò)笛卡爾積變成一個(gè)表。

ON

<篩選條件> # 對(duì)笛卡爾積的虛表進(jìn)行篩選

JOIN <join, left join, right join...>

<join表> # 指定join，用于添加數(shù)據(jù)到on之后的虛表中吵冒，例如left join會(huì)將左表的剩余數(shù)據(jù)添加到虛表中

WHERE

<where條件> # 對(duì)上述虛表進(jìn)行篩選

GROUP BY

<分組條件> # 分組

<SUM()等聚合函數(shù)> # 用于having子句進(jìn)行判斷纯命，在書(shū)寫(xiě)上這類(lèi)聚合函數(shù)是寫(xiě)在having判斷里面的

HAVING

<分組篩選> # 對(duì)分組后的結(jié)果進(jìn)行聚合篩選

SELECT

<返回?cái)?shù)據(jù)列表> # 返回的單列必須在group by子句中，聚合函數(shù)除外

DISTINCT

# 數(shù)據(jù)除重

ORDER BY

<排序條件> # 排序

LIMIT

<行數(shù)限制>

SQL優(yōu)化策略

聲明：以下SQL優(yōu)化策略適用于數(shù)據(jù)量較大的場(chǎng)景下痹栖，如果數(shù)據(jù)量較小亿汞，沒(méi)必要以此為準(zhǔn)，以免畫(huà)蛇添足揪阿。

一疗我、避免不走索引的場(chǎng)景

1. 盡量避免在字段開(kāi)頭模糊查詢，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描南捂。如下：

SELECT*FROMtWHEREusernameLIKE'%陳%'

優(yōu)化方式：盡量在字段后面使用模糊查詢吴裤。如下：

SELECT*FROMtWHEREusernameLIKE'陳%'

如果需求是要在前面使用模糊查詢，

使用MySQL內(nèi)置函數(shù)INSTR(str,substr) 來(lái)匹配溺健，作用類(lèi)似于java中的indexOf()麦牺，查詢字符串出現(xiàn)的角標(biāo)位置

使用FullText全文索引，用match against 檢索

數(shù)據(jù)量較大的情況鞭缭，建議引用ElasticSearch剖膳、solr，億級(jí)數(shù)據(jù)量檢索速度秒級(jí)

當(dāng)表數(shù)據(jù)量較少（幾千條兒那種）岭辣，別整花里胡哨的吱晒，直接用like '%xx%'。

【文章福利】需要C/C++ Linux服務(wù)器架構(gòu)師學(xué)習(xí)資料加群812855908（資料包括C/C++易结，Linux，golang技術(shù)搞动，Nginx，ZeroMQ渣刷，MySQL鹦肿，Redis，fastdfs辅柴，MongoDB箩溃，ZK，流媒體碌嘀，CDN涣旨，P2P，K8S股冗，Docker霹陡，TCP/IP，協(xié)程，DPDK烹棉，ffmpeg等）

2. 盡量避免使用in 和not in攒霹，會(huì)導(dǎo)致引擎走全表掃描。如下：

SELECT*FROMtWHEREidIN(2,3)

優(yōu)化方式：如果是連續(xù)數(shù)值浆洗，可以用between代替催束。如下：

SELECT*FROMtWHEREidBETWEEN2AND3

如果是子查詢，可以用exists代替伏社。詳情見(jiàn)《MySql中如何用exists代替in》如下：

-- 不走索引

select*fromAwhereA.idin(selectidfromB);

-- 走索引

select*fromAwhereexists(select*fromBwhereB.id = A.id);

3. 盡量避免使用 or抠刺，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。如下：

SELECT*FROMtWHEREid=1ORid=3

優(yōu)化方式：可以用union代替or摘昌。如下：

SELECT*FROMtWHEREid=1

UNION

SELECT*FROMtWHEREid=3

4. 盡量避免進(jìn)行null值的判斷速妖，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描。如下：

SELECT*FROMtWHEREscoreISNULL

優(yōu)化方式：可以給字段添加默認(rèn)值0第焰，對(duì)0值進(jìn)行判斷买优。如下：

SELECT*FROMtWHEREscore =0

5.盡量避免在where條件中等號(hào)的左側(cè)進(jìn)行表達(dá)式、函數(shù)操作挺举，會(huì)導(dǎo)致數(shù)據(jù)庫(kù)引擎放棄索引進(jìn)行全表掃描杀赢。

可以將表達(dá)式、函數(shù)操作移動(dòng)到等號(hào)右側(cè)湘纵。如下：

-- 全表掃描

SELECT*FROMTWHEREscore/10=9

-- 走索引

SELECT*FROMTWHEREscore =10*9

6. 當(dāng)數(shù)據(jù)量大時(shí)脂崔，避免使用where 1=1的條件。通常為了方便拼裝查詢條件梧喷，我們會(huì)默認(rèn)使用該條件砌左，數(shù)據(jù)庫(kù)引擎會(huì)放棄索引進(jìn)行全表掃描。如下：

SELECTusername, age, sexFROMTWHERE1=1

優(yōu)化方式：用代碼拼裝sql時(shí)進(jìn)行判斷铺敌，沒(méi) where 條件就去掉 where汇歹，有where條件就加 and。

7. 查詢條件不能用 <> 或者 !=

使用索引列作為條件進(jìn)行查詢時(shí)偿凭，需要避免使用<>或者!=等判斷條件产弹。如確實(shí)業(yè)務(wù)需要，使用到不等于符號(hào)弯囊，需要在重新評(píng)估索引建立痰哨，避免在此字段上建立索引，改由查詢條件中其他索引字段代替匾嘱。

8. where條件僅包含復(fù)合索引非前置列

如下：復(fù)合（聯(lián)合）索引包含key_part1斤斧，key_part2，key_part3三列霎烙，但SQL語(yǔ)句沒(méi)有包含索引前置列"key_part1"撬讽，按照MySQL聯(lián)合索引的最左匹配原則蕊连，不會(huì)走聯(lián)合索引。

selectcol1fromtablewherekey_part2=1andkey_part3=2

9. 隱式類(lèi)型轉(zhuǎn)換造成不使用索引

如下SQL語(yǔ)句由于索引對(duì)列類(lèi)型為varchar锐秦，但給定的值為數(shù)值咪奖，涉及隱式類(lèi)型轉(zhuǎn)換，造成不能正確走索引酱床。

selectcol1fromtablewherecol_varchar=123;

10. order by 條件要與where中條件一致羊赵，否則order by不會(huì)利用索引進(jìn)行排序

-- 不走age索引

SELECT*FROMtorderbyage;

-- 走age索引

SELECT*FROMtwhereage >0orderbyage;

對(duì)于上面的語(yǔ)句，數(shù)據(jù)庫(kù)的處理順序是：

第一步：根據(jù)where條件和統(tǒng)計(jì)信息生成執(zhí)行計(jì)劃扇谣，得到數(shù)據(jù)昧捷。

第二步：將得到的數(shù)據(jù)排序。當(dāng)執(zhí)行處理數(shù)據(jù)（order by）時(shí)罐寨，數(shù)據(jù)庫(kù)會(huì)先查看第一步的執(zhí)行計(jì)劃靡挥，看order by 的字段是否在執(zhí)行計(jì)劃中利用了索引。如果是鸯绿，則可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)跋破。如果不是，則重新進(jìn)行排序操作瓶蝴。

第三步：返回排序后的數(shù)據(jù)毒返。

當(dāng)order by 中的字段出現(xiàn)在where條件中時(shí)，才會(huì)利用索引而不再二次排序舷手，更準(zhǔn)確的說(shuō)拧簸，order by 中的字段在執(zhí)行計(jì)劃中利用了索引時(shí)，不用排序操作男窟。

這個(gè)結(jié)論不僅對(duì)order by有效盆赤，對(duì)其他需要排序的操作也有效。比如group by 歉眷、union 牺六、distinct等。

11. 正確使用hint優(yōu)化語(yǔ)句

MySQL中可以使用hint指定優(yōu)化器在執(zhí)行時(shí)選擇或忽略特定的索引汗捡。一般而言兔乞，處于版本變更帶來(lái)的表結(jié)構(gòu)索引變化，更建議避免使用hint凉唐，而是通過(guò)Analyze table多收集統(tǒng)計(jì)信息。但在特定場(chǎng)合下霍骄，指定hint可以排除其他索引干擾而指定更優(yōu)的執(zhí)行計(jì)劃台囱。

1、USE INDEX 在你查詢語(yǔ)句中表名的后面读整，添加 USE INDEX 來(lái)提供希望 MySQL 去參考的索引列表簿训，就可以讓 MySQL 不再考慮其他可用的索引。例子:?

SELECT col1 FROM table USE INDEX (mod_time, name)...

2、IGNORE INDEX 如果只是單純的想讓 MySQL 忽略一個(gè)或者多個(gè)索引强品，可以使用 IGNORE INDEX 作為 Hint膘侮。例子:?

SELECT col1 FROM table IGNORE INDEX (priority) ...

3、FORCE INDEX 為強(qiáng)制 MySQL 使用一個(gè)特定的索引的榛，可在查詢中使用FORCE INDEX 作為Hint琼了。例子:?

SELECT col1 FROM table FORCE INDEX (mod_time) ...

在查詢的時(shí)候，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)自動(dòng)分析查詢語(yǔ)句夫晌，并選擇一個(gè)最合適的索引雕薪。但是很多時(shí)候，數(shù)據(jù)庫(kù)系統(tǒng)的查詢優(yōu)化器并不一定總是能使用最優(yōu)索引晓淀。如果我們知道如何選擇索引所袁，可以使用FORCE INDEX強(qiáng)制查詢使用指定的索引。

例如：

SELECT*FROMstudentsFORCEINDEX(idx_class_id)WHEREclass_id =1ORDERBYidDESC;

二凶掰、SELECT語(yǔ)句其他優(yōu)化

1. 避免出現(xiàn)select *

首先燥爷，select * 操作在任何類(lèi)型數(shù)據(jù)庫(kù)中都不是一個(gè)好的SQL編寫(xiě)習(xí)慣。

使用select * 取出全部列懦窘，會(huì)讓優(yōu)化器無(wú)法完成索引覆蓋掃描這類(lèi)優(yōu)化前翎，會(huì)影響優(yōu)化器對(duì)執(zhí)行計(jì)劃的選擇，也會(huì)增加網(wǎng)絡(luò)帶寬消耗奶赠，更會(huì)帶來(lái)額外的I/O,內(nèi)存和CPU消耗鱼填。

建議提出業(yè)務(wù)實(shí)際需要的列數(shù)，將指定列名以取代select *毅戈。

2. 避免出現(xiàn)不確定結(jié)果的函數(shù)

特定針對(duì)主從復(fù)制這類(lèi)業(yè)務(wù)場(chǎng)景苹丸。由于原理上從庫(kù)復(fù)制的是主庫(kù)執(zhí)行的語(yǔ)句，使用如now()苇经、rand()赘理、sysdate()、current_user()等不確定結(jié)果的函數(shù)很容易導(dǎo)致主庫(kù)與從庫(kù)相應(yīng)的數(shù)據(jù)不一致扇单。另外不確定值的函數(shù),產(chǎn)生的SQL語(yǔ)句無(wú)法利用query cache商模。

3.多表關(guān)聯(lián)查詢時(shí)，小表在前蜘澜，大表在后施流。

在MySQL中，執(zhí)行 from 后的表關(guān)聯(lián)查詢是從左往右執(zhí)行的（Oracle相反）鄙信，第一張表會(huì)涉及到全表掃描瞪醋，所以將小表放在前面，先掃小表装诡，掃描快效率較高银受，在掃描后面的大表践盼，或許只掃描大表的前100行就符合返回條件并return了。

例如：表1有50條數(shù)據(jù)宾巍，表2有30億條數(shù)據(jù)咕幻；如果全表掃描表2，你品顶霞，那就先去吃個(gè)飯?jiān)僬f(shuō)吧是吧肄程。

4. 使用表的別名

當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí)，請(qǐng)使用表的別名并把別名前綴于每個(gè)列名上确丢。這樣就可以減少解析的時(shí)間并減少哪些友列名歧義引起的語(yǔ)法錯(cuò)誤绷耍。

5. 用where字句替換HAVING字句

避免使用HAVING字句，因?yàn)镠AVING只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾鲜侥，而where則是在聚合前刷選記錄褂始，如果能通過(guò)where字句限制記錄的數(shù)目，那就能減少這方面的開(kāi)銷(xiāo)描函。HAVING中的條件一般用于聚合函數(shù)的過(guò)濾崎苗，除此之外，應(yīng)該將條件寫(xiě)在where字句中舀寓。

where和having的區(qū)別：where后面不能使用組函數(shù)

6.調(diào)整Where字句中的連接順序

MySQL采用從左往右胆数，自上而下的順序解析where子句。根據(jù)這個(gè)原理互墓，應(yīng)將過(guò)濾數(shù)據(jù)多的條件往前放必尼，最快速度縮小結(jié)果集。

三篡撵、增刪改 DML 語(yǔ)句優(yōu)化

1. 大批量插入數(shù)據(jù)

如果同時(shí)執(zhí)行大量的插入判莉，建議使用多個(gè)值的INSERT語(yǔ)句(方法二)。這比使用分開(kāi)INSERT語(yǔ)句快（方法一）育谬，一般情況下批量插入效率有幾倍的差別券盅。

方法一：

insertintoTvalues(1,2);

insertintoTvalues(1,3);

insertintoTvalues(1,4);

方法二：

InsertintoTvalues(1,2),(1,3),(1,4);

選擇后一種方法的原因有三。

減少SQL語(yǔ)句解析的操作膛檀，MySQL沒(méi)有類(lèi)似Oracle的share pool锰镀，采用方法二，只需要解析一次就能進(jìn)行數(shù)據(jù)的插入操作咖刃；

在特定場(chǎng)景可以減少對(duì)DB連接次數(shù)

SQL語(yǔ)句較短泳炉，可以減少網(wǎng)絡(luò)傳輸?shù)腎O。

2. 適當(dāng)使用commit

適當(dāng)使用commit可以釋放事務(wù)占用的資源而減少消耗嚎杨，commit后能釋放的資源如下：

事務(wù)占用的undo數(shù)據(jù)塊胡桃；

事務(wù)在redo log中記錄的數(shù)據(jù)塊；

釋放事務(wù)施加的磕潮，減少鎖爭(zhēng)用影響性能翠胰。特別是在需要使用delete刪除大量數(shù)據(jù)的時(shí)候，必須分解刪除量并定期commit自脯。

3. 避免重復(fù)查詢更新的數(shù)據(jù)

針對(duì)業(yè)務(wù)中經(jīng)常出現(xiàn)的更新行同時(shí)又希望獲得改行信息的需求之景，MySQL并不支持PostgreSQL那樣的UPDATE RETURNING語(yǔ)法，在MySQL中可以通過(guò)變量實(shí)現(xiàn)膏潮。

例如锻狗，更新一行記錄的時(shí)間戳，同時(shí)希望查詢當(dāng)前記錄中存放的時(shí)間戳是什么焕参，簡(jiǎn)單方法實(shí)現(xiàn)：

Updatet1settime=now()wherecol1=1;

Selecttimefromt1whereid=1;

使用變量轻纪，可以重寫(xiě)為以下方式：

Updatet1settime=now()wherecol1=1and@now: =now();

Select@now;

前后二者都需要兩次網(wǎng)絡(luò)來(lái)回，但使用變量避免了再次訪問(wèn)數(shù)據(jù)表叠纷，特別是當(dāng)t1表數(shù)據(jù)量較大時(shí)刻帚，后者比前者快很多。

4.查詢優(yōu)先還是更新（insert涩嚣、update崇众、delete）優(yōu)先

MySQL 還允許改變語(yǔ)句調(diào)度的優(yōu)先級(jí)，它可以使來(lái)自多個(gè)客戶端的查詢更好地協(xié)作航厚，這樣單個(gè)客戶端就不會(huì)由于鎖定而等待很長(zhǎng)時(shí)間顷歌。改變優(yōu)先級(jí)還可以確保特定類(lèi)型的查詢被處理得更快。我們首先應(yīng)該確定應(yīng)用的類(lèi)型幔睬，判斷應(yīng)用是以查詢?yōu)橹鬟€是以更新為主的眯漩，是確保查詢效率還是確保更新的效率，決定是查詢優(yōu)先還是更新優(yōu)先麻顶。下面我們提到的改變調(diào)度策略的方法主要是針對(duì)只存在表鎖的存儲(chǔ)引擎赦抖，比如 MyISAM 、MEMROY澈蚌、MERGE摹芙，對(duì)于Innodb 存儲(chǔ)引擎，語(yǔ)句的執(zhí)行是由獲得行鎖的順序決定的宛瞄。MySQL 的默認(rèn)的調(diào)度策略可用總結(jié)如下：

1）寫(xiě)入操作優(yōu)先于讀取操作浮禾。

2）對(duì)某張數(shù)據(jù)表的寫(xiě)入操作某一時(shí)刻只能發(fā)生一次，寫(xiě)入請(qǐng)求按照它們到達(dá)的次序來(lái)處理份汗。

3）對(duì)某張數(shù)據(jù)表的多個(gè)讀取操作可以同時(shí)地進(jìn)行盈电。MySQL 提供了幾個(gè)語(yǔ)句調(diào)節(jié)符，允許你修改它的調(diào)度策略：

LOW_PRIORITY關(guān)鍵字應(yīng)用于DELETE杯活、INSERT匆帚、LOAD DATA、REPLACE和UPDATE旁钧；

HIGH_PRIORITY關(guān)鍵字應(yīng)用于SELECT和INSERT語(yǔ)句吸重；

DELAYED關(guān)鍵字應(yīng)用于INSERT和REPLACE語(yǔ)句互拾。

如果寫(xiě)入操作是一個(gè) LOW_PRIORITY（低優(yōu)先級(jí)）請(qǐng)求，那么系統(tǒng)就不會(huì)認(rèn)為它的優(yōu)先級(jí)高于讀取操作。在這種情況下，如果寫(xiě)入者在等待的時(shí)候榄檬，第二個(gè)讀取者到達(dá)了剥槐，那么就允許第二個(gè)讀取者插到寫(xiě)入者之前。只有在沒(méi)有其它的讀取者的時(shí)候，才允許寫(xiě)入者開(kāi)始操作。這種調(diào)度修改可能存在 LOW_PRIORITY寫(xiě)入操作永遠(yuǎn)被阻塞的情況。

SELECT 查詢的HIGH_PRIORITY（高優(yōu)先級(jí)）關(guān)鍵字也類(lèi)似箍铭。它允許SELECT 插入正在等待的寫(xiě)入操作之前，即使在正常情況下寫(xiě)入操作的優(yōu)先級(jí)更高椎镣。另外一種影響是诈火，高優(yōu)先級(jí)的 SELECT 在正常的 SELECT 語(yǔ)句之前執(zhí)行，因?yàn)檫@些語(yǔ)句會(huì)被寫(xiě)入操作阻塞衣陶。如果希望所有支持LOW_PRIORITY 選項(xiàng)的語(yǔ)句都默認(rèn)地按照低優(yōu)先級(jí)來(lái)處理柄瑰，那么 請(qǐng)使用--low-priority-updates 選項(xiàng)來(lái)啟動(dòng)服務(wù)器。通過(guò)使用 INSERTHIGH_PRIORITY 來(lái)把 INSERT 語(yǔ)句提高到正常的寫(xiě)入優(yōu)先級(jí)剪况，可以消除該選項(xiàng)對(duì)單個(gè)INSERT語(yǔ)句的影響教沾。

四、查詢條件優(yōu)化

1. 對(duì)于復(fù)雜的查詢译断，可以使用中間臨時(shí)表 暫存數(shù)據(jù)授翻；

2. 優(yōu)化group by語(yǔ)句

默認(rèn)情況下，MySQL 會(huì)對(duì)GROUP BY分組的所有值進(jìn)行排序孙咪，如 “GROUP BY col1堪唐，col2，....;” 查詢的方法如同在查詢中指定 “ORDER BY col1翎蹈，col2淮菠，...;” 如果顯式包括一個(gè)包含相同的列的 ORDER BY子句，MySQL 可以毫不減速地對(duì)它進(jìn)行優(yōu)化荤堪，盡管仍然進(jìn)行排序合陵。

因此，如果查詢包括 GROUP BY 但你并不想對(duì)分組的值進(jìn)行排序澄阳，你可以指定 ORDER BY NULL禁止排序拥知。例如：

SELECTcol1, col2,COUNT(*)FROMtableGROUPBYcol1, col2ORDERBYNULL;

3. 優(yōu)化join語(yǔ)句

MySQL中可以通過(guò)子查詢來(lái)使用 SELECT 語(yǔ)句來(lái)創(chuàng)建一個(gè)單列的查詢結(jié)果，然后把這個(gè)結(jié)果作為過(guò)濾條件用在另一個(gè)查詢中碎赢。使用子查詢可以一次性的完成很多邏輯上需要多個(gè)步驟才能完成的 SQL 操作低剔，同時(shí)也可以避免事務(wù)或者表鎖死，并且寫(xiě)起來(lái)也很容易。但是襟齿，有些情況下姻锁，子查詢可以被更有效率的連接(JOIN)..替代。

例子：假設(shè)要將所有沒(méi)有訂單記錄的用戶取出來(lái)蕊唐，可以用下面這個(gè)查詢完成：

SELECTcol1FROMcustomerinfoWHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo )

如果使用連接(JOIN).. 來(lái)完成這個(gè)查詢工作屋摔，速度將會(huì)有所提升。尤其是當(dāng) salesinfo表中對(duì) CustomerID 建有索引的話替梨，性能將會(huì)更好，查詢?nèi)缦拢?/p>

SELECTcol1FROMcustomerinfo

LEFTJOINsalesinfoON customerinfo.CustomerID=salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

連接(JOIN).. 之所以更有效率一些装黑，是因?yàn)?MySQL 不需要在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作副瀑。

4. 優(yōu)化union查詢

MySQL通過(guò)創(chuàng)建并填充臨時(shí)表的方式來(lái)執(zhí)行union查詢。除非確實(shí)要消除重復(fù)的行恋谭，否則建議使用union all糠睡。原因在于如果沒(méi)有all這個(gè)關(guān)鍵詞，MySQL會(huì)給臨時(shí)表加上distinct選項(xiàng)疚颊，這會(huì)導(dǎo)致對(duì)整個(gè)臨時(shí)表的數(shù)據(jù)做唯一性校驗(yàn)狈孔，這樣做的消耗相當(dāng)高。

高效：

SELECTCOL1, COL2, COL3FROMTABLEWHERECOL1 =10

UNIONALL

SELECTCOL1, COL2, COL3FROMTABLEWHERECOL3='TEST';

低效：

SELECTCOL1, COL2, COL3FROMTABLEWHERECOL1 =10

UNION

SELECTCOL1, COL2, COL3FROMTABLEWHERECOL3='TEST';

5.拆分復(fù)雜SQL為多個(gè)小SQL材义，避免大事務(wù)

簡(jiǎn)單的SQL容易使用到MySQL的QUERY CACHE均抽；

減少鎖表時(shí)間特別是使用MyISAM存儲(chǔ)引擎的表；

可以使用多核CPU其掂。

6. 使用truncate代替delete

當(dāng)刪除全表中記錄時(shí)油挥，使用delete語(yǔ)句的操作會(huì)被記錄到undo塊中，刪除記錄也記錄binlog款熬，當(dāng)確認(rèn)需要?jiǎng)h除全表時(shí)深寥，會(huì)產(chǎn)生很大量的binlog并占用大量的undo數(shù)據(jù)塊，此時(shí)既沒(méi)有很好的效率也占用了大量的資源贤牛。

使用truncate替代惋鹅，不會(huì)記錄可恢復(fù)的信息，數(shù)據(jù)不能被恢復(fù)殉簸。也因此使用truncate操作有其極少的資源占用與極快的時(shí)間闰集。另外，使用truncate可以回收表的水位喂链，使自增字段值歸零返十。

7. 使用合理的分頁(yè)方式以提高分頁(yè)效率

使用合理的分頁(yè)方式以提高分頁(yè)效率 針對(duì)展現(xiàn)等分頁(yè)需求，合適的分頁(yè)方式能夠提高分頁(yè)的效率椭微。

案例1：

select*fromtwherethread_id =10000anddeleted =0

order by gmt_create asc limit0,15;

上述例子通過(guò)一次性根據(jù)過(guò)濾條件取出所有字段進(jìn)行排序返回洞坑。數(shù)據(jù)訪問(wèn)開(kāi)銷(xiāo)=索引IO+索引全部記錄結(jié)果對(duì)應(yīng)的表數(shù)據(jù)IO。因此蝇率，該種寫(xiě)法越翻到后面執(zhí)行效率越差迟杂，時(shí)間越長(zhǎng)刽沾，尤其表數(shù)據(jù)量很大的時(shí)候。

適用場(chǎng)景：當(dāng)中間結(jié)果集很信趴健（10000行以下）或者查詢條件復(fù)雜（指涉及多個(gè)不同查詢字段或者多表連接）時(shí)適用侧漓。

案例2：

selectt.*from(selectidfromtwherethread_id =10000anddeleted =0

orderbygmt_createasclimit0,15) a, t

wherea.id = t.id;

上述例子必須滿足t表主鍵是id列，且有覆蓋索引secondary key:(thread_id, deleted, gmt_create)监氢。通過(guò)先根據(jù)過(guò)濾條件利用覆蓋索引取出主鍵id進(jìn)行排序布蔗，再進(jìn)行join操作取出其他字段。數(shù)據(jù)訪問(wèn)開(kāi)銷(xiāo)=索引IO+索引分頁(yè)后結(jié)果（例子中是15行）對(duì)應(yīng)的表數(shù)據(jù)IO浪腐。因此纵揍，該寫(xiě)法每次翻頁(yè)消耗的資源和時(shí)間都基本相同，就像翻第一頁(yè)一樣议街。

適用場(chǎng)景：當(dāng)查詢和排序字段（即where子句和order by子句涉及的字段）有對(duì)應(yīng)覆蓋索引時(shí)泽谨，且中間結(jié)果集很大的情況時(shí)適用。

五特漩、建表優(yōu)化

1. 在表中建立索引吧雹，優(yōu)先考慮where、order by使用到的字段涂身。

2. 盡量使用數(shù)字型字段（如性別雄卷，男：1 女：2），若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型访得，這會(huì)降低查詢和連接的性能龙亲，并會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)。

這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì) 逐個(gè)比較字符串中每一個(gè)字符悍抑，而對(duì)于數(shù)字型而言只需要比較一次就夠了鳄炉。

3. 查詢數(shù)據(jù)量大的表 會(huì)造成查詢緩慢。主要的原因是掃描行數(shù)過(guò)多搜骡。這個(gè)時(shí)候可以通過(guò)程序拂盯，分段分頁(yè)進(jìn)行查詢，循環(huán)遍歷记靡，將結(jié)果合并處理進(jìn)行展示谈竿。要查詢100000到100050的數(shù)據(jù)，如下：

SELECT*FROM(SELECTROW_NUMBER()OVER(ORDERBYIDASC)ASrowid,*

FROMinfoTab)tWHEREt.rowid >100000ANDt.rowid <=100050

4. 用varchar/nvarchar 代替 char/nchar

盡可能的使用 varchar/nvarchar 代替 char/nchar 摸吠，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小空凸，可以節(jié)省存儲(chǔ)空間，其次對(duì)于查詢來(lái)說(shuō)寸痢，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些呀洲。

不要以為 NULL 不需要空間，比如：char(100) 型，在字段建立時(shí)道逗，空間就固定了兵罢， 不管是否插入值（NULL也包含在內(nèi)），都是占用 100個(gè)字符的空間的滓窍，如果是varchar這樣的變長(zhǎng)字段卖词， null 不占用空間。