高性能MySQL第三本筆記總結（上）

  • SQL語句的執(zhí)行流程

    無論何時，只要有多個查詢需要在同一個時刻修改數(shù)據(jù)時，就會有并發(fā)問題。MySql主要在服務器層與存儲引擎層進行并發(fā)控制。

    假設數(shù)據(jù)庫中國一張郵箱表，每個郵件都是一條記錄。如果某個客戶正在讀取郵箱，同時其他客戶試圖在刪除郵箱表中的某一條數(shù)據(jù)。這個時候，讀取的結構就是不確定的了。在MySql中會通過鎖定防止其它用戶讀取同一數(shù)據(jù)。大多數(shù)時候，MySQL鎖的內(nèi)部管理都是透明的。

MySQL鎖的粒度

  • 每種MySql引擎都可以實現(xiàn)自己的鎖策略和鎖粒度，將鎖粒度固定在某個級別，可以為某些特定的場景提供更好的性能。

表鎖(table lock)

  • 表鎖是mysql中最基本的鎖略，并且是開銷最小的策略。它會鎖定整個表，一個用戶在對表進行寫操作(插入、刪除、更新等)前，需要先獲得寫鎖，這會阻塞其他用戶對該表的所有讀寫操作。只有沒有寫鎖時,其他讀取的用戶才能獲得讀鎖，讀鎖之間是不相互阻塞的。
  • 在特定的場景中，表鎖也可能有良好的性能。例如，READ L0CAL 表鎖支持某些類型的并發(fā)寫操作。另外，寫鎖也比讀鎖有更高的優(yōu)先級，因此-一個寫鎖請求可能會被插入到讀鎖隊列的前面(寫鎖可以插入到鎖隊列中讀鎖的前面，反之讀鎖則不能插入到寫鎖的前面)。
  • 盡管存儲引擎可以管理自己的鎖，MySQL本身還是會使用各種有效的表鎖來實現(xiàn)不同的目的。例如，服務器會為諸如ALTER TABLE之類的語句使用表鎖，而忽略存儲引擎的鎖機制。

行級鎖(row lock)

  • 行級鎖可以最大程度地支持并發(fā)處理(同時也帶來了最大的鎖開銷)。
  • 在InnoDB和XtraDB,以及其他一些存儲引擎中實現(xiàn)了行級鎖。
  • 行級鎖只在存儲引擎層實現(xiàn)，而MySQL服務器層沒有實現(xiàn)。服務器層完全不了解存儲引擎中的鎖實現(xiàn)。

MySQL的事務

事務特性

  • A（原子性）事務的各步操作是不可分的，保證一系列的操作要么都完成，要么都不完成；
  • C（一致性）事務完成，數(shù)據(jù)必須處于一致的狀態(tài)；
  • I（隔離性）對數(shù)據(jù)進行修改的所有并發(fā)事務彼此之間是相互隔離，這表明事務必須是獨立的，不應以任何方式依賴或影響其他事務；
  • D（持久性）表示事務對數(shù)據(jù)處理結束后，對數(shù)據(jù)更改必須持久化，不管是事務成功還是回滾。事務日志都能夠保持事務的永久性。

事務的隔離級別

  • SQL標準的事務隔離級別包括：讀未提交（read uncommitted）、讀提交（read committed）、可重復讀（repeatable read）和串行化（serializable ）
  • 讀未提交是指，一個事務還沒提交時，它做的變更就能被別的事務看到。
  • 讀提交是指，一個事務提交之后，它做的變更才會被其他事務看到。
  • 可重復讀是指，一個事務執(zhí)行過程中看到的數(shù)據(jù)，總是跟這個事務在啟動時看到的數(shù)據(jù)是一致的。未提交的更改對其他事務是不可見的
  • 串行化:對應一個記錄會加讀寫鎖，出現(xiàn)沖突的時候，后訪問的事務必須等前一個事務執(zhí)行完成才能繼續(xù)執(zhí)行。最高的隔離級別

