18 | 在索引字段上加函数踩坑经验

最新推荐文章于 2024-01-04 09:58:41 发布

笔仙笔仙

最新推荐文章于 2024-01-04 09:58:41 发布

阅读量675

点赞数

分类专栏： MySQL笔记文章标签： mysql java

原文链接：https://time.geekbang.org/column/article/74059

版权

MySQL笔记专栏收录该内容

48 篇文章 1 订阅

订阅专栏

本文探讨了MySQL中函数操作导致索引失效的原因，以及如何在全索引扫描下优化查询。同时，解释了隐式类型转换对查询性能的影响，并给出了避免全表扫描的策略。此外，还提到了不同字符编码表连接查询的问题，以及如何通过预估范围减少扫描行数的技巧。最后，讨论了表连接查询中表访问顺序的影响。

摘要由CSDN通过智能技术生成

一、条件字段上加函数操作
背景：现有交易记录表 tradelog 包含交易流水号（tradeid）、交易员 id（operator）、交易时间（t_modified）等字段：

mysql> 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;

现有需求：表中记录了从 2016 年初到 2018 年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中 7 月份的交易记录总数。
SQL：mysql> select count(*) from tradelog where month(t_modified)=7;

重点重点重点

🏁：1.如果对字段做了函数计算，就用不上索引了，这是 MySQL 的规定。why？因为加索引破坏了索引值的有序性，因此优化器放弃走树搜索功能。
⚠️：优化器并不是要放弃使用这个索引。
在这个例子里，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 t_modified，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。但是，会进行全索引扫描。

二、隐式类型转换

举例：mysql> select * from tradelog where tradeid=110717; 这个SQL会有什么问题？

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

好奇心：数据类型转换的规则是什么？为什么有数据类型转换，就需要走全索引扫描？

⭐️：简单验证，看 select “10” > 9 的结果，select “10” > 9 返回的是 1，所以你就能确认 MySQL 里的转换规则了。在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。

答案：举例中的答案就是会造成全表扫描，SQL等价于mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

举一反三：如果select * from tradelog where id=“83126”;会怎么样？答：字符串会转换为数字，不会造成全表扫描

三、隐式隐式字符编码转换

⭐️：两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以两个表做表连接查询的时候用不上关联字段的索引。本质是相当于MySQL内部对关联字符串进行了字符编码的转换，如果想要走索引关联关系可以直接在SQL层转换，where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

四、课后思考
分享踩坑经验，

五、评论出人才
🏁：1.当一个没有索引的大表在做等值查询的时候，如何减少扫描行数？
表结构：

CREATE TABLE `task` (
  `task_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `task_type` int(11) DEFAULT NULL COMMENT '任务类型id',
  `task_rfid` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '关联外键1',
  PRIMARY KEY (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='任务表';

举例：select * from task where task_rfid =123;
答案：
猜测主键task_id的范围，怎么猜，我原表有creat_time字段，我会先查
select max(task_id) from task_history 然后再看看 select * from task_history where task_id = maxId - 10000的时间，估计出大概的id范围。然后语句变成
select * from task_history where task_rfid =99 and id between ？ and ？;

🏁：2.表的访问顺序与连接方式，条件字段有关，跟书写顺序无关，请参考《数据库索引设计与优化》第八章的表访问顺序对索引设计的影响。

🏁：3.面试题，题目是这样的，a表有100条记录，b表有10000条记录，两张表做关联查询时，是将a表放前面效率高，还是b表放前面效率高？
答案：如果是考察语句写法，这两个表谁放前面都一样，优化器会调整顺序选择合适的驱动表；如果考察关联关系用小表做left。
PS：这里跟in的查询优化相同