方法一：用date_format 函数

select count(*) from test where date_format(create_time1,'%Y%m%d')='20220101';
+----------+
| count(*) |
+----------+
|    98256 |
+----------+
1 row in set (17.969 sec)

加索引后

select count(*) from test where date_format(create_time1,'%Y%m%d')='20220101';
+----------+
| count(*) |
+----------+
|    98256 |
+----------+
1 row in set (5.718 sec)

方法二：用 > < 查询

 select count(*) from test where create_time1 >= '20220101' and create_time1 < '20220102';
+----------+
| count(*) |
+----------+
|    98256 |
+----------+
1 row in set (16.445 sec)

加索引后

select count(*) from test where create_time1 >= '20220101' and create_time1 < '20220102';
+----------+
| count(*) |
+----------+
|    98256 |
+----------+
1 row in set (0.049 sec)

方法三：用 SUBSTING函数

select count(*) from test where SUBSTRING(create_time1,1,10)='2022-01-01';
+----------+
| count(*) |
+----------+
|    98256 |
+----------+
1 row in set (16.880 sec)

加索引后

select count(*) from test where SUBSTRING(create_time1,1,10)='2022-01-01';
+----------+
| count(*) |
+----------+
|    98256 |
+----------+
1 row in set (4.234 sec)

方法四： between and

select count(*) from test where create_time1 between '2022-01-01' and '2022-01-02';
+----------+
| count(*) |
+----------+
|   100282 |
+----------+
1 row in set (15.842 sec)

加索引后

select count(*) from test where create_time1 between '2022-01-01' and '2022-01-02';
+----------+
| count(*) |
+----------+
|   100282 |
+----------+
1 row in set (0.045 sec)

方法五：时间用int保存形式

select count(*) from test where create_time between 1640966400 and 1641052800;
+----------+
| count(*) |
+----------+
|   100282 |
+----------+
1 row in set (15.755 sec)

加索引后

select count(*) from test where create_time between 1640966400 and 1641052800;
+----------+
| count(*) |
+----------+
|   100282 |
+----------+
1 row in set (0.037 sec)

PS.
1、使用between 条数多的原因是，1.2日00:00:00 数据也包含在内
2、create_time 为int类型 时间戳，create_time1 为 datetime类型
3、测试数据总条数为1300w