淺聊一下數據庫的索引優化

背景

這里的索引說的是關系數據庫（MSSQL）中的索引。

本篇不是純技術性的內容，只是聊一次性能調優的經歷，包含到一些粗淺的實現和驗證手段，所以，大神忽略即可。

額…對了，筆者對數據庫的優化手段了解有限，文中若有原則性或者概念性錯誤的地方，歡迎大家指正。

提起索引，相信有部分同學跟我的感覺一樣，熟悉又陌生。熟悉是知道索引的概念，也知道它的利弊，但實際工作中并沒有太多利用過索引。

我個人比較喜歡所謂的CodeFirst的編碼方式，對關系數據庫的操作，大部分都是通過orm或者一些自動化腳本來完成，直接去操作數據庫的話，只有改造老項目，或者需要去庫里查詢一些數據的時候才會借助管理工具去直接操作數據庫。所以，對索引的實際操作經歷非常少。正巧，最近公司的一個常態化運行項目就遇到了檢索的性能瓶頸，而現階段又不能去修改業務代碼或者引入一些中間件來緩解，所以想到了是不是可以從索引入手，嘗試做一些性能優化。

結果，你猜怎么著，性能提升不多，也就10倍左右吧🚀

定位問題

發現性能瓶頸

首先，我發現這個問題的時候，在業務系統上做一次復雜的數據檢索最多竟然需要1-2秒左右才能返回，在遇到網絡不穩定的情況，返回時長還會更久，總之體驗非常不好。

數據庫性能查詢

由于賬號權限的問題，我沒辦法使用DTA（Database Engine Tuning Advisor）來具體的定位問題，所以使用了更直接的工具–執行計劃（Excution Plan）來輔助。

具體步驟如下

  1. 首先，在本地環境下，確定此接口生成的查詢語句；
  2. 將Sql直接放到數據庫中執行，并開啟執行計劃和實時查詢統計信息
  3. 獲取SSMS給出的優化建議。

按此操作執行后，我們能看到當前的查詢，究竟落到數據庫中的鏈條有多長，也就是這個查詢復雜度有多高，同時也能獲得一個優化建議。

這里，我們先不管復雜度的問題，因為當前情況下，修改業務代碼肯定是不可能的，所以目前只關注索引。

驗證問題

編寫測試接口

這里，我把這個常用的接口改造了一下，去掉了驗證，鑒權等環節，也把一些額外的屬性關掉了，確保每次請求都是直接打到服務端，且查詢數據庫

[AllowAnonymous]
//[ResponseCache(Duration = 10, VaryByQueryKeys = new string[] { "whereJsonStr", "adminId", "pageIndex", "pageSize", "rd" })]
public IActionResult GetApplyStatusListForTest(string whereJsonStr, int adminId, int pageIndex = 1, int pageSize = 10, int rd = 0)
{// 復雜查詢業務// ... 代碼省略
}

編寫測試腳本

這里還是使用Grafana的K6工具進行測試。

關于K6的內容，大家可以參見其官方文檔👉：https://grafana.com/docs/k6/latest/

筆者之前也寫過一篇類似的博客👉：https://juejin.cn/post/7442535460361109554

