mysql优化和索引

• 表的优化

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中，主索引和次索引都指向物理行（磁盘位置）。

索引覆盖：

如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再找数据。

索引与排序

1. 对于覆盖索引，直接在索引上查，就是有序的；

2. 在innodb引擎上，沿着索引的字段排序，也是自然有序的，对于myisam引擎，按某索引字段排序，但取出的字段包含有不是索引字段，那么会先取出所有行，再进行排序。

3. 先取出数据，形成临时表，做filesort文件排序（要尽可能避免）。

4. 争取目标：取出来的数据本身就是有序的，利用索引来排序。
   

SQL语句优化

1. SQL语句的时间花在等待时间、执行时间，只有降低执行时间，那其他语句锁定的时间也减少了；
   

2. SQL语句的执行时间又花在查找、取出；

3. 如何查询快？a)联合索引的顺序、区分度、长度 b)取得快，索引覆盖 c)传输的少，更少的行和列；

4. 切分查询，将数据拆成多次，例：插入10000条数据，每1000条为单位插入；

5. 分解查询，按逻辑把多表连接查询分成多个简单SQL

   总结：少查，尽量精准数据，少取行；必须要查，尽量走在索引上查询行；取时，取尽量少的列；

6. 在group时，用带有索引的列来group，可以避免临时表和文件排序，速度会稍快些，另外用int型比char型分组，也要快些；

7. 在group时，我们假设只取了A表的内容，group by的列，尽量用A表的列，会比B表的列要快。

8. 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 id>1000000 limit 2;

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

select id,xxx,xxx from tablename inner join (

select id from tablename limit 1000000,2 ) as tmp using(id);

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

select id,xxx,xxx from tablename limit 1000000,2;