Work notes-Weibo database design

Users table Design
CREATE TABLE ' users ' (
' UID ' int (a) not NULL auto_increment,
' Username ' varchar DEFAULT NULL,
' Password ' varchar DEFAULT NULL,
PRIMARY KEY (' UID ')
) Engine=innodb auto_increment=7 DEFAULT charset=latin1;

Fans table Design
CREATE TABLE ' fans ' (
' Fansid ' int (not NULL auto_increment),
' UID ' int (+) DEFAULT NULL,
' FID ' int (DEFAULT NULL),
PRIMARY KEY (' Fansid '),
KEY ' uid ' (' uid '),
KEY ' FID ' (' FID '),
CONSTRAINT ' FID ' FOREIGN KEY (' fid ') REFERENCES ' users ' (' UID '),
CONSTRAINT ' uid ' FOREIGN KEY (' uid ') REFERENCES ' users ' (' UID ')
) Engine=innodb auto_increment=6 DEFAULT charset=latin1;

TMP table Design
CREATE TABLE ' tmp ' (
' ID ' int (a) is not NULL auto_increment,
' UID ' int (+) DEFAULT NULL,
' Message ' varchar DEFAULT NULL,
PRIMARY KEY (' ID '),
KEY ' UID2 ' (' UID '),
CONSTRAINT ' UID2 ' FOREIGN KEY (' uid ') REFERENCES ' users ' (' UID ')
) Engine=innodb auto_increment=138353 DEFAULT charset=latin1;

Message table Design
CREATE TABLE ' message ' (
' MSGID ' int (not NULL auto_increment),
' OID ' int (DEFAULT NULL),
' UID ' int (+) DEFAULT NULL,
' Message ' varchar (+) DEFAULT NULL,
' State ' bit (1) DEFAULT B ' 0 ',
PRIMARY KEY (' MSGID '),
KEY ' oid ' (' oid '),
KEY ' UID1 ' (' UID '),
CONSTRAINT ' oid ' FOREIGN KEY (' oid ') REFERENCES ' users ' (' UID '),
CONSTRAINT ' UID1 ' FOREIGN KEY (' uid ') REFERENCES ' users ' (' UID ')
) Engine=innodb auto_increment=2949116 DEFAULT charset=latin1;

Scenario 1:
Inquire
Select F.fid,t.uid,t.message from fans F LEFT join tmp t on f.uid = T.uid

Inserting into the Receiving table: (Time consuming 28.656ms seconds)
INSERT into message (oid,uid,message) Select F.fid,t.uid,t.message from fans F LEFT join tmp t on f.uid = T.uid

To establish a stored procedure:
CREATE PROCEDURE MyProc ()
Begin
DECLARE I int;
SET I=1;
Loop1:while i<=99999 Do
INSERT INTO TMP (Uid,message) VALUES (1, "abc");
SET i=i+1;
END while Loop1;
End

To call a stored procedure:
Call MyProc ();

Insert in batches: (Takes two batches 21 seconds each time)
1. Total statistics
Select COUNT (f.uid) from the fans F left join tmp t on f.uid = T.uid

2, divided by the number of batches and then inserted
Begin
DECLARE I int;
Select COUNT (f.uid)/2 into I from the fans F left join tmp t on f.uid = T.uid;
INSERT into message (oid,uid,message) Select F.fid,t.uid,t.message from the fans F left join tmp t on f.uid = T.uid limit 0,i;
End

3, query the message table data (0.468 seconds) message table 70w Records 2 article state=1
SELECT * from message where OID =2 and state =1

4. Precautions
3.1, before the implementation of the above operation, not allowed to join new fans and publish new meager content. Add a state to determine if the process is in progress. If so, the queue is used to cache the new fans and the meager.

Scenario 2: (Time consuming 1.14 seconds) TMP table 40w record fans table 10w record
Correlate fans table and TMP table. Fans table Save fan relationship, TMP table save Meager News
SELECT * FROM fans F LEFT join tmp t in F.uid = T.UID where F.fid =2 and state =1

Work notes-Weibo database design