56,679
社区成员
发帖
与我相关
我的任务
分享
mysql> SELECT COUNT(uid),MIN(uid),MAX(uid) FROM nick1
-> UNION SELECT COUNT(uid),MIN(uid),MAX(uid) FROM nick2
-> UNION SELECT COUNT(uid),MIN(uid),MAX(uid) FROM nick3
-> UNION SELECT COUNT(uid),MIN(uid),MAX(uid) FROM nick4;
+------------+----------+--------------+
| COUNT(uid) | MIN(uid) | MAX(uid) |
+------------+----------+--------------+
| 38558758 | 31 | 133152982928 |
| 36101731 | 2 | 133153067302 |
| 12610937 | 87 | 133151412359 |
| 886706 | 533 | 1729210852 |
+------------+----------+--------------+
4 rows in set (1 min 14.58 sec)
mysql> DESC nick;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| uid | bigint(20) | NO | PRI | NULL | |
| nick | varchar(30) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> SELECT MIN(uid),MAX(uid),COUNT(uid) FROM nick;
+----------+--------------+------------+
| MIN(uid) | MAX(uid) | COUNT(uid) |
+----------+--------------+------------+
| 2 | 133153067302 | 88090625 |
+----------+--------------+------------+
1 row in set (0.00 sec)
分页用LIMIT,每页1000条,当达页数达到很大的时候,查询基本上死机,必须重启MYSQL :
mysql> EXPLAIN
-> SELECT * FROM nick ORDER BY uid ASC LIMIT 90000000,100;
+----+-------------+---------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+----------+----------------+
| 1 | SIMPLE | nick | ALL | NULL | NULL | NULL | NULL | 88090625 | Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+----------+----------------+
1 row in set (0.00 sec)
我现在用的方法是指定ID范围扫描,但是uid不是连续的,且nick表也在不停的增删纪录。
mysql> EXPLAIN
-> SELECT * FROM nick WHERE uid>2010000 AND uid<2020000 ORDER BY uid ASC;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | nick | range | PRIMARY | PRIMARY | 8 | NULL| 1 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)