数据库军规63条

数据库军规概述

数据库性能优化的基本原则就是通过尽可能少的磁盘访问获得所需要的数据。一般从计算机硬件、关系系统与应用程序、数据库索引、SQL 语句、事务处理几个比较共性的方面 分析数据库性能优化的问题,从以上几个方面形成数据库性能优化的策略。当然实现优化的方法还 有很多, 要根据具体情况而定。对于不同的应用情况,我们应该具体情况具体分析, 各方面优 化措施综合运用, 以使数据库性能得到提高。数据库应用系统的性能是一项全民工程，开发团队的所有人都有责任为性能做贡献，树立性能意识，使之成为日常工作的习惯而不是单独成为某一阶段的工作，要未雨绸缪，不要寄希望于某一个环节的工作。

建库建表规约

若mysql数据库则必须使用InnoDB存储引擎

说明：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

必须使用UTF8字符集

说明：万国码，无需转码，无乱码风险，节省空间。

禁止存储大文件或者大照片

说明：大文件和照片存储在文件系统，数据库里存URI。

【表达是否字段数据类型】

表达是与否概念的字段，必须使用 is_xxx 的方式命名，mysql时数据类型是 unsigned tinyint （ 1 表示是，0 表示否）；oralce时数据类型是number(5) （ 1 表示是，0 表示否）。
说明：任何字段如果为非负数，必须是 unsigned。 正例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。

【表名、字段名】

表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
正例：getter_admin，task_config，level3_name
反例：GetterAdmin，taskConfig，level_3_name 。
表名最好是加上“业务名称_表的作用”。
正例：tiger_task / tiger_reader / mpp_config 。

2.6 【禁用保留字】
如 desc、range、match、delayed 等，请参考 MySQL 保留关键字https://dev.mysql.com/doc/refman/5.7/en/keywords.html;
Oracle保留关键字http://docs.oracle.com/cd/B10501_01/appdev.920/a42525/apb.htm 。
2.7 【索引名称】
主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。
说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。
2.8 【小数类型】
小数类型为 decimal，禁止使用 float 和 double。
说明：float 和 double 在存储的时候，存在精度损失的问题，很可能在值的比较时，得到不 正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数分开存储。
2.9 【定长字段用char】
如果存储的字符串长度几乎相等，使用 char 定长字符串类型。
说明：CHAR类型用来存储定长字符串；适用于：很短的字符串，或者所有的值都很接近同一长度。例如MD5存储的加密信息、uuid生成的主键。总结：对于经常变更的数据，CHAR比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列CHAR存储效率更高。

2.10 【可变长度字符串】
mysqlvarchar、oraclevarchar2 是可变长字符串，不预先分配存储空间，长度不要超过 4000，如果存储长 度大于此值，定义字段类型为 mysql text、oralce Blob，独立出来一张表，用主键来对应，避免影响其它字段索引效率。
2.11 【表必备三字段】
id, create_date, last_modified_date。其中 id 必为主键，类型为 unsigned bigint、单表时自增、步长为 1（oracle数据库可以为uuid）。create_date, last_modified_date 的类型均为 TIMESTAMP 类型。
说明：oralce数据库时可以利用触发器和队列实现主键自增，也可以为uuid,但uuid字符串索引性能较差（并发量大的时候），可以改进为将uuid字符串转换为2个uint串。
2.12 【库名】
库名与项目应用名称尽量一致。
说明：不强制。
2.13 【注释更新】
如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
2.14 【字段适当冗余】
字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：
1）不是频繁修改的字段。
2）不是 varchar 超长字段，更不能是 text 字段。 正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储类目名称，避免关联查询。
2.15 【分库分表】
单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。
2.16 【合适的字段长度选择】
合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。 正例：如下表，其中无符号值可以避免误存负数，且扩大了表示范围。
说明
对象 年龄 区间 类型 表示范围
人 150 岁之内 unsigned tinyint 无符号值 0 到 255
龟 数百岁 Unsigned smallint 无符号值 0 到 65535
恐龙化石 数千万年 unsigned int 无符号值 0 到42.9 亿
太阳 约50亿年 unsigned bigint 无符号值 0 到约 10 的 19 次方

