判断问题SQL判断SQL是否有问题时可以通过两个表象进行判断系统级别表象CPU消耗严重IO等待严重页面响应时间过长应用的日志出现超时等错误可以使用sar命令top命令查看当前系统状态。也可以通过Prometheus、Grafana等监控工具观察系统状态。感兴趣的可以翻看我之前的文章SQL语句表象冗长执行时间过长从全表扫描获取数据执行计划中的rows、cost很大冗长的SQL都好理解一段SQL太长阅读性肯定会差而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手如下所示执行计划告诉我们本次查询走了全表扫描TypeALLrows很大(9950400)基本可以判断这是一段有味道的SQL。获取问题SQL不同数据库有不同的获取方法以下为目前主流数据库的慢查询SQL获取工具MySQL慢查询日志 测试工具loadrunner Percona公司的ptquery等工具OracleAWR报告 测试工具loadrunner等 相关内部视图如v$、$session_wait等 GRID CONTROL监控工具 达梦数据库 AWR报告 测试工具loadrunner等 达梦性能监控工具dem 相关内部视图如v$、$session_wait等 SQL编写技巧 SQL编写有以下几个通用的技巧• 合理使用索引索引少了查询慢索引多了占用空间大执行增删改语句的时候需要动态维护索引影响性能 选择率高重复值少且被where频繁引用需要建立B树索引 一般join列需要建立索引复杂文档类型查询采用全文索引效率更好索引的建立要在查询和DML性能之间取得平衡复合索引创建时要注意基于非前导列查询的情况使用UNION ALL替代UNIONUNION ALL的执行效率比UNION高UNION执行时需要排重UNION需要对数据进行排序• 避免select * 写法执行SQL时优化器需要将 * 转成具体的列每次查询都要回表不能走覆盖索引。• JOIN字段建议建立索引一般JOIN字段都提前加上索引• 避免复杂SQL语句提升可阅读性避免慢查询的概率可以转换成多个短查询用业务端处理• 避免where 11写法• 避免order by rand()类似写法RAND()导致数据列被多次扫描SQL优化执行计划完成SQL优化一定要先读执行计划执行计划会告诉你哪些地方效率低哪里可以需要优化。我们以MYSQL为例看看执行计划是什么。每个数据库的执行计划都不一样需要自行了解explain sql字段 解释 id 每个被独立执行的操作标识标识对象被操作的顺序id值越大先被执行如果相同执行顺序从上到下 select_type 查询中每个select 字句的类型 table 被操作的对象名称通常是表名但有其他格式 partitions 匹配的分区信息(对于非分区表值为NULL) type 连接操作的类型 possible_keys 可能用到的索引 key 优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道” key_len 被优化器选定的索引键长度单位是字节 ref 表示本行被操作对象的参照对象无参照对象为NULL rows 查询执行所扫描的元组个数对于innodb此值为估计值 filtered 条件表上数据被过滤的元组个数百分比 extra 执行计划的重要补充信息当此列出现Using filesort , Using temporary 字样时就要小心了很可能SQL语句需要优化接下来我们用一段实际优化案例来说明SQL优化的过程及优化技巧。优化案例表结构CREATE TABLE a( id int(11) NOT NULL AUTO_INCREMENT, seller_id bigint(20) DEFAULT NULL, seller_name varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, gmt_create varchar(30) DEFAULT NULL, PRIMARY KEY (id) )ENGINEInnoDB DEFAULT CHARSETutf8; CREATE TABLE b( id int(11) NOT NULL AUTO_INCREMENT, seller_name varchar(100) DEFAULT NULL, user_id varchar(50) DEFAULT NULL, user_name varchar(100) DEFAULT NULL, sales bigint(20) DEFAULT NULL, gmt_create varchar(30) DEFAULT NULL, PRIMARY KEY (id) )ENGINEInnoDB DEFAULT CHARSETutf8; CREATE TABLE c( id int(11) NOT NULL AUTO_INCREMENT, user_id varchar(50) DEFAULT NULL, order_id varchar(100) DEFAULT NULL, state bigint(20) DEFAULT NULL, gmt_create varchar(30) DEFAULT NULL, PRIMARY KEY (id) )ENGINEInnoDB DEFAULT CHARSETutf8;三张表关联查询当前用户在当前时间前后10个小时的订单情况并根据订单创建时间升序排列具体SQL如下select a.seller_id, a.seller_name, b.user_name, c.state from a, b, c where a.seller_name b.seller_name and b.user_id c.user_id and c.user_id 17 and a.gmt_create BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE) AND DATE_ADD(NOW(), INTERVAL 600 MINUTE) order by a.gmt_create查看数据量原执行时间原执行计划初步优化思路 1SQL中 where条件字段类型要跟表结构一致表中user_id 为varchar(50)类型实际SQL用的int类型存在隐式转换也未添加索引。将b和c表user_id 字段改成int类型。 2因存在b表和c表关联将b和c表user_id创建索引 3因存在a表和b表关联将a和b表seller_name字段创建索引 4利用复合索引消除临时表和排序初步优化SQL alter table b modify user_id int(10) DEFAULT NULL; alter table c modify user_id int(10) DEFAULT NULL; alter table c add index idx_user_id(user_id); alter table b add index idx_user_id_sell_name(user_id,seller_name); alter table a add index idx_sellname_gmt_sellid(gmt_create,seller_name,seller_id);查看优化后执行时间查看优化后执行计划查看warnings信息继续优化alter table a modify gmt_create datetime DEFAULT NULL;查看执行时间查看执行计划总结 1查看执行计划 explain 2如果有告警信息查看告警信息 show warnings; 3查看SQL涉及的表结构和索引信息 4根据执行计划思考可能的优化点 5按照可能的优化点执行表结构变更、增加索引、SQL改写等操作 6查看优化后的执行时间和执行计划 7如果优化效果不明显重复第四步操作原文链接数据库优化 - SQL优化-CSDN博客