节选-《数据库高效优化》架构、规范与SQL技巧一书 有关SQL优化、索引、规范等

SQL优化

对于普通的采用数值类型的字段，范围查询就是正常的索引范围扫描，执行效率很高。
对于文本类型字段的表，范围查询就是对应的全表扫描，效率较低是显而易见的。
（分析）字符类型在索引中是“乱序”的，这是因为字符类型的排序方式与我们的预期不同，从“select * from t2 where id>='3199990'”执行返回的多条数据记录可见，不是直观上的10条记录，这也是当初在做表设计时，开发人员没有注意到的问题。
字符类型还导致了聚簇因子很大，原因就是插入顺序与排序顺序不同，详细点说，就是按照数字类型插入，（1...3200000）,按字符类型（‘1’...‘32000000’）t排序

在对字符类型使用大于运算符时，会导致优化器认为需要扫描索引大部分数据且聚簇因子很大，最终导致弃用索引扫描而改用全表扫描方式。

糟糕的数据结构设计往往是致命的，后期的优化只是补救措施。只有从源头上加以杜绝，才是优化的根本。
在设计初期能引入数据库审核，可以起到很好的作用。

由于信息的缺失导致了优化器产生了较差的执行计划。
统计信息是优化器优化的重要参考依据。

常数索引在存储密度上要高于普通字段索引，因此扫描块数更少，耗时更少。

数据库聚簇因子 （多在Oracle）
聚簇因子是Oracle统计信息中在CBO优化器模式下用于计算cost的参数之一，决定了当前SQL语句是否走索引，还是全表扫描以及是否作为嵌套连接外部表等，如此这般，那到底什么是聚簇因子，那些情况会影响聚簇因子，以及如何提高聚簇因子？
https://blog.csdn.net/weixin_30615567/article/details/113557187 （公司不太用oracle暂时不看）

• 优化SQL就是一个抽丝剥茧找到问题本质的过程。在不断猜测、不断试错的过程中，逐步接近事件的本质。你所掌握的知识点越多，可“猜测”的可能性就越多。

• 数据结构的变更要经过DBA的审核，这样可以避免很多问题，也可以尽早发现问题、解决问题。

MySQL执行计划

MySQL执行计划的定义

在MySQL中可以通过explain关键字模拟优化器执行SQL语句，从而知道MySQL是如何处理SQL语句的。
MySQL整个查询的过程

• 客户端向MySQL服务器发送一条查询请求
• 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一个阶段
• 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
• MySQL根据执行计划，调用存储引擎的API来执行查询
• 将结果返回给客户端，同时缓存查询结果
• 注意：只有在8.0之前才有查询缓存，8.0之后查询缓存被去掉了

如何启动执行计划

explain select 投影列 FROM 表名 WHERE 条件

explain两个变种（MySQL5.7)
explain extended : 会在explain的基础上额外提供一些查询优化的信息。紧随其后通过show warnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有filtered列，是一个半分比的值，rows*filtered/100可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的表）

EXPLAIN EXTENDED select * from actor where id=1;show WARNINGS;

explain partitions: 相比explain多了个partitions字段，如果查询是基于分区表的话，会显示查询将访问的分区。

explain中的列

id

• 查询执行顺序：
• id值相同时表示从上向下执行
• id值相同被视为一组
• 如果是子查询，id值会递增，id值越高，优先级越高
• id为null最后执行

select_type

• simple：表示查询中不包含子查询或者union
• primary：当查询中包含任何复杂的子部分，最外层的查询被标记成primary
• derived：在from的列表中包含的子查询被标记成derived
• subquery：在select或where列表中包含了子查询，则子查询被标记成subquery
• union：两个select查询时前一个标记为PRIMARY，后一个标记为UNION，union出现在from从句子查询中，外层select标记为PRIMARY，union中第一个查询为DERIVED，第二个子查询标记为UNION
• unionresult：从union表获取结果的select被标记成union result

table

• 显示这一行的数据是关于哪张表的
• 当from子句中有子查询时，table列是格式，表示当前查询依赖id=N当查询，于是先执行id=N当查询
• 当有union时，UNION RESULT的table列的值为<union1,2>,1和2表示参与union的select行id

type

