數據庫的優(yōu)化包括兩個方面，一是SQL語句的優(yōu)化，二是數據庫服務器和配置的優(yōu)化。下面先講查詢語句的優(yōu)化。

查詢語句優(yōu)化主要涉及兩個方面：一些普遍遵循的原則和怎么對查詢語句進行性能分析。

一、索引與性能分析

通過以下兩條語句可以查看SQL性能報告，針對性地定位性能瓶頸。

-- 查看SQL性能報告
show profiles;
-- 查看指定query執(zhí)行計劃的詳細報告（通過上一條語句獲得query序號）
show profile for query 4;

MySQL執(zhí)行計劃就是在一條SELECT語句前加EXPLAIN關鍵詞。

explain select * from `user` where id = 1;

可以得到如下結果：

• type：聯合查詢使用的類型
• possible_keys：告訴你MySQL能使用哪些索引找到改行，如果沒有，表明沒有相關索引。這時候想提高性能可以看where條件子句，看看是否引用了索引字段或者適合創(chuàng)建索引。
• key：MySQL實際使用的鍵。
• Extra：
  1. Only index，意味著信息只能用索引樹中的信息檢索，這比掃描全表要快；
  2. where used，表示是使用了where限制，但是用索引還不夠；
  3. impossible where，則表示通過收集到的統(tǒng)計信息判斷出不可能存在的結果；
  4. Using filesort，表示包含orderby且無法使用索引進行派訊操作；
  5. using temporary，使用了臨時表，常見于orderby和group by；
  6. 其他。。。

type顯示的訪問類型是較重要的指標，結果從好到壞依次是：system(系統(tǒng)表) > const(讀常量) > eq_ref(最多一條匹配結果，通常是主鍵訪問) > ref(被驅動表索引引擎) > fulltext(全文索引檢索) > ref_or_null(帶空值的索引查詢) > index_merge(合并索引結果集) > unique_subquery(子查詢中返回的字段是唯一組合或索引) > index_subquery(子查詢返回的是索引，但非主鍵) > range(索引范圍掃描) > index(全索引掃描) > ALL(全表掃描)，Extra中的第4或5項可能需要在后端邏輯中權衡一下是先過濾再排序還是先排序再過濾。

一般來說，保證查詢至少達到range級，最好能達到ref級。

MySQL索引建立和使用原則：

• 合理設計和使用索引，在關鍵字段上建立索引
• 不在結果單一的列上建索引，如性別字段
• 索引并非越多越好，維護索引需要成本，盡量在5個以下，合理利用部分索引和聯合索引
• 索引字段的結果集最好分布均勻

二、SQL不走索引的情況

1. where子句參與了計算或者使用了函數（包括正則函數）

SELECT `username` FROM `user` WHERE `age`+10 = 30;
SELECT `username` FROM `user` WHERE LEFT(`birthday`, 4) < 1990;

2. LIKE匹配前面有%

SELECT * FROM `user` WHERE `username` LIKE "%bruce%";

3. 存在隱式轉換

假設字段id(int)和username(varchar)均有索引，int型字段隱式轉換不影響索引，其他類型字段隱式轉換會影響索引。

-- 走索引
SELECT * FROM `user` WHERE `id` = 111;
-- 走索引
SELECT * FROM `user` WHERE `id` = '111';
-- 走索引
SELECT * FROM `user` WHERE `username` = '111';
-- 不走索引
SELECT * FROM `user` WHERE `username` = 111;

4. where子句有OR

where子句有OR時不走索引，可以用union（有distinct效果）或者union all來優(yōu)化SQL。

SELECT * FROM `user` WHERE `username` LIKE "bruce%" OR  `username` LIKE "wu%";

5. where子句中使用復合索引沒有遵循最左原則

譬如有表t，其中對abc三個字段建立了復合索引，根據B+樹搜索順序或者最左原則相當于創(chuàng)建了a、ab、ac、abc三個索引，查詢的時候mysql會一直向右匹配直到遇到了>、<、between、like等。

-- 走索引
SELECT * FROM t WHERE a = 'test1';
-- 走索引
SELECT * FROM t WHERE a = 'test1' AND b = 'test2';
-- 走索引
SELECT * FROM t WHERE a = 'test1' AND b = 'test2' AND c = 'test3';
-- 部分走索引，匹配到b的時候停止匹配，c用不到索引，這種查詢多的話索引應該改成acb，可以全部走索引
SELECT * FROM t WHERE a = 'test1' AND b LIKE 'test%' AND c = 'test3';
-- 走索引
SELECT * FROM t WHERE a = 'test1' AND c = 'test3';
-- 不走索引
SELECT * FROM t WHERE b = 'test2' AND c = 'test3';
-- 不走索引
SELECT * FROM t WHERE b = 'test2';
-- 不走索引
SELECT * FROM t WHERE c = 'test3';

6. 在where子句中IN使用了子查詢

假設有test_table1表對pay_id建立了索引，如果IN查詢是直接的值，則可以正常使用索引：

select * from test_table1 where pay_id in(63999,78000,98877,123000,140000);

如果IN里使用子查詢，則外層可能要進行全表掃描：

select * from test_table1 
where pay_id in (
    select pay_id from test_table1 
    where pay_time >= "2022-06-01 00:00:00"
    and pay_time <= "2022-07-03 12:59:59"
    group by pay_id 
    having count(pay_id) > 1
);

這里建議盡量將IN子查詢語句改成join查詢，這樣外層就能走索引：

select t1.* from test_table1 t1, (
    select pay_id from test_table1 
    where pay_time >= "2022-06-01 00:00:00"
    and pay_time <= "2022-07-03 12:59:59"
    group by pay_id
    having count(pay_id) > 1
) t2
where t1.pay_id = t2.pay_id;

7. mysql估計使用全表掃描比使用索引快

SELECT * FROM `user`;

三、服務器和配置優(yōu)化

MySQL中存在多種存儲引擎，每種引擎都有各自的特色，對比如下。

1. 合理選擇引擎

一般來說理想的讀寫比（R/W）為100:1，當讀寫比達到10:1的時候就認為是以寫為主的數據庫了，一般這個值在30:1左右。選擇引擎的原則如下：
1）選擇MyISAM

• R/W > 100:1且update較少；
• 并發(fā)不高，不需要事務；
• 數據量?。?/li>
• 硬件資源有限。

2）選擇InnoDB

• R/W較小，頻繁更新大字段；
• 數據量超過1000萬，并發(fā)高；
• 安全性和可用性要求高。

3）選擇Memory

• 有足夠的內存；
• 對數據一致性要求不高，如在線人數和Session等；
• 需要定期歸檔的數據。

2. MySQL服務器調整和優(yōu)化措施

• 1. 關閉不必要的二進制日志和慢查詢日志，僅在內存足夠或需要調試的時候打開。

-- 查看是否開啟慢查詢日志
show variables like '%slow%';
-- 查看慢查詢條數
show global status like '%slow%';

• 2. 適度增加Query Cache。
• 3. 增加MySQL允許的最大連接數。

-- 查看MySQL允許的最大連接數;
show variables like 'max_connections';

• 4. 對MyISAM表增加key_buffer_size，這需要根據key_cache命中率計算：

show variables like 'key_read%';

計算公式為：key_cache_miss_rate = Key_reads / Key_read_requests * 100%。
當key_cache_miss_rate值大于1%時就需要適當增加key_buffer_size了。

• 5. 從表中刪除大量行后，可運行OPTIMIZE TABLE TableName進行碎片整理。