mysql - 优化大流量网站上非常慢的 MySQL 查询或表

Question

我遇到了一些非常慢的 MySQL 数据库查询问题，由于超时问题导致我们的网站瘫痪(请参阅 SQL)。希望熟悉 MySQL 内部工作原理的人能够轻松识别问题。

潜在原因

我认为问题出在我们电商平台的表格设计上。

tbl_sessions 表中的主键是 VARCHAR(32)。

如果我错了，请纠正我，但是MySQL在插入新行之前不会先扫描表的主键以确保没有重复吗？我认为这可能是向 tbl_sessions 添加新行时出现的问题之一(请参阅下面的 INSERT 查询)

在定期运行的垃圾收集过程中(请参阅下面的 DELETE 查询)，两个表被连接，并且没有连接 session 的 tbl_carts 行被删除。没有索引可以轻松连接它们，因此我认为 MySQL 必须执行嵌套循环来连接表(Rows_examined:45718650)。这是正确的吗？

可能的解决方案

我正在考虑首先将 tbl_sessions 创建为 MyISAM 表，以消除不断访问和更改的表上的表锁定问题。

然后我将调整表结构和 PHP 对 tbl_sessions 的处理，以使用“int(10) unsigned NOT NULL auto_increment”作为主键，而不是通过生成的当前 VARCH(32)随机种子。

向tbl_sessions添加索引。cart_id可以帮助MySQL在有索引使用的情况下使join语句执行得更快。

大问题

这些更改会缓解我们的 MySQL 服务器在执行这些查询时遇到的问题吗？

我想在更改数据库并可能导致更多问题之前收集一些想法。

提前非常感谢阅读本文并能够提供有关该问题的任何见解的人们。

示例数据

在我的慢速查询日志中，我有这些条目(我混淆了一些信息):

User@Host: my_db[my_db] @  [111.111.111.168]  
Thread_id: 27062158  Schema: my_db  
Query_time: 35.360792  Lock_time: 0.000028  Rows_sent: 0  Rows_examined: 0    Rows_affected: 1  Rows_read: 2  
INSERT INTO `tbl_sessions` (`session_id`, `member_id`, `fingerprint`, `cart_id`,   `expires`) VALUES ('b6792e10a652c951725a2f4ed42785b5', 0, '6398399acb7d1cbf8f47a01bdbfd7c4b78137e64', 99811, 1321259075);

User@Host: my_db[my_db] @  [111.111.111.108]  
  # Thread_id: 27062280  Schema: my_db  
  # Query_time: 35.360284  Lock_time: 0.000037  Rows_sent: 0  Rows_examined: 0    Rows_affected: 1  Rows_read: 2  
  INSERT INTO `tbl_sessions` (`session_id`, `member_id`, `fingerprint`, `cart_id`, `expires`) VALUES ('a55b2259f779d7afe741d4aec52512d5', 0, '18c3d7525633a1420f9e4c396c35a8f70d16d8a2', 99822, 1321259075);  

User@Host: my_db[my_db] @  [111.111.111.109]  
Thread_id: 27062243  Schema: my_db  
Query_time: 35.360519  Lock_time: 0.000042  Rows_sent: 0  Rows_examined: 0  Rows_affected: 1  Rows_read: 2  
INSERT INTO `tbl_sessions` (`session_id`, `member_id`, `fingerprint`, `cart_id`, `expires`) VALUES ('231b4f8cf40aa798c4f9d8ee85e6fe60', 0, '6f50b756815a739ba2faa2c281bf4e4f9af3fd7c', 99819, 1321259075);  

还有这个:

User@Host: my_db[my_db] @  [111.111.111.16]  
Thread_id: 27062326  Schema: my_db  
Query_time: 134.527582  Lock_time: 99.154168  Rows_sent: 0  Rows_examined: 45718650  Rows_affected: 37  Rows_read: 7074  
DELETE `tbl_carts`  
FROM `tbl_carts`  
LEFT OUTER JOIN `tbl_sessions`  
ON `tbl_carts`.`id` = `tbl_sessions`.`cart_id`  
WHERE `tbl_sessions`.`cart_id` IS NULL;

表结构如下:

CREATE TABLE `tbl_sessions` (  
  `session_id` varchar(32) NOT NULL,  
  `cart_id` int(10) default NULL,  
  `fingerprint` varchar(40) default '',  
  `expires` int(11) default '0',  
  `member_id` int(10) default NULL,  
  PRIMARY KEY  (`session_id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

CREATE TABLE `tbl_carts` (  
  `id` int(10) unsigned NOT NULL auto_increment,  
  `cart` text,  
  `timestamp` int(11) default '0',  
  `url` text,  
  PRIMARY KEY  (`id`)  
) ENGINE=MyISAM AUTO_INCREMENT=41635 DEFAULT CHARSET=utf8;  

Answer 1

似乎是锁定问题... INSERT 语句花费的时间太长，导致 SELECT 等待它们完成(SELECT 锁定时间为 99 秒)。我不认为从 Innodb 切换到 MyISAM 会提高速度(如果 Innodb 没有配置错误的话)。

另外，我不会说这是一个磁盘问题，因为这也会影响对其他表的所有其他查询...

session 表有多少行？我想你没有一百万个事件 session ...VARCHAR 上的索引当然较慢，但不应该那么慢...

但是对于 Innodb，通常建议不要使用大的主索引，因为它们用于许多查找...并且 4 字节 INT 比 32 字节 VARCHAR(或 64 字节，如果它不是 BINARY)更快默认字符集是utf8)。所以我建议删除 PRIMARY KEY (这将导致 Innodb 引擎将创建一个 INT 类型的内部主键)并在 session_id 字段上创建一个 UNIQUE 键。这应该会使 INSERT 更快，因为它只会增加主键并检查 session-id 的唯一性。