MySQL中的事務

  • MySQL提供了兩種事務型的存儲引擎: InnoDB和NDB Cluster。另外還有一些第三方存儲引擎也支持事
  • MySQL默認采用自動提交(AUTOCOMIT) 模式。如果不是顯式地開始-一個個事務，則每個查詢都被當作一事務執(zhí)行提交操作。在當前連接中，可以通過設置AUTOCOMMIT變量來啟用或者禁用自動提交模式:
  • InnoDB采用的是兩階段鎖定協(xié)議(two-phase locking protocol)。在事務執(zhí)行過程中，隨時都可以執(zhí)行鎖定，鎖只有在執(zhí)行COMMIT或者ROLLBACK的時候才會釋放，并且所有的鎖是在同一時刻被釋放。
  • InnoDB也支持通過特定的語句進行顯式鎖定SELECT ... LOCK IN SHARE MODE和SELECT FOR UPDATE 些語句不屬于SQL規(guī)范

多版本并發(fā)控制MVCC

  • MVCC是行級鎖的一個變種，但是它在很多情況下避免了加鎖操作，因此開銷更低。雖然實現(xiàn)機制有所不同，但大都實現(xiàn)了非阻塞的讀操作，寫操作也只鎖定必要的行。
  • MVCC的實現(xiàn)，是通過保存數(shù)據(jù)在某個時間點的快照來實現(xiàn)的。也就是說，不管需要執(zhí)行多長時間，每個事務看到的數(shù)據(jù)都是一致的。根據(jù)事務開始的時間不同，每個事務對同張表，同一時刻看到的數(shù)據(jù)可能是不一樣的。
  • InnoDB的MVCC,是通過在每行記錄后面保存兩個隱藏的列來實現(xiàn)的。這兩個列，一個保存了行的創(chuàng)建時間，一個保存行的過期時間(或刪除時間)。當然存儲的并不是實際的時間值，而是系統(tǒng)版本號。每開始一個新的事務，系統(tǒng)版本號都會自動遞增。事務開始時刻的系統(tǒng)版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較。
  • MVCC只在可重復讀和讀提交的隔離級別生效。其它兩個級別都不兼容

在可重復讀(REPEATABLE READ)隔離級別下，MVCC具體是如何操作的。

SELECT查詢操作時

InnoDB會根據(jù)以下兩個條件檢查每行記錄:

  • InnoDB只查找版本早于當前事務版本的數(shù)據(jù)行(也就是,行的系統(tǒng)版本號小于或等于事務的系統(tǒng)版本號),這樣可以確保事務讀取的行，要么是在事務開始前已經(jīng)存在的，要么是事務自身插入或者修改過的。
  • 行的刪除版本要么未定義，要么大于當前事務版本號。這可以確保事務讀取到的行，在事務開始之前未被刪除。

INSERT

  • InnoDB為新播入的每-一行保存當前系統(tǒng)版本號作為行版本號。

DELETE

  • InnoDB為刪除的每-*行保存當前系統(tǒng)版本號作為行刪除標識。

UPDATE

  • InnoDB為插入-行新記錄，保存當前系統(tǒng)版本號作為行版本號，同時保存當前系統(tǒng)版本號到原來的行作為行刪除標識。

數(shù)據(jù)庫存儲引擎

InnDB存儲引擎

  • InnDB是Mysql默認的事務型存儲引擎。它被設計用來處理大量的短期(short-lived) 事務，短期事務大部分情況是正常提交的，很少會被回滾。InnoDB的性能和自動崩潰恢復特性，使得它在非事務型存儲的需求中也很流行
  • InnoDB的數(shù)據(jù)存儲在表空間(tablespace) 中，表空間是由InnoDB管理的-個黑盒子，由一系列的數(shù)據(jù)文件組成。
  • InnoDB采用MVCC來支持高并發(fā)，并且實現(xiàn)了四個標準的隔離級別。其默認級別是REPEATABLE READ (可重復讀) ,并且通過間隙鎖(next-key locking)策略防止幻讀的出現(xiàn)。間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，以防止幻影行的插入。
  • InnoDB表是基于聚簇索引建立的。聚簇素引對主鍵查詢有很高的性能，不過它的二級索引(secondary index，非主鍵索引)中必須包含主鍵列，所以如果主鍵列很大的話，其他的所有索引都會很大。因此，若表上的索引較多的話，主鍵應當盡可能的小。
  • InnoDB內(nèi)部做了很多優(yōu)化，包括從磁盤讀取數(shù)據(jù)時采用的可預測性預讀，能夠自動在內(nèi)存中創(chuàng)建hash索引以加速讀操作的自適應哈希索引(adaptive hash index),以及能夠加速插入操作的插入緩沖區(qū)(insert buffer)等

MyISAM引擎

  • MyISAM不支持事務和行鎖，在MySQL5.1之前的版本是默認的存儲引擎，有一個缺陷是崩潰后無法恢復。
  • 優(yōu)點是對于只讀的數(shù)據(jù)，或者表比較小，可以忍受修復操作，可以繼續(xù)使用
  • MyISAM會將表存儲在兩個文件中:數(shù)據(jù)文件和索引文件，分別以.MYD和.MYI為擴展名
  • MyISAM表可以包含動態(tài)或者靜態(tài)(長度固定)行。MySQL會根據(jù)表的定義來決定采用何種行格式。MyISAM表可以存儲的行記錄數(shù)，一般受限于可用的磁盤空間,或者操作系統(tǒng)中單個文件的最大尺寸。
  • MyISAM對整張表加鎖，而不是針對行。讀取時會對需要讀到的所有表加共享鎖,寫入時則對表加排他鎖。但是在表有讀取查詢的同時，也可以往表中插入新的記錄(這被稱為并發(fā)插入)

Archive引擎

  • Archive引擎會緩存所有的寫并利用zlib對插人的行進行壓縮，所以比MyISAM表的磁盤I/O更少。但是每次SELECT查詢都需要執(zhí)行全表掃描。所以Archive表適合日志和數(shù)據(jù)采集類應用，這類應用做數(shù)據(jù)分析時往往需要全表掃描。或者在一- 些需要更快速的INSERT操作的場合下也可以使用。
  • Archive引擎支持行級鎖和專用的緩沖區(qū)，所可以實現(xiàn)高并發(fā)的插人。在一個查詢開始直到返回表中存在的所有行數(shù)之前，Archive引擎會阻止其他的SELECT執(zhí)行，以實現(xiàn)一致性讀。另外，也實現(xiàn)了批量插入在完成之前對讀操作是不可見的。這種機制模仿了事務和MVCC的一些特性，但Archive引擎不是一個事務型的引擎，而是-一個針對高速插人和壓縮做了優(yōu)化的簡單引擎。

Blackhole引擎

  • Blackhole引擎沒有實現(xiàn)任何的存儲機制，它會丟棄所有插入的數(shù)據(jù)，不做任何保存。但是服務器會記錄Blackhole表的日志，所以可以用于復制數(shù)據(jù)到備庫，或者只是簡單地記錄到日志。這種特殊的存儲引擎可以在--些特殊的復制架構和8志審核時發(fā)揮作用。但這種應用方式我們碰到過很多問題，因此并不推薦。

CSV引擎

  • CSV引擎可以將普通的CSV文件(逗號分割值的文件)作為MySQL的表來處理，但這種表不支持索引。CSV引擎可以在數(shù)據(jù)庫運行時拷入或者拷出文件。可以將Excel等電子表格軟件中的數(shù)據(jù)存儲為CSV文件，然后復制到MySQL數(shù)據(jù)目錄下，就能在MySQL中打開使用。同樣，如果將數(shù)據(jù)寫人到一個CSV引擎表，其他的外部程序也能立即從表的數(shù)據(jù)文件中讀取CSV格式的數(shù)據(jù)。因此CSV引擎可以作為- -種數(shù)據(jù)交換的機制，非常有用。

