优化方向:SQL语句优化、索引、分表...
SQL语句优化
1、不要返回用不到的数据
SELECT * FROM `shop_user`
=>SELECT `id`,`username` FROM `shop_user`
2、WHERE 跟 ORDER BY 是搜索的关键,避免全表扫描。
考虑在对应的列上建立索引;
WHERE 语句中不要使用 != 或 <>操作符
避免列出现null值的判断:SELECT `id` FROM `shop_user` WHERE `login_time` IS NULL,建议可以给 login_time 字段设置默认值为 0 ,将SQL语句修改为 SELECT `id` FROM `shop_user` WHERE `login_time`= 0
避免使用 OR 来做连接查询:SELECT `id` FROM`shop_user` WHERE `id`=1 OR `id`=2 ,建议修改为
select id from shop_user where id =1union allselect id fromshop_user where id =2
in 跟 not in慎用(网上很多人说 in 不走索引,不知真假)
between优化 select id fromshop_user where idbetween 1 and 3 修改为select id from shop_user where id>= 1 id <= 3
like字段慎用