MySQL逻辑架构简介

和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

连接层

最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的 通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证 安全接入的客户端提供线程。同样在该层上可以实现基于 SSL 的安全链接。服务器也会为安全接入的每个客户端验 证它所具有的操作权限。

服务层

管理服务和实用程序：

• SQL Interface:SQL 接口。接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface
• Parser：解析器。 SQL 命令传递到解析器的时候会被解析器验证和解析
• Optimizer：查询优化器。 SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有 where 条件时，优化器来决定先投影还是先过滤。
• Cache 和 Buffer：查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取 数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等。

引擎层

存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

MyISAM和InnoDB对比：

存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

sql回顾

sql的执行顺序

-- 手写的顺序
select distinct <select_list> 
from <left_table> <join_type>
join <right_table> on <join_condition>
where <where_condition>
group by <group_by_list>
having <having_condition>
order by <order_by_condition>
limit <limit_number>
-- 机读的顺序,优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序.下面是经常出现的查询顺序:
from <left_table>
on <join_condition>
<join_type> join <right_table>
where <where_condition>
group by <group_by_list>
having <having_condition>
select 
distinct <select_list> 
order by <order_by_condition>
limit <limit_number>

常见的Join查询

其中mysql不支持外连接，6 = 1 union 2; 7 = 4 union 5。

索引优化分析

索引简介

性能下降原因

• 查询语句写的烂
• 索引失效
• 关联查询太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲、线程数等）

索引的概念

MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质: 索引是数据结构。可以简单理解为排好序的快速查找数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例:

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个 右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引等。

索引的优缺点

优势:

• 提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势:

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间 的。

索引的分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 复合索引：即一个索引包含多个列
• 唯一索引：索引列的值必须唯一，但允许有空值
• 主键索引：它是一种特殊的唯一索引，不允许有空值。 设定为主键后数据库会自动建立索引，innodb为聚簇索引

基本语法

-- 创建索引
CREATE [UNIQUE ] INDEX [indexName] ON table_name(columnname(length));
ALTER table_name ADD [UNIQUE] index [indexName] ON (columnname(legnth));

-- 删除索引
DROP INDEX [indexName] ON table_name;

-- 查看索引
SHOW INDEX FROM table_name\G;

-- 添加数据表的索引
-- 主键索引：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
-- 唯一索引：这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。
ALTER TABLE tbl_name ADD UNIQUE KEY (column_list);
-- 普通索引：索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
-- 全文索引：该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

MySQL索引结构

• Hash索引：

  • 哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

    • 无法用于排序与分组；
    • 只支持精确查找，无法用于部分查找和范围查找。

  • InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

• full-text全文索引：

  • MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

    • 查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
    • 全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。
    • InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
• R-tree索引：MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。必须使用 GIS 相关的函数来维护数据。
• B+ tree索引

MySQL 使用的是 B+ tree 索引。

B Tree

B Tree 指的一颗多路平衡查找树，所有叶子节点位于同一层。

• 一颗m阶的B树的根节点的关键字key数量范围：[1,m-1]，非根节点的key范围：[m/2向上取整,m-1]。在B+树中，则是[1,m]和[m/2向上取整,m]。
• 阶数m 表示 此树的结点 最多 有 多少个孩子结点。
• 每个节点都存有索引和数据，也就是对应的key和value。

初始化介绍：

一颗 b 树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)，如磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3， P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。 真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。 非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如 17、35 并不真实存在于数据表中。

查找过程：

如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间因为非常短(相比磁盘的 IO)可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指 针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总计三次 IO。

真实的情况是，3 层的 B 树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的， 如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本非常非常高。

B+ Tree

B+ Tree 是 B 树的一种变形，它是基于 B Tree 和叶子节点顺序访问指针进行实现，通常用于数据库和操作系统的文件系统中。

