mysql 存储引擎 innodb 学习复习 之 索引

innodb中有两种索引，B+树索引和哈希索引，其中hash索引是自适应性的，存储引擎会根据表的使用情况自动创建哈希索引，不能人为的干涉

1.  聚集索引

聚集索引，innodb存储引擎表是索引组织表，即表中的数据是按照主键顺序存放，而聚集索引是按照每张表的主键构造出一颗B+树，并且叶子节点存放的是整张表的行记录数据，因此，叶子节点是数据节点，并且因为B+树的特点，数据节点通过一个双向的链表连接起来，使得查找更加快速，由于实际的数据页只能按照一颗B+树来存放，所以，一张表只能拥有一个聚集索引，在多数情况下，查询优化器倾向于采用聚集索引。

实验

先创建一张表 t ，这里以人为的方式让其每个页只能存放两行数据

 create table t (a int not null primary key,b varchar(8000));

然后插入3条数据

insert into test.t select 2,repeat('a',7000);

insert into test.t select 3,repeat('a',7000);

insert into test.t select 4,repeat('a',7000);

　　然后使用书中的分析，利用py_innodb_info.py工具分析，

这里创建的是一颗如下的B+树

聚集索引并不是按照物理顺序存储的，只是逻辑上的连续

聚集索引的好处： 对于主键的排序查找和范围查找速度非常的快  例如：要查找一个注册用户表中最近注册的10个用户，可以通过查找最后一个数据页，并取出10条记录，范围查找：如果要查找某一个范围的数据，通过页节点上层的中间节点就可以得到页的范围，之后读取数据页。

2. 非聚集索引

对于非聚集索引，页级别上不包含行的全部数据，页节点除了包含键值之外，每个页级别的索引中还包含一个书签，该书签可以用来告诉存储引擎，在哪里可以找到与索引相对应的行数据，因为innodb存储引擎是索引组织表，所以书签就是相应行数据的聚集索引键。

相比于聚集索引，一个数据库表中可以创建多个非聚集索引，当通过非聚集索引来查询数据时，innodb会遍历非聚集索引并通过叶子节点获得一个指向主键索引（聚集索引）的一个指针，然后通过主键索引查找到相应的行数据，这比聚集索引需要更多的IO。

在上面的实验中的表 中添加一个字段int c

   然后执行：update t set c = 0-a;

添加索引 alter table t add key idx_c(c);

这是得到的是一个聚集索引和非聚集索引结合的树

还可以创建一个列的开头部分：alter table t add key idx_b(b(100));

还可以添加一个联合索引 ：alter table t add key idx_a_b (a,b);

可以使用 drop index idx_a_b on  t;  删除索引

可以使用 show index from t;  查看点钱的index

B+树索引的使用

在访问表中很少一部分行的情况下使用B+树索引才有意义，对于像性别字段只有男女两种情况的，完全没有必要，对于取之范围广的像姓名，使用B+树索引可以达到很好的效果

3. 顺序读，随机读和预读取

顺序读是指顺序的读取磁盘上的block，随机读指访问磁盘上的块是不连续的，需要磁盘的磁头的不断转动，在数据库中，顺序读指的是根据索引的叶子节点数据就能顺序的读到所需的行数据，随机读是指需要访问非聚集索引和聚集索引两个共同才能读到所需的数据

为了提高读取的性能，innodb中引入了预读取技术，通过一次的io请求将多个页读到缓冲池中，有两种方式，随机预读取和线性预读取，但预读取在实际测试中效果不是很好，从innodb plugin 1.0.4后，随机访问只保留了 线性的预读取，并且加入了innodb_read_ahead_threshold

4. 辅助索引（非聚集索引）优化

因为辅助索引的页节点包含有主键，但是辅助索引的页中并不会包含完整的行信息，innodb总是会先通过辅助索引看是否能找到所需的数据

创建表：

         create table t2 (a int not null,b varchar(20),primary key(a),key(b));

插入4行数据：

insert into t2 select 1,'hello';

insert into t2 select 2,'world';

insert into t2 select 3,'what';

insert into t2 select 4,'areyou';

然后使用 select * from t2; 查询的结果是：

4	areyou
1	hello
3	what
2	world

这是因为辅助索引中包含了主键a的值，因此访问b列上的辅助索引就能得到a的值，然后就得到了整张表中的数据，并且通常情况下，辅助索引能够存放的数据比主键页上存放的更多，因此优化器选择了辅助索引

当 解释这条 sql语句时：

explain select * from t2;得到

使用的是索引b，那么如果想对a排序，可以对a进行order by或者强制使用 主键来得到结果

explain  select * from t2 order by a;    或者 select * from t2 force index (primary);

使用的是 主键

 explain select * from t2 force index (primary);但其实它未使用主键，没有使用！

5. 联合索引

联合索引指的是可以在多个列上添加索引

alter table t add index idx_a_b(a,b);

联合索引的好处是： 1）对于类似select * from t where a = 1 and b = 'aaaaaaaaaaaaaaaaaaaaaaaaa...'的查询和select * from t where a = 1这样的也可以使用（a，b）索引，但是对于b就不能使用了 2） 可以对第2键值进行排序

实验

创建表

create table buy_log (userid int unsigned not null,buy_date date);

插入数据

insert into buy_log values(1,'2009-01-01');
insert into buy_log values(2,'2009-01-01');
insert into buy_log values(3,'2009-01-01');
insert into buy_log values(1,'2009-02-01');
insert into buy_log values(2,'2009-02-01');
insert into buy_log values(3,'2009-03-01');
insert into buy_log values(1,'2009-04-01');

　　

添加索引：alter table buy_log add key (userid);   alter table buy_log add key (userid,buy_date);

如果只对 userid 查询， explain select * from buy_log where userid = 2;

在我的电脑上使用的是userid_2： 我想 应该是os的关系，可能在win7中是这样的，与上面的辅助索引类似

在Linux中（ubuntu）中得到的是书中所说的结果：

对于查询explain select * from buy_log where userid = 1 order by buy_date limit 3;

使用的也是 userid_2;因为在这次优化中buy_date已经拍好序了