2.17 【必须设主键】
表必须有主键，例如自增主键
解读：
a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率
c） 无主键的表删除，在row模式的主从架构，会导致备库夯住
2.18 【禁止使用外键】
如果有外键完整性约束，需要应用程序控制
解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先

3 索引规约
3.1 【唯一特性的字段建立索引】
业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。
3.2 【join关联】
尽量不要用超过三个表 join。需要 join 的字段，数据类型必须绝对一致；多表关联查询 时，保证被关联的字段需要有索引。
说明：即使双表 join 也要注意表索引、SQL 性能。
3.3 【varchar 字段建立索引】
在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度即可。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分 度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count()的区分度 来确定。
3.4 【检索】
如果系统中使用了搜索引擎则页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决；如果系统中没有使用搜索引擎则尽量使用右模糊查询不要使用左模糊和全模糊。
说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
3.5 【索引的有序性】
如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。 正例：where a=? and b=? order by c; 索引：a_b_c 反例：索引中有范围查找，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。
说明：filesort是通过相应的排序算法,将取得的数据在内存中进行排序, 双路排序\单路排序
3.6 【覆盖索引】
利用覆盖索引来进行查询操作，避免回表。 说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览 一下就好，这个目录就是起到覆盖索引的作用。 正例：能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是一种查询的一种 效果，用 explain 的结果，extra 列会出现：using index。
3.7 【超多分页优化】
利用延迟关联或者子查询优化超多分页场景。 说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过 特定阈值的页数进行 SQL 改写。 正例：先快速定位需要获取的 id 段，然后再关联： SELECT a. FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
3.8 【SQL 性能优化的目标】
至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。 说明： 1）consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。 2）ref 指的是使用普通的索引（normal index）。 3）range 对索引进行范围检索。 反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级 别比较 range 还低，与全表扫描是小巫见大巫。
3.9 【组合索引的顺序】
建组合索引的时候，区分度最高的在最左边。
说明：正例：如果 where a=? and b=? ，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即 可。 说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>? and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。
3.10 【禁用隐式转换】
防止因字段类型不同造成的隐式转换，导致索引失效。
说明：
(1)例如数据库中age =’13’ 字段为varchar,且该字段建立了索引，where age=’15’ 启用了索引，where age=14 索引失效。
(2)隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果非常严重。将数据库拖死，继而整个系统崩溃，对于大规模系统损失惨重。
(3)MySQL 的隐式类型转换原则：

• 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
• 两个参数都是字符串，会按照字符串来比较，不做类型转换
• 两个参数都是整数，按照整数来比较，不做类型转换
• 十六进制的值和非数字做比较时，会被当做二进制串，和数字做比较时会按下面的规则处理
• 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
• 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
• 所有其他情况下，两个参数都会被转换为浮点数再进行比较
  注意一个安全问题：假如 password 类型为字符串，查询条件为 int 0 则会匹配上。
  由于 MySQL 隐式类型转换规则比较复杂，依赖 MySQL 隐式转换很容易出现各种想想不到的问题，而且 MySQL 隐式类型转换本身也是非常耗费 MySQL 服务器性能的，所以非常不推荐这样使用。
  3.11 【索引误区】
  创建索引时避免有如下极端误解：
  1）宁滥勿缺。误认为一个查询就需要建一个索引。
  2）宁缺勿滥。误认为索引会消耗空间、严重拖慢更新和新增速度。
  3）抵制惟一索引。误认为业务的惟一性一律需要在应用层通过“先查询后新增”方式解决。
  4 SQL 语句
  4.1 【count 聚合函数】
  不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的 标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。 说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
  4.2 【count/distinct函数】
  count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。

4.3 【避免 sum NPE问题】
当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为 NULL，因此使用 sum()时需注意 NPE 问题。 正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IF(ISNULL(SUM(g)),0,SUM(g)) FROM table;
4.4 【NULL值的比较】
使用 ISNULL()来判断是否为 NULL 值。注意：NULL 与任何值的直接比较都为 NULL。
说明： 1） NULL<>NULL 的返回结果是 NULL，而不是 false。 2） NULL=NULL 的返回结果是 NULL，而不是 true。 3） NULL<>1 的返回结果是 NULL，而不是 true。
4.5 【分页sql语句】
在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
说明：先执行count查询，若为0则直接返回，不为0执行分页查询。
4.6 【禁用存储过程】
禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
4.7 【避免使用in】
in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控 制在 1000 个之内。
4.8 【编码问题】
如果有全球化需要，所有的字符存储与表示，均以 (utf-8mb4)utf-8 编码，注意字符统计函数 的区别。
说明：
SELECT LENGTH(“轻松工作”)； 返回为 12 ；
SELECT CHARACTER_LENGTH(“轻松工作”)； 返回为 4 ；
如果要使用表情，那么使用 utfmb4 来进行存储，注意它与 utf-8 编码的区别。
说明： MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等。
4.9 【删除表】
TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。
说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
5 ORM 映射
5.1 【禁止使用*】
在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
说明：1）增加查询分析器解析成本。2）增减字段容易与 resultMap 配置不一致。
5.2 【关于boolean属性名称is】
POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行 字段与属性之间的映射。
说明：参见定义 POJO 类以及数据库字段定义规定，在中增加映射，是必须的。 在 MyBatis Generator 生成的代码中，需要进行对应的修改。
5.3 【#{}与KaTeX parse error: Expected 'EOF', got '#' at position 20: …sql.xml 配置参数使用：#̲{}， #param# 尽量不…{} 此种方式容易出现 SQL 注入。MyBatis排序时使用order by 动态参数时需要注意，用KaTeX parse error: Expected 'EOF', got '#' at position 4: 而不是#̲ ，用于传入数据库对象，例如传…

5.4 【查询结果集避免使用HashMap/Hashtable】
尽量避免直接拿 HashMap 与 Hashtable 作为查询结果集的输出。 说明：resultClass=”Hashtable”，会置入字段名和属性值，但是值的类型不可控。
说明：做独立的页面展示且没有业务操作市可以选择使用。
5.5 【更新时间】
更新数据表记录时，必须同时更新记录对应的 update_time 字段值为当前时间。
5.6 【update操作】
不要写一个大而全的数据更新接口，传入为 POJO 类，不管是不是自己的目标更新字 段，都进行 update table set c1=value1,c2=value2,c3=value3; 这是不对的。执行 SQL 时，不要更新无改动的字段，一是易出错；二是效率低；三是增加 binlog 存储。
说明：更新操作，如果差异较大时建议单独写sqlmap
5.7 【谨慎使用@Transactional】
@Transactional 事务不要滥用。事务会影响数据库的 QPS，另外使用事务的地方需 要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。
5.8 【值对比】
中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时带 上此条件；表示不为空且不为 null 时执行；表示不为 null 值时 执行。
6 Sql 调优
6.1 【explain】
通过explain+sql 查看执行计划，从explain extend+sql的输出中，我们可以
看到sql的执行方式，对于分析sql有一定的帮助。
6.2 【COUNT与LIMIT】
如果明确知道只有一条结果返回，limit 1能够提高效率。
（1）COUNT(*) 计算满足条件的记录行数，COUNT(col)计算满足条件的且col非空的记录行数
（2）LIMTI n 在满足条件的记录中查询n条，发现n条后停止扫描
需要判断记录是否存在时可以用LIMIT取代COUNT，LIMIT 1 找到记录就会停止。
6.3 【尽量使用缓冲】
数据库服务器每次在收到客户端发来SQL后，都会执行这条SQL语句。但当在一定间隔内（如1分钟内），接到完全一样的 SQL语句，也同样执行它。虽然这样可以保证数据的实时性，但在大多数时候，数据并不要求完全的实时，也就是说可以有一定的延时。如果是这样的话，在短时 间内执行完全一样的SQL就有些得不偿失
SELECT * from TABLE1
SELECT * FROM TABLE1
两条SQL语句对于查询缓冲是完全不同的SELECT。而且查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格（因为，查询缓冲并不自动截取首尾空格）
6.4 【Sql 执行顺序】（重点掌握）

