MySQL的数据类型和建库策略详解

无论是在小得可怜的免费数据库空间或是大型电子商务网站 合理的设计表结构 充分利用空间是十分必要的 这就要求我们对数据库系统的常用数据类型有充分的认识 下面我就将我的一点心得写出来跟大家分享

　 　一 数字类型

数字类型按照我的分类方法分为三类 整数类 小数类和数字类

我所谓的 数字类 就是指DECIMAL和NUMERIC 它们是同一种类型 它严格的说不是一种数字类型 因为他们实际上是将数字以字符串形式保存的 他的值的每一位（包括小数点）占一个字节的存储空间 因此这种类型耗费空间比较大 但是它的一个突出的优点是小数的位数固定 在运算中不会 失真 所以比较适合用于 价格 金额 这样对精度要求不高但准确度要求非常高的字段

小数类 即浮点数类型 根据精度的不同 有FLOAT（单精度）和DOUBLE（双精度）两种 它们的优势是精确度 FLOAT可以表示绝对值非常小 小到约 E （ 小数点后面有 个零）的小数 而DOUBLE更是可以表示绝对值小到约 E （ 小数点后面有 个零）的小数 FLOAT类型和DOUBLE类型占用存储空间分别是 字节和 字节 如果需要用到小数的字段 精度要求不高的 当然用FLOAT了！可是说句实在话 我们 民用 的数据 哪有要求精度那么高的呢？这两种类型至今我没有用过——我还没有遇到适合于使用它们的事例

用的最多的 最值得精打细算的 是整数类型 从只占一个字节存储空间的TINYINT到占 个字节的BIGINT 挑选一个 够用 并且占用存储空间最小的类型是设计数据库时应该考虑的 TINYINT SMALLINT MEDIUMINT INT和BIGINT占用存储空间分别为 字节 字节 字节 字节和 字节 就无符号的整数而言 这些类型能表示的最大整数分别为 和 如果用来保存用户的年龄（举例来说 数据库中保存年龄是不可取的） 用TINYINT就够了 九城的《纵横》里 各项技能值 用SMALLINT也够了 如果要用作一个肯定不会超过 行的表的AUTO_INCREMENT的IDENTIFY字段 当然用 MEDIUMINT 不用 INT 试想 每行节约一个字节 行可以节约 兆多呢！

　 　二 日期时间类型

日期和时间类型比较简单 无非是 DATE TIME DATETIME TIMESTAMP和YEAR等几个类型 只对日期敏感 而对时间没有要求的字段 就用DATE而不用DATETIME是不用说的了 单独使用时间的情况也时有发生——使用TIME 但最多用到的还是用DATETIME 在日期时间类型上没有什么文章可做 这里就不再详述

三 字符（串）类型

不要以为字符类型就是 CHAR ！CHAR和VARCHAR的区别在于CHAR是固定长度 只要你定义一个字段是CHAR( ) 那么不论你存储的数据是否达到了 个字节 它都要占去 个字节的空间 而VARVHAR则是可变长度的 如果一个字段可能的值是不固定长度的 我们只知道它不可能超过 个字符 把它定义为 VARCHAR( )是最合算的 VARCHAR 类型的实际长度是它的值的(实际长度+ ) 为什么 + 呢？这一个字节用于保存实际使用了多大的长度呀！从这个 + 中也应该看到 如果一个字段 它的可能值最长是 个字符 而多数情况下也就是用到了 个字符时 用VARCHAR就不合算了 因为在多数情况下 实际占用空间是 个字节 比用CHAR( )还多占用一个字节！

举个例子 就是一个存储股票名称和代码的表 股票名称绝大部分是四个字的 即 个字节 股票代码 上海的是六位数字 深圳的是四位数字 这些都是固定长度的 股票名称当然要用 CHAR( ) 股票代码虽然是不固定长度 但如果使用VARVHAR( ) 一个深圳的股票代码实际占用空间是 个字节 而一个上海的股票代码要占用 个字节！考虑到上海的股票数目比深圳的多 那么用VARCHAR( )就不如CHAR( )合算了

虽然一个CHAR或VARVHAR的最大长度可以到 我认为大于 的CHAR是几乎用不到的——很少有大于 个字节长度的固定长度的东东吧？不是固定长度的就用VARCHAR！大于 的VARCHAR也是几乎用不到的——比这更大的用TEXT就好了 TINYTEXT 最大长度为 占用空间也是(实际长度+ ) TEXT 最大长度 占用空间是(实际长度+ ) MEDIUMTEXT 最大长度 占用空间是(实际长度+ ) LONGTEXT 最大长度 占用空间是(实际长度+ ) 为什么 + ？ + ？ + ？ + ？你要是还不知道就该打PP了 这些可以用在论坛啊 新闻啊 什么的 用来保存文章的正文 根据实际情况的不同 选择从小到大的不同类型

