간단한 용어로 MySQL 배우기(컬렉션)-MySQL 튜토리얼-php.cn

서문

저자의 경험으로는 데이터베이스가 항상 약점이었습니다(python+sqlalchemy). 나는 ORM을 선호하지만 SQL을 철자하는 것은 상대적으로 고통스러운 일이라고 항상 느꼈습니다(주로 SQL을 잘 못하기 때문에). 이전 유지 관리 프로젝트에서도 몇 가지 데이터베이스 함정에 직면했습니다. 인코딩 문제, 부동 소수점 정밀도 손실 등을 방지하기 위해 향후 트랩 반복을 방지합니다.

1장: 도움말 사용

mysql 내장 도움말 명령 사용

msyql> ? data types : 查看数据类型
mysql> ? int
mysql> ? create table

로그인 후 복사

2장: 테이블 유형 선택(스토리지 엔진)

가장 일반적으로 사용되는 두 가지 엔진 :

1. Myisam은 Mysql의 기본 스토리지 엔진입니다. create가 새 테이블을 생성하고 새 테이블의 스토리지 엔진을 지정하지 않으면 기본적으로 Myisam이 사용됩니다. 각 MyISAM은 디스크에 세 개의 파일로 저장됩니다. 파일명은 테이블명과 동일하며, 확장자는 .frm(스토리지 테이블 정의), .MYD(MYData, 스토리지 데이터), .MYI(MYIndex, 스토리지 인덱스)이다. 데이터 파일과 인덱스 파일을 서로 다른 디렉터리에 배치하여 IO를 균등하게 분배하고 더 빠른 속도를 얻을 수 있습니다.

2. InnoDB 스토리지 엔진은 커밋, 롤백 및 충돌 복구 기능을 갖춘 트랜잭션 보안 을 제공합니다. 그러나 Myisam의 스토리지 엔진에 비해 InnoDB의 쓰기 처리 효율성은 덜 효율적이며 데이터와 인덱스를 유지하기 위해 더 많은 디스크 공간을 차지합니다.

일반 환경:

1. MyISAM: 웹, 데이터 웨어하우징 및 기타 애플리케이션 환경에서 가장 일반적으로 사용되는 기본 MySQL 플러그인 스토리지 엔진
사용된 스토리지 엔진 중 하나

2. InnoDB: 트랜잭션 처리 애플리케이션에 사용되며 ACID 트랜잭션 지원을 포함한 많은 기능이 있습니다.

3장: 적절한 데이터 유형 선택

먼저 지정된 스토리지에 따라 적절한 스토리지 엔진을 선택합니다. 엔진 적절한 데이터 유형을 결정합니다.

MyISAM: 가변 길이 데이터 열 대신 고정 길이 데이터 열을 사용하는 것이 더 좋습니다.
InnoDB: varchar 사용을 권장합니다

참고할 데이터 유형:

1. Char 및 varchar: 저장 및 검색 방법이 다르며 최대 길이 및 후행 공백 유지 여부도 다릅니다. char의 길이가 충분하지 않으면 공백을 사용하여 채웁니다. 가져올 때 PAD_CHAR_TO_FULL_LENGTH가 설정되지 않으면 기본적으로 후행 공백이 제거됩니다.
varchar 변수 길이문자열, 검색 중에 후행 공백이 유지됩니다. 쿼리는 대소문자를 구분하지 않습니다. sqlalchemy를 사용하여 대소문자를 구분하는 경우 func.binary 함수를 사용하지 마세요.

2. 텍스트, blob: text 및 blob이 많은 수의 업데이트 또는 삭제를 수행하면 큰 "구멍"이 남게 됩니다. 이러한 테이블은 정기적으로 OPTIMIZE TABLE 기능을 사용하여 조각 모음하는 것이 좋습니다. 큰 blob 또는 텍스트 값을 검색하지 마세요. 텍스트 및 Blob 열을 별도의 테이블로 분리합니다.

3. 부동 소수점 및 고정 소수점:

몇 가지 사항에 유의하세요.

1. 부동 소수점 숫자는 더 큰 데이터 범위를 나타낼 수 있습니다. , 하지만 오류 문제가 있습니다.

2. 통화 등 정밀도에 민감한 문제의 경우 고정 소수점 저장소를 사용해야 합니다. 이전 프로젝트에는 함정이 있었고, 그 결과 문제를 해결하기 위해 확대 및 축소 방법을 사용해야 했는데 상당히 추악했습니다.

3.프로그래밍부동 소수점 숫자가 발생하면 오류 문제에 주의하고 부동 소수점 숫자 비교를 피하십시오(부동 소수점 숫자를 비교하려면 특정 숫자보다 작은 차이가 필요함). 정밀도), python3.5에서는 다음과 같이 비교할 수 있습니다: float_eq = partial(math.isclose, rel_tol=1e-09, abs_tol=0.0)