• B+ 树有两种类型的节点：内部节点和叶子节点，内部节点不存储数据，只存储索引，数据都存在叶子节点。
• 内部节点中的 key 都按照从小到大的顺序排列，对于内部节点中的一个 key，左子树中的所有 key 都小于它，右子树中的 key 都大于等于它，叶子节点的记录也是按照从小到大排列的。
• 每个叶子节点都存有相邻叶子节点的指针。

B+树一般多少层 🤔

• 一般不超过3层，3层的B+树差不多可以存2千万条数据。innodb中是页为存取单位的，一个页的大小是16KB。通常一条记录的数据大小为1k，那么当个叶子节点(页)可以存放16条记录。对于非叶子节点的话，只存放了关键字key和指针，主键一般是bigint类型占8字节，innoDB中的指针为6字节，一共14字节。那么一个非叶子节点就能存放16*1024/14=1170个字节。所以一个高度为3的B+树就可以存放 1170*1170*16 ，大概两千万条数据吧。

二叉查找树和平衡二叉树

二叉查找树在极端情况下会退化成链表。平衡二叉树的话在二叉查找树的基础上增加了左旋/右旋的自平衡机制，保证树的左右两个子树的层级最多相差1。但还是会存在一些问题，因为一个根节点只能有两个孩子节点，数据多了就会导致树的高度太高，从而增加了磁盘IO的次数。另外它也不支持快速的范围查询，需要遍历整颗树。因此就有了B树和B+树。

B + 树与 B 树的比较

• B+ 树的磁盘 IO 更低：B+ 树的内部节点没有存储数据的指针。因此其内部节点相对 B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

• B+ 树的查询效率更加稳定：由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

• B+ 树元素遍历效率高：B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 B 树不支持这样的遍历方式。

B + 树与红黑树的比较

• 磁盘 IO 次数：B+ 树一个节点可以存储多个元素，相对于红黑树的树高更低，磁盘 IO 次数更少。特别是插入的时候，红黑树需要进行节点颜色调整，对于频繁的插入而言，这是一个耗时的过程，而b+树仅仅是页分裂问题。

• B+ 树元素遍历效率高：B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而 红黑树不支持这样的遍历方式。

• B+ 树的查询效率更加稳定：由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

B+树与跳表的比较

• 跳表，跳表的每一层数据越往下越大，会导致一次io读取的页放不下，增加了io开销。而B+树的节点大小被设置为页的大小，使得一次 I/O 就能完全载入一个节点，极大减少了磁盘 I/O 次数，提高了索引结构的效率。

索引的创建时机

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效（因为存在回表的情况）；
• 对于中到大型的表，索引就非常有效；
• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。(分库分表)
• 索引适合在高频的读操作（外键/排序/分组等字段）中建立，且不适合高频的增删改操作。
• 尽量选择区分度大字段建立索引，建立符合索引也是一样，尽量把区分度大的字段放在前面。

Explain性能分析：

Mysql Query Optimizer

mysql常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读出数据的时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top,free,iosat和vmstat来查看系统的性能状态

Explain

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分 析你的查询语句或是表结构的性能瓶颈。

能干嘛：

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

如何使用：

用法: Explain+SQL 语句。

Explain 执行后返回的信息:

字段解释：

• id：select 查询的序列号,包含一组数字，表示查询中执行 select 子句或操作表的顺序。

  • id 相同，执行顺序由上至下

    

  • id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

    

  • 有相同也有不同；id 如果相同，可以认为是一组，从上往下顺序执行;在所有组中，id 值越大，优先级越高，越先执行。衍生 = DERIVED

  • 关注点：id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。

• select_type：代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
  • SIMPLE 简单的 select 查询,查询中不包含子查询或者 UNION
  • PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为 Primary
  • DERIVED 在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里。
  • SUBQUERY 在SELECT或WHERE列表中包含了子查询
  • DEPEDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层
  • UNCACHEABLE SUBQUERY 无法使用缓存的子查询
  • UNION 若第二个SELECT出现在UNION之后，则被标记为UNION; 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
  • UNION RESULT 从UNION表获取结果的SELECT
• table：这个数据是基于哪张表的。
• type：查询的访问类型。
  • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
  • const：表最多有一个匹配行，表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，mysql就能将该查询转换为一个常量。
  • eq_ref：唯一性索引扫描，使用primary key 或者 unique 作为多表链接的条件，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
  • ref： 非唯一性索引扫描，返回匹配某个单独值的所在行。本质上也是一种索引访问，它返回索引匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
  • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪些索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
  • index：全表扫描，但扫描表的方式是按索引的次序进行，先读索引，再读实际的行。主要优点是避免了排序。因为索引是排好的。index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取的）
  • all：全表扫描的方式找到匹配的行。
  • 结果值从最好到最坏依次是system > const > eq_ref > ref > range > index >ALL，一般来说，得保证查询至少达到 range 级别，最好能达到 ref(在百万或千万级别的查询下)。完整版：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL。
• possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一 定被查询实际使用。

• key：实际使用的索引。如果为NULL，则没有使用索引；查询中若使用了覆盖索引，则该索引仅出现在key列表中。

• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
• rows：显示 MySQL 认为它执行查询时必须检查的行数；根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数。越少越好!
• Extra：其他的额外重要的信息。
  • Using filesort 🙅：说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。
  • Using temporary 🙅：使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
  • Using index：🉑️
    • Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错! 覆盖索引：就是select的数据列只用从索引就能够取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。 使用时需要注意：
      • 一点要注意select列表中只取出需要的列，不可select *
      • 因为如果将所有字段一起做索引会导致索引文件过大，导致查询性能下降
    • 如果同时出现 using where，表明索引被用来执行索引键值的查找;
    • 如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。
  • Using where：表明使用了 where 过滤。
  • Using join buffer：使用了连接缓存。
  • impossible where：where 子句的值为 false，不能用来获取任何元组。
  • select tables optimized away：在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
  • distinct：优化distinct操作，在找到第一匹配的原组后即停止找同样值的动作。
• key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
  • key_len 字段能够帮你检查是否充分的利用上了索引。key_len 越长，说明索引使用的越充分。
  • 在不损失精度的情况下，长度越短越好。
  • key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

key_len的计算：

1. 先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
2. 如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 要乘 3,GBK 要乘 2，
3. varchar 这种动态字符串要加 2 个字节
4. 允许为空的字段要加 1 个字节

第一组:key_len=age 的字节长度+name 的字节长度=4+1 + ( 20*3+2)=5+62=67 第二组:key_len=age 的字节长度=4+1=5

热身Case:

写出以下sql对应的执行顺序：

ANSWER：

1. select_type 为union，说明第四个select是union里的第二个select，最先执行select name, id from t2
2. id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derivedselect id,name frin t1 where other_cloumn=
3. select列表中的值查询select_type为subquery，为整个查询中的第二个selectselect name,id from t2

4. id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为<derived3> , 表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select select d1.name...

5. 代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作 两个结果的union操作

索引优化

索引分析

1.单表优化案例

准备数据

CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT (10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL , 
`views` INT(10) UNSIGNED NOT NULL , 
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
SELECT * FROM ARTICLE;

Task：查询category为1且comments大于1情况下，views最多的article_id。

-- V1.1 查询语句
select id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;
-- 结论：type是ALL，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况。优化是必须的

-- 开始优化
-- V1.2 新建索引ccv 
create index idx_article_ccv on article(category_id,comments,views);
-- 结论：type变成了range是可以接受的；但是但是仍然存在Using filesort是无法接受的。
-- 为什么建立了索引还是没有用？  因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id 则在排序comments，comments相同则在排序views。当comments字段在联合索引里处于中间位置时，因为comments>1条件是一个范围值导致mysql无法利用索引在对后面的views部分进行检索，即range类型查询字段后面的索引无效。

-- V1.3 新建索引cv
drop index idx_article_ccv in article;
create index idx_article_ccv on article(category_id,views);
-- 结论：可以看到，type变成了ref，Using filesort也消失了。

2.两表分析案例

准备数据

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20)));
 
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

