标题: mysql出现Copying to tmp table耗时较长，硬盘读100%，修改配置tmp_table_size 无效 [打印本页] 作者: 测试积点老人 时间: 2020-2-17 10:56 标题: 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%，怎么破
<p>SELECT
car_phone,
count(*) AS count
FROM
`car_info_t`
WHERE
`car_phone` <> ''
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)
sql 不加limit all类型查询就用了2秒左右；加limit 3000， range类型就一直卡在copy to tmp table ，mysql读硬盘100%，怎么破
GROUP BY
`car_phone`
ORDER BY id ASC limit 3000;</p><p>不加limit
1 SIMPLE car_info_t ALL car_phone 597170 Using where; Using temporary; Using filesort</p><p>加limit
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%，怎么破