小白学习mysql之高新能索引基础篇

索引在数据库中的地位是及其的重要，同时要想完全的掌握索引并不是一件容易的事，需要对数据的查询原理以及计算机操作系统有深刻的认识，当然相关的算法和数据结构也是必须的。因此，这篇文章感到了一些压力，不过还是决定先拿出来总结一下，理一理索引，就当做学习笔记了。

索引的重要习性犹如一本字典的拼音检索和部首检索部分，想象一下你买了一本只有正文的字典，那该有多么抓狂。而且在一个软件系统中，通常数据的查询与修改往往占到了10:1的比例，也就是我们需要将大部分的精力投入到数据的查询上，其中很多工作是用来提升查询的速度的，那么在这个过程中索引就扮演者非常重要的角色。

索引的实质

如果说一本字典的正文内容的实质是纸张的话，那么它前面按照拼音或者部首检索的索引的实质是什么呢!当然也是纸张了，可能有人觉得是废话，当我们同时类比到索引上就可以知道，一个数据表的实质是数据文件（即文件），那么索引的实质也当然是文件了，Mysql的InnoDB的数据表中的索引就是表空间的一部分。因此，初学者可以把索引完全想象成为一本字典，一本字典就是一个数据表，正文部分呢，就是这个数据表的详细内容，按照拼音和部首的检索都是索引。 当我们通过索引查找一条数据项的时候就犹如通过拼音索引查找某一个字，当在索引中查找到那个字的时候，会根据右边对应的页码找到那个字的解释，同样，MySQL的索引工作原理也是如此，每个索引项都有一个建索引列的关键字和一个指向该数据项的指针（类似字典中的页码），当我们查找到那个目标关键字时，根据指针便可以直接定位到数据表中该关键字的位置。

但现实中的真是数据表并不像字典那样，内容都是严格按照拼音序列排列的，因为有很多不确定的新内容要插入或者要进行其他操作，所以字典只是一种特殊的数据表，并不能把所有的数据表都拿来和字典比，这里用字典来类比只是为了对索引的实质有一个立体的认识。

索引是如何实现快速查找的

假设A公司有1024名员工（员工编号1-1024），早上的出勤人数为1023次，现在想知道员工编号为8的小明是否出勤，如何通过早上的签到表快速的查询小明是否出勤呢！在没有索引的情况下，通常的做法是从第一条记录逐个向后查找，如果小明最后一个来或者没有来，那么就需要查找1023次，效率为O(N)。也许会觉得1023次查找对现在的计算机根本不算什么，但是对于很多系统动辄都是上千万的数据记录，你可以想象查找的时间，比如你用15分钟终于通过了qq的身份校验登陆上了qq是一种怎么样的体验。因此，索引就呼之欲出了，我们如何通过索引来提高一个数量级的查找效率，这个时候就需要对这1024条数据做些什么了，在每条记录插入的时候，可以根据每条记录的员工编号和存储该记录的地址（指针）建立一个二叉查找树，这样1023条记录便可以通过10次查找便可查到，查找效率足足提高了100倍。

同样，为了效率的提升，也付出了一些代价，因为建立二叉查找树需要额外的存储空间，同时每次插入数据的时候需要对二叉查找树进行维护，减缓了数据的更新速度。但综合来考虑，一般认为这样做是值得的。

当然在MySQL中，不是采用的二叉树查找树来完成索引的存储的，上面举得例子只是为了说明索引的工作过程，但其思想是相同的。MySQL中没有特殊说明的话，一般说的索引指的就是B-Tree索引，采用B-Tree这种数据结构是综合了计算机操作系统以及组成而综合考虑的。其核心思想主要是减少磁盘的IO次数，提高查询速度。

如何理解聚簇索引

你只需记住它的名字叫聚簇索引，它不同于其他普通的索引！聚簇索引不仅仅是一种索引，更是一种存储方式，InnoDB中将B-Tree索引和数据行存储在一个数据结构中，意味着什么呢？这意味着数据行即索引，索引即数据行，它们是在一起，在一起，在一起的。

接着通过来建立字典的例子来理解聚簇索引和其他普通索引的区别，现在要建立一本中华字典，这个时候字典是空的，要我们造一本字典出来，首先我们要完成字典的正文，我们按照汉语拼音的顺序去组织字典的正文，第一个是“安 an ”，放在一个位置，同时后面附加上注释(这里，“an”就相当于聚簇索引的关键字，而后面的注释就是数据行，它们存在一起)，第二个是“王 wang”字，通过和“安 an”字比较，拼音顺序靠后，所以放第二个。注意这里的放是指存储在磁盘中的位置，可以理解为存储顺序。第三个字是“小 xiao”字，通过拼音顺序，需要将第三个字存在第一个字“安”后面，那“王”字已经占了磁盘页面的位置，所以它需要向后面移动，如果移出该列，就导致了也分裂，所以可以看到聚集索引的更新代价真的很大，那为什么还要这样做呢？聚簇索引带来了那些好处？

......，假设按照上面方法已经建立好了字典的正文内容，这个时候就可以让我们体会一下聚簇索引的好处了，假如你要找“安 an”字，根据拼音序列，你一定知道它一定在前几个页面，所以直接可以翻到前面，同时你也可以找到拼音“an”所对应的所有的汉字，这就是聚簇索引带来的好处，同时聚簇索引是和数据行放在一起的，你不需要在根据索引里的指针找到对应的数据行，然后翻页找到（翻页就相当于磁盘IO），这些都是效率的提升。然而，上面也看到了聚簇索引的负面影响比如插入的时候，因此它就像一般利剑，用的合适效率提升，用的糟糕也会带来很大的不好影响。

是时候区别一下非聚聚索引了，这个时候我们又按照部首建立了一个索引，那么按照部首建立的索引就是非聚簇索引，它单独的存在在字典的前几个页面，而且同一个部首的字所对应的页码也是没有顺序的，如果我们要山字旁所对应的所有汉字，那就要取很多个页面的值，导致很多随机IO产生，同时不能很好利用计算机存储系统的缓存系统，因此效率远没有聚簇索引高。

InnoDB中聚簇索引产生的原则：

• 当有主键时，主键为聚簇索引
• 当没有主键时，引擎会选择一个唯一的*非空*列来作为聚簇索引
• 如果没有以上两种情况的列，引擎会隐式的定义一个主键作为聚簇索引

最后，通过建立一个数据表来感受聚簇索引，首先建立有一个没有主键也没有唯一非空列索引的数据表，即聚簇索引是系统隐式生成这种情况。这种情况，一般就是按数据插入的先后顺序进行排列。

CREATE TABLE user_log (


user_id INT NOT NULL,


place VARCHAR(20) NOT  NULL DEFAULT '',


login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP


 


) DEFAULT CHARSET utf8;

接下来插入三条数据：

> INSERT INTO user_log(user_id,place)


VALUES('1','中国');


> INSERT INTO user_log(user_id,place)


VALUES('2','中国');


> INSERT INTO user_log(user_id,place)


VALUES('1','美国');

然后，选择所有列（即按照存储顺序查看数据列），果然如我们猜想，是按照插入顺序存储的：

>   SELECT * FROM user_log;


 


user_id place   login_time


1   中国  2015-11-20 10:32:41


2   中国  2015-11-20 10:33:18


1   美国  2015-11-20 10:33:18

接下来，我们添加一个唯一的索引（ID+login_time），此时 系统引擎应该将此索引作为聚簇索引，因此我们再次插入新的数据的时候是按照ID聚簇存储的，就是说ID相同的会存储在一起，存储在同一个页面，甚至连续的几个页面。 下面首先添加这个唯一索引 并接着添加三条新的记录：

> ALTER TABLE user_log ADD UNIQUE KEY U_USER_LOG_ID_LOGIN_TIME(user_id,login_time);


 


> INSERT INTO user_log(user_id,place)


VALUES('1','法国');


> INSERT INTO user_log(user_id,place)


VALUES('2','日本');


> INSERT INTO user_log(user_id,place)


VALUES('1','韩国');

然后接着查看数据的存储情况：

>   SELECT * FROM user_log;


 


user_id place   login_time


1   中国  2015-11-20 10:32:41


1   美国  2015-11-20 10:33:18


1   法国  2015-11-20 10:48:00


2   中国  2015-11-20 10:33:18


2   日本  2015-11-20 10:48:00