Mysql的慢SQL優(yōu)化思路和規(guī)范詳解
1、索引優(yōu)化
1.1 建表或加索引時(shí),保證表里互相不存在冗余索引。
對(duì)于MySQL來說,如果表里已經(jīng)存在key(a,b),則key(a)為冗余索引,需要?jiǎng)h除。
1.2 復(fù)合索引
建立索引時(shí),多考慮建立復(fù)合索引,并把區(qū)分度最高的字段放在最前面。 比如 select * from goods where goods_no = 'aaa' and state=1; 這種情況我們只需要建了一個(gè)復(fù)合索引就可以,這就相當(dāng)于創(chuàng)建了(goods_no ,state)、(goods_no )兩個(gè)索引,這就是最佳左前綴特性。
ALTER TABLE `goods` ADD INDEX `idx_goodsno_state` (`goods_no`,`state`) USING BTREE;
1.3 使用短索引
對(duì)串列進(jìn)行MySql索引,如果可能應(yīng)該指定一個(gè)前綴長度。例如,如果有一個(gè)CHAR(255)的 列,如果在前10 個(gè)或20 個(gè)字符內(nèi),多數(shù)值是惟一的,那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。
1.4 單個(gè)表上的索引個(gè)數(shù)不能超過8個(gè)
索引不是越多越好,索引也要占據(jù)空間,同時(shí)維護(hù)索引也需要消耗時(shí)間。
1.5 在多表join的SQL里,保證被驅(qū)動(dòng)表的連接列上有索引,這樣join執(zhí)行效率最高。
where條件里等號(hào)左右字段類型必須一致,否則無法利用索引
數(shù)據(jù)類型不一致會(huì)導(dǎo)致索引失效
1.6 不要在列上進(jìn)行運(yùn)算,否則導(dǎo)致索引失效而進(jìn)行全表掃描
索引列不要使用函數(shù)或表達(dá)式,否則無法利用索引。如where length(name)='Admin'或where user_id+2=10023。
再比如我們會(huì)在create_tm添加索引,便于按照時(shí)間查詢,這樣情況下,就不要在列上進(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雙百分號(hào)無法使用到索引
一般情況下不鼓勵(lì)使用like操作,如果非使用不可,如何使用也是一個(gè)問題。like “%aaa%” 不會(huì)使用索引而like “aaa%”可以使用索引。
1.8 索引不會(huì)包含有NULL值的列
只要列中包含有NULL值都將不會(huì)被包含在索引中,復(fù)合索引中只要有一列含有NULL值,那么這一列對(duì)于此復(fù)合索引就是無效的。所以我們?cè)跀?shù)據(jù)庫設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL。
1.9 asc和desc混用
select * from _t where a=1 order by b desc, c asc
desc 和asc混用時(shí)會(huì)導(dǎo)致索引失效,所以跟產(chǎn)品經(jīng)理溝通時(shí),盡量不要存在這種排序
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ù)后就直接返回了,不會(huì)繼續(xù)掃描表;
2.2 分組統(tǒng)計(jì)可以禁止排序
默認(rèn)情況下,MySQL對(duì)所有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
所以在需要分組并不需要對(duì)結(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;
否則會(huì)產(chǎn)生間隙鎖,內(nèi)部擴(kuò)大鎖定范圍,導(dǎo)致系統(tǒng)性能下降,產(chǎn)生死鎖。
2.4 不建議使用子查詢,建議將子查詢SQL拆開結(jié)合程序多次查詢,或使用join來代替子查詢。
例:SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name='hechunyang');
子查詢?cè)贛ySQL5.5版本里,內(nèi)部執(zhí)行計(jì)劃器是這樣執(zhí)行的:先查外表再匹配內(nèi)表,而不是先查內(nèi)表t2,當(dāng)外表的數(shù)據(jù)很大時(shí),查詢速度會(huì)非常慢。
在MariaDB10/MySQL5.6版本里,采用join關(guān)聯(lián)方式對(duì)其進(jìn)行了優(yōu)化,這條SQL會(huì)自動(dòng)轉(zhuǎn)換為
但請(qǐng)注意的是:優(yōu)化只針對(duì)SELECT有效,對(duì)UPDATE/DELETE子查詢無效,故生產(chǎn)環(huán)境應(yīng)避免使用子查詢
2.5 Using temporary 優(yōu)化
多表關(guān)聯(lián)left join其他表的時(shí)候,如果以其他表的字段作為查詢條件都會(huì)產(chǎn)生臨時(shí)表Using temporary; 這會(huì)使得性能受到影響 把非直接關(guān)聯(lián)的表改為直接關(guān)聯(lián),可以通過改為不作為查詢條件的子查詢(不要在where后面使用子查詢),
//優(yōu)化前,出現(xiàn)了文件排序和臨時(shí)表問題。 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
2.6 Using filesort
在使用order by關(guān)鍵字的時(shí)候,如果待排序的內(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é)果集請(qǐng)保持在1000行以內(nèi),否則SQL會(huì)很慢
2.8
SELECT語句不要使用UNION,推薦使用UNION ALL,并且UNION子句個(gè)數(shù)限制在5個(gè)以內(nèi)。
因?yàn)閡nion all不需要去重,節(jié)省數(shù)據(jù)庫資源,提高性能。
2.9 線上環(huán)境
多表join不要超過5個(gè)表
2.10
在多表join中,盡量選取結(jié)果集較小的表作為驅(qū)動(dòng)表,來join其他表
2.11
程序端SELECT語句必須指定具體字段名稱,禁止寫成 *
2.12 事務(wù)里包含SQL不超過5個(gè)
因?yàn)檫^長的事務(wù)會(huì)導(dǎo)致鎖數(shù)據(jù)較久,MySQL內(nèi)部緩存、連接消耗過多等問題。
2.13
對(duì)于超過100W行的大表進(jìn)行alter table,必須經(jīng)過DBA審核,并在業(yè)務(wù)低峰期執(zhí)行,多個(gè)alter需整合在一起。
因?yàn)閍lter table會(huì)產(chǎn)生表鎖,期間阻塞對(duì)于該表的所有寫入,對(duì)于業(yè)務(wù)可能會(huì)產(chǎn)生極大影響。
2.14 不使用NOT IN和<>操作
NOT IN和<>操作都不會(huì)使用索引將進(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;
對(duì)于大分頁,越往后性能越差。 優(yōu)化:把上一次的最后一條數(shù)據(jù),也即上面的id傳過來,然后做“id < xxx”處理
2.17 count
- count(主鍵 id)
- InnoDB 引擎會(huì)遍歷整張表,把每一行的 id 值都取出來,返回給 server 層。server 層拿到 id 后,判斷是不可能為空的,就按行累加。
- count(1)
- InnoDB 引擎遍歷整張表,但不取值。server 層對(duì)于返回的每一行,放一個(gè)數(shù)字“1”進(jìn)去,判斷是不可能為空的,按行累加。
- 只看這上面這兩個(gè)用法,count(1) 執(zhí)行得要比 count(主鍵 id) 快。因?yàn)閺囊娣祷?id 會(huì)涉及到解析數(shù)據(jù)行,以及拷貝字段值的操作
- count(字段)
- 如果這個(gè)“字段”是定義為 not null 的話,一行行地從記錄里面讀出這個(gè)字段,判斷不能為 null,按行累加;
- 如果這個(gè)“字段”定義允許為 null,那么執(zhí)行的時(shí)候,判斷到有可能是 null,還要把值取出來再判斷一下,不是 null 才累加。
- count()
- count()是例外,并不會(huì)把全部字段取出來,而是專門做了優(yōu)化,不取值。
- count()肯定不是 null,按行累加。按照效率排序的話,count() = count(1) > count(主鍵 id) > count(字段),所以建議盡量使用 count(*)。
到此這篇關(guān)于Mysql的慢SQL優(yōu)化思路和規(guī)范詳解的文章就介紹到這了,更多相關(guān)慢SQL優(yōu)化思路和規(guī)范內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
mysql5.x升級(jí)到mysql5.7后導(dǎo)入之前數(shù)據(jù)庫date出錯(cuò)的快速解決方法
這篇文章主要介紹了mysql5.x升級(jí)到mysql5.7后導(dǎo)入之前數(shù)據(jù)庫date出錯(cuò)的快速解決方法,非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2016-09-09MySQL中l(wèi)ength()、char_length()的區(qū)別
在MySQL中l(wèi)ength(str)、char_length(str)都屬于判斷長度的內(nèi)置函數(shù),本文主要介紹了MySQL中l(wèi)ength()、char_length()的區(qū)別,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05MySQL數(shù)據(jù)的讀寫分離之maxscale的使用方式
這篇文章主要介紹了MySQL數(shù)據(jù)的讀寫分離之maxscale的使用方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12insert into … on duplicate key update / replace into 多行數(shù)據(jù)介紹
當(dāng)我插入一條數(shù)據(jù)時(shí),我要判斷(k1,k2)是否已經(jīng)存在(1條selete),若存在就update,不存在就insert2013-08-08innodb_flush_method取值方法(實(shí)例講解)
下面小編就為大家?guī)硪黄猧nnodb_flush_method取值方法(實(shí)例講解)。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-03-03