mysql规范总结

参考文献：https://www.cnblogs.com/qlqwjy/p/8425861.html

https://blog.csdn.net/u010498753/article/details/85966709

一、基本规范要求

1、没有特殊情况使用InnoDB作为存储引擎

InnoDB与MyISAM的对比

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响， 适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响

1.1 该怎么选择存储引擎呢？

具体的选择还是需要根据具体的开发需求，可以从以下几点考虑：

1、数据库有外键吗？
2、需要事务支持吗？
3、需要全文索引吗？
4、经常使用什么样的查询模式？
5、数据量有多大？

如果你需要事务处理或是外键，那么InnoDB可能是比较好的方式。如果你需要全文索引，那么通常来说 MyISAM是好的选择，因为这是系统内建的。但是在大数据开发中直接指定存储引擎为InnoDB，因为InnoDB的效率更好，支持的更多，相对于资源来说，更高的效率是我们追求的。

数据的大小也是一个影响选择什么样存储引擎的重要因素，数据量较大的趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事，InnoDB只需要几分钟。

2、数据库和表的字符集统一使用UTF8

避免由于字符集转换产生的乱码，mysql中utf8字符集汉字占3个字节，GBK字符的汉字占用2个字节，ASCII码占用1个字节。

3、控制单表数据量在500万以内

mysql的正常相应速度一般在几百万条，如果数据量过大，在表的结构更新、备份上会造成响应较慢。

特殊情况下，例如流水、日志，可以另行考虑。

4、谨慎使用MYSQL表分区

分区后的查询结果要比未分区的慢很多

分区规则仅限制在主键上使用，性能会有提高，在其他字段上使用，即使有索引，性能也会很差。

5、禁止在线上做数据库压力测试

6、所有表和字段都需要添加注释，使用comment从句添加表和列的备注

二、数据库表的设计规范

1、表的设计首先遵循三大范式

数据库的三范式：

①字段不可分。

②有主键，非主键字段依赖主键。

③非主键字段不能互相依赖。

2、数据库基本规范

• 所有命名均使用小写字母，且命名（索引除外）长度不超过30字符
• 禁止使用Mysql保留关键词
• 命名需使用关联字符时，使用“_” 下划线分割。
• 单库不超过500个表
• 单表字段不超过30字符
• 表和字段需要添加注释

3、字段设计规范

用尽量少的存储空间来存数一个字段的数据;

例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);

固定长度的类型最好使用char,例如：邮编;

能使用tinyint就不要使用smallint,int;

最好给每个字段一个默认值,最好不能为null;

4、数据库表索引规范

命名简洁明确,例如：user_login表user_name字段的索引应为user_name_index唯一索引;

为每个表创建一个主键索引;

为每个表创建合理的索引;

建立复合索引请慎重;

4.1 索引优化

参考：https://www.cnblogs.com/yyjie/p/7486975.html

数据结构：B+Tree

一般来说能够达到range就可以算是优化了

4.2 索引原则：

合理使用索引(改善查询,减慢更新,索引一定不是越多越好);

字符字段必须建前缀索引;

不在索引做列运算;

innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);

不用外键(由程序保证约束);

4.3 索引命名规范

主键命名：pk_ 前缀 pk_uid
唯一键命名：uk_ 前缀 uk_uid
普通索引：idx_前缀 多索引“_”进行链接 idx_uid_dateline_age

4.4 索引使用规范

1、避免在低基数字段上使用索引，不在Null字段上使用索引• 如：性别，值为null等

2、禁止%为前导的查询

• 如：like ‘%123’等

3、建议不使用Select *

4、禁止使用外键

5、建议单张表索引不超过5个，单个索引字段不超过5个

• 合理使用索引，禁止索引冗余

• 如：idx_a_b_c = idx_a & idx_a_b & idx_a_b_c

6、禁止使用字符串前缀索引

7、组合索引顺序

• 把区分度高的字段放在前面

• 如：where a = 10000 and b=2

8、禁止在varchar过长的字段上建立索引

9、在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据

实际文本区分度决定索引长度即可。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

索引长度太短，那么区分度就很低，索引占内存越低，
索引长度太长，区分度就高，索引占内存越多，
所以需要找到一个平衡点；

转自：https://blog.csdn.net/qq_20789179/article/details/108118327

10、避免数据类型的隐式转换

可能导致索引失效，例如将mysql将int类型隐式转换为字符串类型来执行sql语句。

详细请参考：https://blog.csdn.net/hw_libo/article/details/39252427

覆盖索引：SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不需要读取数据，有以下优点：

1、索引项通常比记录要小，所以MySQL访问更少的数据。

2、索引都按值得大小存储，相对于随机访问记录，需要更少的I/O。

3、数据引擎能更好的缓存索引，比如MyISAM只缓存索引。

4、覆盖索引对InnoDB尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引包含查询所需的数据，就不再需要在聚集索引中查找了。

限制：

1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。

2、Hash和full-text索引不存储值，因此MySQL只能使用BTree。

3、不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引。

4、如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

参考文献：MySQL覆盖索引优化，https://yq.aliyun.com/articles/709783







 

5、字段名规范

1、MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。

2、字段名禁止以数字开头，禁止两个下划线中间只出现数字。

3、建议字段值定义为NOT NULL
•用0或者’’以及其他默认值代替
• NULL被索引增加1字节，且统计不准确
• NULL尽量规避索引

4、建议使用UNSIGNED 存储非负数值
同样的字节数，存储的数值范围更大

6、类型规范

小数类型为 decimal，禁止使用 float 和 double。

• float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
• 如果采用float或者double类型的话，数据有时候完全准确的，有时候是不准确的，怎么才能存储一个准确的数字，完全看你需要存什么样的数据，假如存储一个8.25这样的数字，那永远都是准确的。但是如果存储0.9这样的数字，则永远存不准确。

三、SQL 使用规范

1、建议使用预编译语句：• prepared statement,只传参数，比sql效率高

• 一次解析，多次使用

• 避免sql注入

2、Sql 语句中IN包含的值不能过多：

• 不能多个in

• in 值最大不能超过100

3、Sql 语句中Select和Insert必须显示的指明字段名称：

• 不能使用select * 或者 insert table

• 会增加硬件资源消耗，减少索引覆盖的可能性

4、不在索引列进行函数或者数学运算：

• 如：where max(age) = 1

5、避免使用大表的Join

• 尽可能使用2张表join，小表在前，大表在后

• 避免多表join

6、Update,Delete语句中禁止使用limit

• 可能导致主从数据不一致

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

7、Where 条件中使用合适类型，避免隐式转换

• 如：id=123 id=‘123’

8、减少数据库的交互次数

• INSERT … ON DUPLICATE KEY UPDATE

• REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()

• UPDATE … WHERE ID IN(A,B,C,…)

• 根据具体情况而定

9、禁止SQL语句过大，拆分成小SQL

10、不同字段，将or改为union all，减少对不同字段进行 “or” 查询

• 用union all而不是union

• merge index 比较弱

11、复杂统计和查询不可使用线上库，原则上线上线下库隔离使用。

• 线下库联系大数据提供

12、程序端禁止使用set 全局设置语句

• set names、set sql_mode和set isolation_level 等

 

四、查询使用规范

1、Innodb 引擎表禁止使用Select count(*)• 推荐使用 Select count(pkey),select count(1)

• 非实时统计建议使用单表，定期更新

2、IN和OR的选择，推荐使用IN

• IN的效率更高，不能多个IN同时使用。

• IN的值尽可能少，最大不超过100个。

3、Where 子句条件中，索引和组合索引前不能使用函数或算法

• 会导致索引失效

4、Where 子句中禁止使用全模糊LIKE 查询，必须有其他索引条件查询。

5、禁止使用Order by rand

• 磁盘读取数据排序，消耗设备资源

• 建议在程序端解决rand问题

6、禁止多层嵌套子查询

• 建议写成顺序表链接格式

7、查询结果集不宜过大，最大不超过2000 条

• 结果集过大请分段获取。

• 超大数据请联系大数据处理。

8、合理使用LIMIT 分页，提高分页效果

• LIMIT 10000,10, 数据量过大时会导致IO问题

• 推荐使用方法（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 t USING(id)）

• 其他程序端方法实现。

9、禁止在线上库中查询复杂业务

• 线下数据库中查询

mysql存储过程

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

相对于直接使用SQL 语句，在应用程序中直接调用存储过程有以下好处：

(1)减少网络通信量。

调用一个行数不多的存储过程与直接调用SQL 语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL 语句，那么其性能绝对比一条一条的调用SQL 语句要高得多。

(2)执行速度更快。

有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

(3)更强的适应性。

由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

(4) 分布式工作。

应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

参考文献：

https://www.html.cn/qa/other/23253.html

https://www.cnblogs.com/geaozhang/p/6797357.html

大数据业务需求方面

1、所有新增字段必须添加在表的末尾 ，【强制】表必备三字段：id, ctime, utime。

说明：

a. id必为主键 ,单表时自增、步长为1。

b. ctime,utime的类型均为date_time类型，前者为 CURRENT_TIMESTAMP，后者为CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。建议使用int类型。

c. 禁止update set ctime/utime。ctime/utime作为每行的时间记录，只允许自动生成，禁止手动去

d. 推荐 utime字段建索引

3.【推荐】enum/tinyint在comment中详细注明所有的值，以及对应的含义。

4.【推荐】内容为json格式的，使用json字段类型可以避免脏数据。