一眼mysql之四:优化查询

查询性能优化

为什么查询会慢

• 一个查询从客户端,到服务器,然后进行解析,生成执行计划,执行,并返回结果给客户端,最重要就是执行,然而查询需要在不同的地方花费时间,包括网络,CPU计算,生成统计信息和执行计划,锁等待等操作,根据存储引擎不同,可能还会产生大量的上下文切换和系统调用,所以,了解这些信息,才能知道如何优化查询

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

• 是否请求了不需要的数据
• 查询了不必要的数据,比如前端需要10条数据,你查询了20条,可以用limit来截取需要的数据
• 多表关联时返回全部列:只需要返回有需求的列即可,
• 总是取出所有的列:避免select * 的写法
• 重复查询相同的数据:重复执行相同的操作,返回相同的结果,可以用缓存.

是否在扫描额外的记录

• 响应时间:响应时间是由服务时间和排队时间组成的,
• 服务时间是指数据处理这个查询真正花了多长时间.
• 排队时间是指服务器因等待其他资源而没有真正执行查询的时间(io,锁等待等)
• 扫描行数和返回的行数
  • 能指出这个查询是否效果高,理想状态是扫描行数和返回行数是一样的,基本是不可能,扫描行数正常比返回行数多多了
• 扫描的行数和访问类型
  • 不同的访问类型在扫描行数的抉择上是不同的,使用explain执行一个查询,在type上面可以看到,分别为全表扫描,索引扫描,范围扫描,唯一索引查询,常数引用等
• 执行效率高低为 system > const > eq_ref > ref >range>index>all
  • mysql正常使用三种方式来应用where条件,从好到坏依次为:
    • 在索引中使用where条件来过滤不匹配的记录(就是索引都用到查询上),在引擎层完成的
    • 使用索引覆盖扫描(Extra列中出现Using index),这是在mysql服务器层完成的
    • 从数据表返回数据,再过滤(Extra出现Using Where),等于是全表扫描的意思
    • 尝试优化方式
      • 使用索引覆盖扫描,吧所需要用到的列都放到索引汇总,无须回表即可查询(覆盖索引)
      • 改变库表结构,增加单独汇总表,而不是单独查询明细表汇总
      • 重写复杂的查询,能让优化器更优化的方式执行这个查询

重构查询的方式

• 不要执着于当前的sql,而是用另外一种方式或者写法能够返回一样的结果(性能更好些).
• 一个复杂查询还是多个简单查询
  • 如果是一个查询能胜任的情况下,就不需要改造,但是知道的是把简单查询拆分出来是可以提升的方案
• 切分查询
  • 把功能相同的大操作,切分成几个比较小的操作,比如说一下子删除10000条数据,会导致锁表锁很久,但是切分成几个相同的操作,对于事务型引擎来说,小事务大多更高效,删除间隔也可以长一些
• 分解关联查询(把join表,改成两三个where的单表操作)
  • 让缓存的效率更高,对于多个小查询来说可能命中缓存的概率更高
  • 查询分解后,执行单个查询可以减少锁的竞争
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展
  • 查询本身效率可能会提升效率
  • 可以减少冗余记录的查询,如果在数据库中做关联查询,可能重复访问一部分数据,重构之后还能减少网络和内存的消耗
  • 在应用中实现了哈希关联,而不是使用mysql的嵌套循环关联.

查询执行的基础

• 客户端发送一条查询给服务器

• 服务器先检查查询缓存,如果命中了缓存,立刻返回,否则进入下一阶段

• 服务器进行sql解析,预处理,再由优化器生产对应的执行计划

• mysql根据优化器生成的执行计划,调用存储引擎的api来执行查询

• 将结果返回给客户端

