PHP 동적 웹사이트 MySQL 인덱스 분석 및 최적화 가속화

이 기사에서는 주로 MySQL 인덱스 분석을 가속화하고 동적 웹사이트를 최적화하는 방법에 대해 설명합니다.

1. 인덱스란 무엇인가요?

인덱스는 특정 값을 가진 레코드를 빠르게 찾는 데 사용됩니다. 모든 MySQL 인덱스는 B-트리 형태로 저장됩니다. 인덱스가 없는 경우 쿼리를 실행할 때 MySQL은 요구 사항을 충족하는 레코드를 찾을 때까지 첫 번째 레코드부터 시작하여 전체 테이블의 모든 레코드를 검색해야 합니다. 테이블의 레코드 수가 많을수록 이 작업 비용이 높아집니다. 검색 조건으로 사용된 컬럼에 인덱스가 생성되면 MySQL은 레코드를 스캔하지 않고도 대상 레코드의 위치를 ​​빠르게 가져올 수 있습니다. 테이블에 1000개의 레코드가 있는 경우 인덱스를 통해 레코드를 찾는 것이 레코드를 순차적으로 스캔하는 것보다 최소 100배 빠릅니다.

people이라는 테이블을 생성한다고 가정해 보겠습니다.

CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );

그런 다음 1000개의 서로 다른 이름 값을 people 테이블에 완전히 무작위로 삽입합니다. 이름 열에는 데이터 파일에 명시적인 순서가 없습니다. 이름 열에 인덱스를 생성하면 MySQL은 인덱스의 각 항목에 대해 내부적으로 데이터 파일의 실제 레코드 위치에 대한 "포인터"를 저장합니다. 따라서 이름이 "Mike"(SQL 명령은 "SELECT peopleid FROM people WHERE name='Mike';")인 레코드의 peopleid를 찾으려면 MySQL은 "Mike"를 검색할 수 있습니다. 이름에 대한 인덱스 값을 입력한 다음 바로 이동합니다. 데이터 파일의 해당 행은 해당 행의 peopleid(999)를 정확하게 반환합니다. 이 프로세스 동안 MySQL은 결과를 반환하기 위해 하나의 행만 처리하면 됩니다. "이름" 열에 인덱스가 없으면 MySQL은 데이터 파일의 모든 레코드, 즉 1,000개의 레코드를 검색합니다! 분명히 MySQL이 처리해야 하는 레코드 수가 적을수록 작업을 더 빨리 완료할 수 있습니다.

2. 인덱스 유형

MySQL은 다양한 인덱스 유형을 제공합니다.

일반 인덱스:

가장 기본적인 인덱스 유형입니다. , 고유성과 같은 제한이 없습니다. 일반 인덱스는 다음과 같은 방법으로 생성할 수 있습니다.

CREATE INDEX ON tablename(열 목록)과 같은 인덱스를 생성합니다.

다음과 같이 테이블을 수정합니다. ALTER TABLE 테이블 이름 ADD INDEX [인덱스 이름] (열 목록)

테이블을 생성할 때 인덱스를 지정합니다. 예를 들어 CREATE TABLE 테이블 이름 ([...], INDEX [인덱스 이름] ( list of columns));

고유 인덱스:

이런 종류의 인덱스는 기본적으로 이전의 "일반 인덱스"와 동일하지만 한 가지 차이점이 있습니다: 인덱스의 모든 값 열은 한 번만 나타날 수 있습니다. 즉, 고유해야 합니다. 고유 인덱스는 다음과 같은 방법으로 생성할 수 있습니다.

CREATE UNIQUE INDEX ON tablename(열 목록)과 같은 인덱스를 생성합니다.

테이블을 수정합니다. as ALTER TABLE tablename ADD UNIQUE [인덱스 이름] (열 목록)

테이블을 생성할 때 인덱스를 지정합니다. 예를 들어 CREATE TABLE 테이블 이름( [...], UNIQUE [인덱스 이름] (열 목록) );

기본 키:

기본 키는 고유 인덱스이지만 "PRIMARY KEY"로 지정해야 합니다. AUTO_INCREMENT 유형의 열을 사용해 본 적이 있다면 기본 키와 같은 개념에 이미 익숙할 것입니다. 기본 키는 일반적으로 "CREATE TABLE 테이블 이름 ([...], PRIMARY KEY (열 목록));"과 같이 테이블을 생성할 때 지정됩니다. 그러나 "ALTER TABLE 테이블 이름 ADD PRIMARY KEY (열 목록);"과 같이 테이블을 수정하여 기본 키를 추가할 수도 있습니다. 각 테이블에는 기본 키가 하나만 있을 수 있습니다.

전체 텍스트 인덱스:

