MySQL

参考

MySQL内部运行

• 连接器： 主要负责跟客户端建立连接、获取权限、维持和管理连接

• 查询缓存： 优先在缓存中进行查询，如果查到了则直接返回，如果缓存中查询不到，在去数据库中查询。
  MySQL缓存是默认关闭的，也就是说不推荐使用缓存，并且在MySQL8.0 版本已经将查询缓存的整块功能删掉了。这主要是它的使用场景限制造成的：
  先说下缓存中数据存储格式：key（sql语句）- value（数据值），所以如果SQL语句（key）只要存在一点不同之处就会直接进行数据库查询了；
  由于表中的数据不是一成不变的，大多数是经常变化的，而当数据库中的数据变化了，那么相应的与此表相关的缓存数据就需要移除掉；

• 解析器/分析器： 分析器的工作主要是对要执行的SQL语句进行词法解析、语法解析，最终得到抽象语法树，然后再使用预处理器对抽象语法树进行语义校验，判断抽象语法树中的表是否存在，如果存在的话，在接着判断select投影列字段是否在表中存在等。

• 优化器： 主要将SQL经过词法解析、语法解析后得到的语法树，通过数据字典和统计信息的内容，再经过一系列运算 ，最终得出一个执行计划，包括选择使用哪个索引
  在分析是否走索引查询时，是通过进行动态数据采样统计分析出来；只要是统计分析出来的，那就可能会存在分析错误的情况，所以在SQL执行不走索引时，也要考虑到这方面的因素

• 执行器： 根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据，完成SQL的执行。

MySQL储存引擎

参考

• InnoDB是行锁，操作时候只锁一行数据，适合高并发，会出现死锁，锁冲突概率低；MyISAM是表索，不会发生死锁，锁冲突几率高，并发低；
• InnoDB不仅缓存索引，还缓存真实数据，所以需要表空间大；MyISAM只缓存索引，所需表空间相对较小；
• InnoDB关注事务，MyISAM关注性能（查）；
• MyISAM使用B-Tree实现主键索引、唯一索引和非主键索引。InnoDB中非主键索引使用的是B-Tree数据结构，而主键索引使用的是B+Tree。
• mysql的行锁是通过索引加载的，即是行锁是加在索引响应的行上的，要是对应的SQL语句没有走索引，则会全表扫描，行锁则无法实现，取而代之的是表锁。
• 当我们使用范围条件而不是相等条件检索数据，并请求共享排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做间隙；InnoDB也会对这个间隙加锁。

主从复制原理

slave会从master读取binlog来进行数据同步。
mysql复制过程分为三步：

• master将改变记录到二进制日志（binary log）；
• slave将master的binary log events 拷贝到它的中继日志（relay log）；
• slave重做中继日志的时间，将改变应用到自己的数据库中，MySQL复制是异步的且串行化；

每个slave只有一个master，只能有一个唯一的服务器ID，每个master可以有多个salve；

索引

按数据结构分类

R-Tree索引

Hash索引（MyISAM&InnoDB引擎不支持）

Full-texts全文索引

旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。
不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引
ADD FULLTEXT INDEX idx_full(en_name)

跟普通索引稍有不同
使用全文索引的格式： MATCH (columnName) AGAINST (‘string’)
eg:
SELECT * FROM student WHERE MATCH(name) AGAINST(‘聪’)

B+TREE（B+树）索引

B+TREE是B-TREE（B树）的变体，也是一种多路搜索树：
其定义基本与B树相同，除了：
1.非叶子结点的子树指针与关键字个数相同；
2.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树
（B-树是开区间）；
3.为所有叶子结点增加一个链指针；
4.所有关键字都在叶子结点出现；
如：（M=3）

B+TREE的搜索与B-TREE也基本相同，区别是B+TREE只有达到叶子结点才命中（B-TREE可以在
非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；
B+TREE的特性：
1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好
是有序的；
2.不可能在非叶子结点命中（只在叶子节点存储数据）；
3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储
（关键字）数据的数据层；
4.更适合文件索引系统；

B-TREE（B树）索引

是一种多路搜索树（不是二叉的）：
1.定义任意非叶子结点最多只有M个儿子；且M>2；
2.根结点的儿子数为[2, M]；
3.除根结点以外的非叶子结点的儿子数为[M/2, M]；
4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5.非叶子结点的关键字个数=指向儿子的指针个数-1；
6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的
子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8.所有叶子结点位于同一层；
如M=3查找：

B-TREE的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点。