• mysql客户端/服务器通信协议

  • mysql客户端和服务器之间的通信协议是半双工,在任何一个时刻都只会有一个客户端或者服务端在往对方发送数据,缺点是没发进行流量控制,所以当查询的语句很长的时候,参数max_allowed_packet(查询太大,服务端会拒绝接受更多数据并抛出相应错误),一旦客户端发送了请求,就只能被动的等待了,
  • 但是服务器响应给用户的数据通常很多,当服务器开始响应客户端请求时,客户端必须完整的接受整个返回结果,而不能简单的只取前面几条结果然后让服务器停止发送数据,
  • (存储引擎到服务器端)多数连接mysql的库函数都可以获得全部结果集并缓存到内存中,还可以逐行获取需要的数据,默认一般是获取全部结果集并缓存到内存中,需要等到全部数据都发送给客户端才能释放这条查询所占用的资源,所以接受全部数据并缓存通常能减少服务器的压力,让查询早点结束,
  • (服务器端到客户端)客户端从mysql获取数据,实际上都是从库函数的缓存获取数据的,如果是返回一个很大的结果集是,如果能够尽快开始处理这些结果集时,就能大大减少内存的消耗,这种情况可以不使用缓存记录而是直接处理,但是这样做的缺点是服务器需要查询完成后才能释放资源,所以在和客户端交互时,服务器的资源都是被这个查询所占用的(之前是要先查 然后放缓存就可以释放资源了,现在需要发完这些数据才能释放资源)/(指定mysql_use_result=1就是不使用缓存的方式)

• 查询状态

  • 使用SHOW FULL PROCESSLIST命令(该命令中的command列表示当前的状态)

  • Sleep:线程等待客户端发送新的请求

  • Query:正在执行查询或者正在将结果发送给客户端

  • Locked:在MySQL服务器层,该线程正在等待表锁,InnoDB的行锁不会体现在线程中,

  • Analyzing adn statistics:线程正在收集存储引擎的统计信息,并生成查询的执行计划

  • Copying to tmp table(on disk):线程正在执行查询,并将结果集都复制到一个临时表中,一般是做GROUP BY操作要么就是文件排序操作或者是UNION操作,如果有on disk就是表示MySQL正在将一个内存临时表放在磁盘上

  • Sorting result:线程正在对结果集进行排序

  • Sending data:线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据

  • 查询缓存
    -在解析查询语句之前,如果查询缓存是打开的,会先检查这个查询是否命中缓存,检查是通过一个对大小写敏感的哈希查询实现的,查询和缓存中的查询及时只有一个字节不同,也不会匹配缓存结果,(但是注释不影响)

    • 如果成功命中,在返回结果之前mysql会检查一次用户权限,如果权限没问题,mysql会跳过所有阶段,直接从缓存拿到结果返回给客户端,没有解析,没有生成执行计划,不会被执行

• 查询优化处理

  • 语法解析器和预处理:通过关键字将sql语句进行解析,并生成一颗对应的"解析树",mysql解析器将使用mysql语法规则验证和解析查询(是否使用错误的关键字或是使用关键字的顺序是否正确),预处理器则根据一些mysql规则进一步检查解析树是否合法,(数据表或者数据列是否存在,解析名字和别名是否有歧义)
  • 查询优化器:一条查询可以有多种执行方式,优化器选择成本最小的一个,比如使用SHOW STATUS LIKE 'Last_query_cost’能够得知当前查询的成本,value里面的值就是优化器认为大概需要做多少个数据页的随机查询才能完成这条sql,优化器在考虑的时候不考虑任何层面的缓存.假设读取任何数据都需要一次磁盘io,
  • 可能导致mysql优化器选择错误的执行计划:
    • 1.统计信息不准确,依赖存储引擎提供的统计信息来评估成本,但是有的引擎提供的信息不准确,有的准确:比如InnoDB在mvcc架构中,不能维护一个数据表的行数的精确统计信息,
    • 2.执行计划的成本估算不等同于实际执行的成本,所以即使统计信息精确,优化器给出的执行计划可能不是最优的,
    • 3:最优的想法不一致,我们想的是如何查的最快,而mysql想的是如何访问成本最低.
    • 4:mysql从不考虑其他并发致性的查询，可能会影响当前的查询速度
    • 5:mysql并不是任何时候都是基于成本的优化,比如存在全文搜索的MATCH()字句,就会在存在全文索引的时候使用全文索引,可能使用其他索引或条件能更快,但是还是只会使用这个对应的索引
  • 6:不会考虑不受控制的操作的成本,比如执行存储过程或在自定义函数的成本
  • 7:优化器无法估算所有可能的执行计划,导致错过最优方案
    优化器的分类:
    • 静态优化:不依赖特别的数值，比如where条件后面跟的常数,在第一次完成后就一直有效,不同参数相同结果,
  • 动态优化,和查询的上下文有关,可能和很多其他因素有关,每次查询都要重新评估,运行时优化

能够处理的优化类型

• 1:重新定义关联表的顺序：不是都按照在查询中指定的顺序来进行的.
• 2:将外连接转化成内连接:将outer JOIN语句转化成内连接的形式
• 3:使用等价变换规则:可以合并和减少一些比较，还能移除一些恒等式和恒不等式。（5 =5 and a》5）改成a>5
• 优化count(), min(),max(),索引和列是否可为空通常可以优化这类查询
• 预估并转化为常数表达式:
• 覆盖索引扫描:
• 子查询优化:
• 提前终止查询:发现已经能够满足查询需求的时候,就立刻终止查询,在查询后面使用limit 1.
• 等值传播:如果俩个列的值通过等式关联,mysql就能够吧其中一个列的where条件传递到另外一个列上,(join的情况)
• 列表in()的比较:mysql将in()列表中的数据进行排序,然后进行二分法来确定列表的值是否满足要求,所以in中大量取值的时候,mysql处理速度会更快.

如何执行关联查询:

• mysql对任何关联都执行嵌套循环关联操作,先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,直到找到所有表中循环取出单条数据,直到全部匹配为止,然后把需要的列返回给上一个表,进行取值和匹配.

执行计划
mysql不会生成查询字节码来执行查询.而是生成一颗指令树,然后通过存储引擎执行完成这颗指令树并返回结果,

关联查询优化器:
这个优化器决定了多个表关联的顺序,通过多表关联的时候,可能有多种不同的顺序来获得相同的结果,尽量让第一个表查询出来的结果集最少,关联的表不能太多,表一多,关联的方案就多,然后成本计算就无法精准,选择上也不能更好的发挥作用,
排序优化:
尽量避免拍讯或者进行对大量数据的排序,如果无法使用索引生成的排序结果时,mysql需要自己进行排序,如果数据量小就在内存中进行,数据量大就会使用磁盘,如果排序的数据量 小于"排序缓冲区",就会使用内存进行排序,如果内存不够,就会将数据分块,对每个独立的块使用快排,然后将各个快的排序结果放在磁盘上,然后进行合并,最后返回排序结果.

排序算法:

• 1:两次传输排序:读取行指针和需要排序的字段,对其进行排序,在根据排序结果读取所需要的数据行,缺点是会生产大量的随机io,数据传输成本很高,但是优势是,在排序的时候存储尽可能少的数据,让排序缓冲区尽可能容纳多的行来排序
• 2:单次排序:先读取查询所需要的所有列,在根据给定列进行排序,最后返回排序结果.缺点是如果返回的数据量大,需要占据很多空间,而有很多列是对排序没有作用的.
• 3:查询需要所有列的总长度不超过max_length_for_sort_Data时,mysql使用单次传输排序,超过就是使用两次传输

查询执行引擎:
查询致性阶段不是很复杂:mysql简单的根据执行计划来逐步执行,在根据执行计划逐步执行的过程中,有大量的操作需要通过调用存储引擎实现的接口来完成,被称为handler api.查询中每个表都是由一个handler的实例来表示.

