SQL语句优化经验

1.模糊查询前导不会走索引
select id,user_name,price_code from user_activity_info where user_name like '%zhang';
如果非要使用前导索引的话可以借助Apache的Lucence索引工具

2.字段默认值不要设置成null
如果可以请设置为 not null

3.不要在SQL中对字段进行计算,这样会使索引失效
select user_name,price_code,create_time from user_activity_info where from_unixtime(create_time) > now();
4.不要对字段进行隐式转换
select user_name,price_code,create_time from user_activity_info where user_name=12345;
user_name是varchar类型,但是检索匹配值却是int型12345,这样会使user_name的索引失效

5.复合索引类的第一个字段列必须出现在条件中,复合索引中区分度高的要放在第一列
select id,user_name,price_code,create_time from user_activity_info where user_name="simons";
price_code和user_name是联合索引,但是where条件中只有user_name,且user_name是联合索引的第二列,所以这个SQL不会命中索引

6.筛选后的数据只有一条也得加上limit 1
select id,user_name,price_code,create_time from user_activity_info where id='256' limit 1;
这样也可以提高SQL效率,减少数据库游标移动

7.不要在低基数列上建索引,如性别字段,"墙裂"建议在区分度高的字段上建索引

8.负向查询不会使用索引,会导致全表扫描
select id,user_name,price_code,create_time from user_activity_info where id not in('1','5');
9.join查询列字段最好相同,否则可能导致发生隐式转换,不命中索引
select a.user_name,b.price_code,create_time from user_activity_info a join reward_info b on a.price_code=b.price_code;
user_activity_info和reward_info表中的price_code字段类型要一致

10.通过延迟关联(覆盖索引查询返回需要的主键,再根据主键关联原表获取所需数据)的方式

通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据,而不是通过二级索引获取主键再通过主键去遍历数据页。例如存在这么一条SQL:

explain SELECT id,actid,kbid,phaseid,security_code,code,validdays,enddate,status,vip_type,createtime 
FROM suning_act_card where status ='0' AND createtime >='2014-05-29' ORDER BY id asc LIMIT 2638327 ,20;

表数据量为4千万左右,根据上面的原则,可以改写为:

explain SELECT a.* FROM suning_act_card a, (select id from suning_act_card where status ='0' AND createtime >='2014-05-29'
  ORDER BY id asc LIMIT 2638327 ,20 ) b where a.id=b.id;
测试结果是执行速度提升了12%左右,且随着表数据量的增加提升效率越来越明显。看官可亲测下!!!


引申阅读:mysql开发规范

阅读更多

扫码向博主提问

simonsfan

非学,无以致疑;非问,无以广识
  • 擅长领域:
  • java开发
  • 项目管理
  • 团队建设
去开通我的Chat快问
版权声明:如果您觉得此文有用或对您有帮助,请不吝点个赞或留个言,哈哈! https://blog.csdn.net/fanrenxiang/article/details/80689824
文章标签: SQL优化
个人分类: Mysql
所属专栏: Mysql
上一篇高并发处理之接口限流
下一篇SwitchHosts快速切换hosts
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