MySQL 수천만 개의 빠른 페이징을 최적화하는 방법

MySQL 데이터베이스 최적화 처리로 수천만 건의 빠른 페이징 분석이 가능하다는 사실을 살펴보겠습니다.
데이터 테이블 수집(id, title, info, vtype)에는 제목이 고정 길이, 정보가 텍스트, id가 점진적, vtype이tinyint, vtype이 인덱스인 4개의 필드가 있습니다. 이것은 기본 뉴스 시스템의 간단한 모델입니다. 이제 데이터로 채우고 100,000개의 뉴스 항목으로 채우세요.
최종 수집 레코드는 100,000개이며, 데이터베이스 테이블은 1.6G의 하드 디스크를 차지합니다. 좋습니다. 다음 SQL 문을 살펴보세요.
select id,title from Collect Limit 1000,10; 매우 빠릅니다. 기본적으로 0.01초면 OK입니다. 다음을 보세요.
select id,title from Collect Limit 90000,10 ; 페이징은 90,000개 항목부터 시작됩니다. 결과는 무엇입니까?
8~9초 안에 완료, 맙소사 무슨 일이냐? ? ? ? 실제로 이 데이터를 최적화하고 싶다면 온라인에서 답을 찾을 수 있습니다. 다음 문장을 보세요:
id 제한 90000,10으로 수집 주문에서 id를 선택하세요. 매우 빠릅니다. 0.04초 안에 괜찮습니다. 왜? 물론 인덱싱에 id 기본 키를 사용하는 것이 더 빠르기 때문입니다. 온라인 수정은 다음과 같습니다.
select id,title from Collect where id>=(select id from Collect Order by ID Limit 90000,1) Limit 10;
이는 인덱싱에 id를 사용한 결과입니다. 하지만 문제가 조금만 복잡해지면 끝입니다. 다음 설명을 보세요.
select id from Collect where vtype=1 order by idlimit 90000,10; 매우 느립니다. 8-9초가 걸렸습니다!
이쯤 되면 저처럼 무너지는 기분을 느끼시는 분들이 많을 거라 믿습니다! vtype이 색인화되어 있나요? 왜 그렇게 느린가요? vtype을 색인화하는 것이 좋습니다. vtype=1 제한 1000,10인 곳에서 id를 직접 선택할 수 있습니다. 이는 기본적으로 0.05초로 매우 빠르지만 90,000부터 시작하여 0.05*로 늘릴 수 있습니다. 90=4.5초 속도. 그리고 테스트 결과는 8~9초 정도입니다. 여기서부터 누군가가 discuz 포럼과 같은 아이디어인 하위 테이블에 대한 아이디어를 내놓기 시작했습니다. 아이디어는 다음과 같습니다.
인덱스 테이블을 구축합니다: t(id, title, vtype) 및 고정 길이로 설정한 다음 페이징을 수행하고 결과를 페이징한 다음 정보를 찾기 위해 수집으로 이동합니다. 가능합니까? 실험을 통해 알게 될 것입니다.
t(id,title,vtype)에는 100,000개의 레코드가 저장되며, 데이터 테이블 크기는 약 20M입니다.
t에서 id 선택(vtype=1, id 제한 90000,10으로 주문)을 사용하세요. 기본적으로 0.1~0.2초 안에 실행이 가능합니다. 왜 이런 일이 발생합니까? 아무래도 수집되는 데이터가 너무 많아서 페이징에 시간이 오래 걸리기 때문인 것 같습니다. 제한은 데이터 테이블의 크기와 완전히 관련됩니다. 사실 이것은 여전히 ​​전체 테이블 스캔입니다. 단지 데이터의 양이 적고 100,000개만 더 빠르기 때문입니다. 자, 미친 실험을 해보고 이를 100만 개에 추가하고 성능을 테스트해 보겠습니다.
데이터를 10배 추가한 후 t 테이블은 즉시 200M가 넘었고 고정된 길이였습니다. 이제 쿼리 문을 완료하는 데 0.1~0.2초가 걸립니다! 하위 테이블 성능에 문제는 없나요? 잘못된! 우리의 한도는 여전히 90,000이기 때문에 빠릅니다. 900,000부터 시작하세요
vtype=1인 t에서 id를 선택하세요. id 제한 900000,10으로 주문하세요. 결과를 보세요. 시간은 1-2초입니다!
왜?? 시간표를 나눠도 시간이 너무 길어서 너무 답답해요! 어떤 사람들은 길이를 고정하면 리미트 성능이 좋아진다고 하는데, 처음에는 레코드의 길이가 고정되어 있어서 MySQL이 900,000 위치까지 계산할 수 있어야 한다고 생각했습니다. 그러나 우리는 MySQL의 지능을 과대평가했습니다. 이는 비즈니스 데이터베이스가 아닙니다. 고정 길이와 비고정 길이가 한계에 거의 영향을 미치지 않는다는 사실이 입증되었습니다. 어떤 사람들은 discuz가 100만 개의 레코드에 도달하면 속도가 매우 느려질 것이라고 말하는 것이 당연합니다. 이는 데이터베이스 설계와 관련이 있습니다.
MySQL은 100만개 한계를 돌파할 수 없을까? ? ? 100만 페이지에 도달하면 정말 한계인가요? ? ?
답은 NO!!!! 100만을 넘지 못하는 이유는 mysql을 설계할 줄 모르기 때문이다. 논테이블 방식을 소개하고 미친 테스트를 해보자! 하나의 테이블에서 100만 개의 레코드와 10G 데이터베이스를 처리할 수 있습니다. 페이지를 빠르게 매기는 방법!
자, 테스트가 수집 테이블로 돌아왔습니다. 결론은: 300,000 데이터, 분할 테이블 방법을 사용하면 300,000을 초과하면 속도가 견딜 수 없을 것입니다! 물론, 서브테이블+나의 방법을 사용하면 완전 완벽해지겠지만요. 그런데 제 방법을 사용하고 나면 테이블을 나누지 않고도 완벽하게 해결할 수 있어요!
답은 바로 복합지수입니다! 한번은 mysql 인덱스를 설계할 때 인덱스 이름을 임의로 선택할 수 있고 여러 필드를 선택하여 입력할 수 있다는 것을 우연히 발견했습니다. Collect Order by ID Limit 90000,10의 초기 선택 ID는 인덱스를 사용하기 때문에 매우 빠르지만, 여기에 추가하면 인덱스가 사용되지 않습니다. 한번 해보자는 생각으로 search(vtype,id) 같은 인덱스를 추가했습니다. 그런 다음
vtype=1 제한 90000,10인 수집에서 ID를 선택하세요. 0.04초만에 완료!
다시 테스트: vtype=1 제한 90000,10인 수집에서 ID, 제목을 선택하세요. 죄송합니다. 8~9초가 소요되며 검색 색인이 없습니다.
다시 테스트해 보세요. 검색(id,vtype) 또는 select id 문도 매우 아쉽습니다. 0.5초.
정리하자면 where 조건이 있고 인덱싱에 제한을 사용하려면 인덱스를 디자인해야 하고 where를 먼저 넣고 제한에 사용되는 기본 키를 두 번째로 넣고 기본 키만 선택할 수 있습니다!
페이징 문제를 완벽하게 해결했습니다. ID를 빠르게 반환할 수 있다면 한도 최적화를 기대할 수 있습니다. 이 논리에 따르면 백만 레벨 한도는 0.0x초로 나누어야 합니다. mysql 문의 최적화와 인덱싱이 매우 중요한 것 같습니다!
자, 원래 질문으로 돌아가서, 위의 연구를 어떻게 성공적이고 신속하게 개발에 적용할 수 있을까요? 복합 쿼리를 사용하면 경량 프레임워크는 쓸모가 없게 됩니다. 페이징 문자열을 직접 작성해야 하는데 그게 얼마나 번거롭나요? 여기에 또 다른 예가 있는데 아이디어가 나옵니다.
select * from Collect where id in (9000,12,50,7000); 0초 안에 확인할 수 있습니다!
mygod, mysql의 인덱스는 in문에도 효과적이에요! 인덱싱이 불가능하다는 인터넷의 말이 잘못된 것 같습니다!
이러한 결론에 따르면 경량 프레임워크에 쉽게 적용할 수 있습니다.
코드는 다음과 같습니다.
$db=dblink();
$db->pagesize=20;
$sql="vtype=$vtype";
$db->execute($sql);
$strpage=$db->strpage() //Paging 문자열은 다음과 같습니다. 쉬운 출력을 위해 임시 변수에 저장
while($rs=$db->fetch_array()){
$strid.=$rs['id'].',';
}
$strid=substr($strid,0,strlen($strid)-1); //id 문자열 구성
$db->pagesize=0 //매우 중요, 클래스를 로그아웃하지 마세요. 이 경우 데이터베이스 연결을 한 번만 사용하고 다시 열 필요가 없도록 페이징을 지우십시오.
$db->execute("select id,title,url,sTime,gTime, vtype,tag from Collect where id in ($strid)");
fetch_array()): ?>

 < ;?php echo $rs['id'];?>
 
 
 
 
 < ;a href="http://www.php1.cn/">  
< /tr>


echo $strpage;
간단한 변환을 통해 , 아이디어는 실제로 매우 간단합니다. 1) 인덱스를 최적화하여 ID를 찾아 "123,90000,12000"과 같은 문자열로 철자를 입력합니다. 2) 두 번째 쿼리는 결과를 찾습니다.
작은 인덱스 + 약간의 변경으로 mysql은 수백만 또는 수천만 개의 효율적인 페이징을 지원할 수 있습니다!
여기의 예를 통해 저는 다음과 같은 점을 생각해 보았습니다. 대규모 시스템의 경우 PHP는 프레임워크, 특히 SQL 문도 볼 수 없는 프레임워크를 사용해서는 안 됩니다! 내 경량 프레임워크가 처음에는 거의 무너질 뻔했기 때문입니다! ERP, OA, 대규모 웹사이트의 경우 로직 레이어를 포함한 데이터 레이어에서는 프레임워크를 사용할 수 없습니다. 프로그래머가 SQL 문에 대한 통제력을 잃으면 프로젝트의 위험이 기하급수적으로 증가합니다! 특히 mysql을 사용할 때, mysql이 최고의 성능을 발휘하려면 전문 DBA가 필요합니다. 인덱스로 인한 성능 차이는 수천 배에 이를 수 있습니다!
PS: 실제 테스트 결과 100만 데이터, 160만 데이터, 15G 테이블, 190M 인덱스의 경우 인덱스를 사용해도 제한은 0.49초입니다. 따라서 페이징 중에 100,000개의 데이터 이후에는 다른 사람이 데이터를 볼 수 없도록 하는 것이 가장 좋습니다. 그렇지 않으면 속도가 매우 느려질 것입니다! 인덱스를 사용해도 마찬가지입니다. 이러한 최적화 이후 MySQL은 수백만 페이지의 한계에 도달했습니다! 그러나 그러한 결과는 이미 매우 좋습니다. sqlserver를 사용하고 있다면 확실히 멈출 것입니다! 160만 개의 데이터에 대해 id in(str)을 사용하면 기본적으로 0초로 매우 빠릅니다. 그렇다면 MySQL은 수천만 건의 데이터를 쉽게 처리할 수 있어야 합니다.