Mysql学习重要的MySQL开发规范
|
《Mysql学习重要的MySQL开发规范》要点： MYSQL学习? MYSQL学习1、默认使用InnoDB引擎 MYSQL学习此外,频繁读写的InnoDB表,一定要使用具有自增/顺序特征的整型作为显式主键. MYSQL学习【参考】：[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键. MYSQL学习2、字符集选择utf-8 MYSQL学习用latin1存储utf-8数据可能遇到的麻烦是,如果有基于中文的检索时,可能无法100%准确(老叶亲自简单测试常规的中文完检索全不是问题,也就是一般的中文对比是没问题的). MYSQL学习用latin1字符集存储utf-8数据的做法是：在web端(用户端)的字符集是utf-8,后端程序也采用utf-8来处理,但 character_set_client、character_set_connection、character_set_results、character_set_database、character_set_server 这几个都是 latin1,且数据表、字段的字符集也是latin1.或者说数据表采用latin1,每次连接后执行?SET NAMES LATIN1?即可. MYSQL学习【参考】：小谈MySQL字符集(阅读原文查看). MYSQL学习3、InnoDB表行记录物理长度不超过8KB MYSQL学习【老叶观点】InnoDB的data page默认是16KB,基于B+Tree的特点,一个data page中需要至少存储2条记录.因此,当实际存储长度超过8KB(尤其是TEXT/BLOB列)的大列(large column)时会引起“page-overflow存储”,类似ORACLE中的“行迁移”. MYSQL学习因此,如果必须使用大列(尤其是TEXT/BLOB类型)且读写频繁的话,则最好把这些列拆分到子表中,不要和主表放在一起存储.如果不太频繁,可以考虑继续保留在主表中. MYSQL学习当然了,如果将 innodb_page_size 选项修改成 8KB,那么行记录物理长度建议不超过4KB. MYSQL学习【参考】：[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率. MYSQL学习4、是否使用分区表 MYSQL学习比如老叶就在zabbix的数据库采用TokuDB引擎的前提下,又根据时间维度使用了分区表.这样的好处是保证zabbix日常应用不受到影响前提下,方便管理员例行删除过去数据,只需要删除相应分区即可,不需再执行一个非常慢的DELETE而影响整体性能. MYSQL学习参考：迁移Zabbix数据库到TokuDB. MYSQL学习5、是否使用存储过程、触发器 MYSQL学习我们以前就是利用存储完成游戏业务逻辑处理,性能上不是问题,而且一旦需求有变更,只需修改存储过程,变更代价很低.我们还利用触发器维护一个频繁更新的表,对这个表的所有变更都将部分字段同步更新到另一个表中(类似物化视图的变相实现),也不存在性能问题. MYSQL学习不要把MySQL的存储过程和触发器视为洪水猛兽,用好的话,没有问题的,真遇到问题了再优化也不迟.另外,MySQL因为没有物化视图,因此视图能不用就尽量少用吧. MYSQL学习6、选择合适的类型 MYSQL学习6.1、用INT UNSIGNED存储IPV4地址,用INET_ATON()、INET_NTOA()进行转换,基本上没必要使用CHAR(15)来存储. MYSQL学习6.2、枚举类型可以使用ENUM,0);">ENUM的内部存储机制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR),性能一点都不差,记住千万别用CHAR/VARCHAR 来存储枚举数据. MYSQL学习6.3、还个早前一直在传播的“常识性误导”,建议用TIMESTAMP取代DATETIME.其实从5.6开始,建议优先选择DATETIME存储日期时间,因为它的可用范围比TIMESTAMP更大,物理存储上仅比TIMESTAMP多1个字节,整体性能上的损失并不大. MYSQL学习6.4、所有字段定义中,默认都加上NOT NULL约束,除非必须为NULL(但我也想不出来什么场景下必须要在数据库中存储NULL值,可以用0来表示).在对该字段进行COUNT()统计时,统计结果更准确(值为NULL的不会被COUNT统计进去),或者执行 WHERE column IS NULL 检索时,也可以快速返回结果. MYSQL学习6.5、尽可能不要直接 SELECT * 读取全部字段,尤其是表中存在 TEXT/BLOB 大列的时候.可能本来不需要读取这些列,但因为偷懒写成 SELECT * 导致内存buffer pool被这些“垃圾”数据把真正需要缓冲起来的热点数据给洗出去了. MYSQL学习8、关于索引 MYSQL学习 MYSQL学习8.2、定期用 pt-duplicate-key-checker 工具检查并删除重复的索引.比如 index idx1(a,b) 索引已经涵盖了 index idx2(a),就可以删除 idx2 索引了. MYSQL学习8.3、有多字段联合索引时,WHERE中过滤条件的字段顺序无需和索引一致,但如果有排序、分组则就必须一致了. MYSQL学习比如有联合索引 idx1(a,b,c),那么下面的SQL都可以完整用到索引：
MYSQL学习而下面几个SQL则只能用到部分索引：
MYSQL学习下面的几个SQL完全用不到该索引： MYSQL学习从上面的几个例子就能看的出来,以往强调的WHERE条件字段顺序要和索引顺序一致才能使用索引的 “常识性误导”?无需严格遵守. optimizer_switch?选项,关闭某些导致效果反而更差的特性(比如index merge通常是好事,但也遇到过用上index merge后反而更差的,这时候要么强制指定其中一个索引,要么可以临时关闭 index merge 特性). MYSQL学习9、其他 MYSQL学习9.2、多表JOIN时,要把过滤性最大(不一定是数据量最小哦,而是只加了WHERE条件后过滤性最大的那个)的表选为驱动表.此外,如果JOIN之后有排序,排序字段一定要属于驱动表,才能利用驱动表上的索引完成排序. MYSQL学习9.3、绝大多数情况下,排序的大家通常要来的更高,因此如果看到执行计划中有 Using filesort,优先创建排序索引吧. MYSQL学习9.4、利用?pt-query-digest?定期分析slow query log,并结合?Box Anemometer?构建slow query log分析及优化系统. MYSQL学习【参考】：[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注. MYSQL学习备注：若无特别说明,以上规范建议适用于MySQL 5.6及之前的版本.5.7及之后的版本可能会有些变化,个别规范建议需要相应调整. MYSQL学习? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |