MySQL之SQL优化

最新推荐文章于 2024-09-21 17:31:48 发布

互联网小Y

最新推荐文章于 2024-09-21 17:31:48 发布

阅读量101

点赞数 1

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/2301_78140533/article/details/132397070

版权

索引优化

索引优化-全值匹配

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

索引优化-最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询条件要从索引的最左前列开始并且不跳过索引列中的列。--带头大哥不能死，中间兄弟不能断。
为什么要遵循最左前缀法则？
- 因为MySQL索引数据结构是B+tree，它会将联合索引字段进行分组排序，如果说查询条件跳过联合索引的第一个字段，叶子节点的其他索引字段将会是无序的，这样会导致索引失效，进行全表扫描。
SQL的条件字段不按联合索引字段顺序写会遵循最左前缀法则吗？
- 答案是遵循的，因为MySQL优化器底层会先自动进行一次SQL语句优化，会把SQL优化成检索索引最好的SQL语句。但是建议我们写SQL的时候就要把条件按照联合索引字段顺序写，避免给优化器带来不必要的负担。

索引优化-索引列上少计算数

不在索引上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
下面两个sql为什么同为范围查询，而使用函数的会索引失效呢？
- 因为第一条SQL索引列name使用了left()函数，B+树的叶子节点的每条数据的name字段都需要进行left()函数计算，进行了全表扫描从而导致了索引失效。而第二条SQL在B+tree叶子节点进行了范围截取，使用type为range范围索引。

索引优化-联合索引范围查询会部分索引失效

B+tree中范围查询会使后面索引字段无序，造成部分索引失效。
- 例如上面sql会导致postiion索引列失效。因为B+tree叶子节点上name和age索引列是有序的，而postiion索引列是无序的将会索引失效。
如何使SQL走指定的某个索引？
- 使用命令 "FORCE INDEX (索引名)" 实现。

索引优化-覆盖索引不写星

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select * 语句，尽可能的减少回表、减少IO。

索引优化-不等空值还有or，索引失效要少用

mysql在使用不等于(!= 或者 <>)，not in，not exists的时候无法使用索引会导致全表扫描。
<，>，<=，>=这些，mysql内部优化器会根据索引比例、表大小等多个因素整体评估是否使用索引。
如果执行计划中，可能用到的索引列possible_keys有值，而实际用到的索引列key没有值，可以使用命令 "FORCE INDEX (索引名)" 实现索引优化。

索引优化-like百分写最右

因为MySQL底层是使用的B+树的数据结构，叶子节点会将索引列进行排序。SQL查询条件使用索引列like进行模糊查询，百分号%写最右边会取到叶子节点的一个小范围数据，会走范围索引。而百分号%写最左边会进行全表扫描，影响查询效率。
问题：解决like '%字符串%' 索引不被使用的方法？
- 使用覆盖索引，查询字段必须是建立覆盖索引字段，这样会比全表扫描查询效率要高点。
- 如果不能使用覆盖索引则可能需要借助搜索引擎ES(elasticsearch)等技术。

索引优化-VAR引号不可丢

以上实例原因是索引字段name为VARCHAR类型，如果不加引号，MySQL会全表扫描将每条数据的name字段先进行类型转换，使得索引失效。

索引优化-范围查询优化

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

索引优化总结

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

SQL优化

SQL优化-不要写select星

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
- 查询时需要先将星号解析成表的所有字段然后再查询，增加查询解析器的成本。
- select * 查询一般不走覆盖索引会产生大量的回表查询。
- 在实际的应用中我们通常只需要某几个字段，其他不需要使用的字段也查出来浪费CPU、内存资源。
- 文本数据、大字段数据传输增加网络消耗。

SQL优化-小表驱动大表

小表驱动大表就是指使用数据量较小、索引比较完备的表，然后使用其索引和条件对大表进行数据赛选，从而减少数据计算量，提高查询效率。

SQL优化-连接查询代替子查询

因为子查询需要执行两次数据库查询，一次是外部查询，一次是嵌套子查询。因此，使用连接查询可以减少数据库查询的次数，提高查询效率。
连接查询可以更好的利用数据库索引，提高查询的性能。子查询通常会使用临时表或内存表，而连接查询可以直接利用表上索引。这意味着连接查询可以更快的访问表中的数据，减少查询的资源消耗。
对于大型数据集。使用连接查询通常比使用子查询更高效（根据实际业务情况而定）。子查询通常需要扫描整个表，而连接查询可以利用索引加速读取操作。

SQL优化-提升group by的效率

如果使用group by的列没有索引，那么查询可能会变得很慢。因此，可以创建一个或多个适当的索引列来加速查询。

SQL优化-批量插入

假如有1w条数据需要插入到数据库，是一条一条处理还是批量处理？建议批量，逐个插入会频繁的与数据库交互，损耗性能。
但是需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。防止造成内存溢出和死锁的情况发生。

SQL优化-使用Limit

百万级表Limit翻页越往后越慢如何优化处理？
首先需要先了解limit操作是如何运行的，以下面这句查询为例：

select * from table_name limit 10000,10;

这句SQL的执行逻辑是：

1、从数据表中读取第N条数据添加到数据集中。

2、重复第一步直到 N = 10000 + 10。

3、根据 offset 抛弃前面10000条数据。

4、返回剩余的10条数据。
第一次优化：
- 根据数据库这种查询的特征，就有了一种想当然的方法，利用自增索引（假设为id）。
- select * from table_name where (id >= 10000) limit 10;
第二次优化：
- 说起数据库查询优化，第一时间想到的就是索引，所以便有了第二次优化：先查找出需要数据的索引列（假设为id），再通过索引列查找出需要的数据。
- select * from table_name where id in (select id from table_name where ( user = xxx )) limit 10000,10;
- 相比较结果是（500w条数据）：第一条话费平均耗时约第二条的 1/3 左右。
- 同样大的offtset，第二次优化的sql更为复杂，为什么性能反而得到了提升呢？
  - 子查询只用到了索引列，没有取实际的数据，所以不涉及到磁盘IO，所以即便是比较大的offset查询速度也不会太差。
  - 利用子查询的方式，把原来的基于 user 的搜索转化为基于主键(id)的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。
第三次优化：
- 在数据量大的时候in操作的效率就不怎样了，我们需要把 in 操作替换掉，使用join就是一个不错的选择。
- select * from table_name inner join (select id from table_name where (user = xxx) limit 10000,10) b using (id);

SQL优化-union all代替union

union all：获取所有数据但是不去重，包含重复数据。
union ：获取所有数据且数据去重，不包含重复数据。
union去重需要遍历、排序和比较，它更耗时，更消耗cpu资源，会增加msql执行器的负担，所以业务上不需要去重尽量使用union all。如果说必须使用union进行连接，只能在两个连接sql层面做好性能优化。