where 절과 order_by에 대한 MYSQL 인덱스를 만듭니다.

where 절과 order_by에 대한 MYSQL 인덱스를 만듭니다.

이 표를 보면

CREATE TABLE tbl_tax (
  taxdata_id int(11) NOT NULL AUTO_INCREMENT,
  tax_year varchar(255) NOT NULL,
  display_pid varchar(255) NOT NULL,
  type varchar(255) NOT NULL,
  tax_id varchar(255) NOT NULL,
  tax_amount varchar(255) NOT NULL,
  total_due varchar(255) NOT NULL,
  paid_wcert varchar(255) NOT NULL,
  datelast_adv varchar(255) NOT NULL,
  pmtmade_today varchar(255) NOT NULL,
  owner_name varchar(255) NOT NULL,
  PRIMARY KEY (taxdata_id),
  UNIQUE KEY unique_tbl_tax_TaxidYear (tax_id,tax_year),
  KEY tax_year_2 (tax_year, owner_name, tax_id, display_pid, 
    type, tax_amount, total_due, total_paid, datelast_adv, pmtmade_today, 
    taxdata_id, paid_wcert)
) ENGINE=InnoDB AUTO_INCREMENT=100000 DEFAULT CHARSET=latin1;
 tbl_tax;

이 SQL 쿼리를 고려하면

SELECT tax_year
     , tax_id
     , owner_name
     , display_pid
     , type
     , tax_amount
     , total_due
     , total_paid
     , datelast_adv
     , pmtmade_today
     , taxdata_id
     , paid_wcert
  FROM tbl_tax
 WHERE tax_year >= '2015'
   AND tax_year <= '2019'
 ORDER 
    BY tax_year DESC;

인덱스를 만들고 싶어서 커버 인덱스를 만들어 보았습니다.

이 글에서 인용하여 "일반적인 규칙은 먼저 필터링할 열을 선택하고(동등조건이 있는 WHERE 절), 그 다음에 정렬/그룹화(ORDER BY 절과 GROUP BY 절), 마지막으로 데이터 투영(SELECT 절)을 선택하는 것이다."

ALTER TABLE tbl_tax
ADD INDEX (
    `tax_year`, `owner_name`, `tax_id`, `display_pid`, 
    `type`, `tax_amount`, `total_due`, `total_paid`, `datelast_adv`, `pmtmade_today`, 
    `taxdata_id`, `paid_wcert`
);

를 하다explain,드라마들.

        "id" : 1,
        "select_type" : "SIMPLE",
        "table" : "tbl_tax",
        "partitions" : null,
        "type" : "index",
        "possible_keys" : "tax_year_2",
        "key" : "tax_year_2",
        "key_len" : "2831",
        "ref" : null,
        "rows" : 271630,
        "filtered" : 50.00,
        "Extra" : "Using where; Backward index scan; Using index"   

인덱스를 작성하는 동안 다음 사항을 알고 있습니다.

1. 범위 술어를 포함하는 WHERE 절(<=, >=)
2. 쿼리에는 ORDER_BY가 행에 액세스하는 순서와는 다른 순서로 포함됩니다.

이러한 이유들로 인해 출력은explain드라마들."rows" : 271630,

그러나 SQL 쿼리의 결과 집합은 최대 2000 행에 불과합니다.

많은 기사를 읽어보려고 했지만, 아직 최적화하지 못하고 있습니다.

이 상황에서 더 나은 최적화를 얻으려면 어떻게 해야 합니까? 더 나은 방법으로 인덱스를 작성할 수 있습니까? SQL 쿼리에 변경을 가할 수 있습니까?또한 제가 여기서 오해한 부분이 있으면 언제든지 정정해 주세요.

이것은 흥미로운 사례입니다. 왜냐하면 보통 우리는 이 사건들을 보는 것을 좋아하기 때문입니다.Using index하지만 이 경우엔 해롭습니다

그 이유는 이게type: index인덱스 스캔을 하고 있다는 뜻이죠즉, 사용자의 조건에 맞는 행뿐만 아니라 전체 인덱스를 스캔합니다.그래서 보여지는 거야rows: 271630이것은 기본적으로 테이블의 크기(또는 적어도 옵티마이저가 통계정보를 바탕으로 예측한 테이블 크기)입니다.

이 경우 인덱스에 모든 열을 추가하는 것은 도움이 되지 않는다고 생각합니다.인덱스를 한 열로 하는 것이 더 좋았을 것입니다.tax_year.

그럼 DESPLOY가 보여줘야지type: range조건 때문에 검사하는 행은 조건과 일치하는 행뿐임을 나타냅니다.

그러면 우리는 볼 수 있을 것이다.Filtered: 100.00이는 검사된 모든 행이 결과에 포함된다는 의미이며, 이는 양호한 결과입니다.즉, 행이 검사되지 않고 필터링되지 않았다는 점에서 쿼리가 효율적이었다는 것을 의미합니다.

또한 당신의 ORDER BY는 같은 열에 대한 것이므로, 저는 여전히 기대하고 있습니다.Using filesort결석하는 건 좋은 일이죠

---

코멘트 참조:

2015년부터 2019년까지의 tax_year 조건은 표의 큰 부분집합과 일치한다고 생각합니다.MySQL은 조건이 행의 큰 부분과 일치하는 경우 인덱스를 사용하지 않도록 선택합니다.단순히 표를 스캔하는 것보다 지수를 사용하는 것이 더 비용이 많이 들 것으로 추정됩니다.

옵티마이저가 잘못된 선택을 했다고 생각되는 경우 테이블스캔 비용이 더 많이 든다고 가정할 수 있습니다.

... FROM tbl_tax FORCE INDEX(tax_year) ...

(지수의 이름은tax_year, 이

도 동의해요.varchar(255)모든 속성 컬럼이 부적절합니다.

INDEX(tax_year, ...) 그것을 처리하다WHERE ★★★★★★★★★★★★★★★★★」ORDER BY.

쿼리에는 ORDER_BY가 행에 액세스하는 순서와는 다른 순서로 포함됩니다.

.WHERE에는 액세스 순서가 지정되어 있지 않습니다. 실,, 그 inEXPLAIN"백워드 인덱스 스캔"이라고 표시됩니다.하다.

2와 같은 타입을 사용합니다.YEAR★★★★★★에tax_yearVARCHAR(255)됩니다.16월 6일입니다.

("금액" 등에 대한) varchar의 산술은 복잡해집니다.

이렇게 해서 '아주머니,아주머니,아주머니,아주머니,아주머니,아주머니,아주머니,아주머니,아주머니,아주머니,아주머니.그러나 인덱스를 5열보다 크게 만드는 것은 좋아하지 않습니다.의 큰 그 에 도움이 되지만 를 준다.INSERTs아, 아, 아, 아, 아, 아, 아, 아, 아.

(그리고 나는 Bill의 말에 동의해.)

언급URL : https://stackoverflow.com/questions/64514788/create-a-mysql-index-for-where-clause-and-order-by