Mysql数据库优化、数据库规范

1 命名规范

• (1) 数据库名、表名、字段名必须使用小写字母&＃xff0c;并采用下划线”_”分割。
• (2) 数据库名、表名、字段名禁止超过32个字符&＃xff0c;须见名知意&＃xff0c;建议使用名词不是动词。
• (3) 数据库名、表名、字段名禁止使用MySQL保留字。
• (4) 临时库名、表名必须以tmp为前缀&＃xff0c;并以日期为后缀。
• (5) 备份库名、表名必须以bak为前缀&＃xff0c;并以日期为后缀。

2 基础规范

• (1) 使用INNODB存储引擎
• (2) 表字符集使用UTF8
• (3) 所有表都需要添加注释
• (4) 单表数据量建议控制在5000W以内
• (5) 数据库表建议不存储图、文件等大数据
• (6) 禁止在线上做数据库压力测试操作
• (7) 所有字段须定义为not null&＃xff0c;根据业务可指定default值

3 库表设计

• (1) 库名格式为 组织机构_xxx[xxx]&＃xff0c;比如以toon基础为例&＃xff0c;其中“toon”为前缀&＃xff0c;xxx为实际数据库名称&＃xff0c;使用各模块的项目名称字段设计。
• (2) 表名中含有单词全部采用单数形式&＃xff0c;多个单词之间使用”_”分割
• (3) 同业务模块表&＃xff0c;建议在表名前增加模块缩写。
  示例&＃xff1a;客户表&＃xff1a;cust_customer
  客户联系方式&＃xff1a;cust_contact
• (4) 分表命名规则&＃xff1a;原表明_数字&＃xff0c;示例&＃xff1a;cust_customer _01
• (5) 所有表添加注释。
• (6) 所有表必须要显式指定主键。
• (7) 单表控制字段数量&＃xff0c;30个字段的上限。
• (8) 单表数据量建议控制在2000W~5000W以内
  简单字段类型建议 5000W以内&＃xff0c;比如int&＃xff0c;tinyint&＃xff0c;bigint等
  复杂字段类型建议 2000W 以内&＃xff0c;比如varchar(n>2048)&＃xff0c;text等。
• (9) 关联表命名规则&＃xff1a;表a_表b&＃xff0c;如果存在模块缩写&＃xff0c;根据实际的业务需要保留一个模块缩写。

4 字段设计

类型 字节 有/无符号 最小值 最大值
TINYINT 1 有 -128 127
无 0 255
SMALLINT 2 有 -32768 32767
无 0 65535
MEDIUMINT 3 有 -8388608 8388607
无 0 16777215
INT 4 有 -2147483648 2147483647
无 0 4294967295
BIGINT 8 有 -9223372036854775808 9223372036854775807
无 0 18446744073709551615

• (1) 越小越好的原则&＃xff0c;选择合适的数据类型&＃xff0c;数据类型所占用字节数越小越好&＃xff1b;数值类型取值参考下表&＃xff1a;

如主键取值上限不超过42亿&＃xff0c;建议不用BIGINT

• (2) 越简单越好的原则&＃xff0c;字段能用数值型的不要用字符型。
• (3) 尽量避免使用 text/blob数据类型, 若确实需要,根据访问, 更新频次, 看是否有必要从 主表拆分出来。
• (4) 使用TINYINT代替ENUM、SET。
• (5) 字符串类型&＃xff0c;比较小并且固定比如MD5值等选择CHAR&＃xff0c;否则选择VARCHAR。
  使用尽可能小的VARCHAR字段&＃xff0c;VARCHAR(N)中的N表示的是字符数而非字节数&＃xff0c;一个汉字占用三个字节&＃xff0c;一个字母占用一个字节。
• (6) 字段须指定NOT NULL。
• (7) 常用字段类型推荐
  id主键&＃xff1a;bigint或int&＃xff0c;视数据增长范围选择&＃xff0c;自增&＃xff1b;
  phone&＃xff1a;varchar(15)&＃xff1b;
  email&＃xff1a;varchar(254)&＃xff1b;
  邮编&＃xff1a;varchar(11)&＃xff1b;
  枚举型数据&＃xff1a;tinyint&＃xff1b;
  url:varchar(2083)&＃xff1b;
  img:varchar(2083)&＃xff1b;
  IP:varchar(45)或转整型存储&＃xff1b;
  Money:DECIMAL(19,4)&＃xff1b;
  Longitude:DECIMAL(9,6)&＃xff1b;
  Latitude:DECIMAL(8,6)。

