SQL优化
SQL优化
蔡佳娃 发表于5个月前
SQL优化
  • 发表于 5个月前
  • 阅读 42
  • 收藏 2
  • 点赞 0
  • 评论 0

标题:腾讯云 新注册用户域名抢购1元起>>>   

摘要: 工作中遇到的一些SQL优化技巧,以及注意事项。

查询的模糊匹配

    尽量避免在一个复杂查询里面使用 like '%param%', 红色标识位置的百分号会导致相关列的索引无法使用,最好不要用。

    解决办法:

    a. 修改前台程序,把查询条件的文本输入,改为下拉列表,用户模糊输入时,直接在前台就帮忙定位到具体的参数,这样在调用后台程序时,这列就可以直接用=来查询了。

    b. 直接修改后台代码,根据输入条件,先查询出符合条件的记录,将记录保存到临时表中,然后在用临时表做复杂关联。

索引问题

    在做性能跟踪分析过程中,经常发现不少后台程序的性能问题是因为缺少合适的索引造成的,当然也有建立了索引但是使用不当导致索引失效的造成的。

    不要在存在索引的数据列上进行以下操作:

    a. 索引字段上进行计算

--避免:
select id from TB_ where num/2=100;
--推荐
select id from TB_ where num=100*2;

    b. 索引字段上使用not in, <>, !=

--避免
select * from TB_ where col not in (2, 3);
--推荐
select * from TB_ where col not exists (2, 3);

--避免
select * from TB_ where col!=2;
--推荐
select * from TB_ where col>2 or col<2;

    c. 索引字段上使用 is null, is not null

    表设计的时候,需要添加索引的字段上,尽量使用非空约束。

    d. 索引列上出现数据类型转换(隐式转换

--避免
select e.ename, e.job from emp e where e.empno='7369';
--推荐
select e.ename, e.job from emp e where e.empno=7369;

    e. 索引字段上使用函数

--避免
select EmpNo,LastName from Emp where YEAR(HireDate)=2005;
--推荐
select EmpNo,LastName from Emp where HireDate between '' and '';

复杂操作

    部分UPDATE、SELECT语句写的很复杂(经常嵌套多级子查询),可以考虑适当拆成几步,先生成一些临时数据表,再进行关联操作。 

连接条件or

    如果是这样一个查询:

SELECT * FROM TB WHERE A=1 AND B>2 AND C<3 AND D IN (4,5,6);

    并且在TB表上有这样一个索引:CREATE INDEX INX_TB_ABCD ON TB (A,B,C,D),那么这个查询可以用到这个索引。
    如果同样是这个索引,查询换成:

SELECT * FROM TB_ WHERE A=1 OR B>2 OR C<3 OR D IN (4,5,6);

    那么这个查询就用不到上面那个索引,因为结果集是几个条件的并集,最多只能在查找A=1的数据时用索引,其它几个条件都需要表扫描,那优化器就会选择直接走一遍表扫描,所以索引就失效了。
    那么像第二个查询这样的应该怎么建索引呢,答案就是四个列上各建一个索引,或者只在选择性最高的列上建索引,比如A=1的数据量很少,就在A上建,如果D是4,5,6的数据很少,就在D上建,这样优化器就会选择先走索引查找,再对找出的结果集进行筛选,扫描数就会大幅减少。当然还可以这样:

SELECT * FROM TB_ WHERE A=1 
union all
SELECT * FROM TB_ WHERE B>2
union all
SELECT * FROM TB_ WHERE C<3
union all
SELECT * FROM TB_ WHERE D IN (4,5,6);

    需要注意的是:

select * from TB_ where col=10 or col=20;

    两个条件都在col上,所以只要col上有索引,就不存在索引失效的问题。

其他

    a. 尽量避免大事务操作,提高系统并发能力。

    b. 尽量避免向客户端返回大数据量,如果数据量过大,应考虑需求是否合理。

    c. 任何地方都不要使用select *,使用具体的字段列表代替*,不返回用不到的字段。

    d. 尽可能使用varchar代替char,因为变长字段存储空间小,可以节省存储空间。

 

共有 人打赏支持
粉丝 16
博文 54
码字总数 48865
×
蔡佳娃
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: