对于高性能MySQL来说，库表结构优化、索引优化和查询优化需要齐头并进。
对于查询，最重要的是响应时间。查询由一系列子任务构成，要优化查询，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行的更快。
查询的生命周期大致分为：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果。执行可以认为是最重要的阶段。
执行包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。
优化查询的目的就是减少和消除这些操作所花费的时间。
查询性能低下的最基本原因是访问的数据太多。
- 查询不需要的记录：一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是先返回全部结果集再进行计算。最简单有效的方法是加LIMIT
- 多表关联时返回全部列：应该选择需要的行。
SELECT * FROM sakila.actor
INNER JOIN sakila.film_actor USING(actor_id)
INNER JOIN sakila.film USING(film_id)
WHERE sakila.film.title = 'Academy Dinosaur';
会返回三个表的全部列。应该这样写：
mysql> SELECT sakila.actor.*
- 总是取全部列：
SELECT *要怀疑。另外，获取并缓存所有列的查询，相比于多个独立的只获取部分列的查询可能更好。 - 重复查询相同的数据：好方案是初次查询后缓存。
接下来应该看查询为了返回结果是否扫描了过多的数据。
衡量开销的三个指标：响应时间、扫描的行数、返回的行数。这三个指标都会记录到慢日志中。
响应时间是两个部分之和，服务时间和排队时间。一般最常见和重要的等待是I/O和锁等待。响应时间可能是一个问题的结果也可能是一个问题的原因。
实际上可以使用“快速上限估计”法来估算查询的响应时间，这是由TapioLahdenmaki和MikeLeach编写的Relational Database Index Design and the Optimizers（Wiley出版社）一书提到的技术。概括地说，了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O，再用其乘以在具体硬件条件下一次I/O的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值。
扫描的行数和返回的行数。对于找出那些“糟糕”的查询，这个指标可能还不够完美，因为并不是所有的行的访问代价都是相同的。较短的行的访问速度更快，内存中的行也比磁盘中的行的访问速度要快得多。
理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种“美事”并不多。例如在做一个关联查询时，服务器必须要扫描多行才能生成结果集中的一行。扫描的行数对返回的行数的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常非常大。
访问类型：在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描的行数也是从小到大。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。现在应该明白为什么索引对于查询优化如此重要了。索引让MySQL以最高效、扫描行数最少的方式找到需要的记录。
一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：
- 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
- 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
- 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤。
如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：
- 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了
- 改变库表结构。例如使用单独的汇总表。
- 重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询
有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好。但也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的。
在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。
大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法。
简单地，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id= tag.id
JOIN post ON tag_post.post_id= post.id
WHERE tag.tag=' mysql';
可以分 解 成 下面 这些 查询 来 代替：
SELECT * FROM tag_post WHERE tag_id= 1';
SELECT * FROM tag_post WHERE tag_id= 1234;
SELECT * FROM post WHERE post.id in (123, 456, 567, 9098, 8904);
优势：
- 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。例如，上面查询中的tag已经被缓存了，那么应用就可以跳过第一个查询。再例如，应用中已经缓存了ID为123、567、9098的内容，那么第三个查询的IN()中就可以少几个ID。另外，对MySQL的查询缓存来说，如果关联中的某个表发生了变化，那么就无法使用查询缓存了，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
- 将查询分解后，执行单个查询可以减少锁的竞争。
- 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。查询本身效率也可能会有所提升。这个例子中，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效。我们后续将详细介绍这点。
- 可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
- 更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多
在很多场景下，通过重构查询将关联放到应用程序中将会更加高效，这样的场景有很多，比如：当应用能够方便地缓存单个查询的结果的时候、当可以将数据分布到不同的MySQL服务器上的时候、当能够使用IN()的方式代替关联查询的时候、当查询中使用同一个数据表的时候。
查询执行的过程：
- 客户端发送一条查询给服务器。
- 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
- 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
- MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
- 将结果返回给客户端。
MySQL客户端和服务器之间的通信协议是半双工的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块独立来发送。
这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，这意味着没法进行流量控制。一旦一端开始发生消息，另一端要接收完整个消息才能响应它。
客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候，参数max_allowed_packet就特别重要了。一旦客户端发送了请求，它能做的事情就只是等待结果了。
相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据。这种情况下，客户端若接收完整的结果，然后取前面几条需要的结果，或者接收完几条结果后就“粗暴”地断开连接，都不是好主意。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。
换一种方式解释这种行为：当客户端从服务器取数据时，看起来是一个拉数据的过程，但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据，客户端也没法让服务器停下来。客户端像是“从消防水管喝水”。
多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。
当使用多数连接MySQL的库函数从MySQL获取数据时，其结果看起来都像是从MySQL服务器获取数据，而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题，但是如果需要返回一个很大的结果集的时候，这样做并不好，因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集，就能大大减少内存的消耗，这种情况下可以不使用缓存来记录结果而是直接处理。这样做的缺点是，对于服务器来说，需要查询完成后才能释放资源，所以在和客户端交互的整个过程中，服务器的资源都是被这个查询所占用的。
最简单的命令：SHOW FULL PROCESSLIST
- Sleep：线程正在等待客户端发送新的请求
- Query：线程正在执行查询或者正在将结果发送给客户端
- Locked：线程正在等待表锁，行锁并不会体现在线程状态中
- Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
- Sorting result：线程正在对结果集进行排序
- Sending data：线程可能在多个状态之间传送数据，或者生成结果集，或者正在向客户端返回数据
通过大小写敏感的哈希查找实现。查询和缓存中的查询即使只有一个字节不同，也不会匹配。命中后，在缓存中查看权限。返回。
将SQL转换成一个执行计划：解析SQL、预处理、优化SQL执行计划
语法解析器和预处理：通过关键字将SQL语句进行解析，并生成一颗对应的解析树。预处理器则根据一些MySQL规则进一步检查解析树是否合法。
查询优化器：优化器将合法的语法树转化成执行计划。优化器基于成本，会场时预测一个查询使用某种执行计划时的成本。优化器在评估时不使用缓存，假设读取任何数据都需要一次磁盘I/O。
导致优化器错误执行的原因：
- 统计信息不准确：比如InnoDB 因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
- 成本估算不等同于实际成本
- MySQL的最优只是基于其成本模型，并不一定是执行时间
- 从不考虑其它并发的查询
- 有时候也会基于一些固定规则：存在全文搜索MATCH，则会使用全文搜索，不管其他时间更短的方式
- 不会考虑不受其控制的操作的成本。用户自定义函数等
- 有时候无法估算所有可能的执行计划
优化策略分为两种，静态优化与动态优化。
静态优化可以直接对解析树进行分析，并完成优化。是一种编译时优化。
动态优化和查询的上下文有关。
MySQL能处理的优化类型：
- 重新定义关联表的顺序
- 外联结转化成内部联结。有时候外联结等同于内联结。
- 使用等价变换规则：5=5 and a > 5改为a>5
- 优化COUNT MIN MAX：根据索引和列是否为空。MyISAM维护了一个变量来存放数据表的行数
- 预估并转化为常数表达式：表达式可以转化为常数 MIN()函数 主键 唯一索引
- 索引覆盖扫描
- 子查询优化
- 提前终止查询
- 等值传播
- 列表IN的比较：MySQL将IN列表中的数据先进行排序，然后通过二分查找的方式
存储引擎存储着数据和索引的统计信息，查询优化器需要向存储引擎获取相应的统计信息。