数据库规范与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<String, Object> map = new HashMap<String, Object>();

3 map.put("start", start);

4 map.put("size", size);

6. 【强制】 不允许直接拿HashMap与Hashtable作为查询结果集的输出。

7. 【强制】 更新数据表记录时，必须同时更新记录对应的更新人与更新时间。对于拿不

到更新人的场景，可以统一使用保留字”system“，不要留空。

8. 【推荐】 不要写一个大而全的数据更新接口，传入为POJO类，不管是不是自己的目

标更新字段，都进行update table set c1=value1,c2=value2,c3=value3;这是不对的。 执行SQL时，尽量不要更新无改动的字段，一是易出错；二是效率低；三是binlog增加

存储。

9.【推荐】编写查询语句”select column1,column2,... from table where XXX“时，字

段的个数如果比较少，可以适当冗余。

试想一个场景：代码中同时存在针对某个实体的查询方法，它们的入参和返回值完

全一样，只有条件不同，这很容易被误解两个方法的返回值包含的属性值也一致。但如

果底层的实现中，select 的列名不一样，则很容易导致空指针。

思考：只查询需要的字段，数据库只需要返回用得到的字段即可，但方法可能有歧

义导致误用。查询全量字段，会要求数据库返回更多的信息，不过方法含义明确，不会

因为漏了字段值而导致问题。

10.【参考】@Transactional事务不要滥用。事务会影响数据库的QPS，另外使用事务

的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修

正等。

11.【参考】中的compareValue是与属性值对比的常量，一般是数字，表示相等时带上

此条件；表示不为空且不为null时执行；表示不为null值时执行。

12.【参考】作为更新场景的补充，为了不全量更新，一般会先判断传入的值是否为空，不为空

则更新。此时如果确实想设置某些字段的值为空，则无法实现。这种情况下，建议单独写一个设

置空值的方法，而不是通过一个全量更新的方法实现。

数据库层的调优

这里主要是一些与数据库调优相关的内容，包括数据库服务器，SQL分析等。

数据库服务器的性能

服务器端的分析集中在这几个方向上：硬件资源。数据库架构，以及数据库实例参数优化。

硬件资源分析

1，使用top命令查看CPU负载。

2，使用free命令查看内存使用情况。

3，使用iostat工具查看磁盘I/O使用情况。

4，使用vmstat命令查看系统的负载情况。

5，使用perf top命令查看系统热点情况。

6，使用nmon工具监控系统一段时间的整体情况。

使用腾讯云数据库时，也可以用腾讯云提供的相关工具。 如果发现数据库主机的CPU、

I/O、内存等使用率很高，那么原因有两种：

1，数据库实例存在性能瓶颈。

2，实例所在的机器硬件本身存在问题（可能性比较小，也容易排除) 。

数据库架构分析

目前公司主要是云数据库架构，依托腾讯云本身的能力，这一块的架构分析可以略过或找腾

讯云的售后进行支持。 服务器调优

数据库服务器调优。可以结合业务场景对数据库参数进行优化。以下是一个优化示例。

例如：有一个密集交易型数据库服务器配置如下：

CPU：4路8核。

内存：256G。

磁盘阵列：1T。

那么，推荐的参数设置如下：

识别慢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字段会显示类似

【

<union1,3,4>】这种值。这个值表示把ID为1，3，4的查询产生的结果集，作为当前查询所使

用的table。

type字段：

这个字段很关键，其含义是连接类型。不同的连接类型其执行效率差别很大，下面按性能由

高到低的顺序排列它们，并分别解释每一项的含义：

1，system。system表示查询系统表，表里只有一行记录。这个是最快的，然而是一种特

例，没有讨论的价值，基本不会出现。

2，const。它表示通过索引一次就找到了。通过一次索引就能找到数据的情况只有一种：

通过唯一索引查询数据，并且只查询到了一条数据。例如【

select * from t_table where

id=1】。

3，eq_ref。它通常出现在join语句中。对于前表中的每个索引列，都只能匹配到后表中的

唯一结果，此时后表的连接类型就是eq_ref。既然只能匹配到唯一结果，那多半是唯一性的索引

了。一般而言，join语句后面on的条件是唯一索引时会匹配到这种类型。

4，ref。它也是通常出现在join语句中。如果使用的不是唯一性的索引，那么前表中的索引

列的值可能对应后表中的多个结果，此时后表的连接类型就是ref。一般而言，join语句后面on 的条件是非唯一索引时会匹配到这种类型。

5，range。range表示使用索引进行范围查找。通常【

in (...)，between... and ...】或者

【where column_a>{number}】这种类型的语句会匹配到range类型。

6，index。index的意思是走索引的全表扫描。将所有索引扫一遍去找对应结果，这个已经

很慢了。遇到index类型的查询，说明需要优化。

误解：很多人认为“index”表示走索引的查询，这个理解大大的错，index的性能很低，说不

定还不如顺序的全表扫描，至少不用随机读。

7，all。all是最糟糕的情况，它表示不走索引全表扫描。我们应该尽量避免all。

8，Null。这是一种特殊情况。当前查询不需要查询表时，其type会是Null。比如获取系统

时间就不需要查询表，那么其执行计划显示的type就是Null。

我们对SQL语句的优化，需要达到 range 级别以上。

possible_keys字段：

这个是指查询中可能会用到的索引，不必特别关注。

key字段：

指实际查询中使用的索引，这个也不必特别关注。但是如果这个字段的值是Null，说明该查

询没有用到索引。一般来说，组合索引使用时如果不符合最左匹配，会出现“possible_keys"有

