MySQL小知识

1.字段的种类尽可能用int 或者tinyint类型。另外字段尽可能用NOT NULL。
当然无可避免某些字段会用到text ,varchar等字符类型，最好将text字段的单独出另外一个表出来（用主键关联好）。
varchar存储65535个字节。

2.尽量不要用子查询

mysql> explain select uid_,count(*) from smember_6 where uid_ in (select uid_ from alluid) group by uid_;
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+
| 1 | PRIMARY | smember_6 | index | NULL | PRIMARY | 8 | NULL | 53431264 | Using where; Using index | 
| 2 | DEPENDENT SUBQUERY | alluid | ALL | NULL | NULL | NULL | NULL | 2448 | Using where |

–优化后

| mysql> explain select a.uid_,count(*) from smember_6 a,alluid b where a.uid_=b.uid_ group by uid_;
+----+-------------+-------+------+---------------+---------+---------+------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+------------+------+---------------------------------+
| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2671 | Using temporary; Using filesort | 
| 1 | SIMPLE | a | ref | PRIMARY | PRIMARY | 4 | ssc.b.uid_ | 1 | Using index

3.事务的起始点其实是以执行的第一条语句为起始点的，而不是以begin作为事务的起始点的。