MySql索引笔记

  这篇文档仅仅只是对mySql中索引作日常笔记，不讨论索引的分类/概念等。本文简单介绍下聚集索引和非聚集索引。聚集索引和非聚集索引不是索引类型，而是针对数据存储引擎而言的，介绍完聚集索引和非聚集索引后，将对索引使用进行总结。
聚簇索引和非聚簇索引的区别是:
  聚簇索引（innobe）的叶子节点就是数据节点
  非聚簇索引（myisam）的叶子节点仍是索引文件，只是该索引文件中包含指向对应数据块的指针。

聚簇索引

  对于InnoDB引擎来说，是按照聚簇索引的形式存储数据。它的每个聚簇索引的叶子节点都包含主键值、事务ID、回滚指针(用于事务和MVCC)以及余下的列。InnoDB的二级索引和主键索引也有很大的不同：二级索引存放的是主键值+索引字段，这是为了减少了移动数据或者分裂时维护二级索引的开销，因为不需要更新索引的行指针。
聚集索引的数据查询有两种情况：

1. 查找主键上的某一行：直接按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 查询二级索引上的某一行数据：首先通过二级索引查找到的是主键值；再根据主键值在主键索引中查找到相应的数据文件

  InnoDB引擎是必须设置主键的，需要依赖主键生成聚簇索引，因此当没有指定主键的时候，InnoDB引擎会默认寻找一个可以唯一标识每行数据的列作为主键，当这种列不存在的时候，会默认生成一个6字节整型的隐藏列作为主键

非聚簇索引

  对于MyISM引擎来说，使用的是非聚簇索引。MyISAM的二级索引存放的还是列值和行号的组合， 叶子节点中保存的是指向物理数据的指针，因此它的主建索引和二级索引的结构并没有任何区别，只是说主键索引的索引值是唯一且非空的，而MyISAM引擎可以不设置主键。表数据存储在独立的地方，主建索引和二级索引的两颗B+树的叶子节点使用同一个地址指向表数据；由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引和非聚簇索引比较

  对于非聚簇索引来说，每次通过索引检索到所需行号后，还需要通过叶子上的磁盘地址去磁盘内取数据（回行）消耗时间。为了优化这部分回行取数据时间，InnoDB 引擎采用了聚簇索引。聚簇索引，即将数据存入索引叶子页面上。对于 InnoDB 引擎来说，叶子页面不再存该行对应的地址，而是直接存储数据：这样便避免了回行操作所带来的时间消耗。 使得 InnoDB 在某些查询上比 MyISAM 还要快！
  在二级索引方面， InnoDB 与 MyISAM 有很大区别：
  看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，真是这样的吗？答案是否定的。

1. 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2. 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置会随着数据库里数据的修改而发生变化，使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
3. 关于查询时间，一般认为 MyISAM 牺牲了功能换取了性能，查询更快。但事实并不一定如此。多数情况下，MyISAM 确实比 InnoDB 查的快 。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等，使得 InnoDB的叶子页面实际上还包含有事务id（换句话说就是版本号） 以及回滚指针。

索引使用注意项

1. 索引不会包含有NULL值的列
     只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。因为索引上根本没Null值，不能利用到索引，只能全表扫描(将索引列值进行建树，其中必然涉及到诸多的比较操作，null值不会像其他取值一样出现在索引树的叶子节点上)。所以我们在数据库设计时不要让字段的默认值为NULL。
2. 使用短索引
     对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3. 索引列排序
     MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4. like语句操作
     一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5. 不要在列上进行运算
     例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。
6. 不适合键值较少的列（重复数据较多的列）
     假如索引列某个字段有10个键值，如果有10万条数据，那么 WHERE TYPE = 1将访问表中的10000个数据块。如果全表扫描，假设100条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块少一些，肯定就不会利用索引了。
7. 尽量少用or，即使其中有条件带索引也不会使用；要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
8. 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系