如何高效快速地优化MySQL、SQL语句(附源码)
《如何高效快速地优化MySQL、SQL语句(附源码)》要点: 作者介绍 优化SQL,是DBA常见的工作之一.如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题.在日常的优化工作中,我发现有很多操作是在优化过程中必不可少的步骤.然而这些步骤重复性的执行,又会耗费DBA很多精力.于是萌发了自己编写小工具,提高优化效率的想法. 那选择何种语言来开发工具呢? 对于一名DBA来说,掌握一门语言配合自己的工作是非常必要的.相对于shell的简单、perl的飘逸,Python是一种严谨的高级语言.其具备上手快、语法简单、扩展丰富、跨平台等多种优点.很多人把它称为一种“胶水”语言,通过大量丰富的类库、模块,可以快速搭建出自己需要的工具. 于是乎,这个小工具就成了我学习Python的第一个作业,我把它称之为“MySQL语句优化辅助工具”.而且从此以后,我深深爱上了Python,并开发了很多数据库相关的小工具,以后有机会介绍给大家. 一、优化手段、步骤下面在介绍工具使用之前,首先说明下MySQL中语句优化常用的手段、方法及需要注意的问题.这也是大家在日常手工优化中,需要了解掌握的. 1、执行计划 — EXPLAIN命令执行计划是语句优化的主要切入点,通过执行计划的判读了解语句的执行过程.在执行计划生成方面,MySQL与Oracle明显不同,它不会缓存执行计划,每次都执行“硬解析”.查看执行计划的方法,就是使用EXPLAIN命令.
当在一个Select语句前使用关键字EXPLAIN时,MySQL会解释了即将如何运行该Select语句,它显示了表如何连接、连接的顺序等信息.
当使用EXTENDED关键字时,EXPLAIN产生附加信息,可以用SHOW WARNINGS浏览.该信息显示优化器限定SELECT语句中的表和列名,重写并且执行优化规则后SELECT语句是什么样子,并且还可能包括优化过程的其它注解.在MySQL5.0及更新的版本里都可以使用,在MySQL5.1里它有额外增加了一个过滤列(filtered).
显示的是查询要访问的数据分片——如果有分片的话.它只能在MySQL5.1及更新的版本里使用.
另一个格式显示执行计划.可以看到诸如表间关联方式等信息. 下面说明一下EXPLAIN输出的字段含义,并由此学习如何判断一个执行计划.
MySQL选定的执行计划中查询的序列号.如果语句里没有子查询等情况,那么整个输出里就只有一个SELECT,这样一来每一行在这个列上都会显示一个1.如果语句中使用了子查询、集合操作、临时表等情况,会给ID列带来很大的复杂性.如上例中,WHERE部分使用了子查询,其id=2的行表示一个关联子查询.
语句所使用的查询类型.是简单SELECT还是复杂SELECT(如果是后者,显示它属于哪一种复杂类型).常用有以下几种标记类型.
这一步所访问的数据库中表的名称或者SQL语句指定的一个别名表.这个值可能是表名、表的别名或者一个为查询产生的临时表的标识符,如派生表、子查询或集合.
表的访问方式.以下列出了各种不同类型的表连接,依次是从最好的到最差的.
该字段是指MySQL在搜索表记录时可能使用哪个索引.如果没有任何索引可以使用,就会显示为null.
查询优化器从possible_keys中所选择使用的索引.key字段显示了MySQL实际上要用的索引.当没有任何索引被用到的时候,这个字段的值就是NULL.
被选中使用索引的索引键长度.key_len字段显示了MySQL使用索引的长度.当key字段的值为NULL时,索引的长度就是NULL.
列出是通过常量,还是某个表的某个字段来过滤的.ref字段显示了哪些字段或者常量被用来和key配合从表中查询记录出来.
该字段显示了查询优化器通过系统收集的统计信息估算出来的结果集记录条数.
该字段显示了查询中MySQL的附加信息.
这个列式在MySQL5.1里新加进去的,当使用EXPLAIN EXTENDED时才会出现.它显示的是针对表里符合某个条件(WHERE子句或联接条件)的记录数的百分比所作的一个悲观估算. EXPLAIN除了可以显示执行计划外,还可以显示SQL改写.所谓SQL改写,是指MySQL在对SQL语句进行优化前,会基于一些原则进行语句的改写,以方便后面的优化器进行优化生成更优的执行计划.该功能是通过EXPLAIN EXTENDED+SHOW WARNINGS配合使用.下面通过示例说明一下. 从上面示例中,可看到原有语句中的IN子查询被改写成为表间关联的方式. 2、统计信息查看统计信息也是优化语句中必不可少的一步.通过统计信息可以快速了解对象的存储特征如何.下面说明主要的两类统计信息——表、索引.
3、索引统计信息 — SHOW INDEX
系统参数很多,下面介绍几个.
排序区大小.其大小直接影响排序使用的算法.如果系统中排序都比较大、内存充足且并发量不是很大的情况,可以适当增加此参数.这个参数是针对单个Thead的.
Join操作使用内存区域大小.只有当Join是ALL、index、range或index_merge时使用到Join Buffer.如果join语句较多,可以适当增大join_buffer_size.需要注意到是,这个值针对单个Thread.每个Thread都会自己创建独立的Buffer,而不是整个系统共享的Buffer,不要设置过大而造成系统内存不足.
如果内存内的临时表超过该值,MySQL自动将它转换为硬盘上的MyISAM表.如果执行许多高级GROUP BY查询并且有大量内存,则可以增加tmp_table_size的值.
读查询操作所能使用的缓冲区大小.这个参数是针对单个Thead的. 在MySQL中,还有一些参数是可以用来控制优化器行为的.
这个参数控制优化器在穷举执行计划时的限度.如果查询长时间处于”statistics”状态,可以考虑调低此参数.
默认是打开的,这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划.
这个变量包含了一些开启/关闭优化器特性的标志位. 示例 — 干预优化器行为(ICP特性) 默认情况下,ICP特性是开启的.查看一下优化器行为. 基于二级索引的过滤查询,使用了ICP特性,从Extra中的”Using index condition”可见.如果通过优化器开关,干预优化器行为,又会如何呢? 从Extra可见,ICP特性已经禁用. 5、系统状态(SHOW STATUS)MySQL中也内置了一些状态,通过这些状态变量也可反映出语句执行的一些情况,方便定位问题.手工执行的话,可以在执行语句的前后分别执行SHOW STATUS命令,查看状态的变化.当然,因状态变量很多,对比起来不太方便,后面我介绍的小工具,可以解决这个问题. 状态变量很多,这里介绍几个.
排序算法已经执行的合并的数量.如果这个变量值较大,应考虑增加sort_buffer_size系统变量的值.
在范围内执行的排序的数量.
已经排序的行数.
通过扫描表完成的排序的数量.
索引中第一条被读的次数.读取索引头的次数,如果这个值很高,说明全索引扫描很多.
根据键读一行的请求数.如果较高,说明查询和表的索引正确.
按照键顺序读下一行的请求数.如果你用范围约束或如果执行索引扫描来查询索引列,该值增加.
按照键顺序读前一行的请求数.
根据固定位置读一行的请求数.如果执行大量查询并需要对结果进行排序该值较高.则可能使用了大量需要MySQL扫描整个表的查询或连接没有正确使用键.
在数据文件中读下一行的请求数.如果正进行大量的表扫描,该值较高.通常说明表索引不正确或写入的查询没有利用索引. 6、SQL性能分析器(Query Profiler)MySQL的Query Profiler是一个使用非常方便的Query诊断分析工具,通过该工具可以获取一条Query在整个执行过程中多种资源的消耗情况,如CPU、IO、IPC、SWAP等,以及发生的PAGE FAULTS、CONTEXT SWITCHE等,同时还能得到该Query执行过程中的MySQL所调用的各个函数在源文件中的位置.
mysql> set profiling=1; 默认情况下profiling的值为0表示MySQL SQL Profiler处于OFF状态,开启SQL性能分析器后profiling的值为1.
使用”show profile”命令获取当前系统中保存的多个Query的profile的概要信息. +———-+————+———————–+ | Query_ID | Duration?? | Query?????????????????? | +———-+————+———————–+ |??????? 1 | 0.00039300 | select count(*) from t1 | +———-+————+———————–+
在获取概要信息之后,就可以根据概要信息的Query_ID来获取某个Query的执行过程中详细的profile信息. mysql> show profile cpu,block io for query 1; 二、工具说明前面谈到了多种手段,对于SQL语句的调优都有所帮助.通过下面这个小工具,可以自动调用命令将上面这些内容一次性推给DBA,大大加速优化的过程. 1、准备条件
2、调用方法-p? 指定配置文件名称 -s? 指定SQL语句 3、配置文件共分两节信息,分别是[database]描述数据库连接信息,[option]运行配置信息. db_user???? = testuser db_pwd????? = testpwd db_name???? = test sql_plan??? = ON //是否显示执行计划 obj_stat??? = ON //是否显示相关对象(表、索引)统计信息 ses_status? = ON //是否显示运行前后状态信息(激活后会真实执行SQL) sql_profile = ON?? //是否显示PROFILE跟踪信息(激活后会真实执行SQL) 4、输出说明包含运行数据库的地址信息及数据版本信息. 用户执行输入的SQL,这部分主要是为了后续对比SQL改写时使用.语句显示时使用了格式化. 脚本选择显示了部分与SQL性能相关的参数.这部分是写死在代码中的,如需扩展需要修改脚本. 下面是和优化器相关的一些参数,通过调整这些参数可以人为干预优化器行为. 就是调用explain extended的输出结果.如果结果过长,可能出现显示串行的问题(暂时未解决). 通过这里可判断优化器是否对SQL进行了某种优化(例如子查询的处理). 统计信息 在SQL语句中所有涉及到的表及其索引的统计信息都会在这里显示出来. 在会话级别对比了执行前后的状态(SHOW STATUS),并将出现变化的部分显示出来.需要注意的是,因为收集状态数据是采用SELECT方式,会造成个别指标的误差(例如Com_select). 调用SHOW PROFILE得到的详细信息. 根据PROFILE的资源消耗情况,显示不同阶段消耗对比情况(TOP N),直观显示”瓶颈”所在. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |