MySQL性能优化--查询优化

如果查询写的很糟糕，即使库表结构再合理，索引再合适，也无法实现高性能；

1. 为什么查询速度变慢

• 查询的子任务：

查询的生命周期大致可以按照顺序来看：

从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行， 并返回结果给客户端；

其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等；

• 时间消耗：

在完成这些任务的时候，查询需要在不同的地方花费时间，包括：网络、CPU计算、生成统计信息和执行计划、锁等待（互斥等待）等操作； 尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存、CPU、IO（内存不足时）上消耗时间； 根据存储引擎的不同，可能还会产生大量的上下文切换以及系统调用；

优化的目的就是减少和消除这些操作所花费的时间；

2. 慢查询基础：优化数据访问

查询性能低下的最基本原因是访问的数据太多；

大部分性能低下的查询都可以通过减少访问的数据量的方式来优化；

1.确认应用程序是否检索了大量超过需要的数据—-访问了太多的行或太多的列

2.确认MySQL服务器层是否在分析大量超过需要的数据行；

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

• 查询不需要的记录
  使用limit

• 多表关联时返回全部列
  只取需要的列

• 总是取出全部列
  不要用select *

• 重复查询相同的数据

2.2. MySQL是否在扫描额外的记录

衡量查询开销的三个指标：

1.响应时间

2.扫描的行数

3.返回的行数

这三个指标都会记录到MySQL的慢查询日志中，所以慢查询记录是找出扫描行数过多的查询的好办法；

响应时间

响应时间=服务时间+排队时间

服务时间：数据库处理这个查询真正花了多长时间；

排队时间：服务器因为等待某些资源而没有真正执行查询的时间（等待IO或者等待行锁，等等）

扫描的行数和返回的行数

理想情况下扫描的行数和返回的行数应该是相同的，但实际很难做到；

扫描的行数和访问类型（type）

在explain语句中的type列反应了访问类型；

索引可以让MySQL以最高效、扫描行数最少的方式找到需要的记录；

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

1.在索引中使用where条件来过滤不匹配的记录；在存储引擎完成；

2.使用索引覆盖扫描（Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果； 这是在服务器层完成的，但无须再回表查询记录；

3.从数据表中返回数据；然后过滤不满足条件的记录（Extra列出现Using Where）；这在MySQL服务器层完成，MySQL需要先从数据表读出记录然后过滤；

• 优化：

1.使用索引覆盖扫描；

2.改变库表结构；例如使用单独的汇总表；

3.重写这个复杂的查询；

3. 重构查询的方式

1.可以将查询转换一种写法让其返回一样的结果，但性能更好；

2.修改相应应用代码，用另一种方式完成查询，达到一样的目的；（没必要获取一模一样的结果集）

3.1. 一个复杂查询还是多个简单查询

• 使用多个简单查询：

在传统的实现中，由于网络通信、查询解析和优化代价很高，总是强调需要数据库层完成尽可能多的工作；

但对于MySQL，MySQL从设计上让连接和断开都很轻量级，在返回一个小的查询结果方面很高效；

现代的网络速度比以前要快很多，无论是带宽还是延迟；

在某些版本的MySQL，即使在一个通用服务器上，也能运行每秒超过10万的查询，即使是一个千兆网卡也能轻松满足每秒超过2000次的查询； 所以运行多个小查询现在已经不是大问题了；

当然如果一个查询能够胜任时还写成多个独立查询是不明智的；

MySQL内部每秒能扫描内存中上百万行数据，但响应数据给客户端就慢多了，在其他条件都相同的时候，使用尽可能少的查询；

3.2. 切分查询

对于一个特别大的查询，要“分而治之”，将大查询切分成小查询，每个小查询功能完全一样，只完成一小部分然后返回；

例如大量数据的删除：

delete from tasks where created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);

定期删除大量数据时，如果用一个大的语句一次性完成，则可能需要一次性锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但很重要的查询；

可以改进：

rows_affected = 0
do {
    rows_affected = do_query(
        "delete from tasks where created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH) limit 10000"
    )
} while rows_affected > 0

将一个大的delete语句切分成多个较小的查询可以尽可能小地影响MySQL性能，减少MySQL复制的延迟；

每次删除数据后，都暂停一会再做下一次删除，也可以将服务器上原本一次性的压力分散，大大降低对服务器的影响，减少删除时锁的持有时间；

3.3. 分解关联查询

• 让缓存的效率更高

