mysql 힌트가 뭐야?

mysql에서 힌트는 "쿼리 최적화 힌트"를 의미하며, 이는 최적화 프로그램이 특정 방식으로 최적화를 위한 실행 계획을 생성하도록 유도하여 사용자의 SQL 문을 보다 유연하게 만듭니다. 힌트는 연결 순서 및 방법을 기반으로 할 수 있습니다. 테이블, 액세스 경로, 병렬 처리 및 기타 규칙은 DML(Data Manipulation Language) 문에 영향을 미칩니다.

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

다른 사람들이 코드를 빨리 이해할 수 있도록 테이블, 필드 또는 인덱스를 조작할 때 주석을 추가할 수 있습니다. 힌트라는 팁이 있습니다. 데이터베이스에 대한 프롬프트.

힌트란 무엇입니까

힌트는 "쿼리 최적화 힌트"를 의미합니다. 이는 최적화 프로그램이 특정 방식으로 최적화하도록 유도하여 SQL 문을 보다 유연하게 만들어 쿼리를 더 빠르게 만듭니다. 물론 그럴 수도 있습니다. 속도가 느려지는 것은 모두 최적화 프로그램에 대한 이해와 시나리오에 대한 이해에 달려 있습니다.

우리는 SQL 문을 실행할 때 MySQL이 실행 계획을 생성하고 힌트는 쿼리 최적화 프로그램에 우리가 지시한 방식으로 실행 계획을 생성하도록 지시한다는 것을 알고 있습니다.

힌트는 테이블의 연결 순서, 방법, 액세스 경로, 병렬성 및 기타 규칙을 기반으로 DML(Data Manipulation Language) 문에 대해 작동할 수 있습니다. 범위는 다음과 같습니다.

使用的优化器类型；
基于代价的优化器的优化目标，是all_rows还是first_rows；
表的访问路径，是全表扫描，还是索引扫描，还是直接用rowid；
表之间的连接类型；
表之间的连接顺序；
语句的并行程度；

일반적으로 사용되는 힌트

• 포스 인덱스 FORCE INDEX
  SELECT * FROM tbl FORCE INDEX(FIELD1) …SELECT * FROM tbl FORCE INDEX (FIELD1) …

• 忽略索引 IGNORE INDEX
  SELECT * FROM tbl IGNORE INDEX (FIELD1, FIELD2) …

• 关闭查询缓冲 SQL_NO_CACHE
  SELECT SQL_NO_CACHE field1, field2 FROM tbl;
  需要查询实时数据且频率不高时，可以考虑把缓冲关闭，即不论此SQL是否曾被执行，MySQL都不会在缓冲区中查找。

• 强制查询缓冲 SQL_CACHE
  SELECT SQL_CACHE * FROM tbl;
  功能同上一条相反，但仅在my.ini中的query_cache_type设为2时起作用。

• 优先操作 HIGH_PRIORITY
  HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。
  SELECT HIGH_PRIORITY * FROM tbl;

• 滞后操作 LOW_PRIORITY
  LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。
  update LOW_PRIORITY tbl set field1= where field1= …

• 延时插入 INSERT DELAYED
  INSERT DELAYED INTO tbl set field1= …
  指客户端提交插入数据申请，MySQL返回OK状态却并未实际执行，而是存储在内存中排队，当mysql有空余时再插入。
  一个重要的好处是，来自多个客户端的插入请求被集中在一起，编写入一个块，比独立执行许多插入要快很多。
  坏处是，不能返回自增ID，以及系统崩溃时，MySQL还未来得及被插入的数据将会丢失。

• 强制连接顺序 STRAIGHT_JOIN
  SELECT tbl.FIELD1, tbl2.FIELD2 FROM tbl STRAIGHT_JOIN tbl2 WHERE …
  由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按tbl、tbl2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

不常用

• 强制使用临时表 SQL_BUFFER_RESULT
  SELECT SQL_BUFFER_RESULT * FROM tbl WHERE …
  当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。

• 分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT
  SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM tbl GROUP BY FIELD1;


인덱스 무시 IGNORE INDEX🎜SELECT * FROM tbl IGNORE INDEX(FIELD1, FIELD2) … 🎜🎜🎜쿼리 버퍼 닫기 SQL_NO_CACHE🎜SELECT SQL_NO_CACHE field1, field2 FROM tbl;🎜실시간 데이터를 쿼리해야 하는데 빈도가 높지 않은 경우 버퍼를 끄는 것을 고려할 수 있습니다. 즉, 이 SQL이 실행되었는지 여부에 관계없이 MySQL은 버퍼를 찾지 않습니다. 🎜🎜🎜쿼리 버퍼링 강제 SQL_CACHE🎜SELECT SQL_CACHE * FROM tbl;🎜함수는 이전 함수와 반대이지만 my.ini의 query_cache_type이 2로 설정된 경우에만 작동합니다. . 🎜🎜🎜우선순위 작업 HIGH_PRIORITY🎜HIGH_PRIORITY는 선택 및 삽입 작업에 사용되어 MYSQL에 이 작업이 우선순위가 있음을 알릴 수 있습니다. 🎜SELECT HIGH_PRIORITY * FROM tbl;🎜🎜🎜지연 작업 LOW_PRIORITY🎜LOW_PRIORITY는 삽입 및 업데이트 작업에 사용되어 mysql에 이 작업이 지연되고 있음을 알릴 수 있습니다. 🎜update LOW_PRIORITY tbl set field1= where field1= …🎜🎜🎜지연 삽입 INSERT DELAYED🎜INSERT DELAYED INTO tbl set field1= …🎜클라이언트를 참조합니다. 데이터 삽입을 위한 신청서를 제출하면 MySQL은 OK 상태를 반환하지만 실제로 실행되지는 않고 대신 메모리에 저장되어 있다가 mysql이 비어 있을 때 삽입된다. 🎜중요한 이점은 여러 클라이언트의 삽입 요청이 함께 그룹화되어 블록에 기록된다는 것입니다. 이는 여러 삽입을 독립적으로 수행하는 것보다 훨씬 빠릅니다. 🎜단점은 자동 증가된 ID를 반환할 수 없으며 시스템이 충돌하면 MySQL에 아직 삽입할 시간이 없었던 데이터가 손실된다는 것입니다. 🎜🎜🎜연결 시퀀스 STRAIGHT_JOIN을 강제 실행합니다🎜SELECT tbl.FIELD1, tbl2.FIELD2 FROM tbl STRAIGHT_JOIN tbl2 WHERE…🎜위의 SQL 문에서 STRAIGHT_JOIN을 사용하여 강제로 연결하는 것을 볼 수 있습니다. MySQL은 tbl, tbl2를 눌러 테이블을 순차적으로 조인합니다. MySQL에서 권장하는 순서보다 자신만의 순서로 연결하는 것이 더 효율적이라고 생각되면 STRAIGHT_JOIN을 사용하여 연결 순서를 결정할 수 있습니다. 🎜

일반적으로 사용되지 않음

🎜🎜🎜임시 테이블 SQL_BUFFER_RESULT🎜SELECT SQL_BUFFER_RESULT * FROM tbl WHERE…🎜데이터를 쿼리할 때 많은 경우에 SQL_BUFFER_RESULT 옵션을 통해 결과 세트를 임시 테이블에 강제로 적용할 수 있으므로 MySQL 테이블 잠금이 신속하게 해제될 수 있습니다(다른 SQL 문이 이러한 레코드를 쿼리할 수 있도록). 클라이언트에게 대규모 레코드 세트를 제공합니다. 🎜🎜🎜임시 테이블 SQL_BIG_RESULT 및 SQL_SMALL_RESULT를 사용하여 그룹화🎜SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM tbl GROUP BY FIELD1;🎜SELECT 문에 효과적이며 MySQL에 GROUP BY 및 방법을 최적화하도록 지시합니다. DISTINCT 쿼리에 임시 테이블 정렬을 사용하려면 SQL_SMALL_RESULT는 결과 집합이 작고 메모리의 임시 테이블에서 직접 정렬할 수 있음을 나타냅니다. 그렇지 않은 경우에는 디스크 임시 테이블 정렬이 필요합니다. 🎜

SQL_CALC_FOUND_ROWS
실제로는 최적화 프로그램 프롬프트도 아니고 최적화 프로그램의 실행 계획에도 영향을 주지 않지만, mysql이 반환한 결과 집합에 이 작업의 영향을 받은 총 행 수가 포함되도록 해야 합니다. FOUND_ROWS() 결합하여 사용합니다. FOUND_ROWS() 联用。
SQL_CALC_FOUND_ROWS 通知MySQL将本次处理的行数记录下来； FOUND_ROWS() 用于取出被记录的行数，可以应用到分页场景。
一般的分页写法为：先查总数，计算页数，再查询某一页的详情。
SELECT COUNT(*) from tbl WHERE …
SELECT * FROM tbl WHERE … limit m,n
但借助SQL_CALC_FOUND_ROWS，可以简化成如下写法：
SELECT SQL_CALC_FOUND_ROWS * FROM tbl WHERE … limit m,n;
SELECT FOUND_ROWS();
第二条SELECT将返回第一条SELECT不带limit时的总行数，如此只需执行一次较耗时的复杂查询就可同时得到总行数。

LOCK IN SHARE MODE、 FOR UPDATE
同样的，这俩也不是优化提示，是控制SELECT语句的锁机制，只对行级锁有效，即InnoDB支持。

扩展知识：

概念和区别

SELECT ... LOCK IN SHARE MODE添加的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，其他session可读取记录，亦可继续添加IS锁，但无法修改，直到这个加锁的session done(否则直接锁等待超时)。

SELECT ... FOR UPDATE 添加的是IX锁(意向排它锁)，即符合条件的rows上都加了排它，其他session无法给这些记录添加任何S锁或X锁。如果不存在一致性非锁定读的话，则其他session是无法读取和修改这些记录的，但innodb有非锁定读(快照读不需要加锁)。
因此，for update的加锁方式只是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读。

应用场景

LOCK IN SHARE MODE的适用于两张存在关系的表的写场景，以mysql官方例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，从业务角度讲，此时直接insert一条child_id=100记录到child表是存在风险的，因为insert的同时可能存在parent表执行了删除c_child_id=100的记录，业务数据有不一致的风险。正确方法是先执行select * from parent where c_child_id=100 lock in share mode，锁定parent表的这条记录，然后执行insert into child(child_id) values (100)SQL_CALC_FOUND_ROWS는 이번에 처리된 행 수를 기록하도록 MySQL에 알립니다. FOUND_ROWS()는 기록된 행 수를 검색하는 데 사용되며 페이징 시나리오에 적용될 수 있습니다.

페이징을 작성하는 일반적인 방법은 먼저 총 개수를 확인하고 페이지 수를 계산한 다음 특정 페이지의 세부 정보를 확인하는 것입니다.

SELECT COUNT(*) from tbl WHERE …SELECT * FROM tbl WHERE … m,n 제한그러나 SQL_CALC_FOUND_ROWS의 도움으로, 다음과 같이 단순화할 수 있습니다.

SELECT SQL_CALC_FOUND_ROWS * FROM tbl WHERE …limit m,n;🎜SELECT FOUND_ROWS();🎜두 번째 SELECT는 첫 번째 SELECT는 제한 없는 총 행 수를 반환합니다. 이러한 방식으로 총 행 수를 얻으려면 복잡하고 시간이 많이 걸리는 쿼리만 실행하면 됩니다. 동시. 🎜🎜🎜🎜업데이트를 위한 공유 모드 잠금🎜마찬가지로 이 두 가지는 SELECT 문을 제어하는 ​​잠금 메커니즘이며 InnoDB에서 지원하는 행 수준 잠금에만 효과적입니다. 🎜🎜🎜지식 확장:🎜🎜개념 및 차이점🎜🎜SELECT ... LOCK IN SHARE MODE는 IS 잠금(의도 공유 잠금)을 추가합니다. 즉, 공유 잠금이 정규화된 행에 추가됩니다. 다른 세션은 레코드를 읽고 계속해서 IS 잠금을 추가할 수 있지만 수정할 수는 없습니다. 잠긴 세션이 완료될 때까지(그렇지 않으면 직접 잠금 대기 시간이 초과됨) 🎜🎜SELECT ... FOR UPDATE 추가되는 것은 IX 잠금(의도 배타적 잠금), 즉 조건을 만족하는 행에 배타적이 추가되는 것입니다. 다른 세션에서는 S 잠금이나 S를 추가할 수 없습니다. 이 레코드를 잠급니다. X 잠금. 일관된 비잠금 읽기가 없으면 다른 세션은 이러한 레코드를 읽고 수정할 수 없지만 innodb에는 비잠금 읽기가 있습니다(스냅샷 읽기에는 잠금이 필요하지 않음). 🎜따라서 업데이트용 잠금 방법은 공유 모드 잠금 쿼리 모드보다 선택...공유 모드 잠금만 차단합니다. 스냅샷 읽기를 차단하지 않습니다. 🎜🎜애플리케이션 시나리오🎜🎜LOCK IN SHARE MODE는 두 테이블이 관계를 갖는 시나리오를 작성하는 데 적합합니다. MySQL의 공식 예를 들면, 하나의 테이블이 하위 테이블입니다. 하나는 부모 테이블입니다. 자식 테이블의 특정 열 child_id가 부모 테이블의 c_child_id 열에 매핑되어 있다고 가정합니다. 이때 자식 테이블에 child_id=100 레코드를 직접 삽입하는 것은 위험합니다. , 삽입과 동시에 상위 테이블이 존재할 수 있기 때문에 c_child_id=100인 레코드를 삭제한 후 비즈니스 데이터에 불일치가 발생할 위험이 있습니다. 올바른 방법은 먼저 select * from parent where c_child_id=100 lock in share mode를 실행하고 상위 테이블에서 이 레코드를 잠근 다음 insert into child(child_id) 값을 실행하는 것입니다. (100) 코드>. 🎜🎜【관련 추천: 🎜mysql 비디오 튜토리얼🎜】🎜

위 내용은 mysql 힌트가 뭐야?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!