MySQL 索引策略

　　索引（Index）是帮助MySQL高效获取数据的数据结构，这种数据结构是需要额外的写入和存储为代价来提高表上数据检索的速度。一旦建立了索引后，数据库中查询优化器使用索引来快速定位数据，然后就无需扫描表中给定查询的每一行了。索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上。

聚簇索引与非聚簇索引

　　当使用主键或唯一键创建表时，MySQL会自动创建名为PRIMARY的特殊索引, 该索引称为聚簇索引。PRIMARY索引是比较特殊的，这个索引本身与数据一起存储在同一个表中。聚簇索引表中的数据按主键的顺序存放，它实际上就是按主键构建的一个B+树，叶子节点存放的是数据行记录。所以数据库中的数据实际上是索引的一部分。由于实际的数据页只能按照一个顺序存放，所以每张表聚集索引只能有一个。如果为表定义了一个主键，MySQL将使用主键作为聚簇索引；如果不为表指定一个主键，MySQL将第一个组成列都not null的唯一索引作为聚簇索引；如果InnoBD表没有主键且没有适合的唯一索引（没有构成该唯一索引的所有列都NOT NULL），MySQL将自动创建一个隐藏的名字为“GEN_CLUST_INDEX ”的聚簇索引。因此每个InnoDB表都有且仅有一个聚簇索引。

　　非聚簇索引的叶子节点中存放的是键值和主键值，所以通过非聚集索引需要先查找到主键值然后通过聚集索引查询到具体的数据，因此非聚集索引的效率要低于聚集索引。非聚集索引并不会影响到数据的存储顺序，所以非聚集索引可以存在多个。

索引原理

　　索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

　　B-Tree是为磁盘等外存储设备设计的一种平衡查找树。系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，在 MySQL 中可通过如下命令查看页的大小：show variables like 'innodb_page_size'。而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

　　【B树】

　　B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述 B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

　　每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。　

　　模拟查找关键字29个过程：

1.根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2.比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3.根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4.比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5.根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6.在磁盘块8中的关键字列表中找到关键字29。

　　【B+树】

　　B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

　　B+树特征：

1.非叶子结点中的关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。
2.所有的叶子节点包含了全部关键字信息，以及指向这些关键字记录的指针，且叶子节点本身依关键字的大小自小而大顺序链接
3.所有的非叶子节点可以看成是索引部分，节点中仅含有其子树的最大（或最小关键字）
4.通常B+树上有两个头指针，一个指向根节点，一个指向关键字最小的叶子节点

 　【总结】

B+树的优点在于：
1.由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
2.B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历，相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

B树的优点在于：
1.由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

索引失效

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
2、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因).要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
3、组合索引，不是使用第一列索引，索引失效。
4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
5、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
6、对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）
7、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。