mysql database

MySQL数据库设计与管理规范

wdghub - April 1, 2023

以下所有规范会按照【高危】、【强制】、【建议】三个级别进行标注，遵守优先级从高到低。
这是整理网上不同来源的规范以及补充自己实际担任DBA的经验。
删除线 表示有人提出这样的建议，但我不认同。
斜体 表示我对此保留疑虑，但整体表示认同。

1 数据库设计

1.1 命名

  1. 【建议】库名、表名、字段名必须使用小写字母并采用下划线分割。
  2. 【建议】库名、表名、字段名禁止超过32个字符，须见名知意。
  3. 【建议】库名、表名、字段名支持最多64个字符，统一规范、易于辨识以及减少传输量不要超过32。
  4. 【建议】库名、表名、字段名禁止使用MySQL保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来）。
  5. 【建议】库的名称格式：业务系统名称_子系统名，同一模块使用的表名尽量使用统一前缀。
  6. 【建议】一般分库名称命名格式是库通配名_编号，编号从0开始递增，比如db_yyy_xxx_0001以时间进行分库的名称格式是“库通配名_时间”。
  7. 【建议】临时库、临时表名以temp_为前缀并以日期为后缀。
  8. 【建议】备份库、备份表名以bak_ / del_为前缀并以日期为后缀。
  9. 【建议】所有存储相同数据的列名和列类型必须一致（一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。
  10. 【强制】相关模块的表名与表名之间尽量提现join的关系，如user表和user_login表。
  11. 【强制】表名要求模块名强相关。如师资系统采用”sz”作为前缀，渠道系统采用”qd”作为前缀等。
  12. 【建议】主键的名称以“pk_”开头，唯一键以“uk_”或“uq_”开头，普通索引以“idx_”开头，一律使用小写格式，以表名/字段的名称或缩写作为后缀。
  13. 【强制】中间表用于保留中间结果集，名称必须以tmp_开头。备份表用于备份或抓取源表快照，名称必须以bak_开头。中间表和备份表定期清理。 持久化的临时表以temp_开头，脚本操作如合区会忽略。

1.2 表结构

  1. 【强制】创建表时必须显式指定表存储引擎类型，如无特殊需求，一律为InnoDB。当需要使用除InnoDB/MyISAM/Memory以外的存储引擎时，必须通过DBA审核才能在生产环境中使用。因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性，为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的，因此首推InnoDB。
  2. 【强制】表名必须有comment，表字段须有comment。
  3. 【强制】建表时设置主键，innodb为索引组织表，不设置主键则用隐含的rowid组织，业务清晰度不够，而且在复制时会导致延迟（因为没有主键，每个操作可能需要全表扫描，尤其是row模式） 1) 表必须有主键，不使用更新频繁的列作为主键 2) 建议主键类型为int/bigint，尽量不选择字符串列作为主键 3) 不使用UUID、MD5、HASH作为主键。因为如果主键值为随机插值，则会导致记录在磁盘中不连续从而产生大量随机I/O，性能下降 4) 默认使用非空的唯一键 5) 如果没有显示的字段可作为主键（如用户id），则设置主键为自增字段
  4. 【建议】核心表（如用户表，金钱相关的表）必须有行数据的创建时间字段create_time和最后更新时间字段update_time，便于查问题。
  5. 【建议】表中所有字段必须都是NOT NULL属性，业务可以根据需要定义DEFAULT值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。
  6. 【建议】建议对表里的blob、text等大字段，垂直拆分到其他表里，仅在需要读这些对象的时候才去select。
  7. 【建议】反范式设计：把经常需要join查询的字段，在其他表里冗余一份。如user_name属性在user_account，user_login_log等表里冗余一份，减少join查询。
  8. 【建议】范式设计，尽量减少数据的冗余，但需要考虑可能join的成本，与反范式综合考虑。
  9. 【强制】禁止在表中建立预留字段，预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型，所以无法选择合适的类型 对预留字段类型的修改，会对表进行锁定。
  10. 【强制】禁止在数据库中存储图片，文件等大的二进制数据。通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息。
  11. 【强制】尽量避免使用外键约束 1) 不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引 2) 外键可用于保证数据的参照完整性，建议在业务端实现 3) 外键会影响父表和子表的写操作从而降低性能

