MySql 索引优化

待我称王封你为后i 2022-06-05 11:08 266阅读 0赞

在了解EXPLAIN 关键字之后呢，我们就可以根据一些属性查看我们的SQL 语句是否是高效的，从而使我们编写出高效的SQL 语句，接下来就是一些实际应用啦。我在这里建立了一张表并存放了一些数据(建立了一个复合索引)。执行查询SQL 语句，共有三条记录：  
![这里写图片描述][SouthEast]

查看建立的索引：  
![这里写图片描述][SouthEast 1]

#### 最佳左前缀原则 ####

下面我们使用执行查询语句，这里我们只使用了一个索引字段username，可以看出使用到了我们创建的索引，并且type 为ref，ref 为一个const。  
![这里写图片描述][SouthEast 2]

下面我们使用两个索引字段，通过结果可以看出同样使用了索引，并且ref 的变为了两个const，随着查询条件的增多，key\_len 的长度变大，说明查询消耗的资源也就相对多一些。  
![这里写图片描述][SouthEast 3]

当我们使用三个索引字段的时候呢？ 同样使用了索引，并且随着查询条件的变多，key\_len 会变得更大，ref 也变为了三个const。  
![这里写图片描述][SouthEast 4]

也许细心的你已经发现上面的查询条件顺序是按照创建索引的顺序进行的，尽管并没有完全用到创建索引的字段，但是在查询时，仍然使用到了索引。如果我们不按照创建索引的顺序进行查询呢？下面两个查询语句都没有用到username 字段，在三个索引字段中它是最前面的那个，当我们忽略它时发现我们的查询语句并没有用到索引，我们创建的索引索引失效了，type 也由 ref 变成了ALL 全表扫描，rows 也由1 变成了3。这显然不是我们希望的那样。  
![这里写图片描述][SouthEast 5]  
![这里写图片描述][SouthEast 6]

如果我们使用了username 字段并且跳过了中间的那个字段又会是什么样的呢？会不会使用我们创建的索引呢？可以看出确实使用了我们创建的索引type 为ref，但是key\_len 是768，ref 是一个const，而不是两个const，说明并没有使用创建索引的全部字段，只是使用了username 字段，而sex字段并没有使用索引。但是这种情况在某种程度上比上面那种情况要好一些吧？但是作为程序猿我们肯定想要按照我们创建索引的方式使用我们的索引。因此这种方式使用索引并不是最佳的。  
![这里写图片描述][SouthEast 7]

因此我们在使用索引的时候要遵循最佳左前缀原则，什么是最佳左前缀原则呢？  
最佳左前缀原则：当我们创建了的索引包括多列，查询的顺序应该是从索引的最前端开始，并且不要跳过索引中的其他列。

#### 不要在索引列上做计算，函数等操作 ####

如果我们在索引列上操作MySql 的一些函数呢还会按照索引查询我们的数据吗？下面执行了一个left(attribute，len)函数，为了知道left 函数是什么，我做了两次查询，想必聪明的你一定知道是什么意思了吧。  
![这里写图片描述][SouthEast 8]  
![这里写图片描述][SouthEast 9]  
知道了left 函数是什么意思后，我们就用EXPLAIN 执行以下它吧，它会使用我们创建的索引吗？根据执行结果可以看出，并没有使用创建的索引，type 为ALL 全表扫描，rows 为3。  
![这里写图片描述][SouthEast 10]  
所以在我们使用索引时不要在索引列上做任何的操作(这些操作包括：计算，函数等)，它会使索引失效，转向全表扫描查找数据。

#### 不要在索引上做范围操作 ####

如果我们在索引列上做范围条件筛选会发生什么呢？可以看出type 由ref 变为了range(如果你不清楚这些属性是什么意思，请看博主MySql 中关于EXPLAIN 属性的j介绍)，但是使用了我们的索引，key\_len = 773，不是1541 说明并没做全部使用索引字段查询，只是使用了username 字段和age 字段。  
![这里写图片描述][SouthEast 11]  
如果我们的业务需要用到条件范围筛选，那么我们就不要在该字段上创建索引。

#### 在查询时尽量覆盖索引列，少使用SELECT \* … ####

当我们在查询数据时，如果不使用SELECT \* ..，而是覆盖索引列，那么查询效率是不是会高一些呢？看看执行结果，与SELECT \*… 相对比，key\_len 的长度是不变的，唯一有变化的是Extra ,由NULL 变成了Using index,当Extra 中出现Using index 时说明我们编写的SQL 是很高效的。  
![这里写图片描述][SouthEast 12]  
![这里写图片描述][SouthEast 13]  
即当我们编写SQL 查询语句时，根据业务需求，如果不是要查询所有的字段那么我们就尽量少的使用SELECT \* …. 查询语句。

