慢sql優(yōu)化思路及使用規(guī)范

1、索引優(yōu)化

1.1 建表或加索引時，保證表里互相不存在冗余索引。

對于MySQL來說，如果表里已經(jīng)存在key(a,b)，則key(a)為冗余索引，需要刪除。

1.2 復(fù)合索引（組合索引）

建立索引時，多考慮建立復(fù)合索引，并把區(qū)分度最高的字段放在最前面。
有時候區(qū)分度高的字段可能不符合個人或者業(yè)務(wù)習(xí)慣，比如
select * from user where name = '' and sex ='' and age=''；age的區(qū)分度明顯比sex要高，如果要建立符合索引，則 age應(yīng)該是在 sex前面。

比如 select * from goods where goods_no = 'aaa' and state=1;
這種情況我們只需要建了一個復(fù)合索引就可以，這就相當(dāng)于創(chuàng)建了(goods_no ,state)、(goods_no )兩個索引，這就是最佳左前綴特性。

ALTER TABLE `goods` ADD INDEX `idx_goodsno_state` (`goods_no`,`state`) USING BTREE；

如果索引個數(shù)超過5個，可以考慮把多個索引字段拼接后通過md5加密，然后插入表中，這樣可以大大提升索引效率。

同時復(fù)合索引（組合索引）中的字段盡量避免為null，有些場景下索引可能會失效。所以默認(rèn)建表時，所有字段都應(yīng)是 not null 同時給一個默認(rèn)值，字符串類型默認(rèn)可以為 ''

1.3 使用短索引

對串列進(jìn)行MySql索引，如果可能應(yīng)該指定一個前綴長度。例如，如果有一個CHAR(255)的 列，如果在前10 個或20 個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

1.4 單個表上的索引個數(shù)不能超過8個

索引不是越多越好，索引也要占據(jù)空間，同時維護(hù)索引也需要消耗時間。

1.5 在多表join的SQL里，保證被驅(qū)動表的連接列上有索引，這樣join執(zhí)行效率最高。

where條件里等號左右字段類型必須一致，否則無法利用索引

數(shù)據(jù)類型不一致會導(dǎo)致索引失效

1.6 不要在列上進(jìn)行運(yùn)算，否則導(dǎo)致索引失效而進(jìn)行全表掃描

索引列不要使用函數(shù)或表達(dá)式，否則無法利用索引。如where length(name)='Admin'或where user_id+2=10023。

再比如我們會在create_tm添加索引，便于按照時間查詢，這樣情況下，就不要在列上進(jìn)行格式化

 SELECT IFNULL(count(1),0) as sfmSum FROM mg_order_new m1 
where  DATE_FORMAT(m1.create_tm, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')

1.7 LIKE雙百分號無法使用到索引

一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

1.8 索引不會包含有NULL值的列

只要列中包含有NULL值都將不會被包含在索引中，復(fù)合索引中只要有一列含有NULL值，那么這一列對于此復(fù)合索引就是無效的。所以我們在數(shù)據(jù)庫設(shè)計時不要讓字段的默認(rèn)值為NULL。

1.9 asc和desc混用

select * from _t where a=1 order by b desc, c asc

desc 和asc混用時會導(dǎo)致索引失效，所以跟產(chǎn)品經(jīng)理溝通時，盡量不要存在這種排序

1.10 不等于、不包含不能用到索引的快速搜索

select * from _order where shop_id=1 and order_status not in (1,2)
select * from _order where shop_id=1 and order_status != 1

在索引上，避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

1.11 范圍查詢阻斷，后續(xù)字段不能走索引

KEY `idx_shopid_created_status` (`shop_id`, `created_at`, `order_status`)

select * from _order where shop_id = 1 and created_at > '2021-01-01 00:00:00' and order_status = 10

范圍查詢還有“IN、between”

KEY `idx_shopid_status_created` (`shop_id`, `order_status`, `created_at`)

select * from _order where shop_id = 1 and order_status in (1, 2, 3) order by created_at desc limit 10

優(yōu)化：可以(order_status, created_at)互換前后順序

2、sql優(yōu)化

2.1 讀取適當(dāng)?shù)挠涗?limit

