2024年4月27日发(作者：)

0 设计原则

任何语句使用前explain看执行计划是否用到索引

不要从明细表查统计结果，定期统计插入到汇总表

禁止使用SELECT *，必须指定字段名称，包括insert table后边加字段列表

明细统计时，只统计编码，不要关联名称等冗余字段

联合查询时，每个表必须加别名以提高SQL解析效率，如 SELECT FROM GS T1 LEFT JOIN

GSJJ T2 ON =

每个查询结果集使用的内存量不要超过256M，可以通过时间范围控制，如 RK BETWEEN A

AND B，建议大表按可小时操作

页面查询在10秒内要返回结果，服务器超时限制默认为65秒 (查看query cache是否足够

大和命中率,show variables like '%cache%');

语句中避免子查询，子查询无法使用索引

语句中避免使用 GROUP BY, 可通过批量程序定期汇总

1 字段设计

尽可能使用更小的数据类型，如 TINYINT、smallint,MEDIUMINT、INT、BIGINT (如int(11)的

11代表客户端显示宽度，并不是取值范围,tinyint -2^8-2^8-1,smallint -2^15-2^15-1 int

-2^31-2^31-1 bigint -2^63-2^63-1)

尽量少用 TEXT、BLOB 等专有类型 (用链接代替)

字符型，数值型字段类型不能混合使用，依赖后期转换

相同字段不同表中的类型和长度要一致

字段名称不能使用关键字

不要指定字段级编码，建议全库统一

默认值要规范，例如日期不要使用 0000-00-00

2 索引使用

一般情况下，一次查询只会用到一个索引 (特定情况出现merge index的情况,如下可能出现

(a=1 or b=2)会合并a和b的索引,或者使用union all)

每个表索引越少越好，建议1-3个，最多5个 (oltp 1-5,olap 5以上)

每个查询必须用到索引 (小表可能全表更好,视数据量决定)

建立组合索引时，WHERE 条件中用到等于的字段放前边，用到范围的字段放后边，如

DD=100000 AND SJ BETWEEN A AND B 例子(见以上)

删除重复字段的索引，减少dml IO

索引中重复的记录数越少，效率越高，效率最高的是主键 (如果同一记录超过50%,全表扫

描定期analyze table收集统计信息和直方图,如果可以加not null或者unique的最好加上)

索引字段最好不要存在 NULL，NULL可用 0 替代，建议把默认值设置为 0 (也可以

myisam_stats_method和innodb_stats_method取值nulls_equal,在null远多于非null的情况

下,建议表设计 default 0)

组合索引可以只使用第一个，或者前两个，或者前几个，不能从第二个开始用，也不能跳着

使用 (索引使用从前缀开始，多字段索引到between或者<,>等以后字段不会使用,排序最好

在索引中实现)

3 查询条件

SQL 语句的 WHERE 条件避免无效条件和无效括号，如 SELECT BM FROM GS WHERE (1=1) 、

order by

SQL语句中不要加用不到的排序

WHERE 条件中 最好不要用 IN 和 LIKE

WHERE 条件中不要使用 NOW() 等进行判断，影响执行计划

索引要使用的字段不要使用函数或者进行运算，如 field1 + 1 = field2、adddate(field1,…、CAST

禁止字段格式转换，如 SELECT * FROM GS WHERE BM=200000，数值两边不要加引号 (大多

数字段使用函数不会使用索引，只有形如left(BM)='200000'等可以使用)

4 存储过程

存储过程中操作的记录数超过1000条时不能使用游标 (禁止游标，用临时表代替)

在存储过程的关键步骤开始和结束都要记录信息到日志表，用于监控和调试

字符变量使用单引号，不要使用双引号，【"2012-09-23 00:00:00"】 可改为 【'2012-09-23

00:00:00'】

存储过程要能够重复执行，执行时需要清空历史冲突记录

5 远程表

远程表结构要与原始表一致，尤其是索引

远程表数据不要大于256M，远程表的 WHERE 无效

远程表一般用来全表小数据全量同步

6 查询技巧

SQL语句不要太长，如果 IN 列表太多必须改为 LEFT JOIN , 且关联字段主键索引

避免使用 LIKE，【lrsj like "2012-09-23%"】 可改为 【LRSJ BETWEEN '2012-09-23 00:00:00' AND

'2012-09-23 23:59:59'】或者left,right函数

WHERE 多个OR条件不走一个索引时可通过 UNION，如【bm1=953016 or bm2=953016】改

为【SELECT … WHERE BM1=953016 UNION ALL SELECT … WHERE BM2=953016】(merge

index,explain的结果是using union(idx_name,idx_name))

7 性能优化

编译器改为ICC可以提升5%，文件格式改为XFS可以提升5%，增加1/6磁盘可以提升1/6，

优化索引和结构一般可以提升100-1000倍

使用type=heap的临时表

8 引擎使用

innodb引擎,在过程结尾提交，避免过度commit

9 权限控制

PHP连接MYSQL的用户只分配SIUD权限