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(结束时间)
-
联合索引:
select * from voucher where status=1 and type=2; select * from voucher where start_time=x and end_time=y;
-
索引覆盖:
select start_time, end_time FROM voucher WHERE status=1 and type=2;