GBK
| 2 |
은 중국어를 지원하지만 국제 문자 집합은 아닙니다. | |
UTF-8
| 3 |
은 중국어와 영어 혼합 시나리오를 지원하며 국제 범용 문자 집합입니다 |
|
latin1
| 1 |
MySQL 기본 문자 집합 |
|
utf8mb4
| 4 |
UTF-8과 완벽하게 호환됩니다. 8, 더 많은 문자를 저장하려면 4바이트를 사용하세요 |
|
MySQL 데이터베이스 개발 및 운영 시 문자 집합 선택 규칙은 다음과 같습니다.
- 해외 비즈니스용으로 개발된 시스템이고 다양한 국가 및 언어를 처리해야 하는 경우 utf-8 또는 utf8mb4를 선택해야 합니다. 중국어만 지원해야 하는 경우, 외국 비즈니스가 없는 경우 성능상의 이유로 GBK를 사용할 수 있습니다.
-
3. 맞춤 변수 맞춤 변수는 콘텐츠를 저장하는 데 사용되는 임시 컨테이너이며 전체에 존재합니다. MySQL에 연결하는 전체 과정입니다. set을 사용하여 정의할 수 있습니다.
SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;SELECT id,name from user where create_time > @last_week;
맞춤 변수 사용에 관한 참고 사항:
맞춤 변수를 사용하는 쿼리는 캐시를 사용할 수 없습니다.
행을 동시에 업데이트하는 경우 이 행에 대한 정보를 얻고 싶습니다. 반복되는 쿼리를 방지하려면 어떻게 해야 합니까?
이 작업은 일반적으로 수행됩니다.
update user set update_time = now() where id = 1;select update_time from user where id = 1;
맞춤 변수를 사용하면 최적화할 수 있습니다.
update user set update_time = now() where id = 1 and @now := now();select @now;
여전히 두 개의 쿼리처럼 보이지만 두 번째 쿼리는 데이터 테이블에 액세스할 필요가 없으므로 훨씬 빠릅니다.
4. 최적화된 데이터 유형 선택 MySQL은 다양한 데이터 유형을 지원하며, 고성능을 달성하려면 올바른 데이터 유형을 선택하는 것이 중요합니다.
(1) 더 작음 일반적으로 더 작은 데이터 유형을 사용하도록 노력해야 합니다. 더 작은 데이터 유형은 처리에 필요한 디스크, 메모리 및 CPU 캐시를 덜 차지하므로 일반적으로 더 빠릅니다.
(2) 단순함 단순한 데이터 유형은 일반적으로 더 적은 CPU 주기를 필요로 하며, 문자 집합과 유효성 검사 규칙은 문자 비교를 정수 비교보다 더 복잡하게 만들기 때문에 정수는 문자열 유형보다 비용이 저렴합니다.
(3) NULL을 피하십시오.응용 프로그램이 NULL을 저장할 필요가 없더라도 많은 테이블에 NULL 가능 열이 포함되어 있습니다. NULL은 열의 기본 속성이므로 일반적으로 Column을 지정하는 것이 가장 좋습니다. NULL이 아닙니다.
쿼리에 NULL 가능 열이 포함된 경우 NULL 가능 열이 인덱스, 인덱스 통계 및 값 비교를 더 복잡하게 만들기 때문에 MySQL을 최적화하기가 더 어렵습니다. NULL 가능 열은 더 많은 저장 공간을 사용하며 MySQL에서 특별한 처리가 필요합니다. NULL 가능 열이 인덱싱되면 각 인덱스 레코드에는 추가 바이트가 필요하며 이로 인해 MyISAM에서는 인덱스가 가변 크기 인덱스가 될 수도 있습니다. .
5. 뷰 뷰(view)는 가상의 테이블, 즉 그 자체로는 데이터를 담고 있지 않은 논리적 테이블이다. 데이터 사전에 select 문으로 저장됩니다. 여러 테이블에 대한 복잡한 쿼리의 경우 뷰를 사용하면 쿼리가 단순화될 수 있습니다. 뷰가 임시 테이블을 사용하는 경우 where 조건을 사용할 수 없으며 인덱스를 사용할 수 없습니다.
단일 테이블 뷰는 일반적으로 기본 테이블의 데이터를 변경하는 쿼리 및 수정에 사용됩니다. 다중 테이블 뷰는 일반적으로 쿼리에 사용되며 기본 테이블의 데이터를 변경하지 않습니다.
뷰를 사용하는 목적은 데이터 보안을 보장하고 쿼리 효율성을 향상시키는 것입니다.
뷰의 장점:
뷰를 사용하는 사용자는 후속 해당 테이블의 구조, 연결 조건 및 필터링 조건에 대해 신경 쓸 필요가 없습니다. 사용자에게는 이미 필터링된 복합 조건의 결과 집합입니다.
6. 캐시 테이블 및 요약 테이블 때로는 파생된 중복 데이터를 동일한 테이블에 저장하는 것이 성능을 향상시키는 가장 좋은 방법이기도 합니다. 때로는 완전히 독립적인 요약 테이블이나 캐시 테이블을 생성해야 할 수도 있습니다.
캐시 테이블은 얻기 쉽지만 느린 데이터를 저장하는 데 사용됩니다.
- 요약 테이블은 그룹별 문을 사용하여 집계되고 쿼리된 데이터를 저장하는 데 사용됩니다. 테이블은 InnoDB를 사용합니다. 테이블 캐싱을 위한 엔진으로 MyISAM은 더 작은 인덱스 공간을 확보하고 전체 텍스트 검색을 수행할 수 있습니다.
- 캐시 테이블과 요약 테이블을 사용할 때는 데이터를 실시간으로 유지할지, 아니면 주기적으로 재구축할지 결정해야 합니다. 어느 쪽이 더 나은지는 애플리케이션에 따라 다르지만, 정기적인 재구축은 리소스를 절약할 뿐만 아니라 테이블이 조각화되는 것을 방지하고 완전히 순차적으로 구성된 인덱스를 유지합니다.
요약 테이블과 캐시 테이블을 재구축하는 경우 일반적으로 작업 중에 데이터를 계속 사용할 수 있는지 확인해야 합니다. 이는 섀도우 테이블을 사용하여 수행해야 합니다. 구성이 완료됩니다. 테이블 작업 후 원자적 이름 바꾸기 작업을 통해 섀도우 테이블과 원본 테이블을 전환할 수 있습니다.
읽기 속도를 향상시키기 위해 추가 인덱스를 구축하거나 중복 열을 추가하거나 심지어 캐시 테이블과 요약 테이블을 생성하는 경우가 많습니다. 이러한 방법은 쓰기 부담을 증가시키고 추가 유지 관리 작업이 필요하지만 고성능 데이터베이스를 설계할 때 , 이는 쓰기 작업 속도를 늦추지만 읽기 성능을 크게 향상시키는 일반적인 기술입니다.
7. 파티션 테이블
일반적으로 동일한 테이블의 데이터는 물리적 수준에서 함께 저장됩니다. 사업이 성장함에 따라 동일한 테이블에 데이터의 양이 너무 많아지면 관리상의 불편을 초래하게 됩니다. 파티션 기능은 특정 규칙에 따라 테이블을 물리적으로 여러 파티션으로 나눌 수 있습니다. 여러 파티션을 별도로 관리하거나 다른 디스크/파일 시스템에 저장하여 효율성을 높일 수도 있습니다.
파티션 테이블의 장점:
데이터는 디스크에 저장될 수 있어 대용량 데이터 저장에 적합합니다.
-
데이터 관리는 다른 파티션의 정상적인 작동에 영향을 주지 않고 파티션에서 데이터를 운영하는 데 매우 편리합니다.
쿼리 중에 파티션 기능을 잠그면 쿼리 범위를 좁히고 쿼리 성능을 향상시킬 수 있습니다.
8. 외래 키는 일반적으로 데이터를 수정할 때마다 다음을 수행해야 합니다. 추가 쿼리 작업이 수행되어야 합니다. InnoDB는 외래 키에 인덱스를 사용하도록 강제하지만 여전히 이 제약 조건 검사의 오버헤드를 제거할 수는 없습니다. 외래 키의 선택성이 매우 낮으면 매우 선택적인 인덱스가 생성됩니다.
그러나 일부 시나리오에서는 외래 키가 일부 성능을 향상시킵니다. 예를 들어 두 개의 관련 테이블에 항상 일관된 데이터가 있는지 확인하려면 외래 키를 사용하는 것이 애플리케이션에서 일관성을 확인하는 것보다 훨씬 더 효율적입니다. 외래 키는 관련 데이터를 애플리케이션에서 유지 관리하는 것보다 삭제하고 업데이트하는 데 더 효율적입니다. 그러나 외래 키 유지 관리 작업은 행별로 수행되므로 이러한 업데이트는 일괄 삭제 및 업데이트보다 느립니다.
외래 키 제약 조건을 사용하려면 쿼리 중에 다른 테이블에 대한 추가 액세스가 필요하며, 이를 위해서는 추가 잠금이 필요합니다. 레코드가 하위 테이블에 기록되면 외래 키 제약 조건으로 인해 InnoDB가 상위 테이블의 해당 레코드를 확인하게 됩니다. 이는 이 레코드가 손실되지 않도록 상위 테이블의 해당 레코드를 잠가야 함을 의미합니다. 거래가 완료된 후 삭제되었습니다. 이로 인해 추가 잠금 대기 및 일부 교착 상태가 발생할 수 있습니다. 이러한 테이블에 직접 액세스할 수 없기 때문에 이러한 유형의 교착 상태는 해결하기 어렵습니다.
따라서 현재 많은 프로젝트에서는 성능상의 이유로 외래 키가 더 이상 사용되지 않습니다.
9. 쿼리 캐시 MySQL 쿼리 캐시는 쿼리에서 반환된 전체 결과를 저장합니다. 쿼리가 캐시에 도달하면 MySQL은 구문 분석, 최적화 및 실행 프로세스를 건너뛰고 즉시 결과를 반환합니다.
쿼리 캐시 시스템은 쿼리에 포함된 각 테이블을 추적합니다. 이러한 테이블이 변경되면 이 테이블과 관련된 모든 캐시된 데이터가 유효하지 않게 됩니다. 쿼리 결과에 영향을 미치지만 이 간단한 구현 비용은 매우 적으므로 매우 바쁜 시스템에서는 매우 중요합니다.
(1) MySQL은 캐시 적중 여부를 어떻게 판단합니까? 적중 여부를 판단할 때 MySQL은 이를 구문 분석하지 않고 클라이언트가 보낸 SQL 문 및 기타 원본 정보를 직접 사용합니다. 공백이나 주석과 같은 문자의 차이로 인해 캐시 누락이 발생합니다. 일반적으로 통합 코딩 규칙을 사용하는 것은 좋은 습관이며 시스템 실행 속도를 높여줍니다. now() 함수와 같이 쿼리문에 불확실한 데이터가 있는 경우 캐시되지 않습니다. 실제로 캐시에 사용자 정의 함수, 저장 함수, 사용자 변수, 임시 테이블, MySQL 시스템 테이블 또는 열 수준 권한이 포함된 테이블이 포함되어 있으면 캐시되지 않습니다.
(2) 쿼리 캐시를 사용할 때 주의하세요.
쿼리 캐시를 열면 읽기 및 쓰기 작업 모두에 추가 소비가 발생합니다.
읽기 쿼리는 실행하기 전에 먼저 캐시에 도달하는지 확인해야 합니다.
- 읽기 쿼리를 캐시할 수 있어 실행이 완료되었을 때 MySQL이 쿼리가 캐시에 없음을 발견하면 결과를 쿼리 캐시에 저장하므로 시스템이 추가로 소모됩니다.
- 테이블에 데이터를 쓸 때 MySQL은 해당 테이블에 대한 모든 캐시 설정을 무효화해야 하기 때문에 쓰기 작업에도 영향을 미칩니다. 쿼리 캐시가 매우 크거나 조각난 경우 이 작업으로 인해 시스템이 많이 소모될 수 있습니다.
- 그러나 쿼리 캐시는 여전히 시스템 성능을 향상시킵니다. 그러나 위에서 언급한 추가 소모량도 계속 증가할 수 있다. 게다가 쿼리 캐시 작업은 잠금 배타적 작업이므로 이 소모량도 적지 않다. InnoDB 사용자의 경우 트랜잭션의 일부 특성으로 인해 쿼리 캐시 사용이 제한됩니다. 명령문이 트랜잭션의 테이블을 수정하면 MySQL은 트랜잭션이 제출되기 전에 테이블에 해당하는 쿼리 캐시 설정을 무효화합니다. 따라서 장기 실행 트랜잭션은 쿼리 캐시의 적중률을 크게 줄입니다.
(3) 쿼리 캐시 분석 및 구성 방법
10. 저장 프로시저
저장 프로시저는 특정 기능을 완료하기 위한 SQL 문 집합으로, 이름을 지정하여 컴파일되어 데이터베이스에 저장됩니다. 저장 프로시저의 매개변수 값을 제공하면 결과를 반환할 수도 있습니다.
저장 프로시저의 장점:
네트워크 트래픽 감소
실행 속도 향상
데이터베이스 연결 수 감소
높은 보안
-
높은 재사용성
저장 프로시저의 단점:
11. 트랜잭션
트랜잭션 내의 문은 모두 실행되거나 전혀 실행되지 않습니다. 트랜잭션에는 원자성, 일관성, 격리성 및 내구성을 나타내는 ACID 특성이 있습니다.
(1) 원자성
트랜잭션은 분할할 수 없는 최소 작업 단위로 간주되어야 합니다. 전체 트랜잭션의 모든 작업은 완전히 실행되어 성공적으로 제출되거나 모두 실패 없이 롤백됩니다.
(2) 일관성
데이터베이스는 항상 하나의 일관된 상태에서 다른 일관된 상태로 전환됩니다.
(3) 격리
한 트랜잭션에서 수정한 사항은 최종적으로 제출되기 전에 다른 트랜잭션에 표시되지 않습니다.
(4) 내구성
거래가 제출되면 수정 사항이 데이터베이스에 영구적으로 저장됩니다.
12. 인덱스
인덱스는 스토리지 엔진이 레코드를 빠르게 찾기 위해 사용하는 데이터 구조입니다. 데이터베이스에서 가장 중요한 지식 포인트는 인덱스라고 생각합니다.
스토리지 엔진은 B-Tree 인덱스를 다양한 방식으로 사용하며 성능도 다르며 각각 고유한 장점과 단점이 있습니다. 예를 들어 MyISAM은 접두사 압축 기술을 사용하여 인덱스를 더 작게 만들지만 InnoDB는 이를 원래 데이터 형식으로 저장합니다. MyISAM 인덱스는 데이터의 물리적 위치를 기준으로 인덱싱된 행을 참조하는 반면, InnoDB는 기본 키를 기준으로 인덱싱된 행을 참조합니다.
B-Tree는 일반적으로 모든 값이 순서대로 저장되고 각 리프 페이지가 루트에서 동일한 거리에 있음을 의미합니다.
B-Tree 인덱스는 스토리지 엔진이 더 이상 필요한 데이터를 얻기 위해 전체 테이블 스캔을 수행할 필요가 없고 대신 인덱스의 루트 노드에서 검색하기 때문에 데이터 액세스 속도를 높일 수 있습니다. 루트 노드의 슬롯은 자식 노드에 대한 포인터를 저장하고 스토리지 엔진은 이러한 포인터를 기반으로 아래쪽으로 검색합니다. 노드 페이지의 값을 찾고 있는 값과 비교하면 하위 하위 노드에 대한 적절한 포인터를 찾을 수 있습니다. 이러한 포인터는 실제로 하위 노드 페이지에 있는 값의 상한과 하한을 정의합니다. 결국 스토리지 엔진은 해당 값을 찾거나 레코드가 존재하지 않습니다.
Leaf 노드는 특별하며 해당 포인터는 다른 노드 페이지가 아닌 색인화된 데이터를 가리킵니다. B-Tree는 인덱스 컬럼을 순차적으로 구성하여 저장하므로 범위 데이터 검색에 매우 적합합니다. B-Tree는 전체 키 값, 키 값 범위 또는 키 접두사 검색에 적합합니다.
인덱스 트리의 노드는 순서가 지정되어 있으므로 값을 기준으로 조회하는 것 외에도 쿼리의 작업을 기준으로 정렬하는 데에도 인덱스를 사용할 수 있습니다. 일반적으로 B-Tree가 특정 방식으로 값을 찾을 수 있으면 이러한 방식으로 정렬하는 데에도 사용할 수 있습니다.
13. 전체 텍스트 인덱스
전체 텍스트 인덱스의 목적은 정확한 쿼리가 아닌 유사 쿼리를 기반으로 키워드 일치를 통해 쿼리를 필터링하는 것입니다.
전체 텍스트 인덱스는 단어 분할 기술을 사용하여 텍스트에서 특정 키워드의 빈도와 중요성을 분석하고 특정 알고리즘에 따라 원하는 결과를 지능적으로 필터링합니다.
전체 텍스트 인덱스는 일반적으로 char, varchar, text와 같은 문자열의 특정 키워드를 쿼리하는 데 사용됩니다. 또한 자연어 전체 텍스트 인덱스 및 부울 전체 텍스트 인덱스도 지원합니다.
추천 학습: mysql 비디오 튜토리얼
