이 기사에서는 주로 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
테이블을 수정합니다. 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中文网(m.sbmmt.com)!