MySQL- 索引字段的函数使用

目录

条件字段函数操作：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。进行全表扫描。

隐式类型转换： 字符串和数字做比较的话，是将字符串转换成数字。

隐式字符编码转换：比较字段使用不同的字符集，utf8 和 utf8mb4 。

解决办法： 对于需要进行转换后进行比较的字段，可以选择将函数是加在输入参数上的，这样可以使用索引。

---

条件字段函数操作：

Mysql中规定，如果对字段做了函数计算，就用不上索引了。

索引的B+Tree提供快速定位能力，来源于同一层兄弟节点的有序性。对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

 

隐式类型转换：

交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，以下条语句需要走全表扫描。

select * from tradelog where tradeid=110717;

 tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。

【在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。】因此上边的语句对于优化器来讲就相当于以下语句。对索引字段做了函数操作，从而优化器放弃了走树搜索，导致了全表扫描。

select * from tradelog where CAST(tradid AS signed int) = 110717;

隐式字符编码转换：

在表 关联上，其中一个表未走索引字段，进行了全表扫描

SELECT
	d.* 
FROM
	tradelog l,
	trade_detail d 
WHERE
	d.tradeid = l.tradeid 
	AND l.id = 2;

• 第 1 步，是根据 id 在 tradelog 表里找到 L2 这一行；
• 第 2 步，是从 L2 中取出 tradeid 字段的值；
• 第 3 步，是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。

explain 的结果里面第二行的 key=NULL 表示的就是，这个过程是通过遍历主键索引的方式，一个一个地判断 tradeid 的值是否匹配。

是因为这两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引。

MySQL需要将，utf8 字符串转成 utf8mb4 字符集，再做比较。也就是 对索引字段做函数操作，优化器会放弃走树搜索功能。

解决办法：

如果将以上语句修改成以下方式，将函数是加在输入参数上的，这样就可以用上被驱动表的 traideid 索引。

SELECT
	l.operator 
FROM
	tradelog l,
	trade_detail d 
WHERE
	d.tradeid = l.tradeid 
	AND d.id = 4;

MySQL实战 - 18【5525】