----------+--------+------+------------+---------+---------------+
| sbtest | 0 | PRIMARY | 1 | id | A | 1000117 | NULL | NULL | | BTREE | | |
| sbtest | 1 | k | 1 | k | A | 6 | NULL | NULL | | BTREE | | |
| sbtest | 1 | pad | 1 | pad | A | 24 | NULL | NULL | YES | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
这里开始有区别了.Cardinality 从200降到24
varchar(120) Cardinality =200
varchar(60) Cardinality =40 ... 字段是全部60char长度的
mysql> desc select count(*) from sbtest order by pad;
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | sbtest | index | NULL | pad | 363 | NULL | 1000117 | Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
mysql> select count(*) from sbtest order by pad;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.45 sec)
mysql> show profile cpu,block io for query 8;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000120 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000046 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000028 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000038 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.452434 | 0.437933 | 0.014998 | 0 | 0 |
| end | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000098 | 0.000000 | 0.000000 | 0 | 8 |
| cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
这里也有区别了.有索引的情况下,
是0.44 pk 0.45s
=====================================================================================================
加到varchar(600)试下
mysql> alter table sbtest change pad pad varchar(600);
Query OK, 1000000 rows affected, 2 warnings (14.60 sec)
Records: 1000000 Duplicates: 0 Warnings: 2
mysql> desc select count(*) from sbtest order by pad;
+----+-------------+--------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------+
| 1 | SIMPLE | sbtest | ALL | NULL | NULL | NULL | NULL | 1000117 | |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.01 sec)
mysql> select count(*) from sbtest order by pad;
+----------+
| count(*) |
+----------+
| 1000000 |
+----