一旦使用了 DISTINCT 和 GROUP BY，那么在查询的执行过程中，通常需要产生临时中间表.

DISTINCT 会产生文件排序

MySQL 在进行 GROUP BY 操作的时候要想利用索引，必须满足 GROUP BY 的字段必须同时存放于同一个索引中

缓存表和汇总表

我们用术语“缓存表”来表示存储那些可以比较简单地从schema其他表获取（但是每次获取的速度比较慢）数据的表（例如，逻辑上冗余的数据）。

术语“汇总表”时，则保存的是使用GROUP BY语句聚合数据的表（例如，数据不是逻辑上冗余的）

在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，也可以保持表不会有很多碎片，以及有完全顺序组织的索引（这会更加高效）

加快ALTER TABLE操作的速度

MySQL的ALTER TABLE操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作可能需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验，ALTER TABLE操作需要花费数个小时甚至数天才能完成。

下面这些操作是有可能不需要重建表的：

移除（不是增加）一个列的AUTO_INCREMENT属性。
增加、移除，或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字串值。

基本的技术是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件，像下面这样（可能会存在问题，不建议如此）：

创建一张有相同结构的空表，并进行所需要的修改（例如增加ENUM常量）。
执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。
交换.frm文件.
执行UNLOCK TABLES来释放第2步的读锁。

MySQL喜欢简单，需要使用数据库的人应该也同样会喜欢简单的原则：

尽量避免过度设计，例如会导致极其复杂查询的schema设计，或者有很多列的表设计（很多的意思是介于有点多和非常多之间）。
使用小而简单的合适数据类型，除非真实数据模型中有确切的需要，否则应该尽可能地避免使用NULL值。
尽量使用相同的数据类型存储相似或相关的值，尤其是要在关联条件中使用的列。
注意可变长字符串，其在临时表和排序时可能导致悲观的按最大长度分配内存。
尽量使用整型定义标识列。
避免使用MySQL已经遗弃的特性，例如指定浮点数的精度，或者整数的显示宽度。
小心使用ENUM和SET。虽然它们用起来很方便，但是不要滥用，否则有时候会变成陷阱。最好避免使用BIT

创建高性能索引

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最优”的索引经常需要重写查询

对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是对于“多个等值条件查询”则没有这个限制。

索引的类型

索引一般分为：哈希索引和B-Tree索引。

B-Tree索引

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找，对如下类型有效：

全值匹配：全值匹配指的是和索引中的所有列进行匹配
匹配最左前缀：使用索引的前几列。
匹配列前缀：也可以只匹配某一列的值的开头部分
匹配范围值
精确匹配某一列并范围匹配另外一列
只访问索引的查询： B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行

B-Tree 索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引
不能跳过索引中的列
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效

如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中

哈希索引的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的
哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作
如果哈希冲突很多的话，一些索引维护操作的代价也会很高

全文索引

索引优点

总结下来索引有如下三个优点：

索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变为顺序I/O。

只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。

高性能索引

正确地创建和使用索引是实现高性能查询的基础。表中的索引越多插入速度会越慢。一般来说，增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后导致达到了内存瓶颈的时候

独立的列

如果查询中的列不是独立的，则MySQL就不会使用索引 “独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

前缀索引和索引选择性

前缀索引是指索引字段中开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间

计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性，下列就是计算不用前缀长度的选择性：

SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
	COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
	COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
	COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6
	FROM city_demo;

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描

多列索引

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列，但通常不如避免随机IO和排序那么重要。

不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能，比如 guest 用户特别多的情况

聚簇索引

InnoDB 使用的是聚簇索引。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。

聚集的数据有一些重要的优点：

可以把相关数据保存在一起，聚簇数据最大限度地提高了I/O密集型应用的性能。
数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引也有一些缺点：

聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂（page split）”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
二级索引访问需要两次索引查找，而不是一次。

InnoDB二级索引的叶子节点中存储的不是“行指针”，而是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。使用主键值当作指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。例如，从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置

覆盖索引

覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值

MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引

索引覆盖查询，在EXPLAIN的Extra列可以看到“Using index”的信息

在表 products 中有索引：(artist，title，prod_id)，用如下方式查询：

SELECT *
-> FROM products
->    JOIN (
->       SELECT prod_id
->       FROM products
->       WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'
->    ) AS t1 ON (t1.prod_id=products.prod_id)

我们把这种方式叫做延迟关联（deferred join），因为延迟了对列的访问。在查询的第一阶段MySQL可以使用覆盖索引，在FROM子句的子查询中找到匹配的prod_id，然后根据这些prod_id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好。

使用索引来排序

如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序

如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序

如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。

不能使用索引做排序查询：

