原文地址：MySQL 性能優(yōu)化技巧
博客地址：www.extlight.com
一刹帕、背景
最近公司項(xiàng)目添加新功能砚殿，上線后發(fā)現(xiàn)有些功能的列表查詢時(shí)間很久拗秘。原因是新功能用到舊功能的接口贷帮，而這些舊接口的 SQL 查詢語句關(guān)聯(lián)5,6張表且編寫不夠規(guī)范，導(dǎo)致 MySQL 在執(zhí)行 SQL 語句時(shí)索引失效柒瓣，進(jìn)行全表掃描瘪菌。原本負(fù)責(zé)優(yōu)化的同事有事請假回家，因此優(yōu)化查詢數(shù)據(jù)的問題落在筆者手中嘹朗。筆者在查閱網(wǎng)上 SQL 優(yōu)化的資料后成功解決了問題，在此從==全局角度==記錄和總結(jié) MySQL 查詢優(yōu)化相關(guān)技巧诵肛。
二屹培、優(yōu)化思路
數(shù)據(jù)查詢慢默穴，不代表 SQL 語句寫法有問題。 首先褪秀，我們需要找到問題的源頭才能“對癥下藥”蓄诽。筆者用一張流程圖展示 MySQL 優(yōu)化的思路：
無需更多言語，從圖中可以清楚地看出媒吗，導(dǎo)致數(shù)據(jù)查詢慢的原因有多種仑氛，如：緩存失效，在此一段時(shí)間內(nèi)由于高并發(fā)訪問導(dǎo)致 MySQL 服務(wù)器崩潰闸英；SQL 語句編寫問題锯岖；MySQL 服務(wù)器參數(shù)問題；硬件配置限制 MySQL 服務(wù)性能問題等甫何。
三出吹、查看 MySQL 服務(wù)器運(yùn)行的狀態(tài)值
如果系統(tǒng)的并發(fā)請求數(shù)不高，且查詢速度慢辙喂，可以忽略該步驟直接進(jìn)行 SQL 語句調(diào)優(yōu)步驟捶牢。
執(zhí)行命令：
show status復(fù)制代碼
由于返回結(jié)果太多，此處不貼出結(jié)果巍耗。其中秋麸，再返回的結(jié)果中，我們主要關(guān)注 “Queries”炬太、“Threads_connected” 和 “Threads_running” 的值灸蟆，即查詢次數(shù)、線程連接數(shù)和線程運(yùn)行數(shù)娄琉。
我們可以通過執(zhí)行如下腳本監(jiān)控 MySQL 服務(wù)器運(yùn)行的狀態(tài)值
#!/bin/bash
while true
do
mysqladmin -uroot -p"密碼" ext | awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}' >> status.txt
sleep 1
done復(fù)制代碼
執(zhí)行該腳本 24 小時(shí)次乓，獲取 status.txt 里的內(nèi)容，再次通過 awk 計(jì)算==每秒請求 MySQL 服務(wù)的次數(shù)==
awk '{q=$1-last;last=$1}{printf("%d %d %d\n",q,$2,$3)}' status.txt復(fù)制代碼
復(fù)制計(jì)算好的內(nèi)容到 Excel 中生成圖表觀察數(shù)據(jù)周期性孽水。
如果觀察的數(shù)據(jù)有周期性的變化票腰，如上圖的解釋，需要修改緩存失效策略女气。
例如：
通過隨機(jī)數(shù)在[3,6,9] 區(qū)間獲取其中一個(gè)值作為緩存失效時(shí)間杏慰，這樣分散了緩存失效時(shí)間，從而節(jié)省了一部分內(nèi)存的消耗炼鞠。
當(dāng)訪問高峰期時(shí)缘滥，一部分請求分流到未失效的緩存，另一部分則訪問 MySQL 數(shù)據(jù)庫谒主，這樣減少了 MySQL 服務(wù)器的壓力朝扼。
四、獲取需要優(yōu)化的 SQL 語句
4.1 方式一：查看運(yùn)行的線程
執(zhí)行命令：
show processlist復(fù)制代碼
返回結(jié)果：
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 9 | root | localhost | test | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)復(fù)制代碼
從返回結(jié)果中我們可以了解該線程執(zhí)行了什么命令/SQL 語句以及執(zhí)行的時(shí)間霎肯。實(shí)際應(yīng)用中擎颖，查詢的返回結(jié)果會有 N 條記錄榛斯。
其中，返回的 State 的值是我們判斷性能好壞的關(guān)鍵搂捧，其值出現(xiàn)如下內(nèi)容驮俗，則該行記錄的 SQL 語句需要優(yōu)化：
Converting HEAP to MyISAM # 查詢結(jié)果太大時(shí)，把結(jié)果放到磁盤允跑，嚴(yán)重
Create tmp table #創(chuàng)建臨時(shí)表王凑，嚴(yán)重
Copying to tmp table on disk #把內(nèi)存臨時(shí)表復(fù)制到磁盤，嚴(yán)重
locked #被其他查詢鎖住聋丝，嚴(yán)重
loggin slow query #記錄慢查詢
Sorting result #排序復(fù)制代碼
State 字段有很多值索烹，如需了解更多，可以參看文章末尾提供的鏈接潮针。
4.2 方式二：開啟慢查詢?nèi)罩?/h3>
在配置文件 my.cnf 中的 [mysqld] 一行下邊添加兩個(gè)參數(shù)：
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1復(fù)制代碼
其中术荤，slow_query_log = 1 表示開啟慢查詢；slow_query_log_file 表示慢查詢?nèi)罩敬娣诺奈恢茫?br> long_query_time = 2 表示查詢 >=2 秒才記錄日志每篷；log_queries_not_using_indexes = 1 記錄沒有使用索引的 SQL 語句瓣戚。
注意：slow_query_log_file 的路徑不能隨便寫，否則 MySQL 服務(wù)器可能沒有權(quán)限將日志文件寫到指定的目錄中焦读。建議直接復(fù)制上文的路徑子库。
修改保存文件后，重啟 MySQL 服務(wù)矗晃。在 /var/lib/mysql/ 目錄下會創(chuàng)建 slow-query.log 日志文件仑嗅。連接 MySQL 服務(wù)端執(zhí)行如下命令可以查看配置情況。
show variables like 'slow_query%';
show variables like 'long_query_time';復(fù)制代碼
測試慢查詢?nèi)罩荆?/p>
mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)復(fù)制代碼
打開慢查詢?nèi)罩疚募?/p>
[root@localhost mysql]# vim /var/lib/mysql/slow-query.log
/usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2017-10-05T04:39:11.408964Z
# User@Host: root[root] @ localhost [] Id: 3
# Query_time: 2.001395 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test;
SET timestamp=1507178351;
select sleep(2);復(fù)制代碼
我們可以看到剛才執(zhí)行了 2 秒的 SQL 語句被記錄下來了张症。
雖然在慢查詢?nèi)罩局杏涗洸樵兟?SQL 信息仓技，但是日志記錄的內(nèi)容密集且不易查閱。因此俗他，我們需要通過工具將 SQL 篩選出來脖捻。
MySQL 提供 mysqldumpslow 工具對日志進(jìn)行分析。我們可以使用 mysqldumpslow --help 查看命令相關(guān)用法兆衅。
常用參數(shù)如下：
-s：排序方式地沮，后邊接著如下參數(shù)
c：訪問次數(shù)
l：鎖定時(shí)間
r：返回記錄
t：查詢時(shí)間
al：平均鎖定時(shí)間
ar：平均返回記錄書
at：平均查詢時(shí)間
-t：返回前面多少條的數(shù)據(jù)
-g：翻遍搭配一個(gè)正則表達(dá)式，大小寫不敏感復(fù)制代碼
案例：
獲取返回記錄集最多的10個(gè)sql
mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log
獲取訪問次數(shù)最多的10個(gè)sql
mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log
獲取按照時(shí)間排序的前10條里面含有左連接的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log復(fù)制代碼
五羡亩、分析 SQL 語句
5.1 方式一：explain
篩選出有問題的 SQL摩疑，我們可以使用 MySQL 提供的 explain 查看 SQL 執(zhí)行計(jì)劃情況（關(guān)聯(lián)表，表查詢順序畏铆、索引使用情況等）雷袋。
用法：
explain select * from category;復(fù)制代碼
返回結(jié)果：
mysql> explain select * from category;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | category | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)復(fù)制代碼
字段解釋：
id：select 查詢序列號。id相同辞居，執(zhí)行順序由上至下片排；id不同寨腔，id值越大優(yōu)先級越高，越先被執(zhí)行
select_type：查詢數(shù)據(jù)的操作類型率寡，其值如下：
simple：簡單查詢，不包含子查詢或 union
primary:包含復(fù)雜的子查詢倚搬，最外層查詢標(biāo)記為該值
subquery：在 select 或 where 包含子查詢冶共，被標(biāo)記為該值
derived：在 from 列表中包含的子查詢被標(biāo)記為該值，MySQL 會遞歸執(zhí)行這些子查詢每界，把結(jié)果放在臨時(shí)表
union：若第二個(gè) select 出現(xiàn)在 union 之后捅僵，則被標(biāo)記為該值。若 union 包含在 from 的子查詢中眨层，外層 select 被標(biāo)記為 derived
union result：從 union 表獲取結(jié)果的 select復(fù)制代碼
table：顯示該行數(shù)據(jù)是關(guān)于哪張表
partitions：匹配的分區(qū)
type：表的連接類型庙楚，其值，性能由高到底排列如下：
system：表只有一行記錄趴樱，相當(dāng)于系統(tǒng)表
const：通過索引一次就找到馒闷，只匹配一行數(shù)據(jù)
eq_ref：唯一性索引掃描，對于每個(gè)索引鍵叁征，表中只有一條記錄與之匹配纳账。常用于主鍵或唯一索引掃描
ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行捺疼。用于=疏虫、< 或 > 操作符帶索引的列
range：只檢索給定范圍的行，使用一個(gè)索引來選擇行啤呼。一般使用between卧秘、>、<情況
index：只遍歷索引樹
ALL：全表掃描官扣，性能最差復(fù)制代碼
注：前5種情況都是理想情況的索引使用情況翅敌。通常優(yōu)化至少到range級別，最好能優(yōu)化到 ref
possible_keys：指出 MySQL 使用哪個(gè)索引在該表找到行記錄醇锚。如果該值為 NULL洒嗤，說明沒有使用索引，可以建立索引提高性能
key：顯示 MySQL 實(shí)際使用的索引研儒。如果為 NULL顶瞳，則沒有使用索引查詢
key_len：表示索引中使用的字節(jié)數(shù)，通過該列計(jì)算查詢中使用的索引的長度赶促。在不損失精確性的情況下液肌，長度越短越好
顯示的是索引字段的最大長度，并非實(shí)際使用長度ref：顯示該表的索引字段關(guān)聯(lián)了哪張表的哪個(gè)字段
rows：根據(jù)表統(tǒng)計(jì)信息及選用情況鸥滨，大致估算出找到所需的記錄或所需讀取的行數(shù)嗦哆，數(shù)值越小越好
filtered：返回結(jié)果的行數(shù)占讀取行數(shù)的百分比谤祖，值越大越好
extra： 包含不合適在其他列中顯示但十分重要的額外信息，常見的值如下：
using filesort：說明 MySQL 會對數(shù)據(jù)使用一個(gè)外部的索引排序老速，而不是按照表內(nèi)的索引順序進(jìn)行讀取粥喜。出現(xiàn)該值，應(yīng)該優(yōu)化 SQL
using temporary：使用了臨時(shí)表保存中間結(jié)果橘券，MySQL 在對查詢結(jié)果排序時(shí)使用臨時(shí)表额湘。常見于排序 order by 和分組查詢 group by。出現(xiàn)該值旁舰，應(yīng)該優(yōu)化 SQL
using index：表示相應(yīng)的 select 操作使用了覆蓋索引锋华，避免了訪問表的數(shù)據(jù)行，效率不錯(cuò)
using where：where 子句用于限制哪一行
using join buffer：使用連接緩存
distinct：發(fā)現(xiàn)第一個(gè)匹配后箭窜，停止為當(dāng)前的行組合搜索更多的行復(fù)制代碼
注意：出現(xiàn)前 2 個(gè)值毯焕，SQL 語句必須要優(yōu)化。
5.2 方式二：profiling
使用 profiling 命令可以了解 SQL 語句消耗資源的詳細(xì)信息（每個(gè)執(zhí)行步驟的開銷）磺樱。
5.2.1 查看 profile 開啟情況
select @@profiling;復(fù)制代碼
返回結(jié)果：
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)復(fù)制代碼
0 表示關(guān)閉狀態(tài),1 表示開啟
5.2.2 啟用 profile
set profiling = 1;復(fù)制代碼
返回結(jié)果：
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)復(fù)制代碼
在連接關(guān)閉后纳猫，profiling 狀態(tài)自動設(shè)置為關(guān)閉狀態(tài)。
5.2.3 查看執(zhí)行的 SQL 列表
show profiles;復(fù)制代碼
返回結(jié)果：
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------+
| 1 | 0.00062925 | select @@profiling |
| 2 | 0.00094150 | show tables |
| 3 | 0.00119125 | show databases |
| 4 | 0.00029750 | SELECT DATABASE() |
| 5 | 0.00025975 | show databases |
| 6 | 0.00023050 | show tables |
| 7 | 0.00042000 | show tables |
| 8 | 0.00260675 | desc role |
| 9 | 0.00074900 | select name,is_key from role |
+----------+------------+------------------------------+
9 rows in set, 1 warning (0.00 sec)復(fù)制代碼
該命令執(zhí)行之前坊罢，需要執(zhí)行其他 SQL 語句才有記錄续担。
5.2.4 查詢指定 ID 的執(zhí)行詳細(xì)信息
show profile for query Query_ID;復(fù)制代碼
返回結(jié)果：
mysql> show profile for query 9;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000207 |
| checking permissions | 0.000010 |
| Opening tables | 0.000042 |
| init | 0.000050 |
| System lock | 0.000012 |
| optimizing | 0.000003 |
| statistics | 0.000011 |
| preparing | 0.000011 |
| executing | 0.000002 |
| Sending data | 0.000362 |
| end | 0.000006 |
| query end | 0.000006 |
| closing tables | 0.000006 |
| freeing items | 0.000011 |
| cleaning up | 0.000013 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)復(fù)制代碼
每行都是狀態(tài)變化的過程以及它們持續(xù)的時(shí)間。Status 這一列和 show processlist 的 State 是一致的活孩。因此物遇，需要優(yōu)化的注意點(diǎn)與上文描述的一樣。
其中憾儒，Status 字段的值同樣可以參考末尾鏈接询兴。
5.2.5 獲取 CPU、 Block IO 等信息
show profile block io,cpu for query Query_ID;
show profile cpu,block io,memory,swaps,context switches,source for query Query_ID;
show profile all for query Query_ID;復(fù)制代碼
六起趾、優(yōu)化手段
主要以查詢優(yōu)化诗舰、索引使用和表結(jié)構(gòu)設(shè)計(jì)方面進(jìn)行講解。
6.1 查詢優(yōu)化
避免 SELECT *训裆，需要什么數(shù)據(jù)眶根，就查詢對應(yīng)的字段。
小表驅(qū)動大表边琉，即小的數(shù)據(jù)集驅(qū)動大的數(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)復(fù)制代碼
一些情況下录豺，可以使用連接代替子查詢耍属，因?yàn)槭褂?join，MySQL 不會在內(nèi)存中創(chuàng)建臨時(shí)表巩检。
適當(dāng)添加冗余字段，減少表關(guān)聯(lián)示启。
合理使用索引（下文介紹）兢哭。如：為排序、分組字段建立索引夫嗓，避免 filesort 的出現(xiàn)迟螺。
6.2 索引使用
6.2.1 適合使用索引的場景
主鍵自動創(chuàng)建唯一索引
頻繁作為查詢條件的字段
查詢中與其他表關(guān)聯(lián)的字段
查詢中排序的字段
查詢中統(tǒng)計(jì)或分組字段
6.2.2 不適合使用索引的場景
頻繁更新的字段
where 條件中用不到的字段
表記錄太少
經(jīng)常增刪改的表
字段的值的差異性不大或重復(fù)性高
6.2.3 索引創(chuàng)建和使用原則
單表查詢：哪個(gè)列作查詢條件，就在該列創(chuàng)建索引
多表查詢：left join 時(shí)舍咖，索引添加到右表關(guān)聯(lián)字段矩父；right join 時(shí)，索引添加到左表關(guān)聯(lián)字段
不要對索引列進(jìn)行任何操作（計(jì)算排霉、函數(shù)窍株、類型轉(zhuǎn)換）
索引列中不要使用 !=，<> 非等于
索引列不要為空攻柠，且不要使用 is null 或 is not null 判斷
索引字段是字符串類型球订，查詢條件的值要加''單引號,避免底層類型自動轉(zhuǎn)換
違背上述原則可能會導(dǎo)致索引失效，具體情況需要使用 explain 命令進(jìn)行查看
6.2.4 索引失效情況
除了違背索引創(chuàng)建和使用原則外瑰钮，如下情況也會導(dǎo)致索引失效：
模糊查詢時(shí)冒滩，以 % 開頭
使用 or 時(shí)，如：字段1（非索引）or 字段2（索引）會導(dǎo)致索引失效浪谴。
使用復(fù)合索引時(shí)开睡，不使用第一個(gè)索引列。
index(a,b,c) 苟耻，以字段 a,b,c 作為復(fù)合索引為例：
| 語句 | 索引是否生效 |
|---|---|
| where a = 1 | 是篇恒，字段 a 索引生效 |
| where a = 1 and b = 2 | 是，字段 a 和 b 索引生效 |
| where a = 1 and b = 2 and c = 3 | 是梁呈，全部生效 |
| where b = 2 或 where c = 3 | 否 |
| where a = 1 and c = 3 | 字段 a 生效婚度，字段 c 失效 |
| where a = 1 and b > 2 and c = 3 | 字段 a，b 生效，字段 c 失效 |
| where a = 1 and b like 'xxx%' and c = 3 | 字段 a蝗茁，b 生效醋虏，字段 c 失效 |
6.3 數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計(jì)
6.3.1 選擇合適的數(shù)據(jù)類型
使用可以存下數(shù)據(jù)最小的數(shù)據(jù)類型
使用簡單的數(shù)據(jù)類型。int 要比 varchar 類型在mysql處理簡單
盡量使用 tinyint哮翘、smallint颈嚼、mediumint 作為整數(shù)類型而非 int
盡可能使用 not null 定義字段，因?yàn)?null 占用4字節(jié)空間
盡量少用 text 類型,非用不可時(shí)最好考慮分表
盡量使用 timestamp 而非 datetime
單表不要有太多字段饭寺，建議在 20 以內(nèi)
6.3.2 表的拆分
當(dāng)數(shù)據(jù)庫中的數(shù)據(jù)非常大時(shí)阻课，查詢優(yōu)化方案也不能解決查詢速度慢的問題時(shí)，我們可以考慮拆分表艰匙，讓每張表的數(shù)據(jù)量變小限煞，從而提高查詢效率。
垂直拆分：將表中多個(gè)列分開放到不同的表中员凝。例如用戶表中一些字段經(jīng)常被訪問署驻，將這些字段放在一張表中，另外一些不常用的字段放在另一張表中健霹。
插入數(shù)據(jù)時(shí)旺上，使用事務(wù)確保兩張表的數(shù)據(jù)一致性。水平拆分：按照行進(jìn)行拆分糖埋。例如用戶表中宣吱，使用用戶ID，對用戶ID取10的余數(shù)瞳别，將用戶數(shù)據(jù)均勻的分配到0~9的10個(gè)用戶表中征候。查找時(shí)也按照這個(gè)規(guī)則查詢數(shù)據(jù)。
6.3.3 讀寫分離
一般情況下對數(shù)據(jù)庫而言都是“讀多寫少”洒试。換言之倍奢，數(shù)據(jù)庫的壓力多數(shù)是因?yàn)榇罅康淖x取數(shù)據(jù)的操作造成的。我們可以采用數(shù)據(jù)庫集群的方案垒棋，使用一個(gè)庫作為主庫卒煞，負(fù)責(zé)寫入數(shù)據(jù)；其他庫為從庫叼架，負(fù)責(zé)讀取數(shù)據(jù)畔裕。這樣可以緩解對數(shù)據(jù)庫的訪問壓力。
七乖订、服務(wù)器參數(shù)調(diào)優(yōu)
7.1 內(nèi)存相關(guān)
sort_buffer_size 排序緩沖區(qū)內(nèi)存大小
join_buffer_size 使用連接緩沖區(qū)大小
read_buffer_size 全表掃描時(shí)分配的緩沖區(qū)大小
7.2 IO 相關(guān)
Innodb_log_file_size 事務(wù)日志大小
Innodb_log_files_in_group 事務(wù)日志個(gè)數(shù)
Innodb_log_buffer_size 事務(wù)日志緩沖區(qū)大小
Innodb_flush_log_at_trx_commit 事務(wù)日志刷新策略 扮饶，其值如下：
0：每秒進(jìn)行一次 log 寫入 cache，并 flush log 到磁盤
1：在每次事務(wù)提交執(zhí)行 log 寫入 cache乍构，并 flush log 到磁盤
2：每次事務(wù)提交甜无，執(zhí)行 log 數(shù)據(jù)寫到 cache，每秒執(zhí)行一次 flush log 到磁盤
7.3 安全相關(guān)
expire_logs_days 指定自動清理 binlog 的天數(shù)
max_allowed_packet 控制 MySQL 可以接收的包的大小
skip_name_resolve 禁用 DNS 查找
read_only 禁止非 super 權(quán)限用戶寫權(quán)限
skip_slave_start 級你用 slave 自動恢復(fù)
7.4 其他
max_connections 控制允許的最大連接數(shù)
tmp_table_size 臨時(shí)表大小
max_heap_table_size 最大內(nèi)存表大小
筆者并沒有使用這些參數(shù)對 MySQL 服務(wù)器進(jìn)行調(diào)優(yōu)，具體詳情介紹和性能效果請參考文章末尾的資料或另行百度岂丘。
八陵究、硬件選購和參數(shù)優(yōu)化
硬件的性能直接決定 MySQL 數(shù)據(jù)庫的性能。硬件的性能瓶頸奥帘，直接決定 MySQL 數(shù)據(jù)庫的運(yùn)行數(shù)據(jù)和效率铜邮。
作為軟件開發(fā)程序員，我們主要關(guān)注軟件方面的優(yōu)化內(nèi)容寨蹋，以下硬件方面的優(yōu)化作為了解即可
8.1 內(nèi)存相關(guān)
內(nèi)存的 IO 比硬盤的速度快很多松蒜，可以增加系統(tǒng)的緩沖區(qū)容量，使數(shù)據(jù)在內(nèi)存停留的時(shí)間更長已旧，以減少磁盤的 IO
8.2 磁盤 I/O 相關(guān)
使用 SSD 或 PCle SSD 設(shè)備秸苗，至少獲得數(shù)百倍甚至萬倍的 IOPS 提升
購置陣列卡同時(shí)配備 CACHE 及 BBU 模塊，可以明顯提升 IOPS
盡可能選用 RAID-10运褪，而非 RAID-5
8.3 配置 CUP 相關(guān)
在服務(wù)器的 BIOS 設(shè)置中难述，調(diào)整如下配置：
選擇 Performance Per Watt Optimized（DAPC）模式，發(fā)揮 CPU 最大性能
關(guān)閉 C1E 和 C States 等選項(xiàng)吐句，提升 CPU 效率
Memory Frequency（內(nèi)存頻率）選擇 Maximum Performance
九、參考資料
- dev.mysql.com/doc/refman/… show status 語法
- dev.mysql.com/doc/refman/… show processlist 語法
- dev.mysql.com/doc/refman/… 線程狀態(tài)
- dev.mysql.com/doc/refman/… explain 語法
- dev.mysql.com/doc/refman/… show profile 語法
- blog.csdn.net/nightelve/a… MySQL 服務(wù)器參數(shù)調(diào)優(yōu)
- blog.csdn.net/qq_22929803… MySQL 服務(wù)器參數(shù)調(diào)優(yōu)
- blog.chinaunix.net/uid-1164064…
- segmentfault.com/a/119000000…
- blog.csdn.net/gzh0222/art…
