江苏龙网

  1. 主页 > 生活百科 > >

技术总监夸我“索引”用的溜，我飘了......( 三 )

索引


对于多列联合索引 ， 哪列在前 ， 哪列在后也是有讲究的 ， 我们一起来看看加了索引 ， 为何却不生效?
加了索引却不生效可能会有以下几种原因：
①索引列是表示式的一部分 ， 或是函数的一部分
如下 SQL：
SELECT book_id FROM BOOK WHERE book_id + 1 = 5; 或者：
SELECT book_id FROM BOOK WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(gmt_create) <= 10 上述两个 SQL 虽然在列 book_id 和 gmt_create 设置了索引  ， 但由于它们是表达式或函数的一部分 ， 导致索引无法生效 ， 最终导致全表扫描 。
②隐式类型转换
以上两种情况相信不少人都知道索引不能生效 ， 但下面这种隐式类型转换估计会让不少人栽跟头 ， 来看下下面这个例子 。
假设有以下表：
CREATE TABLE `tradelog` (`id` int(11) NOT NULL,`tradeid` varchar(32) DEFAULT NULL,`operator` int(11) DEFAULT NULL,`t_modified` datetime DEFAULT NULL,PRIMARY KEY (`id`),KEY `tradeid` (`tradeid`),KEY `t_modified` (`t_modified`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 执行 SQL 语句：
SELECT * FROM tradelog WHERE tradeid=110717; 交易编号 tradeid 上有索引 ， 但用 EXPLAIN 执行却发现使用了全表扫描 ， 为啥呢 ， tradeId 的类型是 varchar(32) 。
而此 SQL 用 tradeid 一个数字类型进行比较 ， 发生了隐形转换 ， 会隐式地将字符串转成整型 ， 如下：
mysql> SELECT * FROM tradelog WHERE CAST(tradid AS signed int) = 110717; 这样也就触发了上文中第一条的规则  ， 即：索引列不能是函数的一部分 。
③隐式编码转换
这种情况非常隐蔽 ， 来看下这个例子：
CREATE TABLE `trade_detail` (`id` int(11) NOT NULL,`tradeid` varchar(32) DEFAULT NULL,`trade_step` int(11) DEFAULT NULL, /*操作步骤*/`step_info` varchar(32) DEFAULT NULL, /*步骤信息*/PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; trade_detail 是交易详情 ，  tradelog 是操作此交易详情的记录 ， 现在要查询 id=2 的交易的所有操作步骤信息 ， 则我们会采用如下方式：
SELECT d.* FROM tradelog l, trade_detail d WHERE d.tradeid=l.tradeid AND l.id=2; 由于 tradelog 与 trade_detail 这两个表的字符集不同 ， 且 tradelog 的字符集是 utf8mb4 ， 而 trade_detail 字符集是 utf8 ， utf8mb4 是 utf8 的超集 ， 所以会自动将 utf8 转成 utf8mb4 。
即上述语句会发生如下转换：
SELECT d.* FROM tradelog l, trade_detail d WHERE (CONVERT(d.traideid USING utf8mb4)))=l.tradeid AND l.id=2; 自然也就触发了 「索引列不能是函数的一部分」这条规则 。怎么解决呢 ， 第一种方案当然是把两个表的字符集改成一样 ， 如果业务量比较大 ， 生产上不方便改的话 。
还有一种方案是把 utf8mb4 转成 utf8 ， 如下：
mysql> SELECT d.* FROM tradelog l , trade_detail d WHERE d.tradeid=CONVERT(l.tradeid USING utf8) AND l.id=2; 这样索引列就生效了 。
④使用 order by 造成的全表扫描
SELECT * FROM user ORDER BY age DESC 上述语句在 age 上加了索引 ， 但依然造成了全表扫描 ， 这是因为我们使用了 SELECT * ， 导致回表查询 ， MySQL 认为回表的代价比全表扫描更大 。
所以不选择使用索引 ， 如果想使用到 age 的索引 ， 我们可以用覆盖索引来代替：
SELECT age FROM user ORDER BY age DESC 或者加上 limit 的条件(数据比较小)：
SELECT * FROM user ORDER BY age DESC limit 10 这样就能利用到索引 。
无法避免对索引列使用函数 ， 怎么使用索引
有时候我们无法避免对索引列使用函数 ， 但这样做会导致全表索引 ， 是否有更好的方式呢 。
比如我现在就是想记录 2016 ~ 2018 所有年份 7 月份的交易记录总数：


推荐阅读


上一篇：Apache CommonCollection Gadget 几种特殊的玩法

下一篇：SDN的主要组织