Mysql性能优化-查询优化

时间：2022-10-18 23:54:24

如何发现有问题的SQL

使用MySQL慢查询日志

对有效率问题的SQL进行监控：

mysql>set global log_queries_not_using_indexes=on;
mysql>set global long_query_time=1;  (几秒的查询会记录到慢查询日志)
mysql> show variables like 'slow_query_log';
mysql>set global slow_query_log_file=’/home/mysql/sql_log/mysql-slow.log’
mysql>set global slow_query_log=on;

慢查询日志的存储格式

Mysql性能优化-查询优化

MySQL慢查询日志分析工具之mysqldumpslow

mysql安装后会将mysqldumpslow自动安装到系统。

#mysqldumpslow -h

MySQL慢查询日志分析工具之pt-query-digest

Mysql性能优化-查询优化

Mysql性能优化-查询优化

MySQL执行计划

Mysql性能优化-查询优化

Mysql性能优化-查询优化

查询优化

Select 语句

  • 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

关于In操作符

在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 方案代替。

NOT IN操作是强列不推荐使用的，因为它不能应用表的索引,推荐树勇NOT EXISTS代替。

关于NULL

判断字段是否为空一般是不会应用索引的，因为索引是不索引空值的。用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。不允许字段为空，而用一个缺省值代替空值，如申请中状态字段不允许为空，缺省为申请。

LIKE操作符

%不要用在字符串开头，这时不会使用索引。

Exists、In与Join的比较

通常情况下，3种查询方式的执行时间：
EXISTS <= IN <= JOIN
NOT EXISTS <= NOT IN <= LEFT JOIN
只有当表中字段允许NULL时，NOT IN的方式最慢：
NOT EXISTS <= LEFT JOIN <= NOT IN
但具体情况具体分析。

Join的性能分析

尽量用inner join.避免 LEFT JOIN 和 NULL。
在使用left join（或right join）时，应该清楚的知道以下几点：

(1)on与 where的执行顺序
ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。
(2).尽量避免子查询，而用join
(3)对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

在MySQL中，只有一种Join算法，就是Nested Loop Join，意思就是通过驱动表的结果集作为循环基础数据，然后一条一条通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

了解了Join的实现原理，就清楚该如何去优化一个一个Join语句：
1、尽可能减少Join语句中的Nested Loop的循环总次数：让驱动表的结果尽可能的小（永远用小结果集驱动大的结果集）。
2、优先优化Nested Loop的内层循环。
3、保证Join语句中被驱动表上Join条件字段已经被索引。
4、当无法保证被驱动表的Join条件字段被索引且内存资源充足前提下，不要太吝惜Join Buffer的设置。

Where语句条件顺序

  • 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
  • 数据量小的条件放到前面。 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
  • 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
  • 应尽量避免在 where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。(WHERE F1/2=100 )
  • 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。
  • 不要在 where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
  • 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描（可以选择用union/union all代替 or）。
  • 对于连续的数值，能用 between 就不要用 in 了。
  • 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

Limit查询

limit常伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的IO问题。

  • 使用有索引的列或逐渐进行order by操作
  • 记录上次返回的主键，在下次查询时使用主键过滤

尾 注意

  • sum属性时，当查询数据为空时，sum值也是空。
  • sum计算double和float时，会出现精度问题，可以在查询时指定小数位。或者字段类型使用decimal(16,6)代替。
  • 涉及金额的表最好建立唯一索引避免数据重复提交；
  • 涉及金额的表的金钱字段，要检查不能小于0。设置无符号的int类型（unsign int（10））。
  • Count()和Max()应该尽量对查询列建立索引。
标签：

相关文章