MySQL高级：explain分析SQL，索引失效&常见优化场景

分析SQL的工具：ExplainExplain中包含的字段分析：1.id：表示查询中执行的select子句或函数表的序号：id有三种情况：-PRIMARY：最里面的查询；首先执行-SUBQUERY:子查询；效率比上面的条件要低-DERIVED：需要临时表；最低有效性2.类型：最好到最差结果值为：一般为最低阈值水平；如果可能的话，参考。
-const：唯一索引；非关联查询-eq_ref；ref：eq_ref和const的区别在于前者是关系查询，后者是eq_ref和ref的区别，因为后者不是。
-唯一索引-索引；All：读取整个表；区别在于索引转换，读取新索引；所有从硬盘和整个表的读取都将通过未索引的方式传递。
索引中使用的单词；段越短，长度越好。
-key_len：如果索引中使用的字节数不精确。
长度越短，越好。
如果id是一个索引并且=符号后面没有跟常量；将显示id。
这意味着关系表的相关字段为5。
Rows：扫描的行数；一般较小。
Better-Filtered：表中符合条件的记录数占总记录的百分比6.额外百分比：此栏显示其他额外的执行计划信息。
为了强制使用索引条件的特定索引；可以参考索引原理&&设计原理。
索引失败的几个条件：1、最左前缀不满足：-最左前缀不能跳梯；否则，索引将会失败。
例如：姓名；情况我们只需要查询地址最左边前缀的状态或地址即可。
-事实上，MySQL有一个优化器（查询优化器），查询优化器会优化SQL并选择最佳的查询计划来执行。
2.范围查询后：-范围查询后，索引字段不再有效，但用于范围查询的索引字段仍然有效。
3、对索引字段进行操作：对索引字段进行操作；使用函数等会导致索引失败。
4.不要在字符串中添加``：-索引字段是字符串类型5.避免select*危险索引：覆盖索引（索引列完全包含查询列）；最小化选择*；避免非索引对象查询导致表重跑，降低效率。
六、条件ORSEPARATED：-如果前面的条件中的列中有索引，或者使用分隔条件，或者后面的列中没有索引。
不会使用包含的标签。
7.就像以%--开头的模糊查询可以连接到词法树Trie的匹配。
解决方案：使用有效的索引索引只能用于索引列上使用的条件字段。
8.MySQL认为全表扫描更快：-如果MySQL下层执行全表扫描更快。
它会自动决定直接使用全表扫描而不建立索引。
如果表中索引列的大部分数据都是非空值，那么使用isnotnull时就会使用该索引。
不会使用索引（不会像全表扫描那么快）并且会执行全表扫描。
9.使用!=或<>:-不等式也会导致索引失败。
优化条件及SQL提示：1.配置数据环境批量导入的加载命令：-适当的设置可以提高导入效率。
例如，对于InnoDB类型表；提高导入效率的方法有以下几种：主键顺序输入、唯一性验证结束。
2.排序顺序环境提供两种排序方法：-usingindex：您现在可以直接填充索引列，而无需将查询发送回表。
当-usingfilesort-出现时；会做到最好两种扫描算法：两种扫描方法和一种扫描方法。
优化方案包括增加max_length_for_sort_data；这包括减少查询字段的总大小和增加sort_buffer_size。
3.groupby分组：-分组实际上会做排序工作，并且可以使用索引。
在优化解决方案中使用索引；关闭排序；这包括优化分区搜索和使用联合优化。
4.或改善条件——或……在某些条件下进行改善；如果你想使用标签，OR之间的每个条件列都必须使用索引，如果没有索引则不能使用复合索引。
5.使用SQL提示：-SQL提示useindex，包括ignoreindex和forceindex是优化数据库的重要方式。
6.优化分隔符分页：-使用正确的索引完成排序分页任务并使其将位置更改为查询。
摘要：-全值匹配是我最喜欢的，最左边的前缀应该被关注。
索引列中的二哥不容小觑；写在右侧且星星未写在封面标签上；或者如果不等于空值。
索引不能丢失VAR引用；应谨慎使用。
SQL并不难。

