mysql的資料庫索引優化（資料庫索引調優）

本文目錄一覽：

• 1、超詳細MySQL資料庫優化
• 2、第142節課.mysql優化之什麼是索引
• 3、mysql怎麼索引優化
• 4、mysql–索引優化
• 5、北大青鳥java培訓：mysql資料庫的優化方法？
• 6、MySQL資料庫優化（七）：MySQL如何使用索引

超詳細MySQL資料庫優化

資料庫優化一方面是找出系統的瓶頸,提高MySQL資料庫的整體性能,而另一方面需要合理的結構設計和參數調整,以提高用戶的相應速度,同時還要儘可能的節約系統資源,以便讓系統提供更大的負荷.

1. 優化一覽圖

2. 優化

筆者將優化分為了兩大類,軟優化和硬優化,軟優化一般是操作資料庫即可,而硬優化則是操作伺服器硬體及參數設置.

2.1 軟優化

2.1.1 查詢語句優化

1.首先我們可以用EXPLAIN或DESCRIBE(簡寫:DESC)命令分析一條查詢語句的執行信息.

2.例:

顯示:

其中會顯示索引和查詢數據讀取數據條數等信息.

2.1.2 優化子查詢

在MySQL中,盡量使用JOIN來代替子查詢.因為子查詢需要嵌套查詢,嵌套查詢時會建立一張臨時表,臨時表的建立和刪除都會有較大的系統開銷,而連接查詢不會創建臨時表,因此效率比嵌套子查詢高.

2.1.3 使用索引

索引是提高資料庫查詢速度最重要的方法之一,關於索引可以參高筆者MySQL資料庫索引一文,介紹比較詳細,此處記錄使用索引的三大注意事項:

2.1.4 分解表

對於欄位較多的表,如果某些欄位使用頻率較低,此時應當,將其分離出來從而形成新的表,

2.1.5 中間表

對於將大量連接查詢的表可以創建中間表,從而減少在查詢時造成的連接耗時.

2.1.6 增加冗餘欄位

類似於創建中間表,增加冗餘也是為了減少連接查詢.

2.1.7 分析表,,檢查表,優化表

分析表主要是分析表中關鍵字的分布,檢查表主要是檢查表中是否存在錯誤,優化表主要是消除刪除或更新造成的表空間浪費.

1. 分析表: 使用 ANALYZE 關鍵字,如ANALYZE TABLE user;

2. 檢查表: 使用 CHECK關鍵字,如CHECK TABLE user [option]

option 只對MyISAM有效,共五個參數值:

3. 優化表:使用OPTIMIZE關鍵字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不寫入日誌.,優化表只對VARCHAR,BLOB和TEXT有效,通過OPTIMIZE TABLE語句可以消除文件碎片,在執行過程中會加上只讀鎖.

2.2 硬優化

2.2.1 硬體三件套

1.配置多核心和頻率高的cpu,多核心可以執行多個線程.

2.配置大內存,提高內存,即可提高緩存區容量,因此能減少磁碟I/O時間,從而提高響應速度.

3.配置高速磁碟或合理分布磁碟:高速磁碟提高I/O,分布磁碟能提高並行操作的能力.

2.2.2 優化資料庫參數

優化資料庫參數可以提高資源利用率,從而提高MySQL伺服器性能.MySQL服務的配置參數都在my.cnf或my.ini,下面列出性能影響較大的幾個參數.

2.2.3 分庫分表

因為資料庫壓力過大，首先一個問題就是高峰期系統性能可能會降低，因為資料庫負載過高對性能會有影響。另外一個，壓力過大把你的資料庫給搞掛了怎麼辦？所以此時你必須得對系統做分庫分表 + 讀寫分離，也就是把一個庫拆分為多個庫，部署在多個資料庫服務上，這時作為主庫承載寫入請求。然後每個主庫都掛載至少一個從庫，由從庫來承載讀請求。

2.2.4 緩存集群

如果用戶量越來越大，此時你可以不停的加機器，比如說系統層面不停加機器，就可以承載更高的並發請求。然後資料庫層面如果寫入並發越來越高，就擴容加資料庫伺服器，通過分庫分表是可以支持擴容機器的，如果資料庫層面的讀並發越來越高，就擴容加更多的從庫。但是這裡有一個很大的問題：資料庫其實本身不是用來承載高並發請求的，所以通常來說，資料庫單機每秒承載的並發就在幾千的數量級，而且資料庫使用的機器都是比較高配置，比較昂貴的機器，成本很高。如果你就是簡單的不停的加機器，其實是不對的。所以在高並發架構里通常都有緩存這個環節，緩存系統的設計就是為了承載高並發而生。所以單機承載的並發量都在每秒幾萬，甚至每秒數十萬，對高並發的承載能力比資料庫系統要高出一到兩個數量級。所以你完全可以根據系統的業務特性，對那種寫少讀多的請求，引入緩存集群。具體來說，就是在寫資料庫的時候同時寫一份數據到緩存集群里，然後用緩存集群來承載大部分的讀請求。這樣的話，通過緩存集群，就可以用更少的機器資源承載更高的並發。

一個完整而複雜的高並發系統架構中，一定會包含：各種複雜的自研基礎架構系統。各種精妙的架構設計.因此一篇小文頂多具有拋磚引玉的效果,但是資料庫優化的思想差不多就這些了.

第142節課.mysql優化之什麼是索引

從 MySQL 5.7 開始，開發人員改變了 InnoDB 構建二級索引的方式，採用自下而上的方法，而不是早期版本中自上而下的方法了。在這篇文章中，我們將通過一個示例來說明如何構建 InnoDB 索引。最後，我將解釋如何通過為 innodb_fill_factor 設置更合適的值。

索引構建過程

在有數據的表上構建索引，InnoDB 中有以下幾個階段：1.讀取階段（從聚簇索引讀取並構建二級索引條目）2.合併排序階段3.插入階段（將排序記錄插入二級索引）在 5.6 版本之前，MySQL 通過一次插入一條記錄來構建二級索引。這是一種「自上而下」的方法。搜索插入位置從樹的根部（頂部）開始並達到葉頁（底部）。該記錄插入游標指向的葉頁上。在查找插入位置和進行業面拆分和合併方面開銷很大。從MySQL 5.7開始，添加索引期間的插入階段使用「排序索引構建」，也稱為「批量索引載入」。在這種方法中，索引是「自下而上」構建的。即葉頁（底部）首先構建，然後非葉級別直到根（頂部）。

示例

在這些情況下使用排序的索引構建：

ALTER TABLE t1 ADD INDEX（or CREATE INDEX）

ALTER TABLE t1 ADD FULLTEXT INDEX

ALTER TABLE t1 ADD COLUMN, ALGORITHM = INPLACE

OPIMIZE t1

對於最後兩個用例，ALTER 會創建一個中間表。中間表索引（主要和次要）使用「排序索引構建」構建。

演算法

在 0 級別創建頁，還要為此頁創建一個游標

使用 0 級別處的游標插入頁面，直到填滿

頁面填滿後，創建一個兄弟頁（不要插入到兄弟頁）

為當前的整頁創建節點指針（子頁中的最小鍵，子頁碼），並將節點指針插入上一級（父頁）

在較高級別，檢查游標是否已定位。如果沒有，請為該級別創建父頁和游標

在父頁插入節點指針

如果父頁已填滿，請重複步驟 3, 4, 5, 6

現在插入兄弟頁並使游標指向兄弟頁

在所有插入的末尾，每個級別的游標指向最右邊的頁。提交所有游標（意味著提交修改頁面的迷你事務，釋放所有鎖存器）

為簡單起見，上述演算法跳過了有關壓縮頁和 BLOB（外部存儲的 BLOB）處理的細節。

通過自下而上的方式構建索引

為簡單起見，假設子頁和非子頁中允許的 最大記錄數為 3

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c BLOB);

INSERT INTO t1 VALUES (1, 11, ‘hello111’);

INSERT INTO t1 VALUES (2, 22, ‘hello222’);

INSERT INTO t1 VALUES (3, 33, ‘hello333’);

INSERT INTO t1 VALUES (4, 44, ‘hello444’);

INSERT INTO t1 VALUES (5, 55, ‘hello555’);

INSERT INTO t1 VALUES (6, 66, ‘hello666’);

INSERT INTO t1 VALUES (7, 77, ‘hello777’);

INSERT INTO t1 VALUES (8, 88, ‘hello888’);

INSERT INTO t1 VALUES (9, 99, ‘hello999’);

INSERT INTO t1 VALUES (10, 1010, ‘hello101010’);

ALTER TABLE t1 ADD INDEX k1(b);

InnoDB 將主鍵欄位追加到二級索引。二級索引 k1 的記錄格式為(b, a)。在排序階段完成後，記錄為：

(11,1), (22,2), (33,3), (44,4), (55,5), (66,6), (77,7), (88,8), (99,9), (1010, 10)

初始插入階段

讓我們從記錄 (11,1) 開始。

在 0 級別（葉級別）創建頁

