前言:
創(chuàng)新互聯(lián)是一家專注于成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站與策劃設(shè)計(jì),三亞網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)十多年,網(wǎng)設(shè)計(jì)領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:三亞等地區(qū)。三亞做網(wǎng)站價(jià)格咨詢:18980820575
MYSQL 應(yīng)該是最流行了 WEB 后端數(shù)據(jù)庫。雖然 NOSQL 最近越來越多的被提到，但是相信大部分架構(gòu)師還是會(huì)選擇 MYSQL 來做數(shù)據(jù)存儲(chǔ)。本文作者總結(jié)梳理MySQL性能調(diào)優(yōu)的15個(gè)重要變量，又不足需要補(bǔ)充的還望大佬指出。
1.DEFAULT_STORAGE_ENGINE
如果你已經(jīng)在用MySQL 5.6或者5.7，并且你的數(shù)據(jù)表都是InnoDB，那么表示你已經(jīng)設(shè)置好了。如果沒有，確保把你的表轉(zhuǎn)換為InnoDB并且設(shè)置default_storage_engine為InnoDB。
為什么？簡(jiǎn)而言之，因?yàn)镮nnoDB是MySQL(包括Percona Server和MariaDB)最好的存儲(chǔ)引擎 – 它支持事務(wù)，高并發(fā)，有著非常好的性能表現(xiàn)(當(dāng)配置正確時(shí))。這里有詳細(xì)的版本介紹為什么
2.INNODB_BUFFER_POOL_SIZE
這個(gè)是InnoDB最重要變量。實(shí)際上，如果你的主要存儲(chǔ)引擎是InnoDB，那么對(duì)于你，這個(gè)變量對(duì)于MySQL是最重要的。
基本上，innodb_buffer_pool_size指定了MySQL應(yīng)該分配給InnoDB緩沖池多少內(nèi)存，InnoDB緩沖池用來存儲(chǔ)緩存的數(shù)據(jù)，二級(jí)索引，臟數(shù)據(jù)(已經(jīng)被更改但沒有刷新到硬盤的數(shù)據(jù))以及各種內(nèi)部結(jié)構(gòu)如自適應(yīng)哈希索引。
根據(jù)經(jīng)驗(yàn)，在一個(gè)獨(dú)立的MySQL服務(wù)器應(yīng)該分配給MySQL整個(gè)機(jī)器總內(nèi)存的80%。如果你的MySQL運(yùn)行在一個(gè)共享服務(wù)器，或者你想知道InnoDB緩沖池大小是否正確設(shè)置，詳細(xì)請(qǐng)看這里。
3.INNODB_LOG_FILE_SIZE
InnoDB重做日志文件的設(shè)置在MySQL社區(qū)也叫做事務(wù)日志。直到MySQL 5.6.8事務(wù)日志默認(rèn)值innodb_log_file_size=5M是唯一最大的InnoDB性能殺手。從MySQL 5.6.8開始，默認(rèn)值提升到48M,但對(duì)于許多稍繁忙的系統(tǒng)，還遠(yuǎn)遠(yuǎn)要低。
根據(jù)經(jīng)驗(yàn)，你應(yīng)該設(shè)置的日志大小能在你服務(wù)器繁忙時(shí)能存儲(chǔ)1-2小時(shí)的寫入量。如果不想這么麻煩，那么設(shè)置1-2G的大小會(huì)讓你的性能有一個(gè)不錯(cuò)的表現(xiàn)。這個(gè)變量也相當(dāng)重要，更詳細(xì)的介紹請(qǐng)看這里。
當(dāng)然，如果你有大量的大事務(wù)更改，那么，更改比默認(rèn)innodb日志緩沖大小更大的值會(huì)對(duì)你的性能有一定的提高，但是你使用的是autocommit，或者你的事務(wù)更改小于幾k，那還是保持默認(rèn)的值吧。
4.INNODB_FLUSH_LOG_AT_TRX_COMMIT
默認(rèn)下，innodb_flush_log_at_trx_commit設(shè)置為1表示InnoDB在每次事務(wù)提交后立即刷新同步數(shù)據(jù)到硬盤。如果你使用autocommit，那么你的每一個(gè)INSERT, UPDATE或DELETE語句都是一個(gè)事務(wù)提交。
同步是一個(gè)昂貴的操作(特別是當(dāng)你沒有寫回緩存時(shí))，因?yàn)樗婕皩?duì)硬盤的實(shí)際同步物理寫入。所以如果可能，并不建議使用默認(rèn)值。
兩個(gè)可選的值是0和2:
* 0表示刷新到硬盤，但不同步(提交事務(wù)時(shí)沒有實(shí)際的IO操作)
* 2表示不刷新和不同步(也沒有實(shí)際的IO操作)
所以你如果設(shè)置它為0或2，則同步操作每秒執(zhí)行一次。所以明顯的缺點(diǎn)是你可能會(huì)丟失上一秒的提交數(shù)據(jù)。具體來說，你的事務(wù)已經(jīng)提交了，但服務(wù)器馬上斷電了，那么你的提交相當(dāng)于沒有發(fā)生過。
顯示的，對(duì)于金融機(jī)構(gòu)，如銀行，這是無法忍受的。不過對(duì)于大多數(shù)網(wǎng)站，可以設(shè)置為innodb_flush_log_at_trx_commit=0|2，即使服務(wù)器最終崩潰也沒有什么大問題。畢竟，僅僅在幾年前有許多網(wǎng)站還是用MyISAM，當(dāng)崩潰時(shí)會(huì)丟失30s的數(shù)據(jù)(更不要提那令人抓狂的慢修復(fù)進(jìn)程)。
那么，0和2之間的實(shí)際區(qū)別是什么？性能明顯的差異是可以忽略不計(jì)，因?yàn)樗⑿碌讲僮飨到y(tǒng)緩存的操作是非常快的。所以很明顯應(yīng)該設(shè)置為0，萬一MySQL崩潰(不是整個(gè)機(jī)器)，你不會(huì)丟失任何數(shù)據(jù)，因?yàn)閿?shù)據(jù)已經(jīng)在OS緩存，最終還是會(huì)同步到硬盤的。
5.SYNC_BINLOG
已經(jīng)有大量的文檔寫到sync_binlog，以及它和innodb_flush_log_at_trx_commit的關(guān)系，下面我們來簡(jiǎn)單的介紹下：
a) 如果你的服務(wù)器沒有設(shè)置從服務(wù)器，而且你不做備份，那么設(shè)置sync_binlog=0將對(duì)性能有好處。
b) 如果你有從服務(wù)器并且做備份，但你不介意當(dāng)主服務(wù)器崩潰時(shí)在二進(jìn)制日志丟失一些事件，那么為了更好的性能還是設(shè)置為sync_binlog=0.
c) 如果你有從服務(wù)器并且備份，你非常在意從服務(wù)器的一致性，以及能及時(shí)恢復(fù)到一個(gè)時(shí)間點(diǎn)(通過使用最新的一致性備份和二進(jìn)制日志將數(shù)據(jù)庫恢復(fù)到特定時(shí)間點(diǎn)的能力)，那么你應(yīng)該設(shè)置innodb_flush_log_at_trx_commit=1，并且需要認(rèn)真考慮使用sync_binlog=1。
問題是sync_binlog=1代價(jià)比較高 – 現(xiàn)在每個(gè)事務(wù)也要同步一次到硬盤。你可能會(huì)想為什么不把兩次同步合并成一次，想法正確 – 新版本的MySQL(5.6和5.7，MariaDB和Percona Server)已經(jīng)能合并提交，那么在這種情況下sync_binlog=1的操作也不是這么昂貴了，但在舊的mysql版本中仍然會(huì)對(duì)性能有很大影響。
6.INNODB_FLUSH_METHOD
將innodb_flush_method設(shè)置為O_DIRECT以避免雙重緩沖.唯一一種情況你不應(yīng)該使用O_DIRECT是當(dāng)你操作系統(tǒng)不支持時(shí)。但如果你運(yùn)行的是Linux，使用O_DIRECT來激活直接IO。
不用直接IO，雙重緩沖將會(huì)發(fā)生，因?yàn)樗械臄?shù)據(jù)庫更改首先會(huì)寫入到OS緩存然后才同步到硬盤 – 所以InnoDB緩沖池和OS緩存會(huì)同時(shí)持有一份相同的數(shù)據(jù)。特別是如果你的緩沖池限制為總內(nèi)存的50%，那意味著在寫密集的環(huán)境中你可能會(huì)浪費(fèi)高達(dá)50%的內(nèi)存。如果沒有限制為50%，服務(wù)器可能由于OS緩存的高壓力會(huì)使用到swap。
簡(jiǎn)單地說，設(shè)置為innodb_flush_method=O_DIRECT。
7.INNODB_BUFFER_POOL_INSTANCES
MySQL 5.5引入了緩沖實(shí)例作為減小內(nèi)部鎖爭(zhēng)用來提高M(jìn)ySQL吞吐量的手段。
在5.5版本這個(gè)對(duì)提升吞吐量幫助很小，然后在MySQL 5.6版本這個(gè)提升就非常大了，所以在MySQL5.5中你可能會(huì)保守地設(shè)置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以設(shè)置為8-16個(gè)緩沖池實(shí)例。
你設(shè)置后觀察會(huì)覺得性能提高不大，但在大多數(shù)高負(fù)載情況下，它應(yīng)該會(huì)有不錯(cuò)的表現(xiàn)。
對(duì)了，不要指望這個(gè)設(shè)置能減少你單個(gè)查詢的響應(yīng)時(shí)間。這個(gè)是在高并發(fā)負(fù)載的服務(wù)器上才看得出區(qū)別。比如多個(gè)線程同時(shí)做許多事情。
8.INNODB_THREAD_CONCURRENCY
InnoDB有一種方法來控制并行執(zhí)行的線程數(shù) – 我們稱為并發(fā)控制機(jī)制。大部分是由innodb_thread_concurrency值來控制的。如果設(shè)置為0，并發(fā)控制就關(guān)閉了，因此InnoDB會(huì)立即處理所有進(jìn)來的請(qǐng)求(盡可能多的)。
在你有32CPU核心且只有4個(gè)請(qǐng)求時(shí)會(huì)沒什么問題。不過想像下你只有4CPU核心和32個(gè)請(qǐng)求時(shí) – 如果你讓32個(gè)請(qǐng)求同時(shí)處理，你這個(gè)自找麻煩。因?yàn)檫@些32個(gè)請(qǐng)求只有4 CPU核心，顯然地會(huì)比平常慢至少8倍(實(shí)際上是大于8倍)，而然這些請(qǐng)求每個(gè)都有自己的外部和內(nèi)部鎖，這有很大可能堆積請(qǐng)求。
下面介紹如何更改這個(gè)變量，在mysql命令行提示符執(zhí)行：
對(duì)于大多數(shù)工作負(fù)載和服務(wù)器，設(shè)置為8是一個(gè)好開端，然后你可以根據(jù)服務(wù)器達(dá)到了這個(gè)限制而資源使用率利用不足時(shí)逐漸增加?？梢酝ㄟ^show engine innodb status\G來查看目前查詢處理情況，查找類似如下行：
9.SKIP_NAME_RESOLVE
這一項(xiàng)不得不提及，因?yàn)槿匀挥泻芏嗳藳]有添加這一項(xiàng)。你應(yīng)該添加skip_name_resolve來避免連接時(shí)DNS解析。
大多數(shù)情況下你更改這個(gè)會(huì)沒有什么感覺，因?yàn)榇蠖鄶?shù)情況下DNS服務(wù)器解析會(huì)非常快。不過當(dāng)DNS服務(wù)器失敗時(shí)，它會(huì)出現(xiàn)在你服務(wù)器上出現(xiàn)“unauthenticated connections” ，而就是為什么所有的請(qǐng)求都突然開始慢下來了。
所以不要等到這種事情發(fā)生才更改?，F(xiàn)在添加這個(gè)變量并且避免基于主機(jī)名的授權(quán)。
10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX
* innodb_io_capacity：用來當(dāng)刷新臟數(shù)據(jù)時(shí)，控制MySQL每秒執(zhí)行的寫IO量。
* innodb_io_capacity_max: 在壓力下，控制當(dāng)刷新臟數(shù)據(jù)時(shí)MySQL每秒執(zhí)行的寫IO量
首先，這與讀取無關(guān) – SELECT查詢執(zhí)行的操作。對(duì)于讀操作，MySQL會(huì)盡最大可能處理并返回結(jié)果。至于寫操作，MySQL在后臺(tái)會(huì)循環(huán)刷新，在每一個(gè)循環(huán)會(huì)檢查有多少數(shù)據(jù)需要刷新，并且不會(huì)用超過innodb_io_capacity指定的數(shù)來做刷新操作。這也包括更改緩沖區(qū)合并（在它們刷新到磁盤之前，更改緩沖區(qū)是輔助臟頁存儲(chǔ)的關(guān)鍵）。
第二，我需要解釋一下什么叫“在壓力下”，MySQL中稱為”緊急情況”，是當(dāng)MySQL在后臺(tái)刷新時(shí)，它需要刷新一些數(shù)據(jù)為了讓新的寫操作進(jìn)來。然后，MySQL會(huì)用到innodb_io_capacity_max。
那么，應(yīng)該設(shè)置innodb_io_capacity和innodb_io_capacity_max為什么呢？
最好的方法是測(cè)量你的存儲(chǔ)設(shè)置的隨機(jī)寫吞吐量，然后給innodb_io_capacity_max設(shè)置為你的設(shè)備能達(dá)到的最大IOPS。innodb_io_capacity就設(shè)置為它的50-75%，特別是你的系統(tǒng)主要是寫操作時(shí)。
通常你可以預(yù)測(cè)你的系統(tǒng)的IOPS是多少。例如由8 15k硬盤組成的RAID10能做大約每秒1000隨機(jī)寫操作，所以你可以設(shè)置innodb_io_capacity=600和innodb_io_capacity_max=1000。許多廉價(jià)企業(yè)SSD可以做4,000-10,000 IOPS等。
這個(gè)值設(shè)置得不完美問題不大。但是，要注意默認(rèn)的200和400會(huì)限制你的寫吞吐量，因此你可能偶爾會(huì)捕捉到刷新進(jìn)程。如果出現(xiàn)這種情況，可能是已經(jīng)達(dá)到你硬盤的寫IO吞吐量，或者這個(gè)值設(shè)置得太小限制了吞吐量。
11.INNODB_STATS_ON_METADATA
如果你跑的是MySQL 5.6或5.7，你不需要更改innodb_stats_on_metadata的默認(rèn)值，因?yàn)樗呀?jīng)設(shè)置正確了。
不過在MySQL 5.5或5.1，強(qiáng)烈建議關(guān)閉這個(gè)變量 – 如果是開啟，像命令show table status會(huì)立即查詢INFORMATION_SCHEMA而不是等幾秒再執(zhí)行，這會(huì)使用到額外的IO操作。
從5.1.32版本開始，這個(gè)是動(dòng)態(tài)變量，意味著你不需要重啟MySQL服務(wù)器來關(guān)閉它。
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這兩個(gè)變量與性能無關(guān)，不過如果你偶爾重啟mysql服務(wù)器(如生效配置)，那么就有關(guān)。當(dāng)兩個(gè)都激活時(shí)，MySQL緩沖池的內(nèi)容(更具體地說，是緩存頁)在停止MySQL時(shí)存儲(chǔ)到一個(gè)文件。當(dāng)你下次啟動(dòng)MySQL時(shí)，它會(huì)在后臺(tái)啟動(dòng)一個(gè)線程來加載緩沖池的內(nèi)容以提高預(yù)熱速度到3-5倍。
兩件事：
第一，它實(shí)際上沒有在關(guān)閉時(shí)復(fù)制緩沖池內(nèi)容到文件，僅僅是復(fù)制表空間ID和頁面ID – 足夠的信息來定位硬盤上的頁面了。然后它就能以大量的順序讀非?？焖俚募虞d那些頁面，而不是需要成千上萬的小隨機(jī)讀。
第二，啟動(dòng)時(shí)是在后臺(tái)加載內(nèi)容，因?yàn)镸ySQL不需要等到緩沖池內(nèi)容加載完成再開始接受請(qǐng)求(所以看起來不會(huì)有什么影響)。
從MySQL 5.7.7開始，默認(rèn)只有25%的緩沖池頁面在mysql關(guān)閉時(shí)存儲(chǔ)到文件，但是你可以控制這個(gè)值 – 使用innodb_buffer_pool_dump_pct，建議75-100。
這個(gè)特性從MySQL 5.6才開始支持。
13.INNODB_ADAPTIVE_HASH_INDEX_PARTS
如果你運(yùn)行著一個(gè)大量SELECT查詢的MySQL服務(wù)器(并且已經(jīng)盡可能優(yōu)化)，那么自適應(yīng)哈希索引將下你的下一個(gè)瓶頸。自適應(yīng)哈希索引是InnoDB內(nèi)部維護(hù)的動(dòng)態(tài)索引，可以提高最常用的查詢模式的性能。這個(gè)特性可以重啟服務(wù)器關(guān)閉，不過默認(rèn)下在mysql的所有版本開啟。
這個(gè)技術(shù)非常復(fù)雜，在大多數(shù)情況下它會(huì)對(duì)大多數(shù)類型的查詢直到加速的作用。不過，當(dāng)你有太多的查詢往數(shù)據(jù)庫，在某一個(gè)點(diǎn)上它會(huì)花過多的時(shí)間等待AHI鎖和閂鎖。
如果你的是MySQL 5.7，沒有這個(gè)問題 – innodb_adaptive_hash_index_parts默認(rèn)設(shè)置為8，所以自適應(yīng)哈希索引被切割為8個(gè)分區(qū)，因?yàn)椴淮嬖谌只コ狻?/p>
不過在mysql 5.7前的版本，沒有AHI分區(qū)數(shù)量的控制。換句話說，有一個(gè)全局互斥鎖來保護(hù)AHI，可能導(dǎo)致你的select查詢經(jīng)常撞墻。
所以如果你運(yùn)行的是5.1或5.6，并且有大量的select查詢，最簡(jiǎn)單的方案就是切換成同一版本的Percona Server來激活A(yù)HI分區(qū)。
14.QUERY_CACHE_TYPE
如果人認(rèn)為查詢緩存效果很好，肯定應(yīng)該使用它。好吧，有時(shí)候是有用的。不過這個(gè)只在你在低負(fù)載時(shí)有用，特別是在低負(fù)載下大多數(shù)是讀取，小量寫或者沒有。
如果是那樣的情況，設(shè)置query_cache_type=ON和query_cache_size=256M就好了。不過記住不能把256M設(shè)置更高的值了，否則會(huì)由于查詢緩存失效時(shí)，導(dǎo)致引起嚴(yán)重的服務(wù)器停頓。
如果你的MySQL服務(wù)器高負(fù)載動(dòng)作，建議設(shè)置query_cache_size=0和query_cache_type=OFF，并重啟服務(wù)器生效。那樣Mysql就會(huì)停止在所有的查詢使用查詢緩存互斥鎖。
15.TABLE_OPEN_CACHE_INSTANCES
從MySQL 5.6.6開始，表緩存能分割到多個(gè)分區(qū)。
表緩存用來存放目前已打開表的列表，當(dāng)每一個(gè)表打開或關(guān)閉互斥體就被鎖定 – 即使這是一個(gè)隱式臨時(shí)表。使用多個(gè)分區(qū)絕對(duì)減少了潛在的爭(zhēng)用。
從MySQL 5.7.8開始，table_open_cache_instances=16是默認(rèn)的配置。
歡迎做Java的工程師朋友們私信我資料免費(fèi)獲取免費(fèi)的Java架構(gòu)學(xué)習(xí)資料（里面有高可用、高并發(fā)、高性能及分布式、Jvm性能調(diào)優(yōu)、Spring源碼，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多個(gè)知識(shí)點(diǎn)的架構(gòu)資料）
其中覆蓋了互聯(lián)網(wǎng)的方方面面，期間碰到各種產(chǎn)品各種場(chǎng)景下的各種問題，很值得大家借鑒和學(xué)習(xí)，擴(kuò)展自己的技術(shù)廣度和知識(shí)面。
Show Profile 是mysql提供可以用來分析 當(dāng)前會(huì)話 中語句執(zhí)行的資源消耗情況，可以用于Sql調(diào)優(yōu)的測(cè)量。
請(qǐng)讀者繼續(xù)看前面的圖 SQL執(zhí)行具體細(xì)節(jié) ，左邊 Status 列展示了一條SQL執(zhí)行的從開始到清理的整個(gè)生命周期中執(zhí)行的操作。如果在其生命周期階段出現(xiàn)如下的情況的就要重視了：
開啟 Profiling 后，mysql會(huì)留下15條最近執(zhí)行的sql的 現(xiàn)場(chǎng) , 便于我們發(fā)現(xiàn)問題。
Show profiles 用來查最近的15條。
Show profile 用來展示每一個(gè)SQL執(zhí)行階段的耗時(shí)清單，便于我們發(fā)現(xiàn)耗時(shí)最多的地方，然后以此為依據(jù)查找問題所在，最后優(yōu)化SQL或者優(yōu)化mysql參數(shù)。比如耗時(shí)清單創(chuàng)建了臨時(shí)表，就要考慮表是否創(chuàng)建索引，如果創(chuàng)建了那么是否沒有用到或者失效了。
總的來說 Profiling 是一個(gè)很不錯(cuò)的mysql性能分析工具。
限流算法目前程序開發(fā)過程常用的限流算法有兩個(gè)：漏桶算法和令牌桶算法。
漏桶算法
漏桶算法的原理比較簡(jiǎn)單，請(qǐng)求進(jìn)入到漏桶中，漏桶以一定的速率漏水。當(dāng)請(qǐng)求過多時(shí)，水直接溢出。可以看出，漏桶算法可以強(qiáng)制限制數(shù)據(jù)的傳輸速度。如圖所示，把請(qǐng)求比作是水滴，水先滴到桶里，通過漏洞并以限定的速度出水，當(dāng)水來得過猛而出水不夠快時(shí)就會(huì)導(dǎo)致水直接溢出，即拒絕服務(wù)。
圖片來自網(wǎng)絡(luò)
漏桶的出水速度是恒定的，那么意味著如果瞬時(shí)大流量的話，將有大部分請(qǐng)求被丟棄掉（也就是所謂的溢出）。
令牌桶算法
令牌桶算法的原理是系統(tǒng)以一定速率向桶中放入令牌，如果有請(qǐng)求時(shí)，請(qǐng)求會(huì)從桶中取出令牌，如果能取到令牌，則可以繼續(xù)完成請(qǐng)求，否則等待或者拒絕服務(wù)。這種算法可以應(yīng)對(duì)突發(fā)程度的請(qǐng)求，因此比漏桶算法好。
圖片來自網(wǎng)絡(luò)
漏桶算法和令牌桶算法的選擇
兩者的主要區(qū)別漏桶算法能夠強(qiáng)行限制處理數(shù)據(jù)的速率，不論系統(tǒng)是否空閑。而令牌桶算法能夠在限制數(shù)據(jù)的平均處理速率的同時(shí)還允許某種程度的突發(fā)流量。如何理解上面的含義呢？漏桶算法，比如系統(tǒng)吞吐量是 120/s，業(yè)務(wù)請(qǐng)求 130/s，使用漏斗限流 100/s，起到限流的作用，多余的請(qǐng)求將產(chǎn)生等待或者丟棄。對(duì)于令牌桶算法，每秒產(chǎn)生 100 個(gè)令牌，系統(tǒng)容量 200 個(gè)令牌。正常情況下，業(yè)務(wù)請(qǐng)求 100/s 時(shí)，請(qǐng)求能被正常被處理。當(dāng)有突發(fā)流量過來比如 200 個(gè)請(qǐng)求時(shí)，因?yàn)橄到y(tǒng)容量有 200 個(gè)令牌可以同一時(shí)刻處理掉這 200 個(gè)請(qǐng)求。如果是漏桶算法，則只能處理 100 個(gè)請(qǐng)求，其他的請(qǐng)求等待或者被丟棄。
如何提高M(jìn)ySQL Limit查詢的性能?
在MySQL數(shù)據(jù)庫操作中，我們?cè)谧鲆恍┎樵兊臅r(shí)候總希望能避免數(shù)據(jù)庫引擎做全表掃描，因?yàn)槿頀呙钑r(shí)間長，而且其中大部分掃描對(duì)客戶端而言是沒有意義的。其實(shí)我們可以使用Limit關(guān)鍵字來避免全表掃描的情況，從而提高效率。
有個(gè)幾千萬條記錄的表 on MySQL 5.0.x，現(xiàn)在要讀出其中幾十萬萬條左右的記錄。常用方法，依次循環(huán)：
select * from mytable where index_col = xxx limit offset, limit;
經(jīng)驗(yàn)：如果沒有blob/text字段，單行記錄比較小，可以把 limit 設(shè)大點(diǎn)，會(huì)加快速度。
問題：頭幾萬條讀取很快，但是速度呈線性下降，同時(shí) mysql server cpu 99% ，速度不可接受。
調(diào)用 explain select * from mytable where index_col = xxx limit offset, limit;
顯示 type = ALL
在 MySQL optimization 的文檔寫到"All"的解釋
A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.
看樣子對(duì)于 all, mysql 就使用比較笨的方法，那就改用 range 方式？ 因?yàn)?id 是遞增的，也很好修改 sql 。
select * from mytable where id offset and id offset + limit and index_col = xxx
explain 顯示 type = range，結(jié)果速度非常理想，返回結(jié)果快了幾十倍。
Limit語法：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
LIMIT子句可以被用于強(qiáng)制 SELECT 語句返回指定的記錄數(shù)。LIMIT接受一個(gè)或兩個(gè)數(shù)字參數(shù)。參數(shù)必須是一個(gè)整數(shù)常量。
如果給定兩個(gè)參數(shù)，第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量，第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目。初始記錄行的偏移量是 0(而不是 1)。
為了與 PostgreSQL 兼容，MySQL 也支持句法：LIMIT # OFFSET #。
mysql SELECT * FROM table LIMIT 5,10; //檢索記錄行6-15
//為了檢索從某一個(gè)偏移量到記錄集的結(jié)束所有的記錄行，可以指定第二個(gè)參數(shù)為-1
mysql SELECT * FROM table LIMIT 95,-1; //檢索記錄行96-last
//如果只給定一個(gè)參數(shù)，它表示返回最大的記錄行數(shù)目，換句話說，LIMIT n 等價(jià)于 LIMIT 0,n
mysql SELECT * FROM table LIMIT 5; //檢索前5個(gè)記錄行
MySQL的limit給分頁帶來了極大的方便，但數(shù)據(jù)量一大的時(shí)候，limit的性能就急劇下降。同樣是取10條數(shù)據(jù)，下面兩句就不是一個(gè)數(shù)量級(jí)別的。
select * from table limit 10000,10
select * from table limit 0,10
文中不是直接使用limit，而是首先獲取到offset的id然后直接使用limit size來獲取數(shù)據(jù)。根據(jù)他的數(shù)據(jù)，明顯要好于直接使用limit。
這里我具體使用數(shù)據(jù)分兩種情況進(jìn)行測(cè)試。
1、offset比較小的時(shí)候：
select * from table limit 10,10
//多次運(yùn)行，時(shí)間保持在0.0004-0.0005之間
Select * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10
//多次運(yùn)行，時(shí)間保持在0.0005-0.0006之間，主要是0.0006
結(jié)論：偏移offset較小的時(shí)候，直接使用limit較優(yōu)。這個(gè)顯然是子查詢的原因。
2、offset大的時(shí)候：
select * from table limit 10000,10
//多次運(yùn)行，時(shí)間保持在0.0187左右
Select * From table Where vid ＞=(Select vid From table Order By vid limit 10000,1) limit 10
//多次運(yùn)行，時(shí)間保持在0.0061左右，只有前者的1/3。可以預(yù)計(jì)offset越大，后者越優(yōu)。
用Explain語法：
explain select … from … [where ...]
1、id：這是SELECT的查詢序列號(hào)
2、select_type：select_type就是select的類型
3、table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
4、type：這列最重要，顯示了連接使用了哪種類別,有無使用索引，是使用Explain命令分析性能瓶頸的關(guān)鍵項(xiàng)之一。
5、possible_keys：列指出MySQL能使用哪個(gè)索引在該表中找到行
6、key：顯示MySQL實(shí)際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL
7、key_len：顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。使用的索引的長度。在不損失精確性的情況下，長度越短越好
8、ref：顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇行。
9、rows：顯示MySQL認(rèn)為它執(zhí)行查詢時(shí)必須檢查的行數(shù)。
10、Extra：包含MySQL解決查詢的詳細(xì)信息，也是關(guān)鍵參考項(xiàng)之一。
重慶分公司
重慶分公司
