前言应用系统性能测试过程中性能优化是绕不开的话题对测试人员而言性能优化的第一站就是SQL语句的优化与分析。因此本文主要以MySQL数据库为例介绍常见的慢查询SQL语句执行效率分析与优化方法和简单示例为致力于应用系统性能优化的从业人员提供一定参考和借鉴。1 慢查询定位(1)慢查询慢查询SQL语句即在数据库执行耗时超过一定阈值的SQL语句常见阈值为500~2000ms可根据业务需求适当调整。如存在大量慢查询语句会直接导致系统响应时间变长降低用户体验感因此慢查询的定位与优化是SQL语句优化的主要内容。慢查询调优的第一步是准确定位慢查询语句需要数据库开启慢查询日志记录功能然后借助工具对日志进行分析实现慢查询SQL语句的准确定位。-- 慢查询开启状态、日志位置 show variables like slow_query%; -- 慢查询命中时长 show variables like long_query_time;(2)mysqldumpslow慢查询日志分析MySql数据库的慢查询SQL语句可以借助mysqldumpslow工具进行分析其他类型数据库可根据官方提供的技术文档采用对应的工具开展慢查询日志分析。慢查询日志分析的常用参数说明如下例用时最多的10条慢SQL(后半部分为slow_query_log_file地址)sql mysqldumpslow -s t -t 10 -g select /data/mysql/data/dcbi-3306/log/slow.log2 SQL语句执行分析(1)SQL执行顺序分析SQL语句执行效率的第一步需要了解一条SQL语句的执行顺序从而为语句优化提供依据。一般而言执行顺序为from- where- group by- 聚合函数(sum、avg)- having- 计算公式- select字段- order by- limit(2)explain关键字SQL语句执行分析可通过在SQL语句前添加“explain”关键字后在数据库编辑器中执行查看语句具体的执行情况。explain select * from table_name where columns_1 value_1 and columns_2 vales_2(3)SQL执行计划返回结果说明返回结果各列说明可按需查询相关资料重点关注【type】、【ref】、【extra】反映查询效率的3列以【type】为主即可。(4)SQL执行效率分析explain语句根据【type】列的值判断SQL执行效率效率从低到高依次为all index range index_merge ref eq_ref const system。一般而言【type】列值至少要在rangesystem之间执行效率才能达到较高水平。(5)SQL语句执行效率对比在开展SQL优化的过程中对比两条SQL语句执行时间验证优化效果时需要明确语句执行过程中数据的存取方式。根据数据库数据查询机制若数据库内存中已存在目标数据则直接从内存中获取数据不再是从数据库物理磁盘获取数据。这种情况下当优化前SQL语句执行后目标数据已暂存于数据库内存中时执行优化后SQL语句时则直接从数据库内存中获取数据导致该语句执行时间失真。为避免验证优化效果时出现上述SQL语句执行时间失真的情况需在select关键字后添加SQL_NO_CACHE关键字声明通过数据库引擎重新查询数据。SQL_NO_CACHE指的是查询结果在内存展示后直接从内存中释放并非不从内存中读取数据。因此若在执行SQL_NO_CACHE之前已经查询过目标数据导致目标数据已经在数据库内存中则该语句失效。用法示例如下select SQL_NO_CACHE columns from table_name where column_1 vales_1 and columns_2 values_2;需要说明的是SQL_CACHE、SQL_NO_CACHE命令在MySQL 5.7.20开始废弃MySQL 8.0后彻底移除普通select命令即直接从数据库中获取数据无需从数据库内存中获取数据。其他类型数据库相关机制按需查阅对应官方技术文档。3.常见SQL优化方法(1)索引覆盖SQL优化最常见的方法就是实现索引覆盖即select后的查询列、where后的查询条件均包含索引通过查询条件即可获得查询列数据。常见索引覆盖场景1使用主键索引select后的查询列不包含主键则无法实现索引覆盖2使用非主键索引select后的查询列包含非主键索引可实现索引覆盖3使用非主键索引select后的查询列包含主键索引可实现索引覆盖。(2)最左匹配原则MySQL建立联合索引(多列索引)时会遵守最左前缀匹配原则即最左优先在检索数据时从联合索引的最左边开始匹配。具体是因为索引最左列全局有序、其余列局部有序但全局无序因此根据索引查询必须满足最左匹配原则否则索引失效。基于最左匹配原则在创建索引时根据业务需求where中使用最频繁的列放在最左边最左匹配原则遇到范围查询、、between时会停止匹配即范围查询后的索引失效示例1某张表索引按序为a,b,c如筛选条件为where a 1 and b 2索引a、b均被使用到如筛选条件为where b 2则因未使用a 1不满足最左匹配索引失效where a 1 and b 1 and c 3因b为范围查询b、c均索引失效。示例2某张表索引按序为(a, b, c)其中b字段在表table的所有值均为常量02003同一个查询有4种不同的SQL语句写法-- SQL语句1select * from table where c 62412001090472816354-- SQL语句2select * from table where b 02003 and c 62412001090472816354-- SQL语句3select * from table where a 344589 and b 02003 and c 62412001090472816354-- SQL语句4select * from table where a 344589 and c 62412001090472816354上述SQL语句执行3次平均耗时分别为结论SQL语句1、SQL语句2因不满足最左匹配原则导致索引失效查询耗时较长SQL语句3、SQL语句4使用到索引查询速度较快但SQL语句4因缺失索引字段b相对SQL语句3耗时较长可见索引字段b即便在整张表中均为常量列入where后的筛选条件依然能提高查询效率。(3)索引条件下推目的检索数据时采用组合索引且第一索引非等值索引时尽量利用其他索引条件精准选择目标数据减少数据多次回表判断是否符合目标数据的次数以解决慢查询导致的性能问题。方法服务层(Server层)把查询工作下推到数据库引擎(InnoDB)去处理。优势减少回表查询次数提高查询效率降低数据库IO资源消耗。判断explain SQL输出【extra】列结果为using index condition。下面详细对比使用下推和未使用下推时的数据库底层逻辑进一步说明索引条件下推的优势。1)使用下推第一索引非等值索引的SQL语句使用索引条件时应用层将查询请求发送至引擎层引擎层根据索引条件剔除不满足其他索引的数据将剩余满足其他索引条件的数据返回应用层尽量少回表地检索到对应记录。使用条件下推时引擎层可直接剔除不满足非第一索引中各列的数据。2)未使用下推SQL语句存在多个索引时数据库Server层将查询请求发送至引擎层处理引擎层按索引顺序返回符合请求的数据到应用层。数据库Server层完成筛选后再按序发送下一索引检索条件多次重复直到满足所有查询条件。如此多次循环导致数据库IO资源消耗较高。(4)小表驱动大表根据表的结果集大小选择驱动表一般使用小表作为驱动表例如某系统存在表table_a、表table_b数据量分别为100万、10万则查询两表关联数据时将表table_b作为子表select * from table_a where column_1 and column_2 in (select column_2 from table_b where ...)若必须使用大表table_a作为子表则使用exists关键字。select * from table_a where exists (select column_2 from table_b where ...)(5)in代替or若where后查询条件中某字段存在多个值则用in代替or。select * from ar_ar_41 where ID_SHARD 10800000 and (NUM_SEQ_AR 11090141150000002 or NUM_SEQ_AR 11090141450000005); select * from ar_ar_41 where ID_SHARD 10800000 and NUM_SEQ_AR in (11090141150000002, 11090141450000005);(6)分组避免排序MySQL默认对所有group by字段进行排序非必要情况下分组避免排序。SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;(7)批量INSERT插入插入多条数据时尽量避免逐条数据插入优先选择批量数据插入(插入数据量在50条及以上)。-- 批量数据插入 INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’), (3,’Bernice’); -- 逐条数据插入 INSERT INTO t (id, name) VALUES(1,’Bea’); INSERT INTO t (id, name) VALUES(2,’Belle’); INSERT INTO t (id, name) VALUES(3,’Bernice’);4 典型的索引失效案例表city的联合索引为(ID, CountryCode)非索引列(Name,District,Population)(1)where索引列表达式计算索引失效select * from world.city where ID1 4000;索引未失效select * from world.city where ID 4001;(2)where索引列使用函数索引失效select * from world.city where substring(CountryCode, 1, 2) nl;索引未失效select * from world.city where CountryCode like nl%;select * from world.city where CountryCode like nl_;(3)or条件包含非索引列索引失效select * from world.city where ID 4001 or Name Simi Valley;索引未失效select * from world.city where ID 4001 or CountryCode USA;(4)like模糊查询%在字首索引失效select * from world.city where CountryCode like %nld%;索引未失效select * from world.city where CountryCode like nld%;(5)不满足最左匹配原则索引失效select * from world.city where CountryCode USA and Population 111351;索引未失效select * from world.city where ID 4001 and CountryCode USA and Population 111351;备注最左匹配导致的索引失效情况较多详见最左匹配部分。(6)索引列未设置为NOT NULLMySQL执行查询时会判断字段是否为NOT NULL该过程往往需要全表扫描因此最好为索引添加NOT NULL约束并设置默认值利于索引使用、加速查询效率5 关注insert ignore into导致的性能问题或锁表insert ignore into会对插入的每一行数据取共享锁(S锁其他事务只可读)做唯一键的检测同时会对主键自增ID加意向锁(insert intension)在主键较为复杂的情况下检测主键是否唯一时会一直占用主键的插入意向锁其他进程也想给主键ID添加插入意向锁的时候产生冲突导致死锁此外代码中存在的insert replace into也需重点关注。总结SQL语句优化分析是从事性能测试分析从业人员开展性能优化中的第一站也是性能优化的基本技能对系统性能提升具有重要作用和意义。在掌握性能优化基本技能的基础上还需结合业务需求、代码逻辑访问路径准确评估不同优化方法的适用性综合对比不同优化方法工作成本采用合理高效的优化方法开展性能优化工作。