案例分析之mysql选错索引

前言

案例取自极客时间《mysql45讲》

案例

模拟执行器分析查询语句

场景复现

奇了怪了，此时没用索引，进行了全表扫描

虽然使用了索引，但是还是扫描了37116行，不妨结合之前的知识分析一下：

1.另一个事务未提交，需要保存之前的数据的数据版本，因此delete10万行数据实际是标记数据，这样每一行数据就有两个数据版本，旧的是delete之前的，新的是标记为delete的，索引a上的数据有两份

2.那还多出来的1万7呢，之前介绍过索引树的叶子节点存的是主键，select * 还要进行回表查询，这里将回表的扫描行数一并算上

为什么会选错索引

选择索引是优化器的工作，优化器要找到最优的执行方案并选择最小的代价去执行，扫描行数是影响执行代价之一（扫描越小，访问磁盘次数越少，消耗CPU资源越少）

mysql执行语句之前需要通过根据信息来统计记录数

这个统计信息就是索引的区分度，即索引上不同的值越多，区分度越高越好（show index t 的 cardinality字段查看），索引的区分度是利用采样统计得到的即取小部分统计信息再乘以整体。

除了使用统计信息，还会计算回表代价(主键不需要回表)

如果是统计信息不对那就修正

另一种场景复现

按理说这是个空集，利用索引a只扫描1000行，利用索引b要扫描50000行，这里优化器竟然选择了索引b！！

mysql又选错了索引

解决办法

2.引导使用a索引

我们知道索引树上的数据是有序的，优化器使用b索引，一方面是认为索引b可以避免排序 ,order by a,b强制按照a,b排序意味着两个都需要排序，因此扫描行数成了影响决策的主要条件

3.删掉索引b

解决mysql选错索引主要有两大方向

1.强制指定索引

2.干涉优化器选择（比如增大limit数量，增加order by ，写成子查询）

MySQL选错索引导致的线上慢查询事故

mysql中走与不走索引的情况汇集(待全量实验)

数据查询语言（凡是带有 select 关键字的都是查询语句）

select...

数据 *** 作语言（凡是对表中的 数据 进行增删改的都是 DML）

insert 增 delete 删 update 改

数据定义语言（凡是带有 create、drop、alter 的都是 DDL）

主要 *** 作的是 表的结构 ，不是表的数据

事务控制语言（包括：事务提交 commit、事务回滚 rollback）

数据控制语言（授权 grant、撤销权限 revoke）

select 字段 from 表名 where 条件

in(具体值，具体值，......) 不是区间

一个输入对应一个输出，和其对应的是多行处理函数（多个输入，对应一个输出）

输入多行，最终输出一行

如果你 没有对数据进行分组，整张表默认为一组 。

在实际的应用中，可能需要先进行分组，然后对每一组的数据进行 *** 作

案例： 查询每个员工所在部门的名称，显示员工名和部门名？

emp e 和 dept d 表进行连接。条件是：e.deptno = d.deptno

SQL92语法：（结构不够清晰，表的连接条件和后期进一步筛选的条件，都放到了 where 子句中）

SQL99语法：（表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加 where 子句）

技巧： 把一张表看成两张表

思考： 外连接的查询结果条数 >= 内连接的查询结果条数

select 语句中 嵌套 select 语句，被嵌套的 select 语句称为 子查询。

将查询结果集的一部分取出来。（通常使用在分页查询当中）

将字符串 varchar 类型转换成 date 类型

将日期转换成字符串

可以获取当前系统的时间，并且获取的时间是 datetime 类型的

注意：若没有条件限制将会导致所有数据全部更新。

注意：若没有条件，会删除整张表的数据。

constraint

not null 约束的字段 不能为 NULL （只有列级约束）

unique 约束的字段 不能重复 ，但是可以为 NULL

primary key

foreign key

transaction

实现原理 ：缩小扫描的范围（形成树），避免全表扫描

Database Administrator 数据库管理员

数据库表的设计依据。教你怎么进行数据库表的设计。

免费领取有关于java面试题材料和讲解！

在原有实例下创建副本应该可以达到效果。

大多情况下，需要可靠而有效地克隆 MySQL 实例数据。这包括 MySQL 高可用的解决方案，其中需要在将实例加入组复制集群之前配置实例，或者在经典复制模型中将其添加为 Slave。

为复制拓扑而创建 MySQL 副本一直很麻烦。涉及的步骤很多，首先要备份 MySQL 服务器，通过网络将备份传输到我们想要添加到复制集的新 MySQL 节点，然后在该节点上恢复备份并手动启动 MySQL 服务器。为了高可用，最好还要将其正确设置备份的 GTID，并启动并运行群集。涉及的手动步骤数量过多不利于高可用。CLONE 插件解决了这个问题并简化了副本配置。使您可以使用 MySQL 客户端（和 SQL 命令）来配置新节点并在发生时观察克隆进度。无需手动处理多个步骤并维护自己的基础架构来配置新的 MySQL 节点。

MySQL 8.0.17 引入了 CLONE SQL 语句，使当前的 MySQL 服务器成为另一个运行在不同节点的 MySQL 服务器的“克隆”。我们将执行 clone 语句的服务器实例称为“受体”。克隆的源服务器实例称为“供体”。供体克隆以一致的快照存储在 InnoDB 存储引擎中的所有数据和元数据，以替换受体中的数据。

成功执行 CLONE SQL 语句后，将自动重新启动受体服务器。重新启动涉及恢复克隆的快照数据，就像用老方法复制数据一样。恢复完成后，受体就是供体的克隆版，随时可以使用！

这里有一些关于克隆过程的重要注意事项。

不克隆 MySQL 配置参数，并且受体保留所有原始配置参数，如克隆之前。这样做是因为许多配置可能特定于节点（例如 PORT），因此保留它们似乎是一个不错的选择。另一方面，一些存储配置确实需要在供体和受体之间匹配（例如 innodbpagesize），如果这样的配置参数不匹配，CLONE 将报告错误。

CLONE 插件不会克隆二进制日志。

CLONE 插件目前仅支持 InnoDB 存储引擎。在其他存储引擎（如 MyISAM 和 CSV）中创建的表将被克隆为空表。克隆基础架构的设计允许克隆 MySQL 支持的任何存储引擎。但是，只有 InnoDB 序列化和反序列化方法已经实现并经过测试。

克隆会阻止供体中的所有并发 DDL。

需要注意的事实是受体放弃所有数据以及任何二进制日志，以便成为供体实例的克隆。在执行 CLONE 之前，如果认为有必要，需要备份当前受体数据。

---