等待 MySQL 表上的表级锁定
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22809439/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Waiting for table level lock on a MySQL table
提问by oliverbj
The last few days, at random times my website has become very slow. I started to investigate the best I could. I saw that the MySQL process was using 85 - 95% of the available memory of my server (should I upgrade my memory also?).
最近几天，我的网站在随机时间变得非常缓慢。我开始尽我所能进行调查。我看到 MySQL 进程使用了我服务器可用内存的 85 - 95%（我也应该升级我的内存吗？）。
I checked my MySQL process log, and I noticed a huge list of queries with:
我检查了我的 MySQL 进程日志，我注意到一个巨大的查询列表：
Waiting for table level lock
等待表级锁
But what I also noticed, what that ALL of these queries with "table level lock", was only queries which had something to do with my table called users.
但我也注意到，所有这些带有“表级锁”的查询，只是与我的名为users.
I have 20 other tables, with constant queries, but I don't see them on the list.. So I guess the problem is with the users table?
我还有 20 个其他表，有不断的查询，但我没有在列表中看到它们..所以我猜问题出在 users 表上？
I want to know how I can improve the table, and eventually remove the table level lock?
我想知道如何改进表，并最终删除表级锁？
I also ran this:
我也跑了这个：
SHOW VARIABLES LIKE 'query_cache%';
Which resulted in this:
这导致了这个：
query_cache_limit
1048576
query_cache_min_res_unit
4096
query_cache_size
33554432
query_cache_type
ON
query_cache_wlock_invalidate
OFF
Please let me know what I can do to improve my database/mysql.
请让我知道我可以做些什么来改进我的数据库/mysql。
This is a list of the processes:
这是进程列表：
| 228 | db_user | localhost | db_db| Query | 5 | Waiting for table level lock | SELECT count(*) FROM users WHERE createtime>'1396411200' OR createtime='1396411200' |
| 229 | db_user | localhost | db_db| Query | 4 | Waiting for table level lock | UPDATE users SET upline_clicks=upline_clicks+'1', upline_earnings=upline_earnings+'0.0000' WHERE use |
| 203 | db_user | localhost | db_db| Query | 6 | Waiting for table level lock | SELECT SUM(cashedout) FROM users |
| 204 | db_user | localhost | db_db| Query | 4 | Waiting for table level lock | UPDATE users SET upline_clicks=upline_clicks+'1', upline_earnings=upline_earnings+'0.0000' WHERE use |
| 205 | db_user | localhost | db_db| Query | 1 | Waiting for table level lock | SELECT * FROM users WHERE id='12055' |
| 206 | db_user | localhost | db_db| Query | 2 | Waiting for table level lock | SELECT * FROM users WHERE id='22530'
| 197 | db_user | localhost | db_db| Query | 3 | Waiting for table level lock | SELECT * FROM `users` WHERE `username` = 'ptc4life123' LIMIT 1 |
| 200 | db_user | localhost | db_db| Query | 3 | Waiting for table level lock | UPDATE users SET upline_clicks=upline_clicks+'1', upline_earnings=upline_earnings+'0.0050' WHERE use |
This is basically what all the locked processes looks like.
这基本上就是所有锁定进程的样子。
采纳答案by smoore4
During one of the slow periods, run this command:
在缓慢期间之一，运行以下命令：
show processlist;
This will show you the actual SQL commands that are running, and the places to look for in terms of adding indexes. Post the longest running SQL if possible.
这将显示正在运行的实际 SQL 命令，以及在添加索引方面要查找的位置。如果可能，发布运行时间最长的 SQL。
Adding an index will look like this:
添加索引将如下所示：
ALTER TABLE MYTABLE ADD INDEX idx_columnname (COLUMN_NAME ASC) ;
But you want to be careful not to do that during a production period. At first glance, you want to do that on ID and username columns in the user table.
但是你要小心不要在生产期间这样做。乍一看，您希望对用户表中的 ID 和用户名列执行此操作。
回答by Gooner
Check the storage engine of the table .Change to Innodb if possible as it cause only row level locking. The queries which are running may be forcing a table lock even if you are using innodb tables if you are not using the index .
检查表的存储引擎。如果可能，请更改为 Innodb，因为它只会导致行级锁定。如果您不使用索引，即使您使用的是 innodb 表，正在运行的查询也可能会强制锁定表。
