mysql慢查询记录(一)

(一)导致慢查询的操作

1.sql加for update锁
同样的查询条件，加锁会比不加锁慢100ms左右
表里总数据量180w，task_id=197的有35w，task_id建立了索引

sql语句	是否加锁	查询时长
SELECT * FROM picquery WHERE (state = 1 AND task_id = 197) ORDER BY id ASC LIMIT 1;	否	0.331
SELECT * FROM picquery WHERE (state = 1 AND task_id = 197) ORDER BY id ASC LIMIT 1 for update;	是	0.438

for update锁必须在事务中才会生效，下面是详细使用情况

事务A	事务B	是否阻塞	锁表还是锁行
begin; select * from picquery where task_id=197 limit 1 for update;	begin; select * from picquery where task_id=197 limit 1;	不阻塞	锁行
begin; select * from picquery where task_id=197 limit 1 for update;	begin; select * from picquery where task_id=197 limit 1 for update;	阻塞，需要等事务A提交之后，才能查询	锁行
begin; select * from picquery where task_id=197 limit 1 for update;	begin; select * from picquery where task_id=29 limit 1 for update;	不阻塞，因为锁的是不同的行	锁行
begin; select * from picquery where weight=1 limit 1 for update;	begin; select * from picquery where weight=1 limit 1 for update;	阻塞	锁表
begin; select * from picquery where weight=1 limit 1 for update;	begin; select * from picquery where weight=2 limit 1 for update;	阻塞	锁表

2. count()慢
   count()完全走索引就会很快，不完全走索引就会很慢
   表里总数据量180w，task_id=209的有100w，task_id建立了索引

sql语句	是否完全走索引	查询时长
SELECT count(*) from engine_picquery WHERE task_id=209;	是	0.289
SELECT count(*) from engine_picquery WHERE task_id=209 and is_delete=False;	否	1.565

3.sql中有混合排序
表里总数据量180w，task_id=209的有100w，task_id建立了索引

sql语句	排序情况	查询时长
SELECT * FROM picquery WHERE state = 1 AND task_id = 209 ORDER BY weight DESC, id ASC LIMIT 1;	混合排序	2.610
SELECT * FROM picquery WHERE state = 1 AND task_id = 209 ORDER BY id ASC LIMIT 1;	没有混合排序	0.057
SELECT * FROM picquery WHERE state = 1 AND task_id = 209 ORDER BY weight ASC LIMIT 1;	没有混合排序，按非主键字段排序，且此字段上无索引	2.911

(二)待证实会导致慢查询的操作

1.字段类型转换会导致慢查询
表里总数据量180w，task_id=209的有100w，task_id建立了索引

sql语句	是否有类型转换	查询时长
SELECT * FROM picquery WHERE state = 1 AND task_id = 209 ORDER BY id ASC LIMIT 1;	无	0.057
SELECT * FROM picquery WHERE state = “1” AND task_id = “209” ORDER BY id ASC LIMIT 1;	有	0.057