4. 부동 소수점 숫자에서 일부 특수 값 처리에 주의하세요.

4장: 문자 집합

처음에 적절한 문자 집합을 선택하려면 , 그렇지 않으면 나중에 교체하는 데 비용이 많이 듭니다. Python2의 문자 집합은 많은 초보자를 혼란스럽게 하는 오랜 문제입니다. 이전에 유지 관리된 프로젝트에서는 msyql의 기본 latin1 문자 집합을 사용했기 때문에 문자열을 작성할 때마다 문자열을 수동으로 utf8로 인코딩해야 했습니다. 최근에 python3.5+flask를 사용하는 프로젝트에 utf8을 사용했는데 인코딩 문제가 다시 발생한 적이 없습니다.

데이터베이스 만들기 Use utf8, CREATE DATABASE IF NOT 존재함 my_db 기본 문자 세트 utf8 COLLATE utf8_general_ci;
sqlalchemy 연결 URL은 mysql://root:root@127.0.0.1:3306/my_db?charset=utf8을 사용합니다. 더 이상 깨진 문자에 대해 걱정할 필요가 없습니다

5장: 인덱스 설계 및 사용

모든 mysql 열 유형을 인덱싱할 수 있습니다. 관련 열에 인덱스를 사용하는 것이 선택 작업의 성능을 향상시키는 가장 좋은 방법입니다. 인덱스 디자인의 원칙:

1. 검색된 인덱스 컬럼이 반드시 선택되는 컬럼은 아닙니다. 인덱스에 가장 적합한 열은 select 키워드 뒤의 select 목록에 나타나는 열이 아닌 where 절 에 나타나는 열이거나, Join 절에 지정된 열입니다.

2. 고유한 인덱스를 사용하세요. 고유한 값이 있는 열의 경우 인덱싱 효과가 더 좋지만, 중복된 값이 여러 개 있는 열의 경우 인덱싱 효과가 떨어집니다.

3. 짧은 인덱스를 사용하세요. 문자열 열을 인덱싱하는 경우 접두사 길이를 지정해야 하며 가능할 때마다 이를 수행해야 합니다.

4. 가장 왼쪽 접두사를 사용하세요. n열 인덱스를 생성할 때 MySQL에서 사용할 수 있는 n개의 인덱스가 실제로 생성됩니다. 다중 열 인덱스는 인덱스에서 가장 왼쪽 접두사가 되는 가장 왼쪽 열 집합을 사용하여 행을 일치시킬 수 있으므로 여러 인덱스로 작동할 수 있습니다.

5. 과도한 색인을 생성하지 마세요. 인덱스는 디스크 공간을 낭비하고 쓰기 성능을 저하시킵니다.

6. 열에 대해 수행할 비교 유형을 고려합니다.

6장: 잠금 메커니즘 및 트랜잭션 제어

InnoDB 엔진은 행 수준 잠금을 제공하고 공유 잠금을 지원합니다. 두 가지 잠금 모드: 배타적 잠금 및 네 가지 격리 수준. mysql은 AUTOCOMIT, START TRANSACTIONS, COMMIT 및 ROLLBACK과 같은 문을 통해 로컬 트랜잭션을 지원합니다.

7장: SQL의 보안 문제

SQL 주입: 일부 데이터베이스의 외부 인터페이스 활용 실제 데이터베이스 운영음성(sql)에 사용자 데이터를 삽입하여 데이터베이스나 심지어 운영체제까지 침입하려는 목적을 달성합니다. 주된 이유는 프로그램 힙 사용자가 입력한 데이터가 엄격하게 필터링되지 않아 잘못된 데이터베이스 쿼리문이 실행되기 때문입니다. 예방 조치:

prepareStatement = Bind-variable, 스플라이싱 sql 사용하지 마세요
애플리케이션에서 제공하는 변환 기능을 사용하세요
사용자 정의 기능 검증(폼 검증 등)

8장: SQL 모드 및 관련 문제

다음과 같은 기본 mysql 실행 모드를 변경합니다. 다음 엄격 모드 삽입 또는 업데이트가 올바르지 않으면 mysql은 오류를 표시하고 작업을 포기합니다. set session sql_mode='STRICT_TRANS_TABLES'. sql_mode를 설정하려면 애플리케이션 담당자가 다양한 이득과 손실을 평가하고 적절한 선택을 해야 합니다.

9장: 일반적인 SQL 팁

