最近面試一些小朋友，簡歷上赫然寫著“擅長MySQL數據庫優(yōu)化”。

然后，我每每好奇地問上兩句，你都用了什么方式做的數據庫優(yōu)化啊，基本上千篇一律地回復就是三個字：“加索引。”（手動狗頭）

下面跟大家成體系化地詳談一下，MySQL數據庫的優(yōu)化方式有哪些？

既然談到優(yōu)化，一定想到要從多個維度進行優(yōu)化。

這里的優(yōu)化維度有四個：硬件配置、參數配置、表結構設計和SQL語句及索引。

其中 SQL 語句相關的優(yōu)化手段是最為重要的。

硬件配置

硬件方面的優(yōu)化可以有 對磁盤進行擴容、將機械硬盤換為SSD，或是把CPU的核數往上提升一些，增強數據庫的計算能力，或是把內存擴容了，讓Buffer Pool能吃進更多數據， 等等。但這個優(yōu)化手段成本最高，但見效最快。

有句話怎么說的來著，能通過硬件升級來解決的事情，千萬別碰代碼。哈哈。

參數配置

保證從內存讀取

MySQL 會在內存中保存一定的數據，通過 LRU（最近最少使用）算法將不常訪問的數據保存在硬盤文件中。盡可能的擴大內存中的數據量，將數據保存在內存中，從內存中讀取數據，可以提升 MySQL 性能。

MySQL 使用優(yōu)化過后的 LRU 算法：

普通LRU：末尾淘汰法，新數據從鏈表頭部加入，釋放空間時從末尾淘汰
改進LRU：鏈表分為new和old兩個部分，加入元素時并不是從表頭插入，而是從中間 midpoint位置插入，如果數據很快被訪問，那么page就會向new列表頭部移動，如果 數據沒有被訪問，會逐步向old尾部移動，等待淘汰。每當有新的page數據讀取到buffer pool時，InnoDb引擎會判斷是否有空閑頁，是否足夠，如果有就將free page從free list列表刪除，放入到LRU列表中。沒有空閑頁，就會根據LRU算法淘汰LRU鏈表默認的頁，將內存空間釋放分配給新的頁。

LRU 算法針對的是 MySQL 內存中的結構，這里有個區(qū)域叫 Buffer Pool（緩沖池） 作為數據讀寫的緩沖區(qū)域。把這個區(qū)域進行相應的擴大即可提升性能，當然這個參數要針對服務器硬件的實際情況進行調整。

通過以下命令可以查看相應的BufferPool的相關參數：

show global status like 'innodb_buffer_pool_pages_%'

輸入以下命令可以查看 BufferPool 的大小：

show variables like "%innodb_buffer_pool_size%"

在這里我們可以修改這個參數的值，如果該服務器是 MySQL 專用的服務器，我們可以 修改為總內存的 60%~80% ，當然不能影響系統程序的運行。

這個參數是只讀的，可以在 MySQL 的配置文件（my.cnf 或 my.ini）中進行修改。Linux 的配置文件為 my.cnf。

# 修改緩沖池大小為750M
innodb_buffer_pool_size = 750M

數據預熱

數據預熱相當于將磁盤中的數據提前放入 BufferPool 內存緩沖池內。一定程度提升了讀取速度。

對于 InnoDB，這里提供一份預熱 SQL 腳本：

#mysql5.7版本中，如果DISTINCT和order by一起使用將會報3065錯誤，sql語句無法執(zhí)行。這是由于5.7版本語法比之前版本語法要求更加嚴格導致的。
#推薦在mysql的配置文件my.cnf文件(linux)/my.ini文件(window) 的mysqld中增加或者修改sql_model配置選項
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#重啟后生效
SELECT DISTINCT
    CONCAT('SELECT ',rowlist,' FROM ',db,'.',tb,
    ' ORDER BY ',rowlist,';') selectSql
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) rowlist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA 
ORDER BY db,tb;

降低磁盤的寫入次數

（1）增大 redo log，減少落盤次數：

redo log 是重做日志，用于保證數據的一致，減少落盤相當于減少了系統 IO 操作。

innodb_log_file_size 設置為 0.25 * innodb_buffer_pool_size

（2）通用查詢日志、慢查詢日志可以不開 ，binlog 可開啟。

通用查詢和慢查詢日志也是要落盤的，可以根據實際情況開啟，如果不需要使用的話就可以關掉。binlog 用于恢復和主從復制，這個可以開啟。

查看相關參數的命令：

# 慢查詢日志
show variables like 'slow_query_log%'
# 通用查詢日志
show variables like '%general%';
# 錯誤日志
show variables like '%log_error%'
# 二進制日志
show variables like '%binlog%';

（3）寫 redo log 策略 innodb_flush_log_at_trx_commit 設置為 0 或 2

對于不需要強一致性的業(yè)務，可以設置為 0 或 2。

• 0：每隔 1 秒寫日志文件和刷盤操作（寫日志文件 LogBuffer --> OS cache，刷盤 OS cache --> 磁盤文件），最多丟失 1 秒數據
• 1：事務提交，立刻寫日志文件和刷盤，數據不丟失，但是會頻繁 IO 操作
• 2：事務提交，立刻寫日志文件，每隔 1 秒鐘進行刷盤操作

系統調優(yōu)參數

back_log

back_log值可以指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。也就是說，如果MySQL的連接數據達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源?？梢詮哪J的50升至500。

wait_timeout

數據庫連接閑置時間，閑置連接會占用內存資源?？梢詮哪J的8小時減到半小時。

max_user_connection

最大連接數，默認為0無上限，最好設一個合理上限。

thread_concurrency

并發(fā)線程數，設為CPU核數的兩倍。

skip_name_resolve

禁止對外部連接進行DNS解析，消除DNS解析時間，但需要所有遠程主機用IP訪問。

key_buffer_size

索引塊的緩存大小，增加會提升索引處理速度，對MyISAM表性能影響最大。對于內存4G左右，可設為256M或384M，通過查詢show status like 'key_read%'，保證key_reads / key_read_requests在0.1%以下最好。

innodb_buffer_pool_size

緩存數據塊和索引塊，對InnoDB表性能影響最大。通過查詢show status like 'Innodb_buffer_pool_read%'，保證 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests越高越好。

innodb_additional_mem_pool_size

InnoDB存儲引擎用來存放數據字典信息以及一些內部數據結構的內存空間大小，當數據庫對象非常多的時候，適當調整該參數的大小以確保所有數據都能存放在內存中提高訪問效率，當過小的時候，MySQL會記錄Warning信息到數據庫的錯誤日志中，這時就需要該調整這個參數大小。

innodb_log_buffer_size

InnoDB存儲引擎的事務日志所使用的緩沖區(qū)，一般來說不建議超過32MB。

query_cache_size

緩存MySQL中的ResultSet，也就是一條SQL語句執(zhí)行的結果集，所以僅僅只能針對select語句。當某個表的數據有任何變化，都會導致所有引用了該表的select語句在Query Cache中的緩存數據失效。所以，當我們數據變化非常頻繁的情況下，使用Query Cache可能得不償失。根據命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))進行調整，一般不建議太大，256MB可能已經差不多了，大型的配置型靜態(tài)數據可適當調大。可以通過命令show status like 'Qcache_%'查看目前系統Query catch使用大小。

read_buffer_size

MySQL讀入緩沖區(qū)大小。對表進行順序掃描的請求將分配一個讀入緩沖區(qū)，MySQL會為它分配一段內存緩沖區(qū)。如果對表的順序掃描請求非常頻繁，可以通過增加該變量值以及內存緩沖區(qū)大小來提高其性能。

sort_buffer_size

MySQL執(zhí)行排序使用的緩沖大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能，可以嘗試增加sort_buffer_size變量的大小。

read_rnd_buffer_size

MySQL的隨機讀緩沖區(qū)大小。當按任意順序讀取行時(例如按照排序順序)，將分配一個隨機讀緩存區(qū)。進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數據，可適當調高該值。但MySQL會為每個客戶連接發(fā)放該緩沖空間，所以應盡量適當設置該值，以避免內存開銷過大。

record_buffer

每個進行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)。如果你做很多順序掃描，可能想要增加該值。

thread_cache_size

保存當前沒有與連接關聯但是準備為后面新的連接服務的線程，可以快速響應連接的線程請求而無需創(chuàng)建新的。

table_cache

類似于thread_cache _size，但用來緩存表文件，對InnoDB效果不大，主要用于MyISAM。

表結構設計

設計聚合表

設計聚合表，一般針對于統計分析功能，或者實時性不高的需求（報表統計，數據分析等系統），這是一種空間 + 時延性換時間的思想。

設計冗余字段

為減少關聯查詢，創(chuàng)建合理的冗余字段（創(chuàng)建冗余字段還需要注意數據一致性問題），當然，如果冗余字段過多，對系統復雜度和插入性能會有影響。

分表

分表分為垂直拆分和水平拆分兩種。

垂直拆分，適用于字段太多的大表，比如：一個表有100多個字段，那么可以把表中經常不被使用的字段或者存儲數據比較多的字段拆出來。

水平拆分，比如：一個表有5千萬數據，那按照一定策略拆分成十個表，每個表有500萬數據。這種方式，除了可以解決查詢性能問題，也可以解決數據寫操作的熱點征用問題。

字段的設計

數據庫中的表越小，在它上面執(zhí)行的查詢也就會越快。因此，在創(chuàng)建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度設得盡可能小。

• 使用可以存下數據最小的數據類型，合適即可
• 盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數類型而非INT，如果非負則加上UNSIGNED；
• VARCHAR的長度只分配真正需要的空間；
• 對于某些文本字段，比如"省份"或者"性別"，使用枚舉或整數代替字符串類型；在MySQL中， ENUM類型被當作數值型數據來處理，而數值型數據被處理起來的速度要比文本類型快得多
• 盡量使用TIMESTAMP而非DATETIME；
• 單表不要有太多字段，建議在20以內；
• 盡可能使用 not null 定義字段，null 占用4字節(jié)空間，這樣在將來執(zhí)行查詢的時候，數據庫不用去比較NULL值。
• 用整型來存IP。
• 盡量少用 text 類型，非用不可時最好考慮拆表。

SQL語句及索引

如果發(fā)現SQL查詢比較慢，可以開啟慢查詢日志進行排查。

# 開啟全局慢查詢日志
SET global slow_query_log = ON;
# 設置慢查詢日志文件名
SET global slow_query_log_file = 'slow-query.log';
# 記錄未使用索引的SQL
SET global log_queries_not_using_indexes = ON;
# 慢查詢的時間閾值，默認10秒
SET long_query_time = 10;

注：索引并不是越多越好，要根據查詢有針對性的創(chuàng)建。

索引創(chuàng)建和使用原則

• 單表查詢：哪個列作查詢條件，就在該列創(chuàng)建索引
• 多表查詢：left join 時，索引添加到右表關聯字段；right join 時，索引添加到左表關聯字段
• 不要對索引列進行任何操作（計算、函數、類型轉換）
• 索引列中不要使用 !=，<> 非等于
• 字符字段只建前綴索引，最好不要做主鍵；
• 盡量不用UNIQUE，由程序保證約束
• 不用外鍵，由程序保證約束
• 索引列不要為空，且不要使用 is null 或 is not null 判斷
• 索引字段是字符串類型，查詢條件的值要加''單引號，避免底層類型自動轉換

使用 EXPLAIN 分析 SQL

這里對explain的結果進行簡單說明：

• select_type：查詢類型
  • SIMPLE 簡單查詢
  • PRIMARY 最外層查詢
  • UNION union后續(xù)查詢
  • SUBQUERY 子查詢
• type：查詢數據時采用的方式
  • ALL 全表（性能最差）
  • index 基于索引的全表
  • range 范圍 （< > in）
  • ref 非唯一索引單值查詢
  • const 使用主鍵或者唯一索引等值查詢
• possible_keys：可能用到的索引
• key：真正用到的索引
• rows：預估掃描多少行記錄
• key_len：使用了索引的字節(jié)數
• Extra：額外信息
  • Using where 索引回表
  • Using index 索引直接滿足條件
  • Using filesort 需要排序
  • Using temprorary 使用到臨時表

對于以上的幾個列，我們重點關注的是type，最直觀的反映出SQL的性能。

SQL語句盡可能簡單

一條sql只能在一個cpu運算；大語句拆小語句，減少鎖時間；一條大sql可以堵死整個庫。

對于連續(xù)數值，使用 BETWEEN 不用 IN

SELECT id FROM t WHERE num BETWEEN 1 AND 5；

SQL 語句中 IN 包含的值不應過多

MySQL對于IN做了相應的優(yōu)化，即將IN中的常量全部存儲在一個數組里面，而且這個數組是排好序的。如果數值較多，需要在內存進行排序操作，產生的消耗也是比較大的。

