MySQL开发规范与使用技巧总结

置顶 zhoupenghui168

已于 2023-04-03 11:45:55 修改

阅读量721

点赞数 1

分类专栏：数据库 # MySQL 文章标签： mysql 数据库 Powered by 金山文档

于 2023-03-28 16:14:08 首次发布

本文链接：https://blog.csdn.net/zhoupenghui168/article/details/129817514

版权

数据库同时被 2 个专栏收录

34 篇文章 3 订阅

订阅专栏

MySQL

28 篇文章 1 订阅

订阅专栏

文章详细列举了MySQL数据库在命名规范、存储引擎选择、字段类型优化、索引使用、性能提升等方面的一系列最佳实践，包括使用小写字母和下划线的命名规则，推荐使用INNODB引擎，避免使用分区表，以及如何有效利用索引和优化SQL查询以提高数据库性能。

摘要由CSDN通过智能技术生成

一.命名规范

1.库名、表名、字段名必须使用小写字母，并采用下划线分割

1.1 MySQL有配置参数lower_case_table_names，不可动态更改

在linux系统中的默认为 0，表示：库表名以实际情况存储，大小写敏感;
如果是1，以小写存储，大小写不敏感
如果是2，以实际情况存储，但以小写比较

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

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

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

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来进行转化，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2，这会为你带来查询上的优势，在PHP中，也有这样的函数 ip2long() 和 long2ip()。IPv6地址目前没有转化函数，需要使用DECIMAL或两个BIGINT来存储

例如:

SELECT INET_ATON('209.207.224.40'); //3520061480
SELECT INET_NTOA(3520061480); //209.207.224.40
UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id

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个字节，而DATETIME有8个字节，同时TIMESTAMP具有自动赋值以及⾃自动更新的特性。
注意：
在5.5和之前的版本中，如果一个表中有多个timestamp列，那么最多只能有一列能具有自动更新功能

如何使用TIMESTAMP的自动赋值属性?

11.1自动初始化，而且自动更新

column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

11.2只是自动初始化

 column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

11.3自动更新，初始化的值为0

 column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

11.4初始化的值为0

column1 TIMESTAMP DEFAULT 0

12.所有字段均定义为NOT NULL

12.1对表的每一行，每个为NULL的列都需要额外的空间来标识

12.2 B树索引时不会存储NULL值，所以如果索引字段可以为NULL，索引效率会下降

12.3建议用0、特殊值或空串代替NULL值

二.MySQL使用技巧

1.将大字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据

有利于有效利用缓存，防⽌止读入无用的冷数据，较少磁盘IO，同时保证热数据常驻内存提⾼高缓存命中率

2.禁止在数据库中存储明文密码

采用加密字符串存储密码，并保证密码不可解密，同时采用随机字符串加盐保证密码安全

3.表必须有主键，推荐使用UNSIGNED自增列作为主键

表没有主键，INNODB会默认设置隐藏的主键列；没有主键的表在定位数据行的时候非常困难，也会降低基于行复制的效率

4.禁止冗余索引

索引是双刃剑，会增加维护负担，增⼤大IO压力,(a,b,c)、(a,b)，后者为冗余索引。可以利用前缀索引来达到加速目的，减轻维护负担。

5.禁止重复索引

primary key a;uniq index a;重复索引增加维护负担、占用磁盘空间，同时没有任何益处

6.不在低基数列上建立索引，例如“性别”

大部分场景下，低基数列上建立索引的精确查找，相对于不建立索引的全表扫描没有任何优势，而且增大了IO负担

7.合理使用覆盖索引减少IO，避免排序

覆盖索引能从索引中获取需要的所有字段，从⽽而避免回表进行二次查找，节省IO

INNODB存储引擎中，secondary index(非主键索引，又称为辅助索引、二级索引)没有直接存储行地址，而是存储主键值.

如果用户需要查询secondary index中所不包含的数据列，则需要先通过secondary index查找到主键值，然后再通过主键查询到其他数据列，因此需要查询两次。覆盖索引则可以在⼀一个索引中获取所有需要的数据，因此效率较高.

