一、表：

• 表和字段名字一般采用"_" 而不是使用驼峰；

• 指定表级别字符集为“CharsetEncoding = utf8mb4”、“Collation = utf8mb4_general_ci”。 ps:// 作为中国人，你不会不考虑使用中文字符吧，那么请使用utf-8吧

• 使用MySQL主要应用于OLTP(on-line transaction processing)业务，那么请使用InnoDB引擎吧，InnoDB是MySql 5.5.5以后版本的默认引擎，支持事务，支持行级锁等。如果是OLAP (Online Analytical Processing)系统，你还考虑使用MySql是不是就不合时宜呀，hive、Hbase和Elastic Search它不香吗？

• 表、字段定义时，需要添加表注释，字段注释。这点很重要，要不然交接项目的时候十分痛苦。好多项目没有字段注释的一个原因是，线上的数据库都是有DBA维护，开发添加字段或者建表的SQL语句的时候，为了偷懒而不写注释，DBA也不关心是否有注释（毕竟DBA不去接手业务项目），给项目维护增加了太多的麻烦。

• 采用合适的分库分表策略，要考虑数据库的扩容性，分表可以采取水平分表和垂直分表两种方式；

• 禁止在数据库中以明文形式保存手机号，身份证号等敏感信息，如果非要保存，可以使用AES等对称加密算法（密码位数要大于128 bits）避免脱库之后的数据风险；

二、主键和索引：

• 每张表必须要有主键，否则在MySql主从同步延迟增加，update的时候性能大大降低（曾经年少踩坑，惨痛教训）；

• 尽量采用自增id主键(避免使用uuid，插入数据的时候回导致索引树重建，降低性能)；

• 尽量不使用字符串作为主键，字符串的性能不如自增id主键；

• 单表的索引个数不宜过多，一般3个左右，通常不超过5个，超过的话，还是想想自己的表是不是需要垂直拆分了；

• 联合索引中的字段个数不宜过多，一般3个左右，通常不超过5个超过的话，还是想想自己的表是不是需要垂直拆分了；

• 理解主键索引和非主键索引，主键索引的B+数叶子节点保存的是整行数据，非主键索引的B+数叶子节点保存的是主键ID，通过非主键索引查询的时候，如果select的字段不是全部在改非主键索引字段中，那么需要反查主键索引得到对应的整行数据。因此要学会使用覆盖索引，这样的话，使用非主键索引，就不需要反查主键索引了；

• 尽量避免使用UUID，这些数据的插入会导致主键索引的重构，十分影响插入性能；

• 禁止MD5/SHA等的hash计算值作为主键，一方面这些数据的插入会导致主键索引的重构，十分影响插入性能；另一方面一旦遇到Hash冲突，就会导致数据无法插入，导致系统bug； // 工作经验值，竟然有技术提出使用Hash作为主键，当时我也是很无语的，我就直接反击了；

• update/delete尽量使用主键进行操作，如果不使用主键进行操作，where的条件字段要添加索引，否则每次update/delete是表锁，严重影响性能；

• 针对order by、group by、distinct的字段，要创建索引，利用索引的先天顺序性，避免重新排序；

• 创建索引的时候要将区分度大的字段放到前面，这样的话可以降低索引树的高度，增加索引树的宽度，这样查询的时候，减少B+索引树非叶子节点的IO次数，提高查询效率；

• 避免创建冗余，重复和不需要的索引，索引个数的增加会给索引的维护增加很多不必要的性能损耗；

• 索引字段的默认值禁止设置为default null，因为null值不纳入索引计算，如果查询is null 将进行全表扫描，严重影响查询效率； 通常字符串默认值为"", 数字默认为0，特殊数字类型设置为-1；

• 尽可能使用唯一索引，这样提高查询效率；唯一索引（包括联合唯一索引）中的字段如果为null，可以在mysql中保存多条索引值相同的记录。即唯一性索引是允许多个 null 值的存在的。假如A字段为唯一索引，那么下面语句两次都会执行成功，不会抛出‘Duplicate key’ 的异常。
  insert into student (A) VALUES (NULL); // 插入成功
insert into student (A) VALUES (NULL); // 插入成功

• 如果逻辑不是整型自增类型，尽可能添加自增ID主键；

• 禁止使用外键；// 这个好多企业基本都有规范了；

• 在MySQL中，如果varchar字段特别长，MySQL仅对varchar的前n个字符做索引处理（ MyISAM：是1000 个字节；InnoDB引擎是前 767 字节）。

三、字段：

• create table所有的列都最好采用not null+ default value，避免数据库字段的值出现null；

• 禁止使用unsigned int/unsigned bigint，这些类型映射到java bean时，有越界的风险；

• 金额数字推荐使用bigint类型，单位是“分”；

• 严禁将tinyint/unsigned tinyint 映射到java的short/byte，否则不但省不了内存，反而会带来很多麻烦，一定要使用Integer；// 具体原因待分析

• alter table add column的时候，禁止新增字段有not null限制，如果线上运行的旧代码，还没有对改字段进行支持，特别是insert，那么会导致线上执行sql报错。

varchar的长度问题

