选择合适的数据类型

char和varchar

下表为char和varchar的区别：

• char固定长度，处理速度比varchar快，但是浪费空间资源（程序要对尾部空间进行空格处理）。对那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用char类型。
• 随着MySQL的升级，varchar的性能再被不断提高，varchar被更多的使用。

在不同存储引擎下的使用原则：

• MyISAM：建议使用固定长度的列替换可变长度的列。

• InnoDB：建议使用varchar类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据都是使用指向数据列值的头指针），因此，本质上，char性能不一定比varchar好。主要的性能因素是数据行使用的存储总量。由于char平均占用的空间多于varchar，因此使用varchar来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

  TEXT和BLOB

• 少量字符串使用char和varchar。

• 较大文本时使用TEXT或者BLOB。
  • 主要区别是BLOB能用来保存二进制数据，比如照片；
  • TEXT保存字符数据，比如文章或者日记。
  • BLOB（BLOB/MEDIUMBLOB/LONGBLOB）和TEXT（TEXT/MEDIUMTEXT/LONGTEXT），三种不同类型主要区别是存储文本长度和存储字节不同。

常见问题：

1. BLOB和TEXT可能引起性能问题，特别是执行了大量删除操作时。
   删除操作会留下很大的“空洞”，以后填上这些“空洞”的记录在插入的性能会有影响。可以定期使用OPTIMIZE TABLE tb_name功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。eg:optimize table的碎片整理功能。创建test6表，字段id和content。 ```sql ———————创建表———————————— mysql> create table test6(id int, content text); Query OK, 0 rows affected (0.02 sec)

———————插入测试数据—————————— mysql> insert into test6 values (1, repeat(‘haha’, 100)); Query OK, 1 row affected (0.00 sec)

mysql> insert into test6 values (2, repeat(‘haha’, 100)); Query OK, 1 row affected (0.01 sec)

mysql> insert into test6 values (3, repeat(‘haha’, 100)); Query OK, 1 row affected (0.00 sec)

mysql> insert into test6 select * from test6; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into test6 select * from test6; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0

mysql> insert into test6 select * from test6; Query OK, 12 rows affected (0.00 sec) Records: 12 Duplicates: 0 Warnings: 0

mysql> insert into test6 select * from test6; Query OK, 24 rows affected (0.00 sec) Records: 24 Duplicates: 0 Warnings: 0

mysql> insert into test6 select * from test6; Query OK, 48 rows affected (0.02 sec) Records: 48 Duplicates: 0 Warnings: 0

mysql> insert into test6 select * from test6; Query OK, 96 rows affected (0.00 sec) Records: 96 Duplicates: 0 Warnings: 0

mysql> insert into test6 select * from test6; Query OK, 192 rows affected (0.01 sec) Records: 192 Duplicates: 0 Warnings: 0

mysql> insert into test6 select * from test6; Query OK, 384 rows affected (0.01 sec) Records: 384 Duplicates: 0 Warnings: 0

查看磁盘使用情况
```shell
root# pwd
/usr/local/mysql/data/testdb
root# du -sh test6*
 12K    test6.frm
480K    test6.ibd

删除id为1的记录

mysql> delete from test6 where id = 1;
Query OK, 256 rows affected (0.00 sec)

再次查看自盘使用情况，原本磁盘占用空间应该减少1/3，但是并没有减少。

root# du -sh test6*
 12K    test6.frm
480K    test6.ibd

使用optimize table tb_name优化空间。

mysql> optimize table test6;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| testdb.test6 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| testdb.test6 | optimize | status   | OK                                                                |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.06 sec)

再次查看空间，碎片被整理，test6.ibd文件缩小了接近1/3空间。

root# du -sh test6*
 12K    test6.frm
336K    test6.ibd

当optimize table ta_name优化时，msg显示Table does not support optimize, doing recreate + analyze instead，参考以下博主文章：

1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。
3.在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
4.默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld —skip-new或者mysqld —safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。 版权声明：本文为CSDN博主「e421083458」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/e421083458/java/article/details/39522513

1. 可以使用合成（synthetic）索引来提高大文本字段（blob/text）来提高大本文字段的查询效率。
   • 合成索引就是根据大文本字段生成一个散列值，并存储在单独的数据列中，然后通过散列值找到相应的行。
   • 但是这种技术只能用于精确匹配（散列值对于范围匹配<，>=等操作没有用）。
   • 可以使用md5()，SHA1()或者应用程序自定义逻辑生成散列值。
   • 如果散列值存在尾部空间，就不要存储在char或者varchar中。

eg：创建表test7，字段id，content，hash_value，类型分别为int, blob, varchar(32)。

----------------创建表----------------
mysql> create table test7(id int, content blob, hash_value char(32));
Query OK, 0 rows affected (0.02 sec)

----------------插入测试数据----------------
mysql> insert into test7 values (1, repeat('abcdefg', 20), md5(content));
Query OK, 1 row affected (0.00 sec)

mysql> insert into test7 values (1, repeat('abcd', 20), md5(content));
Query OK, 1 row affected (0.00 sec)

mysql> select * from test7 where hash_value=md5('abcd');
Empty set (0.00 sec)

----------------使用hash_value查询----------------
mysql> select * from test7 where hash_value=md5(repeat('abcd', 20));
+------+----------------------------------------------------------------------------------+----------------------------------+
| id   | content                                                                          | hash_value                       |
+------+----------------------------------------------------------------------------------+----------------------------------+
|    1 | abcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcd | dc1be0a41293486d24f0530f7bc45d75 |
+------+----------------------------------------------------------------------------------+----------------------------------+
1 row in set (0.00 sec)

1. 使用前缀索引实现精确查询。
   eg:对content字段的前20个字节进行模糊查询。模糊查询%不能放在第一，否则索引将不会被使用。

   mysql> create index `idx_test7_content` on test7(content(20));
   mysql> mysql> select * from test7 where content like 'abc%' \G
   *************************** 1. row ***************************
        id: 1
   content: abcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcdabcd
   hash_value: dc1be0a41293486d24f0530f7bc45d75
   *************************** 2. row ***************************
        id: 1
   content: abcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefgabcdefg
   hash_value: 4ba3b190adc6b0bce2e0d9b1f451534d
   2 rows in set (0.00 sec)
   

2. 要避免在不必要的时候检索BLOB或者TEXT值。

3. 把BLOB或TEXT列分离到单独的表中。 可以将原数据表的字段转换为固定长度，以减少主表中的碎片，可以得到固定长度数据行的性能优势。并且可以使得主表在select *的时候数据量不会太大。

   浮点数和定点数

• 浮点数：插入时，如果超过精度，则会四舍五入插入，并不会报错。
• 定点数：在默认SQLMode模式下，按照四舍五入插入，会给警告，在SQLMode TRADITIONAL下，会报错。

一些例子：

----------example 浮点数四舍五入-----------
mysql> create table test8 (f float(8,1));
mysql> insert into test8 values (1.2345);
mysql> select * from test8;
+------+
| f    |
+------+
|  1.2 |
+------+
---------example 浮点数精度问题--------------
mysql> create table test9 (c1 float(10,2), c2 decimal(10,2));
mysql> insert into test9 values (131072.32, 131072.32);
mysql> select * from test9;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+

BEST PRACTICE使用原则：

• 浮点数存在误差
• 对货币等敏感数据，应该使用定点数表示和存储
• 在编程中，如果用到浮点数，要特别注意误差，并尽量尽量避免做浮点数比较
• 注意浮点数中一写特殊值的处理