1. show status

可以通過該命令了解mysql的服務(wù)器運行狀態(tài)參數(shù)以及各種SQL 的執(zhí)行頻率等

Aborted_clients：非正常關(guān)閉導致客戶端終止而中斷的連接數(shù)
Aborted_connects：試圖連接到MySQL服務(wù)器而失敗的連接數(shù)
com*：各種數(shù)據(jù)庫操作的數(shù)量 ，可以查看該項數(shù)據(jù)來了解數(shù)據(jù)庫何種操作的頻繁程度(事務(wù)型的操作可以查看Com_commit和Com_rollback來了解事務(wù)的提交已經(jīng)回滾情況，假如回滾操作頻繁，可能意味著應用程序編寫有問題)
slow_queries: 慢查詢的次數(shù)

2. show variables like "%slow%"

定向的查看某項數(shù)據(jù)庫狀態(tài)參數(shù)

以慢查詢?yōu)槔?，首先我們以上顯示關(guān)于慢查詢的配置信息

log_slow_queries  ON/OFF慢查詢sql記錄
slow_lauch_time   默認為2秒     規(guī)定查詢時長超過多久算是慢查詢
slow_query_log   ON/OFF  開啟慢查詢記錄日志
slow_query_log_file   慢查詢記錄日志文件的存放路徑

開啟慢查詢記錄

mysql> set global slow_query_log="ON";

慢查詢只有在查詢完成之后才會被記錄到慢查詢記錄日志中
所以我們可以先使用show processlist命令顯示mysql的線程列表，來查看線程的狀態(tài)，是否鎖表等狀態(tài)，可以實時的查看sql執(zhí)行情況，同時對一些鎖表操作進行優(yōu)化

3. explain

解釋執(zhí)行計劃

當我們定位到慢查詢之后，我們可以使用explain來分析sql的執(zhí)行計劃

mysql> explain select * from shop_detail_info s left join account a on a.mobile = s.username;
#展示的查詢效果
+----+-------------+-------+------------+------+---------------+--------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | s     | NULL       | ALL  | NULL          | NULL   | NULL    | NULL                  |   22 |   100.00 | NULL        |
|  1 | SIMPLE      | a     | NULL       | ref  | mobile        | mobile | 51      | bibixiaopu.s.username |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+--------+---------+-----------------------+------+----------+-------------+

select_type: 查詢類型
table: 輸出結(jié)果集的表
type：表示表的連接類型
當表中僅有一行并且type的值為system是最佳的連接類型
當select操作中使用索引進行表連接時type為ref
當select的表連接沒有使用索引時，經(jīng)常會看到type=all，表示對該表進行了全表掃描，這是需要考慮通過創(chuàng)建索引來提高連接的效率
possible_keys:表示查詢時，可以使用的索引列
key：表示使用的索引
key_len: 當前使用的索引的長度
rows：掃描的范圍
Extra：執(zhí)行情況的描述與說明

4. 索引

一般的慢查詢都是由于索引使用不當引起的問題
創(chuàng)建索引示例

mysql> create index ind_test on table1(name(5))

• 對于char或者varchar可以使用前綴索引來節(jié)省空間
• order By 和group by中索引不能生效
• mysql估計使用索引比全表查詢慢，則不使用索引
  例如 如果key_part1均勻分布在1和100之間,我們使用如下查詢語句

mysql> select * from tableName where key_part1>1 and key_part1<90

• 查詢條件不是索引列的第一部分時索引不生效
  例如 like 以通配符%開始

 mysql> select * from '%ssss';

• where 條件后邊的的字符串一定要加引號，如果限制條件后面跟的是數(shù)字，mysql需要轉(zhuǎn)化為字符串，此時將不會使用索引

5. show status like 'Handler_read%';

使用該命令查看索引命中率

如果索引正在工作,handler_read_key的值將會很高，變相的代表了索引的命中率，假如數(shù)值比較小的話，說明增加索引得到的性能改善不夠明顯，因為所索引的命中率并不是很高，表示此索引不經(jīng)常被使用

Handler_read_rnd_next的值比較高說明查詢運行效率低，此時應該建立索引來提升查詢效率，此數(shù)值的含義是在數(shù)據(jù)文件中讀下一行的請求數(shù),如果數(shù)據(jù)庫正在做大量的表掃描，該數(shù)值則會比較高，通常表示索引不正確或者寫入的查詢沒有利用索引

6. 簡單的優(yōu)化方法

定期分析表

使用analyze table 用來分析和修復存儲表中的關(guān)鍵字分布
比如我們可以使用show index in table_name 來查看索引的散列程度，如果大大小于數(shù)據(jù)的實際的散列程度，那么這時候索引其實就相當于失效了，這時候我們可以使用 ANALYZE TABLE table_name;命令來修復索引的散列程度，之后在使用show index操作可以看到散列程度大大提高

使用check table來查看表中是否存在錯誤

使用optimize table定期的清理表中的文件碎片，并且可以重新利用未使用的空間,一般當表出現(xiàn)了大量刪除或者對于類似于變長類型字段text/ varchar的頻繁更新與修改的時候我們可以定期的使用此命令

7. 常用的sql優(yōu)化

7.1. 優(yōu)化insert語句

大批量insert操作的時候，我們可以分批量的將一定的數(shù)據(jù)拼裝成一條>insert語句，來提高執(zhí)行效率
例如：

mysql>  insert into test values （1,2），（2,3），（3,4）

7.2. 將索引文件和數(shù)據(jù)文件分別存放在不同的磁盤上

7.3.優(yōu)化group by語句

如果查詢包括group by的字段，但又想要避免排序結(jié)果的消耗，我們可以>在sql語句最后添加order by null來指定禁止排序
例如：

mysql> select a count（*） from test group by a order by null

7.4.優(yōu)化order by語句

order by之后的字段最好保持升序或者降序一致，這樣才能使用索引

7.5. 優(yōu)化join 語句

將某些子查詢轉(zhuǎn)換成join的表關(guān)聯(lián)查詢
例如：我們需要將所有沒有訂單記錄的用戶取出來，我們可以使用not in

mysql>  SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

此時如果使用join連接會大幅度的提升查詢效率，尤其是當salesinfo表中創(chuàng)建有customerid列的索引

SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID IS NULL 

join之所以能夠效率更快，是因為這時候mysql不需要在內(nèi)存中創(chuàng)建臨時表

8. 拆分表

8.1. 縱向分表

縱向拆分是按照應用訪問的頻度，將經(jīng)常訪問的字段和不經(jīng)常訪問的字段拆分成不同的表，經(jīng)常訪問的字段盡量是定長的，這樣可以有效的提高表的查詢和更新效率

8.2：橫向分表

按照應用的情況，將數(shù)據(jù)橫向拆分成幾個表恨著通過分區(qū)分到多個分區(qū)中
例如：

1. 訂單是一個實效性很強的實體，我們很少查詢幾年前的訂單數(shù)據(jù)，我們就可以在訂單的創(chuàng)建時間列上創(chuàng)建分區(qū)函數(shù)來做分區(qū)。
2. 比如帖子通常情況下只有在首頁推薦的最新的帖子被訪問次數(shù)很多，而幾年前的帖子被訪問的幾率較小，這時候我們可以根據(jù)帖子的主鍵id來做分區(qū), id小于300w的在一個分區(qū)上，id在300到600w之間的在一個分區(qū)上。

9. 鎖問題

我們可以通過檢查table_lock_waited和table_lock_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖定爭奪,也可以通過檢查Innodb_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖爭奪情況
命令如下：

 mysql> show status like '%Table%'
 mysql> show status like 'innodb_row_lock%'

什么情況下使用表鎖：

• 很多操作都是讀表
• 在嚴格條件的索引上讀取和更新，當更新或者刪除可以用單獨的索引來獲取時
• UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
• DELETE FROM tbl_name WHERE unique_key_col=key_value;
• select和insert語句并發(fā)執(zhí)行，但是只有很少的update和delete語句
• 很多的掃描表和對全表的group by操作，但是沒有任何的寫表操作

行級鎖的優(yōu)點：

• 當在許多線程中訪問不同的行時只存在少量的鎖定沖突
• 回滾時只有少量的更改
• 可以長時間的鎖定單一行

行級鎖的缺點：

• 比頁級鎖和表級鎖占用更多的內(nèi)存
• 當在表的大部分中使用時，比頁級鎖或表級鎖多頂速度慢，因為必須獲取更多的鎖
• 當大部分數(shù)據(jù)上經(jīng)常進行g(shù)roup by 操作或者必須經(jīng)常掃描整個表，比其他鎖定明顯慢很多

insert ......select ........ 帶來的問題

當使用insert ....... select..... 進行記錄的插入時，如果select的表是innodb類型的，不論insert的表是什么類型的，都會對select表的記錄進行鎖定，而在oracle數(shù)據(jù)庫中不存在這種情況，因此從oracle中遷移過來的數(shù)據(jù)可能會存在一些類似于對比較多的記錄進行統(tǒng)計分析，然后將統(tǒng)計結(jié)果插入到另外一個表中，這樣的操作因為非常少，所以可能并沒有設(shè)置相應的索引。如果遷移到mysql數(shù)據(jù)庫中之后沒有做相應的調(diào)整，對需要select的表實際是進行的全表掃描導致的所有記錄的鎖定，這樣將會對對應的其他操作造成很惡劣的影響，所以建議統(tǒng)計數(shù)據(jù)最好不要寫入表中

10. 優(yōu)化mysql server

key_buffer_size 的設(shè)置

可以將指定的表索引緩存進入指定的key_buffer,這樣可以更小的降低線程之間的競爭

 CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache

11. 應用的優(yōu)化

• 使用連接池
• 避免對同一數(shù)據(jù)的重復檢索
• 使用mysql的查詢緩存

查詢緩存存儲select查詢的文本以及發(fā)送給客戶端的相應結(jié)果。如果隨后收到一個相同的查詢，服務(wù)器從查詢緩存中重新得到查詢結(jié)果，而不需要再重新解析和執(zhí)行查詢
適用范圍：不發(fā)生數(shù)據(jù)更新的表。當表更改（包括表結(jié)構(gòu)和表數(shù)據(jù)）后，查詢緩存值的相關(guān)條目會被清空

• 加cache層

12. 負載均衡

• 利用MySQL的主從復制可以有效的分流更新操作和查詢操作
• 采用分布式數(shù)據(jù)庫架構(gòu)