MySQL에서 빅데이터 쿼리를 최적화할 때 주의해야 할 사항은 무엇입니까?

mysql의 빅 데이터 쿼리 최적화에 대한 참고 사항: 1. 쿼리를 최적화할 때 전체 테이블 스캔을 최대한 피해야 합니다. 2. where 절의 필드에 대한 Null 값 판단을 피해야 합니다. 주의해서 사용하십시오. 4. where 절을 사용하여 연결하지 마십시오. 5. 커서를 사용하지 마십시오.

mysql에서 빅 데이터 쿼리를 최적화하는 방법:

1 쿼리를 최적화하려면 전체 테이블 스캔을 피하고 먼저 where 및 order by와 관련된 열에 인덱스를 생성하는 것을 고려하십시오.

2. where 절의 필드에 대해 null 값 판단을 피하십시오. 그렇지 않으면 엔진이 인덱스 사용을 포기하고 다음과 같이 전체 테이블 스캔을 수행합니다. 여기서 num이 null인 경우 id를 선택하세요. num 의 기본값은 0입니다. 테이블의 num 열에 null 값이 없는지 확인한 후 다음과 같이 쿼리하세요.

select id from t where num=0

3 where 절에 != 또는 a8093152e673feb7aba1828c43532094 연산자를 사용하지 마세요. 엔진은 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다.

4 조건을 연결하기 위해 where 절에서 또는를 사용하지 마십시오. 그렇지 않으면 엔진이 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다. 예: num=10 또는 num=20이 될 수 있는 t에서 id를 선택합니다. 다음과 같이 쿼리됩니다:

select id from t where num=10 union all select id from t where num=20

5.in 및 not in도 주의해서 사용해야 합니다. 그렇지 않으면 다음과 같이 전체 테이블 스캔으로 이어집니다. select id from t where num in(1,2,3) 연속 값의 경우 , between을 사용할 수 있는 경우 다음 쿼리에서는 사용하지 마십시오:

select id from t where num between 1 and 3

6. 다음 쿼리도 전체 테이블 스캔이 발생합니다. select id from t where name like '%이자%' 효율성을 높이려면 전체 테이블 검색을 고려할 수 있습니다. 텍스트 검색.

7. where 절에 매개변수를 사용하면 전체 테이블 스캔이 발생합니다. SQL은 런타임 시에만 지역 변수를 분석하므로 옵티마이저는 런타임까지 액세스 계획 선택을 연기할 수 없습니다. 컴파일 시 선택해야 합니다. 그러나 컴파일 타임에 액세스 플랜이 생성되면 변수 값을 아직 알 수 없으므로 인덱스 선택을 위한 입력으로 사용할 수 없습니다. 예를 들어, 다음 명령문은 전체 테이블 스캔을 수행합니다. select id from t where num=@num는 쿼리가 인덱스를 사용하도록 강제로 변경할 수 있습니다. select id from t where num=@num可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为:

select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： 

create table #t(…)

13.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换： 

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15. 索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

16. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar rrreee

8 표현을 피하십시오. where 절의 필드 이로 인해 엔진은 인덱스 사용을 포기하고 전체 테이블 스캔을 수행하게 됩니다. 예를 들어, select id from t where num/2=100은 다음과 같이 변경되어야 합니다: select id from t where num=100*2🎜🎜9. 자막 문장의 필드에 함수 작업을 수행하면 엔진이 인덱스 사용을 포기하고 전체 테이블 스캔을 수행하게 됩니다. 예: substring(name,1,3)='abc'인 t에서 id를 선택하면 이름이 abc로 시작하는 id는 🎜rrreee🎜10으로 변경되어야 합니다. "의 왼쪽에서는 함수나 산술을 수행하지 마세요. ="를 where 절 연산이나 기타 표현식 연산에 사용하지 않으면 시스템이 인덱스를 올바르게 사용하지 못할 수 있습니다. 🎜🎜11. 인덱스 필드를 조건으로 사용할 때 인덱스가 복합 인덱스인 경우 시스템이 인덱스를 사용하는지 확인하기 위해 인덱스의 첫 번째 필드를 조건으로 사용해야 합니다. 그렇지 않으면 인덱스가 사용되지 않습니다. 그리고 필드 순서는 인덱스 순서와 최대한 일치해야 합니다. 🎜🎜12. 의미 없는 쿼리를 작성하지 마세요. 예를 들어 빈 테이블 구조를 생성해야 하는 경우: 🎜rrreee🎜이 유형의 코드는 결과 집합을 반환하지 않지만 시스템 리소스를 소비하게 됩니다. 이: 🎜🎜 create table #t(…)🎜🎜13 in 대신 기존을 사용하는 것이 좋은 선택인 경우가 많습니다. 🎜rrreee🎜다음 명령문으로 바꾸세요: 🎜rrreee🎜14. 모든 인덱스가 쿼리에 유효한 것은 아니지만 SQL은 테이블의 데이터를 기반으로 쿼리 최적화를 수행합니다. 인덱스 열에 중복된 데이터가 많은 경우 SQL 쿼리는 인덱스를 사용하지 않을 수 있습니다. 필드 섹스가 거의 절반은 남성이고 절반은 여성인 테이블에 필드가 섹스를 기반으로 구축된 경우에도 인덱스 손실은 쿼리 효율성에 영향을 미치지 않습니다. 🎜🎜15. 인덱스는 많을수록 좋습니다. 하지만 인덱스는 해당 선택의 효율성을 향상시킬 수 있지만 삽입이나 업데이트 중에 인덱스가 다시 작성될 수 있으므로 주의가 필요합니다. 인덱스를 구축할 때 사례별로 고려됩니다. 하나의 테이블에 6개 이상의 인덱스를 두지 않는 것이 가장 좋으며, 인덱스가 너무 많으면 일반적으로 사용되지 않는 일부 컬럼에 인덱스를 구축할 필요가 있는지 고려해야 합니다. 🎜🎜16. 클러스터형 인덱스 데이터 열의 순서는 테이블 레코드의 물리적 저장 순서이므로 가능한 한 업데이트하지 않는 것이 좋습니다. 전체 테이블 레코드에는 많은 시간이 소요됩니다. 응용 프로그램 시스템이 클러스터형 인덱스 데이터 열을 자주 업데이트해야 하는 경우 인덱스를 클러스터형 인덱스로 구축해야 하는지 여부를 고려해야 합니다. 🎜🎜17. 숫자 필드를 사용해 보십시오. 필드에 숫자 정보만 포함되어 있으면 쿼리 및 연결 성능이 저하되고 저장 오버헤드가 증가합니다. 엔진은 쿼리 및 연결 처리 시 문자열의 각 문자를 하나씩 비교하는데, 숫자 유형의 경우 한 번의 비교만으로 충분하기 때문입니다. 🎜🎜18. char/nchar 대신 varchar/nvarchar를 최대한 많이 사용하세요. 첫째, 가변 길이 필드는 저장 공간이 작아서 저장 공간을 절약할 수 있기 때문입니다. 쿼리의 경우 비교적 작은 필드 내에서 검색하는 것이 확실히 더 효율적입니다. 🎜

