[MySQL--SQL優化]

来源:https://www.cnblogs.com/liupengfei1123/archive/2023/06/01/17449009.html
-Advertisement-
Play Games

# [MySQL--SQL優化] # 1、insert優化(插入數據優化) - ## 建議使用批量插入 ```MYSQL # 批量插入避免頻繁連接斷開資料庫(一次連接插入多條數據) insert into 表名 values(數據1),(數據2),(數據3)...... ``` - ## 建議手動提 ...


[MySQL--SQL優化]

1、insert優化(插入數據優化)

  • 建議使用批量插入

    # 批量插入避免頻繁連接斷開資料庫(一次連接插入多條數據)
    	insert into 表名 values(數據1),(數據2),(數據3)......
    
  • 建議手動提交事務插入(避免頻繁開啟關閉事務)

    # 即開啟一次事務 執行多條sql
    
    start transaction;  # 開啟事務 
    # 執行多條sql
    insert into 表名 values(數據1),(數據2),(數據3);
    insert into 表名 values(數據4),(數據5),(數據6);
    insert into 表名 values(數據7),(數據8),(數據9);
    commit;  # 提交事務
    
    
  • 大批量插入數據(load指令)

    如果一次性需要插入大批量數據,使用insert語句插入性能較低,此時可以使用MySQL資料庫提供的load指令進行插入。

    操作如下:

    # 客戶端連接伺服器時,加上參數 --local-infile
    	mysql --local-infile -u 用戶名 -p 密碼
    	
    # 設置全局參數local-infile為1,開啟從本地載入文件導入數據的開關
    	1、查看是否開啟命令 
    		select @@local_infile;
    	2、開啟命令
    		set global local_infile = 1;
    		
    # 執行load指令將準備好的數據,載入到表格
    	load data local infile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
    	load data local infile '文件路徑' into table '表名' fields terminated by '什麼作為分割符' lines terminated by '每一行的結束符';
    	
    

    註意:主鍵順序插入的性能高於亂序插入!

2、主鍵優化

# 1)滿足業務需求的情況下、儘量降低主鍵的長度
	原因是我們二級索引的葉子節點存放的就是主鍵,如果主鍵過長且葉子節點過多會占用大量的磁碟空間!
	
# 2)插入數據時儘量選擇順序插入,選擇使用auto_increment主鍵自增
	如果不順序插入可能會存在頁分裂現象,如果按照順序插入則可避免
	
# 3)儘量不要使用uuid作為主鍵或者是其他自然主鍵,如身份證號!
	原因 uuid或者身份證號則無序且過長,影響效率!
	
# 4)業務操作時儘量避免對主鍵的修改。
	

3、order by優化

①. Using filesort:通過表的索引或全表掃描,讀取滿足條件的數據行,然後在排序緩衝區sort buffer中完成排序操作,所有不是通過索引直接返回排序結果的排序都叫file sort排序。

②. Using index:通過有序索引順序掃描直接返回有序數據,這種情況即為using index,不需要額外排序,操作效率高。

註意:儘量優化為Using index

# 1、根據排序欄位建立合適的索引,多欄位排序時,也遵循最左首碼法則
# 2、儘量使用覆蓋索引
# 3、多欄位排序,一個升序一個降序,此時需要註意聯合索引在創建時的規則(ASC/DESC)
# 4、如果不可避免的出現filesort,大數據量排序時,可以適當增大排序緩衝區大小sort_buffer_size(預設256k)。
	查看預設緩衝區大小命令
	show variables like 'sort_buffer_size';

4、group by優化

# 在分組操作時,可以通過索引來提高效率。
# 分組操作時,索引的使用也是滿足最左首碼法則的。

5、limit優化

  • 針對於大數據量的情況下 分頁查詢時越往後就會變得越耗時。所以我們需要通過優化limit來提升效率

    註意:官方建議通過覆蓋索引加子查詢來優化

    例如:
    
    未優化前:
    	select * from 表名 limit 9000000,10:
    	耗時 19s
    	
    	
    優化後:
    	# 可以直接使用覆蓋聚集索引,不需要回表查詢 提升效率
    	select id from 表名 order by id limit 9000000,10;
    	# 我們可以將查詢出來的id看作為一張新表
    	# 之後我們可以使用子查詢來獲取具體數據
    	select A.* from 表名1 as A,(select id from 表名 order by id limit 9000000,10:) as B where A.id == B.id;
    	# 括弧內可以看作為一張新表 然後進行連表查詢即可 大大提升效率
    	
    	耗時 12s
    	
    

6、count優化

  • MyISAM 引擎把一個表的總行數存在了磁碟上,因此執行count(*)的時候就會直接返回這個數,效率很高(僅僅在沒有where條件有效)

  • innoDB 引擎就麻煩了,它執行count(*)的時候,需要把數據一行一行地從引擎裡面讀出來。然後累計計數

優化思路:自己計數。

比如:我們可以自己維護一張表來專門存儲我們所需要記錄的數據,利用redis記憶體資料庫 加一條數據 就+1等

count的幾種用法

1.count(主鍵)

InnoDB引擎會遍歷整張表,把每一行的主鍵id值都取出來,返回給服務層。服務層拿到主鍵後,直接按行進行累加(主鍵不可能為nut)

2.count(欄位)

沒有 not nul約束:   Innodb引擎會遍歷整張表把每一行的欄位值都取出來,返回給服務層,服務層判斷是否為nu不為nu,計數累加。
有 not nu約束:   Innodb引擎會遍歷整張表把每一行的欄位值都取出來,返回給服務層,直接按行進行累加。

3.count(1)

Innodb引擎遍歷整張表,但不取值。服務層對於返回的每一行,放一個數字“1”進去,直接按行進行累加。

4.count(*)

Innodb引擎並不會把全部欄位取出來,而是專門做了優化,不取值,服務層直接按行進行累加。