查询使用了两种不同的排序方向，但是索引列都是正序排序的
查询的ORDER BY子句中引用了一个不在索引中的列
查询在条件索引列上是范围条件

使用索引做排序的一个最重要的用法是当查询同时有ORDER BY和LIMIT子句的时候

InnoDB中，因为二级索引的叶子节点包含了主键值，所以在列（A）上的索引就相当于在（A，ID）上的索引

InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢很多。

优化排序

对于那些选择性非常低的列，可以增加一些特殊的索引来做排序，可以创建（sex，rating）索引用于下面的查询：

 SELECT<cols>  FROM profiles WHERE sex='M' ORDER BY rating LIMIT 10;

上述查询同时使用了ORDER BY和LIMIT，如果没有索引的话会很慢。

即使有索引，如果用户界面上需要翻页，并且翻页翻到比较靠后时查询也可能非常慢。

优化这类索引的另一个比较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这可以减少MySQL扫描那些需要丢弃的行数。下面这个查询显示了如何高效地使用（sex，rating）索引进行排序和分页：

SELECT <cols> FROM profiles INNER JOIN (
         SELECT <primary key cols>FROM profiles
         WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
        ) AS x USING(<primary key cols>);

总结

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：

单行访问是很慢的。特别是在机械硬盘存储中（SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
按顺序访问范围数据是很快的，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了。
索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。

查询性能优化

在每一个消耗大量时间的查询案例中，我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。

查询性能低下最基本的原因是访问的数据太多，对于低效的查询可以通过如下步骤分析：

确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。
确认MySQL服务器层是否在分析大量超过需要的数据行

是否向数据库请求了不需要的数据

查询不需要的记录：获取前面N行后关闭结果集，MySQL会查询出全部的结果集，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法就是在这样的查询后面加上LIMIT。
多表关联时返回全部列：SELECT * ...
总是取出全部列：取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O、内存和CPU的消耗
重复查询相同的数据：做缓存

是否在扫描额外的记录

扫描的行数和返回的行数：查看该查询扫描的行数是非常有帮助的，理想情况下扫描的行数和返回的行数应该是相同的
扫描的行数和访问类型：访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等，这里列的这些，速度是从慢到快，扫描的行数也是从小到大。如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引

一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的，但无须再回表查询记录。
从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：

使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了
改变库表结构。例如使用单独的汇总表
重写这个复杂的查询，让MySQL优化器能够以更优化的方式执行这个查询

重构查询方式

在优化有问题的查询时，目标应该是找到一个更优的方法获得实际需要的结果，有时候，可以将查询转换一种写法让其返回一样的结果，但是性能更好。但也可以通过修改应用代码，用另一种方式完成查询，最终达到一样的目的

切分查询

将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。

DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);

DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000

一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法（如果是事务型引擎，很多时候小事务能够更高效）。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

分解关联查询

用分解关联查询的方式重构查询有如下的优势：

让缓存的效率更高
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身效率也可能会有所提升，使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这可能比随机的关联要更高效
可以减少冗余记录的查询，在数据库中做关联查询，则可能需要重复地访问一部分数据

查询执行基础

当向MySQL发送一个请求的时候，MySQL到底做了些什么：

客户端发送一条查询给服务器。
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
将结果返回给客户端。

MySQL 客户端/服务器通信协议

MySQL客户端和服务器之间的通信协议是“半双工”的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，我们无法也无须将一个消息切成小块独立来发送。

这种协议让MySQL通信简单快速，但是也从很多地方限制了MySQL。一个明显的限制是，这意味着没法进行流量控制。一旦一端开始发生消息，另一端要接收完整个消息才能响应它。

一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条结果，然后让服务器停止发送数据

多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放相应的资源。

查询状态

对于一个MySQL连接，或者说一个线程，任何时刻都有一个状态，该状态表示了MySQL当前正在做什么。使用SHOW FULL PROCESSLIST 命令:

Sleep: 线程正在等待客户端发送新的请求
Query: 线程正在执行查询或者正在将结果发送给客户端
Locked: 在MySQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中
Analyzing and statistics: 线程正在收集存储引擎的统计信息，并生成查询的执行计划。
- Copying to tmp table [on disk] 线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上
Sending data: 线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件时也是如此

MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。原因在于MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。

如果查询中有LIMIT的话，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大。

MySQL查询优化器的局限性

MySQL的子查询实现得非常糟糕。最糟糕的一类查询是WHERE条件中包含IN()的子查询语句。

因为使用 IN() 加子查询，性能经常会非常糟，所以通常建议使用 EXISTS() 等效的改写查询来获取更好的效率。

下面的 IN() 加子查询，MySQL 可能会先全表扫描

