MySQL调优

一.索引

什么是索引

💡什么是索引？
提到索引， 我们想到的是查询慢了 设置索引呗！但是索引为什么起作用？设置了索引为什么还是慢我们其实不是很清楚。
在关系数据库中，索引是一种数据结构，他将数据提前按照一定的规则进行排序和组织， 能够帮助快速定位到数据记录的数据，加快数据库表中数据的查找和访问速度。
像书籍的目录、文件夹、标签 、房号… 都可以帮助我们快速定位，都可以视为索引。

能实现快速定位数据的一种存储结构，其设计思想是以空间换时间。

索引的种类

在MySQL中索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。常见的索引分类如下：

• 按数据结构分类：B+tree索引、Hash索引、Full-text索引。
• 按物理存储分类：聚集索引、非聚集索引。
• 按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
• 按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）。

常见索引数据结构和区别3

• 二叉树、红黑树、B树 、B+树

• 区别：树的高度影响获取数据的性能（每一个树节点都是一次磁盘I/O)

二叉树：

特点：每个节点最多有两个子节,大在右，小在左 ，数据随机性情况下树杈越明显。

如果数据是按顺序依次进入：
树的高度则会很高（就是一个链表结构）， 此时元素的查找效率就等于链表查询O(n)，数据检索效率将极为低

极端的情况下 就是一个链表结构（如下图），此时元素的查找效率就等于链表查询O(n)。

红黑树（平衡二叉树）

虽通过自旋平衡，子节点会自动分叉为2个分支，从而减少树的高度，当数据有序插入时比二叉树数据检索性能更佳.
但是如果 数据量过大，节点个数就越多，树高度也会增高（也就是树的深度越深)，增加磁盘I/O次数，影响查询效率。

B树

B树的出现可以解决树高度的问题。之所以是B树，而并不是名称中"xxx二叉树"，就是它不再限制一个父节点中只能有两个子节点，而是允许 M 个子节点（M > 2）。不仅如此，B树的一个节点可以存储多个元素，相比较于前面的那些二叉树数据结构又将整体的树高度降低了。

B 树的节点可以包含有多个字节点，所以 B树是一棵多叉树，它的每一个节点包含的最多子节点数量的称为B树的阶。如下图是一颗3阶的B树。

当一颗3阶的B树查找 7这个的元素时的流程是怎么样的？

先从根节点出发 ，判断 7在4和8之间，根据P2存储指针6的节点，判断7大于6 最后指针找到叶子节点。也就找到有匹配7的键值。

可以发现一颗3阶的B树在查找叶子节点时，由于树高度只有 3，所以查找过程最多只需要3次的磁盘I/O操作。
数据量不大时可能不太真切。但当数据量大时，节点也会随着增多；此时如果还是前面的自平衡二叉树的场景下，由于二叉树只能最多2个叶子节点的约束，也只能纵向去的去扩展子节点，树的高度会很高，意味着需要更多的操作磁盘I/O次数。而B树则可以通过横向扩展节点从而降低树的高度，所以效率自然要比二叉树效率更高。（直白说就是变矮胖了)

小结

看到这，相信你也知道如果B树这么适合，也就没有接下来B+树的什么事了。
接着，那为什么不用B树，而用了B+树呢？
你看啊，B树其实已经满足了我们最前面所要满足的条件，减少磁盘I/O操作，同时支持按区间查找。但注意，虽然B树支持按区间查找，但并不高效。例如上面的例子中，B树能高效的通过等值查询 15 这个值，但不方便查询出一个区间内3 ~ 10区间内所有数的结果。因为当B树做范围查询时需要使用中序遍历，那么父节点和子节点也就需要不断的来回切换涉及了多个节点会给磁盘I/O带来很多负担。

B+tree索引

在MySQL中为什么会选用B+tree做索引结构呢？
B+tree 是在B树基础上的一种优化，其更适合做存储索引结构。在 B+tree 中，非叶子节点上仅存储键值，不存储数据；而所有数据记录均存储在叶子节点上，并且数据是按照顺序排列的。此外在 B+tree 中各个数据页之间是通过双向链表连接的。B+tree 的结构图如下：

B树和B+树的区别，MySQL为什么要选择B+树作为默认索引的数据结构

B树

B+树

B+tree 结构实现数据索引具有如下优点：

1. 非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘I/O的次数就会大大减少，数据查询的效率也会更快。

2. 所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

3. 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操作。

Hash索引

Hash索引其实用的不多，最主要是因为最常见的存储引擎InnoDB不支持显示地创建Hash索引，只支持自适应Hash索引。
虽然可以使用sql语句在InnoDB显示声明Hash索引，但是其实是不生效的

对name字段建立Hash索引，但是通过show index from 表名就会发现实际还是B+树

在存储引擎中，Memory引擎支持Hash索引
Hash索引其实有点像Java中的HashMap底层的数据结构，他也有很多的槽，存的也是键值对，键值为索引列，值为数据的这条数据的行指针，通过行指针就可以找到数据
假设现在user表用Memory存储引擎，对name字段建立Hash索引，表中插入三条数据

