enum 有什么好处_作为开发人员，为什么mysql总是很多问题，请参考以下开发规范...

最新推荐文章于 2022-05-07 10:11:25 发布

weixin_39637256

最新推荐文章于 2022-05-07 10:11:25 发布

阅读量93

点赞数

文章标签： enum 有什么好处找不到mysql的驱动

面对各种各样的MySQL开发、设计规范，哪些最为重要，又有哪些误区呢？

大多数MySQL规范在网上也都能找得到相关的文章，在这我们讨论下比较重要的，容易忽略的几点

建议默认使用InnoDB引擎

InnoDB适用于几乎99%的MySQL应用场景，而且在MySQL 5.7的系统表都改成InnoDB了，我们还有什么原因再死守着 MyISAM呢?

字符集选择utf-8mb4

若为了节省磁盘空间，则建议选择latin1。建议选择utf-8mb4为了更好的通用性，互联网发展到现在，各种花式字符层出不穷，而磁盘价格持续下降，其实没必要节省那么点空间。

3、InnoDB表行记录物理长度不超过8KB

InnoDB的data page默认是16KB，基于B+Tree的特点，一个data page中需要至少存储2条记录。因此，当实际存储长度超过8KB(尤其是TEXT/BLOB列)的大列(large column)时会引起“page-overflow存储”，类似ORACLE中的“行迁移”。

因此，如果必须使用大列(尤其是TEXT/BLOB类型)且读写频繁的话，则最好把这些列拆分到子表中，不要和主表放在一起存储。如果不太频繁，可以考虑继续保留在主表中。

当然了，如果将 innodb_page_size 选项修改成 8KB，那么行记录物理长度建议不超过4KB。

是否使用分区表

在一些使用分区表后明显可以提升性能或者运维便利性的场景下，还是建议使用分区表。

比如在zabbix的数据库采用TokuDB引擎的前提下，又根据时间维度使用了分区表。这样的好处是保证zabbix日常应用不受到影响前提下，方便管理员例行删除过去数据，只需要删除相应分区即可，不需再执行一个非常慢的DELETE而影响整体性能。

是否使用存储过程、触发器

在一些合适的场景下，用存储过程、触发器也完全没问题。

我们以前就是利用存储完成游戏业务逻辑处理，性能上不是问题，而且一旦需求有变更，只需修改存储过程，变更代价很低。我们还利用触发器维护一个频繁更新的表，对这个表的所有变更都将部分字段同步更新到另一个表中(类似物化视图的变相实现)，也不存在性能问题。

不要把MySQL的存储过程和触发器视为洪水猛兽，用好的话，没有问题的，真遇到问题了再优化也不迟。另外，MySQL因为没有物化视图，因此视图能不用就尽量少用吧。

选择合适的类型

除了常见的建议外，还有其他几个要点：

用INT UNSIGNED存储IPV4地址，用INET_ATON()、INET_NTOA()进行转换，基本上没必要使用CHAR(15)来存储。
枚举类型可以使用ENUM，ENUM的内部存储机制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR)，性能一点都不差，记住千万别用CHAR/VARCHAR 来存储枚举数据。
还个早前一直在传播的“常识性误导”，建议用TIMESTAMP取代DATETIME。其实从5.6开始，建议优先选择DATETIME存储日期时间，因为它的可用范围比TIMESTAMP更大，物理存储上仅比TIMESTAMP多1个字节，整体性能上的损失并不大。
所有字段定义中，默认都加上NOT NULL约束，除非必须为NULL(但我也想不出来什么场景下必须要在数据库中存储NULL值，可以用0来表示)。在对该字段进行COUNT()统计时，统计结果更准确(值为NULL的不会被COUNT统计进去)，或者执行 WHERE column IS NULL 检索时，也可以快速返回结果。
尽可能不要直接 SELECT * 读取全部字段，尤其是表中存在 TEXT/BLOB 大列的时候。可能本来不需要读取这些列，但因为偷懒写成 SELECT * 导致内存buffer pool被这些“垃圾”数据把真正需要缓冲起来的热点数据给洗出去了。

关于索引

除了常见的建议外，还有几个要点：

超过20个长度的字符串列，最好创建前缀索引而非整列索引(例如：ALTER TABLE t1 ADD INDEX(user(20)))，可以有效提高索引利用率，不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出，一般略大于平均长度一点就可以了。
定期用 pt-duplicate-key-checker 工具检查并删除重复的索引。比如 index idx1(a, b) 索引已经涵盖了 index idx2(a)，就可以删除 idx2 索引了。
有多字段联合索引时，WHERE中过滤条件的字段顺序无需和索引一致，但如果有排序、分组则就必须一致了。

比如有联合索引 idx1(a, b, c)，那么下面的SQL都可以完整用到索引：

SELECT ... WHERE b = ? AND c = ? AND a = ?; --注意到，WHERE中字段顺序并没有和索引字段顺序一致

SELECT ... WHERE b = ? AND a = ? AND c = ?;

SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;

SELECT ... WHERE a = ? AND b = ? ORDER BY c;

SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;

SELECT ... WHERE a = ? ORDER BY b, c;

SELECT ... ORDER BY a, b, c; -- 可利用联合索引完成排序

而下面几个SQL则只能用到部分索引：

SELECT ... WHERE b = ? AND a = ?; -- 只能用到 (a, b) 部分

SELECT ... WHERE a IN (?, ?) AND b = ?; -- 只能用到 (a, b) 部分

SELECT ... WHERE a = ? AND c = ?; -- 只能用到 (a) 部分

SELECT ... WHERE a = ? AND b IN (?, ?); -- 只能用到 (a, b) 部分

SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?; -- 只能用到 (a) 部分，注意BETWEEN和IN的区别

SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?; -- 只能用到 (a, b) 部分

下面的几个SQL完全用不到该索引：

SELECT ... WHERE b = ?;

SELECT ... WHERE b = ? AND c = ?;

SELECT ... WHERE b = ? AND c = ?;

SELECT ... ORDER BY b;

SELECT ... ORDER BY b, a;

从上面的几个例子就能看的出来，以往强调的WHERE条件字段顺序要和索引顺序一致才能使用索引的 “常识性误导” 无需严格遵守。

此外，有些时候查询优化器指定的索引或执行计划可能并不是最优的，可以手工指定最优索引，或者修改session级的 optimizer_switch 选项，关闭某些导致效果反而更差的特性(比如index merge通常是好事，但也遇到过用上index merge后反而更差的，这时候要么强制指定其中一个索引，要么可以临时关闭 index merge 特性)。

其他

1、哪怕是基于索引的条件过滤，如果优化器意识到总共需要扫描的数据量超过30%时(ORACLE里貌似是20%，MySQL目前是30%，没准以后会调整)，就会直接改变执行计划为全表扫描，不再使用索引。

2、多表JOIN时，要把过滤性最大(不一定是数据量最小哦，而是只加了WHERE条件后过滤性最大的那个)的表选为驱动表。此外，如果JOIN之后有排序，排序字段一定要属于驱动表，才能利用驱动表上的索引完成排序。

3、绝大多数情况下，排序的大家通常要来的更高，因此如果看到执行计划中有 Using filesort，优先创建排序索引吧。

4、利用 pt-query-digest 定期分析slow query log，并结合 Box Anemometer 构建slow query log分析及优化系统。