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/