SELECT 語句必須指明字段名稱

SELECT * 增加很多不必要的消耗（CPU、IO、內存、網絡帶寬）；減少了使用覆蓋索引的可能性。

當只需要一條數據的時候，使用 limit 1

limit 相當于截斷查詢。

例如：對于select * from user limit 1; 雖然進行了全表掃描，但是limit截斷了全表掃描，從0開始取了1條數據。

排序字段加索引

排序的字段建立索引在排序的時候也會用到

如果限制條件中其他字段沒有索引，盡量少用or

盡量用 union all 代替 union

union和union all的差別就在于union會對數據做一個distinct的動作，而這個distanct動作的速度則取決于現有數據的數量，數量越大則時間也越慢。而對于幾個數據集，要確保數據集之間的數據互相不重復，基本是O(n)的算法復雜度。

區(qū)分 in 和 exists、not in 和 not exists

如果是exists，那么以外層表為驅動表，先被訪問，如果是IN，那么先執(zhí)行子查詢。所以IN適合于外表大而內表小的情況；EXISTS適合于外表小而內表大的情況。

使用合理的分頁方式以提高分頁的效率

limit m n，其中的m偏移量盡量小。m越大查詢越慢。

避免使用 % 前綴模糊查詢

例如：like '%name'或者like '%name%'，這種查詢會導致索引失效而進行全表掃描。但是可以使用like 'name%'，這種會使用到索引。

避免在 where 子句中對字段進行表達式操作

這種不會使用到索引：

select user_id,user_project from user_base where age*2=36;

可以改為：

select user_id,user_project from user_base where age=36/2;

任何對列的操作都將導致表掃描，它包括數據庫函數、計算表達式等等，查詢時要盡可能將操作移至等號右邊。

避免隱式類型轉換

where 子句中出現的 column 字段要和數據庫中的字段類型對應

必要時可以使用 force index 來強制查詢走某個索引

有的時候 MySQL 優(yōu)化器采取它認為合適的索引來檢索 SQL 語句，但是可能它所采用的索引并不是我們想要的。這時就可以采用 forceindex 來強制優(yōu)化器使用我們制定的索引。

使用聯合索引時注意范圍查詢

對于聯合索引來說，如果存在范圍查詢，比如between、>、<等條件時，會造成后面的索引字段失效。

某些情況下，可以使用連接代替子查詢

因為使用 join，MySQL 不會在內存中創(chuàng)建臨時表。

使用JOIN的優(yōu)化

使用小表驅動大表，例如使用inner join時，優(yōu)化器會選擇小表作為驅動表

小表驅動大表，即小的數據集驅動大的數據集

如：以 A，B 兩表為例，兩表通過 id 字段進行關聯。

#當 B 表的數據集小于 A 表時，用 in 優(yōu)化 exist；使用 in ，兩表執(zhí)行順序是先查 B 表，再查 A 表
select * from A where id in (select id from B)

#當 A 表的數據集小于 B 表時，用 exist 優(yōu)化 in；使用 exists，兩表執(zhí)行順序是先查 A 表，再查 B 表
select * from A where exists (select 1 from B where B.id = A.id)

上面都是一些常規(guī)的優(yōu)化方法，我們還可以使用：主從和分庫。

主從

主從相對比較簡單，從運維層面搭建好從庫后，工程師要做的就是制定路由策略。

路由策略有如下兩種：

讀寫分離模式，所有寫操作和對實時性要求較高的by id查詢走主庫，剩下的都走從庫，從庫采用Round Robin模式。

鏈路隔離模式：寫操作和核心操作對應的SQL走主庫，耗時大、非核心操作的SQL走從庫。

分庫

分庫策略需要根據業(yè)務場景制定，最常見的有兩種：按照年月分庫和按照角色分庫。

按照角色分庫，最經典的就是淘寶基于訂單的買家?guī)旌唾u家?guī)臁?/p>

結語

整體來講，這篇數據庫優(yōu)化應該總結得還算全面吧，如果有新的方案策略，我再往上添加。

最后，再給知友們來一波福利。

本人在之前看機會的時候，也從網上找遍了各式各類的八股文資料，但總覺得答案還不夠準確，深度還有所欠缺，或是內容組織的邏輯性還不夠清晰。

于是，我便自己動手，豐衣足食地自己總結了一套博采眾家之長的八股文，那可真是字字斟酌，題題驗證。

最新評論