B-TREE的特性：
1.关键字集合分布在整颗树中；
2.任何一个关键字出现且只出现在一个结点中；
3.搜索有可能在非叶子结点结束；
4.其搜索性能等价于在关键字全集内做一次二分查找；
5.自动层次控制；
由于限制了除根结点以外的非叶子结点，至少含有M/2个儿子，确保了结点的至少
利用率，其最底搜索性能为：

其中，M为设定的非叶子结点最多子树个数，N为关键字总数；
所以B-TREE的性能总是等价于二分查找（与M值无关），也就没有B-TREE平衡的问题；
由于M/2的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各
占M/2的结点；删除结点时，需将两个不足M/2的兄弟结点合并；

区别于B Tree

   1.所有非叶子结点至多拥有两个儿子（Left和Right）；

   2.所有结点存储一个关键字；

   3.非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

   如：

二叉搜索树的搜索，从根结点开始，如果查询的关键字与结点的关键字相等，那么就命中；

否则，如果查询关键字比结点关键字小，就进入左儿子；如果比结点关键字大，就进入

右儿子；如果左儿子或右儿子的指针为空，则报告找不到相应的关键字；

   如果二叉搜索树的所有非叶子结点的左右子树的结点数目均保持差不多（平衡），那么B树

的搜索性能逼近二分查找；但它比连续内存空间的二分查找的优点是，改变二叉搜索树结构

（插入与删除结点）不需要移动大段的内存数据，甚至通常是常数开销；

   如：

但二叉搜索树在经过多次插入与删除后，有可能导致不同的结构：

右边也是一个二叉搜索树，但它的搜索性能已经是线性的了；同样的关键字集合有可能导致不同的

树结构索引；所以，使用二叉搜索树还要考虑尽可能让B树保持左图的结构，和避免右图的结构，也就

是所谓的“平衡”问题；

   实际使用的二叉搜索树都是在原二叉搜索树的基础上加上平衡算法，即“平衡二叉树”；如何保持B树

结点分布均匀的平衡算法是平衡二叉树的关键；平衡算法是一种在二叉搜索树中插入和删除结点的

策略；

B+Tree（B+树）相对于B-Tree（B树）有几点不同：

非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。
BTREE在MyISAM里的形式和Innodb稍有不同
在 Innodb里，有两种形态：一是primary key形态，其leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是secondary index，其leaf node和普通的BTREE差不多，只是还存放了指向主键的信息.
而在MyISAM里，主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息.

按物理存储角度分类

聚簇索引&非聚簇索引

MySQL数据库中innodb存储引擎，B+树索引可以分为聚簇索引（也称聚集索引，clustered index）和辅助索引（有时也称非聚簇索引或二级索引，secondary index，non-clustered index）。这两种索引内部都是B+树，聚集索引的叶子节点存放着一整行的数据。

Innobd中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。

Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，不是一种单独的索引类型，而是一种数据存储方式。聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
　　一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。
　　根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，现在找到主键索引再通过主键索引找数据；

聚簇索引(聚集索引)

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

聚簇索引的优缺点

优点：

1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

2.聚簇索引对于主键的排序查找和范围查找速度非常快
　　缺点：

1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
　　　　2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
　　　　3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

辅助索引（非聚簇索引/二级索引）

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

InnoDB索引实现和MyISAM索引实现

按索引字段特性分类

主键索引（PRIMARY KEY）

也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。
ADD PRIMARY KEY (name) USING BTREE;

常规索引(INDEX或KEY)

单列索引

ALTER TABLE testDB.user
ADD INDEX idx_name(name) USING BTREE

组合（联合）索引

ADD INDEX idx_mult(name, address) USING BTREE
组合索引最左前缀原则
例如上面我们创建了一个name, address的组合索引
select * from user where name = ‘xxx’ 此时，会走索引
select * from user where address = ‘xxx’ 则不会走索引

注：
联合索引 (a,b,c) 实际建立了 (a)、(a,b)、(a,b,c) 三个索引

我们可以将组合索引想成书的一级目录、二级目录、三级目录，如index(a,b,c)，相当于a是一级目录，b是一级目录下的二级目录，c是二级目录下的三级目录。要使用某一目录，必须先使用其上级目录，一级目录除外。
如下：

组合（联合）索引的优势

1. 减少开销
   建一个联合索引 (a,b,c) ，实际相当于建了 (a)、(a,b)、(a,b,c) 三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

2. 覆盖索引
   对联合索引 (a,b,c)，如果有如下 sql 的，

SELECT a,b,c from table where a='xx' and b = 'xx';

那么 MySQL 可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机 io 操作。减少 io 操作，特别是随机 io 其实是 DBA 主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

3. 效率高
   索引列多，通过联合索引筛选出的数据越少。比如有 1000W 条数据的表，有如下SQL:

