Mysql慢查询性能优化实战

Mysql慢查询性能优化

where查询太慢

• 使用覆盖索引，通过取消回表操作，提升查询效率。
• 可以通过优化SQL语句（少写select *）或者优化联合索引（把不在列的优化成联合索引），来使用覆盖索引。
• 查询的数据不只使用了一个索引，则不是覆盖索引。

Group by

提高 GROUP BY 语句的效率, 可以通过将不需要的记录在 GROUP BY 之前过滤掉.

select job , avg(sal) from table_demo group by job having  job = ‘manager'	#低效
select job , avg(sal) from table_demo where  job = ‘manager' group by job	#高效

Order by

• 全字段排序 ：
  • 当行小于max_length_for_sort_data时，生成全字段中间表。
  • 把需要的字段都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
• Rowid排序：大于阈值时，只生成排序字段+主键中间表，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

优化：

• 优化排序时间，增大sort_buffer_size（中间表较小时，直接放在内存中；中间表大于sort_buffer_size时，放入硬盘）
• 走覆盖索引，不再回表

count(*)

• count(1)： innodb引擎会扫描整个表，但不取数据。server层对于每一行放个1进去，判断不可能为NULL，逐行累计。

  count(1)：可以统计表中所有数据，不统计所有的列，用1代表代码行，在统计结果中包含列字段为null的数据；

• count(字段)： 计算所有column字段为"非null"值的总数 。 若该字段声明为NOT NULL ，server直接累计得出数量。若可以为NULL，则server层还要判断每一行的值，不为NULL的进行累计。

  count(字段)：只包含列名的列，统计表中出现该字段的次数，并且不统计字段为null的情况；

• count(*)： 专门做了优化，不取值，判断不可能为NULL，逐行累计。

  count(*)：统计所有的列，相当于行数，统计结果中会包含字段值为null的列；

• count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

• 按照效率排序的话，count(字段)<count(1)≈count(*)

  列名为主键，count(列名)比count(1)快；列名不为主键，count(1)会比count(列名)快；
  如果表中多个列并且没有主键，则count(1)的执行效率优于count()；
  如果有主键，则select count(主键)的执行效率是最优的；如果表中只有一个字段，则select count()最优。

• 在 MyISAM 引擎 中，每个表的总行数都会在内存和磁盘文件中进行保存，当执行 count(*) 语句的时候，会直接将内存中保存的数值返回，所以执行非常快。

  而在InnoDB 引擎中，当执行 count(*) 的时候，它需要一行一行的进行统计和计数，并将最终的统计结果返回。

• 为什么 InnoDB 引擎就不能像 MyISAM 引擎一样，也把总行数保存到内存和磁盘文件中呢？

  这是因为 InnoDB 引擎实现了多版本并发控制（MVCC）的原因：对同一个表，不同事物在同一时刻，看到的数据可能是不一样的。

limit

深分页问题：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行， 那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页 数进行 SQL 改写。

优化：

• 使用游标(cursor)代替 offset：游标用来标记查询到的最后一条数据。使用游标时，每次查询都从游标处开始，返回指定数量的数据。这种方式不需要扫描 offset 条数据，查询速度更快。

  select * from voucher where type = 2  limit 100000,10;
  

  • 偏移ID：

    lastId = 0 or min(id)
    do {
    select * from voucher where type = ? and id >{#lastId}  limit ?;
    lastId = max(id)
    } while (isNotEmpty)
    

  • 分段查询：

    minId = min(id) maxId = max(id)
    for(int i = minId; i<= maxId; i+=pageSize){
    select * from voucher where type = ? and id between i and i+ pageSize;
    }
    

• 使用主键或唯一索引替代范围查询：先查询出所有符合条件的 id 值，然后再通过主键或唯一索引查询出相应的订单记录，从而避免了范围查询带来的性能问题。(如果表中的数据量非常大，这种方法也可能会带来一定的性能问题，因为在子查询中需要查询所有符合条件的 id 值，这可能会导致性能瓶颈)

  SELECT * FROM voucher WHERE price BETWEEN 100 AND 200;
  #改写：使用主键或唯一索引替代范围查询
  SELECT * FROM voucher WHERE id IN (
    SELECT id FROM order WHERE price BETWEEN 100 AND 200
  );
  

• 数据缓存：为了加快分页查询速度，可以将查询结果缓存到内存中，以便下次查询时可以直接从内存中读取数据。

建立索引实战：

优惠券表：voucherID(主键)，status（是否被删除0/1）， type(优惠券类型)，start_time（优惠券秒杀开始时间），end_time（结束时间）

1. 联合索引：

    select * from voucher where status=1 and type=2;
    select * from voucher where start_time=x and end_time=y;
   

2. 索引覆盖：

   select start_time, end_time FROM voucher WHERE status=1 and type=2;