20 mysql 최적화 요약-MySQL 튜토리얼-php.cn

이 기사는 mysql의 최적화에 대한 요약을 제공합니다. 이는 특정 참고 가치가 있습니다. 도움이 필요한 친구들이 참고할 수 있기를 바랍니다.

Preface

요즘에는 데이터베이스 작업이 전체 애플리케이션의 성능 병목 현상을 점점 더 많이 일으키고 있으며, 이는 웹 애플리케이션에서 특히 두드러집니다. 따라서 저는 MySQL 최적화를 위한 몇 가지 제안을 정리했습니다. 이러한 최적화 기술이 여러분에게 도움이 되기를 바랍니다. 요약할 수 없다면 추가해도 좋습니다.

SQL 실행 속도가 느린 이유

느린 네트워크 속도, 메모리 부족, 낮은 I/O 처리량, 전체 디스크 공간 및 기타 하드웨어 문제 # 🎜🎜#
인덱스가 없거나 인덱스가 잘못되었습니다
데이터 테이블에 데이터 레코드가 너무 많습니다.
# 🎜🎜#
#🎜 🎜#
Other
1. EXPLAIN은 SELECT 쿼리를 분석합니다

EXPLAIN 키워드는 MySQL이 SQL 문을 처리하는 방법을 알려주므로 쿼리 문을 분석하는 데 도움이 되므로 최적화 방법과 잠재적인 성능 문제를 최대한 빨리 찾을 수 있습니다. EXPLAIN의 구체적인 용도와 각 매개변수의 의미에 대해서는 관련 문서를 참조하시기 바랍니다.

2. SELECT 쿼리는 필드 이름을 지정해야 합니다.

SELECT * 쿼리는 CPU, I/O 등 불필요한 소비를 많이 추가합니다. 동시에 커버링 지수를 사용하기 위해 증가할 수도 있습니다. 따라서 SELECT 쿼리 수행 시 마지막에 쿼리할 해당 필드 이름을 직접 지정해야 합니다.

3 데이터 조각을 쿼리할 때는 LIMIT 1

을 사용하여 중복 쿼리를 줄이세요. 제한 1을 지정한 후에는 쿼리가 더 이상 계속되지 않기 때문입니다. EXPLAIN의 유형 열이 const 유형에 도달하므로 쿼리 문이 더 좋습니다.

4. 검색된 WHERE 필드에 대한 인덱스를 생성합니다.

일반적으로 각 테이블마다 기본 키를 설정하며, 인덱스가 반드시 기본 키일 필요는 없습니다. WHERE 쿼리 및 검색에 항상 사용하는 필드가 있고 작성하는 것보다 읽는 것이 더 많은 경우 해당 필드에 대한 인덱스를 생성하십시오. 인덱싱 원리에 대해 자세히 알아보려면 관련 정보를 참조하세요. 찾을 수 있습니다.

5. 절대로 ORDER BY RAND()

데이터를 무작위로 얻으려면 첫 번째 항목에서 직접 난수를 사용하라고 지시할 것입니다. 시간이 지나면 이 방향으로 계속 생각하고 이 끔찍한 생각을 빨리 멈추려면 두뇌를 제어해야 합니다. 이러한 종류의 쿼리는 데이터베이스 성능(CPU 소모)에 아무런 이점이 없기 때문입니다. 더 나은 해결책 중 하나는 먼저 데이터의 수 N을 찾은 다음 LIMIT N, 1을 사용하여 쿼리하는 것입니다.

6. 각 테이블에 기본 키 ID가 있는지 확인하세요

LIMIT N, 1这样查询。

6、保证每张表都有一个主键ID

我们应该养成一种习惯，每设计新建一张表的时候，都应该为其设计一个ID字段，并让其成为主键，而且最好是INT型（也有使用UUID的），同时设置这个ID字段为自增（AUTO_INCREMENT）的标志。

8、尽可能的使用 NOT NULL

不要以为NULL不需要空间，事实是NULL也需要额外的空间，也许，很多有没注意但是遇到过，NULL字段在进行查询比较的时候，是比较麻烦的。当然了，如果你实在是必须需要NULL的话，那没辙，就使用吧，否则的话，就建议使用NOT NULL吧。

8、选择合适的存储引擎

在MySQL中有MyISAM和InnoDB两种存储引擎，两者各有利弊，所以我们需要了解两者的差异然后来做出最合适的选择，例如InnoDB支持事务而MyISAM不支持，MyISAM查询比InnoDB快等等；总之，如果你不知道选择什么的话，那就用InnoDB吧。

9、把IP地址存为UNSIGNED INT

在遇到需要存储IP地址的时候，很多人的第一想法都会是存储VARCHAR(15)字符串类型的，而不会想到要用INT整型来存储；如果你用整型来存储，只需要4个字节，并且你可以有定长的字段，而且这会为你带来查询上的优势。

10、尽量不要在WHERE查询时对字段进行null值判断

我们都知道，档我们对一个字段进行null的判断时候，会比较慢的，这是因为这个判断会导致引擎放弃使用所有已有的索引而进行全表扫描搜索。

11、尽量不要使用%前缀的LIKE模糊查询

模糊查询，在日常开发中，我们都会经常遇到，但是我相信很多人都是直接 LIKE '%key_word%' 或者 LIKE '%key_word' 这样搜索的，这两种搜索方式，都会导致索引失效从而进行全表扫描搜索。如果解决上面的这种模糊查询呢，答案就是使用“使用全文索引”，具体的用法有兴趣的可以自己查资料一波。

12、避免在WHERE查询时对字段进行表达式操作

