mysql數據庫調優配置,mysql優化配置文件

本文目錄一覽：

1、mysql 參數調優（11）之innodb_buffer_pool_instances設置多個緩衝池實例
2、MySQL性能調優 – 你必須了解的15個重要變量
3、MySQL數據庫性能優化之分區分表分庫
4、mysql數據庫如何優化？誰能給出點具體的解決方案？
5、優化MYSQL數據庫的方法
6、mysql 優化包括哪些內容？

mysql 參數調優（11）之innodb_buffer_pool_instances設置多個緩衝池實例

MySQL 5.5引入了緩衝實例作為減小內部鎖爭用來提高MySQL吞吐量的手段。在5.5版本這個對提升吞吐量幫助很小，然後在MySQL 5.6版本這個提升就非常大了，所以在MySQL5.5中你可能會保守地設置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以設置為8-16個緩衝池實例。設置後觀察會覺得性能提高不大，但在大多數高負載情況下，它應該會有不錯的表現。對了，不要指望這個設置能減少你單個查詢的響應時間。這個是在高並發負載的服務器上才看得出區別。比如多個線程同時做許多事情。

5.7、8.0 下INNODB_BUFFER_POOL_INSTANCES默認為1，若mysql存在高並發和高負載訪問，設置為1則會造成大量線程對BUFFER_POOL的單實例互斥鎖競爭，這樣會消耗一定量的性能的。

pool_instances 可以設置為cpu核心數，它的作用是：

1）對於緩衝池在數千兆位元組範圍內的系統，通過減少爭用不同線程對緩存頁面進行讀寫的爭用，將緩衝池劃分為多個單獨的實例可以提高並發性。可以類比為 java中的 ThreadLocal 線程本地變量就是為每個線程維護一個buffer pool實例，這樣就不用去爭用同一個實例了。相當於減少高並發下mysql對INNODB_BUFFER緩衝池的爭用。

2）使用散列函數將存儲在緩衝池中或從緩衝池讀取的每個頁面隨機分配給其中一個緩衝池實例。每個緩衝池管理自己的空閑列表，刷新列表， LRU和連接到緩衝池的所有其他數據結構，並受其自己的緩衝池互斥量保護。

MySQL性能調優 – 你必須了解的15個重要變量

前言:

MYSQL 應該是最流行了 WEB 後端數據庫。雖然 NOSQL 最近越來越多的被提到，但是相信大部分架構師還是會選擇 MYSQL 來做數據存儲。本文作者總結梳理MySQL性能調優的15個重要變量，又不足需要補充的還望大佬指出。

1.DEFAULT_STORAGE_ENGINE

如果你已經在用MySQL 5.6或者5.7，並且你的數據表都是InnoDB，那麼表示你已經設置好了。如果沒有，確保把你的錶轉換為InnoDB並且設置default_storage_engine為InnoDB。

為什麼？簡而言之，因為InnoDB是MySQL(包括Percona Server和MariaDB)最好的存儲引擎 – 它支持事務，高並發，有着非常好的性能表現(當配置正確時)。這裡有詳細的版本介紹為什麼

2.INNODB_BUFFER_POOL_SIZE

這個是InnoDB最重要變量。實際上，如果你的主要存儲引擎是InnoDB，那麼對於你，這個變量對於MySQL是最重要的。

基本上，innodb_buffer_pool_size指定了MySQL應該分配給InnoDB緩衝池多少內存，InnoDB緩衝池用來存儲緩存的數據，二級索引，臟數據(已經被更改但沒有刷新到硬盤的數據)以及各種內部結構如自適應哈希索引。

根據經驗，在一個獨立的MySQL服務器應該分配給MySQL整個機器總內存的80%。如果你的MySQL運行在一個共享服務器，或者你想知道InnoDB緩衝池大小是否正確設置，詳細請看這裡。

3.INNODB_LOG_FILE_SIZE

InnoDB重做日誌文件的設置在MySQL社區也叫做事務日誌。直到MySQL 5.6.8事務日誌默認值innodb_log_file_size=5M是唯一最大的InnoDB性能殺手。從MySQL 5.6.8開始，默認值提升到48M,但對於許多稍繁忙的系統，還遠遠要低。

根據經驗，你應該設置的日誌大小能在你服務器繁忙時能存儲1-2小時的寫入量。如果不想這麼麻煩，那麼設置1-2G的大小會讓你的性能有一個不錯的表現。這個變量也相當重要，更詳細的介紹請看這裡。

當然，如果你有大量的大事務更改，那麼，更改比默認innodb日誌緩衝大小更大的值會對你的性能有一定的提高，但是你使用的是autocommit，或者你的事務更改小於幾k，那還是保持默認的值吧。

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

默認下，innodb_flush_log_at_trx_commit設置為1表示InnoDB在每次事務提交後立即刷新同步數據到硬盤。如果你使用autocommit，那麼你的每一個INSERT, UPDATE或DELETE語句都是一個事務提交。

同步是一個昂貴的操作(特別是當你沒有寫回緩存時)，因為它涉及對硬盤的實際同步物理寫入。所以如果可能，並不建議使用默認值。

兩個可選的值是0和2:

* 0表示刷新到硬盤，但不同步(提交事務時沒有實際的IO操作)

* 2表示不刷新和不同步(也沒有實際的IO操作)

所以你如果設置它為0或2，則同步操作每秒執行一次。所以明顯的缺點是你可能會丟失上一秒的提交數據。具體來說，你的事務已經提交了，但服務器馬上斷電了，那麼你的提交相當於沒有發生過。

顯示的，對於金融機構，如銀行，這是無法忍受的。不過對於大多數網站，可以設置為innodb_flush_log_at_trx_commit=0|2，即使服務器最終崩潰也沒有什麼大問題。畢竟，僅僅在幾年前有許多網站還是用MyISAM，當崩潰時會丟失30s的數據(更不要提那令人抓狂的慢修復進程)。

那麼，0和2之間的實際區別是什麼？性能明顯的差異是可以忽略不計，因為刷新到操作系統緩存的操作是非常快的。所以很明顯應該設置為0，萬一MySQL崩潰(不是整個機器)，你不會丟失任何數據，因為數據已經在OS緩存，最終還是會同步到硬盤的。

5.SYNC_BINLOG

已經有大量的文檔寫到sync_binlog，以及它和innodb_flush_log_at_trx_commit的關係，下面我們來簡單的介紹下：

a) 如果你的服務器沒有設置從服務器，而且你不做備份，那麼設置sync_binlog=0將對性能有好處。

b) 如果你有從服務器並且做備份，但你不介意當主服務器崩潰時在二進制日誌丟失一些事件，那麼為了更好的性能還是設置為sync_binlog=0.

c) 如果你有從服務器並且備份，你非常在意從服務器的一致性，以及能及時恢復到一個時間點(通過使用最新的一致性備份和二進制日誌將數據庫恢復到特定時間點的能力)，那麼你應該設置innodb_flush_log_at_trx_commit=1，並且需要認真考慮使用sync_binlog=1。

問題是sync_binlog=1代價比較高 – 現在每個事務也要同步一次到硬盤。你可能會想為什麼不把兩次同步合併成一次，想法正確 – 新版本的MySQL(5.6和5.7，MariaDB和Percona Server)已經能合併提交，那麼在這種情況下sync_binlog=1的操作也不是這麼昂貴了，但在舊的mysql版本中仍然會對性能有很大影響。

6.INNODB_FLUSH_METHOD

將innodb_flush_method設置為O_DIRECT以避免雙重緩衝.唯一一種情況你不應該使用O_DIRECT是當你操作系統不支持時。但如果你運行的是Linux，使用O_DIRECT來激活直接IO。

不用直接IO，雙重緩衝將會發生，因為所有的數據庫更改首先會寫入到OS緩存然後才同步到硬盤 – 所以InnoDB緩衝池和OS緩存會同時持有一份相同的數據。特別是如果你的緩衝池限制為總內存的50%，那意味着在寫密集的環境中你可能會浪費高達50%的內存。如果沒有限制為50%，服務器可能由於OS緩存的高壓力會使用到swap。

簡單地說，設置為innodb_flush_method=O_DIRECT。

7.INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5引入了緩衝實例作為減小內部鎖爭用來提高MySQL吞吐量的手段。

在5.5版本這個對提升吞吐量幫助很小，然後在MySQL 5.6版本這個提升就非常大了，所以在MySQL5.5中你可能會保守地設置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以設置為8-16個緩衝池實例。

你設置後觀察會覺得性能提高不大，但在大多數高負載情況下，它應該會有不錯的表現。

