一些 Mysql 的优化经验

一些 Mysql 的优化经验

1. 从数据库结构做起
   1. 字段类型的定义时遵循以下规则:
      1. 选用字段长度最小
      2. 优先使用定长型
      3. 尽可能的定义 "NOT NULL"
      4. 数值型字段中避免使用 "ZEROFILL"
      5. 如果要储存的数据为字符串, 且可能值已知且有限, 优先使用 enum 或 set
   2. 索引的优化至关重要(以下如果没有特殊说明, 均指查询密集的情况)
      1. 被索引的字段的长度越小, 该索引的效率越高
      2. 被索引的字段中, 值的重复越少, 该索引的效率越高
      3. 查询语句中, 如果使用了 "group" 子句, 根据其中字段出现的先后顺序建立多字段索引
      4. 查询语句中, 如果使用了 "distinct", 根据其中字段出现的先后顺序建立多字段索引
      5. "where" 子句中, 出现对同一表中多个不同字段的 "and" 条件时, 按照字段出现的先后顺序建立多字段索引
      6. "where" 子句中, 出现对同一表中多个不同字段的 "or" 条件时, 对重复值最少的字段建立单字段索引
      7. 进行 "内/外连接" 查询时, 对 "连接字段" 建立索引
      8. 对 "主键" 的 "unique" 索引 毫无意义, 不要使用
      9. 被索引字段尽可能的使用 "NOT NULL" 属性
      10. 对写入密集型表, 尽量减少索引, 尤其是 "多字段索引" 和 "unique" 索引
2. 查询语句的优化
1. 多多利用 "explain" 查询索引使用情况, 以便找出最佳的查询语句写法和索引设置方案
2. 慎用 "select *", 查询时只选出必须字段
3. 查询使用索引时, 所遍历的索引条数越少, 索引字段长度越小, 查询效率越高 (可使用 "explain" 查询索引使用情况)
4. 避免使用 mysql 函数对查询结果进行处理, 将这些处理交给客户端程序负责
5. 使用 "limit" 时候, 尽量使 "limit" 出的部分位于整个结果集的前部, 这样的查询速度更快, 系统资源开销更低
6. 在 "where" 子句中使用多个字段的 "and" 条件时, 各个字段出现的先后顺序要与多字段索引中的顺序相符
7. 在 "where" 子句 中使用 "like" 时, 只有当通配符不出现在条件的最左端时才会使用索引
8. 在 mysql 4.1 以上版本中, 避免使用子查询, 尽量使用 "内/外连接" 实现此功能
9. 减少函数的使用, 如果可能的话, 尽量用单纯的表达式来代替
10. 避免在 "where" 子句中, 对不同字段进行 "or" 条件查询, 将其拆分成多个单一字段的查询语句效率更高

附: Mysql 字段长度说明表

Mysql 字段长度说明
TINYINT	1 字节	SMALLINT	2 字节
MEDIUMINT	3 字节	INTEGER:	4 字节
BIGINT	8 字节	DOUBLE	8 字节
FLOAT(X)	X <=24: 4 字节 X > 24: 8 字节	DECIMAL(M,D)	MM>=D: M 字节
DATE	3 字节	DATETIME	8 字节
TIMESTAMP	4 字节	TIME	4 字节
YEAR	1 字节
CHAR(M)	M 字节	VARCHAR(M)	值长度 + 1 字节
TINYBLOB	值长度 + 1 字节	TINYTEXT	值长度 + 1 字节
BLOB	值长度 + 2 字节	TEXT	值长度 + 2 字节
MEDIUMBLOB	值长度 + 3 字节	MEDIUMTEXT	值长度 + 3 字节
LONGBLOB	值长度 + 4 字节	LONGTEXT	值长度 + 4 字节
ENUM	1 或 2 字节 取决于枚举值数