mysql 优化

1. 查询

1-1 group by 比distinct 快

[SQL]select driver_id from XXX where created_time > '2017-12-27 00:00:00' and created_time<'2017-12-28 00:00:00' group by driver_id
受影响的行: 0
时间: 0.418s
=============

[SQL]select DISTINCT(driver_id) from XXX where created_time > '2017-12-27 00:00:00' and created_time<'2017-12-28 00:00:00'
受影响的行: 0
时间: 0.556s

 

1-2 where 条件顺序 先过滤掉最多的、过滤最快的(int 比varchar快 比timestamp快)

[SQL]select driver_id,integral,source,city_id from XXX where created_time > '2017-12-27 00:00:00' and created_time<'2017-12-28 00:00:00' and driver_id=216654
受影响的行: 0
时间: 0.030s

==========

[SQL]select driver_id,integral,source,city_id from XXX where driver_id=216654 and created_time > '2017-12-27 00:00:00' and created_time<'2017-12-28 00:00:00'
受影响的行: 0
时间: 0.017s

1-3  日期函数处理

sql中经常有日期函数的使用 其中‘2018-03-01’、‘2018-03’一般为传递到持久层框架的参数

select * from driver_XXX where DATE_FORMAT(created_time,'%Y-%m-%d')== '2018-03-01'

select * from driver_XXX where DATE_FORMAT(created_time,'%Y-%m')= '2018-03'

此时 created_time字段的索引不会使用!!!

explain 查看执行计划 扫描行数 几乎全表

更改为

select * from driver_XXX where created_time BETWEEN '2018-03-01' and DATE_ADD('2018-03-01',INTERVAL 1 DAY)

select * from driver_XXX where created_time BETWEEN concat('2018-03','-01') and DATE_ADD(concat('2018-03','-01'),INTERVAL 1 MONTH)

再次执行explain 扫描行数有显著减少!!!