MySQL中使用show status語句查詢MySQL的性能參數(shù)
創(chuàng)新互聯(lián)建站堅(jiān)持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站制作、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的丹陽網(wǎng)站設(shè)計(jì)、移動媒體設(shè)計(jì)的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！
語法：
show status like [statement];
常用statement說明：
# 查詢連接MySQL服務(wù)器的次數(shù)
# 查詢MySQL服務(wù)器上線的時間
# 查詢慢查詢的次數(shù)
# 查詢 查詢操作的次數(shù)
查看MySQL服務(wù)器配置信息 ：
查看MySQL服務(wù)器運(yùn)行的各種狀態(tài)值 ：
慢查詢：
連接數(shù)：
key_buffer_size 是對MyISAM表性能影響最大的一個參數(shù)
臨時表：
查看open table ：
進(jìn)程使用情況：
查詢緩存(query cache) ：
文件打開數(shù)(open_files) ：
表鎖情況 ：
表掃描情況 ：
查詢吞吐量：
連接情況：
分析查詢語句
mysql提供了explain語句和describe語句，進(jìn)行分析查詢語句
EXPLAIN的語法：
說明：
示例：
DESCRIBE語句
使用方法與EXPLAIN語句是一樣的，并且分析結(jié)果也是一樣的。
語法：
mysql中提高性能的最有效的方式：
1, 對數(shù)據(jù)表設(shè)計(jì)合理的索引。索引提供了高效訪問數(shù)據(jù)的方法，并且加快了查詢的速度。
使用索引查詢幾種特殊情況：
可以使用EXPLAIN語句查看SQL腳本性能：
EXPLAIN SELECT 語句；
例如學(xué)生表，其中電話字段建立了索引
學(xué)生表有6條記錄
查詢SQL語句的運(yùn)行效率
explain select * from students where phone like '%34%'\G
下圖rows為6，顯示該查詢掃描了6行記錄，沒有利用到索引
explain select * from students where phone like '45%'\G
下圖rows為1，顯示該查詢掃描了1行記錄，利用到了索引
兩圖比較like匹配查詢
第一種寫法 '%34%'?掃描了6行，索引未被利用；第二種寫法?'45%' 掃描1行，索引發(fā)揮作用，效率更高。
限流算法目前程序開發(fā)過程常用的限流算法有兩個：漏桶算法和令牌桶算法。
漏桶算法
漏桶算法的原理比較簡單，請求進(jìn)入到漏桶中，漏桶以一定的速率漏水。當(dāng)請求過多時，水直接溢出?？梢钥闯?，漏桶算法可以強(qiáng)制限制數(shù)據(jù)的傳輸速度。如圖所示，把請求比作是水滴，水先滴到桶里，通過漏洞并以限定的速度出水，當(dāng)水來得過猛而出水不夠快時就會導(dǎo)致水直接溢出，即拒絕服務(wù)。
圖片來自網(wǎng)絡(luò)
漏桶的出水速度是恒定的，那么意味著如果瞬時大流量的話，將有大部分請求被丟棄掉（也就是所謂的溢出）。
令牌桶算法
令牌桶算法的原理是系統(tǒng)以一定速率向桶中放入令牌，如果有請求時，請求會從桶中取出令牌，如果能取到令牌，則可以繼續(xù)完成請求，否則等待或者拒絕服務(wù)。這種算法可以應(yīng)對突發(fā)程度的請求，因此比漏桶算法好。
圖片來自網(wǎng)絡(luò)
漏桶算法和令牌桶算法的選擇
兩者的主要區(qū)別漏桶算法能夠強(qiáng)行限制處理數(shù)據(jù)的速率，不論系統(tǒng)是否空閑。而令牌桶算法能夠在限制數(shù)據(jù)的平均處理速率的同時還允許某種程度的突發(fā)流量。如何理解上面的含義呢？漏桶算法，比如系統(tǒng)吞吐量是 120/s，業(yè)務(wù)請求 130/s，使用漏斗限流 100/s，起到限流的作用，多余的請求將產(chǎn)生等待或者丟棄。對于令牌桶算法，每秒產(chǎn)生 100 個令牌，系統(tǒng)容量 200 個令牌。正常情況下，業(yè)務(wù)請求 100/s 時，請求能被正常被處理。當(dāng)有突發(fā)流量過來比如 200 個請求時，因?yàn)橄到y(tǒng)容量有 200 個令牌可以同一時刻處理掉這 200 個請求。如果是漏桶算法，則只能處理 100 個請求，其他的請求等待或者被丟棄。
如何提高M(jìn)ySQL Limit查詢的性能?
在MySQL數(shù)據(jù)庫操作中，我們在做一些查詢的時候總希望能避免數(shù)據(jù)庫引擎做全表掃描，因?yàn)槿頀呙钑r間長，而且其中大部分掃描對客戶端而言是沒有意義的。其實(shí)我們可以使用Limit關(guān)鍵字來避免全表掃描的情況，從而提高效率。
有個幾千萬條記錄的表 on MySQL 5.0.x，現(xiàn)在要讀出其中幾十萬萬條左右的記錄。常用方法，依次循環(huán)：
select * from mytable where index_col = xxx limit offset, limit;
經(jīng)驗(yàn)：如果沒有blob/text字段，單行記錄比較小，可以把 limit 設(shè)大點(diǎn)，會加快速度。
問題：頭幾萬條讀取很快，但是速度呈線性下降，同時 mysql server cpu 99% ，速度不可接受。
調(diào)用 explain select * from mytable where index_col = xxx limit offset, limit;
顯示 type = ALL
在 MySQL optimization 的文檔寫到"All"的解釋
A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.
看樣子對于 all, mysql 就使用比較笨的方法，那就改用 range 方式？ 因?yàn)?id 是遞增的，也很好修改 sql 。
select * from mytable where id offset and id offset + limit and index_col = xxx
explain 顯示 type = range，結(jié)果速度非常理想，返回結(jié)果快了幾十倍。
Limit語法：
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
LIMIT子句可以被用于強(qiáng)制 SELECT 語句返回指定的記錄數(shù)。LIMIT接受一個或兩個數(shù)字參數(shù)。參數(shù)必須是一個整數(shù)常量。
如果給定兩個參數(shù)，第一個參數(shù)指定第一個返回記錄行的偏移量，第二個參數(shù)指定返回記錄行的最大數(shù)目。初始記錄行的偏移量是 0(而不是 1)。
為了與 PostgreSQL 兼容，MySQL 也支持句法：LIMIT # OFFSET #。
mysql SELECT * FROM table LIMIT 5,10; //檢索記錄行6-15
//為了檢索從某一個偏移量到記錄集的結(jié)束所有的記錄行，可以指定第二個參數(shù)為-1
mysql SELECT * FROM table LIMIT 95,-1; //檢索記錄行96-last
//如果只給定一個參數(shù)，它表示返回最大的記錄行數(shù)目，換句話說，LIMIT n 等價(jià)于 LIMIT 0,n
mysql SELECT * FROM table LIMIT 5; //檢索前5個記錄行
MySQL的limit給分頁帶來了極大的方便，但數(shù)據(jù)量一大的時候，limit的性能就急劇下降。同樣是取10條數(shù)據(jù)，下面兩句就不是一個數(shù)量級別的。
select * from table limit 10000,10
select * from table limit 0,10
文中不是直接使用limit，而是首先獲取到offset的id然后直接使用limit size來獲取數(shù)據(jù)。根據(jù)他的數(shù)據(jù)，明顯要好于直接使用limit。
這里我具體使用數(shù)據(jù)分兩種情況進(jìn)行測試。
1、offset比較小的時候：
select * from table limit 10,10
//多次運(yùn)行，時間保持在0.0004-0.0005之間
Select * From table Where vid ＞=(Select vid From table Order By vid limit 10,1) limit 10
//多次運(yùn)行，時間保持在0.0005-0.0006之間，主要是0.0006
結(jié)論：偏移offset較小的時候，直接使用limit較優(yōu)。這個顯然是子查詢的原因。
2、offset大的時候：
select * from table limit 10000,10
//多次運(yùn)行，時間保持在0.0187左右
Select * From table Where vid ＞=(Select vid From table Order By vid limit 10000,1) limit 10
//多次運(yùn)行，時間保持在0.0061左右，只有前者的1/3?？梢灶A(yù)計(jì)offset越大，后者越優(yōu)。
給你看個范例：
測試環(huán)境：
DELL?2.4G?memory?512M
RH9.0?MySQL?3.23.54
測試使用的是mysql缺省參數(shù)，用mysql提供的API用C編寫測試程序
測試程序共啟動40個線程進(jìn)行數(shù)據(jù)庫操作，查找、插入、修改、刪除各10個，每個線程獨(dú)立與Sql?Server連接。
數(shù)據(jù)庫結(jié)構(gòu)，單表，表結(jié)構(gòu)如下：
toheader?????????100byte?主鍵
contactheader?100byte
called???????50byte
cseq????????100byte
hashval??????int
timestamp???????int??次鍵
對主次鍵分別建了索引。分別在5萬、10萬、50萬用戶環(huán)境做測試，結(jié)果如下：
查找100次
插入100次
修改100次
刪除100次
5萬
100－300ms
100－300ms
100－300ms
100－300ms
10萬
500ms-1s
500ms-1s
500ms-1s
500ms-1s
50萬
3s-5s
3s-5s
3s-5s
3s-5s
從此數(shù)據(jù)看性能是很不錯的，因?yàn)閙ysql能保證每個操作是原子的，所以不用考慮線程間的同步。
但有一個問題：即mysql的每個操作是原子的，所以做每個操作時，其它線程是阻塞的，在大數(shù)據(jù)量查詢時，花的時間較長，會對其它線程有影響。我在保持其它線程工作不變的情況下，將每個查找線程改為做一次對所有記錄的查詢，在5萬和10萬記錄時表現(xiàn)不錯，分別為250ms和450ms，但在50萬記錄時，這個數(shù)值達(dá)到了22秒。而且在50萬用戶時，通過條件查找部分?jǐn)?shù)據(jù)也很慢，如查詢結(jié)果為10萬記錄時用11秒。
懷疑是sql?server的參數(shù)影響，按數(shù)據(jù)庫說明修改了幾個緩沖區(qū)的參數(shù)，但沒有效果。
由于首次同步發(fā)生的頻率很低，象250ms和450ms這樣的數(shù)據(jù)還是可以接受的，但22秒太離譜了。支持50萬用戶在線，需要考慮一個解決辦法，現(xiàn)在有一個辦法是建一個備份表，寫備份表的請求放到一個隊(duì)列里由一個單獨(dú)線程處理，這樣阻塞不會影響正常業(yè)務(wù)處理了。但這個線程的緩沖區(qū)要足夠容納30秒內(nèi)發(fā)生的操作。
本來擔(dān)心數(shù)據(jù)量大了mysql的緩沖區(qū)不夠會出錯或丟數(shù)據(jù)，但測試發(fā)現(xiàn)，一次查詢最多讀了50M數(shù)據(jù)，沒有出現(xiàn)問題，經(jīng)測算我們首次同步的數(shù)據(jù)不會超過10M。
在MySQL中啟動了innoDB引擎后，可以實(shí)現(xiàn)真正的行級鎖，select和update操作可以并發(fā)，這樣在全表查詢進(jìn)行中間可以進(jìn)行其它的select和update操作，但insert和delete不行。
性能也有很大提高，50萬記錄時100次操作300ms，全表查詢11秒多
常用參數(shù)?[options]?詳細(xì)說明：--auto-generate-sql,?
-a?自動生成測試表和數(shù)據(jù)，表示用mysqlslap工具自己生成的SQL腳本來測試并發(fā)壓力。
--auto-generate-sql-load-type=type?測試語句的類型。代表要測試的環(huán)境是讀操作還是寫操作還是兩者混合的。取值包括：read，key，write，update和mixed(默認(rèn))。
--auto-generate-sql-add-auto-increment?代表對生成的表自動添加auto_increment列，從5.1.18版本開始支持。--number-char-cols=N,?
-x?N?自動生成的測試表中包含多少個字符類型的列，默認(rèn)1--number-int-cols=N,?-y?N?自動生成的測試表中包含多少個數(shù)字類型的列，默認(rèn)1--number-of-queries=N?總的測試查詢次數(shù)(并發(fā)客戶數(shù)×每客戶查詢次數(shù))
--query=name,-q?使用自定義腳本執(zhí)行測試，例如可以調(diào)用自定義的一個存儲過程或者sql語句來執(zhí)行測試。--create-schema?代表自定義的測試庫名稱，測試的schema，MySQL中schema也就是database。--commint=N?多少條DML后提交一次。
--compress,?-C?如果服務(wù)器和客戶端支持都壓縮，則壓縮信息傳遞。--concurrency=N,?-c?N?表示并發(fā)量，也就是模擬多少個客戶端同時執(zhí)行select?？芍付ǘ鄠€值，以逗號或者--delimiter參數(shù)指定的值做為分隔符。例如：
--concurrency=100,200,500。
--engine=engine_name,?-e?engine_name?代表要測試的引擎，可以有多個，用分隔符隔開。例如：--engines=myisam,innodb。--iterations=N,?-i?N?測試執(zhí)行的迭代次數(shù)，代表要在不同并發(fā)環(huán)境下，各自運(yùn)行測試多少次。
--only-print?只打印測試語句而不實(shí)際執(zhí)行。--detach=N?執(zhí)行N條語句后斷開重連。--debug-info,?-T?打印內(nèi)存和CPU的相關(guān)信息。說明：測試的過程需要生成測試表，插入測試數(shù)據(jù)，這個mysqlslap可以自動生成，默認(rèn)生成一個mysqlslap的schema，如果已經(jīng)存在則先刪除。可以用--only-print來打印實(shí)際的測試過程，整個測試完成后不會在數(shù)據(jù)庫中留下痕跡。各種測試參數(shù)實(shí)例（-p后面跟的是mysql的root密碼）：單線程測試。測試做了什么。
#?mysqlslap?-a?-uroot?-p123456多線程測試。使用–concurrency來模擬并發(fā)連接。#?mysqlslap?-a?-c?100?-uroot?-p123456迭代測試。用于需要多次執(zhí)行測試得到平均值。#?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測試同時不同的存儲引擎的性能進(jìn)行對比：#?mysqlslap?-a?--concurrency=50,100?--number-of-queries?1000?--iterations=5?--engine=myisam,innodb?--debug-info?-uroot?-p123456執(zhí)行一次測試，分別50和100個并發(fā)，執(zhí)行1000次總查詢：#?mysqlslap?-a?--concurrency=50,100?--number-of-queries?1000?--debug-info?-uroot?-p12345650和100個并發(fā)分別得到一次測試結(jié)果(Benchmark)，并發(fā)數(shù)越多，執(zhí)行完所有查詢的時間越長。為了準(zhǔn)確起見，可以多迭代測試幾次:#?mysqlslap?-a?--concurrency=50,100?--number-of-queries?1000?--iterations=5?--debug-info?-uroot?-p123456
使用語法如下：
# mysqlslap [options]
常用參數(shù) [options] 詳細(xì)說明：
--auto-generate-sql, -a 自動生成測試表和數(shù)據(jù)，表示用mysqlslap工具自己生成的SQL腳本來測試并發(fā)壓力。
--auto-generate-sql-load-type=type 測試語句的類型。代表要測試的環(huán)境是讀操作還是寫操作還是兩者混合的。取值包括：read，key，write，update和mixed(默認(rèn))。
--auto-generate-sql-add-auto-increment 代表對生成的表自動添加auto_increment列，從5.1.18版本開始支持。
--number-char-cols=N, -x N 自動生成的測試表中包含多少個字符類型的列，默認(rèn)1
--number-int-cols=N, -y N 自動生成的測試表中包含多少個數(shù)字類型的列，默認(rèn)1
--number-of-queries=N 總的測試查詢次數(shù)(并發(fā)客戶數(shù)×每客戶查詢次數(shù))
--query=name,-q 使用自定義腳本執(zhí)行測試，例如可以調(diào)用自定義的一個存儲過程或者sql語句來執(zhí)行測試。
--create-schema 代表自定義的測試庫名稱，測試的schema，MySQL中schema也就是database。
--commint=N 多少條DML后提交一次。
--compress, -C 如果服務(wù)器和客戶端支持都壓縮，則壓縮信息傳遞。
--concurrency=N, -c N 表示并發(fā)量，也就是模擬多少個客戶端同時執(zhí)行select。可指定多個值，以逗號或者--delimiter參數(shù)指定的值做為分隔符。例如：--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要測試的引擎，可以有多個，用分隔符隔開。例如：--engines=myisam,innodb。
--iterations=N, -i N 測試執(zhí)行的迭代次數(shù)，代表要在不同并發(fā)環(huán)境下，各自運(yùn)行測試多少次。
--only-print 只打印測試語句而不實(shí)際執(zhí)行。
--detach=N 執(zhí)行N條語句后斷開重連。
--debug-info, -T 打印內(nèi)存和CPU的相關(guān)信息。
說明：
測試的過程需要生成測試表，插入測試數(shù)據(jù)，這個mysqlslap可以自動生成，默認(rèn)生成一個mysqlslap的schema，如果已經(jīng)存在則先刪除?？梢杂?-only-print來打印實(shí)際的測試過程，整個測試完成后不會在數(shù)據(jù)庫中留下痕跡。
各種測試參數(shù)實(shí)例（-p后面跟的是mysql的root密碼）：
單線程測試。測試做了什么。
# mysqlslap -a -uroot -p123456
多線程測試。使用–concurrency來模擬并發(fā)連接。
# mysqlslap -a -c 100 -uroot -p123456
迭代測試。用于需要多次執(zhí)行測試得到平均值。
# 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
測試同時不同的存儲引擎的性能進(jìn)行對比：
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456
執(zhí)行一次測試，分別50和100個并發(fā)，執(zhí)行1000次總查詢：
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456
50和100個并發(fā)分別得到一次測試結(jié)果(Benchmark)，并發(fā)數(shù)越多，執(zhí)行完所有查詢的時間越長。為了準(zhǔn)確起見，可以多迭代測試幾次:
# mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456
重慶分公司
重慶分公司