1.3 列数据类型优化

  1. 【建议】表中的自增列（auto_increment属性），推荐使用bigint类型。因为无符号int存储范围为-2147483648~2147483647（大约21亿左右），溢出后会导致报错。
  2. 【建议】业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间。
  3. 【建议】业务中IP地址字段推荐使用int unsigned类型，不推荐用char(15)，强烈不推荐使用varchar(15)。因为int只占4字节，可以用如下函数相互转换，而char(15)占用至少15字节。一旦表数据行数到了1亿，那么要多用1.1G存储空间。 SQL：select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long('192.168.2.12'); long2ip(3530427185);
  4. 【建议】不推荐使用enum，set。 推荐使用tinyint或smallint。修改ENUM值需要使用ALTER语句。ENUM类型的ORDER BY操作效率低，需要额外操作（否则默认按照创建时的顺序排序而不是值的顺序）。
  5. 【建议】不推荐使用blob，text等类型。它们都比较浪费硬盘和内存空间。在加载表数据时，会读取大字段到内存里从而浪费内存空间，影响系统性能。建议和PM、RD沟通，是否真的需要这么大字段。Innodb中当一行记录超过8098字节时，会将该记录中选取最长的一个字段将其768字节放在原始page里，该字段余下内容放在overflow-page里。不幸的是在compact行格式下（默认且不建议更改），原始page和overflow-page都会加载。
  6. 【建议】存储金钱的字段，建议用int，程序端乘以100和除以100进行存取。因为int占用4字节，而double占用8字节，空间浪费。使用DECIMAL[(M),(N)]存储金额会是更好的选择，整数和小数分别声明。而使用float则可能出现精度丢失问题，即如果值过大，则导致小数点后的数量不够。
  7. 【建议】不固定长度值的文本数据尽量用varchar存储。因为varchar是变长存储，比char更省空间；char是定长，即使使用不完在存储时也占用声明时的长度。MySQL server层规定一行所有文本最多存65535字节，因此在utf8字符集下最多存21844个字符，超过会自动转换为mediumtext字段。而text在utf8字符集下最多存21844个字符，mediumtext最多存2^24/3个字符，longtext最多存2^32个字符。一般建议用varchar类型，字符数不要超过2700
  8. 【建议】定长的字符串用char，因为varchar变长需要记录头特殊标记，增加计算量。
  9. 【建议】时间类型尽量选取timestamp。因为datetime占用8字节，timestamp仅占用4字节，但是范围为1970-01-01 00:00:01到2038-01-01 00:00:00。更为高阶的方法，选用int来存储时间，使用SQL函数unix_timestamp()和from_unixtime()来进行转换。timestamp最大只是到2038-01-19 03:14:07，不要给别人埋雷。
  10. 【建议】优先选择符合存储需要的最小的数据类型 列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的IO次数也就越多， 索引的性能也就越差。 1）将字符串转换成数字类型存储（INT UNSIGNED），如：将IP地址转换成整形数据 2）对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整型来存储因为：无符号相对于有符号可以多出一倍的存储空间，VARCHAR(N)中的N代表的是字符数，而不是字节数使用UTF8存储255个汉字 Varchar(255)=765个字节。过大的长度会消耗更多的内存
  11. 【强制】避免使用TEXT、BLOB数据类型 最常见的TEXT类型可以存储64k的数据，建议把BLOB或是TEXT列分离到单独的扩展表中 MySQL内存临时表不支持TEXT、BLOB这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行 且对于这种数据，MySQL还是要进行二次查询，会使sql性能变得很差，但是不是说一定不能使用这样的数据类型 如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select * 而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询 注意：TEXT或BLOB类型只能使用前缀索引，因为MySQL对索引字段长度是有限制的，所以TEXT类型只能使用前缀索引，并且TEXT列上是不能有默认值的
  12. 【强制】尽可能把所有列定义为NOT NULL 索引NULL列需要额外的空间来保存，所以要占用更多的空间；（？在磁盘并没有占更多空间，但null的单独标记），进行比较和计算时要对NULL值做特别的处理。
  13. 【强制】财务相关的金额类数据必须使用decimal类型 1) 非精准浮点：float,double 2) 精准浮点：decimal Decimal类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据
  14. 【建议】用DECIMAL代替FLOAT和DOUBLE存储精确浮点数 浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；浮点数的缺点是会引起精度问题 1) 将字符转化为数字 3) 字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量
  15. 【强制】使用UNSIGNED存储非负整数。 同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255；INT类型固定占用4个字节存储。
  16. 【强制】使用VARBINARY存储大小写敏感的变长字符串。
  17. 【强制】禁止在数据库中存储明文密码。

