MySQL 쿼리 중 과도한 오프셋이 성능에 영향을 미치는 이유와 최적화 방법에 대한 자세한 설명
- jacklove원래의
- 2018-06-08 17:17:012079검색
mysql 쿼리는 limit 및 offset 매개변수와 결합된 select 명령을 사용하여 지정된 범위의 레코드를 읽습니다. 이 기사에서는 mysql 쿼리 중 과도한 오프셋이 성능에 영향을 미치는 이유와 최적화 방법을 소개합니다.
테스트 데이터 테이블 및 데이터 준비
1. 테이블 생성
CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL COMMENT '姓名', `gender` tinyint(3) unsigned NOT NULL COMMENT '性别', PRIMARY KEY (`id`), KEY `gender` (`gender`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2. 1000000개의 레코드 삽입
<?php
$pdo = new PDO("mysql:host=localhost;dbname=user","root",'');for($i=0; $i<1000000; $i++){ $name = substr(md5(time().mt_rand(000,999)),0,10); $gender = mt_rand(1,2); $sqlstr = "insert into member(name,gender) values('".$name."','".$gender."')"; $stmt = $pdo->prepare($sqlstr); $stmt->execute();}
?>mysql> select count(*) from member;
+----------+| count(*) |
+----------+| 1000000 |
+----------+1 row in set (0.23 sec)
3. mysql> select version();
+-----------+| version() |
+-----------+| 5.6.24 |
+-----------+1 row in set (0.01 sec)
분석해 보세요 과도한 오프셋이 성능에 영향을 미치는 이유
1. 오프셋이 작을 때mysql> select * from member where gender=1 limit 10,1;
+----+------------+--------+| id | name | gender |
+----+------------+--------+| 26 | 509e279687 | 1 |
+----+------------+--------+1 row in set (0.00 sec)mysql> select * from member where gender=1 limit 100,1;
+-----+------------+--------+| id | name | gender |
+-----+------------+--------+| 211 | 07c4cbca3a | 1 |
+-----+------------+--------+1 row in set (0.00 sec)mysql> select * from member where gender=1 limit 1000,1;
+------+------------+--------+| id | name | gender |
+------+------------+--------+| 1975 | e95b8b6ca1 | 1 |
+------+------------+--------+1 row in set (0.00 sec)
오프셋이 작을수록 쿼리 속도가 빠르고 효율성이 높습니다.
2. 오프셋이 큰 경우mysql> select * from member where gender=1 limit 100000,1;
+--------+------------+--------+| id | name | gender |
+--------+------------+--------+| 199798 | 540db8c5bc | 1 |
+--------+------------+--------+1 row in set (0.12 sec)mysql> select * from member where gender=1 limit 200000,1;
+--------+------------+--------+| id | name | gender |
+--------+------------+--------+| 399649 | 0b21fec4c6 | 1 |
+--------+------------+--------+1 row in set (0.23 sec)mysql> select * from member where gender=1 limit 300000,1;
+--------+------------+--------+| id | name | gender |
+--------+------------+--------+| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+1 row in set (0.31 sec)
오프셋이 커지면 효율성 문제가 발생하므로 실행 효율성이 떨어집니다.
성능에 영향을 미치는 이유 분석 select * from member where gender=1 limit 300000,1;
데이터 테이블이
InnoDB이므로 InnoDB 인덱스 구조에 따라 쿼리 프로세스는 다음과 같습니다.
- 보조 색인(모든 성별 찾기 = 1 ID)
- 그 다음 찾은 기본키 값을 기준으로 기본키 인덱스를 통해 해당 데이터 블록을 찾습니다(id를 기준으로 해당 데이터 블록 내용 찾기).
- 오프셋 값에 따라 기본 키 인덱스의 데이터를 300001번 쿼리하고, 마지막으로 이전 300000 항목을 버리고 마지막 항목을 꺼냅니다.
기본 키 인덱스를 찾은 후 오프셋 처리를 먼저 수행하고 300000개의 레코드를 건너뛴 다음 300001번째 레코드의 기본 키 인덱스를 통해 데이터 블록을 읽으면 효율성이 향상됩니다.
기본 키만 쿼리하면 차이점이 무엇인지 확인해보세요
mysql> select id from member where gender=1 limit 300000,1; +--------+| id | +--------+| 599465 | +--------+1 row in set (0.09 sec)
분명히 기본 키만 쿼리하면 모든 필드를 쿼리하는 것보다 실행 효율성이 크게 향상됩니다.
기본 키만 쿼리하는 것으로 추측됩니다
보조 인덱스는 이미 기본 키 값을 찾았고 쿼리는 기본 키만 읽으면 되므로 mysql은 먼저 오프셋 작업을 수행한 후 후속 기본 키 인덱스를 기반으로 데이터 블록을 읽습니다.
보조 인덱스는 기본 키 값만 찾기 때문에 다른 필드의 값은 데이터 블록에서 읽어서 얻어야 합니다. 따라서 mysql은 먼저 데이터 블록 내용을 읽은 다음 오프셋 작업을 수행하고 마지막으로 건너뛰어야 하는 이전 데이터를 버리고 후속 데이터를 반환합니다.
확인됨 InnoDB에는 데이터 페이지 및 인덱스 페이지를 포함하여 최근에 액세스한 데이터 페이지를 저장하는
버퍼 풀이 있습니다. 테스트를 위해 먼저 mysql을 재시작한 후 버퍼 풀의 내용을 확인하세요.
mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name; Empty set (0.04 sec)
다시 시작한 후에는 데이터 페이지에 액세스하지 않은 것을 볼 수 있습니다.
모든 필드를 쿼리한 후 버퍼 풀의 내용을 확인합니다mysql> select * from member where gender=1 limit 300000,1;
+--------+------------+--------+| id | name | gender |
+--------+------------+--------+| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+1 row in set (0.38 sec)mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
+------------+----------+| index_name | count(*) |
+------------+----------+| gender | 261 || PRIMARY | 1385 |
+------------+----------+2 rows in set (0.06 sec)
여기서 버퍼 풀에 멤버 테이블에 대한 데이터 페이지가
데이터 페이지와 261인덱스 페이지가 있음을 알 수 있습니다. 시간.
mysql을 다시 시작하여 버퍼 풀을 비우고 계속해서 기본 키만 쿼리하도록 테스트합니다mysql> select id from member where gender=1 limit 300000,1;
+--------+| id |
+--------+| 599465 |
+--------+1 row in set (0.08 sec)mysql> select index_name,count(*) from information_schema.INNODB_BUFFER_PAGE where INDEX_NAME in('primary','gender') and TABLE_NAME like '%member%' group by index_name;
+------------+----------+| index_name | count(*) |
+------------+----------+| gender | 263 || PRIMARY | 13 |
+------------+----------+2 rows in set (0.04 sec)
멤버 테이블에는
데이터 페이지와 263인덱스 페이지만 있는 것을 알 수 있습니다. 현재 버퍼 풀에 있습니다. 따라서 기본 키 인덱스를 통해 데이터 블록에 접근하기 위한 다중 I/O 작업이 줄어들고 실행 효율성이 향상됩니다. 그래서
mysql 쿼리 시 과도한 오프셋이 성능에 영향을 미치는 이유는 기본 키 인덱스를 통해 데이터 블록에 접근하는 다중 I/O 작업 때문임을 확인할 수 있습니다. (InnoDB에만 이 문제가 있으며 MYISAM 인덱스 구조는 InnoDB와 다릅니다. 보조 인덱스는 데이터 블록을 직접 가리키므로 이러한 문제는 없습니다).
InnoDB와 MyISAM 엔진의 인덱스 구조 비교 차트
최적화 방법
위의 분석에 따르면, 모든 필드를 쿼리하면 기본 필드에 의한 I/O가 발생한다는 것을 알고 있습니다. 데이터 블록에 여러 번 액세스하는 키 인덱스입니다.
그래서 먼저 오프셋 기본 키를 찾은 다음 기본 키 인덱스를 기반으로 데이터 블록의 모든 내용을 쿼리하여 최적화합니다.mysql> select a.* from member as a inner join (select id from member where gender=1 limit 300000,1) as b on a.id=b.id;
+--------+------------+--------+| id | name | gender |
+--------+------------+--------+| 599465 | f48375bdb8 | 1 |
+--------+------------+--------+1 row in set (0.08 sec)
이 글에서는 MySQL 쿼리 시 과도한 오프셋이 성능에 영향을 미치는 이유와 최적화 방법에 대해 설명합니다. 자세한 내용은 PHP 중국어 웹사이트를 참조하세요.
관련 추천:
일반 PHP를 사용하여 너비 및 높이 스타일을 제거하는 방법에 대하여
위 내용은 MySQL 쿼리 중 과도한 오프셋이 성능에 영향을 미치는 이유와 최적화 방법에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!