28个案例问题分析---012---数据库数据类型问题--Mysql数据类型，索引失效

桃扇骨 2023-10-09 15:47 36阅读 0赞

#### Mysql数据类型+索引失效 ####

*  一：背景介绍
 *  二：思路&方案
 *   *  Mysql数据类型
     *   *  数值类型
         *  日期/时间类型
         *  字符串（字符）类型
     *  索引失效复现
     *  结论
 *  三：扩展
 *   *  索引失效
     *   *  对索引使用左或者左右模糊匹配
         *  对索引使用函数
         *  对索引进行表达式计算
         *  对索引隐式类型转换
         *  联合索引非最左匹配
         *  WHERE 子句中的 OR
 *  四：总结

## 一：背景介绍 ##

MySql数据库的is\_delete字段，两个不同的表，一个表内有217069条数据，另一个表内有76015条数据，查询起来发现速度特别的慢，推测是索引失效的问题，让我们来尝试进复现  
![在这里插入图片描述][66a3b1629f204199a18d7cb9a70ab413.png]  
![在这里插入图片描述][24411acd5afd434f9aafe49358229b45.png]

## 二：思路&方案 ##

### Mysql数据类型 ###

在进行验证之前，我们先来学习一下mysql的三种数据类型。Mysql主要分为三种类型\*\* 数值\*\*、**日期/时间**和\*\*字符串(字符)\*\*类型。

#### 数值类型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小(bytes)</th> 
   <th>范围（无符号）</th> 
   <th>范围（带符号）</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>tinyint</td> 
   <td>1</td> 
   <td>(-128，127)</td> 
   <td>（0,255）</td> 
   <td>小整数值</td> 
  </tr> 
  <tr> 
   <td>smallint</td> 
   <td>2</td> 
   <td>(-32 768，32 767)</td> 
   <td>（0,65 535）</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>mediumint</td> 
   <td>3</td> 
   <td>(-8 388 608，8 388 607)</td> 
   <td>（0,16 777 215）</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>int</td> 
   <td>4</td> 
   <td>(-2 147 483 648，2 147 483 647)</td> 
   <td>（0,4 294 967 295）</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>bigint</td> 
   <td>8</td> 
   <td>(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)</td> 
   <td>（0,18 446 744 073 709 551 615）</td> 
   <td>极大整数值</td> 
  </tr> 
  <tr> 
   <td>float</td> 
   <td>4</td> 
   <td>(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)</td> 
   <td>0，(1.175 494 351 E-38，3.402 823 466 E+38)</td> 
   <td>单精度，浮点数值</td> 
  </tr> 
  <tr> 
   <td>double</td> 
   <td>8</td> 
   <td>(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td> 
   <td>0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)</td> 
   <td>双精度，浮点数值</td> 
  </tr> 
  <tr> 
   <td>decimal</td> 
   <td>对decimal（M，D），如果M&gt;D,为M+2否则为D+2</td> 
   <td>依赖于M和D的值</td> 
   <td>依赖于M和D的值</td> 
   <td>小数值</td> 
  </tr> 
 </tbody> 
</table>

#### 日期/时间类型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小(bytes)</th> 
   <th>范围</th> 
   <th>格式</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>date</td> 
   <td>3</td> 
   <td>1000-01–1/9999-12.31</td> 
   <td>YYYY-MM_DD</td> 
   <td>日期值</td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>3</td> 
   <td>‘-838:59:59’/‘838:59:59’</td> 
   <td>HH:MM:SS</td> 
   <td>时间值或持续时间</td> 
  </tr> 
  <tr> 
   <td>year</td> 
   <td>1</td> 
   <td>1901/2155</td> 
   <td>YYYY</td> 
   <td>年份值</td> 
  </tr> 
  <tr> 
   <td>datetime</td> 
   <td>8</td> 
   <td>‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’</td> 
   <td>YYYY-MM_DD hh:mm:ss</td> 
   <td>混合日期和时间值</td> 
  </tr> 
  <tr> 
   <td>timestamp</td> 
   <td>4</td> 
   <td>有专有的自动更新特性</td> 
   <td>YYYY-MM_DD hh:mm:ss</td> 
   <td>混合日期和时间值，时间戳</td> 
  </tr> 
 </tbody> 
</table>

#### 字符串（字符）类型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>大小（bytes）</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>char</td> 
   <td>0 - 255</td> 
   <td>定长字符串</td> 
  </tr> 
  <tr> 
   <td>varchar</td> 
   <td>0 - 65 535</td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td>tinyblob</td> 
   <td>0 - 255</td> 
   <td>不超过255个字符的二进制字符串</td> 
  </tr> 
  <tr> 
   <td>tinytext</td> 
   <td>0 - 255</td> 
   <td>短文本字符串</td> 
  </tr> 
  <tr> 
   <td>blob</td> 
   <td>0 - 65535</td> 
   <td>二进制形式长文本数据</td> 
  </tr> 
  <tr> 
   <td>text</td> 
   <td>0 - 65535</td> 
   <td>长文本数据</td> 
  </tr> 
  <tr> 
   <td>mediumblob</td> 
   <td>0 - 16 777 215</td> 
   <td>二进制形式的中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>mediumtext</td> 
   <td>0 - 16 777 215</td> 
   <td>中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td>longblob</td> 
   <td>0 - 4 294 967 295</td> 
   <td>二进制形式极大文本数据</td> 
  </tr> 
  <tr> 
   <td>longtext</td> 
   <td>0 - 4 294 967 295</td> 
   <td>极大文本数据</td> 
  </tr> 
 </tbody> 