創建一個到頁的游標

所有插入都將轉到此頁面，直到它填滿了

箭頭顯示游標當前指向的位置。它目前位於第 5 頁，下一個插入將轉到此頁面。

還有兩個空閑插槽，因此插入記錄 (22,2) 和 (33,3) 非常簡單

對於下一條記錄 (44,4)，頁碼 5 已滿（前面提到的假設最大記錄數為 3）。這就是步驟。

頁填充時的索引構建

創建一個兄弟頁，頁碼 6

不要插入兄弟頁

在游標處提交頁面，即迷你事務提交，釋放鎖存器等

作為提交的一部分，創建節點指針並將其插入到 【當前級別 + 1】 的父頁面中（即在 1 級別）

節點指針的格式 (子頁面中的最小鍵,子頁碼) 。第 5 頁的最小鍵是 (11,1) 。在父級別插入記錄 ((11,1)，5)。

1 級別的父頁尚不存在，MySQL 創建頁碼 7 和指向頁碼 7 的游標。

將 ((11,1)，5) 插入第 7 頁

現在，返回到 0 級並創建從第 5 頁到第 6 頁的鏈接，反之亦然

0 級別的游標現在指向兄弟頁，頁碼為 6

將 (44,4) 插入第 6 頁

下一個插入 – (55,5) 和 (66,6) – 很簡單，它們轉到第 6 頁。

插入記錄 (77,7) 類似於 (44,4)，除了父頁面 （頁面編號 7） 已經存在並且它有兩個以上記錄的空間。首先將節點指針 ((44,4),8) 插入第 7 頁，然後將 (77,7) 記錄到同級 8 頁中。

插入記錄 (88,8) 和 (99,9) 很簡單，因為第 8 頁有兩個空閑插槽。

下一個插入 (1010,10) 。將節點指針 ((77,7),8) 插入 1級別的父頁（頁碼 7）。

MySQL 在 0 級創建同級頁碼 9。將記錄 (1010,10) 插入第 9 頁並將游標更改為此頁面。

以此類推。在上面的示例中，資料庫在 0 級別提交到第 9 頁，在 1 級別提交到第 7 頁。

我們現在有了一個完整的 B+-tree 索引，它是自下至上構建的！

索引填充因子

全局變數 innodb_fill_factor 用於設置插入 B-tree 頁中的空間量。默認值為 100，表示使用整個業面（不包括頁眉）。聚簇索引具有 innodb_fill_factor=100 的免除項。 在這種情況下，聚簇索引也空間的 1 /16 保持空閑。即 6.25% 的空間用於未來的 DML。

值 80 意味著 MySQL 使用了 80% 的頁空間填充，預留 20% 於未來的更新。如果 innodb_fill_factor=100 則沒有剩餘空間供未來插入二級索引。如果在添加索引後，期望表上有更多的 DML，則可能導致業面拆分並再次合併。在這種情況下，建議使用 80-90 之間的值。此變數還會影響使用 OPTIMIZE TABLE 和 ALTER TABLE DROP COLUMN, ALGOITHM=INPLACE 重新創建的索引。也不應該設置太低的值，例如低於 50。因為索引會佔用浪費更多的磁碟空間，值較低時，索引中的頁數較多，索引統計信息的採樣可能不是最佳的。優化器可以選擇具有次優統計信息的錯誤查詢計劃。

排序索引構建的優點

沒有頁面拆分（不包括壓縮表）和合併

沒有重複搜索插入位置

插入不會被重做記錄（頁分配除外），因此重做日誌子系統的壓力較小

缺點

ALTER 正在進行時，插入性能降低 Bug＃82940，但在後續版本中計劃修復。

請點擊輸入圖片描述

mysql怎麼索引優化

說一下不同引擎的優化，myisam讀的效果好，寫的效率差，這和它數據存儲格式，索引的指針和鎖的策略有關的，它的數據是順序存儲的（innodb數據存儲方式是聚簇索引），他的索引btree上的節點是一個指向數據物理位置的指針，所以查找起來很快，（innodb索引節點存的則是數據的主鍵，所以需要根據主鍵二次查找）；myisam鎖是表鎖，只有讀讀之間是並發的，寫寫之間和讀寫之間（讀和插入之間是可以並發的，去設置concurrent_insert參數，定期執行表優化操作，更新操作就沒有辦法了）是串列的，所以寫起來慢，並且默認的寫優先順序比讀優先順序高，高到寫操作來了後，可以馬上插入到讀操作前面去，如果批量寫，會導致讀請求餓死，所以要設置讀寫優先順序或設置多少寫操作後執行讀操作的策略;myisam不要使用查詢時間太長的sql，如果策略使用不當，也會導致寫餓死，所以盡量去拆分查詢效率低的sql,

