创建高性能索引

MySQL索引

索引是存储引擎用于快速找到记录的一种数据结构，如果没有索引，MySQL必须从第一行，遍历整个表进行查找，索引对于良好的性能非常关键，尤其是数据量越来越大时，索引对性能的影响越发重要。

索引的类型

索引有很多类型，可以根据不同的场景提供更好的性能，在MySQL中索引是在存储层而不是服务层实现的，所以没有统一的索引标准：不同存储引擎的索引的工作方式也是不一样的，不同的存储引擎支持的索引类型也都不是一样的。
我们先来看一下MySQL支持的索引类型（只讨论常用的InnoDB、MyISAM存储引擎中的索引）

B-Tree索引

一般我们在谈论索引时，如果没有直接指明类型，多半说的就是B-Tree索引，它使用B-Tree数据结构存储数据，大多数存储引擎都支持这个索引，（InnoDB使用的是B+Tree）
MyISAM和InnoDB都支持B-Tree索引，但是使用的方式确是不同的，MyISAM使用前缀压缩技术使得索引更小，但InnoDB按照元数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，InnoDB根据主键引用被索引的行。

（前缀压缩：myISAM压缩每一个索引的方法是，先保存索引的第一个值，然后将其他值和其比较，得到相同前缀的字节数和剩余不同的后缀部分，把这部分存储起来。例如：索引块的第一个值“act”，第二个值是“action”，那么第二个值的前缀压缩后存储的类似“3，ion”这样的形式）

使用B-Tree也意味着所有的值都是按顺序进行存储的，每一个叶子页到根的距离相同。
B-Tree索引能快速访问数据，因为存储引擎不需要在进行全表扫描，而直接从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据指针向下层查找。通过比较节点页的值和需要查找的值可以找到合适的指针进行下一层级。

索引的使用

• （1）列索引
  最常见的索引类型就是单列索引，将来自该列的值的副本存储在数据结构中（B-Tree），运行快速查询相对应的行，B-Tree让索引快速找到特定值、一组值或者值范围，对应于where子句中的=、>、<=、Between、In等运算符。每个表的最大索引数和最大索引长度是每个存储引擎定义的（InnoDB引擎：一个表最多1027列，最多包含64个二级索引）
  在查询时可以全值匹配（索引中所有的列进行匹配），也可以匹配列前缀（只匹配某一列开头的一部分）
  （2）多列索引
  Mysql中也可以创建复合索引（多列索引），一个索引最多包含16列。如果表具有多列索引，则优化器可以使用索引的任何最左边前缀进行查找，比如：你有一个三列的索引（col1、col2、col3），你的索引可以查找（col1）、（col1、col2）、（col1、col2、col3）
  （3）聚簇索引
  聚簇索引不是一种单独的索引类型，它是一种数据存储方式。InnoDB的聚簇索引是在同一个结构中保存了B-Tree索引和数据行，数据行存放在索引的叶子页中。

哈希索引

哈希索引是基于哈希表实现的，只有在精确匹配索引所有列的查询才有效。对每一行数据，存储引擎会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每一个数据行的指针。
哈希索引的特征
哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行。
哈希索引不是按照索引值顺序存储，所以也就无法用于排序。
哈希索引只能用整个键搜索，而无法像B-Tree索引使用最左前缀查找。
哈希索引只支持等值比较，不支持范围查询
哈希索引查询数据很快，除非哈希冲突（不同索引值有相同的哈希值），出现冲突存储引擎会全表遍历，逐行比较

索引的优缺点

索引可以让服务器快速定位，但也不是唯一的用处。
B-Tree索引，按照顺序存储数据，MySQL可以进行order by和group by操作
（1、大大减少服务器需要扫描的数据量 2、帮助服务器避免排序和临时表 3、可以将随机I/O变为顺序I/O）
但索引也不是最好的解决方案，对于比较小的表，大部分情况全局扫描更有效。对于特大型的表建立索引的代价也很大