准备数据

sysbench --test=oltp --oltp-nontrx-mode=update_key --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-socket=/tmp/mysql3392.sock --mysql-user=dba --mysql-host=localhost --mysql-password=localdba --db-driver=mysql --mysql-db=test prepare

mysql> desc sbtest;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| k | int(10) unsigned | NO | MUL | 0 | |
| c | char(120) | NO | | | |
| pad | char(60) | NO | | | |
+-------+------------------+------+-----+---------+----------------+

mysql> select count(c) from sbtest;
+----------+
| count(c) |
+----------+
| 1000000 |
+----------+

char的情况下：：：

mysql> desc select * 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 | 1000126 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+

mysql> set profiling = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from sbtest order by pad;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.53 sec)

mysql> show profiles;
+----------+------------+------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------+
| 1 | 0.53102850 | select count(*) from sbtest order by pad |
+----------+------------+------------------------------------------+
1 row in set (0.02 sec)

mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000127 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000042 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000022 | 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.000022 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.530471 | 0.502923 | 0.018997 | 32 | 0 |
| end | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000037 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000128 | 0.000000 | 0.000000 | 0 | 8 |
| cleaning up | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+

/////char　change to varchar

mysql> alter table sbtest change pad pad varchar(60);
Query OK, 1000000 rows affected (10.72 sec)
Records: 1000000 Duplicates: 0 Warnings: 0