MySQL数据克隆的用户权限设计及MySQL表上做查询慢出现状况[图]

一、MySQL数据克隆的用户权限设计
最近完成了初版的数据逻辑备份恢复的功能，和业务方做了一些交流和演示，发现他们对于备份恢复侧的一些东东还是不够敏感，因为因为一些术语的差异，他们不大理解所谓的备份恢复能干什么，所以我们做了一些讨论，最后决定改为“数据克隆”。
1.产品定位：
数据克隆是高效，安全的从通过从线上指定数据库/表克隆数据，从而快速构建虚拟环境，提供更高效的数据交付服务。从效率上可以支持业务自助提取数据，分钟级快速构建环境，可以通过workbench等工具访问数据，整个过程基本不需要DBA手工操作介入。从安全上，支持数据操作日志审计，提供库/表访问过滤，随机虚拟环境和临时密码交付，此外会对虚拟环境使用时长进行限制，尽可能保证数据的使用安全。

2.适用场景：
目前数据克隆功能支持如下的场景：
1）线上配置数据的快速查看
2）提取线上表结构
3）日志数据查询，线上大表
4）线上SQL异常，快速构建虚拟环境进行SQL优化，压测等
5）指定大表的变更和数据操作影响评估
6）数据补丁合并，基于业务逻辑的数据操作和数据补丁整理
整个实现的过程有很多考虑的细节，不过还是在设计和实现中由同事和我一并解决了。
到了交付的时机了，我们想到还有一个关键的地方需要补充，那就是数据库和用户的权限关联，也就意味着每个人可以看到和使用的数据库应该是不大一样的，因为做一些权限隔离，所以接下来我会说说数据克隆方向的用户权限设计。
数据克隆的用户权限设计是面向业务使用的基础功能，目前对于用户权限的设计可以基于数据库级别。
权限的实现可以分两个阶段来完成:
1)数据初始化阶段，可以使用用户组批量初始化的方式，部分数据可以从工单历史中获取
2)定制化配置阶段，根据业务需求变更和组织架构调整进行数据库和用户映射关系的微调
其中，
实例信息和库信息可以基于数据库基线表mysql_db_baseline
用户组信息和用户信息可以基于用户表user_info
数据库-用户关系表需要新建，表名为：mysql_db_user_rel(id,ip_addr,db_port,db_name,user_id)
需要实现四个子功能：
1）数据库-用户关系映射，实现单一数据库和单一用户的关系，在关系表中为一条记录
2）数据库-用户关系列表，通过数据库维度(IP+端口+数据库名）查看相关用户列表信息
3）数据库-用户组关系映射，实现单一数据库和单一用户组的关系，具体逻辑为：
a)输入数据库信息（IP+端口+数据库名）,UserID
b)根据UserID关联用户组信息
c)根据用户组信息得到组内的UserID列表
d)循环用户列表，根据数据库-用户关系建立映射,需要考虑重复记录的过滤
4）实例列表信息，根据用户UserID得到归属的实例列表，具体逻辑为：
a)输入UserID
b)根据UserID在数据库-用户关系表中得到相关的数据库列表，进一步过滤得到实例IP+端口信息。
二、MySQL表上做查询慢出现状况
我们搭建一个MySQL5.7的环境，此处省略搭建步骤。
写个简单的脚本，制造一批带主键和不带主键的表：
执行一下脚本：
现在执行以下SQL看看效果：
执行了16.80s，感觉是非常慢了。
现在用一下DBA三板斧，看看执行计划：
感觉有点惨，由于information_schema.columns是元数据表，没有必要的统计信息。
那我们来showwarnings看看MySQL改写后的SQL：
我们格式化一下SQL：
可以看到MySQL将
selectfromAwhereA.xnotin(selectxfromB)//非关联子查询
转换成了
selectfromAwherenotexists(select1fromBwhereB.x=a.x)//关联子查询
如果我们自己是MySQL，在执行非关联子查询时，可以使用很简单的策略：

selectfromAwhereA.xnotin(selectxfromBwhere...)//非关联子查询:1.扫描B表中的所有记录，找到满足条件的记录，存放在临时表C中，建好索引2.扫描A表中的记录，与临时表C中的记录进行比对，爱国心报国情强国志征文（https://www.isanxia.com/zuowen/30380.html）直接在索引里比对，
而关联子查询就需要循环迭代：selectfromAwherenotexists(select1fromBwhereB.x=a.xand...)//关联子查询扫描A表的每一条记录rA：扫描B表，找到其中的第一条满足rA条件的记录。

显然，关联子查询的扫描成本会高于非关联子查询。
我们希望MySQL能先"缓存"子查询的结果（缓存这一步叫物化，MATERIALIZATION），但MySQL认为不缓存更快，我们就需要给予MySQL一定指导。
可以看到执行时间变成了0.67s。
整理
我们诊断的关键点如下：
1.对于information_schema中的元数据表，执行计划不能提供有效信息。
2.通过查看MySQL改写后的SQL，我们猜测了优化器发生了误判。
3.我们增加了hint，指导MySQL正确进行优化判断。
但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。