MYSQL索引与查询优化

MYSQL索引与查询优化

一、什么是索引

1、简单定义

索引通过各种数据结构实现值到行（值=》行）位置的映射，没有索引会全表扫描；

2、索引的作用

（1）提高访问速度； 
（2）实现主键、唯一键逻辑；

——（2.1）在计算机世界了，磁盘的IO（读写性能）是常见的性能瓶颈，它与内存IO/CPU的IO根本不在一个量级上。一般机械硬盘的随机读写效率在120/s~150s/s；也就是每秒只能做120到150次的响应，因此计算机里的IO是宝贵的资源。
索引通过各种数据结构的实现可以减少磁盘IO的消耗，提高了访问速度。
——（2.2）数据库里有主键或唯一索引时，每次新插入数据都要验证新增记录是否在数据库里存在，这里的验证其实就是一个select操作。对主键建立索引可以大大提高这种验证查询的效率，因此数据库不管是mysql还是oracle都会对主键默认建立索引（聚集索引）。

3、索引类型（数据结构）

索引类型是指实现索引的具体数据结构，mysql的索引类型主要包括：Btree索引、hash索引等,重点是B-tree索引

（1）Btree索引

MYSQL里的Btree索引，实际上是B+tree索引，是在Btree上（平衡二叉树）的改良的。 
 
Btree上每个节点只有一个值，而B+tree的每个节点上可以保存很多个值，且值是按照索引键值顺序来排序的，这是因为innodb每次读取数据节点的数据块是以page（页，16k）为单位读取数据，如果尽可能多的将数据值放入一个page可以极大减少IO次数。

B+tree的层高不多，例如图上层高是3，也就是做查询最多会查三次数据页（page）也就是三次IO操作，就能查到数据的rowID，然后根据rowID获取到整个数据行信息。

例子：有表t1(id,name)，是innodb存储类型，其中id是主键，name上有索引；
【查询-1】：select * from t1 where id=1000;
由于查询条件字段是主键id，主键是聚簇索引，innodb存储类型的表都是以索引组织表存储的，也就是将主键作为键值把整个表建立成一个索引，也就意味着，整个表的所有    数据的物理存储都是按照主键的存储顺序来排序的（叶子节点根据主键顺序，依次存储数据行记录）。可以想象整个数据表就是一个B+tree结构，查询键是主键，叶子节点是数据行，假设B+tree层高为3，则select * from t1 id=1000;语句只要三次IO操作（查找三个page）就能找到id=1000的记录。 
【查询-2】：select * from t1 where name='chenjing';
由于查询条件字段不是主键但是有建立索引，可以想象：有一个B+tree树，查询键是name字段值，叶子节点是rowID。select * from t1 where name='chenjing';这个语句要先根据name字段查询索引，获取到rowID，在根据主键查找表结构的B+tree，根据主键查找到数据记录行。

补充：innodb存储引擎表，没有显示定义主键，也没有非空的唯一索引，那么innodb存储引擎会自动创建一个隐藏的主键。 
这个自动创建的隐藏主键是6字节大小的指针，范围大小有限，只可以到21亿 如果到达了21亿后不会报错， 会重新生成就会把之前的数据覆盖掉；且这个隐藏主键是无序的聚集索引。也就是说不显示定义自主主键，指望innodb表自动创建隐藏主键，在数据插入时是随机写入。没有主键有唯一索引的插入，也是随机插入。只有显示定义自增主键，插入才是有序插入。

（2）innodb的表是以索引组织表形式存储的，MYISAM的表是以堆表形式存储

innodb存储类型的表都是以索引组织表形式存储的，也就是整个数据表的存储都是B+tree结构的，其中查询键是主键（如果没有显示定义自主主键，就用不为空的唯一索引来做聚簇索引，如果也没有唯一索引，则innodb内部会自动生成6字节的隐藏主键来做聚簇索引），叶子节点存储了完整的数据行信息（以主键+row_data形式存储）。

因为innobd表是索引组织表，也就是B+tree结构的，因此如果innodb表有自主主键，则数据写入是有序写入的，效率会很高；如果innodb表没有自增的主键，那插入的话就是随机插入。这也是为什么会建议innodb表要有无业务意义的自增主键，可以大大提高数据插入效率。

a、索引组织表：索引组织表（IOT）数据存储是把表按照索引的方式存储的，数据是有序的，数据的位置是预先定好的，与插入的顺序没有关系。
b、堆表：堆表（heap table）数据插入时存储位置是随机的，主要是数据库内部块的空闲情况决定，获取数据是按照命中率计算，全表扫表时不见得先插入的数据先查到。
c、索引表的查询效率比堆表高（相当于查询索引的效率），插入数据的速度比堆表慢（索引组织表如果是有自增主键，插入就是有序插入；如果没有自增主键，则插入是无序插入）。

4、聚簇索引和二级索引（非聚簇索引，一般索引）

在《数据库原理》里面，对聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正式因为如此，所以一个表最多只能有一个聚簇索引。 
（1）聚簇索引：主键就是聚簇索引，将主键作为键值把整个表建成一个索引。也就意味着，整个表的所有数据的物理存储都是按照主键的索引顺序来排序的（叶子节点根据主键顺序，依次存储数据行）； 
（2）二级索引：除了主键以外，用户创建的其他索引，也是B+tree结构，键值就是建立了索引的那个字段，叶子节点的值（指针指向的）其实就是主键，一般查询会先根据二级索引（建立了二级索引的字段）查找对应的主键，再根据主键查找数据表的B+tree，找到对应的记录行数据。