值，但”key"字段为空的情况。

Extra字段：

extra这个单词的意思是额外的。这里这个字段表示针对该查询干了哪些额外的事情，也是

一个比较重要的字段。关于这个字段的值，常用的是以下几个：

1，Using filesort。如果没有使用索引排序，而是使用了额外的排序规则，那么Extra就会

记录Using filesort。

2，Using temporary。tempoary的意思是临时工，它表示Mysql使用了临时表存储中间结

果。一般而言【

group by，distinct】之类的语句需要用到“临时工”。

3，Using index。它表示使用了覆盖索引。命中覆盖索引则不需要回表。另外，之前介绍索

引条件下推时也说到，索引条件下推时，extra字段的值也会是Using index。

4，Using where。它表示存储引擎返回数据后，server层使用where条件进行了数据筛选。

5，Select tables optimized away。这个表示没走到查询阶段就可以返回结果了。按官网

的说法，“在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM

存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成

优化。”

拿到数据后，最后一步就是返回数据给客户端了。Mysql采用的是一种即时返回的策略。它

不会等所有结果集就位后一起返回，而是从产生第一条结果时就开始返回数据到客户端。当然，

如果配置了缓存，结果会放到缓存里。返回数据是走网络，所以一个显而易见的结论是：传输的

数据越少返回越快。所以提取数据时，应该指明需要的字段，而不是无脑写“ * ”。

索引结构与调优 Mysql采用经过改良的B+树来存储索引数据。 B+树是一个加强版的B树，Mysql的B+树

与传统的B+树还不太一样，它有以下特征：

1，关键字个数等于路数。

2，InnoDB里，B+树节点不存储实际数据，所有数据放到最底层的叶子节点里。这些叶子

节点投影成了一个有序数组。

3，InnoDB里，每个叶子节点维护了一个指向它下一个叶子节点的指针，这样就形成了一个

双向链表。由于B树的叶子节点投影本身有序，这就成了一个有序的双向链表。这个有序链表对

范围查找非常友善，不需要再回到根节点重复IO。

4，这棵树的分叉非常多，使得这棵树的结构非常扁平。这样的结构能够在存储尽可能多的

树的同时，让IO次数尽可能少。假设这棵树只有三层，按每条数据1KB计算，大约有130万子节

点，能存储两千多万条数据。

5，存储数据时，需要进行子节点的分裂和合并重新调整。

整体结构如下：

根据以上结构，能推导出以下优化经验：

1，仅在需要的时候创建索引。索引再能满足需求的前提下，越少越好。一方面索引本身也

会占据不少的数据库资源（磁盘+内存），另一方面，在对数据进行增删改时，需要同步更新索

引树导致额外开销。

2，索引的字段要保证比较高的离散度。这是因为，如果一个字段的离散度太低，那么扫描

这颗B+树时就需要走更多的路数。当离散度低到一定程度时，甚至还不如直接走叶子节点扫全

表，这样至少能适用磁盘顺序读，比走索引更快。

3，创建联合需要符合最左匹配原则。对于联合索引，B+树上的键值也是从左往右构建和匹

配索引数据的。如果左边第一个命中，则直接按第一个走。未命中会继续走第二个，以此类推。

4，应用覆盖索引减少回表操作。根据上面的B+树结构可知，聚簇索引的叶子节点存储原始

数据。所以如果SQL的返回值刚好是索引值，那就不需要走叶子节点取数，直接在索引层就能返

回了。 5，有序ID减少页分裂。由于这棵B+树要保证叶子节点有序，所以如果插入了无序的ID，会

重新排列叶子节点。但如果数据本身有序，那么只需要顺序往后写即可，会更加高效。

6，查询条件里尽量不要有函数运算或子查询。索引树放在存储引擎层，只能做简单的逻辑

判断。如果写SQL时能保证逻辑简单，那这部分逻辑可以下推到存储引擎层，从而只返回符合条

件的数据。否则，只能用尽可能简单的方式扫描尽可能多的数据，再由执行引擎过滤掉不符合要

求的数据，导致性能低下。

SQL优化经验

其实，掌握了SQL的分析方法，优化方法也就出来了。有很多零零碎碎的优化经验，这里简

单列一些：

1，尽量避免在WHERE子句中使用函数，因为会使索引失效。

2，使用JOIN语句而不是子查询，因为JOIN语句更有效率。

3，确保表有适当的索引，以加快查询速度。

4，避免在SELECT语句中使用“*”，尽可能只选择需要的列。

5，尽量避免使用OR操作符，它会导致全表扫描而不是使用索引。

6，对于大型数据集，请考虑分区表或使用分页技术。

7，使用EXPLAIN语句来检查查询执行计划，找到问题并进行优化。

8，使用合适的数据类型，例如使用INT代替VARCHAR存储数字类型。

9，将多个单行查询组合成一个查询，以减少与数据库服务器的通信次数。

10，定期清理不再使用的索引和表，以保持数据库性能稳定。

个人经验和网传资料太过片面，想要系统掌握SQL优化的方法，还是建议看官网。以下是官

方提供的优化办法： https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-loggin

g.html 。

Mysql使用基于开销的优化器，大多数时候它能够很好工作。如果查询优化器没有按预期的

方式工作，可以使用 FORCE INDEX 扫描表告诉 MySQL强制走索引。与使用给定索引相比，表

扫描非常昂贵： 1 SELECT * FROM t1, t2 FORCE INDEX (index_for_column)

2 WHERE t1.col_name=t2.col_name;