Mysql 5.6 '암시적 변환'으로 인한 인덱스 실패 및 부정확한 데이터 문제 해결

mysql 비디오 튜토리얼column에서는 Mysql 5.6

에서 인덱스 오류 및 데이터 부정확성 문제 해결 방법을 소개합니다. 관련 무료 학습 권장 사항: mysql 비디오 튜토리얼

Background

• 하나의 SQL 쿼리 작은따옴표를 제거하여 where 조건의 vachar 유형 필드를 쿼리하려고 시도했을 때 빨라야 하는 이 명령문이 실제로는 매우 느리다는 것을 발견했습니다. 이 varchar 필드에는 복합 인덱스가 있습니다. 총 항목 수는 58989개이며, 작은따옴표 없이도 발견된 데이터는 우리가 원하는 데이터가 아닙니다.
• mysql 버전 5.6을 사용하고 있습니다. innoDB 엔진의 실제 상황은 다음과 같습니다

실행 결과를 살펴보겠습니다

위 설명에서 문자열에도 주의해야 합니다. where 조건의 작은 따옴표 없이 전체 숫자여야 합니다. 그렇지 않으면 오류가 보고되고(

) 발견된 데이터가 우리가 원하는 데이터가 아닐 수도 있습니다. 아래 그림과 같이

분석

1. 실행 결과에서 작은따옴표를 사용하여 해당 인덱스를 제거합니다. 작은따옴표를 사용하지 않으면 인덱싱이 이루어지지 않고 전체 테이블 스캔이 수행됩니다.
2. 이게 왜죠? mysql의 옵티마이저가 유형 변환을 직접 수행하지 않는 이유는 무엇입니까?

• SQL 문에 작은따옴표가 도입된다는 것은 이 유형이 문자열 데이터 유형 CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM 및 SET임을 의미합니다. .
• 작은따옴표를 추가하지 않는다는 것은 이것이 int, bigDecimal 유형 등과 같이 문자열이 아닌 유형이라는 것을 의미합니다.
• 자막 및 특수 기호가 포함된 문자열에 작은따옴표를 추가하지 않으면 결과는 유형 변환이 됩니다. 오류입니다. SQL을 실행할 수 없습니다.

위 그림과 같이:

1054 - Unknown column '000w1993521' in 'where clause', Time: 0.008000s

먼저 SQL의 실행 과정을 살펴보겠습니다.

(네트워크 다이어그램)

• 먼저 결론을 내립니다. 인덱스 필드(이 예에서는 암시적 변환을 수행하는 캐스트 함수)가 인덱스 값의 질서를 파괴할 수 있으므로 최적화 프로그램은 트리 검색 기능을 포기하기로 결정했습니다. (https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html)
• [외부 링크 이미지 전송에 실패했습니다. 소스 사이트에 리칭 방지 메커니즘이 있을 수 있습니다. (img-l5AwT0xu-1607244327891)(http://note.youdao.com/yws/res/23689/CE6F785994E6476D816B23787CE65217)]
• 의미: BINARY, CAST()를 사용하는 경우 주의하세요. 또는 CONVERT()를 사용하여 인덱스 열을 변환하면 MySQL이 인덱스를 효과적으로 사용하지 못할 수 있습니다.
• 암시적 변환으로 인해 감지된 데이터가 정확하지 않습니다. 변환 후 숫자 유형이 달라서 불평등이 동일해집니다.
  

암시적 변환

1. 조건 생성
연산자가 다른 유형의 피연산자와 함께 사용되면 피연산자가 호환되도록 유형 변환이 발생합니다. 그러면 암시적 변환이 발생합니다.
암시적 변환 조건:

1. 두 매개변수 중 하나 이상이 NULL인 경우 비교 결과도 NULL입니다. 두 개의 NULL을 비교하기 위해 return 1. 두 경우 모두 유형 변환이 필요하지 않습니다
2. 두 매개변수 모두 문자열이며 유형 변환 없이 문자열로 비교됩니다
3. 두 매개변수 모두 정수이므로 유형 변환 없이 정수로 비교됩니다
4. 16진수 값을 변환할 때 ​​숫자가 아닌 값과 비교하면 이진 문자열로 처리됩니다
5. 한 매개변수는 TIMESTAMP 또는 DATETIME이고 다른 매개변수는 상수이며 상수는 타임스탬프로 변환됩니다
6. 한 매개변수가 10진수 유형인 경우. 다른 매개변수가 소수 또는 정수인 경우 정수는 비교를 위해 소수로 변환됩니다. 다른 매개변수가 부동 소수점 숫자인 경우 소수는 비교를 위해 부동 소수점 숫자로 변환됩니다. 부동 소수점 숫자로 변환한 후 비교합니다

2. 실제 발생한 상황을 분석합니다

1. 그러면 위에서 제안한 예가 다른 상황에 속하는 정수와 문자열의 비교라는 것을 알 수 있습니다. 그럼 먼저 인덱스 실패 원인을 분석해보겠습니다

• 다른 암시적 변환의 경우로 인해 비교를 위해서는 비교값을 부동소수점수로 변환해야 합니다
• 먼저 쿼리 조건값을 부동소수점수로 변환한 후 레코드값을 ​​​테이블의 ​또한 변환이 필요하므로 이 시점에서는 이전에 생성된 인덱스 정렬이 더 이상 적용되지 않습니다. 암시적 변환(함수)으로 인해 원래 값이 변경되었기 때문에 옵티마이저는 여기서 인덱스를 사용하지 않고 직접 전체 테이블 스캔을 사용합니다.
  

2. 위의 쿼리 결과와 같이 일치하지 않는 값(또는 부분적으로 일치하는 값)을 쿼리합니다. 정말 소스 코드를 살펴봐야 합니다. 이것이 MYsql의 암시적 변환 규칙입니다. 여기서는 자세히 분석하지 않겠습니다(관련 문서가 발견되지 않았기 때문입니다)
역사적 이유로 인해 이전 디자인과 호환되어야 합니다. MySQL의 유형 변환 기능을 사용하여 명시적으로 변환할 수 있습니다.
요약

• 암시적 변환 및 함수를 사용하면 인덱스 오류가 발생하고 선택한 데이터가 부정확해집니다.
• 암시적 변환의 조건 및 규칙
• 암시적 변환이 인덱스 오류를 일으키는 구체적인 이유는 비교 필요성으로 인해 발생합니다. 실패를 유발하려면 값을 유형으로 변환해야 합니다.
• 암시적 유형 변환을 피하세요. 암시적 변환 유형에는 주로 일관되지 않은 필드 유형, 여러 유형이 포함된 매개변수, 일관되지 않은 문자 집합 유형 또는 교정 규칙 등이 포함됩니다.

프로그래밍에 대해 자세히 알아보려면 계속 지켜봐 주시기 바랍니다. PHP 교육칼럼!

위 내용은 Mysql 5.6 '암시적 변환'으로 인한 인덱스 실패 및 부정확한 데이터 문제 해결의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!