MySQL은 버전 3.23.23부터 전체 텍스트 인덱싱 및 전체 텍스트 검색을 지원합니다. MySQL에서 전체 텍스트 인덱스의 인덱스 유형은 FULLTEXT입니다. VARCHAR 또는 TEXT 유형 열에 전체 텍스트 인덱스를 만들 수 있습니다. CREATE TABLE 명령이나 ALTER TABLE 또는 CREATE INDEX 명령을 사용하여 생성할 수 있습니다. 대규모 데이터 세트의 경우 ALTER TABLE(또는 CREATE INDEX) 명령을 통해 전체 텍스트 인덱스를 생성하는 것이 전체 텍스트 인덱스가 있는 빈 테이블에 레코드를 삽입하는 것보다 빠릅니다. 이 문서 아래의 논의에서는 더 이상 전체 텍스트 인덱스를 다루지 않습니다. 자세한 내용은 MySQL 설명서를 참조하세요.

3. 단일 열 인덱스와 다중 열 인덱스

인덱스는 단일 열 인덱스일 수도 있고 다중 열 인덱스일 수도 있습니다. 아래에서는 구체적인 예를 사용하여 두 인덱스 간의 차이점을 설명합니다. 이러한 people 테이블이 있다고 가정해 보겠습니다.

CREATE TABLE people ( peopleid 
SMALLINT NOT NULL AUTO_INCREMENT,
firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, 
age SMALLINT NOT NULL,
townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );

다음은 people 테이블에 삽입하는 데이터입니다.

이 데이터 조각에는 "Mikes"라는 이름의 사람이 4명 있습니다. Sullivans 1명, McConnells 2명), 17세 소년 2명, Joe Smith라는 특이한 이름을 가진 사람 1명.

이 테이블의 주요 목적은 지정된 사용자의 성, 이름 및 나이를 기반으로 해당 peopleid를 반환하는 것입니다. 예를 들어, 이름이 Mike Sullivan이고 나이가 17세인 사용자의 peopleid를 찾아야 할 수 있습니다(SQL 명령은 SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17입니다). ;). 쿼리가 실행될 때마다 MySQL이 전체 테이블을 스캔하는 것을 원하지 않기 때문에 여기서는 인덱스를 고려해야 합니다.

首先，我们可以考虑在单个列上创建索引，比如firstname、lastname或者age列。如果我们创建firstname列的索引（ALTER TABLE people ADD INDEX firstname (firstname)；），MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录，然后再在这个“中间结果集”上进行其他条件的搜索：它首先排除那些lastname不等于“Sullivan”的记录，然后排除那些age不等于17的记录。当记录满足所有搜索条件之后，MySQL就返回最终的搜索结果。

由于建立了firstname列的索引，与执行表的完全扫描相比，MySQL的效率提高了很多，但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引，再创建lastname或者age列的索引，但总地看来，不论在哪个列上创建索引搜索效率仍旧相似。

为了提高搜索效率，我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引，MySQL只需一次检索就能够找出正确的结果！下面是创建这个多列索引的SQL命令：

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age)；

由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！

那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

四、最左前缀

多列索引还有另外一个优点，它通过称为最左前缀（Leftmost Prefixing）的概念体现出来。继续考虑前面的例子，现在我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用fname_lname_age索引：

firstname，lastname，age
firstname，lastname
firstname

从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引：

SELECT peopleid FROM people 
WHERE firstname='Mike' AND lastname='Sullivan' AND age='17'; 
SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'; 
SELECT peopleid FROM people WHERE firstname='Mike'; 
The following queries cannot use the index at all: 
SELECT peopleid FROM people WHERE lastname='Sullivan'; 
SELECT peopleid FROM people WHERE age='17'; 
SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17';

五、选择索引列

在性能优化过程中，选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列：在WHERE子句中出现的列，在join子句中出现的列。请看下面这个查询：

SELECT age ## 不使用索引
FROM people WHERE firstname='Mike' ## 考虑使用索引
AND lastname='Sullivan' ## 考虑使用索引

这个查询与前面的查询略有不同，但仍属于简单查询。由于age是在SELECT部分被引用，MySQL不会用它来限制列选择操作。因此，对于这个查询来说，创建age列的索引没有什么必要。下面是一个更复杂的例子：

SELECT people.age, ##不使用索引
town.name ##不使用索引
FROM people LEFT JOIN town ON
people.townid=town.townid ##考虑使用索引
WHERE firstname='Mike' ##考虑使用索引
AND lastname='Sullivan' ##考虑使用索引

与前面的例子一样，由于firstname和lastname出现在WHERE子句中，因此这两个列仍旧有创建索引的必要。除此之外，由于town表的townid列出现在join子句中，因此我们需要考虑创建该列的索引。那么，我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢？差不多如此，但并不完全。我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引：，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符（%或者_）开头的情形。例如，“SELECT peopleid FROM people WHERE firstname LIKE 'Mich%';”这个查询将使用索引，但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引。

以上内容就是加速PHP动态网站 MySQL索引分析和优化，希望可以帮助到大家，想要更多相关内容请关注PHP中文网（www.php.cn）！