Mysql查询优化

最新推荐文章于 2022-07-27 10:35:45 发布

firesea

最新推荐文章于 2022-07-27 10:35:45 发布

阅读量117

点赞数

分类专栏：数据库文章标签： mysql优化

本文链接：https://blog.csdn.net/firesea/article/details/119101748

版权

7 篇文章 0 订阅

订阅专栏

一、count()的优化

它可以统计某个列值的数量，也可以统计行数。在统计列值的时候要求列值是非空的。

如果希望知道的是结果集的行数，最好使用count(*)，这样写意义清晰，性能会更好。如果mysql知道某列col不可能为null，那么mysql会将count(col)表达式优化为count(*)。

MyISAM的count()函数快的前提是，没有任何where条件，这样mysql可以从统计表中直接获取count()值。当sql语句中带有where字句，MyISAM的count()和其他引擎没有任何不同。

优化场景A：MyISAM引擎下，合理利用count(*)全表非常快的特性

表crm_hc_area中存储3597条数据，当需要查询id>10的数据条数，常规的sql如下：

select count(1) from crm_hc_area where id > 10;

Explain查看执行计划如下：

可以看到，mysql进行了全表扫描。

如果将sql语句修改如下：

select (select count(*) from crm_hc_area) - count(*) from crm_hc_area where id <= 10;

再次查看执行计划如下：

大大减少了需要扫描的行数，因为mysql在查询优化阶段，会将子查询直接作为一个常数处理。数据量越大，该方式优化的结果越明显。

优化场景B：查询不同省份下，行政区域划分数量

常规的sql如下：

SELECT count(*) as 北京 from crm_hc_area where reid = 1;
SELECT count(*) as 上海 from crm_hc_area where reid = 2;
......

如果采用如下sql，无法区分北京和上海的行政区域划分。

SELECT count(*) from crm_hc_area where reid = 1 or reid = 2;

如果将sql语句修改如下：

select count(reid = 1 or null) '北京',count(reid = 2 or null) '上海' from crm_hc_area ;

即可满足查询需求。

注：整理涉及另外一个知识点，count(reid = 1 or reid = 2 or null)，为什么要加上“or null”？可通过下面一组sql，自行领悟。

select true or null;
select false or null;
select count(true);
select count(false);
select count(true or null);
select count(false or null);

也可以不使用count()函数，使用sum()函数也可以实现该功能，sql如下：

select sum(IF(reid = 1,1,0)) 北京 ,sum(IF(reid = 2,1,0)) 上海 from crm_hc_area ;

其他优化场景：1：使用近似值；2：使用索引覆盖扫描；3：增加汇总表或者外部缓存系统等。

二、优化查询需要注意

1）确保ON或者USING子句的列上有索引；

2）确保条件中两个表相应字段的类型一致，如果不一致，很可能会造成索引失效；

3）相关联的两个表，只需要在后表对应的列上创建索引；

4）确保group by和order by中的表达式只涉及到一个表中的列，这样mysql才可能使用索引来优化执行过程；

5）尽可能使用关联查询代替子查询；

三、Limit的优化

如果系统需要分页操作的时候，通常会用到limit加上偏移量的方法进行查询。在数据量较小的时候，这么做的效率还不错。当数据量非常大的时候，例如：limit 100000,10，mysql需要遍历100010调数据，然后抛弃前面的100000条，这样的代价非常高！

优化方法A：将上一分页的最后一条数据id带入当前的查询中，sql如下：

select * from T1 where id > 100000 limit 10;

优化方法B：采用索引覆盖扫描，延迟关联的方式，sql如下：

SELECT * FROM T1
	INNER JOIN (
		SELECT id FROM T1 ORDER BY id DESC LIMIT 100010,10
	) AS t1 USING(id);

这种延迟关联方法，可以大大提升查询效率，它让mysql扫描尽可能少的数据，获取需要访问的记录后再根据关联列回原表查询需要的所有列。

其他优化方法：

C：预先计算汇总表

D：记录冗余表，冗余表中只记录主键及需要做排序的数据列；

关注