select col1,col2,col3 from table where col1=1 and col2=2 and col3=3;

假设：假设每个条件可以筛选出 10% 的数据。

A. 如果只有单列索引，那么通过该索引能筛选出 1000W10%=100w 条数据，然后再回表从 100w 条数据中找到符合 col2=2 and col3= 3 的数据，然后再排序，再分页，以此类推（递归）；

B. 如果是（col1,col2,col3）联合索引，通过三列索引筛选出 1000w10% 10% *10%=1w，效率提升可想而知！

唯一索引（UNIQUE KEY）

ADD UNIQUE INDEX idx_unique(en_name);
主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

MySQL锁分类

死锁问题

例如说两个事务，事务A锁住了1-5行，同时事务B锁住了6-10行，此时事务A请求锁住6-10行，就会阻塞直到事务B施放6-10行的锁，而随后事务B又请求锁住1-5行，事务B也阻塞直到事务A释放1~5行的锁。死锁发生时，会产生Deadlock错误。锁是对表操作的，所以自然锁住全表的表锁就不会出现死锁。

按锁的粒度分类

行级锁

行级锁是MySQL锁定粒度最小的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，单加锁开销最大。InnoDb支持行级锁。

表级锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

按锁的级别分类

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改前，会自动给涉及的表加写锁。

共享锁（读锁）

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
SELECT … LOCK IN SHARE MODE;

排他锁（写锁）

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
SELECT … FOR UPDATE;

按加锁方式（自动锁、显示锁）

INSERT、UPDATE、DELETE InnoDB会自动加排他锁，对于普通SELECT语句，InnoDB不会加任何锁，当然也可以显示加锁。

按操作分类

DML（data manipulation language）

它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。这些语句的操作对于的锁为DML锁

DDL（data definition language）

DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。这些语句的操作对于的锁为DDL锁

按适用方式划分

悲观锁和乐观锁MySQL是不直接提供的，需要我们自己去用代码实现

优化

• 索引没起作用
• 优化数据库结构
• 分解关联查询
• 优化limit分页

数据库结构优化

● 选择innodb引擎
● 对于经常联合查询的表，建立中间表提高查询效率，将需要通过联合查询的数据插入中间表，然后联合查询改为对中间表的查询
● 增加合理的冗余字段
● 建表字段应尽可能not null设置
● 选用简单的数据类型，数值类型要比varchar处理快，字符串类型会降低查询和连接的性能，并会增加存储开销，并且字段的范围选择合理，引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
● 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
● 垂直拆分表，有些表的字段使用频率很低，可以将这些字段拆分出来
● 水平拆分表，解决数据量大的问题，数据结构一致

索引优化（索引优化）

● 基于建立索引原则创建索引
● 定义主键的数据列一定要建立索引。
● 定义有外键的数据列一定要建立索引。
● 对于经常查询的数据列最好建立索引。
● 对于需要在指定范围内的快速或频繁查询的数据列;
● 经常用在WHERE子句中的数据列。
● 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。
● 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
● 对于定义为text、image和bit的数据类型的列不要建立索引。
● 对于经常存取的列避免建立索引
● 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
● 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

避免索引失效

1、避免在 where 子句中使用!=或<>操作符或者对字段进行函数操作，或者使用or连接条件，或者对字段进行null值判断，尽量建表时设置非null。
2、考虑在 where 及 order by 涉及的列上建立索引。
3、避免模糊查询中前置%
4、in 和 not in 也要慎用，对于连续的数值，能用 between 就不要用 in 了
5、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。最左前缀原则。
6、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
7、索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
8.应尽可能的避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新聚簇索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
9、尽量使用覆盖索引，只访问索引的查询（索引列和查询列一致），任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。（覆盖索引：建索引的列和要查询的列相同，例如索引列是c1，c2，select 查询的也是c1，c2；）
10、字符串不加单引号

insert优化

批量插入

多条sql的insert语句修改成VALUES (‘0’, ‘userid_0’, ‘content_0’, 0), (‘1’, ‘userid_1’, ‘content_1’, 1) 减少sql语句解析的操作并且sql语句较短减少网络传输的IO

在事务中进行插入处理

START TRANSACTION;
INSERT INTO insert_table (datetime, uid, content, type) VALUES (‘0’, ‘userid_0’, ‘content_0’, 0);
INSERT INTO insert_table (datetime, uid, content, type) VALUES (‘1’, ‘userid_1’, ‘content_1’, 1);
COMMIT;
使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内进行真正插入处理。通过使用事务可以减少数据库执行插入语句时多次“创建事务，提交事务”的消耗，所有插入都在执行后才进行提交操作。

结合多条数据合并sql+事务中插入：性能最高

