SQL查询优化:拒绝一整天只跑一个查询

爱数据精选
爱数据精选
爱数据精选
545
文章
0
评论
2021-03-0114:09:10 评论 187 2131字
摘要

SQL优化,无论是面试还是工作,都是常问和常用的知识点。这是一个相对复杂的问题,在MySQL官方文档中也是用了相当长的篇幅来专门介绍MySQL优化,所以想要全部掌握可能会有点难啃,今天主要带大家从SQL查询语句来进行优化。

一、为什么要使用索引?

  1. 通过创建唯一性索引,可以保证数据库中每一行数据的唯一性。
  2. 可以极大地加快数据的检索速度(大大减少检索的数据量),也是创建索引的最主要原因。
  3. 帮助服务器避免排序和临时表。
  4. 可以加速表与表之间的链接,特别是在实现数据的参考完整性方面有更突出的意义。

索引是如何提高查询速度的?

将无序的数据变成相对有序的数据,就像查看目录一样。

二、具体内容

最左前缀法则:

如果一个表的索引有多个字段(即多列索引),那么我们需要从最左列开始按照顺序,且中间不能跳过索引列来进行查询。

举个例子:

比如有3个字段a、b、c,均设置为索引,那么可以写a,a&b,a&b&c这样使用索引,而不能使用b&c,a&c这样来进行查询。

具体代码示例:

可行的方案:

1. 用到 aselect a,b,c from test where a = "box";2. 用到 a&bselect a,b,c from test where a = "box" and b = 10;select a,b,c from test where a = "box" and (b = 10 or b = 20);select a,b,c from test where a = "box" and b>=10 and b<20;3. 用到 a&b&cselect a,b,c from test where a = "box" and b = 10 and c = "20200101";select a,b,c from test where a = "box" and (b = 10 or b = 20) and (c = "20200101" or c = "20200201");select a,b,c from test where a = "box" and (b = 10 or b = 20) and c >= "20200101" and c <= "20200201";

不可行的方案

1. 用到 b select a,b,c from test where b = 10; 2. 用到 a or b select a,b,c from test where a = "box" or b = 10 3. 用到 a&c select a,b,c from test where a = "box" and c = "20200101"; 4. 用到 b&c select a,b,c from test where b = 10 and c = "20200101";

索引失效的情况:

避免在索引列上进行任何操作:计算。函数、类型转换等,否则会导致索引失效而变成全表扫描。

索引失效的语句:select a,b,c from test where week(c,1) = 24;

覆盖索引:

尽量使用覆盖索引,即查询的列被索引覆盖,减少使用select *语句。例如上面最左前缀法则中的例子,用具体的列字段名替代*。

like语句:

以通配符‘%’开头的情况会导致索引失效从而变成全表扫描。

例如:

可行的:select a,b,c from test where a like "b%";不可行的:select a,b,c from test where a like "%x";

or和in的使用:

少用or和in,在使用这个的时候MySQL不一定会使用到索引,MySQL优化器会根据索引比例、表大小等多个因素来评估时候使用索引。

很多时候用 exists 来代替 in 是一个比较好的选择。

in 语句:SELECT * FROM A WHERE A.id IN (SELECT id FROM B);转换为exists 语句:SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

游标的使用:

尽量避免使用游标,因为游标的效率较差。

其他优化:

SQL语句的逻辑优化。即重新梳理逻辑再进行代码的实现。

在具体实践过程中,可以多使用explain来查看,具体使用方法是在查询语句开头加上explain即可。

结果列的详细解释如下:

table:表名type:很重要的一列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALLpossible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句key:实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好ref:显示索引的哪一列被使用了,如果可能的话,是一个常数rows:MYSQL认为必须检查的用来返回请求数据的行数

不是所有情况都适合建立索引

创建索引和维护索引的时间成本会随着数据增多而增加,会占用物理空间;后期维护数据也要维护索引,从而降低了数据的维护速度。

以下情况不适合建立索引

  • 在查询过程中很少使用或参考的列。
  • 只有很少数据值的列,不应该创建索引,例如:性别。
  • 对于那些定义为image,text和bit数据类型的列。
  • 修改性能远大于检索性能。
  • 重复值较多。

End.

本文为挖数网原创文章,未经允许禁止转载,需要转载请微信联系授权(微信号:lovedata0520)

更多优质文章搜索http://www.itongji.cn/

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: