数据库架构--索引

一、类型：

B-tree索引，常见的索引（顺序存储）

B-tree过引以B+树的结构存储数据

B-tree索引能够加快数据的查询速度

B-tree索引更适合进行范围查找

a、使用场景：

1、全值匹配的查询 order_sn= ‘1234432424242'

2、匹配最左前缀的查询

3、匹配列前缀查询 order_sn like ‘809090'

4、匹配范围值的查询  order_sn > ‘22222223’   and order_sn < ‘3333333333333’

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

6、只访问索引的查询

b、使用限制：

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

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

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

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

二、Hash索引的特点

1、Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引的所有列时，才能够使用到hash索引。（即只能使用等值查询，不能是模糊查询、范围查询）

2、对于hash索引中所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是hash码。查找比较快。

a、使用限制

1、Hash索引必须进行二次查找

2、Hash索引无法用于排序

3、Hash索引不支持部分索引查找也不支持范围查找

4、Hash索引中Hash码的计算可能存在Hash冲突

三、索引的好处

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

2、索引可以帮助我们进行排序以免使用临时表

3、索引可以把随机I/O变成顺序I/O

四、索引数量

1、索引会增加写操作的成本

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

五、索引优化策略

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

六、前缀索引和索引列的选择性

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

七、联合索引

1、经常被使用到的列优先

2、选择性高的列优先

3、宽度小的优先  

八、覆盖索引

优点：

1、可以优化缓存，减少磁盘IO操作

2、可以减少随机IO，随机IO操作变为顺序IO

3、可以避免对Innodb主键索引的二次查询  

4、可以避免MyISAM表进行系统调用  

九、无法使用覆盖索引的情况

1、部分存储引擎不支持覆盖索引:hash索引也不支持

2、查询中使用了太多列  

3、使用了双%号的like查询

十、使用索引扫描来优化排序

1、通过排序操作

2、按照索引顺序扫描数据

3、索引的列顺序和Order by子句的顺序完全一致

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

5、order by 中的字段全部在关联表中的第一张表中

十一、使用索引来优化查询

1、利用索引优化锁

1.1、索引可以减少锁定的行数

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

2、删除重复和冗余的索引

primary key 主键索引

unique key 唯一索引

index 单列索引

index(a,b) 联合索引

检查索引工具:pt-duplicate-key-checher h=127.0.0.1

更新索引统计信息及减少索引碎片  

analyze table table_name

optimize table table_name 清除表的碎片，使用不当会导致锁表