四 枚举和集合类型

枚举（ENUM）类型 最多可以定义 种不同的字符串从中做出选择 只能并且必须选择其中一种 占用存储空间是一个或两个字节 由枚举值的数目决定 集合（SET）类型 最多可以有 个成员 可以选择其中的零个到不限定的多个 占用存储空间是一个到八个字节 由集合可能的成员数目决定

举个例子来说 在SQLServer中 你可以节约到用一个Bit类型来表示性别（男/女） 但MySQL没有Bit 用TINTINT？不 可以用ENUM( 帅哥 美眉 )！只有两种选择 所以只需一个字节——跟TINYINT一样大 但却可以直接用字符串 帅哥 和 美眉 来存取 真是太方便啦！

lishixinzhi/Article/program/MySQL/201311/29648

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

集一个索引包含多个列（最左前缀匹配原则）

索引列的值必须唯一，但允许有空值

全文索引为FUllText，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在CHAR,VARCHAR,TEXT类型列上创建

设定主键后数据会自动建立索引，InnoDB为聚簇索引

即一个索引只包含单个列，一个表可以有多个单列索引

覆盖索引是指一个查询语句的执行只用从所有就能够得到，不必从数据表中读取，覆盖索引不是索引树，是一个结果，当一条查询语句符合覆盖索引条件时候，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后的回表 *** 作，减少了I/O效率

查看索引

列名解析：

删除索引

查看：

删除前:

删除后：

普通的索引，没有什么介绍

查看:（注意和前缀索引Sub_part的区别）

当索引的列是unique的时候，会生成唯一索引，唯一索引关于null有下列两种情况

SQLSERVER 下的唯一索引的列，允许null值，但最多允许有一个空值

MYSQL下的唯一索引的列，允许null值，并且允许多个空值

查看:

会建立两个索引，一个非聚簇索引，一个是唯一索引

结果:

可以插入两个空值（明人不说暗话，我喜欢MySQL）

一方面，它不会索引所有字段所有字符，会减小索引树的大小.

另外一方面，索引只是为了区别出值，对于某些列，可能前几位区别很大，我们就可以使用前缀索引。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

查看：

查看：

复合索引的最左前缀匹配原则 ：

对于复合索引，查询在一定条件才会使用该索引

减少开销。 建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写 *** 作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。 对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io *** 作。减少io *** 作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。 索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w。

在模糊搜索中很有效，搜索全文中的某一个字段，可以参考这篇博文

: https://zhuanlan.zhihu.com/p/88275060

我们先进行下面一个实验看看InnoDB下的主键索引的一个现象。

查看:

我们插入进去的时候，数据的id都是乱序的，为什么这里最后select查询出来的结果都是进行了排序？

这是因为InnoDB索引底层实现的是B+tree，B+tree具有下列的特点：

所以上面的排序是为了使用B+tree的结构 ，B+tree为了范围搜索，将主键按照从小到大排序后，拆分成节点。后续还有新的节点进入的时候，和B-tree相同的 *** 作，会进行分裂。

一般来说，聚簇索引的B+tree都是三层

InnoDB中主键索引一定是聚簇索引，聚簇索引一定是主键索引。

为什么这里辅助索引叶子结点不直接存储数据呢？

MYISAM只有非聚簇索引，索引最终指向的都是物理地址。

Q:既然有回表的存在，那么聚簇索引的优势在哪里？

Q:主键索引作为聚簇索引需要注意什么

在查询语句中使用LIke关键字进行查询时，如果匹配字符串的第一个字符为"%"，索引不会使用。如果“%”不是在第一位，索引就会使用

多列索引是在表的多个字段上创建的索引，满足最左前缀匹配原则，索引才会被使用

查询语句只有Or关键字时候，如果OR前后的两个条件都是索引，这这次查询将会使用索引，否则Or前后有一个条件的列不是索引，那么查询中将不使用索引

在开始演示之前，我们先介绍下两个概念。

概念一，数据的可选择性基数，也就是常说的cardinality值。

查询优化器在生成各种执行计划之前，得先从统计信息中取得相关数据，这样才能估算每步 *** 作所涉及到的记录数，而这个相关数据就是cardinality。简单来说，就是每个值在每个字段中的唯一值分布状态。

比如表t1有100行记录，其中一列为f1。f1中唯一值的个数可以是100个，也可以是1个，当然也可以是1到100之间的任何一个数字。这里唯一值越的多少，就是这个列的可选择基数。

那看到这里我们就明白了，为什么要在基数高的字段上建立索引，而基数低的的字段建立索引反而没有全表扫描来的快。当然这个只是一方面，至于更深入的探讨就不在我这篇探讨的范围了。

概念二，关于HINT的使用。

这里我来说下HINT是什么，在什么时候用。

HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作，使她生成最优的执行计划。一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化。

比如：表t1经过大量的频繁更新 *** 作，（UPDATE,DELETE,INSERT），cardinality已经很不准确了，这时候刚好执行了一条SQL，那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢？

来看下具体演示

譬如，以下两条SQL，

A：

select * from t1 where f1 = 20

B：

select * from t1 where f1 = 30

如果f1的值刚好频繁更新的值为30，并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等，那么对这两条语句来说，可能不准确的就是B了。

这里顺带说下，MySQL提供了自动更新和手动更新表cardinality值的方法，因篇幅有限，需要的可以查阅手册。

那回到正题上，MySQL 8.0 带来了几个HINT，我今天就举个index_merge的例子。

示例表结构：

mysql>desc t1+------------+--------------+------+-----+---------+----------------+| Field      | Type         | Null | Key | Default | Extra          |+------------+--------------+------+-----+---------+----------------+| id         | int(11)      | NO   | PRI | NULL    | auto_increment || rank1      | int(11)      | YES  | MUL | NULL    |                || rank2      | int(11)      | YES  | MUL | NULL    |                || log_time   | datetime     | YES  | MUL | NULL    |                || prefix_uid | varchar(100) | YES  |     | NULL    |                || desc1      | text         | YES  |     | NULL    |                || rank3      | int(11)      | YES  | MUL | NULL    |                |+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

表记录数：

mysql>select count(*) from t1+----------+| count(*) |+----------+|    32768 |+----------+1 row in set (0.01 sec)

这里我们两条经典的SQL：

SQL C：

select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2

SQL D：

select * from t1 where rank1 =100  and rank2 =100  and rank3 =100

表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。

那我们来看SQL C的查询计划。

显然，没有用到任何索引，扫描的行数为32034，cost为3243.65。

mysql>explain  format=json select * from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "3243.65"    },    "table": {      "table_name": "t1",      "access_type": "ALL",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "rows_examined_per_scan": 32034,      "rows_produced_per_join": 115,      "filtered": "0.36",      "cost_info": {        "read_cost": "3232.07",        "eval_cost": "11.58",        "prefix_cost": "3243.65",        "data_read_per_join": "49K"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))"    }  }}1 row in set, 1 warning (0.00 sec)

我们加上hint给相同的查询，再次看看查询计划。

这个时候用到了index_merge,union了三个列。扫描的行数为1103，cost为441.09，明显比之前的快了好几倍。

mysql>explain  format=json select /*+ index_merge(t1) */ * from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "441.09"    },    "table": {      "table_name": "t1",      "access_type": "index_merge",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "union(idx_rank1,idx_rank2,idx_rank3)",      "key_length": "5,5,5",      "rows_examined_per_scan": 1103,      "rows_produced_per_join": 1103,      "filtered": "100.00",      "cost_info": {        "read_cost": "330.79",        "eval_cost": "110.30",        "prefix_cost": "441.09",        "data_read_per_join": "473K"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))"    }  }}1 row in set, 1 warning (0.00 sec)

我们再看下SQL D的计划：

不加HINT，

mysql>explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "534.34"    },    "table": {      "table_name": "t1",      "access_type": "ref",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "idx_rank1",      "used_key_parts": [        "rank1"      ],      "key_length": "5",      "ref": [        "const"      ],      "rows_examined_per_scan": 555,      "rows_produced_per_join": 0,      "filtered": "0.07",      "cost_info": {        "read_cost": "478.84",        "eval_cost": "0.04",        "prefix_cost": "534.34",        "data_read_per_join": "176"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))"    }  }}1 row in set, 1 warning (0.00 sec)

加了HINT，

mysql>explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "5.23"    },    "table": {      "table_name": "t1",      "access_type": "index_merge",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "intersect(idx_rank1,idx_rank2,idx_rank3)",      "key_length": "5,5,5",      "rows_examined_per_scan": 1,      "rows_produced_per_join": 1,      "filtered": "100.00",      "cost_info": {        "read_cost": "5.13",        "eval_cost": "0.10",        "prefix_cost": "5.23",        "data_read_per_join": "440"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))"    }  }}1 row in set, 1 warning (0.00 sec)

对比下以上两个，加了HINT的比不加HINT的cost小了100倍。

总结下，就是说表的cardinality值影响这张的查询计划，如果这个值没有正常更新的话，就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。

---