mysql 重新整理——索引简介[七]

前言

百度百科索引:

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。
当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

索引是一种数据结构，是一种排好序能快速查找的数据结构，利于排序和查询。

正文

索引其实就是已经排好序的数据结构，因为已经排序好的东西，所以有很多规律，那么这个时候就可以通过算法来实现更加高效的查找。

比较深入一点的解释就是:

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

举个例子哈:

上面这个图，可以看到，创建的索引如左边这样。

如果数据结构如右边这样。如果是要找到77,那么可以查34，发现大于32，然后找右边，然后找89，发现小于，然后再左边找到了77。

如果77对应了2，那么这个2就是主键的值了，那么就用主键的值进行回表，查到到真实的数据。主键其实也是一颗树，但是主键对应的是真实数据。

也就是说查找两颗树，然后就可以找到数据了。当然上面是b-树哈。在innordb中是b+树，后面再介绍。

然后这里也说明了，为什么我们在开发的时候为什么去做逻辑删除，而不怎么做物理删除，其实原因也较为简单，一个是要有查询记录，后面可以查到这条数据怎么没的，当然这个也可以写日志表。

还有一个原因，那么就是说如果删除了数据，那么索引会发生变化，那么就得去改索引数据结构了。

也就是说索引其实是可以单独出来作为一个数据库的，它里面有独立的增删改查，一般是查。

因为索引单独出来数据库的，随着表的数据的数量增加，那么索引也是会增加的。

那索引是不可能全部存在内存中的，要是全部存在内存中一个是内存不够，第二个是要有一个持久化的功能，不然一关机就炸了。

索引的好处:

1.提高是数据的检索效率，降低数据库的IO成本。

2.以为索引是已经排好序的，所以用索引去排序，降低数据排序的成本，降低cpu的消耗。

索引的劣势:

1.索引是安装某种数据格式存储的结构，也可以理解为另外一张表，该表保存了主键与索引字段，并指向实体表记录，索引列消耗空间。

2.如果更新数据的时候，索引是需要更新的，会降低表的更新速度。因为是等索引更新完了，然后才会表去插入成功，才算一次成功，保持他们的一致性。

3.需要dba去维护索引，因为索引建立是变化的，dba需要找出最优索引。

那么什么时候用索引呢?

1.主键建议唯一索引，这个就不用说了，因为一般找会用到主键。

2.频繁的查询条件的字段应该创立索引。

3.查询中与其他表关联的字段，外键关系建立索引。

4.查询中的排序字段，排序字段如果通过索引去访问将大大提高排序速度。

5.查询中统计或者分组字段

不合适索引：

1.频繁更新的字段。

2.不作为查询的字段，也就是不写在where的字段。

3.表记录不多

4.数据重复且分布平均的表字段，如果某个数据列包含许多重复的内容，为他创建索引效果不大，比如性别

如果一个表2000条数据，索引列为1980个不同值，那么索引的选择性为:1980/2000,这个值越接近1，效率越高。

索引物理分类:

1. 聚集索引

2. 非聚集索引

索引逻辑的分类:

单值索引:单列作为索引，一个表可以有多个单值索引。

唯一索引：索引的列的值必须唯一，单允许有空值。就是索引列的每一个都完全不一样，也就是说100个人没有一个长的一样的。

复合索引:一个索引包含多个列。

索引语句，建议5个索引:

创建:

create [unique] INDEX indexname ON table(columname)

alter table add unique indexname ON (columname)

删除:

drop index indexname on table

查看:
show index from tablename

这样就可以查看到索引字段了。

non_unique 是否是唯一字段、。
seq_in_index 表示联合索引的顺序
Collation 指的是列以什么方式存储在索引中，A表示升序，B表示降序，NULL表示未排序
cardinality 是基数的意思，表示索引中唯一值的数目的估计值。我们知道某个字段的重复值越少越适合建索引，所以我们一般都是根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，那就需要重新评估这个字段是否适合建立索引。
Sub_part 前置索引的意思，如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。

Null 如果列含有NULL，则含有YES。

Index_type 表示索引类型，Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

Comment Index_comment 注释的意思。

mysql 索引结构:

1.Btree 索引

2.hash 索引

3.full-text 全文索引

4.R-Tree 索引

用Btree 举例:

盗图:

1.浅蓝色的块为一个磁盘块

2.深蓝色表示数据项

3.黄色部分表示指针

比如说查找29。

那么每次都是从磁盘块一加载。

对比17-35。

然后29>12 所以不是p1,然后29在26和30之间，然后根据p2指向磁盘块3，然后加载磁盘块3，一共3次io。

再进行重复的操作，找到29。

值得注意的是，真实的数据存放在叶子节点中，就是下面那一排，所以没有说去找8这个的说法。

非叶子节点不存储真实数据，只存储搜索方向的数据项，如17和35并不是指正的指向数据表某个数据。

下一节

索引优化