一、类型:
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 清除表的碎片,使用不当会导致锁表