Ruby- Mysql2编码utf8mb4创建索引问题（因此影响性能）

debugcn 发表于 Dev

加德满都

我正在尝试在我的表上创建一个复合索引，因为下表给了我性能问题，并且它的字符编码为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 :: Error：指定的密钥太长

给出的解决方案是将字符编码转换为utf8，在我的情况下是不可能的。有没有解决的办法。

任何建议或帮助都将非常有帮助。

非常感谢

里克·詹姆斯

在旧版本中，索引限制为767个字节。这是足够的空间VARCHAR(255) CHARACTER SET utf8，很受欢迎。

VARCHAR(255) CHARACTER SET utf8mb4需要的还不止这些。较新的版本提高了限制，因此可以使用。

解决方案：

升级mysql。
减少255-查看您的数据并使用一个有意义的数字，而不是过度使用的255。如果该数字小于191，则索引将起作用。
获取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。

“密钥过长”只会间接影响“性能”，因为您无法添加索引。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。