返回结果给客户端
如果查询不需要返回结果给客户端,仍然会返回这个查询的一些信息,如影响的行数等
如果查询可以被缓存,在这个阶段也会将结果存放到查询缓存中,
mysql将结果集返回客户端是一个增量,逐步返回的过程,也就是说在服务器处理完最后一个关联表,开始生成第一条数据的时候,就开始逐步返回结果集了,好处是服务器端不需要存储太多的结果,也就不会因为要返回太多结果而耗费太多内存,这样的处理也可以让mysql客户端第一时间获得返回的结果

mysql查询优化器的局限性

• 关联子查询:当我们写出select * from table1 where cloumn in(select cloumn from table2 where cloumn2 = 1)的时候 ,mysql会将外层表压到子查询中,往往这时候我们就使用连表查询即可,
  select a.* from table1 a inner join table2 b on a.cloumn = b.cloumn where b.cloumn2 = 1这种类似的手法.

• union的限制
  如果两条数据都需要取前面的20条,那就要在各自的语句中加上limit,否则会把两个语句满足条件的都查出来在来取前20条数据,而且还有加上全局的limit和order by

• 等值传递
  在等值传递的过程中,如果包含了大的in列表,则还是会吧这个当做条件在另外一个表里执行一次,暂时没办法解决

• 并行关联
  mysql无法使用多核的功能来执行查询

• 哈希关联
  mysql不支持哈希关联

• 松散索引关联(跳跃式的索引扫描)
  mysql不支持松散索引关联,无法按照不连续的方式扫描一个索引,还是需要定义一个起点和终点,

• 最大值和最小值优化
  如果是最大值和最小值,在where 条件不是索引的情况下,mysql会进行全表扫描,然后方法是使用limit然后移除min()来优化,也就是 select 列 from 表 use index(主键)where 条件 limit 1

• 在同一个表上查询和更新
  无法在同一个表中同时进行查询和更新,但是可以用关联表来绕过这个限制,

查询优化器的提示

如果对优化器的结果不满意,可以自己加参数来提示优化器自己的意图

• HIGH_PRIORITY和LOW_PRIORITY,提示优先级的高低,只对使用表锁的存储引擎有效,不要在InnoDB中使用,因为HIGG_PRIORITY就是把这个语句放再表的队列最前面,LOW_PRIORITY则是如果表还有访问的,就先等待
• DELAYED:对INSERT 和REPLACE有效,如果加了,mysql会将语句立即返回给客户端,并将插入的行数放入缓存区,在表空闲的时候批量写入数据,对于大批量的写入但是客户端不想等待的时候可以使用,
• STRAIGHT_JOIN:放在SELECT语句的SELECT关键字之后,也可以放在任何两个关联表的名字之间,第一个用法是让查询中所有的表按照语句中出现的顺序进行关联,第二个则是固定其前后两个表的关联顺序
• SQL_SMALL_RESULT和SQL_BIG_RESULT:只对SELECT语句有效,告诉优化器对GROUP BY或者DISTINCT查询如果使用临时表和排序,SQL_SMALL_RESULT告诉优化器结果集会很小,可以将结果集放在内存中的索引临时表中,以避免排序操作,其他则建议使用磁盘临时表做排序操作
  -SQL_BUFFER_RESULT:提示优化器江传结果放入在一个临时表,然后尽可能快的释放表锁,缺点是服务器需要更多内存
• SQL_CACHE 和SQL_NO_CACHE:提示结果集是否应该缓存在查询缓存汇总
• SQL_CALC_FOUND_ROWS:会让mysql返回的结果集包含更多的信息,
• FOR UPDATE和LOCK IN SHARE MODE:控制select语句的锁机制,只对实现了行级锁的存储引擎有效,尽可能的避免使用这两个提示
• USE INDEX,IGNORE INDEX,FORCE INDEX:告诉优化器或者不使用那些索引来查询记录

有些参数可以控制优化器的行为

• optimizer_search_Depth:控制优化器在穷举执行计划时的限度,如果查询长时间处于"Statistics"状态.可以考虑调低
• optimizer_prune_level:默认打开,让优化器根据需要扫描的行数来决定是否跳过某些执行计划
• optiomizer_switch:包含开启/关闭优化器特性的标志位.

