最近在读<<高性能mysql(第四版)>>
学习了一下这个mysql的查询优化
我们应该明确:优化应该三管齐下:不做,少做,快速的做
对一查询,真正重要的是响应时间
如果把查询看作一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。
那我们应该查看查询的生命周期(大概)
客户端 -> 服务端 -> 语法解析-> 生成执行计划-> 执行 -> 返回客户端
下面开始分析
返回的数据
一条查询如果性能差的话 大概是因为访问了太多的数据,在这一步需要考虑
- 确认应用程序是否检索的了大量且不要的数据,比如太多的行 和 太多的列
- mysql服务层是否分析了大量的不需要的数据行
如果查询了大量超过实际需求的数据,多余的数据会被应用程序丢弃,也会给mysql 服务带来大量的无用的网络开销,也会消耗服务器的cpu和存储
可以举出常见的例子
- 查询了不需要的记录.
比如先用select 语言查询全部的行,再取出需要的10条数据(比如你问一个聚会上的所有人,然后再自己判断是不是自己想找的人)
我们可以直接使用limit(直接问你是不是谁谁谁,找到之后就不用找了) - 多表联接时返回全部列
比如你想查询所有这个电影中出现的演员,你需要两张表联合查询,你直接读取全部的列 使用select * from 什么什么
这个是多余的,应该直接读取你所需要的数据 比如 select name from 什么什么 - 当我们多次查询相同的数据
如何我需要一个列的全部数据,可以先读取全部的数据,放入缓存中,在需要的时候使用,减少多次调用查询
查询的扫描
在确认了查询只返回需要的数据
下一步是 查询返回结果是不是扫描了过多的行
可以通过 三个简单的数据指标 大概 确认是否扫描了过多的数据
- 响应时间
- 扫描的行数
- 返回行数
响应时间
响应时间分为服务时间(数据库查询) 和 排队时间(等待i/o的消耗,等待行锁等造成的时间)
我们只需要确认这个响应时间是个合理的数据就可以了
我们可以通过
了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O，再用其乘以在具体硬件条件下一次I/O的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值。
扫描行数和 返回的行数
最好扫描的行数 最接近返回的行数
扫描的行数和访问类型
我们可以使用explain 来查询访问类型,
查看type 的值
访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等
如果不是合适的访问类型, 通常添加一索引
一般地，MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：
●在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
●使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
●从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Usingwhere）。这在MySQL服务器层完成，MySQL需要先从数据表中读出记录然后过滤。
这个是书中的
但是我认为最快的方法使用索引覆盖扫描,书中认为这个 索引中使用where 是最好的,可能是因为不要添加额外的索引
如果发现查询需要扫描大量的数据但只返回少数行
可以通过以下的方法优化
- 使用索引覆盖扫描
- 改变库结构,使用单独的汇总表
- 重写复杂的查询
重构查询
复杂的查询还是多个简单的查询
在书中说
但是这样的想法对于MySQL并不适用，因为MySQL从设计上让连接和断开连接都很轻量，在返回一个小的查询结果方面很高效。现代的网络速度比以前要快很多，能在很大程度上降低延迟。在某些版本的MySQL中，即使在一台通用服务器上，也能够运行每秒超过10万次的简单查询，即使是一个千兆网卡也能轻松满足每秒超过2000次的查询。所以运行多个小查询现在已经不是大问题了。
认为是可以将一个复杂的查询分成多个简单的查询
书中还说
在设计应用的时候，如果在一个查询能够胜任时还将其写成多个独立的查询是不明智的。例如，我们看到有些应用对一个数据表做10次独立的查询来返回10行数据，每个查询返回一条结果，查询10次，这时可以使用单个查询获取10行数据。有的应用甚至每次只查询一个字段，获取一行数据就需要执行多次查询。
在一个查询可以胜任的时候,是不要分成多个简单的查询
切分查询
有时候对于一个大查询，我们需要“分而治之”，将大查询切分成小查询，每个查询的功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
删除旧的数据就是一个很好的例子。定期清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
我们可以将一个查询拆分多个较小的查询,尽可能的小的影响mysql的性能,和mysql的复制的延迟
一次删除一万行数据一般来说是一个比较高效而且对服务器影响最小的做法（如果是事务型引擎，很多时候小事务能够更高效）。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，也可以将服务器上原本一次性的压力分散到一个很长的时间段中，可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。
需要将 一次压力分散 可以使用 切分查询
分解连接查询
很多高性能的应用都会对联接查询进行分解。简单地说，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行联接
在应用程序中进行连接,而不是在数据中
- 让缓存的效率更高 方便的使用缓存
- 将查询分解后，执行单个查询可以减少锁的竞争
- 在应用层做联接，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
- 查询本身的效率也可能会有所提升
- 可以减少对冗余记录的访问
查询优化的基础
mysql 客户端 和服务端使用是”半双工”
在任何时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。
我们无法也无须将一个消息切成小块来独立发送。
那么客户端接收服务端时候,一般使用的缓存存储所有的数据,再读取
所以我们也可以不缓存
mw