前提

默认已经具备sql基本知识，能够编写简单sql语句

运行流程

在z级数据中如何找到我们想要找的数据呢？

计算机是如何加载的数据的

io、如何加快io的访问（减少io次数，减少IO的量）、数据的存储原理、数据量大了计算机是如何读取数据

mysql以page的方式加载数据

索引介绍

什么是Mysql索引

• MySQL官方对于索引的定义：索引是帮助MySQL高效获取数据的数据结构。例如我们书籍的目录、字典
• MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。
• 简单理解为“排好序的可以快速查找数据的数据结构”。

索引分类

1. 主键索引

表中的列设定为主键后，数据库会自动建立主键索引。

-- 创建主键索引语法： 
alter table 表名 add primary key (字段);
-- 删除主键索引语法： 
alter table 表名 drop primary key;

2. 唯一索引

表中的列创建了唯一约束时，数据库会自动建立唯一索引。

-- 创建唯一索引语法：
alter table 表名 add unique 索引名(字段)；
-- 删除唯一索引语法：
alter table 表名 drop index 索引名 on 表名;

3. 单值索引

即一个索引只包含单个列，一个表可以有多个单值索引，建表时可随表一起建立单值索引

-- 创建单值索引：
alter table 表名 add index 索引名(字段);
-- 删除单值索引：
alter table 表名 drop index 索引名 on 表名;

4. 复合索引

即一个索引包含多个列，建表时可随表一起建立复合索引

-- 创建复合索引：
alter table 表名 add index 索引名(字段,字段2);
-- 删除复合索引：
alter table 表名 drop index 索引名 on 表名;

索引优势和劣势

优势

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

劣势

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

索引使用场景

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引(where 后面的语句)
• 查询中与其它表关联的字段，外键关系建立索引
• 多字段查询下倾向创建复合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

常见的索引数据结构

hash、二叉树、BST(二叉搜索树)、AVL(自平衡二叉查找树)、红黑树、B树、B+树

hash数据结构

memory存储引擎是用的该数据结构

缺点：无法支持范围查询，需要解决哈希碰撞(扰动函数)

二叉树、BST、AVL、红黑树

二叉树

当极端情况下，数据递增插入时，会一直向右插入，形成链表，查询效率会降低

BST

BST相较一般的二叉树，特点概括其来只有一条：处处满足顺序性。

AVL

他的左右子树都是AVL树，左右子树高度差(又称平衡因子)的绝对值不操作1

如果一颗二叉搜索树是高度平衡的，他就是AVL树，如果他有n个节点，其高度可保持在O(log2n),搜索时间复杂度O(log2n)。

红黑树

是一种二叉搜索树，但在每个结点上增加一个存储位表示结点的颜色，可以是Red或Black。 通过任何一条从根到叶子的路径上各个结点着色方式的限制，红黑树确保没有一条路径会比其他路径长出俩倍，因而是接近平衡的

上面树的缺点：只有两个分支，数据量大后树的深度较大，io操作频繁

B树、B+树

B树

• 三层结构B树能存多少数据

• 如何加大数据的存储
• 加大数据存储后的缺点是什么

B+树

• 与B树相比有什么区别
• 三层B+树能存多少数据
• 生产环境我们一般是几层结构
• 主键我们应该怎么设置，使用自增ID还是uuid,或者是其他的，他们在插入操作是有什么区别

问题

1. 一个表中可以有几个索引？（多个）
2. 一个索引对应一颗树，还是多个索引对应一颗树？
3. 在B+树叶子结点存储数据，那么实际的数据有几份？（那为什么不是多份呢？按照我们的目前的理解是不是多个索引下，每个索引的叶子结点要存储数据，那innodb是怎么做的呢）
4. 索引的叶子结点存储什么？

在innodb存储引擎中，数据在进行插入的时候必须要跟某一个索引的列绑定，如果有主键就使用主键，如果没有使用唯一索引，如果还是没有innodb会生成一个6字节的rowId,其他索引的叶子结点存储的是跟数据绑定存储的索引列的值

常见概念

