目录

• 索引数据结构
• • 主键索引
  • 非主键索引
• 索引在什么时候是有效的？
• • 字符串比较大小
  • • btween and

索引数据结构

主键索引

   我们先来看看索引的数据结构，以及我们是如何利用索引来搜索数据的。MySQL的数据存储结构是B+树，在叶子节点存储了数据行，非叶子节点是主键索引。（MySQL的叶子节点是用双向链表链接的）

   在MySQL中，表的数据行只存在于主键索引的叶子节点；因此查询任何数据都必须要搜索到叶子节点。B+树是一个多路平衡搜索树可以看出整个树结构是有序的（搜索过程与二叉搜索树的搜索过程相似）；（PS：如果不了解B+树可以先了解下它的结构和特性）

  如果要搜索id =7的数据行它的搜索过程是怎样的呢？select * from test_a where id = 7;(id是表主键)

  因为搜索条件是id这是一个主键索引，因此在搜索数据时会直接利用B+树的结构做搜索。只用3次查找就可以找到id=7的数据行了。如果我们用年龄来做搜索条件呢？(select * from test_a where age = 78;)

  只能全表挨个搜索了，因为年龄字段的存储是乱序的没有办法像搜索id那样利用id的有序性来做搜索。搜索7次才找到明显比上面的搜索次数多。并且有索引的行搜索所有的数据行都是3次（id=1~10），效率是很稳定的；实际在利用age做搜索条件时会搜索整个表，因为不确定后面是否有age=78的行。在数据量很大的时候，明显能感受到有索引和没有索引的差距。因为利用没有索引的字段做搜索条件会搜索整个表，而有索引的字段只需要根据B+树做搜索搜索次数是成指数减少的。

  怎样让age作为搜索条件时，也可以减少搜索次数呢？给age建立一个B+树的结构—>建立索引。将表中所有行的age值用来建立一个B+树，那它的叶子节点也存储整个数据行吗？ 显然不能这样做，这样做太浪费空间了。MySQL的做法是叶子节点存储主键id的值，这样就可以跟数据行关联起来了。（问题1：为什么不直接存储数据地址呢，岂不是更方便直接？这个问题文章末尾再讨论）

非主键索引

  现在仍然用age做搜索条件：select * from test_a where age = 78；因为给age加了索引，因此搜索首先会搜索age的索引，也就是age值组成的B+树；

   当搜索找到了age=7的时候只能够拿到主键id的值，而我们需要的是整个数据行的值，因此还需要用id的值去搜索主键的索引（B+树）也就是所谓的返表。从这里看出，如果只需要id字段就不要使用select * 来查询了。（问题2：在实际操作的时候，很少会使用select * 但是有时侯就是需要查询其他字段比如 ：select id，name from test_a where age =78.这个时候该如何优化呢？有没有一种方法能够避免返表的操作呢？）

  虽然使用索引可能会导致查找2个索引，但是在数据量特别大的时候相比较于没有索引的搜索也会快很多；使用索引搜索查找的次数与不使用索引相比是呈指数减少的。（问题3：这里有一个问题，age的值可能有重复不是唯一索引，因此当搜索到叶子节点age=7的时候搜索还没有完成，MySQL会如何处理普通索引呢？是所有字段都适合建立索引吗？）

索引在什么时候是有效的？

  可以回想下，MySQL是如何利用索引进行搜索的？其实很简单，就是利用被搜索的字段值和B+树节点的值作比较，通过结果选择该搜索哪个分支。每搜索一层就会过滤掉其他分支减少搜索的次数，B+树就是通过这种方式来提高搜索效率的。（可以类比二叉搜索树的过程）简单来说，如果可以利用被搜索的字段值与B+树的节点值作比较，那就可以利用B+树来做搜索。如果被搜索字段不能与B+树的节点值比较，那索引就失效了。（理论上是这样的，但是MySQL还做了其他优化），要看是否走了索引可以用explain关键字来查看。

字符串比较大小

  这涉及到在MySQL中如何比较2个值的大小问题了。在MySQL中比较字符串，是取出字符串的每一个字符来分别作比较。与String类的compareTo方法比较字符串大小大的规则是一样的。都是从第一个字符开始比较大小：如果第一个字符分出大小就返回；如果相同继续比较后面的字符；

CREATE TABLE test_a(
id INT PRIMARY KEY ,
NAME VARCHAR(20),
age INT ,
gender char(1)
);INSERT INTO test_a VALUES(1,'qwe',12,'m');
INSERT INTO test_a VALUES(2,'wer',122,'m');
INSERT INTO test_a VALUES(3,'qa',23,'f');
INSERT INTO test_a VALUES(4,'we',44,'m');
INSERT INTO test_a VALUES(5,'wsx',67,'m');
INSERT INTO test_a VALUES(6,'dc',89,'m');
INSERT INTO test_a VALUES(7,'rfv',78,'m');
INSERT INTO test_a VALUES(8,'yhn',54,'f');
INSERT INTO test_a VALUES(9,'ikm',43,'f');
INSERT INTO test_a VALUES(10,'pol',32,'f');SELECT * FROM test_a ;CREATE INDEX index_age ON test_a(age);

btween and

  现在举几个例子判断一下索引是否失效:

 # test_a有一个主键id索引，age索引explain select id from test_a   where  age between 10 AND  100;explain select * from test_a   where  age between 10 AND  100;explain select * from test_a   where  age between 10 AND  40;

• 第一个使用了索引；explain select id from test_a where age between 10 AND 100;
  

• 第二个没有使用索引；explain select * from test_a where age between 10 AND 100;
  

• 第三个使用了索引；explain select * from test_a where age between 10 AND 40;
  

  前面2个SQL语句对比起来看：第一个sql为什么走了age的索引？第二个SQL不走索引？

  分析一下这2个SQL有什么不同？第一个SQL直接查询id，不会返表。第二个SQL查询所有字段，拿到id会返回主键索引查询。从这2条SQL的结果对比来看会得出一个结论：在索引上使用（between and 或者 age > mm and age

explain select *  from test_A where age != 67;
explain select id  from test_A where age != 67;