mysql 데이터베이스 최적화 요약

PHP 개발에서 mysql 데이터베이스를 사용하는 것은 모든 PHP 프로그래머의 습관이 되었습니다. PHP가 mysql을 더 빠르고 편리하게 운영하려면 MySQL 최적화도 인터뷰에서 자주 묻는 질문이므로 한 번 들어보겠습니다. 함께 봐!

1. MySQL 쿼리 캐시 최적화

MySQL 서버에서 쿼리할 때 고속 쿼리 캐시를 활성화할 수 있습니다. 데이터베이스 엔진이 백그라운드에서 조용히 작업을 처리하도록 하는 것은 성능을 향상시키는 가장 효과적인 방법 중 하나입니다. 동일한 쿼리가 여러 번 실행될 때 결과를 캐시에서 가져오면 속도가 상당히 빠릅니다.
그러나 가장 큰 문제는 그것이 너무 쉽게 숨겨져 대부분의 프로그래머가 무시한다는 것입니다. 일부 처리 작업에서는 실제로 쿼리 캐시가 작동하지 못하게 할 수 있습니다.

// query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
 $today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
 // query cache does NOT work
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
 // query cache works!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. EXPLAIN을 사용하여 SELECT 쿼리를 더 명확하게 만드세요

EXPLAIN 키워드를 사용하면 MySQL이 수행하는 쿼리 작업의 종류를 이해할 수 있는 또 다른 MySQL 최적화 팁이 됩니다. 병목 현상을 찾는 데 도움이 되고 쿼리 또는 테이블 구조가 어디에서 잘못되었는지 보여줍니다.
EXPLAIN 쿼리의 결과는 어떤 인덱스가 참조되고 있는지, 테이블이 어떻게 스캔되고 정렬되는지 등을 알려줄 수 있습니다.
SELECT 쿼리(가급적 조인이 포함된 더 복잡한 쿼리)를 구현하고 여기에 키워드 설명을 추가하면 테이블에 결과가 표시됩니다. 예를 들어 조인을 수행할 때 인덱스에 열을 추가하는 것을 잊어버린 경우 EXPLAIN을 사용하면 문제를 찾는 데 도움이 될 수 있습니다.

group_id 필드에 인덱스를 추가한 후

3. 고유한 행을 얻으려면 LIMIT 1을 사용하세요

때때로 테이블을 쿼리하려면 다음 작업만 수행하면 됩니다. 한 행을 보세요. 매우 고유한 레코드를 찾고 있을 수도 있고 WHERE 절을 충족하는 존재하는 레코드 수를 확인하고 있을 수도 있습니다.
이 경우 LIMIT 1을 추가하면 쿼리가 더 효율적이 됩니다. 이러한 방식으로 데이터베이스 엔진은 전체 테이블이나 인덱스를 검색하는 대신 단 1개만 찾은 후 검색을 중지합니다.

 // do I have any users from Alabama?   
 // what NOT to do:   
 $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }     
 // much better:   
 $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");   
 if (mysql_num_rows($r) > 0) {   
     // ...   
 }

4. 인덱스의 검색 필드

인덱스는 기본 키나 고유 키가 아닙니다. 테이블의 열을 검색하려면 항상 인덱스를 가리켜야 합니다.

5. 조인된 인덱스가 동일한 유형인지 확인하세요.

