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 인덱스 구조에 따라 쿼리 프로세스는 다음과 같습니다.
기본 키 인덱스를 찾은 후 오프셋 처리를 먼저 수행하고 300000개의 레코드를 건너뛴 다음 300001번째 레코드의 기본 키 인덱스를 통해 데이터 블록을 읽으면 효율성이 향상됩니다.
기본 키만 쿼리하면 차이점이 무엇인지 확인해보세요
mysql> select id from member where gender=1 limit 300000,1; +--------+| id | +--------+| 599465 | +--------+1 row in set (0.09 sec)
분명히 기본 키만 쿼리하면 모든 필드를 쿼리하는 것보다 실행 효율성이 크게 향상됩니다.
보조 인덱스는 이미 기본 키 값을 찾았고 쿼리는 기본 키만 읽으면 되므로 mysql은 먼저 오프셋 작업을 수행한 후 후속 기본 키 인덱스를 기반으로 데이터 블록을 읽습니다.
보조 인덱스는 기본 키 값만 찾기 때문에 다른 필드의 값은 데이터 블록에서 읽어서 얻어야 합니다. 따라서 mysql은 먼저 데이터 블록 내용을 읽은 다음 오프셋 작업을 수행하고 마지막으로 건너뛰어야 하는 이전 데이터를 버리고 후속 데이터를 반환합니다.
이 있습니다. 테스트를 위해 먼저 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 엔진의 인덱스 구조 비교 차트
최적화 방법
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 중국어 웹사이트의 기타 관련 기사를 참조하세요!