一,核心

1. 不要在数据库做运算.运算移到程序.

2. 一年内单表数据量纯INI不超1KW 含CHAR不超500W

3. 合分表 userid date area 单库 300-400 表.

4. 表字段少而精,单表不超50个INI字段，不超20CHAR字段.单表20－50字段

5. 拒绝大SQL大事务大批量.

   
   

二，用好字段类型.

1. TINYINIT SMALLINT MEDIUMNIT bad case int(1) vs int(11)

2. 字符转化为数字

3. 避免使用NULL  `c` int(10) not null default 0;

4. 少用并拆分TEXT/BLOB到单独的表

5. 不在数据库里存图片

三，索引

1. 合理添加索引

2. 字符字段必须建前缀索引

3. 不在索引列做运算

4. 自增列做INNODB主键

5. 尽量不用外键

数据操作语句

DELETE语法

单表语法：

  

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

    [WHERE where_definition]

    [ORDER BY ...]

    [LIMIT row_count]

多表语法：

  

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    tbl_name[.*] [, tbl_name[.*] ...]

    FROM table_references

    [WHERE where_definition]

或：

  

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    FROM tbl_name[.*] [, tbl_name[.*] ...]

    USING table_references

    [WHERE where_definition]

tbl_name中有些行满足由where_definition给定的条件。DELETE用于删除这些行，并返回被删除的记录的数目。

  

如果您编写的DELETE语句中没有WHERE子句，则所有的行都被删除。

 

DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

或：

 

DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

INSERT语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    VALUES ({expr | DEFAULT},...),(...),...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

 

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    SET col_name={expr | DEFAULT}, ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

 

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    SELECT ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

 

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

INSERT ... SELECT语法

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    SELECT ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

使用INSERT...SELECT，您可以快速地从一个或多个表中向一个表中插入多个行。

 

示例：

 

INSERT INTO tbl_temp2 (fld_id)

    SELECT tbl_temp1.fld_order_id

    FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

SELECT语法

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

      [HIGH_PRIORITY]

      [STRAIGHT_JOIN]

      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr, ...

    [INTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name']

    [FROM table_references

    [WHERE where_definition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_definition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC] , ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [FOR UPDATE | LOCK IN SHARE MODE]]

 

 

  mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name

·                    -> FROM mytable ORDER BY full_name;

JOIN语法

table_references:

    table_reference [, table_reference] …

 

table_reference:

    table_factor

  | join_table

 

table_factor:

    tbl_name [[AS] alias]

        [{USE|IGNORE|FORCE} INDEX (key_list)]

  | ( table_references )

  | { OJ table_reference LEFT OUTER JOIN table_reference

        ON conditional_expr }

 

join_table:

    table_reference [INNER | CROSS] JOIN table_factor [join_condition]

  | table_reference STRAIGHT_JOIN table_factor

  | table_reference STRAIGHT_JOIN table_factor ON condition

  | table_reference LEFT [OUTER] JOIN table_reference join_condition

  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor

  | table_reference RIGHT [OUTER] JOIN table_reference join_condition

  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

 

join_condition:

    ON conditional_expr

  | USING (column_list)

 

 

 

 

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

UPDATE语法

Single-table语法：

 

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

    SET col_name1=expr1 [, col_name2=expr2 ...]

    [WHERE where_definition]

    [ORDER BY ...]

    [LIMIT row_count]

Multiple-table语法：

 

UPDATE [LOW_PRIORITY] [IGNORE] table_references

    SET col_name1=expr1 [, col_name2=expr2 ...]

    [WHERE where_definition]

 

mysql> UPDATE persondata SET age=age*2, age=age+1;