如何查看mysql數(shù)據(jù)庫的性能

如何提高M(jìn)ySQL Limit查詢的性能?

十多年專注成都網(wǎng)站制作，企業(yè)網(wǎng)站制作，個人網(wǎng)站制作服務(wù)，為大家分享網(wǎng)站制作知識、方案，網(wǎng)站設(shè)計流程、步驟,成功服務(wù)上千家企業(yè)。為您提供網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)頁設(shè)計及定制高端網(wǎng)站建設(shè)服務(wù),專注于企業(yè)網(wǎng)站制作,高端網(wǎng)頁制作,對iso認(rèn)證等多個行業(yè)，擁有多年的網(wǎng)站推廣經(jīng)驗。

在MySQL數(shù)據(jù)庫操作中，我們在做一些查詢的時候總希望能避免數(shù)據(jù)庫引擎做全表掃描，因為全表掃描時間長，而且其中大部分掃描對客戶端而言是沒有意義的。其實(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)驗：如果沒有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 方式？ 因為 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 等價于 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。可以預(yù)計offset越大，后者越優(yōu)。

如何查看高并發(fā)下mysql數(shù)據(jù)庫的性能

限流算法目前程序開發(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 個請求時，因為系統(tǒng)容量有 200 個令牌可以同一時刻處理掉這 200 個請求。如果是漏桶算法，則只能處理 100 個請求，其他的請求等待或者被丟棄。

如何監(jiān)控MySQL性能

一，獲取mysql用戶下的進(jìn)程總數(shù)

ps -ef | awk '{print $1}' | grep "mysql" | grep -v "grep" | wc-1

二，主機(jī)性能狀態(tài)

# uptime

[root@ ~]# uptime

13:05:52 up 53 days, 52 min, 1 user, load average: 0.00, 0.00, 0.00

三，CPU使用率

# top

或

# vmstat

四，磁盤IO量

# vmstat 或 # iostat

五，swap進(jìn)出量[內(nèi)存]

# free

六，數(shù)據(jù)庫性能狀態(tài)

(1)QPS(每秒Query量)

QPS = Questions(or Queries) / seconds

mysql show /*50000 global */ status like 'Question';

(2)TPS(每秒事務(wù)量)

TPS = (Com_commit + Com_rollback) / seconds

mysql show status like 'Com_commit';

mysql show status like 'Com_rollback';

(3)key Buffer 命中率

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%

key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

mysql show status like 'Key%';

(4)InnoDB Buffer命中率

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

mysql show status like 'innodb_buffer_pool_read%';

(5)Query Cache命中率

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

mysql show status like 'Qcache%';

(6)Table Cache狀態(tài)量

mysql show status like 'open%';

(7)Thread Cache 命中率

Thread_cache_hits = (1 - Threads_created / connections ) * 100%

mysql show status like 'Thread%';

mysql show status like 'Connections';

(8)鎖定狀態(tài)

mysql show status like '%lock%';

(9)復(fù)制延時量

mysql show slave status

(10) Tmp Table 狀況(臨時表狀況)

mysql show status like 'Create_tmp%';

(11) Binlog Cache 使用狀況

mysql show status like 'Binlog_cache%';

(12) Innodb_log_waits 量

mysql show status like 'innodb_log_waits';

當(dāng)然你也可以使用一下開源監(jiān)控軟件進(jìn)行監(jiān)控

一，RRDTool

二，Nagios

三，MRTG

四，Cacti

如何查看 mysql 性能瓶頸

通過sysbench的oltp_read_write測試來模擬業(yè)務(wù)壓力、以此來給指定的硬件環(huán)境配置一份比較合理的MySQL配置文件。

環(huán)境介紹

硬件配置

請點(diǎn)擊輸入圖片描述

軟件環(huán)境

請點(diǎn)擊輸入圖片描述

優(yōu)化層級與指導(dǎo)思想

優(yōu)化層級

MySQL數(shù)據(jù)庫優(yōu)化可以在多個不同的層級進(jìn)行，常見的有：

SQL優(yōu)化

參數(shù)優(yōu)化

架構(gòu)優(yōu)化

本文重點(diǎn)關(guān)注：參數(shù)優(yōu)化

指導(dǎo)思想

日志先行 -- 一個事務(wù)能否成功提交的關(guān)鍵是日志是否成功落盤，與數(shù)據(jù)沒有太大的關(guān)系；也就是說對寫的優(yōu)化可以表述為各方面的資源向?qū)懖僮鲀A斜。

瓶頸分析 -- 通過show global status 的各個計數(shù)器的值基本上就能分析出當(dāng)前瓶頸所在，再結(jié)合一些簡單的系統(tǒng)層面的監(jiān)控工具如top iostat 就能明確瓶頸。

整體性能是“讀”“寫”之間的再平衡。

如何查看mysql 腳本的性能

可以使用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ā)揮作用，效率更高。

本文名稱：怎么看mysql的性能 mysql性能分析命令
鏈接地址：http://www.chinadenli.net/article0/dodceoo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供做網(wǎng)站、全網(wǎng)營銷推廣、手機(jī)網(wǎng)站建設(shè)、ChatGPT、虛擬主機(jī)、外貿(mào)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)