按照效率排序的話, count欄位)< count(主鍵id< count(1) c count(·),所以儘量使用 count(*)

7、update優化

update student set no='2000100100' where id=1;

update student set no='2000100105' where name='韋一笑';

# Innode的行鎖是針對索引加的鎖,不是針對記錄加的鎖,並且該索引不能失效,否則會從行鎖升級為表鎖。
# 升級為表鎖就會降低性能!

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 1. ADO.NET的前世今生 ADO.NET的名稱起源於ADO(ActiveX Data Objects),是一個COM組件庫,用於在以往的Microsoft技術中訪問數據。之所以使用ADO.NET名稱,是因為Microsoft希望表明,這是在NET編程環境中優先使用的數據訪問介面。 ADO.NE ...
  • ### 前言 當我們編寫 C# 代碼時,經常需要處理大量的數據集合。在傳統的方式中,我們往往需要先將整個數據集合載入到記憶體中,然後再進行操作。但是如果數據集合非常大,這種方式就會導致記憶體占用過高,甚至可能導致程式崩潰。 C# 中的`yield return`機制可以幫助我們解決這個問題。通過使用`y ...
  • ## 引言 最近發現自己喜歡用的 Todo 軟體總是差點意思,畢竟每個人的習慣和工作流不太一樣,我就想著自己寫一個小的[Todo 項目]( https://github.com/circler3/TodoTrack ),核心的功能是自動記錄 Todo 執行過程中消耗的時間(尤其面向程式員),按照自己 ...
  • 目錄 一、函數 1.傳參 二、返回值 return 三、終止符 exit 四、實驗 一、函數 概念:函數是定義一個函數名,可以調用函數方法,完成便捷處理。 函數作用: 1.避免方法名重覆。 2.將代碼分割成一塊一塊,便於查看。 傳參:內部傳參 外部傳參 混合外部內部傳參混合外部內部傳參 例子演示: ...
  • Linux技術文檔 1.說明 研究思路:從Linux外部向內部研究,從外部工具到系統內核深入的研究思路。本文檔主要針對外部工具進行實驗。並賦予實驗結論。 2.系統內核初識 1.系統記憶體管理:管理物理記憶體與虛擬記憶體(內核通過硬碟上的存儲空間來時間虛擬記憶體,這塊區域稱為交換空間) 2.軟體程式管理:in ...
  • RTC簡介: RTC是一個獨立的定時器,它可以連續計數和提供了時鐘日曆功能。使用BKP寄存器存儲具有掉電保存功能 存粹的計時的, 觸發中斷 ——鬧鐘中斷,用來產生一個可編程的鬧鐘中斷。 ——秒中斷,用來產生一個可編程的周期性中斷信號(最快1秒,最慢1秒,只能1秒) ——溢出中斷,指示內部可編程計數器 ...
  • # 修改表 ## 修改表名 ```SQL -- 1.將名為 table1 的表修改為 table2 ALTER TABLE table1 RENAME table2; -- 示例 ALTER TABLE aggregate_test RENAME aggregate_test1; -- 2.將表 e ...
  • 雖然之前收集過網名資料庫,比如:《4萬個性網名大全網路名稱大全ACCESS資料庫》、《8萬多個網名大全QQ網名ACCESS資料庫》,但是都包含有~!#@等特殊符號,而今天這份則是沒有特殊符號的,並且記錄數達到了71萬且網名沒有重覆。 分類統計:搞笑網名(20577)、男生網名(66783)、女生網名 ...
一周排行
    -Advertisement-
    Play Games
  • .Net8.0 Blazor Hybird 桌面端 (WPF/Winform) 實測可以完整運行在 win7sp1/win10/win11. 如果用其他工具打包,還可以運行在mac/linux下, 傳送門BlazorHybrid 發佈為無依賴包方式 安裝 WebView2Runtime 1.57 M ...
  • 目錄前言PostgreSql安裝測試額外Nuget安裝Person.cs模擬運行Navicate連postgresql解決方案Garnet為什麼要選擇Garnet而不是RedisRedis不再開源Windows版的Redis是由微軟維護的Windows Redis版本老舊,後續可能不再更新Garne ...
  • C#TMS系統代碼-聯表報表學習 領導被裁了之後很快就有人上任了,幾乎是無縫銜接,很難讓我不想到這早就決定好了。我的職責沒有任何變化。感受下來這個系統封裝程度很高,我只要會調用方法就行。這個系統交付之後不會有太多問題,更多應該是做小需求,有大的開發任務應該也是第二期的事,嗯?怎麼感覺我變成運維了?而 ...
  • 我在隨筆《EAV模型(實體-屬性-值)的設計和低代碼的處理方案(1)》中介紹了一些基本的EAV模型設計知識和基於Winform場景下低代碼(或者說無代碼)的一些實現思路,在本篇隨筆中,我們來分析一下這種針對通用業務,且只需定義就能構建業務模塊存儲和界面的解決方案,其中的數據查詢處理的操作。 ...
  • 對某個遠程伺服器啟用和設置NTP服務(Windows系統) 打開註冊表 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W32Time\TimeProviders\NtpServer 將 Enabled 的值設置為 1,這將啟用NTP伺服器功 ...
  • title: Django信號與擴展:深入理解與實踐 date: 2024/5/15 22:40:52 updated: 2024/5/15 22:40:52 categories: 後端開發 tags: Django 信號 松耦合 觀察者 擴展 安全 性能 第一部分:Django信號基礎 Djan ...
  • 使用xadmin2遇到的問題&解決 環境配置: 使用的模塊版本: 關聯的包 Django 3.2.15 mysqlclient 2.2.4 xadmin 2.0.1 django-crispy-forms >= 1.6.0 django-import-export >= 0.5.1 django-r ...
  • 今天我打算整點兒不一樣的內容,通過之前學習的TransformerMap和LazyMap鏈,想搞點不一樣的,所以我關註了另外一條鏈DefaultedMap鏈,主要調用鏈為: 調用鏈詳細描述: ObjectInputStream.readObject() DefaultedMap.readObject ...
  • 後端應用級開發者該如何擁抱 AI GC?就是在這樣的一個大的浪潮下,我們的傳統的應用級開發者。我們該如何選擇職業或者是如何去快速轉型,跟上這樣的一個行業的一個浪潮? 0 AI金字塔模型 越往上它的整個難度就是職業機會也好,或者說是整個的這個運作也好,它的難度會越大,然後越往下機會就會越多,所以這是一 ...
  • @Autowired是Spring框架提供的註解,@Resource是Java EE 5規範提供的註解。 @Autowired預設按照類型自動裝配,而@Resource預設按照名稱自動裝配。 @Autowired支持@Qualifier註解來指定裝配哪一個具有相同類型的bean,而@Resourc... ...