在新的MySQL8.0.23中，引入了一个有趣的新特性：不可见列。这是关于此新功能的第一篇文章，我希望写一个由3部分组成的系列文章。这是前言。在MySQL8.0.23之前，表中的所有列都是可见的（如果您有权限）。现在可以指定一个不可见的列，它将在查询中隐藏。如果明确引用，可以查找它。让我们看看它的样子：createtabletable1(idintauto_incrementprimarykey,namevarchar(20),ageintinvisible);在表结构中我们可以看到Extra列中的INVISIBLE关键字：desctable1;+--------+-----------+------+-----+--------+----------------+|Field|Type|Null|Key|Default|Extra|+-------+------------+------+-----+--------+---------------+|id|int|NO|PRI|NULL|auto_increment||name|varchar(20)|YES||NULL|||age|int|YES||NULL|INVISIBLE|+-------+------------+------+-----+--------+----------------+查看showcreatetable语句并注意一个区别，当我创建表时，我想查看INVISIBLE关键字，但它不是：showcreatetabletable1\\G***************************1.row*************************表：table1CreateTable：CREATETABLE`table1`（idintNOTNULLAUTO_INCREMENT，namevarchar（20）DEFAULTNULL，ageintDEFAULTNULL/*！80023INVISIBLE*/，PRIMARYKEY（id））ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci但是我确认这个语句会在创建表的时候把年龄列设置为不可见。所以我们有2种不同的语法来创建不可见的列。在INFORMATION_SCHEMA中也可以看到相关信息：SELECTTABLE_NAME,COLUMN_NAME,EXTRAFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_SCHEMA='test'ANDTABLE_NAME='table1';+------------+---------------+------------+|TABLE_NAME|COLUMN_NAME|EXTRA|+------------+-------------+----------------+|table1|id|auto_increment||table1|name|||table1|age|INVISIBLE|+------------+------------+----------------+插入一些数据，继续观察：insertintotable1values(0,'mysql',25),(0,'kenny',35),(0,'levred','44');ERROR:1136:Columncountdoesn'tmatchvaluecountatrow1不出所料，如果我们不在插入语句中引用它，就会报错被举报。引用这些列：insertintotable1(id,name,age)values(0,'mysql',25),(0,'kenny',35),(0,'levred','44');QueryOK,3rowsaffected(0.1573sec查询表数据：select*fromtable1;+----+--------+|id|name|+----+------+|1|mysql||2|kenny||3|lefred|+----+--------+同样，正如预期的那样，我们看到不可见的列没有显示。如果我们指定它：selectname,agefromtable1;+--------+-----+|name|age|+------+-----+|mysql|25||kenny|35||lefred|44|+--------+-----+当然我们可以将列从可见变为不可见或从不可见变为可见：altertable1modifynamevarchar(20)invisible,modifyageintegervisible;QueryOK，0rowsaffected(0.1934sec)select*fromtable1;+----+-----+|id|age|+----+-----+|1|25||2|35||3|44|+--+-----+我对这个新特性非常满意，在下一篇文章中我们将看到为什么这对InnoDB函数很重要。本文是与MySQL不可见列相关的系列文章的第二部分。这篇文章描述了为什么不可见列对InnoDB存储引擎很重要。首先，让我简要解释一下InnoDB如何处理主键以及为什么一个好的主键很重要。最后，为什么主键也很重要。InnoDB是如何存储数据的？InnoDB将数据存储在表空间中。这些记录使用聚簇索引（主键）存储和排序：它们被称为索引组织表。所有二级索引也将主键作为索引中最右边的列（即使它没有暴露）。这意味着当使用二级索引检索记录时，会使用两个索引：二级索引指向用于最终检索该记录的主键。主键影响随机I/O和顺序I/O之间的比率以及二级索引的大小。随机主键还是顺序主键？如上所述，数据存储在聚集索引内的表空间中。这意味着如果不使用顺序索引，InnoDB在执行插入时必须重新平衡表空间中的所有页面。如果我们用InnoDBRuby来说明这个过程，下图展示了当使用随机字符串作为主键插入记录时，表空间是如何更新的：每次插入时，几乎所有的页面都会被触及。当使用自增类型作为主键时，同样的插入：在自增主键的情况下，只会触及第一页和最后一页。让我们用一个高级示例来解释这一点：假设一个InnoDB页面可以存储4条记录（免责声明：这只是一个虚构的示例），我们插入一些带有随机主键的记录：插入带有主键AA的新记录！修改所有页面以“重新平衡”聚集索引，如果是连续的主键，则只会修改最后一页。想象一下当发生数千次插入时所完成的额外工作。这意味着选择一个好的主键很重要。需要注意两点：主键必须是连续的。主键必须短。UUID呢？我通常建议对主键使用自动递增整数（或bigints），但不要忘记监控它们！但是我也了解到越来越多的开发者喜欢使用uuid。如果你打算使用UUID，你应该阅读MySQL8.0中的UUID支持，本文推荐你使用binary(16)来存储UUID。如：CREATETABLEt(idbinary(16)PRIMARYKEY);INSERTINTOtVALUES(UUID_TO_BIN(UUID()));不过，我并不完全认同这个观点，为什么呢？因为使用uuid_to_bin()可能会改变MySQL的UUID实现的顺序行为（更多信息请参见额外部分）。但是如果需要UUID，就需要在大索引上花费一定的代价，索引不要在不需要的二级索引上浪费存储和内存：select*fromsys.schema_unused_indexeswhereobject_schemanotin('performance_schema','mysql');没有任何主键？对于InnoDB表，当没有定义主键时，使用第一个唯一的非空列。如果没有可用的列，InnoDB会创建一个隐藏的主键（6位数字）。这种类型的主键的问题是你无法控制它，更糟糕的是，这个值对于所有没有主键的表都是全局的，如果你对这些表进行多次写入，就会产生争用问题同时（dict_sys->mutex）。对不可见列有用有了新的不可见列，如果应用程序不允许添加新列，我们现在可以向没有主键的表添加合适的主键。首先找到这些表：SELECTtables.table_schema,tables.table_name,tables.engineFROMinformation_schema.tablesLEFTJOIN(SELECTtable_schema,table_nameFROMinformation_schema.statisticsGROUPBYtable_schema,table_name,index_nameHAVINGSUM(casewhennon_unique=uktable0andnullable!='YES'then1else0end)puks.table_schemaANDtables.putullIS.table_name=puks.table_type='BASETABLE'ANDEngine="InnoDB";+------------+---------------+--------+|TABLE_SCHEMA|TABLE_NAME|ENGINE|+------------+------------+--------+|test|table2|InnoDB|+------------+------------+--------+MySQL中也可以使用check插件外壳：https://github.com/levred/mysqlshell-plugins/wiki/check#getinnodbtableswithnopk让我们检查表定义：showcreatetabletable2\\G***************1.row**************Table:table2CreateTable:CREATETABLEtable2(namevarchar(20)DEFAULTNULL,ageintDEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci中的数据：select*fromtable2;+--------+-----+|姓名|年龄|+--------+-----+|mysql|25||kenny|35||lefred|44|+--------+-----+现在添加指定的不可见主键：altertabletable2addcolumidintunsignedauto_incrementprimarykeyinvisiblefirst；插入一条新记录：insertintotable2(name,age)values('PHP',25);select*fromtable2;+--------+-----+|name|age|+--------+-----+|mysql|25||kenny|35||lefred|44||PHP|25|+-------+-----+如果我们想要查看主键：selectid,table2.*fromtable2;+----+--------+-----+|id|name|age|+----+--------+-----+|1|mysql|25||2|kenny|35||3|lefred|44||4|PHP|25|+----+--------+-----+总结现在你知道为什么主键在InnoDB中很重要，为什么一个好的主键更重要。从MySQL8.0.23开始，可以使用不可见列来解决没有主键的表。只是为了好玩，为了说明我对使用UUID_TO_BIN(UUID())作为主键的看法，让我们再次使用UUID作为不可见列重复该示例。altertable2addcolumnidbinary(16)invisiblefirst;altertabletable2modifycolumnidbinary(16)default(UUID_TO_BIN(UUID()))invisible;updatetable2setid=uuid_to_bin(uuid());altertabletable2addprimarykey(id);到目前为止没有什么特别的，只是创建一个不可见的主键需要一些技巧。查询：select*fromtable2;+--------+-----+|name|age|+------+-----+|mysql|25||kenny|35||lefred|44|+--------+-----+现在，我们向该表中插入一个新数据：insertintotable2(name,age)values('PHP',25);select*fromtable2;+--------+-----+|name|age|+--------+-----+|PHP|25||mysql|25||kenny|35||lefred|44|+--------+-----+Mmmm...为什么PHP现在是第一行？因为uuid()不连续...selectbin_to_uuid(id),table2.*fromtable2;+------------------------------------+--------+-----+|bin_to_uuid(id)|name|age|+-----------------------------------+--------+-----+|05aedcbd-5b36-11eb-94c0-c8e0eb374015|PHP|25||af2002e8-5b35-11eb-94c0-c8e0eb374015|mysql|25||af20117a-5b35-11eb-94c0-c8e0eb374015|kenny|35||af201296-5b35-11eb-94c0-c8e0eb374014-lefred+-----------------------------------+--------+------+我们还有其他选择吗？是的，如果我们参考官方文档，我们可以使用uuid_to_bin()函数。altertable2addcolumnidbinary(16)invisiblefirst;altertabletable2modifycolumnidbinary(16)默认(UUID_TO_BIN(UUID(),1))不可见;updatetable2setid=uuid_to_bin(uuid(),1);现在我们每次都插入一条新记录，插入按预期顺序进行：selectbin_to_uuid(id,1),table2.*fromtable2;+------------------------------------+--------+-----+|bin_to_uuid(id,1)|name|age|+------------------------------------+--------+-----+|5b3711eb-023c-e634-94c0-c8e0eb374015|mysql|25||5b3711eb-0439-e634-94c0-c8e0eb374015|kenny|35||5b3711eb-0471-e634-94c0-c8e0eb374015|lefred|44||f9f075f4-5b37-11eb-94c0-c8e05b374015|PHP|811eb-94c0-c8e0eb374015|PHP8|1||9385cc6a-5b38-11eb-94c0-c8e0eb374015|Python|20|+--------------------------------------+--------+-----+我们已经从MySQL8.0.23看到了新的不可见列功能。如果未定义主键，我们如何使用它向InnoDB表添加主键。前面说过，一个好的主键对于InnoDB（存储、IOPS、二级索引、内存等）来说非常重要，但是主键在MySQL中还有一个重要的作用：复制！异步复制当使用“传统复制”时，如果你修改一行记录（更新和删除），副本上要修改的记录将使用索引来标识，当然还有主键（如果有的话）。InnoDB自动生成的隐藏全局6字节主键永远不会被使用，因为它是全局的，因此不能保证源和副本之间是相同的。你根本不应该考虑它。如果算法找不到合适的索引，或者只能找到非唯一索引或包含空值，则需要使用哈希表来识别表记录。该算法创建一个哈希表，其中包含更新或删除操作的记录，并使用键作为行的先前完整图像。然后该算法遍历目标表中的所有记录，如果找到选定的索引，则使用该索引，否则执行全表扫描（参见官方文档）。因此，如果应用程序不支持将附加键用作主键，则使用隐藏列作为主键是一种加快复制速度的方法。mysql>createtablet1(namevarchar(20),ageint);mysql>insertintot1values('mysql',25),('kenny',35),('lefred',44);现在添加一个自增列作为主键：mysql>altertablet1addidintauto_incrementprimarykeyfirst;然后根据应用中指定的INSERT语句添加一条记录：mysql>insertintot1values('python',20);有多少应用程序仍然使用SELECT*并引用像col[2]这样的列？如果是这样，你有两种方法：分析所有查询，使用重写查询插件使用不可见列在这种情况下选择很容易（至少对于像我这样的懒人来说）。mysql>altertablet1modifyidintauto_incrementinvisible;mysql>insertintot1values('python',20);QueryOK,1rowaffected(0.0887sec)很简单，不是吗？GroupReplicationMySQLInnoDBCluster使用另一种复制：GroupReplication。使用组复制的要求之一是要有一个主键（这就是为什么可以使用sql_require_primary_key的原因）。我们使用上面的例子，在不添加主键的情况下，对表进行重构，查看实例是否可以作为InnoDBCluster使用：https://lefred.be/wp-content/uploads/2021/01/Selection_9991017-1024x561。png提示很清楚，表上的Modifications没有复制到其他节点。添加不可见的主键，重新检查：https://lefred.be/wp-content/uploads/2021/01/Selection_9991018-1024x89.pnghttps://lefred.be/wp-content/uploads/2021/01/Selection_9991019-1024x384.png这意味着如果应用程序使用没有主键的表，则不允许迁移到MySQLInnoDBCluster等高可用性架构，这要归功于不可见的列。这也解决了HadoopHive对MySQLInnoDBCluster的支持（参见Hive-17306）。
