Mysql：索引的使用
索引在多数数据库程序中都担当着重要的角色，是优化数据库性能的重要手段
其实它们的大致原理都相同
使用索引:
- 过多的索引或让插入/修改操作变得缓慢，并且让数据文件更快到达系统限制
- InnoDB表可以通过设置多个文件来避免系统限制
- 索引列一般是出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列
- 索引列的重复几率最好小于30%
- 索引列尽可能选择较小的数据列，能更快的进行比较，减少I/0的操作
- 对与字符串类型的索引，可以通过设置“前缀”来提高索引速度，设置索引时同时设置前缀长度，来提高对字符型的检索速度
- 复合索引（联合索引），有“最左前缀”的效果，如果3个数据列在查询时都是按重左到右的顺序建立，可以设置复合索引，此索引等同于为3个数据列添加索引
复合索引
假设你在表的state、city和zip数据列上建立了复合索引。
索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。
这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：
- state, city, zip
- state, city
- state
MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。
例如，如果你按照city或zip来搜索，就不会使用到这个索引。
如果你搜索给定的state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的
尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围
索引类型
mysql支持4种类型，B树索引，散列索引，空间(R-Tree)索引，全文(Full-text)索引
- 全文(Full-text)索引，是MyISAM的一个特殊索引类型，主要用于全文检索
- 空间(R-Tree)索引，MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY(几何学)
- 散列(Hash)索引，MySQL 中只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。
- Memory存储 引擎支持非唯一hash索引，这在数据库领域是罕见的，如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中
- Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。
- 由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。
- 不能使用hash索引排序。
- Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。
- Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。
- B树索引，MyISAM默认提供的索引类型，索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询
- 匹配全值(Match the full value)：对索引中的所有列都指定具体的值。
- 匹配最左前缀(Match a leftmost prefix)：
- 匹配列前缀(Match a column prefix)：
- 匹配值的范围查询(Match a range of values)：
- 匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：
- 仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。
由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。当然，使用B-tree索引有以下一些限制：
- 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。
- 不能跳过某一索引列。
- 存储引擎不能使用索引中范围条件右边的列。
聚簇索引(Clustered Indexes)
聚簇索引保证关键字的值相近的元组存储的物理位置也相同
所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作
且一个表只 能有一个聚簇索引。
因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。
InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。
如果不存在这样的索引，InnoDB会定义 一个隐藏的主键，然后对其建立聚簇索引。
覆盖索引(Covering Indexes)
如果索引包含满足查询的所有数据，就称为覆盖索引。
- 索引项通常比记录要小，所以MySQL访问更少的数据；
- 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；
- 大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
- 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。
覆盖索引不能是任何索引都支持，只有B-TREE索引存储相应的值。
而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。
索引进行排序
利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排 序操作。
MySQL 中，有两种方式生成有序结果集：
- 使用filesort
- 按索引顺序扫描
当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序。
如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引。其它情况都会使用filesort。
当 MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序
如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是 外排序）。
对于filesort，MySQL有两种排序算法。
(1)两遍扫描算法(Two passes) ：
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，
然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。
注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。
(2)一次扫描算法(single pass) ：
该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。
注： 从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。
如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要 的内存。
注：在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。
当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。
为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法
所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。
当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，
然后进行连接处理，此时，EXPLAIN输出“Using filesort”；
否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出 “Using temporary;Using filesort”。
索引与加锁
索引对于InnoDB非常重要，因为它可以让查询锁更少的元组。
这点十分重要，因为MySQL 5.0中，InnoDB直到事务提交时才会解锁。有两个方面的原因：
- 首先，即使InnoDB行级锁的开销非常高效，内存开销也较小，但不管怎么样，还是存在开销。
- 其次，对不需要的元组的加锁，会增加锁的开销，降低并发性。
InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。
但是，只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。
一旦索引不允许InnoDB那样做（即达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，
此时，已经无法避免对那些元组加锁了：InnoDB已经锁住那些元组，服务器无法解锁了。
以上是Mysql的索引使用需要注意的事项，在索引类型和符合索引上，大多数数据库的原理都是一致的，也是索引优化中最关键的思考点