聚簇索引、非聚簇索引

聚簇索引：跟数据绑定的索引就是聚簇索引

非聚簇索引：跟非聚簇索引绑定的索引就是非聚簇索引

回表、索引覆盖、最左匹配、索引下推

回表

先根据索引查询到聚簇索引列值，然后根据聚簇索引列去查询对应的数据的过程叫做回表

索引覆盖

发现索引的叶子结点包含了所有的查询列，不需要回表，这个过程就是索引覆盖，要尽量使用

最左匹配

组合索引就像一个漏斗会减少我们过滤数据，就像我们的级联选择一样

索引下推

select *
from tb_user
where name='a766ed' and age>20

5.6之前版本的执行流程是通过组合索引查找name='a766ed',然后到内存中过滤age大于20的数据

5.6版本之后默认开启索引下推(Using index condition),通过组合索引查找name='a766ed',然后直接在组合索引上过滤age>20的数据

SQL性能分析

MySQL常见瓶颈

• SQL中对大量数据进行比较、关联、排序、分组时CPU的瓶颈。
• 实例内存满足不了缓存数据或排序等需要，导致产生大量的物理IO。查询数据时扫描过多数据行，导致查询效率低。

Explain

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

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

EXPLAIN关键字使用起来比较简单： explain + SQL语句：

Explain重要字段名

id字段：

• select查询的序列号，表示查询中执行select子句或操作表的顺序。
• id相同时，执行顺序由上至下。
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，则先被执行。
• id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越大，优先级越高越先执行。

select_type字段：

查询的类型，常见值有：

• SIMPLE :简单的 select 查询,查询中不包含子查询或者UNION。
• PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。
• DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。(mysql5.7+过后)
• SUBQUERY: 在SELECT或WHERE列表中包含了子查询。
• MATERIALIZED 物化子查询,子查询来自视图
• UNION：在 UNION 查询语句中的第二个和紧随其后的 SELECT。
• UNION RESULT：组中union结果的临时表
• DEPENDENT SUBQUERY 子查询依赖外部查询(慎用)
• DEPENDENT UNION 子查询中包含union

table字段：

• 显示这一行的数据是关于哪张表的。

type字段：

• 访问类型排序(从左往右索引效率越高)：

• System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
• Const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
• range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
• Index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的。
• all：Full Table Scan，将遍历全表以找到匹配的行。

从最好到最差依次是:system>const>eq_ref>ref>range>index>All。一般来说，最好保证查询能达到range级别，最好能达到ref。

possible_keys字段：

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则该索引将会被列出来，但不一定会被查询实际使用上。

key字段：

查询中实际使用的索引，如果为NULL，则没有使用索引。

key_len字段：

查询中实际使用索引的性能，越大越好。

ref字段：

显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

rows字段：

rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。

extra字段：

一些常见的重要的额外信息：

• Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”。
• Using temporary：Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。
• Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错。
• Using where：表示使用了where过滤。
• Using index condition：索引下推

尽量避免Using filesort!

查询优化

索引失效

• 最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
• 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。
• 存储引擎不能使用索引中范围条件右边的列。

因为 >,<,>=,<=不一定走索引，因为优化器会计算，符合条件的值的总和，与总数据量的比值，比值<=0.30，才会走索引

• Mysql在使用不等于时无法使用索引会导致全表扫描。
• is null可以使用索引，但是is not null无法使用索引。
• like以通配符开头会使索引失效导致全表扫描。
• 字符串不加单引号索引会失效。
• 使用or连接时索引失效。

演示：

复合索引练习

需求查询年龄为20岁姓名叫“883dea”的男生

在tb_user表创建复合索引

alter table tb_user
    add index ind_name_age_sex (name,age, sex);

单表查询优化

explain
select *
from tb_order
where goods_id='20230613103906GO00000001' and order_status>1
order by count desc
limit 10;

执行计划是

在goods_id、order_status、count创建组合索引再次测试

在goods_id、count重新创建索引这里保证两个索引之间没有其他的索引列 使key_len效率最高

关联查询优化

