php+mysql记事本_mysql优化

一,核心

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

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

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

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

拒绝大SQL大事务大批量.

二，用好字段类型.

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

字符转化为数字

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

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

不在数据库里存图片

三，索引

合理添加索引

字符字段必须建前缀索引

不在索引列做运算

自增列做INNODB主键

尽量不用外键

数据操作语句

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;