• 将查询分解后，执行单个查询可以减少锁的竞争

• 在应用层做关联，方便对数据库进行拆分，更容易做到高性能和可扩展；

• 查询本身效率可能会有所提升；

• 减少冗余记录的查询

• 更进一步，这相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联；

4. 查询执行的基础

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

1.客户端发送一条查询给服务器；

2.服务端先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果；否则进入下一阶段；

3.服务端进行SQL解析、预处理，再由优化器生成对应的执行计划；

4.MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；

5.将结果返回给客户端；

查询优化器是其中特别复制也特别难理解的部分；

4.1. MySQL客户端、服务端通信协议

MySQL客户端和服务端之间的通信协议是“半双工”的； 即在任何一个时刻，要么是由服务器向客户端发送数据、要么是🈶由客户端向服务端发送数据，两个动作不能同时发生；

所以无法将一个消息切成小块独立来发送；

这种协议让MySQL通信简单快速，但也从很多地方限制了MySQL：没有办法进行流量控制； 一旦一方开始发送消息，另一端要接收完整个消息才能响应；（类比两个人抛一个球）

• 大量数据传输

客户端：
客户端用一个单独的数据包将查询传给服务器，查询语句很长的时候，需要使用max_allowed_packet; 一旦客户端发送了请求，就只能等待结果了；

服务端：
服务端响应的数据很多时，响应客户端请求时，客户端必须完整的接收整个返回结果；（所以要加limit）；

客户端从服务器取数据时，实际是MySQL在向客户端推送数据的过程；

查询状态

show full processlist;

mysql> show full processlist;
+----+-----------------+-----------------+----------+---------+--------+------------------------+-----------------------+
| Id | User            | Host            | db       | Command | Time   | State                  | Info                  |
+----+-----------------+-----------------+----------+---------+--------+------------------------+-----------------------+
|  4 | event_scheduler | localhost       | NULL     | Daemon  | 301594 | Waiting on empty queue | NULL                  |
|  9 | root            | localhost:54807 | qwq_test | Query   |      0 | starting               | show full processlist |
+----+-----------------+-----------------+----------+---------+--------+------------------------+-----------------------+
2 rows in set (0.00 sec)

在一个查询的生命周期中，状态会变化很多次；

4.2. 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据；

如果检查用户权限没有问题，MySQL会跳过其他阶段，直接从缓存中拿到结果返回给客户端；

这种情况，查询不会被解析，不用生成执行计划，不会被执行；

4.3. 查询优化处理

将SQL转换成执行计划，MySQL再依照这个执行计划和存储引擎进行交互；

包括多个子阶段：解析SQL、预处理、优化SQL执行计划

语法解析器和预处理

首先，MySQL通过关键字将SQL语句解析，并生成一颗对应的“解析树”，MySQL解析器将使用MySQL语法规则验证和解析查询；

预处理器则根据一些MySQL规则进一步检查解析树是否合法；

接下来预处理器会验证权限，通常很快，除非服务器上有很多权限配置；

查询优化器

一条查询可以有很多种执行方式，最后都返回相同的结果；优化器的作用是找到这其中最好的执行计划；

查询优化器是一个非常复杂的部件，使用了很多优化策略来生成一个最优的执行计划；

优化策略分为两种：

1.静态优化：直接对解析树进行分析，并完成优化； 优化器可以通过一些简单的代数变换，将where条件转换成另一种等价形式；

2.动态优化：和查询的上下文有关，也可能和其他很多因素有关；如where条件中的取值、索引中条目对应的数据行等；

MySQL能够处理的优化类型：

• 重新定义关联表的顺序

• 将外连接转化成内连接

• 使用等价交换规则

• 优化COUNT()、MIN()、MAX()

• 预估并转化为常数表达式

• 覆盖索引扫描

• 子查询优化

• 提前终止查询

• 等值传播

• 列表IN()的比较

优化器已经很智能了，但有时也无法给出最优的结果；

如果能够确认优化器给出的不是最佳选择，并且清楚背后的原理，那么也可以帮助优化器做进一步的优化；

数据和索引的统计信息

服务器层没有任何统计信息，所以MySQL查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息；

存储引擎提供给优化器对应的统计信息，包括： 每个表或者索引有多少个页面，每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等；

MySQL如何执行关联查询

执行计划

MySQL生成查询的一颗指令树，然后通过存储引擎执行完成这颗指令树并返回结果； 最终的执行计划包含了重构查询的全部信息；

mysql> explain select * from project where name like "%test";
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | project | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `qwq_test`.`project`.`id` AS `id`,`qwq_test`.`project`.`name` AS `name`,`qwq_test`.`project`.`c1` AS `c1` from `qwq_test`.`project` where (`qwq_test`.`project`.`name` like '%test') |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

使用show warnings可以看到重构出的查询；

关联查询优化器

优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序；

关联优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树； 如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一颗可能的执行计划树的成本，最后返回一个最优的执行计划；

排序优化

从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序；

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

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作； 如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘， 然后将各个排好序的块进行合并，最后返回排序结果；

MySQL的两种排序算法：

1.两次传输排序（旧版本）
读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行；

2.单次传输排序（新版本使用）
先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果；

很难说哪个算法效率更高，两种算法都有各自最好和最糟的场景；当查询需要所有列的总长度不超过max_length_for_sort_data时， MySQL使用“单次”传输排序； 可以通过调整这个参数来影响MySQL排序算法的选择；

关联查询中的排序：

如果order by子句中的所有列来自关联的第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序； 在explain结果中可以看到Extra字段会有：Using filesort；

其他情况下，MySQL会先将关联的结果放到一个临时表中，然后在所有的关联都结束后，再进行文件排序； 在explain结果的Extra字段可以看到：Using temporary；Using filesort；

如果查询中有limit的话，limit也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然非常大； MySQL5.6做了很多改进，当只需要返回部分结果时，例如使用limit，不再对所有结果排序，而是根据实际情况，选择抛弃不满足条件的结果，然后排序；

4.4. 查询执行引擎

MySQL只是简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成；

这些接口也就是“handler API”；查询中的每个表由一个handler的实例表示；实际上，MySQL在优化阶段就为每个表创建了一个handler实例， 优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息，等等；

4.5. 返回结果给客户端

返回结果集或者查询影响的行数给客户端；

如果查询可以被缓存，那么MySQL在这个阶段也会将结果存放到查询缓存；

MySQL将结果集返回给客户端是一个增量、逐步返回的过程； 一旦服务器处理完最后一个关联表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了；

好处： 1.服务端无须存储太多的结果，也不会因为要返回太多结果而消耗大量内存；
2.客户端第一时间获得返回结果

结果集中的每一行都会以一个满足MySQL客户端/服务端通信协议的封包发送，再通过TCP传输；

在TCP传输过程中，可能对MySQL的封包进行缓存然后批量传输；

5. MySQL查询优化器的局限性

MySQL5.6之后，消除了很多MySQL原本的限制，让更多的查询能尽可能高效的完成；

5.1. 关联子查询

MySQL的子查询实现的很糟糕； 最糟糕的一类查询是where条件中包含IN()的子查询语句；

select * from user where id in (select user_id from user_roles where role_id=xxxx);

mysql> explain select * from user where id in (select user_id from user_roles where role_id=xxxx);
+----+--------------+-------------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key     | key_len | ref                 | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                | NULL |   100.00 | NULL        |
|  1 | SIMPLE       | user        | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | <subquery2>.user_id |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | user_roles  | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                |    5 |    20.00 | Using where |
+----+--------------+-------------+------------+--------+---------------+---------+---------+---------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `qwq_test`.`user`.`id` AS `id`,`qwq_test`.`user`.`name` AS `name`,`qwq_test`.`user`.`description` AS `description`,`qwq_test`.`user`.`email` AS `email`,`qwq_test`.`user`.`token_expired` AS `token_expired`,`qwq_test`.`user`.`password_expired` AS `password_expired`,`qwq_test`.`user`.`created_at` AS `created_at`,`qwq_test`.`user`.`updated_at` AS `updated_at`,`qwq_test`.`user`.`creator` AS `creator`,`qwq_test`.`user`.`modifier` AS `modifier`,`qwq_test`.`user`.`real_name` AS `real_name`,`qwq_test`.`user`.`verify_code` AS `verify_code`,`qwq_test`.`user`.`verify_code_expire` AS `verify_code_expire`,`qwq_test`.`user`.`user_type` AS `user_type` from `qwq_test`.`user` semi join (`qwq_test`.`user_roles`) where ((`qwq_test`.`user`.`id` = `<subquery2>`.`user_id`) and (`qwq_test`.`user_roles`.`role_id` = xxxx)) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL会将相关的外层表压到子查询中，它认为这样可以更高效率的查到数据行； show warnings： select * from user semi join user_roles where ((user.id = <subquery2>.user_id) and (user_roles.role_id = xxxx))；

select * from user where exists (select * from user_roles where role_id=xxxx and user_roles.user_id=user.id);

如果外层的表是一个非常大的表，那么这个查询的性能会非常糟糕；

改写：
1.使用inner join … using(user_id)；
2.使用函数GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表；
3.使用EXISTS()等效的改写查询来获取更好的效率；

不需要听取那些关于子查询的“绝对真理”； 应该用测试来验证对子查询的执行计划和响应时间的假设；

5.2. UNION的限制

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

(select name from project order by name) union all (select name from app order by name) limit 20;

改进：

(select name from project order by name limit 20) union all (select name from app order by name limit 20) limit 20;

5.3. 索引合并优化

在5.0和更新的版本中，当where子句中包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行；

5.4. 等值传递

某些时候，等值传递会带来一些意想不到的额外消耗； 例如，有一个非常大的IN()列表，而MySQL优化器发现存在where、on或者using的子句，将这个列表的值和另一个表的某个列相关联；

优化器会将IN()列表都复制应用到关联的各个表中； 通常，因为各个表新增了过滤条件，优化器可以更高效的从存储引擎过滤记录； 但是如果这个列表非常大，则会导致优化和执行都会变慢；

5.5. 并行执行

MySQL无法利用多核特性来并行执行查询；

5.6. 哈希关联

MySQL不支持哈希关联—MySQL的所有关联都是嵌套循环关联；

如果使用的是Memory存储索引，则索引都是哈希索引；所以关联的时候也类似于哈希关联；

5.7. 松散索引扫描

由于历史原因，MySQL并不支持松散索引扫描；无法按照不连续的方式扫描一个索引；

5.8. 最大值和最小值优化

对于MAX()和MIN()查询，MySQL的优化做的并不好，例如：

select min(project_id) from project where name="test";

如果name字段没有索引，MySQL会进行一次全表扫描；

改进：

select project_id from project where name="test" limit 1;

5.9. 在同一个表上查询和更新

MySQL不允许对同一张表同时进行更新和查询；

mysql> update project as p set c1 = (select count(*) from project where id=3);
ERROR 1093 (HY000): You can't specify target table 'p' for update in FROM clause

You can’t specify target table ‘p’ for update in FROM clause

可以通过使用生成表的形式绕过限制；

mysql> update project as p inner join(select count(*) as cnt from project where id=3) as p1 set p.c1=p1.cnt;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 4  Changed: 3  Warnings: 0

6. 查询优化器的提示

如果对优化器选择的执行计划不满意，可以使用优化器提供的几个提示(hint)来控制最终的执行计划；

• HIGH_PRIORITY和LOW_PRIORITY

• DELAYED

• STRAIGHT_JOIN

• SQL_SMALL_RESULT和SQL_BIG_RESULT

• SQL_BUFFER_RESULT

• SQL_CACHE和SQL_NO_CACHE

• SQL_CALL_FOUND_ROWS

• FOR UPDATE和LOCK IN SHARE MODE

• USE INDEX, IGNORE INDEX和FORCE INDEX

MySQL5.0和更新的版本：

• optimizer_search_depth

• optimizer_prune_level

• optimizer_switch

7. 优化特定类型的查询

在不同的 MySQL 引擎中，count(*) 有不同的实现方式。

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；

而 InnoDB 引擎执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

MyISAM 表虽然 count(*) 很快，但是不支持事务；

show table status 命令虽然返回很快，但是不准确；

InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。

7.0.1. COUNT()的作用

• 为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？

即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的

这和 InnoDB 的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是 MVCC 来实现的。

每一行记录都要判断自己是否对这个会话可见，因此对于 count(*) 请求来说，InnoDB 只好把数据一行一行地读出依次判断，可见的行才能够用于计算“基于这个查询”的表的总行数。

• MySQL，在执行 count(*) 操作的优化。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。

对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。

因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

7.0.2. 关于MyISAM的神话

如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。

当统计带上WHERE子句的结果集行数，可以是统计某个列值的数量时，MyISAM和其他存储引擎没有任何不同，不再有神话般的速度了；

所以，在MyISAM引擎上执行COUNT()有时候比别的引擎快，有时候慢，要视具体情况而定；

7.0.3. 简单的优化

select count(*) from world.city where id > 5;

select (select count() from world.city) - count() from world.city where id <= 5;

7.0.4. COUNT(*)、COUNT(id)和COUNT(1)