内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理。我们最好保证被驱动表上的字段建立了索引。

explain
select A.user_id,A.name,B.order_id
from tb_user A left join tb_order B on A.user_id=B.user_id
where name ='a766ed' and age>20 and age<22
limit 100;

执行计划

排序优化

• 尽量避免使用Using FileSort方式排序。
• order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。
• where子句中如果出现索引范围查询会导致order by索引失效。

explain
select *
from tb_user
where name='a766ed'
order by age

执行计划

分组优化

explain
select sex,count(1)
from tb_user
where name='a766ed' and age>20
group by sex;

执行计划

添加索引name、age、sex

慢查询日志

介绍：MySQL的慢查询日志是MySQL提供的一种日志记录，他用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。

默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数：

• 查看是否开启：show variables like '%slow_query_log%';
• 开启日志：set global slow_query_log = 1;
• 设置时间: set global long_query_time = 1;
• 查看时间: SHOW VARIABLES LIKE 'long_query_time%';
• 查看超时的sql记录日志：Mysql的数据文件夹下/data/...-slow.log;在Navicat中输入show variables like '%slow_query_log%命令'，就可以得到文件目录;

注意：非调优场景下，一般不建议启动改参数，慢查询日志支持将日志记录写入文件，开启慢查询日志会或多或少带来一定的性能影响。

前提

默认已经具备sql基本知识，能够编写简单sql语句

运行流程

在z级数据中如何找到我们想要找的数据呢？

计算机是如何加载的数据的

io、如何加快io的访问（减少io次数，减少IO的量）、数据的存储原理、数据量大了计算机是如何读取数据

mysql以page的方式加载数据

索引介绍

什么是Mysql索引

• MySQL官方对于索引的定义：索引是帮助MySQL高效获取数据的数据结构。例如我们书籍的目录、字典
• MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。
• 简单理解为“排好序的可以快速查找数据的数据结构”。

索引分类

1. 主键索引

表中的列设定为主键后，数据库会自动建立主键索引。

-- 创建主键索引语法： 
alter table 表名 add primary key (字段);
-- 删除主键索引语法： 
alter table 表名 drop primary key;

2. 唯一索引

表中的列创建了唯一约束时，数据库会自动建立唯一索引。

-- 创建唯一索引语法：
alter table 表名 add unique 索引名(字段)；
-- 删除唯一索引语法：
alter table 表名 drop index 索引名 on 表名;

3. 单值索引

即一个索引只包含单个列，一个表可以有多个单值索引，建表时可随表一起建立单值索引

-- 创建单值索引：
alter table 表名 add index 索引名(字段);
-- 删除单值索引：
alter table 表名 drop index 索引名 on 表名;

4. 复合索引

即一个索引包含多个列，建表时可随表一起建立复合索引

-- 创建复合索引：
alter table 表名 add index 索引名(字段,字段2);
-- 删除复合索引：
alter table 表名 drop index 索引名 on 表名;

索引优势和劣势

优势

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

劣势

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

索引使用场景

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引(where 后面的语句)
• 查询中与其它表关联的字段，外键关系建立索引
• 多字段查询下倾向创建复合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

常见的索引数据结构

hash、二叉树、BST(二叉搜索树)、AVL(自平衡二叉查找树)、红黑树、B树、B+树

hash数据结构

memory存储引擎是用的该数据结构

缺点：无法支持范围查询，需要解决哈希碰撞(扰动函数)

二叉树、BST、AVL、红黑树

二叉树

当极端情况下，数据递增插入时，会一直向右插入，形成链表，查询效率会降低

BST

BST相较一般的二叉树，特点概括其来只有一条：处处满足顺序性。

AVL

他的左右子树都是AVL树，左右子树高度差(又称平衡因子)的绝对值不操作1

如果一颗二叉搜索树是高度平衡的，他就是AVL树，如果他有n个节点，其高度可保持在O(log2n),搜索时间复杂度O(log2n)。

红黑树

是一种二叉搜索树，但在每个结点上增加一个存储位表示结点的颜色，可以是Red或Black。 通过任何一条从根到叶子的路径上各个结点着色方式的限制，红黑树确保没有一条路径会比其他路径长出俩倍，因而是接近平衡的

上面树的缺点：只有两个分支，数据量大后树的深度较大，io操作频繁

B树、B+树

B树

• 三层结构B树能存多少数据

• 如何加大数据的存储
• 加大数据存储后的缺点是什么

B+树

• 与B树相比有什么区别
• 三层B+树能存多少数据
• 生产环境我们一般是几层结构
• 主键我们应该怎么设置，使用自增ID还是uuid,或者是其他的，他们在插入操作是有什么区别

问题

1. 一个表中可以有几个索引？（多个）
2. 一个索引对应一颗树，还是多个索引对应一颗树？
3. 在B+树叶子结点存储数据，那么实际的数据有几份？（那为什么不是多份呢？按照我们的目前的理解是不是多个索引下，每个索引的叶子结点要存储数据，那innodb是怎么做的呢）
4. 索引的叶子结点存储什么？

在innodb存储引擎中，数据在进行插入的时候必须要跟某一个索引的列绑定，如果有主键就使用主键，如果没有使用唯一索引，如果还是没有innodb会生成一个6字节的rowId,其他索引的叶子结点存储的是跟数据绑定存储的索引列的值

常见概念

聚簇索引、非聚簇索引

聚簇索引：跟数据绑定的索引就是聚簇索引

非聚簇索引：跟非聚簇索引绑定的索引就是非聚簇索引

回表、索引覆盖、最左匹配、索引下推

回表

先根据索引查询到聚簇索引列值，然后根据聚簇索引列去查询对应的数据的过程叫做回表

索引覆盖

发现索引的叶子结点包含了所有的查询列，不需要回表，这个过程就是索引覆盖，要尽量使用

最左匹配

组合索引就像一个漏斗会减少我们过滤数据，就像我们的级联选择一样

索引下推

select *
from tb_user
where name='a766ed' and age>20

5.6之前版本的执行流程是通过组合索引查找name='a766ed',然后到内存中过滤age大于20的数据

5.6版本之后默认开启索引下推(Using index condition),通过组合索引查找name='a766ed',然后直接在组合索引上过滤age>20的数据

SQL性能分析

MySQL常见瓶颈

• SQL中对大量数据进行比较、关联、排序、分组时CPU的瓶颈。
• 实例内存满足不了缓存数据或排序等需要，导致产生大量的物理IO。查询数据时扫描过多数据行，导致查询效率低。

Explain

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

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

EXPLAIN关键字使用起来比较简单： explain + SQL语句：

Explain重要字段名

id字段：

• select查询的序列号，表示查询中执行select子句或操作表的顺序。
• id相同时，执行顺序由上至下。
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，则先被执行。
• id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越大，优先级越高越先执行。

select_type字段：

查询的类型，常见值有：

• SIMPLE :简单的 select 查询,查询中不包含子查询或者UNION。
• PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。
• DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。(mysql5.7+过后)
• SUBQUERY: 在SELECT或WHERE列表中包含了子查询。
• MATERIALIZED 物化子查询,子查询来自视图
• UNION：在 UNION 查询语句中的第二个和紧随其后的 SELECT。
• UNION RESULT：组中union结果的临时表
• DEPENDENT SUBQUERY 子查询依赖外部查询(慎用)
• DEPENDENT UNION 子查询中包含union

table字段：

• 显示这一行的数据是关于哪张表的。

type字段：

• 访问类型排序(从左往右索引效率越高)：

• System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
• Const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
• range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
• Index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的。
• all：Full Table Scan，将遍历全表以找到匹配的行。

从最好到最差依次是:system>const>eq_ref>ref>range>index>All。一般来说，最好保证查询能达到range级别，最好能达到ref。

possible_keys字段：

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则该索引将会被列出来，但不一定会被查询实际使用上。

key字段：

查询中实际使用的索引，如果为NULL，则没有使用索引。

key_len字段：

查询中实际使用索引的性能，越大越好。

ref字段：

