记录一次Sql性能优化

附带 SQL 优化建议，在本文最下方

场景：

主业务表 contract（合同表），对于不同主体（人员），能查看的合同是不一样的。系统企业业务用到了，系统资源表 PERMISSION_RESOURCE 、员工对于资源关系表：ENTRY_JOIN

正常情况下。查询个人能看的合同。sql如下：简化版、且 对应索引都已加
 

SELECT
	COUNT( DISTINCT C.id ) 
FROM
	CONTRACT C
	INNER JOIN PERMISSION_RESOURCE PR ON PR.resourceId = C.id
	INNER JOIN ENTRY_JOIN EJ ON PR.entryId = EJ.entryId 
	AND EJ.joinId = 2931819442069999624 
	AND PR.canview = 1 
WHERE
	C.STATUS != 'DELETE' 

根据，sql优化建议，内联性能更好。对于业务说contract 往往历史合同无需查看，仅查看最新的合同

inner join（内连接），在两张表进行连接查询时，只保留两张表中完全匹配的结果集。

from A，B 取得是 A，B表得笛卡尔积

第一次优化，通过id 约定查询范围（id 是 根据时间戳 偏移得到）

SELECT
	COUNT( DISTINCT C.id ) 
FROM
	CONTRACT C
	INNER JOIN PERMISSION_RESOURCE PR ON PR.resourceId = C.id
	INNER JOIN ENTRY_JOIN EJ ON PR.entryId = EJ.entryId 
	AND EJ.joinId = 2931819442069999624 
	AND PR.canview = 1 
WHERE
	C.STATUS != 'DELETE' 
	AND C.id > 3016248654885646520 
	AND C.id < 3036245744471820304

结果：查询的效果非常低。达到 4.283 秒

通过解释sql 可以看到 对于 PERMISSION_RESOURCE 查询 使用了 where 。这步没有问题。

问题在于 contract 是资源主体。 PERMISSION_RESOURCE 是资源表。

contract 是小表（9k条），PERMISSION_RESOURCE 是大表（58w）

小对多  内联是 比较耗费性能的

因此 改成 匹配子查询

先排查出满足条件的合同，在对合同 权限进行筛选。

SELECT
	COUNT( DISTINCT C.id ) 
FROM
	CONTRACT C 
WHERE
	C.STATUS != 'DELETE' 
	AND C.id > 3016248654885646520 
	AND C.id < 3036245744471820304 
	AND EXISTS (
	SELECT
		1 
	FROM
		PERMISSION_RESOURCE PR
		INNER JOIN ENTRY_JOIN EJ ON PR.entryId = EJ.entryId 
	WHERE
		PR.resourceId = C.id 
		AND EJ.joinId = 2931819442069999624 
	AND PR.canview = 1 
	)

修改后 性能飙升了 。 

 

反转

以上改法、会让页数参数较小时查询很快。

因为。通过contract 数据去判断权限-》达到数据量后进行返回。

此时查询速度很快，但是在 count 时会进行全表扫描、子查询的数据会明显变慢。

也解释了为什么内联表比子查询更好。

（忘记截图 剩下的执行计划了）

通过执行计划和语法分析得到。可以看出。inner join 时 sql 优化 会选择表更小去内联表更大的。当时实际执行情况不是。

首先来解释下STRAIGHT_JOIN到底是用做什么的：

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. 
This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.

驱动表的概念，mysql中指定了连接条件时，满足查询条件的记录行数少的表为驱动表；如未指定查询条件，则扫描行数少的为驱动表。mysql优化器就是这么粗暴以小表驱动大表的方式来决定执行顺序的。

这个时候需要人工 指定。 以哪个表作为驱动表

XXX  过程省略、理念已传达 哈哈哈哈

总结：

对sql性能优化时，不能照搬书上知识或者网络文章。还是需要有扎实的技术对实际情况进行分析。

启发式优化规则

1. 尽可能执行选择运算
2. 尽可能在执行投影运算
3. 尽可能笛卡级时 将 一连串的选择和投影合并起来做
4. 根据 执行计划去优化sql  比如使用 STRAIGHT_JOIN

SQL 优化建议：
 

1、使用连接(JOIN)来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用select语句来创建一个单例的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

连接(JOIN)之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上 需要两个步骤的查询工作。

有些语句隐形的用到了等值连接，如：

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

2、选择最合适的字段属性

Mysql是一种关系型数据库，可以很好地支持大数据量的存储，但是一般来说，数据库中的表越小，在它上面执行的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小。

3、尽量把字段设置为NOT NULL

在可能的情况下，尽量把字段设置为NOT NULL,这样在将来执行查询的时候，数据库不用去比较NULL值。

4、事务

保持数据库中的数据的一致性和完整性。事务以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL语句操作失败，那么Rollback命令就可以把数据库恢复到begin开始之前的状态。

5、禁止不必要的ORDER BY排序

6、避免数据类型不一致

7、LIKE双百分号无法使用到索引

8、读取适当的记录LIMIT M,N

9、用IN来替换OR

10、避免函数索引