几个问题

如何发现慢查询？
为什么不建议使用订单号作为主键？
为什么要在需要排序的字段上加索引？
为什么字段加上函数或者计算就不走索引了？
为什么不建议字段允许为NULL？

理论

MySQL逻辑架构

MySQL逻辑架构整体分为三层，如下图：

客户端：
- 如，连接处理、授权认证、安全等功能
核心服务：
- MySQL大多数核心服务均在这一层
- 包括查询解析、分析、优化、缓存、内置函数（如，时间、数学、加密等）
- 所有的跨存储引擎的功能也在这一层，如，存储过程、触发器、视图等
存储引擎：
- 负责MySQL中的数据存储和读取
- 中间的服务层通过API与存储引擎通信，这些API屏蔽了不同存储引擎间的差异
重点解释下查询缓存：
- 对于select语句，在解析查询之前，服务器会先检查查询缓存（Query Cache）。
- 如果命中，服务器便不再执行查询解析、优化和执行的过程，而是直接返回缓存中的结果集。

MySQL执行过程

下图是MySQL执行一个查询的过程。实际上每一步都比想象中的复杂，尤其优化器，更复杂也更难理解。

MySQL查询过程如下：

客户端将查询发送到MySQL服务器；
服务器先检查查询缓存，如果命中，立即返回缓存中的结果，否则进入下一阶段；
服务器对SQL进行解析、预处理，再由优化器生成对象的执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎API来执行查询；
服务器将结果返回给客户端，同时缓存查询结果。

InnoDB存储引擎

InnoDB索引结构

在MySQL中索引是在存储引擎层实现的，而不是在服务器层实现的。不同存储引擎实现索引的方式也各有不同。

Index_Type：
- 主键
- 普通索引
- 唯一索引
Index_Method：
- HASH：
  在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引。
  例如这种SQL：
  1
  SELECT … FROM t WHERE C1 = ?; — 仅等值查询
- BTREE（B+树）：
  在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。B+树索引结构适用于绝大多数场景。
  为什么使用B+树？
  一、从内存⻆度上说，数据库中的索引一般时在磁盘上，数据量大的情况可能无法一次性装入内存，B+树的设计可以允许数据分批加载。
  二、从业务场景上说，如果只选择一个数据那确实是hash更快，但是数据库中经常会选中多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。
  - B-树
    为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。
    那么B-Tree是满足下列条件的数据结构：
    - d为大于1的一个正整数，称为B-Tree的深度。
    - h为一个正整数，称为B-Tree的高度。
    - 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
    - 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
    - 所有叶节点具有相同的深度，等于树高h。
    - key和指针互相间隔，节点两端是指针。
    - 一个节点中的key从左到右非递减排列。
    - 所有节点组成树结构。
    - 每个指针要么为null，要么指向另外一个节点。
    - 如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key_1)，其中v(key_1)为node的第一个key的值。
    - 如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(key_m)，其中v(key_m)为node的最后一个key的值。
    - 如果某个指针在节点node的左右相邻key分别是key_i和key{i+1}且不为null，则其指向节点的所有key小于v(key{i+1})且大于v(key_i)。
    下图是一个d=2的B-Tree示意图。
  - B+Tree
    B-Tree有许多变种，其中最常用的是B+Tree，例如MySQL就普遍使⽤B+Tree实现其索引结构。
    与B-Tree相比，B+Tree有以下不同点：
    - 每个节点的指针上限为2d而不是2d+1；
    - 内节点不存储data，只存储key；
    - 叶子节点不存储指针。
    下图是一个简单的B+Tree示意图。
    一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关。
  - 带有顺序访问指针的B+Tree
    一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。
    如图所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

InnoDB索引实现

聚簇索引
在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。
定义：数据行的物理顺序与列值（一般是主键那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
辅助索引
InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：
聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

实战

慢查询

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

slow_query_log
这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。
long_query_time
当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。
slow_query_log_file
记录日志的文件路径以及文件名。
log_queries_not_using_indexes
这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得很快。

慢查询解读

