mysql在工作中遇到的

一：数据类型

数据类型上踩到过一次坑，是因为sql的不规范写法的，在这之前确实没有注意到过平时的写法，就是在表结构上那一列字段类型是字符串的，然后在sql查询的时候是数字：

where  col1 = 1234;

这样子结果就导致了索引失效，这个是因为mysql的内部的执行计划导致的，我个人是这么理解的，就比如索引的失效条件中有一个是对索引的条件进行函数运算，然后在上面的sql ，因为字段类型是 字符串，然后where 后面是数字，那么mysql 就会自动的把数字转化为字符串（隐式加了函数），所以就导致了索引失效；

和他类似的也遇到过一个，那个是有关于$ 和 # 的问题，就是用到了mybaits，当时的sql大概是：

select ... from   where ...  order by #{}

这个#{} 真的是习惯性的写上去了，因为平时都这么写的，用在预编译上可以防止sql注入，结果当时用了之后发现order by 没有生效，一开始是没找到原因，sql 打印出来也没发现任何的问题，但就是排序失效

select ... from   where ...  order by 'age'

然后就是自己也手打了一遍的sql，发现在手打的sql 排序是有效果的

select ... from   where ...  order by age

仔细对比后发现是因为排序字段后面加了 引号，引号就是#{} 来预编译，后续改成了

select ... from   where ...  order by ${}

时间字段自动赋值：

关于updateTime /creatTime 这类自动赋值的字段，之前的时候我都是用mybatis-plus 来进行自动赋值的，有次我看到了别人没有配置自动填充，在业务代码里面也没有设置属性值，但就是自动更新了，然后查了一下，发现mysql 针对字段类型

DATETIME和 TIMESTAMP 有2个自动赋值的属性 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE

create table test (
      col1 DATETIME DEFAULT CURRENT_TIMESTAMP,
      col2 DATETIME ON UPDATE CURRENT_TIMESTAMP,
      col3 DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

在上面col1 就可以创建的时候空的情况下设置个当前时间戳

col2 就可以在更新的时候更新最新的时间

然后就是关于字符集的，因为我之前都是这种的没出现过啥问题

但是之前有遇到过一次，在老表上导入emoji表情的时候，发现导入不进去，格式不支持，后续就是改了该表的结构

二：锁

关于锁，主要是关于间隙锁，之前有遇到过一次情况就是因为间隙锁的产生从而引发了死锁问题，至于读锁写锁，共享锁等，因为没遇到过这方面的问题，所以暂时先不记录

1：间隙锁（cap lock）不锁记录 RR隔离级别

2：临建锁（next-key lock）锁住数据，以及数据前的间隙 RR隔离级别

临建锁可以理解为间隙锁+行锁

锁升级：

前提就是在默认的事务隔离级别下，Innodb会使用next-key (临键锁)进行扫描

1：遇到唯一索引的时候，如果是已经存在的记录进行等值匹配，那么临建锁会变成行锁，如果这条记录不存在，那么临建锁会变成间隙锁；

2：如果查询条件是没有通过索引的，那么会针对表的所有记录都行锁

3：常规的等值查询中，查询的条件左边为临建锁(锁自身和左边范围)后边为间隙锁（锁自身和右边范围）

比如在 需要查询的数据c 在 a ，b 之间，那么innodb加锁是 ==>（a，c] +(c,b) 也就是 锁住（a，b）

-----》 这个其实可以这么理解，因为是常规的索引，比如需要查询name = zs 的数据，但是常规索引不保证数据唯一，就有可能是在查询的时候，后续又会加入name = zs 的数据，所以需要对左右都要进行加锁

当初遇到的死锁问题就类似于条件3，当时的业务代码流程大概就是 先进行更新数据，然后再插入一条数据； 于是就出现了问题

1：事务A 先更新数据 5，此时锁住了数据 （3，7），因为只是常规索引，所以操作会把左右都加上间隙锁，但是3,7这2个数据没有被锁
2：事务B也进行了类似的操作，导致数据 （3，7）被锁，（因为间隙锁是可兼容的，就是共享锁，所以2个锁可以共存）
3：事务A又插入了数据6，此时因为存在着间隙锁，但是数据6是插入操作，插入操作会添加插入意向锁，插入意向锁遇到了间隙锁就会等待
4：事务B也进行了同样的操作，于是就出发了死锁，innodb底层对于死锁就会触发异常

ps：我在网上看到过一个关于并发的优化，就是在一个事务中如果是先用update，那么从用update开始就会直接上锁，然后如果是先用select，因为普通的select不会加锁，那么他锁的时间就会变短，就能提高效率

死锁排查sql（8.0.19）

-- 1、查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX;
-- 2、查看正在锁的事务
SELECT * FROM performance_schema.data_locks;
-- 3、查看等待锁的事务
SELECT * FROM performance_schema.data_lock_waits;
-- 5、查看最近死锁的日志
show engine innodb status;
-- 查看当前正在进行中的进程
show processlist

三：索引

索引上，基本上遇到的问题就是索引添加的太多了，然后innodb内部是会自己选择索引的，选择的就不一定是最优解，所以可以进行指定索引

force index 这类的指定sql

遇到过几个特殊情况：

1: 2个表的编码风格不一样，就是一个是utf8 一个是utf8mb4 ，这就导致了索引失效

2：在订单表中有着商户id 和时间 作为索引，因为要统计一段时间下的商户嘛，然后就是mysql 选择的索引是时间索引，然后就是因为订单表中有很多的商户，有些大商户呢订单数量多，有些商户订单数量少，然后因为innodb他查询数据的话是一页一页加载到内存中进行匹配的，如果遇到了小商户，他订单少，然后innodb就得重复加载多次之后才能筛选到想要的数据，这就导致了小商户查询时间长，大商户查询时间短，这个时候就比较适合使用 联合索引

3： 这个就是听别人说起的了，因为我的话 删除数据都是用逻辑删除的，然后有人会使用物理删除嘛，然后因为mysql 在选择索引的时候会通过 show index 中的cardinal ，但是这个cardinal数据只会新增不会减少，也就是删除数据不会导致这个减少，因此 需要偶然使用analyze tables 来维护表数据