집 >데이터 베이스 >MySQL 튜토리얼 >Mysql 다중 테이블 공동 쿼리 효율성 분석 및 최적화
Mysql 다중 테이블 공동 쿼리 효율성 분석 및 최적화
- 小云云원래의
- 2017-12-13 14:10:5414224검색
MySQL 빅 데이터 쿼리 최적화는 많은 웹마스터에 의해 주의 깊게 분석되지 않습니다. 이 문제에 대해 편집자는 최근 100W 데이터 최적화 문제에 직면했습니다. 다음은 일부 MySQL 관련 쿼리 최적화 테스트 및 관련 목록입니다. 분석이 모든 사람에게 도움이 되기를 바랍니다. .
추천 관련 mysql 동영상 튜토리얼: "mysql tutorial"
첫째, 단순 상관 하위 쿼리의 최적화
mysql에 구현된 하위 쿼리의 성능이 좋지 않은 경우가 많습니다. . 특히 IN() 서브쿼리 문을 사용할 때 특정 크기의 테이블에 대해서는 추정하는 데 너무 많은 시간이 걸리는 경우가 있습니다. 내 mysql 지식이 깊지 않아서 천천히 그 미스터리를 알아낼 수 밖에 없다.
다음과 같은 존재 쿼리 문이 있다고 가정합니다.
select * from table1
where exists
(select * from table2 where id>=30000 and table1.uuid=table2.uuid);table1은 10만 행이 있는 테이블이고, table2는 100만 행이 있는 테이블이며 로컬 테스트 결과는 2.40초가 걸립니다.
설명을 통해 하위 쿼리가 관련 하위 쿼리(DEPENDENCE SUBQUERY)임을 알 수 있습니다. MySQL은 먼저 외부 테이블1에서 전체 테이블 스캔을 수행한 다음 반환된 uuid를 기반으로 하위 쿼리를 하나씩 실행합니다. 외부 테이블이 큰 테이블인 경우 쿼리 성능이 이번 테스트보다 나빠질 것이라고 상상할 수 있습니다.
간단한 최적화 솔루션은 내부 조인 방법을 사용하여 하위 쿼리를 대체하는 것입니다. 쿼리 문은 다음과 같이 변경할 수 있습니다.
select * from table1 innner join table2 using(uuid) where table2.id>=30000;
로컬 테스트 결과는 0.68초가 걸렸습니다.
mysql이 SIMPLE 유형(하위 쿼리 또는 공용체 이외의 쿼리 방법)을 사용한다는 것을 설명을 통해 볼 수 있습니다. Mysql 최적화 프로그램은 먼저 table2를 필터링한 다음 table1과 table2의 데카르트 곱을 수행하여 결과 집합을 얻은 다음 통과합니다. 데이터를 필터링하는 조건입니다.
2. 다중 테이블 공동 쿼리 효율성 분석 및 최적화
1. 다중 테이블 연결 유형
1. Cartesian product(cross Join) MySQL에서는 CROSS JOIN이거나 CROSS 또는 JOIN을 생략하거나 ',' 등을 사용할 수 있습니다. as:
01.SELECT * FROM table1 CROSS JOIN table2 02.SELECT * FROM table1 JOIN table2 03.SELECT * FROM table1,table2 SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,table2
반환된 결과는 연결된 두 데이터 테이블의 곱이므로 일반적으로 WHERE, ON 또는 USING 조건이 있는 경우에는 사용하지 않는 것이 좋습니다. 데이터 테이블 항목이 너무 많으면 매우 느립니다. 일반적으로 LEFT [OUTER] JOIN 또는 RIGHT [OUTER] JOIN
2을 사용합니다. INNER JOIN INNER JOIN은 MySQL에서 동등 조인(equijoin)이라고 하는데, MySQL에서는 CROSS와 INNER JOIN을 Together로 나누어서 조건을 지정해야 합니다. . Join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]
3. MySQL의 외부 조인은 왼쪽 외부 조인과 오른쪽 조인으로 구분됩니다. 즉, 조인 조건을 만족하는 결과를 반환하는 것 외에도 왼쪽 테이블(왼쪽 Connection) 또는 오른쪽 테이블의 결과(right Join)가 연결 조건을 만족하지 않는 경우에는 그에 따라 NULL을 사용합니다.
예:
user 테이블:
id | name ——— 1 | libk 2 | zyfon 3 | daodao
user_action 테이블:
user_id | action ————— 1 | jump 1 | kick 1 | jump 2 | run 4 | swim
sql:
01.select id, name, action from user as u 02.left join user_action a on u.id = a.user_id select id, name, action from user as u left join user_action a on u.id = a.user_idresult: id | name | action ——————————– 1 | libk | jump ① 1 | libk | kick ② 1 | libk | jump ③ 2 | zyfon | run ④ 3 | daodao | null ⑤
Analytic:
user_action에 user_id=4, action=swim이라는 기록이 있지만, 결과
user 테이블에서 id=3, name=daodao인 사용자는 user_action에 해당 레코드가 없지만 결과 집합에는 나타납니다
이제 Left 조인이므로 모든 작업은 Left를 기반으로 합니다.
결과 1, 2, 3, 4는 모두 왼쪽 테이블과 오른쪽 테이블의 레코드이고, 5는 왼쪽 테이블에만 레코드가 있고 오른쪽 테이블에는 레코드가 없습니다.
작동 원리:
왼쪽 테이블의 레코드를 읽고 on 조건과 일치하는 모든 레코드를 선택합니다. 오른쪽 테이블 레코드(n)는 n개의 레코드(결과 1, 결과 3 등 중복 행 포함)로 연결됩니다. 오른쪽에 on 조건과 일치하는 테이블이 없으면 연결됩니다. 필드가 모두 null이면 다음 기사를 계속 읽어보세요.
확장:
오른쪽 테이블에 일치 항목이 없으면 null이 표시되어 왼쪽 테이블에는 있지만 오른쪽 테이블에는 없는 모든 레코드를 찾는 규칙을 사용할 수 있습니다. 판결은 null이 아닌 것으로 선언되어야 합니다.
예:
sql:
01.select id, name, action from user as u 02.left join user_action a on u.id = a.user_id 03.where a.user_id is NULL select id, name, action from user as u left join user_action a on u.id = a.user_id where a.user_id is NULL
(참고:
1. 열 값이 null인 경우 =NULL
대신 is null을 사용해야 합니다. 2. 여기서 a.user_id 열은 NOT NULL로 선언되어야 합니다. )
위의 SQL 결과:
id | name | action ————————– 3 | daodao | NULL ——————————————————————————–
일반 사용법:
a.LEFT [OUTER] JOIN:
조인 조건을 충족하는 결과를 반환하는 것 외에도 왼쪽 테이블에 다음과 같은 데이터 열을 표시해야 합니다.
01.SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column b. RIGHT [OUTER] JOIN：
RIGHT는 LEFT JOIN과 유사하지만 유일한 차이점은 연결 조건을 충족하는 결과를 표시하는 것 외에도 오른쪽 테이블에 데이터 열도 표시해야 한다는 것입니다. 연결 조건을 충족하지 않는 경우 NULL을 사용하세요
01.SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.columnTips:
1. on a.c1 = b.c1은 using(c1)
과 동일합니다. 2. INNER JOIN과 (쉼표)는 의미상 동일합니다
3. MySQL이 검색할 때 테이블의 정보를 확인하려면 어떤 인덱스를 선택하라는 메시지를 표시할 수 있습니다.
이 기능은 MySQL이 가능한 인덱스 목록 중 잘못된 인덱스를 사용하고 있음을 EXPLAIN에서 표시하는 경우 유용합니다.
USE INDEX(key_list)를 지정하면 MySQL이 테이블에서 행을 찾기 위해 가능한 가장 적절한 인덱스를 사용하도록 지시할 수 있습니다.
선택적 두 번째 선택 구문 IGNORE INDEX(key_list)를 사용하면 MySQL에 특정 인덱스를 사용하지 않도록 지시할 수 있습니다. 예:
01.mysql> SELECT * FROM table1 USE INDEX (key1,key2) 02.-> WHERE key1=1 AND key2=2 AND key3=3; 03.mysql> SELECT * FROM table1 IGNORE INDEX (key3) 04.-> WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 USE INDEX (key1,key2) -> WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3) -> WHERE key1=1 AND key2=2 AND key3=3;
2. 테이블 연결 제약조건
표시 조건 추가 WHERE, ON, USING
1. WHERE 절 mysql>
01.SELECT * FROM table1,table2 WHERE table1.id=table2.id; SELECT * FROM table1,table2 WHERE table1.id=table2.id;
2. ON
mysql>
01.SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; 02. 03.SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id 04.LEFT JOIN table3 ON table2.id=table3.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id;
3. USING子句，如果连接的两个表连接条件的两个列具有相同的名字的话可以使用USING
例如：
SELECT FROM LEFT JOIN USING ()
连接多于两个表的情况举例：
mysql>
01.SELECT artists.Artist, cds.title, genres.genre 02. 03.FROM cds 04. 05.LEFT JOIN genres N cds.genreID = genres.genreID 06. 07.LEFT JOIN artists ON cds.artistID = artists.artistID; SELECT artists.Artist, cds.title, genres.genre FROM cds LEFT JOIN genres N cds.genreID = genres.genreID LEFT JOIN artists ON cds.artistID = artists.artistID;
或者 mysql>
01.SELECT artists.Artist, cds.title, genres.genre 02. 03.FROM cds 04. 05.LEFT JOIN genres ON cds.genreID = genres.genreID 06. 07. LEFT JOIN artists -> ON cds.artistID = artists.artistID 08. 09. WHERE (genres.genre = 'Pop'); SELECT artists.Artist, cds.title, genres.genre
FROM cds
LEFT JOIN genres ON cds.genreID = genres.genreID LEFT JOIN artists -> ON cds.artistID = artists.artistID WHERE (genres.genre = 'Pop');
--------------------------------------------
另外需要注意的地方 在MySQL中涉及到多表查询的时候，需要根据查询的情况，想好使用哪种连接方式效率更高。
1. 交叉连接(笛卡尔积)或者内连接 [INNER | CROSS] JOIN
2. 左外连接LEFT [OUTER] JOIN或者右外连接RIGHT [OUTER] JOIN 注意指定连接条件WHERE, ON，USING.
3. MySQL如何优化LEFT JOIN和RIGHT JOIN
在MySQL中，A LEFT JOIN B join_condition执行过程如下：
1)· 根据表A和A依赖的所有表设置表B。
2)· 根据LEFT JOIN条件中使用的所有表(除了B)设置表A。
3)· LEFT JOIN条件用于确定如何从表B搜索行。(换句话说，不使用WHERE子句中的任何条件）。
4)· 可以对所有标准联接进行优化，只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系，MySQL提示出现一个错误。
5)· 进行所有标准WHERE优化。
6)· 如果A中有一行匹配WHERE子句，但B中没有一行匹配ON条件，则生成另一个B行，其中所有列设置为NULL。
7)· 如果使用LEFT JOIN找出在某些表中不存在的行，并且进行了下面的测试：WHERE部分的col_name IS NULL，其中col_name是一个声明为 NOT NULL的列，MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。
RIGHT JOIN的执行类似LEFT JOIN，只是表的角色反过来。
联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作，因为检查的表交换更少。请注意这说明如果执行下面类型的查询，MySQL进行全扫描b，因为LEFT JOIN强制它在d之前读取：
01.SELECT * 02.FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) 03.WHERE b.key=d.key; SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
在这种情况下修复时用a的相反顺序，b列于FROM子句中：
01.SELECT * 02.FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) 03.WHERE b.key=d.key; SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
MySQL可以进行下面的LEFT JOIN优化：如果对于产生的NULL行，WHERE条件总为假，LEFT JOIN变为普通联接。
例如，在下面的查询中如果t2.column1为NULL，WHERE 子句将为false：
01.SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;因此，可以安全地将查询转换为普通联接：
01.SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;这样可以更快，因为如果可以使查询更佳，MySQL可以在表t1之前使用表t2。为了强制使用表顺序，使用STRAIGHT_JOIN。
三、利用缓存来实现
现在社区分享类网站很火，就拿方维购物分享网站举例说明吧。也是对二次开发方维购物分享网站的一点总结，高手可以飞过。
购物分享的关键表有：分享表、图片表、文件表、评论表、标签表、分类表等。
围绕分享的表就么多，哇，那也不少啊。当我们查看一个图片的详细信息时，就要显示以上表里的信息。显示图片所属的分类、给图片打的标签、图片的评论、有文件的话还要显示文件下载信息等。难道让我们6个表去关联查询嘛，当然不能这么多关联来查询数据，我们可以只查询一个表即可，这怎么讲？这里分享表是主表，我们可以在主表里建立一个缓存字段。比如我们叫cache_data字段，赋予它text类型，这样可以存储很长的字符串，而不至于超过字段的最大存储。
这个缓存字段怎么用呢？在新增一条分享信息后，产生分享ID。如果用户发布图片或文件的话，图片信息入图片表，文件信息入文件表，然后把新产生的图片或文件信息写入到缓存字段里。同样的，如果用户有选择分类、打了标签的话，也把相应的信息写入到缓存字段里。对于评论而言，没有必要把全部评论存到缓存字段里，因为你不知道他有多少条记录，可以把最新的10条存到缓存字段里用于显示，这样缓存字段就变成一个二维或三维数组，序列化后存储到分享表里。
array( 'img' = array( name => '123.jpg', url => 'http: //tech.42xiu.com/123.jpg', width => 800, width => 600, ), 'file' = array( name => 'abc.zip', download_url => 'http: //tech.42xiu.com/abc.zip', size => 1.2Mb, ), 'category' = array( 1 => array( id => 5, name => PHP乐知博客 ), 2 => array( id => 6, name => PHP技术博客 ), ), 'tag' => array( tag1 tag2 ...... ), 'message' => array( 1 => array(id, uid, name, content, time), 2 => array(id, uid, name, content, time), 3 => array(id, uid, name, content, time), 4 => array(id, uid, name, content, time), ), ) //比如，上面的数组结构，序列化存入数据库。
UPDATE share SET cache_data=mysql_real_escape_string(serialize($cache_data)) WHERE id=1;这样查询就变得简单了，只需要查询一条就行了，取到缓存字段，把其反序列化，把数组信息提取出来，然后显示到页面。如果是以前那个结构，在几十万的数据量下，估计早崩溃了。数据缓存的方法也许不是最好的，如果你有更好的方法，可以相互学习，相互讨论。
相关推荐：
위 내용은 Mysql 다중 테이블 공동 쿼리 효율성 분석 및 최적화의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!