MySQL 支持的索引类型 ,B-tree索引,Hash索引

sqlercn | | 访问(86)

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

  B-tree索引使用场景

  1、全值匹配的查询

  例如:order_sn='2018071724608094'

  2、匹配最左前缀的查询

  联合索引A B,匹配左A

  3、匹配列前缀查询

  例如:order_sn like '20180717%'

  4、匹配范围值查询

  order_sn > '2018071700000000' and order_sn < '2018071724608094'

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

  联合索引A B,匹配左A,范围查找B

  6、只访问索引的查询

  7、支持分组排序

  B-tree索引使用限制

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

  联合索引A B,匹配右B查询,无法使用索引

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

  联合索引 订单日期 下单人姓名 下单人电话, 如果在查找中只包含了 日期和电话,只能使用日期过滤无法使用电话这一列,是因为跳过了下单人姓名这一列。

  3、NOT IN 和 != 或 <> 操作无法使用索引

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

  二、Hash索引

  Hash索引使用场景

  1、Hash索引是基于Hash表实现的,只有查询条件精确匹配Hash索引中的所有列时,才能够使用到Hash索引。

  2、对于Hash索引中的所有列,存储引擎都会为每一行计算一个Hash码,Hash索引中存储的就是Hash码。

  Hash索引的限制

  1、Hash索引必须进行二次查找(先找到索引值在去查那行数据)

  2、Hash索引无法用于排序

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

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

  三、为什么要使用索引

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

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

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

  四、索引太多带来的损耗

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

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

  五、什么叫做覆盖索引?

  解释一: 就是select的数据列只用从索引中就能够取得,不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。

  解释二: 索引是高效找到行的一个方法,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。

  解释三:是非聚集组合索引的一种形式,它包括在查询里的Select、Join和Where子句用到的所有列(即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段,也即,索引包含了查询正在查找的所有数据)。

  不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列,而哈希索引、空间索引和全文索引等都不存储索引列的值,所以MySQL只能使用B-Tree索引做覆盖索引

  当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时,在EXPLAIN的Extra列可以看到“Using index”的信息

  六、索引优化策略

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

  from product where to_day(out_date)-to_days(current_date)<=30

  优化后

  from product where out_date<=date_add(current_date,interval 30 day)

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

  3、联合索引

  如何选择索引列的顺序

  从左到右,经常会被使用到的列优先放左边,选择性高的列优先放左,宽度小的列优先放左。

  4、索引覆盖

  可以优化缓存,减少磁盘IO操作

  可以减少随机IO,变随机IO操作变为顺序IO操作

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

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

  无法使用覆盖索引的情况:存储引擎不支持覆盖索引,查询中使用了太多的列,使用了双%号的like查询。

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

  1、索引的列顺序和Order By子句的顺序完全一致

  2、索引中所有列的方向(升序,降序)和Order By子句完全一致

  3、Order By中的字段全部在关联表中的第一张表中