찾다
데이터 베이스MySQL 튜토리얼수백만 개의 데이터에 대한 MySQL 페이징 쿼리 방법 및 최적화 제안

수백만 개의 데이터에 대한 MySQL 페이징 쿼리 방법 및 최적화 제안

데이터베이스 SQL 최적화는 수백만 개의 데이터 볼륨이 포함된 페이징 쿼리에 직면했을 때 어떤 좋은 최적화 제안이 있습니까? 참조 및 학습을 위해 일반적으로 사용되는 일부 방법이 아래에 나열되어 있습니다!

방법 1: 데이터베이스에서 제공하는 SQL 문을 직접 사용

  • 문 스타일: MySQL에서는 다음 방법을 사용할 수 있습니다. SELECT * FROM 테이블 이름 LIMIT M,N
  • 적응형 시나리오: 적합 소량의 데이터(수백/수천 레벨의 튜플)
  • 원인/단점: 전체 테이블 스캔이 매우 느리고 일부 데이터베이스 결과 세트가 불안정하게 반환됩니다(예를 들어 한 번은 1, 2, 3을 반환하고 다른 번은 반환함). 2, 1,3) 제한은 결과 집합의 M 위치에서 N개의 출력을 가져오고 나머지는 삭제하는 것입니다.

방법 2: 기본 키 또는 고유 인덱스를 만들고 인덱스를 사용합니다(가정) 페이지당 항목 10개)

  • 문 스타일: MySQL에서는 다음 방법을 사용할 수 있습니다. SELECT * FROM table name WHERE id_pk > (pageNum*10) LIMIT M
  • 적응형 시나리오: 대용량 상황에 적합 데이터 수(수만 개의 튜플)
  • 이유: 인덱스 스캔이 매우 빠릅니다. 친구가 지적한 바: 데이터 쿼리가 pk_id에 따라 정렬되지 않기 때문에 데이터가 누락되는 경우가 있습니다. 유일한 방법은 방법 3입니다.

방법 3: 인덱스 정렬 기준

  • 문 스타일: MySQL에서는 다음 방법을 사용할 수 있습니다. SELECT * FROM table name WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
  • 적응형 시나리오: 데이터 양이 많은 상황에 적합합니다(요소 그룹 수가 수만 개임). ORDER BY 뒤의 열 개체가 기본 키이거나 고유하므로 ORDER BY 작업이 수행되는 것이 가장 좋습니다. 인덱스를 사용하여 제거할 수 있지만 결과 집합은 안정적입니다(안정성의 의미는 방법 1 참조)
  • 원인: 인덱스 스캔, 속도가 매우 빠르지만 MySQL의 정렬 작업에는 DESC가 없고 ASC만 있습니다. 가짜, 진짜 DESC는 미래에 이루어질 것입니다. 기대합니다...).

방법 4: 인덱스 기반 준비 사용

첫 번째 물음표는 pageNum을 나타내고 두 번째는? 페이지당 튜플 수를 나타냅니다.

  • 문 스타일: MySQL에서는 다음 방법을 사용할 수 있습니다: PREPARE stmt_name FROM SELECT * FROM table name WHERE id_pk > (?*?) ORDER BY id_pk ASC LIMIT M
  • Adapt to 시나리오: 대용량 데이터
  • 이유: 인덱스 스캔이 일반 쿼리 문보다 매우 빠릅니다.

방법 5: MySQL을 사용하여 ORDER 작업을 지원하면 인덱스를 사용하여 일부 튜플을 빠르게 찾고 전체 테이블 스캔을 피할 수 있습니다.

예: 1000~1019행의 튜플 읽기(pk가 기본임) 키/고유 키)

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

방법 6: 하위 쿼리/조인 + 인덱스를 사용하여 튜플을 빠르게 찾은 다음 튜플을 읽습니다.

예를 들어(id는 기본 키/고유 키, 파란색 글꼴) 시간 변수)

하위 쿼리 사용 예:

SELECT * FROM your_table WHERE id <=
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc
LIMIT $pagesize

연결 사용 예:

SELECT * FROM your_table AS t1
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

MySQL은 대용량 데이터에 대해 제한 페이징을 사용합니다.

테스트 실험

1. 내 프로그램에서도 사용하는 방법인 Limit start, count paging 문을 직접 사용합니다.

select * from product limit start, count

시작 페이지가 작을 때는 성능 문제가 없습니다. 10, 100, 1000, 10000부터 시작하는 페이징 실행 시간(페이지당 20개 항목)을 별도로 살펴보겠습니다.

다음과 같습니다.

select * from product limit 10, 20 0.016秒
select * from product limit 100, 20 0.016秒
select * from product limit 1000, 20 0.047秒
select * from product limit 10000, 20 0.094秒