优化特定类型的查询

优化count()查询

• count()统计了不为null的列的行数数量,如果是count(*)则是统计所有的行数,忽略了null.
• 在MyISAM中不含where条件的count()才是最快的,如果加了条件,就和其他引擎没有太大的区别
• 优化:
  -查询互斥条件中相互的数量可以使用sum也可以使用count
  select sum(IF(color = ‘blue’ , 1, 0)) as blue ,sum(IF(color = ‘red’ , 1,0)) as red from items
  selec count(color = ‘blue’ or null) as blue ,count(color = ‘red’ or null) as red from items
• 可以使用近似值
  在某些使用场景中,不需要精确的值,可以直接使用explain来获取数量,也可以尽量去掉一些约束条件,这样查询速度大大提高,还能获取对应的值
• 加上汇总表,要查询数量的时候,直接查询汇总表即可

优化关联查询

• 确保on或者using子句的列上有索引,在创建索引的时候要考虑关联的顺序,基本上只需要在关联顺序中的第二个表的相应列上创建索引
• 确保group by和order by的表达式只涉及一个表的列,这样才可能使用索引来优化这个过程
• 升级mysql时,需要注意,关联语法,运算符优先级等其他可能会发生变化的地方

优化子查询

• 尽量使用关联查询代替,

优化GROUP BY 和 DISTINCT

• 可以使用索引优化
• 当索引无法使用的时候,GROUP BY要么使用临时表,要么是文件排序(磁盘),使用SQL_BIG_RESULT和SQL_SMALL_RESULT来提示.
• 分组的列最好是具有标识性,
• 尽可能把分组聚合的操作放在应用程序里面

优化limit分页

• 可以使用延迟关联的方法,先把分页的主键查出来,然后再查数据行
• 如果知道边界值且有索引,则可以写成select * from table where limit_cloumn between a and b order by limit_cloumn
• 也可以先计算出边界值,select * from table where limit_cloumn < 边界值 order by limit_cloumn desc limit 20;永远都是第一页,所以性能都很好
• 还能使用预先计算的汇总表,或者关联一个冗余表,

优化SQL_CALC_FOUNND_ROWS(对于页面总条数的优化)

• 在limit中使用SQL_CALC_fOUND_ROWS,获取去掉limint以后满足条件的行数,作为分页的总数,但是mysql只有在扫描了所有满足条件的行之后,才会知道行数,所以,加了这个提示之后,无论是否需要,都会扫描所有满足条件的行,再抛弃不需要的行,而不是在满足limit之后就终止扫描,尽量少使用
• 如果单页是20,则获取21条,如果有21条,证明还有下一页,否则就没有,减少计算总行数
• 获取并缓存较多的数据,也就是一次性缓存多页数据,然后如果总数据小于已缓存的,则显示所有的数据,如果大于所有的数据,则显示一条,已有xxx条,点击访问更多
• 也可以使用explain的rows列的值来作为结果集.需要精准结果时,在使用count(*)

优化 union查询

• 除非需要服务器消除重复的行,则一定使用union all,否则mysql会给临时表加上DISTINCT选项,导致整个临时表做唯一性检查,代价高,

使用用户自定义变量

使用SET和select语句来定义,

但是也有一些限制

• 无法使用查询缓存
• 不能再使用常量或标识符的地方使用自定义变量,如表名,列名,和limit中
• 用户自定义变量的生命周期在一个连接中有效,无法做连接间的通信
• 如果使用连接池或者持久化连接,可能让毫无关系的代码作交互…
• 大小写敏感
• 不能显式的声明自定义变量的类型,如 0 和0.0
• mysql优化器在某些场景下会将这些变量优化掉,导致代码运行结果不准确
• 赋值的顺序和赋值的时间不总是固定的,
• 赋值符号:=优先级很低,赋值表达式应该使用明确的括号
• 使用未定义变量不会产生任何语法错误.