注意事项

1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packe配置可以修改，默认是1M。
2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，默认8M，超过这个值会日志会使用磁盘数据，这时，效率会有所下降。所以比较好的做法是，在事务大小达到配置项数据级前进行事务提交。

delete&update语句优化

加上limit：delete from euser where age > 30 limit 200;

• 「降低写错SQL的代价」, 你在命令行执行这个SQL的时候，如果不加limit，执行的时候一个「不小心手抖」，可能数据全删掉了，如果「删错」了呢？加了limit 200，就不一样了。删错也只是丢失200条数据，可以通过binlog日志快速恢复的。
• 「SQL效率很可能更高」，你在SQL行中，加了limit 1，如果第一条就命中目标return， 没有limit的话，还会继续执行扫描表。
• 「避免了长事务」，delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。
• 「数据量大的话，容易把CPU打满」 ,如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢的。

select优化

临时表优化

1、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
2、避免频繁创建和删除临时表，以减少系统表资源的消耗。
3、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
4、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
5、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

游标的优化

1、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
2、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
3、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

其他优化

1、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
2、避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
3、避免大事务操作，提高系统并发能力。
4、当只需要一条数据的时候，使用limit 1，这是为了使EXPLAIN中type列达到const类型。
5、尽量用union all代替union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

永远小表驱动大表

in 和 exists 选择：

// 工作原理，先查B表数据，然后查A的 id
select * from A where id in (select id from B)
// 工作原理，先查A表的id，然后查B表的id
select * from A a where exists (select 1 from B b where a.id = b.id )
// 结论：当B表的数据小于A表时候用 in；当A表数据小时候用 exists

Order By 排序

MySQL支持两种排序，index和fileSort，index效率高，它指MySQL扫描索引本身完成排序。
Order By满足两种情况使用index：
1、Order By 语句使用索引最左前列
2、使用where子句与Order By子句条件组合满足索引最左前列
如果不在索引列上，fileSort有两种算法，4.1版本之前双路排序，进行两次IO；之后单路排序，进行一次IO；
Order By时不要select *，只查询所需要的字段；当两种算法的数据超出sort_buffer的容量会创建tmp文件进行合并运算，导致多次IO，所以需要尝试提高sort_buffer_size 和
max_length_for_sort_ size。
为排序使用索引，MySQL能为排序与查询使用相同的索引
如果我们对结果没有排序的要求，就尽量少用排序，可以指定ORDER BY NULL禁止排序。

Group By优化

如果对group by语句的结果没有排序要求，要在语句后面加 order by null（group 默认会排序）；

尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；

如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；

如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。
使用where子句替换Having子句：避免使用having子句，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。

低效: SELECT JOB, AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
高效: SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP by JOB

大数据表查询优化

● 加缓存
● 主从复制，读写分离
● 垂直拆分、水平拆分

超大分页优化

通常我们是使用**<limit m,n>** + 合适的 order by 来实现分页查询，这种实现方式在没有任何索引条件支持的情况下，需要做大量的文件排序操作（file sort），性能将会非常得糟糕。如果有对应的索引，通常刚开始的分页查询效率会比较理想，但越往后，分页查询的性能就越差。
这是因为我们在使用 LIMIT 的时候，偏移量 M 在分页越靠后的时候，值就越大，数据库检索的数据也就越多。例如 LIMIT 10000,10 这样的查询，数据库需要查询 10010 条记录，最后返回 10 条记录。也就是说将会有 10000 条记录被查询出来没有被使用到。

数据库层面,这也是我们主要集中关注的(虽然收效没那么大)

类似于
select * from table where age > 20 limit 1000000,10
这种查询其实也是有可以优化的余地的. 这条语句需要load 1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为
select * from table where id in (select id from table where age > 20 limit 1000000,10)
这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以
select * from table where id > 1000000 limit 10
效率也是不错的,优化的可能性有许多种,但是核心思想一样,就是减少load的数据

从需求的角度减少这种请求并可以靠缓存预测性获取内容

主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.

【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回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

利用子查询中使用索引覆盖扫描快速定位：
select * from table where id> ( select id from table order by order_no limit 10000, 1) limit 20
利用

利用select id from table order by order_no limit 10000, 1中id使用了覆盖索引，先快速定位到要分页的起始行，然后再查询需要的其他行数据

优化select *

MySQL 常用的存储引擎有 MyISAM 和 InnoDB，其中 InnoDB 在默认创建主键时会创建主键索引，而主键索引属于聚簇索引，即在存储数据时，索引是基于 B + 树构成的，具体的行数据则存储在叶子节点。

而 MyISAM 默认创建的主键索引、二级索引以及 InnoDB 的二级索引都属于非聚簇索引，即在存储数据时，索引是基于 B + 树构成的，而叶子节点存储的是主键值。

假设我们的订单表是基于 InnoDB 存储引擎创建的，且存在 order_no、status 两列组成的组合索引。此时，我们需要根据订单号查询一张订单表的 status，如果我们使用 select * from order where order_no='xxx’来查询，则先会查询组合索引，通过组合索引获取到主键 ID，再通过主键 ID 去主键索引中获取对应行所有列的值。

如果我们使用 select order_no, status from order where order_no='xxx’来查询，则只会查询组合索引，通过组合索引获取到对应的 order_no 和 status 的值。

查询效率低的原因

不需要的列会增加数据传输时间和网络开销

用“SELECT * ”数据库需要解析更多的对象、字段、权限、属性等相关内容，在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。
增大网络开销；* 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。如果DB和应用程序不在同一台机器，这种开销非常明显
即使 mysql 服务器和客户端是在同一台机器上，使用的协议还是 tcp，通信也是需要额外的时间。

对于无用的大字段，如 varchar、blob、text，会增加 io 操作

准确来说，长度超过 728 字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次 io 操作。（MySQL InnoDB）

失去MySQL优化器“覆盖索引”策略优化的可能性

SELECT * 杜绝了覆盖索引的可能性，而基于MySQL优化器的“覆盖索引”策略又是速度极快，效率极高，业界极为推荐的查询优化方式。

例如，有一个表为t(a,b,c,d,e,f)，其中，a为主键，b列有索引。

那么，在磁盘上有两棵 B+ 树，即聚集索引和辅助索引（包括单列索引、联合索引），分别保存(a,b,c,d,e,f)和(a,b)，如果查询条件中where条件可以通过b列的索引过滤掉一部分记录，查询就会先走辅助索引，如果用户只需要a列和b列的数据，直接通过辅助索引就可以知道用户查询的数据。

如果用户使用select *，获取了不需要的数据，则首先通过辅助索引过滤数据，然后再通过聚集索引获取所有的列，这就多了一次b+树查询，速度必然会慢很多。

于辅助索引的数据比聚集索引少很多，很多情况下，通过辅助索引进行覆盖索引（通过索引就能获取用户需要的所有列），都不需要读磁盘，直接从内存取，而聚集索引很可能数据在磁盘（外存）中（取决于buffer pool的大小和命中率），这种情况下，一个是内存读，一个是磁盘读，速度差异就很显著了，几乎是数量级的差异。

子查询

什么是子查询

所谓子查询，是指在一个查询中嵌套了其他的若干查询，即在一个SELECT查询语句的WHERE或FROM子句中包含另一个SELECT查询语句。在查询语句中，外层SELECT查询语句称为主查询，WHERE子句中的SELECT查询语句被称为子查询，也被称为嵌套查询。
通过子查询可以实现多表查询，该查询语句中可能包含IN、ANY、ALL和EXISTS等关键字，除此之外还可能包含比较运算符。理论上，子查询可以出现在查询语句的任意位置，但是在实际开发中子查询经常出现在WHERE和FROM子句中。

原理

使用子查询可以进行 SELECT语句的嵌套查询,即一个 SELECT查询的结果作为另一个 SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多 个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活,但执行效率不高。执行子查询时, MYSQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。因此,子查询的速度会受到一定的影响。如果查询的数据量比较大,这种影响就会随之增大。

带比较运算符的子查询

子查询可以使用比较运算符。这些比较运算符包括=、!=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询中使用得非常广泛，如查询分数、年龄、价格和收入等。

例如查询学生表中与小孙同班级的学生：
SQL语句示例 和 查询结果：

select * from student 
where class_id=(select class_id from student where name='小孙');

注意：使用比较运算符时，select 子句获得的记录数不能大于1条（子查询结果只能是1条或0条）！！！

如果子查询结果多于一条时，例如子条件改为class_id>0：

带关键字IN的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这时可以使用IN关键字，SQL示例如下：

NOT IN的用法与IN相同。

例如查询与小红和小孙同一班级的学生信息

select * from student 
where class_id in (select class_id from student where name='小红' or name='小宋');

查询结果

关键字EXISTS的子查询

关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行；如果至少返回一行，那么EXISTS的结果为true，此时外层语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

例如子查询查找class_id为1005和1001的学生信息：

    select * from student 
    where exists(select * from student where class_id=1005);
select * from student 
    where exists(select * from student where class_id=1001);

查询结果

带关键字ANY的子查询