例如查询语句SELECT id FROM table WHERE num * 2 = 50;새 테이블을 디자인할 때마다 새 테이블도 디자인해야 합니다. ID 필드를 선택하고 이를 기본 키(바람직하게는 INT 유형(일부는 UUID 사용))로 만들고 ID 필드를 AUTO_INCREMENT 플래그로 설정합니다.

#🎜🎜#8. 가능한 한 NOT NULL을 사용하세요#🎜🎜##🎜🎜#NULL에는 공간이 필요하지 않다고 생각하지 마세요. 사실 NULL에도 추가 공간이 필요합니다. 주의를 기울이지 않고 발생했습니다. NULL 필드는 쿼리 비교를 수행할 때 더 문제가 됩니다. 물론, 정말로 NULL이 필요하다면 문제 없습니다. 그렇지 않으면 NOT NULL을 사용하는 것이 좋습니다. #🎜🎜##🎜🎜#8. 적절한 스토리지 엔진을 선택하세요 #🎜🎜##🎜🎜# MySQL에는 MyISAM과 InnoDB라는 두 가지 스토리지 엔진이 있으며 둘 다 장단점이 있으므로 이해해야 합니다. 가장 적절한 선택을 하려면, 예를 들어 InnoDB는 트랜잭션을 지원하지만 MyISAM은 지원하지 않습니다. MyISAM 쿼리는 InnoDB보다 빠릅니다. 즉, 무엇을 선택해야 할지 모르겠다면 InnoDB를 사용하십시오. . #🎜🎜##🎜🎜#9. IP 주소를 UNSIGNED INT#🎜🎜##🎜🎜#로 저장하세요. IP 주소를 저장해야 할 때 많은 사람들이 가장 먼저 생각하는 것은 VARCHAR(15) 문자열 유형입니다. INT 정수 유형을 사용하여 저장하는 것을 생각하지 않을 것입니다. 정수 유형을 사용하여 저장하면 4바이트만 필요하고 고정 길이 필드를 가질 수 있으므로 쿼리에 이점이 있습니다. #🎜🎜##🎜🎜#10. WHERE 쿼리 중에 필드의 null 값을 판단하지 마세요. #🎜🎜##🎜🎜# 우리 모두는 필드를 null로 판단하면 속도가 느려진다는 것을 알고 있습니다. 왜냐하면 이 판단으로 인해 엔진은 기존의 모든 인덱스 사용을 포기하고 전체 테이블 스캔 검색을 수행하게 되기 때문입니다. #🎜🎜##🎜🎜#11. % 접두사가 있는 LIKE 퍼지 쿼리를 사용하지 마세요. #🎜🎜##🎜🎜# 퍼지 쿼리, 일상적인 개발에서 자주 접하게 되지만 많은 사람들이 직접적으로 코드>LIKE를 사용합니다. '%key_word%' 또는 LIKE '%key_word'는 이러한 방식으로 검색됩니다. 이 두 가지 검색 방법은 인덱스 실패를 유발하고 전체 테이블 검색을 수행합니다. 위의 퍼지 쿼리를 해결하려면 "전체 텍스트 인덱스 사용"을 사용하는 것이 좋습니다. 구체적인 사용법에 관심이 있는 경우 직접 정보를 확인할 수 있습니다. #🎜🎜##🎜🎜#12. WHERE 쿼리 중에 필드에 표현식 작업을 수행하지 마세요. #🎜🎜##🎜🎜#예: 쿼리 문 SELECT id FROM table WHERE num * 2 = 50; , 이러한 쿼리는 num 필드에 2를 곱하는 산술 연산을 수행하므로 인덱스가 실패하게 됩니다. #🎜🎜#

14. 불필요한 정렬을 줄입니다

정렬 작업은 더 많은 CPU 리소스를 소비하므로 불필요한 정렬을 줄이면 캐시 적중률이 높고 I/O가 충분할 때 SQL의 응답 시간을 줄일 수 있습니다.

14. 하위 쿼리 대신 JOIN을 사용하는 것이 좋습니다

어떤 사람들은 JOIN의 성능이 그다지 좋지 않다고 말하지만 여전히 하위 쿼리에 비해 성능상의 이점이 큽니다. 구체적으로 하위 쿼리의 실행 계획과 관련된 문제에 대해 알아볼 수 있습니다.

15. 암시적 유형 변환 방지

유형 변환은 주로 WHERE 절의 필드 유형이 전달된 매개변수 유형과 일치하지 않을 때 발생하는 유형 변환을 나타냅니다. 필드 유형과 일치하지 않는 경우 MySQL은 우리가 전달한 데이터에 대해 유형 변환 작업을 수행하거나 이를 처리하지 않고 처리를 위해 스토리지 엔진에 직접 넘겨줄 수 있습니다. 이 경우 인덱스가 사용되지 않고 실행이 발생할 수 있습니다. 계획 문제.

16. 다중 테이블 쿼리에서 일관되지 않은 필드 유형을 피하세요

여러 테이블의 공동 쿼리가 필요한 경우 테이블 구조를 설계할 때 테이블과 관련 필드를 테이블과 일관되게 유지하고 인덱스를 설정하십시오. . 동시에 다중 테이블 연결 쿼리를 수행할 때 결과 집합이 작은 테이블을 구동 테이블로 사용해 보세요.

17. 쿼리 캐시를 켜는 것이 좋습니다

대부분의 MySQL 서버에는 쿼리 캐시가 켜져 있을 때 성능을 향상시키는 가장 효과적인 방법 중 하나입니다. 동일한 쿼리가 여러 번 실행되면 이러한 쿼리 결과가 캐시에 저장되므로 이후의 동일한 쿼리는 테이블을 조작할 필요가 없고 캐시된 결과에 직접 액세스할 수 있습니다.