由于超时问题,我遇到了一些非常慢的MySQL数据库查询问题,导致我们的网站崩溃(参见SQL) . 希望熟悉MySQL内部工作原理的人能够轻松识别问题 .
潜在原因
我认为问题源于我们电子商务平台的桌面设计 .
tbl_sessions
表中的主键是VARCHAR(32) .
如果我错了,请纠正我,但在插入新行之前,MySQL是否首先扫描表的PRIMARY KEY以确保没有重复?我认为这可能是将新行添加到 tbl_sessions
时的问题之一(请参阅下面的INSERT查询)
在定期运行的垃圾收集过程中(请参阅下面的DELETE查询),将连接两个表,并删除没有加入会话的 tbl_carts
行 . 没有索引可以轻松加入它们,因此我认为MySQL必须执行嵌套循环才能使表连接(Rows_examined:45718650) . 它是否正确?
可能的解决方案
我想首先制作一个MyISAM表_478582_来删除不断访问和更改的表上的表锁定问题 .
然后我将调整 tbl_sessions
的表结构和PHP处理,使用"int(10) unsigned NOT NULL auto_increment"作为PRIMARY KEY,而不是通过随机种子生成的当前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;
1 回答
看起来像锁定问题...... INSERT语句花费的时间太长而导致SELECT等待它们完成(SELECT锁定时间为99秒) . 我不认为从Innodb切换到MyISAM会增加速度(如果Innodb没有错误配置) .
此外,我不会说这是一个磁盘问题,因为这也会影响到其他表的所有其他查询...
会话表有多少行?我想你没有百万活跃的会话...... VARCHAR上的索引当然比较慢,但不应该那么慢......
但对于Innodb,通常建议不要使用大的主索引,因为它们用于许多查找...而且4字节的INT比32字节的VARCHAR快(或者如果它不是BINARY和默认的charset,则为64字节)是utf8) . 因此我建议删除PRIMARY KEY(这将导致Innodb引擎将生成INT类型的内部主键)并在
session_id
字段上创建一个UNIQUE键 . 这应该使INSERT更快,因为它只会增加主键并检查session-id
的唯一性 .