一、User表信息
二、结论
采用查询语句：select count(1) from user;通过explain查看，该语句的确是走了索引，以下的结果是该语句的查询时间。
数据量 查询时间（单位秒） 386781 0.17 609341 0.27 612181 0.25 638021 0.27 670821 0.28 788461 0.34 795261 0.35 800501 0.34 821741 0.35 902421 0.66 911181 1.70 917661 1.71 920421 1.58 927501 1.61 953181 1.73 991821 1.96 995701 1.77 997341 2.04 1188461 1.18 1588461 1.64 1588461 1.68 1874461 3.04 1897861 3.57 1903161 3.23 2001561 3.11 2124361 3.49 2588461 2.78 3088461 2.99
三、Mysql查询时间记录
mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 386781 | +----------+ 1 row in set (0.17 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 609341 | +----------+ 1 row in set (0.27 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 612181 | +----------+ 1 row in set (0.25 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 613221 | +----------+ 1 row in set (0.26 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 615021 | +----------+ 1 row in set (0.26 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 616141 | +----------+ 1 row in set (0.26 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 633461 | +----------+ 1 row in set (0.27 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 634981 | +----------+ 1 row in set (0.27 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 638021 | +----------+ 1 row in set (0.27 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 670821 | +----------+ 1 row in set (0.28 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 788461 | +----------+ 1 row in set (0.34 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 795261 | +----------+ 1 row in set (0.35 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 797461 | +----------+ 1 row in set (0.33 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 799301 | +----------+ 1 row in set (0.34 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 800501 | +----------+ 1 row in set (0.34 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 801581 | +----------+ 1 row in set (0.33 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 802421 | +----------+ 1 row in set (0.34 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 803221 | +----------+ 1 row in set (0.34 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 821741 | +----------+ 1 row in set (0.35 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 902421 | +----------+ 1 row in set (0.66 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 908661 | +----------+ 1 row in set (1.62 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 911181 | +----------+ 1 row in set (1.70 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 916341 | +----------+ 1 row in set (1.86 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 917661 | +----------+ 1 row in set (1.71 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 920421 | +----------+ 1 row in set (1.58 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 922061 | +----------+ 1 row in set (1.66 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 923741 | +----------+ 1 row in set (2.05 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 925861 | +----------+ 1 row in set (1.64 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 927501 | +----------+ 1 row in set (1.61 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 953181 | +----------+ 1 row in set (1.73 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 991821 | +----------+ 1 row in set (1.96 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 994261 | +----------+ 1 row in set (1.69 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 995701 | +----------+ 1 row in set (1.77 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 997341 | +----------+ 1 row in set (2.04 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 999581 | +----------+ 1 row in set (1.81 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 1001661 | +----------+ 1 row in set (1.80 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 1004261 | +----------+ 1 row in set (1.81 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 1006701 | +----------+ 1 row in set (1.85 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 1188461 | +----------+ 1 row in set (1.18 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 1188461 | +----------+ 1 row in set (1.31 sec) mysql> explain select count(1) from user; +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ | 1 | SIMPLE | user | index | NULL | PRIMARY | 8 | NULL | 1188729 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ 1 row in set (0.03 sec) mysql> explain select count(1) from user; +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ | 1 | SIMPLE | user | index | NULL | PRIMARY | 8 | NULL | 1188729 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ 1 row in set (0.00 sec) mysql> explain select count(1) from user; +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ | 1 | SIMPLE | user | index | NULL | PRIMARY | 8 | NULL | 1188729 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ 1 row in set (0.00 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 1188461 | +----------+ 1 row in set (1.20 sec) mysql> explain select * from user limit 100000,10; +----+-------------+-------+------+---------------+------+---------+------+----- ----+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+----- ----+-------+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 1188 729 | | +----+-------------+-------+------+---------------+------+---------+------+----- ----+-------+ 1 row in set (0.00 sec) mysql> explain select count(*) from user; +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ | 1 | SIMPLE | user | index | NULL | PRIMARY | 8 | NULL | 1188729 | Using index | +----+-------------+-------+-------+---------------+---------+---------+------+- --------+-------------+ 1 row in set (0.00 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 1188461 | +----------+ 1 row in set (1.23 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 1588461 | +----------+ 1 row in set (1.64 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 1588461 | +----------+ 1 row in set (1.68 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 1588461 | +----------+ 1 row in set (1.56 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 1874461 | +----------+ 1 row in set (3.04 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 1897861 | +----------+ 1 row in set (3.57 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 1903161 | +----------+ 1 row in set (3.23 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 2001561 | +----------+ 1 row in set (3.11 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 2124361 | +----------+ 1 row in set (3.49 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 2324761 | +----------+ 1 row in set (4.03 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 2588461 | +----------+ 1 row in set (2.78 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 2588461 | +----------+ 1 row in set (3.09 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 2588461 | +----------+ 1 row in set (2.95 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 2588461 | +----------+ 1 row in set (2.44 sec) mysql> select count(1) from user; +----------+ | count(1) | +----------+ | 2588461 | +----------+ 1 row in set (2.41 sec)
