MySQL语句优化

The success of one thing, like a puzzle, never a puzzle can be successful
一件事的成功，好比拼图，从来都不是一块拼图就可以成功

随着项目的运行，数据量越来越多，经常会遇到大数据量的查询而导致服务挂掉，影响用户体验，今天主要举例实际开发项目中几种常见的sql优化策略，可提高服务器响应速度

话不多说，上菜......

1.尽量避免在where字句中使用or来连接条件，否则会导致引擎放弃使用索引而进行全表扫描;如：

注：union和union all 的区别：union进行表连接的时候重复列只显示一列，去重复的，而union all则会显示所有列的所有数据

2.很多时候使用exists 代替in是一个好的选择

3.尽量避免全表扫描

4.尽量不使用嵌套子查询，因为每一条都要进行匹配，数据量比较大的话，查询就会非常的耗时，而用表连接

5.关联更新，删除

UPDATE operation o
SET STATUS = 'applying'
WHERE
	o.id IN (
		SELECT
			id
		FROM
			(
				SELECT
					o.id,
					o. STATUS
				FROM
					operation o
				WHERE
					o. GROUP = 123
				AND o. STATUS NOT IN ('done')
				ORDER BY
					o.parent,
					o.id
				LIMIT 1
			) t
);

优化后

UPDATE operation o
JOIN (
	SELECT
		o.id,
		o. STATUS
	FROM
		operation o
	WHERE
		o. GROUP = 123
	AND o. STATUS NOT IN ('done')
	ORDER BY
		o.parent,
		o.id
	LIMIT 1
) t ON o.id = t.id
SET STATUS = 'applying'

6.混合排序

SELECT
	*
FROM
	my_order o
INNER JOIN my_appraise a ON a.orderid = o.id
ORDER BY
	a.is_reply ASC,
	a.appraise_time DESC
LIMIT 0,20 

由于 is_reply 只有0和1两种状态，我们按照下面的方法重写后 执行时间从1.58秒降低到2毫秒。

SELECT
	*
FROM
	(
		(
			SELECT
				*
			FROM
				my_order o
			INNER JOIN my_appraise a ON a.orderid = o.id
			AND is_reply = 0
			ORDER BY
				appraise_time DESC
			LIMIT 0,20
		)
		UNION ALL
		(
			SELECT
				*
			FROM
				my_order o
			INNER JOIN my_appraise a ON a.orderid = o.id
			AND is_reply = 1
			ORDER BY
				appraise_time DESC
			LIMIT 0,20
		)
	) t
ORDER BY
	is_reply ASC,
	appraisetime DESC
LIMIT 20;

7.exists语句

SELECT
	*
FROM
	my_neighbor n
LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id
AND sra.user_id = 'xxx'
WHERE
	n.topic_status < 4
AND EXISTS (
	SELECT
		1
	FROM
		message_info m
	WHERE
		n.id = m.neighbor_id
	AND m.inuser = 'xxx'
)
AND n.topic_type <> 5

去掉 exists 更改为 join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。

SELECT
      *
FROM
	my_neighbor n
INNER JOIN message_info m ON n.id = m.neighbor_id
AND m.inuser = 'xxx'
LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id
AND sra.user_id = 'xxx'
WHERE
	n.topic_status < 4
AND n.topic_type <> 5

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

select id from t where substring(name,1,3)='abc'   --name以abc开头的id 

总结：

以上主要列举项目中常见的sql语句优化策略，在平常的开发中，满足业务开发需求的同时，要逐渐培养自己的优化意识，掌握优化技巧。一方面提高自己代码性能，磨练自己的技术，另一方面搬砖也搬得更加娴熟