Task：外键关联查询

-- V1.1 左连接
select * from class left join book on class.card=book.card;
-- 结论： type都为All

-- 开始优化
-- V1.2 在右表book中添加索引优化
alter table book add index Y(card);
-- 结论：可以看到第二行的type变为了ref，rows优化也比较明显。这是由左连接特性决定的，left join条件用于确定如何从右表搜索行，左边一定都有，索引右边是我们的关键点，一定要建立索引。right join同理。

3.三表分析案例

准备数据

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)ENGINE = INNODB;

INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

Task：外键关联查询

-- V1.1 左连接
select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
-- 结论： type都为All

-- 开始优化
-- V1.2 在右表book和phone中添加索引优化
alter table book add index Y(card);
alter table phone add index Y(card);
-- 结论：后两行的type都是ref且总rows优化很好。因此索引最好设置在需要经常查询的字段中。

结论

1. 尽可能减少Join语句中的NestedLoop的循环总数；“永远用小结果集驱动大的结果集”。
2. 优先优化NestedLoop的内层循环；
3. 保证Join语句中被驱动表上Join条件字段已经被索引；
4. 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。

索引失效

数据准备：

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

案例举例：

1. 全值匹配我最爱

   查询的字段按照顺序在索引中都可以匹配到！

2. 最佳左前缀法则

   如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。查询字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效!

   原因：使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

   结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

3. 不要在索引列上做任何操作（计算、函数、自动或手动类型转换），会导致索引失效而转向全表扫描

4. 索引列上不能有范围查询

5. 尽量使用覆盖索引，不要写 select *

6. 使用不等于(!= 或者<>)的时候，有时会无法使用索引会导致全表扫描

7. 字段的 is not null 和 is null

8. like 以通配符开头（‘%abc…’）mysql索引失效会变成全表扫描的操作

   问题：解决like ‘%字符串%’时索引不被使用的方法？

   ANSWER：使用覆盖索引；建的索引包含要查寻和匹配的字段

9. ⚠️ 字符串(varchar)不加单引号索引失效，会导致自动类型转换
10. 减少使用 or，用它来连接时会索引失效

练习：假设index(a,b,c)

group by / order by 优化

数据准备：

create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10));

insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

问题：我们创建了复合索引idx_test03_c1234，根据一下sql分析索引使用情况。

-- 会使用索引，mysql优化器会自动调整顺序为复合索引idx_test03_c1234的顺序
select * from test03 where c4='a4' and c3='a3' and c2='a2' and c1='a1' 

-- 使用到了索引123
select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4' 

-- 使用到了索引1234，进行了自动调整，且范围查找在最后
select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3' 

-- order by
-- 使用到了索引12;严格来说c3也用到了，不过只用于排序并没有查找
select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
select * from test03 where c1='a1' and c2='a2' order by c3;

-- 使用到了索引12;Using filesort
select * from test03 where c1='a1' and c2='a2' order by c4;

-- 使用到了索引1;c2,c3用于排序，无filesort
select * from test03 where c1='a1' and c5='a5' order by c2,c3;

-- 使用到了索引1;有filesort，排序需要按照索引的顺序
select * from test03 where c1='a1' and c5='a5' order by c3,c2;

-- 使用到了索引12;c2,c3用于排序，无filesort
select * from test03 where c1='a1' and c2='a2' order by c2,c3;
select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
-- ❗️特例 排序字段已经是常量了 无filesort
select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;

-- group by ❗️分组之前必排序
-- 使用到了索引c1;c2,c3用于排序
select * from test03 where c1='a1' and c4='a4' group by c2,c3;

-- ⚠️ Using temporary;Using filesort
select * from test03 where c1='a1' and c4='a4' group by c3,c2;

