MySQL 性能

①最大數(shù)據(jù)量

拋開數(shù)據(jù)量和并發(fā)數(shù)，談性能都是耍流氓。

MySQL 沒有限制單表最大記錄數(shù)，它取決于操作系統(tǒng)對(duì)文件大小的限制。

《阿里巴巴 Java 開發(fā)手冊(cè)》提出單表行數(shù)超過 500 萬行或者單表容量超過 2GB，才推薦分庫分表。

性能由綜合因素決定，拋開業(yè)務(wù)復(fù)雜度，影響程度依次是硬件配置、MySQL 配置、數(shù)據(jù)表設(shè)計(jì)、索引優(yōu)化。

500 萬這個(gè)值僅供參考，并非鐵律。

一個(gè)4 億行數(shù)據(jù)的單表，分頁查詢最新的 20 條記錄耗時(shí) 0.6 秒，SQL 語句大致是：

select field_1,field_2 from table where id < #{prePageMinId} order by id desc limit 20

prePageMinId 是上一頁數(shù)據(jù)記錄的最小 ID。

雖然當(dāng)時(shí)查詢速度還湊合，隨著數(shù)據(jù)不斷增長(zhǎng)，有朝一日必定不堪重負(fù)。

分庫分表是個(gè)周期長(zhǎng)而風(fēng)險(xiǎn)高的大活兒，應(yīng)該盡可能在當(dāng)前結(jié)構(gòu)上優(yōu)化，比如升級(jí)硬件、遷移歷史數(shù)據(jù)等等，實(shí)在沒轍了再分。

②最大并發(fā)數(shù)

并發(fā)數(shù)是指同一時(shí)刻數(shù)據(jù)庫能處理多少個(gè)請(qǐng)求，由 max_connections 和 max_user_connections 決定。

max_connections 是指 MySQL 實(shí)例的最大連接數(shù)，上限值是 16384;

max_user_connections 是指每個(gè)數(shù)據(jù)庫用戶的最大連接數(shù)。

MySQL 會(huì)為每個(gè)連接提供緩沖區(qū)，意味著消耗更多的內(nèi)存。如果連接數(shù)設(shè)置太高硬件吃不消，太低又不能充分利用硬件。

一般要求兩者比值超過 10%，計(jì)算方法如下：

max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%

查看最大連接數(shù)與響應(yīng)最大連接數(shù)：

show variables like '%max_connections%';show variables like '%max_user_connections%';

在配置文件 my.cnf 中修改最大連接數(shù)：

[mysqld]max_connections = 100max_used_connections = 20

③查詢耗時(shí) 0.5 秒

建議將單次查詢耗時(shí)控制在 0.5 秒以內(nèi)，0.5 秒是個(gè)經(jīng)驗(yàn)值，源于用戶體驗(yàn)的 3 秒原則。

如果用戶的操作 3 秒內(nèi)沒有響應(yīng)，將會(huì)厭煩甚至退出。

響應(yīng)時(shí)間=客戶端 UI 渲染耗時(shí)+網(wǎng)絡(luò)請(qǐng)求耗時(shí)+應(yīng)用程序處理耗時(shí)+查詢數(shù)據(jù)庫耗時(shí)，

0.5 秒就是留給數(shù)據(jù)庫 1/6 的處理時(shí)間。

④實(shí)施原則

相比 NoSQL 數(shù)據(jù)庫，MySQL 是個(gè)嬌氣脆弱的家伙。它就像體育課上的某些同學(xué)，一點(diǎn)糾紛就和同學(xué)鬧別扭（擴(kuò)容難），跑兩步就氣喘吁吁（容量小并發(fā)低），常常身體不適要請(qǐng)假（SQL 約束太多）。

如今大家都會(huì)搞點(diǎn)分布式，應(yīng)用程序擴(kuò)容比數(shù)據(jù)庫要容易得多，所以實(shí)施原則是數(shù)據(jù)庫少干活，應(yīng)用程序多干活：

• 充分利用但不濫用索引，須知索引也消耗磁盤和 CPU。

• 不推薦使用數(shù)據(jù)庫函數(shù)格式化數(shù)據(jù)，交給應(yīng)用程序處理。

• 不推薦使用外鍵約束，用應(yīng)用程序保證數(shù)據(jù)準(zhǔn)確性。

• 寫多讀少的場(chǎng)景，不推薦使用唯一索引，用應(yīng)用程序保證唯一性。

• 適當(dāng)冗余字段，嘗試創(chuàng)建中間表，用應(yīng)用程序計(jì)算中間結(jié)果，用空間換時(shí)間。