Hash索引会对索引列name的值进行Hash计算，然后找到对应的槽下面，如下图所示

当遇到name字段的Hash值相同时，也就是Hash冲突，就会形成一个链表，比如有name=张三有两条数据，就会形成一个链表。
之后如果要查name=李四的数据，只需要对李四进行Hash计算，找到对应的槽，遍历链表，取出name=李四对应的行指针，然后根据行指针去查找对应的数据。

Hash索引优缺点

hash索引只能用于等值比较，所以查询效率非常高
不支持范围查询，也不支持排序，因为索引列的分布是无序的

什么是聚簇索引与非聚集索引和区别？

按物理存储分类：InnoDB的存储方式是聚集索引，MyISAM的存储方式是非聚集索引。

聚簇索引

1. 聚簇索引将数据存储在索引树的叶子节点上。
2. 聚簇索引可以减少一次查询，因为查询索引树的同时就能获取到数据。
3. 聚簇索引的缺点是，对数据进行修改或删除操作时需要更新索引树，会增加系统的开销。
4. 聚簇索引通常用于数据库系统中，主要用于提高查询效率。

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

1. 非聚簇索引不将数据存储在索引树的叶子节点上，而是存储在数据页中。
2. 非聚簇索引在查询数据时需要两次查询，一次查询索引树，获取数据页的地址，再通过数据页的地址查询数据（通常情况下来说是的，但如果索引覆盖的话实际上是不用回表的)。
3. 非聚簇索引的优点是，对数据进行修改或删除操作时不需要更新索引树，减少了系统的开销。
4. 非聚簇索引通常用于数据库系统中，主要用于提高数据更新和删除操作的效率。

二级索引

在MySQL中，创建一张表时会默认为主键创建聚簇索引，B+树将表中所有的数据组织起来，即数据就是索引主键所以在InnoDB里，主键索引也被称为聚簇索引，索引的叶子节点存的是整行数据。而除了聚簇索引以外的所有索引都称为二级索引，二级索引的叶子节点内容是主键的值。
在MySQL中主键索引的叶子节点存的是整行数据，而二级索引叶子节点内容是主键的值.

回表

讲完二级索引，接下来讲一讲如何使用二级索引查找数据。
这里假设对name字段创建了一个索引，并且表里就存了上面示例中的几条数据，这里我再把图拿过来
例如执行下面这条sql 则需要进行回表:
由于查询条件是name = ‘赵六’，所以会走name索引
整个过程大致分为以下几个步骤：

从根节点开始，21<35 定位右边存储指针，
在索叶子节点找到35的第一条记录，也就是id=9的那条
由于是select *，还要查其它字段，此时就会根据id=9到聚簇索引（主键索引）中查找其它字段数据，这个查找过程前面说了很多次了，这个根据id=4到聚簇索引中查找数据的过程就被称为回表

覆盖索引

上一节说当执行select * from user where age = 35;这条sql的时候，会先从索引页中查出来age = 35;对应的主键id，之后再回表，到聚簇索引中查询其它字段的值。
那么当执行下面这条sql，又会怎样呢？
这次查询字段从select *变成select id，查询条件不变，所以也会走age索引
所以还是跟前面一样了，先从索引页中查出来age = 35;对应的主键id之后，惊讶的发现，sql中需要查询字段的id值已经查到了，那次此时压根就不需要回表了，已经查到id了，还回什么表。
而这种需要查询的字段都在索引列中的情况就被称为覆盖索引，索引列覆盖了查询字段的意思。
当使用覆盖索引时会减少回表的次数，这样查询速度更快，性能更高。
所以，在日常开发中，尽量不要select * ，需要什么查什么，如果出现覆盖索引的情况，查询会快很多。

单列索引

ALTER TABLE test.user ADD INDEX(name);
假设，我们现在对name字段加了一个普通非唯一索引，那么name就是索引列，同时name这个索引也就是单列索引
此时如果往表中插入三条数据，那么name索引的叶子节点存的数据就如下图所示
mysql会根据name字段的值进行排序，这里我假设张三排在李四前面，当索引列的值相同时，就会根据id排序，所以索引实际上已经根据索引列的值排好序了。
这里肯定有小伙伴疑问，name字段存储的中文也可以排序么？
答案是可以的，并且mysql支持很多种排序规则，我们在建数据库或者是建表的时候等都可以指定排序规则，并且后面文章涉及到的字符串排序都是我随便排的，实际情况可能不一样。
对于单个索引列数据查找也是跟前面说的聚簇索引一样，也会对数据分组，之后可以根据二分查找在单个索引列来查找数据。
当数据不断增多，一个索引页存储不下数据的时候，也会用多个索引页来存储，并且索引页直接也会形成双向链表
当索引页不断增多是，为了方便在不同索引页中查找数据，也就会抽取一个索引页，除了存页中id，同时也会存储这个id对应的索引列的值
当数据越来越多越来越多，还会抽取，也会形成三层的一个B+树，这里我就不画了。

联合索引