User@Host: test[test] @ [127.0.0.1] Id: 85
Query_time: 3.299343 
Lock_time: 0.001484
Rows_sent: 10 
Rows_examined: 4844057
SET timestamp=1557113299;
SELECT
 *
FROM
salaries
ORDER BY
from_date,
salary
LIMIT 2000000, 10;

从慢查询日志里面摘选一条慢查询日志，数据组成如下：

用户名、用户的IP信息、线程ID号
执行花费的时间【单位：毫秒】
执行获得锁的时间
获得的结果行数
扫描的数据行数
这SQL执行的具体时间
具体的SQL语句

执行计划

简要说明

列名	表格查询的顺序编号	降序查看，id相同的从上到下查查看。id可以为null ，当table为(union ,m,n )类型的时候，id为null，这个时候，id的顺序为 m跟 n的后面。
id	表查询的顺序编号	id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行
select_type	查询的方式	下文详细说明
table	表格名称	表名，别名，( union m,n ）
partitions	分区名称	查询使用到表分区的分区名
type	表连接的类型	下文详细说明
possible_keys	可能使用到的索引	这儿的索引只是可能会有到，实际不一定会用到
key	使用到的索引	实际使用的索引
key_len	使用到索引的长度	比如多列索引，只用到最左的一列，那么使用到索引的长度则为该列的长度，故该值不一定等于 key 列索引的长度
ref	谓词的关联信息	当 join type 为 const、eq_ref 或者 ref 时，谓词的关联信息。可能为：null（非 const \ eq_ref \ ref join type 时）、const（常量）、关联的谓词列名。显示该表的索引字段关联了哪张表的哪个字段
rows	扫描的行数	该表格扫描到的行数。这儿注意在mysql里边是嵌套链接，所以，需要把所有rows相乘就会得到查询数据行关联的次数
filtered	实际显示行数占扫描rows的比例	实际显示的行数 = rows * filtered / 100
extra	特性使用

select_type

SIMPLE，简单查询方式，不使用UNION跟子查询；
PRIMARY，该表格位于最外层开始查询，通常会跟其他查询方式组合；
SUBQUERY，子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集（不会被数据库引擎改写的情况）；
UNION，UNION 第一个SELECT 为PRIMARY，第二个及之后的所有SELECT 为 UNION SELECT TYPE；
UNION RESULT，每个结果集的取出来后，会做合并操作，这个操作就是 UNION RESULT；
DEPENDENT UNION，子查询中的UNION操作，从UNION 第二个及之后的所有SELECT语句的SELECT TYPE为 DEPENDENT UNION，这个一般跟DEPENDENT SUBQUERY⼀起结合应用，子查询中UNION 的第一个为DEPENDENT SUBQUERY；
DEPENDENT SUBQUERY，子查询中内层的第一个SELECT，依赖于外部查询的结果集；
DERIVED，查询使用内联视图；
MATERIALIZED，子查询物化，表出现在非相关子查询中，并且需要进行物化时会出现MATERIALIZED关键词；
UNCACHEABLE SUBQUERY，结果集无法缓存的子查询，需要逐次查询；
UNCACHEABLE UNION，表示子查询不可被物化，需要逐次运行。

type

性能排序：system->const->eq-ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。

system：表只有一行记录，相当于系统表。
const：通过索引一次就找到，只匹配一行数据。主键或者唯一索引的常量查询。
eq_ref：join查询中，关联条件为主键或者唯一索引。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，用于=、<或>操作符带索引的列。
range：只检索给定范围的行，使用一个索引来选择行。一般使用between、<、>。
index：只遍历索引树。使用到索引，但不是索引查找。
all：全表扫描，性能最差。

extra

重点以下几种情况：

Using filesort：MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。若出现有该值，应该优化SQL语句。
Using temporary：使用临时表保存中间结果，比如MySQL在对查询结果排序时使用临时表，常见于group by和order by。若出现有该值，应该优化SQL语句。
Using index：索引覆盖，不止用到索引，而且没有回表。避免了访问表的数据行，效率不错。

优化案例

优化案例可以参考这篇文章 MySql 使用Explain分析SQL语句及索引

优化口诀

全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用。

SakuraTears的博客

MySQL优化