优化性能需要查询优化、索引优化、库表结构优化这三辆马车齐头并进。这篇文章主要围绕查询优化,要对查询进行优化首先需要了解查询是如何被执行的,这一点在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则可以进一步优化,因为索引只能支持最多一个范围查找;把基于估算筛选出的少量数据再利用精算进行过滤。