mysql优化和索引
表的优化
成都创新互联是一家专注于网站制作、网站建设与策划设计,宣州网站建设哪家好?成都创新互联做网站,专注于网站建设十余年,网设计领域的专业建站公司;建站业务涵盖:宣州等地区。宣州做网站价格咨询:13518219792
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 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;
本文题目：mysql优化和索引
网页URL：http://6mz.cn/article/gispoe.html