4.B-tree索引和hash索引

1.B-tree索引

（1）特点

　　1.通过B+树的结构存储数据；

　　2.b-tree索引能够加快数据的查询速度；

　　3.b-tree索引更适合进行范围查找。

（2）使用场景

　　1.全值匹配查询where order_sn=’877465354897’

　　2.匹配最左前缀查询

　　3.匹配列前缀查询

　　4.匹配范围值的查询

　　5.精确匹配左前列并范围另一列

　　6.只访问索引的查询

（3）使用限制

　　1.如果不是按照索引最左列开始查找，则无法使用索引；

　　2.使用索引时不能跳过索引中的列

　　3.Not in 和<>操作无法使用索引

　　4.如果查询中有某个列的查询范围，则其右边所有列都无法使用索引

2.hash索引

（1）特点

　　1.hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列时，才能够使用到hash索引。范围查询或模糊查询不能使用

　　2.对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码；

（2）使用限制

　　1.hash索引必须进行二次查找

　　2.hash无法用于排序

　　3.hash索引不支持部分索引查找也不支持键值范围查找

　　4.hash索引中hash码的计算可能存在hash冲突，适合于不重复的列

3.为什么使用索引

　　（1）大大减少了存储引擎需要扫描的数据量

　　（2）可以帮助我们进行排序可以避免使用临时表

　　（3）可以把随机I/O变为顺序I/O

4.索引是不是越多越好

　　（1）索引会增加写操作的成本，Innodb有插入缓存，多次插入操作一次执行

　　（2）太多的索引会增加查询优化器的选择时间

5.建立索引策略

　　（1）索引列上不能使用表达式或函数

　　（2）前缀索引和索引列的选择性；

　　　　Create INDEX index_name ON table(col_name(n));前缀索引

　　　　索引的选择性是不重复的索引值和表的记录数的比值

　　（3）联合索引：

　　　　如何选择索引列的顺序

　　　　1.经常会被使用到的列优先，放在最左边，选择性很差的列则不适合

　　　　2.选择性高的列优先

　　　　3.宽度小的列优先

　　（4）覆盖索引：在btree的叶子节点上保存的需要查询字段的全部值的索引叫做覆盖索引

　　　　优点：可以优化缓存，减少磁盘IO操作；可以减少随机IO，变随机IO操作变为顺序IO操作；可以避免对Innodb主键索引的二次查询；可以避免MYISAM表进行系统调用；

　　　　缺点：无法覆盖索引的情况：存在引擎不支持覆盖索引；查询中使用了太多的列；使用了双%的like查询

6.优化索引查询

　　（1）使用索引扫描优化顺序：

　　　　1.通过排序操作；2.按照索引顺序扫描数据

　　　　索引的列顺序和order by子句的顺序完全一致

　　　　索引中所有列的方向（升序，降序）和order by子句完全一致

　　　　Order by中的字段全部在关联中的第一张表

　　（2）模拟hash索引会优化查询：

　　　　只能处理键值的全值匹配查找；

　　　　所使用的hash函数决定着索引键的大小

　　（3） 利用索引优化锁：

　　　　1.索引可以减少锁定的行数；

　　　　2.索引可以加快处理速度，同时也加快了锁的释放