关键字ANY表示满足其中任一条件。使用关键ANY时，只要满足内层查询语句返回的结果中的任何一个就可以通过该条件来执行外层查询语句。例如，需要查询哪些学生可以获取奖学金，那么首先要有一张奖学金表，从表中查询出各种奖学金要求的最低分，只要一个同学的成绩大于等于不同奖学金最低分的任何一个，这个同学就可以获得奖学金。关键字ANY通常和比较运算符一起使用。例如，“>ANY”表示大于任何一个值，“=ANY”表示等于任何一个值。

例如查询学生成绩表中能拿奖学金的学生：

SQL语句示例和查询结果：

select name 姓名,math+chinese+english 总分 from score 
    where (math+chinese+english)>=any(select score from scholarship);

带关键字ALL的子查询

关键字ALL表示满足所有条件。使用关键字ALL时，只有满足内层查询语句返回的所有结果才可以执行外层查询语句。例如，需要查询哪些同学能够获得一等奖学金，首先要从奖学金表中查询出各种奖学金要求的最低分。因为一等奖学金要求的分数最高，只有当成绩高于所有奖学金最低分时，这个同学才可能获得一等奖学金。关键字ALL也经常与比较运算符一起使用。例如，“>ALL”表示大于所有值，“<ALL”表示小于所有值。 

 例如，查询score表中可以拿一等奖学金的学生：

select name 姓名 ,math+chinese+english 总分 from score
where (math+chinese+english)>=all(select score from scholarship)

查询没有拿到奖学金的学生

select name 姓名 ,math+chinese+english 总分 from score 
    where (math+chinese+english)<all(select score from scholarship);

子查询优化

在 MYSQL中,可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表,其
速度比子查询要快,如果查询中使用索引的话,性能会更好。连接之所以更有效率,是因为 MYSQL
不需要在内存中创建临时表来完成查询工作。

join连接查询

基本描述

MySQL的连接查询对于每一个使用MySQL数据库的开发者来说都是耳熟能详的东西，包含了内连接inner join和外连接outer join，外连接又分为左（外）链接left join 和右（外）连接right join。通过它实现将两个或者多个表中符合条件的数据进行组合返回结果集。
以两个表为例：第一个需要查询的表A表称之为“驱动表”，第二个表B表称之为“被驱动表”。
内连接即就两个表数据的笛卡尔积（两个表中无论谁作为驱动表，记录得到的笛卡尔积也一定是一样的，所以在内连接中驱动表和被驱动表是可以互换的，只是在这里方便说明，设定A表为驱动表，B表为被驱动表），带上查询条件以后，如果驱动表A表中的记录在被驱动表B表中找不到对应记录，则该记录会被丢弃，不会加入到最终的结果集中。
外连接划分成左或者右（外）连接，来源于驱动表的不同，左（外）连接即意味着左边的表为驱动表，右（外）连接亦然。
查询条件：
一般情况下，我们把只涉及到单表的过滤条件放在where子句中，把涉及到两表的过滤条件放在ON子句中，同时一般将ON子句中的过滤条件称为连接条件。
where子句中无论是内外连接，不符合where过滤条件的结果都会被丢弃，不会加入到返回的结果集中。
ON子句中对于外连接，被驱动表中如果们无法找到匹配ON过滤条件的记录，则会填充一条各个字段为null占位的记录到结果集中，并且ON子句是专门为外连接驱动表的记录在被驱动表中找不到对应的记录这个场景下提出的，在内连接中MySQL会将其和where子句同样对待，所以在内连接查询时不要求一定要写ON子句。

原理

MySQL在处理连接时是以嵌套循环连接算法的方式来实现的。
嵌套循环连接，顾名思义就是一层层嵌套的循环判断，伪代码如下：

for each row in t1{
	for each row in t2{
		for each row in t3{
		// 匹配判断
		...
		}
	}
}

多表连接的时候，先从驱动表t1中进行单表查询得到结果集r1，然后拿r1结果集中的每条数据挨个作为条件去和被驱动表t2中进行单表查询得到的结果集r2进行匹配,最终得到匹配结果r12，然后使用r12结果集中的每条数据挨个作为条件去和被驱动表t3中进行单表查询得到的结果集r3进行匹配，得到最终结果集并返回，当有很多个表连接查询的时候就是不断重复上面的步骤，简单来说就是拿上次（连接）查询的结果作为驱动表，来和被驱动表进行单表查询得到的结果集进行匹配。
上边强调了每条数据挨个作为条件这几个字眼，为什么要强调这几个字眼呢，就要说到嵌套循环连接算法的问题了，在连接查询过程中，驱动表只访问一次，但是被驱动表需要被访问多次，我们知道MySQL的数据都是持久化存储在硬盘上的，而要匹配驱动表和被驱动表中的数据是发生在内存中的，那么使用每条数据挨个作为条件和被驱动表单表查询结果进行匹配时都要经历一波IO操作，当驱动表和被驱动表中的数据量很大时，这个查询就会相当耗时和消耗资源，并且从伪代码可以看出，N多个表连接查询时，算法时间复杂度就会成指数增长，这也是阿里巴巴开发手册中要求join不能超过3个表的原因之一。

