数据库规范与SQL调优
数据库设计规范章节，依旧以《阿里巴巴Java开发手册》为原型进行修正和完善。
MySQL规约
(一) 建表规约
(二) 索引规约
(三) SQL规约
(四) ORM规约
(一) 建表规约
1. 【强制】 表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是
unsignedtinyint（
1表示是，0表示否），此规则同样适用于odps建表。
说明：任何字段如果为非负数，必须是unsigned。
2. 【强制】 表名、字段名必须使用小写字母或数字；禁止出现数字开头，禁止两个下划
线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名
称需要慎重考虑。
正例： getter_admin ， task_config ， level3_name
反例： GetterAdmin ， taskConfig ， level_3_name
3. 【强制】 表名不使用复数名词。
说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于DO类名
也是单数形式，符合表达习惯。
4. 【强制】 禁用保留字，如desc、range、match、delayed等，请参考MySQL官方保留
字。
5. 【强制】 唯一索引名为uk 字段名；普通索引名则为idx 字段名。
说明：uk 即 unique key；idx 即index的简称。
6. 【强制】 小数类型为decimal，禁止使用float和double。
说明：float和double在存储的时候，存在精度损失的问题，很可能在值的比较时，得
到不正确的结果。如果存储的数据范围超过decimal的范围，建议将数据拆成整数和
小数分开存储。
7. 【强制】 如果存储的字符串长度几乎相等，使用char定长字符串类型。
8. 【强制】 varchar是可变长字符串，不预先分配存储空间，长度不要超过5000，如果
存储长度大于此值，定义字段类型为text，独立出来一张表，用主键来对应，避免影响
其它字段索引效率。
9. 【强制】 表必备字段：id， creation_time，creator，modified_time，modifier，valid。
说明：其中id必为主键，类型为unsigned bigint、单表时自增、步长为1。
gmt_create,gmt_modified的类型均为date_time类型。
10. 【强制⭐】 所有表必须有主键，主键必须保证有序。Mysql的锁，锁的是主键索引树，没主
键会导致所有操作都是锁全表。而主键无序，会导致频繁页分裂，大大降低更新速度。
11. 【推荐】 表的命名最好是加上“业务名称_表的作用”。
正例： tiger_task / tiger_reader / mpp_config
12. 【推荐】 库名与应用名称尽量一致。
13. 【推荐】 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。
14. 【推荐】 字段允许适当冗余，以提高性能，但是必须考虑数据同步的情况。冗余字段
应遵循：
不是频繁修改的字段。
不是varchar超长字段，更不能是text字段。
正例：商品类目名称使用频率高，字段长度短，名称基本一成不变，可在相关联的表中冗余存储
类目名称，避免关联查询。
15. 【推荐】一般情况下， 单表行数超过500万行或者单表容量超过2GB，才推荐进行分
库分表。
如果某张表字段特别多，或者字段比较大，则需要提前进行。按照以前的经验，
110个字段的表，在数据量超过100万时，已经会比较严重地拖慢整体性能了。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分
表。
16.【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是
提升检索速度。
1 正例：人的年龄用 unsigned tinyint （表示范围 0-255 ，人的寿命不会超过 255 岁）；海龟就
必须是 smallint ，但如果是太阳的年龄，就必须是 int ；如果是所有恒星的年龄都加起来，那么
就必须使用 bigint 。 (二) 索引规约
1. 【强制】 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速
度是明显的；另外，即使在应用层做了非常完善的校验和控制，只要没有唯一索引，
根据墨菲定律，必然有脏数据产生。
2. 【强制】 超过三个表禁止join。需要join的字段，数据类型保持绝对一致；多表关联查
询时，保证被关联的字段需要有索引。
说明：即使双表join也要注意表索引、SQL性能。
3. 【强制】 在varchar字段上建立索引时，必须指定索引长度，没必要对全字段建立索
引，根据实际文本区分度（离散度）决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索
引，区分度会高达90%以上，可以使用count(distinct left(列名,索引长度))/count(*)
的区分度来确定。
4. 【强制】 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
说明：索引文件具有B-Tree的最左前缀匹配特性，如果左边的值未确定，那么无法
使用此索引。
5. 【强制】 对于联合索引左边第一个能够覆盖到的索引列，不要再单独添加索引。
6.【强制】创建索引的列，不要有Null值，查询Null值无法走索引。
7. 【推荐】 如果有order by的场景，请注意利用索引的有序性。order by最后的字段是
组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查
询性能。
1 正例： where a=? and b=? order by c; 索引： a_b_c
2 反例：索引中有范围查找，那么索引有序性无法利用，如： WHERE a>10 ORDER BY b; 索引 a_
b 无法排序。
8. 【推荐】 利用覆盖索引来进行查询操作，来避免回表操作。
说明：如果一本书需要知道第11章是什么标题，会翻开第11章对应的那一页吗？目
录浏览一下就好，这个目录就是起到覆盖索引的作用。
1 正例：能够建立索引的种类：主键索引、唯一索引、普通索引，而覆盖索引是一种查询的一种效
果，用 explain 的结果， extra 列会出现： using index 。
9. 【推荐】 利用延迟关联或者子查询优化超多分页场景。 说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，
返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，
要么对超过特定阈值的页数进行SQL改写。
10. 【推荐】 SQL性能优化的目标：至少要达到 range级别，要求是ref级别，如果可以
是consts最好。
说明：
1）consts单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取
到数据。
2）ref指的是使用普通的索引（
normal index）。
3）range对索引进行范围检索。
11. 【推荐】 建组合索引的时候，区分度最高的在最左边（最左匹配原则）。
12.【参考】创建索引时避免有如下极端误解：
误认为一个查询就需要建一个索引。
误认为索引会消耗空间、严重拖慢更新和新增速度。
误认为唯一索引一律需要在应用层通过“先查后插”方式解决。
误认为索引建了就一定会走索引。
误认为索引一定能提高查询效率。
(三) SQL规约
1. 【强制】 不要使用count(列名)或count(常量)来替代count（
*） ，count（
*） 就是
SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的
行。
2. 【强制】 count(distinct column)计算该列除NULL之外的不重复数量。注意
count(distinct column1, column2)如果其中一列全为NULL，那么即使另一列有不同的
值，也返回为0。
1 正例：先快速定位需要获取的 id 段，然后再关联：
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b wh
ere a.id=b.id
2
反例： explain 表的结果， type=index ，索引物理文件全扫描，速度非常慢，这个 index 级别
比较 range 还低，与全表扫描是小巫见大巫。
1
1 正例：如果 where a=? and b=? ， a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如： wherea
>?and b=? 那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。
2 3. 【强制】 当某一列的值全是NULL时，count(column)的返回结果为0，但
sum(column)的返回结果为NULL，因此使用sum(column)时需注意NPE问题。
1 正例：可以使用如下方式来避免 sum 的 NPE 问题： SELECT IF(ISNULL(SUM(g)),0,SUM(g))
FROM table;
4. 【强制】 使用ISNULL()来判断是否为NULL值。注意：NULL与任何值的直接比较都为
NULL。
说明：
1） NULL<>NULL的返回结果是NULL，而不是false。
2） NULL=NULL的返回结果是NULL，而不是true。
3） NULL<>1的返回结果是NULL，而不是true。
5. 【强制】 在代码中写分页查询逻辑时，若count为0应直接返回，避免执行后面的分页
语句。
6.【强制⭐】不得使用外键与级联，一切外键概念必须在应用层解决。
说明：（概念解释）学生表中的student_id是主键，那么成绩表中的student_id则为
外键。如果更新学生表中的student_id，同时触发成绩表中的student_id更新，则为
级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更
新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。
7. 【强制】 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。
8. 【强制】 数据订正时，删除和修改记录时，要先select，避免出现误删除，确认无误
才能执行更新语句。
9. 【强制】 删除数据时，禁止“delete from Table where Condition”。必须先查询出待删除的
数据，然后根据数据ID删除。程序如果没有传入Condition的条件，最终结果不是不删除数据，
而是删除所有数据，必须避免。
10. 【推荐】 in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数
量，控制在1000个之内。
11.【参考】如果有全球化需要，所有的字符存储与表示，均以utf-8编码，那么字符计
数方法
注意：
说明：
SELECT LENGTH("轻松工作")；返回为12
SELECT CHARACTER_LENGTH("轻松工作")；返回为4
如果要使用表情，那么使用utfmb4来进行存储，注意它与utf-8编码的区别。 12.【参考】TRUNCATETABLE比 DELETE速度快，且使用的系统和事务日志资源少，
但TRUNCATE无事务且不触发trigger，有可能造成事故，故不建议在开发代码中使用此
语句。
说明：TRUNCATE TABLE在功能上与不带 WHERE子句的 DELETE语句相同。
(四) ORM规约
1. 【强制】 在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确
写明。
说明：
1）增加查询分析器解析成本。
2）增减字段容易与resultMap配置不一致。
2. 【强制】 POJO类的boolean属性不能加is，而数据库字段必须加is_，要求在
resultMap中进行字段与属性之间的映射。
说明：参见定义POJO类以及数据库字段定义规定，在sql.xml增加映射，是必须的。
3. 【强制】 不要用resultClass当返回参数，即使所有类属性名与数据库字段一一对应，
也需要定义；反过来，每一个表也必然有一个与之对应。
说明：配置映射关系，使字段与DO类解耦，方便维护。
4. 【强制】 xml配置中参数注意使用：#{}，#param#不要使用${}此种方式容易出现SQL
注入。
5. 【强制】 iBATIS自带的queryForList(String statementName,int start,int size)不推荐
使用（它是基于内存的分页方式，存在深分页问题）。
说明：其实现方式是在数据库取到statementName对应的SQL语句的所有记录，再
通过subList取start,size的子集合，线上因为这个原因曾经出现过OOM。
1 正例：在 sqlmap.xml 中引入 #start#, #size#
2 Map
识别慢SQL 目前使用腾讯云数据库，它们提供了数据库监控以及慢日志监控等功能，可以直接拿到慢 SQL。接下来按并发度对这些SQL进行分类：
1，并发非常高。SQL特征：SQL的条数很少(按5%统计)，但是执行频率非常高，甚至达到
每秒上百次，只要一慢，系统很可能瘫痪。
优化级别：最优先处理。
优化方向：
对SQL本身进行优化，调到最优。
对发起SQL请求的应用进行优化，减少执行次数。包括使用缓存，多次请求合并为一次
等等。
2，并发一般 。SQL特征：占大多数(按80%统计)，如果有慢的，对系统整体稳定性影响不
大，但是会造成局部的某些操作慢。
优化级别：次优先处理。
优化方向：对SQL本身进行优化，综合考虑索引，SQL改写等方式。
3，并发很少特别慢。SQL特征：数量少(按15%统计)，往往是很复杂的查询，可能一天就
执行几次，对系统整体影响不大，但是优化难度很大。
优化级别：最后处理 。
优化方向：对SQL本身进行优化，同时可以对这类SQL有一定容忍度。
阻塞与死锁分析
死锁与阻塞是最为常见的两个性能杀手。可以用一些SQL来观测SQL的执行情况：
【show processlist】：此命令可以查看服务端线程状态。利用这些信息，可以找到耗时比
较长的环节并对其进行优化。还可以对阻塞的线程进行kill。使用【show processlist】会列出一
系列信息，其含义如下：
1，id。这是线程的唯一标记，【
kill {id}】可以终止该线程。
2，user。这个字段表示启动该线程的用户。
3，host。这个字段表示发起该连接的host。
4，db。这个字段表示操作的数据库是哪个。
5，command。表示操作的命令，包括SQL语句也是一种命令。
6，time。表示该操作持续了多久。
7，state。表示该线程当前处于什么状态。
8，info。包含前100个字符的Sql语句。如果想查看完整SQL，可以使用【show full processlist】。
【show status】：此命令可以查看Mysql服务器的运行状态，重启后状态信息会被清空。
还可以带上作用域，比如【
show global status】表示查看全局状态。
【show engine】：此命令用于查看存储引擎的状态。包括事务持有的表锁、行锁信息；事
务的锁等待情况；线程信号量等待；文件IO 请求；buffer pool 统计信息等。
死锁的特征：
1，死锁是相互堵塞。
2，数据库自动识别死锁并解锁。
3，SQL日志中会记录死锁信息。 避免数据库死锁需要采取以下措施：
1，减少事务的数量：减少并发事务的数目是避免死锁的首要步骤。
2，统一事务处理：尽量将对同一数据库表的更新操作放在同一个事务中，以减少出现死锁
的机会。
3，按照相同的顺序访问资源： 所有事务都按照相同的顺序来访问资源，以避免出现交叉等
待的情况。
4，使用低级别锁：尽可能使用低级别锁，例如行锁而不是表锁，以减少出现死锁的机会。
5，缩短锁定时间：尽量缩短锁定时间，并在完成后立即释放锁，以便其他事务可以及时访
问相关资源。
6，定期清理无用的锁：通过定期清理不再使用的锁，可以避免死锁的产生。
7，使用数据库提供的工具检测死锁：大部分数据库系统都提供了用于检测死锁的工具或
API，可以使用这些工具或API来监视数据库的死锁情况，并及时采取措施。
8，优化数据库设计和查询：优化数据库表格设计和查询语句，包括索引的建立、SQL 查询
语句的优化等，可以降低数据库死锁的风险。
阻塞的特征：
1，阻塞的SQL不会互相影响，它是单向的。
2，数据库无法自动识别阻塞并进行处理。
3，SQL日志中不会记录阻塞信息，表现形式只会是某SQL执行时间超长。
处理阻塞需要人工介入，大致方向有两个：减少并发操作，以及减少慢SQL。具体措施：
1，使用低级别锁：尽量使用低级别的行级别锁而不是高级别的表级别锁，以减少对资源的
阻塞。
2，使用索引： 通过在 WHERE 子句中使用索引列可以极大地加快 SQL 查询速度，从而减
少对相关资源的占用时间。
3，分批处理数据：如果需要处理大量数据，可以将其分批处理，以便避免一次性占用过多
的资源。
4，避免长事务和长查询：长时间运行的事务和长时间运行的查询会持续占用资源，导致其
他操作被阻塞。应该避免这种情况并定期清理无用的事务和查询。
5，调整数据库参数： 可以通过调整数据库参数来优化 SQL 执行性能，例如增加缓存大
小、调整线程池大小等。
6，定期优化数据库： 定期检查和优化数据库表格结构、索引、存储引擎等，可以提高数据
库的性能和响应速度。
7，合理设计 SQL 查询语句：合理的 SQL 查询语句设计可以有效地降低系统的负载和阻塞
风险。
SQL执行流程分析
对SQL执行流程所涉及的每一个环节进行优化，最终结果就是最优的。按照这个思路，先来
看看SQL执行流程： 客户端层的优化：
1，减少连接消耗：多次请求合并为一次，合理设置连接池的大小和连接施放时间。注意连
接数并不是越多越好。连接过多除了导致资源消耗增加外，还会额外引入更多的线程切换成本。
这里提供一个经验公式用于推断连接数的个数：【连接数=CPU核心数*2+1】
2，降低查询频率：合理使用缓存，将多条请求合并为同一条。
Mysql连接层的优化：
1，增加连接数。
2，及时释放不使用的连接。
3，Mysql相关参数调优。
查询缓存层的优化：查询缓存层设计得比较纠结，默认已经禁用了，仅当读远远大于写的场
景才有用。这一层的优化可以忽略。
SQL解析器和预处理器：这两个组件的作用是生成一颗正确的语法树。它的使用对用户来说
是黑盒，无法干预。
查询优化器：这一层会根据语法树生成多个执行计划，并进行优选。对SQL的优化，主要集
中在这里。
执行引擎：这一层根据执行计划进行调度，对用户来说，只能通过干预执行计划来间接干预
执行引擎的工作。
存储引擎层的优化：根据业务场景选择合适的存储引擎。例如归档表往往对事务的要求比较
弱，又以读为主，可以设置使用Mysiam引擎。再比如一些高频使用的小表可直接使用Memory
引擎常驻内存。
分析执行计划
在SQL语句前面加上explain并执行，可以列出该sql语句的执行计划，其语法格式如下：
【
explain {sql}】。在MySQL 5.6.3 以前的版本，只能分析SELECT。MySQL5.6.3以后就可以
分析update、delete和insert了。
一个典型的执行计划信息如下： 可以看到上述SQL语句出现了多行执行计划信息。这些执行计划表达的含义就是该SQL执行
时需要经过的步骤和其它信息，每条数据就是执行计划的一个步骤。接下来详细看看每个字段的
含义。
id字段：
id表示SQL语句执行的顺序。数字越大的执行计划越先被执行。如果数字相同，则按从上往
下的顺序依次执行。例如，子查询的优先级会高于外层查询。
如果是并列的查询，没有父子之分，查询优化器会自动进行优化：它会选用笛卡尔积相对较
小的结果作为中间结果，从而使得它们先被执行。Mysql的优化器是基于开销的，更小的中间结
果消耗更少的资源。此外，这也要求我们在做优化时，选择更小的表作为驱动表。
select_type字段：
这个字段表示查询的类型。查询类型很多，这里列举几个常见的：
1，simple。simple表示普通查询，它不包含子查询也不包含union查询。
2，primary。如果一个SQL语句包含子查询，那么最外层的查询类型就是primary。
3，subquery。如果一个SQL语句包含子查询，那么最内层的子查询类型就是subquery。
4，derived。意思是衍生查询。如果在得到最终结果之前，一个查询操作用到了临时表，那
么该查询的类型就是derived。比如一个union查询，Mysql会先执行union右边的语句，得到中
间结果放到临时表里，然后执行union左边的语句并与刚刚的临时表关联，其类型就是derived。
5，union。使用了union查询时，出现在union右边的查询会被标记为union类型。
6，union result。这种类型表示所有的union语句执行完毕后产生的中间结果。即使有多个
union查询，union result也只有一项。
table字段：
table字段表示查询过程中用到的表，包括普通的表和中间结果的表。普通表就不用细说
了，显示哪个表名，就表示针对那张表的查询。在union查询时，有时候table字段会显示类似
【
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！