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ū)分度最高的字段放在最前面。
有時(shí)候區(qū)分度高的字段可能不符合個(gè)人或者業(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;
這種情況我們只需要建了一個(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；

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

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

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)行索引。短索引不僅可以提高查詢(xún)速度而且可以節(jié)省磁盤(pán)空間和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)左右字段類(lèi)型必須一致悲立，否則無(wú)法利用索引

數(shù)據(jù)類(lèi)型不一致會(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í)間查詢(xún)原献，這樣情況下，就不要在列上進(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ún)阻斷牌柄，后續(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

范圍查詢(xún)還有“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)行排序守屉。如果查詢(xú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

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

上面的例子在MySQL 5.7及更低版本生效滨巴，GROUP BY在某些條件下隱式排序。 在MySQL 8.0中俺叭，不再發(fā)生這種情況兢卵，因此不再需要在末尾指定ORDER BY NULL來(lái)抑制隱式排序。
不過(guò)目前公司線(xiàn)上環(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)生死鎖窃款。
具體原理見(jiàn) mysql多線(xiàn)程update死鎖問(wèn)題

• 2.4 不建議使用子查詢(xún)，建議將子查詢(xún)SQL拆開(kāi)結(jié)合程序多次查詢(xún)牍氛，或使用join來(lái)代替子查詢(xún)晨继。
  例：SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name='hechunyang');

  子查詢(xún)?cè)贛ySQL5.5版本里，內(nèi)部執(zhí)行計(jì)劃器是這樣執(zhí)行的：先查外表再匹配內(nèi)表搬俊，而不是先查內(nèi)表t2紊扬，當(dāng)外表的數(shù)據(jù)很大時(shí)蜒茄，查詢(xún)速度會(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子查詢(xún)無(wú)效腹缩，故生產(chǎn)環(huán)境應(yīng)避免使用子查詢(xún)

2.5 Using temporary 優(yōu)化

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

//優(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)連接查詢(xún)出來(lái)的昵稱(chēng)
   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查詢(xún) 改為 不作為查詢(xún)條件的子查詢(xún)盘寡，來(lái)優(yōu)化 Using temporary

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

2.6 Using filesort

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

2.7 包含了order by、group by喊式、distinct這些查詢(xún)的語(yǔ)句孵户，where條件過(guò)濾出來(lái)的結(jié)果集請(qǐng)保持在1000行以?xún)?nèi)，否則SQL會(huì)很慢

2.8 SELECT語(yǔ)句不要使用UNION岔留，推薦使用UNION ALL夏哭，并且UNION子句個(gè)數(shù)限制在5個(gè)以?xún)?nèi)。

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

2.9 線(xiàn)上環(huán)境里逆，多表join不要超過(guò)5個(gè)表

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

2.11 程序端SELECT語(yǔ)句必須指定具體字段名稱(chēng)原押，禁止寫(xiě)成 *

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 事務(wù)操作 不要和 http盯漂、rpc調(diào)用寫(xiě)到一起

假如 微服務(wù)A 和 微服務(wù)B同時(shí)操作一張表中同樣數(shù)據(jù)颇玷， 然后呢，微服務(wù)A 把mysql 和 http調(diào)用 微服務(wù)B寫(xiě)在了一起就缆，
這就會(huì)導(dǎo)致一個(gè)嚴(yán)重的問(wèn)題帖渠，微服務(wù)A 在 update 鎖表后，在調(diào)用微服務(wù)B之后才會(huì)提交事務(wù)违崇，但是微服務(wù)B里同樣對(duì) 同一張表的數(shù)據(jù)有update阿弃，也加鎖诊霹，所以呢羞延，高并發(fā)情況下，會(huì)直接鎖表脾还。

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

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

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ì)于該表的所有寫(xiě)入籍胯，對(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

低效查詢(xún)
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
高效查詢(xún)
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 的話(huà)，一行行地從記錄里面讀出這個(gè)字段己儒，判斷不能為 null崎岂，按行累加；
  如果這個(gè)“字段”定義允許為 null闪湾，那么執(zhí)行的時(shí)候冲甘，判斷到有可能是 null，還要把值取出來(lái)再判斷一下途样，不是 null 才累加江醇。
• count()
  count()是例外，并不會(huì)把全部字段取出來(lái)何暇，而是專(zhuān)門(mén)做了優(yōu)化陶夜，不取值。count()肯定不是 null裆站，按行累加条辟。按照效率排序的話(huà)，count() = count(1) > count(主鍵 id) > count(字段)遏插，所以建議盡量使用 count(*)捂贿。

image.png