VPS에서 MySQL 및 MariaDB의 쿼리 및 테이블을 최적화하는 방법

VPS에서 MySQL 및 MariaDB의 쿼리 및 테이블을 최적화하는 방법

2022-06-01 last update

7 minutes reading Server Optimization MySQL MariaDB

소개


MySQL과 MariaDB는 데이터베이스 관리 시스템에서 자주 사용하는 선택입니다.둘 다 SQL 쿼리 언어를 사용하여 데이터를 입력하고 쿼리합니다.
SQL 쿼리는 쉽게 학습할 수 있는 간단한 명령이지만 모든 쿼리와 데이터베이스 함수가 같은 효율로 실행되는 것은 아니다.저장된 정보의 양이 증가하고 데이터베이스가 하나의 사이트를 지원한다면 사이트의 인기가 높아짐에 따라 이 점은 점점 중요해진다.
이 안내서에서 MySQL과 MariaDB 조회 속도를 높일 수 있는 간단한 조치를 논의할 것입니다.운영 체제에 적합한 가이드 중 하나를 사용하여 MySQL 또는 MariaDB를 설치했다고 가정합니다.

테이블 설계 개요


조회 속도를 높이는 가장 기본적인 방법 중 하나는 표 구조 설계 자체부터 시작하는 것이다.이것은 소프트웨어를 사용하기 전에 데이터를 조직하는 가장 좋은 방법을 고려해야 한다는 것을 의미한다.
다음은 자신에게 물어야 할 몇 가지 질문입니다.

당신의 책상은 주로 어떻게 사용합니까?


표의 데이터를 어떻게 사용하는지 예측하는 것은 일반적으로 데이터 구조를 설계하는 가장 좋은 방법을 결정한다.
만약 어떤 데이터를 자주 업데이트하려면 그것들을 자신의 표에 넣는 것이 가장 좋다.만약 이렇게 하지 않는다면, 검색 캐시 (소프트웨어에서 유지되는 내부 캐시) 가 새로운 정보를 식별하기 때문에 반복적으로 저장되고 재구성될 수 있다.만약 단독 테이블에서 이런 상황이 발생한다면 다른 열은 계속 캐시를 사용할 수 있다.
일반적으로 비교적 작은 테이블에서 업데이트 작업의 속도가 더 빠르고 복잡한 데이터에 대한 깊이 있는 분석은 대형 테이블에 귀속되는 가장 좋은 임무이다. 왜냐하면 연결은 대가가 높은 작업일 수 있기 때문이다.

어떤 데이터 형식이 필요합니까?


때때로 데이터 크기를 미리 제한할 수 있다면 장기적으로 보면 많은 시간을 절약할 수 있다.
예를 들어, 문자열 값을 수락하는 특정 필드의 유효한 항목 수가 제한되어 있다면, "varchar"형식이 아니라 "enum"형식을 사용할 수 있습니다.이 데이터 형식은 치밀하기 때문에 빠른 조회가 가능합니다.
예를 들어, 몇 가지 다른 유형의 사용자만 있다면, 가능한 값을 사용하여 이'매거'를 처리하는 열을 만들 수 있습니다:admin,moderator,poweruser,user.

어떤 열을 조회하시겠습니까?


어떤 필드를 반복해서 조회해야 속도를 현저히 높일 수 있는지 미리 알아야 한다.
검색에 사용할 색인 열이 매우 유용합니다.다음 구문을 사용하여 테이블을 만들 때 색인을 추가할 수 있습니다.
<예>
테이블 example\u TABLE 만들기(
id INTEGER NOT NULL AUTO\u 증분,
이름 VARCHAR(50),
주소 VARCHAR(150),
사용자 이름 VARCHAR(16),
키 (id),
색인(사용자 이름)
);

만약 우리가 사용자가 사용자 이름을 통해 정보를 검색할 것을 알고 있다면, 이것은 매우 유용할 것이다.그러면 다음과 같은 속성이 있는 테이블이 생성됩니다.
<예>
example\u 테이블 설명하기;

<예>
±---------±-------------±-----±----±--------±---------------+
| 필드 | Type | Null | Key | Default | Extra|
±---------±-------------±-----±----±--------±---------------+
|id | int(11) | NO |PRI| NULL | 자동 증량 |
| 이름 | varchar(50) | 예 | 비어 있음 |
| 주소 | varchar(150) | 예 | 비어 있음 |
| 사용자 이름 | varchar(16) | 예 | MUL|
±---------±-------------±-----±----±--------±---------------+
4행 설정(0.00초)

보시다시피 저희 시계는 두 개의 색인이 있습니다.첫 번째는 메인 키입니다. 이 예에서는 id 필드입니다.두 번째는 우리가 username 필드에 추가한 인덱스입니다.이것은 이 필드를 이용한 조회를 개선할 것입니다.
개념적인 측면에서 볼 때 창설 과정에서 어떤 필드에 색인을 작성해야 하는지를 고려하는 것이 유용하지만 기존 표에 색인을 추가하는 것도 간단하다.다음과 같이 추가할 수 있습니다.
<예>
테이블 이름에 색인 (열 이름) 만들기;

같은 일을 완성하는 또 다른 방법은 다음과 같다.
<예>
테이블 이름 변경 인덱스 추가( 열 이름);

