共計 10065 個字符，預計需要花費 26 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關 mysql 中索引配置如何優化，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

2  索引及查詢優化

索引的類型

普通索引：這是最基本的索引類型，沒唯一性之類的限制。

唯一性索引：和普通索引基本相同，但所有的索引列值保持唯一性。

主鍵：主鍵是一種唯一索引，但必須指定為 PRIMARY KEY。

全文索引：MYSQL 從 3.23.23 開始支持全文索引和全文檢索。在 MYSQL 中，全文索引的索引類型為 FULLTEXT。全文索引可以在 VARCHAR 或者 TEXT 類型的列上創建。

大多數 MySQL 索引 (PRIMARY KEY、UNIQUE、INDEX 和 FULLTEXT) 使用 B 樹中存儲。空間列類型的索引使用 R - 樹，MEMORY 表支持 hash 索引。

單列索引和多列索引（復合索引）

索引可以是單列索引，也可以是多列索引。對相關的列使用索引是提高 SELECT 操作性能的最佳途徑之一。

多列索引：

MySQL 可以為多個列創建索引。一個索引可以包括 15 個列。對于某些列類型，可以索引列的左前綴，列的順序非常重要。

多列索引可以視為包含通過連接索引列的值而創建的值的排序的數組。一般來說，即使是限制最嚴格的單列索引，它的限制能力也遠遠低于多列索引。

最左前綴

多列索引有一個特點，即最左前綴（Leftmost Prefixing）。假如有一個多列索引為 key(firstname lastname age)，當搜索條件是以下各種列的組合和順序時，MySQL 將使用該多列索引：

firstname，lastname，age

firstname，lastname

firstname

也就是說，相當于還建立了 key(firstname lastname)和 key(firstname)。

索引主要用于下面的操作：

快速找出匹配一個 WHERE 子句的行。

刪除行。當執行聯接時，從其它表檢索行。

對具體有索引的列 key_col 找出 MAX()或 MIN()值。由預處理器進行優化，檢查是否對索引中在 key_col 之前發生所有關鍵字元素使用了 WHERE key_part_# = constant。在這種情況下，MySQL 為每個 MIN()或 MAX()表達式執行一次關鍵字查找，并用常數替換它。如果所有表達式替換為常量，查詢立即返回。例如：

SELECT MIN(key2), MAX (key2) FROM tb WHERE key1=10;

  如果對一個可用關鍵字的最左面的前綴進行了排序或分組(例如，ORDER BY key_part_1,key_part_2)，排序或分組一個表。如果所有關鍵字元素后面有 DESC，關鍵字以倒序被讀取。

在一些情況中，可以對一個查詢進行優化以便不用查詢數據行即可以檢索值。如果查詢只使用來自某個表的數字型并且構成某些關鍵字的最左面前綴的列，為了更快，可以從索引樹檢索出值。

SELECT key_part3 FROM tb WHERE key_part1=1

有時 MySQL 不使用索引，即使有可用的索引。一種情形是當優化器估計到使用索引將需要 MySQL 訪問表中的大部分行時。(在這種情況下，表掃描可能會更快些）。然而，如果此類查詢使用 LIMIT 只搜索部分行，MySQL 則使用索引，因為它可以更快地找到幾行并在結果中返回。例如：

合理的建立索引的建議：

(1)  越小的數據類型通常更好：越小的數據類型通常在磁盤、內存和 CPU 緩存中都需要更少的空間，處理起來更快。 

(2)  簡單的數據類型更好：整型數據比起字符，處理開銷更小，因為字符串的比較更復雜。在 MySQL 中，應該用內置的日期和時間數據類型，而不是用字符串來存儲時間；以及用整型數據類型存儲 IP 地址。

(3)  盡量避免 NULL：應該指定列為 NOT NULL，除非你想存儲 NULL。在 MySQL 中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用 0、一個特殊的值或者一個空串代替空值

這部分是關于索引和寫 SQL 語句時應當注意的一些瑣碎建議和注意點。

1. 當結果集只有一行數據時使用 LIMIT 1

2. 避免 SELECT *，始終指定你需要的列

從表中讀取越多的數據，查詢會變得更慢。他增加了磁盤需要操作的時間，還是在數據庫服務器與 WEB 服務器是獨立分開的情況下。你將會經歷非常漫長的網絡延遲，僅僅是因為數據不必要的在服務器之間傳輸。

3. 使用連接（JOIN）來代替子查詢(Sub-Queries)

  連接（JOIN）.. 之所以更有效率一些，是因為 MySQL 不需要在內存中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。

4. 使用 ENUM、CHAR 而不是 VARCHAR，使用合理的字段屬性長度

5. 盡可能的使用 NOT NULL

6. 固定長度的表會更快

7. 拆分大的 DELETE 或 INSERT 語句

8. 查詢的列越小越快

 Where 條件

在查詢中，WHERE 條件也是一個比較重要的因素，盡量少并且是合理的 where 條件是很重要的，盡量在多個條件的時候，把會提取盡量少數據量的條件放在前面，減少后一個 where 條件的查詢時間。

有些 where 條件會導致索引無效：

where 子句的查詢條件里有！=，MySQL 將無法使用索引。

where 子句使用了 Mysql 函數的時候，索引將無效，比如：select * from tb where left(name, 4) = xxx

使用 LIKE 進行搜索匹配的時候，這樣索引是有效的：select * from tbl1 where name like xxx%，而 like %xxx% 時索引無效

三、  配置優化

安裝 MySQL 后，配置文件 my.cnf 在 /MySQL 安裝目錄 /share/mysql 目錄中，該目錄中還包含多個配置文件可供參考，有 my-large.cnf，my-huge.cnf， my-medium.cnf，my-small.cnf，分別對應大中小型數據庫應用的配置。win 環境下即存在于 MySQL 安裝目錄中的.ini 文件。

下面列出了對性能優化影響較大的主要變量，主要分為連接請求的變量和緩沖區變量。

1.  連接請求的變量：

1)  max_connections