SELECT * FROM sakila.film
WHERE film_id IN(
   SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

MySQL会将查询改写成下面的样子：

SELECT * FROM sakila.film
WHERE EXISTS (
	SELECT * FROM sakila.film_actor WHERE actor_id = 1
	AND film_actor.film_id = film.film_id);

这时，子查询需要根据film_id来关联外部表film，因为需要film_id字段，所以MySQL认为无法先执行这个子查询。通过EXPLAIN我们可以看到子查询是一个相关子查询（DEPENDENT SUBQUERY）（可以使用EXPLAIN EXTENDED来查看这个查询被改写成了什么样子）

使用 JOIN 来重写查询:

SELECT film.* FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id)
    WHERE actor_id = 1;

UNION 限制

MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上

如果希望 UNION 的各个子句能够根据 LIMIT 只取部分结果集，或者希望能够先排好序再合并结果集的话，就需要在 UNION 的各个子句中分别使用这些子句。例如，想将两个子查询结果联合起来，然后再取前20条记录，那么MySQL会将两个表都存放到同一个临时表中，然后再取出前20行记录

(SELECT first_name, last_name
     FROM sakila.actor
     ORDER BY last_name)
    UNION ALL
    (SELECT first_name, last_name
     FROM sakila.customer
     ORDER BY last_name)
    LIMIT 20;

这条查询将会把actor和customer表中的所有记录存放在一个临时表中，然后再从临时表中取出前20条，在每个子查询加上 LIMIT 来限制：

(SELECT first_name, last_name
	FROM sakila.actor
	ORDER BY last_name
	LIMIT 20)
UNION ALL
(SELECT first_name, last_name
	FROM sakila.customer
	ORDER BY last_name
	LIMIT 20)
LIMIT 20;

从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要加上一个全局的ORDER BY和LIMIT操作。

优化特定类型的查询

优化 `COUNT()` 查询

COUNT() 是一个特殊的函数，有两种非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。

如果在 COUNT() 的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数

在统计行数。最简单的就是使用COUNT（*）

有时候某些业务场景并不要求完全精确的COUNT值，此时可以用近似值来代替。EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行 EXPLAIN 并不需要真正地去执行查询，所以成本很低。

通常来说，COUNT() 都需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此是很难优化的。除了前面的方法，在 MySQL 层面还能做的就只有索引覆盖扫描了。如果这还不够，就需要考虑修改应用的架构，可以增加汇总表,或者增加类似Memcached这样的外部缓存系统。可能很快你就会发现陷入到一个熟悉的困境，“快速，精确和实现简单”，三者永远只能满足其二，必须舍掉其中一个。

优化关联查询

优化关联查询需要特别注意的是:

确保 ON 或者 USING 子句中的列上有索引
确保任何的 GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方

优化 `group by` 和 `distinct`

MySQL优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化，这也是最有效的优化办法。

当无法使用索引的时候，GROUP BY 使用两种策略来完成：使用临时表或者文件排序来做分组。

GROUP BY 后的字段顺序同索引顺序完全一致时，可以使用索引进行分组

CREATE TABLE `admin` (
  `version` int(11) NOT NULL,
  `mode` int(11) NOT NULL,
  `log_level` int(10) unsigned NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `mode` (`mode`,`version`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT;;

对于具有索引 (mode, version) 的表执行如下查询：

EXPLAIN SELECT version FROM admin WHERE mode = 3 GROUP BY version;
--Using where; Using index

EXPLAIN SELECT version FROM admin WHERE mode > 3 GROUP BY  version
--Using where; Using index; Using temporary; Using filesort

EXPLAIN SELECT version FROM admin WHERE mode = 3 GROUP BY version
--Using where; Using index

EXPLAIN SELECT version FROM admin WHERE mode > 3 GROUP BY mode, version
--Using where; Using index

优化 LIMIT 分页

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，例如可能是LIMIT 1000,20这样的查询，这时MySQL需要查询10 020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列

如果这个表非常大，那么这个查询最好改写成下面的样子：

SELECT film.film_id, film.description
FROM sakila.film
   INNER JOIN (
      SELECT film_id FROM sakila.film
      ORDER BY title LIMIT 50, 5
   ) AS lim USING(film_id);

这里的“延迟关联”将大大提升查询效率，它让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用于优化关联查询中的LIMIT子句。

有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得到对应的结果

如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字，MySQL仍然会使用临时表存储结果。事实上，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候这样做是没有必要的

第10章复制

MySQL内建的复制功能是构建基于MySQL的大规模、高性能应用的基础，这类应用使用所谓的“水平扩展”的架构。我们可以通过为服务器配置一个或多个备库的方式来进行数据同步。复制功能不仅有利于构建高性能的应用，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。事实上，可扩展性和高可用性通常是相关联的话题。

MySQL支持两种复制方式：基于行的复制和基于语句的复制。