用c 做的網(wǎng)站怎么打開(kāi)嗎百度seo關(guān)鍵詞優(yōu)化公司
一、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)樾史浅5汀?/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í),不用排序操作。