本文转自网络,原文地址:http://www.2cto.com/database/201403/285170.html
-------------------------------
1.库名、表名、字段名必须使用小写字母,并采用下划线分割。
a)MySQL有配置参数lower_case_table_names,不可动态更改,linux系统默认为 0,即库表名以实际情况存储,大小写敏感。如果是1,以小写存储,大小写不敏感。如果是2,以实际情况存储,但以小写比较。
b)如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱。
c)字段名显式区分大小写,但实际使用不区分,即不可以建立两个名字一样但大小写不一样的字段。
d)为了统一规范, 库名、表名、字段名使用小写字母。
2.库名、表名、字段名禁止超过32个字符。
库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符。
3.使用INNODB存储引擎。
INNODB引擎是MySQL5.5版本以后的默认引擘,支持事务、行级锁,有更好的数据恢复能力、更好的并发性能,同时对多核、大内存、SSD等硬件支持更好,支持数据热备份等,因此INNODB相比MyISAM有明显优势。
4.库名、表名、字段名禁止使用MySQL保留字。
当库名、表名、字段名等属性含有保留字时,SQL语句必须用反引号引用属性名称,这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂。
5.禁止使用分区表。
分区表对分区键有严格要求;分区表在表变大后,执行DDL、SHARDING、单表恢复等都变得更加困难。因此禁止使用分区表,并建议业务端手动SHARDING。
6.建议使用UNSIGNED存储非负数值。
同样的字节数,非负存储的数值范围更大。如TINYINT有符号为 -128-127,无符号为0-255。
7.建议使用INT UNSIGNED存储IPV4。
UNSINGED INT存储IP地址占用4字节,CHAR(15)则占用15字节。另外,计算机处理整数类型比字符串类型快。使用INT UNSIGNED而不是CHAR(15)来存储IPV4地址,通过MySQL函数inet_ntoa和inet_aton来进行转化。IPv6地址目前没有转化函数,需要使用DECIMAL或两个BIGINT来存储。
例如:
SELECT INET_ATON('209.207.224.40'); 3520061480
SELECT INET_NTOA(3520061480); 209.207.224.40
8.强烈建议使用TINYINT来代替ENUM类型。
ENUM类型在需要修改或增加枚举值时,需要在线DDL,成本较大;ENUM列值如果含有数字类型,可能会引起默认值混淆。
9.使用VARBINARY存储大小写敏感的变长字符串或二进制内容。
VARBINARY默认区分大小写,没有字符集概念,速度快。
10.INT类型固定占用4字节存储,例如INT(4)仅代表显示字符宽度为4位,不代表存储长度。
数值类型括号后面的数字只是表示宽度而跟存储范围没有关系,比如INT(3)默认显示3位,空格补齐,超出时正常显示,python、java客户端等不具备这个功能。
11.区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。
DATETIME和TIMESTAMP都是精确到秒,优先选择TIMESTAMP,因为TIMESTAMP只有4个字节,而DATETIME8个字节。同时TIMESTAMP具有自动赋值以及自动更新的特性。注意:在5.5和之前的版本中,如果一个表中有多个timestamp列,那么最多只能有一列能具有自动更新功能。
如何使用TIMESTAMP的自动赋值属性?
a)自动初始化,并自动更新: column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
b)只是自动初始化: column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
c)自动更新,初始化的值为0: column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
d)初始化的值为0: column1 TIMESTAMP DEFAULT 0
12.所有字段均定义为NOT NULL。
a)对表的每一行,每个为NULL的列都需要额外的空间来标识。
b)B树索引时不会存储NULL值,所以如果索引字段可以为NULL,索引效率会下降。
c)建议用0、特殊值或空串代替NULL值。
13.将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据。
有利于有效利用缓存,防止读入无用的冷数据,较少磁盘IO,同时保证热数据常驻内存提高缓存命中率。
14.禁止在数据库中存储明文密码。
采用加密字符串存储密码,并保证密码不可解密,同时采用随机字符串加盐保证密码安全。防止数据库数据被公司内部人员或黑客获取后,采用字典攻击等方式暴力破解用户密码。
15.表必须有主键,推荐使用UNSIGNED自增列作为主键。
表没有主键,INNODB会默认设置隐藏的主键列;没有主键的表在定位数据行的时候非常困难,也会降低基于行复制的效率。
16.禁止冗余索引。
索引是双刃剑,会增加维护负担,增大IO压力。(a,b,c)、(a,b),后者为冗余索引。可以利用前缀索引来达到加速目的,减轻维护负担。
17.禁止重复索引。
primary key a;uniq index a;重复索引增加维护负担、占用磁盘空间,同时没有任何益处。
18.不在低基数列上建立索引,例如“性别”。
大部分场景下,低基数列上建立索引的精确查找,相对于不建立索引的全表扫描没有任何优势,而且增大了IO负担。
19.合理使用覆盖索引减少IO,避免排序。
覆盖索引能从索引中获取需要的所有字段,从而避免回表进行二次查找,节省IO。INNODB存储引擎中, secondary index(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。如果用户需要查询secondary index中所不包含的数据列,则需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据,因此效率较高。主键查询是天然的覆盖索引。例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid 不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。
20.用IN代替OR。SQL语句中IN包含的值不应过多,应少于1000个。
IN是范围查找,MySQL内部会对IN的列表值进行排序后查找,比OR效率更高。
21.表字符集使用UTF8,必要时可申请使用UTF8MB4字符集。
a)UTF8字符集存储汉字占用3个字节,存储英文字符占用一个字节。
b)UTF8统一而且通用,不会出现转码出现乱码风险。
c)如果遇到EMOJ等表情符号的存储需求,可申请使用UTF8MB4字符集。
22.用UNION ALL代替UNION。
UNION ALL不需要对结果集再进行排序。
23.禁止使用order by rand()。
order by rand()会为表增加一个伪列,然后用rand()函数为每一行数据计算出rand()值,然后基于该行排序, 这通常都会生成磁盘上的临时表,因此效率非常低。建议先使用rand()函数获得随机的主键值,然后通过主键获取数据。
24.建议使用合理的分页方式以提高分页效率。
第一种分页写法:
select *
from t
where thread_id = 771025
and deleted = 0
order by gmt_create asc limit 0, 15;
select * from t
where thread_id = 771025
and deleted = 0
order by gmt_create asc limit 0, 15;
原理:一次性根据过滤条件取出所有字段进行排序返回。
数据访问开销=索引IO+索引全部记录结果对应的表数据IO
缺点:该种写法越翻到后面执行效率越差,时间越长,尤其表数据量很大的时候。
适用场景:当中间结果集很小(10000行以下)或者查询条件复杂(指涉及多个不同查询字段或者多表连接)时适用。
第二种分页写法:
select t.* from (
select id from t
where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15) a, t
where a.id = t.id;
前提:假设t表主键是id列,且有覆盖索引secondary key:(thread_id, deleted, gmt_create)
原理:先根据过滤条件利用覆盖索引取出主键id进行排序,再进行join操作取出其他字段。
数据访问开销=索引IO+索引分页后结果(例子中是15行)对应的表数据IO。
优点:每次翻页消耗的资源和时间都基本相同,就像翻第一页一样。
适用场景:当查询和排序字段(即where子句和order by子句涉及的字段)有对应覆盖索引时,且中间结果集很大的情况时适用。
25.SELECT只获取必要的字段,禁止使用SELECT *。
减少网络带宽消耗;
能有效利用覆盖索引;
表结构变更对程序基本无影响。
26.SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数。
语句级复制场景下,引起主从数据不一致;不确定值的函数,产生的SQL语句无法利用QUERY CACHE。
27.采用合适的分库分表策略。例如千库十表、十库百表等。
采用合适的分库分表策略,有利于业务发展后期快速对数据库进行水平拆分,同时分库可以有效利用MySQL的多线程复制特性。
28.减少与数据库交互次数,尽量采用批量SQL语句。
使用下面的语句来减少和db的交互次数:
a)INSERT ... ON DUPLICATE KEY UPDATE
b)REPLACE INTO
c)INSERT IGNORE
d)INSERT INTO VALUES()
29.拆分复杂SQL为多个小SQL,避免大事务。
简单的SQL容易使用到MySQL的QUERY CACHE;减少锁表时间特别是MyISAM;可以使用多核CPU。
30.对同一个表的多次alter操作必须合并为一次操作。
mysql对表的修改绝大部分操作都需要锁表并重建表,而锁表则会对线上业务造成影响。为减少这种影响,必须把对表的多次alter操作合并为一次操作。例如,要给表t增加一个字段b,同时给已有的字段aa建立索引, 通常的做法分为两步:
alter table t add column b varchar(10);
然后增加索引:
alter table t add index idx_aa(aa);
正确的做法是:
alter table t add column b varchar(10),add index idx_aa(aa);
31.避免使用存储过程、触发器、视图、自定义函数等。
这些高级特性有性能问题,以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、 SHARDING等变得更加困难。
32.禁止有super权限的应用程序账号存在。
安全第一。super权限会导致read only失效,导致较多诡异问题而且很难追踪。
33.提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE),便于DBA进行审核和优化。
并不只是SELECT语句需要用到索引。UPDATE、DELETE都需要先定位到数据才能执行变更。因此需要业务提供所有的SQL语句便于DBA审核。
34.不要在MySQL数据库中存放业务逻辑。
数据库是有状态的服务,变更复杂而且速度慢,如果把业务逻辑放到数据库中,将会限制业务的快速发展。建议把业务逻辑提前,放到前端或中间逻辑层,而把数据库作为存储层,实现逻辑与存储的分离。
相关推荐
### 某行MySQL数据库开发规范 #### 一、规范的范围和目的 **1.1 规范的范围** 本规范主要涵盖MySQL数据库的SQL语句开发与性能优化、数据库用户及权限管理、数据库日常维护等内容。适用于招联消费金融有限公司内部...
MySQL开发规范总结是针对数据库设计和管理的一套标准,旨在提升效率、标准化开发流程并方便数据库的统一管理。本规范适用于平安科技所有涉及MySQL的开发人员、DBA和运营人员。 1. 引言 - 背景与目的:随着业务的...
MySQL开发规范旨在优化数据库设计,提高查询性能,避免因不当的SQL使用对业务造成严重影响。以下是一些关键的规范要点: 1. **避免大事务**:大事务可能导致长时间锁定资源,影响其他并发操作。应将大事务拆分成小...
总结起来,"mysql自动生成实体类"是一种实用的开发辅助工具,它结合了MySQL数据库和Java反射技术,帮助开发者快速生成与数据库表结构相对应的实体类,便于在ORM框架中使用,提高了开发效率和代码的可维护性。
在IT行业中,数据库管理和开发是不可或缺的部分,尤其是MySQL与Java的结合使用,为开发者提供了高效、可靠的后端数据存储解决方案。本指南将深入探讨这两个关键领域的结合,帮助Java开发者更好地理解和应用MySQL...
【MySQL数据库设计与操作】 本话题涉及的是一个网络玩具销售系统的数据库设计与操作,主要涵盖了数据库的创建、表的结构定义、数据插入、查询及更新等基本操作。以下是相关知识点的详细说明: 1. **数据库创建**:...
在Android开发中,连接到MySQL数据库通常涉及到与服务器端的交互,因为Android设备本身并不支持直接连接MySQL。这里,我们通常会使用PHP作为中间层,处理数据库操作,并通过HTTP或HTTPS协议与Android客户端进行通信...
本话题聚焦于将HSQL数据库的数据迁移到MySQL的过程,这是一个常见的需求,特别是在开发、测试和生产环境之间进行数据同步时。以下是对这一过程的详细说明: 1. **HSQL数据库**:HSQLDB(HyperSQL Database)是一款...
根据提供的文件信息,我们可以看到文档《MySQL Internals Manual.pdf》涵盖了MySQL内部机制、代码结构、...通过对这些知识点的掌握,开发人员能够更好地理解MySQL的内部工作原理,从而进行更深入的定制开发和性能优化。
MySQL 5.7 版本驱动包是针对Java开发者用于连接MySQL数据库的重要组件,它遵循JDBC(Java Database Connectivity)规范,使得Java程序能够通过标准API与MySQL数据库进行交互。在Java应用程序中,我们通常使用MySQL ...
总的来说,MySQL驱动包是Java开发者连接MySQL数据库不可或缺的工具,它简化了数据库操作,提高了开发效率,并提供了稳定可靠的数据库连接。通过不断升级和优化,MySQL Connector/J为Java应用程序提供了强大且全面的...
【JSP+JAVABEAN+MYSQL网络硬盘】是一个典型的Web应用开发案例,适合初学者理解Web开发的基本流程和核心技术。在这个项目中,JSP(JavaServer Pages)用于前端展示,JAVABEAN作为业务逻辑处理,而MySQL则是后台数据库...
MySQL的JDBC驱动分为四种类型:Type 1、Type 2、Type 3和Type 4,其中MySQL 5.5版本的驱动主要是Type 4,即纯Java的网络驱动,由MySQL公司自己开发,无需任何本地库,这使得它在跨平台环境下的兼容性极佳。...
MySQL规范是数据库管理和开发中的重要指导原则,它涵盖了SQL语句编写、数据库设计、性能优化等多个方面。在学习、面试和实践中,理解并遵循这些规范能够显著提高代码质量,提升数据库系统的稳定性和效率。以下是对...
MySQL 5.7 驱动 jar 包是Java应用程序连接到MySQL数据库的关键组件,它实现了JDBC(Java Database Connectivity)规范,使得开发者能够在Java环境中与MySQL数据库进行交互。本篇将详细介绍如何获取和使用MySQL 5.7...
MySQL Connector/J是MySQL数据库系统与Java应用程序之间的重要桥梁,它是一个实现了JDBC(Java Database Connectivity)规范的驱动程序,使得Java开发者能够通过编写Java代码来访问和操作MySQL数据库。"mysql-...
MySQL JDBC驱动,也称为MySQL Connector/J,是MySQL官方提供的实现JDBC规范的驱动程序。这个驱动包包含了一系列的Java类和接口,实现了JDBC API,使得Java应用程序能够通过网络与MySQL服务器进行通信。在本例中,...
10. **软件工程实践**:源码可能包含了模块化设计、注释、文档等方面的信息,这些都是软件开发中的良好实践,有助于提高代码可读性和维护性。 通过深入研究这个源码,不仅能够掌握易语言的编程技巧,还能学习到...
`zys.mysql-connector-java-5.1.34-bin.jar`,包含了MySQL Connector/J,这是MySQL官方提供的Java数据库连接器,它实现了Java Database Connectivity (JDBC) API,允许Java程序通过网络与MySQL服务器进行通信。...
MySQL Connector/J遵循Java Database Connectivity (JDBC) API规范,它是Java平台的标准接口,用于访问各种类型的数据库。JDBC驱动程序分为四种类型:Type 1、Type 2、Type 3和Type 4。MySQL Connector/J属于Type 4...