除了单列索引，联合索引其实也是一样的，只不过索引页存的数据就多了一些索引列
比如，在name和age上建立一个联合索引，此时单个索引页就如图所示

先以name排序，name相同时再以age排序，如果再有其它列，依次类推，最后再以id排序。
相比于只有name一个字段的索引来说，索引页就多存了一个索引列。
最后形成的B+树简化为如下图

最左前缀原则

顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。
如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了
当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！

单列索引联合索引分别什么场景创建，优势是什么

联合索引的优势

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

覆盖索引
对联合索引(a,b,c),如果有如下sql的
select a,b,c from table where a=‘xxx’ and b=‘xx’;
那么mysql可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作.减少io操作,特别是随机io其实DBA主要的优化策略.所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一.

效率高
索引列多,通过联合索引筛选出的数据越少.比如有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,效率提升可想

索引下推

索引下推（INDEX CONDITION PUSHDOWN，简称 ICP）是在 MySQL 5.6 针对扫描二级索引的一项优化改进。 用来在范围查询时减少回表的次数 。ICP 适用于 MYISAM 和 INNODB。
不使用索引下推实现
image.png
image.png
使用索引下推实现
image.png
image.png
接下来要执行如下的sql
在MySQL5.6(不包括5.6)之前，整个sql大致执行步骤如下：
先根据二分查找，定位到name > '王五’的第一条数据，也就是id=4的那个赵六
之后就会根据id=4进行回表操作，到聚簇索引中查找id=4其它字段的数据，然后判断数据中的age是否大于22，是的话就说明是我们需要查找的数据，否则就不是
之后顺着链表，继续遍历，然后找到一条记录就回一次表，然后判断age，如此反复下去，直至结束
所以对于图上所示，整个搜索过程会经历5次回表操作，两个赵六，两个刘七，一个王九，最后符合条件的也就是id=6的赵六那条数据，其余age不符和。
虽然这么执行没什么问题，但是不知有没有发现其实没必要进行那么多次回表，因为光从上面的索引图示就可以看出，符合name > ‘王五’ and age > 22的数据就id=6的赵六那条数据
所以在MySQL5.6之后，对上面的age > 22判断逻辑进行了优化
前面还是一样，定位查找到id=4的那个赵六，之后就不回表来判断age了，因为索引列有age的值了，那么直接根据索引中age判断是否大于22，如果大于的话，再回表查询剩余的字段数据（因为是select *），然后再顺序链表遍历，直至结束
所以这样优化之后，回表次数就成1了，相比于前面的5次，大大减少了回表的次数。
而这个优化，就被称为索引下推，就是为了减少回表的次数。
之所以这个优化叫索引下推，其实是跟判断age > 22逻辑执行的地方有关，这里就不过多赘述了。

索引合并

索引合并（index merge）是从MySQL5.1开始引入的索引优化机制，在之前的MySQL版本中，一条sql多个查询条件只能使用一个索引，但是引入了索引合并机制之后，MySQL在某些特殊的情况下会扫描多个索引，然后将扫描结果进行合并
结果合并会为下面三种情况：

取交集（intersect）

取并集（union）
排序后取并集（sort-union）
为了不耽误演示，删除之前所有的索引，然后为name和age各自分别创建一个二级索引idx_name和idx_age
取交集（intersect）
当执行下面这条sql就会出现取交集的情况
查看执行计划
type是index_merge，并且possible_key和key都是idx_name和idx_age，说明使用了索引合并，并且Extra有Using intersect(idx_age,idx_name)，intersect就是交集的意思。
整个过程大致是这样的，分别根据idx_name和idx_age取出对应的主键id，之后将主键id取交集，那么这部分交集的id一定同时满足查询name = ‘赵六’ and age= 22的查询条件（仔细想想），之后再根据交集的id回表
不过要想使用取交集的联合索引，需要满足各自索引查出来的主键id是排好序的，这是为了方便可以快速的取交集
比如下面这条sql就无法使用联合索引
只能用name这个索引，因为age > 22查出来的id是无序的，前面在讲索引的时候有说过索引列的排序规则
由此可以看出，使用联合索引条件还是比较苛刻的。

取并集（union）

取并集就是将前面例子中的and换成or
前面执行的情况都一样，根据条件到各自的索引上去查，之后对查询的id取并集去重，之后再回表
同样地，取并集也要求各自索引查出来的主键id是排好序的，如果查询条件换成age > 22时就无法使用取并集的索引合并

排序后取并集（sort-union）

虽然取并集要求各自索引查出来的主键id是排好序的，但是如果遇到没排好序的情况，mysql会自动对这种情况进行优化，会先对主键id排序，然后再取并集，这种情况就叫 排序后取并集（sort-union）。
比如上面提到的无法直接取并集的sql就符合排序后取并集（sort-union）这种情况
select * from user where name = ‘赵六’ or age > 22;

为什么Mysql默认 InnoDB, 而不是 MyISAM ？

