1、索引優(yōu)化

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

對(duì)于MySQL來(lái)說(shuō)，如果表里已經(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è)前綴長(zhǎng)度。例如，如果有一個(gè)CHAR(255)的 列，如果在前10 個(gè)或20 個(gè)字符內(nèi)，多數(shù)值是惟一的，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

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

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

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

where條件里等號(hào)左右字段類型必須一致，否則無(wú)法利用索引

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

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

索引列不要使用函數(shù)或表達(dá)式，否則無(wú)法利用索引。如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)無(wú)法使用到索引

一般情況下不鼓勵(lì)使用like操作，如果非使用不可，如何使用也是一個(gè)問(wèn)題。like “%aaa%” 不會(huì)使用索引而like “aaa%”可以使用索引。

1.8 索引不會(huì)包含有NULL值的列

只要列中包含有NULL值都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有NULL值，那么這一列對(duì)于此復(fù)合索引就是無(wú)效的。所以我們?cè)跀?shù)據(jù)庫(kù)設(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來(lái)抑制隱式排序。 不過(guò)目前公司線上環(huán)境通用的還是MySQL 5.7

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

否則會(huì)產(chǎn)生間隙鎖，內(nèi)部擴(kuò)大鎖定范圍，導(dǎo)致系統(tǒng)性能下降，產(chǎn)生死鎖。

2.4 不建議使用子查詢，建議將子查詢SQL拆開結(jié)合程序多次查詢，或使用join來(lái)代替子查詢。

例：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子查詢無(wú)效，故生產(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)，可以通過(guò)改為不作為查詢條件的子查詢（不要在where后面使用子查詢），

//優(yōu)化前，出現(xiàn)了文件排序和臨時(shí)表問(wè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   #這部分代替原來(lái)的內(nèi)連接查詢出來(lá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ò)把非直接關(guān)聯(lián)表member 從join查詢 改為 不作為查詢條件的子查詢，來(lái)優(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這些查詢的語(yǔ)句，where條件過(guò)濾出來(lái)的結(jié)果集請(qǐng)保持在1000行以內(nèi)，否則SQL會(huì)很慢

2.8

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

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

2.9 線上環(huán)境

多表join不要超過(guò)5個(gè)表

2.10

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

2.11

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

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

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

2.13

對(duì)于超過(guò)100W行的大表進(jìn)行alter table，必須經(jīng)過(guò)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來(lái)代替。

2.15 用IN來(lái)替換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 大分頁(yè)

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

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

2.17 count

• count(主鍵 id)
  • InnoDB 引擎會(huì)遍歷整張表，把每一行的 id 值都取出來(lái)，返回給 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，還要把值取出來(lái)再判斷一下，不是 null 才累加。
• count()
  • count()是例外，并不會(huì)把全部字段取出來(lái)，而是專門做了優(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)文章希望大家以后多多支持腳本之家！

最新評(píng)論