mysql 海量随机查询_MySQL”海量数据”查询性能分析

dtdt2dt3itit2it3

ididididididint PK

ext1ext1varchar(256)

timetimetimetimetimetimeint/datetime KEY

ext2ext2ext2ext2varchar(128)

说明, MyISAM引擎, dt表示时间字段使用datetime类型, it表示时间字段使用int类型.

初始数据

首先生成100K个UNIX时间戳(int), 然后随机选取10M次, 每一次往6个表里插入一条记录(当time字段是datetime类型时, 做类型转换). 所以每一个表都有10M条记录. ext1和ext2字段会用随机的字符串填充.

SQL查询

使用的查询SQL语句如: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用于消除查询结果缓存的影响. use key用于指定查询时使用的索引. 统计每一条SQL的执行时间(单位s)和满足WHERE条件的记录总数(total), it-tm表示在dt表上执行SQL的耗时, 并explain得到key和extra, 结果如下.wheretotalselectorderbykeyit-tmdt-tmit2-tmdt2-tmit3-tmdt3-tmextra

time>100008999050count(*)time3.524.283.744.493.534.47where; index

count(time)time3.444.003.694.363.564.26where; index

count(id)NULL1.441.924.304.604.794.98where

*timetime0.000.000.000.000.000.00where

*idtime14.8115.3819.3720.3020.9421.42where; filesort

*idPK0.000.030.000.020.000.04where

time>500004987990count(*)1.902.362.022.411.992.42

count(time)1.902.232.012.321.962.29

count(id)1.481.914.254.614.805.12

*time0.000.000.000.000.000.00

*id8.158.7710.7411.3611.5911.79

*id0.000.000.000.000.000.00

time>800001991982count(*)0.760.950.830.980.801.00

count(time)0.770.910.810.910.830.92

count(id)1.381.864.174.494.715.02

*time0.000.000.000.000.000.00

*id3.263.444.264.514.564.76

*id0.000.000.000.000.000.00

time>9990010871count(*)0.000.000.000.010.010.00

count(time)0.010.010.010.000.010.01

count(id)0.010.010.020.030.020.02

*time0.000.000.000.000.000.00

*id0.020.020.030.030.030.03

*id0.000.000.000.000.000.00

分析和结论

count(*), count(time)和count(id)的对比. 结果有较大变化. 当表的字段只有2个且查询条件较宽松(即符合条件的记录数较多)时, count(id)比count(*)快很多, 但是, 当表中还有其它的字段时, count(id)反而更慢了. 虽然id是主键, time是索引列, 但是select count(id) where time并没有用到索引, 而是进行全表扫描. 当对count(*)进行ignore key(time)时, 查询时间和count(id)相同.证明当结果集较大时索引导致查询变慢,应该是全表扫描进行的是连续的磁盘IO和内存操作, 而使用索引是进行随机的磁盘IO和内存操作, 并且MyISAM存储索引的BTree结构占用更多的空间. 当WHERE条件约束更严格, total的值小到一定程度时, 全表扫描比使用索引慢, 因为索引极大减少了磁盘IO和内存操作.

排序字段和索引的使用. 当有排序且LIMIT(偏移为0)时,如果查询时使用的索引不是排序字段的索引, 那么速度非常慢. 当偏移不为0时, 如果使用排序列的索引, 要考虑偏移可能导致扫描的记录数, 所以应该根据情况选取合适的索引.

判断符合条件的记录是否存在, 使用select * limit 1速度要比select count(*)计数快得多.

时间字段类型的选择. int比datetime快, 但差距不是很明显.

无论如何, 条件限制得越严格, 查询就会越快.

另外, 根据随机id更新时, 大约能达到5K行/s.

列的先后顺序对查询性能的影响也非常大.