MySQL優化那些事兒

FuyunWang發表於2019-03-02
MySql優化

MySQL的使用優化主要從優化庫表結構、使用合適的索引、優化查詢等方面考慮。

優化庫表結構

說說資料型別

字串型別

在MySQL中表示字串的型別有多種,其中常見的有Char和VarChar,BLOB和TEXT以及可以實現某些優化的ENUM。我們要認識到不同的資料型別在儲存和使用的區別,然後合理的使用就能實現優化。

關於MySQL中資料型別的介紹,可以參看我的另一篇博文: MySQL的常見操作

Char和VarChar

Char是定長型別,MySQL會根據定義的字串的長度分配足夠的空間,並且MySQL在儲存CHAR值的時候會刪除末尾所有的空格。對於經常變更的資料,一般採用CHAR來進行儲存,因為CHAR型別在變化的時候不容易產生碎片。

VARCHAR是變長型別,它比CHAR更加節省空間,但是VARCHAR在資料變化的時候容易產生碎片,所以一般用於作為不經常變化的資料的資料型別。VARCHAR需要多使用一個或者兩個額外位元組來記錄字串的長度,如果列的最大長度小於等於255位元組就用一個額外的位元組來儲存長度,否則使用兩個位元組。例如VARCHAR(10)的列需要11個位元組的儲存空間,VARCHAR(1000)的列需要1002個位元組。

注意,在5.0或者更高的版本中,MySQL在儲存或者檢索VARCHAR資料型別時保留末尾空格,但是在4.1或者更老的版本中,MySQL在儲存或者檢索VARCHAR資料型別時和CHAR一樣都是刪除末尾的空格

BLOB和TEXT

BLOB即SMALLBLOB,TEXT即SMALLTEXT。BLOB和TEXT都是為了儲存很大的資料而設計的字串資料型別,分別採用二進位制和字串的方式來儲存。

使用ENUM來代替字串型別

MySQL在儲存列舉的時候非常的緊湊,會根據列表值的數量壓縮到一個或者兩個位元組中。MySQL在內部會將每個值在列表中的位置儲存為整數,並且在表的.frm檔案中儲存”數字-字串”對映關係的”查詢表”。

例如:create table enum_test(e enum(`apple`,`banana`,`pear`));insert into enum_test(e) values(`apple`),(`banana`),(`pear`);然後我們所插入的資料在表中其實是儲存為整數的。

數字型別

MySQL可以為整數型別指定寬度,如INT(1),INT(20),但是這對大多數應用是沒有意義的。MySQL所指定的整數型別的寬度只是用於設定一些MySQL客戶端用於顯示字元的個數,對於儲存和計算而言,INT(1)和INT(20)不會限制值的合法範圍,這兩種型別都是相同的。

日期和時間型別

DateTime和TimeStamp

DATETIME和TIMESTAMP是兩種日期型別,兩種型別在MySQL中儲存資料的格式完全相同(都是yyyy-MM-dd HH:mm:ss),但是兩者也有不同之處。

DATETIME能儲存大範圍的值,從1001年到9999年,精度為秒。MySQL採用8個位元組來儲存DATETIME資料型別所包含的值。預設情況下,MySQL以一種可排序的、無歧義的格式顯示DATETIME的值。

TIMESTAMP儲存了從1970年1月1日午夜(格林尼治標準時間)以來的秒數,它和UNIX的時間戳相同。TIMESTAMP僅僅使用4個位元組的儲存空間,所以它能表示的時間範圍也比DATETIME小,只能表示從1970年到2038年。TIMESTAMP也有DATETIME沒有的特殊屬性,預設情況下,如果插入時沒有指定第一個TIMESTAMP列的值,MySQL則設定該列的值為當前時間。

採用合適的索引

索引優化

索引優化是一個很大的方面,這裡只是簡單的介紹一些基本使用,過後會推出關於索引優化與設計的專題。

索引基礎

在MySQL中,索引是在儲存引擎層而不是伺服器層實現的。Mysql中索引結構有:B-Tree索引、雜湊索引、空間資料索引(R-Tree索引)、全文索引等索引結構,不同的儲存引擎對於上述索引結構的實現不同,而且也不是所有的儲存引擎都有這5種索引結構型別。

索引型別:

MySQL中的索引型別主要有5種:

  1. 普通索引: 最基本的索引、沒有任何限制。MyIASM中預設的BTREE型別的索引。如ALTER TABLE article ADD INDEX index_title_name ON title(100);、CREATE INDEX index_name ON table(column(100)),或者直接在建立表的時候定義索引index index_title_name(title(100))
  2. 唯一索引: 索引列的值可以為空。與普通索引類似,不同之處在於索引列的值必須唯一。如ALTER TABLE article ADD UNIQU index_title_name ON title(100);、CREATE UNIQUE INDEX index_name ON table(column(100))或者直接在建立表的時候定義索引UNIQUE index_title_name(title(100))
  3. 全文索引:主要用來查詢文字中的關鍵字，而不是直接與索引中的值相比較。僅可用於 MyISAM 表，針對較大的資料，生成全文索引很耗時耗空間。如ALTER TABLE article ADD FULLTEXT index_content(content)、CREATE FULLTEXT INDEX index_content ON article(content)和FULLTEXT (content)。
  4. 主鍵索引：它是一種特殊的唯一索引，不允許有空值。
  5. 最左索引(組合索引): 組合索引可以更好的提高MySQL效率,最左索引遵循”最左索引”原則。建立複合索引時應該將最常用（頻率）作限制條件的列放在最左邊，依次遞減。

索引方法:

可以使用B樹索引的查詢:

  1. 全值匹配的查詢
  2. 匹配最左字首的查詢
  3. 匹配列字首的查詢
  4. 匹配範圍值的查詢
  5. 精確匹配左前列並且範圍匹配另外一列
  6. 覆蓋索引(只需要訪問索引而無需查詢資料行)

使用B樹索引的限制

  1. 在多列索引中,必須按照索引的最左列開始查詢,否則索引無法使用
  2. 在多列索引中,不能跳過索引中的列。比如一個三列組成的聯合索引,不能只使用第一列和第三列進行查詢而跳過第二列。
  3. not in和<>操作無法使用索引
  4. 如果查詢中有某一個列的範圍查詢,則其右邊所有的列都不能使用索引。

Hash索引的特點:

Hash索引是基於Hash表實現的,只有查詢條件精確匹配Hash索引中的所有列時,才能使用Hash索引,Hash索引只適用於等值查詢不適合模糊查詢和範圍查詢。

對於Hash索引中的所有列,儲存引擎都會為該列的每一行計算一個Hash碼,Hash索引中儲存的就是Hash碼。

使用Hash索引的限制:

  1. Hash索引必須進行二次的查詢。
  2. Hash索引無法進行排序。
  3. Hash索引不支援部分索引查詢也不支援範圍查詢。
  4. Hash索引中Hash的計算可能存在Hash衝突。

B樹索引與Hash索引在很多地方是不同的。B樹索引除了能加快資料的查詢速度之外還可以做到排序和分組,B樹索引的葉子節點儲存了索引關鍵字的值,可以直接通過索引查詢關鍵字的資訊從而避免了訪問資料行。但是Hash索引的葉子節點中儲存的是關鍵字資訊的Hash碼,我們需要將查詢資訊轉化成Hash在表中找到對應的資料行才能查詢到資料的資訊。因此Hash索引不能作為覆蓋索引來使用。

覆蓋索引:

如果一個索引包含所有需要查詢的欄位的值(where語句的引數、order by的引數、group by的引數),那麼我們通常稱這個索引為覆蓋索引。對於Memory儲存引擎不能使用覆蓋索引,查詢過程中如果包含了太多的列(如select *)也不適合使用覆蓋索引。
使用覆蓋索引也有很多的優點。

  • 優化快取,減少磁碟I/O操作。
  • 減少隨機I/O,變隨機I/O為順序I/O。
  • 可以避免對Innodb主鍵索引的二次查詢。

InnoDB儲存引擎中的索引

InnoDB作為MySQL最為著名的儲存引擎,這裡要做特別的介紹。InnoDB中儲存引擎支援B+樹索引、全文索引和雜湊索引。InnoDB儲存引擎支援的雜湊引擎是自適應的,InnoDB儲存引擎會根據表的使用情況自動為表生成雜湊索引,不能人為干預是否在一張表中生成雜湊索引。

傳統意義上的索引就是指的B+樹索引,這是目前關係型資料庫系統中查詢最為常用和有效的索引,其構造就是採用了二叉樹的思想,根據鍵值對快速找到資料。通過B+樹索引找到被查詢資料行所在的頁,然後資料庫把頁讀入到記憶體,再在記憶體中進行查詢,找到對應的資料。

InnoDB使用的是行鎖,只有在修改行時,才會對行進行加鎖。使用索引能夠使得資料在查詢過程中鎖定更少的行,增加了資料處理的併發性,提高了資料庫的效能。

索引使用的注意事項

  1. 保證在MySQL中查詢資料時,表中對應的列數獨立的。獨立的列在於索引列不能是表示式中的一部分,也不能是函式的引數。即不允許select id from article where id+1=5等情況的出現,否則索引將不能使用。
  2. 索引很長的字元列,會讓索引變得大且慢。這個時候就要採用字首索引,就是選取列開始的部分字元作為索引,字首索引的選擇也要保證合理的索引選擇性(越接近1越好)。
  3. 如果不需要考慮排序和分組的需要,在聯合索引中,應該將選擇性最高的索引放到索引的最前列、將經常會被使用的列放到索引的最前列、寬度較小的列放到索引的最前列。
  4. 使用pt-duplicate-key-checker h=127.0.0.1查詢重複和冗餘的索引,然後將重複冗餘的索引刪除。

改造SQL查詢語句

MySQL連線過程與狀態

MySQL連線狀態

MySQL客戶端和伺服器之間的通訊協議是”半雙工”的,在任何一個時刻,要麼是由伺服器向客戶端傳送資料,要麼是由客戶端向伺服器端傳送資料,兩個動作不能同時發生。對於每一個時刻,可以通過命令show full processlist來檢視mysql當前連線的狀態(Command列就代表當前的狀態)。

MySQL的狀態如下:

Sleep: 執行緒正在等待客戶端傳送新的請求

Query: 執行緒正在執行查詢或者正在將結果傳送給客戶端。

Locked: 在MySQL伺服器層,該執行緒正在等待表鎖.

Sorting result: 執行緒正在對結果集進行排序。

Copying to tmp table [on disk]: 執行緒正在執行查詢,並且將其結果集都複製到一個臨時表中,這種狀態要麼是在做GROUP BY操作,要麼是檔案排序操作,或者是UNION操作。如果狀態上有on disk的標記,那麼表示MySQL正在將一個記憶體臨時表放到磁碟上。

Analyzing and statistics: 執行緒正在收集儲存引擎的統計資訊,並生成查詢的執行計劃。

Sending data: 這表示執行緒或者在多個狀態之間傳送資料,或者在生成結果集,或者在向客戶端返回資料。

MySQL連線過程

  1. MySQL客戶端傳送一條查詢給伺服器
  2. MySQL如果開啟了查詢快取,那麼MySQL伺服器會優先檢查查詢快取。檢查的過程是通過一個對大小敏感的雜湊查詢實現的,如果快取命中,那麼在返回查詢結果之前MySQL會檢查一次使用者許可權,如果許可權合適,那麼直接返回快取中的結果資訊,查詢完成,否則執行下一步。
  3. 伺服器進行SQL解析、預處理,然後再由優化器生成對應的執行計劃。
  4. MySQL根據優化器生成的執行計劃,呼叫儲存引擎的API來執行查詢。
  5. 將查詢的結果返回給客戶端。

查詢優化

在上面MySQL執行查詢的過程中,伺服器已經提供了一些SQL的優化措施,我們也需要正確理解並使用這些個優化措施

  1. 使用explain+sql查詢語句可以檢視SQL查詢的效率。
  2. mysql使用基於成本的優化器。使用show status like `last_query_cost`可以查詢當前會話的last_query_cost值來得知mysql計算的當前查詢的成本,mysql會進行評估並得到成本最小的執行計劃。
  3. MySQL自帶一種”巢狀迴圈”能夠對我們的大多數查詢進行優化操作,調整關聯表的關聯順訊以達到高效的查詢。

優化資料訪問

優化資料訪問的關鍵在於:減少資料訪問量,只檢索必要訪問的資料,保證向資料庫發出的查詢資料量只是實際需要的資料量。

為了實現資料訪問量的優化,可以使用:

  1. 在SQL的查詢語句中,合理的使用limit控制行數。
  2. 在多表關聯的SQL查詢中,只查詢需要的表的列,儘量不要用”select *”
  3. 藉助第三方的快取系統,將經常查詢的資料快取起來。
  4. 如果查詢是需要掃描大量的資料但只是返回少量的行,那麼可以使用索引覆蓋掃描,把需要資料的行放到索引中。

重構查詢

  1. 將大的查詢分解成小的查詢。特別是對於刪除不需要的資料,一般來說就是分批刪除少量的資料,這樣可以大大減少資料庫鎖的持有時間。
  2. 合理的分解關聯查詢。關聯查詢分解成單表查詢可以減少鎖的競爭;同時單表查詢的結果在應用層做關聯,可以實現資料庫的拆分,做到高效能和可擴充套件。此外,通過將重複查詢的資料做快取可以提高效率。
  3. MySQL的某些子查詢效率很低(如使用in的子查詢),我們應該使用explain語句測試當前查詢的成本,然後決定是否應該使用內連線或者左(右)外連線改寫mysql的in()子查詢。但當我們需要返回一個表中的某些列時,多表關聯查詢我們可以使用exists關鍵字的子查詢,這樣效率也會更高。——在MySQL5.6版本以前需注意
  4. 在使用union關鍵字進行sql查詢時,如果有限制資料量和排序等操作,應在每一條sql語句中使用這些限制。
  5. 使用主鍵自帶的排序效果和limit關鍵字來代替max和min關鍵字實現最大和最小值。
  6. MySQL在需要進行分頁時,通過使用limit外加偏移量來實現,同時加上合適的order by子句,這樣可以充分的利用具有索引的列。此外,在分頁中,偏移量如果相差資料量過大,應該採用索引覆蓋掃描。
  7. 進行關聯查詢時,在on和using子句的列上新增索引,並且注意在關聯順序上,應該在第二章表中新增索引,提高效率。
  8. 確保group by和order by子句的表示式上只涉及一個表中的列,只有這樣才有可能使用索引優化這個過程。

相關文章