MyISAM 与 InnoDB
众所周知，MySQL 有两种常见的存储引擎。一种是 MyISAM，一种是 InnoDB。
一、它们是什么？
先来看看官网对 MyISAM 的描述，只有一句话，看来官方也不想多加解释。
MyISAM is based on the older (and no longer available) ISAM storage engine but has many useful extensions.
大意：MyISAM 是一款青出于蓝而胜于蓝的存储引擎，它在 ISAM 基础上作了一些扩展和加工。关于 ISAM ，我只告诉你它是 Indexed Sequential Access Method 的缩写，翻译为“有索引的顺序访问方法”。
而对 InnoDB 的描述，就更 professional 一些了。
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 8.0, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table.
大意：InnoDB 是一种通用的存储引擎，在高可靠和高性能上作了均衡。MySQL 8.0 中，它是默认的存储引擎（其实在5.5之后的版本就是了），当你执行 CREATE TABLE 建表语句并且不带 “ENGINE = ”子句时，默认帮你创建的就是 InnoDB 表了。
二、两者有什么区别？
拿官网两者的 Features 来作一个分析对比吧：
image.png
image.png
1、InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，否则其他索引也会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
2、InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败。
3、InnoDB 在 MySQL 5.6 之前不支持全文索引，而 MyISAM 一直都支持，如果你用的是老版本，查询效率上 MyISAM 要高。
4、InnoDB 锁粒度是行锁，而 MyISAM 是表锁。
5、InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组成一个事务。
6、InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快，但如果上述语句还包含了 where 子句，那么两者执行效率是一样的。

https://blogs.oracle.com/mysql/post/still-using-myisam-it-is-time-to-switch-to-innodb
https://tecadmin.net/choosing-between-innodb-myisam-and-memory-storage-engines/
http://dimitrik.free.fr/blog/archives/2015/12/mysql-performance-revisiting-innodb-vs-myisam-with-mysql-57.html

如果一个表没有主键索引那还会创建B+树吗？

答案是会的！！！
InnoDB是MySQL中的一种存储引擎，它会为每个表创建一个主键索引。如果表没有明确的主键索引，InnoDB会使用一个隐藏的、自动生成的主键来创建索引。这个隐藏的主键索引使用的就是B+树结构。因此，在InnoDB中，即使表没有明确的主键索引，也会创建一个B+树索引。

索引的优缺点，什么时候该用和不该用

优点：

1. 提高检索效率

2. 降低排序成本，索引对应的字段是会有一个自动排序功能的，默认是升序asc。

它缺点是

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2. 索引需要占用物理空间，数据量越大，占用空间越大
3. 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护

竟然索引有坏有好，什么时候需要索引，什么时候不需要？

适合：

1. 较频繁的作为查询条件的字段应该创建索引

不适合：

1. 字段值的唯一性太差不适合单独做索引
2. 更新非常频繁的字段不适合
3. 不会出现在where句中的字段不适合。

二.优化

1、优化方法

image.png

关于SQL优化方法，包括5点
1）创建索引减少扫描量；
2）调整索引减少计算量；
3）索引覆盖（减少不必访问的列，避免回表查询）；
4）干预执行计划；
5）SQL改写；

2、通过Explain干预执行计划