• 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为system>const>eq_reg>ref>range>index>ALL
• 一般来说，得保证查询达到range级别，最好达到ref
• NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
• system：表中只有一行数据。属于const的特例，如果物理表中就一行数据为ALL
• const：查询结果最多有一个匹配行，因为只有一行，所以可以被视为常量。const查询速度非常快，因为只读一次。一般情况下把主键或唯一索引作为唯一条件的查询都是const
• eq_ref：查询时查询外键表全部数据。且只能查询主键列或关联列。且外键表中外键列中数据不能有重复数据，且这些数据都必须在主键表中有对应数据（主键表中数据可以有没有用到的）
• ref：比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
• range：把这个列当作条件只检索其中一个范围。常见where从句中出现between，<,>,>=,in等。主要应用在具有索引等列中
• index：扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树节点开始快速查找，而是直接对二级索引等叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些
• ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点，通常情况下这需要增加索引来进行优化了。

possible_keys

• 查询条件字段涉及到的索引，可能没有使用。
• explain时可能出现possible_keys有列，而key显示NULL的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
• 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果

key

• 实际使用的索引，如果为NULL，则没有使用索引。
• 如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用forceindex、ignore index。

key_len

• 表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len是根据表定义计算而得等，不是通过表内检索出的。

ref

• 显示索引的哪一列被使用了，如果可能的话，是一个常量const

rows

• 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，注意这个不是结果集里的行数

filtered

• 显示了通过条件过滤出的行数的百分比估计值

Extra

• Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行
• Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的行组合在该表内检查更多的行。
• range checked for each record （index map：#）：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。
• Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序，这种情况下一般也是需要考虑使用索引来优化的。
• Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息，（使用覆盖索引）覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extr里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
• Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。
• Using where：WHERE 子句用于限制哪一个行匹配下一个表或发送到客户，并且查询的列未被索引覆盖
• Using sort_union(...), Using union(...), Using intersect(...) 这些函数说明如何为index_merge连接类型合并索引扫描。
• Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。

优化SQL就是一个抽丝剥茧找到问题本质的过程，在不断猜测、不断试错的过程中，逐步接近事件的本质。你所掌握的知识点越多，可“猜测”的可能性就越多。

优化器是数据库最核心的功能，也是最复杂的一部分。它负责将用户提交的SQL语句根据各种判断标准，制定出最优出最优的执行计划，并交由执行器来最终执行。优化器算法的好坏、能力的强弱，直接决定了语句的执行效率。笔者也使用了其他诸如MySQL，postgreSQL，SQLServer等关系型数据库。综合比较来说，Oracle的优化器是功能最强大的。学习SQL优化，从本质来讲就是学习从优化器的角度如何看待SQL，如何制定出更优的执行计划。当然，优化器本身是数据库系统中最复杂的一个部分，本书会就优化器的分类、工作原理等做简单介绍，不会深入细节。

成本是优化器（基于成本的优化器）中反映SQL语句执行代价的一个指标。优化器通过比较不同执行计划的成本，选择成本最小的作为最终的执行计划。如何理解成本、成本如何计算也就成为我们学习基于成本的优化器的关键所在。

优化器

优化器在整个SQL语句的执行过程中充当了非常重要的角色。

执行计划

执行计划是SQL优化的基础，只有在充分了解执行计划的基础上才能判断语句执行是否高效。如何获得执行计划？怎样读取执行计划？常见的执行计划有哪些？如何干预执行计划？

什么是执行计划

数据库执行SQL语句是按照一定顺序、分步骤完成的。至于采用怎样的顺序、用什么方法访问数据，是由优化器来决定的。一旦优化器确定好了一个它认为最高效的执行方法，这一系列的顺序、步骤就被称为执行计划。

SQL优化相关对象

在SQL优化的过程中，首先需要了解语句相关对象的情况。数据库对象设计的好坏，会直接影响相关对象语句执行的效率。
因此，有时在SQL语句实在无法优化的情况下，可以考虑通过修改对象的结构来完成优化。

在做表设计时，频繁访问的字段放在前面。
相关字段类型操作时，建议使用同一类型，以免引起不必要的转化成本。

索引