详细存储大小参考mysql文档

1.4 索引设计

  1. 【强制】InnoDB和MyISAM存储引擎表，索引类型必须为BTREE（默认）；MEMORY表可以根据需要选择HASH或者BTREE类型索引。
  2. 【强制】单个索引中每个索引记录的长度不能超过64KB。
  3. 【建议】单个表上的索引个数不能超过7个 索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能
  4. 【建议】单个索引中的字段数不超过5个。过多的字段需要更多的磁盘空间，一次查询可能要扫过更多的page，性能下降。
  5. 【建议】对字符串使用前缀索引，前缀索引长度不超过10个字符。如有一个CHAR(200)列，在前10个字符内，多数值是惟一的，就可不要对整个列进行索引。对前10个字符进行索引能够节省大量索引空间，也可能会使查询更快。
  6. 【建议】在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面。如列userid的区分度可由select count(distinct userid)/count(*)计算出来；不要对区分度很小的字段单独创建索引，如对性别字段创建索引，实际查询并不会走索引但会浪费空间存储索引以及拖慢插入速度。
  7. 【建议】在多表join的SQL里，被驱动表（优化器自动选择大表为被驱动表，小表驱动大表效率最高）的连接列上应该有索引（即使用的连接字段带有索引），这样join执行效率最高。
  8. 【建议】建表或加索引时，保证表里互相不存在冗余索引。对于MySQL来说，如果表里已经存在key(a,b)，则key(a)为冗余索引，需要删除；但同时不建议将很多较少过滤查询的字段添加到联合索引，这样会导致存储索引的空间增大，增大查询时的读取时间。
  9. 【强制】禁止给表中的每一列都建立单独的索引。5.6版本之前，一个sql只能使用到一个表中的一个索引，5.6以后，虽然有了合并索引的优化方式，但远没有使用联合索引的查询方式效率高。
  10. 【建议】频繁更新的列在后，可减少更新时对索引结构的更改。update t_name set a=xxx where b=yyy，存在查询 select ... from t_name where a=xxx and b=yyy，需要对a、b建立联合索引，则(b,a)更为理想。
  11. 【强制】核心SQL优先考虑覆盖索引。
  12. 【强制】避免冗余或重复索引，冗余/重复索引会增加查询优化器生成执行计划的时间。 1) 重复索引示例：primary key(id)、index(id)、unique index(id) 2) 冗余索引示例：index(a,b,c)、index(a,b)、index(a)
  13. 【建议】区分度最大的字段放在联合索引的左侧，使用频繁的列放到联合索引的左侧，可以尽量重用同一个索引。index(a,b,c)，如果a的区分度高，则select ...from ... where a=...可以重用这个索引。
  14. 【建议】索引列建议 1) 出现在SELECT、UPDATE、DELETE语句的WHERE子句中的列 2) 包含在ORDER BY、GROUP BY、DISTINCT中的字段 3) 多表join的关联列 注意：并不要将符合1和2中的字段的列都建立一个索引，通常将1、2中的字段建立联合索引效果更好

