mysql建表优化

---

前言

之前建表比较随意,最近看了些文章总结了一点建表方面觉得值得注意的地方

---

一、关于 char,varchar,text的选择

char 是定长的,插入的数据超长不会截取直接报错,插入数据不足规定长度的，右边补空格，查询出来的数据也会有空格，在读取的时候可能要多次用到trim()。因为char是定长的，所以查询的效率比varchar高（后面会将为什么效率高），但在列容量不能充分利用的情况下会造成一定的空间浪费。
varchar 是不定长的，在5.0版本以后的最大长度是65536字节。varchar(M)中 M代表字符长度，字节和字符个数之间的换算关系是根据编码决定的utf8 65533/3=21844(汉字占3个字符)/utf8mb4 65533/4=16383(汉字占4个字符，包含了生僻汉字和文字表情)。
对比：char类型/查询效率高/,varchar省磁盘空间；如下图，varchar数据在每个数据段开头，都要有一段空间（1~2个字节）存放数据段的长度，在数据段的结尾还有一段空间（1个字节）标记此字段的节数。MySQL在读取一个数据段的时候，首先要读开头，比如读到了3，说明数据段的长度是3，之后就不多不少，只读3个字节。所以MySQL在遍历数据的时候，磁针要比char类型的列多读很多次磁盘来获取字段的真实长度。char类型在存放数据的时候，中间是没有间隔的，数据本身是有空格的，但是数据段之间没有间隔，因为我们在创建列的时候已经告诉MySQL列的长度了，MySQL在查询数据的时候，只需要按部就班寻找就行了，不需要在中途计算这个数据段的长度。
应用： char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定长数据，取时要去头尾空格。
varchar可变长度，可以设置最大长度；适合用在长度可变的属性,长度超过5000用text。
text不设置长度， 当不知道属性的最大长度时，如个人介绍，备注，使用 text需独立出一张表，用主键来对应，避免因为其大字段导致一列数据量太大，导致InnoDB 每个数据页中存放的行数减少，影响其他索引执行效率，详见（转载)

如下（示例）：

 'mobile' char(11) DEFAULT NULL COMMENT '手机号码',
 'name' varchar(100) DEFAULT NULL COMMENT '姓名',
 'remark' text COMMENT '备注'

查询速度： char>varchar>text

二、小数类型

必须使用 decimal，禁止使用 double, float,因这两种存在精度损失问题。 如果数据范围超过了 decimal的范围，可以将数据拆成整数和小数部分分开存储。

如下（示例）：

 'price' decimal(20,2) DEFAULT NULL COMMENT '价格'

三、datetime , timestamp , date的使用

DATE ‘1000-01-01’ to ‘9999-12-31’ 只有日期部分，没有时间部分
DATETIME ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’ 时间格式为 YYYY-MM-DD hh:mm:ss，默认精确到秒
TIMESTAMP ‘1970-01-01 00:00:01’ UTC to '2038-01-19 03:14:07’UTC 默认精确到秒
TIMESTAMP 适用于create_time跟update_time字段，使其入库时自动赋值（/2038年到期/）
区别： TIMESTAMP 受时区影响，存和取的时区不同会导致取的时间变化,而 DATETIME不受影响，存什么取什么；
存入的时间为 NULL时， TIMESTAMP存当前时间， DATETIME存 NULL

如下（示例）：

`create_time` TIMESTAMP NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',

---

四、索引创建

1.唯一特性的字段，包括组合字段，必须建立唯一索引
2.三表及以上禁用 join，双表使用 join检查sql性能
3. varchar字段上建立索引需指定索引长度,即前缀索引，其值的确定可以通过查看区分度确定，查询语句：SELECT COUNT(DISTINCT(LEFT(列名,索引长)))/COUNT(*) AS Selectivity FROM 表名
4.模糊搜索严禁左查询或全模糊，需要的话可以考虑走ES等搜索引擎，因为索引文件所用的B树具有最左匹配原则，左边值未确定会使其索引失效
5.联合索引的优化问题，见https://www.cnblogs.com/littlestart/p/6073298.html
6.覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。例如:SELECT phone FROM user WHERE age = 20 ORDER BY birthday > '1999-01-28' 建立联合索引和覆盖索引( ALTER TABLE user ADD INDEX idx_age_birthday_phone (age,birthday,phone);) 通过age,birthday就能查到phone的数据，不需要走B树先查主键再回表查phone
7.利用延迟关联和子查询优化超多分页场景,例如:
如下（示例）：
差 SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;
优 SELECT a.* FROM relation a, (select id from relation where biz_type ='0' AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id；
其中的优化后的 select id from relation where biz_type =‘0’ AND end_time >=‘2014-05-29’ ORDER BY id asc LIMIT 149420 ,20 用到了覆盖索引，直接确定id范围，防止回表。 先进行子查询再进行关联查询（相当于延迟了关联查询的时机），关联查询中因为已经获得了id的范围，就不会因为 1492420,20 这个大数据量而造成分页的offset性能丢失（offset的性能丢失见 转载)
8.预防隐式转换导致索引失效：主要是在用 where 或者 in 时候左右两边数据类型不兼容，使得mysql自动隐式转换时导致的索 引失效，比如 建表时，user_name 是 varchar , where 条件是 where user_name = 123

总结

祝愿:世界和平。@亲切的隔壁老王们