`
luowei925
  • 浏览: 8190 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

【转载】MySQL开发规范

阅读更多

1、默认使用InnoDB引擎
【老叶观点】已多次呼吁过了,InnoDB适用于几乎99%的MySQL应用场景,而且在MySQL 5.7的系统表都改成InnoDB了,还有什么理由再死守MyISAM呢。


此外,频繁读写的InnoDB表,一定要使用具有自增/顺序特征的整型作为显式主键。

当然了,也不是说MyISAM就一无是处,比如老叶之前就把MyISAM用于临时导数据数据(把数据导入MyISAM,一番处理后再入到InnoDB表)、或者一些特殊的数据统计类场景用MyISAM(大数据量下MyISAM全表顺序读取比InnoDB有明显优势)可能比较合适。前提是,你得非常清楚MyISAM引擎的优势在哪里。

【参考】:[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键。

2、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。

【参考】:[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率。

3、是否使用表分区(partition)
【老叶观点】在一些使用表分区后明显可以提升性能或者运维便利性的场景下,还是建议使用表分区。

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

当然了,使用表分区可能不利于物理扩展,比如大数据量下想要做哈希水平拆分,这个就见仁见智了,如果你的业务场景下使用表分区更有好处,就放心大胆的用吧。该进行拆分就用拆分方案,不要继续抱着表分区方案不放。

参考:迁移Zabbix数据库到TokuDB。

4、是否使用存储过程、触发器
【老叶观点】在一些合适的场景下,用存储过程、触发器也完全没问题。

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

有同行认为存储过程和触发器的应用可能会导致发生锁等待、死锁时排查问题上的困惑。嗯,这个是有这个可能性,不过如果真发生时,根据当时现场记录的SQL反查对应的存储过程或触发器,应该也不是难事,只不过要求DBA对线上业务环境更要了然于胸了。

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

5、选择合适的类型
【老叶观点】除了常见的建议外,还有其他几个要点:

5.1、用INT UNSIGNED存储IPV4地址,用INET_ATON()、INET_NTOA()进行转换,基本上没必要使用CHAR(15)来存储。

5.2、枚举类型可以使用ENUM,ENUM的内部存储机制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR),性能一点都不差,记住千万别用CHAR/VARCHAR 来存储枚举数据。

5.3、还个早前一直在传播的“常识性误导”,建议用TIMESTAMP取代DATETIME。其实从5.6开始,建议优先选择DATETIME存储日期时间,因为它的可用范围比TIMESTAMP更大,物理存储上仅比TIMESTAMP多1个字节,整体性能上的损失并不大。

5.4、所有字段定义中,默认都加上NOT NULL约束,除非必须为NULL(但我也想不出来什么场景下必须要在数据库中存储NULL值,可以用0来表示)。在对该字段进行COUNT()统计时,统计结果更准确(值为NULL的不会被COUNT统计进去),或者执行 WHERE column IS NULL 检索时,也可以快速返回结果。

5.5、杜绝直接 SELECT * 读取全部字段,当表中存在 TEXT/BLOB 大列的时候就会是灾难了。可能本来不需要读取这些列,但因为偷懒写成 SELECT * 导致内存buffer pool被这些“垃圾”数据把真正需要缓冲起来的热点数据给洗出去了。
相应地,在写INSERT时,也要写上相对应的字段列表。
要求在SQL中写清楚每个字段的重要意义还在于,当业务需要表DDL发生更新后,如果不写清楚字段,可能会导致旧业务代码不可用,这个就折腾大发了。

6、关于索引
【老叶观点】除了常见的建议外,还有几个要点:

6.1、超过20个长度的字符串列,最好创建前缀索引而非整列索引(例如:ALTER TABLE t1 ADD INDEX(user(20))),可以有效提高索引利用率,不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出,一般略大于平均长度一点就可以了。

6.2、定期用 pt-duplicate-key-checker 工具检查并删除冗余的索引。比如 index idx1(a, b) 索引已经涵盖了 index idx2(a),就可以删除 idx2 索引了。

6.3、有多字段联合索引时,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则只能用到部分索引,或者可利用到ICP特性:
SELECT ... WHERE b = ? AND a = ?; -- 只能用到 (a, b) 部分

SELECT ... WHERE a IN (?, ?) AND b = ?; -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?; -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND b IN (?, ?); -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?; -- EXPLAIN显示用到 (a, b, c) 整个索引,同时有ICP

SELECT ... WHERE a = ? AND c = ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND c >= ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP

 

ICP(index condition pushdown)是MySQL 5.6的新特性,其机制会让索引的其他部分也参与过滤,减少引擎层和server层之间的数据传输和回表请求,通常情况下可大幅提升查询效率。

下面的几个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 特性)。


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

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

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

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

【参考】:[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注。

 

分享到:
评论

相关推荐

    阿里巴巴MySQL开发规范

    ### 阿里巴巴MySQL开发规范详解 #### 一、概述 阿里巴巴MySQL开发规范是一套针对MySQL数据库设计、实现及优化的最佳实践指南。这套规范旨在提高数据库应用的稳定性、性能和可维护性,同时降低潜在的风险。规范主要...

    MySQL数据库开发规范

    MySQL数据库开发规范MySQL数据库开发规范MySQL数据库开发规范MySQL数据库开发规范

    mysql开发规范 去哪网编写

    MySQL开发规范是数据库管理和开发中的重要指南,尤其对于去哪网这样的在线旅游服务平台,数据的高效、稳定和安全至关重要。本规范旨在确保开发人员和运维人员在使用MySQL时遵循最佳实践,以提升系统的整体性能、可...

    MYSQL开发规范.pdf

    开发规范对于保证数据库的性能、稳定性和可维护性至关重要。MySQL作为一款广泛使用的...以上内容涉及了MySQL开发的多个关键知识点,通过规范的制定和遵循,可以有效提高MySQL数据库的性能,保证系统的高效稳定运行。

    mysql开发规范

    mysql的开发规范文档,这里可能记录的并不是很全也算是给自己一个提醒

    解读阿里巴巴开发规范之MySQL - 知乎.pdf

    解读阿里巴巴开发规范之MySQL - 知乎

    MySQL开发规范

    # MySQL开发规范详解 本文将基于提供的MySQL开发规范文件,深入解析其中提到的关键知识点,并结合实际应用场景进行详尽说明。内容覆盖开发实用技术、MySQL开发规范、项目支持、变更管理、开发测试服务器说明以及...

    MySQL数据库开发规范方案.pdf

    MySQL数据库开发规范方案.pdf

    去哪儿MySQL开发规范-完整版

    去哪儿MySQL开发规范-完整版,MySQL DBA必须看看

    MYSQL开发规范总结

    MySQL开发规范总结是针对数据库设计和管理的一套标准,旨在提升效率、标准化开发流程并方便数据库的统一管理。本规范适用于平安科技所有涉及MySQL的开发人员、DBA和运营人员。 1. 引言 - 背景与目的:随着业务的...

    MySQL开发规范和原则大全

    MySQL开发规范和原则大全

    mysql开发规范,mysql优化指南

    MySQL开发规范和优化指南为数据库开发人员和管理员提供了一套详细的标准和建议,旨在规范化和标准化MySQL开发设计,以及指导合理使用MySQL以获得最优性能。本文档适用于MySQL 5.0至MySQL 5.6版本,并详细介绍了权限...

    去哪儿mysql开发规范.pdf

    根据给定文件的信息,我们可以详细地探讨去哪儿 MySQL 开发规范中的关键知识点,这些知识点主要集中在命名规范、基础规范、库表设计、字段设计等方面。 ### 命名规范 命名规范是任何数据库设计中非常重要的一环,...

    mysql数据库开发规范PPT讲义

    mysql数据库开发规范PPT讲义,用于培训新入职开发人员或者学校课堂讲课使用。

    MySQL开发规范,后端开发人员必备

    对于后端开发人员来说,遵循良好的MySQL开发规范至关重要,这不仅有助于提高代码的可读性和可维护性,还能提升数据库性能,确保数据安全。以下是关于MySQL开发规范的一些核心要点: 1. **设计规范**: - **数据库...

    MySQL数据库开发规范.pdf

    MySQL数据库开发规范.pdf

    MySQL设计规范.pdf

    MySQL 设计规范是指在 MySQL 数据库设计和开发过程中需要遵守的一系列规则和标准,以确保数据库的稳定性、可靠性和高效性。该规范涵盖了数据库设计、表和字段命名、字段结构、SQL 语句、性能与效率、索引优化、查询...

    MYSQL开发规范最新版pdf

    随着漫道金服业务的发展,使用MySQL数据库的系统和应用数量不断扩大,为了提高数据库效率,实现标准化开发及便于数据库的统一管理,制定本规范。 本规范适用于所有与MySQL相关的开发人员、数据库管理员与运营人员。

Global site tag (gtag.js) - Google Analytics