准备数据 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 --mysq准备数据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 mysql> select pad from sbtest limit 30;+----------------------------------------------------+| pad |+----------------------------------------------------+| qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt || qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |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 | varchar(60) | YES | | NULL | |+-------+------------------+------+-----+---------+----------------+＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝无索引的情况下：＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝varchar(60)的情况下：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 | 1000117 | Using filesort |+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+mysql> select count(*) from sbtest order by pad;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.51 sec) mysql> show profile cpu,block io for query 11;+----------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting | 0.000081 | 0.000000 | 0.000000 | 0 | 0 || checking permissions | 0.000014 | 0.000000 | 0.000000 | 0 | 0 || Opening tables | 0.000033 | 0.000000 | 0.000000 | 0 | 0 || System lock | 0.000025 | 0.000000 | 0.000000 | 0 | 0 || init | 0.000028 | 0.000000 | 0.000000 | 0 | 0 || optimizing | 0.000009 | 0.000000 | 0.000000 | 0 | 0 || statistics | 0.000019 | 0.000000 | 0.000000 | 0 | 0 || preparing | 0.000013 | 0.000000 | 0.000000 | 0 | 0 || executing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 || Sending data | 0.506327 | 0.489925 | 0.018997 | 0 | 0 || end | 0.000026 | 0.001000 | 0.000000 | 0 | 0 || query end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 || closing tables | 0.000022 | 0.000000 | 0.000000 | 0 | 0 || freeing items | 0.000034 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000114 | 0.000000 | 0.000000 | 0 | 8 || cleaning up | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |+----------------------+----------+----------+------------+--------------+---------------+varchar(120)的情况下：mysql> alter table sbtest change pad pad varchar(120);Query OK, 1000000 rows affected (11.77 sec) Records: 1000000 Duplicates: 0 Warnings: 0 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 | varchar(120) | YES | | NULL | |+-------+------------------+------+-----+---------+----------------+mysql> desc select * from sbtest;+----+-------------+--------+------+---------------+------+---------+------+---------+-------+| 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.00 sec) mysql> desc select count(*) from sbtest;+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+| 1 | SIMPLE | sbtest | index | NULL | k | 4 | NULL | 1000117 | Using index |+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+1 row in set (0.00 sec) mysql> select count(*) from sbtest order by pad;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.51 sec) mysql> show profile cpu,block io for query 17;+----------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting | 0.000073 | 0.000000 | 0.000000 | 0 | 0 || checking permissions | 0.000013 | 0.000000 | 0.000000 | 0 | 0 || Opening tables | 0.000031 | 0.000000 | 0.000000 | 0 | 0 || System lock | 0.000024 | 0.000000 | 0.000000 | 0 | 0 || init | 0.000026 | 0.000000 | 0.000000 | 0 | 0 || optimizing | 0.000008 | 0.000000 | 0.000000 | 0 | 0 || statistics | 0.000019 | 0.000000 | 0.000000 | 0 | 0 || preparing | 0.000013 | 0.000000 | 0.000000 | 0 | 0 || executing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 || Sending data | 0.510981 | 0.491925 | 0.018997 | 0 | 0 || end | 0.000025 | 0.000000 | 0.000000 | 0 | 0 || query end | 0.000013 | 0.000000 | 0.000000 | 0 | 0 || closing tables | 0.000026 | 0.000000 | 0.000000 | 0 | 0 || freeing items | 0.000039 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000110 | 0.000000 | 0.000000 | 0 | 8 || cleaning up | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |+----------------------+----------+----------+------------+--------------+---------------+＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝有索引的情况下：＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝alter table sbtest change pad pad varchar(60);mysql> alter table sbtest add key(pad);Query OK, 0 rows affected (7.60 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from sbtest;+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| 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 | 200 | NULL | NULL | YES | BTREE | | |+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+varchar(60) 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 | 183 | NULL | 1000117 | Using index |+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+mysql> select count(*) from sbtest order by pad;+----------+| count(*) |+----------+| 1000000 |+----------+1 row in set (0.44 sec) mysql> show profile cpu,block io for query 1-> ;+----------------------+----------+----------+------------+--------------+---------------+| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting | 0.000115 | 0.000000 | 0.000000 | 0 | 0 || checking permissions | 0.000017 | 0.000000 | 0.000000 | 0 | 0 || Opening tables | 0.000043 | 0.000000 | 0.000000 | 0 | 0 || System lock | 0.000023 | 0.000000 | 0.000000 | 0 | 0 || init | 0.000038 | 0.000000 | 0.000000 | 0 | 0 || optimizing | 0.000010 | 0.000000 | 0.000000 | 0 | 0 || statistics | 0.000024 | 0.000000 | 0.000000 | 0 | 0 || preparing | 0.000018 | 0.000000 | 0.000000 | 0 | 0 || executing | 0.000015 | 0.000000 | 0.000000 | 0 | 0 || Sending data | 0.445040 | 0.428935 | 0.015998 | 0 | 0 || end | 0.000023 | 0.000000 | 0.000000 | 0 | 0 || query end | 0.000013 | 0.000000 | 0.000000 | 0 | 0 || closing tables | 0.000028 | 0.000000 | 0.000000 | 0 | 0 || freeing items | 0.000041 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 || logging slow query | 0.000133 | 0.000000 | 0.001000 | 0 | 8 || cleaning up | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |+----------------------+----------+----------+------------+--------------+---------------+varchar(120)的:mysql> alter table sbtest change pad pad varchar(120);Query OK, 1000000 rows affected (14.56 sec) Records: 1000000 Duplicates: 0 Warnings: 0 mysql> show index from sbtest;+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| 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 |+----------+1 row in set (0.50 sec) 600是0.5s....