mysql–索引優化

        索引覆蓋是指如果查詢的列恰好是索引的一部分,那麼查詢只需要在索引文件上進行,不需要回行到磁碟再找數據。這種查詢速度非常快,稱為」索引覆蓋」

        1查詢頻繁    2區分度高    3長度小    4盡量能覆蓋常用查詢欄位

        索引長度直接影響索引文件的大小,影響增刪改的速度,並間接影響查詢速度(佔用內存多)。因此對於一些長短不同的位元組，我們會針對列中的值,從左往右截取部分,來建索引。但是：

        1:截的越短, 重複度越高,區分度越小, 索引效果越不好

        2:截的越長, 重複度越低,區分度越高, 索引效果越好,但帶來的影響也越大–增刪改變慢,並間影響查詢速度.

        所以,我們要在  區分度 + 長度  兩者上,取得一個平衡（ distinct 去重 ）

        select count (distinct left (word,6)) / count (*) from tablename;

        對於一般的系統應用區別度能達到 0.1 ,索引的性能就可以接受.

        alter table tablename add index word(word(4));

        給字元串類型的欄位建立索引效率不高，但是必須要經常查這個欄位怎麼建索引？

        比如說一個欄位url，類型是字元串。那麼可以建一個欄位 crcurl 來存儲url欄位crc32後的值，並給 crcurl 建立索引。

        crc32：循環冗餘校驗。根據網上數據包或計算機文件等數據產生簡短固定位數校驗碼的一種散列函數，主要用來檢測或校驗數據傳輸或者保存後可能出現的錯誤。生成的數字在傳輸或者存儲之前計算出來並且附加到數據後面，然後接收方進行檢驗確定數據是否發生變化。一般來說，循環冗餘校驗的值都是32位的整數。

        crc32 是整形，在MySQL中，給整形欄位建立索引效率比較高，crc32雖然不能確保唯一性，但是無礙，相同的機率也是極小，關鍵是可以大大減少查詢的範圍，給crcurl這個欄位建立索引，查詢的時候帶上crcurl欄位就可以利用到索引。

            不允許翻過100頁（百度搜索一般到70頁左右） 

            首先我們直接大數據分頁limit 5000000,10  發現耗時4.41秒

            接下來我們轉換方式使用where條件查詢，只耗時0.02秒

            2次的查詢結果不一致，這是因為數據被物理刪除過有空洞.，因此我們可以追加軟刪除功能

            分析:優化思路是 不查,少查,查索引,少取.

            我們現在必須要查,則只查索引,不查數據,得到id.

            再用id去查具體條目.  這種技巧就是延遲索引.

        分析：limit是先查詢再越過，也就是說我們先查詢出所有數據再進行跳躍，上圖我們越過500W頁，還使用了inner join  內存並沒有崩掉，這是因為我們子句tmp臨時表中只查詢了id（索引覆蓋，不需要回行去磁碟找數據了）然後拿到這10個id 分別查詢這10條數據 。

        排序可能發生2種情況:

        1:對於覆蓋索引,直接在索引上查詢時,就是有順序的, using index

        2:先取出數據,形成臨時表做filesort(文件排序,但文件可能在磁碟上,也可能在內存中)

        我們的爭取目標：取出來的數據本身就是有序的! 利用索引來排序,那麼什麼時候發生索引排序呢？即查詢索引和order by的欄位是同一個欄位

        goods表中 cat_id與shop_price組成聯合索引：

            select goods_id,cat_id,shop_price from goods where cat_id=4 order by shop_price;    可以直接利用索引來排序,

             using where按照shop_price索引取出的結果,本身就是有序的

             select goods_id,cat_id,shop_price from goods order by click_count;

              using filesort用到了文件排序,即取出的結果再次排序

        重複索引是指 在同1個列(如age), 或者順序相同的幾個列(age,school), 建立了多個索引,稱為重複索引,重複索引沒有任何幫助,只會增大索引文件,拖慢更新速度。

        冗餘索引是指2個索引所覆蓋的列有重疊, 稱為冗餘索引。比如x,m,列，加索引 index x(x),  index xm(x,m) x,xm索引, 兩者的x列重疊了,  這種情況,稱為冗餘索引. （mx, xm 不是重複的,因為列的順序不一樣）

北大青鳥java培訓：mysql資料庫的優化方法？

我們都知道，伺服器資料庫的開發一般都是通過java或者是PHP語言來編程實現的，而為了提高我們資料庫的運行速度和效率，資料庫優化也成為了我們每日的工作重點，今天，河北IT培訓就一起來了解一下mysql伺服器資料庫的優化方法。

為什麼要了解索引真實案例案例一：大學有段時間學習爬蟲，爬取了知乎300w用戶答題數據，存儲到mysql數據中。

那時不了解索引，一條簡單的「根據用戶名搜索全部回答的sql「需要執行半分鐘左右，完全滿足不了正常的使用。

案例二：近線上應用的資料庫頻頻出現多條慢sql風險提示，而工作以來，對資料庫優化方面所知甚少。

例如一個用戶數據頁面需要執行很多次資料庫查詢，性能很慢，通過增加超時時間勉強可以訪問，但是性能上需要優化。

索引的優點合適的索引，可以大大減小mysql伺服器掃描的數據量，避免內存排序和臨時表，提高應用程序的查詢性能。

索引的類型mysql數據中有多種索引類型，primarykey，unique，normal，但底層存儲的數據結構都是BTREE;有些存儲引擎還提供hash索引，全文索引。

BTREE是常見的優化要面對的索引結構，都是基於BTREE的討論。

B-TREE查詢數據簡單暴力的方式是遍歷所有記錄;如果數據不重複，就可以通過組織成一顆排序二叉樹，通過二分查找演算法來查詢，大大提高查詢性能。

而BTREE是一種更強大的排序樹，支持多個分支，高度更低，數據的插入、刪除、更新更快。

現代資料庫的索引文件和文件系統的文件塊都被組織成BTREE。

btree的每個節點都包含有key，data和只想子節點指針。

btree有度的概念d=1。

假設btree的度為d，則每個內部節點可以有n=[d+1，2d+1)個key，n+1個子節點指針。

樹的大高度為h=Logb[(N+1)/2]。

索引和文件系統中，B-TREE的節點常設計成接近一個內存頁大小(也是磁碟扇區大小)，且樹的度非常大。

這樣磁碟I/O的次數，就等於樹的高度h。

假設b=100，一百萬個節點的樹，h將只有3層。

即，只有3次磁碟I/O就可以查找完畢，性能非常高。

索引查詢建立索引後，合適的查詢語句才能大發揮索引的優勢。

另外，由於查詢優化器可以解析客戶端的sql語句，會調整sql的查詢語句的條件順序去匹配合適的索引。

MySQL資料庫優化（七）：MySQL如何使用索引

索引用於快速找到特定一些值的記錄。如果沒有索引，MySQL就必須從第一行記錄開始讀取整個表來檢索記錄。表越大，資源消耗越大。如果在欄位上有索引的話，MySQL就能很快決定該從數據文件的哪個位置開始搜索記錄，而無須查找所有的數據。如果表中有1000條記錄的話，那麼這至少比順序地讀取數據快100倍。注意，如果需要存取幾乎全部1000條記錄的話，那麼順序讀取就更快了，因為這樣會使磁碟搜索最少。

大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B樹方式存儲。只有空間類型的欄位使用R樹存儲，MEMORY (HEAP)表支持哈希索引。

字元串默認都是自動壓縮前綴和後綴中的空格。

通常，如下所述幾種情況下可以使用索引。哈希索引(用於 MEMORY 表)的獨特之處在後面會討論到。

想要儘快找到匹配 WHERE 子句的記錄。

根據條件排除記錄。如果有多個索引可共選擇的話，MySQL通常選擇能找到最少記錄的那個索引。

做表連接查詢時從其他表中檢索記錄。

想要在指定的索引欄位 key_col 上找到它的 MIN() 或 MAX() 值。優化程序會在檢查索引的

key_col 欄位前就先檢查其他索引部分是否使用了 WHERE key_part_# = constant 子句。這樣的話，

MySQL會為 MIN() 或 MAX() 表達式分別單獨做一次索引查找，並且將它替換成常數。當所有的表達式都被替換成常數後，查詢就立刻返回。如下：

SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;

對錶作排序或分組，當在一個可用的最左前綴索引上做分組或排序時(如 ORDER

BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。

有些時候，查詢可以優化使得無需計算數據就能直接取得結果。當查詢使用表中的一個數字型欄位，且這個欄位是索引的最左部分，則可能從索引樹中能很快就取得結果：

SELECTkey_part3FROMtbl_nameWHEREkey_part1=1

假設有如下 SELECT 語句：

如果在 col1 和 col2 上有一個多欄位索引的話，就能直接取得對應的記錄了。