1
关于 MySQL 函数效率的疑问
source link: https://www.v2ex.com/t/816330
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
在清理公司旧代码时顺便做点性能优化,发现公司里有很多业务在用
select date_format(create_time, '%Y-%m-%d') dateDay, count(*) count
from table
GROUP BY date_format(create_time, '%Y-%m-%d')
ORDER BY date_format(create_time, '%Y-%m-%d')
这样的语句全表扫描+日期转换。一个 47 万行的表这么跑一遍差不多 550-600ms 。而当我用 date(create_time)
换掉之后,耗时就会降到 230-300ms 。类似的还有用 CONCAT(YEAR(create_time), '-', MONTH(update_time)) dateMonth
替换 date_format(create_time, '%Y-%m')
之后,耗时也能减少 30%以上。对于 mysql 来说, date
这样的函数是比 date_format
的效率高出非常多的么?
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK