> select count(*) from user;或者如下也可以:
> select count(1) from user;但是,假设目前user表中有超过数以千万级别的记录量,我们来模拟一下这个数据量。
CREATE TABLE user100w(然后创建存储过程:
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(10) NOT NULL,
last_name VARCHAR(10) NOT NULL,
sex VARCHAR(5) NOT NULL,
score INT NOT NULL,
copy_id INT NOT NULL,
PRIMARY KEY (`id`)
);
mysql> DELIMITER;我们开始调用存储过程插入1千万条数据,可以看到耗费了1个小时15分钟17.49秒:mysql> call add_user(10000000);Query OK, 1 row affected (1 hour 15 min 17.49 sec)这个数据量的时候,我们使用count直接查询可能持续需要几秒甚至更长时间,这里看到用了两秒多的时间其实已经很长了: 那么,如何缩短这个总记录量的查询时间呢?我们可以通过information_schema来做查询,首先切换到mysql库:mysql> use mysql;然后执行:
mysql> create PROCEDURE add_user(in num INT)
-> BEGIN
-> DECLARE rowid INT DEFAULT 0;
-> DECLARE firstname CHAR(1);
-> DECLARE name1 CHAR(1);
-> DECLARE name2 CHAR(1);
-> DECLARE lastname VARCHAR(3) DEFAULT ''; -> DECLARE sex CHAR(1); -> DECLARE score CHAR(2); -> WHILE rowid < num DO -> SET firstname = SUBSTRING('赵钱孙李周吴郑王林杨柳刘孙陈江阮侯邹高彭徐',FLOOR(1+21*RAND()),1); -> SET name1 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); -> SET name2 = SUBSTRING('一二三四五六七八九十甲乙丙丁静景京晶名明铭敏闵民军君俊骏天田甜兲恬益依成城诚立莉力黎励',ROUND(1+43*RAND()),1); -> SET sex=FLOOR(0 + (RAND() * 2)); -> SET score= FLOOR(40 + (RAND() *60)); -> SET rowid = rowid + 1; -> IF ROUND(RAND())=0 THEN -> SET lastname =name1; -> END IF; -> IF ROUND(RAND())=1 THEN -> SET lastname = CONCAT(name1,name2); -> END IF; -> insert INTO user100w (first_name,last_name,sex,score,copy_id) VALUES (firstname,lastname,sex,score,rowid); -> END WHILE; -> END //
select table_rows from information_schema.tables where table_name = 'user100w' and table_schema = 'test';结果我们可以很清楚的看到耗费不到一秒钟了:
mysql> analyze table test.user100w;
联系客服