显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

rows字段：

rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。

extra字段：

一些常见的重要的额外信息：

• Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”。
• Using temporary：Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。
• Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错。
• Using where：表示使用了where过滤。
• Using index condition：索引下推

尽量避免Using filesort!

查询优化

索引失效

• 最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
• 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。
• 存储引擎不能使用索引中范围条件右边的列。

因为 >,<,>=,<=不一定走索引，因为优化器会计算，符合条件的值的总和，与总数据量的比值，比值<=0.30，才会走索引

• Mysql在使用不等于时无法使用索引会导致全表扫描。
• is null可以使用索引，但是is not null无法使用索引。
• like以通配符开头会使索引失效导致全表扫描。
• 字符串不加单引号索引会失效。
• 使用or连接时索引失效。

演示：

复合索引练习

需求查询年龄为20岁姓名叫“883dea”的男生

在tb_user表创建复合索引

alter table tb_user
    add index ind_name_age_sex (name,age, sex);

单表查询优化

explain
select *
from tb_order
where goods_id='20230613103906GO00000001' and order_status>1
order by count desc
limit 10;

执行计划是

在goods_id、order_status、count创建组合索引再次测试

在goods_id、count重新创建索引这里保证两个索引之间没有其他的索引列 使key_len效率最高

关联查询优化

内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理。我们最好保证被驱动表上的字段建立了索引。

explain
select A.user_id,A.name,B.order_id
from tb_user A left join tb_order B on A.user_id=B.user_id
where name ='a766ed' and age>20 and age<22
limit 100;

执行计划

排序优化

• 尽量避免使用Using FileSort方式排序。
• order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。
• where子句中如果出现索引范围查询会导致order by索引失效。

explain
select *
from tb_user
where name='a766ed'
order by age

执行计划

分组优化

explain
select sex,count(1)
from tb_user
where name='a766ed' and age>20
group by sex;

执行计划

添加索引name、age、sex

慢查询日志

介绍：MySQL的慢查询日志是MySQL提供的一种日志记录，他用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。

默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数：

• 查看是否开启：show variables like '%slow_query_log%';
• 开启日志：set global slow_query_log = 1;
• 设置时间: set global long_query_time = 1;
• 查看时间: SHOW VARIABLES LIKE 'long_query_time%';
• 查看超时的sql记录日志：Mysql的数据文件夹下/data/...-slow.log;在Navicat中输入show variables like '%slow_query_log%命令'，就可以得到文件目录;

注意：非调优场景下，一般不建议启动改参数，慢查询日志支持将日志记录写入文件，开启慢查询日志会或多或少带来一定的性能影响。

前提

默认已经具备sql基本知识，能够编写简单sql语句

运行流程

在z级数据中如何找到我们想要找的数据呢？

计算机是如何加载的数据的

io、如何加快io的访问（减少io次数，减少IO的量）、数据的存储原理、数据量大了计算机是如何读取数据

mysql以page的方式加载数据

索引介绍

什么是Mysql索引

• MySQL官方对于索引的定义：索引是帮助MySQL高效获取数据的数据结构。例如我们书籍的目录、字典
• MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。
• 简单理解为“排好序的可以快速查找数据的数据结构”。

索引分类

1. 主键索引

表中的列设定为主键后，数据库会自动建立主键索引。

-- 创建主键索引语法： 
alter table 表名 add primary key (字段);
-- 删除主键索引语法： 
alter table 表名 drop primary key;

2. 唯一索引

表中的列创建了唯一约束时，数据库会自动建立唯一索引。

-- 创建唯一索引语法：
alter table 表名 add unique 索引名(字段)；
-- 删除唯一索引语法：
alter table 表名 drop index 索引名 on 表名;

3. 单值索引

即一个索引只包含单个列，一个表可以有多个单值索引，建表时可随表一起建立单值索引

-- 创建单值索引：
alter table 表名 add index 索引名(字段);
-- 删除单值索引：
alter table 表名 drop index 索引名 on 表名;

4. 复合索引

即一个索引包含多个列，建表时可随表一起建立复合索引

-- 创建复合索引：
alter table 表名 add index 索引名(字段,字段2);
-- 删除复合索引：
alter table 表名 drop index 索引名 on 表名;

索引优势和劣势

优势

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

劣势

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

索引使用场景

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引(where 后面的语句)
• 查询中与其它表关联的字段，外键关系建立索引
• 多字段查询下倾向创建复合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

常见的索引数据结构

hash、二叉树、BST(二叉搜索树)、AVL(自平衡二叉查找树)、红黑树、B树、B+树

hash数据结构

memory存储引擎是用的该数据结构

缺点：无法支持范围查询，需要解决哈希碰撞(扰动函数)

二叉树、BST、AVL、红黑树

二叉树

当极端情况下，数据递增插入时，会一直向右插入，形成链表，查询效率会降低

BST

BST相较一般的二叉树，特点概括其来只有一条：处处满足顺序性。

AVL

他的左右子树都是AVL树，左右子树高度差(又称平衡因子)的绝对值不操作1

如果一颗二叉搜索树是高度平衡的，他就是AVL树，如果他有n个节点，其高度可保持在O(log2n),搜索时间复杂度O(log2n)。

红黑树

是一种二叉搜索树，但在每个结点上增加一个存储位表示结点的颜色，可以是Red或Black。 通过任何一条从根到叶子的路径上各个结点着色方式的限制，红黑树确保没有一条路径会比其他路径长出俩倍，因而是接近平衡的

上面树的缺点：只有两个分支，数据量大后树的深度较大，io操作频繁

B树、B+树

B树

• 三层结构B树能存多少数据

• 如何加大数据的存储
• 加大数据存储后的缺点是什么

B+树

• 与B树相比有什么区别
• 三层B+树能存多少数据
• 生产环境我们一般是几层结构
• 主键我们应该怎么设置，使用自增ID还是uuid,或者是其他的，他们在插入操作是有什么区别

问题

1. 一个表中可以有几个索引？（多个）
2. 一个索引对应一颗树，还是多个索引对应一颗树？
3. 在B+树叶子结点存储数据，那么实际的数据有几份？（那为什么不是多份呢？按照我们的目前的理解是不是多个索引下，每个索引的叶子结点要存储数据，那innodb是怎么做的呢）
4. 索引的叶子结点存储什么？

在innodb存储引擎中，数据在进行插入的时候必须要跟某一个索引的列绑定，如果有主键就使用主键，如果没有使用唯一索引，如果还是没有innodb会生成一个6字节的rowId,其他索引的叶子结点存储的是跟数据绑定存储的索引列的值

常见概念

聚簇索引、非聚簇索引

聚簇索引：跟数据绑定的索引就是聚簇索引

非聚簇索引：跟非聚簇索引绑定的索引就是非聚簇索引

回表、索引覆盖、最左匹配、索引下推

回表

先根据索引查询到聚簇索引列值，然后根据聚簇索引列去查询对应的数据的过程叫做回表

索引覆盖

发现索引的叶子结点包含了所有的查询列，不需要回表，这个过程就是索引覆盖，要尽量使用

最左匹配

组合索引就像一个漏斗会减少我们过滤数据，就像我们的级联选择一样

索引下推

select *
from tb_user
where name='a766ed' and age>20

5.6之前版本的执行流程是通过组合索引查找name='a766ed',然后到内存中过滤age大于20的数据

5.6版本之后默认开启索引下推(Using index condition),通过组合索引查找name='a766ed',然后直接在组合索引上过滤age>20的数据

SQL性能分析

MySQL常见瓶颈

• SQL中对大量数据进行比较、关联、排序、分组时CPU的瓶颈。
• 实例内存满足不了缓存数据或排序等需要，导致产生大量的物理IO。查询数据时扫描过多数据行，导致查询效率低。

Explain

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

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

EXPLAIN关键字使用起来比较简单： explain + SQL语句：

Explain重要字段名

id字段：

• select查询的序列号，表示查询中执行select子句或操作表的顺序。
• id相同时，执行顺序由上至下。
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，则先被执行。
• id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越大，优先级越高越先执行。

