나의 여름방학 영어 작문 MySQL 성능 점검 및 최적화 방법

1. 인덱스가 작성되지 않았습니다.
3. 구성이 잘못되었습니다.
1.
Mysql이 CPU를 많이 소모하는 경우 mysql 클라이언트 도구를 사용하여 확인할 수 있습니다.
Linux에서 실행
/usr/local/mysql/bin/mysql -hlocalhost -uroot -p
비밀번호를 입력하세요. 비밀번호가 없을 경우 -p 매개변수 없이 클라이언트 인터페이스에 들어갈 수 있습니다.
현재 실행 상태 보기
show full processlist
여러 번 실행할 수 있습니다
이 명령은 현재 실행 중인 sql 문을 볼 수 있으며 실행된 sql, 데이터베이스 이름 및 실행을 알려줍니다. 상태, 클라이언트 IP, 사용된 계정, 실행 시간 및 기타 정보
내 캐시 백엔드에는 대부분 SQL 문이 표시되지 않는 것이 비교적 정상적인 것 같습니다. sql 문이 많이 보이면 이 mysql에 성능 문제가 있는 게 틀림없습니다
성능 문제가 발생하면 다음과 같이 분석할 수 있습니다.
1. 멈춰 있는 sql 문이 있나요?
이런 상황이 많죠? 데이터베이스가 myisam을 사용하는 경우 데이터 테이블을 잠그는 쓰기 스레드가 있을 수 있습니다. 이 문이 끝나지 않으면 다른 문을 실행할 수 없습니다.
프로세스 목록의 시간 항목을 확인하여 실행하는 데 시간이 오래 걸리는 명령문이 있는지 확인하세요.
2. 동일한 SQL문이 다수 실행되고 있는 경우
이런 경우에는 SQL문의 실행 효율이 낮은 것일 수도 있습니다.
그런 다음 의심되는 모든 진술을 수집하고 desc(explain)를 사용하여 이러한 진술을 확인하십시오.
먼저 일반적인 설명 출력을 살펴보세요.
mysql> desc select * from imgid=1651768337;
---- ------------- --- - --- ------- --------------- --------- --------- ------ - ------ -------
| id | 가능_키 | ref | 행 | ------- ------- ------- --------------- --------- ---- - ---- ------- ------ ---- 1 | imgs | 기본 | ---- ------------- ------- ------- --------------- --- - ----- --------- ------- ------ -------
세트당 1행(0.00초)
참고 키 , 행 및 Extra는 세 가지 항목입니다. 이 문에서 반환된 결과는 SQL이 쿼리에 PRIMARY 기본 키 인덱스를 사용함을 보여줍니다. 결과 집합 수는 1입니다. Extra는 표시되지 않으며 이는 정렬이나 기타 작업이 없음을 증명합니다. 사용된. 이 결과를 통해 mysql이 인덱스에서 imgid=1651768337 레코드를 쿼리한 다음 실제 테이블에서 모든 필드를 검색한다는 것을 추론할 수 있는데 이는 매우 간단한 작업입니다.
key는 현재 SQL에서 사용할 인덱스를 나타냅니다. MySQL은 간단한 명령문을 실행할 때 하나의 인덱스만 사용할 수 있습니다. 행은 반환되는 결과 집합의 크기이며 결과 집합은 모두입니다. 검색에 이 색인을 사용하여 일치합니다. 결과는 일반적으로 쿼리 및 정렬 방법을 표시합니다.
키를 사용하지 않거나 행이 매우 크고 파일 정렬을 사용하는 경우 일반적으로 효율성에 영향을 미칩니다. 예:
mysql> desc select * from imgs where userid="7mini" 순서 클릭 내림차순 10;
---- ------------- ------- ------ ------------- -- -- ---- --------- ------ ------- ------- ------ -----
| id | 가능_키 | ref | 행 ----- - ------ ------ --------------- ------ --------- -- ---- -- --------
| 단순 | ALL | NULL | NULL | 12506 파일 정렬 사용 | - ----- ---------- ------ --------- ------ ------- ------ ---- ------------------
1개 행(0.00초)
이 SQL 결과 집합에는 12506개의 항목이 사용되므로 실행하는 데 비용이 매우 많이 듭니다. 이때 mysql이 실행되면 전체 테이블을 스캔하여 userid="7mini"와 일치하는 레코드를 하나씩 찾은 다음 이러한 레코드의 클릭을 정렬하는 효율성을 상상할 수 있습니다. 실제 실행 시 상대적으로 빠른 것으로 확인된 경우는 서버 메모리가 상대적으로 짧은 레코드 12506개를 모두 메모리에 읽어 들일 만큼 충분하기 때문이므로 그래도 동시성이 증가하거나 테이블이 커지면 상대적으로 빠르다. 효율성 문제가 심각해질 것이다.
이번에 index에 userid를 추가했습니다.
imgs(userid)에 index userid를 생성합니다.
그런 다음 다시 확인합니다.
mysql> desc select * from imgs where userid="7mini" order by 클릭 수 제한 10
---- ------------- ------- ------ ------------ -- - -------- --------- ------- ------ ---- -- ----------
| id | 가능_키 | ref | -- ---- ------- ------ --------------- -------- -------- -- - ------ ------ -------------
| 단순 | | ref | userid | const 8 사용 | -- --------------- -------- --------- ------- ------ --- -------------
1 row in set (0.00초)
글쎄, mysql이 사용된 것을 볼 수 있다 이번에는 userid 인덱스를 이용하여 검색한 결과 8개의 결과가 나왔습니다. Filesort를 이용하여 하나씩 정렬하였지만, 결과 세트에 8개 항목만 포함되어 있어 효율성 문제가 완화되었습니다.
그러나 다른 사용자 ID로 쿼리하면 결과가 달라집니다.
mysql> desc select * from imgs where userid="admin" order by clicks desclimit 10; ----------- ------- ------ --------------- -------- - -- ------ ------- ------ ------------------
| ID | 가능 키 | 행 | 추가 | - ------ --------------- -------- --------- ------- ---- ------------------
| 단순 | 사용자 ID | | 2944 | 파일 정렬 사용
---- ------------- ------- ------ ------- -------- --------- ------- ------ ------------- ----------------
1 row in set (0.00초)
이 결과는 userid="7mini"의 결과와 기본적으로 동일하지만 mysql에서는 userid를 사용합니다. 한 번 검색할 인덱스 최종 결과 세트의 크기는 2944에 도달합니다. 이 2944개의 레코드는 파일 정렬을 위해 메모리에 추가됩니다. 효율성은 7mini보다 훨씬 나쁩니다. 이 문제를 해결하는 방법은 두 가지가 있는데, 첫 번째 방법은 클릭수를 기준으로 가장 큰 10개의 데이터만 구하면 되기 때문에 인덱스와 판단 조건을 추가하는 방법이 있는데, 데이터가 너무 많기 때문입니다. 클릭수가 10회 미만이면 이러한 데이터가 큰 부분을 차지할 수 있습니다.
클릭에 인덱스를 추가한 다음 where 조건을 추가하고 다시 쿼리합니다.
imgs(clicks)에 대한 인덱스 클릭 생성
mysql> desc select * from imgs where userid="admin" order by clicks; 내림차순 10;
---- ------------- ------- ------ ------------- - - -------- --------- ------- ------ ----- - ----------
| id | 가능_키 | ref | 행 | - ---- ------- ------ --------------- -------- --------- - - ------ ------ -------------
| 단순 | ref | userid,clicks | const | 2944 | 파일 정렬 사용 | --- --------------- -------- --------- ------- ------ - - --------------
1 row in set (0.00초)
이때 maximum_keys 가 되는 것을 볼 수 있습니다. Userid, clicks 및 available_keys는 모두 일치할 수 있는 인덱스입니다. MySQL은 available_keys의 인덱스 중 하나를 판단하고 사용하여 명령문을 실행합니다. MySQL에서 사용하는 인덱스는 최적화되지 않을 수 있다는 점에 유의할 가치가 있습니다. 이번에는 userid 인덱스를 이용하여 mysql을 쿼리했는데, 내 뜻대로 되지 않아서 여전히 결과가 바뀌지 않았다.sql을 변경하고 사용 인덱스를 추가하여 mysql이 클릭 인덱스를 사용하도록 합니다:
mysql> desc select * from imgs use index (clicks) where userid='admin' and clicks>10 order by clicks desclimit 10
- -- -------------- ------- ------- --------------- ----- -- - --------- ------ ------ -------------
| 선택_유형 | | 키 | 키 | 행 | 추가
---- ---------- -- -------- -------- --------- ------ ------ ----------- --
| 1 | imgs | 클릭수 | NULL | -- -- ------- --------------- -------- --------- ------ - -- ----------------
1 row in set (0.00초)
이때 mysql은 clicks index를 쿼리에 사용하지만 결과 set이 더 크다. 다시 제한해야 합니다:
mysql> desc select * from imgs 사용 인덱스(클릭) 여기서 userid='admin' 및 클릭>1000 클릭 수 제한 10
---- ----- -------- ------- ------- --------------- -------- ----- ---- ------ ------ -------------
| id | key_len | 행 | 추가
---- ------------- ------- ------- -------- ----- -- -------- --------- ------ ------ -------------
| 단순 | imgs | 클릭수 | NULL | 312 | - ---- --- --------------- -------- --------- ------ ---- -- --- ----------
세트당 1행(0.00초)
1000을 더하면 결과 세트는 312가 되므로 정렬 효율성은 허용할 수 있을 정도입니다.
그러나 인덱스 변경 최적화 방법을 사용하려면 이 예에서 숫자 1000과 같은 샘플링 지점을 가져와야 합니다. 이런 방식으로 userid의 각 값에 대해 샘플링 지점을 찾아야 하는데 이는 프로그램에 문제가 됩니다. . 다루기가 매우 어렵습니다. 샘플링이 1000을 기준으로 한다면 userid='7mini'의 예에서 얻은 결과는 8이 아니라 2가 되어 사용자에게 혼란을 줍니다.
물론 이중 인덱스를 추가하는 다른 방법도 있습니다.
imgs에 userid_clicks 인덱스 만들기(userid, clicks)
mysql> desc select * from imgs where userid="admin" order by clicks desclimit 10 ;
---- ------------- ------- ------ --- - ----- --------------- --------- ------- ------ ------- - -----
| id | 가능_키 | ref | 행 | ------ ------ --------- --------------- - -------- ------- ------ -------------
| imgs | userid_clicks | 51 | const | 2944 | where
사용 ---- ---------- - ---- --------------- --------- ------- -- ---- -------------
세트의 행 1개(0.00초)
이때 결과 세트에는 여전히 2944개의 항목이 있지만 파일 정렬은 추가사항이 없습니다. 이때 mysql은 userid_clicks 인덱스를 사용하여 userid="admin"인 모든 레코드를 빠르게 쿼리할 수 있을 뿐만 아니라 결과를 클릭수에 따라 정렬하므로 이 결과 세트를 메모리에 읽어들일 필요가 없습니다. 하나씩 정리하면 효율성이 훨씬 높아집니다.
그러나 다중 필드 인덱스를 사용하는 데에는 문제가 있습니다. SQL 쿼리 유형이 많으면 신중하게 계획해야 하며, 그렇지 않으면 많은 인덱스가 생성되므로 데이터 효율성에 영향을 미칠 수 있습니다. 삽입 및 업데이트뿐만 아니라 데이터 테이블도 손상되기 쉽습니다.
위는 인덱스를 최적화하는 방법인데 이유가 더 복잡할 수 있기 때문에 작성 시간이 길어질 것입니다. 일반적으로 인덱스를 최적화하고 나면 mysql의 효율성이 n단계 향상되므로 별 문제가 없습니다. 문제를 해결하려면 더 많은 시스템을 추가하는 것을 고려해야 합니다.
그러나 mysql 또는 심지어 모든 데이터베이스가 제한 문제를 해결하지 못할 수도 있습니다. mysql에서는 인덱스가 적절한 한 제한 0과 10에는 문제가 없지만 제한 100000과 10은 매우 느립니다. 왜냐하면 mysql은 정렬된 결과를 스캔한 다음 100000 지점을 찾아 10개의 레코드를 꺼내서 반환하기 때문입니다. 그들을. 100,000개 지점을 찾으려면 100,000개의 레코드를 스캔해야 합니다. 이 주기는 상대적으로 시간이 많이 걸립니다. 이 스캐닝 엔진을 최적화할 수 있는 좋은 알고리즘이 있는지 모르겠습니다. 열심히 생각해 봤지만 좋은 방법이 생각나지 않습니다. 한계에 관해서는 지금부터 비교적 먼 미래까지는 비즈니스, 프로그램 및 데이터 테이블 계획을 통해서만 최적화할 수 있다고 생각합니다. 제가 생각한 최적화 방법 중 어느 것도 완벽한 전략이 아니며 이에 대해서는 나중에 논의하겠습니다.
2. sql 작성 방법이 너무 복잡합니다
groupby나 다중 테이블 공동 쿼리 등 sql 작성 방법에 일부 특수 기능을 사용하는 경우 mysql을 쿼리할 때 어떤 방법을 사용하면 되나요? 복잡한 SQL을 하는 경우가 많지 않아 자주 분석하지도 않고, 당분간 좋은 제안도 없어 여기에 사용합니다.
3. 구성 오류
구성의 주요 매개변수는 key_buffer, sort_buffer_size/myisam_sort_buffer_size입니다. 이 두 매개변수는 다음을 의미합니다.
key_buffer=128M: 모든 테이블의 인덱스가 이 메모리 영역에 배치됩니다. 인덱스가 상대적으로 크면 조금 더 크게 열어도 되는데 저는 보통 128M으로 설정하는데, 거의 사용하지 않고 상대적으로 큰 테이블을 다른 곳으로 옮기는 방법을 찾는 것이 메모리를 크게 줄일 수 있습니다. MySQL이 차지했습니다.다른 구성:
thread_c
interactive_timeout=30
wait_timeout=30: 이 두 구성은 10~30초 동안 사용할 수 있으며, 이는 가능한 한 빨리 메모리 리소스를 해제합니다. 사용됨 연결이 끊어지지 않습니다. 이 구성은 오랫동안 활성화되지 않은 연결만 끊습니다.
query_cache: 이 기능을 사용하지 마세요. 이제 많은 사람들이 문자 캐시를 마치 보물을 보는 것처럼 봅니다. MySQL의 query_cache는 테이블 데이터가 변경될 때마다 테이블에 연결된 모든 캐시를 다시 지웁니다. 업데이트가 자주 발생하면 query_cache는 도움을 줄 수 없을 뿐만 아니라 효율성에도 큰 영향을 미칩니다. 이 매개변수는 읽기 전용 데이터베이스에만 적합합니다. 이를 사용해야 하는 경우 query_cache_type=2만 사용하여 SQL_CACHE를 사용하여 캐싱할 일부 SQL을 지정할 수 있습니다.
max_connections: 기본값은 100으로 일반적으로 충분하지만 일반적으로 더 높게 설정해야 하며 400~600이면 충분합니다. 600을 초과하면 일반적으로 효율성 문제가 발생하므로 다른 대책을 찾아야 합니다. 그냥 의지하세요. 이 숫자를 늘리는 것은 답이 아닙니다.
다른 구성은 그냥 기본값으로 놔두셔도 됩니다. 개인적으로 문제는 그리 크지 않다고 생각합니다. 주의할 점은 다음과 같습니다. 1. 구성은 매우 중요하지만 대부분의 경우 효율성 문제의 원인은 아닙니다. 2. MySQL은 데이터베이스입니다. 데이터베이스에 대해 고려해야 할 가장 중요한 것은 효율성이 아니라 안정성과 데이터 정확성입니다.
4. 머신이 실제로 부하를 견딜 수 없습니다.
위 조정을 수행한 후에도 서버가 여전히 부하를 견딜 수 없는 경우 아키텍처 수준 조정을 통해서만 서버를 최적화할 수 있습니다.
1. mysql 동기화.
mysql 동기화 기능을 통해 여러 슬레이브 데이터베이스에 데이터를 동기화하면, 마스터 데이터베이스는 슬레이브 데이터베이스에서 쓰고 읽습니다.
저는 개인적으로 mysql 동기화를 사용하는 것을 별로 좋아하지 않습니다. 이 방법은 프로그램의 복잡성을 증가시키고 종종 데이터 오류를 유발할 수 있기 때문입니다. 부하가 높은 서비스에서는 충돌이 나면 빠르게 다시 시작할 수 있지만 데이터가 잘못된 경우 복구하는 것이 더 번거롭습니다.
2. 캐시 추가
캐시를 추가하면 동시성 문제를 해결할 수 있으며 효과는 확실합니다. 실시간 시스템인 경우 캐시를 최신 상태로 유지하기 위해 캐시를 새로 고치는 것을 고려할 수 있습니다.
적중률이 높은 애플리케이션에서는 기본적으로 문제를 해결할 수 있도록 프런트엔드 아키텍처에 Squid를 추가하는 것이 좋습니다.
캐싱이 프로그램 로직 계층에서 수행되면 복잡성이 많이 추가되고 문제를 해결하기가 더 어려워집니다. 이 수준에서 조정하는 것은 권장되지 않습니다.
3. 여러 데이터베이스 동시접속을 지원하도록 프로그램 구조 조정
웹을 캐시에 추가한 후에도 문제가 여전히 심각한 경우, 프로그램 구조를 조정하고, 애플리케이션을 해체한 후 사용하는 방법밖에 없습니다. 동시에 서비스를 제공하는 여러 기계.
해체되면 비즈니스에 약간의 영향을 미칠 것입니다. 비즈니스의 일부 기능이 모든 데이터를 사용해야 하는 경우 하나의 완전한 라이브러리와 n개의 분산된 라이브러리의 아키텍처를 사용할 수 있습니다. 전체 라이브러리와 분산된 라이브러리를 한 번만 수행하거나 전체 라이브러리를 정기적으로 구성합니다.
물론 가장 어리석은 방법도 있는데, 데이터베이스의 전체 복사본을 만든 다음 프로그램이 매번 이러한 라이브러리에서 전체 SQL을 실행하고 액세스 중에 액세스를 폴링하는 것입니다. mysql보다 동기식으로 더 안전합니다.
4. mysql 프록시 사용
mysql 프록시는 프록시를 통해 데이터베이스의 테이블을 여러 서버에 분산시킬 수 있지만, 인기 있는 콘텐츠가 여러 테이블에 분산되어 있는 경우 문제를 해결할 수 없습니다. 방법을 사용하면 비교적 쉽게 문제를 해결할 수 있습니다.
이 소프트웨어를 사용해 본 적도 없고 자세히 확인해 본 적도 없지만 그 기능, 즉 여러 테이블 간의 공동 쿼리를 어떻게 구현하는지에 대해 조금 의문이 듭니다. 구현이 가능하다면 얼마나 효율적일까요? >5. memcachedb 사용
mysql을 지원하는 memcachedb로 데이터베이스를 변경하는 것은 memcachedb의 구현 방법이나 수준 측면에서 볼 때 데이터에 영향을 주지 않으며 문제가 발생하지도 않는다는 생각입니다. 사용자에게.
데이터베이스에 큰 문제가 없어서 아직 시도해보지 않았습니다. 그러나 MySQL의 주요 구문을 대부분 지원하고 안정적이라면 사용성에는 의심의 여지가 없습니다.
위 내용은 저의 여름방학 영어작문 내용을 포함하여 저의 여름방학 영어작문 mysql 성능 확인 및 최적화 방법을 소개하고 있으니 PHP 튜토리얼에 관심이 있는 친구들에게 도움이 되었으면 좋겠습니다.