MySQL 的最大連接數，增加該值增加 mysqld 要求的文件描述符的數量。如果服務器的并發連接請求量比較大，建議調高此值，以增加并行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多，介于 MySQL 會為每個連接提供連接緩沖區，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。

數值過小會經常出現 ERROR 1040: Too many connections 錯誤，可以過 conn% 通配符查看當前狀態的連接數量，以定奪該值的大小。

show variables like max_connections 最大連接數

show  status like max_used_connections 響應的連接數

如下：

mysql show variables like lsquo;max_connections lsquo;;

+———————–+——-+

| Variable_name | Value |

+———————–+——-+

| max_connections | 256 |

+———————–+——-+

mysql show status like lsquo;max%connections lsquo;;

+———————–+——-+

| Variable_name   | Value |

+—————————-+——-+

| max_used_connections | 256|

+—————————-+——-+

max_used_connections / max_connections * 100%（理想值 asymp; 85%） 

如果 max_used_connections 跟 max_connections 相同 那么就是 max_connections 設置過低或者超過服務器負載上限了，低于 10% 則設置過大。

2)  back_log

MySQL 能暫存的連接數量。當主要 MySQL 線程在一個很短時間內得到非常多的連接請求，這就起作用。如果 MySQL 的連接數據達到 max_connections 時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即 back_log，如果等待連接的數量超過 back_log，將不被授予連接資源。

back_log 值指出在 MySQL 暫時停止回答新請求之前的短時間內有多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多連接，你需要增加它，換句話說，這值對到來的 TCP/IP 連接的偵聽隊列的大小。

當觀察你主機進程列表（mysql show full processlist），發現大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進程時，就要加大 back_log 的值了。

默認數值是 50，可調優為 128，對于系統設置范圍為小于 512 的整數。 

3)  interactive_timeout

一個交互連接在被服務器在關閉前等待行動的秒數。一個交互的客戶被定義為對 mysql_real_connect()使用 CLIENT_INTERACTIVE 選項的客戶。 

默認數值是 28800，可調優為 7200。 

2.  緩沖區變量

全局緩沖：

4)  key_buffer_size

key_buffer_size 指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態值 Key_read_requests 和 Key_reads，可以知道 key_buffer_size 設置是否合理。比例 key_reads / key_read_requests 應該盡可能的低，至少是 1:100，1:1000 更好（上述狀態值可以使用 SHOW STATUS LIKE lsquo;key_read% rsquo; 獲得）。

key_buffer_size 只對 MyISAM 表起作用。即使你不使用 MyISAM 表，但是內部的臨時磁盤表是 MyISAM 表，也要使用該值。可以使用檢查狀態值 created_tmp_disk_tables 得知詳情。

舉例如下：

mysql show variables like lsquo;key_buffer_size lsquo;;

+——————-+————+

| Variable_name | Value  |

+———————+————+

| key_buffer_size | 536870912 |

+———— ———-+————+

key_buffer_size 為 512MB，我們再看一下 key_buffer_size 的使用情況：

mysql show global status like lsquo;key_read% lsquo;;

+————————+————-+

| Variable_name   | Value   |

+————————+————-+

| Key_read_requests| 27813678764 |

| Key_reads |  6798830    |

+————————+————-+

一共有 27813678764 個索引讀取請求，有 6798830 個請求在內存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率：

key_cache_miss_rate =Key_reads / Key_read_requests * 100%，設置在 1 /1000 左右較好

默認配置數值是 8388600(8M)，主機有 4GB 內存，可以調優值為 268435456(256MB)。

5)  query_cache_size

使用查詢緩沖，MySQL 將查詢結果存放在緩沖區中，今后對于同樣的 SELECT 語句（區分大小寫），將直接從緩沖區中讀取結果。

通過檢查狀態值 Qcache_*，可以知道 query_cache_size 設置是否合理（上述狀態值可以使用 SHOW STATUS LIKE lsquo;Qcache% rsquo; 獲得）。如果 Qcache_lowmem_prunes 的值非常大，則表明經常出現緩沖不夠的情況，如果 Qcache_hits 的值也非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小；如果 Qcache_hits 的值不大，則表明你的查詢重復率很低，這種情況下使用查詢緩沖反而會影響效率，那么可以考慮不用查詢緩沖。此外，在 SELECT 語句中加入 SQL_NO_CACHE 可以明確表示不使用查詢緩沖。

與查詢緩沖有關的參數還有 query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type 指定是否使用查詢緩沖，可以設置為 0、1、2，該變量是 SESSION 級的變量。

query_cache_limit 指定單個查詢能夠使用的緩沖區大小，缺省為 1M。

query_cache_min_res_unit 是在 4.1 版本以后引入的，它指定分配緩沖區空間的最小單位，缺省為 4K。檢查狀態值 Qcache_free_blocks，如果該值非常大，則表明緩沖區中碎片很多，這就表明查詢結果都比較小，此時需要減小 query_cache_min_res_unit。

舉例如下：

mysql show global status like lsquo;qcache% lsquo;;

+——————————-+—————–+

| Variable_name  | Value   |

+——————————-+—————–+

| Qcache_free_blocks   | 22756   |

| Qcache_free_memory   | 76764704  |

| Qcache_hits   | 213028692 |

| Qcache_inserts   | 208894227  |

| Qcache_lowmem_prunes  | 4010916  |

| Qcache_not_cached | 13385031  |

| Qcache_queries_in_cache | 43560 |

| Qcache_total_blocks  | 111212   |

+——————————-+—————–+

mysql show variables like lsquo;query_cache% lsquo;;

+————————————–+————–+

| Variable_name     | Value   |

+————————————–+———–+

| query_cache_limit   | 2097152   |

| query_cache_min_res_unit   | 4096   |

| query_cache_size   | 203423744 |

| query_cache_type   | ON     |

| query_cache_wlock_invalidate | OFF   |

+————————————–+—————+

查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查詢緩存碎片率超過 20%，可以用 FLUSH QUERY CACHE 整理緩存碎片，或者試試減小 query_cache_min_res_unit，如果你的查詢都是小數據量的話。

查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查詢緩存利用率在 25% 以下的話說明 query_cache_size 設置的過大，可適當減小；查詢緩存利用率在 80%以上而且 Qcache_lowmem_prunes 50 的話說明 query_cache_size 可能有點小，要不就是碎片太多。

查詢緩存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

示例服務器查詢緩存碎片率=20.46%，查詢緩存利用率=62.26%，查詢緩存命中率=1.94%，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。

每個連接的緩沖

6) record_buffer_size

每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區。如果你做很多順序掃描，你可能想要增加該值。

默認數值是 131072(128K)，可改為 16773120 (16M)

7)  read_rnd_buffer_size

隨機讀緩沖區大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區。進行排序查詢時，MySQL 會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數據，可適當調高該值。但 MySQL 會為每個客戶連接發放該緩沖空間，所以應盡量適當設置該值，以避免內存開銷過大。

一般可設置為 16M 

8)  sort_buffer_size

每個需要進行排序的線程分配該大小的一個緩沖區。增加這值加速 ORDER BY 或 GROUP BY 操作。

默認數值是 2097144(2M)，可改為 16777208 (16M)。

9)  join_buffer_size

聯合查詢操作所能使用的緩沖區大小

record_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size 為每個線程獨占，也就是說，如果有 100 個線程連接，則占用為 16M*100

10)  table_cache

表高速緩存的大小。每當 MySQL 訪問一個表時，如果在表緩沖區中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值 Open_tables 和 Opened_tables，可以決定是否需要增加 table_cache 的值。如果你發現 open_tables 等于 table_cache，并且 opened_tables 在不斷增長，那么你就需要增加 table_cache 的值了（上述狀態值可以使用 SHOW STATUS LIKE lsquo;Open%tables rsquo; 獲得）。注意，不能盲目地把 table_cache 設置成很大的值。如果設置得太高，可能會造成文件描述符不足，從而造成性能不穩定或者連接失敗。

1G 內存機器，推薦值是 128-256。內存在 4GB 左右的服務器該參數可設置為 256M 或 384M。