B树索引是数据库默认索引，也是最为常见的一种索引，通常我们所说就是B树索引，MySQL默认创建不指定索引类型时，也是B树索引。整个索引结构就是一个平衡树（Balance Tree），这也就是称为B树索引的原因。在整个树结构中，包含3种节点，分别是根节点，分支节点，叶子节点。有的简单索引只有根节点和叶子节点。

位图索引，虽说常见，但一般用于分析型数据库中。

表空间：
表空间是数据库的一种逻辑结构，它在物理上对应着一个或多个数据文件。平常所说的表空间管理实际上指的是对表空间所对应的数据文件的空间管理，Oracle支持两种管理方式：一种是字典管理（简称DMT），一种是本地管理（简称LMT）。这里所说的管理方式是指针对extent的管理方式。extent也是数据库的一种逻辑结构，它包含一定数量的、连续的Oracle块，是Oracle空间分配的最小单位。针对它的管理方式是指表空间中的extent是如何被管理的（记录extent的free、used使用情况）。

段：
数据段（即基表段），是Oracle数据库中用于存储基表数据段段。数据段存储在表空间中，对应于一个或多个数据文件（段可以来自多个文件，但段中指定的一个区只能来自一个文件）。每个基表段都有一个数据段（聚簇段中，两个基表段对应一个数据段）。每当用户创建一个基表时，系统会在用户默认的表空间中创建一个数据段。

区：
区是磁盘空间分配的最小单位，磁盘按区划分的，每次至少分配一个区。区存储于段中，是数据库存储空间逻辑单位，是由连续的数据块组成的。一个或多个数据块组成一个区，一个或多个区组成一个段。当一个段中所有空间用完时，系统会自动给该段分配一个新区。段的增大是通过增加区的个数实现的。

块：
块是Oracle数据库中最小的一个数据组织单位。它的大小由参数db_block_size确定，取值和os有关，一般是os物理块的整数倍，即512Byte的倍数。块大小在创建数据库之前确定，数据库创建或安装结束后不得修改。对于系统表空间及其它默认表空间使用参数db_block_size确定块大小，而对于其他非默认表空间使用参数blocksize确定块大小。

索引设计

对于查询中需要作为查询条件的字段，可以考虑建立索引，当然，最终还要根据性能的需要决定是否建立索引。建立索引时也要考虑维护成本，不能无序地创建索引。
构建战略性索引的策略：
B树索引：

• 控制单表索引个数，尽量不要超过5个。没有任何索引的单表也需要关注。
• 要注意监控创建的索引中不要出现无用索引。必要时，删除无用的索引，避免对执行计划造成影响。
• 如果存在外键，需要在对应字段创建索引，否则会引发死锁并影响表连接性能。
• 对于经常出现在WHERE子句中且过旅性比较强的字段，特别是大表的字段，应该创建索引。

复合索引：
当某个索引包含多个索引的列时，这种索引称之为复合索引。当查询多个条件为AND关系的数据时，可以使用复合索引快速定位到该数据。对复合索引的使用，应注意：

• 过度索引：不要讲所有的WHERE条件中的字段都创建为复合索引。因为索引会造成DML操作的开销增大，且空间上也会存在很大的浪费。
• 复合索引中的列顺序：所有的索引列都在WHERE条件中时，索引的效率和索引中的列的顺序是无关的。因而，当不是所有索引的列都在WHERE条件中时，最佳的列顺序要求就是能够最大可能使用索引，换句话说，应该能够对最多的SQL语句应用索引。当多个列的使用频率相同时，可以遵从以下两种截然相反的方法。
• 如果将来的SQL语句只对部分列应用限制条件，前导列应该是具有最多唯一值的列。换句话说，就是使索引被优化器选择的概率最大化。
  复合索引的使用建议：
• 如果约束条件字段比较固定，则优先考虑创建针对多字段多普通B树复合索引。
• 如果单个字段是主键或唯一字段，或者可选性非常高的字段，尽管约束条件字段比较固定，也不一定要创建复合索引，有时可创建但字段索引，降低复合索引开销。
• 在复合索引设计中，应考虑复合索引的第一个设计原理：复合索引的前缀性。即在SQL语句中，只有将复合索引的第一个字段作为约束条件，该复合索引才会启用。
• 在复合索引设计中，应考虑复合索引的可选性，即按可选性高低，进行复合索引字段的排序。
• 如果条件涉及的字段不固定，组合比较灵活，可以考虑分别创建索引。
• 如果是多表连接SQL语句，考虑是否可以在被驱动表的连接字段与该表的其他约束条件字段上创建复合索引。

