데이터 베이스 MySQL 튜토리얼 MySQL Order By索引优化方法_MySQL

MySQL Order By索引优化方法_MySQL

Jun 01, 2016 pm 01:23 PM

bitsCN.com 尽管 ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了。

使用索引的MySQL Order By
下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分:

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

不使用索引的MySQL Order By
在另一些情况下,MySQL无法使用索引来满足 ORDER BY,尽管它会使用索引来找到记录来匹配 WHERE 子句。这些情况如下:
* 对不同的索引键做 ORDER BY :
SELECT * FROM t1 ORDER BY key1, key2;
* 在非连续的索引键部分上做 ORDER BY:
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
* 同时使用了 ASC 和 DESC:
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* 用于搜索记录的索引键和做 ORDER BY 的不是同一个:
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* 有很多表一起做连接,而且读取的记录中在 ORDER BY 中的字段都不全是来自第一个非常数的表中(也就是说,在 EXPLAIN 分析的结果中的第一个表的连接类型不是 const)。
* 使用了不同的 ORDER BY 和 GROUP BY 表达式。
* 表索引中的记录不是按序存储。例如,HASH 和 HEAP 表就是这样。

通过执行 EXPLAIN SELECT ... ORDER BY,就知道MySQL是否在查询中使用了索引。如果 Extra 字段的值是 Using filesort,则说明MySQL无法使用索引。详情请看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。当必须对结果进行排序时,MySQL 4.1以前 它使用了以下 filesort 算法:

1. 根据索引键读取记录,或者扫描数据表。那些无法匹配 WHERE 分句的记录都会被略过。
2. 在缓冲中每条记录都用一个‘对'存储了2个值(索引键及记录指针)。缓冲的大小依据系统变量 sort_buffer_size 的值而定。
3. 当缓冲慢了时,就运行 qsort(快速排序)并将结果存储在临时文件中。将存储的块指针保存起来(如果所有的‘对'值都能保存在缓冲中,就无需创建临时文件了)。
4. 执行上面的操作,直到所有的记录都读取出来了。
5. 做一次多重合并,将多达 MERGEBUFF(7)个区域的块保存在另一个临时文件中。重复这个操作,直到所有在第一个文件的块都放到第二个文件了。
6. 重复以上操作,直到剩余的块数量小于 MERGEBUFF2 (15)。
7. 在最后一次多重合并时,只有记录的指针(排序索引键的最后部分)写到结果文件中去。
8. 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作,MySQL将记录指针读取放到一个大的块里,并且使用它来按序读取记录,将记录放到缓冲中。缓冲的大小由系统变量 read_rnd_buffer_size 的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。


这个逼近算法的一个问题是,数据库读取了2次记录:一次是估算 WHERE 分句时,第二次是排序时。尽管第一次都成功读取记录了(例如,做了一次全表扫描),第二次是随机的读取(索引键已经排好序了,但是记录并没有)。在MySQL 4.1 及更新版本中,filesort 优化算法用于记录中不只包括索引键值和记录的位置,还包括查询中要求的字段。这么做避免了需要2次读取记录。改进的 filesort 算法做法大致如下:
1. 跟以前一样,读取匹配 WHERE 分句的记录。
2. 相对于每个记录,都记录了一个对应的;‘元组'信息信息,包括索引键值、记录位置、以及查询中所需要的所有字段。
3. 根据索引键对‘元组'信息进行排序。
4. 按序读取记录,不过是从已经排序过的‘元组'列表中读取记录,而非从数据表中再读取一次。

使用改进后的 filesort 算法相比原来的,‘元组'比‘对'需要占用更长的空间,它们很少正好适合放在排序缓冲中(缓冲的大小是由 sort_buffer_size 的值决定的)。因此,这就可能需要有更多的I/O操作,导致改进的算法更慢。为了避免使之变慢,这种优化方法只用于排序‘元组'中额外的字段的大小总和超过系统变量 max_length_for_sort_data 的情况(这个变量的值设置太高的一个表象就是高磁盘负载低CPU负载)。想要提高 ORDER BY 的速度,首先要看MySQL能否使用索引而非额外的排序过程。如果不能使用索引,可以试着遵循以下策略:
* 增加 sort_buffer_size 的值。
* 增加 read_rnd_buffer_size 的值。
* 修改 tmpdir,让它指向一个有很多剩余空间的专用文件系统。
如果使用MySQL 4.1或更新,这个选项允许有多个路径用循环的格式。各个路径之间在 Unix 上用冒号(':')分隔开来,在 Windows,NetWare以及OS/2 上用分号(';')。可以利用这个特性将负载平均分摊给几个目录。注意:这些路径必须是分布在不同物理磁盘上的目录,而非在同一个物理磁盘上的不同目录。bitsCN.com

본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

핫 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를 무료로 생성하십시오.

뜨거운 도구

메모장++7.3.1

메모장++7.3.1

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

SublimeText3 중국어 버전

SublimeText3 중국어 버전

중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

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

Docker에서 MySQL 메모리 사용을 줄입니다 Docker에서 MySQL 메모리 사용을 줄입니다 Mar 04, 2025 pm 03:52 PM

이 기사는 Docker에서 MySQL 메모리 사용을 최적화합니다. 모니터링 기술 (Docker Stats, Performance Schema, 외부 도구) 및 구성 전략에 대해 설명합니다. 여기에는 Docker 메모리 제한, 스와핑 및 CGroups와 함께 포함됩니다

MySQL의 문제를 해결하는 방법 공유 라이브러리를 열 수 없습니다. MySQL의 문제를 해결하는 방법 공유 라이브러리를 열 수 없습니다. Mar 04, 2025 pm 04:01 PM

이 기사에서는 MySQL의 "공유 라이브러리를 열 수 없음"오류를 다룹니다. 이 문제는 MySQL이 필요한 공유 라이브러리 (.so/.dll 파일)를 찾을 수 없음에서 비롯됩니다. 솔루션은 시스템 패키지 M을 통한 라이브러리 설치 확인과 관련이 있습니다.

Alter Table 문을 사용하여 MySQL에서 테이블을 어떻게 변경합니까? Alter Table 문을 사용하여 MySQL에서 테이블을 어떻게 변경합니까? Mar 19, 2025 pm 03:51 PM

이 기사는 MySQL의 Alter Table 문을 사용하여 열 추가/드롭 테이블/열 변경 및 열 데이터 유형 변경을 포함하여 테이블을 수정하는 것에 대해 설명합니다.

Linux에서 MySQL을 실행합니다 (Phpmyadmin이있는 Podman 컨테이너가 포함되지 않음) Linux에서 MySQL을 실행합니다 (Phpmyadmin이있는 Podman 컨테이너가 포함되지 않음) Mar 04, 2025 pm 03:54 PM

이 기사는 Linux에 MySQL을 직접 설치하는 것과 Phpmyadmin이없는 Podman 컨테이너 사용을 비교합니다. 각 방법에 대한 설치 단계에 대해 자세히 설명하면서 Podman의 격리, 이식성 및 재현성의 장점을 강조하지만 또한

sqlite 란 무엇입니까? 포괄적 인 개요 sqlite 란 무엇입니까? 포괄적 인 개요 Mar 04, 2025 pm 03:55 PM

이 기사는 자체 포함 된 서버리스 관계형 데이터베이스 인 SQLITE에 대한 포괄적 인 개요를 제공합니다. SQLITE의 장점 (단순성, 이식성, 사용 용이성) 및 단점 (동시성 제한, 확장 성 문제)에 대해 자세히 설명합니다. 기음

MacOS에서 여러 MySQL 버전을 실행 : 단계별 가이드 MacOS에서 여러 MySQL 버전을 실행 : 단계별 가이드 Mar 04, 2025 pm 03:49 PM

이 안내서는 Homebrew를 사용하여 MacOS에 여러 MySQL 버전을 설치하고 관리하는 것을 보여줍니다. 홈 브루를 사용하여 설치를 분리하여 갈등을 방지하는 것을 강조합니다. 이 기사에는 설치, 서비스 시작/정지 서비스 및 Best Pra에 대해 자세히 설명합니다

MySQL 연결에 대한 SSL/TLS 암호화를 어떻게 구성합니까? MySQL 연결에 대한 SSL/TLS 암호화를 어떻게 구성합니까? Mar 18, 2025 pm 12:01 PM

기사는 인증서 생성 및 확인을 포함하여 MySQL에 대한 SSL/TLS 암호화 구성에 대해 설명합니다. 주요 문제는 자체 서명 인증서의 보안 영향을 사용하는 것입니다. [문자 수 : 159]

인기있는 MySQL GUI 도구는 무엇입니까 (예 : MySQL Workbench, Phpmyadmin)? 인기있는 MySQL GUI 도구는 무엇입니까 (예 : MySQL Workbench, Phpmyadmin)? Mar 21, 2025 pm 06:28 PM

기사는 MySQL Workbench 및 Phpmyadmin과 같은 인기있는 MySQL GUI 도구에 대해 논의하여 초보자 및 고급 사용자를위한 기능과 적합성을 비교합니다. [159 자].

See all articles