优化性能需要查询优化、索引优化、库表结构优化这三辆马车齐头并进。这篇文章主要围绕查询优化，要对查询进行优化首先需要了解查询是如何被执行的，这一点在Mysql的逻辑架构一文中已经阐述了。

一、查询的生命周期

性能是完成某任务的时间度量，也就是响应时间；优化查询性能就是提高查询的响应速度。响应时间包括执行时间和等待时间，等待时间又包括等待IO和等待锁的时间。那么查询的时间都花在哪儿了呢？我们可以通过show full processlist查看线程状态进而查看查询的生命周期：

1）Sleep：等待客户端发送请求；2）Query：正在执行查询，或者正在返回结果给客户端；3）Locked：在服务器层等待表锁，等待InnoDB的行锁并不会在此显示；4）analysing & statistices：正在收集存储引擎统计信息，生成执行计划；5）Copying to temp table(on disk)：正在执行查询并把结果复制到临时表，在group by、文件排序和union等操作出现；6）sorting result：正在排序；7）Sending data：在多个状态间传送数据，或者正在生成结果集，或者向客户端返回数据；

二、查询优化的思路

优化我们的查询语句需要考虑以下几个点：

1）客户端是否向数据库请求了不需要的数据：1. 可使用limit减少返回的行；2. 可通过避免使用select * 减少返回的列，但有时select * 配合缓存总体性能也不错；3. 通过缓存避免重复查询相同的数据。

2）通过日志中记录的扫描行数和返回行数，查看服务端是否扫描了不需要的数据。理想情况下，扫描行数等于返回行数；但联表查询时扫码多行才能连结为一行返回，扫描行数会明显大于返回行数。Where条件对应的3种处理方式，扫描行数从少到多依次是：1. 索引作为查询条件，在存储引擎层完成；2. 索引覆盖扫描(using index)，服务层直接从索引中过滤掉不需要的数据；3. 服务层过滤不满足条件的记录(using where)。

3）单行访问速度慢，最好读取的数据块包含尽可能多需要的数据行；小查询性能不是问题：mysql采用线程缓存，使得建立/断开连接更高效；现代网速也很给力。

三、重构查询的方式

1）切分查询：删除过期数据时通常采用切分删除方式，例如每月执行一次任务，删除最近一个月过期的数据，而不是每年执行一次，删除该年过期的数据。区别在于锁定更少数据，从而减轻对其他线程的阻塞。

2）分解关联查询：词条表tag、微博表post、关联表tag_post，查找‘mysql’词条对应的微博；可以用联表查询，也可以拆分成3条查询：首先查询‘mysql’对应的tagId，然后查询tagid对应的post ids，最后找到post。拆分的好处在于：1. 缓存效率更高；2. 减少锁竞争；3. 更容易对数据库拆分，更容易做到高性能和可扩展，例如tag和post可以存储在不同数据库服务器；4. 查询本身效率的提升，如用in代替关联查询；5. 减少扫描行数。

四、Mysql对低效查询的处理方式

典型的低效查询如联表查询，子查询，聚合查询等。

1）Union查询：先将单个查询结果放到临时表，再从临时表读取数据做union。建议把where、limit、order by等子句下沉到union的子查询，以便优化器利用这些子句进行优化。

2）from中的嵌套子查询：先执行子查询并把结果放在临时表；

3）联表查询的排序：建议order by中所有的列来自于同一张表；如果order by中所有列来自第一个表，则查询第一张表时就进行排序，执行计划显示using filesort；否则mysql把查询结果放到临时表，在关联查询结束后进行排序，执行计划显示using temporary using filesort。

4）group by和distinct：mysql对两种查询用相同的方式处理。最有效的优化方式是索引，如选用主键作为group by的列(分组列)；不使用索引则使用临时表或文件排序来做分组。避免在select中使用非分组列。使用group by时，自动按照分组字段进行排序，可以在group by中指定asc/desc；也可以指定order by null告诉mysql不要排序。

5）自定义变量：通过set @var := (select * from tbl)设置自定义变量，并通过select * from tbl where col in @var等方式引用变量。变量采用动态类型，无法预设类型，只能通过赋值决定类型；使用未定义变量会在语法检查阶段报错。变量在一个连接内有效；使用变量的查询不能用缓存；不能在使用表名/列名的位置使用变量。

6）Left outer join：左表为准，左表行对应右表有值，则返回(left, right)，否则返回(left, null)；right join会被mysql等价改写为left join。

7）Count聚合函数：count(列名)用于统计某个列值的数量，count(*)用于统计行数

8）文件排序优化：当查询列和排序列的总大小超过maxLengthForSortData、或涉及blob字段排序时，时采用two pass算法。否则，仍然使用single pass算法为所有参与排序的行创建固定大小的缓冲。必须排序blob/text时，推荐用前缀排序。

五、查询优化案例

5.1 用mysql构建一个队列表

需求：用数据表维护任务对列，包含未处理任务、已处理任务和正在处理任务；调用方在数据表中查找未处理记录，更新为正在处理，处理完成后更新为已处理。

问题和解决方案：1）伴随队列表越来越大，找到未处理记录的速度变慢；解决方法是把已处理记录归档到历史表，从而始终保持对列列表很小；2）终极解决方案是把对列从数据库迁移出来，用redis或者rabbitMQ实现

5.2 计算两点之间到距离

需求：地图应用中需要查找某个点附近所有的停车场；或者社交网站中查找周边用户。

问题和解决方案：查询条件是一个复杂的计算方式，不仅无法使用索引，还会消耗CPU时间。把精确计算公式转化为估算，使用lat between valueA and valueB的方式使用索引；如果能把between优化为in则可以进一步优化，因为索引只能支持最多一个范围查找；把基于估算筛选出的少量数据再利用精算进行过滤。