结论：定值、范围还是排序，一般order by是给个范围；group by 基本上都需要进行排序，会有临时表产生。

在使用group by时产生临时表的原因：

• 如果GROUP BY 的列没有索引,产生临时表.
• 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
• 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
• 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
• 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.
• 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.
• GROUP BY 和 ORDER BY 的列一样且是主键，但SELECT 列含有除GROUP BY列之外的列，也会产生临时表

产生临时表的原因：

• UNION查询；
• 表连接中，ORDER BY的列不是驱动表中的；(指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]，未指定联接条件时，行数少的表为[驱动表]，多表联合查询时)。永远用小结果集驱动大的结果集，保证Join语句中被驱动表上Join条件字段已经被索引。
• DISTINCT查询并且加上ORDER BY时；
• FROM中的子查询；

总结

首先是要找出需要优化的慢SQL。这个我一般会先开启慢查询日志功能，按照业务需求设定时间，然后先用SQLNoCACHE排除缓存的干扰。找到后使用explain执行计划去分析这个语句到底有没有走索引。一般需要关注的字段有实际使用的索引key字段，查询涉及到的行数rows，查询类型type和一些额外的信息Extra查看发生文件排序filesort和产生临时表temporary。

优化的方向我一般会考虑这么几点，首先能使用覆盖索引的话就尽量不要使用select * ，避免回表操作。然后使用复合索引的话需要遵守最左匹配原则，where后的查询条件必须按照索引建立时的顺序，一旦跳过某个字段或遇到范围查询都会停止匹配。还有就是检查有没有在索引列做计算、聚合、类型转换等操作，不然导致索引失效，尤其是字符串不加单引号会导致自动类型转换。还有使用like进行模糊查询时，通配符%不能出现在开头，不然也会导致索引失效。 还有使用order by排序的话也要尽量和索引顺序保持一致，避免产生文件排序。另外对于批量操作一大批数据的时候，最好先获取最大id和最小id分批次去更新，这样既能快速完成更新，又能保证主从复制不会出现延迟。对于多连接的查询，要分解成对每一个表进行一次单表查询，然后在后台程序进行关联，让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。

另外就是一些建立索引的策略，尽量选取区分度大和经常使用条件查询、排序、分组的字段上建立索引。经常与其他表进行连接的表，在连接字段上也应该建立索引。还有尽可能的扩展索引，而不是建立新的索引，不过要注意复合索引不要含有NULL值，不然这一列就是无效的，另外如果复合索引中的字段进程被单独查询，还是拆成单独的比较好。对于比较长的VARCHAR类型的字段要使用前缀索引，选取的长度根据区分度高低来确定。

准备工作：

• 开启MYSQL的show profile功能或者慢查询日志，执行需要分析的sql语句。分析sql的时候先用SQLNoCache 排除缓存的干扰。
• 然后用explain执行计划去分析，通过type分析索引的使用情况。

优化可以以下几点：

• 尽量使用覆盖索引，不要用select *，以免造成回表操作。
• 使用复合索引时需要遵循 最左匹配原则，过滤条件必须要按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。遇到范围查询 （>,<,BETWEEN,LIKE）也会停止匹配。如果只是顺序的话MySQL的优化器会自动优化。
• 在进行查询时，不要在索引列做计算、行数、类型转换等操作，会导致索引失效。比如字符串不加单引导则会自动类型转换，导致索引失效。
• like 语句如果通配符 % 不出现在开头，才能用到索引。
• order by 排序要跟索引保持一致，充分利用索引的有序性来消除排序带来的CPU开销。
• 对于limit分页，越往后面翻页，SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。
• 批量更新大批数据的时候，先获取where条件中的最小id和最大id，然后分批次去更新，这样既能快速完成更新，又能保证主从复制不会出现延迟。

另外还有一些建立索引时的策略：

• 建立索引的时候，让区分度最高的索引列放在前面。另外复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL。

• 尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

• 前缀索引：对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定，也可以通过反转 截取等操作来增加区分度。