一、SQL執(zhí)行流程

MySQL是客戶端-服務(wù)器的模式。一條SQL的執(zhí)行流程如下：

在執(zhí)行過(guò)程中，主要有三類角色：客戶端、服務(wù)器、存儲(chǔ)引擎。

大致可以分為三層：

第一層：客戶端連接到服務(wù)器，構(gòu)造SQL并發(fā)送給服務(wù)器。

第二層：服務(wù)器收到SQL進(jìn)行解析及優(yōu)化，最終生成執(zhí)行計(jì)劃并執(zhí)行

第三層：服務(wù)器調(diào)用存儲(chǔ)引擎的API，進(jìn)行數(shù)據(jù)的查詢和存儲(chǔ)。

上述的查詢緩存，在MySQL8.0版本后移出，原因是不太實(shí)用，對(duì)表進(jìn)行修改操作后，需要進(jìn)行刪除緩存并重新添加，對(duì)于一些寫(xiě)并不頻繁的表還好，但市面上還有一些更加實(shí)用做緩存的工具，因此這個(gè)功能比較尷尬。

二、執(zhí)行計(jì)劃分析

可以通過(guò)explain提前知道當(dāng)前MySQL是如何處理SQL語(yǔ)句的。

在我們要執(zhí)行的SQL前加上explain即可。

explain select * from user;explain insert into user values(user_name, password, email) values('1', '1', '1');

---

通過(guò)分析執(zhí)行計(jì)劃，我們可以得出以下幾點(diǎn)：

1.表的讀取順序 -> id

2.表讀取操作的類型 -> select_type

3.可以被使用的索引 -> possible_keys

4.實(shí)際使用的索引 -> key

5.表之間的引用 -> ref

6.每張表有多少行被優(yōu)化器查詢 -> rows

上述查詢結(jié)果字段的含義：

1.id：表示查詢中執(zhí)行select子句或操作表的順序，id越大，執(zhí)行優(yōu)先級(jí)越高，id相同，則從上至下

2.select_type：表示查詢的類型，用來(lái)區(qū)分普通查詢、聯(lián)合查詢、子查詢等。一共有9種類型。

3.table:? 輸出行所引用的表名，如果使用了別名則顯示別名

4.partitions：使用的哪個(gè)分區(qū)

5.type：查詢使用了那種類型。描述的是當(dāng)前如何去找數(shù)據(jù)的，如：all 表示掃描全表。

6.possible_keys：可能有助于查詢的索引

7.key：實(shí)際使用的索引

8.key_len:??使用的索引的長(zhǎng)度

9.ref：顯示索引的哪一列被使用了?

10.rows：請(qǐng)求數(shù)據(jù)大概返回的行數(shù)

11.filtered：表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過(guò)濾后，剩下多少滿足查詢的記錄數(shù)量的比例

12.extra:??其他信息，出現(xiàn)Using filesort、Using temporary 意味著不能使用索引,效率會(huì)受到重大影響。應(yīng)盡可能對(duì)此進(jìn)行優(yōu)化。

其中比較重要的字段：

1.type：可以看出是如何查詢數(shù)據(jù)的方式。一般需要達(dá)到 ref、eq_ref?級(jí)別，范圍查找需要達(dá)到 range。

2.key：是否使用索引，如果為NULL表示沒(méi)有使用索引，需要優(yōu)化調(diào)整。

3.rows：表示返回的行數(shù)，可以直觀觀察到結(jié)果。

4.extra：有Using filesort、Using temporary 的一定需要優(yōu)化。

三、表結(jié)構(gòu)優(yōu)化

數(shù)據(jù)庫(kù)效率的影響主要是因?yàn)閿?shù)據(jù)量太大，進(jìn)行一次查找需要掃描很多數(shù)據(jù)(硬盤上的磁頭需要越過(guò)很多數(shù)據(jù)來(lái)找到目標(biāo)數(shù)據(jù))，通過(guò)表結(jié)構(gòu)優(yōu)化的方式可以減輕當(dāng)前訪問(wèn)的數(shù)據(jù)量。

3.1 數(shù)據(jù)類型優(yōu)化

主旨就是能用小字段類型就不用大字段類型～

• 使用簡(jiǎn)單的數(shù)據(jù)類型。int 要比 varchar 類型在mysql處理簡(jiǎn)單
• 盡量使用 tinyint、smallint、mediumint 作為整數(shù)類型而非 int
• 盡可能使用 not null 定義字段，因?yàn)?null 占用4字節(jié)空間。數(shù)字可以默認(rèn)0，字符串默認(rèn)“”
• 盡量少用 text 類型，非用不可時(shí)最好考慮分表
• 盡量使用 timestamp而非 datetime
• 單表不要有太多字段，建議在 20 以內(nèi)

3.2 分庫(kù)分表優(yōu)化

當(dāng)數(shù)據(jù)太多的時(shí)候，即使走索引啥的也不能解決效率問(wèn)題，根本就在于要掃描的數(shù)據(jù)太多了，并且存儲(chǔ)也是比較難的。

這時(shí)候就可以采用分表的方式，將一張表拆分成多張，然后通過(guò)編號(hào)等手段進(jìn)行查詢。

拆分大致也有兩種方式：

垂直拆分：

? ? ? ? 按照列的維度，將表中的列拆分開(kāi)來(lái)，分別放在多張表中。例如：某些字段在一張表中可能更加平凡的查詢，可以將這些字段放到一張表中，不常用的放在另一張表中。

????????但需要注意的是，這種方式需要保證原子性！可以在進(jìn)行插入的時(shí)候使用事務(wù)～

水平拆分：