19. 어디에서나 select * from t 를 사용하지 말고, "*"를 특정 필드 목록으로 바꾸고, 사용하지 않는 필드를 반환하지 마세요. select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27. 与临时表一样，游标并不是不可使 用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC

20. 임시 테이블 대신 테이블 변수를 사용해 보세요. 테이블 변수에 많은 양의 데이터가 포함되어 있는 경우 인덱스가 매우 제한적이라는 점에 유의하세요(기본 키 인덱스만 해당).

21. 시스템 테이블 리소스 소비를 줄이려면 임시 테이블을 자주 생성하고 삭제하지 마세요.

22. 임시 테이블은 사용할 수 없으며 적절하게 사용하면 특정 루틴을 더 효율적으로 만들 수 있습니다. 예를 들어 큰 테이블이나 일반적으로 사용되는 테이블에서 특정 데이터 세트를 반복적으로 참조해야 하는 경우입니다. 그러나 일회성 이벤트의 경우 내보내기 테이블을 사용하는 것이 좋습니다.

23. 임시 테이블을 생성할 때 한 번에 많은 양의 데이터가 삽입되는 경우 create table 대신 select into를 사용하면 큰 문제가 발생하는 것을 방지할 수 있습니다. ; 데이터 양이 많지 않은 경우에는 시스템 테이블의 리소스를 줄이기 위해 먼저 테이블을 생성한 후 삽입해야 합니다. 24. 임시 테이블을 사용하는 경우 저장 프로시저 끝에서 모든 임시 테이블을 명시적으로 삭제해야 합니다. 먼저 테이블을 자르고 테이블을 삭제하세요. 시스템 테이블의 장기간 잠금을 피하십시오. 25. 커서는 효율성이 떨어지므로 사용을 피하세요. 커서로 작동하는 데이터가 10,000행을 초과하면 다시 작성하는 것이 좋습니다. 26. 커서 기반 방법이나 임시 테이블 방법을 사용하기 전에 먼저 문제를 해결하기 위한 집합 기반 솔루션을 찾아야 합니다. 일반적으로 집합 기반 방법이 더 효과적입니다. 27. 임시 테이블과 마찬가지로 커서를 사용할 수 없습니다. 작은 데이터 세트에 FAST_FORWARD 커서를 사용하는 것이 다른 행별 처리 방법보다 나은 경우가 많습니다. 특히 필요한 데이터를 얻기 위해 여러 테이블을 참조해야 하는 경우에는 더욱 그렇습니다. 결과 집합에 "합계"를 포함하는 루틴은 일반적으로 커서를 사용하는 것보다 빠릅니다. 개발 시간이 허락한다면 커서 기반 방법과 세트 기반 방법을 모두 시도하여 어떤 방법이 더 효과적인지 확인할 수 있습니다.

28. 모든 저장 프로시저 및 트리거의 시작 부분에 SET NOCOUNT ON을 설정하고 끝 부분에 SET NOCOUNT OFF를 설정하세요. 저장 프로시저 및 트리거의 각 문 후에 클라이언트에 DONE_IN_PROC 메시지를 보낼 필요가 없습니다. 🎜🎜29. 대규모 트랜잭션 작업을 피하고 시스템 동시성을 개선하세요. 🎜🎜30. 클라이언트에 많은 양의 데이터를 반환하지 않도록 하세요. 데이터 양이 너무 많으면 해당 요구 사항이 합리적인지 고려해야 합니다. 🎜🎜🎜🎜더 많은 관련 무료 학습 권장사항: 🎜🎜🎜mysql 튜토리얼🎜🎜🎜(동영상)🎜🎜🎜

위 내용은 MySQL에서 빅데이터 쿼리를 최적화할 때 주의해야 할 사항은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!