MySql索引的添加与使用

索引的优缺点:

优点:大大的优化了查询的速度,使查询的时候可以减少全表扫描的操作,转而扫描索引,对于速度的提升十分大。
缺点: 1.没有limit限制的时候是会全表扫描的,索引没用。
      2.对于重复率特别高的字段的效果很差,如:sex等字段。
      3.有些表的内容很少,对于全表扫描来说根本就没有压力,加上索引甚至会大大提高update/insert/delete执行所需的时间。
对于索引来说当数据量达到一定程度的时,索引的字段就要慎重来选择了,为新增的数据增加索引的时间会随着数据量的增加而增加,毕竟新增数据后整个索引表都会重新排序。

索引的创建准则

1.经常会当成where条件查询的字段,往往是表中的外键,与其他表关联的字段。
2.主键会自动创建索引,所以搜索的时候尽可能通过主键。
3.如价格一般的需要范围搜索的字段,索引建立的时候都已经排好序了,可以一段段的取。
4.如时间一般的排序字段,但一般表中只有生成时间的话最好还是使用主键排序,速度会很快。
5.对于bit这种选择性很小(如果订单状态)或者text这种数据量特别大(如文章内容,但中文分词可以使用,不过如果项目够大建议使用搜索引擎)的不建议加索引。
6.对于那些经常性的增删改的表来说,假如数据变动的太频繁而检索需求却不是那么的频繁,那么增加索引的话对修改性能造成很大的影响,所以这种情况下尽可能的少加或不加索引。

索引的种类

1.主键索引
2.唯一索引
3.普通索引
4.外键索引
5.全文索引
6.复合索引
前两个,主键索引和唯一索引可以看做是同一个索引都属于唯一性的索引,在数据创建或更改的时候,数据库都会检查唯一性索引列的数据冗余性,如果发现数据冗余,那么数据库会取消该条数据的增改操作并返回错误。
第三个普通索引会加在经常出现的查询和排序字段上,最好选择整型的数据来添加,可以加快对数据的访问速度。
第四个外键索引就是外键的索引,外键只在InnoDB表可用并且必须加索引(可以为普通、主键、唯一,如果不设置会自动为普通索引),外键可以操作多个表的同时删除与改动,外键索引为加速这一过程的索引,但是外键对于数据库的性能有很大的影响,并不建议使用。
第五个全文索引,在mysql5.5之后的版本中InnoDB表支持全文索引,全文索引可以大大的加快搜索的速度,具体使用可以参考后期使用全文索引优化搜索的文章。
第六个复合索引,当一个索引创建在同表的多个列上,那么这个索引就是复合索引,最多可以将16个列合在一起,在复合索引中,多个列的前后顺序十分重要,顺序不同就不属于同一个索引,在表中有多个关键的列的时候,多种条件查询的时候可能会十分有用,可以减少该表中的索引数量,但是多个列的数据类型要尽量相同。

聚簇索引(innodb数据表才有)

数据表的物理顺序与索引顺序相同,也就相当于顺序存放,如同挂的日历一样有十二页,想看一月的日历就在第一页,看第二月的就在第二页,是有顺序排列的索引就是聚簇索引,如果该表没有聚簇索引,那么该表的数据是一种无序结构的堆数据。
在聚簇索引中,表中的数据所在的数据页是叶级,在叶级之上的索引页是非叶级,索引数据所在的索引页是非叶级。在聚簇索引中,数据值的顺序总是按照升序排列。每一个表只能有一个聚簇索引,因为表中数据的物理顺序只能有一个;表中行的物理顺序和索引中行的物理顺序是相同的,在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了表中行的物理顺序,数据行按照一定的顺序排列,并且自动维护这个顺序;关键值的唯一性要么使用UNIQUE关键字明确维护,要么由一个内部的唯一标识符明确维护,这些唯一性标识符是系统自己使用的,用户不能访问;聚簇索引的平均大小大约是数据表的百分之五,但是,实际的聚簇索引的大小常常根据索引列的大小变化而变化;在索引的创建过程中,一定要保证有足够的空间来创建聚簇索引。
从聚簇索引的特性来看,主键索引属于聚簇索引(没有主键时,会用一个唯一且不为空的索引列做为主键,成为此表的聚簇索引)。在mysql查询数据的时候会在相应的查询条件列上查找是否有索引和该索引对于查询是否有意义。如果该列有索引并且有意义,那么查询就使用索引来查询所需要的结果,而不会去全表扫描。

非聚簇索引(除了主键索引,都是非聚簇索引)

非聚簇索引并不是在物理上排列数据,即索引中的逻辑顺序并不等同于表中行的物理顺序,索引是指向表中行的位置的指针,这些指针本身是有序的,通过这些指针可以在表中快速定位数据。就像是字典一样,首先通过偏旁或拼音查目录,然后通过目录再找到实际的字。
非聚簇索引有两种体系结构:一种体系结构是在没有聚簇索引的表上创建非聚簇索引(没有主键的表中创建索引),另一种体系结构是在有聚簇索引的表上创建非聚簇索引(有主键的表中创建索引)。
如果一个数据表中没有聚簇索引,那么这个数据表也称为数据堆。数据堆是通过使用索引分配图(IAM)页来维护的。IAM页包含了数据堆所在簇的存储信息。在系统表sysindexes中,有一个指针指向了与数据堆相关的第一个IAM页。系统使用IAM页在数据堆中浏览和寻找可以插入新的记录行的空间。这些数据页和在这些数据页中的记录没有任何的顺序并且也没有链接在一起。在这些数据页之间的唯一的连接是IAM中记录的顺序。
当非聚簇索引创建在有聚簇索引的表上的时候,系统使用索引页中的指向聚簇索引的聚簇键。聚簇键存储了数据的位置信息。如果某一个表有聚簇索引,那么非聚簇索引的叶级包含了映射到聚簇键的聚簇键值,而不是映射到物理的行标识符。当系统访问有非聚簇索引的表中数据时,并且这种非聚簇索引创建在聚簇索引上,那么它首先从非聚簇索引来找到指向聚簇索引的指针,然后通过使用聚簇索引来找到数据。

对数据表数据查找

查找访问数据有两种方式:
    1、表扫描:系统将指针放置在该表的表头数据所在的数据页上,然后按 照数据页的排列顺序,一页一页地从前向后扫描该表数据所占有的全部数据页,直至扫描完表中的全部记录。在扫描时,如果找到符合查询条件的记录,那么就将这 条记录挑选出来。最后,将全部挑选出来符合查询语句条件的记录显示出来。
    2、索引查找:索引是一种树状结构(B树,B-树,B+树等),其中存储了关键字和指向包含关键字所在记录的数据页的指针。当使用索引查找时,系统沿着索引的树状结构,根据索引中关键字和指针,找到符合查询条件的的记录。最后,将全部查找到的符合查询语句条件的记录显示出来。
了解了索引的方式,又了解了数据表的数据查找,现在你应该很明白索引的重要性了吧,并且也应该可以根据业务来选择对关键列加上准确高效的索引。

索引的维护

因为一个表会经常的进行增删改操作,所以索引会变得碎片化,所以要经常对于大表的索引进行维护,这些内容在以后的文章内会提到,或者大家也可以去百度查一下索引的维护来了解。

附加:

myisam索引:因为myisam的索引和数据是分开存储存储的,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,所以当myisam表的查询无法命中索引的时候会很慢。
innodb索引:innodb的数据和索引放在一起,当找到索引也就找到了数据。
自适应哈希索引:innodb会监控表上的索引使用情况,如果观察到建立哈希索引可以带来速度的提升,那就建立哈希索引,自适应哈希索引通过缓冲池的B+树构造而来,因此建立的速度很快,不需要将整个表都建哈希索引,InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。自适应哈希索引不需要存储磁盘的,当停库内容会丢失,数据库起来会自己创建,慢慢维护索引。

create by 任庆彬