我试图在我的表上创建一个复合索引，因为下面的表给了我性能问题，它有一个字符编码为ut8mb4，我得到以下错误
Mysql2::Error: Specified key was too long
整个错误如下:-
== 20161103114941 AddIndexOnKeyAndIdOnInvitees: migrating - Shard: master =====
-- add_index(:invitees, [:key, :created_at])
rake aborted!
StandardError: An error has occurred, all later migrations canceled:
Mysql2::Error: Specified key was too long; max key length is 767 bytes: CREATE INDEX `index_invitees_on_key_and_created_at` ON `invitees` (`key`, `created_at`)
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `query'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `block in execute'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `block in log'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activesupport-4.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/abstract_adapter.rb:15:in `instrument'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:467:in `log'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `execute'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:231:in `execute'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:529:in `add_index'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:662:in `block in method_missing'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:632:in `block in say_with_time'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:632:in `say_with_time'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:652:in `method_missing'
/var/www/production_hobnob_copy/db/migrate/20161103114941_add_index_on_key_and_id_on_invitees.rb:3:in `change'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:606:in `exec_migration'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:590:in `block (2 levels) in migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:589:in `block in migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:588:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:765:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:995:in `block in execute_migration_in_transaction'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:1043:in `ddl_transaction'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:994:in `execute_migration_in_transaction'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:956:in `block in migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:952:in `each'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:952:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:86:in `migrate_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:820:in `up'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:113:in `block in up_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:246:in `block (2 levels) in run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:509:in `using_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:245:in `block in run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:492:in `keeping_connection_proxy'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:244:in `run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:253:in `block in send_queries_to_multiple_shards'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:252:in `map'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:252:in `send_queries_to_multiple_shards'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:112:in `up_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/migration.rb:798:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:104:in `block in migrate_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:246:in `block (2 levels) in run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:509:in `using_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:245:in `block in run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:492:in `keeping_connection_proxy'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:244:in `run_queries_on_shard'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:253:in `block in send_queries_to_multiple_shards'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:252:in `map'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/proxy.rb:252:in `send_queries_to_multiple_shards'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/ar-octopus-0.8.6/lib/octopus/migration.rb:103:in `migrate_with_octopus'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/tasks/database_tasks.rb:137:in `migrate'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/gems/activerecord-4.2.1/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top (required)>'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/bin/ruby_executable_hooks:15:in `eval'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p648/bin/ruby_executable_hooks:15:in `<main>'
Tasks: TOP => db:migrate
我在这个论坛上浏览了以下帖子:-
Rails创建schema_migrations - Mysql2::错误:指定的键太长
和给出的解决方案是将字符编码转换为utf8，这在我的情况下是不可能的。有没有办法解决这个问题?
任何建议或帮助都会很有帮助。
Thanks to lot
在旧版本中，索引被限制为767字节。这对VARCHAR(255) CHARACTER SET utf8来说足够了，它很流行。
VARCHAR(255) CHARACTER SET utf8mb4需要更多。较新的版本已经提高了限制，所以它可以工作。
解决方案:
- 升级mysql。
- 减少255——查看你的数据并使用一个合理的数字，而不是过度使用的255。
- 获取5.6.3/5.5.14(或更高版本)和…
说明(5.7.7及以后版本不需要)
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC;
也许你可以索引(255)utf8mb4。
"key too long"只会间接影响"性能"，因为你不能添加索引。