查看 SQL 执行效率

• show [session|global] status
  • 查看当前连接或者服务器的状态信息，可以用于了解各种SQL的执行效率
• 如： Show status like ‘Com_%’;

• Com_select：一次查询只累加一次
• Com_insert：对于批量插入，一次插入算一次
• Com_update：对于更新操作的计数，不论提交还是回滚都会进行累加。
• 通过参数了解数据库当前是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。
• 通过提交和回滚了解事务的提交和回滚情况，对于回滚操作比较频繁的数据库，可能意味着应用编写存在问题。

定位SQL语句

• 在GaussDB(for MySQL)中可以通过慢日志来定位执行效率较低的SQL语句。
• 点击“日志管理”> “慢日志”，可以查看数据库实例中所有的慢日志信息。
• 慢SQL闽值默认为10s, 可以通过 “ 参数修改”，修改参数long_query_time的值生效。
• 慢SQL 日志需要等待语句结束才能记录，对于正在运行的SQL语句，可以使用showprocesslist命令查看当前数据库进行的线程。
  • 包含线程状态、 是否锁表等。
• 可以在Info字段下， 实时查吞到对应执行时间Time字段值（秒）较
  大的SQL语句。

分析SQL语句

• 定位SQL语句之后，可以通过EXPLAIN和DESC命令获取GaussDB(for MySQL)如何执行SQL语句的信息，包括执行过程中如何连接以及连接的顺序。
• Explain | DESC SQL_Statement;

EXPLAIN解析

• Id: SELECT的查询序列号，用于标识执行的顺序。
  • Id越大优先级别越离，相同从上到下
• Select_type
  • SIMPLE:简单SELECT，不使用UNION或子查询等。
  • DEPENDENT UNION:UNION中的第二个或后面的SELECT语句。
  • PRIMARY:子查询中最外层查询，查询中若包含子查询，最外层的select被标记为PRIMARY。
  • UNION RESULT:UNION的结果。
  • UNION:UNION中的第二个或后面的SELECT语句。
  • SUBQUERY:子查询中的第—个SELECT,结果不依赖于外部查询。
  • DEPENDENT SUBQUERY:子查询中的第一个SELECT,依赖于外部查询；
  • DERIVED:派生表的SELECT, FROM子句的子查询；
  • UNCACHEABLE SUBQUERY:一个子查询的结果不能被缓存，必须重新评估外链接的第一行。
• Table:这一步所访问数据库中表名称
  • 有时不是真实的表名字，可能是简称。
• TYPE:对表访问方式，表示在表中找到所需行的方式，又称“访问类型”
  • ALL:Full Tabll Scan，将遍历全表以找到匹配的行；
  • Index:Full Index Scan，index与ALL区别为index类型只遍历索引树；
  • Range:只检索给定范围的行，使用一个索引来选择行；
  • Index_range:表示使用了索引合并的优化方法；
  • Ref_or_null:类似ref，可以搜索值为NULL的行；
  • Ref:表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值；
  • eq_ref:类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique key作为关联条件；
  • const、system:对查询某部分进行优化，并转换为一个常量时，使用这些类型访问；
  • NULL:在优化过程中分解语句，执行时甚至不用访问表或索引；
  • 性能从上到下依次递增。
  • const、system：将主键置于where列表中，GaussDB(for MySQL)就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system。
  • NULL：从一个索引列里选取最小值可以通过单独索引查找完成。
• possible_keys:能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
• Key:显示实际决定使用的键（索引），必然包含在possible_keys中。
  • 没有选择索引，键是NULL。要想强制数据库使用或忽视ossible_keys列中的索引，在查询中使用FORCE INDEX、 USE INDEX或者IGNORE INDEX。
• key_len:索引中使用的字节数，可通过该列计笢查询中使用的索引的长度。
  • 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
• Ref:列与索引的比较，上述表的连接匹配条件，即哪些列或常童被用于查找索引列上的值。
• Rows:估算出结果集行数，表示数据库根据表统计信息及索引选用情况，估算所需要读取的行数；
• Extra:解决查询的详细信息
  • Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据；
  • Using temporary:表示需要使用临时表来存储结果集，常见于排序和分组查询；
  • Using filesort:当Query中包含order by 操作，而且无法利用索引完成的排序操作；
  • Using join buffer:强调在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果；
  • Impossible where:强调了where语句会导致没有符合条件的行；
  • Distinct:一旦找到了与行相联合匹配的行，就不在搜索；
  • Select tables optimized away:仅通过使用索引，优化器可能仅从聚合函数结果中返回一行；
  • No tables used:Query语句中使用from dual或不含任何from子句；
  • Using where: 这发生在对表的全部的请求列都是同一个索引的部分的时候，表示服务器将在存储引擎检索行后再进行过滤。
  • Using temporary: group by ; order by。
  • Using filesort: explain select * from emp order by name;其中name没有索引。
  • Using join buffer: 出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能。

EXPLAIN总结

• EXPLAIN无法获取触发器、存储过程以及用户自定义函数对查询的影响；
• EXPLAIN不考虑cache;
• EXPLAIN无法显示数据库在执行查询时做的优化工作；
• EXPLAIN产生的统计信息是估算的，并非精准的；
• EXPLAIN—般针对SELECT语句。

常见SQL语句调优

• 使用Insert插入多行记录
  • insert into tableName values(),(),()…
• Select语句尽量不要使用*；
• 尽量少用group by，一般建议通过关联查询和distinct代替；
• 查询一条记录时使用linit 1；
• 灵活使用exists和in、not exists和not in
  • exists和in：假设有两个待查询的表，如果两个表的数据量相差不大，那么用in和exists差别不大；如果两个表中数据量一个较小一个较大，则子查询表大的用exists，子查询表小的用in；
  • not exists 和 not in：用not exists问题比not in效率高；
• Group by非要用的话，在分组字段上添加索引。
• A表数据大于B：select * from A where id in (select id from B);
• A表数据小于B：select * from A where exists (select 1 from B where B.id=A.id);
• 多用函数
  • GaussDB(for MySQL)中内置函数很多， 根据业务需羽苔配合适的函数减少数据再转换。
• 大重数据插入时， 先删除索引及约束
  • 删除索引和约束，并关闭自动提交，等待数据插入完成后，再创建索引和约束。
• 适当添加索引。