假如我們確定記錄只有一條，那還是要習(xí)慣加上limit 1, 這樣在找到一條數(shù)據(jù)后就直接返回了，不會繼續(xù)掃描表；

2.2 分組統(tǒng)計可以禁止排序

默認(rèn)情況下，MySQL對所有GROUP BY col1，col2…的字段進(jìn)行排序。如果查詢包括GROUP BY，想要避免排序結(jié)果的消耗，則可以指定ORDER BY NULL禁止排序

//隱式排序
select goods_no as n, name as m,point_price as p, exchange_total as t, serial_num as s, sale_channel as c from mall_goods mg
        where mg.state = 1 group by goods_no, serial_num
                                
//添加ORDER BY NULL禁止排序               
select goods_no as n, name as m,point_price as p, exchange_total as t, serial_num as s, sale_channel as c from mall_goods mg  
        where  mg.state = 1 group by goods_no, point_price  ORDER BY NULL
image.png
image.png

所以在需要分組并不需要對結(jié)果進(jìn)行排序的情況下，我們可以禁止隱式排序

上面的例子在MySQL 5.7及更低版本生效，GROUP BY在某些條件下隱式排序。 在MySQL 8.0中，不再發(fā)生這種情況，因此不再需要在末尾指定ORDER BY NULL來抑制隱式排序。
不過目前公司線上環(huán)境通用的還是MySQL 5.7

2.3 事務(wù)里更新語句盡量基于主鍵或unique key，如update … where id=XX;

否則會產(chǎn)生間隙鎖，內(nèi)部擴(kuò)大鎖定范圍，導(dǎo)致系統(tǒng)性能下降，產(chǎn)生死鎖。
具體原理見 mysql多線程update死鎖問題

  • 2.4 不建議使用子查詢，建議將子查詢SQL拆開結(jié)合程序多次查詢，或使用join來代替子查詢。
    例：SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name='hechunyang');

    子查詢在MySQL5.5版本里，內(nèi)部執(zhí)行計劃器是這樣執(zhí)行的：先查外表再匹配內(nèi)表，而不是先查內(nèi)表t2，當(dāng)外表的數(shù)據(jù)很大時，查詢速度會非常慢。

    在MariaDB10/MySQL5.6版本里，采用join關(guān)聯(lián)方式對其進(jìn)行了優(yōu)化，這條SQL會自動轉(zhuǎn)換為

    但請注意的是：優(yōu)化只針對SELECT有效，對UPDATE/DELETE子查詢無效，故生產(chǎn)環(huán)境應(yīng)避免使用子查詢

2.5 Using temporary 優(yōu)化

多表關(guān)聯(lián)left join其他表的時候，如果以其他表的字段作為查詢條件都會產(chǎn)生臨時表Using temporary; 這會使得性能受到影響
把非直接關(guān)聯(lián)的表改為直接關(guān)聯(lián)，可以通過改為不作為查詢條件的子查詢（不要在where后面使用子查詢），

//優(yōu)化前，出現(xiàn)了文件排序和臨時表問題。
 EXPLAIN  SELECT  video.target,video.state, video.flag,video.time_length,video.upload_time,video.cover_position,video.click_count,
     member.nickname
from app_recommend_controller
left join video on app_recommend_controller.video_id= video.id 
left join member on member.id= video.member_id
 WHERE video.display= 1   AND video.game_id= '9930'
ORDER BY video.upload_time  desc LIMIT 0,20

 ```
 //優(yōu)化后
 EXPLAIN  SELECT  video.target,video.state, video.flag,video.time_length,video.upload_time,video.cover_position,video.click_count,
   (select nickname form member where id= video.id) as   nickname   #這部分代替原來的內(nèi)連接查詢出來的昵稱
   from app_recommend_controller
   left join video on app_recommend_controller.video_id= video.id 
   WHERE video.display= 1   AND video.game_id= '9930'
    ORDER BY app_recommend_controller.video_id desc LIMIT 0,20
 ```

通過把非直接關(guān)聯(lián)表member 從join查詢 改為 不作為查詢條件的子查詢，來優(yōu)化 Using temporary

參考：https://www.cnblogs.com/jpfss/p/9156422.html

2.6 Using filesort

