当前位置:   article > 正文

sql 全解(索引、索引失效、sql优化)_sql索引优化

sql索引优化

一、sql 优化

1. 小表驱动大表

先执行查询数据少的表,再执行查询数据多的表。

2. 建索引,一张表不超过5个索引

避免大量内存占用。

3. 走索引,避免索引失效
4. 尽量做到冷热数据分离

减少查询表列数,避免进行冷数据过滤。

5. 调整索引列的顺序

唯一性较好、字段较短、使用频繁的列放在联合索引的最左侧

6. 对于频繁的查询优先考虑使用覆盖索引

走索引时一并查询出数据。

7. 避免数据类型的隐式转换

避免索引失效。

8. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

会存在冗余字段,消耗更多的CPU和网络带宽 会无法使用覆盖索引。

9. 避免使用子查询,可以把子查询优化为 join 操作

子查询会产生大量的临时表也没有索引。

10. 避免使用 JOIN 关联太多的表

关联表越多关联缓存越大。

11. 在明显不会有重复值时使用 UNION ALL 而不是 UNION

UNION ALL不会进行去重操作,加快查询速度。

12. 大SQL拆分,多个同种SQL进行批处理

一个 SQL 只能使用一个cpu 进行计算,拆分后可由多个cpu并行计算。

13. WHERE 从句中禁止对列进行函数转换和计算

避免索引失效。

14. 使用count(*)而不是count(列名)?

综合性能:count(非主键列) < count(主键) < count(1) ≈ count()
count(
)是SQL92定义的标准统计行数的语法,跟数据库无关,count(*)会统计值为 NULL的行,而
count(列名)不会统计此列为 NULL值的行。

二、索引失效(产生索引失效的时机)

(1)select *
(2)>、<、!=、between
(3)前置 % 的like查询。
(4)where字段值类型与数据库不一致,存在自动转换。
(5)or连接不同字段。
(6)or连接同一字段,但存在 >、<、!= 非索引查询。(左右查询均为索引时才有效。)
(7)函数或运算导致索引失效。
(8)IS NULL不走索引,IS NOT NULL走索引(表设计:非必要时,字段不要为NULL,设置默认空字符串或0)
(9)复合索出现范围查询时,后面的索引失效 。
(10)IN会走索引,但是当IN的取值范围较大时会导致索引失效。

  • ep_range_index_dive_limit这个参数影响in是否使用索引,MySQL 5.6默认10, MySQL。5.7默认200。但是我们代码更倾向于控制在50内。 A表数据大于B表数据时,选择in比exists执行效率要高。 相反,A表数据小于B表数据时,选择exists比较高效。 in先执行子查询,exists先执行外表。
    (11)not in会使索引失效,无论在哪种情况not exists 都比 not in 高效。
  • 使用 left join 或 not exists 来优化 not in 操作。

三、mysql 更新会使用索引吗?

  1. 当只更新少量数据时,mysql 会使用索引来定位数据
  2. 当更新大量数据时,mysql 不会使用索引,而是会全表扫描

当只更新少量数据时,mysql 会使用索引来快速定位数据行,进而提高更新的速度。但是,当对大量数据进行批量更新时,索引的使用就会失效,而采用全表扫描的方式来更新数据。这是因为,mysql 更新数据始终都会去在进行大量数据更新时,需要对每行数据进行存储引擎的读写操作,索引在这种情况下会产生额外的性能开销,进而影响更新的速度。因此,这时候全表扫描的方式更为高效。

总体来说,索引在 mysql 更新操作中的作用是有限的。我们应该根据实际情况来进行索引的使用,避免不必要的开销,提高系统的性能。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/不正经/article/detail/572132
推荐阅读
相关标签
  

闽ICP备14008679号