mysql出现Copying to tmp table耗时较长，硬盘读100%，修改配置tmp_table_size 无效

测试积点老人

mysql出现Copying to tmp table耗时较长，硬盘读100%，修改配置tmp_table_size 无效，
sql 不加limit all类型查询就用了2秒左右；加limit 3000， range类型就一直卡在copy to tmp table ，mysql读硬盘100%，怎么破

1. <p>SELECT
   
2.     car_phone,
   
3.     count(*) AS count
   
4. FROM
   
5.     `car_info_t`
   
6. WHERE
   
7.     `car_phone` <> ''
   
8. AND `car_number` REGEXP '^[1-9][[:digit:]]{7}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}$|^[1-9][[:digit:]]{5}[1-9][[:digit:]]{3}((0[[:digit:]])|(1[0-2]))(([0|1|2][[:digit:]])|3[0-1])[[:digit:]]{3}([0-9]|X)
   
9. 
   
10. sql 不加limit all类型查询就用了2秒左右；加limit 3000， range类型就一直卡在copy to tmp table ，mysql读硬盘100%，怎么破
    
11. 
    
12. 
    
13. 
    
14. GROUP BY
    
15.     `car_phone`
    
16. ORDER BY id ASC limit 3000;</p><p>不加limit
    
17. 1 SIMPLE car_info_t ALL car_phone    597170 Using where; Using temporary; Using filesort</p><p>加limit
    
18. 1 SIMPLE car_info_t range car_phone car_phone 767  298585 Using where; Using temporary; Using filesort</p>

复制代码

sql 不加limit all类型查询就用了2秒左右；加limit 3000， range类型就一直卡在copy to tmp table ，mysql读硬盘100%，怎么破

bellas

来学习下

jingzizx

调优的不清楚