mysql优化

建表原则:

  定长与变长分离:

    int,char(4),time,每个单元值占的字节是固定的。核心且常用字段,宜建成定长,放在一张表,这样查询时磁盘能很快定位到目标位置

    varchar,text,blob属于变长字段。变长或不常查询字段,适合当放一张表,用主键与核心表关联起来

  常用字段与不常用字段分离

  需要关联统计的字段上,添加冗余字段

列类型选择:

  字段类型优先级:

    整型>date,time>enum,char>varchar>blob,text

  够用就行,不要太多

  尽量不用NULL:

    不利于索引

索引:btree索引/hash索引,索引是可以高效查询的数据结构(对应二叉查找树和hash表)

  btree有利于范围查找

  hash用于内存索引中,磁盘中位置难以固定所以不用,无法针对范围优化

  索引是独立的,同时只能用一个

  在多列上创建索引,即联合索引,效率更好,对多列都起作用

  聚簇索引(innodb引擎,健壮的事务型存储引擎):索引包含有数据,两者在一起,主键索引包含数据,次级索引指向主键索引

    优点:主键查询时,不用回行去找数据,数据就在主键节点下,取出时是有序的

    缺点:碰到不规则数据插入会频繁分页,挂载大量数据时会慢

  非聚簇索引(myism引擎,无法处理事务):索引指向数据,但不在一起,联合索引每个索引相互独立

  索引覆盖:联合索引,需要的数据就包括在索引中(联合中的一部分),不用回行,extra:use index

理想的索引:

  查询频繁

  区分度高:100w个性别,只有男女两类,区分度低

  长度小:索引长度影响索引文件大小,影响增删改的速度

    区分度和长度:从左到右从列中截取做索引,截的越短,重复度越高,区分度越低,截的越长,重复度越低,区分度越高,和区分度矛盾,要取得平衡

    手法:截取不同长度,测试区分度,区分度0.1就可以接受

    对于左前缀不易区分的列,可以把它倒过来,或者把列编码

  尽量能覆盖常用查询字段

  多列索引:注意查询频率,注意长度,注意区分度,注意实际场景

索引碎片与维护:

  长期的数据更改中,索引文件和数据文件都会产生空洞,形成碎片

  可以通过nop操作,即不对数据产生实际影响的操作来维护

  optimize table 表名 可以用来修复

  修复会把所有数据重新整理一遍,非常耗时,所以不能频繁,可以按周,按月为周期

sql语句优化:

  sql语句花的时间:等待时间和执行时间,要降低执行时间

  执行时间:查找(按索引,或者全表扫描),取出(查到行后取出数据)

  查询快:联合索引的顺序,区分度,长度

  取得快:索引覆盖,取更少的列

  切分查询:对于特别大的查询,按数据拆分成多次

  分解查询:按逻辑把多表连接查询分成多个简单sql

  优化思路:不查->少查->高效的查(走索引),少查少取

  强制使用索引:use index

limit offset, N ,翻页优化:是取出全部offset+N,再舍去offset部分,所以offset大时效率低

  措施:限制翻页最大值,走索引

  

  

  

 

posted on 2017-08-02 17:37  zawjdbb  阅读(96)  评论(0编辑  收藏  举报

导航