count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；

而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数

• count(主键 id):

InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

• count(1):

InnoDB 引擎遍历整张表，但不取值。 server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

• count(字段)：

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

• count(*):

并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

结论：

按照效率排序的话，count(字段) < count(主键id) < count(1) ~ count(*)

尽量使用count(*)

7.0.5. 计数功能

1.使用Redis计数

用一个 Redis 服务来保存这个表的总行数。这个表每被插入一行 Redis 计数就加 1，每被删除一行 Redis 计数就减 1

问题：

缓存系统可能会丢失更新

将计数保存在缓存系统中的方式，还不只是丢失更新的问题。即使 Redis 正常工作，这个值还是逻辑上不精确的。

在并发系统里面，我们是无法精确控制不同线程的执行时刻的，即使 Redis 正常工作，这个计数值还是逻辑上不精确的。

2.在数据库保存计数

从并发系统性能的角度考虑，应该先插入操作记录，再更新计数表。

更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少了事务之间的锁等待，提升了并发度。

7.1. 优化关联查询

7.2. 优化GROUP BY和DISTINCT

7.3. 优化LIMIT分页

系统中需要分页的时候，通常使用limit offset的办法来实现；同时加上合适的order by子句；

如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序工作；

优化：

1.尽可能的使用索引覆盖扫描，而不是查询所有的列；然后根据需要做一次关联操作再返回所需的列；

select project_id, description from project order by name limit 5000,5;

改进： select project_id, description from project inner join (select project_id from project order by name limit 5000,5) as lim using(project_id);

“延迟关联”将大大提升查询效率，可以让MySQL扫描尽可能少的页面，获取需要访问的记录后再根据关联列回原表查询需要的所有列；

2.将limit查询转换为已知位置的查询，让MySQL通过范围扫描得到对应的结果；

select project_id, description from project where position between 5000 and 5050 order by position;

3.limit和offset的问题，其实是offset的问题，会导致MySQL扫描大量不需要的行然后再抛弃掉； 如果可以记录上次获取的位置，则可以避免使用offset；

select * from project order by position desc limit 20; –>5030–5050的记录

下一页查询从5030开始： select * from project where position <5030 order by position desc limit 20;

4.使用预先计算的汇总表；或者关联到一个冗余表；

7.4. 优化SQL_CALC_FOUND_ROWS

select count(*) from table where …; 查出符合条件的记录总数

select * from table where … limit M,N; 查询当页要显示的数据

可以使用以下语句代替：

select SQL_CALC_FOUND_ROWS * from table where … limit M, N;

mysql> select SQL_CALC_FOUND_ROWS * from t;
+---------------------+------+
| ftime               | c    |
+---------------------+------+
| 2017-04-01 00:00:00 |    2 |
| 2017-02-01 00:00:00 |    2 |
| 2018-04-01 00:00:00 |    2 |
| 2018-02-01 00:00:00 |    2 |
+---------------------+------+
4 rows in set (0.00 sec)

select FOUND_ROWS();

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

MySQL只有在扫描了所有满足条件的行以后，才会知道行数，所以加上这个提示以后，不管是否需要，MySQL都会扫描所有满足条件的行：

mysql> select SQL_CALC_FOUND_ROWS * from t limit 2 offset 1;
+---------------------+------+
| ftime               | c    |
+---------------------+------+
| 2017-02-01 00:00:00 |    2 |
| 2018-04-01 00:00:00 |    2 |
+---------------------+------+
2 rows in set (0.00 sec)

mysql> 
mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)

改进：

1.将具体的页数换成“下一页”按钮，假设每页显示20条记录，那么每次查询21条记录，如果第21条记录存在，就显示“下一页”按钮；

2.使用缓存；

7.5. 优化UNION查询

除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL

如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价很高；有All也会使用临时表

7.6. 静态查询分析

Percona Toolkit的pt-query-advisor可以解析查询日志、分析查询模式；

7.7. 使用用户自定义变量

用户自定义变量是一个用来存储内容的临时容器；

优化排名语句

避免重复查询刚刚更新的数据

统计更新和插入的数量

使用insert on duplicate key update,如果想知道更新了多少行，插入了多少行：

mysql> insert into project(id, name, c1) values(1,"qwq",1),(2, "qwe",2),(4, "qwer",3) ON duplicate key update c1 = values(c1) + (0*( @x:=@x+1));
Query OK, 1 row affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

确定取值的顺序

编写偷懒的UNION