1.5 分库分表、分区表

  1. 【强制】分区表的分区字段（partition-key）必须有索引，或者是组合索引的首列。
  2. 【强制】单个分区表中的分区（包括子分区）个数不能超过1024。
  3. 【强制】上线前RD或者DBA必须指定分区表的创建、清理策略。
  4. 【强制】访问分区表的SQL必须包含分区键。
  5. 【建议】单个分区文件不超过2G，总大小不超过50G。建议总分区数不超过20个。
  6. 【强制】对于分区表执行alter table操作，必须在业务低峰期执行。
  7. 【强制】采用分库策略的，库的数量不能超过1024。
  8. 【强制】采用分表策略的，表的数量不能超过4096。
  9. 【建议】单个分表不超过500W行，ibd文件大小不超过2G，这样才能让数据分布式变得性能更佳。
  10. 【建议】水平分表尽量用取模方式，日志、报表类数据建议采用日期进行分表。
  11. 【建议】尽量控制单表数据量的大小，建议控制在1000万以内。 1000万并不是MySQL数据库的限制，过大会造成修改表结构，备份，恢复都会有很大的问题可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小。
  12. 【建议】谨慎使用MySQL分区表。 业务生命周期内，评估单表数据量是否在1000万以内，超出此范围需考虑分库分表可扩展性；分区表在物理上表现为多个文件，在逻辑上表现为一个表，谨慎选择分区键，跨分区查询效率可能更低，建议采用物理分表的方式管理大数据。
  13. 【建议】尽量做到冷热数据分离，减小表的宽度。 MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）。

1.6 字符集

  1. 【强制】数据库本身库、表、列所有字符集必须保持一致，为utf8或utf8mb4。优先utf8mb4，因为MySQL的utf8最大只能存储3字节，导致如emoji字符不能存储，实际表现两者基本一致，都是变长的字符编码（如一个中文字符占用三字节，英文字符占用1字节）。utf8mb4只是MySQL中特殊的存在，其他系统没有。
  2. 【建议】直接在实例级别设置默认的字符编码，其他的库、表、列使用默认的编码，不必每次显式声明。
  3. 【强制】前端程序字符集或者环境变量中的字符集，与数据库、表的字符集必须一致，统一为utf8。

1.7 程序层设计建议

  1. 【建议】新的代码不要用model，推荐使用手动拼SQL+绑定变量传入参数的方式。因为model虽然可以使用面向对象的方式操作db，但是其使用不当很容易造成生成的SQL非常复杂，且model层自己做的强制类型转换性能较差，最终导致数据库性能下降。使用框架在很大程度上比徒手高明，而且扩展性也更好（不同类型数据库、不同版本），代码简洁性更高。但这些的前提是在对框架比较清晰或者反复验证操作的情况下。
  2. 【建议】前端程序连接MySQL或者redis，必须要有连接超时和失败重连机制，且失败重试必须有间隔时间。
  3. 【建议】前端程序报错里尽量能够提示MySQL或redis原生态的报错信息，便于排查错误。
  4. 【建议】对于有连接池的前端程序，必须根据业务需要配置初始、最小、最大连接数，超时时间以及连接回收机制，否则会耗尽数据库连接资源，造成线上事故。
  5. 【建议】对于log或history类型的表，随时间增长容易越来越大，因此上线前RD或者DBA必须建立表数据清理或归档方案。这中类型的表如果没有跟其他表关联查询的需要，可以考虑使用列式数据库或者时序数据库存储，以更好支持聚合分析，同时不拖累OLTP业务性能。或者折中考虑，定期将历史数据迁移。
  6. 【建议】在应用程序设计阶段，RD必须考虑并规避数据库中主从延迟对于业务的影响。建议强制一致性的读开启事务走主库，或更新后过一段时间再去读从库。
  7. 【建议】多个并发业务逻辑访问同一块数据（innodb表）时，会在数据库端产生行锁甚至表锁导致并发下降，因此建议更新类SQL尽量基于主键/唯一键去更新。
  8. 【建议】业务逻辑之间加锁顺序尽量保持一致，否则会导致死锁（反例：A事务，对a数据集合加锁，然后对b数据集合加锁；B事务，对b数据集合加锁，然后对a数据集合加锁。高并发时会增大出现循环互斥产生死锁的概率，即A拥有a的锁但等待b的锁，B拥有b的锁但等待a的锁）。
  9. 【建议】对于单表读写比大于10:1的数据行或单个列，可以将热点数据放在缓存里（如mecache或redis），加快访问速度，降低MySQL压力。
  10. 【建议】使用预编译语句PREPARE。预编译语句可以重复使用执行计划，减少SQL解析所需要的时间，还可以解决动态SQL所带来的SQL注入的问题。只传参数，比传递SQL语句更高效。

  11. 【建议】避免使用SQL拼接，使用成熟的参数替换。未成熟的SQL拼接可能导致存在SQL注入风险。如 select * from t_name where a="$a"，如果传入的$a为 ";drop table xx;--，则导致执行恶意语句。

    1.8 一个规范的建表语句示例