#### 不要在索引上使用 != 、 <> 、 IS NULL 、 IS NOT NULL ####

如果我们使用了会怎么样呢？答案就是在查询数据时不会使用到索引key 为NULL，type 是ALL 全表查找数据，导致索引失效。  
![这里写图片描述][SouthEast 14]  
所以我们不要在索引上使用 != 、 <> 、 IS NULL 、 IS NOT NULL 进行筛选，这样会导致索引失效。

#### 模糊查询”%” 放关键字右边 ####

有的时候我们的需求需要做模糊查询，那么模糊查询使用索引时要注意什么呢？首先我们在关键字两边都加% 进行查询。可以看出没有使用索引。  
![这里写图片描述][SouthEast 15]  
如果在关键字左边加% 呢？答案是也没有使用索引。  
![这里写图片描述][SouthEast 16]  
那如果在关键字右边加% 呢？这时type 由上面的ALL 变为了range ，并且也是用到了索引。  
![这里写图片描述][SouthEast 17]  
所以在能保证工作需求的情况下，当我们遇到这种情况时，要将% 放在关键字的右边。但是如果将% 放在右边并不能完成业务的需求，那又要怎么办呢？  
当查询的数据是索引列时，使用到了索引。  
![这里写图片描述][SouthEast 18]  
![这里写图片描述][SouthEast 19]  
当查询的数据既有索引列又有非索引列时，type 为ALL， key 为NULL， 没有使用索引。  
![这里写图片描述][SouthEast 20]  
由结果可以得出当使用”% + 关键字”可以完成我们的业务需求时，此时我们就使用”% + 关键字”，因为它使用了索引。当”% + 关键字” 不能满足需求的时候，那么我们查询的数据要覆盖索引列(当然也可以包含主键),但是覆盖索引列时不要添加不是索引的列，这样会使索引失效。

#### 字符串类型的索引不要忘记加引号 ####

这里就不测试了，意思是如果我们的字段是字符串类型的，并且有的值为数字，那么我们在不加引号的时候也可以通过该字段查到数据，因为MySql 会自动给我们转换类型。但是我们在使用索引的时候，上面的操作是不会使用索引的。

#### 少使用OR ####

下面我们就用OR 连接查询条件看看执行结果，从下图可以看出索引失效了，type 是ALL 全表扫描数据。  
![这里写图片描述][SouthEast 21]  
在我们编写SQL 语句时，要尽量避免这样的情况出现。

[SouthEast]: /images/20220605/c7757dcd5bcf4b878dec1c300d020309.png
[SouthEast 1]: /images/20220605/247b6cebe7a644959ac1c930a6ca4d46.png
[SouthEast 2]: /images/20220605/f592bf163f5d4726993646308e9b8a77.png
[SouthEast 3]: /images/20220605/9173de1af9304def9523b004f893852a.png
[SouthEast 4]: /images/20220605/6073224d76b24ad6abcbe6bd4ecca9d8.png
[SouthEast 5]: /images/20220605/84f65e2c6bea4018b43c856e7353941a.png
[SouthEast 6]: /images/20220605/73b1b70a232441308957f0d9b84b6b57.png
[SouthEast 7]: /images/20220605/600439557f9046efa643eebf81bbfd7a.png
[SouthEast 8]: /images/20220605/286b001529d44e1ca1ef5e736c08ab80.png
[SouthEast 9]: /images/20220605/ab25b7481549418f81c0ba2ae261b704.png
[SouthEast 10]: /images/20220605/061bd628aeab4c6fa6bb0fceb9ea6a68.png
[SouthEast 11]: /images/20220605/5514a58212f9435b80953fd3ad52e55f.png
[SouthEast 12]: /images/20220605/3bacf98484744cbca9914b3f90f1a97c.png
[SouthEast 13]: /images/20220605/c955df89471649d888683869aba444e5.png
[SouthEast 14]: /images/20220605/085e155f0def42918d6fa113c448f460.png
[SouthEast 15]: /images/20220605/6fc118e0e6594d52b2b35db479357932.png
[SouthEast 16]: /images/20220605/031ab8a770ae4a759a2bdb9a8c717f46.png
[SouthEast 17]: /images/20220605/2d1b0dbf2c7b48229c746a5344df444f.png
[SouthEast 18]: /images/20220605/aa0f5d94e66840e1ae099cade0949210.png
[SouthEast 19]: /images/20220605/9ab010950f4f47f7a5bb4d83c0a89c85.png
[SouthEast 20]: /images/20220605/c089c33cc4ac409ba66eacbbc044ab7f.png
[SouthEast 21]: /images/20220605/3a70b6825a8644239ceb86b523fe7cc9.png