MYSQL 쿼리를 최적화하는 방법은 무엇입니까? MySQL 쿼리 최적화 방법 소개

이 기사의 내용은 Python에서 프로세스 풀의 간단한 구현 코드에 대한 것입니다. 필요한 친구가 참고할 수 있기를 바랍니다.

1. where, order by 및 group by에 사용되는 모든 열에 인덱스를 추가합니다. where,order by和group by的列上添加索引

索引除了能够确保唯一的标记一条记录，还能是MySQL服务器更快的从数据库中获取结果。索引在排序中的作用也非常大。

Mysql的索引可能会占据额外的空间，并且会一定程度上降低插入，删除和更新的性能。但是，如果你的表格有超过10行数据，那么索引就能极大的降低查找的执行时间。

强烈建议使用“最坏情况的数据样本”来测试MySql查询，从而更清晰的了解查询在生产中的行为方式。

假设你正在一个超过500行的数据库表中执行如下的查询语句：

mysql>select customer_id, customer_name from customers where customer_id='345546'

上述查询会迫使Mysql服务器执行一个全表扫描来获得所查找的数据。

型号，Mysql提供了一个特别的Explain语句，用来分析你的查询语句的性能。当你将查询语句添加到该关键词后面时，MySql会显示优化器对该语句的所有信息。

如果我们用explain语句分析一下上面的查询，会得到如下的分析结果：

mysql> explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

可以看到，优化器展示出了非常重要的信息，这些信息可以帮助我们微调数据库表。首先，MySql会执行一个全表扫描，因为key列为Null。其次，MySql服务器已经明确表示它将要扫描500行的数据来完成这次查询。

为了优化上述查询，我们只需要在customer_id这一列上添加一个索引m即可：

mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果我们再次执行explain语句，会得到如下结果：

mysql> Explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

从上述的输出结果，显然MySQL服务器会使用索引customer_id来查询表格。可以看需要扫描的行数为1。虽然我只是在一个行数为500的表格中执行这条查询语句，索引在检索一个更大的数据集的时候优化程度更加明显。

2. 用Union优化Like语句

有时候，你可能需要在查询中使用or操作符进行比较。当or关键字在where子句中使用频率过高的时候，它可能会使MySQL优化器错误的选择全表扫描来检索记录。union子句可以是查询执行的更快，尤其是当其中一个查询有一个优化索引，而另一个查询也有一个优化索引的时候。

比如，在first_name和last_name上分别存在索引的情况下，执行如下查询语句：

mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%'

上述查询和下面使用union合并两条充分利用查询语句的查询相比，速度慢了许多。

mysql> select * from students where first_name like 'Ade%' union all select * from students where last_name like 'Ade%'

3. 避免使用带有前导通配符的表达式

当查询中存在前导通配符时，Mysql无法使用索引。以上面的student表为例，如下的查询会导致MySQL执行全表扫描，及时first_name字段上加了索引。

mysql> select * from students where first_name like '%Ade'

使用explain分析得到如下结果：

mysql> explain select * from students where first_name like  '%Ade'  ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

如上所示，Mysql将扫描全部500行数据，这将使得查询极其缓慢。

4. 充分利用MySQL的全文检索

如果你正面临着使用通配符查询数据，但是并不想降低数据库的性能，你应当考虑使用MySQL的全文检索（FTS），因为它比通配符查询快得多。除此以外，FTS还能够返回质量更好的相关结果。

添加一个全文检索索引到student样表上的语句如下：

mysql> alter table students add fulltext(first_name, last_name)';
mysql> select * from students where match(first_name, last_name) against ('Ade');

在上面的例子中，我们针对搜索关键字Ade

인덱스는 레코드 외에 고유한 태그도 보장할 수 있습니다. 또한 MySQL 서버를 활성화하여 데이터베이스에서 더 빠르게 결과를 얻을 수도 있습니다. 인덱스는 정렬에서도 매우 중요한 역할을 합니다.

Mysql의 인덱스는 추가 공간을 차지하여 삽입, 삭제, 업데이트 성능이 어느 정도 저하될 수 있습니다. 그러나 테이블에 10개 이상의 데이터 행이 있는 경우 인덱싱을 사용하면 검색 실행 시간을 크게 줄일 수 있습니다.

프로덕션에서 쿼리가 어떻게 작동하는지 더 명확하게 이해하려면 "최악의 경우 데이터 샘플"을 사용하여 MySql 쿼리를 테스트하는 것이 좋습니다.

행이 500개가 넘는 데이터베이스 테이블에서 다음 쿼리 문을 실행한다고 가정해 보겠습니다.

mysql> explain Select * from students where match(first_name, last_name) AGAINST ('Ade');
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table    | partitions | type     | possible_keys | key        | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | students | NULL       | fulltext | first_name    | first_name | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+

위 쿼리는 Mysql 서버가 찾고 있는 데이터를 얻기 위해 전체 테이블 스캔을 수행하도록 합니다. Model, Mysql은 쿼리 문의 성능을 분석하기 위해 특별한 Explain 문을 제공합니다. 키워드 뒤에 쿼리 문을 추가하면 MySql은 최적화 프로그램이 해당 문에 대해 가지고 있는 모든 정보를 표시합니다.

explain 문을 사용하여 위 쿼리를 분석하면 다음과 같은 분석 결과를 얻을 수 있습니다.

rrreee

보시다시피 옵티마이저는 데이터베이스 테이블을 미세 조정하는 데 도움이 되는 매우 중요한 정보를 표시합니다. 먼저 MySql은 키 열이 Null이므로 전체 테이블 스캔을 수행합니다. 둘째, MySql 서버는 이 쿼리를 완료하기 위해 500행의 데이터를 스캔할 것임을 분명히 했습니다.

