mysql优化

2013-06-30 23:04:08 0  category: PHP记事本

一,核心


  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;