• 不允許執(zhí)行極度耗時(shí)的事務(wù)，配合應(yīng)用程序拆分成更小的事務(wù)。

• 預(yù)估重要數(shù)據(jù)表（比如訂單表）的負(fù)載和數(shù)據(jù)增長(zhǎng)態(tài)勢(shì)，提前優(yōu)化。

數(shù)據(jù)表設(shè)計(jì)

①數(shù)據(jù)類型

數(shù)據(jù)類型的選擇原則，更簡(jiǎn)單或者占用空間更?。?/p>

• 如果長(zhǎng)度能夠滿足，整型盡量使用 tinyint、smallint、medium_int 而非 int。

• 如果字符串長(zhǎng)度確定，采用 char 類型。

• 如果 varchar 能夠滿足，不采用 text 類型。

• 精度要求較高的使用 decimal 類型，也可以使用 BIGINT，比如精確兩位小數(shù)就乘以 100 后保存。

• 盡量采用 timestamp 而非 datetime。

相比 datetime，timestamp 占用更少的空間，以 UTC 的格式儲(chǔ)存自動(dòng)轉(zhuǎn)換時(shí)區(qū)。

②避免空值

MySQL 中字段為 NULL 時(shí)依然占用空間，會(huì)使索引、索引統(tǒng)計(jì)更加復(fù)雜。從 NULL 值更新到非 NULL 無法做到原地更新，容易發(fā)生索引分裂影響性能。因此盡可能將 NULL 值用有意義的值代替，也能避免 SQL 語句里面包含 is not null 的判斷。

③Text 類型優(yōu)化

由于 Text 字段儲(chǔ)存大量數(shù)據(jù)，表容量會(huì)很早漲上去，影響其他字段的查詢性能。建議抽取出來放在子表里，用業(yè)務(wù)主鍵關(guān)聯(lián)。

索引優(yōu)化

索引分類如下：

• 普通索引：最基本的索引。

• 組合索引：多個(gè)字段上建立的索引，能夠加速復(fù)合查詢條件的檢索。

• 唯一索引：與普通索引類似，但索引列的值必須唯一，允許有空值。

• 組合唯一索引：列值的組合必須唯一。

• 主鍵索引：特殊的唯一索引，用于唯一標(biāo)識(shí)數(shù)據(jù)表中的某一條記錄，不允許有空值，一般用 primary key 約束。

• 全文索引：用于海量文本的查詢，MySQL 5.6 之后的 InnoDB 和 MyISAM 均支持全文索引。由于查詢精度以及擴(kuò)展性不佳，更多的企業(yè)選擇 Elasticsearch。

索引優(yōu)化原則：

• 分頁查詢很重要，如果查詢數(shù)據(jù)量超過 30%，MySQL 不會(huì)使用索引。

• 單表索引數(shù)不超過 5 個(gè)、單個(gè)索引字段數(shù)不超過 5 個(gè)。

• 字符串可使用前綴索引，前綴長(zhǎng)度控制在 5-8 個(gè)字符。

• 字段唯一性太低，增加索引沒有意義，如：是否刪除、性別。

• 合理使用覆蓋索引，如下所示：

select login_name, nick_name from member where login_name = ?

login_name, nick_name 兩個(gè)字段建立組合索引，比 login_name 簡(jiǎn)單索引要更快。

SQL 優(yōu)化

①分批處理

一個(gè)魚塘挖開小口子放水，水面有各種漂浮物。

浮萍和樹葉總能順利通過出水口，而樹枝會(huì)擋住其他物體通過，有時(shí)還會(huì)卡住，需要人工清理。

MySQL 就是魚塘，最大并發(fā)數(shù)和網(wǎng)絡(luò)帶寬就是出水口，用戶 SQL 就是漂浮物。

不帶分頁參數(shù)的查詢或者影響大量數(shù)據(jù)的 update 和 delete 操作，都是樹枝，我們要把它打散分批處理，下面舉例說明。

業(yè)務(wù)描述：更新用戶所有已過期的優(yōu)惠券為不可用狀態(tài)。SQL 語句：

update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;

如果大量?jī)?yōu)惠券需要更新為不可用狀態(tài)，執(zhí)行這條 SQL 可能會(huì)堵死其他 SQL，分批處理偽代碼如下：

int pageNo = 1;
int PAGE_SIZE = 100;
while(true) {
    List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');
    if (CollectionUtils.isEmpty(batchIdList)) {
        return;
    }
    update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')
    pageNo ++;
}

②操作符 <> 優(yōu)化

