[求教]mysql中很多update的慢查询

单独执行需要1/10毫秒级，我压力测试的时候，更是慢的一踏糊涂，怀疑是由于单条较慢，大量并发导致锁时间太长。
客户端用的php的短连接。
另一个奇怪的现象是mysql的最大连接数上不去。请前辈指教表结构：CREATE TABLE `_player_server` (
  `player_uid` varchar(50) NOT NULL,
  `group_id` varchar(20) DEFAULT NULL,
  `chanel_id` varchar(20) DEFAULT NULL,
  `login_st` int(11) DEFAULT NULL,
  `login_ip` varchar(20) DEFAULT NULL,
  `create_time` int(11) DEFAULT NULL,
  `login_time` int(11) DEFAULT NULL,
  `ol_time` int(11) DEFAULT '0',
  `qq` varchar(50) NOT NULL,
  `wb_lvl` int(11) DEFAULT NULL,
  `fcm` int(11) DEFAULT NULL,
  `passmd` varchar(50) DEFAULT NULL,
  `game_logout` int(11) DEFAULT '0',
  `tx_logout` int(11) DEFAULT '0',
  `cumulate_online` int(11) DEFAULT '0',
  `cumulate_offline` int(11) DEFAULT '0',
  `huangzuan` char(5) DEFAULT NULL,
  `lanzuan` char(5) DEFAULT NULL,
  `huiyuan` char(5) DEFAULT NULL,
  PRIMARY KEY (`player_uid`),
  KEY `login_index` (`login_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
慢查询语句(这个是我测试生成的，其中的值都是随机的)：
UPDATE _player_server SET group_id='khUI2EN_T3', chanel_id='yqkfInPJZj', login_st=6526992, login_ip='Y5jMLjpcd0', create_time=1958279, login_time=1875789, ol_time=5709184, qq='VddxcWqAcA', wb_lvl=2, fcm=2, passmd='OljCvYqp83', game_logout=8427927, tx_logout=1187723, cumulate_online=1249840, cumulate_offline=8785359, huangzuan='l8exH', lanzuan='ZALmC', huiyuan='wC3z3' WHERE player_uid = 'puid_4cc90f042f2b50.28881786';我这边show profile信息：
mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000065 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000007 |
| System lock          | 0.000003 |
| Table lock           | 0.000003 |
| init                 | 0.000039 |
| Updating             | 0.000041 |
| end                  | 0.000008 |
| query end            | 0.000003 |
| freeing items        | 0.081482 |
| logging slow query   | 0.000008 |
| cleaning up          | 0.000003 |
+----------------------+----------+

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

数据量多大，EXPLAIN SQL语句，贴结果出来看看
数据量很小，才2000多条update的条件是主键，explain主键查询结果如下
mysql> explain select * from _player_server where player_uid = 'puid_4cc90f042f2b50.28881786';
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table          | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | _player_server | const | PRIMARY       | PRIMARY | 152     | const |    1 |       |
+----+-------------+----------------+-------+---------------+---------+---------+-------+------+-------+
不是SELECT的，是UPDATE的 EXPLAIN
挑出你执行慢的，EXPLAIN出来看看它的执行计划是怎么样的。
update 也能EXPLAIN ？？我试了怎么不行啊，语法错误。
测试了一下，确实只有EXPLAIN SELECT的，从你的结果来看，速度不慢嘛
player_uid
用字符串做主键，不是个好主意。
另外用个自增ID做主键，然后在player_uid 这个字段上加个范围索引 index player_uid(10)看看怎么样。
CREATE TABLE `A` (
  `a` varchar(11) NOT NULL DEFAULT '',
  PRIMARY KEY (`a`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
这样范围索引。取这个字段的前10个字节。
player_uid这个做主键不好，不如自增主键，这个就普通索引就好
player_uid这个做主键不好，不如自增主键，这个就普通索引就好
我发的profile是我一个人操作数据库时候的，所以应该不会存在锁的问题
如果按照您的看法，这样的语句不需要这么长时间，那您觉得数据库配置是否需要优化呢？
我贴下我的variables
+-----------------------------------------+-------------------------------------------------------------------------------------------+
|Variable_name|Value|
+-----------------------------------------+-------------------------------------------------------------------------------------------+
|auto_increment_increment|1|
|auto_increment_offset|1|
|autocommit|ON|
|automatic_sp_privileges|ON|
|back_log|50|
|basedir|/usr/local/mysql/|
|big_tables|OFF|
|binlog_cache_size|32768|
|binlog_format|MIXED|
|bulk_insert_buffer_size|8388608|
|character_set_client|utf8|
|character_set_connection|utf8|
|character_set_database|utf8|
|character_set_filesystem|binary|
|character_set_results|utf8|
|character_set_server|utf8|
|character_set_system|utf8|
|character_sets_dir|/var/mysqldata/mysql/charsets/|
|collation_connection|utf8_general_ci|
|collation_database|utf8_general_ci|
|collation_server|utf8_general_ci|
|completion_type|0|
|concurrent_insert|1|
|connect_timeout|10|
|datadir|/usr/local/mysql/var/|
|date_format|%Y-%m-%d|
|datetime_format|%Y-%m-%d%H:%i:%s|
|default_week_format|0|
|delay_key_write|ON|
|delayed_insert_limit|100|
|delayed_insert_timeout|300|
|delayed_queue_size|1000|
|div_precision_increment|4|
|engine_condition_pushdown|ON|
|error_count|0|
|event_scheduler|OFF|
|expire_logs_days|0|
|flush|OFF|
|flush_time|0|
|foreign_key_checks|ON|
|ft_boolean_syntax|+-><()~*:""&||
|ft_max_word_len|84|
|ft_min_word_len|4|
|ft_query_expansion_limit|20|
|ft_stopword_file|(built-in)|
|general_log|OFF|
|general_log_file|/usr/local/mysql/var/mysql.log|
|group_concat_max_len|1024|
|have_community_features|YES|
|have_compress|YES|
|have_crypt|YES|
|have_csv|YES|
|have_dynamic_loading|YES|
|have_geometry|YES|
|have_innodb|YES|
|have_ndbcluster|NO|
|have_openssl|DISABLED|
|have_partitioning|YES|
|have_query_cache|YES|
|have_rtree_keys|YES|
|have_ssl|DISABLED|
|have_symlink|YES|
|hostname|mysql|
|identity|0|
|ignore_builtin_innodb|OFF|
|init_connect|SETNAMESutf8|
|init_file||
|init_slave||
|innodb_adaptive_hash_index|ON|
|innodb_additional_mem_pool_size|1048576|
|innodb_autoextend_increment|8|
|innodb_autoinc_lock_mode|1|
|innodb_buffer_pool_size|8388608|
|innodb_checksums|ON|
|innodb_commit_concurrency|0|
|innodb_concurrency_tickets|500|
|innodb_data_file_path|ibdata1:10M:autoextend|
|innodb_data_home_dir||
|innodb_doublewrite|ON|
|innodb_fast_shutdown|1|
|innodb_file_io_threads|4|
|innodb_file_per_table|OFF|
|innodb_flush_log_at_trx_commit|1|
|innodb_flush_method||
|innodb_force_recovery|0|
|innodb_lock_wait_timeout|50|
|innodb_locks_unsafe_for_binlog|OFF|
|innodb_log_buffer_size|1048576|
|innodb_log_file_size|5242880|
|innodb_log_files_in_group|2|
|innodb_log_group_home_dir|./|
|innodb_max_dirty_pages_pct|90|
|innodb_max_purge_lag|0|
|innodb_mirrored_log_groups|1|
|innodb_open_files|300|
|innodb_rollback_on_timeout|OFF|
|innodb_stats_on_metadata|ON|
|innodb_support_xa|ON|
|innodb_sync_spin_loops|20|
|innodb_table_locks|ON|
|innodb_thread_concurrency|8|
|innodb_thread_sleep_delay|10000|
|innodb_use_legacy_cardinality_algorithm|ON|
|insert_id|0|
|interactive_timeout|28800|
|join_buffer_size|131072|
|keep_files_on_create|OFF|
|key_buffer_size|16777216|
|key_cache_age_threshold|300|
|key_cache_block_size|1024|
|key_cache_division_limit|100|
|language|/var/mysqldata/mysql/english/|
|large_files_support|ON|
|large_page_size|0|
|large_pages|OFF|
|last_insert_id|0|
|lc_time_names|en_US|
|license|GPL|
|local_infile|ON|
|locked_in_memory|OFF|
|log|OFF|
|log_bin|ON|
|log_bin_trust_function_creators|OFF|
|log_bin_trust_routine_creators|OFF|
|log_error|/usr/local/mysql/var/mysql.err|
|log_output|FILE|
|log_queries_not_using_indexes|OFF|
|log_slave_updates|OFF|
|log_slow_queries|ON|
|log_warnings|1|
|long_query_time|10.000000|
|low_priority_updates|OFF|
|lower_case_file_system|OFF|
|lower_case_table_names|0|
|max_allowed_packet|1048576|
|max_binlog_cache_size|18446744073709547520|
|max_binlog_size|1073741824|
|max_connect_errors|10|
|max_connections|2000|
|max_delayed_threads|20|
|max_error_count|64|
|max_heap_table_size|16777216|
|max_insert_delayed_threads|20|
|max_join_size|18446744073709551615|
|max_length_for_sort_data|1024|
|max_prepared_stmt_count|16382|
|max_relay_log_size|0|
|max_seeks_for_key|18446744073709551615|
|max_sort_length|1024|
|max_sp_recursion_depth|0|
|max_tmp_tables|32|
|max_user_connections|0|
|max_write_lock_count|18446744073709551615|
|min_examined_row_limit|0|
|multi_range_count|256|
|myisam_data_pointer_size|6|
|myisam_max_sort_file_size|9223372036853727232|
|myisam_recover_options|OFF|
|myisam_repair_threads|1|
|myisam_sort_buffer_size|8388608|
|myisam_stats_method|nulls_unequal|
|myisam_use_mmap|OFF|
|net_buffer_length|8192|
|net_read_timeout|30|
|net_retry_count|10|
|net_write_timeout|60|
|new|OFF|
|old|OFF|
|old_alter_table|OFF|
|old_passwords|OFF|
|open_files_limit|10000|
|optimizer_prune_level|1|
|optimizer_search_depth|62|
|optimizer_switch|index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on|
|pid_file|/usr/local/mysql/var/mysql.pid|
|plugin_dir|/usr/local/mysql/lib/mysql/plugin|
|port|3306|
|preload_buffer_size|32768|
|profiling|OFF|
|profiling_history_size|15|
|protocol_version|10|
|pseudo_thread_id|0|
|query_alloc_block_size|8192|
|query_cache_limit|1048576|
|query_cache_min_res_unit|4096|
|query_cache_size|16777216|
|query_cache_type|ON|
|query_cache_wlock_invalidate|OFF|
|query_prealloc_size|8192|
|rand_seed1||
|rand_seed2||
|range_alloc_block_size|4096|
|read_buffer_size|262144|
|read_only|OFF|
|read_rnd_buffer_size|524288|
|relay_log||
|relay_log_index||
|relay_log_info_file|relay-log.info|
|relay_log_purge|ON|
|relay_log_space_limit|0|
|report_host||
|report_password||
|report_port|3306|
|report_user||
|rpl_recovery_rank|0|
|secure_auth|OFF|
|secure_file_priv||
|server_id|1|
|skip_external_locking|ON|
|skip_networking|OFF|
|skip_show_database|OFF|
|slave_compressed_protocol|OFF|
|slave_exec_mode|STRICT|
|slave_load_tmpdir|/tmp|
|slave_net_timeout|3600|
|slave_skip_errors|OFF|
|slave_transaction_retries|10|
|slow_launch_time|2|
|slow_query_log|ON|
|slow_query_log_file|/usr/local/mysql/var/slow-qurey.log|
|socket|/tmp/mysql.sock|
|sort_buffer_size|524288|
|sql_auto_is_null|ON|
|sql_big_selects|ON|
|sql_big_tables|OFF|
|sql_buffer_result|OFF|
|sql_log_bin|ON|
|sql_log_off|OFF|
|sql_log_update|ON|
|sql_low_priority_updates|OFF|
|sql_max_join_size|18446744073709551615|
|sql_mode||
|sql_notes|ON|
|sql_quote_show_create|ON|
|sql_safe_updates|OFF|
|sql_select_limit|18446744073709551615|
|sql_slave_skip_counter||
|sql_warnings|OFF|
|ssl_ca||
|ssl_capath||
|ssl_cert||
|ssl_cipher||
|ssl_key||
|storage_engine|MyISAM|
|sync_binlog|0|
|sync_frm|ON|
|system_time_zone|CST|
|table_definition_cache|256|
|table_lock_wait_timeout|50|
|table_open_cache|64|
|table_type|MyISAM|
|thread_cache_size|0|
|thread_handling|one-thread-per-connection|
|thread_stack|262144|
|time_format|%H:%i:%s|
|time_zone|SYSTEM|
|timed_mutexes|OFF|
|timestamp|1288275439|
|tmp_table_size|16777216|
|tmpdir|/tmp|
|transaction_alloc_block_size|8192|
|transaction_prealloc_size|4096|
|tx_isolation|REPEATABLE-READ|
|unique_checks|ON|
|updatable_views_with_limit|YES|
|version|5.1.38-log|
|version_comment|Sourcedistribution|
|version_compile_machine|x86_64|
|version_compile_os|unknown-linux-gnu|
|wait_timeout|28800|
|warning_count|0|
+-----------------------------------------+-------------------------------------------------------------------------------------------+
271rowsinset(0.00sec)
select * from _player_server  WHERE player_uid = 'puid_4cc90f042f2b50.28881786';测试这个需要花多久？
如果这个正常，则说明与索引，表结构，磁盘碎片没什么关系了。
如果这个SELECT就慢，则检查方向就是相关的参数了。
单纯的select是不慢的..下面是profile
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000009 |
| checking query cache for query | 0.000005 |
| checking privileges on cached  | 0.000003 |
| sending cached result to clien | 0.000015 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000002 |
+--------------------------------+----------+问题在于那条update语句，哪怕由于行值相同没实际更新，时间也一样久，都是耗在freeing items上面，不知道这一项是干什么的。
同样的库，同样的语句，放到windows服务器上的mysql中，却变得快了。。
呵呵，现在才说明系统不是WINDOWS
一般和操作系统关系不大。freeing items 是语句已经执行完成，释放在查询缓存中的一些东西。但这些东西并不准确。你试一下，
update _player_server
set group_id=group_id
WHERE player_uid = 'puid_4cc90f042f2b50.28881786';update _player_server
set group_id='khUI2EN_T3'
WHERE player_uid = 'puid_4cc90f042f2b50.28881786';这两句的时间如何。
老大，你怎么发现是这个问题的？这个怎么解决？group_id = group_id
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000035 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000006 |
| System lock          | 0.000003 |
| Table lock           | 0.000002 |
| init                 | 0.000030 |
| Updating             | 0.000026 |
| end                  | 0.000006 |
| query end            | 0.000002 |
| freeing items        | 0.000086 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000005 |
+----------------------+----------+group_id='khUI2EN_T3'
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000040 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000007 |
| System lock          | 0.000003 |
| Table lock           | 0.000002 |
| init                 | 0.000033 |
| Updating             | 0.000046 |
| end                  | 0.000008 |
| query end            | 0.000001 |
| freeing items        | 0.077255 |
| logging slow query   | 0.000006 |
| cleaning up          | 0.000002 |
+----------------------+----------+
我又测试了几条，又这么一个规律：
1. 对于使用列名运算进行更新的：如果值变化则慢，如果值不变则快
2. 对于直接使用字符串更新的：不论值是否变化，都是慢的
慢都是因为freeing items慢