집 >백엔드 개발 >PHP 튜토리얼 >인덱스를 다루는 MySQL 인덱스 최적화

인덱스를 다루는 MySQL 인덱스 최적화

小云云원래의: 2018-03-22 09:29:531756검색

최근 오래된 비즈니스 코드를 다룰 때 문제가 발생했습니다. 이 기사는 주로 MySQL 인덱스 최적화에 대한 내용을 공유합니다.

최근에 오래된 비즈니스 코드를 다룰 때 이런 예를 접했습니다.

테이블 구조는 다음과 같습니다.

CREATE TABLE `group_user` ( 
  `id` int(11) NOT NULL auto_increment, 
  `uid` int(11) NOT NULL, 
  `username` varchar(16) NOT NULL,
  `gid` int(11) NOT NULL, 
  `create_time` int(10) NOT NULL, 
  `update_time` int(10) NOT NULL, 
  PRIMARY KEY  (`id`), 
  KEY `idx_uid` (`uid`), 
  KEY `idx_gid` (`gid`) 
) ENGINE=InnoDB AUTO_INCREMENT=1530312 DEFAULT CHARSET=utf8

150w의 데이터, 다음과 같은 명령문:

SELECT SQL_NO_CACHE uid FROM group_user WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;

실제로 2초가 걸리는 느린 쿼리 로그가 많이 있습니다. explain 결과는 :

+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ 
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra                          | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+ 
|  1 | SIMPLE      | group_user | ref  | idx_gid       | idx_gid  | 4       | const | 6535 | Using where; Using filesort    | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+--------------------------------+

Explain 결과를 보면 쿼리가 인덱스를 사용한 것을 알 수 있는데, 왜 여전히 느린 걸까요?

분석: 우선 ORDER BY 문은 파일 정렬 파일 정렬을 사용하며 쿼리 효율성이 낮습니다. 둘째, 쿼리 필드가 인덱스에 없고 포함 인덱스를 사용하지 않으므로 쿼리해야 합니다. 인덱스를 통해 다시 테이블로 돌아갑니다. 마지막으로 데이터 분포 측면에서 gid는 동일합니다. uid 해싱은 상대적으로 균일하며 보조 인덱스만 사용하는 효과는 평균입니다(인덱스를 모르는 경우). 분류하려면 다음을 클릭하세요: MySQL 인덱스 분류 소개).

해결책: uid 필드만 쿼리되므로 공동 인덱스를 추가하면 테이블 백업 및 파일 정렬을 피할 수 있고, 커버링 인덱스를 사용하여 쿼리 속도를 높이고 인덱스를 사용하여 정렬을 완료할 수 있습니다.

Covered index: MySQL은 보조 인덱스를 통해 기본 키를 찾은 후 데이터를 쿼리할 필요 없이 쿼리에 필요한 데이터를 반환하기 위해 인덱스만 사용하면 됩니다.

ALTER TABLE group_user ADD INDEX idx_gid_ctime_uid (gid, create_time, uid);

다시 설명해주세요:

EXPLAIN SELECT SQL_NO_CACHE uid FROM group_user USE INDEX(idx_gid_ctime_uid) WHERE gid = 2 ORDER BY create_time ASC LIMIT 10;

+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+ 
| id | select_type | table      | type | possible_keys     | key               | key_len | ref   | rows | Extra                    |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | group_user | ref  | idx_gid_ctime_uid | idx_gid_ctime_uid | 4       | const | 6375 | Using where; Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+--------------------------+

추가 정보에 이미 'Using Index'가 있어서 커버링 인덱스를 사용했다는 의미입니다. (보통 *를 선택하는 동료가 많아서 함정입니다.)

문에서 사용할 인덱스를 수동으로 지정해야 하는 이유는 무엇입니까? MySQL 쿼리 최적화 프로그램은 삭제되지 않는 한 idx_gid 인덱스를 사용할 수 있기 때문입니다.

인덱스 최적화 후 온라인 쿼리는 기본적으로 0.001초를 넘지 않습니다.

마지막 질문: 이 테이블이 MyISAM 엔진을 사용한다면 실제 상황은 어떨까요?

인덱스를 다루는 MySQL 인덱스 최적화

관련 기사