本文共 10760 字,大约阅读时间需要 35 分钟。
查看一下mysql版本
mysql> select version();+------------+| version() |+------------+| 5.6.27-log |+------------+1 row in set (0.01 sec)
创建测试表t1
mysql> create table t1 (id1 int, in2 int(5));Query OK, 0 rows affected (0.02 sec)mysql> desc t1;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id1 | int(11) | YES | | NULL | || in2 | int(5) | YES | | NULL | |+-------+---------+------+-----+---------+-------+2 rows in set (0.01 sec)
插入测试数据
mysql> insert into t1 values(1,1);Query OK, 1 row affected (0.02 sec)mysql> select * from t1;+------+------+| id1 | in2 |+------+------+| 1 | 1 |+------+------+1 row in set (0.01 sec)
修改字段类型,加入zerofill,可以看到数值前面用字符0填充了剩余的宽度
mysql> alter table t1 modify id1 int zerofill;Query OK, 1 row affected (0.04 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> alter table t1 modify in2 int(5) zerofill;Query OK, 1 row affected (0.02 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> select * from t1;+------------+-------+| id1 | in2 |+------------+-------+| 0000000001 | 00001 |+------------+-------+1 row in set (0.01 sec)
测试插入大于宽度限制的值,可见宽度限制并不影响数据的正常保存
mysql> insert into t1 values(1,1111111);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------------+---------+| id1 | in2 |+------------+---------+| 0000000001 | 00001 || 0000000001 | 1111111 |+------------+---------+2 rows in set (0.00 sec)
mysql小数表示分为:浮点数和定点数。定点数在mysql中用字符串形式存放,比浮点数精确,适用于表示货币等精度高的数据。两者都可以使用(M,D)方式表示,M:精度;D:标度。默认定点数在不指定精度时,默认会按照实际精度显示,二定点数默认整数位为10,小数位为0
创建测试表t1
mysql> create table t1 ( -> id1 float(5,2) default null, -> id2 double(5,2) default null, -> id3 decimal(5,2) default null);Query OK, 0 rows affected (0.03 sec)
插入数据1.23,数据都正常显示
mysql> insert into t1 values (1.23,1.23,1.23);Query OK, 1 row affected (0.00 sec)mysql> select * from t1;+------+------+------+| id1 | id2 | id3 |+------+------+------+| 1.23 | 1.23 | 1.23 |+------+------+------+1 row in set (0.00 sec)
插入数据1.234,id1、id2犹豫标度限制,舍去最后一位;id3显示被截断
mysql> insert into t1 values (1.234,1.234,1.234);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> show warnings;+-------+------+------------------------------------------+| Level | Code | Message |+-------+------+------------------------------------------+| Note | 1265 | Data truncated for column 'id3' at row 1 |+-------+------+------------------------------------------+1 row in set (0.00 sec)mysql> select * from t1;+------+------+------+| id1 | id2 | id3 |+------+------+------+| 1.23 | 1.23 | 1.23 || 1.23 | 1.23 | 1.23 |+------+------+------+2 rows in set (0.00 sec)
将字段的精度及标度都去掉,插入数据1.23。可以看到id1、id2正常,id3截断。
mysql> alter table t1 modify id1 float;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table t1 modify id2 double;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table t1 modify id3 decimal;Query OK, 2 rows affected, 2 warnings (0.03 sec)Records: 2 Duplicates: 0 Warnings: 2mysql> show warnings;+-------+------+------------------------------------------+| Level | Code | Message |+-------+------+------------------------------------------+| Note | 1265 | Data truncated for column 'id3' at row 1 || Note | 1265 | Data truncated for column 'id3' at row 2 |+-------+------+------------------------------------------+2 rows in set (0.00 sec)mysql> desc t1;+-------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------------+------+-----+---------+-------+| id1 | float | YES | | NULL | || id2 | double | YES | | NULL | || id3 | decimal(10,0) | YES | | NULL | |+-------+---------------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> insert into t1 values (1.234,1.234,1.234);Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t1;+-------+-------+------+| id1 | id2 | id3 |+-------+-------+------+| 1.23 | 1.23 | 1 || 1.23 | 1.23 | 1 || 1.234 | 1.234 | 1 |+-------+-------+------+3 rows in set (0.00 sec)
通过上面的例子,可以看到浮点数如果没有精度和标度,会安装实际精度显示,如果有精度和标度,会四舍五入。定点数如果不写精度和标度,会按照默认值decimal(10,0)来进行操作,如果数据超越了精度和标度值,系统会报错。
通过测试可知,datetime为date和time的组合。
mysql> create table t ( -> d date, -> t time, -> dt datetime);Query OK, 0 rows affected (0.02 sec)mysql> desc t;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| d | date | YES | | NULL | || t | time | YES | | NULL | || dt | datetime | YES | | NULL | |+-------+----------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> insert into t values (now(),now(),now());Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from t;+------------+----------+---------------------+| d | t | dt |+------------+----------+---------------------+| 2016-09-20 | 14:51:08 | 2016-09-20 14:51:08 |+------------+----------+---------------------+1 row in set (0.00 sec)
timestamp类型也可用来表示日期
mysql> create table t (id1 timestamp,id2 datetime);Query OK, 0 rows affected (0.00 sec)mysql> insert into t values(now(),now());Query OK, 1 row affected (0.00 sec)mysql> select * from t;+---------------------+---------------------+| id1 | id2 |+---------------------+---------------------+| 2016-09-20 15:07:55 | 2016-09-20 15:07:55 |+---------------------+---------------------+1 row in set (0.00 sec)
修改时区,可见timestamp显示当地实际时间
#当前为系统时区(东八区)mysql> show variables like 'time_zone';+---------------+--------+| Variable_name | Value |+---------------+--------+| time_zone | SYSTEM |+---------------+--------+1 row in set (0.00 sec)#修改时区mysql> set time_zone='+9:00';Query OK, 0 rows affected (0.00 sec)mysql> select * from t;+---------------------+---------------------+| id1 | id2 |+---------------------+---------------------+| 2016-09-20 16:07:55 | 2016-09-20 15:07:55 |+---------------------+---------------------+1 row in set (0.00 sec)
创建测试表vc
mysql> create table vc (v varchar(4), c char(4));Query OK, 0 rows affected (0.05 sec)
插入测试数据
mysql> insert into vc values ('ab ','ab ');Query OK, 1 row affected (0.01 sec)
显示查询结果
mysql> select length(v), length(c) from vc;+-----------+-----------+| length(v) | length(c) |+-----------+-----------+| 4 | 2 |+-----------+-----------+1 row in set (0.01 sec)mysql> select concat(v,'+'),concat(c,'+') from vc;+---------------+---------------+| concat(v,'+') | concat(c,'+') |+---------------+---------------+| ab + | ab+ |+---------------+---------------+1 row in set (0.06 sec)
可以看到char类型自动去除尾部的空格
blob和text会引起一些性能问题,特别是在执行大量删除操作时。删除操作会造成空洞,建议使用OPTIMIZE TABLE进行碎片整理。
mysql> create table t (id varchar(100),context text);Query OK, 0 rows affected (0.00 sec)mysql> insert into t values (1,repeat('haha',100));Query OK, 1 row affected (0.00 sec)mysql> insert into t values (2,repeat('haha',100));Query OK, 1 row affected (0.00 sec)mysql> insert into t values (3,repeat('haha',100));Query OK, 1 row affected (0.00 sec)mysql> insert into t select * from t;Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0......mysql> insert into t select * from t;Query OK, 393216 rows affected (4.05 sec)Records: 393216 Duplicates: 0 Warnings: 0
查看文件大小
# du -sh t.*12K t.frm365M t.ibd
删除部分数据;查看文件大小,没变化
mysql> delete from t where id=1;Query OK, 262144 rows affected (1.29 sec)# du -sh t.*12K t.frm365M t.ibd
对表进行OPTIMIZE
mysql> optimize table t;+--------+----------+----------+-------------------------------------------------------------------+| Table | Op | Msg_type | Msg_text |+--------+----------+----------+-------------------------------------------------------------------+| test.t | optimize | note | Table does not support optimize, doing recreate + analyze instead || test.t | optimize | status | OK |+--------+----------+----------+-------------------------------------------------------------------+2 rows in set (8.34 sec)[root@db3 test]# du -sh t.*12K t.frm237M t.ibd
可见空洞被回收
对于blob和text字段的查询性能问题。可以使用合成索引和前缀索引进行优化。
合成索引示例mysql> create table t ( -> id varchar(100), -> context blob, -> hash_value varchar(40));Query OK, 0 rows affected (0.00 sec)mysql> insert into t values (1,repeat('beijing',2),md5(context));Query OK, 1 row affected (0.01 sec)mysql> insert into t values (2,repeat('beijing',2),md5(context));Query OK, 1 row affected (0.00 sec)mysql> insert into t values (3,repeat('beijing 2008',2),md5(context));Query OK, 1 row affected (0.00 sec)mysql> select * from t;+------+--------------------------+----------------------------------+| id | context | hash_value |+------+--------------------------+----------------------------------+| 1 | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 || 2 | beijingbeijing | 09746eef633dbbccb7997dfd795cff17 || 3 | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |+------+--------------------------+----------------------------------+3 rows in set (0.00 sec)mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));+------+--------------------------+----------------------------------+| id | context | hash_value |+------+--------------------------+----------------------------------+| 3 | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |+------+--------------------------+----------------------------------+1 row in set (0.00 sec)
合成索引只能用于精确匹配。
使用前缀索引实现模糊查询
mysql> create index idx_blob on t(context(100));Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc select * from t where context like 'beijing%'\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ALLpossible_keys: idx_blob key: NULL key_len: NULL ref: NULL rows: 3 Extra: Using where1 row in set (0.00 sec)
避免对有blob和text字段的表进行全扫描,应尽量使用where子句并取所需字段的信息,避免造成大量的网络传输。
某些情况下,可以考虑将blob和text分离到单独的表中。