MySQL 规范及优化

一、建库建表优化

1、核心规范（推荐）

• 表字符集选择UTF8 （“表情”字段单独设置为其他字符集）
• 存储引擎使用INNODB
• 不在库中存储图片、文件等
• 使用可变长字符串（varchar）
• 每张表数据量控制在5000W以下

2、字段命名规范（建议）

• 库名、表名、字段名、索引名使用小写字母，以下划线分割
• 非唯一索引按照“idx_字段名[_字段名]”进行命名
• 唯一索引按照“uniq_字段名[_字段名]”进行命名（不要直接采用字段名称定义索引名称。防止删除索引时，误删除字段）

3、字段属性规则（建议）

• 所有字段均定义为 NOT NULL（null会降低索引效果;索引会产生额外的空间）
• ·使用unsigned存储非负整数
• ·使用timestamp存储时间（可利用该类型的默认值，进行查询优化）

4、字段类型规则（推荐）

• 使用tinyint来代替enum类型
• 尽可能不用text、blob类型
• 将字符转化为数字
• 存储 “abcd” 时 varchar(5) 比 varchar(10) 更优

5、索引规则（推荐）

• 选择自增列作为主键
• 单表索引数不超过5个、单个索引字段数不超过5个
• 字符串可使用前缀索引，前缀长度控制在5-8个字符
• 不在低基数列上建立索引，如：性别、是否删除、是否发布
• 不使用select * 优化成 select id,name,age……..
• 不在索引列进行数学运算、函数

6、SQL规范

• 避免隐式转换
• 避免使用存储过程、触发器、函数
• 避免进行数学运算
• 尽可能拆分大SQL

二、建立高效索引

目的：加速查询、加速排序、覆盖索引（只需要在索引中完成查询，不需要回到表中）

1、主键：和数据存储在一起。

• 通常选择自增列作为主键
• 优点：
  • a 顺序插入，不会出现数据页内数据移动的情况发生（插入更快）
  • b 数据存储更紧凑（查询更快）
• 缺点：
  • 多出4至8字节无意义的数据

2、二级索引：和数据分开存储

• 二级索引中是按照索引列+主键的对应关系进行存储的，每多一个索引就会多一个这样的对应关系。所以索引的个数越多，占用空间越大，在插入、删除的时候会越慢。

3、什么样的字段适合加索引？

• 首先，要满足主要功能的查询条件。
• 其次，要看该字段的唯一值多少。
• 唯一值： select count(distinct uid)/count(1) from table; 值越大，索引效果越好。

　　　type  ：建议优化的类型

 

• • • system           表只有一行
    • const              用到的是主键或唯一索引
    eq_ref            多表查询时，匹配到了1行，并且利用的是主键或唯一索引
    • ref                   匹配到了多行，通常是利用的普通索引（如果是联合唯一索引，只用到了其中1个也是这个类型）
    • ref_or_null     与ref类似，条件中用到了 null 的搜索

• • • all                   没有用到索引
    • 出现上面所列之外的类型时，如range、index等说明用到的索引性能很差

　　　 rows：

• • • 查询影响的行数，值越小越优。

　　    extra：　

• • • 查询的详细信息，类型包括：
    • using where、using index、using filesort等都是正常查询过程
    • using temporary 出现时，说明需要对sql或索引进行优化

二、优化SQL

• 需要多表查询时，内（外）连接查询不一定是最佳的方案，适当的采用子查询，会是更好的选择。
• 把 select * 换成部分字段，可少许降低查询时间
• 垃圾索引只会影响插入、删除效率，对查询速度影响较小。
• 字段唯一性太低，索引效率不高。
• 字段唯一性非常高，索引的性能会很优秀。
• 时间范围很大时，用不到索引。尽可能让时间范围有开口和闭口，区间也不易过大，根据数据量及最早时间来决定。