join索引失效

join索引失效

今天我就简简单单的开发一个新功能，开心的写sql，

大概类似这种

select * from
act_hi_option
join act_hi_taskinst on act_hi_option.task_id =act_hi_taskinst.id_
order by act_hi_option.end_time desc limit 5;

先到测试环境执行看看，emmm。不对啊，是不是网断了，为什么直接卡住不动的样子。

在本地跑跑看，emm，能执行呀。

然后order by 条件去掉，也能执行呀。为什么啊。

这就是一个简简单单的连接查询呀。连表查询两个表，一个表1w，一个表15w。为什么卡死了。

第一个考虑的就是锁表

锁表了？

我本来以为是不是测试环境有程序在跑，会不会锁表了，不对呀，一个简简单单的内连接查询

select会锁表，但分情况，一般是加S锁（共享读锁），如果是有索引，会给行锁，如果没有索引，则给的是表锁。

S锁不会影响到其他的查询，但是会影响到插入和更新，也就是说，如果你有一个查询很慢，且进行了表锁，你的插入和更新都会被影响到，但不会影响其他的查询，但如果有索引，走的行锁，又不会影响到其他的插入和更新。

原文链接：https://blog.csdn.net/iteye_5212/article/details/108893825

那是为啥

按道理，act_hi_taskinst,id主键索引，一般走了的呀。

口说无凭，先explain看看

emmm，走的全表扫描，不应该啊。join的连接索引情况

Mysql在遇到inner join联接语句时，MySQL表关联的算法是 Nest Loop Join（嵌套联接循环），Nest Loop Join就是通过两层循环手段进行依次的匹配操作，最后返回结果集合。SQL语句只是描述出希望连接的对象和规则，而执行计划和执行操作要切实际将一行行的记录进行匹配。Nest Loop Join的操作过程很简单，很像我们最简单的排序检索算法，两层循环结构。进行连接的两个数据集合（数据表）分别称为外侧表（驱动表）和内侧表（非驱动表）。Mysql又会怎样去确定，哪张表是驱动表，哪张表又是非非驱动表呢？mysql它以表中数据最小的一张表作为驱动表(也就是基表)，而另一张表就叫做非驱动表，首先处理驱动表中每一行符合条件的数据，之后的每一行数据和非驱动表进行连接匹配操作，直到循环结束，最后合并结果、返回结果给用户。对于驱动表的字段它是可以直接排序的，然而对于非驱动表的字段排序需要通过循环查询的合并结果（临时表）进行排序，因此，order by rak_score desc,rank_uploadtime desc 时，就先产生了 using temporary（使用临时表），而我们都知道，数据库的底层就是IO操作，数据库无法对表排序，所以最后要借助于磁盘，那么产生using filesort（使用文件排序）就不难理解了！

https://blog.csdn.net/qq_16605855/article/details/78039922

按道理，会走act_hi_taskinst的id_的索引的，难道要在act_hi_option上建索引？（我建了，走了索引了，但是感觉不对，应该会走的act_hi_taskinst的id索引的）。

那基本就是索引失效了

为什么join的索引失效了？

然后我就搜到了join的字符集不同会导致索引失效。

然后show table 一看，emm，一个utf8，一个utfbmp4。基本确定是字符集不同导致索引失效了。

这个算是遗留问题了，我们系统用到了工作流，而当时工作流的建表语句带上了字符集，没用建库时候的默认字符集，导致工作流的表和我们自己的表，字符集不同。本来也没啥事，毕竟我们工作流和其他表不交互，然后这次突然交互，暴露出来了。

然后怎么解决呢。

https://mp.weixin.qq.com/s/FsmVdOW28M6lsUfGGnDIig

1、调整SQL语句

2、调整字符集一致，建议

我当然想调整这个工作流的字符集，调整这个表的字符集的语句，

修改表的字符集

ALTER TABLE act_hi_taskinst CONVERT TO CHARACTER SET utf8mb4;

但是，额，工作流的表里很多外键，如果想要调整一个表的字符集，需要先去掉外键，然后调整字符集，然后加回来。

emm，我尝试去掉外键，然后发现一个表外键关联了10几个~，放弃

然后选了第一个。

然后再看一下，走索引了吗。

emm。很好的走了索引。

复盘

卡死——》想到锁表阻塞——》explain查看索引是否命中-》join的索引方式（这里我觉得有异议，不一定是小表驱动大表，好像看的是哪个字段有索引，所以应该在大表上建索引）。

然后想想看为什么不加order by能执行，加了不能，测试环境不能执行，本地库能。

两个猜想：

1.因为不走索引，所以

连接表，排序都在内存中，所以内存不够，运行很慢？可能是测试环境内存设置比较小，本地内存较大？

2.会不会因为不走索引，orderby的时候是加了表锁，而线上的表可能有程序在改动，导致死锁了。

目前看来第二种情况可能更大点，因为如果是内存，我运行了很久也没出结果，基本可以确定不是内存的原因。

比较靠谱的回答

那么，select查询会锁表吗？

有可能，某个查询使用非聚集索引来select数据，那么它会在非聚集索引上持有一个S锁。当有一些select的列不在该索引上，它需要根据rowid找到对应的聚集索引的那行，然后找到其他数据。而此时，第二个的查询中，update正在聚集索引上忙乎：定位、加锁、修改等。但因为正在修改的某个列，是另外一个非聚集索引的某个列，所以此时，它需要同时更改那个非聚集索引的信息，这就需要在那个非聚集索引上，加第二个X锁。select开始等待update的X锁，update开始等待select的S锁，死锁发生了。

原文链接：https://blog.csdn.net/yujianping_123/article/details/103409287