一、表设计

1、库名、表名、字段名****必须使用小写字母，“_”分割。

a) MySQL 有配置参数 lower_case_table_names，不可动态更改，linux 系统默认为 0，即库表名以实际情况存储，大小写敏感。如果是 1，以小写存储，大小写不敏感。如果是 2，以实际情况存储，但以小写比较。

b) 如果大小写混合用，可能存在 abc,Abc,ABC 等多个表共存，容易导致混乱。

c) 字段名显示区分大小写，但实际使用不区分，即不可以建立两个名字一样但大小写不一样的字段。

d) 为了统一规范， 库名、表名、字段名使用小写字母。

2、库名、表名、字段名必须不超过 12 个字符。

库名、表名、字段名支持最多 64 个字符，但为了统一规范、易于辨识以及减少传输量，必须不超过

12字符。

**3、**库名、表名、字段名见名知意,建议使用名词而不是动词。

a) 例如用户评论可用表名 usercomment 或者 comment。

b) 库表是一种客观存在的事物，一种对象，所以建议使用名词。

**4、**必须使用 InnoDB 存储引擎。

a) 5.5 以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存， ssd 等硬件支持更好。

b) 具体比较可见附件的官方白皮书。

5**、存储精确浮点数****必须使用 DECIMAL 替代 FLOAT 和 DOUBLE**。

a) mysql 中的数值类型（不包括整型）：

浮点数： float （单精度） ， double 或 real （双精度）

定点数： decimal 或 numeric

单精度浮点数的有效数字二进制是 24 位，按十进制来说，是 8 位；双精度浮点数的有效数字二进制是 53 位，按十进制来说，是 16 位

一个实数的有效数字超过 8 位，用单精度浮点数来表示的话，就会产生误差！同样，如果一个实数的有效数字超过 16 位，用双精度浮点数来表示，也会产生误差

b) 标准的计算机浮点数，在内部是用二进制表示的，但在将一个十进制数转换为二进制浮点数时，也会造成误差，原因是不是所有的数都能转换成有限长度的二进制数。

即一个二进制可以准确转换成十进制，但一个带小数的十进制不一定能够准确地用二进制来表示。

实例：

drop table if exists t;

create table t(value float(10,2));

insert into t values(131072.67),(131072.68);

select value from t;

+-----------+

| value |

+-----------+

| 131072.67 |

| 131072.69 |

6**、建议使用 UNSIGNED 存储非负数值。**

同样的字节数，存储的数值范围更大。如 smallint 有符号为–32768 -32767，无符号为 0 - 65535

7**、** 如何使用INT UNSIGNED存储ip？

使用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**、整形定义中不添加长度，比如使用 INT，而不是 INT(4)。**

注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如 INT(3)默认显示 3 位，空格补齐，超出时正常显示，python、java 客户端等不具备这个功能。

9**、使用短数据类型，比如取值范围为 0-80 时，使用 SMALLINT UNSIGNED。**

10**、不建议使用 ENUM、SET 类型和TINYINT，使用 SMALLINT来代替。**

a) ENUM，有三个问题：添加新的值要做 DDL，默认值问题(将一个非法值插入 ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值)，索引值问题（插入数字实际是插入索引对应的值）

b) 由于mysql没有boolean类型，所以会用到TINYINT[1]类型来表示，在mysql中

boolean=tinyint[1],在别的数据库语言中也会被解读成是true/false,为了避免不必要的麻烦，在我们系

统中一律不使用tinyint,使用smallint来代替

（详解可以参阅mysql的tinyint(1).docx）

实例：

drop table if exists t;

create table t(sex enum('0','1'));

insert into t values(1);

insert into t values('3');

select * from t;

+------+

| sex |

+------+

| 0 |

| |

+------+

2 rows in set (0.00 sec)

11**、尽可能不使用 TEXT、BLOB 类型。**

a) 索引排序问题，只能使用 max_sort_length 的长度或者手工指定 ORDER BY SUBSTRING(column, length)的长度来排序

b) Memory 引擘不支持 text,blob 类型，会在磁盘上生成临时表

c) 可能浪费更多的空间

d) 可能无法使用 adaptive hash index

e) 导致使用 where 没有索引的语句变慢

12**、VARCHAR(N)，N 表示的是字符数不是字节数，比如 VARCHAR(255)，可以最大可存**

储255 个汉字，需要根据实际的宽度来选择 N。

13、VARCHAR(N)，N 尽可能小，因为 MySQL 一个表中所有的 VARCHAR 字段最大长度是 65535 个字节，进行排序和创建临时表一类的内存操作时，会使用 N 的长度申请内存。

VARCHAR 中会产生额外存储吗？

VARCHAR(M)，如果 M<256 时会使用一个字节来存储长度，如果 M>=256 则使用两个字节来存储

长度。

14**、 如果表里有字段需要存储表情使用表字符集选择 Utf8mb4**

a) Utf8mb4兼容utf8,且比utf8能表示更多的字符，例 存储emoji表情，utf8是无法存储的，

Utf8mb4则可以做到

b) 统一，不会有转换产生乱码风险

c) 其他地区的用户（美国、印度、台湾）无需安装简体中文支持，就能正常看您的文字，并且不会出现乱码

d) mysql 5.5.3版本以后支持Utf8mb4

15**、存储年使用 YEAR 类型。**

16**、存储日期使用 DATE 类型。**

17**、建议字段设置不允空。**

18**、建议字段默认值不要为NUll。**

a）如果 null 字段被索引，需要额外的 1 字节

b）使索引，索引统计，值的比较变得更复杂

c）可用 0，''代替

d） 如果是索引字段，一定要定义为 not null

19**、所有表、字段均应用 comment 列属性来描述此表、字段所代表的真正含义，如枚举值则建议将该字段中使用的内容都定义出来。**

20**、如无说明，表中的第一个id字段一定是主键 bigint unsigned** 且为自动增长，禁止在非事务内上下文作为条件进行数据传递。禁止使用varchar类型作为主键语句设计。

21**、如无说明，表必须包含create_time和modify_time字段**

create_time TIMESTAMP(3) not null DEFAULT CURRENT_TIMESTAMP(3);

modify_time TIMESTAMP(3) not null DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3);

create_time建立索引

modify_time建立索引

22**、如无说明，表必须包含is_del，用来标示数据是否被删除，原则上数据库数据不允许物理删除。**

is_del tinyint not null default 0 comment'是否删除:0否，1是'

23**、禁止在数据库中使用** VARBINARY**、BLOB** 存储图片、文件等。

采用分布式文件系统更高效

24**、表结构变更需要通知 DBA 审核。**

二、索引

1**、 为什么 MySQL 的性能依赖于索引？**

MySQL 的查询速度依赖良好的索引设计，因此索引对于高性能至关重要。合理的索引会加快查询速度（包括UPDATE和DELETE的速度，MySQL会将包含该行的page加载到内存中，然后进行UPDATE或者 DELETE 操作），不合理的索引会降低速度。

MySQL 索引查找类似于新华字典的拼音和部首查找，当拼音和部首索引不存在时，只能通过一页一页的 翻页来查找。当 MySQL 查询不能使用索引时，MySQL 会进行全表扫描，会消耗大量的 IO

2**、非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名。**

3**、唯一索引须按照“uniq_字段名称_字段名称[_字段名]”必进行命名。**

4**、索引名称必须使用小写。**

5**、索引中的字段数建议不超过 5 个。**

InnoDB 的 secondary index 使用 b+tree 来存储，因此在 UPDATE、DELETE、INSERT 的时候需要对 b+tree 进行调整，过多的索引会减慢更新的速度。

6**、单张表的索引数量控制在 5 个以内**。

7**、唯一键由 3 个以下字段组成，并且字段都是整形时，使用唯一键作为主键。**

8**、没有唯一键或者唯一键不符合 5 中的条件时，使用自增（或者通过发号器获取）id 作为**

主键。

9**、唯一键不和主键重复**。

10**、索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面（复合索引的最左原则）。**

11**、ORDER BY，GROUP BY，DISTINCT 的字段需要添加在索引的后面。**

12**、使用 EXPLAIN 判断 SQL 语句是否合理使用索引，尽量避免 extra 列出现：Using File Sort，Using Temporary。**

EXPLAIN 语句（在 MySQL 客户端中执行）可以获得 MySQL 如何执行 SELECT 语句的信息。通过对 SELECT 语句执行 EXPLAIN，可以知晓 MySQL 执 行该 SELECT 语句时是否使用了索引、全表扫描、临时表、排序等信息。尽量避免 MySQL 进行全表扫描、使用临时表、排序等。详见官方文档。

