mysql 索引

1 索引类型

1.1 btree索引

特点

• B-tree索引能够加快数据的查询速度
• B-tree索引更适合进行范围查找
• innodb存储引擎中btree指向主键,myisam指向物理地址

使用位置

• 全值匹配的查询order_sn='987654345678'
• 匹配最左前缀的查询
• 匹配列前缀查询order_sn like '9876%' ;
• 匹配范围值的查询
• 精确匹配左前列并范围匹配另一列
• 只访问索引的查询

使用限制

• 不是按照索引最左列开始查找,则无法使用索引.
• 使用索引时不能跳过左边的列
• not in 和<>操作无法使用索引
• 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引

1.2 hash索引

特点

• Hash索引时基于Hash表实现的，只有查询条件精确匹配Hash索引中 的所有列时，才能够使用hash索引
• 对于Hash索引中的所有列，存储引擎都会为每一列计算一个Hash 吗，Hash索引中存储的就是Hash码

限制

• Hash索引必须进行二次查找，Hash索引中存储的是Hash码
• Hash索引无法用于排序
• Hash索引不支持部分索引查找也不支持范围查找
• Hash索引中Hash码的计算可能存在Hash冲突

2 使用索引的好处

• 索引大大减少了存储引擎需要扫描的数据量
• 索引可以帮助我们进行排序以避免使用临时表
• 索引可以把随机I/O变为顺序I/O

3 索优化策略

• 使用索引的列需要有隔离列:
  隔离是指不能是表达式的一部分，也没有位于函数
  如:select actor_id from sakila.actor where actor_id + 1 = 5;
  select * from product where to_days(out_date)-to_days(current_date)<= 30 ;
• 使用前缀索引取数值的前缀进行索引
  使用方式可以为create index idx_name on table_name(col_name(n))

3.1 使用索引优化排序

• 索引的列顺序和Order By子句的顺序完全一致
• 索引中所有列的方向(升序，降序)和Order by子句完全一致
• Order by中的字段全部在关联表中的第一张表中