分表是分散數(shù)據(jù)庫壓力的好方法。
創(chuàng)新互聯(lián)公司長期為1000多家客戶提供的網(wǎng)站建設服務，團隊從業(yè)經(jīng)驗10年，關注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為淮陽企業(yè)提供專業(yè)的成都網(wǎng)站建設、做網(wǎng)站，淮陽網(wǎng)站改版等技術服務。擁有10年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。
分表，最直白的意思，就是將一個表結構分為多個表，然后，可以再同一個庫里，也可以放到不同的庫。
當然，首先要知道什么情況下，才需要分表。個人覺得單表記錄條數(shù)達到百萬到千萬級別時就要使用分表了。
分表的分類
**1、縱向分表**
將本來可以在同一個表的內容，人為劃分為多個表。（所謂的本來，是指按照關系型數(shù)據(jù)庫的第三范式要求，是應該在同一個表的。）
分表理由：根據(jù)數(shù)據(jù)的活躍度進行分離，（因為不同活躍的數(shù)據(jù)，處理方式是不同的）
案例：
對于一個博客系統(tǒng)，文章標題，作者，分類，創(chuàng)建時間等，是變化頻率慢，查詢次數(shù)多，而且最好有很好的實時性的數(shù)據(jù)，我們把它叫做冷數(shù)據(jù)。而博客的瀏覽量，回復數(shù)等，類似的統(tǒng)計信息，或者別的變化頻率比較高的數(shù)據(jù)，我們把它叫做活躍數(shù)據(jù)。所以，在進行數(shù)據(jù)庫結構設計的時候，就應該考慮分表，首先是縱向分表的處理。
這樣縱向分表后：
首先存儲引擎的使用不同，冷數(shù)據(jù)使用MyIsam 可以有更好的查詢數(shù)據(jù)?；钴S數(shù)據(jù)，可以使用Innodb ,可以有更好的更新速度。
其次，對冷數(shù)據(jù)進行更多的從庫配置，因為更多的操作時查詢，這樣來加快查詢速度。對熱數(shù)據(jù)，可以相對有更多的主庫的橫向分表處理。
其實，對于一些特殊的活躍數(shù)據(jù)，也可以考慮使用memcache ,redis之類的緩存，等累計到一定量再去更新數(shù)據(jù)庫?；蛘適ongodb 一類的nosql 數(shù)據(jù)庫，這里只是舉例，就先不說這個。
**2、橫向分表**
字面意思，就可以看出來，是把大的表結構，橫向切割為同樣結構的不同表，如，用戶信息表，user_1,user_2等。表結構是完全一樣，但是，根據(jù)某些特定的規(guī)則來劃分的表，如根據(jù)用戶ID來取模劃分。
分表理由：根據(jù)數(shù)據(jù)量的規(guī)模來劃分，保證單表的容量不會太大，從而來保證單表的查詢等處理能力。
案例：同上面的例子，博客系統(tǒng)。當博客的量達到很大時候，就應該采取橫向分割來降低每個單表的壓力，來提升性能。例如博客的冷數(shù)據(jù)表，假如分為100個表，當同時有100萬個用戶在瀏覽時，如果是單表的話，會進行100萬次請求，而現(xiàn)在分表后，就可能是每個表進行1萬個數(shù)據(jù)的請求（因為，不可能絕對的平均，只是假設），這樣壓力就降低了很多很多。
延伸：為什么要分表和分區(qū)？
日常開發(fā)中我們經(jīng)常會遇到大表的情況，所謂的大表是指存儲了百萬級乃至千萬級條記錄的表。這樣的表過于龐大，導致數(shù)據(jù)庫在查詢和插入的時候耗時太長，性能低下，如果涉及聯(lián)合查詢的情況，性能會更加糟糕。分表和表分區(qū)的目的就是減少數(shù)據(jù)庫的負擔，提高數(shù)據(jù)庫的效率，通常點來講就是提高表的增刪改查效率。
什么是分表？
分表是將一個大表按照一定的規(guī)則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應三個文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結構文件。這些子表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據(jù)事先定義好的規(guī)則得到對應的子表名，然后去操作它。
什么是分區(qū)？
分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個獨立的實體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區(qū)后，表面上還是一張表，但數(shù)據(jù)散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區(qū)的數(shù)據(jù)。
**MySQL分表和分區(qū)有什么聯(lián)系呢？**
1、都能提高mysql的性高，在高并發(fā)狀態(tài)下都有一個良好的表現(xiàn)。
2、分表和分區(qū)不矛盾，可以相互配合的，對于那些大訪問量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結合的方式（如果merge這種分表方式，不能和分區(qū)配合的話，可以用其他的分表試），訪問量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。
3、分表技術是比較麻煩的，需要手動去創(chuàng)建子表，app服務端讀寫時候需要計算子表名。采用merge好一些，但也要創(chuàng)建子表和配置子表間的union關系。
4、表分區(qū)相對于分表，操作方便，不需要創(chuàng)建子表。
我們知道對于大型的互聯(lián)網(wǎng)應用，數(shù)據(jù)庫單表的數(shù)據(jù)量可能達到千萬甚至上億級別，同時面臨這高并發(fā)的壓力。Master-Slave結構只能對數(shù)據(jù)庫的讀能力進行擴展，寫操作還是集中在Master中，Master并不能無限制的掛接Slave庫，如果需要對數(shù)據(jù)庫的吞吐能力進行進一步的擴展，可以考慮采用分庫分表的策略。
**1、分表**
在分表之前，首先要選中合適的分表策略（以哪個字典為分表字段，需要將數(shù)據(jù)分為多少張表），使數(shù)據(jù)能夠均衡的分布在多張表中，并且不影響正常的查詢。在企業(yè)級應用中，往往使用org_id(組織主鍵)做為分表字段，在互聯(lián)網(wǎng)應用中往往是userid。在確定分表策略后，當數(shù)據(jù)進行存儲及查詢時，需要確定到哪張表里去查找數(shù)據(jù)，
數(shù)據(jù)存放的數(shù)據(jù)表 = 分表字段的內容 % 分表數(shù)量
**2、分庫**
分表能夠解決單表數(shù)據(jù)量過大帶來的查詢效率下降的問題，但是不能給數(shù)據(jù)庫的并發(fā)訪問帶來質的提升，面對高并發(fā)的寫訪問，當Master無法承擔高并發(fā)的寫入請求時，不管如何擴展Slave服務器，都沒有意義了。我們通過對數(shù)據(jù)庫進行拆分，來提高數(shù)據(jù)庫的寫入能力，即所謂的分庫。分庫采用對關鍵字取模的方式，對數(shù)據(jù)庫進行路由。
數(shù)據(jù)存放的數(shù)據(jù)庫=分庫字段的內容%數(shù)據(jù)庫的數(shù)量
**3、即分表又分庫**
數(shù)據(jù)庫分表可以解決單表海量數(shù)據(jù)的查詢性能問題，分庫可以解決單臺數(shù)據(jù)庫的并發(fā)訪問壓力問題。
當數(shù)據(jù)庫同時面臨海量數(shù)據(jù)存儲和高并發(fā)訪問的時候，需要同時采取分表和分庫策略。一般分表分庫策略如下：
中間變量 = 關鍵字%（數(shù)據(jù)庫數(shù)量*單庫數(shù)據(jù)表數(shù)量）
庫 = 取整（中間變量/單庫數(shù)據(jù)表數(shù)量）
表 = （中間變量%單庫數(shù)據(jù)表數(shù)量）
實例：
1、分庫分表
很明顯，一個主表（也就是很重要的表，例如用戶表）無限制的增長勢必嚴重影響性能，分庫與分表是一個很不錯的解決途徑，也就是性能優(yōu)化途徑，現(xiàn)在的案例是我們有一個1000多萬條記錄的用戶表members,查詢起來非常之慢，同事的做法是將其散列到100個表中，分別從members0到members99，然后根據(jù)mid分發(fā)記錄到這些表中，牛逼的代碼大概是這樣子：
復制代碼 代碼如下:
?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表，前期設計的表結構不盡合理，隨著數(shù)據(jù)庫不斷運行，其冗余數(shù)據(jù)也是增長巨大，同事使用了下面的方法來處理：
先創(chuàng)建一個臨時表：
/*創(chuàng)建臨時表*/
CREATE TABLE members_tmp LIKE members
然后修改members_tmp的表結構為新結構，接著使用上面那個for循環(huán)來導出數(shù)據(jù)，因為1000萬的數(shù)據(jù)一次性導出是不對的，mid是主鍵，一個區(qū)間一個區(qū)間的導，基本是一次導出5萬條吧，這里略去了
接著重命名將新表替換上去：
/*這是個頗為經(jīng)典的語句哈*/
RENAME TABLE members TO members_bak,members_tmp TO members;
就是這樣，基本可以做到無損失，無需停機更新表結構，但實際上RENAME期間表是被鎖死的，所以選擇在線少的時候操作是一個技巧。經(jīng)過這個操作，使得原先8G多的表，一下子變成了2G多。
mysql -u root -p 回車輸入密碼進入mysql
show processlist;
查看連接數(shù)，可以發(fā)現(xiàn)有很多連接處于sleep狀態(tài)，這些其實是暫時沒有用的，所以可以kill掉
show variables like "max_connections";
查看最大連接數(shù)，應該是與上面查詢到的連接數(shù)相同，才會出現(xiàn)too many connections的情況
set GLOBAL max_connections=1000;
修改最大連接數(shù)，但是這不是一勞永逸的方法，應該要讓它自動殺死那些sleep的進程。
show global variables like 'wait_timeout';
這個數(shù)值指的是mysql在關閉一個非交互的連接之前要等待的秒數(shù)，默認是28800s
set global wait_timeout=300;
修改這個數(shù)值，這里可以隨意，最好控制在幾分鐘內
set global interactive_timeout=500;
修改這個數(shù)值，表示mysql在關閉一個連接之前要等待的秒數(shù)，至此可以讓mysql自動關閉那些沒用的連接，但要注意的是，正在使用的連接到了時間也會被關閉，因此這個時間值要合適
SHOW VARIABLES LIKE '%table_open_cache%';
查看
show global status like 'Open%tables';
前言:
MYSQL 應該是最流行了 WEB 后端數(shù)據(jù)庫。雖然 NOSQL 最近越來越多的被提到，但是相信大部分架構師還是會選擇 MYSQL 來做數(shù)據(jù)存儲。本文作者總結梳理MySQL性能調優(yōu)的15個重要變量，又不足需要補充的還望大佬指出。
1.DEFAULT_STORAGE_ENGINE
如果你已經(jīng)在用MySQL 5.6或者5.7，并且你的數(shù)據(jù)表都是InnoDB，那么表示你已經(jīng)設置好了。如果沒有，確保把你的表轉換為InnoDB并且設置default_storage_engine為InnoDB。
為什么？簡而言之，因為InnoDB是MySQL(包括Percona Server和MariaDB)最好的存儲引擎 – 它支持事務，高并發(fā)，有著非常好的性能表現(xiàn)(當配置正確時)。這里有詳細的版本介紹為什么
2.INNODB_BUFFER_POOL_SIZE
這個是InnoDB最重要變量。實際上，如果你的主要存儲引擎是InnoDB，那么對于你，這個變量對于MySQL是最重要的。
基本上，innodb_buffer_pool_size指定了MySQL應該分配給InnoDB緩沖池多少內存，InnoDB緩沖池用來存儲緩存的數(shù)據(jù)，二級索引，臟數(shù)據(jù)(已經(jīng)被更改但沒有刷新到硬盤的數(shù)據(jù))以及各種內部結構如自適應哈希索引。
根據(jù)經(jīng)驗，在一個獨立的MySQL服務器應該分配給MySQL整個機器總內存的80%。如果你的MySQL運行在一個共享服務器，或者你想知道InnoDB緩沖池大小是否正確設置，詳細請看這里。
3.INNODB_LOG_FILE_SIZE
InnoDB重做日志文件的設置在MySQL社區(qū)也叫做事務日志。直到MySQL 5.6.8事務日志默認值innodb_log_file_size=5M是唯一最大的InnoDB性能殺手。從MySQL 5.6.8開始，默認值提升到48M,但對于許多稍繁忙的系統(tǒng)，還遠遠要低。
根據(jù)經(jīng)驗，你應該設置的日志大小能在你服務器繁忙時能存儲1-2小時的寫入量。如果不想這么麻煩，那么設置1-2G的大小會讓你的性能有一個不錯的表現(xiàn)。這個變量也相當重要，更詳細的介紹請看這里。
當然，如果你有大量的大事務更改，那么，更改比默認innodb日志緩沖大小更大的值會對你的性能有一定的提高，但是你使用的是autocommit，或者你的事務更改小于幾k，那還是保持默認的值吧。
4.INNODB_FLUSH_LOG_AT_TRX_COMMIT
默認下，innodb_flush_log_at_trx_commit設置為1表示InnoDB在每次事務提交后立即刷新同步數(shù)據(jù)到硬盤。如果你使用autocommit，那么你的每一個INSERT, UPDATE或DELETE語句都是一個事務提交。
同步是一個昂貴的操作(特別是當你沒有寫回緩存時)，因為它涉及對硬盤的實際同步物理寫入。所以如果可能，并不建議使用默認值。
兩個可選的值是0和2:
* 0表示刷新到硬盤，但不同步(提交事務時沒有實際的IO操作)
* 2表示不刷新和不同步(也沒有實際的IO操作)
所以你如果設置它為0或2，則同步操作每秒執(zhí)行一次。所以明顯的缺點是你可能會丟失上一秒的提交數(shù)據(jù)。具體來說，你的事務已經(jīng)提交了，但服務器馬上斷電了，那么你的提交相當于沒有發(fā)生過。
顯示的，對于金融機構，如銀行，這是無法忍受的。不過對于大多數(shù)網(wǎng)站，可以設置為innodb_flush_log_at_trx_commit=0|2，即使服務器最終崩潰也沒有什么大問題。畢竟，僅僅在幾年前有許多網(wǎng)站還是用MyISAM，當崩潰時會丟失30s的數(shù)據(jù)(更不要提那令人抓狂的慢修復進程)。
那么，0和2之間的實際區(qū)別是什么？性能明顯的差異是可以忽略不計，因為刷新到操作系統(tǒng)緩存的操作是非常快的。所以很明顯應該設置為0，萬一MySQL崩潰(不是整個機器)，你不會丟失任何數(shù)據(jù)，因為數(shù)據(jù)已經(jīng)在OS緩存，最終還是會同步到硬盤的。
5.SYNC_BINLOG
已經(jīng)有大量的文檔寫到sync_binlog，以及它和innodb_flush_log_at_trx_commit的關系，下面我們來簡單的介紹下：
a) 如果你的服務器沒有設置從服務器，而且你不做備份，那么設置sync_binlog=0將對性能有好處。
b) 如果你有從服務器并且做備份，但你不介意當主服務器崩潰時在二進制日志丟失一些事件，那么為了更好的性能還是設置為sync_binlog=0.
c) 如果你有從服務器并且備份，你非常在意從服務器的一致性，以及能及時恢復到一個時間點(通過使用最新的一致性備份和二進制日志將數(shù)據(jù)庫恢復到特定時間點的能力)，那么你應該設置innodb_flush_log_at_trx_commit=1，并且需要認真考慮使用sync_binlog=1。
問題是sync_binlog=1代價比較高 – 現(xiàn)在每個事務也要同步一次到硬盤。你可能會想為什么不把兩次同步合并成一次，想法正確 – 新版本的MySQL(5.6和5.7，MariaDB和Percona Server)已經(jīng)能合并提交，那么在這種情況下sync_binlog=1的操作也不是這么昂貴了，但在舊的mysql版本中仍然會對性能有很大影響。
6.INNODB_FLUSH_METHOD
將innodb_flush_method設置為O_DIRECT以避免雙重緩沖.唯一一種情況你不應該使用O_DIRECT是當你操作系統(tǒng)不支持時。但如果你運行的是Linux，使用O_DIRECT來激活直接IO。
不用直接IO，雙重緩沖將會發(fā)生，因為所有的數(shù)據(jù)庫更改首先會寫入到OS緩存然后才同步到硬盤 – 所以InnoDB緩沖池和OS緩存會同時持有一份相同的數(shù)據(jù)。特別是如果你的緩沖池限制為總內存的50%，那意味著在寫密集的環(huán)境中你可能會浪費高達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個緩沖池實例。
你設置后觀察會覺得性能提高不大，但在大多數(shù)高負載情況下，它應該會有不錯的表現(xiàn)。
對了，不要指望這個設置能減少你單個查詢的響應時間。這個是在高并發(fā)負載的服務器上才看得出區(qū)別。比如多個線程同時做許多事情。
8.INNODB_THREAD_CONCURRENCY
InnoDB有一種方法來控制并行執(zhí)行的線程數(shù) – 我們稱為并發(fā)控制機制。大部分是由innodb_thread_concurrency值來控制的。如果設置為0，并發(fā)控制就關閉了，因此InnoDB會立即處理所有進來的請求(盡可能多的)。
在你有32CPU核心且只有4個請求時會沒什么問題。不過想像下你只有4CPU核心和32個請求時 – 如果你讓32個請求同時處理，你這個自找麻煩。因為這些32個請求只有4 CPU核心，顯然地會比平常慢至少8倍(實際上是大于8倍)，而然這些請求每個都有自己的外部和內部鎖，這有很大可能堆積請求。
下面介紹如何更改這個變量，在mysql命令行提示符執(zhí)行：
對于大多數(shù)工作負載和服務器，設置為8是一個好開端，然后你可以根據(jù)服務器達到了這個限制而資源使用率利用不足時逐漸增加?？梢酝ㄟ^show engine innodb status\G來查看目前查詢處理情況，查找類似如下行：
9.SKIP_NAME_RESOLVE
這一項不得不提及，因為仍然有很多人沒有添加這一項。你應該添加skip_name_resolve來避免連接時DNS解析。
大多數(shù)情況下你更改這個會沒有什么感覺，因為大多數(shù)情況下DNS服務器解析會非?？?。不過當DNS服務器失敗時，它會出現(xiàn)在你服務器上出現(xiàn)“unauthenticated connections” ，而就是為什么所有的請求都突然開始慢下來了。
所以不要等到這種事情發(fā)生才更改?，F(xiàn)在添加這個變量并且避免基于主機名的授權。
10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX
* innodb_io_capacity：用來當刷新臟數(shù)據(jù)時，控制MySQL每秒執(zhí)行的寫IO量。
* innodb_io_capacity_max: 在壓力下，控制當刷新臟數(shù)據(jù)時MySQL每秒執(zhí)行的寫IO量
首先，這與讀取無關 – SELECT查詢執(zhí)行的操作。對于讀操作，MySQL會盡最大可能處理并返回結果。至于寫操作，MySQL在后臺會循環(huán)刷新，在每一個循環(huán)會檢查有多少數(shù)據(jù)需要刷新，并且不會用超過innodb_io_capacity指定的數(shù)來做刷新操作。這也包括更改緩沖區(qū)合并（在它們刷新到磁盤之前，更改緩沖區(qū)是輔助臟頁存儲的關鍵）。
第二，我需要解釋一下什么叫“在壓力下”，MySQL中稱為”緊急情況”，是當MySQL在后臺刷新時，它需要刷新一些數(shù)據(jù)為了讓新的寫操作進來。然后，MySQL會用到innodb_io_capacity_max。
那么，應該設置innodb_io_capacity和innodb_io_capacity_max為什么呢？
最好的方法是測量你的存儲設置的隨機寫吞吐量，然后給innodb_io_capacity_max設置為你的設備能達到的最大IOPS。innodb_io_capacity就設置為它的50-75%，特別是你的系統(tǒng)主要是寫操作時。
通常你可以預測你的系統(tǒng)的IOPS是多少。例如由8 15k硬盤組成的RAID10能做大約每秒1000隨機寫操作，所以你可以設置innodb_io_capacity=600和innodb_io_capacity_max=1000。許多廉價企業(yè)SSD可以做4,000-10,000 IOPS等。
這個值設置得不完美問題不大。但是，要注意默認的200和400會限制你的寫吞吐量，因此你可能偶爾會捕捉到刷新進程。如果出現(xiàn)這種情況，可能是已經(jīng)達到你硬盤的寫IO吞吐量，或者這個值設置得太小限制了吞吐量。
11.INNODB_STATS_ON_METADATA
如果你跑的是MySQL 5.6或5.7，你不需要更改innodb_stats_on_metadata的默認值，因為它已經(jīng)設置正確了。
不過在MySQL 5.5或5.1，強烈建議關閉這個變量 – 如果是開啟，像命令show table status會立即查詢INFORMATION_SCHEMA而不是等幾秒再執(zhí)行，這會使用到額外的IO操作。
從5.1.32版本開始，這個是動態(tài)變量，意味著你不需要重啟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 – 足夠的信息來定位硬盤上的頁面了。然后它就能以大量的順序讀非?？焖俚募虞d那些頁面，而不是需要成千上萬的小隨機讀。
第二，啟動時是在后臺加載內容，因為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服務器(并且已經(jīng)盡可能優(yōu)化)，那么自適應哈希索引將下你的下一個瓶頸。自適應哈希索引是InnoDB內部維護的動態(tài)索引，可以提高最常用的查詢模式的性能。這個特性可以重啟服務器關閉，不過默認下在mysql的所有版本開啟。
這個技術非常復雜，在大多數(shù)情況下它會對大多數(shù)類型的查詢直到加速的作用。不過，當你有太多的查詢往數(shù)據(jù)庫，在某一個點上它會花過多的時間等待AHI鎖和閂鎖。
如果你的是MySQL 5.7，沒有這個問題 – innodb_adaptive_hash_index_parts默認設置為8，所以自適應哈希索引被切割為8個分區(qū)，因為不存在全局互斥。
不過在mysql 5.7前的版本，沒有AHI分區(qū)數(shù)量的控制。換句話說，有一個全局互斥鎖來保護AHI，可能導致你的select查詢經(jīng)常撞墻。
所以如果你運行的是5.1或5.6，并且有大量的select查詢，最簡單的方案就是切換成同一版本的Percona Server來激活AHI分區(qū)。
14.QUERY_CACHE_TYPE
如果人認為查詢緩存效果很好，肯定應該使用它。好吧，有時候是有用的。不過這個只在你在低負載時有用，特別是在低負載下大多數(shù)是讀取，小量寫或者沒有。
如果是那樣的情況，設置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開始，表緩存能分割到多個分區(qū)。
表緩存用來存放目前已打開表的列表，當每一個表打開或關閉互斥體就被鎖定 – 即使這是一個隱式臨時表。使用多個分區(qū)絕對減少了潛在的爭用。
從MySQL 5.7.8開始，table_open_cache_instances=16是默認的配置。
歡迎做Java的工程師朋友們私信我資料免費獲取免費的Java架構學習資料（里面有高可用、高并發(fā)、高性能及分布式、Jvm性能調優(yōu)、Spring源碼，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多個知識點的架構資料）
其中覆蓋了互聯(lián)網(wǎng)的方方面面，期間碰到各種產(chǎn)品各種場景下的各種問題，很值得大家借鑒和學習，擴展自己的技術廣度和知識面。
上一篇給小伙伴們講了關于SQL查詢性能優(yōu)化的相關技巧，一個好的查詢SQL離不開合理的索引設計。這篇小二就來嘮一嘮怎么合理的設計一個索引來優(yōu)化我們的查詢速度，要是有不合理的地方...嗯..
當然啦，開個玩笑，歡迎小伙伴們指正！
通常情況下，字段類型的選擇是需要根據(jù)業(yè)務來判斷的，通常需要遵循以下幾點。
下列各種類型表格內容來自菜鳥教程，權當備忘。
優(yōu)化建議：
注意： INT(2)設置的為顯示寬度，而不是整數(shù)的長度，需要配合 ZEROFILL 使用 。
例如 id 設置為 TINYINT(2) UNSIGNED ，表示無符號，可以存儲的最大數(shù)值為255，其中 TINYINT(2) 沒有配合 ZEROFILL 實際沒有任何意義，例如插入數(shù)字200，長度雖然超過了兩位，但是這個時候是可以插入成功的，查詢結果同樣為200；插入數(shù)字5時，同樣查詢結果為5。
而 TINYINT(2) 配合 ZEROFILL 后，當插入數(shù)字5時，實際存儲的還是5，不過在查詢是MySQL會在前面補上一個0，即查詢出來的實際為 05 。
優(yōu)化建議：
優(yōu)化建議：
通常來說，考慮好表中每個字段應該使用什么類型和長度，建完表需要做的事情不是馬上建立索引，而是先把相關主體業(yè)務開發(fā)完畢，然后把涉及該表的SQL都拿出來分析之后再建立索引。
盡量少建立單值索引( 唯一索引除外 )，應當設計一個或者兩三個聯(lián)合索引，讓每一個聯(lián)合索引都盡量去包含SQL語句中的 where、order by、group by 的字段，同時確保聯(lián)合索引的字段順序盡量滿足SQL查詢的最左前綴原則。
索引基數(shù)是指這個字段在表里總共有多少個不同的值，比如一張表總共100萬行記錄，其中有個性別字段，性別一共有三個值：男、女、保密，那么該字段的基數(shù)就是3。
如果對這種小基數(shù)字段建立索引的話，因為索引樹中只有男、女、保密三個值，根本沒法進行快速的二分查找，同時還需要回表查詢，還不如全表掃描嘞。
一般建立索引，盡量使用那些基數(shù)比較大的字段，那么才能發(fā)揮出B+樹快速二分查找的優(yōu)勢來。
在 where 和 order by 出現(xiàn)索引設計沖突時，是優(yōu)先針對where去設計索引？還是優(yōu)先針對order by設計索引？
通常情況下都是優(yōu)先針對 where 來設計索引，因為通常情況下都是先 where 條件使用索引快速篩選出來符合條件的數(shù)據(jù)，然后對進行篩選出來的數(shù)據(jù)進行排序和分組，而 where 條件快速篩選出來的的數(shù)據(jù)往往不會很多。
對生產(chǎn)實際運行過程中，或者測試環(huán)境大數(shù)據(jù)量測試過程中發(fā)現(xiàn)的慢查詢SQL進行特定的索引優(yōu)化、代碼優(yōu)化等策略。
終于輪到實戰(zhàn)了，小二最喜歡實戰(zhàn)了。
寫到這里不得不吐槽一下，這個金三銀四的跳槽季節(jié)，年前提離職了，結果離職還沒辦完就封村整整兩個禮拜了，嗚嗚嗚...
上節(jié)小二就提到會有個很有意思的小案例，那么在疫情當下，門都出不去的日子，感覺這個例子更有意思了，咱們來討論一下各種社交平臺怎么做的用戶信息搜索呢。
社交平臺有一個小伙伴們都喜歡的功能，搜索好友信息，比如小二熟練的點開省份...城市..性別..年齡..身高...
咳咳咳...小二怎么可能干這種事情，小二的心里只有代碼，嗯...沒錯，就是這樣。
這個就可以說是對于用戶信息的查詢篩選了，通常這種表都是非常大數(shù)據(jù)量的，在不考慮分庫分表的情況下，怎么通過索引配合SQL來優(yōu)化呢？
通常我們在編寫SQL是會寫出類似如下的SQL來執(zhí)行，有 where、order by、limit 等條件來查詢。
那么接下來小二一個一個慢慢增加字段來分析分析，怎么根據(jù)業(yè)務場景來設計索引。
針對這種情況，很簡單，設計一個聯(lián)合索引 (provice, city, sex) 就完事了。
那么這時候有小伙伴就會說了，很簡單啊，范圍字段放最后咱還是知道的，聯(lián)合索引改成 (provice, city, sex, age) 不就可以了。
嗯，是的，這么干沒毛病，但是小伙伴們有沒有想過有些人萬一既喜歡帥哥又喜歡美女，別想歪了哈...，挺多小姐姐就既喜歡帥哥又喜歡美女的。
那么這個時候小姐姐就不搜索性別了，那么這個時候聯(lián)合索引只能用到前兩個字段了，那么不符合咱們的專業(yè)標準啊，咋辦呢？這時候還是有辦法的，咱們只需要動動小腦袋改改SQL就行了，在沒有選擇性別時判斷一下，改成下面這樣就可以了。
咋辦嘞，同樣往聯(lián)合索引里面塞，例如 (provice, city, sex, hobby, xx, age) 。
針對這種多個范圍查詢的話，為了比較好的利用索引，在業(yè)務允許的情況下可以使用固定范圍，然后數(shù)據(jù)庫字段存儲范圍標識就可以了，這樣就轉化為了等值匹配，就可以很好地利用索引了。
例如最后登錄時間字段不記錄最后登錄時間，而是記錄設置字段 is_login_within_seven_days 在7天內有登錄則為1，否則為0，最后索引設計成 (provice, city, sex, hobby, xx, is_login_within_seven_days, age) 。
那么根據(jù)場景最后設計出來的這個索引可能已經(jīng)可以覆蓋大部分的查詢流量了，那么如果還有其他一部分熱度比較高的查詢怎么辦呢，辦法也很簡單啊，再加一兩個索引即可。
例如通常會查詢這個城市比較受歡迎(評分：score)的小姐姐，這時候添加一個聯(lián)合索引 (provice, city, sex, score) 那么就可以了。
可以看出，索引時必須結合場景來設計的，思路就是盡量用不超過3個復雜的聯(lián)合索引來抗住大部分的80%以上的常用查詢流量，然后再用一兩個二級索引來抗下一些非常用查詢流量。
以上就是小二要給大家分享的索引設計，如果能動動你發(fā)財?shù)男∈纸o小二點個免費的贊就更好啦~
下篇小二就來講講MySQL事務和鎖機制。
重慶分公司
重慶分公司
