MySql调优（三）Query SQL优化（1）语句优化

w_t_y_y

已于 2024-11-12 16:43:09 修改

阅读量372

点赞数

分类专栏： # 关系型数据库MySQL 文章标签： sql mysql 数据库

于 2019-03-29 10:40:51 首次发布

本文链接：https://blog.youkuaiyun.com/w_t_y_y/article/details/88886042

版权

关系型数据库MySQL 专栏收录该内容

34 篇文章

订阅专栏

mysql中Query语句优化基本思路和原则：

1. 优化更需要优化的 Query

一般来说，高并发低消耗（相对）的 Query 对整个系统的影响远比低并发高消耗的 Query大。从对整个系统的影响来分析。一个频繁执行的高并发 Query 的危险性比一个低并发的 Query 要大很多。

2. 定位优化对象的性能瓶颈

MySQL 的 Query Profiler 是一个使用非常方便的 Query 诊断分析工具，通过该工具可以获取一条Query 在整个执行过程中多种资源的消耗情况，如 CPU，IO，IPC，SWAP 等，以及发生的 PAGE FAULTS，CONTEXT SWITCHE 等等，同时还能得到该 Query 执行过程中 MySQL 所调用的各个函数在源文件中的位置。

3. 明确的优化目标

一般来说，首先需要清楚的了解数据库目前的整体状态，同时也要清楚的知道数据库中与该 Query 相关的数据库对象的各种信息，而且还要了解该 Query 在整个应用系统中所实现的功能。这样我们就能知道数据库所能承受的最大压力，也就清楚了能够接受的最悲观情况。把握了该 Query 相关数据库对象的信息，知道实现该 Query 的消耗最理想情况下需要消耗多少资源，最糟糕又需要消耗多少资源。最后，通过该 Query 所实现的功能点在整个应用系统中的重要地位，可以大概的分析出该 Query 可以占用的系统资源比例，而且也能够知道该 Query 的效率给客户带来的体验影响到底有多大

4. 从 Explain 入手

5. 多使用 profile

6. 永远用小结果集驱动大的结果集

注意是结果集而不是表，小表驱动大表的说法不太严谨。因为大表经过 WHERE 条件过滤之后所返回的结果集并不一定就比小表所返回的结果集大，可能反而更小。在这种情况下如果仍然采用小表驱动大表，就会得到相反的性能效果。

在 MySQL 中的 Join，只有 Nested Loop 一种 Join 方式，也就是 MySQL 的 Join 都是通过嵌套循环来实现的。驱动结果集越大，所需要循环的此时就越多，那么被驱动表的访问次数自然也就越多，而每次访问被驱动表，即使需要的逻辑 IO 很少，循环次数多了，总量自然也不可能很小，而且每次循环都不能避免的需要消耗 CPU ，所以 CPU 运算量也会跟着增加。所以，如果仅仅以表的大小来作为驱动表的判断依据，假若小表过滤后所剩下的结果集比大表多很多，结果就是需要的嵌套循环中带来更多的循环次数，反之，所需要的循环次数就会更少，总体 IO 量和 CPU 运算量也会少。而且，就算是非 Nested Loop 的 Join 算法，如 Oracle 中的 Hash Join，同样是小结果集驱动大的结果集是最优的选择。

7. 尽可能在索引中完成排序

8. 只取出自己需要的 Columns

对于任何 Query，返回的数据都是需要通过网络数据包传回给客户端，如果取出的 Column 越多，需要传输的数据量自然会越大，不论是从网络带宽方面考虑还是从网络传输的缓冲区来看，都是一个浪费。

9. 仅仅使用最有效的过滤条件

10. 尽可能避免复杂的 Join 和子查询

一、单表优化

1、单表查询：

首先尽量只查询单表，可以分解成多次单表查询的尽量分解；

2、合理利用索引；

where、on后的字段尽量命中索引。

3、使用默认排序

SQL查询会默认按照主表的id排序，尽量不要加上其他的排序；

4、in查询：

in查询范围不要太大；

二、分页查询优化：