최대/최소 값이 포함된 행 검색: MAX([DISTINCE] expr), MIN([DISTINCE] expr)
rand()/rand(n)을 능숙하게 사용하여 임의의 행 추출
group by 및 with rollup 절을 사용하여 통계 수행
bit group functions를 이용해 통계를 작성하세요

10장: 주의가 필요한 기타 문제

데이터베이스 및 테이블 이름 대소문자 문제: 플랫폼과 시스템마다 대소문자 구분이 다릅니다. 조언은 항상 소문자 이름을 사용하는 것입니다.
외래 키 사용 시 주의 사항: mysql의 InnoDB는 외래 키워드 제약 조건 검사를 지원합니다.

11장: SQL 최적화

SQL 최적화를 위한 일반 단계:

1. 다음을 사용합니다. 다양한 SQL의 실행 빈도와 다양한 SQL의 대략적인 실행 비율을 이해할 수 있도록 상태 및 응용 특성을 보여줍니다. 예를 들어 InnoDB의 매개변수 Innode_rows_read 쿼리에서 반환된 행 수, 삽입 수행 시 Innodb_rows_inserted에서 삽입된 행 수, Innodb_rows_updated에서 업데이트된 행 수입니다. 몇 가지 매개변수도 있습니다. Connections는 mysql 서버에 연결을 시도하고, Uptime 서버의 작업 시간 및 Slow_queries 느린 쿼리 수를 나타냅니다.

2. 실행 효율성이 낮은 SQL 문을 찾습니다. 두 가지 방법이 있는데, 하나는 느린 쿼리 로그를 통해 실행 효율성이 낮은 명령문을 찾는 것입니다. --log-slow-queries[=file_name] 옵션으로 시작하면 mysqld는 실행 시간이 long_query_time을 초과하는 모든 SQL 명령문을 포함하는 로그 파일을 작성합니다. 초. 다른 하나는 스레드의 상태 , 테이블 잠금 여부 등을 포함하여 현재 MySQL 스레드를 보기 위해 processlist를 표시하는 것입니다. SQL 실행 상태를 실시간으로 보고 일부 잠금 테이블 작업을 최적화할 수 있습니다.

3. EXPLAIN을 통해 비효율적인 SQL의 실행 계획을 분석합니다. explain은 인덱스를 사용하여 레코드를 찾는 더 빠른 SELECT를 얻기 위해 테이블을 인덱스해야 하는 시기를 알 수 있습니다. 설명은 다음과 같습니다. 얻은 결과 중:

select_type: 선택 유형
table: 결과 집합을 출력하는 테이블
type: 나타냅니다. 테이블 유형의 연결. 유형 값이 시스템인 테이블에 행이 하나만 있는 경우 이것이 가장 좋은 연결 유형입니다. 선택 작업에서 테이블 연결에 인덱스가 사용되면 유형 값은 선택 테이블 연결이 아닌 경우 ref입니다. 인덱스를 사용하면 type의 값이 자주 나타나는데, ALL이면 해당 테이블에 대해 전체 테이블 스캔을 수행했다는 의미입니다. 이때 테이블 연결 효율성을 높이기 위해 인덱스 생성을 고려해야 합니다.
possible_keys: 쿼리 시 사용할 수 있는 인덱스 열을 나타냅니다.
key: 사용된 인덱스를 나타냅니다.
key_len: 인덱스 길이
rows: 스캔 범위
추가: 실행 설명 및 설명

4. 문제 확인 그에 상응하는 최적화 조치를 취합니다.

인덱스 문제

인덱스의 저장 분류: 마이삼 테이블의 데이터 파일과 인덱스 파일이 자동으로 분리되며, innodb는 테이블스페이스 내부의 같은 위치에 배치됩니다. myisam과 innodb의 인덱스 저장 유형은 모두 btree입니다
Mysql이 인덱스를 사용하는 방법: 인덱스는 열에서 특정 값을 가진 행을 빠르게 찾는 데 사용됩니다. 쿼리에서 인덱스를 사용하기 위한 가장 중요한 조건은 쿼리 조건에서 인덱스 키를 사용하는 것입니다. 다중 컬럼 인덱스인 경우 다중 컬럼 키의 가장 왼쪽 접두사가 사용되는 경우에만 인덱스를 사용할 수 있습니다. 그렇지 않으면 인덱스를 사용할 수 없습니다.
인덱스 사용 확인: Handler_read_key 값은 행이 인덱싱된 횟수를 나타냅니다. 값이 낮을수록 해당 인덱스가 자주 사용되지 않음을 나타냅니다. Handler_read_rnd_next 값이 높으면 쿼리가 비효율적으로 실행되고 있으며 이를 해결하기 위해 인덱스를 생성해야 함을 의미합니다. show status like 'Handler_read%';

