MySQL非聚簇索引&&二级索引&&辅助索引

mysql中每个表都有一个聚簇索引&＃xff08;clustered index &＃xff09;&＃xff0c;除此之外的表上的每个非聚簇索引都是二级索引&＃xff0c;又叫辅助索引&＃xff08;secondary indexes&＃xff09;。

以InnoDB来说&＃xff0c;每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键&＃xff0c;该主键索引是聚集索引。如果你不定义为您的表的主键时&＃xff0c;MySQL取第一个唯一索引&＃xff08;unique&＃xff09;而且只含非空列&＃xff08;NOT NULL&＃xff09;作为主键&＃xff0c;InnoDB使用它作为聚集索引。如果没有这样的列&＃xff0c;InnoDB就自己产生一个这样的ID值&＃xff0c;它有六个字节&＃xff0c;而且是隐藏的&＃xff0c;使其作为聚簇索引。

聚簇索引和聚集索引(Clustered Index)

说起索引&＃xff0c;不能不说B&＃43;树。

引用&＃xff1a;http://blog.codinglabs.org/articles/theory-of-mysql-index.html

MySQL官方对索引的定义为&＃xff1a;索引&＃xff08;Index&＃xff09;是帮助MySQL高效获取数据的数据结构。提取句子主干&＃xff0c;就可以得到索引的本质&＃xff1a;索引是数据结构。

我们知道&＃xff0c;数据库查询是数据库的最主要功能之一。我们都希望查询数据的速度能尽可能的快&＃xff0c;因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找&＃xff08;linear search&＃xff09;&＃xff0c;这种复杂度为O(n)的算法在数据量很大时显然是糟糕的&＃xff0c;好在计算机科学的发展提供了很多更优秀的查找算法&＃xff0c;例如二分查找&＃xff08;binary search&＃xff09;&＃xff0c;二叉树查找&＃xff08;binary tree search&＃xff09;等。如果稍微分析一下会发现&＃xff0c;每种查找算法都只能应用于特定的数据结构之上&＃xff0c;例如二分查找要求被检索数据有序&＃xff0c;而二叉树查找只能应用于二叉查找树上&＃xff0c;但是数据本身的组织结构不可能完全满足各种数据结构&＃xff08;例如&＃xff0c;理论上不可能同时将两列都按顺序进行组织&＃xff09;&＃xff0c;所以&＃xff0c;在数据之外&＃xff0c;数据库系统还维护着满足特定查找算法的数据结构&＃xff0c;这些数据结构以某种方式引用&＃xff08;指向&＃xff09;数据&＃xff0c;这样就可以在这些数据结构上实现高级查找算法。这种数据结构&＃xff0c;就是索引。

MySQL就普遍使用B&＃43;Tree实现其索引结构。

聚簇索引并不是一种单独的索引类型&＃xff0c;而是一种数据存储方式。具体的细节依赖于其实现方式&＃xff0c;但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表有聚簇索引时&＃xff0c;他的数据行实际上存放在索引的叶子页&＃xff08;leaf page&＃xff09;中。术语 “聚簇”表示数据行和相邻的键值紧凑地存储在一起&＃xff08;这并非总成立&＃xff09;。

因为无法同时把数据行存放在两个不同的地方&＃xff0c;索引一个表只能有一个聚簇索引。

注&＃xff1a;叶子页面包含完整的元组&＃xff0c;而内节点页面仅包含索引的列(索引的列为整型)。一些DBMS允许用户指定聚簇索引&＃xff0c;但是MySQL的存储引擎到目前为止都不支持。InnoDB对主键建立聚簇索引。如果你不指定主键&＃xff0c;InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引&＃xff0c;InnoDB会定义一个隐藏的主键&＃xff0c;然后对其建立聚簇索引。一般来说&＃xff0c;DBMS都会以聚簇索引的形式来存储实际的数据&＃xff0c;它是其它二级索引的基础。

 

索引组织表(Index Organized Table, IOT) 

其实和聚簇索引说的是一个意思。

索引组织表&＃xff08;Index organized table, IOT&＃xff09;就是存储在一个索引结构中的表。与堆组织表无序存储不同的是&＃xff0c;IOT中的数据按主键存储和排序。

相比堆组织表&＃xff0c;索引组织表能够节省一部分空间&＃xff0c;因为使用堆组织表时&＃xff0c;我们必须为表和表的主键上的索引分别留出空间。而IOT则可以省去主键索引的开销&＃xff0c;因为数据就是按顺序存储的&＃xff0c;可以当做索引使。换句话说&＃xff0c;如果你只会通过一个表的主键来访问这个表&＃xff0c;这个表就适合创建成索引组织表。

举例&＃xff1a;

 1.一个客户有很多地址信息&＃xff0c;客户是一个表&＃xff0c;客户地址信息是另外一个表。读取一个客户地址信息的时候&＃xff0c;如果这个客户的所有地址信息都存放在相邻的地方&＃xff0c;读取速度就会快一些。这个时候&＃xff0c;客户地址信息表适合创建成IOT。

2. 经常查看一支股票的最近几天的信息&＃xff0c;股票信息一般是千万级别的数据&＃xff0c;如果能够把最近几天的信息存放在一起就会快很多。

&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;END&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;

如下除主键外都是二级索引&＃xff0c;或叫做辅助索引。

"hljs markdown">"hljs-quote">"hljs-quote">> show create table article "hljs-strong">"hljs-strong">*****"hljs-strong">"hljs-strong">*****"hljs-strong">"hljs-strong">*****"hljs-strong">"hljs-strong">***** 1. row "hljs-strong">"hljs-strong">*****"hljs-strong">"hljs-strong">*****"hljs-strong">"hljs-strong">*****"hljs-strong">"hljs-strong">******        Table: article Create Table: CREATE TABLE "hljs-code">"hljs-code">&＃96;article&＃96; (   "hljs-code">"hljs-code">&＃96;id&＃96; int(11) NOT NULL AUTO_INCREMENT,   "hljs-code">"hljs-code">&＃96;title&＃96; varchar(255) NOT NULL,   "hljs-code">"hljs-code">&＃96;shortName&＃96; varchar(255) NOT NULL,   "hljs-code">"hljs-code">&＃96;authorId&＃96; int(11) NOT NULL,   "hljs-code">"hljs-code">&＃96;createTime&＃96; datetime NOT NULL,   "hljs-code">"hljs-code">&＃96;state&＃96; int(11) NOT NULL,   "hljs-code">"hljs-code">&＃96;totalView&＃96; int(11) DEFAULT NULL,   PRIMARY KEY ("hljs-code">"hljs-code">&＃96;id&＃96;),   UNIQUE KEY "hljs-code">"hljs-code">&＃96;idx_short_name_title&＃96; ("hljs-code">"hljs-code">&＃96;title&＃96;,"hljs-code">"hljs-code">&＃96;shortName&＃96;),   KEY "hljs-code">"hljs-code">&＃96;idx_author_id&＃96; ("hljs-code">"hljs-code">&＃96;authorId&＃96;) ) ENGINE&＃61;InnoDB AUTO_INCREMENT&＃61;6 DEFAULT CHARSET&＃61;latin1 1 rows in set

&＃xff1d;&＃xff1d;&＃xff1d;&＃xff1d;&＃xff1d;&＃xff1d;&＃xff1d;END&＃xff1d;&＃xff1d;&＃xff1d;&＃xff1d;&＃xff1d;&＃xff1d;&＃xff1d;