【七天深入MySQL实战营】答疑汇总Day6 MySQL表和索引优化实战

【开营第六课】【MySQL表和索引优化实战】
讲师：田杰，阿里云高级运维专家。
课程内容：InnoDB表和索引设计最佳实践；索引设计的分析与优化。
答疑汇总：特别感谢班委@李敏 同学

https://help.aliyun.com/document_detail/129925.html  大家有时间建议看一下这篇文档，很多实用的功能，无论是云上还是云下都是比较好的解决问题思路参考

1. Mysql 有没有类似 oracle 的快速单表恢复？
A：rds / polar for mysql 都是有的

2. 在什么场景下，适合建 hash 索引？
A：5.7之下，包括 5.8 初期都是没有 hash join，5.8 最新有没有记不得太清楚了，pg 都是有 hash join 的。hash join 适合用在长字符串的等值比较，不能是 like、match、against，不能是模糊查询，也不能是全文索引，只能是等值比较；而且是长字符串，什么叫长字符串，像 abc 这种长度就没必要，五个六个七个八个没必要，可能适合在像 二三十个，三四十个，这种长度的字符串，我说的是字符串，字符串做比较，每个字符，像 utf8 是三个字节来表示，utf8amb 就是四个字节，这种长字符串的等值比较，适合用 hash join，只能做等值，因为本质是要做 hash 值比较，不能支持范围，比如说短字符串，十以内，十五以内，这种长度的比较，其实也是没必要的。在 pg 里面也是比较少用 hash join 的。

3. 运行中生产环境上，myisam 表是否不停机下线直接转换成 innodb，表中有数据，需要注意什么吗？
A：转换后是否还有需要调整的地方吗？rds / polar for mysql 环境下，大家是不可能建成 myisam 表来的，尤其是新购的实例，就我们默认在内核这一块会有一个转换，大家指定 create table engine = myisam，即便写 myisam，也会被替换成 innodb，本身 myisam 的表是创建不起来的，比如说特别老的一些实例，像一些存量的 5.5，可能还能支持 myisam，非常少的 5.5。myisam 表不停机，这种情况，首先得搭建一个复制关系，用 dts 搭建一复制关系，或者自己写 triger，但 trigger 实际上是对事务、业务是有侵入性，我们不太建议，要么就自己搭建一个 dts 的这种复制关系，从 myisam 同步到 innodb，它只读的情况下，对实例的压力还好，搭建一个复制关系，然后等到业务割接的时候做切换。

4. 我公司有张表有两千多万数据，使用的UUID作为主键，没有使用到自增列（这个历史原因），sql 语句：select count(1) cnt from table name where StartDate>=‘2020-11-01’ and StartDate < ‘2020-12-01’ and State=‘C’ and Source=‘Alipay’ 查询大概平均七分钟左右，StartDate，State，Source 都有索引，老师有什么好的优化建议吗？
A：首先这个查询里头，startdate 的两个边界，直接跨度一个月了，state、source 可选的值也不太多，如果真正想解决这个问题，如果这个查询真的运行非常频繁，经常要跑的话。首先第一件事，如果是取count，把它变成每天单独算一下，每天取一个sum值，把它的 count 取出来，然后把业务改造一下，如果我算每个月，我把每天的 count 相加就可以了，做这种事情，因为 select count，不管是 count(1) 还是 count(*) 也好，建议使用 count(*) ，它本身在 innodb 引擎表里面是怎样执行的呢？还是选择最小的索引，它会扫一下，满足这个查询条件，上面最小的索引是谁，然后它会扫这个索引，实际上是全索引扫描；如果没有合适的索引，就会做全表扫描，全表扫描实际上就是扫主键，把主键跑一遍；如果有合适的索引，会做全索引扫描。如果数据量确实比较大，而且时间跨度也比较大，没有什么太好的办法，因为你这个过滤性是比较差的，三个条件加一块，可能过滤性不太好，所以建议是每天出一个 count 值，然后 count 值相加就好了，这个比较好，能比较快的解决问题。如果不行的话，如果业务上不改造的话，建议你做一个组合索引，就把 state、source、startDate，你组合在一起，做一个组合索引，然后看一下 state 和 source ，是哪个改动量比较小，它不经常做 update，像 state 我觉得可能会经常做 update，可以把 source 放在第一个字段，把 state 放在第二个字段，把 startDate 放在第三个字段，做一个组合索引，看看这个组合索引的过滤性怎样，尺寸怎样，如果尺寸不太大，过滤性还比较好，做这么一个组合索引，看一下 count(*) 的执行计划，跑这个索引就好了，就不要再跑 primary key，不要跑原表，但是还是建议，在业务方面该，业务方面在每天做一个 count(*) ，明天算今天的值，最后做加法就好。

5. 单 rds，10亿大表，做优化的思路是怎样的？
A：十亿大表的话就只能拆，如果不用分库分表的话，这种 drds 也好，polar-X 也好，我们 drds 现在已经改名叫 polar-X了。十亿大表你现在只能去拆，有几种方法。第一种方法，考虑冷热数据，十亿大表不可能全部都是热的数据，不可能当前正在跑的业务，这十亿数据都需要加载到内存里头，很有可能不是这样的，那怎么办？拆成两张表，一张冷表，一张热表。保证最频繁查询的，就是最近两周或者一周的数据，这些数据在一块，可能才三千万，五千万的，做成一张表，五千万大小的一张表，性能上一般是不会有多大问题。剩下的我做成冷数据，冷数据要考虑是拆成多张表，还是拆成，如果是拆成多张表的话，每张表的数据会比较平均；如果你要是拆成一张表的话，可能也得有八九亿，操作起来也很痛苦。所以第一个考虑冷数据，第二个按业务角度拆，比如说你的业务是全国的，全国有多少个省，按省的维度拆，我查询经常是按照省的维度查，或者位置也好，时间也好，看按哪个维度拆分。拆的话有两种，第一种做分区，不太建议使用分区表，还有一种是自己去拆，我写一个函数，我的查询每次，都先通过这个函数，把这个表名确定下来，相当于是自己做一下拆表，这是比较好的方法，否则十亿大表，真的是不太好处理。

6. Rds 和 drds，单表的列数多少合适？
A：单表的列数不要太多，50 个左右是比较合适的。大家知道 tp（transaction process）类型的业务，本身是短平快的：查询简单，业务逻辑简单，然后快速地执行，对 rt（response time）要求是很敏感的。比如业务的一个动作，可能会有 5/6 sql 组合在一起，如果每个 sql  执行的时间，rt 很长，组合在一起是会有问题的。我们之前和某家银行，它做这个业务逻辑，它做一个登陆操作，要做 15 个sql，才能完成一个登陆操作，这样的情况下，要求 rt...。因为登陆操作对 rt 很敏感的，我手机登录也好，网页也好，你登陆的时候登陆不上去，对用户体验来说是一个致命的硬伤，那你如果想控制 rt 的话，就要保证它这个里面的动作简洁有效。像 tp 类型的业务，短平快，所以你这个表设计不要太复杂，弄一个大宽表，大宽表一般是 ap（analysis process）类型的业务，比如出报表，做分析，挖掘数据，做预测，是吧。是一定要考虑你的 ap 和 tp 是要分开的，不能说 tp 和ap 混着