MySQL”海量数据”查询性能分析 -- MySQL -- IT技术博客大学习 -- 共学习共进步！

您现在的位置：首页 --> MySQL --> MySQL”海量数据”查询性能分析

MySQL”海量数据”查询性能分析

浏览:2650次出处信息

最近做了一次MySQL所谓的”海量数据”查询性能分析.

表结构

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表示时间字段使用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, 结果如下.

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

分析和结论

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.

建议继续学习：

QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习

<< 前一篇：Using MySQL as a NoSQL

后一篇：你的数据库过度 Sharding 了吗 >>

文章信息

作者：ideawu 来源： idea's blog
标签：查询性能海量数据
发布时间：2011-05-25 13:41:48

建议继续学习

近3天十大热文