對了，不要指望這個設置能減少你單個查詢的響應時間。這個是在高並發負載的服務器上才看得出區別。比如多個線程同時做許多事情。

8.INNODB_THREAD_CONCURRENCY

InnoDB有一種方法來控制並行執行的線程數 – 我們稱為並發控制機制。大部分是由innodb_thread_concurrency值來控制的。如果設置為0，並發控制就關閉了，因此InnoDB會立即處理所有進來的請求(儘可能多的)。

在你有32CPU核心且只有4個請求時會沒什麼問題。不過想像下你只有4CPU核心和32個請求時 – 如果你讓32個請求同時處理，你這個自找麻煩。因為這些32個請求只有4 CPU核心，顯然地會比平常慢至少8倍(實際上是大於8倍)，而然這些請求每個都有自己的外部和內部鎖，這有很大可能堆積請求。

下面介紹如何更改這個變量，在mysql命令行提示符執行：

對於大多數工作負載和服務器，設置為8是一個好開端，然後你可以根據服務器達到了這個限制而資源使用率利用不足時逐漸增加。可以通過show engine innodb status\G來查看目前查詢處理情況，查找類似如下行：

9.SKIP_NAME_RESOLVE

這一項不得不提及，因為仍然有很多人沒有添加這一項。你應該添加skip_name_resolve來避免連接時DNS解析。

大多數情況下你更改這個會沒有什麼感覺，因為大多數情況下DNS服務器解析會非常快。不過當DNS服務器失敗時，它會出現在你服務器上出現「unauthenticated connections」，而就是為什麼所有的請求都突然開始慢下來了。

所以不要等到這種事情發生才更改。現在添加這個變量並且避免基於主機名的授權。

10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

* innodb_io_capacity：用來當刷新臟數據時，控制MySQL每秒執行的寫IO量。

* innodb_io_capacity_max: 在壓力下，控制當刷新臟數據時MySQL每秒執行的寫IO量

首先，這與讀取無關 – SELECT查詢執行的操作。對於讀操作，MySQL會盡最大可能處理並返回結果。至於寫操作，MySQL在後台會循環刷新，在每一個循環會檢查有多少數據需要刷新，並且不會用超過innodb_io_capacity指定的數來做刷新操作。這也包括更改緩衝區合併（在它們刷新到磁盤之前，更改緩衝區是輔助臟頁存儲的關鍵）。

第二，我需要解釋一下什麼叫「在壓力下」，MySQL中稱為」緊急情況」，是當MySQL在後台刷新時，它需要刷新一些數據為了讓新的寫操作進來。然後，MySQL會用到innodb_io_capacity_max。

那麼，應該設置innodb_io_capacity和innodb_io_capacity_max為什麼呢？

最好的方法是測量你的存儲設置的隨機寫吞吐量，然後給innodb_io_capacity_max設置為你的設備能達到的最大IOPS。innodb_io_capacity就設置為它的50-75%，特別是你的系統主要是寫操作時。

通常你可以預測你的系統的IOPS是多少。例如由8 15k硬盤組成的RAID10能做大約每秒1000隨機寫操作，所以你可以設置innodb_io_capacity=600和innodb_io_capacity_max=1000。許多廉價企業SSD可以做4,000-10,000 IOPS等。

這個值設置得不完美問題不大。但是，要注意默認的200和400會限制你的寫吞吐量，因此你可能偶爾會捕捉到刷新進程。如果出現這種情況，可能是已經達到你硬盤的寫IO吞吐量，或者這個值設置得太小限制了吞吐量。

11.INNODB_STATS_ON_METADATA

如果你跑的是MySQL 5.6或5.7，你不需要更改innodb_stats_on_metadata的默認值，因為它已經設置正確了。

不過在MySQL 5.5或5.1，強烈建議關閉這個變量 – 如果是開啟，像命令show table status會立即查詢INFORMATION_SCHEMA而不是等幾秒再執行，這會使用到額外的IO操作。

從5.1.32版本開始，這個是動態變量，意味着你不需要重啟MySQL服務器來關閉它。

12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN INNODB_BUFFER_POOL_LOAD_AT_STARTUP

innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup這兩個變量與性能無關，不過如果你偶爾重啟mysql服務器(如生效配置)，那麼就有關。當兩個都激活時，MySQL緩衝池的內容(更具體地說，是緩存頁)在停止MySQL時存儲到一個文件。當你下次啟動MySQL時，它會在後台啟動一個線程來加載緩衝池的內容以提高預熱速度到3-5倍。

