Mysql慢查询优化

Mysql慢查询优化

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作

一、找到慢查询

1.开启mysql的慢查询日志

修改配置文件  在 my.ini 增加几行:  
    slow_query_log=ON
    log-slow-queries = 存储路径
    long_query_time = 2

二、优化这些慢查询

1.EXPLAIN优化

explain语句会有如下字段：
ID
ID越大越先执行
ID相同上面的先执行

type
system > const > eq_ref > ref > range > index > all
一般来说，得保证查询至少达到range级别。

• system: 表里只有一条数据
• const: 通过索引一次就找到了，主键或唯一键

  explain select * from users where uid = 1001686122 
  

• eq_ref: 出现在要连接多个表的查询计划中

  explain select * from players p, users u where u.uid = p.uid
  

• ref: 非唯一性索引扫描，返回匹配某个单独值的所有

  explain select * from players where created_time = unix_timestamp('2019-05-01')
  

• range: 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引
  一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

  explain select * from players where created_time between unix_timestamp('2019-05-01') and unix_timestamp('2019-05-02')
  

• index: 遍历索引 使用了索引，但是没有用where或者where条件不合适

  explain select player_id from players
  

possible_keys
可能用到的索引

key
实际用到的索引列

key_len
索引长度，越短越好

ref
显示索引的那一列被使用了

rows
估计扫描的行数

extra
Using filesort: 文件排序，mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
Using temporary: 使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
Using index
Using where

2.索引优化

mysql索引的原理

b+树:

    1每个非叶子节点有N条数据和N+1个指针指向子节点
    2数据间的值域就是子节点所有数据的值域
    3叶子节点存放真实数据
    4叶结点构成一个有序链表，可以按照关键码排序的次序遍历全部记录

每一层相当于一次IO操作，一次IO大约9ms。一次I/O读一页 page，一个磁盘块
真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。
这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。
这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

索引的原则

• 最左前缀匹配原则
  mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

• 尽量选择区分度高的列作为索引
  区分度的公式是count(distinct col)/count(*)，一般需要join的字段要求是0.1以上，即平均1条索引扫描10条记录

• 索引列不能参与计算
  from_unixtime(created_time) = ’2019-05-01’ 应写成 created_time = unix_timestamp(’2019-05-01’)

• 尽量的扩展索引，不要新建索引
  比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

注意点

• 应尽量避免在 where 子句中对字段进行 null 值判断; 可以在num上设置默认值0，确保表中num列没有null值，然后查询

  select id from t where num is null
  select id from t where num=0 
  

• 应尽量避免在 where 子句中对索引使用!=或<>操作符，否则将引擎放弃使用该索引而进行全表扫描

• 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用该索引而进行全表扫描, 应该用union all代替

  select id from t where num=10 or num=20 
  可以这样查询： 
  select id from t where num=10 
  union all 
  select id from t where num=20 
  

• like %开头 也会放弃使用索引

  explain select * from players where player_name LIKE '%test'
  

• where中使用变量也会全表扫描

3.细节优化

• 关键字大写

• 不要返回不必要的行和列
  select的时候，写明列名，尽量不要写select *

• 一般在GROUP BY 和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select 的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组

• COUNT(*) 和 COUNT(1)已经没区别了

• 小表驱动大表

  explain select * from games where game_code in (select game_code from game_codes)
  explain select * from games where exists(select 1 from game_codes where games.game_code = game_codes.game_code)
  in 小 exists 大
  

• 能用between就不要用in

• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

• 类型要对应

  explain select * from users where uid = '561075524' 
  explain select * from users where uid = 561075524
  类型严格的一方转换为类型松散的一方的类型，可以将数值型向字符串类型转，反过来则会有问题。
  explain select * from players where player_id = '1056903111'
  

• 没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在程序中执行。它们增加了额外的开销。

• 将一个大的查询分解为多个小查询
  很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效

• limit 100000,10的优化方法，延迟关联

  explain select * from orders limit 1000000,10      5秒
  explain select * from orders po join (select order_id from orders limit 1000000,10) tmp on po.order_id = tmp.order_id       1秒