11)  max_heap_table_size

用戶可以創建的內存表 (memory table) 的大小。這個值用來計算內存表的最大行數值。這個變量支持動態改變，即 set @max_heap_table_size=#

這個變量和 tmp_table_size 一起限制了內部內存表的大小。如果某個內部 heap（堆積）表大小超過 tmp_table_size，MySQL 可以根據需要自動將內存中的 heap 表改為基于硬盤的 MyISAM 表。

12)  tmp_table_size

通過設置 tmp_table_size 選項來增加一張臨時表的大小，例如做高級 GROUP BY 操作生成的臨時表。如果調高該值，MySQL 同時將增加 heap 表的大小，可達到提高聯接查詢速度的效果，建議盡量優化查詢，要確保查詢過程中生成的臨時表在內存中，避免臨時表過大導致生成基于硬盤的 MyISAM 表。

mysql show global status like lsquo;created_tmp% lsquo;;

+——————————–+———+

| Variable_name   | Value |

+———————————-+———+

| Created_tmp_disk_tables | 21197  |

| Created_tmp_files | 58 |

| Created_tmp_tables | 1771587 |

+——————————–+———–+

每次創建臨時表，Created_tmp_tables 增加，如果臨時表大小超過 tmp_table_size，則是在磁盤上創建臨時表，Created_tmp_disk_tables 也增加,Created_tmp_files 表示 MySQL 服務創建的臨時文件文件數，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% = 25% 比如上面的服務器 Created_tmp_disk_tables / Created_tmp_tables * 100% =1.20%，應該相當好了

默認為 16M，可調到 64-256 最佳，線程獨占，太大可能內存不夠 I / O 堵塞

13) thread_cache_size

可以復用的保存在中的線程的數量。如果有，新的線程從緩存中取得，當斷開連接的時候如果有空間，客戶的線置在緩存中。如果有很多新的線程，為了提高性能可以這個變量值。

通過比較 Connections 和 Threads_created 狀態的變量，可以看到這個變量的作用。

默認值為 110，可調優為 80。 

14)  thread_concurrency

推薦設置為服務器  CPU 核數的 2 倍，例如雙核的 CPU, 那么 thread_concurrency 的應該為 4；2 個雙核的 cpu, thread_concurrency 的值應為 8。默認為 8

15)  wait_timeout

指定一個請求的最大連接時間，對于 4GB 左右內存的服務器可以設置為 5 -10。

3.  配置 InnoDB 的幾個變量

innodb_buffer_pool_size

對于 InnoDB 表來說，innodb_buffer_pool_size 的作用就相當于 key_buffer_size 對于 MyISAM 表的作用一樣。InnoDB 使用該參數指定大小的內存來緩沖數據和索引。對于單獨的 MySQL 服務器，最大可以把該值設置成物理內存的 80%。

根據 MySQL 手冊，對于 2G 內存的機器，推薦值是 1G（50%）。

innodb_flush_log_at_trx_commit

主要控制了 innodb 將 log buffer 中的數據寫入日志文件并 flush 磁盤的時間點，取值分別為 0、1、2 三個。0，表示當事務提交時，不做日志寫入操作，而是每秒鐘將 log buffer 中的數據寫入日志文件并 flush 磁盤一次；1，則在每秒鐘或是每次事物的提交都會引起日志文件寫入、flush 磁盤的操作，確保了事務的 ACID；設置為 2，每次事務提交引起寫入日志文件的動作，但每秒鐘完成一次 flush 磁盤操作。

實際測試發現，該值對插入數據的速度影響非常大，設置為 2 時插入 10000 條記錄只需要 2 秒，設置為 0 時只需要 1 秒，而設置為 1 時則需要 229 秒。因此，MySQL 手冊也建議盡量將插入操作合并成一個事務，這樣可以大幅提高速度。

根據 MySQL 手冊，在允許丟失最近部分事務的危險的前提下，可以把該值設為 0 或 2。

innodb_log_buffer_size

log 緩存大小，一般為 1 -8M，默認為 1M，對于較大的事務，可以增大緩存大小。

可設置為 4M 或 8M。

innodb_additional_mem_pool_size

該參數指定 InnoDB 用來存儲數據字典和其他內部數據結構的內存池大小。缺省值是 1M。通常不用太大，只要夠用就行，應該與表結構的復雜度有關系。如果不夠用，MySQL 會在錯誤日志中寫入一條警告信息。

根據 MySQL 手冊，對于 2G 內存的機器，推薦值是 20M，可適當增加。

innodb_thread_concurrency=8

推薦設置為  2*(NumCPUs+NumDisks)，默認一般為 8

關于“mysql 中索引配置如何優化”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。