Federated引擎

  • Federated引擎是訪問其他MySQL服務器的-一個代理，它會創(chuàng)建-一個到遠程MySQL服務器的客戶端連接，并將查詢傳輸?shù)竭h程服務器執(zhí)行，然后提取或者發(fā)送需要的數(shù)據(jù)。最初設計該存儲引擎是為了和企業(yè)級數(shù)據(jù)庫如Microsoft SQL Server和Oracle的類似特性競爭的，可以說更多的是一種市場行為。盡管該引擎看起來提供了一種很好的跨服務器的靈活性，但也經(jīng)常帶來問題，因此默認是禁用的。

Memory引擎

  • 如果需要快速地訪問數(shù)據(jù)，并且這些數(shù)據(jù)不會被修改，重啟以后丟失也沒有關系，那么使用Memory表(以前也叫做HEAP表)是非常有用的。Memory 表至少比MyISAM表要快一個數(shù)量級，因為所有的數(shù)據(jù)都保存在內(nèi)存中,不需要進行磁盤I/O。Memory 表的結構在重啟以后還會保留,但數(shù)據(jù)會丟失。

Schema(數(shù)據(jù)庫的組織和結構)與數(shù)據(jù)類型優(yōu)化

更小的通常好

  • 盡量使用可以正確存儲數(shù)據(jù)的最下數(shù)據(jù)類型，更小的數(shù)據(jù)類型通常更快，占用更小的磁盤,內(nèi)存和cpu緩存,并且處理時需要的cpu周期更少
  • 但是要確保沒有低估需要存儲的值的范圍，因為在schema中的多個地方增加數(shù)據(jù)類型的范圍是一個非常耗時和痛苦的操作。如果無法確定哪個數(shù)據(jù)類型是最好的，就選擇你認為不會超過范圍的最小類型

簡單就好

  • 簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期。例如，整型比字符操作代價更低，
  • 整型比字符操作代價更低，因為字符集和校對規(guī)則(排序規(guī)則)使字符比較比整型比較更復雜
  • 使用MySQL內(nèi)建的類型而不是字符串來存儲日期和時間
  • 用整型存儲IP地址。int類型占4個字節(jié)，tinyint占1個字節(jié)。

盡量避免NUll

  • 如果查詢中包含null的列，會使得索引，索引統(tǒng)計和值比較更復雜
  • 當可以NULL的列被索引時,每個索引記錄需要一個額外的字節(jié)

datetime和timesamp

  • datetime和timesamp都可以存儲相同的數(shù)據(jù)類型,時間和日期，精確到秒。然而timesamp只使用datetime一半的存儲空間
  • timesamp 會根據(jù)時區(qū)變化，具有特殊的自動更新能力，允許的時間范圍要小很多，有時它的特許能力會成為障礙

字段列的類型選擇

  • 在滿足值的范圍情況下，盡量選擇最小的數(shù)據(jù)類型。列如tinyint比int少三個字節(jié)。tinyint 1字節(jié) (-128，127) (0，255) 小整數(shù)值
  • 字段固定長度如手機號，身份證號用char,可變長度使用varchar因為其長度固定，方便程序的存儲與查找,付出的是空間的代價。varchar是以空間效率為首位的
  • 整數(shù)類型通常是標識列最好的數(shù)據(jù)類型，因為他們很快并且可以使AUTO_INCREMENT
  • 如果可能，盡量批量使用字符串類型，因為它們消耗更多的空間，比數(shù)字類型慢
  • 如果存儲UUID值，可以移除-符號。使用UNHEX()函數(shù)轉換為UUID的16字節(jié)的的數(shù)字，并存儲在一個binary(16)列中。取值時可以使用HEX()格式化為16進制格式

