索引

索引在关系型数据库中是用于加速数据访问，实现数据化快速定位的一个数据结构。如我们购买一本书我们首先回去看目录而准确地翻到对应的书页。目录标题就是一种索引方式。在数据库中索引的存在不能太多，应为对数据化进行插入删除修改等DML操作是除了除了记录本身还需要维护索引，当数据基数增大，那这个的维护索引的成本也是在增大，所以执行这些操作的性能消耗也在增大,索引的存在减少了服务器扫描的数据量，避免使用filesort和临时表，把随机IO变成顺序IO。

索引类型

哈希索引

顾名思义，哈希索引基于哈希表来实现，适用于精确匹配-等值查询，索引值存了对应field的哈希值和数据行的指针，所以结构紧凑占用小，针对哈希值冲突基于拉链法来解决。哈希槽是连续分布，但是映射的数据行是离散的，所以对范围查询支持极差，同时离散的数据存储对于排序也无发走索引，还有不支持部分索引匹配。针对数据列（name，age）的哈希索引，单纯查询name也无法走索引。在InnoDB存储中，除了可手动指定索引为哈希索引，还有基于查询的数据的预热程度来增加自适应哈希索引，他会在内存中增加一个基于B-tree索引的一个哈希索引。但是这个是无法让用户感知到的，完全是MySQL系统内部处理，如果我们需要一个这样一个辅助查询，但是也不想直接设置为哈希索引导致失去范围查询，和排序的诉求，可以冗余一个数据列来存放对应的字段的哈希值，然后再查询时候匹配即可。如果数据量较大吗，那出现冲突的几率会增大，所以再查询是可以加上该字段的原值比较。SELECT (X,X,X) FROM TABLE_NAME WHERE XXX='DDD' AND HASH_XXX='HASH(111111)'

B-Tree

B-Tree是按照顺序来存储的，所以可以用来排序和分组，给予B-Tree来实现，是为了实现磁盘查找的一刻平衡查找树。在数据查询和删除时候，对应的叶节点会进行页分裂或者旋转来保证平衡。在MyISAM和InnoDB存储引擎中都有其身影，主要是聚簇索引和普通索引，普通索引就可以直接理解为一个B-Tree。叶节点保存了索引值。叶子节点保存了对应行的主键。而聚簇索引来讲只是一种数据存储方式，在Innodb当中聚簇索引的实现方式是一个B-Tree和数据行，所以和普通索引的区别就在于叶子节点存放的整个数据行，而innodb存储引擎默认基于数据主键来实现聚簇索引，如果没有主键则会基于一个唯一非空的额数据列实现，什么都没有则会局域数据行的RowID来隐式实现聚簇索引，聚簇索引是让同一个页内的数据行实现聚簇，但是夸数据页则不支持。所以对于聚簇索引的查询而言，首先访问一个数据页可以把相关的数据都获取到，同时根据主键查询无需其他消耗，可以直接定位到数据行返回数据，由于聚簇的处理，当插入数据的速度和插入顺序相关联，插入按照主键顺序插入则较快，不按照主键顺序插入则会很慢。亦或是当进行数据导入的时候，插入没有按主键顺序插入，数据页分布比较稀疏，随意即便是针对主键的查询，最坏情况也会导致全表扫描，所以在导数据的操作执行后最好执行一下OPTIMIZE TABLE,在业务地方使用哦会导致锁住整张表。还有不管是聚簇索引还是普通索引，都会在数据的插入时候发生页分裂(当前页存放的数据记录不满足，需要提升或者下降对应的节点，来满足当前数据页的存放)。聚簇索引在MyISAM存储引擎中主要是一个命名为PRIMARY的唯一非空索引。解决主键的插入顺序的问题，在InnoDB中直接使用主键是整数的AUTO_INCREMENT自增列，同时要避免一些字符串主键，如UUID这样的方式。不仅是占用

索引策略

如何可以被称为一个优秀的索引，需要满足下列这个三个要求。第一这个索引可名称对应的数据行，第二这个索引要匹配到对应的索引，第三这个索引的索引列可以满足返回要求的数据列。

要把索引对应的数据列放在where条件的一侧，拒绝使用上述的错误方式。

要避免在索引列使用逻辑计算

错误:select ad_id from ad_table where ad_id+1=5;
FIX:select ad_id from ad_table where ad_id+1=5;

要避免在索引列使用函数计算

前缀索引

如果针对某个特殊的字符进行检索，可以使用上述对齐使用hash索引，还可以从开始位置索引其一部分字符，这样可以节省空间，但是也会降低索引的选择性，就是降低SHOW INDEX FROM TABLE_NAME的返回中cardinality值。这个代表不重复索引行的个数。这个值的意义在于和整体数据行的比例越接近1，则代表根据索引选择数据的行数越多，性能越高。但是使用了前缀索引之后则会导致无法使用排序和分组,也无法使用索引覆盖。

联合索引

面试的时候常见的灵魂拷问，比如要查询表的a,b,c字段，你怎么建立合适的索引，答案五花八门，又说每个都加上索引也有说建立三个字段的联合索引。每个都加上索引这估计是听了某专家的语录给我把WHERE条件之后的查询列都加上索引,这样建立方式顶多满足优秀索引的第一个要取，可以命中数据行。。。反而增加了数据DML的维护，熟悉专家了。不过针对单个索引的联合查询或者相交查询，在Innodb5.0以后默认开启了索引合并,优化器会对这样的查询进行索引合并操作，使用union（a,b）处理，这也就是之前说老版本为什么要用union操作来代替or查询，or会导致全表扫描。老版本没这个功能啊（手动狗头），真相了！但是索引合并的开启也会导致优化器的查询成本增大，最后还不如你直接sql就改用union来处理。真香！再说建立三个字段的联合索引，这个没错，但是顺序要怎么处理呢在，这里才是见真章的地方。第一种来讲经验法则，针对单纯的where查询把cardinality的值最高的放到最前面没问题，当需要考虑分组和排序的时候，则需要看这个常用的排序字段和分组字段来根据其顺序来建立索引。

覆盖索引

怎么说呢！覆盖索引就是在查询的时候就返回对应的数据列，在获取数据时候不需要进行回表查询，同时针对指定字段的检索返回的数量也是较小，也侧面提升了查询效率，可以让范围查询按照索引的顺序访问。只有B-Tree索引可以支持索引覆盖。

当查询的过滤数据列不存在任何索引字段，无法使用覆盖索引查询，还有使用模糊查询的时候如果不是最左前缀模糊查询的话同样也无法走覆盖索引查询。对应like查询而言这个是存储引擎的API限制。最左匹配可以在索引内做字符串比较。如果是通配符起手则需要全表扫描，然后过滤。避免无使用索引字段和模糊查询导致无法查询的处理就可以使用延迟关联来处理，比如对这些字段增加联合索引，基于子查询内连接来满足联合索引的顺序，从而实现查询走索引。

利用索引优化排序

当索引的列顺序和ORDER BY 的顺序是一样的，那么就会避免使用filesort，还有就是ORDER BY的时候也需要要求对应的数据列的顺序，要按照索引的顺序才可以。比如在表t中有这样的KEY(a,b,c)索引，再发起查询 select a,b,c from t where a='2' order by b,c 这样也会利用索引来优化排序，因为索引顺序的前导列为常量。如果排序和索引的顺序不一致，则会导致filesort 例如 select a,b,c from t where a >'4' order by b DESC,c 还有在where条件和order by 条件的查询从中缺少联合索引的字段也是无法使用索引排序，例如 select a,b,c from t where a >'4' order by c看似有a,c 但是这样的不满足联合索引的顺序索引页无法使用还有即便是满足索引的顺序，如果其中的字段是范围查询，在查询的时候也会导致filesort。

索引冗余处理

比如查询查询是我们使用联合索引来进行索引覆盖，但是统计的时候却由于字段不满足联合索引而导致性能降低，这时候可以增加一个单独列的索引来实现，这就是冗余处理，在数据表的字段设计上也有这样反范式的处理。同时要注意的是，我们都知道在二级索引上有主键，索引默认就有了一个（二级索引，主键）的一个联合索引所以在类似select * from t where a='3' order by id 这样也会避免filesort。

范围查询和排序优化

可以使用in查询来处理范围查询

索引和锁

索引可以让锁锁定的行更少，针对innodb的行锁，行存在锁行，不存在则会锁住表，如果对应的查询走索引，则会锁定被索引区域的数据行，在过滤完之后释放，在早起的数据库中只有事务提交才会释放锁。还有就是在访问普通索引会使用共享锁，而读取主键索引则会使用排它锁。

总结

把握索引策略的三个要求来建立索引，可以更合理的实现我们的业务数据查询、如果需要查询和排序要考虑联合索引，如果考虑不同场景的统计和查询，需要参考反范式的设计。索引设计并没有对应的银弹。需要在设计是针对应数据列在整体数据行中的cardnality的值。