在使用order by關(guān)鍵字的時候，如果待排序的內(nèi)容不能由所使用的索引直接完成排序的話，那么mysql有可能就要進(jìn)行文件排序。
優(yōu)化：1、修改邏輯，不在mysql中使用order by而是在應(yīng)用中自己進(jìn)行排序。
2、使用mysql索引，將待排序的內(nèi)容放到索引中，直接利用索引的排序。

2.7 包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結(jié)果集請保持在1000行以內(nèi)，否則SQL會很慢

2.8 SELECT語句不要使用UNION，推薦使用UNION ALL，并且UNION子句個數(shù)限制在5個以內(nèi)。

因?yàn)閡nion all不需要去重，節(jié)省數(shù)據(jù)庫資源，提高性能。

2.9 線上環(huán)境，多表join不要超過5個表

2.10 在多表join中，盡量選取結(jié)果集較小的表作為驅(qū)動表，來join其他表

2.11 程序端SELECT語句必須指定具體字段名稱，禁止寫成 *

2.12 事務(wù)里包含SQL不超過5個

因?yàn)檫^長的事務(wù)會導(dǎo)致鎖數(shù)據(jù)較久，MySQL內(nèi)部緩存、連接消耗過多等問題。

2.13 事務(wù)操作 不要和 http、rpc調(diào)用寫到一起

假如 微服務(wù)A 和 微服務(wù)B同時操作一張表中同樣數(shù)據(jù)， 然后呢，微服務(wù)A 把mysql 和 http調(diào)用 微服務(wù)B寫在了一起，
這就會導(dǎo)致一個嚴(yán)重的問題，微服務(wù)A 在 update 鎖表后，在調(diào)用微服務(wù)B之后才會提交事務(wù)，但是微服務(wù)B里同樣對 同一張表的數(shù)據(jù)有update，也加鎖，所以呢，高并發(fā)情況下，會直接鎖表。

解決辦法，就微服務(wù)高內(nèi)聚 低耦合，把對同一塊業(yè)務(wù) 同一張表的代碼 聚合到一個微服務(wù)里。

還有就是，不要把 mysql 事務(wù)操作 和 http、rpc調(diào)用寫到一起。

2.13 對于超過100W行的大表進(jìn)行alter table，必須經(jīng)過DBA審核，并在業(yè)務(wù)低峰期執(zhí)行，多個alter需整合在一起。

因?yàn)閍lter table會產(chǎn)生表鎖，期間阻塞對于該表的所有寫入，對于業(yè)務(wù)可能會產(chǎn)生極大影響。

2.14 不使用NOT IN和<>操作

NOT IN和<>操作都不會使用索引將進(jìn)行全表掃描。NOT IN可以NOT EXISTS代替，id<>3則可使用id>3 or id<3來代替。

2.15 用IN來替換OR

低效查詢
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
高效查詢
SELECT * FROM t WHERE LOC_IN IN (10,20,30);

2.16 大分頁

select * from _t where a = 1 and b = 2 order by id desc limit 10000, 10;

對于大分頁，越往后性能越差。
優(yōu)化：把上一次的最后一條數(shù)據(jù)，也即上面的id傳過來，然后做“id < xxx”處理

2.17 count

  • count(主鍵 id)
    InnoDB 引擎會遍歷整張表，把每一行的 id 值都取出來，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加。
  • count(1)
    InnoDB 引擎遍歷整張表，但不取值。server 層對于返回的每一行，放一個數(shù)字“1”進(jìn)去，判斷是不可能為空的，按行累加。
    只看這上面這兩個用法，count(1) 執(zhí)行得要比 count(主鍵 id) 快。因?yàn)閺囊娣祷?id 會涉及到解析數(shù)據(jù)行，以及拷貝字段值的操作
  • count(字段)
    如果這個“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個字段，判斷不能為 null，按行累加；
    如果這個“字段”定義允許為 null，那么執(zhí)行的時候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。
  • count()
    count(    )是例外，并不會把全部字段取出來，而是專門做了優(yōu)化，不取值。count()肯定不是 null，按行累加。按照效率排序的話，count() = count(1) > count(主鍵 id) > count(字段)，所以建議盡量使用 count(*)。
image.png
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容