数据库建表SQL索引那些规范，整理好了建议直接打印收藏用

数据库建表SQL索引那些规范,整理好了建议直接收藏

表设计规范

1. 表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。 这是因为MySQL在Linux下默认是大小写敏感的，而在Windows下不敏感，使用统一小写可以避免因操作系统不同导致的问题，字段名也不要用大写，看起来不舒服，数字开头和双下划线中间的数字是数据库保留的，用了可能会出奇怪的错误。
2. 表名不使用复数名词。 表名应该仅仅表示表里面的实体内容，不应该表示实体数量，应该叫 user，而不是 users。
3. 表必备三字段：id, create_time, update_time。 id 必须是主键，类型为 bigint unsigned 或者自增的整数，单表时自增，分布式场景下用雪花算法等，create_time 和 update_time 建议使用 datetime 类型，分别记录数据的创建时间和最后更新时间，阿里巴巴《Java开发手册》中强制要求这点，便于追踪数据。
4. 表的命名最好是遵循“业务名称_表的作用”的格式。 trade_order（交易订单表）、forum_comment（论坛评论表），这样一看就知道是哪个业务模块的表。
5. 字段允许适当冗余，以提高查询性能，但必须是频繁查询的字段，且基本不会修改的字段。 比如商品类目名称，在商品表里冗余存储类目名称，就不用每次查商品详情都去关联类目表了，但是要注意数据一致性，如果类目名改了，所有冗余这个类目名的商品记录都要更新，这个度要把握好，优先考虑不是频繁联合查询的字段，就不建议冗余了。
6. 单表字段数目不要太多，建议尽量少于30个。 字段太多，说明表设计得可能不够合理，耦合度太高，可以考虑拆分成多个表。
7. 选择合适的字段类型，在满足需求的前提下，尽量使用存储空间小的类型。 比如数字类型的优先级是：tinyint > smallint > mediumint > int > bigint，能用 int 就不要用 bigint，字符串类型，如果长度固定就用 char，比如身份证号用 char(18)；长度变化就用 varchar，但也要设定一个合理的长度上限，不要动不动就设成5000，过大的字段长度不仅浪费空间，还会影响查询性能。
8. 金额相关的字段，推荐使用 decimal 类型，禁止使用 float 和 double。 因为 float 和 double 是浮点数，存在精度损失的问题，在进行金额计算时可能导致结果不准确，decimal 是定点数，能精确存储和计算，比如定义为 decimal(10,2)，表示总共10位数，小数点后占2位。
9. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。 不要以为应用层做了校验就万无一失了，因为很难保证没有并发的情况，而且数据库层面的唯一索引是最可靠的约束，比如用户登录名、手机号、邮箱等。
10. 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1表示是，0表示否）。 is_deleted（是否删除）、is_valid（是否有效），阿里巴巴《Java开发手册》中强制要求这点，这样命名一目了然。
11. varchar 是可变长字符串，不预先分配存储空间，长度不要超过5000字符。 如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段的索引效率。

索引设计规范

1. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。 这点在表设计规范里提过，因为太重要了，在索引这里再强调一遍，它可以防止脏数据的产生。
2. 超过三个表禁止 join。 需要 join 的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引，表越多，join 的效率越低，而且对数据库的性能冲击很大，阿里巴巴《Java开发手册》中强制要求最多三表 join，如果业务复杂，应该在应用层分步查询，或者做适当的冗余。
3. 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。 比如地址表，有个 address 字段，前10个字符的区分度可能已经很高了，就没必要对整个地址建索引，可以建一个索引长度是10的索引，索引的长度越小，索引占用的空间就越小，一页（数据库存储单位）中能放下的索引值就越多，查询效率也就会更高，区分度的计算方法是 count(distinct left(列名， 索引长度)) / count(*)。
4. 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。 where name like ‘%张’ 或者 where name like ‘%张%’，这类查询是无法使用索引的，会导致全表扫描，性能极差，搜索引擎如 Elasticsearch 就是专门干这个的。
5. 如果有 order by 的场景，请注意利用索引的有序性。 order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能，比如有查询条件 where a=? and b=? order by c，那么可以建立联合索引 idx_a_b_c，这样数据库在找到 a 和 b 匹配的数据后，这些数据本身就是按照 c 排好序的，直接返回就行了。
6. 利用覆盖索引来进行查询操作，避免回表。 覆盖索引是指一个索引包含了所有需要查询的字段的值，比如你有一个用户表，主键是 id，你在 username 上建了索引，如果你只查询 select id, username from user where username = ‘xxx’，那么只需要在 username 的索引树上就能找到全部信息，不用再根据 id 去主键索引里查了，这就叫覆盖索引，效率很高。
7. 利用延迟关联或者子查询优化超多分页场景。 MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回N行，当 offset 特别大的时候，效率就非常低下，优化思路是：先通过覆盖索引快速拿到需要的数据行的主键ID，再根据这些主键ID去原表查询所需要的行，select * from table_name inner join (select id from table_name where condition order by id limit 1000000, 10) as tmp using(id)。
8. SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。 这是指 explain 执行计划中的 type 字段，consts 是直接通过主键或者唯一索引一次就找到了；ref 是使用了普通索引；range 是索引范围扫描，比 range 差的还有 index（全索引扫描）和 ALL（全表扫描），这些都是要尽量避免的。
9. 建组合索引的时候，区分度最高的字段放在最左边。 这是因为索引匹配是从最左列开始的，比如有 (a, b, c) 这个联合索引，查询条件用 where a = 1 and b = 2 就可以用到索引，但 where b = 2 就用不到，把区分度高的放左边，能更快地过滤掉大部分数据。
10. 防止因字段类型不同造成的隐式转换，导致索引失效。 比如你给一个 varchar 类型的字段 phone 建了索引，但查询时写了 where phone = 13800138000（数字），这时候数据库会对 phone 字段做隐式的类型转换，相当于用了函数，就会导致索引失效，所以查询条件里的类型要和字段定义的类型一致。

SQL编写规范

1. *不要使用 count(列名) 或 count(常量) 来替代 count()。* count() 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 也无关，count() 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行，所以如果你要统计的是表的总行数，就用 count()。
2. count(distinct col) 计算该列除 NULL 之外的不重复行数。 注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为0。
3. 当某一列的值全为 NULL 时，count(col) 的返回结果为 0，但 sum(col) 的返回结果为 NULL，因此使用 sum() 时需注意 NPE（空指针）问题。 可以使用 select ifnull(sum(column_name), 0) ... 来避免返回 NULL。
4. 使用 ISNULL() 来判断是否为 NULL 值。 因为 NULL 与任何值的直接比较都为 NULL，NULL = NULL 的结果是 NULL，而不是 true，用 ISNULL(col) 来判断。
5. 代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。 这是一个小小的性能优化。
6. 不得使用外键与级联，一切外键概念必须在应用层解决。 外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度，阿里巴巴《Java开发手册》中强制要求这点，认为这是数据库服务器的事情，应该由应用服务器来保证数据完整性。
7. 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。 复杂的逻辑应该在应用层用代码实现。
8. 数据订正（特别是删除、修改记录操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句。 这是个好习惯，尤其是在生产环境。