sql索引和性能调优
我们都知道添加索引是优化sql的一大利器,那么其原理是什么呢?有哪些注意事项呢?
sql调优是很注重理论和经验的,既要遵循基本的调优原理,又依赖于经验。
首先,看一个例子,有一张大表,记录数超过100000,SELECT * FROM student WHERE name='xinan'。如果没有索引,查找程序就得全表遍历查找,算法时间复杂度为O(n)。建立索引类似于哈希表映射,这样在查找是可以快速定位,最好的情况可以将查找时间复杂度由O(n)降为O(1),其开销就是增加了索引数据结构。
mysql的默认创建的索引都是btree(balanced tree),全文索引不常用,对于memory引擎,还有hash索引。
CREATE INDEX index_id ON book(book_id); // 典型语句
explain sql; // sql语句性能定位
创建了索引,并不是在执行sql时都会用到,根据Btree索引的原理,有些sql是不会用到索引的,即索引失效,如果不懂这个问题,有可能在开发中采坑。比如以%开头的like语句,or中任意一个字段没有索引的语句。sql执行计划实际上有没有用索引,我们可以通过explain来查看sql语句中索引的使用情况。
接下来我们通过实战来看一下sql的性能优化方法,管中窥豹,图一和图二显示了
- sql语句
- sql语句执行时间(duration)
- 数据传输时间(fetch time)
该表大概有1w条数据,type和title字段都未创建索引。在图一中的最后两行,区别在于有没有or title="title53",其duration和fetch time都相差了若干个数量级,但是其总时间是差不多的。比较第一条语句和最后一条语句,返回的数据差了若干个数量级,但是其fetch time差不多,说明or语句在数据合成时占用了不少时间,可能or有一个合并操作,这个可以使用trace分析,具体参考专业书籍。
图一
在看图二,对于倒数第一个行和倒数第二行,count语句的fetch time又比where语句的时间快上一个数量级,这么快的速度,count(*)应该是直接内存返回, 没有磁盘读写。同时,duration时间相对还是很慢,说明是扫描全表个数还是有些耗时的。
图二
count(*)的执行时间在所有sql中,耗时是很少的,为什么fetch时间可以这么少呢?主要原因在于其数据量很少吧
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列:如何用heaptrack跟踪.NET程序的非托管内存泄露
· TypeScript + Deepseek 打造卜卦网站:技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗?
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句:使用策略模式优化代码结构
· 10年+ .NET Coder 心语 ── 封装的思维:从隐藏、稳定开始理解其本质意义
2018-04-18 Tomcat服务器使用和debug