驱动表

当连接查询没有where条件时

• left join 前面的表是驱动表，后面的表是被驱动表
• right join 后面的表是驱动表，前面的表是被驱动表
• inner join / join 会自动选择表数据比较少的作为驱动表
• straight_join(≈join) 直接选择左边的表作为驱动表（语义上与join类似，但去除了join自动选择小表作为驱动表的特性）

当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表

假设有表如右边：t1与t2表完全一样，a字段有索引，b无索引，t1有100条数据，t2有1000条数据

1. 若被驱动表有索引，那么其执行算法为：Index Nested-Loop Join（NLJ），示例如下：
   执行语句：select * from t1 straight_join t2 on (t1.a=t2.a)；由于被驱动表t2.a是有索引的，其执行逻辑如下：

• 从表t1中读入一行数据 R；
• 从数据行R中，取出a字段到表t2里去查找；
• 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
• 重复执行步骤1到3，直到表t1的末尾循环结束。
• 如果一条join语句的Extra字段什么都没写的话，就表示使用的是NLJ算法
  

2. 若被驱动表无索引，那么其执行算法为：Block Nested-Loop Join（BLJ）（Block 块，每次都会取一块数据到内存以减少I/O的开销），示例如下：
   执行语句：select * from t1 straight_join t2 on (t1.a=t2.b)；由于被驱动表t2.b是没有索引的，其执行逻辑如下：

• 把驱动表t1的数据读入线程内存join_buffer（无序数组）中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
• 顺序遍历表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
  

3. 另外还有一种算法为Simple Nested-Loop Join（SLJ），其逻辑为：顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分返回。
   另外，Innodb会为每个数据表分配一个存储在磁盘的 表名.ibd 文件，若关联的表过多，将会导致查询的时候磁盘的磁头移动次数过多，从而影响性能
   所以实践中，尽可能减少Join语句中的NestedLoop的循环次数：“永远用小结果集驱动大的结果集”

• 用小结果集驱动大结果集，将筛选结果小的表（在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”）首先连接，再去连接结果集比较大的表，尽量减少join语句中的Nested Loop的循环总次数

• 优先优化Nested Loop的内层循环（也就是最外层的Join连接），因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；

• 对被驱动表的join字段上建立索引；

• 当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。

• 尽量用inner join(因为其会自动选择小表去驱动大表).避免 LEFT JOIN (一般我们使用Left Join的场景是大表驱动小表)和NULL，那么如何优化Left Join呢？
  1、条件中尽量能够过滤一些行将驱动表变得小一点，用小表去驱动大表
  2、右表的条件列一定要加上索引（主键、唯一索引、前缀索引等），最好能够使type达到range及以上（ref,eq_ref,const,system）

• 适当地在表里面添加冗余信息来减少join的次数

• 使用更快的固态硬盘

性能优化，left join 是由左边决定的，左边一定都有，所以右边是我们的关键点，建立索引要建在右边。当然如果索引是在左边的，我们可以考虑使用右连接，如下

select * from atable left join btable on atable.aid=btable.bid;
-- 最好在bid上建索引

Tips：Join左连接在右边建立索引；组合索引则尽量将数据量大的放在左边，在左边建立索引

优化

使用索引

上边讲到连接查询是使用嵌套循环连接算法来实现的，驱动表只访问一次，被驱动表需要被访问多次，在没有使用索引的情况下，驱动表和被驱动表每次单表查询都会是全表扫描，那么既然连接查询的算法是固定的，就从每次查询的速度优化入手，这时候就必须提到索引，索引是为了提高查询效率的数据结构，如果在一般情况下每次驱动表和被驱动表的单表查询是都使用到索引，查询效率就会大幅提高（不一般的情况下就是查询优化器觉得索引查询的效率低于全表扫描，就会放弃索引查询，这时候是不使用索引的）。

基于块的嵌套循环连接