兩件事：

第一，它實際上沒有在關閉時複製緩衝池內容到文件，僅僅是複製表空間ID和頁面ID – 足夠的信息來定位硬盤上的頁面了。然後它就能以大量的順序讀非常快速的加載那些頁面，而不是需要成千上萬的小隨機讀。

第二，啟動時是在後台加載內容，因為MySQL不需要等到緩衝池內容加載完成再開始接受請求(所以看起來不會有什麼影響)。

從MySQL 5.7.7開始，默認只有25%的緩衝池頁面在mysql關閉時存儲到文件，但是你可以控制這個值 – 使用innodb_buffer_pool_dump_pct，建議75-100。

這個特性從MySQL 5.6才開始支持。

13.INNODB_ADAPTIVE_HASH_INDEX_PARTS

如果你運行着一個大量SELECT查詢的MySQL服務器(並且已經儘可能優化)，那麼自適應哈希索引將下你的下一個瓶頸。自適應哈希索引是InnoDB內部維護的動態索引，可以提高最常用的查詢模式的性能。這個特性可以重啟服務器關閉，不過默認下在mysql的所有版本開啟。

這個技術非常複雜，在大多數情況下它會對大多數類型的查詢直到加速的作用。不過，當你有太多的查詢往數據庫，在某一個點上它會花過多的時間等待AHI鎖和閂鎖。

如果你的是MySQL 5.7，沒有這個問題 – innodb_adaptive_hash_index_parts默認設置為8，所以自適應哈希索引被切割為8個分區，因為不存在全局互斥。

不過在mysql 5.7前的版本，沒有AHI分區數量的控制。換句話說，有一個全局互斥鎖來保護AHI，可能導致你的select查詢經常撞牆。

所以如果你運行的是5.1或5.6，並且有大量的select查詢，最簡單的方案就是切換成同一版本的Percona Server來激活AHI分區。

14.QUERY_CACHE_TYPE

如果人認為查詢緩存效果很好，肯定應該使用它。好吧，有時候是有用的。不過這個只在你在低負載時有用，特別是在低負載下大多數是讀取，小量寫或者沒有。

如果是那樣的情況，設置query_cache_type=ON和query_cache_size=256M就好了。不過記住不能把256M設置更高的值了，否則會由於查詢緩存失效時，導致引起嚴重的服務器停頓。

如果你的MySQL服務器高負載動作，建議設置query_cache_size=0和query_cache_type=OFF，並重啟服務器生效。那樣Mysql就會停止在所有的查詢使用查詢緩存互斥鎖。

15.TABLE_OPEN_CACHE_INSTANCES

從MySQL 5.6.6開始，表緩存能分割到多個分區。

表緩存用來存放目前已打開表的列表，當每一個表打開或關閉互斥體就被鎖定 – 即使這是一個隱式臨時表。使用多個分區絕對減少了潛在的爭用。

從MySQL 5.7.8開始，table_open_cache_instances=16是默認的配置。

歡迎做Java的工程師朋友們私信我資料免費獲取免費的Java架構學習資料（裏面有高可用、高並發、高性能及分佈式、Jvm性能調優、Spring源碼，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多個知識點的架構資料）

其中覆蓋了互聯網的方方面面，期間碰到各種產品各種場景下的各種問題，很值得大家借鑒和學習，擴展自己的技術廣度和知識面。

MySQL數據庫性能優化之分區分表分庫

分表是分散數據庫壓力的好方法。

分表，最直白的意思，就是將一個表結構分為多個表，然後，可以再同一個庫里，也可以放到不同的庫。

當然，首先要知道什麼情況下，才需要分表。個人覺得單表記錄條數達到百萬到千萬級別時就要使用分表了。

分表的分類

**1、縱向分表**

將本來可以在同一個表的內容，人為劃分為多個表。（所謂的本來，是指按照關係型數據庫的第三範式要求，是應該在同一個表的。）

分表理由：根據數據的活躍度進行分離，（因為不同活躍的數據，處理方式是不同的）

案例：

對於一個博客系統，文章標題，作者，分類，創建時間等，是變化頻率慢，查詢次數多，而且最好有很好的實時性的數據，我們把它叫做冷數據。而博客的瀏覽量，回複數等，類似的統計信息，或者別的變化頻率比較高的數據，我們把它叫做活躍數據。所以，在進行數據庫結構設計的時候，就應該考慮分表，首先是縱向分表的處理。

這樣縱向分表後：

首先存儲引擎的使用不同，冷數據使用MyIsam 可以有更好的查詢數據。活躍數據，可以使用Innodb ,可以有更好的更新速度。

其次，對冷數據進行更多的從庫配置，因為更多的操作時查詢，這樣來加快查詢速度。對熱數據，可以相對有更多的主庫的橫向分表處理。

其實，對於一些特殊的活躍數據，也可以考慮使用memcache ,redis之類的緩存，等累計到一定量再去更新數據庫。或者mongodb 一類的nosql 數據庫，這裡只是舉例，就先不說這個。

**2、橫向分表**

字面意思，就可以看出來，是把大的表結構，橫向切割為同樣結構的不同表，如，用戶信息表，user_1,user_2等。表結構是完全一樣，但是，根據某些特定的規則來劃分的表，如根據用戶ID來取模劃分。

分表理由：根據數據量的規模來劃分，保證單表的容量不會太大，從而來保證單表的查詢等處理能力。

案例：同上面的例子，博客系統。當博客的量達到很大時候，就應該採取橫向分割來降低每個單表的壓力，來提升性能。例如博客的冷數據表，假如分為100個表，當同時有100萬個用戶在瀏覽時，如果是單表的話，會進行100萬次請求，而現在分表後，就可能是每個表進行1萬個數據的請求（因為，不可能絕對的平均，只是假設），這樣壓力就降低了很多很多。

延伸：為什麼要分表和分區？

日常開發中我們經常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過於龐大，導致數據庫在查詢和插入的時候耗時太長，性能低下，如果涉及聯合查詢的情況，性能會更加糟糕。分表和表分區的目的就是減少數據庫的負擔，提高數據庫的效率，通常點來講就是提高表的增刪改查效率。

什麼是分表？

分表是將一個大表按照一定的規則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個文件，MYD數據文件，.MYI索引文件，.frm表結構文件。這些子表可以分佈在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據事先定義好的規則得到對應的子表名，然後去操作它。

什麼是分區？

分區和分表相似，都是按照規則分解表。不同在於分表將大表分解為若干個獨立的實體表，而分區是將數據分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區後，表面上還是一張表，但數據散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區的數據。

**MySQL分表和分區有什麼聯繫呢？**

1、都能提高mysql的性高，在高並髮狀態下都有一個良好的表現。

2、分表和分區不矛盾，可以相互配合的，對於那些大訪問量，並且表數據比較多的表，我們可以採取分表和分區結合的方式（如果merge這種分表方式，不能和分區配合的話，可以用其他的分表試），訪問量不大，但是表數據很多的表，我們可以採取分區的方式等。

3、分表技術是比較麻煩的，需要手動去創建子表，app服務端讀寫時候需要計算子表名。採用merge好一些，但也要創建子表和配置子表間的union關係。

4、表分區相對於分表，操作方便，不需要創建子表。

我們知道對於大型的互聯網應用，數據庫單表的數據量可能達到千萬甚至上億級別，同時面臨這高並發的壓力。Master-Slave結構只能對數據庫的讀能力進行擴展，寫操作還是集中在Master中，Master並不能無限制的掛接Slave庫，如果需要對數據庫的吞吐能力進行進一步的擴展，可以考慮採用分庫分表的策略。

**1、分表**

在分表之前，首先要選中合適的分表策略（以哪個字典為分表字段，需要將數據分為多少張表），使數據能夠均衡的分佈在多張表中，並且不影響正常的查詢。在企業級應用中，往往使用org_id(組織主鍵)做為分表字段，在互聯網應用中往往是userid。在確定分表策略後，當數據進行存儲及查詢時，需要確定到哪張表裡去查找數據，

數據存放的數據表 = 分表字段的內容 % 分表數量

**2、分庫**

分表能夠解決單表數據量過大帶來的查詢效率下降的問題，但是不能給數據庫的並發訪問帶來質的提升，面對高並發的寫訪問，當Master無法承擔高並發的寫入請求時，不管如何擴展Slave服務器，都沒有意義了。我們通過對數據庫進行拆分，來提高數據庫的寫入能力，即所謂的分庫。分庫採用對關鍵字取模的方式，對數據庫進行路由。

數據存放的數據庫=分庫字段的內容%數據庫的數量

**3、即分表又分庫**

數據庫分表可以解決單表海量數據的查詢性能問題，分庫可以解決單台數據庫的並發訪問壓力問題。

當數據庫同時面臨海量數據存儲和高並發訪問的時候，需要同時採取分表和分庫策略。一般分表分庫策略如下：

中間變量 = 關鍵字%（數據庫數量*單庫數據表數量）

庫 = 取整（中間變量/單庫數據表數量）

表 = （中間變量%單庫數據表數量）

實例：

1、分庫分表

很明顯，一個主表（也就是很重要的表，例如用戶表）無限制的增長勢必嚴重影響性能，分庫與分表是一個很不錯的解決途徑，也就是性能優化途徑，現在的案例是我們有一個1000多萬條記錄的用戶表members,查詢起來非常之慢，同事的做法是將其散列到100個表中，分別從members0到members99，然後根據mid分發記錄到這些表中，牛逼的代碼大概是這樣子：

複製代碼代碼如下:

?php

for($i=0;$i 100; $i++ ){

//echo “CREATE TABLE db2.members{$i} LIKE db1.members

“;

echo “INSERT INTO members{$i} SELECT * FROM members WHERE mid%100={$i}

“;

}

2、不停機修改mysql表結構

同樣還是members表，前期設計的表結構不盡合理，隨着數據庫不斷運行，其冗餘數據也是增長巨大，同事使用了下面的方法來處理：

先創建一個臨時表：

/*創建臨時表*/

CREATE TABLE members_tmp LIKE members

然後修改members_tmp的表結構為新結構，接着使用上面那個for循環來導出數據，因為1000萬的數據一次性導出是不對的，mid是主鍵，一個區間一個區間的導，基本是一次導出5萬條吧，這裡略去了

接着重命名將新表替換上去：

/*這是個頗為經典的語句哈*/

RENAME TABLE members TO members_bak,members_tmp TO members;

就是這樣，基本可以做到無損失，無需停機更新表結構，但實際上RENAME期間表是被鎖死的，所以選擇在線少的時候操作是一個技巧。經過這個操作，使得原先8G多的表，一下子變成了2G多。

mysql數據庫如何優化？誰能給出點具體的解決方案？

1、explain：解釋sql的執行計劃，後邊的sql不執行

2、explain partitions ：用於查看存在分區的表的執行計劃

3、explain extended：待驗證

4、show warnings:

5、show create table:查看錶的詳細的創建語句，便於用戶對錶進行優化

6、show indexes :產看錶的所有索引，show indexes from table_name，同樣也可以從information_schema.statistics表中獲得同樣的信息。cardinality列很重要，表示數據量。

7、show tables status: 查看數據庫表的底層大小以及表結構，同樣可以從information_schema.tables表中獲得底層表的信息。

8、show [global|session]status:可以查看mysql服務器當前內部狀態信息。可以幫助卻行mysql服務器的負載的各種指標。默認是session。同information_schema.global_status和information_schema.session_status

9、show [global|session] variables ：查看當前mysql系統變量的值，其中一些值能影響到sql語句的執行方式。同information_schema.global_variables和information_schema.session_variables;

10、information_schema:包含的表的數量和mysql的版本有關係。

優化MYSQL數據庫的方法

在開始演示之前，我們先介紹下兩個概念。

概念一，數據的可選擇性基數，也就是常說的cardinality值。

查詢優化器在生成各種執行計劃之前，得先從統計信息中取得相關數據，這樣才能估算每步操作所涉及到的記錄數，而這個相關數據就是cardinality。簡單來說，就是每個值在每個字段中的唯一值分佈狀態。

比如表t1有100行記錄，其中一列為f1。f1中唯一值的個數可以是100個，也可以是1個，當然也可以是1到100之間的任何一個數字。這裡唯一值越的多少，就是這個列的可選擇基數。

那看到這裡我們就明白了，為什麼要在基數高的字段上建立索引，而基數低的的字段建立索引反而沒有全表掃描來的快。當然這個只是一方面，至於更深入的探討就不在我這篇探討的範圍了。

概念二，關於HINT的使用。

這裡我來說下HINT是什麼，在什麼時候用。

