MyISAM 테이블에서 count(*)가 너무 느립니다.

MyISAM 테이블에서 count(*)가 너무 느립니다.

데이터베이스 엔진의 대부분이 MyISAM이고 서버 RAM은 약 20GB이지만 query_cache 및 기타 구성이 기본값(16MB)으로 설정되어 있는 빅데이터베이스를 가지고 있습니다.MySQL 버전은 5.7.27이며 3.5 데이터베이스의 이전 백업을 받았습니다.GB 및 로컬에서 쿼리를 실행하면 프로덕션보다 훨씬 빠릅니다.

FLUSH QUERY CACHE;
RESET QUERY CACHE;
SELECT SQL_NO_CACHE 
    count(*)
FROM
    company_reports rep
    LEFT JOIN system_users usr ON rep.user_id = usr.id
    LEFT JOIN company_rep_subjects sbj ON rep.subject_id = sbj.id
    INNER JOIN company_partners cus ON rep.partner_id = cus.id
    LEFT JOIN project_con_messages mes ON rep.message_id = mes.id
    LEFT JOIN company_par_user_settings pus ON cus.id = pus.partner_id 
    AND 1 = pus.user_id 
WHERE
    1=1 -- or other where and will generate by ACL and advanced search

캐시를 사용하지 않도록 설정했습니다.

 query_cache_limit = 0
 query_cache_size = 0

저는 왜 이 쿼리가 카운트를 얻기 위해 13s 정도의 생산이 필요한지 모르겠습니다.최적화 제안이 있습니까?RAM이 비어 있으므로 query_cache에 할당하는 것보다 캐시 설정이 몇 개 더 좋습니다.

중요: InnoDB로 마이그레이션하는 것이 마음에 드는데, 로컬에서 이 쿼리가 왜 이렇게 빠른지 모르겠습니다. MySQL이 프로덕션에서 레코드를 더 빨리 세는 것을 막을 수 있는 잠금 시스템이 있습니까?우리의 시스템이 진행 중이지 않고 작동 시간이 지났을 때도 테스트했습니다.

로컬 시스템은 MYSQL 5.7.27이 아니며 10.1.38-MariaDB를 사용하고 있습니다.

여기 내 지역에서 EXPLE이 있습니다.

1   SIMPLE  cus index   PRIMARY PRIMARY 3       62293   Using index
1   SIMPLE  rep ref partner_id  partner_id  3   MYDBNAME.cus.id 13  

운영 중:

"왜 그들은 같지 않습니까!"로컬은 Mariadb 10(2열)이고 프로덕션은 MYSQL 5.7.27(6열)입니다.

편집: 로컬 인덱스를 프로덕션과 동기화한 후 로컬이 느려집니다!그것도 좋고 생산 속도가 느린 이유와 어떤 지표를 놓쳤는지 알아냈습니다.나는 생산에 두 개의 색인을 추가해야 합니다, 댓글을 읽어보세요.

새로운 설명(로컬):

1   SIMPLE  cus index   PRIMARY companyname 362     63490   Using index
1   SIMPLE  rep ref partner_id  partner_id  3   ebrahim.cus.id  5   
1   SIMPLE  pus ref partner_id_2,user_id    partner_id_2    3   ebrahim.cus.id  2   Using where

제 로컬이 주를 사용하도록 강제하면 회사 이름을 인덱스에 사용하는 이유를 알 수 없지만 인덱스가 동기화되어 있음에도 불구하고 현재 설명이 다른 이유를 알 수 없습니다.

편집: buffer_key_size를 4G로 늘리고 partner_id와 user_id를 복합 인덱스로 추가한 후 모든 쿼리 테이블에서 Unique 및 run Analyze 실행 속도가 6.7초로 증가했습니다.

join_buffer_size=>262144
key_buffer_size=>4294967296
myisam_sort_buffer_size=>8388608
net_buffer_length=>16384
read_buffer_size=>131072
preload_buffer_size=>32768

query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 16777216

'%buffer%'와 같은 변수 표시

bulk_insert_buffer_size
8388608
innodb_buffer_pool_chunk_size
134217728
innodb_buffer_pool_dump_at_shutdown
ON
innodb_buffer_pool_dump_now
OFF
innodb_buffer_pool_dump_pct
25
innodb_buffer_pool_filename
ib_buffer_pool
innodb_buffer_pool_instances
1
innodb_buffer_pool_load_abort
OFF
innodb_buffer_pool_load_at_startup
ON
innodb_buffer_pool_load_now
OFF
innodb_buffer_pool_size
134217728
innodb_change_buffer_max_size
25
innodb_change_buffering
all
innodb_log_buffer_size
16777216
innodb_sort_buffer_size
1048576
join_buffer_size
262144
key_buffer_size
4294967296
myisam_sort_buffer_size
8388608
net_buffer_length
16384
preload_buffer_size
32768
read_buffer_size
131072
read_rnd_buffer_size
262144
sort_buffer_size
262144
sql_buffer_result

미리 감사드리고 제 영어실력에 대해 사과드립니다.

• 반드시 큰 것이 더 좋은 것은 아닙니다.query_cache_size테이블이 수정되면 해당 테이블의 모든 항목(QC)이 제거됩니다.QC가 클수록 시간이 더 오래 걸립니다.50M합리적인 최대값입니다.
• 필요한 비 QC 캐싱은 쿼리의 모든 테이블을 포함합니다. 인덱스 블록은 다음으로 이동해야 합니다.key_buffer데이터 블록은 OS에 의해 캐시되어야 합니다.제공하십시오.SHOW VARIABLES LIKE '%buffer%';두 기계 모두에서.
• 사용량이 많은 프로덕션 시스템의 13초는 사용량이 적은 다른 시스템에서 발생하지 않은 캐시 누락을 포함할 수 있습니다.
• 카운트가 정확합니까?겉으로 에는 쓸모없어 보이는 것들이 .JOINs이것들은 공기를 팽창시킬 수 있습니다.COUNT(*)

답은 다음과 같습니다.

MariaDB와 MySQL의 Optimizer 코드가 서로 다릅니다.MariaDB는 대부분의 테이블이 쿼리와 관련이 없다고 (정확하게) 판단한 것 같습니다.

나는 당신이 오직 사용하기 위해 쿼리를 다시 쓰는 것을 제안합니다.cus그리고.repMySQL 프로덕션 시스템에서 실행합니다.그런 다음 현재 6-테이블 쿼리와 동일한 결과가 나오는지 확인합니다.그리고 쿼리가 훨씬 더 빨리 실행되는지 확인합니다.

(한 가지 참고 사항...) 장기적으로 볼 때 My ISAM은 사라지고 있습니다.당신은 InnoDB로 이동할 계획을 세워야 합니다.

언급URL : https://stackoverflow.com/questions/58062193/count-is-so-slow-in-myisam-table