SQL 성능을 분석하는 방법

이 기사에서는 explain을 사용하여 SQL을 분석하는 방법을 소개합니다.

실제로 인터넷에는 explain의 사용법을 자세히 소개하는 글이 많이 있습니다. 이 글은 여러분이 더 잘 이해할 수 있도록 예와 원리를 결합한 것입니다. 수확하다.

explain은 설명으로 번역됩니다. mysql에서는 실행 계획이라고 합니다. 이 명령을 사용하면 mysql이 최적화 프로그램에 의해 분석된 후 SQL을 실행하기로 결정하는 방법을 확인할 수 있습니다.

옵티마이저에 관해 한 가지 더 말하면, MySQL에는 강력한 내장 옵티마이저가 있습니다. 옵티마이저의 주요 작업은 작성한 SQL을 최적화하고 적은 수를 스캔하는 등 최대한 저렴한 비용으로 실행하는 것입니다. 정렬 등을 피하기 위해 행 수 SQL 문을 실행하면서 어떤 경험을 해보셨나요? 이전 기사에서 옵티마이저를 소개했습니다.

주로 explain을 언제 사용하시나요? 대부분의 경우 mysql의 느린 쿼리 로그에서 쿼리 효율성이 상대적으로 느린 일부 SQL을 추출하여 explain 분석을 사용하고, 일부는 mysql을 최적화할 때 사용합니다. 인덱스를 추가하면서 추가된 인덱스가 적중될 수 있는지 분석하기 위해 explain을 사용합니다. 또한 비즈니스 개발 중에 요구 사항이 충족되면 더 효율적인 SQL을 선택하기 위해 explain을 사용해야 할 수도 있습니다.

그렇다면 explain을 사용하는 방법은 매우 간단합니다. 아래와 같이 SQL 앞에 explain을 추가하면 됩니다.

mysql> explain select * from t;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL | 100332 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.04 sec)

explain은 약 10개의 필드를 반환하는 것을 볼 수 있습니다. 버전마다 반환되는 필드가 약간 다릅니다. 이 기사에서는 각 필드를 자세히 소개하지 않을 것입니다. 먼저 몇 가지 중요한 분야를 이해하는 것이 좋습니다.

그 중에서 type, key, row, Extra 필드가 더 중요하다고 생각합니다. 이러한 필드의 의미를 더 잘 이해할 수 있도록 구체적인 예를 사용하겠습니다.

우선, 이들 분야의 문자 그대로의 의미를 간략하게 소개할 필요가 있습니다.

type은 MySQL이 데이터에 액세스하는 방식을 나타냅니다. 일반적인 유형에는 전체 테이블 스캔(all), 인덱스 순회(index), 간격 쿼리(range), 상수 또는 동등 쿼리(ref, eq_ref), 기본 키 동등 쿼리(const)가 포함됩니다. 테이블(시스템)에 레코드가 하나만 있는 경우. 다음은 최고에서 최악까지의 효율성 순위입니다.

system > const > eq_ref > ref > range > index > all

key는 쿼리 프로세스에서 실제로 사용될 인덱스 이름을 나타냅니다.

rows는 쿼리 프로세스 중에 스캔해야 할 행 수를 나타냅니다. 이 데이터는 반드시 정확하지는 않으며 MySQL 샘플링 통계 데이터입니다.

Extra는 일반적으로 인덱스 사용 여부, 정렬이 필요한지 여부, 임시 테이블 사용 여부 등을 보여주는 몇 가지 추가 정보를 나타냅니다.

자, 본격적으로 예시 분석을 시작하겠습니다.

이전 기사에서 생성한 스토리지 엔진을 사용하여 테스트 테이블을 생성해 보겠습니다. 여기에 10개의 테스트 데이터 조각을 삽입합니다. 테이블 구조는 다음과 같습니다.

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

그런 다음 이 테이블을 살펴보세요. 현재 기본 키 인덱스는 하나만 있습니다. 일반 인덱스는 생성되지 않습니다.

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

유형 값은 ALL이며 이는 전체 테이블이 스캔되었음을 의미합니다. 행 필드에는 실제로 총 100,000개의 데이터만 있으므로 이 필드는 단지 mysql의 추정치일 뿐입니다. 정확하지 않을 수도 있습니다. 이 전체 테이블 스캔의 효율성은 매우 낮으므로 최적화가 필요합니다.

다음으로, a와 b 필드에 각각 일반 인덱스를 추가하고, 인덱스를 추가한 후의 여러 SQL 문을 살펴보겠습니다.