一个较为规范的建表语句为：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE TABLE user (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(11) NOT NULL COMMENT '用户id'
  `username` varchar(45) NOT NULL COMMENT '真实姓名',
  `email` varchar(30) NOT NULL COMMENT '用户邮箱',
  `nickname` varchar(45) NOT NULL COMMENT '昵称',
  `avatar` int(11) NOT NULL COMMENT '头像',
  `birthday` date NOT NULL COMMENT '生日',
  `sex` tinyint(4) DEFAULT '0' COMMENT '性别',
  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍自己，最多50个汉字',
  `user_resume` varchar(300) NOT NULL COMMENT '用户提交的简历存放地址',
  `user_register_ip` int NOT NULL COMMENT '用户注册时的源ip',
  `create_time` datetime NOT NULL COMMENT '用户记录创建的时间',
  `update_time` datetime NOT NULL COMMENT '用户资料修改的时间',
  `user_review_status` tinyint NOT NULL COMMENT '用户资料审核状态，1为通过，2为审核中，3为未通过，4为还未提交审核',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_id` (`user_id`),
  KEY `idx_username`(`username`),
  KEY `idx_create_time`(`create_time`,`user_review_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='网站用户基本信息';

2 SQL操作

2.1 DML语句

  1. 【强制】SELECT语句必须指定具体字段名称，禁止写成*。因为select *会将不使用的字段也从MySQL里读出来，消耗更多的CPU和IO以网络带宽资源；可减少表结构变更带来的影响，表字段一旦更新，但model层没有来得及更新的话，系统会报错；无法使用覆盖索引。读取的字段如果刚好都在使用的索引，如select a,b,c from ...使用的索引是key(a,b,c)，即查询使用覆盖索引，无需进行回表查询，查询效率最高。
  2. 【强制】insert语句指定具体字段名称，不要写成insert into t1 values(…)，减少表结构变更带来的影响。
  3. 【建议】insert into…values(XX),(XX),(XX)…。这里XX的值不要超过5000个。值过多虽然上线很很快，但会引起主从同步延迟。
  4. 【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以内。因为union all不需要去重，节省数据库资源，提高性能。
  5. 【建议】in值列表限制在500以内。例如select… where userid in(….500个以内…)，这么做是为了减少底层扫描，减轻数据库压力从而加速查询。
  6. 【强制】事务涉及的表必须全部是innodb表。否则一旦失败不会全部回滚，且易造成主从库同步中断。
  7. 【强制】写入和事务发往主库，只读SQL发往从库。
  8. 【强制】除静态表或小表（100行以内），DML语句必须有where条件，且使用索引查找。
  9. 【强制】生产环境禁止使用hint，如sql_no_cache，force index，ignore key，straight join等。因为hint是用来强制SQL按照某个执行计划来执行，但随着数据量变化我们无法保证自己当初的预判是正确的，因此我们要相信MySQL优化器！
  10. 【强制】where条件里等号左右字段类型必须一致，否则无法利用索引。即避免数据类型的隐式转换，隐式转换会导致索引失效。
  11. 【建议】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句，且WHERE子句的条件建议使用索引查找。
  12. 【强制】生产数据库中强烈不推荐大表上发生全表扫描，但对于100行以下的静态表可以全表扫描。查询数据量不要超过表行数的25%，否则不会利用索引。
  13. 【建议】WHERE 子句尽量避免只使用模糊的LIKE条件进行查找，如需使用like尽量避免左模糊查找（如like'%AAA'），这种查询不会使用索引。
  14. 【建议】索引列不要使用函数或表达式，否则无法利用索引。如where length(name)=10或where user_id+2=10023。
  15. 【建议】减少使用or语句，可将or语句优化为union，然后在各个where条件上建立索引。如where a=1 or b=2优化为where a=1… union …where b=2, key(a),key(b)。
  16. 【建议】分页查询，limit的查询过程需要先扫描前面的匹配记录，然后再截取指定位置返回结果，当limit起点较高时，会造成查询效率下降。可先用可以比较大小的主键/唯一键进行过滤然后再分页，查询性能不因分页数的变化而改变。如select a,b,c from t1 limit 10000,20;优化为: select id,a,b,c from t1 where id>=10000 limit 20;，应用程序记录id的返回值代替limit的起点实现下一页查询。
  17. 【强制】不使用反向查询，如not in / not like，无法使用索引，导致全表扫描，全表扫描导致buffer pool被不需要的数据刷新。
  18. 【强制】充分利用表上已经存在的索引 有index(a,b,c) ，在查询条件中有a列的范围查询，则在b,c列上的索引将不会被用到。在定义联合索引时，如果a列要用到范围查找的话，就要把a列放到联合索引的右侧，即定义为index(b,c,a)。使用left join或 not exists来优化not in操作，因not in 也通常会使用索引失效。
  19. 【强制】程序连接不同的数据库使用不同的账号，禁止跨库查询 1) 为数据库迁移和分库分表留出余地 2) 降低业务耦合度 3) 避免权限过大而产生的安全风险
  20. 【强制】对应同一列进行or判断时，使用in代替or。不要超过500个in操作可以更有效的利用索引，or大多数情况下很少能利用到索引。
  21. 【强制】禁止使用order by rand() 进行随机排序。随机排序会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的CPU和IO及内存资源。推荐在程序中获取一个随机值，然后从数据库中获取对应的数据。
  22. 【强制】在明显不会有重复值时使用UNION ALL而不是UNION 1) UNION会把两个结果集的所有数据放到临时表中后再进行去重操作 2) UNION ALL不会再对结果集进行去重操作
  23. 【强制】拆分复杂的大SQL为多个小SQL 1) 大SQL逻辑上比较复杂，需要占用大量CPU进行计算 2) 一个SQL只能使用一个CPU进行计算 3) SQL拆分后可以通过并行执行来提高处理效率

