sql优化面试题

zhou吗

于 2024-08-04 08:54:30 发布

阅读量340

点赞数 15

文章标签： sql 数据库 java spring jvm spring cloud spring boot

本文链接：https://blog.csdn.net/ZHOUJC5417/article/details/140901652

版权

文章目录

- SQL优化

SQL优化

优化手段

对于大表数据的查询的优化，尽量走索引。

利用主从复制，读写分离

将大表进行垂直拆表，冷热数据分离，减少不必要的列查询。

将大表进行水平拆表，利用shardingJDBC,Mycat这类组件辅助开发。

设置合适的值，根据内容的长度来设置。

对于在业务中一定是固定长度的数值就用char,不是固定的用varchar.

表的字段尽量设置为not null并且设置默认值。

连表的时候要小表驱动大表。

使用连接查询代替子查询

避免比较null值，最好每个字段设置为not null并给一个默认值。

避免使用！=，not in这类反的查询，会导致全表扫描

避免对查询列进行计算，函数，表达式运算等操作。

select指明需要查的列，不用select *

对于连续的数据不用用in，用between and。

能用exits尽量用exits，而不用in。

避免Using temporary

mysql-索引出现Using temporary场景
在MySQL中，查询执行过程中出现Using temporary提示，通常意味着MySQL需要创建一个临时表来存储中间结果。这种情况多发生在数据库
    优化器无法通过现有的索引直接有效地执行查询时，尤其在复杂的查询条件下。
    以下是几种索引设计或查询场景可能导致Using temporary出现的情况：

场景1: 复杂的查询条件，尤其是GROUP BY语句
在使用GROUP BY时，如果查询涉及到复杂的表达式，或者GROUP BY列没有适当的索引，MySQL可能需要创建一个临时表来聚集数据。例如，
    当GROUP BY依据的字段在索引中不是第一个字段时，可能会触发临时表的使用。

场景2: 多列的排序或分组
当在ORDER BY 或者 GROUP BY 后的列不是一个单一列，而是一个表达式，或者列没有合适的索引时，MySQL可能使用临时表存储中间结果，
    再对临时表进行排序或分组。

场景3: DISTINCT操作
在执行DISTINCT操作时，如果涉及到的列没有相应的索引，MySQL会创建临时表来存储结果并执行去除重复的处理，
    这也会触发Using temporary警告。

场景4: 某些函数和表达式的使用
例如，当查询中包含COUNT(DISTINCT ...), MIN(...), MAX(...)等聚合函数时，如果没有适当的索引，可能会触发临时表的使用。
    特别是当这些函数涉及到表达式计算或不被索引覆盖的列时。

场景5: ALL, ANY, SOME子查询
在进行这些子查询操作时，如果子查询中的求最大值或最小值等操作无法用索引覆盖，MySQL可能会创建临时表来辅助运算。

如何避免Using temporary:
为了避免出现使用临时表的情况，可以考虑以下策略：

创建合适的索引：尽可能为GROUP BY、ORDER BY、DISTINCT涉及的列创建索引。确保索引覆盖到查询中所有使用的列或表达式，
    尤其是GROUP BY及相关表达式的字段。
优化查询：避免在GROUP BY或DISTINCT中使用复杂表达式，尽量简化查询逻辑。
使用覆盖索引：建立多列索引时，确保按照查询中最常涉及到的字段顺序排列，以达到覆盖索引的效果，减少数据读取。
数据分析与索引维护：定期分析数据分布和查询模式，调整索引结构，确保索引对常见查询模式最优化。
检查查询的WHERE条件和JOIN语句：确保任何用于过滤或连接的列都是高效的，并且有适当的索引支持。
通过以上的调整，可以有效减少或避免Using temporary的出现，从而提高查询效率和响应速度。

explain详解

列	JSON 名称	意义
`id`	`select_id`	标识符`SELECT`
`select_type`	没有	类型`SELECT`
`table`	`table_name`	输出行的表
`partitions`	`partitions`	匹配的分区
`type`	`access_type`	联接类型
`possible_keys`	`possible_keys`	可供选择的可能索引
`key`	`key`	实际选择的索引
`key_len`	`key_length`	所选密钥的长度
`ref`	`ref`	与索引比较的列
`rows`	`rows`	要检查的行的估计值
`filtered`	`filtered`	按表条件筛选的行的百分比
`Extra`	没有	其他信息：

extra
Using filesort
MySQL有两种方式可以生成有序的结果，通过排序操作或者使用索引，当Extra中出现了Using filesort 说明MySQL使用了后者，
    但注意虽然叫filesort但并不是说明就是用了文件来进行排序，只要可能排序都是在内存里完成的。
    大部分情况下利用索引排序更快，所以一般这时也要考虑优化查询了。使用文件完成排序操作，
    这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，
    用索引来为查询结果排序。

Using temporary
用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用
    也要尽量避免硬盘临时表的使用。
	什么情况下会产生这个呢？
    
    
Not exists
MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。

Using index
说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，
    表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。
    这是MySQL服务层完成的，但无需再回表查询记录。

Using index condition
这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的
    ，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。

Using where
使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将
    存储引擎返回服务层以后再应用WHERE条件过滤。

Using join buffer
使用了连接缓存：Block Nested Loop，连接算法是块嵌套循环连接;Batched Key Access，连接算法是批量索引连接

impossible where
where子句的值总是false，不能用来获取任何元组

select tables optimized away
在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，
    不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作