数据库sql优化总结之1-百万级数据库优化方案+案例分析

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 数据库sql优化总结之1-百万级数据库优化方案+案例分析

目录


项目背景


1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。


案例分析:


2、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描


案例分析:


3、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。


案例分析:


4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描


案例分析:


5.in 和 not in 也要慎用,否则会导致全表扫描


案例分析


案例分析2:


案例分析3:


6、like模糊全匹配也将导致全表扫描


案例分析


项目背景

有三张百万级数据表


知识点表(ex_subject_point)9,316条数据


试题表(ex_question_junior)2,159,519条数据 有45个字段


知识点试题关系表(ex_question_r_knowledge)3,156,155条数据


测试数据库为:mysql (5.7)



1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

案例分析:

image.png    执行时间:17.609s (多次执行,在17s左右徘徊)


 优化后:给GRADE_ID字段添加索引后


 执行时间为:11.377s(多次执行,在11s左右徘徊)


 备注:我们一般在什么字段上建索引?


 这是一个非常复杂的话题,需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:


   a、字段出现在查询条件中,并且查询条件可以使用索引;


   b、语句执行频率高,一天会有几千次以上;


   c、通过字段条件可筛选的记录集很小,那数据筛选比例是多少才适合?


 这个没有固定值,需要根据表数据量来评估,以下是经验公式,可用于快速评估:


 小表(记录数小于10000行的表):筛选比例<10%;


 大表:(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16


 单条记录长度≈字段平均内容长度之和+字段数*2


 以下是一些字段是否需要建B-TREE索引的经验分类:image.png2、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num is null


最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.


备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。


不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。


可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:


select id from t where num = 0


案例分析:

在mysql数据库中对字段进行null值判断,是不会放弃使用索引而进行全表扫描的。image.png执行时间是12.253s

时间几乎一样。

3、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。

案例分析:

在mysql数据库中where 子句中使用 != 或 <> 操作符,引擎不会放弃使用索引。image.png

4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描

案例分析:

GRADE_ID字段有索引,QUESTION_TYPE没索引

image.png执行时间是:11.811s


但是,非索引字段依然查询速度会很慢,所以查询条件,能加索引的尽量加索引


5.in 和 not in 也要慎用,否则会导致全表扫描

案例分析

注:在mysql数据库中where 子句中对索引字段使用 in 和 not in操作符,引擎不会放弃使用索引。

image.png


执行时间为0.924s



时间上是相差不多的


案例分析3:

用exists 和 in区别:结论


用exists 和 in区别:结论


1. in()适合B表比A表数据大的情况(A


select * from A


where id in(select id from B)


2. exists()适合B表比A表数据小的情况(A>B)


 select * from A


 where exists(


 select 1 from B where B.id = A.id


 )


3.当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.语法


select * from A


where id in(select id from B)


ex_question_r_knowledge表数据量大,ex_subject_point表数据量小


****************************************************************************


ex_question_r_knowledge(A)表数据量大,ex_subject_point表数据量小(B)(A>B)


用exists适合

image.pngimage.pngimage.png若要提高效率,可以考虑全文检索。lucene了解一下。或者其他可以提供全文索引的nosql数据库,比如tt server或MongoDB


还会陆续更新,还有几个小节。



昨天晚上突发奇想,like 模糊全匹配,会导致全表扫描,那模糊后匹配和模糊前匹配也会是全表扫描吗?


今天开电脑,做了下测试。结果如下:


like模糊后匹配,不会导致全表扫描

image.pngimage.pngMY SQL的原理就是这样的,LIKE模糊全匹配会导致索引失效,进行全表扫描;LIKE模糊前匹配也会导致索引失效,进行全表扫描;但是LIKE模糊后匹配,索引就会有效果。



参考:



https://mp.weixin.qq.com/s?__biz=MzIxMjg4NDU1NA==&mid=2247483684&idx=1&sn=f5abc60e696b2063e43cd9ccb40df101&chksm=97be0c01a0c98517029ff9aa280b398ab5c81fa1fcfe0e746222a3bfe75396d9eea1e249af38&mpshare=1&scene=1&srcid=0606XGHeBS4RBZloVv786wBY#rd



***************************************************************************


作者:小虚竹

欢迎任何形式的转载,但请务必注明出处。

限于本人水平,如果文章和代码有表述不当之处,还请不吝赐教。



我不是个伟大的程序员,我只是个有着一些优秀习惯的好程序员而己


image.png


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
13天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
14天前
|
存储 NoSQL 分布式数据库
微服务架构下的数据库设计与优化策略####
本文深入探讨了在微服务架构下,如何进行高效的数据库设计与优化,以确保系统的可扩展性、低延迟与高并发处理能力。不同于传统单一数据库模式,微服务架构要求更细粒度的服务划分,这对数据库设计提出了新的挑战。本文将从数据库分片、复制、事务管理及性能调优等方面阐述最佳实践,旨在为开发者提供一套系统性的解决方案框架。 ####
|
15天前
|
存储 SQL 数据库
深入浅出后端开发之数据库优化实战
【10月更文挑战第35天】在软件开发的世界里,数据库性能直接关系到应用的响应速度和用户体验。本文将带你了解如何通过合理的索引设计、查询优化以及恰当的数据存储策略来提升数据库性能。我们将一起探索这些技巧背后的原理,并通过实际案例感受优化带来的显著效果。
31 4
|
22天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
77 10
|
17天前
|
SQL druid 数据库
如何进行数据库连接池的参数优化?
数据库连接池参数优化包括:1) 确定合适的初始连接数,考虑数据库规模和应用需求;2) 调整最大连接数,依据并发量和资源状况;3) 设置最小空闲连接数,平衡资源利用和响应速度;4) 优化连接超时时间,确保系统响应和资源利用合理;5) 配置连接有效性检测,定期检查连接状态;6) 调整空闲连接回收时间,适应访问模式并配合数据库超时设置。
|
21天前
|
SQL 存储 缓存
SQL Server 数据太多如何优化
11种优化方案供你参考,优化 SQL Server 数据库性能得从多个方面着手,包括硬件配置、数据库结构、查询优化、索引管理、分区分表、并行处理等。通过合理的索引、查询优化、数据分区等技术,可以在数据量增大时保持较好的性能。同时,定期进行数据库维护和清理,保证数据库高效运行。
|
21天前
|
SQL 缓存 监控
数据库优化
【10月更文挑战第29天】数据库优化
29 1
|
21天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
45 1
|
23天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
116 1
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
下一篇
无影云桌面