select_type字段：

查询的类型，常见值有：

• SIMPLE :简单的 select 查询,查询中不包含子查询或者UNION。
• PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。
• DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。(mysql5.7+过后)
• SUBQUERY: 在SELECT或WHERE列表中包含了子查询。
• MATERIALIZED 物化子查询,子查询来自视图
• UNION：在 UNION 查询语句中的第二个和紧随其后的 SELECT。
• UNION RESULT：组中union结果的临时表
• DEPENDENT SUBQUERY 子查询依赖外部查询(慎用)
• DEPENDENT UNION 子查询中包含union

table字段：

• 显示这一行的数据是关于哪张表的。

type字段：

• 访问类型排序(从左往右索引效率越高)：

• System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
• Const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
• range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引 一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
• Index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的。
• all：Full Table Scan，将遍历全表以找到匹配的行。

从最好到最差依次是:system>const>eq_ref>ref>range>index>All。一般来说，最好保证查询能达到range级别，最好能达到ref。

possible_keys字段：

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则该索引将会被列出来，但不一定会被查询实际使用上。

key字段：

查询中实际使用的索引，如果为NULL，则没有使用索引。

key_len字段：

查询中实际使用索引的性能，越大越好。

ref字段：

显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

rows字段：

rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。

extra字段：

一些常见的重要的额外信息：

• Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”。
• Using temporary：Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。
• Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错。
• Using where：表示使用了where过滤。
• Using index condition：索引下推

尽量避免Using filesort!

查询优化

索引失效

• 最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
• 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。
• 存储引擎不能使用索引中范围条件右边的列。

因为 >,<,>=,<=不一定走索引，因为优化器会计算，符合条件的值的总和，与总数据量的比值，比值<=0.30，才会走索引

• Mysql在使用不等于时无法使用索引会导致全表扫描。
• is null可以使用索引，但是is not null无法使用索引。
• like以通配符开头会使索引失效导致全表扫描。
• 字符串不加单引号索引会失效。
• 使用or连接时索引失效。

演示：

复合索引练习

需求查询年龄为20岁姓名叫“883dea”的男生

在tb_user表创建复合索引

alter table tb_user
    add index ind_name_age_sex (name,age, sex);

单表查询优化

explain
select *
from tb_order
where goods_id='20230613103906GO00000001' and order_status>1
order by count desc
limit 10;

执行计划是

在goods_id、order_status、count创建组合索引再次测试

在goods_id、count重新创建索引这里保证两个索引之间没有其他的索引列 使key_len效率最高

关联查询优化

内连接时，mysql会自动把小结果集的选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描，所以右边大表字段最好加上索引，右外连接同理。我们最好保证被驱动表上的字段建立了索引。

explain
select A.user_id,A.name,B.order_id
from tb_user A left join tb_order B on A.user_id=B.user_id
where name ='a766ed' and age>20 and age<22
limit 100;

执行计划

排序优化

• 尽量避免使用Using FileSort方式排序。
• order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。
• where子句中如果出现索引范围查询会导致order by索引失效。

explain
select *
from tb_user
where name='a766ed'
order by age

执行计划

分组优化

explain
select sex,count(1)
from tb_user
where name='a766ed' and age>20
group by sex;

执行计划

添加索引name、age、sex

慢查询日志

介绍：MySQL的慢查询日志是MySQL提供的一种日志记录，他用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。

默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数：

• 查看是否开启：show variables like '%slow_query_log%';
• 开启日志：set global slow_query_log = 1;
• 设置时间: set global long_query_time = 1;
• 查看时间: SHOW VARIABLES LIKE 'long_query_time%';
• 查看超时的sql记录日志：Mysql的数据文件夹下/data/...-slow.log;在Navicat中输入show variables like '%slow_query_log%命令'，就可以得到文件目录;

注意：非调优场景下，一般不建议启动改参数，慢查询日志支持将日志记录写入文件，开启慢查询日志会或多或少带来一定的性能影响。

Tags：mysql 绝对值