Explain을 사용하여 질의에서 색인할 점 찾기
만약 프로그램이 매우 예측 가능한 방식으로 조회를 진행한다면, 가능한 한 색인을 사용할 수 있도록 조회를 분석해야 합니다.explain 기능을 사용하면 쉽게 할 수 있습니다.
MySQL 예제 데이터베이스를 가져와서 다음 중 일부는 어떻게 작동하는지 살펴보겠습니다.
wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar xjvf employees_db-full-1.0.6.tar.bz2
cd employees_db
mysql -u root -p -t < employees.sql
이제 MySQL에 다시 로그인하여 질의를 실행할 수 있습니다.
mysql -u root -p
use employees;
우선, MySQL이 이 작업을 완료하는 데 필요한 시간을 정확하게 판단할 수 있도록 캐시를 사용하지 않도록 지정해야 합니다.
SET GLOBAL query_cache_size = 0;
SHOW VARIABLES LIKE "query_cache_size";

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.00 sec)
이제 대규모 데이터 세트에서 간단한 질의를 실행할 수 있습니다.
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.60 sec)
ySQL에서 질의를 수행하는 방법을 보려면 질의하기 전에 키워드explain를 직접 추가할 수 있습니다.
EXPLAIN SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | salaries | ALL  | NULL          | NULL | NULL    | NULL | 2844738 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)
key 필드를 보면 NULL 필드의 값이 표시됩니다.이것은 이 검색에 사용할 색인이 없다는 것을 의미합니다.
다음 중 하나를 추가한 다음 질의를 다시 실행하여 속도가 빨라졌는지 확인합니다.
ALTER TABLE salaries ADD INDEX ( salary );
SELECT COUNT(*) FROM salaries WHERE salary BETWEEN 60000 AND 70000;
+----------+
| count(*) |
+----------+
|   588322 |
+----------+
1 row in set (0.14 sec)
보시다시피 이것은 검색 성능을 현저히 향상시켰다.
색인과 함께 사용되는 또 다른 일반적인 규칙은 주의표 연결입니다.연결표에 사용할 모든 열에 색인을 만들고 같은 데이터 형식을 지정해야 합니다.
예를 들어, "cheeses"라는 테이블과 "Components"라는 테이블이 있다면, 모든 테이블에 INT가 될 수 있는 유사한 component\u id 필드를 추가하기를 원할 수도 있습니다.
그 다음에, 우리는 이 두 필드를 위해 색인을 만들 수 있으며, 우리의 연결은 속도를 높일 것이다.

빠른 검색을 위한 최적화


검색 속도를 높이려고 할 때, 다른 한쪽은 검색 자체를 최적화하는 것이다.어떤 조작은 다른 조작보다 계산이 더 필요하다.일반적으로 여러 가지 방법이 같은 결과를 얻을 수 있는데, 그 중 일부 방법은 비싼 조작을 피할 수 있다.
검색 결과를 사용하는 목적에 따라 제한된 수량의 결과만 필요할 수 있습니다.예를 들어, 40000달러 미만의 수익자가 회사에 있는지 확인하기만 하면 다음을 사용할 수 있습니다.
SELECT * FROM SALARIES WHERE salary < 40000 LIMIT 1;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10022 |  39935 | 2000-09-02 | 2001-09-02 |
+--------+--------+------------+------------+
1 row in set (0.00 sec)
이 조회는 첫 번째 긍정적인 결과가 나올 때 기본적으로 단락되기 때문에 매우 빨리 실행된다.
만약 조회가'또는'비교를 사용하고 이 두 구성 요소 부분에서 다른 필드를 테스트하고 있다면 조회가 너무 길어질 수 있습니다.
예를 들어, 이름이나 성이 "Bre"로 시작하는 직원을 검색하려면 두 개의 별도의 열을 검색해야 합니다.
SELECT * FROM employees WHERE last_name like 'Bre%' OR first_name like 'Bre%';
만약 우리가 한 검색에서 이름을 검색하고, 다른 검색에서 일치하는 성씨를 검색한 다음, 출력을 합치면 이 작업은 더욱 빠를 수 있습니다.유니온 연산자를 사용하여 이 작업을 수행할 수 있습니다.
SELECT * FROM employees WHERE last_name like 'Bre%' UNION SELECT * FROM employees WHERE first_name like 'Bre%';
경우에 따라 MySQL은 유니온 작업을 자동으로 사용합니다.위의 예는 실제로 MySQL이 자동으로 이 작업을 수행하는 경우입니다.재사용explain을 통해 진행 중인 정렬 유형을 검사하면 이러한 상황이 있는지 확인할 수 있습니다.

결론


용례에 따라 MySQL과 MariaDB 테이블과 데이터베이스를 미세하게 조정할 수 있는 방법이 많습니다.본고는 당신의 입문에 유용할 수 있는 몇 가지 힌트만 포함합니다.
이런 데이터베이스 관리 시스템에는 어떻게 서로 다른 장면을 최적화하고 미세하게 조정하는지에 관한 문서가 대량으로 있다.구체적인 세부 사항은 어느 정도 당신이 어떤 기능을 최적화하기를 원하느냐에 달려 있다. 그렇지 않으면 그것들은 완전히 최적화될 것이다.일단 당신이 수요를 확정하고 반복적으로 실행될 조작을 파악하면 이 조회의 설정을 조정하는 것을 배울 수 있습니다.
저스틴 엘린우드