HINT簡單來說就是在某些特定的場景下人工協助MySQL優化器的工作，使她生成最優的執行計劃。一般來說，優化器的執行計劃都是最優化的，不過在某些特定場景下，執行計劃可能不是最優化。

比如：表t1經過大量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經很不準確了，這時候剛好執行了一條SQL，那麼有可能這條SQL的執行計劃就不是最優的。為什麼說有可能呢？

來看下具體演示

譬如，以下兩條SQL，

A：

select * from t1 where f1 = 20;

B：

select * from t1 where f1 = 30;

如果f1的值剛好頻繁更新的值為30，並且沒有達到MySQL自動更新cardinality值的臨界值或者說用戶設置了手動更新又或者用戶減少了sample page等等，那麼對這兩條語句來說，可能不準確的就是B了。

這裡順帶說下，MySQL提供了自動更新和手動更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊。

那回到正題上，MySQL 8.0 帶來了幾個HINT，我今天就舉個index_merge的例子。

示例表結構：

mysql desc t1;+————+————–+——+—–+———+—————-+| Field | Type | Null | Key | Default | Extra |+————+————–+——+—–+———+—————-+| id | int(11) | NO | PRI | NULL | auto_increment || rank1 | int(11) | YES | MUL | NULL | || rank2 | int(11) | YES | MUL | NULL | || log_time | datetime | YES | MUL | NULL | || prefix_uid | varchar(100) | YES | | NULL | || desc1 | text | YES | | NULL | || rank3 | int(11) | YES | MUL | NULL | |+————+————–+——+—–+———+—————-+7 rows in set (0.00 sec)

表記錄數：

mysql select count(*) from t1;+———-+| count(*) |+———-+| 32768 |+———-+1 row in set (0.01 sec)

這裡我們兩條經典的SQL：

SQL C：

select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

SQL D：

select * from t1 where rank1 =100 and rank2 =100 and rank3 =100;

表t1實際上在rank1,rank2,rank3三列上分別有一個二級索引。

那我們來看SQL C的查詢計劃。

顯然，沒有用到任何索引，掃描的行數為32034，cost為3243.65。

mysql explain format=json select * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { “query_block”: { “select_id”: 1, “cost_info”: { “query_cost”: “3243.65” }, “table”: { “table_name”: “t1”, “access_type”: “ALL”, “possible_keys”: [ “idx_rank1”, “idx_rank2”, “idx_rank3” ], “rows_examined_per_scan”: 32034, “rows_produced_per_join”: 115, “filtered”: “0.36”, “cost_info”: { “read_cost”: “3232.07”, “eval_cost”: “11.58”, “prefix_cost”: “3243.65”, “data_read_per_join”: “49K” }, “used_columns”: [ “id”, “rank1”, “rank2”, “log_time”, “prefix_uid”, “desc1”, “rank3” ], “attached_condition”: “((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))” } }}1 row in set, 1 warning (0.00 sec)

我們加上hint給相同的查詢，再次看看查詢計劃。

這個時候用到了index_merge,union了三個列。掃描的行數為1103，cost為441.09，明顯比之前的快了好幾倍。

mysql explain format=json select /*+ index_merge(t1) */ * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { “query_block”: { “select_id”: 1, “cost_info”: { “query_cost”: “441.09” }, “table”: { “table_name”: “t1”, “access_type”: “index_merge”, “possible_keys”: [ “idx_rank1”, “idx_rank2”, “idx_rank3” ], “key”: “union(idx_rank1,idx_rank2,idx_rank3)”, “key_length”: “5,5,5”, “rows_examined_per_scan”: 1103, “rows_produced_per_join”: 1103, “filtered”: “100.00”, “cost_info”: { “read_cost”: “330.79”, “eval_cost”: “110.30”, “prefix_cost”: “441.09”, “data_read_per_join”: “473K” }, “used_columns”: [ “id”, “rank1”, “rank2”, “log_time”, “prefix_uid”, “desc1”, “rank3” ], “attached_condition”: “((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))” } }}1 row in set, 1 warning (0.00 sec)

我們再看下SQL D的計劃：

不加HINT，

