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

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页