欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Mysql的慢SQL優(yōu)化思路和規(guī)范詳解

 更新時(shí)間:2023年05月16日 08:30:23   作者:出世&入世  
這篇文章主要介紹了Mysql的慢SQL優(yōu)化思路和規(guī)范詳解,官方介紹索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。更通俗的說,數(shù)據(jù)庫索引好比是一本書前面的目錄,能加快數(shù)據(jù)庫的查詢速度,需要的朋友可以參考下

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)文章

  • Linux下安裝配置MySQL

    Linux下安裝配置MySQL

    mysql最流行的關(guān)系型數(shù)據(jù)庫之一,目前隸屬于oracle公司,因體積小、速度快、總體擁有成本低,開放源代碼這一特點(diǎn),所以是我們?nèi)粘i_發(fā)的首選。下面我們來看看如何在Linux下安裝配置MySQL
    2017-05-05
  • 超詳細(xì)MySQL8.0.22安裝及配置教程

    超詳細(xì)MySQL8.0.22安裝及配置教程

    這篇文章主要介紹了超詳細(xì)MySQL8.0.22安裝及配置教程,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2020-12-12
  • MYSQL中Truncate的用法詳解

    MYSQL中Truncate的用法詳解

    這篇文章主要介紹了MYSQL中Truncate的用法詳解,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2021-01-01
  • MySql學(xué)習(xí)心得之存儲(chǔ)過程

    MySql學(xué)習(xí)心得之存儲(chǔ)過程

    之前總是在MSSQL上寫存儲(chǔ)過程,沒有在MYSQL上寫過,也基本沒有用過,今天需要用到MYSQL,研究了下,把項(xiàng)目的需要的存儲(chǔ)過程寫了一部分,寫一下工作總結(jié)。這里沒有給出數(shù)據(jù)庫結(jié)構(gòu),不討論SQL語句的細(xì)節(jié),主要探討存儲(chǔ)過程語法,適合有基礎(chǔ)的人。
    2014-06-06
  • mysql5.x升級(jí)到mysql5.7后導(dǎo)入之前數(shù)據(jù)庫date出錯(cuò)的快速解決方法

    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-09
  • 從零開始搭建MySQL MMM架構(gòu)

    從零開始搭建MySQL MMM架構(gòu)

    這篇文章主要介紹了從零開始搭建MySQL MMM架構(gòu),本文講解了配置MySQL Relication、新建同步數(shù)據(jù)庫需要的用戶、同步主從數(shù)據(jù)庫、安裝MMM、配置MMM、啟動(dòng)MMM等問題,需要的朋友可以參考下
    2015-04-04
  • MySQL中l(wèi)ength()、char_length()的區(qū)別

    MySQL中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-05
  • MySQL數(shù)據(jù)的讀寫分離之maxscale的使用方式

    MySQL數(shù)據(jù)的讀寫分離之maxscale的使用方式

    這篇文章主要介紹了MySQL數(shù)據(jù)的讀寫分離之maxscale的使用方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2023-12-12
  • insert into … on duplicate key update / replace into 多行數(shù)據(jù)介紹

    insert into … on duplicate key update / replace into 多行數(shù)據(jù)介紹

    當(dāng)我插入一條數(shù)據(jù)時(shí),我要判斷(k1,k2)是否已經(jīng)存在(1條selete),若存在就update,不存在就insert
    2013-08-08
  • innodb_flush_method取值方法(實(shí)例講解)

    innodb_flush_method取值方法(實(shí)例講解)

    下面小編就為大家?guī)硪黄猧nnodb_flush_method取值方法(實(shí)例講解)。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧
    2017-03-03

最新評(píng)論