SQL mysql优化

慢查询

如何通过慢查日志发现有问题的SQL？

• 查询次数多且每次查询占用时间长的SQL
  • pt-query-digest分析前几个查询
• IO大的SQL
  • pt-query-diges分析中的Rows examine项
• 未命中索引的SQL
  • pt-query-digest分析中Rows examine 和Rows Send的对比

如何分析SQL查询

• 使用explain查询SQL的执行计划

  ​ explain select custome_id,first_name,last_name from customers;

• explain返回列的含义

  table:显示这一行的数据是哪张表的

  type: 这是重要的列，显示连接用了何种类型，从最好到最差的类型为

  const,eq_reg、ref、range、index、ALL

  possible_keys: 显示可能应用在这张表中的索引，如果未空，没有可能的索引。

  key:实际使用的索引。如果为NULL，则没有使用索引。

  key_len:使用的索引长度。在不损失精确性的情况下，长度越短越好

  ref:显示索引被哪一列使用了，如果可能的话是一个常数

  rows:MYSQL认为必须检查的用来返回请求数据的行数。

• 需要注意的返回列
  • Using filesort:看到这个的时候，查询就需要优化了，MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
  • Using temporary 看到这个的时候就需要优化了。这里，MSQL需要创建一个临时表来存储结果，这通常发生在对不同的列进行Order By上，而不是Group By上。

max()和Count()的优化

查询最后支付的时间-优化max()函数

select max(payment_date) from payment

explain select max(payment_date) from payment

建索引：

create index idx_paydate on payment(pay_date);

在一条SQL中同事查出2006年和2007年的电影的数量--优化count()函数

错误的方式
select count(release_year='2006' or release_year='2006') from film;
分开计算2006和2007年的电影数量
select count(*) from film where release_year='2006' AND
release_year='2007';

优化：

select count(release_year='2006' or NULL)

select count(release_year='2076' or NULL)

备注：count(*)包含空值，count(某一列) 不包含某一列

子查询优化：

通常情况下，需要把子查询优化为join查询，但在优化时需要注意关联键是否有一对多的关系。需要注意数据重复

groupby优化：

通过关联子查询优化

limit查询的优化：

limit常用于分页处理，时常会伴随order by 从句使用，因此大多时候会使用Filesorts这样会造成大量的IO。

记录上次返回的主键，在下次查询时使用主键过滤。

索引优化

pt-duplicate-key-checker \
-h 127.0.0.1 \
-uroot \
-p root \
--databases hand_sql \
--tables HAND_COURSE

索引使用情况分析

pt-index-usage \
-u root \ 
-p root \
mysql-slow.log

 pt-query-digest --report  /tmp/mysql-slow.log