在MySQL表上等待表级锁

Question

最近几天，我的网站在随机时间变得非常缓慢。我尽力进行了调查。我发现MySQL进程正在使用服务器可用内存的85-95％（我应该升级内存吗？）。

我检查了MySQL进程日志，并注意到一个巨大的查询列表：

“等待表级锁定”

但是我还注意到，所有这些具有“表级锁定”的查询都只涉及名为“users”的表。

我有其他20个表，有不断的查询，但我没有在列表上看到它们...所以我猜问题出在用户表上？

我想知道如何改进表格，并最终删除表级锁定？

我还运行了以下命令：

SHOW VARIABLES LIKE 'query_cache%';

这导致了这样的结果：

query_cache_limit
1048576
query_cache_min_res_unit
4096
query_cache_size
33554432
query_cache_type
ON
query_cache_wlock_invalidate
OFF

请告诉我如何提高我的数据库/MySQL的性能。以下是一些处理过程列表：

   | 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 |

这基本上就是所有被锁定进程的样子。

- oliverbj

---

开始为用户添加索引，特别是像用户姓氏、部门、ID等经常在查询中使用的字段。 - smoore4

索引？能否稍微解释一下？（抱歉，我对MySQL还比较新） - oliverbj

Answer 1

在低峰期间，运行此命令：

show processlist;

这将展示实际运行的SQL命令，并指出需要添加索引的位置。如果可能，请发布最长运行的SQL。

添加索引的方式如下：

 ALTER TABLE MYTABLE ADD INDEX idx_columnname (COLUMN_NAME ASC) ;

但是在生产期间，您要小心不要这样做。乍一看，您想在用户表中的ID和用户名列上执行此操作。

- smoore4

---

请查看我的更新问题。我已经列出了慢速处理的列表。 - oliverbj

好的。索引很可能在不到10秒钟内创建完成。 - smoore4

那我应该在“ID”和“用户名”上创建索引吗？ ALTER TABLE users ADD INDEX id (COLUMN_NAME ASC); 以及 ALTER TABLE users ADD INDEX username (COLUMN_NAME ASC);这样正确吗？ - oliverbj

哦，抱歉！其余的都只是：“WHERE username=:username”或“WHERE id=:id”。 - oliverbj

关于这个问题，我们能否私下聊一聊？在StackOverflow上交换详细信息是允许的吗？ - oliverbj

Answer 2

检查表的存储引擎。如果可能的话，请更改为Innodb，因为它只会引起行级锁定。 运行的查询可能会强制使用表锁定，即使您正在使用innodb表，如果您没有使用索引。

- Gooner

---

请问您能否更详细地解释一下“索引”是什么？我应该如何使用它们？ - oliverbj

索引是指向数据的指针。它通常是一个二叉树，拥有完整行作为叶节点。例如，主键是指向单个行的索引。不同之处在于它会被排序，这样您就不必执行完整的表扫描来获取数据。 - Gooner

如果您没有进行索引，则它会像执行全表扫描而不是索引扫描一样工作，从而锁定整个表。 - Gooner

请查看文档 https://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html - Gooner

哦，好的。我只需要在表格中使用一行的INDEX吗？（例如像“userid”这样的） - oliverbj

如果它是主键，则已经被索引。根据您的WHERE条件创建索引，例如，如果它类似于（user_id =1 and user_name ='test'），则创建一个如下的索引（user_id，user_name）。但请注意，表在单个查询中只使用一个索引，太多的索引将使插入、更新和删除变得更慢。 - Gooner