MySQL에서 쿼리가 인덱스를 사용하도록 강제하는 방법은 무엇입니까?

MySQL에서 쿼리가 인덱스를 사용하도록 강제하는 방법은 무엇입니까?

슬로우 쿼리 로그에 나타나는 쿼리에 인덱스를 추가하여 해머드 워드프레스 DB의 성능을 개선하고자 합니다.

MS SQL에서는 쿼리 힌트를 사용하여 쿼리가 인덱스를 사용하도록 강제할 수 있지만 열 등을 올바르게 커버하는 경우 인덱스를 사용하기 위한 쿼리를 얻는 것은 일반적으로 매우 쉽습니다.

느린 쿼리 로그에 많이 나타나는 쿼리가 있습니다.

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID 
FROM wp_posts  
WHERE 1=1  
  AND wp_posts.post_type = 'post' 
  AND (wp_posts.post_status = 'publish')  
ORDER BY wp_posts.post_date DESC 
  LIMIT 18310, 5;

는 에 .wp_posts위에post_date, post_status, post_type and post_id할 때 는 라고 설명합니다.SQL을 다시 시작했는데 실행해보니 사용된 인덱스가

status_password_id

가능한 키에서는 새 인덱스가 표시되지 않지만 커버링 인덱스입니다. 예를 들어 방금 얻은 것과 같이

type_status_date,status_password_id

따라서 사용된 인덱스나 MySQL에 post_date가 있는 경우 "optimiser"를 선택하는 것 모두 첫 번째 열로 post_date가 있는 내 인덱스를 고려하지 않습니다.저는 기본적으로 TOP을 하고 날짜별로 주문하는 쿼리를 생각했을 것입니다.

ORDER BY wp_posts.post_date DESC LIMIT 18310, 5;

속도를 위해 날짜별로 정렬된 인덱스, 특히 다른 모든 필드가 쿼리를 충족시키는 데 필요한 인덱스를 사용하시겠습니까?

MySQL에 속도/성능 테스트에 인덱스를 사용하도록 강제하는 쿼리 힌트가 있습니까? 아니면 이 인덱스가 무시되는 이유를 확인하기 위해 수행해야 할 다른 작업이 있습니까?

Navicat에 MS SQL과 같은 Visual Query Execution Plan이 있으면 좋겠지만 EXPLAINE이 최선인 것 같습니다.

인덱스를 강제로 사용하거나 무시되는 이유를 알아낼 수 있는 힌트가 있는 사람은 누구나 매우 도움이 됩니다!

감사해요.

MySQL에 속도/성능 테스트에 인덱스를 사용하도록 강제하는 쿼리 힌트가 있습니까? 아니면 이 인덱스가 무시되는 이유를 확인하기 위해 수행해야 할 다른 작업이 있습니까?

이 문서에서는 다음과 같은 질문에 대해 자세히 답변합니다.

USE INDEX(index_list)MySQL에서 테이블에서 행을 찾기 위해 명명된 인덱스 중 하나만 사용하도록 지시할 수 (index_list)있습니다.대체 구문IGNORE INDEX(index_list) MySQL에서 특정 인덱스나 인덱스를 사용하지 않도록 지시하는 데 사용할 수 있습니다.이러한 힌트는 MySQL이 가능한 인덱스 목록에서 잘못된 인덱스를 사용하고 있음을 보여주는 경우에 유용합니다.

사용할 수도 있습니다.FORCE INDEX을 하는지, ㅇUSE INDEX(index_list) 하지만 여기에 테이블 스캔 비용이 매우 많이 든다고 가정합니다.즉, 테이블 검색은 테이블에서 행을 찾기 위해 지정된 인덱스 중 하나를 사용할 방법이 없는 경우에만 사용됩니다.

각 힌트에는 열 이름이 아니라 인덱스 이름이 필요합니다.PRIMARY KEY이다.PRIMARY. 테이블의 인덱스 이름을 보려면 을 사용합니다.

한다면USE INDEX안 돼요,다를 해 보세요. 사용해 보십시오.IGNORE INDEX최적화자의 두 번째 선택(또는 세 번째 등)을 확인합니다.

구문의 간단한 예는 다음과 같습니다.

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) WHERE ...

링크된 문서에는 그 이상의 것들이 있습니다.버전 5.0 페이지에 연결했지만 왼쪽 사이드바를 사용하여 해당 버전으로 쉽게 이동할 수 있습니다. 버전 5.1부터는 몇 가지 추가 구문 옵션을 사용할 수 있습니다.

MySQL 5.6은 새로운 형식의 EXPRESON을 지원하며, MySQL Workbench GUI를 통해 더욱 매력적으로 시각화할 수 있습니다.그러나 MySQL 5.5 이전 버전에 갇혀 있는 경우에는 아무런 도움이 되지 않습니다.

MySQL에는 @AirThomas가 언급한 것처럼 힌트가 있지만 실제로는 이 힌트를 거의 사용하지 않는 것이 좋습니다.사용자가 보여주는 것과 같은 간단한 쿼리에서는 인덱스 힌트를 사용할 필요가 없습니다. 만약 올바른 인덱스를 가지고 있다면 말입니다.인덱스 힌트를 사용하면 응용프로그램에 하드코딩된 인덱스 이름이 있으므로 인덱스를 추가하거나 삭제할 경우 코드를 업데이트해야 합니다.

쿼리에서 다음 항목에 대한 인덱스.(post_date, post_status, post_type, post_id)도움이 되지 않을 겁니다

인덱스에서 가장 왼쪽에 있는 열을 행 제한에 사용하려고 합니다.그래서 집어넣습니다.post_status, post_type첫번째. 더 선택적인 열이 첫번째인 경우가 가장 좋습니다.즉라면,post_type = 'post'의 3%합니다.post_status = 'publish'테이블의 1%와 일치한 다음 post_type 앞에 post_status를 우선합니다.

.=e AND연산자: 일치하는 모든 행은 기본적으로 두 열에 대해 묶여 있습니다.그래서 만약 여러분이.post_date인덱스의 세 번째 열로서 최적화자는 인덱스에 저장된 순서대로 행을 가져올 수 있으며 ORDER BY에 대한 다른 작업을 건너뛸 수 있음을 알고 있습니다.EXPRESON 출력에서 "Using filesort"(파일 정렬 사용)이 사라지면 이것이 작동하는 것을 볼 수 있습니다.

따라서 당신의 지수는 다음과 같습니다.

ALTER TABLE wp_posts ADD INDEX (post_status, post_type, post_date);

색인을 디자인하는 방법에 대해서도 설명해 드리겠습니다.

이 경우 InnoDB 인덱스에는 기본 키 열이 자동으로 포함되므로 인덱스에 ID를 추가할 필요가 없습니다.

LIMIT 18310, 5비용이 많이 들 수 밖에 없습니다.MySQL은 서버 측에서 최대 18315개 행의 전체 결과 집합을 생성해야 하지만 대부분의 결과 집합을 폐기해야 합니다.도대체 누가 3662페이지를^nd 건너뛸 필요가 있습니까?

SQL_CALC_FOUND_ROWSMySQL은 요청한 페이지 이전과 이후에 모두 전체 결과 집합을 생성해야 하므로 페이징 중인 큰 결과 집합이 있을 때 주요 성능 저하 요인이 됩니다.꼭 필요한 경우가 아니라면 해당 쿼리 수정자를 제거하는 것이 가장 좋습니다.FOUND_ROWS()가 필요한 에도 를 두 이 더 수 하나는 다음과 같습니다.SELECT COUNT(*).
(* 두 가지 방법을 모두 테스트하여 확인합니다.)

LIMIT를 최적화하는 몇 가지 팁은 다음과 같습니다.

• http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
• http://www.mysqlperformanceblog.com/2008/09/24/four-ways-to-optimize-paginated-displays/
• http://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

인덱스 정의의 순서를 다음으로 변경해 봅니다.

post_type, post_status, post_date, post_id

아니면

post_date desc, post_type, post_status,  post_id

제가 다른 PC를 사용하고 있어서 사용자 이름이 바뀌었지만 원래 질문을 작성했습니다.

MS SQL 배경의 사람들이 MySQL에 비밀리에 사용할 수 있도록 도와주는 변환 가이드가 큰 도움이 될 것 같습니다. 특히 다른 스토리지 엔진이 기본 키에 자동으로 추가된다는 사실과 성능 조정을 지원하는 도구 부족 문제를 해결하는 방법에 대해서는 알지 못했습니다.