5 索引设计

• (1) 非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名。
• (2) 唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名。
• (3) 控制索引数量&＃xff0c;单表索引数量不超过5个&＃xff0c;单个索引字段不超过5个。
• (4) 索引尽量健在区分度性高的列上&＃xff0c;不在低区分度列上建立索引&＃xff0c;例如性别。
• (5) 不要在频繁更新的列上建索引&＃xff0c;不在索引列进行数学运算和函数运算。
• (6) 合理创建联合索引&＃xff08;避免冗余&＃xff09;&＃xff0c;(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)
• (7) 重要的SQL必须被索引,UPDATE、DELETE语句的WHERE条件列,ORDER BY、GROUP BY、DISTINCT的字段,多表JOIN的字段
• (8) 不使用%前缀的查询&＃xff0c;如like “%ab”&＃xff1b;不使用负向查询&＃xff0c;如not in/like(推荐考虑用全文检索sphinx)。

6 SQL设计

• (1) sql语句尽可能简单&＃xff0c;大的sql根据业务拆分成小的sql语句(充分利用QUERY CACHE和充分利用多核CPU)。
• (2) limit分页注意效率。limit越大&＃xff0c;效率越低。可以改写limit&＃xff0c;比如例子改写&＃xff1a;select id from t limit 10000, 10; &＃61;> select id from t where id > 10000 limit10。
• (3) 减少与数据库的交互次数&＃xff0c;尽量使用批量sql语句。
• (4) 注意使用性能分析的工具
  Sql explain / showprofile / mysqlsla
• (5) 建议SQL关键字全部是大写&＃xff0c;每个词只允许有一个空格
• (6) SQL语句不可以出现类型隐式转换&＃xff0c;比如 select id from 表 where id&＃61;‘1’
• (7) IN条件里面的数据数量要少&＃xff0c;使用exist代替in&＃xff0c;exist在一些场景查询会比in快
  在两个都可以使用的情况下&＃xff0c;建议通过执行计划来做取舍。
• (8) 禁止在数据库中跑大查询
• (9) 能不用NOT IN就不用NOT IN。
• (10) 在SQL语句中&＃xff0c;不建议使用模糊前缀匹配操作&＃xff0c;比如like
• (11) 关于分页查询&＃xff1a;程序里建议合理使用分页来提高效率limit&＃xff0c;offset较大要配合子查询使用
• (12) 使用预编译语句&＃xff0c;只传参数&＃xff0c;比传递SQL语句更高效&＃xff1b;一次解析&＃xff0c;多次使用&＃xff1b;降低SQL注入概率

7 行为规范

• (1) 数据库设计须有DBA参与&＃xff0c;表结构变更必须通知DBA&＃xff1b;
• (2) 批量导入、导出或者批量更新数据&＃xff0c;必须DBA进行审核&＃xff0c;执行&＃xff1b;
• (3) 建议对同一个表的多次alter操作合并为一次操作&＃xff1b;
• (4) 不要在MySQL数据库中存放业务逻辑&＃xff1b;
• (5) 建议禁止存储过程、函数、触发器的使用。

8 Mysql优化

MySQL优化需要在三个不同层次上协调进行&＃xff1a;MySQL级别、OS级别和硬件级别。MySQL级别的优化包括表优化、查询优化和MySQL服务器配置优化等&＃xff0c;而MySQL的各种数据结构又最终作用于OS直至硬件设备&＃xff0c;因此还需要了解每种结构对OS级别的资源的需要并最终导致的CPU和I/O操作等&＃xff0c;并在此基础上将CPU及I/O操作需要尽量降低以提升其效率。

• 数据库层面的优化着眼点&＃xff1a;

• 1、是否正确设定了表结构的相关属性&＃xff0c;尤其是每个字段的字段类型是否为最佳。

• 2、是否为高效进行查询创建了合适的索引。

• 3、是否为每张表选用了合适的存储引擎&＃xff0c;并有效利用了选用的存储引擎本身的优势和特性。

• 4、是否基于存储引擎为表选用了合适的行格式(row format)。

• 5、是否使用了合适的锁策略&＃xff0c;如在并发操作场景中使用共享锁&＃xff0c;而对较高优先级的需求使用独占锁等。

• 6、是否为InnoDB的缓冲池、MyISAM的键缓存以及MySQL查询缓存设定了合适大小的内存空间&＃xff0c;以便能够存储频繁访问的数据且又不会引起页面换出。

• 第一优化sql和索引&＃xff1b;

• 第二加缓存&＃xff0c;memcached,redis&＃xff1b;

• 第三以上都做了后&＃xff0c;还是慢&＃xff0c;就做主从复制或主主复制&＃xff0c;读写分离&＃xff0c;可以在应用层做&＃xff0c;效率高&＃xff0c;也可以用三方工具&＃xff0c;第三方工具推荐360的atlas&＃xff1b;

• 第四如果以上都做了还是慢&＃xff0c;不要想着去做切分&＃xff0c;mysql自带分区表&＃xff0c;先试试这个&＃xff0c;对你的应用是透明的&＃xff0c;无需更改代码,但是sql语句是需要针对分区表做优化的&＃xff0c;sql条件中要带上分区条件的列&＃xff0c;从而使查询定位到少量的分区上&＃xff0c;否则就会扫描全部分区&＃xff1b;

• 第五如果以上都做了&＃xff0c;那就先做垂直拆分&＃xff0c;其实就是根据你模块的耦合度&＃xff0c;将一个大的系统分为多个小的系统&＃xff0c;也就是分布式系统&＃xff1b;

• 第六才是水平切分&＃xff0c;针对数据量大的表&＃xff0c;这一步最麻烦&＃xff0c;最能考验技术水平&＃xff0c;要选择一个合理的sharding key,为了有好的查询效率&＃xff0c;表结构也要改动&＃xff0c;做一定的冗余&＃xff0c;应用也要改&＃xff0c;sql中尽量带sharding key&＃xff0c;将数据定位到限定的表上去查&＃xff0c;而不是扫描全部的表&＃xff1b;

• 操作系统和硬件级别的优化着眼点&＃xff1a;

• 1、是否为实际的工作负载选定了合适的CPU&＃xff0c;如对于CPU密集型的应用场景要使用更快速度的CPU甚至更多数量的CPU&＃xff0c;为有着更多查询的场景使用更多的CPU等。因为MySQL尚不能高效的运行于多CPU&＃xff0c;并且其对CPU数量的支持也有着限制。一般来说&＃xff0c;较新的版本可以支持16至24颗CPU甚至更多。
  2、是否有着合适大小的物理内存&＃xff0c;并通过合理的配置平衡内存和磁盘资源&＃xff0c;降低甚至避免磁盘I/O。缓存可以有效地延迟写入、优化写入&＃xff0c;但并能消除写入&＃xff0c;并综合考虑存储空间的可扩展性等&＃xff0c;为业务选择合理的外部存储设备也是非常重要的工作。
  3、是否选择了合适的网络设备并正确地配置了网络对整体系统系统也有着重大影响。延迟和带宽是网络连接的限制性因素&＃xff0c;而常见的网络问题如丢包等&＃xff0c;即是很小的丢包率也会赞成性能的显著下降。而更重要的还有按需调整系统中关于网络方面的设置&＃xff0c;以高效处理大量的连接和小查询。
  4、是否基于操作系统选择了适用的文件系统。同时&＃xff0c;关闭文件系统的某些特性如访问时间和预读行为&＃xff0c;并选择合理的磁盘调度器通常都会给性能提升带来帮助。
  5、MySQL为响应每个用户连接使用一个单独的线程&＃xff0c;再加内部使用的线程、特殊目的线程以及其它任何由存储引擎创建的线程等&＃xff0c;MySQL需要对这些大量线程进行有效管理。Linux系统上的NPTL线程库更为轻量级也更有效率。