varchar 所占内存的影响 测试

23ee

准备数据

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....