使用inet_aton和inet_ntoa處理ip地址數(shù)據(jù)

  • 插入數(shù)據(jù)前，先用inet_aton把ip地址轉為整型，可以節(jié)省空間，因為char(15) 占16字節(jié)。
  • 顯示數(shù)據(jù)時，使用inet_ntoa把整型的ip地址轉為電地址顯示即可。

總結

  • 盡量避免過度設計，例如會導致極其復雜査詢的schema設計，或者有很多列的表設計
  • 使用小而簡單的合適數(shù)據(jù)類型，除非真實數(shù)據(jù)模型中有確切的需要，否則應該盡可能地避免使用NULL值。
  • 盡量使用相同的數(shù)據(jù)類型存儲相似或相關的值，尤其是要在關聯(lián)條件中使用的列。
  • 注意可變長字符串，其在臨時表和排序時可能導致悲觀的按最大長度分配內(nèi)存。
  • 盡量使用整型定義標識列。
  • 避免使用MySQL已經(jīng)遺棄的特性，例如指定浮點數(shù)的精度，或者整數(shù)的顯示寬度。
  • 小心使用ENUM和SET。雖然它們用起來很方便，但是不要濫用，否則有時候會變成 陷阱。最好避免使用BIT。
  • ALTER TABLE是讓人痛苦的操作，因為在大部分情況下，它都會鎖表并且重建整張表。例如在備機執(zhí)行ALTER并在完成后把它切換為主庫。

創(chuàng)建高性能的索引

索引基礎

  • 索引有很多類型,Mysql是使用B+tree樹索引,索引是在引擎層實現(xiàn)而不是服務層。不同引擎的索引工作方式不一樣。
  • MyISAM使用前綴壓縮技術使得索引更小，InnoDB按照原數(shù)據(jù)根式進行存儲。MyISAM索引通過數(shù)據(jù)到物理位置引用被索引的行，InnoDB根據(jù)主鍵引用被索引的行
  • 全值匹配：指的是和索引中所有列進行匹配。比如EXPLAIN 中的type=index
  • 最左匹配原則: mysql索引規(guī)則中要求復合索引要想使用第二個索引，必須先使用第一個索引的原因。（而且第一個索引必須是等值匹配）。也就是如果使用復合索引查詢時優(yōu)先按照索引創(chuàng)建的順序進行條件查詢

索引的選擇策略

  • 索引字段必須是獨立的列，不嗯呢該是表達式和函數(shù)運算
  • 計算合適的前綴索引長度
  • 善用復合索引，而不是為每個字段都建立索引，選擇合適的索引列順序
    *當不需要排序和分組時使用頻率較高的放在復合索引前列,這時候的索引優(yōu)化用于where條件
  • 盡可能將左范圍查詢的列放在索引的后面,以便優(yōu)化器使用盡可能多的索引列。
  • 對于范圍條件查詢,mysql范圍列后面的其它的索引列,對于多個等值條件查詢則沒有這種限制