二级索引并不是说层高只有两层，二级索引也就是非聚簇索引，和聚簇索引相区别。 
建立索引是有代价的，在增删改操作时都可能要更新索引的B+tree来保证树的平衡，导致每一个索引行的更新都变成了一个内部事务，索引越多，事务越长。因此索引不是越多越好；

【聚簇索引和非聚簇索引的区分】
聚簇索引和非聚簇索引，这是索引的两种类型。在聚簇索引中，索引的叶子节点包含实际的数据，记录的索引顺序和物理顺序相同。主键就是聚簇索引，innodb类型的表就是以B+tree形式存储，叶子节点包含的是完整的记录和行数据。
非聚簇索引也即是一般索引（包括唯一索引），又叫二级索引。非聚簇索引中，叶子节点指向的是表中的记录rowID，记录的物理顺序和逻辑顺序没有必然联系。
总结：
主键是聚簇索引，也是唯一索引；但是唯一索引不是聚簇索引。
非聚簇索引也就是二级索引，是除了主键外，用户创建的其他索引，包括唯一索引。唯一索引不是聚簇索引，聚簇索引是唯一索引。

5、索引的优势

（1）减少查询IO; 
（2）优化等值查询或者范围查询；也即是范围查询和等值查询都是会走索引的； 
（3）利用有序特性（例如：order by/group by/distinct/max/min等函数）；这些操作都是利用排序技术来实现的，而索引天然就是有序的，因此使用到order by等有序操作时，对相关字段建立索引会提高效率。

6、如何用好索引

（1）依据where查询条件建立索引； 
（2）使用联合索引，而不是多个单列索引； 
例如：select * from tab_a where b=? and c=?这个SQL，对b c字段建立联合索引的效率比单列的索引效率更高。 
（3）联合索引中索引的顺序根据区分度排，区分度大的放在前面。区分度是指字段值的种类，字段值种类越多的字段要放在前面，例如：idx_smp(name,gender)的效率要比idx_smp(gender,name)的效率高 
（4）联合索引能为前缀单列、复列查询提供帮助； 
例如： 
有idx_smp(a,b,c)这样的索引，where a=?或者where a=? and b=?都可以使用该索引，但是where c=?就无法使用该索引。 
（5）同样的，要合理创建联合索引，避免冗余 
例如建立了idx_smp（a,b,c）就不需要建立idx_smp(a)、idx_smp(a,b)索引了。 
（6）order by group by distinct等需要排序的操作，在没有索引的大数据量情况下需要排序，对IO和CPU性能消耗很大。如果有类似排序需求，则需要对相关字段建立索引，这样利用索引的有序特性不需要排序，直接按着索引顺序扫描即可。 
（7）select …where .. like ‘%xx’;这种%放在头部的，是无法走索引的。 
（8）select ＊ 不建议使用，因为会读取大量数据，也不利于使用索引覆盖技术。索引字段能够完全在索引中获取， 就不要使用select ＊（因为会导致回表），无法完整在索引中获取，也是建议select具体字段。

7、查看是否使用了索引：explain命令查看

（1）explain是确定一个查询如何走索引的最简便有效的方法；

（2）关注的字段值：

——id字段：表示查询中执行select子句或操作表的顺序。 
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中， id值越大，优先级越高，越先执行。 
——type字段：查询access的方式； 
type=all表示全表扫描数据，不走索引； 
type=index表示full index scan，和all的区别是index类型只遍历索引树。 
——key字段：本次查询最终选择使用哪个索引，NULL表示未使用索引； 
——key_len字段：选择的索引使用的前缀长度或者整个长度（判断联合索引的使用情况）； 
——rows字段：可以理解为查询逻辑读，需要扫描过的记录行数； 
——extra字段：额外信息，主要指的fetch data的具体方式； 
extra=using tmporary表示mysql需要使用临时表来存储结果集，常见于排序和分组查询。 
extra=using filesort表示文件排序，需要对其优化。mysql中无法利用索引完成的排序操作称为“文件排序”。 
using tmporary可能是内存临时表也可能是磁盘临时表，如果临时表大小超过tmp_table_size大小才会产生基于磁盘的临时表，也就是说，只是通过explain执行计划是无法查看是否用来磁盘临时表的，如果show processlist查看的线程有“Created_tmp_disk_tables”关键字才能代表是用使用了磁盘临时表

（3）explain的一些使用建议：

（3.1）对不确定执行计划的关键语句上线前务必explain； 
（3.2）type为all的要格外注意，避免全表扫描； 
（3.3）key_len只能用很少一部分前缀的，要注意索引字段顺序等； 
（3.4）extra里看到using filesort和using tmporary都要尽量优化，这两种fetch方式不应该出现在任何执行频繁的关键语句中。

（4）强制使用索引hint：

select * from table_1 force index(xxx)… 
select * from table_1 ignore index(yyy)…. 
默认情况下，建议使用mysql优化器，不要强制所用或忽略索引

转载：http://www.cnblogs.com/zhoubaojian/articles/7866249.html