我有一张包含3800万行条目的表。为了使最终的查询更快,我想在一些列上创建索引。
在一个列上创建索引时,它运行大约2小时,然后显示:

Lost connection to MySQL server during query

但是,如果我重新启动mySql工作台,它会在那里显示我的新索引。我有两个问题:
1)创建的新索引是完整索引还是不完整/无效索引?
2)如何解决失去连接的问题?
从Edit>Preferences>SQL Editor:我已经将DBMS connection read-out和其他两个值更改为一个大值。但那没用。

最佳答案

索引创建是一个原子操作,从某种意义上说,它要么完全成功,要么完全失败。。。所以如果你有一个索引,它将是完整的。
您断开连接的原因很可能是网络—在您和服务器之间的网络路径中,至少有一个设备(如防火墙或NAT路由器,或者如果这是基于云的服务器,则它可能是云提供商基础结构中的一个设备)维护活动TCP会话的TCP流状态表,在一段时间内没有数据传输的情况下,连接将从设备的内存中清除,因此连接将崩溃。
MySQL客户机/服务器协议没有第7层保持活动机制来保持网络上的空闲连接打开。。。从网络的角度来看,在索引创建操作期间,连接完全处于空闲状态。
可以更改客户端和/或服务器上的内核级参数,以便在靠近第4层的地方交换一些保持活动状态的消息,从而将连接保持在较低的级别,但这是特定于系统的(Linux example)。
通常,在添加索引的同时,只对连接禁用外键检查,也可以大大加快MySQL上的索引创建速度。除非您确信索引操作不会危及任何数据完整性,否则不要执行此操作(即,除非您没有添加外键,否则不要使用此操作)。

mysql> SET @@FOREIGN_KEY_CHECKS = 0;
mysql> ALTER TABLE ADD KEY ...;
mysql> SET @@FOREIGN_KEY_CHECKS = 1;

还请注意,如果使用工作台的GUI添加索引,而不是实际键入SQL语句来更改表。。。别那么做。为DDL使用图形化工具会增加浪费时间的可能性,因为它们有时会生成实现预期目的的语句,但却以非常低效、有时不合逻辑的方式执行。
在许多情况下,您也可以使用:
mysql> ALTER TABLE ALGORITHM=INPLACE, LOCK=NONE, ADD KEY ...;

这些选项通过避免不必要的锁定和按原样将索引附加到表而不是复制表来加快索引操作。如果服务器不喜欢您正在执行的特定操作的这些选项,它会告诉您,但会出现错误,不会造成任何损害。在ALGORITHMLOCK选项之前,有时需要禁用外键检查,并在完成后启用它们。
值得重复:关闭外键检查(如上所示)只会影响一个连接——您的连接——而不会影响任何其他连接。如果其他用户或甚至您从另一个连接访问同一个表,则这不会禁用对正在更改的表的检查。只要在禁用外键引用时不做任何干扰外键引用的操作,此设置不会危害数据完整性。这是一种众所周知的常用优化方法。在添加索引时不需要检查,但在某些情况下服务器将尝试不必要地验证现有数据。

关于mysql - 创建索引时在查询过程中失去与mysql服务器的连接,但此后仍然获取索引,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/48040789/

10-16 19:30
查看更多