6.5 【sql避免使用的关键字】
避免使用!=(或<>)、IS NULL 或IS NOT NULL、IN、NOT IN等这样的操作符，避免在WHERE 子句中使用非聚合表达式。这些操作符会使系统无法使用索引，而只能直接搜 索表中的数据。例如，SELECT id，name FROM employee WHERE id!=B% 优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行
6.6 【避免使用OR，用UNION 代替】
OR 语句的执行原理并不是利用列上的索引根据每 个语句分别查找再将结果求并集，而是先取出满足每个OR 子句的行，存入临时数据库的 工作表中，再建立唯一索引以去掉重复行，最后从这个临时表中计算结果。这样使用可能 造成索引失效，导致顺序扫描整个表，大大降低查询效率。
6.7 【连接查询】
在执行连接前对关系作适当的预处理，预处理的方法有两种，在连接属性上建立索引和对关系进行排序。
6.8 【避免使用相关子查询】
应避免使用相关子查询，把子查询转换成联结来实现。对于主查询的每一条记录子 查询都要执行一次，嵌套的层次越多效率越低。避免对子句使用数学运算符。即不要对数 据表的属性列进行操作。SQL 概念上将位于WHERE 子句中的相关子查询，处理成获取参 数并且返回一个单独的值或值的集合的函数。因为子查询要对应位于外层查询的每一个元组 进行单独的计算。从而导致大量的随机磁盘I/O 操作。所以在实际应用中若可以用连接代替的子查询，则用连接实现。
6.9 【覆盖索引】
MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件 包含所有满足查询需要的数据的索引称为 覆盖索引（Covering Index） 如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降
6.10 【Using filesort】
Sql 的恶魔之一Extra列会出现“Using filesort ”，尽量避免出现“Using filesort ”。
说明：
用Explain分析SQL语句的时候，经常发现有的语句在Extra列会出现Using filesort，根据MySQL官方文档对他的描述：
MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.
Using filesort 是Mysql里一种速度比较慢的外部排序，如果能避免是最好的了，很多时候，我们可以通过优化索引来尽量避免出现Using filesort，从而提高速度。
6.11 【为order by使用索引】
主要是解决Order by带来Using filesort的问题。

6.12 【Exists与In的区别】
（1） exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条 件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个boolean条件，当能返回结果集则为true，不能返回结果集则为 false。not exists与exists 相反。
（2） in查询相当于多个or条件的叠加，in查询就是先将子查询条件的记录全都查出来，假设结果集为B，共有m条记录，然后在将子查询条件的结果集分解成m个，再进行m次查询。not in与in相反。
（3）比较：
mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。
select * from A where cc in (select cc from B)
select * from A where exists(select cc from B where cc=A.cc)
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
in 与 =的性能是一样的。
6.13 【避免索引失效】
尽量避免索引失效、索引失效的几大原因（来自于简书的大神总结）
• 1）、全值匹配我最爱
建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。
• 2）、最佳左前缀法则
如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，不跳过索引中间的列。（带头大哥不能死，中间兄弟不能丢）
• 3、不再索引列上做任何操作(计算、函数、（自动or手动）类型转换)，会导致索引失效而转向全表扫描
• 4、存储引擎不能使用索引中范围条件右边的列。（范围之后全失效）
若中间索引列用到了范围（>、<、like等），则后面的所以全失效。*
• 5、尽量使用覆盖索引（只访问索引的查询(索引列和查询列一致)），减少select*
• 6、Mysql在使用不等于(!=、<>)或like的左模糊的时候无法试用索引会导致全表扫描。
• 7、IS NULL和IS NOT NULL也无法使用索引
• 8、字符串不加单引号索引失效
• 9、少用or，用它来连接时索引会失效

6.14 【优化多表join】
 减少Join语句的次数，用小结果集驱动大结果集
 保证Join语句中被驱动表上Join条件字段已经被索引
 若为左连接，则把索引加到第二张表上的连接字段。
 若为右连接，则把索引加到第一张表的连接字段
6.15 【用like进行模糊查询时应注意】
 select*from contact where username like ‘%yue%’
 关键词%yue%，由于yue前面用到了“%”，因此该查询必然走全表扫描，除非必要，否则不要在关键词前加%
6.16 【避免隐式转换】
 强制类型转换会全表扫描 select * from user where phone=13800001234不会命中phone索引。
6.17 【允许为null的列，查询有潜在大坑】
 单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集
 select * from user where name != ‘shenjian’ 如果name允许为null，索引不存储null值，结果集中不会包含这些记录。所以，请使用not null约束以及默认值。