mysql联合查询union和unionall的使用介绍

MySQL中UNION和UNIONALL联合查询介绍

在MySQL中，UNION和UNIONALL是用于执行联合查询的关键字。
他们可以将多个查询的结果集合并为一个结果集。
返回。
两者之间的主要区别在于重复记录的处理方式。

1.UNION的使用

UNION运算符用于合并两个或多个SELECT语句的结果集并自动删除重复记录。
使用UNION合并的结果集将仅包含唯一记录。

例如，如果有两个表table1和table2，它们具有相同的列结构，并且想要获得两个表中相同的记录，则可以使用UNION来合并两个表的结果查询。

2.UNIONALL的使用

与UNION相比，UNIONALL也会合并多个SELECT语句的结果集，但不会删除重复记录。
它返回所有记录，包括重复记录。

如果查询结果中有重复记录，则使用UNIONALL获取所有记录，而UNION会去除重复记录，只返回唯一的记录。
因此，当知道查询结果中不会有重复记录或者需要保留重复记录时，可以使用UNIONALL来提高效率。

3.详细说明

1.UNION的用法：当需要合并多个查询的结果并希望结果集中不包含重复记录时，可以使用UNION。
它比较结果集中的每一行，删除重复项，并仅返回唯一的记录。

2.UNIONALL的用法：当需要合并多个查询的结果并希望结果集包含所有记录（包括重复部分）时，请使用UNIONALL。
它不会删除任何重复记录，直接返回所有查询的结果。

3.性能考虑：由于UNION需要去重，所以处理时间可能会稍长一些。
UNIONALL不需要重复数据删除，因此通常返回结果的速度更快。
但当处理大量数据时，性能差异可能并不明显。

4.使用场景：选择使用UNION还是UNIONALL时，应根据实际需要来决定。
如果需要去重，则使用UNION；如果不需要去重或者需要保留重复记录，可以使用UNIONALL。

了解了两者的区别后，您可以根据实际的查询需求和性能要求选择合适的查询方式。

MySQL索引优化orderby与groupby

在优化MySQL索引时，了解“orderby”和“groupby”的用法和影响至关重要。
当SQL查询使用“orderby”或“groupby”时，数据库引擎可能无法充分利用现有索引，从而导致性能下降。
第一种情况，如果`name`列符合最左前缀规则，但`age`列与索引分离，那么即使索引长度为202，`orderby`仍然可以使用`索引树`index_union`这说明orderby操作限制了索引的充分利用。
第二种情况，“where”之后遵循最左边的前缀规则，并且仅使用“name”列索引。
但是，当使用“orderby”时，如果列没有排序，引擎将不会使用索引树，而是会将结果集加载到内存中进行排序。
可以通过“额外”字段查看此操作的附加信息。
当我们观察到结果集是在名称列上排序时，年龄和位置列实际上是排序的。
然而，要按特定顺序（例如“位置”、“年龄”）排序，必须将相关列加载到内存中进行排序，这在第三种情况的第二张图中清楚地显示出来。
在某些情况下，比如“age”是一个常量值时，它实际上相当于“orderbyposition”。
案例3的第二张图中，显然“姓名”是“王五”，“年龄”=1的结果集，接下来的排序“位置”自然就准备好了。
案例3的第二张图显示，“年龄”和“职位”的排序顺序必须一致，才能利用B+树的特性，直接得到结果，否则需要进行文件排序。
看第一张图，索引本身似乎与文件排序同时存在。
然而，对比第二张图，我们可以清楚地了解到，索引树主要用于“where”条件，而“orderby”基本上是文件排序。
在第三张图中，虽然“in”子句中的“name”已排序，但“age”和“position”未预先排序。
综上所述，了解“orderby”和“groupby”对索引使用的影响是提高MySQL查询性能的关键。
正确设计SQL查询、合理使用索引、避免不必要的排序操作是提高数据库效率的策略之一。