查询sql尽量不要使用select * ,而是具体字段。
理由:①只取需要的字段,节省资源、减少网络开销
②select * 进行查询时,很可能不会用到索引,就会造成全表扫描
避免在where中使用or来连接条件,使用union all
eg:select * from student where id=1 union all select * from student where salary=30000
理由:①使用or可能会使索引失效,从而全表扫描
②对于or没有索引的salary这种情况,假设它走了id的索引,但是走到salary查询条件时,它还得全表扫描。也就是说整个过程需要三步:全表扫描+索引扫描+合并。如果它一开始就走全表扫描,直接一遍扫描就搞定。虽然mysql是有优化器的,处于效率与成本考虑,遇到or条件,索引还是可能失效的
使用explain分析你SQL执行计划
sql很灵活,一个需求可以很多实现,那哪个最优呢?sql提供了explain关键字,它可以分析你的sql执行计划,看它是否最佳。Explain主要看sql是否使用了索引。
eg:explain select * from student where id=1
避免在where子句中使用!=或<>操作符
应尽量避免在where子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。
记住实现业务优先,实在没办法,就只能使用,并不是不能使用。
where中使用默认值代替null
反例:explain selsct * from student where age is not null 12
正例:explain select * from student where age>0
理由:①并不是说使用了is null 或者 is not null 就会不走索引了,这个跟mysql版本以及查询成本都有关
②如果mysql优化器发现,走索引比不走索引成本还要高,就会放弃索引,这些条件 !=,<>,is null,is not null经常被认为让索引失效,其实是因为一般情况下,查询的成本高,优化器自动放弃索引的
③如果把null值,换成默认值,很多时候让走索引成为可能,同时,表达意思也相对清晰一点
查询尽量避免返回大量数据
如果查询返回数据量很大,就会造成查询时间过长,网络传输时间过长。
同时,大量数据返回也可能没有实际意义。如返回上千条甚至更多,用户也看不过来。
通常采用分页,一页习惯10/20/50/100条。
尽量使用数值替代字符串类型
主键(id):primary key优先使用数值类型int,tinyint
性别(sex):0-代表女,1-代表男;数据库没有布尔类型,mysql推荐使用tinyint
支付方式(payment):1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
服务状态(state):1-开启、2-暂停、3-停止
商品状态(state):1-上架、2-下架、3-删除
使用varchar代替char
理由:①varchar变长字段按数据内容实际长度存储,存储空间小,可以节省存储空间
②char按声明大小存储,不足补空格
③其次对于查询来说,在一个相对较小的字段内搜索,效率更高
不要有超过5个以上的表连接
inner join 、left join、right join,优先使用inner join
三种连接如果结果相同,优先使用inner join,如果使用left join左边表尽量小
①inner join 内连接,只保留两张表中完全匹配的结果集
②left join会返回左表所有的行,即使在右表中没有匹配的记录
③right join会返回右表所有的行,即使在左表中没有匹配的记录
理由:①如果inner join是等值连接,返回的行数比较少,所以性能相对会好一点
同理,使用了左连接,左边表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能比较少。这是mysql优化原则,就是小表驱动大表,小的数据集驱动大的数据集,从而让性能更优