数据库表设计（二）：索引规范和SQL语句规范

一、索引规范

1.索引名称规范

（1）索引名称必须使用小写。

主键索引名为pk_字段名，唯一索引名为uk_字段名，普通索引名则为idx_字段名。d

pk_即primary key，uk_即unique key，idx_即index的简称。
1

（2）索引字段不能为空

（3）组成索引中的字段数建议不超过5个。单张表的索引数量控制在5个以内。

2.索引创建规范

（1）重要的SQL必须被索引

• UPDATE、DELETE语句的WHERE条件列
• ORDER BY、GROUP BY、DISTINCT的字段
• 多表JOIN的字段

UPDATE、DELETE语句不使用LIMIT

（2）ORDER BY，GROUP BY，DISTINCT的字段需要添加在组合索引的后面。

如果有order by的场景，请注意利用索引的有序性。order by最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。

正例：where a=? and b=? order by c索引为：a_b_c
反例： 索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b， 索引a_b无法排序。
1
2

（3）建组合索引的时候，区分度最高的在最左边。

正例： 如果where a=? and b=?，a列的几乎接近于唯一值，那么只需要单建idx_a索引即可。
说明： 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。
如：where a>? and b=?那么即使a的区分度更高，也必须把b放在索引的最前列。
1
2
3

（4）选择区分度大的列建立索引，组合索引中，区分度大的字段放在最前；

（5）不在低基数列上建立单独索引

例如“状态”，“性别“只有男、女两种；可以建立联合索引，应该将低基数列放在后面；

（6）业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但会明显提高查找速度；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，就必然有脏数据产生。

（7）主键的选择要慎重 【 强烈建议】

• 首选使用非空的唯一键， 其次选择自增列或发号器
• 不使用更新频繁的列，尽量不选择字符串列，不使用UUID MD5 HASH、

（8）唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。

（9）在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。

对过长的varchar字段建议优先考虑前缀索引，前缀索引长度不超过12个字符;

什么是前缀索引？
前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。
为什么要用前缀索引？
前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引(索引会失效,group by 是去重)，也不能把它们用于覆盖索引。
什么情况下适合使用前缀索引？
当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。

（10）页面搜索严禁左模糊或者全模糊

如果需要，那么请通过搜索引擎来解决。不建议使用%前缀模糊查询，例如LIKE “%weibo”。

（11）唯一键不和主键重复。

（12）使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，UsingTemporary。

二、SQL语言编码规范

1.SQL名称规范

（1）所有关键字必须大写

如：INSERT、UPDATE、DELETE、SELECT及其子句，IF……ELSE、CASE、DECLARE等。

（2）所有函数及其参数中除用户变量以外的部分必须大写。

（3）在定义变量时用到的数据类型必须小写。

（4）对于数据库中表记录的查询和变更，只要涉及多个表，就需要在列名前加表的别名（或表名）进行限定。

说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在，就会抛异常。

正例：select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;
反例：如果没加别名限制，在另一个表中增加了一个同名字段
线上查询可能会导致1052异常：Column 'name' in field list is ambiguous。
1
2
3

（5）SQL语句中表的别名前加as，并且以t1、t2、t3……的顺序依次命名。

说明：1）别名可以是表的简称，或者是表在SQL语句中出现的顺序，以t1、t2、t3……的方式依次命名。2）在别名前加as可使别名更容易被识别。

2.SQL语句规范

(1）超过三个表禁止join。

需要join的字段，数据类型必须绝对一致；当多表关联查询时，保证被关联的字段需要有索引。
说明：即使双表join，也要注意表索引、SQL性能。

(2)不要使用count(列名)或count(常量)来替代count(*)

count(*)是SQL92定义的标准统计行数的语法，与数据库无关，与NULL和非NULL无关。

不要使用count(列名)或count(常量)来替代count()，count()是SQL92定义的标准统计行数的语法，与数据库无关，与NULL和非NULL无关。

count(distinct column) 计算该列除NULL外的不重复行数。注意，count(distinct column1, column2)，如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。
当某一列的值全为NULL时，count(column)的返回结果为0，但sum(column)的返回结果为NULL，因此使用sum()时需注意避免NPE问题。

正例：可以使用如下方式避免sum的NPE问题：SELECT IFNULL(SUM (column), 0) FROM table;
1

(3)使用ISNULL()判断是否为NULL值。

说明：NULL与任何值的直接比较都为NULL。

1）NULL<>NULL的返回结果是NULL，而不是false。
2）NULL=NULL的返回结果是NULL，而不是true。
3）NULL<>1的返回结果是NULL，而不是true。

(4)WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。

尽量不要使用!=，大部分情况会跳过索引

正例：where id < 7 and id > 8
反例：where id != 7 and id !=8
1
2

尽量不要使用OR、IN运算符，大部分情况会跳过索引

in操作能避免则避免，若实在避免不了，则需要仔细评估in后面的集合元素数量，控制在1000之内。
可使用UNION ALL代替OR、JOIN或EXISTS代替IN

尽量不要使用IS NULL和IS NOT NULL，大部分情况会跳过索引

可使用范围查询或存入-1代替NULL

3.SQL操作规范

（1）当订正数据（特别是删除或修改记录操作）时，要先select，避免出现误删除，确认无误才能执行更新语句

（2）利用覆盖索引进行查询操作，避免回表。

说明：如果想知道一本书的第11章是什么标题，我们有必要翻开第11章对应的那一页吗？只要浏览一下目录就好，这个目录就起到覆盖索引的作用。

正例：能够建立索引的种类分为主键索引、唯一索引、普通索引3种，而覆盖索引只是查询的一种效果
用explain的结果，extra列会出现“using index”。
1
2

3.ORM规范

（1）在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。

1）增加查询分析器解析成本。
2）增减字段容易与resultMap配置不一致。
3）多余字段增加网络开销，尤其是text类型的字段。

（2）POJO类的布尔属性不能加is，而数据库字段必须加is_，要求在resultMap中进行字段与属性之间的映射。

（3）不要用resultClass作为返回参数，即使所有类属性名与数据库字段一一对应，也需要定义；反过来，每个表也必然有一个与之对应。

（4）sql.xml配置参数使用：#{}，#param#，不要使用${}，此种方式容易出现SQL注入。

（5）不允许直接将HashMap与Hashtable作为查询结果集的输出。

反例：某工程师为避免写一个xxx ，直接使用HashTable接收数据库返回结果
结果由于数据库版本不一样，出现日常把bigint转成Long值，而线上把bigint解析成BigInteger的现象，导致线上出现问题。
1
2

(6)不要写一个大而全的数据更新接口。

传入为POJO类，不管是不是自己的目标更新字段都进行update table set c1=value1,c2=value2,c3=value3; 是不对的。当执行SQL时，不要更新无改动的字段，一是容易出错；二是效率低；三是增加binlog存储。