저는 주 클러스터 인덱스, 주 키 및 기타 고유한 제약 조건과 인덱스, 키가 포함된 비클러스터 인덱스, 일부 포괄 인덱스 등을 만드는 데 익숙합니다.

그런 다음 타임 작업을 실행하여 누락된 인덱스 DMV 리포트를 테이블에 기록하여 재시작 시 데이터가 손실되지 않도록 합니다.그런 다음 보고서를 실행하여 SQL 옵티마이저가 "사용해야 한다"고 생각하는 인덱스 또는 "사용하지 않는" 인덱스를 확인할 수 있습니다.그런 다음 이 정보, 오히트 카운터 및 누락된 인덱스를 사용한 경우 잠재적 효율성 백분율을 사용하여 인덱싱 성능을 세부적으로 조정하는 데 도움이 되는 지침으로 사용할 수 있습니다.

MySQL에는 DMV의 MsSQL과 유사한 기능이 없는 것으로 알고 있습니다.

aeons 전부터 MS SQL Studio에 내장된 멋진 그래픽 실행 계획은 튜닝에 많은 도움을 주며 bog standard MySQL의 설명은 비교가 어렵습니다.select @@version을 실행하면 5.0.51a-24+lenny5-log가 반환되지만 말씀하신 툴을 확인해 보겠습니다.

게시물과 관련하여 몇 가지 사항이 있습니다.

1. 목적은 북마크 룩업(MySQL에서 북마크 룩업이라고 부르는 경우)이 필요하지 않고 데이터를 인덱스에서 바로 가져올 수 있도록 커버링 인덱스를 갖는 것이었습니다.

2. 거의 모든 게시물이 "게시"되어 있기 때문에(99.99%), post_type은 거의 모든 "게시"(99.99%)이며, "페이지"의 비율은 작습니다.이 두 열에는 선택성이 없으며 표지에 대한 색인에 있습니다.수정사항 등이 쌓이지 않도록 자동 초안을 껐는데 초안의 수가 매우 적습니다.

3. 따라서 인덱스의 첫 번째 키로 post_date를 사용하는 것이 LIMIT로 더 도움이 될 것이라고 생각했습니다(비싸다고 말씀하신 것처럼,Wordpress의 코드를 제어할 수 없습니다. 따라서 ORDER BY 및 LIMIT(기본적으로 TOP인)는 쿼리에서 가장 비용이 많이 들고 선택적인 부분이었으며 다른 키(선택적이지 않은)와 비교하여 인덱스에 더 많이 사용되었을 것입니다.이것이 제가 그것을 먼저 생각하는 이유입니다.

4. 저는 워드프레스를 사용하고 있으며 테이블은 wp_posts이고 저장 엔진은 MyISAM으로 전체 텍스트 검색이 필요하여 변경할 수 없다고 생각합니다.

5. 내가 다른 사람에게 말했듯이 나는 이미 post_type, post_status, post_date 순서의 인덱스를 가지고 있지만 EXPLAINE은 가능한 키에만 그것을 보여주고 post_status, password, id를 중심으로 한 인덱스를 사용하기 위해 그것을 무시합니다.

6. 암호가 쿼리에 사용되지 않고 post_status가 완전히 선택적이지 않기 때문에(모든 post_type이 "post"이기 때문에) MySQL의 "클레버" 옵티마이저가 제공된 인덱스 또는 내 인덱스보다 이 인덱스를 선택해야 한다고 생각하는 이유를 모르겠습니다.

그래서 어떤 제안도 통하지 않는 것 같아 저는 여전히 꼼짝 못합니다.

몇 번이나 주문을 바꿔봤는데 20k열밖에 없는데도 매번 30분 이상 걸려요!MySQL에서 이것이 정상인지 아닌지는 모르겠지만 MSSQL에서는 수백만 행이 있는 테이블에서 인덱스를 추가/삭제하는 데 몇 분이 걸립니다.

그래서 지금까지 아무 것도 작동하지 않았기 때문에 (왜?) 그리고 그것이 도움이 되는지 알아보기 위한 쿼리 힌트에 대해 알고 싶습니다.

색인을 다시 작성한 후 DB를 다시 시작했습니다(그리고 웹 서버도 다시 시작했습니다).

도와주셔서 고마워요.

언급URL : https://stackoverflow.com/questions/17435950/how-do-you-force-a-query-in-mysql-to-use-an-index