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 * 换成部分字段,可少许降低查询时间
  • 垃圾索引只会影响插入、删除效率,对查询速度影响较小。
  • 字段唯一性太低,索引效率不高。
  • 字段唯一性非常高,索引的性能会很优秀。
  • 时间范围很大时,用不到索引。尽可能让时间范围有开口和闭口,区间也不易过大,根据数据量及最早时间来决定。

 

posted @ 2018-03-07 23:15  K战神  阅读(206)  评论(0编辑  收藏  举报