13**、UPDATE、DELETE 语句需要根据 WHERE 条件添加索引。**

14**、不建议使用%前缀模糊查询，例如 LIKE “%weibo”。**

会导致全表扫描

15****合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分

索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效

**16 **经常join其他表，在连接字段上应该建立索引 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引 复合索引的几个字段是否经常同时以AND方式出现在Where子句中,单字段查询是否极少甚至没有,如果是，则可以建立复合索引；否则考虑单字段索引

17**、SQL 变更需要确认索引是否需要变更并通知 DBA。**

三、 SQL 语句

1**、使用 prepared statement，可以提供性能并且避免 SQL 注入。**

2**、SQL 语句中 IN 包含的值不应过多。**

3**、UPDATE语句不使用 LIMIT**。

a) 可能导致主从数据不一致

b) 会记录到错误日志，导致日志占用大量空间

4**、WHERE 条件中必须使用合适的类型，避免 MySQL 进行隐式类型转化。**

因为 MySQL 进行隐式类型转化之后，可能会将索引字段类型转化成=号右边值的类型，导致使用不

到索引，原因和避免在索引字段中使用函数是类似的。（字符型应该加引号，数值型不加引号）

例 select * from user where mobile=159000000,mobile字段类型是字符型，值却给了数值型

5**、SELECT、INSERT 语句必须显式的指明字段名称，不使用 SELECT 。*

增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发

生改变时，前段也需要更新。

6**、WHERE 条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。**

7**、避免在 SQL 语句进行数学运算或者函数运算，容易将业务逻辑和 DB 耦合在一起。**

8**、INSERT 语句使用 batch 提交（INSERT INTO table VALUES(),(),()……），values 的个数不应过多**。

9**、多表连接时，尽量小表驱动大表，即小表 left join 大表。**

10**、使用合理的 SQL 语句减少与数据库的交互次数。**

使用下面的语句来减少和 db 的交互次数：

INSERT ... ON DUPLICATE KEY UPDATE

REPLACE INTO

INSERT IGNORE

INSERT INTO VALUES(),(),()

UPDATE … WHERE ID IN(10,20,50,…)

11**、不使用 ORDER BY RAND()，使用其他方法替换。**

因为 ORDER BY rand()会将数据从磁盘中读取，进行排序，会消耗大量的 IO 和 CPU，可以在程序

中获取一个 rand 值，然后通过在从数据库中获取对应的值

12**、 InnoDB 存储引擎为什么避免使用 COUNT(*)?**

InnoDB 表避免使用 COUNT(*)操作，计数统计实时要求较强可以使用 memcache 或者 redis，非实时统计可以使用

**13 **不使用负向查询，如not in/like 无法使用索引，导致全表扫描 全表扫描导致buffer pool利用率降低

14**、使用EXPLAIN诊断，避免生成临时表**

EXPLAIN****语句（在MySQL客户端中执行）可以获得MySQL如何执行SELECT语句的信息。通过对SELECT语句执行EXPLAIN，可以知晓MySQL执行该SELECT语句时是否使用了索引、全表扫描、临时表、排序等信息。尽量避免MySQL进行全表扫描、使用临时表、排序等。详见官方文档。

15**、禁止单条SQL语句同时更新多个表**

四、分表

1、每张表数据量建议控制在 2000w 以下。

2、推荐使用 求余（或者类似的算术算法）进行分表，表名后缀使用数字，数字必须从 0 开始并等宽，比如分 100 张表，后缀从 00-99。

3、使用时间分表，表名后缀必须使用特定格式，比如按日散表 user_20110209、按月分表

user_201102。

五、其他

1、批量导入、导出数据需要 DBA 进行审查，并在执行过程中观察服务。

2、批量更新数据，如 update,delete 操作，需要 DBA 进行审查，并在执行过程中观察服

务。

3、产品出现非数据库平台运维导致的问题和故障时，如前端被抓站，请及时通知 DBA，便于维护服务稳定。

4、业务部门程序出现 bug 等影响数据库服务的问题,请及时通知 DBA，便于维护服务稳定。

5、业务部门推广活动，请提前通知 DBA 进行服务和访问评估。

6、如果出现业务部门人为误操作导致数据丢失，需要恢复数据，请在第一时间通知 DBA，并提供准确时间，误操作语句等重要线索。

7、SQL 变更需要确认索引是否需要变更并通知 DBA

8、表结构变更需要通知 DBA 审核