MySQL 고급 12 - 인덱스

인덱싱은 빠른 검색의 핵심입니다. MySQL 인덱스의 구축은 MySQL의 효율적인 운영을 위해 매우 중요합니다. 다음은 몇 가지 일반적인 MySQL 인덱스 유형을 소개합니다.

데이터베이스 테이블에서 인덱싱 필드는 쿼리 속도를 크게 향상시킬 수 있습니다. mytable 테이블을 생성한다고 가정해 보겠습니다.

코드는 다음과 같습니다.

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  );

여기에 5555, admin을 포함하여 10,000개의 레코드를 무작위로 삽입합니다.

username="admin"

SELECT * FROM mytable WHERE username='admin';

의 레코드를 찾을 때, username에 인덱스가 설정되어 있으면 MySQL은 아무런 스캔 없이 해당 레코드를 정확하게 찾을 수 있습니다. 반대로 MySQL은 모든 레코드를 검색합니다. 즉, 10,000개의 레코드가 쿼리됩니다.

인덱스는 단일열 인덱스와 결합 인덱스로 구분됩니다. 단일 열 인덱스는 인덱스가 단일 열만 포함한다는 것을 의미합니다. 테이블은 여러 개의 단일 열 인덱스를 가질 수 있지만 이는 결합된 인덱스가 아닙니다. 결합 인덱스, 즉 하나의 인덱스에 여러 열이 포함됩니다.

MySQL 인덱스 유형은 다음과 같습니다.

1. 일반 인덱스

가장 기본적인 인덱스이며 제한이 없습니다. 생성 방법은 다음과 같습니다.

1. 인덱스를 생성합니다

코드는 다음과 같습니다.

CREATE INDEX indexName ON mytable(username(length));

CHAR 또는 VARCHAR 유형인 경우 길이는 다음과 같습니다. 필드의 실제 길이보다 작습니다. BLOB 및 TEXT 유형인 경우 길이를 지정해야 합니다. 아래와 동일합니다.

2. 테이블 구조 수정

코드는 다음과 같습니다.

ALTER mytable ADD INDEX [indexName] ON (username(length)) CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,   
INDEX [indexName] (username(length)));

인덱스 삭제:

DROP INDEX [indexName] ON mytable;

2. 🎜>
인덱스 컬럼의 값이 유일해야 한다는 점만 빼면 이전의 일반 인덱스와 비슷하지만, null값은 허용된다. 복합 인덱스의 경우 컬럼 값의 조합이 고유해야 합니다. 생성 방법은 다음과 같습니다.

코드는 다음과 같습니다.

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ;

테이블 구조 수정

ALTER mytable ADD UNIQUE [indexName] ON (username(length)) ;

테이블 생성 시 직접 지정

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName] (username(length)));

3. 기본 키 인덱스

null 값을 허용하지 않는 특수한 고유 인덱스입니다. 일반적으로 기본 키 인덱스는 테이블 생성과 동시에 생성됩니다.

코드는 다음과 같습니다.

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID));

물론 ALTER 명령을 사용할 수도 있습니다. 기억하세요: 테이블에는 기본 키가 하나만 있을 수 있습니다.

4. 결합 인덱스

단일 컬럼 인덱스와 결합 인덱스를 생생하게 비교하기 위해 테이블에 여러 필드를 추가합니다.

코드는 다음과 같습니다.

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,city VARCHAR(50) NOT NULL,   age INT NOT NULL);

MySQL의 효율성을 더욱 끌어내기 위해서는 통합 인덱스 구축을 고려할 필요가 있습니다. 이름, 도시, 나이를 인덱스로 만드는 것입니다.

코드는 다음과 같습니다.

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

테이블을 생성할 때 사용자 이름 길이는 16이고 여기서는 10이 사용됩니다. . 이는 일반적으로 이름 길이가 10을 초과하지 않으므로 인덱스 쿼리 속도가 빨라지고 인덱스 파일 크기가 줄어들며 INSERT의 업데이트 속도가 향상되기 때문입니다.

사용자 이름, 도시, 연령에 대해 각각 단일 열 인덱스를 생성하여 테이블에 3개의 단일 열 인덱스가 있는 경우 위의 결합 인덱스와 훨씬 낮은 쿼리 효율성이 매우 달라집니다. 우리의 결합 지수보다. 현재 3개의 인덱스가 있지만 MySQL은 가장 효율적이라고 생각되는 단일 열 인덱스만 사용할 수 있습니다.

이러한 통합 인덱스를 구축하는 것은 실제로 다음 세 가지 조합 인덱스 세트를 구축하는 것과 같습니다.

usernname, city, age usernname, city usernname 왜 도시와 나이 같은 통합 인덱스가 없는 걸까요? ? 이는 MySQL 복합 인덱스의 "가장 왼쪽 접두사"의 결과입니다. 간단한 이해는 가장 왼쪽부터 조합을 시작하는 것입니다. 이 세 개의 열을 포함하는 쿼리는 이 결합 인덱스를 사용할 뿐만 아니라 다음 SQL도 이 결합 인덱스를 사용합니다.

SELECT * FROM mytable WHREE username="admin" AND city="郑州"  SELECT * FROM mytable WHREE username="admin";

다음 몇 개는 이를 사용하지 않습니다.

코드는 다음과 같습니다. :

SELECT * FROM mytable WHREE age=20 AND city="郑州"  SELECT * FROM mytable WHREE city="郑州";

5. 인덱스 생성 타이밍

지금까지 인덱스 생성 방법을 배웠는데, 어떤 상황에서 인덱스를 생성해야 할까요? 일반적으로 WHERE 및 JOIN에 나타나는 열은 인덱싱되어야 하지만 MySQL은 23735d90c0339e974c9b8bbfdf9ee4cb, >=, BETWEEN, IN 및 때로는 LIKE 인덱스만 사용하기 때문에 이는 전적으로 사실이 아닙니다. 색인. 예를 들면

코드는 다음과 같습니다.

SELECT t.Name  FROM mytable t LEFT JOIN mytable m    ON t.Name=m.username WHERE m.age=20 AND m.city='郑州';

이때 mytable 테이블의 사용자 이름도 JOIN 절에 나타나므로 도시와 나이를 인덱스해야 합니다. 또한 색인이 필요합니다.

지금 LIKE는 특정 시간에만 인덱싱하면 된다고 말씀드렸습니다. MySQL은 와일드카드 문자 % 및 _로 시작하는 쿼리를 만들 때 인덱스를 사용하지 않기 때문입니다. 예를 들어 다음 문장은 색인을 사용합니다.

코드는 다음과 같습니다.

SELECT * FROM mytable WHERE username like'admin%';

다음 문장은 색인을 사용하지 않습니다.

코드는 다음과 같습니다. 다음과 같습니다:

SELECT * FROM mytable WHEREt Name like'%admin';

因此，在使用LIKE时应注意以上的区别。

六、索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

七、使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

1.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2.使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4.like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5.不要在列上进行运算

代码如下:

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:

代码如下:

select * from users where adddate<‘2007-01-01&#39;;

6.不使用NOT IN和a8093152e673feb7aba1828c43532094操作

 以上就是MySQL高级十二——索引的内容，更多相关内容请关注PHP中文网（www.php.cn）！