出现的问题
索引方面
- 没建立最优索引
- 走的不是最优索引
- 索引区分度低
- 索引长度太长导致查询效率低
- 索引失效问题-in(ids)导致索引失效
- group by 和 where 条件中的联合索引
-
其他方面
左连接的驱动表过滤条件不足，做连接拆分成子查询
- in(ids)数据量太大
- 没有传限定时间 OR 时间跨度太长
- 数据量大造成全表
执行方案时要注意的问题
- 重复索引应该删除，但确保代码中没有强制走该索引
- 数据量大的表，执行方案要选择凌晨
- 如果慢SQL的执行时间多在凌晨，看是否有必要优化
- Job的话，不一定需要优化
- 建索引记得使用规范
- 该表的索引数如何，是否超5个
- 索引的区分度低
``sql updatelq_detailsetdept_id= '4806',lq_detail.updated_time` =
‘2022-03-10 11:43:33’ where teacher_id = ‘29657’ and lq_detail.delete_time is null
<br />问题：没建立 teacher_id 的索引，但是对 delete_time 建立了索引<br />lq_detail 中 detail_time is null 的数据量约等于全表数据量（62w），区分度很低，那不应该是会全表扫描的成本低么，因为还要大量回表判断 teacher_id 是否满足条件。<br /><br />直接走全表扫描花费时间0.22s，rows = 62w，而用了索引花费时间0.66s ，rows = 32W。<br />这个rows 应该也是不准确的，可能受到了事务影响。走 idx_delete索引 的代价 = 扫描二级索引 + 回表MySQL 会在解析 SQL 语句之后，进入优化器，找到一个最优的执行方案，并用自认为的最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的**因素之一**。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。但 rows 不是唯一的评判指标，同时可能它是不准确的值，会导致走错索引。2. **索引合并问题**```sqlSELECT `id`,`cid`,`uid`,`up`,`down` FROM `ol_moment` WHERE `cid` = '1000'AND `uid` = 224 AND `type` = 2 AND `down` IS NULL ORDER BY id desc LIMIT 1// ol_moment 表 存在 uid索引、cid 索引
可以看到 MySQL 选用了索引合并方案去获取结果集，但是该方案 是否是最优的呢？
强制走uid索引，虽然rows 变成了4w多，但是时间却从 0.1s-> 0.038s
索引合并的优化原理：是分别用两个索引去执行SQL，然后取两个结果集的交集，便是该SQL的结果集。
也就是说一条SQL 分别走两条索引。
我猜可能索引合并可以大幅减少回表次数，uid 索引树存在id，cid 索引树存在id，然后先求出交集id，拿去主键索引找数据， 不过执行时间反而变长了。 直接使用uid 就得回表4w多，但是会快点。
- in（） 的 单个id太长 ```sql ottu.teacher_id AND otts.task_id = ott.id AND otts.is_deleted = 0) AND ottu.teacher_id IN (10376 , 10381 , 10387 , 10391 , 10397 , 10401 , 10402 , 10403 , 10405 , 10406 , 10407 , 10409 , 10410 , 10411 , 10412 , 10413 , 10414 , 10415 , 10416 , 10418 , 10421 , 10423 , 10424 , 10425 , 10426 , 10427 , 10429 , 10430 , 10431 , 10432 , 10434 , 10435 , 10437 , 10438 , 10439 , 10440 , 10441 , 10442 , 10444 , 10447 , 10451 , 10454 , 10455 , 10463 , 10464 , 10469 , 10470 , 10471 , 10472 , 10474 , 10476 , 10479 , 10481 , 10482 , 10483 , 10489 , 10490 , 10492 , 10495 , 10497 , 10506 , 10509 , 10512 , 10531 , 10534 , 10536 , 10551 , 10555 , 10557 , 10558 , 10559 , 10561 , 10565 , 10566 , 10567 , 10568 , 10569 , 10571 , 10572 , 10574 , 10578 , 10579 , 10581 , 10582 , 10583 , 10584 , 10585 , 10587 , 10588 , 10590 , 10591 , 10592 , 10593 , 10595 , 10612 , 10613 , 10614 , 10615 , 10616 , 10618) —————-依然不走ottu.teacher_id索引，50个—————— 10376 , 10381 , 10387 , 10391 , 10397 , 10401 , 10402 , 10403 , 10405 , 10406 , 10407 , 10409 , 10410 , 10411 , 10412 , 10413 , 10414 , 10415 , 10416 , 10418 , 10421 , 10423 , 10424 , 10425 , 10426 , 10427 , 10429 , 10430 , 10431 , 10432 , 10434 , 10435 , 10437 , 10438 , 10439 , 10440 , 10441 , 10442 , 10444 , 10447 , 10451 , 10454 , 10455 , 10463 , 10464 , 10469 , 10470 , 10471 , 10472 , 10474
—————-走ottu.teacher_id索引 —————— 1485 , 1486 , 1488 , 1489 , 1490 , 1494 , 1495 , 1496 , 1497 , 1498 , 1499 , 1500 , 1501 , 1505 , 1506 , 1507 , 1508 , 1509 , 1510 , 1511 , 1514 , 1516 , 1517 , 1519 , 1520 , 1522 , 1524 , 1525 , 1526 , 1527 , 1528 , 1529 , 1531 , 1533 , 1534 , 1535 , 1536 , 1537 , 1538 , 1539 , 1540 , 1541 , 1542 , 1543 , 1544 , 1545
in（）里面的单个id 的长度，及in 的数量，或 in 对应的数据量大 等属性都会影响到MySQL选择索引吧<br /> 选择走了ottu.task_id = 48S<br /><br />强制走 ottu.teacher_id 后 rows扫描的行数多了，但时间缩短到 0.7s 且 filtered 率高了。<br />最后选择的方案是 强制走 teacher _id 索引，同时减少id数到50个
4. **数据量大，导致全表扫描**
```sql
select count(*) as aggregate from `ol_todo_task` where `todo_id` in ('98', '175', '186', '281', '468')and `start_time` < '2022-03-08 11:00:42' and `complete` = '1' and `ol_todo_task`.`delete_time` is null
// 全表 400w 数据 ,有todo_id 索引 和 start_time 索引
- in(‘98’, ‘175’, ‘186’, ‘281’, ‘468’)的数据量大达130w+ 占全表30%+ ，走todo_id 会导致大量回表。
MySQL 评估成本大从而选择全表扫描。 - start_time < ‘2022-03-08 11:00:42’ 的范围太大了。不过可能是业务需要
左连接的驱动表过滤条件不足
SELECT count( DISTINCT teacher_id ) AS studyNum, tca.course_id as courseId FROM ol_train_course_annex_record AS tcar LEFT JOIN ol_train_course_annex AS tca ON ( tca.`annex_id` = tcar.`annex_id` OR `tca`.`annex_id2` = `tcar`.`annex_id` ) WHERE tca.is_deleted = 1 AND tca.course_id in (853,762,763,775,773,588,679,677,437,438,480,481,526,527,586) AND tcar.annex_id GROUP BY tca.course_id
where 条件中缺少 驱动表 tcar 的过滤条件，导致扫描驱动表的annex_id 索引树, 扫描的行数rows 高达60w。
改动的思路：把该SQL拆成两条SQL，先从被驱动表中查出annex_id ，在驱动表中 in() 一下，拿出数据再到内存中去组合成想要的结果集。时间跨度大 或 没有限制时间范围
这类慢查询的占比还是比较大的，不过在开发过程中注意下应该是可以避免的的
区分度小的一定不适合建立索引吗
像 sex 字段，一般来说有男女两种值。如果两种值对应的数据体量相差不达，那确实是不适合建索引。
那这个场景呢？ 经常要在女儿国中找个男的，对sex建立索引合适吗？
若有收获，就点个赞吧
0 人点赞