MySql 5.0 之前的版本: n指的是n个字节，如果存放utf-8格式只能保存 (n / 3)个汉字，即如果varchar(20) 那么只能保存6个汉字；
MySql 5.0 之后的版本: n指的是n个字符，如果存放utf-8格式，那么无论是数字，字母还是汉字，都可存放n个，即如果varchar(20) 那么可以保存20个汉字（自己亲测可以）;
gbk类型：varchar(n)中每个字符最多占2个字节，最大长度不能超过(65535 / 2);
utf-8类型：varchar(n)中每个字符最多占3个字节，最大长度不能超过(65535 / 3);

关于n的大小，看到有人建议设置为8的倍数或者(2^k)-1大小，// 这些理论依据待补充。

• 针对访问频率低的字段，可以将冷字段放到单独的表中，实现垂直拆分，避免冷热数据混在一起的情况，提高效率；

• 按日期时间分表需符合YYYY[MM][DD][HH]格式。

• 尽量避免使用TEXT、BLOB类型，如果非用不可，则要将TEXT、BLOB类型字段拆到单独表中，即垂直拆分表;

• 针对非负整数，建议使用UNSIGNED类型存储，这样存储的范围更大。

• 使用UNSIGNED INT 存储IPV4（可以通过inet_ntoa和inet_aton函数进行转化），DECIMAL或者两个bigINT来存储IPV6（暂无处理函数）。

• varchar是不区分大小写的，针对大小写敏感的变长字符串，可以使用varbinary二进制字符类型存储；

• 尽量在表中添加create_time和modify_time 字段，modifyTime建议设置为：DEFAULT NOW() ON UPDATE CURRENT_TIMESTAMP，一般update_time要有索引；

备注：timestamp 类型的default CURRENT_TIMESTAMP

• 禁止在MySql中存文件、照片等。一般讲文件保存到文件系统中，mysql中仅保存文件的访问路径；

datetime 和 timestamp 的区别：

类型	占据字节	表示形式	表示范围	说明
datetime	8 字节	yyyy-mm-dd hh:mm:ss	'1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'	1.datetime以 8 个字节储存，不会进行时区的检索； 2.对于datetime来说，存什么拿到的就是什么； 3.如果存进去的是NULL， datetime会储存为NULL。
timestamp	4 字节	yyyy-mm-dd hh:mm:ss	'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'	1.timestamp 只占 4 个字节，而且是以utc的格式储存， 它会自动检索当前时区并进行转换。 2.时间范围只到2039年，需要特别注意业务场景； 3.对于timestamp来说，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。 4.如果存进去的是NULL，timestamp会自动储存当前时间

四、SQL书写：

• 禁止使用select * , 必须用具体的列名来代替，使用的话，必走索引反查，降低查询性能；

• 禁止在where条件中使用函数，否则会导致索引失效；

• 禁止使用子查询，原因？

• update/delete只允许但表操作，不允许进行多表链接，不允许使用子查询；

• 尽量避免使用delete语句，通常使用update set is_deleted = 1实现逻辑删除；

• insert语句要用具体的列名；

• 尽量避免使用or，可以使用用union或者union all进行合并。

• 尽量避免在sql中进行计算。

• 禁止使用 like ’%keyword%‘ 前缀模糊查询，会导致不走索引，全表扫描，这种情况可以使用全文索引，一般使用Elastic Search；

• update/delete尽量根据主键进行操作

• 尽量减少count（）的使用，

count(*) : 所有的列，相当于数据行数，在统计结果的时候，字段值为null的也计入统计；
count(1): 所有的列，相当于数据行数，在统计结果的时候，字段值为null的也计入统计；
count(列名)：只包括列名那一列，字段值为null时，不统计.
从上面原理可以理解：count(1)和count(*)基本无差别。
count性能由高到低：count(主键字段) > count(1) > count(非主键字段) > count(*)

• group by的时候，没有排序，可以加上order by null

• sql中禁止使用类型隐式转换，join中是用的字段类型一定要一样，理解inner join、left join、right join和outer join的区别

• 尽量不要在sql语句中指定索引类型，join的字段要有索引，使用mysql内部的优化策略。

• 尽量避免使用事务，如果使用事务，事务要简单，执行时间短，避免大事务；

• 尽量避免使用join或者多表联查查询，特别是大表更应该禁止；

• 尽量避免not in、<>、!=等负向查询，否则会导致索引失效；

• 理解索引的最左匹配原则

• limit实现分页，limit过大会影响效率，较大时可以配合子查询提高效率。

• 尽量使用union all替代union，union进行了去重和排序，union all的不进行排序和去重。

• 减少数据库访问次数，一次访问就会占用一次访问资源，比如insert可以插入多条数据，upsert语句避免了先查询后更新或者插入的问题（根据主键）；

• IN条件里面的数据数量不能太多，太多可以通过联查代替；

• 使⽤sql预编译，通过绑定变量实现一次解析，多次使用，同时降低SQL注入的风险；

• 通常禁止使用触发器、函数、视图、存储过程等；

• 避免一次更新太多数据，建议不超过100行；

• 禁止在线上数据库中执行时间长的sql；

• 禁止使⽤order by rand()；

• 禁⽌单条SQL更新多个表的数据。

• 尽量避免在区分度十分低的列上建立索引，如“性别”只有男、女两种值，"婚姻状况"只有未婚、已婚和离异等三种值。Oracle的位图索引适合在这种场景。

• 数据库连接池的选择优先级：tomcat-jdbc > druid > dbcp2 > dbcp1.4 > c3p0

五、Explain执行计划

<未完待续>