MySQL在插入90万条数据消耗的时间以及查询90万条数据时的性能优化

我的MySQL版本是5-1-39。

查看MySQL版本:

mysql> select version();  
+-------------+  
| version()   |  
+-------------+  
| 3.23.56-log |  
+-------------+  
1 row in set (0.00 sec)

 

做这个测试的目的在于以后有海量数据可以采用最优的方案。

现我做以下测试步骤：1)创建一张表(TestMySQL）

2)向已创建的表插入90万条数据,统计所消耗的时间

3)查询100000  条、500000  条、900000  条数据分别耗时多长

##drop table TestMySQL;
CREATE TABLE TestMySQL(
tmId_int INT AUTO_INCREMENT PRIMARY KEY,
tmName_varchar VARCHAR(20),
tmPwd_varchar VARCHAR(20),
tmBirthday_datetime DATETIME,
tmRemark_nvarchar NVARCHAR(1000)
);

SELECT * FROM TestMySQL;

##创建一个插入9十万条数据的存储过程,在调用这个存储过程执行插入操作时,至少要耗你约1个小时，
##请先100000执行一次插入操作，这样可能比一次插入900000条要快些，
##我是一次性插入的9十万条数据，耗我差不多一个小时
##drop PROCEDURE pro_while1
DELIMITER//
CREATE PROCEDURE pro_while1()
BEGIN
  DECLARE cout INT;
  DECLARE satrtTime DATETIME;
  DECLARE endTime DATETIME;
  SET cout=900000;
    WHILE cout>0 DO   ##cout与=之间不能空格,否则mysql会报错
 IF cout=900000 THEN
  SELECT NOW() INTO satrtTime;
 END IF;
 INSERT INTO TestMySQL VALUES(NULL,cout,cout,NOW(),cout);
 IF cout=1 THEN
  SELECT NOW() INTO endTime;
  SELECT DATEDIFF(satrtTime,endTime);
 END IF;
 SET cout=cout-1;
    END WHILE;
END;//

CALL pro_while1();

##创建一个根据传入的显示记录条数的查询存储过程
##drop PROCEDURE selectTestMySQL_PRO
DROP PROCEDURE IF EXISTS selectTestMySQL_PRO;
DELIMITER//
CREATE PROCEDURE selectTestMySQL_PRO()
BEGIN
DECLARE Cout INT;
DECLARE totalTime FLOAT;
DECLARE startTime DATETIME;
DECLARE endTime DATETIME;
SELECT NOW() INTO startTime;
SELECT * FROM TestMysql LIMIT 900000; 
SET endTime=NOW();
SELECT UNIX_TIMESTAMP(endTime) - UNIX_TIMESTAMP(startTime) INTO totalTime;##执行查询所消耗的时间
SELECT totalTime;
END;//

CALL selectTestMySQL_PRO()
##当上面查询100000  查询消耗1s
##当上面查询500000  查询消耗3s
##当上面查询900000  查询消耗5s

 

第一次测试插入900000条数据MySQL直接消耗了我差不多1个小时。

第一种优化方案:采用创建索引方法:

##给表(TestMySQL)创建索引
CREATE INDEX tmId_int ON TestMySQL(tmId_int)

##创建索引后,调用CALL selectTestMySQL_PRO()  查询100000  查询消耗1s

##创建索引后,调用CALL selectTestMySQL_PRO()  查询500000  查询消耗2s
##创建索引后,调用CALL selectTestMySQL_PRO()  查询900000  查询消耗4s

第二种优化方案:采用分表方法: