、多表JOIN时,MySQL开发规范之我见

时间:2016-05-11 作者:叶白帆 阅读:1744次

  、多表JOIN时,MySQL开发规范之我见   建议选择utf-8通常是为了所谓的“通用性”,但事实上用户提交的utf-8数据也一样可以以latin1字符集存储,、定期用 pt-duplicate-key-checker 工具检查并删除重复的索引,、绝大多数情况下,排序的大家通常要来的更高,因此如果看到执行计划中有 Using filesort,优先创建排序索引吧。   大多数MySQL规范在网上也都能找得到相关的分享,在这里要分享的是老叶个人认为比较重要的,或者容易被忽视的,以及容易被混淆的一些地方。   已多次呼吁过了,InnoDB适用于几乎99%的MySQL应用常合盗?— 为什么InnoDB表要建议用自增列做主键。   若为了节省磁盘空间,则建议选择latin1。建议选择utf-8通常是为了所谓的“通用性”,但事实上用户提交的utf-8数据也一样可以以latin1字符集存储。   用latin1存储utf-8数据可能遇到的麻烦是,如果有基于中文的检索时,可能无法100%准确。   用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 即可。   3、InnoDB表行记录物理长度不超过8KB   InnoDB的data page默认是16KB,基于B+Tree的特点,一个data page中需要至少存储2条记录。因此,当实际存储长度超过8KB的大列时会引起“page-overflow存储”,类似ORACLE中的“行迁移”。   
、多表JOIN时,MySQL开发规范之我见
  因此,如果必须使用大列且读写频繁的话,则最好把这些列拆分到子表中,不要和主表放在一起存储。如果不太频繁,可以考虑继续保留在主表中。   当然了,如果将 innodb_page_size 选项修改成 8KB,那么行记录物理长度建议不超过4KB。   :系列 — 优化InnoDB表BLOB列的存储效率。   在一些使用分区表后明显可以提升性能或者运维便利性的常呵ㄒ芞abbix数据库到TokuDB。   在一些合适的吵顺<慕ㄒ橥猓褂衅渌父鲆悖?   、用INT UNSIGNED存储IPV4地址,用_ATON、_NTOA进行转换,基本上没必要使用CHAR来存储。   、枚举类型可以使用ENUM,ENUM的内部存储机制是采用TINYINT或SMALLINT,性能一点都不差,记住千万别用CHAR/VARCHAR 来存储枚举数据。   、还个早前一直在传播的“常识性误导”,建议用TIMESTAMP取代DATETIME。其实从开始,建议优先选择DATETIME存储日期时间,因为它的可用范围比TIMESTAMP更大,物理存储上仅比TIMESTAMP多1个字节,整体性能上的损失并不大。   、所有字段定义中,默认都加上NOT NULL约束,除非必须为NULL)),可以有效提高索引利用率,不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出,一般略大于平均长度一点就可以了。   、定期用 pt-duplicate-key-checker 工具检查并删除重复的索引。比如 index idx1 索引已经涵盖了 index idx2,就可以删除 idx2 索引了。   、有多字段联合索引时,WHERE中过滤条件的字段顺序无需和索引一致,但如果有排序、分组则就必须一致了。   比如有联合索引 idx1,那么下面的SQL都可以完整用到索引:   = ? AND c = ? AND a = ?; --注意到,WHERE中字段顺序并没有和索引字段顺序一致   , b, c; -- 可利用联合索引完成排序   而下面几个SQL则只能用到部分索引:   = ? AND a = ?; -- 只能用到 部分   = ? AND c = ?; -- 只能用到 部分   = ? AND b IN ; -- 只能用到 部分   = ?; -- 只能用到 部分,注意BETWEEN和IN的区别   = ? AND AND c = ?; -- 只能用到 部分   下面的几个SQL完全用不到该索引:   从上面的几个例子就能看的出来,以往强调的WHERE条件字段顺序要和索引顺序一致才能使用索引的 “常识性误导” 无需严格遵守。   此外,有些时候查询优化器指定的索引或执行计划可能并不是最优的,可以手工指定最优索引,或者修改session级的 optimizer_switch 选项,关闭某些导致效果反而更差的特性。   、哪怕是基于索引的条件过滤,如果优化器意识到总共需要扫描的数据量超过30%时,就会直接改变执行计划为全表扫描,不再使用索引。   、多表JOIN时,要把过滤性最大的表选为驱动表。此外,如果JOIN之后有排序,排序字段一定要属于驱动表,才能利用驱动表上的索引完成排序。   、绝大多数情况下,排序的大家通常要来的更高,因此如果看到执行计划中有 Using filesort,优先创建排序索引吧。   、利用 pt-query-digest 定期分析slow query log,并结合 Box Anemometer 构建slow query log分析及优化系统。   :系列 — EXPLAIN结果中哪些信息要引起关注。   备注:若无特别说明,以上规范建议适用于及之前的版本。及之后的版本可能会有些变化,个别规范建议需要相应调整。   若为了节省磁盘空间,则建议选择latin1,在一些合适的吵顺<慕ㄒ橥猓褂衅渌父鲆悖?,= ? AND a = ?; -- 只能用到 部分,= ?; -- 只能用到 部分,注意BETWEEN和IN的区别。

上一篇:没有了
下一篇:加百利在线娱乐是什么平台?
相关教程