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

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

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

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

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

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

硬件配置

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

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

參數(shù)配置

保證從內(nèi)存讀取

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

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

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

LRU 算法針對(duì)的是 MySQL 內(nèi)存中的結(jié)構(gòu)，這里有個(gè)區(qū)域叫 Buffer Pool（緩沖池） 作為數(shù)據(jù)讀寫的緩沖區(qū)域。把這個(gè)區(qū)域進(jìn)行相應(yīng)的擴(kuò)大即可提升性能，當(dāng)然這個(gè)參數(shù)要針對(duì)服務(wù)器硬件的實(shí)際情況進(jìn)行調(diào)整。

通過以下命令可以查看相應(yīng)的BufferPool的相關(guān)參數(shù)：

show global status like 'innodb_buffer_pool_pages_%'

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

show variables like "%innodb_buffer_pool_size%"

在這里我們可以修改這個(gè)參數(shù)的值，如果該服務(wù)器是 MySQL 專用的服務(wù)器，我們可以 修改為總內(nèi)存的 60%~80% ，當(dāng)然不能影響系統(tǒng)程序的運(yùn)行。

這個(gè)參數(shù)是只讀的，可以在 MySQL 的配置文件（my.cnf 或 my.ini）中進(jìn)行修改。Linux 的配置文件為 my.cnf。

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

數(shù)據(jù)預(yù)熱

數(shù)據(jù)預(yù)熱相當(dāng)于將磁盤中的數(shù)據(jù)提前放入 BufferPool 內(nèi)存緩沖池內(nèi)。一定程度提升了讀取速度。

對(duì)于 InnoDB，這里提供一份預(yù)熱 SQL 腳本：

#mysql5.7版本中，如果DISTINCT和order by一起使用將會(huì)報(bào)3065錯(cuò)誤，sql語句無法執(zhí)行。這是由于5.7版本語法比之前版本語法要求更加嚴(yán)格導(dǎo)致的。
#推薦在mysql的配置文件my.cnf文件(linux)/my.ini文件(window) 的mysqld中增加或者修改sql_model配置選項(xiàng)
#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;

降低磁盤的寫入次數(shù)

（1）增大 redo log，減少落盤次數(shù)：

redo log 是重做日志，用于保證數(shù)據(jù)的一致，減少落盤相當(dāng)于減少了系統(tǒng) IO 操作。

innodb_log_file_size 設(shè)置為 0.25 * innodb_buffer_pool_size

（2）通用查詢?nèi)罩尽⒙樵內(nèi)罩究梢圆婚_ ，binlog 可開啟。

通用查詢和慢查詢?nèi)罩疽彩且浔P的，可以根據(jù)實(shí)際情況開啟，如果不需要使用的話就可以關(guān)掉。binlog 用于恢復(fù)和主從復(fù)制，這個(gè)可以開啟。

查看相關(guān)參數(shù)的命令：

# 慢查詢?nèi)罩?
show variables like 'slow_query_log%'
# 通用查詢?nèi)罩?
show variables like '%general%';
# 錯(cuò)誤日志
show variables like '%log_error%'
# 二進(jìn)制日志
show variables like '%binlog%';

（3）寫 redo log 策略 innodb_flush_log_at_trx_commit 設(shè)置為 0 或 2

對(duì)于不需要強(qiáng)一致性的業(yè)務(wù)，可以設(shè)置為 0 或 2。

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

系統(tǒng)調(diào)優(yōu)參數(shù)

back_log

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

wait_timeout

數(shù)據(jù)庫連接閑置時(shí)間，閑置連接會(huì)占用內(nèi)存資源?？梢詮哪J(rèn)的8小時(shí)減到半小時(shí)。

max_user_connection

最大連接數(shù)，默認(rèn)為0無上限，最好設(shè)一個(gè)合理上限。

thread_concurrency

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

skip_name_resolve

禁止對(duì)外部連接進(jìn)行DNS解析，消除DNS解析時(shí)間，但需要所有遠(yuǎn)程主機(jī)用IP訪問。

key_buffer_size

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

innodb_buffer_pool_size

緩存數(shù)據(jù)塊和索引塊，對(duì)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存儲(chǔ)引擎用來存放數(shù)據(jù)字典信息以及一些內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存空間大小，當(dāng)數(shù)據(jù)庫對(duì)象非常多的時(shí)候，適當(dāng)調(diào)整該參數(shù)的大小以確保所有數(shù)據(jù)都能存放在內(nèi)存中提高訪問效率，當(dāng)過小的時(shí)候，MySQL會(huì)記錄Warning信息到數(shù)據(jù)庫的錯(cuò)誤日志中，這時(shí)就需要該調(diào)整這個(gè)參數(shù)大小。

innodb_log_buffer_size

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

query_cache_size

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

read_buffer_size

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

sort_buffer_size

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

read_rnd_buffer_size

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

record_buffer

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

thread_cache_size

保存當(dāng)前沒有與連接關(guān)聯(lián)但是準(zhǔn)備為后面新的連接服務(wù)的線程，可以快速響應(yīng)連接的線程請(qǐng)求而無需創(chuàng)建新的。

table_cache

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

表結(jié)構(gòu)設(shè)計(jì)

設(shè)計(jì)聚合表

設(shè)計(jì)聚合表，一般針對(duì)于統(tǒng)計(jì)分析功能，或者實(shí)時(shí)性不高的需求（報(bào)表統(tǒng)計(jì)，數(shù)據(jù)分析等系統(tǒng)），這是一種空間 + 時(shí)延性換時(shí)間的思想。

設(shè)計(jì)冗余字段

為減少關(guān)聯(lián)查詢，創(chuàng)建合理的冗余字段（創(chuàng)建冗余字段還需要注意數(shù)據(jù)一致性問題），當(dāng)然，如果冗余字段過多，對(duì)系統(tǒng)復(fù)雜度和插入性能會(huì)有影響。

分表

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

垂直拆分，適用于字段太多的大表，比如：一個(gè)表有100多個(gè)字段，那么可以把表中經(jīng)常不被使用的字段或者存儲(chǔ)數(shù)據(jù)比較多的字段拆出來。

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

字段的設(shè)計(jì)

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

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

SQL語句及索引

如果發(fā)現(xiàn)SQL查詢比較慢，可以開啟慢查詢?nèi)罩具M(jìn)行排查。

# 開啟全局慢查詢?nèi)罩?
SET global slow_query_log = ON;
# 設(shè)置慢查詢?nèi)罩疚募?
SET global slow_query_log_file = 'slow-query.log';
# 記錄未使用索引的SQL
SET global log_queries_not_using_indexes = ON;
# 慢查詢的時(shí)間閾值，默認(rèn)10秒
SET long_query_time = 10;

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

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

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

使用 EXPLAIN 分析 SQL

這里對(duì)explain的結(jié)果進(jìn)行簡單說明：

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

對(duì)于以上的幾個(gè)列，我們重點(diǎn)關(guān)注的是type，最直觀的反映出SQL的性能。

SQL語句盡可能簡單

一條sql只能在一個(gè)cpu運(yùn)算；大語句拆小語句，減少鎖時(shí)間；一條大sql可以堵死整個(gè)庫。

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

SELECT id FROM t WHERE num BETWEEN 1 AND 5；

SQL 語句中 IN 包含的值不應(yīng)過多

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

SELECT 語句必須指明字段名稱

SELECT * 增加很多不必要的消耗（CPU、IO、內(nèi)存、網(wǎng)絡(luò)帶寬）；減少了使用覆蓋索引的可能性。

當(dāng)只需要一條數(shù)據(jù)的時(shí)候，使用 limit 1

limit 相當(dāng)于截?cái)嗖樵儭?/p>

例如：對(duì)于select * from user limit 1; 雖然進(jìn)行了全表掃描，但是limit截?cái)嗔巳頀呙瑁瑥?開始取了1條數(shù)據(jù)。

排序字段加索引

排序的字段建立索引在排序的時(shí)候也會(huì)用到

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

盡量用 union all 代替 union

union和union all的差別就在于union會(huì)對(duì)數(shù)據(jù)做一個(gè)distinct的動(dòng)作，而這個(gè)distanct動(dòng)作的速度則取決于現(xiàn)有數(shù)據(jù)的數(shù)量，數(shù)量越大則時(shí)間也越慢。而對(duì)于幾個(gè)數(shù)據(jù)集，要確保數(shù)據(jù)集之間的數(shù)據(jù)互相不重復(fù)，基本是O(n)的算法復(fù)雜度。

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

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

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

limit m n，其中的m偏移量盡量小。m越大查詢?cè)铰?/p>

避免使用 % 前綴模糊查詢

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

避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作

這種不會(huì)使用到索引：

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

可以改為：

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

任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊。

避免隱式類型轉(zhuǎn)換

where 子句中出現(xiàn)的 column 字段要和數(shù)據(jù)庫中的字段類型對(duì)應(yīng)

必要時(shí)可以使用 force index 來強(qiáng)制查詢走某個(gè)索引

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

使用聯(lián)合索引時(shí)注意范圍查詢

對(duì)于聯(lián)合索引來說，如果存在范圍查詢，比如between、>、<等條件時(shí)，會(huì)造成后面的索引字段失效。

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

因?yàn)槭褂?join，MySQL 不會(huì)在內(nèi)存中創(chuàng)建臨時(shí)表。

使用JOIN的優(yōu)化

使用小表驅(qū)動(dòng)大表，例如使用inner join時(shí)，優(yōu)化器會(huì)選擇小表作為驅(qū)動(dòng)表

小表驅(qū)動(dòng)大表，即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集

如：以 A，B 兩表為例，兩表通過 id 字段進(jìn)行關(guān)聯(lián)。

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

#當(dāng) A 表的數(shù)據(jù)集小于 B 表時(shí)，用 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)化方法，我們還可以使用：主從和分庫。

主從

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

路由策略有如下兩種：

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

鏈路隔離模式：寫操作和核心操作對(duì)應(yīng)的SQL走主庫，耗時(shí)大、非核心操作的SQL走從庫。

分庫

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

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

結(jié)語

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

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

本人在之前看機(jī)會(huì)的時(shí)候，也從網(wǎng)上找遍了各式各類的八股文資料，但總覺得答案還不夠準(zhǔn)確，深度還有所欠缺，或是內(nèi)容組織的邏輯性還不夠清晰。

于是，我便自己動(dòng)手，豐衣足食地自己總結(jié)了一套博采眾家之長的八股文，那可真是字字斟酌，題題驗(yàn)證。