import http from 'k6/http';
import { check, sleep } from 'k6';export const options = {vus: 10,duration: '1m',thresholds: {checks: ['rate>0.95'],  // 至少 95% 的請求必須成功http_req_duration: ['p(95)<500']  // 95% 的請求響應時間小于 500 毫秒}
}const urls = [`https://localhost:5001/matchai/getApplyStatusListForTest?pageindex=${Math.floor(Math.random() * 10) + 1}&pagesize=10&whereJsonStr=...`,`https://localhost:5001/matchai/getApplyStatusListForTest?pageindex=${Math.floor(Math.random() * 10) + 1}&pagesize=10&whereJsonStr=...`,`https://localhost:5001/matchai/getApplyStatusListForTest?pageindex=${Math.floor(Math.random() * 10) + 1}&pagesize=10&whereJsonStr=...`,`https://localhost:5001/matchai/getApplyStatusListForTest?pageindex=${Math.floor(Math.random() * 10) + 1}&pagesize=10&whereJsonStr=...`
]
export default function () {const url = urls[Math.floor(Math.random() * urls.length)];console.log(`-------------------start----------------------`);console.log(`Request URL: ${url}`);console.log(`--------------------end----------------------`);let params = {headers: {'Content-Type': 'application/json'}};let res = http.get(url, params);// 解析響應體let response = JSON.parse(res.body);// 定義檢查點let checks = {'status code is 200': (r) => r.status === 200,'API code is 1': (r) => response.code === 1};// 執行檢查點check(res, checks);// 記錄非成功的響應if (response.code !== 1) {console.log(`Error: code=${response.code}, msg=${response.msg}, data=${JSON.stringify(response.data)}`);}console.log(`Response Time: ${res.timings.duration}ms`);const sleepTime = Math.floor(Math.random() * 51) + 50; // 隨機生成50到100毫秒sleep(sleepTime / 1000); // 將毫秒轉換為秒}

簡單說明下這段測試腳本的含義

  • 導入必要模塊，這里用到http，check，sleep
  • 配置壓測參數，模擬10個并發，持續1分鐘
  • 設定性能閾值，95%的請求必須成功，且響應時間小于500毫秒
  • 定義URL列表，隨機抽取，模擬多種條件下的請求行為
  • 主函數執行，即10個uv隨機發起請求，并輸出觀測日志
  • 定義響應檢查行為，時間，并記錄失敗響應
  • 模擬50-100毫秒的操作間隔

控制變量

首先，在沒有索引和有索引的前提下，跑一遍腳本

K6_WEB_DASHBOARD=true K6_WEB_DASHBOARD_EXPORT=html-report.html k6 run simulatescript1.js

通過上述命令，會得到一個比較完整的報表，這里我們只看Performance Overview部分，可以

  • 無索引

  • 有索引

對比可以看到，得到的測試性能提升了10倍，這期間，除了索引的建立，其余條件均一致。

由此，可以判定，根據SSMS執行計劃給出的建議，建立索引之后，性能的確可以得到明顯的改善。

這里的索引，就是根據SSMS執行計劃給出的建議，完成的，比如

CREATE NONCLUSTERED INDEX [IX_索引名稱] ON [dbo].[表名稱]
({待索引的字段}
)
INCLUDE({包含的字段}) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

需要說明的是，我這里真的就只關注了索引，實際上，執行計劃還會給出很多檢索執行過程的詳細信息，大家可以導出成xml或者直接在ssms窗口查看

其他可行手段

客戶端緩存

確定了增加索引，可以顯著提高檢索性能后，我還引入了客戶端緩存，設置一個合理的過期時間，這樣，再到正式站點測試后，得到的結果就是這樣了👇

雖然現在的結果也并不優秀，但相比之前秒級的響應，已經能明顯感覺到絲滑了許多。

索引維護

我們都知道，索引建立之后，是需要定期維護的，否則會產生過多碎片，造成性能下降。在SSMS中，可以使用Sql Agent來輔助完成。

筆者這里是建立了一個存儲過程，通過系統定期調用執行。

USE [數據庫]
GO
/****** Object:  StoredProcedure [dbo].[IndexMaintenanceProcedure]    Script Date: 2025-05-13 17:36:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER PROCEDURE [dbo].[IndexMaintenanceProcedure]
AS
BEGINSET NOCOUNT ON;DECLARE @schemaName NVARCHAR(128);DECLARE @tableName NVARCHAR(128);DECLARE @indexName NVARCHAR(128);DECLARE @avg_fragmentation_in_percent FLOAT;DECLARE @startTime DATETIME;DECLARE @endTime DATETIME;DECLARE @errorMessage NVARCHAR(MAX);DECLARE @actionTaken NVARCHAR(50);DECLARE @sql NVARCHAR(MAX);-- 定義游標，獲取所有碎片化超過5%的索引DECLARE curIndexFrag CURSOR FORSELECT s.name AS schema_name,t.name AS table_name,i.name AS index_name,ips.avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ipsINNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_idINNER JOIN sys.tables t ON i.object_id = t.object_idINNER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE i.name IS NOT NULL -- 排除堆（heap）AND t.is_ms_shipped = 0 -- 排除系統表AND ips.avg_fragmentation_in_percent > 5; -- 設置閾值OPEN curIndexFrag;FETCH NEXT FROM curIndexFrag INTO @schemaName, @tableName, @indexName, @avg_fragmentation_in_percent;WHILE @@FETCH_STATUS = 0BEGINSET @startTime = GETDATE();SET @errorMessage = NULL;BEGIN TRYIF @avg_fragmentation_in_percent >= 30BEGIN-- 碎片率高，重建索引SET @actionTaken = 'Rebuild';SET @sql = N'ALTER INDEX ' + QUOTENAME(@indexName) + N' ON ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' REBUILD;';ENDELSE IF @avg_fragmentation_in_percent >= 5 AND @avg_fragmentation_in_percent < 30BEGIN-- 中度碎片，重組索引SET @actionTaken = 'Reorganize';SET @sql = N'ALTER INDEX ' + QUOTENAME(@indexName) + N' ON ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N' REORGANIZE;';ENDPRINT '執行操作: ' + @sql;EXEC sp_executesql @sql;END TRYBEGIN CATCHSET @errorMessage = ERROR_MESSAGE();PRINT '錯誤發生: ' + @errorMessage;END CATCHSET @endTime = GETDATE();-- 記錄日志INSERT INTO dbo.IndexMaintenanceLog (SchemaName, TableName, IndexName, Fragmentation, ActionTaken, StartTime, EndTime, ErrorMessage)VALUES (@schemaName, @tableName, @indexName, @avg_fragmentation_in_percent, @actionTaken, @startTime, @endTime, @errorMessage);FETCH NEXT FROM curIndexFrag INTO @schemaName, @tableName, @indexName, @avg_fragmentation_in_percent;ENDSELECT count(1) as cnt FROM dbo.IndexMaintenanceLog WHERE EndTime >= @startTime;CLOSE curIndexFrag;DEALLOCATE curIndexFrag;
END

至此，本次優化工作基本完成。

一點注意

對索引熟悉的同學，應該都知道，索引有很多類型，包括聚集索引，非聚集索引，唯一索引等等。索引的主要作用，還是提升讀性能，尤其在OLAP的場景，而不是和OLTP場景。

索引的建立也不是越多越好，尤其是聚集索引，是會明顯影響寫入性能的。本篇提到的優化過程，建立的都是非聚集索引，對寫性能的影響有限。

其他的，我就不多說了，大家一搜就能得到很多標準答案，我這里再推薦一本書《數據庫系統內幕》。筆者也是在有了這次優化經歷之后，發現自己在很多基礎性的知識還是有欠缺，分享給大伙共勉。

最后，說起性能調優，本篇聊到的內容只是冰山一角，但我們也要注意不要陷入過度優化的陷阱，所以還是看開發人員的綜合能力，開發習慣和開發經驗，最終找到一條適合自己項目的系統優化之路。總之呢，系統做好了，過度調整肯定不對，完全或者幾乎不調整更不對，這也是對團隊能力和軟件質量的考驗。當然了，如果你做的都是那種小項目，就幾千上萬條數據量，甚至是那種單機應用，一次性應用，那確實不太需要關注這方面，把功能做好，別出低級錯誤就可以了。

好了，這篇就聊到這里。

*附

最后附上k6測試的完整報表

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/web/79696.shtml
繁體地址，請注明出處：http://hk.pswp.cn/web/79696.shtml
英文地址，請注明出處：http://en.pswp.cn/web/79696.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

【android bluetooth 框架分析 02】【Module詳解 7】【VendorSpecificEventManager 模塊介紹】

【android bluetooth 框架分析 02】【Module詳解 7】【VendorSpecificEventManager 模塊介紹】

1. 背景 我們在 gd_shim_module 介紹章節中&#xff0c;看到 我們將 VendorSpecificEventManager 模塊加入到了 modules 中。 // system/main/shim/stack.cc modules.add<hci::VendorSpecificEventManager>();在 ModuleRegistry::Start 函數中我們對 加入的所有 module…
閱讀更多...
小剛說C語言刷題—1080質因子

小剛說C語言刷題—1080質因子

1.題目描述 任意輸入一正整數 N &#xff0c;求出它的所有質因子。如&#xff1a;10&#xff1d;25&#xff0c;20&#xff1d;225。 輸入 輸入只有一行&#xff0c;包括 11個整數 n (1≤n≤32768) 輸出 輸出若干行&#xff0c;按從小到大的順序給出這個數的所有質因子&am…
閱讀更多...
C語言中的宏

C語言中的宏

1.防止頭文件重復包含 1.#pragma once #pragma once 是一個編譯器指令&#xff0c;用于防止頭文件被重復包含。它的核心作用是通過簡單語法替代傳統的頭文件保護宏&#xff08;#ifndef/#define/#endif&#xff09;&#xff0c;提升代碼簡潔性和可維護性。 作用詳解 防止重復…
閱讀更多...
MapReduce 模型

MapReduce 模型

?引言? MapReduce 是分布式計算領域的里程碑式模型&#xff0c;由 Google 在 2004 年論文中首次提出&#xff0c;旨在簡化海量數據處理的復雜性。其核心思想是通過函數式編程的 ?Map? &#xff08;映射&#xff09;和 ?Reduce? &#xff08;歸約&#xff09;階段&#x…
閱讀更多...
Linux文件編程——標準庫函數fopen、fread、fwrite等函數

Linux文件編程——標準庫函數fopen、fread、fwrite等函數

1. fopen — 打開文件 函數原型&#xff1a; FILE *fopen(const char *filename, const char *mode);參數&#xff1a; filename&#xff1a;要打開的文件名&#xff0c;可以是相對路徑或絕對路徑。 mode&#xff1a;文件打開模式&#xff0c;表示文件的操作方式&#xff08…
閱讀更多...
從 Git 到 GitHub - 使用 Git 進行版本控制 - Git 常用命令

從 Git 到 GitHub - 使用 Git 進行版本控制 - Git 常用命令

希望本貼能從零開始帶您一起學習如何使用 Git 進行版本控制&#xff0c;并結合遠程倉庫 GitHub。這會是一個循序漸進的指南&#xff0c;我們開始吧&#xff01; 學習 Git 和 GitHub 的路線圖&#xff1a; 理解核心概念&#xff1a;什么是版本控制&#xff1f;Git 是什么&…
閱讀更多...
2025.05.11拼多多機考真題算法崗-第四題

2025.05.11拼多多機考真題算法崗-第四題

?? 點擊直達筆試專欄 ??《大廠筆試突圍》 ?? 春秋招筆試突圍在線OJ ?? 筆試突圍OJ 04. 記憶碎片重組 問題描述 盧小姐正在開發一款名為"記憶碎片"的游戲，玩家需要分析混亂的記憶數據，推測出形成這些記憶的原始序列。游戲中，記憶數據存儲在一個特殊的數…
閱讀更多...
Android Exoplayer多路不同時長音視頻混合播放

Android Exoplayer多路不同時長音視頻混合播放

在上一篇Android Exoplayer 實現多個音視頻文件混合播放以及音軌切換中我們提到一個問題&#xff0c;如果視頻和音頻時長不一致&#xff0c;特別是想混合多個音頻和多個視頻時就會出問題&#xff0c;無法播放。報錯如下&#xff1a; E/ExoPlayerImplInternal(11191): Playback…
閱讀更多...
Datawhale PyPOTS時間序列5月第1次筆記

Datawhale PyPOTS時間序列5月第1次筆記

課程原地址&#xff1a; https://github.com/WenjieDu/PyPOTS&#xff08;Package地址&#xff09; https://github.com/WenjieDu/BrewPOTS/tree/datawhale/202505_datawhale&#xff08;Tutorial地址&#xff09; 2.1 PyPOTS簡介 PyPOTS 是一個專為處理部分觀測時間序列&a…
閱讀更多...
網安學途—流量分析 attack.pcap

網安學途—流量分析 attack.pcap

attack.pacp 使用Wireshark查看并分析虛擬機windows 7桌面下的attack.pcapng數據包文件&#xff0c;通過分析數據包attack.pcapng找出黑客的IP地址&#xff0c;并將黑客的IP地址作為FLAG &#xff08;形式&#xff1a;[IP地址]&#xff09;提交&#xff1a; 過濾器篩選&#x…
閱讀更多...
【大模型】DeepResearcher：通用智能體通過強化學習探索優化

【大模型】DeepResearcher：通用智能體通過強化學習探索優化

DeepResearcher&#xff1a;通過強化學習在真實環境中擴展深度研究 一、引言二、技術原理&#xff08;一&#xff09;強化學習與深度研究代理&#xff08;二&#xff09;認知行為的出現&#xff08;三&#xff09;模型架構 三、實戰運行方式&#xff08;一&#xff09;環境搭建…
閱讀更多...
go語言實現IP歸屬地查詢

go語言實現IP歸屬地查詢

效果: 實現代碼main.go package mainimport ("encoding/json""fmt""io/ioutil""net/http""os" )type AreaData struct {Continent string json:"continent"Country string json:"country"ZipCode …
閱讀更多...
基于STM32、HAL庫的SGTL5000XNLA3R2音頻接口芯片驅動程序設計

基于STM32、HAL庫的SGTL5000XNLA3R2音頻接口芯片驅動程序設計

一、簡介： SGTL5000XNLA3R2 是 Cirrus Logic 推出的高性能、低功耗音頻編解碼器，專為便攜式和電池供電設備設計。它集成了立體聲 ADC、DAC、麥克風前置放大器、耳機放大器和數字信號處理功能，支持 I2S/PCM 音頻接口和 I2C 控制接口，非常適合與 STM32 微控制器配合使用。 二…
閱讀更多...
window 顯示驅動開發-報告圖形內存（一）

window 顯示驅動開發-報告圖形內存（一）

計算圖形內存 在 VidMm 能夠向客戶端報告準確的帳戶之前&#xff0c;它必須首先計算圖形內存的總量。 VidMm 使用以下內存類型和公式來計算圖形內存&#xff1a; 系統總內存 此值是操作系統可訪問的系統內存總量。 BIOS 分配的內存不會出現在此數字中。 例如&#xff0c;一臺…
閱讀更多...
[FA1C4] 博客鏈接

[FA1C4] 博客鏈接

Blog Link 博客已經從 CSDN 轉移 高情商&#xff1a;博客是給人看的 低情商&#xff1a;CSDN 已經爛了根本不能看 鏈接: https://fa1c4.github.io/
閱讀更多...
python通過curl訪問deepseek的API調用案例

python通過curl訪問deepseek的API調用案例

廢話少說&#xff0c;開干&#xff01; API申請和充值 下面是deepeek的API網站 https://platform.deepseek.com/ 進去先注冊&#xff0c;是不是手機賬號密碼都不重要&#xff0c;都一樣&#xff0c;完事充值打米&#xff0c;主要是打米后左側API Keys里面創建一個API Keys&am…
閱讀更多...
【計算機視覺】OpenCV項目實戰：基于face_recognition庫的實時人臉識別系統深度解析

【計算機視覺】OpenCV項目實戰：基于face_recognition庫的實時人臉識別系統深度解析

基于face_recognition庫的實時人臉識別系統深度解析 1. 項目概述2. 技術原理與算法設計2.1 人臉檢測模塊2.2 特征編碼2.3 相似度計算 3. 實戰部署指南3.1 環境配置3.2 數據準備3.3 實時識別流程 4. 常見問題與解決方案4.1 dlib安裝失敗4.2 人臉檢測性能差4.3 誤識別率高 5. 關鍵…
閱讀更多...
第6章: SEO與交互指標

第6章: SEO與交互指標

第6章: SEO與交互指標 在當今的SEO環境中&#xff0c;Google越來越重視用戶交互指標&#xff0c;如頁面停留時長、交互性能等。本章將深入探討如何優化網頁速度和用戶交互體驗&#xff0c;以提升SEO效果和用戶滿意度。 1. Google的新時代SEO指標 隨著互聯網技術的發展&#xff…
閱讀更多...
Starrocks的主鍵表涉及到的MOR Delete+Insert更新策略

Starrocks的主鍵表涉及到的MOR Delete+Insert更新策略

背景 寫這個文章的作用主要是做一些總結和梳理&#xff0c;特別是正對大數據場景下的實時寫入更新策略 COW 和 MOR 以及 DeleteInsert 的技術策略的演進&#xff0c; 這也適用于其他大數據的計算存儲系統。該文章主要參考了Primary Key table. 分析總結 Starrocks 的主鍵表主…
閱讀更多...
C 語言_常見排序算法全解析

C 語言_常見排序算法全解析

排序算法是計算機科學中的基礎內容，本文將介紹 C 語言中幾種常見的排序算法，包括實現代碼、時間復雜度分析、適用場景和詳細解析。 一、冒泡排序（Bubble Sort） 基本思想：重復遍歷數組，比較相鄰元素，將較大元素交換到右側。 代碼實現： void bubbleSort(int arr[], i…
閱讀更多...
最新文章

Copyright @ 2022~2023