2.2 多表连接

  1. 【强制】禁止跨db的join语句。因为这样可以减少模块间耦合，为数据库拆分奠定坚实基础
  2. 【强制】禁止在业务的更新类SQL语句中使用join，比如update t1 join t2…
  3. 【建议】不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询，因为子查询限制驱动表的顺序，join使用优化器优化驱动表顺序实现最高查询效率
  4. 【建议】多表连接查询推荐使用别名，且SELECT列表中要用别名引用字段，数据库.表格式，如select a from db1.table1 alias1 where …
  5. 【建议】在多表join中，尽量选取结果集较小的表作为驱动表，来join其他表（使用join时优化器自动选择，如确认优化器的选择不是最优，使用子查询控制驱动表顺序）
  6. 【强制】避免使用JOIN关联太多表 MySQL最擅长的是单表的主键/二级索引查询，MySQL存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置。在MySQL中，对于同一个SQL多关联（join）一个表，会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。Join消耗较多的内存，产生临时表。如程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。同时对于关联操作来说，会产生临时表操作，影响查询效率MySQL最多允许关联61个表，但业务生产环境中建议不超过5个。

2.3 事务

  1. 【建议】事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在2000以内，以及WHERE子句中IN列表的传参个数控制在500以内。
  2. 【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep，一般建议值5-10秒。
  3. 【建议】对于有auto_increment属性字段的表的插入操作，并发需要控制在200以内。
  4. 【强制】程序设计必须考虑“数据库事务隔离级别”带来的影响，包括脏读、不可重复读和幻读。线上建议事务隔离级别为repeatable-read（mysql默认使用，如非特殊必要，请不要在sql语句中更改隔离级别）。
  5. 【建议】事务里包含SQL不超过5个（支付业务除外）。因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等雪崩问题。
  6. 【建议】事务里更新语句尽量基于主键或unique key，如update … where id=XX; 否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。
  7. 【建议】尽量把一些典型外部调用移出事务，如调用webservice，访问文件存储等，从而避免事务过长。
  8. 【建议】对于MySQL主从延迟严格敏感的select语句，请开启事务强制访问主库。
  9. 【强制】优化大事务。 超100万行的批量写（UPDATE、DELETE、INSERT）操作，要分批多次进行操作 1) 大批量操作可能会造成严重的主从延迟 主从环境中，大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，只有当主库上执行完成后，才会在其他从库上执行，会造成主库与从库长时间的延迟情况 2) binlog日志为row格式时会产生大量的日志 大批量写操作会产生大量日志，特别是对于row格式二进制数据而言，由于在row格式中会记录每一行数据的修改，一次修改的数据越多，产生的日志量也会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因 3) 避免产生大事务操作 大批量修改数据，一定是在一个事务中进行的，这会造成表中大批量数据进行锁定，导致大量的阻塞，阻塞会对MySQL的性能影响很大。尤其是长时间的阻塞会占满所有数据库的可用连接，会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批

2.4 排序和分组

  1. 【建议】减少使用order by，将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU。
  2. 【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by b可以利用key(a,b)。
  3. 【建议】包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

2.5 线上禁止使用的SQL语句

  1. 【高危】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。因为会导致主从不一致，导致数据错乱。建议加上order by PK
  2. 【高危】禁止使用关联子查询，如update t1 set … where name in(select name from user where…);效率极其低下 避免使用子查询，可把子查询优化为join操作，通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。 1) 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能 会受到一定的影响 2) 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大 3) 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询
  3. 【建议】尽量不使用procedure、function、trigger、event。因为会额外消耗数据库资源，降低数据库实例可扩展性，不同类型数据库实现差异很大影响应用程序的兼容性。降低业务耦合度，为分库分表留余地。难以调试，迁移更新等维护困难，升级版本可能不支持。推荐都在程序端实现
  4. 【强制】禁用insert into …on duplicate key update…在高并发环境下，会造成主从不一致
  5. 【强制】禁止联表更新语句，如update t1,t2 set … where t1.id=t2.id…（？效率低下，业务不可控缺，缺乏清晰业务模型？）
  6. 【强制】禁止使用select * from ...，只获取需要的字段

2.6 DDL

  1. 【强制】对单表的多次alter操作合并为一次操作。只需要影响一次。
  2. 【强制】更新前确认是否原生支持online DDL，如不支持，使用第三方在线DDL工具，如pt-online-schema-change、ghost等。
  3. 【强制】对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行。因为alter table会产生表锁，期间阻塞对于该表的所有写入，对于业务可能会产生极大影响。(即使支持online DDL，已经存在的事物也会阻塞DDL，直到DDL获取到元数据锁，DDL占用元数据锁的时间比较短，之后的变更操作不影响DML)。

3. 管理与操作规范

3.1 管理

  1. 【强制】禁止为程序使用的账号赋予super权限。当MySQL 达到最大连接数限制时，此刻还运行1个有super权限的用户连接，super不会被read_only参数影响。super权限只能留给DBA处理问题的账号使用。
  2. 对于程序连接数据库账号，遵循权限最小原则程序，使用数据库账号只能在一个DB下使用，不准跨库，程序使用的账号原则上不准有drop权限。

3.2 操作

  1. 【强制】禁止在线上做数据库压力测试。
  2. 【建议】禁止从开发环境，测试环境直接连接生成环境数据库。
  3. 【强制】任何数据库的线上操作，必须走工单。
  4. 【强制】禁止在主库上执行统计类的功能查询。
  5. 【强制】数据必须有备份机制和定期的恢复演练。
  6. 【强制】不在业务高峰期批量更新、查询数据库。
  7. 【强制】有大规模市场推广、运营活动必须提前通知DBA进行流量评估。
  8. 【强制】重大项目的数据库方案选型和设计必须提前通知DBA参与。
FEATURED TAGS
python mysql database innodb cas ha mongodb mongoshake tao web