01如何发现有问题的SQL

数据库是基于文件的,如果性能好,则要求内存大,IO输出速度快,SSD等,对CPU不是硬性的要求.

1. 使用慢查询日志进行监控.

1
2
3
4

show variables like 'slow_query_log'   //查询状态
set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log'    //设置存储位置
set global log_query_not_using_indexes = on;    //开始慢查询
set global log_query_time = 1     //超过 1 秒的为慢查询

慢查询日志所包含的信息:

1. 执行SQL语句的主机信息.(用户,地址)
2. SQL 的执行信息. (如执行所用时间)
3. SQL 的执行的时间 (执行sql时候的时间 时间戳)
4. SQL 语句的内容.

2. 慢查询的工具

1. mysqldumpslow (安装完成就有,官方自带)

• 使用:

  1	mysqldumpslow -h //可以查看使用命令

  如:

  1	mysqldumpslow -t 3 /home/mysql/data/mysql-slow.log | more

2. pt-query-digest 工具

• 使用
  (1). 输出到文件中

  1	pt-query-digest slow-log > slow-log.report

  (2). 输出到数据库表中

  1 2 3

  pt-query-digest slow.log -review \ n=127.0.0.1 , D=test ,p=root,P=3306,u=root,t=query_review \ --create -review table \ --review -history t=hostname -slow

  如:

  1	pt-query-digest /home/mysql/data/mysql-slow.log | more

  当查询出慢查询的SQL语句后,该优化哪些SQL语句

1. 查询次数比较多的,占用时间长的SQL
2. IO大的SQL(扫描的行数多的SQL,IO大,参考pt-query-digest 分析中的Rows examin项)
3. 未命名索引的SQL

3. explain

使用方法如:

1

explain select * from users;

执行语句后返回各列的意义:

• table : 显示着一行的数据是关于那张表的.
• type : 这是重要的一列 , 显示连接使用了何种类型 , 从最好到最差的连接类型分别为 const (多为主键或唯一索引) , eq_reg(范围查找) , ref (连接的查找 , 基于索引的查找) , range(范围查找) , index(对索引的扫描) , All(表扫描)
• possible_keys : 显示可能应用在这张表中的索引,如果为空,没有可能的索引.
• key_len : 使用索引的长度,在不损失精确性的情况下,长度越短越好.
• rows : MYSQL 认为必须检查用来返回请求数据的行数.

extra需要注意的返回值:

• using filesort 看到这个的使用,查询的 sql 语句需要优化 , mysql 需要进行额外的步骤来发现如何对返回的行排序 , 它根据连接类型以及存储排序键值和匹配条件全部行指针来排序行.
• using temporary , 看到这个的使用, 该 sql 语句需要优化 , 这里, mysql需要创建一个临时表来存储结果 , 这通常发生在不同的列集进行order by 上, 而不是group by 上.

优化 count() 和 max() 的统计类函数 :

1. 在统计的字段上添加索引 .
2. 利用count(‘字段名’) 和 count() 的不同进行统计.
   (1). count(‘字段名’) 不包括 NULL
   (2). count() 包括NULL

**如 :**将下面的SQL语句进行优化(分别统计2016和2017的数据)

1

select count(time='2016' OR time='2017') from film   //优化前SQL实际是错误的

1

select count(time='2016' OR NULL) as 2016,count(time=2017 OR NULL) as 2017 from film    //优化后的SQL

子查询的优化 :

• 通常情况下 , 需要将子查询优化成 join 查询 , 但在优化时需要注意是否存在一对多的关系 , 要注意重复数据 .
  如:

  1	select distinct aid from user join admins on users.uid = admins.aid;

group by优化:

• 当按照一定规则分组的时候 , 在制定规则的子查询中增加过滤条件 , 一定要做到先过滤,后分组 .

limit 查询优化 :

• 常用于分页处理 , 时而伴随着 order by 从句的使用 , 因此大多会使用 Filesorts(文件排序) , 这样会造成大量的 IO 被占用的问题.

1. 优化步骤一 :
   尽量使用主键排序 .
2. 优化步骤二 :记录上次返回的主键,在下次查询时,使用主键过滤 .
   如:

   1	select film_id,description from skia,film order by film_id limit 600,5; //原SQL 扫描605行

   优化后:

   1	select film_id,description from skia,film where film_id>600 and film_id<=605 order by film_id limit 0,5; //扫描了5行

选择合适的列建立索引 :

1. 在 where 从句 , group by 从句 , order by 从句 , on 从句 , 中出现的列.
2. 索引字段的长度越小越好.
3. 查询频繁的字段.
4. 离散度(区分度)越大索引的效果越好.且把离散度(区分度)大的放在联合查询(AND)的前面最好 . (计算公式 : select count(distinct(name))/count(*) from 表名 结果范围 0-1.0 空表为0的结果除外 )
5. 最左前缀匹配原则 : myqsl 会一直向左 , 直到遇到范围查询就会停止 (‘>’ , ‘<’ , ‘between’ , ‘not in’ , ‘not exist’ , ‘!=’ , like )等 , 故 , 将范围查询放在最左(后)面 .
6. 在 SQL 里进行运算会使索引失效 .
7. 扩展优先 , 修改索引的时候 , 尽量在已存在的索引中修改 , 而不是新建 .