mysql explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { “query_block”: { “select_id”: 1, “cost_info”: { “query_cost”: “534.34” }, “table”: { “table_name”: “t1”, “access_type”: “ref”, “possible_keys”: [ “idx_rank1”, “idx_rank2”, “idx_rank3” ], “key”: “idx_rank1”, “used_key_parts”: [ “rank1” ], “key_length”: “5”, “ref”: [ “const” ], “rows_examined_per_scan”: 555, “rows_produced_per_join”: 0, “filtered”: “0.07”, “cost_info”: { “read_cost”: “478.84”, “eval_cost”: “0.04”, “prefix_cost”: “534.34”, “data_read_per_join”: “176” }, “used_columns”: [ “id”, “rank1”, “rank2”, “log_time”, “prefix_uid”, “desc1”, “rank3” ], “attached_condition”: “((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))” } }}1 row in set, 1 warning (0.00 sec)

加了HINT，

mysql explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { “query_block”: { “select_id”: 1, “cost_info”: { “query_cost”: “5.23” }, “table”: { “table_name”: “t1”, “access_type”: “index_merge”, “possible_keys”: [ “idx_rank1”, “idx_rank2”, “idx_rank3” ], “key”: “intersect(idx_rank1,idx_rank2,idx_rank3)”, “key_length”: “5,5,5”, “rows_examined_per_scan”: 1, “rows_produced_per_join”: 1, “filtered”: “100.00”, “cost_info”: { “read_cost”: “5.13”, “eval_cost”: “0.10”, “prefix_cost”: “5.23”, “data_read_per_join”: “440” }, “used_columns”: [ “id”, “rank1”, “rank2”, “log_time”, “prefix_uid”, “desc1”, “rank3” ], “attached_condition”: “((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))” } }}1 row in set, 1 warning (0.00 sec)

對比下以上兩個，加了HINT的比不加HINT的cost小了100倍。

總結下，就是說表的cardinality值影響這張的查詢計劃，如果這個值沒有正常更新的話，就需要手工加HINT了。相信MySQL未來的版本會帶來更多的HINT。

mysql 優化包括哪些內容？

mysql的優化大的有兩方面：

1、配置優化

配置的優化其實包含兩個方面的：操作系統內核的優化和mysql配置文件的優化

1)系統內核的優化對專用的mysql服務器來說，無非是內存實用、連接數、超時處理、TCP處理等方面的優化，根據自己的硬件配置來進行優化，這裡不多講；

2)mysql配置的優化，一般來說包含：IO處理的常用參數、最大連接數設置、緩存使用參數的設置、慢日誌的參數的設置、innodb相關參數的設置等，如果有主從關係在設置主從同步的相關參數即可，網上的相關配置文件很多，大同小異，常用的設置大多修改這些差不多就夠用了。

2、sql語句的優化

1）盡量稍作計算

Mysql的作用是用來存取數據的，不是做計算的，做計算的話可以用其他方法去實現，mysql做計算是很耗資源的。

2）盡量少 join

MySQL 的優勢在於簡單，但這在某些方面其實也是其劣勢。MySQL 優化器效率高，但是由於其統計信息的量有限，優化器工作過程出現偏差的可能性也就更多。對於複雜的多表 Join，一方面由於其優化器受限，再者在 Join 這方面所下的功夫還不夠，所以性能表現離 Oracle 等關係型數據庫前輩還是有一定距離。但如果是簡單的單表查詢，這一差距就會極小甚至在有些場景下要優於這些數據庫前輩

3）盡量少排序

排序操作會消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場景下會較大影響 SQL的響應時間。

對於MySQL來說，減少排序有多種辦法，比如：

通過利用索引來排序的方式進行優化

減少參與排序的記錄條數

非必要不對數據進行排序

4）盡量避免 select *

在數據量少並且訪問量不大的情況下，select * 沒有什麼影響，但是量級達到一定級別的時候，在執行效率和IO資源的使用上，還是有很大關係的，用什麼字段取什麼字段，減少不必要的資源浪費。

5）盡量用 join 代替子查詢

雖然 Join 性能並不佳，但是和 MySQL 的子查詢比起來還是有非常大的性能優勢。MySQL 的子查詢執行計劃一直存在較大的問題，雖然這個問題已經存在多年，但是到目前已經發佈的所有穩定版本中都普遍存在，一直沒有太大改善。雖然官方也在很早就承認這一問題，並且承諾儘快解決，但是至少到目前為止我們還沒有看到哪一個版本較好的解決了這一問題。

原創文章，作者：小藍，如若轉載，請註明出處：https://www.506064.com/zh-hk/n/196021.html