阐述常见的一些SQL优化方式

SQL优化

1. 避免不必要的列

这个是老生常谈,但还是经常会出的情况,SQL查询的时候,应该只查询需要的列,而不要包含额外的列,像 select * 这种写法应该尽量避免。

2. 分页优化

在数据量比较大、分页深度较深的情况下,可以考虑以下分页优化方案:

  • 延迟关联(Deferred Join):先通过WHERE条件提取出主键,然后再将该表与原数据表进行关联,通过主键ID提取数据行,而不是使用原有的二级索引提取数据行。这样可以减少关联操作的数据量,提高查询效率。
    例如:
SELECT a.* FROM table a, 
 (SELECT id FROM table WHERE type = 2 AND level = 9 ORDER 
BY id ASC LIMIT 190289, 10) b
WHERE a.id = b.id
  • 书签方式(Bookmark):书签方式是根据LIMIT语句中的第一个参数对应的主键值,通过该主键值进行过滤和限制。先找到满足条件的第一个主键值,然后再根据该主键值进行过滤并进行LIMIT操作。
    例如:
SELECT * FROM table WHERE id > 
 (SELECT id FROM table WHERE type = 2 AND level = 9 ORDER 
BY id ASC) LIMIT 190, 10

3. 索引优化

合理地设计和使用索引,是优化慢SQL的利器。
利用覆盖索引InnoDB使用非主键索引查询数据时会回表,但是如果索引的叶节点中已经包含要查询的字段,那它没有必要再回表查询了,这就叫覆盖索引
例如对于如下查询:

select name from test where city='上海'

我们将被查询的字段建立到联合索引中,这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

4. 低版本避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询,可以使用 union 或者子查询来替代,因为早期的 MySQL 版本使用 or 查询可能会导致索引失效,高版本引入了索引合并,解决了这个问题。

5. 避免使用 != 或者 <> 操作符

SQL中,不等于操作符会导致查询引擎放弃查询索引,引起全表扫描,即使比较的字段上有索引
解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描
例如,把 column<>’aaa’,改成column>’aaa’ or column<’aaa’ ,就可以使用索引了

6.适当使用前缀索引

适当地使用前缀索引,可以降低索引的空间占用,提高索引的查询效率。
比如,邮箱的后缀都是固定的“ @xxx.com ”,那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));

PS:需要注意的是,前缀索引也存在缺点,MySQL无法利用前缀索引做order by和group by 操作,也无法作为覆盖索引

7. 避免列上函数运算

要避免在列字段上进行算术运算或其他表达式运算,否则可能会导致存储引擎无法正确使用索引,从而影响了查询的效率

8. 正确使用联合索引

使用联合索引的时候,注意最左匹配原则。

  • 14
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值