mysql 성능 최적화 문제 정리
- 王林원래의
- 2019-08-19 15:46:022155검색
Mysql 최적화 종합 문제:
A. 테이블은 3가지 패러다임에 맞춰 합리적으로 설계되었습니다.
B. )[4가지 유형: 일반 인덱스, 기본 키 인덱스, 고유 인덱스, 고유, 전체 텍스트 인덱스]
C, 테이블 분할 기술(수평 분할, 수직 분할)
D, 읽기 및 쓰기 [쓰기: 업데이트/삭제/추가] 분리
E. 저장 프로시저 [속도를 높일 수 있는 모듈형 프로그래밍]
F. 최대 구성 동시성 수, my.ini는 캐시 크기 조정]
G, Mysql 서버 권장 업그레이드
H, 정기적으로 불필요한 데이터 제거 및 정기적으로 조각 모음#🎜🎜 ##🎜 🎜# 추천 Mysql 관련 동영상 튜토리얼:
https://www.php.cn/course/list/51/type/2.html
첫 번째 정규형: 1NF는 속성(열)이 원자적이고 분해될 수 없도록 요구하는 속성에 대한 원자성 제약 조건입니다. 1NF를 만족함)
제2정규형: 2NF는 레코드에 대한 고유 제약조건으로, 레코드에 고유 식별자, 즉 엔터티의 고유성이 있어야 합니다.
제3정규형; : 3NF는 레코드의 고유성에 대한 제한입니다. 필드 중복 제약 조건은 필드가 중복되지 않도록 요구합니다. 중복된 데이터베이스 디자인은 이를 수행할 수 없습니다.
2. SQL 최적화를 위한 일반 단계
작업 단계:
1 show status 명령을 사용하여 다양한 SQL의 실행 빈도를 확인하세요.
2. 실행 효율성이 낮은 SQL 문 찾기 - (키 선택)
3 explain
#🎜 🎜#4로 비효율적인 SQL 문 실행 분석 . 문제를 파악하고 해당 최적화 조치를 취합니다MySQL은 show [session|global] status 명령을 사용하여 서버 상태 정보를 제공할 수 있습니다.session은 현재 연결의 통계 결과를 나타내고, global은 데이터베이스가 마지막으로 시작된 이후의 통계 결과를 나타냅니다. 기본값은 세션 수준입니다. 
주요 사항: Com_select,Com_insert,Com_update,Com_delete 이러한 매개변수를 통해 현재 데이터베이스 응용 프로그램이 주로 삽입 및 업데이트 작업을 기반으로 하는지 쿼리 작업을 기반으로 하는지, 그리고 각각의 What을 기반으로 하는지 쉽게 이해할 수 있습니다. 이 클래스의 대략적인 SQL 실행 비율입니다.
Connections: MySQL 서버에 연결을 시도한 횟수Uptime: 서버가 작동하는 시간(초)
Slow_queries: 느린 쿼리 횟수(기본값은 느린 쿼리 시간 10초) )
#🎜 🎜#'Handler_read%'와 같은 상태 표시 쿼리가 사용된 횟수
기본적으로 Mysql은 전체 쿼리 로그를 기록하지 않습니다. 시작 시 
binmysqld.exe- -log-slow-queries=d: bac.log
구체적인 작업은 다음과 같습니다:
느린 쿼리가 활성화된 경우 기본값은 mysql.ini 파일에 저장됩니다
1. mysql을 다시 시작하고 datadir 경로를 찾은 다음 cmd를 사용하여 data#🎜 🎜#
2. binmysqld 명령을 실행합니다. exe –safe-mode –slow-query-log (실행 전 mysql 서비스 종료 주의)
3. 생성된 로그 파일은 모든 레코드 정보를 기록합니다
#🎜🎜 #명령 종료 기호 수정: (저장 프로시저를 정상적으로 실행하려면 다음을 수행해야 합니다. 명령 끝 기호 수정)
Delimiter $$
느린 쿼리 sql 문 수정 방법 로그에 기록됨(mysql에서는 기본적으로 기록하지 않으므로 지정해야 함) mysql을 시작할 때 쿼리 속도가 느림).
3. 인덱스
♥ 인덱스 종류: ★4가지 인덱스 ① 기본 키 인덱스 ② 고유 인덱스 ③ 일반 index ④ 전체 텍스트 인덱스 1. 추가
1.1 기본 키 인덱스 추가
테이블의 컬럼이 기본 키로 설정된 경우, 열은 기본 키 인덱스입니다.
Createtable aaa(id int unsigned 기본 키 auto_increment,
name varchar(32) not null 기본값);
1.2 일반 인덱스#🎜🎜 #ㅋㅋㅋ 일반적으로 일반 인덱스를 생성하려면 먼저 테이블을 생성한 후 일반 인덱스를 생성합니다. m은 유용하지만 innodb에 유용하지 않습니다. + 사용법:
[ 예] | ('키워드') 4. 전체 텍스트에 불용어가 색인되어 있습니다. 텍스트에서는 무한한 책을 위해 색인이 생성되므로 일부 일반적인 단어 및 문자는 생성되지 않습니다. 이러한 단어를 불용어라고 합니다. 제약조건, 이 열 이때 기본적으로 name이 유일한 인덱스입니다테이블 생성 eee(id int Primary keyauto_increment, name varchar(32)); KEY=UNIQUE+NOT NULL
필드는 null일 수 있고 여러 개의 null을 가질 수 있지만 특정 콘텐츠인 경우에는 불가능합니다. 반복하세요
. 설명 테이블 이름 [이 방법의 단점, 인덱스 이름을 구현할 수 없음]
2. 테이블 이름에서 인덱스 표시 테이블 이름에서 인덱스 표시; 테이블 이름에서 키 표시
3. 삭제
테이블 이름 삭제 인덱스 인덱스 이름, 변경 가능한 테이블 이름 기본 키 삭제. (기본 바늘 이름 삭제)
4. 수정
삭제를 먼저 하세요.
2가지 모두 SQL 작성으로 인한 SQL 속도 저하로 인해 상대적으로 최적화가 편리합니다. 이전 섹션에서 언급했듯이 인덱스를 올바르게 사용하면 쿼리 속도가 빨라질 수 있으므로 SQL 작성 시 인덱스와 관련된 규칙에 주의해야 합니다.
1 필드 유형 변환 결과 문자열과 같은 인덱스가 필요하지 않습니다. 따옴표가 없는 유형. 인덱스를 사용하지 않고 전체 테이블 스캔을 유발할 수 있는 숫자 유형에는 따옴표를 사용하십시오.
2. Mysql은 함수 변환을 지원하지 않으므로 필드 앞에 함수를 추가할 수 없습니다.
3. 필드 앞에서 더하거나 빼지 마세요.
4. 문자열이 상대적으로 긴 경우 인덱스 파일 크기를 줄이고 쓰기 효율성을 높이기 위해 해당 문자열의 일부를 인덱싱하는 것을 고려할 수 있습니다.
5. 마찬가지로 앞의 인덱스를 사용하지 않습니다.
6. 공동 인덱스의 두 번째 및 후속 필드에는 별도의 쿼리가 필요하지 않습니다.
8. 정렬을 위해 오름차순을 사용해 보세요.
9. 또는 쿼리에 대해서는 Union을 사용해 보세요.
10. 복합 인덱스의 선택성이 높은 필드가 먼저 표시됩니다. by/groupby 필드를 인덱스에 포함시켜 정렬을 줄여 효율성이 높아집니다.
위 인덱스 사용 규칙 외에도 SQL 작성 시 다음 사항에 특히 주의해야 합니다.
1 데이터베이스의 동시성 성능 및 마스터-슬레이브 동기화에 영향을 미치는 대규모 트랜잭션 SQL을 피하십시오. ;
2. 페이징 문 제한 문제
3. 테이블의 모든 레코드를 삭제하려면 삭제를 사용하지 마세요.
4.
5. 나중에 방지하기 위해 필드를 입력하고 작성합니다. 테이블 변경으로 인해 발생하는 문제의 경우 성능도 비교적 좋습니다(데이터 사전 분석이 포함되므로 직접 정보를 쿼리하세요). 6. Innodb, Innodb는 통계 정보를 저장하기 때문에 7. Oder by rand()를 주의해서 사용하세요. 3. 느린 쿼리 수를 표시합니다. 'slow_queries'와 같은 상태를 표시합니다. 다음 정보가 생성됩니다. > down off out ¶ ~ ¶llllllllllllmbmbps outlly-- select_type: 쿼리 유형을 나타냅니다. ㅋㅋ ~ .ㅋㅋㅋ | 쿼리: 하위 쿼리의 내부 레이어에 대한 첫 번째 선택, 결과는 외부 쿼리에 의존하지 않음 종속 하위 쿼리: 내부 레이어의 첫 번째 선택은 쿼리, 외부 쿼리에 따라 Union: Union 문, 두 번째 선택은 시작 후 시작됩니다. ~ 통합 결과: 통합 결과(=시스템 테이블) 이것은 const 연결 유형입니다. 특별한 경우입니다. const: 테이블은 최대 일치하는 행 1개 추가 유형: ' s ' s ' s ' ‐ ‑ ‑ ‑ 정렬을 완료하려면 색인을 사용하세요. ~ ‐‐‐‐‐‐‐‐‐ “ WHERE's ‐‐‐‐not‐inging 사용: 귀하 테이블의 모든 정보를 읽을 필요 없이 인덱스를 통해서만 필요한 데이터를 얻을 수 있습니다. 4. 인덱스를 사용하면 쿼리 속도가 빨라지는 이유는 무엇입니까?일반 쿼리에 계속해서 문의를 해야 하고, 추후 문의가 있을지 장담할 수 없습니다. 전체 텍스트 인덱싱이 필요합니다.
■인덱스 사용 시 주의사항
인덱싱 비용:
1. 디스크 공간을 차지합니다.
2. DML(삽입, 업데이트, 생성) 작업에 영향을 주고 속도가 느려집니다.
■ 요약: An 인덱스는 다음 조건이 충족되는 경우에만 생성되어야 합니다.
B 이 필드의 내용은 고유한 값(sex)이 아닙니다.C. 빈번하게 변경
■ 인덱스 사용 참고:
alter table dept add index myind (dname,loc) // dname은 왼쪽 컬럼, loc은 오른쪽 컬럼
에서 인덱스를 사용할 수 있습니다. 다음 상황
다음 상황에서는 인덱스가 사용되지 않습니다.
a. 조건에 or 가 있으면 인덱스가 있는 조건이 있어도 사용되지 않습니다. 즉, 사용되는 모든 필드는 인덱스를 생성해야 합니다. 제안: or 키워드 사용을 피하세요
1: myISAM
요구사항은 높지 않으며 주로 동료들이 BBS에 게시하고 답글을 달고 쿼리하고 추가하는 것입니다.
2: InnoDB
， 거래에 대한 요구 사항이 높습니다. 저장된 데이터는 중요한 데이터입니다. myISAM과 InnoDB의 차이점: 1. MyISAM 배치 삽입은 빠르고 InnoDB 삽입은 느리며 삽입 시 myISAM이 정렬되지 않습니다. 2. InnoDB는 트랜잭션을 지원하지만 myISAM은 트랜잭션을 지원하지 않습니다. 3. MyISAM은 전체 텍스트 인덱스를 지원합니다. 4. 잠금 메커니즘, myISAM은 테이블 잠금, InnoDB는 행 잠금입니다.5 myISAM은 외래 키를 지원하지 않습니다.① 진행 요구 사항이 높으므로 고정 소수점 데이터를 사용하여 값을 저장하는 것이 좋습니다. 그룹 U는 데이터의 정확성을 보장합니다. 엔진에서 작업을 삭제하고 수정하려면 정기적으로 실행해야 합니다. 3 날짜 유형은 실제 요구 사항을 기준으로 저장 공간이 가장 작은 초기 유형을 선택해야 합니다. 데이터베이스 수동 백업: 1.Mysqldump –uroot –proot 데이터베이스 [테이블 이름 1, 테이블 이름 2...] > 파일 경로 Eg: mysqldump -uroot -proot temp > d:/temp.bak 백업 파일 데이터 복원: 소스 d:/temp.bak(mysql 콘솔에서) ) 합리적인 하드웨어 자원 그리고 운영체제 Master Slave1 Slave2 Slave3--슬레이브 3's- 선택에 사용되며 각 데이터베이스 는 훨씬 적은 압력을 공유합니다. . 이 방법을 구현하려면 프로그램을 특별히 설계해야 합니다. 마스터는 쓰기용으로, 슬레이브는 읽기용으로 사용하므로 프로그램 개발에 추가적인 부담이 됩니다. 물론 이 프록시를 구현하기 위한 미들웨어가 이미 있습니다. 이는 프로그램이 어떤 데이터베이스를 읽고 쓰는지 투명하게 보여줍니다. 공식 mysql-proxy가 있지만 아직은 알파 버전입니다. Sina는 이 목적을 달성할 수 있는 amobe를 가지고 있습니다. 구조는 다음과 같습니다:
5. 테이블 분할
수평 분할:
대규모 데이터 테이블 검색을 제공할 때 다음을 기반으로 해야 합니다. 비즈니스 요구에 따라 테이블 기준을 찾아 검색 페이지에서 사용자의 검색 방법을 제한하고 페이징에 협조합니다. ID가 %3인 사용자 사용을 사용하여 결과에 따라 다른 테이블에 넣습니다
create tableqqlogin0(
id int unsigned not null 기본 키,/* 이 ID는 자동 증가로 설정할 수 없습니다*/
H PWD VARCHAR (32) Not Null Default'
) 엔진 = MyISAM 기본 Charset = UTF8; Bleak C 이름 Varchar(32) not null 기본값 '',
pwd varchar(32) Not null 기본값'
) 엔진 = Myisam 기본 CHARSET = unsigned not null 기본; key, / * 이 ID는 self-growth를 설정할 수 없습니다. * /
name varchar (32) not null default '',
PWD VARCHAR (32) NOT NULL DEFAULT'E) Engine = MyISAM 기본 Charset = UTF8; 수직 분할:
테이블의 일부 필드를 넣습니다. 이러한 필드는 쿼리할 때 관련이 없지만 데이터 양이 많습니다. 효율성을 높이기 위해 이러한 필드를 테이블에 넣는 것이 좋습니다.
6. 최적화된 Mysql 구성
MY. INI
port = 3306 기본 포트는 3306입니다.
포트 = 3309를 수정하려면 mysql_connect('localhost: 3309', 'root', 'root')에 주의하세요.
query_cache_size = 15M 는 쿼리 캐시의 크기입니다
InnoDB 매개변수는 다음 두 매개변수로 조정할 수도 있습니다
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 1G
myisam은 key_buffer_size를 조정해야 합니다
상태를 보려면 매개변수를 조정하면 됩니다. 조정할 매개변수를 결정하려면 현재 상태를 확인하세요
7. 증분 백업
실제 사례: 증분 백업 및 복구를 수행하는 방법단계:그림 1과 같이 구성된 내 .ini 파일 또는 my.cof, 바이너리 백업 활성화2, MySQL 재시작
시작 후 mylog 디렉터리에 몇 개의 파일이 생성된 것을 확인할 수 있습니다
그 중: E: 바이너리 로그 mylog.index 인덱스 파일, 어떤 백업 파일이 있는지
E: 바이너리 로그 mylog.000001 , 사용자 개체 데이터베이스 작업을 저장하는 파일
3. 작업(선택)할 때
MySQL 설치 디렉터리에 bin을 입력해야 하는지 확인한 다음 mysqlbinlog를 실행하여 파일을 찾고 파일 경로를 추가합니다
그림 4와 같이 특정 문장의 시점으로 복원
4,1 시점에 따라 응답
Mysqlbinlog -stop-datetime="2013-01-17 12:00: 23"d:/binlog/mylog.000001 | mysq - uroot -p
(중지 시간 전에 모든 데이터 복원)
Mysqlbinlog-start-datetime="2013-01-17 12:00:23" d:/binlog /mylog.000001 | mysq -uroot -p
(시작 시간 이후 모든 데이터 복원)
4,2 위치에 따라 복원
Mysqlbinlog-stop-position = "234" d:/binlog/mylog. 000001 | mysq -uroot -p
(이전의 모든 데이터를 중지하도록 복원)
Mysqlbinlog-start-position="234"d:/binlog/mylog.000001 | mysq -uroot -p
(이후의 모든 데이터 복구 시작 시간)
더 많은 관련 질문이 있으시면 PHP 중국어 웹사이트를 방문하세요: https://www.php.cn/
위 내용은 mysql 성능 최적화 문제 정리의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!