MySQL开发规范和基础

        俗话说，无规矩不成方圆。而规范的本质不是解决问题，而是有效杜绝一些潜在问题，所以规范的意义很明确，能够让彼此的工作都可以互利互惠。但是从工作实践来说，大家对于数据库开发规范还是存在一些误解，主要表现在以下三个方面:
        (1)有了开发规范，但是规范粒度太粗，规范落实没有参考。
        (2)制定了太过细致的开发规范，导致在工作落实中难以适应业务场景。
        (3)有了开发规范，束之高阁了，没有一个持续的改进过程。
        所以我们也要结合MySQL的特点给出一些具体可行的建议,这就需要我们对MySQL的基础内容有一个全面地梳理和总结。

一、配置规范

        (1) MySQL数据库默认使用InnoDB存储引擎。
        (2) 保证字符集设置统一，MySQL数据库相关系统、数据库、表的字符集使都用UTF8，应用程序连接、展示等可以设置字符集的地方也都统一设置为UTF8字符集。
        注:UTF8格式是存储不了表情类数据，需要使用UTF8MB4，可在 MySQL字符集里面设置。在8.0中已经默认为 UTF8MB4，可以根据公司的业务情况进行统一或者定制化设置，
        (3) MySQL数据库的事务隔离级别默认为RR(Repeatable-Read)，建议初始化时统一设置为RC(Read-Committed)，对于OLTP业务更适合。
        (4) 数据库中的表要合理规划，控制单表数据量，对于MySOL数据库来说，建议单表记录数控制在2000W以内。
        (5) MySOL实例下，数据库、表数量尽可能少:数据库一般不超过50个，每个数据库下，数据表数量一般不超过500个(包括分区表)。

二、建表规范

        (1) InnoDB禁止使用外键约束，可以通过程序层面保证
        (2) 存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE。
        (3) 整型定义中无需定义显示宽度，比如:使用INT，而不是INT(4)。
        (4) 不建议使用ENUM 类型，可使用TINYINT来代替。
        (5) 尽可能不使用TEXT、BLOB类型，如果必须使用，建议将过大字段或是不常用的描述型较大字段拆分到其他表中;另外，禁止用数据库存储图片或文件。
        (6) 存储年时使用YEAR(4)，不使用 YEAR(2)。
        (7) 建议字段定义为NOT NULL。

三、命名规范

        (1) 库、表、字段全部采用小写。
        (2) 库名、表名、字段名、索引名称均使用小写字母，并以“_”分隔。
        (3) 库名、表名、字段名建议不超过12个字符。(库名、表名、字段名支持最多64个字符，但为了统一规范、易于辨识以及减少传输量，统一不超过12字符)。
        (4) 库名、表名、字段名见名知意。

四、索引规范

        (1) 索引建议命名规则:idx_col1_col2[_colN]、uniq_col1_col2[coIN](如果字段过长建议采用缩写)。
        (2) 索引中的字段数建议不超过5个。
        (3) 单张表的案引个数控制在5个以内。
        (4) InnoDB表一般都建议有主键列，尤其在高可用集群方案中是作为必须项的。
        (5) 建立复合索引时，优先将选择性高的字段放在前面。
        (6) UPDATE、DELETE语句需要根据WHERE条件添加索引。
        (7) 不建议使用%前缀模糊查询，例如LIKE“%weibo”，无法用到索引，会导致全表扫描。
        (8) 合理利用覆盖索引，例如:
        SELECT email,uid FROM user cmail WHERE uid=xx，如果 uid 不是主键，可以创建覆盖索引idx_uid_email(uid,email)来提高查询效率。
        (9)避免在索引字段上使用函数，否则会导致查询时索引失效。

五、应用规范

        (1) 避免使用存储过程、触发器、自定义函数等,容易将业务逻辑和DB耦合在一起，后期做分布式方案时会成为瓶颈。
        (2) 考虑使用 UNION ALL，减少使用 UNION。因为 UNION ALL不去重，而少了排序操作，速度相对比UNION要快，如果没有去重的需求，优先使用UNIONALL。                                                  (3) 考虑使用limit N，少用limit M，N，特别是大表或 M 比较大的时候。
        (4) 减少或避免排序，如:group by语句中如果不需要排序，可以增加order by null。                          (5) 统计表中记录数时使用 COUNT(*)，而不是 COUNT(primary_key)和 COUNT(1)；而InnoDB 表则避免使用COUNT(*)操作，计数统计实时要求较强可以使用memcache或者redis，非实时统计可以使用单独统计表，定时更新。
        (6) 做字段变更操作(modify column/change column)的时候必须加上原有的注释属性，否则修改后，注释会丢失。
        (7) 使用prepared statement可以提高性能并且避免 SQL注入。
        (8) SQL语句中IN包含的值不应过多。
        (9) UPDATE、DELETE语句一定要有明确的WHERE条件。
        (10) WHERE条件中的字段值需要符合该字段的数据类型，避免MySQL进行隐式类型转化。
        (11) SELECT、INSERT语句必须显式的指明字段名称，禁止使用SELECT*或是INSERT INTO table name values()。
        (12) INSERT 语句使用batch提交(INSERT INTO table name VALUES (),().()……),values的个数不应过多。

六、分表规范

        (1) 不建议使用分区表来实现分表需求。
        (2) 可以结合使用 hash、range、lookup table 进行拆分。
        (3) 使用时间散表，表名后缀必须使用特定格式，比如:按日散表user_20110209，按月散表 user_201102。
        (4) 分表的设定规范可以借助数据库中间件来完成。
        (5) 如果使用MD5(或者类似的HASH算法)进行散表，表名后缀使用16进制比如 user_ff。
        (6) 推荐使用CRC32求余(或者类似的算术算法)进行散表，表名后缀使用数字，数字必须从0开始并等宽，比如拆分后100张表，后缓建议为:00-99。

七、安全规范

        (1) 杜绝SQL注入
        SQL注入是一种将SOL代码插入或添加到用户输入的参数中，之后将这些参数传递给后台的SOL服务器加以解析并执行的攻击。
        举例:
        select ipaddr from tl_ip where ipaddr = "?"
        变量可以等于一个合法的字符也可以等于一个SQL语句。
        select ipaddr from tl_ip where ipaddr = "?"                                                                                union                                                                                                                                   select group_concat(table name) from information_schema.tables where table_schema in (select database()) 
        这种就是采用SQL拼接的方式进行的SQL注入攻击，而字段名和表名可以通过试错和查询数据库元信息检索出来。
        解决方法:
        前端验证:JS中首先规范用户输入，规避用户输入不符合规范的变量，比如设置输入长度为6位。                                                                                                                                                            数据库层面:采用SQL预处理模式，定义变量类型来提高变量的合法性，也可以后端做成API或者接口，并增加容错次数限制。
        (2) 异常捕获
        程序抛异常尤其是与MySQL元信息相关的禁止直接抛到前台页面,不给攻击者试错的机会。
        (3) 权限回收
        为了避免不必要的数据操作问题，需要对权限进行回收，申请权限时也需要应需申请，不可申请过大的权限，导致安全隐患。