mysql数据库 数据类型与表字段类型不一致导致索引失效

  • ​​一：背景介绍​​
  • ​​二：思路&方案​​
  • ​​数值类型​​
  • ​​日期和时间类型​​
  • ​​字符串类型​​
  • ​​二进制类型​​
  • ​​问题复现​​
  • ​​结论​​
  • ​​三、扩展​​
  • ​​索引列上有计算​​
  • ​​对索引使用函数​​
  • ​​对索引隐式类型转换​​
  • ​​其余索引失效的情况后续进行补充​​
  • ​​四：总结​​
  • ​​五：升华​​

一：背景介绍

mysql库中有两张表的查询速度特别慢，一张表是76015条数据，另一张表是217069条数据。推测使用由于数据类型与表字段类型不一致导致需要进行类型转换和索引失效导致查询速度慢的问题。

项目实战典型案例12——mysql数据库 数据类型与表字段类型不一致导致索引失效_java


项目实战典型案例12——mysql数据库 数据类型与表字段类型不一致导致索引失效_字符串_02

二：思路&方案

在进行思路和方案的制定之前，我们先学习一下mysql的数据类型。
MySQL的数据类型分为四种 数值型、字符型 日期和时间类型 二进制类型
数值型可以分为：整数类型浮点数类型

数值类型

类型

说明

大小（bytes）

存储范围（无符号）

存储范围（带符号）

TINYINT

很小的整数

1

0 〜255

-128〜127

SMALLINT

小的整数

2

0〜65535

-32768〜32767

MEDIUMINT

中等大小的整数

3

0〜16777215

-8388608〜8388607

INT (INTEGHR

普通大小的整数

4

0〜4294967295

-2147483648〜2147483647

BIGINT

大整数

8

0〜18446744073709551615

-9223372036854775808〜9223372036854775807

FLOAT

单精度浮点数

4

0 和 1.175494351E-38～3.402823466E+38

-3.402823466E+38～1.175494351E-38

DOUBLE

双精度浮点数

8

0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

(-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)

DECIMAL (M, D)，DEC

压缩的“严格”定点数

M+2

DECIMAL 的存储空间并不是固定的，而由精度值 M 决定，占用 M+2 个字节。

注意： 在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

日期和时间类型

类型

说明

大小（bytes）

YEAR

年份值

1

TIME

时间值或持续时间

3

DATE

日期值

3

DATETIME

混合日期和时间值

8

TIMESTAMP

混合日期和时间值，时间戳

4

字符串类型

类型

说明

大小（bytes）

CHAR(M)

固定长度非二进制字符串

M 字节，1<=M<=255

VARCHAR(M)

变长非二进制字符串

L+1字节，在此，L< = M和 1<=M<=255

TINYTEXT

非常小的非二进制字符串

L+1字节，在此，L<2^8

TEXT

小的非二进制字符串

L+2字节，在此，L<2^16

MEDIUMTEXT

中等大小的非二进制字符串

L+3字节，在此，L<2^24

LONGTEXT

大的非二进制字符串

L+4字节，在此，L<2^32

ENUM

枚举类型，只能有一个枚举字符串值

1或2个字节，取决于枚举值的数目 (最大值为65535)

SET

一个设置，字符串对象可以有零个或 多个SET成员

1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

注意：VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

二进制类型

类型

说明

大小（bytes）

BIT(M)

位字段类型

大约 (M+7)/8 字节

BINARY(M)

固定长度二进制字符串

M 字节

VARBINARY (M)

可变长度二进制字符串

M+1 字节

TINYBLOB (M)

非常小的BLOB

8

BLOB (M)

小 BLOB

L+2 字节，在此，L<2^16

MEDIUMBLOB (M)

中等大小的BLOB

L+3 字节，在此，L<2^24

LONGBLOB (M)

非常大的BLOB

L+4 字节，在此，L<2^32

问题复现

表索引

项目实战典型案例12——mysql数据库 数据类型与表字段类型不一致导致索引失效_mysql_03


表字段类型

项目实战典型案例12——mysql数据库 数据类型与表字段类型不一致导致索引失效_mysql_04


查询语句

使用数值类型进行查询

EXPLAIN
    SELECT * FROM arpro_chapter_template 
    WHERE
      is_delete =0
      AND 
      active_id=385538879022694400

结果

索引失效

会发现type类型变成了all全表查询，索引已经失效。

项目实战典型案例12——mysql数据库 数据类型与表字段类型不一致导致索引失效_数据库_05


使用字符串类型查询

EXPLAIN
    SELECT * FROM arpro_chapter_template 
    WHERE
      is_delete ='0'
      AND 
      active_id=385538879022694400

结果

索引生效

项目实战典型案例12——mysql数据库 数据类型与表字段类型不一致导致索引失效_数据库_06

结论

在进行数值类型转换时，会使我们的索引失效。补充：mysq在遇到字符串和数字比较的时候，会默认将字符串转换为数值类型进行处理，所以如果is_delete类型为数值类型，那么如果sql赋值给它的数据类型为字符串类型，那么索引是不会失效的。

我们在进行实体设计，包括给sql语句赋值的时候。最好是与数据库的数据类型保持以及，避免由于数据类型不一致的原因出现索引失效的情况。

三、扩展

总结索引失效的情况

索引列上有计算

执行sql如下：

EXPLAIN
    SELECT * FROM arpro_chapter_template 
    WHERE
    is_delete+1=1

可以看出变成了全表扫描，索引列上有计算，索引会失效。因为索引里存储的是列的原始值而不是计算后的值。

项目实战典型案例12——mysql数据库 数据类型与表字段类型不一致导致索引失效_mysql_07

对索引使用函数

在索引列上加某个函数，sql如下：

EXPLAIN
    SELECT * FROM arpro_chapter_template 
    WHERE
    SUM(is_delete)=1

编程全表扫描，索引失效。因为索引里存储的是列的原始值而不是计算后的值。

项目实战典型案例12——mysql数据库 数据类型与表字段类型不一致导致索引失效_字符串_08

对索引隐式类型转换

1.如果索引字段是字符型，但是条件查询时，传入的是整型的话，会出现索引失效问题。
2.如果索引是整型，但是条件查询的时候，传入的是字符型，不会出现索引失效问题。
mysq在遇到字符串和数字比较的时候，会默认将字符串转换为数值类型进行处理，所以如果is_delete类型为数值类型，那么如果sql赋值给它的数据类型为字符串类型，那么索引是不会失效的。

上面的案例已经进行了实践，不再进行演示。

其余索引失效的情况后续进行补充

四：总结

1.与数据库打交道需要特别注意数据类型是否对应，不能忽视如何数据类型不一致会带来什么影响。
2.在开发过程中规避掉索引失效的情况，不使用索引与使用索引带来截然不同的效率。

五：升华

在总结博客的过程中，战胜了非理性，又在理性的阵营中加强了一步。
对于每一个案例都进行了实践和验证。