收藏篇：SQL優(yōu)化的36個(gè)建議！干貨滿滿！
當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流
→『 技術(shù)文檔交流 』
一、優(yōu)化知識(shí)儲(chǔ)備1、mysql優(yōu)化原則：盡量避免全表掃描、合理使用索引、避免返回大量數(shù)據(jù)給客戶端、避免使用游標(biāo)、避免頻繁創(chuàng)建刪除臨時(shí)表。 聯(lián)表查詢時(shí)盡量用小表驅(qū)動(dòng)大表，小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集，小表放左邊。 2、學(xué)會(huì)查看SQL執(zhí)行計(jì)劃 explain select * from users; 速度排行： type: system > const > eq_ref > ref > range > index > ALL ---------------------------------------- ALL 全表掃描 index 掃描索引上全部數(shù)據(jù)，比ALL快一點(diǎn) 場(chǎng)景：count(*) range 范圍 user_id>200 ref 普通非唯一索引 user_name=100 eq_ref 唯一索引 const 唯一索引且條件是普通常量 user_id=100 system 系統(tǒng)表 3、SQL優(yōu)化目標(biāo)：優(yōu)化到range及以上級(jí)別，index也很慢和ALL基本一樣。 4、注意客戶端自帶的limit會(huì)影響你的判斷。（干貨啊！！！這個(gè)一般百度上是沒有的，百度不到！！但是卻容易被忽視！！！） 客戶端一般都有默認(rèn)帶上limit 200,比如dbeaver配置，可以關(guān)掉。 dbeaver 窗口-首選項(xiàng)-編輯器-數(shù)據(jù)編輯器 ，把數(shù)據(jù)集獲取大小改為0
所以為什么大家經(jīng)常測(cè)性能的時(shí)候，明明程序里面查詢很慢，在dbeaver里面卻很快，原因就是dbeaver可能偷偷給你加了limit 200。 這個(gè)在你測(cè)試性能的時(shí)候關(guān)掉，平時(shí)還是加上200的配置吧 5、用mysql自帶性能分析可以知道dbeaver默認(rèn)加了limit 200 set profiling =1; select * from users where user_name =9000; show profiles; 二、盡量不做的事情1.盡量不在where條件用!= 或 <> 2.盡量不用 is null 和 is not null 3.盡量不用or 4.盡量不用like，如果一定要用就用右模糊 user_name like'xx%' 可以用explain驗(yàn)證一下： 在字段已經(jīng)建立索引的情況下： 'xx%' 走的是range執(zhí)行計(jì)劃，前面說過range也是優(yōu)化目標(biāo)。 '%xx' 走ALL全表掃描 '%xx%' 走ALL全表掃描如果不是索引字段，那不管什么方式都是ALL全表掃描。 如果要用全模糊，那可以用全文索引解決like慢問題 某個(gè)段接like，那么這個(gè)字段是不走索引的，所以like就特別慢 ，要700ms
全文索引可以解決這個(gè)問題 -- 全文索引create fulltext index idx_users_remark on users(remark); 全文索引查詢方式：只要1ms select * from users where Match (remark) Against('備注_100111*' in boolean mode);
5.盡量不用 in not in 6.盡量不要在=左邊計(jì)算，或函數(shù)，如 where to_char(name)='xx' 7.不要用字符串作為主鍵 8.不要用select * select * 問題： 增加很多不必要的消耗，比如CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬； 增加了使用覆蓋索引的可能性； 增加了回表的可能性； 當(dāng)表結(jié)構(gòu)發(fā)生變化時(shí)，前端也需要更改； 查詢效率低； select * from users;--用時(shí) 2.1s
select user_id from users;--用時(shí) 453ms
9. 不要用group by having 來過濾，而是先在where 條件過濾后再group by. 10.盡量使得表連接不要超過5個(gè) 11.索引不是越多越好，會(huì)降低插入更新的速度，控制在5個(gè)以內(nèi) 12.盡量避免使用游標(biāo) 因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。 13.索引不適合建在有大量重復(fù)數(shù)據(jù)的字段上，比如性別，排序字段應(yīng)創(chuàng)建索引 14.盡量不要存儲(chǔ)圖片、文件等大數(shù)據(jù)。 15.單表數(shù)據(jù)最好不要超過500w，超過2000w速度明顯變慢。 16.in 內(nèi)數(shù)據(jù)盡量不要太多，如果是連續(xù)的就用between代替。 17.不要在varchar字段上用數(shù)字查詢，否則會(huì)導(dǎo)致索引失效。 比如 user_name =1234 要改成 user_name='1234' 18.復(fù)合索引(a,b,c)，不要單獨(dú)用b、c、或者bc進(jìn)行查詢。 復(fù)合索引最左原則：a 、ab、abc、ac 都是能用上索引的。 (a,b,c) 為復(fù)合索引 ，那么它滿足最左原則。什么意思？where a=xx 走索引，其他情況呢 a 走索引 a,b 走索引 , 順序可以變 ba 也走，mysql會(huì)優(yōu)化位置 a,c 走索引 a,b,c 走索引 b 不走索引 ，為什么這么說，它走的是index，和全表掃描ALL幾乎沒區(qū)別，慢的要死 c 不走索引 ， 為什么這么說，它走的是index，和全表掃描ALL幾乎沒區(qū)別，慢的要死 bc 不走索引 就說，必須從最左邊開始都要有。 19.主鍵是自帶唯一索引的，因此不需要再在主鍵上建索引。 三、盡量要做的事情1.復(fù)合索引應(yīng)該要第一個(gè)作為條件，否則不生效。 2.能用between 就不要用in 3.exists 代替in 4.數(shù)字型字段盡量用number別用varchar 5.查詢條件盡量使用上索引 6.varchar代替char varchar 可變，存多少占多少空間；char如果存的不夠會(huì)補(bǔ)空格。 7.left join 左邊放小表（數(shù)據(jù)量少的表） 8. 盡量使用limit 可以提高查詢速度，避免全表掃描。 9.批量插入提高性能。 10.查詢使用最頻繁的列放在聯(lián)合索引的最左側(cè)。 INSERT INTO users (user_id,user_name) VALUES(1,'aaa'),(2,'bbb'); 11.財(cái)務(wù)、銀行相關(guān)的金額字段必須使用decimal類型 非精準(zhǔn)浮點(diǎn)：float,double 精準(zhǔn)浮點(diǎn)：decimal Decimal類型為精準(zhǔn)浮點(diǎn)數(shù)，在計(jì)算時(shí)不會(huì)丟失精度； 占用空間由定義的寬度決定，每4個(gè)字節(jié)可以存儲(chǔ)9位數(shù)字，并且小數(shù)點(diǎn)要占用一個(gè)字節(jié)； 可用于存儲(chǔ)比bigint更大的整型數(shù)據(jù)； 12.建議把BLOB或是TEXT列分離到單獨(dú)的擴(kuò)展表中 Mysql內(nèi)存臨時(shí)表不支持TEXT、BLOB這樣的大數(shù)據(jù)類型，如果查詢中包含這樣的數(shù)據(jù)，在排序等操作時(shí)，就不能使用內(nèi)存臨時(shí)表，必須使用磁盤臨時(shí)表進(jìn)行。而且對(duì)于這種數(shù)據(jù)，Mysql還是要進(jìn)行二次查詢，會(huì)使sql性能變得很差，但是不是說一定不能使用這樣的數(shù)據(jù)類型。 如果一定要使用，建議把BLOB或是TEXT列分離到單獨(dú)的擴(kuò)展表中，查詢時(shí)一定不要使用select * 而只需要取出必要的列，不需要TEXT列的數(shù)據(jù)時(shí)不要對(duì)該列進(jìn)行查詢。 該文章在 2024/1/22 8:54:26 編輯過 |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
