MariaDB中将DATETIME转为DATE的四种方法性能差异问询

Great question—let’s dive into the real-world performance differences between these conversion methods, especially when dealing with large datasets and multiple columns.

Core Performance Breakdown #

First, let’s unpack how each method works under the hood, since that’s where the performance gaps come from:

• DATE(datetime_expr)
  This is the purpose-built function for extracting the date portion from a DATETIME (or TIMESTAMP) in MariaDB/MySQL. Under the hood, it directly accesses the binary storage structure of the DATETIME type (which stores year, month, day, hour, minute, second as separate components) to pull out just the date part. No string conversion or formatting is involved—this is about as efficient as it gets.

• CAST(datetime_expr AS DATE) & CONVERT(datetime_expr, DATE)
  These two are functionally and performance-wise identical. CAST is MySQL/MariaDB’s implementation of the SQL standard, while CONVERT is the standard-compliant syntax. Both use the same underlying logic as DATE(): they skip string processing and directly extract the date from the binary DATETIME structure. You won’t notice any meaningful performance difference between these and DATE() in large datasets.

• DATE_FORMAT(datetime_expr, '%Y-%m-%d')
  This is where things change. Unlike the other methods, DATE_FORMAT first converts the DATETIME value to a string, then formats it to match the %Y-%m-%d pattern. String manipulation is inherently more CPU-intensive than direct binary extraction, especially when processing millions of rows or multiple columns. In large datasets, this method will consistently lag behind the other three—sometimes by a significant margin.

Other Potential Methods (And Why to Avoid Them) #

You might consider string-based hacks like LEFT(datetime_expr, 10) to get the date portion. Don’t do this. Not only does it require converting the DATETIME to a string (just like DATE_FORMAT), but it’s also fragile: if your server’s date string format ever changes (unlikely, but possible with non-default settings), this will break. It’s also no faster than DATE_FORMAT, and in some cases, slower.

Real-World Implications for Large Datasets #

When dealing with multi-column conversions across hundreds of thousands or millions of rows:

• DATE(), CAST(), and CONVERT() will keep your queries running as efficiently as possible, with negligible differences between them.
• DATE_FORMAT() will add measurable CPU overhead, leading to longer query times and increased load on your shared server (which is especially bad when you can’t control other users’ activity).

One quick note: if you’re using these conversions in a WHERE clause (e.g., filtering rows by date), all four methods will prevent the use of indexes on the DATETIME column (since you’re applying a function to the column). But that’s a separate concern from the conversion performance itself.

内容的提问来源于stack exchange，提问作者Sean