例如SELECT email，uid FROM user_email WHERE uid=xx，如果uid不是主键，适当时候可以将索引添加为index(uid，email)，以获得性能提升

8.用IN代替OR,SQL语句中IN包含的值不应过多，应少于1000个

IN是范围查找，MySQL内部会对IN的列表值进行排序后查找，比OR效率更高

9.表字符集使用UTF8，必要时可申请使用UTF8MB4字符集

UTF8字符集存储汉字占用3个字节，存储英文字符占用一个字节
UTF8统一而且通用，不会出现转码出现乱码风险
如果遇到EMOJ等表情符号的存储需求，可申请使用UTF8MB4字符集

10.用UNION ALL代替UNION

UNION ALL不需要对结果集再进行排序

11.禁止使用order by rand()

order by rand()会为表增加一个伪列，然后用rand()函数为每一行数据计算出rand()值，然后基于该行排序，这通常都会生成磁盘上的临时表，因此效率非常低。建议先使用rand()函数获得随机的主键值，然后通过主键获取数据

12.建议使用合理的分页方式以提高分页效率

假如有类似下面分页语句:

SELECT * FROM table ORDER BY TIME DESC LIMIT 10000，10;

这种分页方式会导致大量的io，因为MySQL使用的是提前读取策略

推荐分页方式:

SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10.
SELECT * FROM table inner JOIN (SELECT id FROM table ORDER BY TIME LIMIT 10000，10) as USING(id)

13.SELECT只获取必要的字段，禁⽌止使用SELECT *

减少网络带宽消耗
能有效利用覆盖索引
表结构变更对程序基本无影响

14.SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数

语句级复制场景下，引起主从数据不一致；不确定值的函数，产⽣生的SQL语句无法利用QUERY CACHE

15.采用合适的分库分表策略,例如千库十表、十库百表等

采用合适的分库分表策略，有利于业务发展后期快速对数据库进行水平拆分，同时分库可以有效利⽤用MySQL的多线程复制特性

16.减少与数据库交互次数，尽量采用批量SQL语句

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

INSERT ... ON DUPLICATE KEY UPDATE
REPLACE INTO
INSERT IGNORE
INSERT INTO VALUES()

17.拆分复杂SQL为多个小SQL，避免大事务

简单的SQL容易使⽤用到MySQL的QUERY CACHE；减少锁表时间特别是MyISAM；可以使用多核 CPU

18.对同一个表的多次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);

19.避免使用存储过程、触发器、视图、自定义函数等

这些高级特性有性能问题，以及未知BUG较多,业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、SHARDING等变得更加困难

20.禁止有super权限的应用程序账号存在

安全第一,super权限会导致read only失效，导致较多诡异问题而且很难追踪

21.不要在MySQL数据库中存放业务逻辑

数据库是有状态的服务，变更复杂而且速度慢，如果把业务逻辑放到数据库中，将会限制业务的快速发展,建议把业务逻辑提前，放到前端或中间逻辑层，而把数据库作为存储层，实现逻辑与存储的分离

22.当只要一行数据时使用LIMIT 1

当查询表的有些时候，已经知道结果只会有一条结果，单因为可能需要去fetch游标，也许会去检查返回的记录数。在这种情况下，加上LIMIT 1 可以增加性能。这样一样， MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。
下面的示例，只是为了找一下是否有“中国”的用户，很明显，后面的会比前面的更有效率。（请注意，第一条中是Select *，第二条是Select 1）

// 没有效率的： $r = mysql_query("SELECT * FROM user WHERE country = 'China'"); if (mysql_num_rows($r) > 0) { // ... } 
// 有效率的： $r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1"); if (mysql_num_rows($r) > 0) { // ... }

23.为搜索字段建索引

索引并不一定就是给主键或是唯一的字段。如果在表中，有某个字段总要会经常用来做搜索，那么，请为其建立索引

24.永远为两张表设置一个ID

为数据库里的每张表都设置一个ID作为其主键，而最好的是一个INT型（推荐使用UNSIGNED），并设置上自动增长的AUTO INCREMENT标志。就算是 users 表有一个主键叫 “email”的字段，也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外，在程序中，应该使用表的ID来构造数据结构。而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……