Full scan on NULL key：子查询中的一种优化方式，在无法通过索引访问null值的时候使用
Using Index：仅使用索引树中的信息从表中检索列信息，不用进行其他查找就可以读取实际行。(覆盖索引：查询列都是索引列)
No tables used：当此查询没有FROM子句或拥有FROM DUAL子句时出现。
Impossible where：where子句始没有满足条件的目标数据，不会命中任何行
Using index condition：表示先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。（使用索引范围查找，因查询列未被索引全覆盖，最终会回表查询。）
Using join buffer：使用join buffer降低对被驱动表的扫描次数。
Using where：不是读取表的所有数据或者不通过索引就可以获取所有需要的数据。（未使用索引检索数据）
Select tables optimized away：使用某些聚合函数（min,max）来访问某个索引值。（无需查找表、索引就可以返回数据，效率非常高）
Using filesort：当查询中包含 order by 操作而且无法利用索引完成的排序操作，数据较少时从内存排序，如果数据较多需要在磁盘中排序，需优化成索引排序。（未使用索引进行排序导致在内存中或硬盘中排序）
Using temporary：表示MySQL需要使用临时表来存储结果集；如果查询包含不同列的GROUP BY和 ORDER BY子句，通常会发生这种情况。（使用临时表存储数据进行数据去重等操作）
可以通过添加索引的方式，优化成using index
Extra：SQL执行查询的一些额外信息。
NULL：最后执行。且表示结果集，不需要使用它来进行查询
id相同：执行顺序从上往下
同时存在：先执行序号大，在从上往下
id不同：序号大先执行
id：每个select都对应一个id，从1开始递增。如果该查询有子查询，将显示多个id值。
filtered：表示符合查询条件的数据百分比。可以使用rows * filtered/100计算出与explain前一个表进行连接的行数。
rows：全表扫描时表示需要扫描表的行数估计值；索引扫描时表示扫描索引的行数估计值；值越小越好。
UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，而是需要每次查询时重新计算。
SIMPLE：简单select，不包括union与子查询
UNION RESULT：如果两个查询中有相同的列，则会对这些列进行重复删除，只保留一个表中的列。
PRIMARY：复杂查询中最外层查询，比如使用union或union all时，id为1的记录select_type通常是primary
DEPENDENT UNION：当union作为子查询时，其中第一个union为dependent subquery，第二个union为dependent union。
SUBQUERY：指在 select 语句中出现的查询语句，结果不依赖于外部查询
UNION：分union与union all两种，id大于1的select被标记为union；如果union被from子句的子查询包含，那么第一个select会被标记为derived；union会针对相同的结果集进行去重，union all不会进行去重处理。
DEPENDENT SUBQUERY：指在 select 语句中出现的查询语句，结果依赖于外部查询
DERIVED：派生表，在FROM子句的查询语句，表示从外部数据源中推导出来的，而不是从 SELECT 语句中的其他列中选择出来的。
select_type：查询类型。
ref：表示将哪个字段或常量和key列所使用的字段进行比较。
如果有别名，展示别名。
如果有多个表，将显示多行记录。
table：查询所涉及的表名。
partitions：表分区情况。
字段如果为NULL，需要1个字节记录是否为NULL（空值字段：1字节）
varchar(n)：如果是uft-8：3n+2字节，加的2个字节存储字符串长度。如果是utf8mb4：4n+2 字节（变长字段：2字节）
char(n)：n个字节
字符串
datetime：8字节
date：3字节
timestamp：4字节
时间类型
bigint：8字节
tinyint：1字节
int：4字节
smaillint：2字节
数值类型
计算规则
作用：使用联合索引的时候可以知道使用了哪几列
key_len：表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度。
key：表示实际在查询中使用的索引，如果没有使用索引，显示NULL。
possible_keys：表示在查询中可能使用到某个索引或多个索引；如果没有选择索引，显示NULL。
all：扫描整个表进行匹配
避免使用
很少出现
NULL：MySQL在优化过程中分解语句就已经可以获取到结果，执行时甚至不用访问表或索引，效率高
index：扫描整个索引进行匹配
system：const类型的一种特殊场景，查询的表只有一行的情况
range：使用非唯一索引扫描部分索引，比如使用索引获取某些范围区间的记录
index_subquery：查询使用子查询的索引，当查询需要过滤大量数据时，使用子查询可以提高查询效率，而使用索引可以提高查询性能。
const：基于主键或唯一索引查看一行，当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问转换成常量查询，效率高
eq_ref：基于主键或唯一索引连接两个表，对于每个索引键值，只有一条匹配记录
unique_subquery：查询使用合并索引来执行，当查询需要过滤大量数据时，使用子查询可以避免重复行，从而提高查询效率。
ref：基于非唯一索引连接两个表，通过二级索引列与常量进行等值匹配，可能会存在多条匹配记录
index_merge：表示使用了索引合并的优化方法，当查询需要扫描大量的数据时，使用合并索引可以提高查询效率。
fulltext：全文索引
ref_or_null：基于非唯一索引连接两个表，通过二级索引进行等值匹配询，该索引列的值可以是NULL值。
推荐使用
type：查询所使用的访问类型。（查询效率从上往下依次递减）
Explain返回列详解
Explain含义：Explain是 SQL 分析工具中非常重要的一个功能，它可以模拟优化器执行查询语句，帮助我们理解查询是如何执行的；分析查询执行计划可以帮助我们发现查询瓶颈，优化查询性能。
与show warnings搭配使用查看执行器优化后的SQL：
Explain select * from student;
show warnings;
MySQL5.7版本之前使用Explain Extended关键字：Explain Extended select * from student;
MySQL5.7版本开始使用Explain关键字：Explain select * from student;
Explain使用方法
Explain使用与详解

1.Explain含义

Explain是 SQL 分析工具中非常重要的一个功能，它可以模拟优化器执行查询语句，帮助我们理解查询是如何执行的；分析查询执行计划可以帮助我们发现查询瓶颈，优化查询性能。

2.Explain作用

表的读取顺序
SQL执行时查询操作类型
可以使用哪些索引
实际使用哪些索引
每张表有多少行记录被扫描
SQL语句性能分析

3.Explain用法

数据准备

