五、索引策略

1 前缀索引

            前缀索引是一种争对与varchar类型或者text或者bolb类型中存储着相当大的数据量时所使用的一种索引类型，因为MySQL不支持对这些列的完整数据内容做索引，所以这个时候就可以针对内容数据的前几个字符来做前缀索引(注意，前缀是指从左到右)。注意，前缀索引无法使用order by 和 group by

2 多列索引和单列索引

            我们一般创建索引都是创建的多列索引。网上有一些建议说把每个where条件的字段都建立上索引，这种说法叫做 "单列索引" ，它是不严谨的，因为mysql有一种叫做 "索引合并" 的策略， 它在一定程度上可以使用表中的多个单列索引来定位指定的行。 在这种情况下， 查询能够同时使用两个单列索引进行扫描， 并将结果进行合并。 这种算法有三个变种： OR条件的联合（union） ， AND条件的相交（intersection） ， 组合前两种情况的联合及相交。

            这种策略有时确实可以优化性能，但是更多时候则是相反，因为如果优化器需要对多个单列索引进行相交或者联合操作时，通常是需要一个包含所有列的多列索引， 并且如果你有很多 OR 条件， 通常需要在算法的缓存、 排序和合并操作上耗费大量CPU和内存资源， 尤其是当其中有些索引的选择性不高， 需要合并扫描返回的大量数据的时候，更重要的是， 优化器不会把这些操作计算到“查询成本”（cost） 中， 优化器只关心随机页面读取。 这会使得查询的成本被“低估”， 导致该执行计划还不如直接进行全表扫描。 这样做不但会消耗更多的CPU和内存资源， 还可能会影响并发的查询， 但如果单独运行这样的查询则往往会忽略对并发性的影响。 通常来说， 使用UNION改写查询， 往往是最好的办法。

            所以我们需要在意的应该是多列索引的顺序问题

            多列索引的顺序问题需要特别关注，这里需要注意，优化器只会根据创建索引从左到右的顺序以此进行查询。这里涉及到MYSQL使用的B-Tree树的数据结构。

    3 聚簇索引

    4 覆盖索引

            如果一个索引包含所有需要查询字段的值（这里涉及到B-Tree的数据存储结构，因为B-Tree的叶子节点中是存放着索引值，注意，只有B-Tree索引的数据结构可以支持覆盖索引），那么这个索引就称为覆盖索引。（这里可以理解为如果你执行一条select a,b from table， 那么a列b列也都是有索引的，这时候因为叶子节点中是存放着a列b列的值，所以就可以直接从叶子节点上获取到值而不需要在去扫描数据行）

    5 使用索引扫描来做排序

            Mysql有两种方式进行排序，第一种就是全表的扫描排序，第二种就是通过索引进行排序。

            索引排序通常是比全表扫描快的，但是有一种特殊情况就是如果你需要的列没有索引(select a,b from table order by c， b不是索引列)，那么mysql就会每获取一个索引就去进行回表查询数据（通过a去回表查询b）， 这种情况其实索引排序是不如全表扫描快的。

             EXPLAIN的输出结果中， type列的值为“index”， 则说明MySQL使用了索引扫描来做排序

             只有当索引的顺序和ORDER BY子句的顺序完全一致， 并且所有列的排序方向（倒序或正序） 都一样时， MySQL才能使用索引来对结果做排序。 如果查询需要联接多张表，则只有当ORDER BY子句引用的字段全部在第一个表中时， 才能使用索引做排序。 

            ORDER BY子句和查找型查询的限制是一样的： 需要满足索引的最左前缀的要求， 否则， MySQL需要执行排序操作， 而无法利用索引排序。有一种特殊情况， 如果前导列为常量的时候， ORDER BY子句中的列也可以不满足索引的最左前缀的要求， 用索引做排序的另一个最重要的场景是，查询语句中同时有ORDER BY和LIMIT子句的情况

    6 冗余和重复索引

                MYSQL允许在相同的列上创建多个相同的索引，优化器在执行的时候会逐个评估使用哪个索引，这就会影响性能也会占用磁盘空间。

                重复索引就是指相同列相同顺序相同索引类型 

                冗余索引就是指例如有（A，B）索引，然后在创建（A）索引，这就叫冗余索引，因为（A）只是（A，B）的前缀索引，所以（A，B）的效果和（A）是一样的。这种所谓的冗余只适用B-Tree数据结构。同时冗余索引是建立在重复索引的基础上称为冗余，例如上边的例子中（A）和（A，B）的索引类型不用，那么也不叫冗余索引。

                冗余的索引越多，占用的磁盘空间就越大，而且也会影响插入、修改等磁盘IO操作。 可以使用Percona工具箱中的pt-duplicate-key-checker， 该工具通过分析表结构来找出冗余和重复索引

                 在删除或扩展索引的时候要非常小心。 回忆一下， 在前面的InnoDB的示例表中， 因为二级索引的叶子节点包含了主键值， 所以在列（A） 上的索引就相当于在（A， ID） 上的索引。 如果有像WHERE A=5 ORDER BY ID这样的查询， 这个索引会很有用。 但如果将索引扩展为（A， B） ， 则实际上就变成了（A， B， ID） ， 那么上面查询的ORDER BY子句就无法使用该索引做排序， 而只能用文件排序（文件排序就是全表扫描）了。

    7 未使用的索引

                有一些索引从创建以来一直都没有使用过，这类索引可以考虑删除，可以节省下磁盘空间的同时也提升磁盘IO的性能。注意，有一些索引是用来做唯一约束的，删除的时候需要结合实际情况。

                 找到未使用索引的最好办法就是使用系统数据库performance_schema和sys。 在sys数据库中， 在table_io_waits_summary_by_index_usage视图中可以非常简单地知道哪些索引从来没有被使用过

b-树

索引有很多种类型，可以为不同的场景提供不同的性能。在mysql中，索引是在存储引擎层实现的