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.索引可以加快处理速度,同时也加快了锁的释放