mysql官網(wǎng)：https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html
一、查詢(xún)執(zhí)行過(guò)程
二、優(yōu)化思路
數(shù)據(jù)查詢(xún)慢，不代表sql語(yǔ)句寫(xiě)法有問(wèn)題，用一張流程圖展示MySQL優(yōu)化思路
主要包括：
1、適當(dāng)添加索引（四種：普通索引、主鍵索引、唯一索引、全文索引）
2、分表技術(shù)（水平分割、垂直分割）
3、讀寫(xiě)（寫(xiě)：update/delete/add）分離
4、對(duì)mysql配置優(yōu)化（my.conf）
5、mysql服務(wù)器硬件升級(jí)
2.1mysql查詢(xún)執(zhí)行順序
示例SQL：
SELECT *
FROM user
LEFT JOIN order ON user.id = order.uid
WHERE order.price > 1000
GROUP BY user.name
HAVING count(1) > 5
ORDER BY user.name
LIMIT 0,10
1、FROM（將最近的兩張表，進(jìn)行笛卡爾積）—VT1
2、ON（將VT1按照它的條件進(jìn)行過(guò)濾）—VT2
3、LEFT JOIN（保留左表的記錄）—VT3
4、WHERE（過(guò)濾VT3中的記錄）–VT4…VTn
5、GROUP BY（對(duì)VT4的記錄進(jìn)行分組）—VT5
6、HAVING（對(duì)VT5中的記錄進(jìn)行過(guò)濾）—VT6
7、SELECT（對(duì)VT6中的記錄，選取指定的列）–VT7
8、ORDER BY（對(duì)VT7的記錄進(jìn)行排序）–游標(biāo)
9、LIMIT（對(duì)排序之后的值進(jìn)行分頁(yè)）
WHERE條件執(zhí)行順序（影響性能）
1、MYSQL：從左往右去執(zhí)行WHERE條件的。
2、Oracle：從右往左去執(zhí)行WHERE條件的。
結(jié)論：寫(xiě)WHERE條件的時(shí)候，優(yōu)先級(jí)高的部分要去編寫(xiě)過(guò)濾力度最大的條件語(yǔ)句。
三、查看MySQL服務(wù)器運(yùn)行的狀態(tài)值/服務(wù)器配置信息
3.1 查詢(xún)MySQL 服務(wù)器運(yùn)行的狀態(tài)值
如果系統(tǒng)的并發(fā)請(qǐng)求數(shù)不高，且查詢(xún)速度慢，可以忽略該步驟直接進(jìn)行 SQL 語(yǔ)句調(diào)優(yōu)步驟。
執(zhí)行命令：show status/show global status 可以查看所有的性能會(huì)話(huà)參數(shù)/全局參數(shù)；或 show status like '參數(shù)名稱(chēng)' 可以查看指定參數(shù)名稱(chēng)的性能參數(shù)，一般某一類(lèi)參數(shù)都有相同的前綴。
返回結(jié)果太多，我們主要關(guān)注“Queries”、“Threadsconnected” 和 “Threadsrunning” 的值，即查詢(xún)次數(shù)、線(xiàn)程連接數(shù)和線(xiàn)程運(yùn)行數(shù)。
3.2 查詢(xún)MySQL服務(wù)器配置信息
執(zhí)行命令：show variables/show global variables
3.3 status 與 variables 區(qū)別
status查看的參數(shù)值是由MySQL自己統(tǒng)計(jì)計(jì)算得到的。它是MySQL服務(wù)運(yùn)行狀態(tài)具體的量化體現(xiàn)。都是不可以修改的，也就是不能通過(guò)setxxx=yyy;的方式來(lái)改變它的值的。參數(shù)大多數(shù)以大寫(xiě)的英文字母開(kāi)頭。
variables查看的參數(shù)是MySQL服務(wù)的配置參數(shù)，在啟動(dòng)MySQL服務(wù)的時(shí)候，是可以修改具體的參數(shù)值來(lái)達(dá)到對(duì)MySQL進(jìn)行動(dòng)態(tài)配置的目的，通常配置在MySQL的my.cnf配置文件中。這些參數(shù)中，有些動(dòng)態(tài)的參數(shù)可以通過(guò)setxxx=yyy;的方式來(lái)動(dòng)態(tài)修改。這種參數(shù)大多數(shù)以小寫(xiě)的英文字母開(kāi)頭。
3.4 什么是全局參數(shù)？什么是會(huì)話(huà)session級(jí)別的參數(shù)？
全局參數(shù)：某個(gè)參數(shù)的值設(shè)置之后，對(duì)所有連接到MySQL服務(wù)的用戶(hù)都生效，所有用戶(hù)登錄到MySQL后，該參數(shù)都是使用同一個(gè)值。
會(huì)話(huà)級(jí)別的參數(shù)：某個(gè)參數(shù)的值設(shè)置之后，只對(duì)當(dāng)前連接到MySQL服務(wù)器的當(dāng)前會(huì)話(huà)生效，當(dāng)前會(huì)話(huà)退出后，這個(gè)參數(shù)就恢復(fù)了默認(rèn)值。并且當(dāng)連接A登錄的時(shí)候，這個(gè)參數(shù)對(duì)A生效。但是對(duì)于新開(kāi)一個(gè)連接服務(wù)B，在B的會(huì)話(huà)中，設(shè)置的參數(shù)并不會(huì)生效。
3.5 核心參數(shù)解析
3.5.1 慢查詢(xún)
mysql> show variables like '%slow%';
+——————+——-+
| Variable_name | Value |
+——————+——-+
| log_slow_queries | ON |
| slow_launch_time | 2 |
+——————+——-+
mysql> show global status like '%slow%';
+———————+——-+
| Variable_name | Value |
+———————+——-+
| Slow_launch_threads | 0 |
| Slow_queries | 4148 |
+———————+——-+
配置中打開(kāi)了記錄慢查詢(xún)，執(zhí)行時(shí)間超過(guò)2秒的即為慢查詢(xún)，系統(tǒng)顯示有4148個(gè)慢查詢(xún)，你可以分析慢查詢(xún)?nèi)罩?，找出有?wèn)題的SQL語(yǔ)句，慢查詢(xún)時(shí)間不宜設(shè)置過(guò)長(zhǎng)，否則意義不大，最好在5秒以?xún)?nèi)，如果你需要微秒級(jí)別的慢查詢(xún)，可以考慮給MySQL打補(bǔ)丁：http://www.percona.com/docs/wiki/release:start，記得找對(duì)應(yīng)的版本。
打開(kāi)慢查詢(xún)?nèi)罩究赡軙?huì)對(duì)系統(tǒng)性能有一點(diǎn)點(diǎn)影響，如果你的MySQL是主－從結(jié)構(gòu)，可以考慮打開(kāi)其中一臺(tái)從服務(wù)器的慢查詢(xún)?nèi)罩荆@樣既可以監(jiān)控慢查詢(xún)，對(duì)系統(tǒng)性能影響又小。
3.5.2 連接數(shù)
經(jīng)常會(huì)遇見(jiàn)”MySQL: ERROR 1040: Too many connections”的情況，一種是訪(fǎng)問(wèn)量確實(shí)很高，MySQL服務(wù)器抗不住，這個(gè)時(shí)候就要考慮增加從服務(wù)器分散讀壓力，另外一種情況是MySQL配置文件中max_connections值過(guò)小：
mysql> show variables like 'max_connections';
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 256 |
+—————–+——-+
這臺(tái)MySQL服務(wù)器最大連接數(shù)是256，然后查詢(xún)一下服務(wù)器響應(yīng)的最大連接數(shù)：
mysql> show global status like 'Max_used_connections';
MySQL服務(wù)器過(guò)去的最大連接數(shù)是245，沒(méi)有達(dá)到服務(wù)器連接數(shù)上限256，應(yīng)該沒(méi)有出現(xiàn)1040錯(cuò)誤，比較理想的設(shè)置是
Max_used_connections / max_connections * 100% ≈ 85%
最大連接數(shù)占上限連接數(shù)的85％左右，如果發(fā)現(xiàn)比例在10%以下，MySQL服務(wù)器連接數(shù)上限設(shè)置的過(guò)高了。
3.5.3 進(jìn)程使用情況
mysql> show global status like 'Thread%';
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 46 |
| Threads_connected | 2 |
| Threads_created | 570 |
| Threads_running | 1 |
+——————-+——-+
如果我們?cè)贛ySQL服務(wù)器配置文件中設(shè)置了thread_cache_size，當(dāng)客戶(hù)端斷開(kāi)之后，服務(wù)器處理此客戶(hù)的線(xiàn)程將會(huì)緩存起來(lái)以響應(yīng)下一個(gè)客戶(hù)而不是銷(xiāo)毀（前提是緩存數(shù)未達(dá)上限）。Threads_created表示創(chuàng)建過(guò)的線(xiàn)程數(shù)，如果發(fā)現(xiàn)Threads_created值過(guò)大的話(huà)，表明MySQL服務(wù)器一直在創(chuàng)建線(xiàn)程，這也是比較耗資源，可以適當(dāng)增加配置文件中thread_cache_size值，查詢(xún)服務(wù)器thread_cache_size配置：
mysql> show variables like 'thread_cache_size';
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 64 |
+——————-+——-+
四、獲取需要優(yōu)化的SQL語(yǔ)句
4.1 方式一：查看運(yùn)行的線(xiàn)程
執(zhí)行命令：show processlist / show full processlist
返回結(jié)果：
從返回結(jié)果中我們可以了解該線(xiàn)程執(zhí)行了什么命令/SQL 語(yǔ)句以及執(zhí)行的時(shí)間。實(shí)際應(yīng)用中，查詢(xún)的返回結(jié)果會(huì)有 N 條記錄。
其中，返回的 State 的值是我們判斷性能好壞的關(guān)鍵，其值出現(xiàn)如下內(nèi)容，則該行記錄的 SQL 語(yǔ)句需要優(yōu)化：
- Converting HEAP to MyISAM # 查詢(xún)結(jié)果太大時(shí)，把結(jié)果放到磁盤(pán)，嚴(yán)重
- Create tmp table #創(chuàng)建臨時(shí)表，嚴(yán)重
- Copying to tmp table on disk #把內(nèi)存臨時(shí)表復(fù)制到磁盤(pán)，嚴(yán)重
- locked #被其他查詢(xún)鎖住，嚴(yán)重
- loggin slow query #記錄慢查詢(xún)
- Sorting result #排序
4.2 方式二：開(kāi)啟慢查詢(xún)?nèi)罩荆ㄍ扑]）
在配置文件 my.cnf 中的 [mysqld] 一行下邊添加幾個(gè)參數(shù)：
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
其中，slowquerylog = 1 表示開(kāi)啟慢查詢(xún)；slowquerylogfile 表示慢查詢(xún)?nèi)罩敬娣诺奈恢?；longquerytime = 2 表示查詢(xún) >=2 秒才記錄日志；logqueriesnotusing_indexes = 1 記錄沒(méi)有使用索引的 SQL 語(yǔ)句。
注意：slowquerylog_file 的路徑不能隨便寫(xiě)，否則 MySQL 服務(wù)器可能沒(méi)有權(quán)限將日志文件寫(xiě)到指定的目錄中。
修改保存文件后，重啟 MySQL 服務(wù)。在 /var/lib/mysql/ 目錄下會(huì)創(chuàng)建 slow-query.log 日志文件。連接 MySQL 服務(wù)端執(zhí)行如下命令可以查看配置情況。
show variables like 'slow_query%';
show variables like 'long_query_time';
測(cè)試慢查詢(xún)?nèi)罩荆?/p>
打開(kāi)慢查詢(xún)?nèi)罩疚募?/p>
我們可以看到剛才執(zhí)行了 2 秒的 SQL 語(yǔ)句被記錄下來(lái)了。
雖然在慢查詢(xún)?nèi)罩局杏涗洸樵?xún)慢的 SQL 信息，但是日志記錄的內(nèi)容密集且不易查閱。因此，我們需要通過(guò)工具將 SQL 篩選出來(lái)。
MySQL 提供 mysqldumpslow 工具對(duì)日志進(jìn)行分析。我們可以使用 mysqldumpslow --help 查看命令相關(guān)用法。
常用參數(shù)如下：
案例：
獲取返回記錄集最多的10個(gè)sql
mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log
獲取訪(fǎng)問(wèn)次數(shù)最多的10個(gè)sql
mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log
獲取按照時(shí)間排序的前10條里面含有左連接的查詢(xún)語(yǔ)句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log
五、分析SQL語(yǔ)句
5.1 方式一：explain
篩選出有問(wèn)題的 SQL，我們可以使用 MySQL 提供的 explain 查看 SQL 執(zhí)行計(jì)劃情況（關(guān)聯(lián)表，表查詢(xún)順序、索引使用情況等）。
用法：explain select * from user;
返回結(jié)果：
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
注意：在5.7以前的版本中，想要顯示partitions需要使用explain partitions命令；想要顯示filtered需要使用explain extended命令。在5.7版本后，默認(rèn)explain直接顯示partitions和filtered中的信息。
字段解釋?zhuān)?/p>
- id：select 查詢(xún)序列號(hào)。id相同，執(zhí)行順序由上至下；id不同，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行
- select_type：查詢(xún)數(shù)據(jù)的操作類(lèi)型，其值如下：
- simple：簡(jiǎn)單查詢(xún)，不包含子查詢(xún)或 union
- primary:包含復(fù)雜的子查詢(xún)，最外層查詢(xún)標(biāo)記為該值
- subquery：在 select 或 where 包含子查詢(xún)，被標(biāo)記為該值
- derived：在 from 列表中包含的子查詢(xún)被標(biāo)記為該值，MySQL 會(huì)遞歸執(zhí)行這些子查詢(xún)，把結(jié)果放在臨時(shí)表
- union：若第二個(gè) select 出現(xiàn)在 union 之后，則被標(biāo)記為該值。若 union 包含在 from 的子查詢(xún)中，外層 select 被標(biāo)記為 derived
- union result：從 union 表獲取結(jié)果的 select
- table：顯示該行數(shù)據(jù)是關(guān)于哪張表
- partitions：匹配的分區(qū)
- type：表的連接類(lèi)型，其值，性能由高到底排列如下：
- system：表只有一行記錄，相當(dāng)于系統(tǒng)表
- const：通過(guò)索引一次就找到，只匹配一行數(shù)據(jù)
- eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常用于主鍵或唯一索引掃描
- ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行。用于=、< 或 > 操作符帶索引的列
- range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行。一般使用between、>、<情況
- index：只遍歷索引樹(shù)
- ALL：全表掃描，性能最差
注：前5種情況都是理想情況的索引使用情況。通常優(yōu)化至少到range級(jí)別，最好能優(yōu)化到 ref
- possible_keys：指出 MySQL 使用哪個(gè)索引在該表找到行記錄。如果該值為 NULL，說(shuō)明沒(méi)有使用索引，可以建立索引提高性能
- key：顯示 MySQL 實(shí)際使用的索引。如果為 NULL，則沒(méi)有使用索引查詢(xún)
- key_len：表示索引中使用的字節(jié)數(shù)，通過(guò)該列計(jì)算查詢(xún)中使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好 顯示的是索引字段的最大長(zhǎng)度，并非實(shí)際使用長(zhǎng)度
- ref：顯示該表的索引字段關(guān)聯(lián)了哪張表的哪個(gè)字段
- rows：根據(jù)表統(tǒng)計(jì)信息及選用情況，大致估算出找到所需的記錄或所需讀取的行數(shù)，數(shù)值越小越好
- filtered：返回結(jié)果的行數(shù)占讀取行數(shù)的百分比，值越大越好
- extra：包含不合適在其他列中顯示但十分重要的額外信息，常見(jiàn)的值如下：
- using filesort：說(shuō)明 MySQL 會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。出現(xiàn)該值，應(yīng)該優(yōu)化 SQL
- using temporary：使用了臨時(shí)表保存中間結(jié)果，MySQL 在對(duì)查詢(xún)結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)于排序 order by 和分組查詢(xún) group by。出現(xiàn)該值，應(yīng)該優(yōu)化 SQL
- using index：表示相應(yīng)的 select 操作使用了覆蓋索引，避免了訪(fǎng)問(wèn)表的數(shù)據(jù)行，效率不錯(cuò)
- using where：where 子句用于限制哪一行
- using join buffer：使用連接緩存
- distinct：發(fā)現(xiàn)第一個(gè)匹配后，停止為當(dāng)前的行組合搜索更多的行
注意：出現(xiàn)前 2 個(gè)值，SQL 語(yǔ)句必須要優(yōu)化。
5.2 方式二：profiling
使用 profiling 命令可以了解 SQL 語(yǔ)句消耗資源的詳細(xì)信息（每個(gè)執(zhí)行步驟的開(kāi)銷(xiāo)）。
5.2.1 查看 profile 開(kāi)啟情況
執(zhí)行命令：select @@profiling;
返回結(jié)果：
0 表示關(guān)閉狀態(tài),1 表示開(kāi)啟
5.2.2 啟用 profile
執(zhí)行命令：set profiling = 1;
返回結(jié)果：
在連接關(guān)閉后，profiling 狀態(tài)自動(dòng)設(shè)置為關(guān)閉狀態(tài)。
5.2.3 查看執(zhí)行的 SQL 列表
執(zhí)行命令：show profiles;
返回結(jié)果：
該命令執(zhí)行之前，需要執(zhí)行其他 SQL 語(yǔ)句才有記錄。
5.2.4 查詢(xún)指定 ID 的執(zhí)行詳細(xì)信息
執(zhí)行命令：show profile for query Query_ID;
返回結(jié)果：
每行都是狀態(tài)變化的過(guò)程以及它們持續(xù)的時(shí)間。Status 這一列和 show processlist 的 State 是一致的。因此，需要優(yōu)化的注意點(diǎn)與上文描述的一樣。
六、優(yōu)化手段
6.1 查詢(xún)優(yōu)化
- 避免 SELECT *，需要什么數(shù)據(jù)，就查詢(xún)對(duì)應(yīng)的字段。
- 小表驅(qū)動(dòng)大表，即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集。如：以 A，B 兩表為例，兩表通過(guò) 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)
- 一些情況下，可以使用連接代替子查詢(xún)，因?yàn)槭褂?join，MySQL 不會(huì)在內(nèi)存中創(chuàng)建臨時(shí)表。
- 適當(dāng)添加冗余字段，減少表關(guān)聯(lián)。
- 合理使用索引。如：為排序、分組字段建立索引，避免 filesort 的出現(xiàn)。
6.2 索引使用
6.2.1 適合使用索引的場(chǎng)景
- 主鍵自動(dòng)創(chuàng)建唯一索引
- 頻繁作為查詢(xún)條件的字段
- 查詢(xún)中與其他表關(guān)聯(lián)的字段
- 查詢(xún)中排序的字段
- 查詢(xún)中統(tǒng)計(jì)或分組字段
6.2.2 不適合使用索引的場(chǎng)景
- 頻繁更新的字段
- where 條件中用不到的字段
- 表記錄太少
- 經(jīng)常增刪改的表
- 字段的值的差異性不大或重復(fù)性高
6.2.3 索引創(chuàng)建和使用原則
- 單表查詢(xún)：哪個(gè)列作查詢(xún)條件，就在該列創(chuàng)建索引
- 多表查詢(xún)：left join 時(shí)，索引添加到右表關(guān)聯(lián)字段；right join 時(shí)，索引添加到左表關(guān)聯(lián)字段
- 不要對(duì)索引列進(jìn)行任何操作（計(jì)算、函數(shù)、類(lèi)型轉(zhuǎn)換）
- 索引列中不要使用 !=，<> 非等于
- 索引列不要為空，且不要使用 is null 或 is not null 判斷
- 索引字段是字符串類(lèi)型，查詢(xún)條件的值要加''單引號(hào),避免底層類(lèi)型自動(dòng)轉(zhuǎn)換
- 違背上述原則可能會(huì)導(dǎo)致索引失效，具體情況需要使用 explain 命令進(jìn)行查看
6.2.4 索引失效情況
除了違背索引創(chuàng)建和使用原則外，如下情況也會(huì)導(dǎo)致索引失效：
- 模糊查詢(xún)時(shí)，以 % 開(kāi)頭
- 使用 or 時(shí)，如：字段1（非索引）or 字段2（索引）會(huì)導(dǎo)致索引失效。
- 使用復(fù)合索引時(shí)，不使用第一個(gè)索引列。index(a,b,c) ，以字段 a,b,c 作為復(fù)合索引為例：
注意：檢索字段加了索引，但是范圍查詢(xún)時(shí)mysql優(yōu)化器不一定會(huì)使用該索引，因?yàn)槿绻樵?xún)的數(shù)據(jù)較多，優(yōu)化器會(huì)選擇走全表掃描
6.3 數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)
6.3.1 選擇合適的數(shù)據(jù)類(lèi)型
使用可以存下數(shù)據(jù)最小的數(shù)據(jù)類(lèi)型
- 使用簡(jiǎn)單的數(shù)據(jù)類(lèi)型。int 要比 varchar 類(lèi)型在mysql處理簡(jiǎn)單
- 盡量使用 tinyint、smallint、mediumint 作為整數(shù)類(lèi)型而非 int
- 盡可能使用 not null 定義字段，因?yàn)?null 占用4字節(jié)空間
- 盡量少用 text 類(lèi)型,非用不可時(shí)最好考慮分表
- 盡量使用 timestamp 而非 datetime
- 單表不要有太多字段，建議在 20 以?xún)?nèi)
6.3.2 表的拆分
當(dāng)數(shù)據(jù)庫(kù)中的數(shù)據(jù)非常大時(shí)，查詢(xún)優(yōu)化方案也不能解決查詢(xún)速度慢的問(wèn)題時(shí)，我們可以考慮拆分表，讓每張表的數(shù)據(jù)量變小，從而提高查詢(xún)效率。
- 垂直拆分：將表中多個(gè)列分開(kāi)放到不同的表中。例如用戶(hù)表中一些字段經(jīng)常被訪(fǎng)問(wèn)，將這些字段放在一張表中，另外一些不常用的字段放在另一張表中。插入數(shù)據(jù)時(shí)，使用事務(wù)確保兩張表的數(shù)據(jù)一致性。
- 水平拆分：按照行進(jìn)行拆分。例如用戶(hù)表中，使用用戶(hù)ID，對(duì)用戶(hù)ID取10的余數(shù)，將用戶(hù)數(shù)據(jù)均勻的分配到0~9的10個(gè)用戶(hù)表中。查找時(shí)也按照這個(gè)規(guī)則查詢(xún)數(shù)據(jù)。
6.3.3 讀寫(xiě)分離
一般情況下對(duì)數(shù)據(jù)庫(kù)而言都是“讀多寫(xiě)少”。換言之，數(shù)據(jù)庫(kù)的壓力多數(shù)是因?yàn)榇罅康淖x取數(shù)據(jù)的操作造成的。我們可以采用數(shù)據(jù)庫(kù)集群的方案，使用一個(gè)庫(kù)作為主庫(kù)，負(fù)責(zé)寫(xiě)入數(shù)據(jù)；其他庫(kù)為從庫(kù)，負(fù)責(zé)讀取數(shù)據(jù)。這樣可以緩解對(duì)數(shù)據(jù)庫(kù)的訪(fǎng)問(wèn)壓力。
6.4 補(bǔ)充
6.4.1 MySQL自帶壓力測(cè)試工具mysqlslap
常用參數(shù) [options] 詳細(xì)說(shuō)明：
-u:連接數(shù)據(jù)庫(kù)用戶(hù)名
-p:鏈接數(shù)據(jù)庫(kù)密碼
--auto-generate-sql, -a 自動(dòng)生成測(cè)試表和數(shù)據(jù)，表示用mysqlslap工具自己生成的SQL腳本來(lái)測(cè)試并發(fā)壓力。
--auto-generate-sql-load-type=type 測(cè)試語(yǔ)句的類(lèi)型。代表要測(cè)試的環(huán)境是讀操作還是寫(xiě)操作還是兩者混合的。取值包括：read，key，write，update和mixed(默認(rèn))。
--auto-generate-sql-add-auto-increment 代表對(duì)生成的表自動(dòng)添加auto_increment列，從5.1.18版本開(kāi)始支持。
--number-char-cols=N, -x N 自動(dòng)生成的測(cè)試表中包含多少個(gè)字符類(lèi)型的列，默認(rèn)1
--number-int-cols=N, -y N 自動(dòng)生成的測(cè)試表中包含多少個(gè)數(shù)字類(lèi)型的列，默認(rèn)1
--number-of-queries=N 總的測(cè)試查詢(xún)次數(shù)(并發(fā)客戶(hù)數(shù)×每客戶(hù)查詢(xún)次數(shù))
--query=name,-q 使用自定義腳本執(zhí)行測(cè)試，例如可以調(diào)用自定義的一個(gè)存儲(chǔ)過(guò)程或者sql語(yǔ)句來(lái)執(zhí)行測(cè)試。
--create-schema 代表自定義的測(cè)試庫(kù)名稱(chēng)，測(cè)試的schema，MySQL中schema也就是database。
--commint=N 多少條DML后提交一次。
--compress, -C 如果服務(wù)器和客戶(hù)端支持都?jí)嚎s，則壓縮信息傳遞。
--concurrency=N, -c N 表示并發(fā)量，也就是模擬多少個(gè)客戶(hù)端同時(shí)執(zhí)行select?？芍付ǘ鄠€(gè)值，以逗號(hào)或者--delimiter參數(shù)指定的值做為分隔符。例如：--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要測(cè)試的引擎，可以有多個(gè)，用分隔符隔開(kāi)。例如：--engines=myisam,innodb。
--iterations=N, -i N 測(cè)試執(zhí)行的迭代次數(shù)，代表要在不同并發(fā)環(huán)境下，各自運(yùn)行測(cè)試多少次。
--only-print 只打印測(cè)試語(yǔ)句而不實(shí)際執(zhí)行。
--detach=N 執(zhí)行N條語(yǔ)句后斷開(kāi)重連。
--debug-info, -T 打印內(nèi)存和CPU的相關(guān)信息。
說(shuō)明：測(cè)試的過(guò)程需要生成測(cè)試表，插入測(cè)試數(shù)據(jù)，這個(gè)mysqlslap可以自動(dòng)生成，默認(rèn)生成一個(gè)mysqlslap的schema，如果已經(jīng)存在則先刪除?？梢杂?-only-print來(lái)打印實(shí)際的測(cè)試過(guò)程，整個(gè)測(cè)試完成后不會(huì)在數(shù)據(jù)庫(kù)中留下痕跡。
各種測(cè)試參數(shù)實(shí)例（-p后面跟的是mysql的root密碼）：
單線(xiàn)程測(cè)試。測(cè)試做了什么。
mysqlslap -a -uroot -p123456
多線(xiàn)程測(cè)試。使用–concurrency來(lái)模擬并發(fā)連接。
mysqlslap -a -c 100 -uroot -p123456
迭代測(cè)試。用于需要多次執(zhí)行測(cè)試得到平均值。
mysqlslap -a -i 10 -uroot -p123456
mysqlslap ---auto-generate-sql-add-autoincrement -a -uroot -p123456
mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456
mysqlslap -a --auto-generate-secondary-indexes=3 -uroot -p123456
mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456
mysqlslap --create-schema world -q "select count(*) from City" -uroot -p123456
mysqlslap -a -e innodb -uroot -p123456
mysqlslap -a --number-of-queries=10 -uroot -p123456
測(cè)試同時(shí)不同的存儲(chǔ)引擎的性能進(jìn)行對(duì)比：
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456
執(zhí)行一次測(cè)試，分別50和100個(gè)并發(fā)，執(zhí)行1000次總查詢(xún)：
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456
50和100個(gè)并發(fā)分別得到一次測(cè)試結(jié)果(Benchmark)，并發(fā)數(shù)越多，執(zhí)行完所有查詢(xún)的時(shí)間越長(zhǎng)。為了準(zhǔn)確起見(jiàn)，可以多迭代測(cè)試幾次:
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456
七、補(bǔ)充
7.1 數(shù)據(jù)表設(shè)計(jì)建議
《阿里巴巴Java開(kāi)發(fā)手冊(cè)》，數(shù)據(jù)庫(kù)的表設(shè)計(jì)允許適當(dāng)冗余，以提升SQL查詢(xún)的性能，避免表的關(guān)聯(lián)查詢(xún)。
7.1.1 適度冗余，減少join的關(guān)聯(lián)
冗余更新頻率不高，但是查詢(xún)頻率極高的字段。如訂單中的商品名稱(chēng)。
7,1.2 大字段垂直拆分
比如商品SPU的商品的描述
7.1.3 大表水平拆分
比如優(yōu)惠券、帖子等
7.1.4 合適的數(shù)據(jù)類(lèi)型
如果數(shù)據(jù)量一樣，但數(shù)據(jù)類(lèi)型更小的話(huà)，數(shù)據(jù)存放同樣的數(shù)據(jù)就會(huì)占用更少的空間，這樣檢索同樣的數(shù)據(jù)所帶來(lái)的IO消耗自然會(huì)降低，性能也就很自然的得到提升。此外，MySQL 對(duì)不同類(lèi)型的數(shù)據(jù)，處理方式也不一樣，比如在運(yùn)算或者排序操作中，越簡(jiǎn)單的數(shù)據(jù)類(lèi)型操作性能越高，所以對(duì)于要頻繁進(jìn)行運(yùn)算或者排序的字段盡量選擇簡(jiǎn)單的數(shù)據(jù)類(lèi)型。
7.2 SQL優(yōu)化建議
7.2.1 聯(lián)合索引盡量覆蓋條件
比如可以設(shè)計(jì)一個(gè)或者兩三個(gè)聯(lián)合索引(盡量少建單值索引)，讓每一個(gè)聯(lián)合索引都盡量去包含sql語(yǔ)句里的where、order by、group by的字段，還要確保這些聯(lián)合索引的字段順序盡量滿(mǎn)足sql查詢(xún)的最左前綴原則。
7.2.2 不要在小基數(shù)字段上建立索引
索引基數(shù)是指這個(gè)字段在表里總共有多少個(gè)不同的值，比如一張表總共100萬(wàn)行記錄，其中有個(gè)性別字段，其值不是男就是女，那么該字段的基數(shù)就是2。如果對(duì)這種小基數(shù)字段建立索引的話(huà)，還不如全表掃描了，因?yàn)槟愕乃饕龢?shù)里就包含男和女兩種值，根本沒(méi)法進(jìn)行快速的二分查找，那用索引就沒(méi)有太大的意義了。一般建立索引，盡量使用那些基數(shù)比較大的字段，就是值比較多的字段，那么才能發(fā)揮出B+樹(shù)快速二分查 找的優(yōu)勢(shì)來(lái)。
7.2.3 where與order by沖突時(shí)優(yōu)先where
因?yàn)榇蠖鄶?shù)情況基于索引進(jìn)行where篩選往往可以最快速度篩選出你要的少部分?jǐn)?shù)據(jù)，然后做排序的成本可能會(huì)小很多。
7.2.4 分頁(yè)查詢(xún)優(yōu)化
select * from A ORDER BY name limit 90000,5;
可以讓排序和分頁(yè)操作先查出主鍵，然后根據(jù)主鍵查到對(duì)應(yīng)的記錄，SQL改寫(xiě)如下
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id= ed.id;
7.2.5 對(duì)于join關(guān)聯(lián)sql的優(yōu)化
1.關(guān)聯(lián)字段加索引
2.小表驅(qū)動(dòng)大表，寫(xiě)多表連接sql時(shí)如果明確知道哪張表是小表可以用straight_join寫(xiě)法固定連接驅(qū)動(dòng)方式，省去mysql優(yōu)化器自己判斷的時(shí)間
straight_join解釋?zhuān)簊traight_join功能同join類(lèi)似，但能讓左邊的表來(lái)驅(qū)動(dòng)右邊的表，能改表優(yōu)化器對(duì)于聯(lián)表查詢(xún)的執(zhí)行順序。
比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql選著 t2 表作為驅(qū)動(dòng)表。
straight_join只適用于inner join，并不適用于left join，right join。（因?yàn)閘eft join，right join已經(jīng)代表指定了表的執(zhí)行順序）
盡可能讓優(yōu)化器去判斷，因?yàn)榇蟛糠智闆r下mysql優(yōu)化器是比人要聰明的。使用straight_join一定要慎重，因?yàn)椴糠智闆r下人為指定的執(zhí)行順序并不一定會(huì)比優(yōu)化引擎要靠譜。
3.對(duì)于小表定義的明確
在決定哪個(gè)表做驅(qū)動(dòng)表的時(shí)候，應(yīng)該是兩個(gè)表按照各自的條件過(guò)濾，過(guò)濾完成之后，計(jì)算參與 join 的各個(gè)字段的總數(shù)據(jù)量，數(shù)據(jù)量小的那個(gè)表，就是“小表”，應(yīng)該作為驅(qū)動(dòng)表。
4.in和exsits優(yōu)化
原則：小表驅(qū)動(dòng)大表，即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集
in：當(dāng)B表的數(shù)據(jù)集小于A表的數(shù)據(jù)集時(shí)，in優(yōu)于exists
select * from A where id in (select id from B)
等價(jià)于：
for(select id from B){
select * from A where A.id= B.id
}
exists：當(dāng)A表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時(shí)，exists優(yōu)于in
select * from A where exists (select 1 from B where B.id = A.id)
等價(jià)于:
for(select * from A){
select * from B where B.id = A.id
}
