【开营第六课】【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 混着用，小业务说没问题，是吧，不这么讲究，到了一定尺寸、一定体量，还是要分开的。
7. 因为业务需求，不能使用自增主键，不得不使用还是 UUID 当作主键的时候，怎么操作会更高效一些？
A：你要用 UUID 也没问题。用 UUID，只要对 inset 这种操作，我加载数据，没有太高的要求。比如说，我们之前碰到的这个客户，它是对 insert，就是我加载数据，往里面插入数据，有很高的要求，对数据写入的 rt 是有很高的要求的，一个是要保证，每秒钟写入多少数据，因为他有很多的采集点，要保证把这些数据，它要写到这个库里去，否则它跟不上了， 后台数据写入跟不不上，前台数据吐不出，这样的话，会对他造成一个瓶颈。你如果说没问题，我 1s 就写10条，用 UUID 没问题，100ms 写一条数据，还是可以的，但是表不要太多。
8. Drds 百亿大表如果做查询优化？
A：百亿大表，做 ddl 管理是很困难的，而且你的有足够大的内存，还有你索引的效率可能会下降，所以一个表不要弄得太大。百亿肯定是要拆表了，一般情况下，你考虑这个拆表，里头是有建议值的，一般的 mysql 单实例，这个表最理想的尺寸是存，一千万数据量，五个 GB 数据尺寸。五千万也可以，六千万也可以，十个 GB 一张表，没什么太大问题，那个是理想经验值。当然你说我到了 百亿，这个表的尺寸，绝对不能说是 十几个GB，二十个GB，除非你里头插的都是 int，都是整形值，这是不太可能的。那你到百亿了，你那个表可能得几百个GB，你到这个尺寸了，即便 mysql 最大的 rds 实例，470 GB的内存，承 70%，你放这一张表也不可能，它还有二级索引，这表上肯定还会有二级索引呢，所以还是要拆，而且这张表上是不是这些数据吗，全是我业务当前正在用的，从业务角度看一眼。
9. 虚拟列设置为不持久化，请问数据结果存储在内存中吗？
A：如果是的话，那重启数据库还需要重新计算虚拟列结果到内存中，是吗？没有太多的研究，5.8，8.0 新出的这个。
10. 关于索引字段，是说 varchar 100 是要比 varchar 250 好很多是吗？所以关于索引字段的类型及长度应该更准确？
A：这个 varchar 的问题，实际上它要跟你的字符串实体来呀，最好的情况下是不要把它弄得太长，不是说 varchar 100 一定比 varchar 250 好很多，不要弄得太长。
11. 请问高可用和主从同步一致性，您一般采用什么架构呢？
A：首先说一点，主备实例之间不是同步复制，同步复制什么意思，就是主实例、备实例，做事务 commit 提交的时候，我要保证两遍的事务日志都要同步落盘，mysql 没有干这种事情的，oracle 有，但是 o 真正做这种datagate 也好，三个模式里面选择一个最安全的，数据安全的，这个不可能的，因为你走网络的这样，主库被备库拖累是很严重的，严重影响你的 rt。而且网络波动，大家要有一个概念：网络是不可靠的，网络协议为什么这么复杂，都是处理二般情况的，一般情况都很好处理。所以说这个网络波动，所以是没有用同步复制的，都是异步复制，或者是半同步复制。那这种中间肯定是会出现这种...。数据一致性和高可用之间是有一个矛盾关系，只能是我在这种场景，或者技术条件下，我有一种妥协。我们这边的架构，实际上都是主备复制，咱们的 rds 默认都是半同步复制，大家如果再公共云上，开一个 rds ，如果不做任何操作的话，默认，不能选高性能模版，选那个最基本的性能模版的话，出来的都是半同步复制。半同步复制，是要求主实例事务提交的时候，就commit的时候，也许是自动，也许是手动 commit，也就是隐式/显式 commit 的时候，备库是要能收到主库对这个事务，所有的事务信息是要发过来的，发过来的情况下，我不落盘，但是我要收全了，也就是我的 receive buffer 要收到，并且我的 receive buffer 要通知到 mysql 的，mysql 要认为我这个事务收全了，mysql 要读到结束的标志位了，这个时候它会把这个，返回一个 ack 给主实例，主实例收到这个 ack 之后，主实例才会把这个 commit 提交成功，返回给应用。意思是说，在主实例上，你是看到事务提交成功，那么表示至少有一个备实例，它拿到了主实例上面的事务信息的。
12. Mysql 服务每次重启都特别慢，mysql 进程一直在读数据，3306 进程要两个小时左右才能起来，库里共 1T 左右数据，这种情况是什么原因导致的？
A：重启特别慢，实际上跟踪下，看它到底在干什么。有可能，是不是你的 innodb buffer pool 给 dump 到磁盘上了，启动的时候，是要这个加载回去的，实际上你也跟踪一下，看它在干什么，mysqld 启两个小时才能起起来，实在是有点时间太长了，最好是做一下跟踪，看一下他到底是在干什么，就你 debug 一下。
13. 请问主从复制时，从库开启多线程复制时，如何保证从库应用主库 binlog 的执行顺序，例如：A事务创建表 T，B 事务向表 T 中插入数据 1，两个事务的 binlog 同时复制到从库上，此时多线程会不会先应用 B 事务？是如何保证先应用 A 的？逻辑时钟的执行原理是咋样的呢？Mysql 复制？多线程复制，它本身是表级别的，就是我按照表级别的，我一个表一个线程，他是这样，mysql 官方最开始是按照库级别的，后来改成表级别，我们这边一开始全都是按照表级别的。
14. 表分区可以作为查询优化的方向吗？
A：分区表作为查询优化的方向，分区表，它有一个最大的问题，就是分几个方面。第一方面，它是对主键是有要求的，主键和分区键，是需要都定义在分区键里的，我的分区键都是要包含主键的，或者是要包含一个唯一键也可以，这是一个问题，就是我在做这个分区设计的时候很非常不方便，就是我要把这个主键放进去，这是第一件事；第二件事呢，就是它本身，mysql 的分区设计比 oracle 的灵活性还是要差一些的，差一些差在哪里呢，始载于它没有那么多的这种排列组合，oracle 支持 range list、list range、range hash、hash range，反正怎么折腾都行，排列组合都是可以的，mysql 还是有些限制的；它最大的一个问题是在于，分区表下面的这个数据文件是有多个的，每一个分区实际对应的是一个数据文件，innodb 的一个文件，但是它的这个锁只有一个吧，就是我的这个 mdl 锁，就我做 ddl 的时候，就是我做 ddl 的时候，首先要拿到 mdl 锁，我的 mdl 锁只有一把，就是我如果一旦对这个表做 ddl，做管理操作，那这个表下面所有的分区都会受影响，也就是说，对数据库管理员非常不友好，这个是最大的问题，从业务角度来说还好，因为业务本身对 ddl 不是很敏感，不会经常去跑做那些 ddl 的，如果说我管理员愿意麻烦一点，那么也ok。如果分区规则满足业务的需求，你使用分区也没什么问题，对管理员，对 dba 非常不太友好，通常情况下，我们不太建议用。尤其是什么情况，咱们有的同学做业务设计的时候，喜欢规划三年，规划三年的时候，他按分区的时候，他可能不是按照月分区，而是按照天分区，然后搞得好多表会出一堆特别碎的文件，一张表下面可能会堆好多好多的文件，它每个分区都会做出一个文件，你这种情况下呢，他这个文件会非常多，首先第一个你会吃这个文件描述符，这是一件事；就是 mysql 在文件特别多的时候是有问题的，文件特别特别多，什么情况，就是几十万这种场景，rds 说一个值，rds 超过 40W 数据文件的话，这是没法做备份的，做备份会很困难，基本上做不了，超过 40W，我们的建议值，就是你表的总数量保证在 6/7W 以下，否则对性能是有影响的，所以即便做分区，不要说我规划地非常远，搞得下面几十W 个文件都是分区文件。
15. Optimize table，结果表大了很多，这是为什么？
A：如果确实是这种情况的话，首先第一件事情，把 optimize table 改成 alter table，然后要去加参数的，把 algorithm 从 inplace 改成 copy。改成 copy 的话，是强制要复制表的，但是注意如果你改成 copy 的情况下，你的表是被锁了的，不能写只能读，这种情况下，它是强制复制表。一般情况下，如果你那个表里面真的有很多空洞，有很多空间浪费的，它是能收缩回来的，它那个后台会变成：创建一张新表，把数据倒腾过去，它会变成这种算法，但是它是锁表的。一般情况下，我们不太喜欢用 optimize table，我们都会用 alter table，后边加上 engine = innodb ，这样加参数的，algorithm 控制它的方式，lock 控制它加不加锁，是要用这种方式的。5.8.8.0 的这个版本会好很多，好多事情都是不用这个再去改这个表结构的，它事实上改一下元数据就好，而且有些操作，它是支持新的这个 instant 的，5.8.0 以下 alter table 只支持 copy 和 inplace 这两种方式，5.8 支持 instant，好多操作如果做 instant，会省事很多。技术在发展，我建议大家上来，不要直接做 optimize table这个命令。大家可以看一下，在 innodb 里面，optimize table 等于两条命令的，变成 alter table 表名 engine = table，它先做一件这件事；第二件事它做一个 analyze table，它变成这两件事情。但是它做那个 alter table 是不加参数的，不加的情况下，会有一个问题，就是它有可能会偷偷摸摸地帮你降级，如果你这个表这个操作不满足 inplace 和 lock = none，它是不会报错的，它直接帮你降级，所以有可能会锁表，而且用 inplace 这种方式，它有可能出来之后，表的空间收缩是不明显的，因为它实际上是一个偷懒迂回的策略。如果你为了收缩空间，用 copy 这种方式会是比较好的，但是注意 copy 是要锁表。我们建议还是用 dms，或者是其他的工具，比如像 gost，也有同学喜欢用 percona 的 online schema change，那个也可以，但那个似乎是有点慢，它那个实际是基于 trigger 的，对外键实际上是有特殊考虑的，gost 也是对外键有特殊考虑的。
16. 20亿条数据的表，加字段，大致会加一个星期左右，加字段会导致表不可用，怎么解决？
A：几种方法：一种方法，新买一个实例，新买一个 rds 实例，拿 dts 把数据同步过去，在 dts 的同步的时候，你在那张表单独做一个任务，然后把这个表做个映射，那边新建一个表做个映射，这种最后做切换，这是一种方式，这种情况不影响原实例的业务，如果再做实例上做 ddl 操作，做一个星期，这个是很危险的事情，因为如果你做原生的 ddl，它是有两次缩表的，在最开始和结束的时候，它那个 dml 锁是有两次锁升级的，拿到这个互斥的；当然在 8.0 这个加字段，就不是什么太大的问题了。
17. 硬链接删表是怎么操作的呢？
A：这是日常做这种线下的库吧，在 Rds 是没有这种硬连接删表的，他怎么做的呢。我们会发现一个问题，在 rds，这种大表，做 truncate 或者 drop 的时候，truncate 实际上也是先 drop 掉，再create，这种是要把内存是要扫一遍的，然后本身对 io 有是有比较大冲击，所以我们的 rds 是做了优化，她是对这种大表是做异步的，我对大表做 truncate 还是 drop，都是后台在删除的，这个对前面没有什么太大影响。大家如果线下，如果硬链接，实际上是文件句柄，有多个文件句柄对这一个数据，只要文件句柄还剩下一个，那么这个文件就还存在，说白了就是偷梁换柱。
18. 数据少不需要加索引，那什么时候加（数据越多增加索引代价就越大）？
A：索引这事，首先要看查询，大家优化的时候，首先要关注慢查询，mysql 为什么要把这个慢查询首先一个单独的日志，这个慢查询实际上是很重要的，大家日常是要去扫一扫慢查询的，慢查询里面大家要去关注什么。执行频次非常频繁，扫数据量多，执行时间长，这三个条件来选。而且它是这种这种一层一层的，就是割韭菜一样，割掉一批再出来一批。你先把这个 mysql long query time 参数默认是 1s，先找这种 1s 以上的，性能特别不好的。先改成 3s 或者 5s，先把这些慢查询干掉，干掉一批，优化完了之后，再找 1s 以上的，处理掉，再找 0.5s 的，不要一上来把 long query time，long query time 设置得非常低的话，会发现慢日志里面的条数会非常多，人的处理能力实际上是很有限的，一次应付个十几条，一下子出来 3w 多条慢日志，在丛里头摘可就费了劲了，这就稻草里头长根针，别干这种事情，即便是 CPU 打满了，如果抓不到现场，你要去看慢日志的话，也是这种方法。好多这种同学，我们也碰上好多项目，也是一上来就是 0.1、0.03，定着这个值。出事的时候，生成 36GB 的慢日志，你说这个慢日志怎么分析。数据少的时候不要加索引，表里头几百条，上千条，几千条，这种是没必要加索引的，到了十几万的时候，你是要考虑加索引的给表里头的数据，主要是要看看这个表是不是经常用，如果是像配置表，里头三千行数据，这个配置表加不加索引，如果只是个配置表，它使用的频率是比较低。我举个例子，酒店的业务，会员级别表本身使用频率是非常低的，在内存里头就保留很少一块，你给它加索引不加索引没什么意义，会员级别也就就十来个，表里头就十几行数据。
19. Where 条件中 like 双边都有 % 号，怎么优化走索引，场景是文本匹配？
A：首先你看查询条件后面，insert / delete 都一样的，首先去看 where 条件以后的条件，去看 where 条件子句，根据这个子句，去考虑建立什么样的索引。Like 双 %，有几种情况，但是最好的方法是，把它放到 es（elastic search）里头，或者 adb 里头，我们的 adb for mysql，这种模糊查询最好是放到那边去处理，这叫术业有专攻。真的靠数据库双 %，有在特别特殊的场景下，你 force index，让它走like 对应的字段，全索引扫描，有可能性能会比优化之前会好，这个是很特别的场景，是有强烈的限制条件的。我们不能说把这个作为通用的方法，我们之前优化过，它那个场景没办法走 es ，页来不及放到 adb，没有搭建 es，查询还非得是加 %走，我看它那个表，表里头数据是比较特殊的，它里头可选的值并不是很多，这是让它去 force index，那个 index 会比较小，扫 index 全扫描一遍，速度反而会比它扫描全表要快，它这个是非常特殊的场景，如果是文本匹配，你要看看你是不是要做分词，做全文检索，全文检索必须要用全文检索的引擎，还是用 like %，它出来的结果其实是不一样的。
20. 为什么全文索引中，无论任何查询条件都会 explain 出使用索引？比如 id + age + 那么，建立一个三列的索引，即便单独使用 name 的等值查询，也会 explain 出索引？我建议大家不要考虑在 innodb 里面做全文索引，它对 ddl 是有影响的。在 8.0 以前，5.7、5.6 这两个版本，如果你这个 innodb 表建过全文索引，全文索引即便已经删除了，后边你在做 ddl 操作的时候，好多 ddl 是不能用 inplace 这种操作去做的，只能用 copy 去做，这是 mysql 的限制，所以不要在 mysql 里面做全文检索，不太好用。
21. 达到什么条件需要进行分表？
A：数据量大了，查询慢了，索引的效率下降。或者是你管理上不好管理，那你就考虑要分了，最好是分某个数据。
22. 建议在生产大规模使用 mysql 8.0 MGR 架构吗？有无普通主从复制稳定？
A：Mysql 8.0 mgr，我们是阿里云，我们只能推荐我们自己的产品，关于 mgr 这个架构，就不太多说，不太深入的说。
23. 对亿级大表的查询（字段很少 5 ～ 10）每次 explain 执行计划总是变，我们是靠 force index（时间列二级索引）来解决的，从原理上，这种情况怎么回事？
A：执行计划变这种事情，不光是 mysql 里面出，oracle、db2、pg 也出，只要是这种 cbo（cost-based optimizer） 模型的，肯定会出，大家说这种事很好解，大家到 rds 上，rds 都是有绑定执行计划，有绑定执行计划的 profile，上官网看看 rds 的新特性，新特性里面都有，不用去改 sql，加force index，hint了，因为你改 sql 的话，是要改业务的，我们这边直接绑定执行计划的，绑定就行了。
24. 单表列数 10 列左右比较合理是吗？刚才没有听清？
A：单表列数，经验值是 50 列以内，10 列太少了，50 列就好了，当然这只是经验值，供大家参考。
25. Drds 5.1.28 分库分表 128 分库，整个逻辑表 70 亿数据，4T 大小，这种情况下增加字段有什么建议？
A：Drds 加字段，建议大家，第一个 drds 升级到最新的一个版本，最新的版本 ddl 管理方式和之前是不一样，新版本的 ddl 会比之前有很多增强；第二点是 drds 上加多 ddl 这种事情，一定要对 rds 有一个监控，因为下面可能挂了，我印象中挂了最多，就是 1 个 drds 后面挂了 256个rds 实例，就是你做一个 ddl，分发到每一个 rds 上，每个实例都要跑的，所以说这种多了什么情况都会有，mdl 锁的问题可能是最严重，或者说性能的问题会碰到比较多，所以 ddl 操作一定要在业务低峰期，同时有个监控去看一下。