上面的基本嵌套循环连接查询和使用索引都是被驱动表中得到的结果集每次只和一条驱动表中记录进行匹配，那么在这个过程中驱动表中有多少条数据，就要访问多少次被驱动表进行单表查询，访问被驱动表就是要将被驱动表中的记录从硬盘加载到内存中，然后在内存中和驱动表中的数据进行匹配，然后将被驱动表中的记录从内存中移除，然后再从驱动表中拿出下一条记录重复这个步骤，由此可见这种方式会带来大量的IO操作，所以MySQL的设计者就提出了基于块的嵌套循环连接来减少被驱动表的访问次数，减少对应的IO操作。
基于块的嵌套循环连接引入了“join buffer”概念，即在进行连接查询前申请一块固定大小的内存，将驱动表中结果集中若干条记录存储在join buffer中，然后扫描被驱动表，将被驱动中的每一条记录一次性和join buffer中的多条记录进行比较匹配，从而减少被驱动表的访问次数。
join buffer 的大小可以通过系统变量“join_buffer_size”进行配置，默认大小为256KB，最小可以设置为128KB，既然是要一次性和驱动表中的多条记录进行匹配，那么最理想的情况就是驱动表中符合条件的所有数据都存在于join buffer中，那么只需要访问一次被驱动表就可以完成所有的连接查询，所以在优化连接查询时，可以适当增大join_buffer_size的大小，使得驱动表中的记录可以尽可能多的存储到join buffer中，从而减少被驱动表的访问次数。同时在这个过程中尽量不要使用select *进行查询，指定确实需要的字段，减少存储到join buffer中每条数据的大小，这样在固定大小的join_buffer中可以存放更多的驱动表记录，减少被驱动表的访问次数。

慢查询分析

我们在执行一条 SQL 语句时，要想知道这个 SQL 先后查询了哪些表，是否使用了索引，这些数据从哪里获取到，获取到数据遍历了多少行数据等等，我们可以通过 EXPLAIN 命令来查看这些执行信息。这些执行信息被统称为执行计划。作用：

• 表的读取顺序；
• 数据读取操作的操作类型；
• 哪些索引可以使用；
• 那些索引被实际使用；
• 表之间的引用；
• 每张表有多少行被优化器查询；

开启sql慢查询

• my.ini文件开启
  修超过2秒就是慢查询，以及慢查询log日志记录（ slow_query_log）
  
• sql语句开启
  

分析慢查询日志

利用explain关键字模拟优化器执行sql查询语句分析慢查询语句

id

select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序；
有三种情况：

• id相同，执行顺序由上至下；
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，先被执行 ；
• id有相同，有不同，相同的可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，先被执行；（table 列中的 derived 的意思是衍生，由 id 衍生的）

select_type

table

显示这一行数据是哪个表的；

type

性能高低从上到下的顺序；

possible_keys

显示可能应用在这张表中的索引，可以是多个；
查询涉及到的字段上若存在索引，则被列出，但不一定被使用；

key

实际使用到的索引，null代表没使用索引。
查询若使用覆盖索引，则该索引仅出现在key列表中；

key_len

表示索引中使用的字节数，越小越好，显示的值为索引字段的最大可能长度，并非实际使用长度。

ref

显示索引的哪一列被使用了，最好是常量；

rows

根据表统计信息及索引选用情况，大致估计出找到所需的记录所需要读取的行数；

extra

包含不适合在其他列展示的但重要的额外信息

usingwhere 使用where语句；
usingbuffer 使用缓冲池；

如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
所以无论那个表大，用not exists都比not in要快。not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。

通过 Show Profile 分析 SQL 执行性能

上述通过 EXPLAIN 分析执行计划，仅仅是停留在分析 SQL 的外部的执行情况，如果我们想要深入到 MySQL 内核中，从执行线程的状态和时间来分析的话，这个时候我们就可以选择 Profile。

Profile 除了可以分析执行线程的状态和时间，还支持进一步选择 ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES 等类型来查询 SQL 语句在不同系统资源上所消耗的时间。

示例查询分析

Join连接查询分析

单表分析

例：select id from A where c1 = 1 and c2 > 1 order by v1 desc limit 1;
建立联合索引（c1,c2,v1），但explain时候发现 type 是range，extra中使用using filesort，这需要优化；
产生原因：按照BTree索引工作原理，先排序c1，如果c1相同，排序c2，c2相同在排序v1，当c2字段在联合索引中处于中间位置，因为c2 > 1条件是一个范围值（range），MySQL无法利用索引在对后面的v1部分进行索引。所以建立（c1，v1）解决这个问题。

双表分析

左连接加在右表的索引，右连接加在左表的索引；
Left Join 条件用于确定如何从右表搜索行，左边数据一定有，所以右边数据一定要建索引。

三表分析

建索引和双表的原理相同；

总结

尽可能减少Join语句的NestedLoop的循环总次数，永远用小结果集驱动大的结果集；
优先优化NestedLoop的内层循环；
保证Join语句中被驱动表上Join条件字段已经被索引；