</table>

### 索引失效复现 ###

首先我们摆出结论。where条件里，字符类型的列如过传递数值类型可以查出来数据，但是会索引失效。  
对应的索引  
![在这里插入图片描述][0fcd835655074c93a72d8e6e19f241bb.png]  
is\_delete 字段的类型  
![在这里插入图片描述][6878a4707b8e451aa2dcd84f081c6bce.png]  
**使用数值型进行查询**  
![在这里插入图片描述][28bd9ab0608c4a17887d087d88117da8.png]  
我们使用EXPLAIN关键字分析可以发现。type是 ALL，也就是全表扫描，并没有走索引。  
**使用字符型查询**  
![在这里插入图片描述][321003359fb347d28dfb3c3047df5fac.png]  
我们发现 type类型变成了ref，说明是走了我们新建的索引的。

### 结论 ###

数值类型的的转换，会使我们的**索引失效**，所以我们设计实体类的数值类型的时候，一定要和数据库的数据类型进行对应，避免索引失效的情况的出现。

## 三：扩展 ##

既然这次出现了索引失效，那么我们就总结一下，常见的索引失效的情况。

### 索引失效 ###

#### 对索引使用左或者左右模糊匹配 ####

使用 %xx 或者 %xx% 都会造成索引失效问题。但是 xx% 不会造成索引失效。

#### 对索引使用函数 ####

在where条件里，使用函数。比如 select \* from user where length(name) = 6  
这里进行的就是全表扫描，因为索引里存储的是列的原始值而不是计算后的值

#### 对索引进行表达式计算 ####

例如：**select \* from user where age + 1 = 10** 。  
这里也将会进行全表扫描，原因与对索引使用函数一样，都是因为索引内存储的是原始值而不是计算后的值。但是如果将上文的查询修改为 select \* from user where age = 10 - 1 的话就没有问题，是会正常走age的索引的

#### 对索引隐式类型转换 ####

我们上文讲到，mysql有三种数据类型，分别是 **数值型、日期型、字符型** ,能够隐式转换的就是 数值型和字符型了。  
1.如果索引字段是字符型，但是条件查询时，传入的是整型的话，会出现索引失效问题。  
2.如果索引是整型，但是条件查询的时候，传入的是字符型，不会出现索引失效问题。  
**这是因为mysq在遇到字符串和数字比较的时候，会默认将字符串转换为数值类型进行处理**

#### 联合索引非最左匹配 ####

假设我们对字段 a，b ，c 建立了一个联合索引（a，b，c）。  
如果我们的查询是以下几种，则会正常走联合索引：

*  where a = 1;
 *  where a = 2 and b = 2;
 *  where a = 2 and b = 2 and c = 2;

如果是如下几种则不会走联合索引：

*  where b = 2;
 *  where c = 2;
 *  where b = 2 and c = 3;

通过对比我们可以看出，索引的生效是遵循最左匹配原则的。在使用的时候，必须从最左侧的索引开始。

还有一种特殊的情况

*  where a = 2 and c = 2 ；

这种情况的话，在MySql 5.5的时候，前面的a会走索引，找到对应的主键值后，开始进行回表查询。Mysql 5.6 后，会进行索引下推，速度更快。索引还是会生效。

#### WHERE 子句中的 OR ####

如果where 子句里 or前的列是索引列，or后面的条件列不是索引列，索引就会失效。  
例如：  
select \* from user where id = 2 or age = 10 ;  
id是主键，默认是主键索引。 age如果没有添加为主键的话，都会进行全表扫描。 因为or表示两个条件满足一个就可以。所以只有一个列有索引的话，没有作用，还是会去全表扫描。

## 四：总结 ##

开发的过程中，要谨记这些索引失效的大坑，这样以后我们在使用的时候，才能够避免！！

[66a3b1629f204199a18d7cb9a70ab413.png]: https://img-blog.csdnimg.cn/66a3b1629f204199a18d7cb9a70ab413.png
[24411acd5afd434f9aafe49358229b45.png]: https://img-blog.csdnimg.cn/24411acd5afd434f9aafe49358229b45.png
[0fcd835655074c93a72d8e6e19f241bb.png]: https://img-blog.csdnimg.cn/0fcd835655074c93a72d8e6e19f241bb.png
[6878a4707b8e451aa2dcd84f081c6bce.png]: https://img-blog.csdnimg.cn/6878a4707b8e451aa2dcd84f081c6bce.png
[28bd9ab0608c4a17887d087d88117da8.png]: https://img-blog.csdnimg.cn/28bd9ab0608c4a17887d087d88117da8.png
[321003359fb347d28dfb3c3047df5fac.png]: https://img-blog.csdnimg.cn/321003359fb347d28dfb3c3047df5fac.png