위 쿼리를 최적화하려면 customer_id 열에 인덱스 m만 추가하면 됩니다.

rrreee

explain 문을 다시 실행하면 다음과 같은 결과를 얻게 됩니다.

rrreee 🎜위의 결과 출력에서 ​​MySQL 서버는 테이블을 쿼리하기 위해 customer_id 인덱스를 사용할 것이 분명합니다. 스캔할 행 수가 1개임을 알 수 있습니다. 비록 500개의 행이 있는 테이블에서만 이 쿼리를 실행하고 있지만, 더 큰 데이터 세트를 검색할 때 인덱스가 훨씬 더 최적화됩니다. 🎜🎜2. Union을 사용하여 Like 문을 최적화하세요🎜🎜때때로 비교를 위해 쿼리에 or 연산자를 사용해야 할 수도 있습니다. where 절에서 or 키워드가 너무 자주 사용되면 MySQL 최적화 프로그램이 실수로 레코드를 검색하기 위해 전체 테이블 스캔을 선택하게 될 수 있습니다. Union 절을 사용하면 특히 쿼리 중 하나에 최적화된 인덱스가 있고 다른 쿼리에도 최적화된 인덱스가 있는 경우 쿼리 실행 속도가 빨라집니다. 🎜🎜예를 들어 first_name과 last_name에 대한 인덱스가 있는 경우 다음 쿼리 문을 실행합니다. 🎜rrreee🎜위 쿼리와 다음 쿼리를 Union을 사용하여 병합하여 만듭니다. 두 쿼리를 모두 사용하면 명령문 쿼리에 비해 속도가 훨씬 느려집니다. 🎜rrreee🎜3. 앞에 와일드카드가 있는 표현식을 사용하지 마세요.🎜🎜Mysql은 쿼리에 앞에 와일드카드가 있으면 인덱스를 사용할 수 없습니다. 위의 학생 테이블을 예로 들면, 다음 쿼리는 MySQL이 전체 테이블 스캔을 수행하고 적시에 first_name 필드에 인덱스를 추가하도록 합니다. 🎜rrreee🎜설명 분석을 사용하여 다음과 같은 결과를 얻었습니다. 🎜rrreee🎜위에 표시된 것처럼 Mysql은 500행의 데이터를 모두 스캔하므로 쿼리 속도가 매우 느려집니다. 🎜🎜4. MySQL의 전체 텍스트 검색을 최대한 활용하세요🎜🎜데이터 쿼리에 와일드카드 문자를 사용해야 하지만 데이터베이스 성능을 저하시키고 싶지 않다면 MySQL의 전체 텍스트 검색(FTS)을 사용하는 것이 좋습니다. ), 와일드카드 쿼리보다 훨씬 빠르기 때문입니다. 이 외에도 FTS는 더 나은 품질의 관련 결과를 반환할 수 있습니다. 🎜🎜학생 샘플 테이블에 전체 텍스트 검색 인덱스를 추가하는 명령문은 다음과 같습니다. 🎜rrreee🎜위의 예에서는 검색 키워드 Ade와 일치시키려는 열(first_name, last_name)을 지정했습니다. . 쿼리 최적화 프로그램이 위 명령문을 실행하면 다음과 같은 결과를 얻게 됩니다. 🎜rrreee🎜5. 데이터베이스 아키텍처 최적화 🎜🎜Normalization🎜🎜 먼저, 약간의 손실이 있더라도 모든 데이터베이스 테이블을 정규화합니다. 예를 들어, 고객 및 주문 데이터를 기록하기 위해 두 개의 테이블을 생성해야 하는 경우 주문 테이블에서 고객 ID로 고객을 참조해야 하며 그 반대로는 안됩니다. 아래 다이어그램은 데이터 중복 없이 설계된 데이터베이스 아키텍처를 보여줍니다. 🎜🎜🎜🎜🎜🎜🎜또한 유사한 값을 저장하려면 동일한 데이터 유형 클래스를 사용하세요. 🎜🎜최고의 데이터 유형을 사용하세요🎜🎜MySQL은 정수, 부동 소수점, 실수, 날짜, 날짜/시간, varchar, 텍스트 등 다양한 데이터 유형을 지원합니다. 데이터베이스 테이블을 디자인할 때에는 특성을 만족할 수 있는 가장 짧은 데이터 타입을 사용하도록 노력해야 합니다. 🎜🎜예를 들어 시스템 사용자 테이블을 설계하고 사용자 수가 100명을 초과하지 않는 경우 user_ud에 'TINYINT' 유형을 사용해야 합니다. 이 유형의 값 범위는 -128~128입니다. 필드에 날짜 유형 값을 저장해야 하는 경우 쿼리할 때 복잡한 유형 변환을 수행할 필요가 없으므로 datetime 유형을 사용하는 것이 좋습니다. 🎜

값이 모두 숫자형인 경우 Integer를 사용하세요. 계산을 수행할 때 Integer 유형의 값은 Text 유형의 값보다 빠릅니다.

Avoid NULL

NULL은 열에 값이 없음을 의미합니다. 이러한 유형의 값은 데이터베이스 결과에 해를 끼칠 수 있으므로 가능하면 피해야 합니다. 예를 들어, 데이터베이스에 있는 모든 주문 금액의 합계를 구해야 하는데 주문 레코드의 금액이 null인 경우 null 포인터에 주의를 기울이지 않으면 계산에서 예외가 발생할 수 있습니다. 결과. 경우에 따라 열의 기본값을 정의해야 할 수도 있습니다.

위 내용은 MYSQL 쿼리를 최적화하는 방법은 무엇입니까? MySQL 쿼리 최적화 방법 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!