本文用于记录苦啃《高性能MySQL(第3版)》后,个人认为有价值的笔记,核心内容均围绕标题展开。既做一个学习的记录,同时也做一个沟通交流,欢迎各位大佬互动~
为什么查询速度会慢
编写快速查询,真正重要的是响应时间。
把查询看作一个任务,它由一系列子任务组成(都消耗时间)。
优化方向:消除部分子任务、减少子任务执行次数、让子任务执行更快
查询的大致生命周期:从客户端到服务器,服务器上解析,生成执行计划,执行(最重要,包括大量为了检索数据到存储引擎的调用以及调用后的数据处理,包括排序和分组),并返回结果给客户端。
时间花费:网络、CPU计算,生成统计信息和执行计划、锁等待(互斥等待)等操作;
慢查询基础:优化数据访问
查询性能地下的最基本原因:访问数据太多。
分析步骤:
- 确认应用程序是否在检索大量超过需要的数据(访问过多的行/列);
- 确认MySQL服务器层是否在分析大量超过需要的数据行。
是否想数据库请求了不需要的数据
- 查询不需要的记录。网站取100条记录,页面只显示10条,最好的方式就是加上LIMIT。
- 多表关联时返回全部列。
- 总是取出全部列。SELECT *,会导致优化器无法完成索引覆盖优化,还会带来额外的I/O、内存和CPU的消耗。
- 重复查询相同的数据。用户评论多次,重复获取头像URL,最好的方式是首次查询的时候将数据缓存起来。
MySQL是否在扫描额外的记录
衡量查询开销的三个指标:响应时间(服务时间、排队时间)、扫描行数、返回的行数(理想情况下两者应该一样)。
一般MySQL能够使用三种方式应用WHERE条件,从好到坏为:
- 在索引中使用WHERE条件来过滤不匹配的记录。存储引擎层完成。
- 使用索引覆盖扫描(Extra列出现了Using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。服务器层完成,无须回表;
- 从数据表中返回数据,然后过滤不满足条件的记录(Extra列出现了Using Where),服务器层完成。
某查询扫描大量数据但返回少数的行,策略:
- 使用索引覆盖扫描,把所有需要的列放进索引;
- 改变库表结构。例如单独的汇总表;
- 重写复杂的查询。让优化器更加优化的方式执行这个查询。
重构查询的方式
一个复杂查询还是多个简单查询
网络速度块,无需让一次查询在服务器完成尽可能多的工作。
大查询拆分成小查询。
切分查询
删除数据,避免一次锁住大量数据,占满整个事务日志,耗尽系统资源,阻塞很多很小的但是很重要的查询,将大的DELETE语句切分成多个较小的查询。
分解关联查询
很多高性能应用都会对关联查询进行分解。每个表进行单表查询,然后结果在应用程序中进行关联。
优势:
- 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
- 将查询分解后,执行单个查询可以减少锁的竞争。
- 应用层关联,可以做到高性能和可拓展。
- 查询本身效率可能提升。
- 可以减少冗余记录查询。
- 相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套循环关联。
查询执行的基础
向MySQL发送一个请求,MySQL做了什么:
MySQL客户端/服务器通信协议
查询缓存
查询优化处理(语法解析器和预处理、查询优化器、数据和索引的统计信息)-执行计划(指令树)
查询执行引擎(执行计划是一个数据结构)
返回结果给客户端