MySQL索引

• 索引分类
  • 索引是在mysql的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。mysql目前提供了以下4种索引。
    • B-Tree索引：最常见的索引类型，大部分引擎都支持B树索引。
    • HASH索引：只有Memory引擎支持，使用场景简单
    • R-Tree索引（空间索引）：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据，通常使用较少，不做特别介绍
    • Full-text（全文索引):全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从mysql5.6开始提供对全文索引的支持。
  • InnoDB引擎暂时只支持B-Tree索引
• mysql中能够使用索引的典型场景
  • 匹配全值，对索引中所有列都指定具体值，即使对索引中的所有列都有等值匹配的条件。
    • eg:alter table rental add index idx_rental_date(rental_date,inventory_id,customer_id) select * from rental where rental_date='2005-05-05 17:22:10' and inventory_id=373 and customer_id=343
  • 匹配值的范围查询，对索引的值能攻进行范围查找
    • select * from rental where customer_id >=373 and customer_id<400 explain查看索引key为idx_fk_customer_id说明优化器选择索引idx_fk_customer_id来加速访问，注意到extra列为Using where,表示优化器除了利用索引来加速访问之外，还需要根据索引回表查询数据。
  • 匹配最左边前缀，仅仅使用索引中的最左边列进行查找，比如在col1+col2+col3字段上的联合索引能够被包含col1、(col1+col2)、(col1+col2+col3)的等值查询利用到，可是不能够被col2、(col2+col3)的等值查询利用到。
  • 仅仅对索引进行查询，当查询的列都在索引的字段中时，查询效率更高；
    • eg:select last_update from payment where payment_date='2006-02-14 15:16:03' and amount=3.98 explain查看extra部分变成了Using index,也就意味着，现在直接访问索引就足够获取到所需要的数据，不需要通过索引回表，Using index 也就是平常说的覆盖索引扫描。
  • 匹配列前缀，仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。
    • eg:create index idx_title_desc_part on film_text(title(10),descritption(20)); explain select title from film_text where title like 'AFRICAN%' Extra值为Using where表示优化器需要通过索引回表查询数据。
  • 能够实现索引匹配部分精确而其他部分进行范围匹配。
    • eg:explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >=300 and customer_id <=400 类型type为range说明优化器选择范围查询，索引key为idx_rental_date说明优化器选择索引idx_rental_date帮助加速查询，同时由于只查询索引字段inventory_id的值，所以在Extra部分能看到Using_index，表示查询使用了覆盖索引扫描。
  • 如果列名是索引，那么使用column_name is null就会使用索引。
    • eg:select * from payment where rental_id is null.
• 存在索引但不能使用索引的典型场景
  • 以%开头的like查询不能够利用B-Tree索引
  • 数据类型出现隐式转换的时候也不会使用索引，特别死活当列类型是字符串，那么一定记得在where条件中把字符串常量值用引号引起来，否则即使这个列上有索引，MySQL也不会用到。
  • 复合索引的情况下，假如查询条件不包括索引列最左边部分，即不满足最左原则Leftmost，是不会使用复合索引的。
  • 如果MySQL估计使用索引比全表扫描更慢，则不使用索引
  • 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。