mysql> alter table t add index a_index(a);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add index b_index(b);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t     |          0 | PRIMARY  |            1 | id          | A         |      100332 |     NULL | NULL   |      | BTREE      |         |               |
| t     |          1 | a_index  |            1 | a           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
| t     |          1 | b_index  |            1 | b           | A         |      100332 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from t where a > 1000;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | t     | ALL  | a_index       | NULL | NULL    | NULL | 100332 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

위의 SQL이 조금 혼란스러워 보이죠? 유형은 실제로 필드 a에 인덱스가 방금 추가되었음을 보여주고, available_keys도 a_index를 사용할 수 있음을 보여주지만 키에는 null이 표시되어 mysql이 실제로 이를 사용하지 않음을 나타냅니다. .인덱스, 왜 이래?

*를 선택하면 b 필드를 찾기 위해 기본 키 인덱스로 다시 돌아가야 하기 때문입니다. 이 프로세스를 테이블 반환이라고 합니다. 이 문은 조건을 충족하는 90,000개의 데이터를 필터링한다는 의미입니다. 이 90,000개의 데이터는 테이블로 반환되어야 하며, 전체 테이블 스캔에는 100,000개의 데이터만 있으므로 mysql 최적화 프로그램의 관점에서는 직접 전체 테이블 스캔만큼 좋지는 않습니다. 테이블 반환 프로세스.

물론, 테이블 반환 작업이 있는 한 인덱스가 적중되지 않는다는 의미는 아닙니다. 인덱스를 사용할지 여부는 mysql이 어떤 쿼리를 더 저렴하다고 생각하는지에 따라 결정됩니다. 위 SQL의 조건은 다음과 같습니다.

mysql> explain select * from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

이번에는 유형 값이 range이고, 키가 a_index인데, 이는 a 인덱스가 적중되었음을 의미합니다. MySQL에서는 이 SQL 조건을 충족하는 데이터가 1000개만 있다고 생각하기 때문에 좋은 선택입니다. 1000개의 데이터가 테이블로 반환되면 전체 테이블 스캔보다 비용이 저렴하므로 mysql은 실제로 매우 똑똑한 사람입니다.

Extra 필드의 값이 Using index 조건인 것을 확인할 수 있습니다. 이는 인덱스를 사용하지만 테이블을 반환해야 함을 의미합니다. 다음 명령문을 살펴보세요.

mysql> explain select a from t where a > 99000;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

这个 Extra 中的值为 Using where; Using index ，表示查询用到了索引，且要查询的字段在索引中就能拿到，不需要回表，显然这种效率比上面的要高，所以不要轻易写 select * ，只查询业务需要的字段即可，这样可以尽可能避免回表。

再来看一个需要排序的。

mysql> explain select a from t where a > 99000 order by b;
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | t     | range | a_index       | a_index | 5       | NULL |  999 | Using index condition; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

这个 Extra 中返回了一个 Using filesort，意味着需要排序，这种是需要重点优化的的，也就是说查到数据后，还需要 mysql 在内存中对其进行排序，你要知道索引本身就是有序的，所以一般来讲要尽量利用索引的有序性，比如像下面这样写。

mysql> explain select a from t where a > 99990 order by a;
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys    | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | a_index | 5       | NULL |   10 | Using where; Using index |
+----+-------------+-------+-------+------------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

我们再创建一个复合索引看看。

mysql> alter table t add index ab_index(a,b);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t where a > 1000;
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
| id | select_type | table | type  | possible_keys    | key      | key_len | ref  | rows  | Extra                    |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | t     | range | a_index,ab_index | ab_index | 5       | NULL | 50166 | Using where; Using index |
+----+-------------+-------+-------+------------------+----------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

这条 sql 刚刚在上面也有讲到过，在没有创建复合索引的时候，是走的全表扫描，现在其实是利用了覆盖索引，同样是免去了回表过程，即在 （ab_index） 索引上就能找出要查询的字段。

这篇文章通过几个实例介绍了如何使用 explain 分析一条 sql 的执行计划，也提到了一些常见的索引优化，事实上还有更多的可能性，你也可以自己去写一个 sql ，然后使用 explain 分析，看看有哪些是可以被优化的。

这篇文章我断断续续写了有三四天了，本来准备了更多的例子，但每次都是写了一部分，思路也打乱了，好了，有问题欢迎在下面留言交流，文章对你有帮助，点个赞表示鼓励支持。

更多MySQL相关技术文章，请访问MySQL教程栏目进行学习！

위 내용은 SQL 성능을 분석하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!