查询性能优化-01

1.为什么查询会慢

查询需要在不同的地方花费时间，包括网络，CPU计算，生成统计信息和执行计划，锁等待等操作，尤其是底层存储引擎检索数据，这些检索需要内存操作、CPU 操作和内存不足时的IO操作。

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

最基本的原因-访问数据太多，低效查询，分析步骤：

• 确认应用程序是否检索大量不必要的的数据。太多行或太多列。
• 确认MySQL服务器层是否在分析大量不必要的数据行。

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

• 查询不需要的记录

误以为mysql值返回需要的数据，其实返回的可能是全部的数据，使用 SELECT 查询所有结果，获取前面的N行后关闭，实际上MySQL已经查询出全部结果。简单最有效的方法在后边加个limit。

• 多表关联时取出全部列

正确的方式是取出需要的列

获取并缓存所有列的查询，相比独立获取某些列的数据可能更有效

• 重复查询相同的数据

利用缓存，减少重复查询，例如用户头像

2.2MySQL 是否扫描额外的记录

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

• 响应时间
• 扫描的行数
• 返回的行数

响应时间 

响应时间= 服务时间 + 排队时间，服务时间：查询这个数据需要的时间；排队时间：为了等待某些资源花费的时间——可能是IO，也可能是锁等等。

扫描的行数和返回的行数

对查询速度的影响很大。但并不是所有的行的访问代价都是相同的，较短的行的访问速度快，内存中的行比磁盘中的行访问速度快。扫描行与返回行的比值通常很小，一般在1:1和10:1之间。

扫描行数和访问类型

MySQL中有好几种访问方式可以返回一行结果，有些要扫描很多行，有些甚至无需扫描行。

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

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

如果发现查询需要扫描大量的数据，但只返回很少的行，可以尝试以下技巧：

• 使用索引覆盖扫描，把需要的列都放到索引中。
• 改变表结构，例如使用单独的汇总表。
• 重写这个复杂的查询，让优化器能以更优化的方式执行。

3.重构查询的方式

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

传统的实现中强调数据库完成尽可能多的工作，其逻辑在于认为网络通信，总是认为查询解析和优化是一件代价很高的事情。但这对于MySQL不适用，MySQL在设计上连接和断开链接都很轻量，返回小结果集很高效。

在其他条件相同的时候，使用尽可能少的查询更有利。但有的时候，将一个大查询分解成为多个小查询是必要的。

3.2切分查询

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

例子：

-- 大查询
DELETE FROM message WHERE created < DATA_SUB(NOW(), INTERBAL 3 MONTH);

-- 小查询 一次删除一万条
rows_affected = 0
do {
    rows_affected = do_query(
        "DELETE FROM message WHERE created < DATA_SUB(NOW(), INTERBAL 3 MONTH) 
        LIMIT 10000"
    ) 
} while rows_affected > 0

一次删除1w条数据是一个高效且对服务器影响较小的做法，每次删除数据之后，都要暂停一下在删除，可以减少服务器的压力，还可以减少删除时锁的持有时间。

3.3分解关联查询

分解关联查询将关联操作转移到应用程序中的优势：

• 让缓存的效率更高。很多应用程序会缓存单表查询的结果进行复用。另外，对于 MySQL 的查询缓存（query cacheA）来说，关联中的表发生了变化就无法使用缓存了，拆分后如果某个表很少改变，那就可以重复利用查询缓存。
• 分解后，单个查询减少锁的竞争。
• 在应用层关联更容易对数据库进行拆分，更容易提高性能和扩展性
• 查询本身效率可能会提升。拆分查询有时可以指定查询条件中的查询顺序（使用 IN()）
• 减少冗余记录的查询
• 拆分后相当于在应用中实现了哈希关联，而不是使用嵌套循环关联