1.避免使用select *
在實(shí)際業(yè)務(wù)場景中，可能我們真正需要使用的只有其中一兩列。
但我們寫sql語句時(shí)，為了方便，喜歡直接使用select *，一次性查出表中所有列的數(shù)據(jù)。多查出來的數(shù)據(jù)，通過網(wǎng)絡(luò)IO傳輸?shù)倪^程中，也會(huì)增加數(shù)據(jù)傳輸?shù)臅r(shí)間。
還有一個(gè)最重要的問題是：select *不會(huì)走覆蓋索引，會(huì)出現(xiàn)大量的回表操作，而從導(dǎo)致查詢sql的性能很低。
2.小表驅(qū)動(dòng)大表
小表驅(qū)動(dòng)大表，即用小表的數(shù)據(jù)集驅(qū)動(dòng)大表的數(shù)據(jù)集。
假如有order和user兩張表，其中order表有10000條數(shù)據(jù)，而user表有100條數(shù)據(jù)。
這時(shí)如果想查一下，所有有效的用戶下過的訂單列表。
可以使用in關(guān)鍵字實(shí)現(xiàn)：
select * from order where user_id in (select id from user where status=1)
sql語句中包含了in關(guān)鍵字，則它會(huì)優(yōu)先執(zhí)行in里面的子查詢語句，然后再執(zhí)行in外面的語句。如果in里面的數(shù)據(jù)量很少，作為條件查詢速度更快。
3.避免in中值太多
對于批量查詢接口，我們通常會(huì)使用in關(guān)鍵字過濾出數(shù)據(jù)。比如：想通過指定的一些id，批量查詢出用戶信息。
select id,name from category where id in (1,2,3...100000000);
如果我們不做任何限制，該查詢語句一次性可能會(huì)查詢出非常多的數(shù)據(jù)，很容易導(dǎo)致接口超時(shí)。
如果ids超過500條記錄，可以分批用多線程去查詢數(shù)據(jù)。每批只查500條記錄，最后把查詢到的數(shù)據(jù)匯總到一起返回。
4.高效的分頁
在mysql中分頁一般用的limit關(guān)鍵字：
select id,name,age from user limit 10,20;
select id,name,age from user limit 1000000,20;
mysql會(huì)查到1000020條數(shù)據(jù)，然后丟棄前面的1000000條，只查后面的20條數(shù)據(jù)，這個(gè)是非常浪費(fèi)資源的。
解決方案：先找到上次分頁最大的id，然后利用id上的索引查詢。不過該方案，要求id是連續(xù)的，并且有序的。
select id,name,age from user where id > 1000000 limit 20;
5.連接代替子查詢
mysql中如果需要從兩張以上的表中查詢出數(shù)據(jù)的話，一般有兩種實(shí)現(xiàn)方式：子查詢 和 連接查詢。
select * from order where user_id in (select id from user where status=1)
子查詢語句可以通過in關(guān)鍵字實(shí)現(xiàn)，一個(gè)查詢語句的條件落在另一個(gè)select語句的查詢結(jié)果中。程序先運(yùn)行在嵌套在最內(nèi)層的語句，再運(yùn)行外層的語句。
子查詢語句的優(yōu)點(diǎn)是簡單，結(jié)構(gòu)化，如果涉及的表數(shù)量不多的話。
但缺點(diǎn)是mysql執(zhí)行子查詢時(shí)，需要?jiǎng)?chuàng)建臨時(shí)表，查詢完畢后，需要再刪除這些臨時(shí)表，有一些額外的性能消耗。
select o.* from order o inner join user u on o.user_id = u.id where u.status=1
6.控制join表的數(shù)量
根據(jù)阿里巴巴開發(fā)者手冊的規(guī)定，join表的數(shù)量不應(yīng)該超過3個(gè)。如果join太多，mysql在選擇索引的時(shí)候會(huì)非常復(fù)雜，很容易選錯(cuò)索引。
并且如果沒有命中中，nested loop join 就是分別從兩個(gè)表讀一行數(shù)據(jù)進(jìn)行兩兩對比，復(fù)雜度是 n^2。所以我們應(yīng)該盡量控制join表的數(shù)量。
如果實(shí)現(xiàn)業(yè)務(wù)場景中需要查詢出另外幾張表中的數(shù)據(jù)，可以在a、b、c表中冗余專門的字段，比如：在表a中冗余d_name字段，保存需要查詢出的數(shù)據(jù)。
如果兩張表使用left join關(guān)聯(lián)，mysql會(huì)默認(rèn)用left join關(guān)鍵字左邊的表，去驅(qū)動(dòng)它右邊的表。如果左邊的表數(shù)據(jù)很多時(shí)，就會(huì)出現(xiàn)性能問題。
7.控制索引的數(shù)量
眾所周知，索引能夠顯著的提升查詢sql的性能，但索引數(shù)量并非越多越好。因?yàn)楸碇行略鰯?shù)據(jù)時(shí)，需要同時(shí)為它創(chuàng)建索引，而索引是需要額外的存儲空間的，而且還會(huì)有一定的性能消耗。
阿里巴巴的開發(fā)者手冊中規(guī)定，單表的索引數(shù)量應(yīng)該盡量控制在5個(gè)以內(nèi)，并且單個(gè)索引中的字段數(shù)不超過5個(gè)。
mysql使用的B+樹的結(jié)構(gòu)來保存索引的，在insert、update和delete操作時(shí)，需要更新B+樹索引。如果索引過多，會(huì)消耗很多額外的性能。
8.提升group by的效率
反例：
select user_id,user_name from order
group by user_id
having user_id <= 200;
這種寫法性能不好，它先把所有的訂單根據(jù)用戶id分組之后，再去過濾用戶id大于等于200的用戶。
正例：
select user_id,user_name from order
where user_id <= 200
group by user_id
使用where條件在分組前，就把多余的數(shù)據(jù)過濾掉了，這樣分組時(shí)效率就會(huì)更高一些。
其實(shí)這是一種思路，不僅限于group by的優(yōu)化。我們的sql語句在做一些耗時(shí)的操作之前，應(yīng)盡可能縮小數(shù)據(jù)范圍，這樣能提升sql整體的性能。
9.索引優(yōu)化
sql優(yōu)化當(dāng)中，有一個(gè)非常重要的內(nèi)容就是：索引優(yōu)化。很多時(shí)候sql語句，走了索引，和沒有走索引，執(zhí)行效率差別很大。
索引優(yōu)化的第一步是：檢查sql語句有沒有走索引。可以使用explain命令，查看mysql的執(zhí)行計(jì)劃。進(jìn)而查看索引是否生效
sql語句沒有走索引，排除沒有建索引之外，最大的可能性就是索引失效。
該文章在 2024/9/10 10:13:09 編輯過
400 186 1886
