JavaRanger - 专注JAVA高性能程序开发、JVM、Mysql优化、算法

1 修改/etc/mysql/my.cnf配置文件 增加default-character-set=utf8

配置文件如下

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
default-character-set=utf8
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
[mysqld]
#
# * Basic Settings
#
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci

2 重启mysql

sudo service mysql restart

如果MySQL中varchar（M）M设置的太大，在磁盘上表占用空间大小只跟varchar中实际存放内容大小有关。

但是如果查询要生成临时表，无论临时表是放在内存还是磁盘上，varchar都会扩张成M大小。这样将会消耗更多内存或者磁盘，对性能有严重的影响。

另外能用varchar就不要使用text，text性能很差，而且非常占磁盘空间。

varchar的长度是列共享的，一张表不要超过65535字节，对utf8编码约21485个字符

mysql> explain select uid,sum(times) from tbl_name group by uid\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: tbl_name
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 10000
 Extra: Using temporary; Using filesort
1 row in set (0.00 sec)
mysql> explain select uid,sum(times) from tbl_name group by uid order by null\G;
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: tbl_name
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 10000
 Extra: Using temporary
1 row in set (0.00 sec)

默认情况下，Group by col会对col字段进行排序，这就是为什么第一语句里面有Using filesort的原因，如果你不需要对col字段进行排序，加上order by null吧，要快很多，因为filesort很慢的。

环境：CentOS5.x，MySQL5.1.x
UTF8字符集下：

SQL>create table test(id int auto_increment,name varchar(10),primary key(id));
SQL>insert into test values(null,'1234567890');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,'一二三四五六七八九十');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,'abcdefghig');
Query OK, 1 row affected (0.01 sec)
SQL>insert into test values(null,12345678901);
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>insert into test values(null,'一二三四五六七八九十1');
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>insert into test values(null,'一二三四五六七八九十一');
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>select id,name,length(name),char_length(name) from test;

阅读全文…