通常 <> 操作符無法使用索引，舉例如下，查詢金額不為 100 元的訂單：

select id from orders where amount  != 100;

如果金額為 100 的訂單極少，這種數(shù)據(jù)分布嚴(yán)重不均的情況下，有可能使用索引。

鑒于這種不確定性，采用 union 聚合搜索結(jié)果，改寫方法如下：

(select id from orders where amount > 100
) union all
(select id from orders where amount < 100 and amount > 0)

③OR 優(yōu)化

在 Innodb 引擎下 OR 無法使用組合索引，比如：

select id，product_name from orders where mobile_no = '13421800407' or user_id = 100;

OR 無法命中 mobile_no + user_id 的組合索引，可采用 union，如下所示：

(select id，product_name from orders where mobile_no = '13421800407') 
union
(select id，product_name from orders where user_id = 100);

此時(shí) id 和 product_name 字段都有索引，查詢才最高效。

④IN 優(yōu)化

IN 適合主表大子表小，EXIST 適合主表小子表大。由于查詢優(yōu)化器的不斷升級(jí)，很多場(chǎng)景這兩者性能差不多一樣了。

嘗試改為 Join 查詢，舉例如下：

select id from orders where user_id in (select id from user where level = 'VIP');

采用 Join 如下所示：

select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';

⑤不做列運(yùn)算

通常在查詢條件列運(yùn)算會(huì)導(dǎo)致索引失效，如下所示，查詢當(dāng)日訂單：

select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';

date_format 函數(shù)會(huì)導(dǎo)致這個(gè)查詢無法使用索引，改寫后：

select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';

⑥避免Select All

如果不查詢表中所有的列，避免使用 SELECT *，它會(huì)進(jìn)行全表掃描，不能有效利用索引。

⑦Like 優(yōu)化

Like 用于模糊查詢，舉個(gè)例子（field 已建立索引）：

SELECT column FROM table WHERE field like '%keyword%';

這個(gè)查詢未命中索引，換成下面的寫法：

SELECT column FROM table WHERE field like 'keyword%';

去除了前面的 % 查詢將會(huì)命中索引，但是產(chǎn)品經(jīng)理一定要前后模糊匹配呢？全文索引 fulltext 可以嘗試一下，但 Elasticsearch 才是終極武器。

⑧Join 優(yōu)化

Join 的實(shí)現(xiàn)是采用 Nested Loop Join 算法，就是通過驅(qū)動(dòng)表的結(jié)果集作為基礎(chǔ)數(shù)據(jù)，通過該結(jié)數(shù)據(jù)作為過濾條件到下一個(gè)表中循環(huán)查詢數(shù)據(jù)，然后合并結(jié)果。如果有多個(gè) Join，則將前面的結(jié)果集作為循環(huán)數(shù)據(jù)，再次到后一個(gè)表中查詢數(shù)據(jù)。驅(qū)動(dòng)表和被驅(qū)動(dòng)表盡可能增加查詢條件，滿足 ON 的條件而少用 Where，用小結(jié)果集驅(qū)動(dòng)大結(jié)果集。被驅(qū)動(dòng)表的 Join 字段上加上索引，無法建立索引的時(shí)候，設(shè)置足夠的 Join Buffer Size。禁止 Join 連接三個(gè)以上的表，嘗試增加冗余字段。

⑨Limit 優(yōu)化

Limit 用于分頁查詢時(shí)越往后翻性能越差，解決的原則：縮小掃描范圍，如下所示：

select * from orders order by id desc limit 100000,10//耗時(shí)0.4秒
select * from orders order by id desc limit 1000000//10耗時(shí)5.2秒

先篩選出 ID 縮小查詢范圍，寫法如下：

select * from orders where id >
(select id from orders order by id desc  limit 1000000, 1)
order by id desc limit 0
//10耗時(shí)0.5秒

如果查詢條件僅有主鍵 ID，寫法如下：

select id from orders where id between 1000000 and 1000010 order by id desc
//耗時(shí)0.3秒

如果以上方案依然很慢呢？只好用游標(biāo)了，感興趣的朋友閱讀 JDBC 使用游標(biāo)實(shí)現(xiàn)分頁查詢的方法。

其他數(shù)據(jù)庫

作為一名后端開發(fā)人員，務(wù)必精通作為存儲(chǔ)核心的 MySQL 或 SQL Server，也要積極關(guān)注 NoSQL 數(shù)據(jù)庫，他們已經(jīng)足夠成熟并被廣泛采用，能解決特定場(chǎng)景下的性能瓶頸。