Inndb主鍵索引和非主鍵索引的區(qū)別

  • 主鍵索引即存儲了索引值，又在葉子中存儲了行的數(shù)據(jù)。所以通過主鍵查詢時效率高，一次查詢即可，不需要回表操作。這種即存儲索引值又存儲行的所有數(shù)據(jù)的結構叫做(聚簇索引）
  • 非主鍵索引存儲索引值，但葉子中存儲的是主鍵ID，所以查詢時需要進行一次回表操作才可以取到所有的行數(shù)據(jù)

索引覆蓋

  • 索引覆蓋是指如果查詢的列恰好是索引的一部分,那么查詢只需要在索引文件上進行,不需要回行到磁盤再找數(shù)據(jù).這種查詢速度非?？?稱為”索引覆蓋”

理想的索引

  • 理想的索引。1:查詢頻繁 2:區(qū)分度高 3:長度小 4:盡量能覆蓋常用查詢字段.
  • 索引長度直接影響索引文件的大小,影響增刪改的速度,并間接影響查詢速度(占用內(nèi)存多).
  • 針對列中的值,從左往右截取部分,來建索引
  • 截的越短,重復度越高,區(qū)分度越小, 索引效果越不好
    -截的越長,重復度越低,區(qū)分度越高,索引效果越好,但帶來的影響也越大--增刪改變慢,并間影響查詢速度.所以,我們要在 區(qū)分度+長度，兩者上,取得一個平衡.
  • 慣用手法:截取不同長度,并測試其區(qū)分度

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

使用索引掃描來做排序

  • mysql有兩種方式可以生成有序的結果：通過排序操作或者按索引順序掃描。如果explan出來的type值為index則說明使用了索引掃描來做排序。
  • 只有索引列的順序和order by字段的順序完全一致，并且所有列的排序方向都一樣時,mysql才能使用索引對結果進行排序。
  • 如果查詢需要關聯(lián)多表，則只有當order by中使用的字段完全是第一個表時，才能使用索引做排序。order by和查找型的限制一樣，都要滿足最左前綴要求。
  • 如果索引的第一個字段為常量時，where或join中指對這個列制定了固定的常量值，就可以不滿足最左前綴要求。比如有個復合索引(c1,c2,c3), 執(zhí)行 select * from where c1=1 order c2,c3 就可以使用到索引排序

重復索引與冗余索引

  • 重復索引: 是指 在同1個列(如age), 或者 順序相同的幾個列(age,school), 建立了多個索引,稱為重復索引,重復索引沒有任何幫助,只會增大索引文件,拖慢更新速度, 去掉.
  • 冗余索引:是指2個索引所覆蓋的列有重疊, 稱為冗余索引比如x,m,列,加索引 index x(x),index xm(x,m)x,xm索引,兩者的x列重疊了,這種情況,稱為冗余索引.
  • 甚至可以把index mx(m,x)索引也建立,mx,xm也不是重復的,因為列的順序不一樣.

索引和鎖

  • 索引可以讓查詢鎖定更少的行,Innodb在訪問行的時候?qū)ζ浼渔i，而索引可以減少Innodb訪問的行數(shù),從而減少鎖的數(shù)量

查詢性能優(yōu)化

優(yōu)化數(shù)據(jù)庫訪問

  • 確認程序是否在檢索大量超過需要的數(shù)據(jù)
  • 確認MySQL服務器層是否在分析大量超過需要的數(shù)據(jù)行
  • 是否查詢不需要的記錄，常見的錯誤是誤認為MySQL只會返回需要的數(shù)據(jù)，實際上mysql先是返回全部數(shù)據(jù)，在進行計算。一般使用limit區(qū)分
  • 多表關聯(lián)的時候返回了全部的列，只需要取出所用的列即可
  • 優(yōu)化select *操作,以及重復查詢同一條數(shù)據(jù)行做好緩存
  • 執(zhí)行語句分析，檢查MySQL是否掃描了額外的記錄

重構查詢的方式

  • 設計查詢語句時，是否要將一個復雜的查詢拆分成多個簡單的查詢
  • 切分查詢：將大查詢切分成小查詢，每個查詢功能完全一樣，但是只完成一部分數(shù)據(jù)的操作，每次返回一小部分的結果。比如在做定時清除線日志表大表數(shù)據(jù)刪除時，分批次刪除比較高效，可以大大減少刪除時鎖的持有時間
  • 分解關聯(lián)查詢:可以對每一個表進行一次單表查詢，然后將結果在程序中就行關聯(lián)匯總。查詢拆分后,執(zhí)行單個查詢可以減少鎖的競爭?？梢宰尵彺娴男矢?，許多應用可以方便緩存單表查詢對應的結果對象。

Mysql中驅(qū)動表的概念

  • mysql中指定了連接條件時，滿足查詢條件的記錄行數(shù)少的表為驅(qū)動表；如未指定查詢條件，則掃描行數(shù)少的為驅(qū)動表。mysql優(yōu)化器就是這么粗暴以小表驅(qū)動大表的方式來決定執(zhí)行順序的。
  • 有時這種優(yōu)化方式反而使表的查詢效率變低，這個時候STRAIGHT_JOIN就排上用場了

