您可以在主从服务器上对表进行不同的索引吗（MySQL）
是否可以在只读从站上设置与主站不同的索引？基本上，考虑到两个系统的不同要求，这似乎是有意义的，但我想确保它能够工作并且不会引起任何问题。
Is it possible to set up different indexing on a read only slave, from on the master? Basically, this seems like it makes sense given the different requirements of the two systems, but I want to make sure it will work and not cause any problems.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我相信是这样。复制工作后，您可以删除从属服务器上的索引并创建您想要的索引，这样就可以了。由于 MySQL 复制语句而不是数据（至少默认情况下如此），只要插入、更新或从表中选择所需的 SQL 不需要更改，它就不会注意到。
现在这显然有缺点。如果您创建主服务器上没有的唯一密钥，则可能会在主服务器上插入无法插入从服务器上的数据。如果使用索引完成更新，它可能在主服务器上运行得很快，但会导致从服务器上进行表扫描（因为您没有方便的索引）。
如果主服务器上发生任何 DDL 更改（例如更改索引），该更改将被传递到从服务器，并且新索引也将在那里创建，即使您不希望这样做。
I believe so. After replication is working, you can drop the indexes on the slave and create the indexes you want and that should do it. Since MySQL replicates statements and not data (at least by default), as long as the SQL necessary to insert or update or select from the table doesn't need to change, it shouldn't notice.
Now there are obviously downsides to this. If you make a unique key that isn't on the master, you could get data inserted on the master that can't be inserted on the slave. If an update is done that uses an index it may run fast on the master but cause a table scan on the slave (since you don't have whatever index was handy).
And if any DDL changes ever happen on the master (such as to alter an index) that will be passed to the slave and the new index will be created there as well, even though you don't want it to.
一定。我一直这样做。我遇到的问题：
SELECTS中通过FORCE/USE/IGNORE INDEX引用索引会出错ALTER语句中引用索引可以中断复制UNIQUE索引，则任何 < code>INSERT... ON DUPLICATE KEY、INSERT IGNORE或REPLACE语句将导致极端的数据漂移/发散For sure. I do it all the time. Issues I've run into:
FORCE/USE/IGNORE INDEXinSELECTSwill error outALTERstatments on the master can break replicationUNIQUEindexes, anyINSERT... ON DUPLICATE KEY,INSERT IGNOREorREPLACEstatments will cause extreme data drifting / divergence当然，我认为将 InnoDB 表复制到从属服务器上的 MyISAM 表中甚至是一种常见的做法，以便能够添加全文索引。
Sure, I think it's even a common practice to replicate InnoDB tables into MyISAM tables on the slave to be able to add full-text indexes.