分区索引

同分区表类似，索引页可以创建分区索引，这里有两种分区索引要创建。

• 本地（局部）分区索引：对于建立的每个分区表，都存在一个分区索引。每个分区索引中的数据仅仅指向一个分区表中的数据。分区表和分区索引之间存在一对一的映射。
• 全局分区索引：索引按自己的模式分区。分区索引和分区表之间没有匹配关系。

函数索引

函数索引可以是B树索引，页可以是位图索引，它将一个函数计算的结果存储在索引中，而不是存储列数据本身。我们可以把基于函数的索引看作一个虚拟列上的索引，总之，所谓函数索引，是基于加工过的逻辑列创建的索引。
函数索引适用于基于基础表中国呢一个或多个列的函数或表达式，查询语句条件列上包含函数的情况。函数和表达式的值预先计算并存放在索引中。要使用函数索引就要分析表，启用查询重写，设置函数索引，会减慢DML速度，因为需要先求函数值或表达式。

https://blog.csdn.net/horses/article/details/85059678
http://www.weijingbiji.com/2124/

范式与逆范式

基本结构设计，应尽量满足范式设计。但是完全满足范式设计的数据库往往不是最好的设计。
1.范式设计：
范式设计主要包括三个。在数据库设计中，为了更好地应用三个范式，必须通俗地理解三个范式（通俗理解并不是最科学、最准确的）

• 第一范式（1NF）：对属性的原子性约束，要求属性具有原子性，不可再分解。
• 第二范式（2NF）：对记录的唯一性约束，要求记录有唯一标识，即实体的唯一性。
• 第三范式（3NF）：对字段的冗余性约束，即任何字段不能由其他字段派生，要求字段没有冗余。
  没有冗余的数据库可以做到，但其未必是最好的数据库。有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，在物理数据模型设计时降低范式标准的。降低范式就是增加字段，允许冗余。
  2.逆范式设计：
  逆范式的好处是降低连接操作的需求、降低外键和索引的数目，以及减少表的数目，相应带来的问题是可能出现数据的完整性问题。逆范式设计可以加快查询速度，但会降低修改速度。因此决定做逆范式时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点。好的索引设计通常能够解决性能问题，而不必采用逆范式设计。

其他设计问题

1.表与实体的关系
表与实体的关系可以是一对一、一对多、多对一的关系。一般情况下，它们是一对一的关系，即一条记录对应且只对应一个实体。在特殊情况下，它们可能是一对多或多对一的关系。

2.多对多的关系
若两个实体之间存在多对多的关系，则应消除这种关系。消除的办法是，在两者之间增加第三个实体，即将原来两个实体的属性合理地分配到三个实体中。这样原来一个多对多的关系就变成了两个一对多的关系。

3.业务主键与自然主键
业务主键是指表中的一个或几个字段组合在一起构成的主键。自然主键是指通过数据库的方式定义的主键，只作为记录的唯一标识，没有任何业务含义。我们不需要从数据建模和数据库设计的角度去关注业务主键和自然主键，从性能的角度探究人造键的优点才是明智之举。毫无疑问，自然主键通常会带来更好的性能。

编码规范

1.单条SQL不宜超过100行。
2.当一条SQL或PLSQL语句中涉及多个表时，始终使用别名来限定字段名。别名要避免使用毫无意义的代号，方便维护者阅读。
3.存储过程、函数、触发器、程序块中定义的变量和输入/输出参数在命名上要有所区分。
4.避免使用SELECT *语句，应给出字段列表，同样，INSERT语句也必须给出字段列表，避免由于表结构的更改导致语句不可执行。
5.要从表中的同一笔记录中获取记录的字段值，必须使用同一SQL语句，不允许使用多条SQL语句，这样可以降低与数据库的交互。
6.存储过程中变量的定义应放在AS和BEGIN关键字之间，不允许在代码中随意定义变量。相同功能模块的变量放在一起，与其他模块的变量之间应用以空行分隔，这样可以增加代码的可读性。
7.IN、OUT参数按类别分开书写，不允许交叉。