MySQL”海量數(shù)據(jù)”查詢性能分析_MySQL

來源：懂視網(wǎng) 責(zé)編：小采時(shí)間：2020-11-09 17:58:48

MySQL”海量數(shù)據(jù)”查詢性能分析_MySQL

MySQL海量數(shù)據(jù)查詢性能分析_MySQL:bitsCN.com 最近做了一次MySQL所謂的”海量數(shù)據(jù)”查詢性能分析.表結(jié)構(gòu) dt dt2 dt3 it it2 it3 id id id id id id int PK ext1 ext1 varchar(256) time time time time time time int/datetime KE

推薦度：

點(diǎn)擊下載本文 文檔為doc格式

導(dǎo)讀MySQL海量數(shù)據(jù)查詢性能分析_MySQL:bitsCN.com 最近做了一次MySQL所謂的”海量數(shù)據(jù)”查詢性能分析.表結(jié)構(gòu) dt dt2 dt3 it it2 it3 id id id id id id int PK ext1 ext1 varchar(256) time time time time time time int/datetime KE

bitsCN.com

最近做了一次MySQL所謂的”海量數(shù)據(jù)”查詢性能分析.

表結(jié)構(gòu)

dt dt2 dt3 it it2 it3 id id id id id id int PK ext1 ext1 varchar(256) time time time time time time int/datetime KEY ext2 ext2 ext2 ext2 varchar(128)

說明, MyISAM引擎, dt表示時(shí)間字段使用datetime類型, it表示時(shí)間字段使用int類型.

初始數(shù)據(jù)

首先生成100K個(gè)UNIX時(shí)間戳(int), 然后隨機(jī)選取10M次, 每一次往6個(gè)表里插入一條記錄(當(dāng)time字段是datetime類型時(shí), 做類型轉(zhuǎn)換). 所以每一個(gè)表都有10M條記錄. ext1和ext2字段會(huì)用隨機(jī)的字符串填充.

SQL查詢

使用的查詢SQL語(yǔ)句如:

select SQL_NO_CACHE count(*) from it where time>10000;select SQL_NO_CACHE count(*) from dt where time>from_unixtime(10000);select SQL_NO_CACHE * from it where time>10000 order by time limit 1;select SQL_NO_CACHE * from it use key(PRIMARY) where time>10000 order by id limit 1;

SQL_NO_CACHE用于消除查詢結(jié)果緩存的影響. use key用于指定查詢時(shí)使用的索引. 統(tǒng)計(jì)每一條SQL的執(zhí)行時(shí)間(單位s)和滿足WHERE條件的記錄總數(shù)(total), it-tm表示在dt表上執(zhí)行SQL的耗時(shí), 并explain得到key和extra, 結(jié)果如下.

where total select orderby key it-tm dt-tm it2-tm dt2-tm it3-tm dt3-tm extra time>10000 8999050 count(*) time 3.52 4.28 3.74 4.49 3.53 4.47 where; index count(time) time 3.44 4.00 3.69 4.36 3.56 4.26 where; index count(id) NULL 1.44 1.92 4.30 4.60 4.79 4.98 where * time time 0.00 0.00 0.00 0.00 0.00 0.00 where * id time 14.81 15.38 19.37 20.30 20.94 21.42 where; filesort * id PK 0.00 0.03 0.00 0.02 0.00 0.04 where time>50000 4987990 count(*) 1.90 2.36 2.02 2.41 1.99 2.42 count(time) 1.90 2.23 2.01 2.32 1.96 2.29 count(id) 1.48 1.91 4.25 4.61 4.80 5.12 * time 0.00 0.00 0.00 0.00 0.00 0.00 * id 8.15 8.77 10.74 11.36 11.59 11.79 * id 0.00 0.00 0.00 0.00 0.00 0.00 time>80000 1991982 count(*) 0.76 0.95 0.83 0.98 0.80 1.00 count(time) 0.77 0.91 0.81 0.91 0.83 0.92 count(id) 1.38 1.86 4.17 4.49 4.71 5.02 * time 0.00 0.00 0.00 0.00 0.00 0.00 * id 3.26 3.44 4.26 4.51 4.56 4.76 * id 0.00 0.00 0.00 0.00 0.00 0.00 time>99900 10871 count(*) 0.00 0.00 0.00 0.01 0.01 0.00 count(time) 0.01 0.01 0.01 0.00 0.01 0.01 count(id) 0.01 0.01 0.02 0.03 0.02 0.02 * time 0.00 0.00 0.00 0.00 0.00 0.00 * id 0.02 0.02 0.03 0.03 0.03 0.03 * id 0.00 0.00 0.00 0.00 0.00 0.00

分析和結(jié)論

count(*), count(time)和count(id)的對(duì)比. 結(jié)果有較大變化. 當(dāng)表的字段只有2個(gè)且查詢條件較寬松(即符合條件的記錄數(shù)較多)時(shí), count(id)比count(*)快很多, 但是, 當(dāng)表中還有其它的字段時(shí), count(id)反而更慢了. 雖然id是主鍵, time是索引列, 但是select count(id) where time并沒有用到索引, 而是進(jìn)行全表掃描. 當(dāng)對(duì)count(*)進(jìn)行ignore key(time)時(shí), 查詢時(shí)間和count(id)相同.證明當(dāng)結(jié)果集較大時(shí)索引導(dǎo)致查詢變慢,應(yīng)該是全表掃描進(jìn)行的是連續(xù)的磁盤IO和內(nèi)存操作, 而使用索引是進(jìn)行隨機(jī)的磁盤IO和內(nèi)存操作, 并且MyISAM存儲(chǔ)索引的BTree結(jié)構(gòu)占用更多的空間. 當(dāng)WHERE條件約束更嚴(yán)格, total的值小到一定程度時(shí), 全表掃描比使用索引慢, 因?yàn)樗饕龢O大減少了磁盤IO和內(nèi)存操作.

排序字段和索引的使用. 當(dāng)有排序且LIMIT(偏移為0)時(shí),如果查詢時(shí)使用的索引不是排序字段的索引, 那么速度非常慢. 當(dāng)偏移不為0時(shí), 如果使用排序列的索引, 要考慮偏移可能導(dǎo)致掃描的記錄數(shù), 所以應(yīng)該根據(jù)情況選取合適的索引.

判斷符合條件的記錄是否存在, 使用select * limit 1速度要比select count(*)計(jì)數(shù)快得多.

時(shí)間字段類型的選擇. int比datetime快, 但差距不是很明顯.

無論如何, 條件限制得越嚴(yán)格, 查詢就會(huì)越快.

另外, 根據(jù)隨機(jī)id更新時(shí), 大約能達(dá)到5K行/s.

列的先后順序?qū)Σ樵冃阅艿挠绊懸卜浅４?

bitsCN.com

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com