通过触发器解决统计,count(*)问题

时间:2021-09-15 02:02:15

单表过千万,以按用户分表1024个,count(*)统计还是会慢

    所谓的慢,是指高并发了,MySQL就痿了。SAS硬盘,随机IO速率到达10~15M,IO就基本是100%了。
这时就呵呵了。CPU有巨大的剩余也没用,内存也帮不上忙,只能SSD才能解决。
所谓通过程序--异步事件之类的,去加1,减1地统计结果--好像是一种方案.
最终上线后,数据量一大,并发量一大,数据最后出来负数---尼玛,这就是所谓的`数据一致性的bug`
最终尝试使用MySQL的触发器解决,SQL代码如下:
CREATE TABLE if NOT EXISTS `yuec_users`(
invite_id VARCHAR(255)
);


CREATE TABLE
IF NOT EXISTS `total_yuec_users_invite_id` (total BIGINT NOT NULL);


DROP TRIGGER IF EXISTS after_insert_yuec_users;
DROP TRIGGER IF EXISTS after_update_yuec_users;
DROP TRIGGER IF EXISTS before_delete_yuec_users;

CREATE TRIGGER after_insert_yuec_users
AFTER INSERT
ON yuec_users FOR EACH ROW
BEGIN
IF NEW.invite_id != '1014766' AND NEW.invite_id != '' AND NEW.invite_id IS NOT NULL THEN
UPDATE `total_yuec_users_invite_id` SET total=`total`+1;

END IF;
END;


CREATE TRIGGER after_update_yuec_users
AFTER UPDATE
ON yuec_users FOR EACH ROW
BEGIN
IF NEW.invite_id != '1014766' AND NEW.invite_id != '' AND NEW.invite_id IS NOT NULL THEN
UPDATE `total_yuec_users_invite_id` SET total=`total`+1;

END IF;
IF OLD.invite_id != '1014766' AND OLD.invite_id != '' AND OLD.invite_id IS NOT NULL THEN
UPDATE `total_yuec_users_invite_id` SET total=`total`-1;
END IF;
END;

CREATE TRIGGER before_delete_yuec_users
BEFORE DELETE
ON yuec_users FOR EACH ROW
BEGIN
IF OLD.invite_id != '1014766' AND OLD.invite_id != '' AND OLD.invite_id IS NOT NULL THEN
UPDATE `total_yuec_users_invite_id` SET total=`total`-1;

END IF;
END;