比如如下測試的sql,Table1表的FilterID字段建了索引


select t1.*
from Table1 t1
inner join Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

STRAIGHT_JOIN 方式改寫驅(qū)動表

select t1.*
from Table1 t1
STRAIGHT_JOIN  Table2 t2
on t1.CommonID = t2.CommonID
where t1.FilterID = 1

排序優(yōu)化

  • 當MySQL不能通過索引生成排序結果時，MySQL就需要自己排序，如果數(shù)據(jù)量少則在內(nèi)存中進行，如果數(shù)據(jù)量大則使用磁盤，這個過程統(tǒng)一稱為文件排序
  • 排序的數(shù)據(jù)量小于排序緩沖區(qū)，則使用內(nèi)存進行快速排序操作。如果內(nèi)存不夠，會先將數(shù)據(jù)分塊，對每個獨立的塊進行排序,并將各個塊的排序結果放在磁盤上，最后將各個排序好的塊進行合并返回結果
  • mysql在新版本中使用單次排序算法，先讀取查詢所需要的所有列，然后在根據(jù)給定的列進行排序，最后直接返回排序結果。

優(yōu)化COUNT查詢

# 改查詢需要掃描474條行數(shù)
EXPLAIN select count(*)  from  system_log20190401000001 where id >5;

# 將條件反轉一下，先查詢id<5的數(shù)據(jù)，然后利用總數(shù)去做減法也能得到結果 
EXPLAIN select (select count(*)  from  system_log20190401000001)-count(*) from system_log20190401000001  where id <=5;

優(yōu)化limit分頁

  • 在做limit分頁時通常使用偏移量加排序的方式實現(xiàn)，但是當偏移量非常大的時候性能非常低下，比如取10020條只返回最后20條，前10000條都拋棄了，一般這種方面優(yōu)化，要嗎在頁面上做大分頁數(shù)限制，要嗎優(yōu)化大偏移量的性能。
  • 優(yōu)化此類分頁查詢的最簡單辦法就是利用覆蓋索返回需要的列，防止回表操作。然后利用返回的數(shù)據(jù)做一次關聯(lián)返回所需要的其余列。
  • 有時也可以將limit查詢轉為為已知的位置進行查詢，比如betwen 10000 and 10020
  • 另一種做法就是緩存1000條數(shù)據(jù)，每次分頁從緩存中取，大于1000就在頁面額外設計按鈕找到更多數(shù)據(jù)。

優(yōu)化UNION查詢

  • UNION 操作符用于合并兩個或多個SELECT語句的結果集
  • 默認地，UNION操作符選取不同的值。如果允許重復的值，請使用 UNION ALL
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

# UNION ALL
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

  • 今天看到一位朋友寫的mysql筆記總結，覺得寫的很詳細很用心，這里轉載一下，供大家參考下，也希望大家能關注他原文地...
    信仰與初衷閱讀 4,818評論 0贊 30
  • 轉 # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    呂品?閱讀 10,107評論 0贊 44
  • 為了充分發(fā)揮MySQL的性能并順利地使用，就必須理解其設計。MySQL的靈活性體現(xiàn)在很多方面。例如，你可以通過配置...
    李文文丶閱讀 1,312評論 0贊 4
  • 一、MySQL優(yōu)化 MySQL優(yōu)化從哪些方面入手： （1）存儲層（數(shù)據(jù)） 構建良好的數(shù)據(jù)結構?？梢源蟠蟮奶嵘覀僑...
    寵辱不驚丶歲月靜好閱讀 2,633評論 1贊 8
  • 驚蟄是一年中的第三個節(jié)氣。驚蟄時節(jié)，早晚溫差大，陰雨天氣多，乍暖還寒很容易導致感冒！所以要在飲食上進行調(diào)理和經(jīng)絡的...
    冬陽桑梓閱讀 325評論 0贊 1

友情鏈接更多精彩內(nèi)容