? ? ? ? 按照行的維度，將一張表的數(shù)據(jù)切分。如0-100的數(shù)據(jù)放在這張表中，101-200的數(shù)據(jù)放在另一張表中。

3.3 讀寫(xiě)分離優(yōu)化

由于一臺(tái)數(shù)據(jù)庫(kù)服務(wù)器的性能肯定是有瓶頸的，可以進(jìn)行部署一個(gè)數(shù)據(jù)庫(kù)集群。并采用主從的方式。設(shè)置一些主庫(kù)，一些從庫(kù)，主庫(kù)用來(lái)負(fù)責(zé)寫(xiě)入數(shù)據(jù)，從庫(kù)用來(lái)負(fù)責(zé)讀取數(shù)據(jù)，當(dāng)一個(gè)新的數(shù)據(jù)寫(xiě)入的時(shí)候，主庫(kù)需要將數(shù)據(jù)同步到從庫(kù)中，以保證數(shù)據(jù)的完整性。

四、查詢語(yǔ)句優(yōu)化

4.1避免使用 select *

sql在解析過(guò)程中，還需要把*依次轉(zhuǎn)換為所有的列名，這個(gè)工作需要查詢數(shù)據(jù)字典完成。額外開(kāi)銷！因此建議將需要的列寫(xiě)出來(lái)。

4.2多表聯(lián)查時(shí)，小表在前，大表在后

from 后的表關(guān)聯(lián)查詢是從左往右執(zhí)行的（Oracle相反），第一張表會(huì)涉及到全表掃描，所以將小表放在前面，先掃小表，掃描快效率較高，在掃描后面的大表。

4.3調(diào)整where子句中的連接順序

where子句是從左往右，自上而下的順序執(zhí)行的（Oracle相反），根據(jù)這個(gè)原理，應(yīng)將過(guò)濾數(shù)據(jù)多的條件往前放，最快速度縮小結(jié)果集。

4.4調(diào)整group by和order by子句中的順序

group by和order by子句是從左往右的順序執(zhí)行的，根據(jù)這個(gè)原理，應(yīng)將排序影響數(shù)據(jù)多的條件往前放，最快速度縮小結(jié)果集。

4.5用exists、not exists和in、not in相互替代

exists以外層表為驅(qū)動(dòng)表，先被訪問(wèn)，適合于外表小而內(nèi)表大的情況。

in則是先執(zhí)行子查詢，適合外表大而內(nèi)表小的情況，一般情況是不推薦使用not in，因?yàn)樾史浅５汀?/p>

原則是哪個(gè)的子查詢產(chǎn)生的結(jié)果集小，就選哪個(gè)

4.6用where子句替換having子句

where子句搜索條件在進(jìn)行分組操作之前應(yīng)用；而having子句條件在進(jìn)行分組操作之后應(yīng)用。

盡可能讓where來(lái)縮小結(jié)果集！

4.7分段和分頁(yè)查詢

使用合理的分頁(yè)方式，在數(shù)據(jù)表量級(jí)逐漸增加的時(shí)候，limit分頁(yè)查詢的效率會(huì)降低。

可以根據(jù)字段索引進(jìn)行快速定位，直接找到偏移量。

五、索引優(yōu)化

眾所周知，索引是通過(guò)犧牲空間來(lái)?yè)Q取時(shí)間的。當(dāng)我們頻繁的去進(jìn)行插入/刪除的時(shí)候，會(huì)影響性能，因此索引更適合于“讀多寫(xiě)少”的場(chǎng)景。

索引可以幫助我們提高查詢效率，因此需要避免索引失效的場(chǎng)景。

5.1盡量避免在字段開(kāi)頭模糊查詢

select * from user where user_name like '%陳%'; -不走索引
select * from user where user_name like '陳%'; -走索引

5.2盡量避免使用not in和in

如果是連續(xù)數(shù)值，可以用between代替

select * from user id in(1,2); -不走索引
select * from user id between 1 and 2; -走索引

如果是子查詢，可以用exists代替

select * from A where A.id in (select id from B);	-- 不走索引
select * from A where exists (select * from B where B.id = A.id);	-- 走索引

5.3盡量避免使用or或in

SELECT * FROM t WHERE id = 1 OR id = 3; -- 不走索引
SELECT * FROM t WHERE id in (1, 3); -- 不走索引
SELECT * FROM t WHERE id = 1UNION
SELECT * FROM t WHERE id = 3; -- 走索引

5.4盡量避免查詢條件使用用!=或者<>

5.5盡量避免進(jìn)行null值的判斷

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

SELECT * FROM t WHERE score IS NULL; -- 不走索引
SELECT * FROM t WHERE score = 0; -- 走索引

5.6盡量避免在where條件中等號(hào)的左側(cè)進(jìn)行表達(dá)式、函數(shù)操作

SELECT * FROM T WHERE score/10 = 9; -- 不走索引
SELECT * FROM T WHERE score = 9*10; -- 走索引

5.7盡量避免隱式數(shù)據(jù)類型轉(zhuǎn)換

有些字符串可以自動(dòng)轉(zhuǎn)換為數(shù)字類型，可以避免轉(zhuǎn)換

5.8盡量避免使用復(fù)合索引時(shí)，不使用第一個(gè)索引列

復(fù)合（聯(lián)合）索引包含key1，key2，key3三列，SQL語(yǔ)句沒(méi)有包含索引前置列"key1"

select col1 from table where key2=2 and key3=3; -- 不走索引
select col1 from table where key1=1 and key2=2 and key3=3; -- 走索引

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

SELECT * FROM t order by age; -- 不走age索引
SELECT * FROM t where age > 0 order by age; -- 走age索引

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