sql篇之Mysql数据库添加索引经验(工作中积攒)

例子: 

SELECT * FROM tms_order WHERE store_nbr in ( 'S79023' , 'S61968' , 'S75069' , 'S78733' , 'S26752' , 'S77883' , 'S47871' , 'S58789' , 'S51793' ) AND date_format(created_at,'%Y-%m-%d') = '2023-04-03' AND dispatch_status in ( '10' ) AND status in ( '10' , '80' )

1.如果查询某个字段,唯一度高(只有几个值的话)不建议添加索引列,相反如果唯一度低,则可以添加为索引项,上述status和dispatch_status唯一度较高,所以不加索引;

2.上述created_at 不能嵌套函数,否则不走索引,尽量放到后面参数上

改写为:

 SELECT * FROM tms_order WHERE store_nbr in ( 'S79023' , 'S61968' , 'S75069' , 'S78733' , 'S26752' , 'S77883' , 'S47871' , 'S58789' , 'S51793' )
 AND created_at BETWEEN  '2023-04-03 00:00:00' AND "2023-04-03 23:59:59" AND dispatch_status in ( '10' ) AND status in ( '10' , '80' )