mysql 索引的维护开销

 

index的维护开销。

首先先创建测试表testhash。

CREATE TABLE `testhash` (

 `fname` varchar(50) NOT NULL,

 `lname` varchar(50) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 

再创建测试使用的存储过程test_pro()。

DELIMITER $$

 

DROP PROCEDURE IF EXISTS `test_pro`$$

 

CREATE PROCEDURE `test_pro`()

BEGIN

 

DECLARE  num INT ;

DECLARE beginNum INT;

DECLARE endNum INT ;

 

SET num=0;

SET beginNum=1000000;

SET endNum=1000000;

 

WHILE num < 1000000 DO

SET beginNum = beginNum + 1 ;

SET endNum = endNum + 1;

INSERT INTO testhash VALUES(beginNum,endNum);

SET num = num + 1;

END WHILE;

 

END$$

 

DELIMITER ;

然后我们执行下面的操作。

1、首先执行 call test_pro();

该语句向表testhash中写入100万条数据。以供使用了32s的时间。

2、执行该SELECT COUNT(*) FROM testhash ; 确认是否有写入失败的情况，结果为100万，

说明写入正常。

3、执行创建index语句：CREATE INDEX test1 ON testhash (fname,lname) ;

该语句的执行时间是11s。

4、现在我们清空表，DELETE FROM testhash;

5、再执行 call test_pro();语句，注意这个时候表中是有index情况下的插入。

该语句的执行时间是81s

通过以上5个步骤，我们能够知道不带index的写入(32s)+一次性创建index(11s)的时间总和是43s。

该时间远远小于实时对index进行维护的时间81s。

结论：实时的维护index，db系统的开销是一次性创建index的近两倍。

好了，接下来我们在执行一次创建index的语句，该语句与前面创建index的结构一模一样，只是index的名字

我们改为test2。语句为：CREATE INDEX test2 ON testhash (fname,lname) ;

6、清空表，DELETE FROM testhash;

7、我们这个时候执行一下拥有test1和test2 index的存储过程，看看执行的时间有什么变化。

执行 call test_pro() ;语句。结果为125s

通过以上的测试结果我们能够计算出来，125-81=44,81-32=49。

那么如果我们在创建一个同样结构的test3 index呢？下面我们来看看会是什么结果。

8、清空表，DELETE FROM testhash;

9、创建index test3。语句为：CREATE INDEX test3 ON testhash (fname,lname) ;

测试的结果为：169s。

那么169-125 = 44s ，所以表中的多index，对表的维护来说是大概的等量递增的。

我们知道，index会影响insert操作的速度，那我们来看一下上面的测试结果，平均每个insert执行的时间是多少。

a、没有index的时候 1000000/32/1000=31.25 。既每毫秒执行31.25个insert操作。

b、有一个index test1的时候。1000000/81/1000=12.35 。既每毫秒执行12.35个insert操作。

c、有两个index test1、test2的时候。1000000/125/1000=8 。既每毫秒执行8个insert操作。

d、有三个index test1、test2、test3的时候。1000000/169/1000=5.92 。既每毫秒执行5.92个insert操作。

看来结果还是蛮可观的，执行到速度还是蛮不错的。

这只是举个例子，在实际使用的时候还需要根据实际情况具体的去测试，来确定一个表中觉得能容纳多少个index。