시작 레코드가 증가할수록 시간도 증가하는 것을 확인했습니다. 이는 페이징 문 제한이 시작 페이지 번호와 밀접한 관련이 있음을 보여주기 때문에 시작 레코드를 다음과 같이 변경합니다. 40w (즉, 평균 기록 정도)

select * from product limit 400000, 20 3.229秒

기록의 마지막 페이지를 가져오는 데 걸린 시간을 보세요

select * from product limit 866613, 20 37.44秒

이렇게 페이지 수가 가장 많은 페이지에서는 당연히 이런 시간은 견딜 수 없습니다.

이로부터 두 가지 결론을 내릴 수도 있습니다.

  1. 제한 문의 쿼리 시간은 시작 레코드의 위치에 비례합니다.
  2. MySQL의 제한 문은 매우 편리하지만 테이블에 직접 사용하기에는 적합하지 않습니다. 많은 기록.

2. 페이징 제한 문제에 대한 성능 최적화 방법

페이징 쿼리 속도를 높이려면 테이블의 커버링 인덱스를 사용하세요

인덱스 쿼리를 사용하는 문에 해당 인덱스 열(커버링 인덱스)만 포함되어 있다면 우리는 모두 알고 있습니다. ), 이 경우 신속하게 쿼리됩니다.

인덱스 검색에는 최적화 알고리즘이 있고, 쿼리 인덱스에 데이터가 있기 때문에 해당 데이터 주소를 찾을 필요가 없어 시간이 많이 절약됩니다. 게다가 MySQL에도 관련 인덱스 캐시가 있는데, 동시성이 높을 때 캐시를 사용하는 것이 좋습니다.

이 예에서는 id 필드가 기본 키라는 것을 알고 있으므로 기본 기본 키 인덱스가 자연스럽게 포함됩니다. 이제 포함 인덱스를 사용하는 쿼리가 어떻게 수행되는지 살펴보겠습니다.

이번에는 다음과 같이 마지막 페이지의 데이터를 쿼리합니다(id 열만 포함하는 커버링 인덱스 사용).

select id from product limit 866613, 20 0.2秒

모든 열을 쿼리하는 데 걸리는 37.44초에 비해 속도는 약 100배 향상됩니다

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join,看下实际情况:

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

查询时间为0.2秒!

另一种写法

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

查询时间也很短!

3. 复合索引优化方法

MySql 性能到底能有多高?MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇新闻的小型系统怎么写都可以,用xx框架可以实现快速开发。可是数据量到了10万,百万至千万,他的性能还能那么高吗?一点小小的失误,可能造成整个系统的改写,甚至更本系统无法正常运行!好了,不那么多废话了。

用事实说话,看例子:

数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据,填充10万篇新闻。最后collect 为 10万条记录,数据库表占用硬1.6G。

OK ,看下面这条sql语句:

select id,title from collect limit 1000,10;

很快;基本上0.01秒就OK,再看下面的

select id,title from collect limit 90000,10;

从9万条开始分页,结果?

8-9秒完成,my god 哪出问题了?其实要优化这条数据,网上找得到答案。看下面一条语句:

select id from collect order by id limit 90000,10;

很快,0.04秒就OK。为什么?因为用了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 id limit 90000,10;

很慢,用了8-9秒!

到了这里我相信很多人会和我一样,有崩溃感觉!vtype 做了索引了啊?怎么会慢呢?vtype做了索引是不错,你直接

select id from collect where vtype=1 limit 1000,10;

是很快的,基本上0.05秒,可是提高90倍,从9万开始,那就是0.05*90=4.5秒的速度了。和测试结果8-9秒到了一个数量级。

从这里开始有人提出了分表的思路,这个和dis #cuz 论坛是一样的思路。思路如下:

建一个索引表:t (id,title,vtype) 并设置成定长,然后做分页,分页出结果再到 collect 里面去找info 。是否可行呢?实验下就知道了。

10万条记录到 t(id,title,vtype) 里,数据表大小20M左右。用

select id from collect where vtype=1 limit 1000,10;

很快了。基本上0.1-0.2秒可以跑完。为什么会这样呢?我猜想是因为collect 数据太多,所以分页要跑很长的路。limit 完全和数据表的大小有关的。其实这样做还是全表扫描,只是因为数据量小,只有10万才快。OK, 来个疯狂的实验,加到100万条,测试性能。加了10倍的数据,马上t表就到了200多M,而且是定长。还是刚才的查询语句,时间是0.1-0.2秒完成!分表性能没问题?

错!因为我们的limit还是9万,所以快。给个大的,90万开始

select id from t where vtype=1 order by id limit 900000,10;

看看结果,时间是1-2秒!why ?

分表了时间还是这么长,非常之郁闷!有人说定长会提高limit的性能,开始我也以为,因为一条记录的长度是固定的,mysql 应该可以算出90万的位置才对啊?可是我们高估了mysql 的智能,他不是商务数据库,事实证明定长和非定长对limit影响不大?怪不得有人说discuz到了100万条记录就会很慢,我相信这是真的,这个和数据库设计有关!

难道MySQL 无法突破100万的限制吗???到了100万的分页就真的到了极限?

答案是:NO 为什么突破不了100万是因为不会设计mysql造成的。下面介绍非分表法,来个疯狂的测试!一张表搞定100万记录,并且10G 数据库,如何快速分页!

好了,我们的测试又回到 collect表,开始测试结论是:

30万数据,用分表法可行,超过30万他的速度会慢道你无法忍受!当然如果用分表+我这种方法,那是绝对完美的。但是用了我这种方法后,不用分表也可以完美解决!

答案就是:复合索引!有一次设计mysql索引的时候,无意中发现索引名字可以任取,可以选择几个字段进来,这有什么用呢?

开始的

select id from collect order by id limit 90000,10;

这么快就是因为走了索引,可是如果加了where 就不走索引了。抱着试试看的想法加了 search(vtype,id) 这样的索引。

然后测试

select id from collect where vtype=1 limit 90000,10;

非常快!0.04秒完成!

再测试:

select id ,title from collect where vtype=1 limit 90000,10;

非常遗憾,8-9秒,没走search索引!

再测试:search(id,vtype),还是select id 这个语句,也非常遗憾,0.5秒。

综上:如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!

完美解决了分页问题了。可以快速返回id就有希望优化limit , 按这样的逻辑,百万级的limit 应该在0.0x秒就可以分完。看来mysql 语句的优化和索引时非常重要的!

추천: "mysql 튜토리얼"

위 내용은 수백만 개의 데이터에 대한 MySQL 페이징 쿼리 방법 및 최적화 제안의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명
이 기사는 脚本之家에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제
MySQL : 초보자가 마스터하는 필수 기술MySQL : 초보자가 마스터하는 필수 기술Apr 18, 2025 am 12:24 AM

MySQL은 초보자가 데이터베이스 기술을 배우는 데 적합합니다. 1. MySQL 서버 및 클라이언트 도구를 설치하십시오. 2. SELECT와 같은 기본 SQL 쿼리를 이해하십시오. 3. 마스터 데이터 작업 : 데이터를 만들고, 삽입, 업데이트 및 삭제합니다. 4. 고급 기술 배우기 : 하위 쿼리 및 창 함수. 5. 디버깅 및 최적화 : 구문 확인, 인덱스 사용, 선택*을 피하고 제한을 사용하십시오.

MySQL : 구조화 된 데이터 및 관계형 데이터베이스MySQL : 구조화 된 데이터 및 관계형 데이터베이스Apr 18, 2025 am 12:22 AM

MySQL은 테이블 구조 및 SQL 쿼리를 통해 구조화 된 데이터를 효율적으로 관리하고 외래 키를 통해 테이블 ​​간 관계를 구현합니다. 1. 테이블을 만들 때 데이터 형식을 정의하고 입력하십시오. 2. 외래 키를 사용하여 테이블 간의 관계를 설정하십시오. 3. 인덱싱 및 쿼리 최적화를 통해 성능을 향상시킵니다. 4. 데이터 보안 및 성능 최적화를 보장하기 위해 데이터베이스를 정기적으로 백업 및 모니터링합니다.

MySQL : 주요 기능 및 기능이 설명되었습니다MySQL : 주요 기능 및 기능이 설명되었습니다Apr 18, 2025 am 12:17 AM

MySQL은 웹 개발에 널리 사용되는 오픈 소스 관계형 데이터베이스 관리 시스템입니다. 주요 기능에는 다음이 포함됩니다. 1. 다른 시나리오에 적합한 InnoDB 및 MyISAM과 같은 여러 스토리지 엔진을 지원합니다. 2.로드 밸런싱 및 데이터 백업을 용이하게하기 위해 마스터 슬레이브 복제 기능을 제공합니다. 3. 쿼리 최적화 및 색인 사용을 통해 쿼리 효율성을 향상시킵니다.

SQL의 목적 : MySQL 데이터베이스와 상호 작용합니다SQL의 목적 : MySQL 데이터베이스와 상호 작용합니다Apr 18, 2025 am 12:12 AM

SQL은 MySQL 데이터베이스와 상호 작용하여 데이터 첨가, 삭제, 수정, 검사 및 데이터베이스 설계를 실현하는 데 사용됩니다. 1) SQL은 Select, Insert, Update, Delete 문을 통해 데이터 작업을 수행합니다. 2) 데이터베이스 설계 및 관리에 대한 생성, 변경, 삭제 문을 사용하십시오. 3) 복잡한 쿼리 및 데이터 분석은 SQL을 통해 구현되어 비즈니스 의사 결정 효율성을 향상시킵니다.

초보자를위한 MySQL : 데이터베이스 관리를 시작합니다초보자를위한 MySQL : 데이터베이스 관리를 시작합니다Apr 18, 2025 am 12:10 AM

MySQL의 기본 작업에는 데이터베이스, 테이블 작성 및 SQL을 사용하여 데이터에서 CRUD 작업을 수행하는 것이 포함됩니다. 1. 데이터베이스 생성 : createAbasemy_first_db; 2. 테이블 만들기 : CreateTableBooks (idintauto_incrementprimarykey, titlevarchar (100) notnull, authorvarchar (100) notnull, published_yearint); 3. 데이터 삽입 : InsertIntobooks (Title, Author, Published_year) VA

MySQL의 역할 : 웹 응용 프로그램의 데이터베이스MySQL의 역할 : 웹 응용 프로그램의 데이터베이스Apr 17, 2025 am 12:23 AM

웹 응용 프로그램에서 MySQL의 주요 역할은 데이터를 저장하고 관리하는 것입니다. 1. MySQL은 사용자 정보, 제품 카탈로그, 트랜잭션 레코드 및 기타 데이터를 효율적으로 처리합니다. 2. SQL 쿼리를 통해 개발자는 데이터베이스에서 정보를 추출하여 동적 컨텐츠를 생성 할 수 있습니다. 3.mysql은 클라이언트-서버 모델을 기반으로 작동하여 허용 가능한 쿼리 속도를 보장합니다.

MySQL : 첫 번째 데이터베이스 구축MySQL : 첫 번째 데이터베이스 구축Apr 17, 2025 am 12:22 AM

MySQL 데이터베이스를 구축하는 단계에는 다음이 포함됩니다. 1. 데이터베이스 및 테이블 작성, 2. 데이터 삽입 및 3. 쿼리를 수행하십시오. 먼저 CreateAbase 및 CreateTable 문을 사용하여 데이터베이스 및 테이블을 작성한 다음 InsertInto 문을 사용하여 데이터를 삽입 한 다음 최종적으로 SELECT 문을 사용하여 데이터를 쿼리하십시오.

MySQL : 데이터 저장에 대한 초보자 친화적 인 접근 방식MySQL : 데이터 저장에 대한 초보자 친화적 인 접근 방식Apr 17, 2025 am 12:21 AM

MySQL은 사용하기 쉽고 강력하기 때문에 초보자에게 적합합니다. 1.MySQL은 관계형 데이터베이스이며 CRUD 작업에 SQL을 사용합니다. 2. 설치가 간단하고 루트 사용자 비밀번호를 구성해야합니다. 3. 삽입, 업데이트, 삭제 및 선택하여 데이터 작업을 수행하십시오. 4. Orderby, Where and Join은 복잡한 쿼리에 사용될 수 있습니다. 5. 디버깅은 구문을 확인하고 쿼리를 분석하기 위해 설명을 사용해야합니다. 6. 최적화 제안에는 인덱스 사용, 올바른 데이터 유형 선택 및 우수한 프로그래밍 습관이 포함됩니다.

See all articles

핫 AI 도구

Undresser.AI Undress

Undresser.AI Undress

사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

AI Hentai Generator

AI Hentai Generator

AI Hentai를 무료로 생성하십시오.

뜨거운 도구

MinGW - Windows용 미니멀리스트 GNU

MinGW - Windows용 미니멀리스트 GNU

이 프로젝트는 osdn.net/projects/mingw로 마이그레이션되는 중입니다. 계속해서 그곳에서 우리를 팔로우할 수 있습니다. MinGW: GCC(GNU Compiler Collection)의 기본 Windows 포트로, 기본 Windows 애플리케이션을 구축하기 위한 무료 배포 가능 가져오기 라이브러리 및 헤더 파일로 C99 기능을 지원하는 MSVC 런타임에 대한 확장이 포함되어 있습니다. 모든 MinGW 소프트웨어는 64비트 Windows 플랫폼에서 실행될 수 있습니다.

메모장++7.3.1

메모장++7.3.1

사용하기 쉬운 무료 코드 편집기

WebStorm Mac 버전

WebStorm Mac 버전

유용한 JavaScript 개발 도구

Dreamweaver Mac版

Dreamweaver Mac版

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)