高性能mysql读书笔记4(查询性能优化1)

优化mysql,除了需要最优的库表设计、很好的索引设计以外,还需要编写合理的查询。如果查询写的比较烂,索引有可能根本就用不上。那样不管库表、索引设计的再好也没用。

下面会介绍如何写出高效的查询语句,以及明白高效与低效的原因。

为什么查询速度会慢

查询需要快速,主要是要响应时间快。

查询需要消耗的时间如下:网络、CPU计算、生成统计信息和执行计划、锁等待(互斥等待)等,尤其是向底层存储引擎检索数据的时候的这些调用。这些调用需要在内存操作、CPU操作和内存不足时导致的IO操作上消耗时间。

有些操作我们做了一些不必要的额外操作、甚至重复调用。所以我们需要优化这些查询,去优化和消除这些操作花费的时间。

慢查询基础:优化数据访问

查询性能低的最主要原因就是访问的数据过多。有时候我们可能并不总是需要那么多的数据,但是我们仍然去访问那么多的数据。

通常我们从两方面去分析会比较有效:

1、确认应用程序是否在检索大量超过需要的数据。通常是访问了太多的行,也有可能是访问了太多的列。
2、确认Mysql是否在分析大量超过需要分析的数据。

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

有些查询会请求超过需要的数据,然后这些多余的数据会被应用程序丢弃。这会给Mysql服务器带来额外的负担,并增加网络开销,另外也会消耗应用服务器的CPU和内存资源。

下面是一些常见的错误:

查询不需要的记录

使用ResultSet返回结果集的时候,我们可能只需要前10条,一些人会天真的以为应用程序是一个循环,拿一条数据,我们关闭结果集,就不会继续查询了。实际上Mysql不会这样,而会全部查出,然后存在内存里。这种情况我们最好使用LIMIT来截取数据。

多表关联时返回全部列

join时最好只取需要的列,减少子查询内容(有时更可以让子查询使用覆盖查询)。

总是取出全部的列

使用select *进行查询,当我们看到这样的语句的时候,总要用怀疑的眼光看看是否真的需要所有的列,这样会导致查询无法使用索引覆盖查询。不过很多人觉得这样可以简化开发,同时这样写其实有时候可以使用到查询缓存。

多次查询相同的数据

有时候一个接口需要几处相同的数据,我们尽可能的把一样的数据通过一次查询获取出来。不要一遍遍的查询,耗费性能。

Mysql是否在扫描额外的记录

在确定只返回了需要的记录之后,我们需要在确定下是否只扫描了需要的记录。衡量Mysql查询开销最简单的三个准则就是:

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

这三个指标都会记录到Mysql的慢日志中,我们主要查看是否扫描了过多的行这点最为重要。

响应时间

响应时间其实只是表面上的值。

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

一般比较常见的排队等待时间是消耗在了I/O及锁上。一般这个并不是很好分析时间是否在一个合理的范围,多数是凭经验去判断这样一个查询,它的响应时间是否在一个合理的值。

扫描的行数和返回的行数

理想情况下,扫描的行数和返回的行数应该是相同的,但是理想的情况往往很少,尤其是在做关联查询的时候。但是我们也应该尽可能的减少扫描的行数。

扫描行数和访问类型

Mysql有好几种方式可以查询一条数据,有些方式需要扫描好多行才能获得一条数据,有些方式不需要扫描就能获取数据。

在EXPLAIN语句返回的type列反映了返回类型。访问类型有很多种:从全表扫描到索引扫描、范围扫描、唯一索引扫描、常数引用等。这里列的是速度从慢到快,扫描的行数也少从多到少。

如果查询没有办法找到合适的访问类型,那么最好的办法就是为之建立一个合适的索引。

比如select * from xxx where id=1

这种查询,当id是主键索引的时候,我们使用EXPLAIN的时候发现,type是ref,其另一个参数值rows为1也说明了这个查询只需要访问一条数据。如果没有索引呢,这时候type会是ALL,同时rows一般会比较大,约等数据条数,另外这里的Extra参数会显示为Using where,标识Mysql是通过where条件来确定数据行的。

一般Mysql通过三种方式应用where条件:

在索引中使用where条件过滤不匹配的记录,这是在存储引擎层做的。
使用覆盖索引返回记录(Extra会提示为Using index),直接从索引过滤并返回需要的记录。
从数据表返回数据然后过滤数据(Extra会提示为Using where)。

所以说创建好的索引可以大大优化我们的查询性能,但也并不总是可以优化,有时候确实是只能访问很多数据而没有什么好的索引能够帮忙。

一般我们如果发现,一个查询扫描的行比返回的行多很多,我们通常可以采用下面的方法去解决:

使用索引覆盖扫描。
改变库表结构,比如使用单独的汇总表。
重写查询语句,让Mysql能够更好的优化这个查询(后面会说)。

本文原创于赵伊凡BLOG

©原创文章,转载请注明来源: 赵伊凡's Blog
©本文链接地址: 高性能mysql读书笔记4(查询性能优化1)

“高性能mysql读书笔记4(查询性能优化1)”的一个回复

发表评论

电子邮件地址不会被公开。 必填项已用*标注