간단하고 실용적인 두 가지 최적화 방법

일반 분석 테이블: ANALYZE TABLE, CHECK TABLE, CHECKSUM TABLE
OPTIMIZE 테이블 사용;

클라이언트(코드 측) 관점에서 최적화

연결 오버헤드를 방지하려면 데이터베이스에 대한 지속적인 연결을 사용하세요. 코드에서는 일반적으로 연결 풀
을 사용하여 모든 삽입이 실제로 필요한 인덱스를 사용하는지 확인합니다.
읽기 및 쓰기 충돌로 인한 테이블 잠금과 관련된 문제를 방지하려면 자주 업데이트되는 테이블에서 복잡한 선택 쿼리를 실행하지 마세요.
기본값을 최대한 활용하고 기본값과 다른 경우에만 값을 명시적으로 삽입하세요. 삽입 속도를 높이기 위해 MySQL이 수행해야 하는 구문 분석을 줄입니다.
읽기와 쓰기를 분리하면 성능이 향상됩니다
이를 방지하려면 변수에서 자체 증가 테이블 필드를 사용하지 마세요. 높은 동시성 조건에서 이 필드가 발생하는 것을 방지합니다. 자동 증가는 효율성에 영향을 미칩니다. 애플리케이션을 통해 필드 자동 증가를 구현하는 것이 좋습니다.

12장: 데이터베이스 최적화 객체

테이블 데이터 유형 최적화: PROCEDURE ANALYZE()현재 테이블 유형을 판단하기 위한 최적화 제안을 제공합니다. 실제로 통계 정보는 실제 최적화와 결합하여 사용될 수 있습니다.

분할을 통해 테이블 액세스 효율성 향상: 여기서 분할은 주로 Myisam 유형 테이블에 대한 것입니다.

수직 분할: 애플리케이션 액세스 빈도에 따라 테이블에서 자주 액세스하는 필드와 자주 액세스하지 않는 필드를 두 개의 테이블로 분할하여 최대한 확실하게 액세스해야 합니다. 긴.
수평 분할: 애플리케이션 상황에 따라 데이터를 의도적으로 여러 테이블로 수평으로 분할하거나 파티션을 통해 여러 파티션으로 분할하여 Myisam 테이블 읽기로 인한 잠금 문제를 효과적으로 방지할 수 있습니다. 가져오기 및 업데이트.

비정규화: 정규화된 디자인은 데이터의 중복을 최대한 줄여야 합니다. 즉, 동료는 더 많은 물리적 공간을 차지하게 됩니다. 시험이 문제를 낳는다. 적절한 중복성은 다중 테이블 액세스를 줄이고 쿼리 효율성을 크게 향상시킬 수 있습니다. 이 경우 효율성을 높이기 위해 적절한 중복성을 고려할 수 있습니다.

중복 통계 테이블 사용: 통계 분석을 위해 create temporary table 사용

보다 적절한 테이블 유형 선택: 1. 애플리케이션에 심각한 잠금 충돌이 있는 경우 의도적으로 변경할지 여부를 고려하십시오. InnoDB에 대한 스토리지 엔진의 행 잠금 메커니즘은 잠금 충돌 발생을 효과적으로 줄일 수 있습니다. 2. 애플리케이션에 쿼리 작업이 많고 트랜잭션 무결성에 대한 엄격한 요구 사항이 없는 경우 Myisam 사용을 고려할 수 있습니다.

13장: 잠금 문제

잠금 획득 대기: table_locks_waited 및 table_locks_immediate 상태 변수를 통해 시스템 테이블 분석 잠금 경합. 행 잠금 경합을 분석하려면 Innode_row_lock을 확인하세요.

14장: Mysql 서버 최적화

Mysql 서버의 현재 매개변수 보기

서버 매개변수 기본값 보기: mysqld --verbose --help
실제 서버 매개변수 값 보기: shell> mysqladmin variables or mysql> SHOW VARIABLES
서버 실행 상태 값 보기: mysqladmin extended-status or mysql>SHOW STATUS

Mysql 성능에 영향을 미치는 중요 매개변수

key_buffer_size: keycache
table_cache: 열림 위치 데이터베이스 캐시 수
innode_buffer_pool_size: InnoDB 데이터 및 인덱스를 캐싱하기 위한 메모리 버퍼 크기
innodb_flush_log_at_trx_commit: 설정하는 것이 좋습니다. 커밋할 때마다 로그 버퍼가 로그 파일에 기록되고, 디스크 작업에 의해 로그 파일이 새로 고쳐집니다.

15장: I/O 문제

디스크 검색은 엄청난 성능 병목 현상을 발생시킵니다.