千万级MySQL分页，使用 SELECT * FROM LIMIT 0,10 分别对表进行常规分页,当偏移量过大，如limit 1000000000,10扫描的行数会非常多造成查询过慢。MySQL的limit工作原理就是先读取前面n条记录，然后抛弃前n条，读后面m条想要的，所以n越大，偏移量越大，性能就越差。优化方法有下面几种：

（1）子查询法：

SELECT c1,c2,cn... FROM table WHERE id>=(SELECT id FROM table LIMIT 20000,1 )  LIMIT 10;

（2）只读索引方法：：高性能MySQL一书中提到的

SELECT * FROM `content` AS t1   
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

优化前：SELECT c1,c2,cn... FROM member ORDER BY last_active LIMIT 50,5 优化后：

SELECT c1, c2, cn .. . FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id)

区别在于，优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了，然后通过member_id读取需要的列。

（3）in查询法：第一步用用程序读取出ID，然后再用IN方法读取所需记录：

 SELECT id FROM table LIMIT 20000, 10;

    SELECT c1, c2, cn .. . FROM table WHERE id IN (id1, id2, idn.. .)

（4）根据主键id排序读取法：

#查询分页
SELECT
	id
FROM
	t_user
WHERE
	...
AND id >= (
	SELECT
		id
	FROM
		t_user
	WHERE
		...
	LIMIT (page - 1) * pageSize,
	1
)
LIMIT pageSize;

//导出用户，分页查询批量写入excel
//计算总条数
int count = userMapper.countExportUser();
//计算总页数
int pages = (count + 500 - 1) / 500;
rowIndex = this.addUserRowData( pages, rowIndex, excelFileUtils, sxssfSheet);
		


int addUserRowData(int pages, int rowIndex, ExcelFileUtils excelFileUtils, SXSSFSheet sxssfSheet){
    int id = 0;
	for (int j = 1; j <= pages; j++) {
			List<UserDTO> users = userMapper.listExportUser(id,500);
			if (CollectionUtils.isEmpty(users)){
				return rowIndex;
			}
			List<List<String>> datas = this.analysisUserExportData(users);
			rowIndex = excelFileUtils.addDataList(datas, sxssfSheet, rowIndex);
			id = users.get(users.size() -1).getId();
	}
	return rowIndex;
}

		
		
	<select id="countExportUser" resultType="java.lang.Integer">
        SELECT
            COUNT(1)
        FROM
            t_user user
         LEFT JOIN  t_role role ON user.role_id = role.id
    </select>
		
	<select id="listExportUser" resultType = "UserDTO">
		 SELECT
        user.id,
        user.province_name,
        user.city_name,
        user.district_name,
		role.role_name
        FROM
        t_user user
        LEFT JOIN  t_role role ON user.role_id = role.id
        WHERE
        user.id > #{id}
        ORDER BY user.id
        limit #{limit}
	</select>

三、join语句优化

尽量使用JOIN代替子查询，子查询通常效率低，JOIN性能更好。

四、order by优化

ORDER BY 会消耗大量资源，尤其是大数据量时，只有在需要排序时才使用。

order by后面的字段尽量命中索引，避免产生额外的文件排序。

五、group by的实现与优化

group by的原理是先分组再排序。

1、消除group by的无谓排序

在group by ..后面加上 order by null。避免产生额外的文件排序。

2、后面的字段尽量命中索引

3、使用CUBE和ROLLUP进行多维聚合分析，减少多次单独的GROUP BY操作

SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;
SELECT region, SUM(sales) FROM sales_data GROUP BY region;

优化为：

SELECT department_id, region, SUM(sales) FROM sales_data 
GROUP BY department_id, region WITH ROLLUP;

六、distinct的实现与优化

七、in优化

对大量IN操作，可以将数据放到临时表中，使用JOIN代替。

八、避免使用NOT IN

NOT IN性能较差，改用NOT EXISTS或LEFT JOIN。

九、并行查询优化（Parallel Query）

充分利用数据库的并行查询能力，尤其在数据量巨大的查询时，开启并行执行计划提高效率。

ALTER SESSION ENABLE PARALLEL QUERY;
SELECT /*+ PARALLEL(sales_data, 4) */ SUM(sales) FROM sales_data;