mysql优化和索引

jsnjzlw

• 　　表的优化
  

　　1.定长与变长分离
　　如 int,char(4),time核心且常用字段，建成定长，放在一张表；
　　而varchar,text,blob这种变长字段适合单放一张表，用主键与核心表关联。
　　2.常用字段和不常用字段要分离
　　3.在 1 对多需要关联统计的字段上，添加冗余字段。

• 　　列类型选择原则
  

　　1.字段类型优先级  整型 > date,time > enum,char > varchar > blob,text
　　2.够用就行，不要慷慨，大的字段浪费内存，影响速度。
　　3.尽量避免用NULL,NULL不利于索引，需要特殊的字节来标注。

• 　　Enum列的说明
  

　　1.enum列在内部是用整型来存储的
　　2.enum列和enum列想关联速度最快
　　3.enum列比(var)char的弱势--在碰到与char关联时，要转化，要花时间
　　4.优势---当char非常长时，enum依然是整型固定长度，当查询的数据量越大时。enum的优势越明显。

• 　　索引优化
  

　　B-tree索引：myisam,innodb中默认使用B-tree索引。
　　hash索引：在memory表里，默认使用hash索引。
　　hash优缺点：1.查询时间复杂度为O（1）。
　　2.hash计算的结果是随机的，在磁盘上也是随机放置。
　　3.无法对范围查询进行优化；
　　4.无法利用前缀索引；
　　5.排序也无法优化；
　　6.必须回行，就是说，通过索引拿到数据位置必须回到表中取数据。
　　在where条件常用的列上都加上索引，若是独立的索引，同时只能用上1个。
　　若建立多列索引，index（a,b,c），注意和顺序有关,索引发挥作用，需要满足左前缀要求。
　　索引在where，order by，group by 中会用上（满足左前缀要求）。
　　多列索引在实际应用中更加实用，注意在建立多列索引的时候，要结合实际，创建合理的索引。
　　索引提高查询的速度、提高排序的速度、提高分组查询的速度。

• 　　聚簇索引和非聚簇索引
  

　　聚簇索引：btree
　　innodb引擎，索引和数据是在一块的，
　　innodb直接在主键索引的树中，储存行的数据，（既存储了主键值，又存储了行数据）。
　　innodb次索引指向对主键的引用。
　　优点：根据主键查询条目比较少时，不用回行（数据就在主键节点下）。
　　缺点：如果碰到不规则数据插入时，造成频繁的页分裂。
　　非聚簇索引：btree
　　myisam引擎， 索引和数据是分开的，
　　myisam索引指向行在磁盘上的位置
　　myisam中，主索引和次索引都指向物理行（磁盘位置）。
　　索引覆盖：
　　如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再找数据。
　　索引与排序

• 　　对于覆盖索引，直接在索引上查，就是有序的；
  
• 　　在innodb引擎上，沿着索引的字段排序，也是自然有序的，对于myisam引擎，按某索引字段排序，但取出的字段包含有不是索引字段，那么会先取出所有行，再进行排序。
  
• 　　先取出数据，形成临时表，做filesort文件排序（要尽可能避免）。
  
• 　　争取目标：取出来的数据本身就是有序的，利用索引来排序。
  

　　SQL语句优化

• 　　SQL语句的时间花在等待时间、执行时间，只有降低执行时间，那其他语句锁定的时间也减少了；
  
• 　　SQL语句的执行时间又花在查找、取出；
  
• 　　如何查询快？a)联合索引的顺序、区分度、长度 b)取得快，索引覆盖 c)传输的少，更少的行和列；
  
• 　　切分查询，将数据拆成多次，例：插入10000条数据，每1000条为单位插入；
  
• 　　分解查询，按逻辑把多表连接查询分成多个简单SQL
  　　总结：少查，尽量精准数据，少取行；必须要查，尽量走在索引上查询行；取时，取尽量少的列；
  
• 　　在group时，用带有索引的列来group，可以避免临时表和文件排序，速度会稍快些，另外用int型比char型分组，也要快些；
  
• 　　在group时，我们假设只取了A表的内容，group by的列，尽量用A表的列，会比B表的列要快。
  
• 　　order by的列要和group by 的列一致，否则也会引起临时表，因为两者都需要排序，如果两者的列不一致，那必须经过至少1次排序；
  

　　如何判断查询是否用到了索引？
　　查询方法：  explain  SQL  \G
　　extra字段:
　　using index： 指用到了索引覆盖，效率非常高；
　　using where：指光靠索引还定位不到，还得where判断一下
　　using temporary:指用上了临时表，group by 与 order by 不同列时或 group by 、order by 别的表的列时
　　using filesort:文件排序，文件可能在磁盘，也可能在内存。
　　子查询
　　1.from型子查询
　　注意：内层from语句查到的临时表，是没有索引的，所以from的返回内容要尽量少，需要排序，在内层先排好序。
　　2.in型子查询
　　mysql的查询优化器，针对in型优化，被改成exists子查询的执行效果，单行单行的执行过滤。当外层表越大时，查询速度越慢。可以用连接查询来代替子查询。
　　limit及翻页优化
　　limit offset，N
　　当offset非常大时，效率很低，因为mysql先取offset+N行，返回放弃前offset行，返回N行。
　　优化办法：
　　1）从业务上去解决，不允许翻过100页。例如百度，一般能翻到70多页。
　　2）不用offset，用条件查询(ID上有索引)

　　select * from tablename where>　　3）只查索引，不查数据，得到ID，再用ID去查具体条目(ID上有索引)，这种技巧就是延迟关联

　　select>
　　select>　　比下面直接查数据的方法快，

　　select>