애플리케이션에 여러 조인 쿼리가 포함된 경우 연결하는 열이 두 테이블 모두에서 인덱스되는지 확인해야 합니다. 이는 MySQL이 내부 조인 작업을 최적화하는 방법에 영향을 미칩니다.
추가된 열은 동일한 유형이어야 합니다. 예를 들어, DECIMAL 열을 조인하고 동시에 다른 테이블의 int 열을 조인하는 경우 MySQL은 표시기 중 하나 이상을 사용할 수 없습니다. 문자 인코딩도 문자열 유형과 동일해야 합니다.

 // looking for companies in my state   
 $r = mysql_query("SELECT company_name FROM users  
     LEFT JOIN companies ON (users.state = companies.state)  
     WHERE users.id = $user_id");   

 // both state columns should be indexed   
 // and they both should be the same type and character encoding   
 // or MySQL might do full table scans

6. BY RAND() 명령을 사용하지 마세요

这是一个令很多新手程序员会掉进去的陷阱。你可能不知不觉中制造了一个可怕的平静。这个陷阱在你是用BY RAND（）命令时就开始创建了。
如果您真的需要随机显示你的结果，有很多更好的途径去实现。诚然这需要写更多的代码，但是能避免性能瓶颈的出现。问题在于，MySQL可能会为表中每一个独立的行执行BY RAND()命令（这会消耗处理器的处理能力），然后给你仅仅返回一行。

 // what NOT to do:   
 $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");   
 // much better:   
 $r = mysql_query("SELECT count(*) FROM user");   
 $d = mysql_fetch_row($r);   
 $rand = mt_rand(0,$d[0] - 1);   
 $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. 尽量避免SELECT *命令

从表中读取越多的数据，查询会变得更慢。他增加了磁盘需要操作的时间，还是在数据库服务器与WEB服务器是独立分开的情况下。你将会经历非常漫长的网络延迟，仅仅是因为数据不必要的在服务器之间传输。
始终指定你需要的列，这是一个非常良好的习惯。

 // not preferred   
 $r = mysql_query("SELECT * FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";   
 // better:   
 $r = mysql_query("SELECT username FROM user WHERE user_id = 1");   
 $d = mysql_fetch_assoc($r);   
 echo "Welcome {$d['username']}";    
 // the differences are more significant with bigger result sets

8. 从PROCEDURE ANALYSE()中获得建议

PROCEDURE ANALYSE()可让MySQL的柱结构分析和表中的实际数据来给你一些建议。如果你的表中已经存在实际数据了，能为你的重大决策服务。

9. 准备好的语句

准备好的语句，可以从性能优化和安全两方面对大家有所帮助。
准备好的语句在过滤已经绑定的变量默认情况下，能给应用程序以有效的保护，防止SQL注入攻击。当然你也可以手动过滤，不过由于大多数程序员健忘的性格，很难达到效果。

 // create a prepared statement   
 if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {    
     // bind parameters   
     $stmt->bind_param("s", $state);    
     // execute   
     $stmt->execute();    
     // bind result variables   
     $stmt->bind_result($username);     
     // fetch value   
     $stmt->fetch();    
     printf("%s is from %s\n", $username, $state);     
     $stmt->close();   
 }

10. 将IP地址存储为无符号整型

许多程序员在创建一个VARCHAR（15）时并没有意识到他们可以将IP地址以整数形式来存储。当你有一个INT类型时，你只占用4个字节的空间，这是一个固定大小的领域。
你必须确定你所操作的列是一个UNSIGNED INT类型的,因为IP地址将使用32位unsigned integer。

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

11.永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的(推荐使用UNSIGNED)，并设置上自动增加的AUTO_INCREMENT标志。
就算是你users表有一个主键叫“email”的字段，你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。
而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……
在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。

12. VARCHAR 대신 ENUM을 사용하세요

ENUM 유형은 매우 빠르고 컴팩트합니다. 실제로는 TINYINT를 저장하지만 문자열로 나타납니다. 이런 식으로 이 필드를 사용하여 선택 목록을 만드는 것이 매우 완벽해집니다.
"성별", "국가", "민족", "상태" 또는 "부서"와 같은 필드가 있고 이러한 필드의 값이 제한되고 고정되어 있다는 것을 알고 있는 경우 ENUM을 사용해야 합니다. VARCHAR 대신.
MySQL에는 테이블 구조를 재구성하는 방법을 알려주는 "제안"(항목 10 참조)도 있습니다. VARCHAR 필드가 있는 경우 이 제안에서는 해당 필드를 ENUM 유형으로 변경하라고 알려줍니다. PROCEDURE ANALYSE()를 사용하면 관련 제안을 얻을 수 있습니다.

13. PROCEDURE ANALYSE() p 프로그래머 스테이션에서 제안 받기

PROCEDURE ANALYSE()는 MySQL을 통해 필드와 실제 데이터를 분석하고 몇 가지 유용한 제안을 제공합니다. 일부 중요한 결정을 내리려면 데이터가 기초로 필요하기 때문에 이러한 제안은 테이블에 실제 데이터가 있는 경우에만 유용합니다.
예를 들어 INT 필드를 기본 키로 생성했지만 데이터가 많지 않은 경우 PROCEDURE ANALYSE()는 이 필드의 유형을 MEDIUMINT로 변경하도록 권장합니다. 또는 VARCHAR 필드를 사용하는 경우 데이터가 많지 않아 ENUM으로 변경하라는 제안을 받을 수도 있습니다. 이러한 제안은 모두 데이터가 부족하여 의사결정이 정확하지 않기 때문에 가능한 일입니다.
phpmyadmin에서 테이블을 볼 때 "테이블 구조 제안"을 클릭하면 이러한 제안 사항을 볼 수 있습니다.

이러한 제안 사항은 테이블에 데이터가 점점 더 많아질 때만 변경됩니다. 정확한. 최종 결정을 내리는 사람은 자신이라는 점을 항상 기억하세요.

14. 가능한 한 NOT NULL PHP 프로그래머 스테이션을 사용하세요.

NULL 값을 사용해야 하는 특별한 이유가 없는 한, 항상 다음을 수행해야 합니다. 필드를 NULL이 아닌 상태로 유지하세요. 다소 논란의 여지가 있을 수 있으니 계속 읽어보시기 바랍니다.
먼저 "Empty"와 "NULL"(INT인 경우 0과 NULL)의 차이점이 무엇인지 자문해 보세요. 둘 사이에 차이가 없다고 생각되면 NULL을 사용하면 안 됩니다. (아시나요? Oracle에서는 NULL과 빈 문자열이 동일합니다!)
NULL에는 공백이 필요하지 않고 추가 공간이 필요하며 비교를 수행하면 프로그램이 더 복잡해질 것이라고 생각하지 마십시오. 물론 이것이 NULL을 사용할 수 없다는 의미는 아닙니다. 현실은 매우 복잡하며 여전히 NULL 값을 사용해야 하는 상황이 있을 것입니다.
다음은 MySQL 자체 문서에서 발췌한 것입니다.

15. 준비된 문

준비된 문은 백그라운드에서 실행되는 SQL 문의 모음과 매우 유사합니다. 성능 문제인지 보안 문제인지.
Prepared 문은 바인딩된 일부 변수를 확인할 수 있으므로 "SQL 주입" 공격으로부터 프로그램을 보호할 수 있습니다. 물론 변수를 수동으로 확인할 수도 있습니다. 그러나 수동 확인은 문제가 발생하기 쉽고 프로그래머가 잊어버리는 경우가 많습니다. 일부 프레임워크나 ORM을 사용하면 이 문제가 더 좋아질 것입니다.
성능 측면에서 이는 동일한 쿼리가 여러 번 사용될 때 상당한 성능 이점을 제공합니다. 이러한 준비된 명령문에 대한 일부 매개변수를 정의할 수 있으며 MySQL은 이를 한 번만 구문 분석합니다.
최신 버전의 MySQL은 준비된 명령문을 전송할 때 바이너리 형식을 사용하지만 이로 인해 네트워크 전송이 매우 효율적이 됩니다.
물론, 쿼리 캐싱을 지원하지 않기 때문에 준비된 문을 사용하지 말아야 하는 경우도 있습니다. 그런데 5.1버전 이후부터 지원된다고 하네요. PHP에서 준비된 명령문을 사용하려면 설명서: mysqli 확장을 확인하거나 PDO.

16과 같은 데이터베이스 추상화 계층을 사용할 수 있습니다.

일반적인 상황에서 스크립트에서 SQL 문을 실행하면 더 이상 SQL 문이 반환되지 않을 때까지 프로그램이 중지된 다음 프로그램이 계속 실행됩니다. 버퍼링되지 않은 쿼리를 사용하여 이 동작을 변경할 수 있습니다.
이 문제에 대해 PHP 문서에 아주 좋은 설명이 있습니다: mysql_unbuffered_query() 함수:
위 문장의 번역은 mysql_unbuffered_query()가 MySQL에 SQL 문을 보내는 것을 의미하며 mysql_query()와는 다릅니다. 자동 가져오기 및 캐시된 결과를 위해. 이렇게 하면 특히 많은 수의 결과를 생성하는 쿼리의 경우 상당한 메모리가 절약되며 모든 결과가 반환될 때까지 기다릴 필요가 없으며 데이터의 첫 번째 행만 반환하면 됩니다. 즉시 작업 중입니다. 쿼리 결과가 표시됩니다.
그러나 여기에는 몇 가지 제한 사항이 있습니다. 왜냐하면 모든 행을 읽어야 하거나 다음 쿼리 전에 mysql_free_result()를 호출하여 결과를 지워야 하기 때문입니다. 또한 mysql_num_rows() 또는 mysql_data_seek()은 작동하지 않습니다. 따라서 버퍼링되지 않은 쿼리를 사용할지 여부를 신중하게 고려해야 합니다.

17. IP 주소를 UNSIGNED INT로 저장하세요

많은 프로그래머는 정수 IP 대신 문자열 형식으로 IP를 저장하기 위해 VARCHAR(15) 필드를 만듭니다. 정수를 사용하여 저장하는 경우 4바이트만 필요하며 고정 길이 필드를 가질 수 있습니다. 또한 이는 특히 ip1과 ip2 사이의 IP와 같은 WHERE 조건을 사용해야 하는 경우 쿼리 시 이점을 제공합니다.
IP 주소는 32비트 부호 없는 정수 전체를 사용하므로 UNSIGNED INT를 사용해야 합니다.
쿼리의 경우 INET_ATON()을 사용하여 문자열 IP를 정수로 변환하고 INET_NTOA()를 사용하여 정수를 문자열 IP로 변환할 수 있습니다. PHP에는 ip2long(), long2ip() 함수도 있습니다.

18. 고정 길이 테이블이 더 빨라집니다.

테이블의 모든 필드가 "고정 길이"인 경우 전체 테이블이 "정적" 또는 "고정 길이"로 간주됩니다. 예를 들어 테이블에는 VARCHAR, TEXT, BLOB 유형의 필드가 없습니다. 이러한 필드 중 하나를 포함하는 한 테이블은 더 이상 "고정 길이 정적 테이블"이 아니며 MySQL 엔진은 이를 다른 방식으로 처리합니다.
고정 길이 테이블은 MySQL이 더 빠르게 검색하므로 성능이 향상됩니다. 이러한 고정 길이를 사용하면 다음 데이터의 오프셋을 쉽게 계산할 수 있으므로 자연스럽게 읽는 속도도 빨라집니다. 그리고 필드의 길이가 고정되어 있지 않으면 다음 필드를 찾으려고 할 때마다 프로그램이 기본 키를 찾아야 합니다.
또한 고정 길이 테이블은 캐시하고 다시 작성하기가 더 쉽습니다. 그러나 유일한 부작용은 고정 길이 필드가 일부 공간을 낭비한다는 것입니다. 고정 길이 필드는 사용 여부에 관계없이 너무 많은 공간을 필요로 하기 때문입니다. PHP 프로그래머 스테이션
"수직 분할" 기술(다음 항목 참조)을 사용하면 테이블을 두 개로 분할할 수 있습니다. 하나는 고정 길이이고 다른 하나는 가변 길이입니다.

19. 수직 분할

"수직 분할"은 데이터베이스의 테이블을 열별로 여러 테이블로 변환하는 방법으로, 테이블의 복잡성과 필드 수를 줄여 최적화를 달성할 수 있습니다. 목적. (은행에서 프로젝트를 하다가 100개가 넘는 필드가 있는 테이블을 봤는데 무서웠습니다.)
예제 1: 사용자 테이블에 집 주소인 필드가 있습니다. 이 필드는 선택 사항입니다. database를 운영하는 경우 개인 정보를 제외하고 이 필드를 자주 읽거나 다시 쓸 필요가 없습니다. 그러면 다른 테이블에 넣으면 테이블이 더 좋아질 것입니다.

관련 권장 사항:

MySQL 최적화 요약 - 총 쿼리 수

위 내용은 mysql 데이터베이스 최적화 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!