drop table orders;
drop table products;
drop table users;
CREATE TABLE users ( 
 id INT PRIMARY KEY AUTO_INCREMENT, 
 name VARCHAR(50) NOT NULL, 
 email VARCHAR(100) NOT NULL, 
 password VARCHAR(100) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE products ( 
 id INT PRIMARY KEY AUTO_INCREMENT, 
 name VARCHAR(50) NOT NULL, 
 price FLOAT NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE orders ( 
 id INT PRIMARY KEY AUTO_INCREMENT, 
 user_id INT NOT NULL, 
 order_date DATETIME NOT NULL, 
 total_price FLOAT NOT NULL, 
 product_id INT NOT NULL, 
 FOREIGN KEY (user_id) REFERENCES users(id), 
 FOREIGN KEY (product_id) REFERENCES products(id) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
alter table users add index index_name_email (name,email);
INSERT INTO users (name, email, password) 
VALUES ('张三', 'zhangsan@example.com', 'password123'), 
('李四', 'lisi@example.com', 'password123'), 
('王五', 'wangwu@example.com', 'password123'), 
('赵六', 'zhaoli@example.com', 'password123'), 
('钱七', 'qianqi@example.com', 'password123'); 
INSERT INTO products (name, price) 
VALUES ('产品 1', 10.00), 
('产品 2', 15.00), 
('产品 3', 20.00), 
('产品 4', 12.00), 
('产品 5', 18.00); 
INSERT INTO orders (user_id, order_date, total_price, product_id) 
VALUES (1, '2023-02-18 10:00:00', 100.00, 1), 
(2, '2023-02-18 11:00:00', 50.00, 2), 
(3, '2023-02-18 12:00:00', 20.00, 3), 
(4, '2023-02-18 13:00:00', 15.00, 4), 
(5, '2023-02-18 14:00:00', 25.00, 5);

MySQL5.7版本之前，使用Explain Extended在Explain的基础上额外多返回filtered列与extra列；

Explain Extended select * from users;

MySQL5.7版本之前，使用Explain Partitions在Explain的基础上额外多返回partitions列；

Explain Partitions select * from users;

MySQL5.7版本引入了这两个特性，直接使用Explain关键字可以将partitions列、filtered列、extra列直接查询出来。

Explain select * from users;

Explain语句返回列的各列含义：

这些查询列大家先留一个印象，后续会详细讲解。

4.Explain返回列详解

接下来我们将展示Explain中每个列的信息

1. id列：每个select都有一个对应的id号，并且是从1开始自增的。

2.select_type列：表示查询语句执行的查询操作类型

2.1.simple：简单select，不包括union与子查询
image.png
2.2.primary：复杂查询中最外层查询，比如使用union或union all时，id为1的记录select_type通常是primary
image.png
2.3.subquery：指在 select 语句中出现的子查询语句,结果不依赖于外部查询（不在from语句中）
2.4.dependent subquery：指在 select 语句中出现的查询语句，结果依赖于外部查询
2.5.derived：派生表，在FROM子句的查询语句，表示从外部数据源中推导出来的，而不是从 SELECT 语句中的其他列中选择出来的。
image.png
2.6.union：分union与union all两种，若第二个select出现在union之后，则被标记为union；如果union被from子句的子查询包含，那么第一个select会被标记为derived；union会针对相同的结果集进行去重，union all不会进行去重处理。
image.png
image.png
2.7.dependent union：当union作为子查询时，其中第一个union为dependent subquery，第二个union为dependent union。
image.png
2.8.union result：如果两个查询中有相同的列，则会对这些列进行重复删除，只保留一个表中的列。
image.png

3.table列：查询所涉及的表名。如果有多个表，将显示多行记录

4.partitions列：表分区情况

查询语句所涉及的表的分区情况。具体来说，它会显示出查询语句在哪些分区上执行，以及是否使用了分区裁剪等信息。如果没有分区，该项为NULL。

5.type列：查询所使用的访问类型

6.possible_keys列：表示在查询中可能使用到某个索引或多个索引；如果没有选择索引，显示NULL

7.key列：表示在查询中实际使用的索引，如果没有使用索引，显示NULL。

8.key_len列：表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度（主要使用在联合索引）

4d78a53182cb41d18592168dfff218ab.png

9.ref列：表示将哪个字段或常量和key列所使用的字段进行比较。

10.rows列：全表扫描时表示需要扫描表的行数估计值；索引扫描时表示扫描索引的行数估计值；值越小越好（不是结果集中的行数）

11.filtered列：表示符合查询条件的数据百分比。可以使用rows * filtered/100计算出与explain前一个表进行连接的行数。

12.Extra列：SQL执行查询的一些额外信息

12.1.Using Index：使用非主键索引树就可以查询所需要的数据。一般是覆盖索引，即查询列都包含在辅助索引树叶子节点中，不需要回表查询。

12.2.Using where：不通过索引查询所需要的数据

12.3.Using index condition：表示查询列不被索引覆盖，where 条件中是一个索引范围查找，过滤完索引后回表找到所有符合条件的数据行。

12.4.Using temporary：表示需要使用临时表来处理查询；

1.total_price列无索引，需要创建一张临时表进行去重
2.name列有联合索引

12.5.Using filesort：当查询中包含 order by 操作而且无法利用索引完成的排序操作，数据较少时从内存排序，如果数据较多需要在磁盘中排序。 需优化成索引排序。

1.total_price列无索引，无法通过索引进行排序。需要先保存total_price与对应的主键id，然后在排序total_price查找数据。
image.png
2.name列有索引，因索引已经是排好序的所以直接读取就可以了。
image.png

12.6.Select tables optimized away：使用某些聚合函数（min,max）来访问某个索引值。

5.索引优化最佳实践

示例表：

1.全值匹配

MySQL全值匹配是指在使用复合索引时，查询条件要包含索引的所有列，才能最大程度地利用索引。

查看索引长度是74=(324+2),可以算出联合索引中只使用了name前缀索引.
clipboard.png
查看索引长度是78=(324+2)+4,可以算出联合索引中只使用了name和age前缀索引.
clipboard.png
查看索引长度是140=(324+2)+4+(320+2),可以算出联合索引中只使用了完整的联合索引
clipboard.png

2.最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
——带头大哥不能死，中间兄弟不能断；
clipboard.png

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

clipboard.png
给hire_time增加一个普通索引：
clipboard.png
转化为日期范围查询，有可能会走索引：
clipboard.png
还原最初索引状态

4.存储引擎不能使用索引中范围条件右边的列

范围查询会使后面字段无序，造成部分索引失效。
——范围之后全失效；
clipboard.png

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

——覆盖索引不写星；
clipboard.png
clipboard.png

6.不等空值还有or，索引失效要少用；

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描
< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

clipboard.png
is null,is not null 一般情况下也无法使用索引
clipboard.png
少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化
clipboard.png

7.Like百分写最右

——Like百分写最右
clipboard.png
clipboard.png
问题：解决like’%字符串%'索引不被使用的方法？
a）使用覆盖索引，查询字段必须是建立覆盖索引字段
clipboard.png
b）如果不能使用覆盖索引则可能需要借助搜索引擎

8.字符串不加单引号索引失效

——VAR引号不可丢
clipboard.png

9.范围查询优化

给年龄添加单值索引
clipboard.png
没走索引原因：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子，可能是由于单次数据量查询过大导致优化器最终选择不走索引
优化方法：可以将大的范围拆分成多个小范围

clipboard.png
还原最初索引状态

索引使用总结：

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

6.trace工具用法：

3、SQL改写

1.1. 准备工作：

1.1.1. 创建student表：

1.1.2. 创建scores表：

1.1.3. 添加索引：

1.1.4. 插入数据：

1.2. SQL优化最佳实践：

1.2.1. 避免使用select *

阿里规范：

[强制]在表查询中，一律不要使用*作为查询的字段列表，需要哪些字段必须明确说明。

说明：

1. 增加查询分析器解析成本
2. 增减字段容易与resultMap配置不一致
3. 无用字段增加网络消耗，尤其是text类型的字段

分析：
查看执行计划，select * 走全表扫描，没有用到任何索引，查询效率非常低；查询列都是索引列那么这些列被称为覆盖索引。这种情况下查询的相关字段都能走索引，索引查询的效率相对较高。
通过show warnings语句查看查询列*号替换成表所有字段。
image.png
总结：
查询时需要先将星号解析成表的所有字段然后在查询，增加查询解析器的成本；
select * 查询一般不走覆盖索引会产生大量的回表查询；
在实际应用中我们通常只需要使用某几个字段，其他不需要使用的字段也查出来浪费CPU、内存资源；
文本数据、大字段数据数据传输增加网络消耗。
SQL执行过程.jpg
InnoDB索引结构图 (1).jpg

1.2.2. 小表驱动大表

小表驱动大表就是指用数据量较小、索引比较完备的表，然后使用其索引和条件对大表进行数据筛选，从而减少数据计算量，提高查询效率。比如说student表有30条数据，scores表有80w条数据。
image.png
image.png
Join Buffer（连接缓冲区）是优化器用于处理连接查询操作时的临时缓冲区。简单来说当我们需要比较两个或多个表的数据进行Join操作时，Join Buffer可以帮助MySQL临时存储结果，以减少磁盘读取和CPU负担，提高查询效率。需要注意的是每个join都有一个单独的缓冲区。
Block nested-loop join（BNL算法）会将驱动表数据加载到join buffer里面，然后再批量与非驱动表进行匹配；如果驱动表数据量较大，join buffer无法一次性装载驱动表的结果集，将会分阶段与被驱动表进行批量数据匹配，会增加被驱动表的扫描次数，从而降低查询效率。所以开发中要遵守小表驱动大表的原则。
分阶段匹配过程如下：
1、先把student表前15条数据读到join buffer中。
2、然后用scores表去匹配join buffer中的前15条。
3、记录下匹配结果。
4、清空join buffer。
5、再把student表后15条读取join buffer中。
6、然后用scores表去匹配join buffer中的后15条。
7、记录下匹配结果。
Block nested-loop join.jpg

1.2.3. 用连接查询代替子查询

mysql需要在两张表以上获取数据的方式有两种：第一种通过连表查询获取，第二种通过子查询获取。
模拟一个真实场景，同样student表有30条数据，scores表有80w条数据，我们想查看学号小于15的学员各科分数信息：
因为子查询需要执行两次数据库查询，一次是外部查询，一次是嵌套子查询。因此，使用连接查询可以减少数据库查询的次数，提高查询的效率。
连接查询可以更好地利用数据库索引，提高查询的性能。子查询通常会使用临时表或内存表，而连接查询可以直接利用表上的索引。这意味着连接查询可以更快地访问表中的数据，减少查询的资源消耗。
对于大型数据集，使用连接查询通常比使用子查询更高效。子查询通常需要扫描整个表，而连接查询可以利用索引加速读取操作。
使用连接查询可以更快地执行查询操作，减少数据库的负载，提高查询的性能和效率。

1.2.4. 提升group by的效率

创建索引：如果你使用group by的列没有索引，那么查询可能会变得很慢。因此，可以创建一个或多个适当的索引来加速查询。
添加索引前：
image.png
添加索引后：
image.png
调整查询：查询的写法也会影响group by的效率。可以尝试不使用子查询或临时表，或者可以使用JOIN或EXISTS来代替IN子查询。
限制结果集的数量：如果你只需要查看一小部分结果，可以在查询中添加LIMIT子句，以便只返回一定数量的结果。

1.2.5. 批量操作

批量插入或批量删除数据，比如说现在需要将1w+数据插入到数据库，大家是一条一条处理还是批量操作呢？建议是批量操作，逐个处理会频繁的与数据库交互，损耗性能。
反例：
在循环中逐条插入数据。
该操作需要多次请求数据库，才能完成这批数据的插入。
但众所周知，我们在代码中，每次远程请求数据库，是会消耗一定性能的。而如果我们的代码需要请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。
那么如何优化呢？
正例：
提供一个批量插入数据的方法。
这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。
但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。

1.2.6. 使用limit

提高查询效率：一个查询返回成千上万的数据行，不仅占用了大量的系统资源，也会占用更多的网络带宽，影响查询效率。使用LIMIT可以限制返回的数据行数，减轻了系统负担，提高了查询效率。
避免过度提取数据：对于大型数据库系统，从数据库中提取大量的数据可能会导致系统崩溃。使用LIMIT可以限制提取的数据量，避免过度提取数据，保护系统不受影响。
优化分页查询：分页查询需要查询所有的数据才能进行分页处理，这会浪费大量的系统资源和时间。使用LIMIT优化分页查询可以只查询需要的数据行，缩短查询时间，减少资源的浪费。
简化查询结果：有时我们只需要一小部分数据来得出决策，而不是整个数据集。使用LIMIT可以使结果集更加精简和易于阅读和理解。
限制行数非常有用，因为它可以提高查询性能、减少处理需要的时间，并且只返回我们关心的列。
百万级表Limit翻页越往后越慢咋办
为什么 offset 偏大之后 limit 查找会变慢？这需要了解 limit 操作是如何运作的，以下面这句查询为例：
这句 SQL 的执行逻辑是
1.从数据表中读取第N条数据添加到数据集中
2.重复第一步直到 N = 10000 + 10
3.根据 offset 抛弃前面 10000 条数
4.返回剩余的 10 条数据
第一次优化
根据数据库这种查找的特性，就有了一种想当然的方法，利用自增索引（假设为id）：
第二次优化
说起数据库查询优化，第一时间想到的就是索引，所以便有了第二次优化：先查找出需要数据的索引列（假设为 id），再通过索引列查找出需要的数据。
相比较结果是（500w条数据）：第一条花费平均耗时约为第二条的 1/3 左右。
同样是较大的 offset，第一条的查询更为复杂，为什么性能反而得到了提升？
这涉及到 mysql 主索引的数据结构 b+Tree ，这里不展开，基本原理就是：
子查询只用到了索引列，没有取实际的数据，所以不涉及到磁盘IO，所以即使是比较大的 offset 查询速度也不会太差。
利用子查询的方式，把原来的基于 user 的搜索转化为基于主键（id）的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。
第三次优化
在数据量大的时候 in 操作的效率就不怎么样了，我们需要把 in 操作替换掉，使用 join 就是一个不错的选择。

1.2.7. 用union all代替union

union all：获取所有数据但是数据不去重，包含重复数据；
union：获取所有数据且数据去重，不包含重复数据；
image.png
image.png
那么union all与union如果当然它业务数据容许出现重复的记录，我们更推荐使用union all，因为union去重数据需要遍历、排序和比较，它更耗时，更消耗cpu资源，但是数据结果最完整。

1.2.8. join的表不宜过多

查询效率下降：多表JOIN查询数据对比时间边长
系统负载增加：JOIN操作需要进行大量的计算，因此会导致系统负载增加。
维护难度加大：在一个连接了多个表的查询中，如果需要修改其中一个表的结构或内容，就可能会需要同时修改其他表的结构或内容。
因此，在数据库设计时，应该尽量减少JOIN操作的使用频率，并且简化表之间的关系，以提高查询效率和系统的性能。
除上述优化之外，通常在建表还需要注意以下内容：
控制索引数量
选择合理的字段类型

2.总结

SQL优化是提高数据库性能的重要方法，在实际开发中我们的SQL要尽量遵守以下几点原则，避免留下技术债：
减少数据扫描
返回更少数据
减少交互次数
减少服务器CPU及内存开销

本文章来源于我的博客：https://blog.hikki.site