InnoDB引擎之索引的优化及有效使用

Innodb 存储引擎的表数据和索引是存储在同一个表空间里面，在一定程度上索引的效率没有MyISAM快，但这丝毫不影响InnoDB存储引擎成为主流的存储引擎。
拓展：MyISAM 存储引擎的表的数据和索引是分开存储的，即每个 MyISAM 在磁盘上存储 .frm（存储表定义）、.MYD（存储数据）、.MYI（存储索引），数据和索引存放在不同的文件里，获取速度更快。

EXPLAIN 查看执行计划信息的重点列

+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | in_name       | in_name | 93      | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+---------+---------+-------+------+--------------------------+

如上所示，explain显示了多列内容，其中type、possible_keys、key、key_len、ref、rows、Extra是优化SQL时必须要关注的。

• type：mysql决定如何查找表中的行，下面是type的类型，从上到下性能由好到差

  NULL：MySQL在优化阶段分解语句，在执行阶段甚至不需要访问表或者索引，例如从索引列中查找最小的索引通过单独查找索引就可以完成，而不需要访问表数据
  system,const：通过主键查询数据，只能返回一行数据，主键包括单列主键和联合主键，联合主键必须都存在，否则type列是ref类型，例如联合主键(id,name)，id和name都在where条件中才可以
  eq_ref：MySQL最多只会返回一行数据，待实验
  ref：使用索引查找，但不是主键和唯一索引，它可能会返回多个匹配的行
  range：有范围的索引扫描，即带有 BETWEEN 或在 WHERE 子句里面带有 > 的查询，有时候使用in或or也会显示范围扫描
  index：全表扫描，但会按索引次序进行而不是行，避免了排序，缺点是按索引次序读取数据整个表。如果在 Extra 列中看到了 Using index，说明使用的是覆盖索引，只扫描索引的数据，而不是按照索引次序。
  All：全表扫描
  

  

• possible_keys
  可能用到的索引

• key
  用到的索引

• key_len
  索引的长度。例如联合索引（name,state,address），如果只使用name部分的索引，那么长度是name部分索引长度，如果使用了name+state部分索引，那么长度是两部分索引长度和。

• ref
  显示key列使用的索引中查找值所用的列或常量

• rows
  涉及的行数

• Extra

  # 索引相关
  using index：使用了覆盖索引
  using where：在查找使用索引的情况下，需要回表查询数据
  using index condition：查找使用了索引，但是需要回表查询查询数据
  using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询
  # 排序相关
  using index：使用单列索引排序；使用联合索引排序，且必须是联合索引的首列在前，并且多列使用相同的排序。无所谓查询的列是否只是索引列
  using filesort：使用一般列排序或联合索引的非首列
  using index ; using filesort：多字段排序时，部分使用索引字段，部分是一般列；或联合索引的多列时，非首列在在前；或联合索引的多列时，首列在前但多列使用不同的排序
  # 分组相关
  using temporary ; using filesort：group by默认情况下会排序
  using temporary ：使用order by null优化后的情况
  using index：使用索引分组
  

索引的使用和优化

1. 是否使用索引与where后的条件顺序无关

2. 索引列使用比较符号，会使用索引，单列索引和联合索引都可以，但是索引后面的索引不会被使用，联合索引比较详见第5条的案例

3. 尽量使用联合索引，减少使用单列索引。例如列name、state、address
   联合索引(name,state,address)相当于创建了三个索引：name、name+state、name+state+address
   单列索引name、state、address，当三个索引一起作为查询条件时，MySQL会选择一个最优的索引（数据的辨识度高的索引）来使用，并不会使用全部索引。
   辨识度高是指根据索引查出的数据量小，例如数据库中有100w条数据，name和state都是单列索引，其中name='瞎子' and state = 0的数据只有一条，其他数据均为name='瞎子' and state = 1，对于select * from table_name where name = '瞎子' and state = 0;，MySQL会使用索引status

4. 索引全值匹配，例如联合索引(name,state,address)，SQL：select * from table_name where state = '1' and name = '小乔' and address = '北京';

5. SQL符合联合索引的最左前缀法则，例如联合索引(name ,state,address)，要想用到索引，条件中必须要包括联合索引中的第一个列，具体会用几个索引参考下面的代码

   # 用到三个列的索引
   where name = '' and state = '' and address = ''
   # 用到两个列的索引
   where name = '' and state = ''
   # 用到name列的索引，因为跳过了state列，所以address的列不会使用索引
   where name = '' and address = ''
   # 用到name和state列的索引，address的列不会使用索引
   where name = '' and state > '1' and address = ''
   # 没有用到索引
   where state = '' and address = ''
   

6. 通常情况下，我们都会根据where条件来创建合适的索引，这是索引优化的一个方面，但数据库通过索引查询数据后要不要回表查询，这也是索引优化的方面，也就是说MySQL通过索引查数据时，索引的叶子节点上已经包含要查询的数据，这样就不需要回表查询了。这就是著名的覆盖索引。MySQL的InnoDB引擎只有B-Tree类型的索引具有覆盖索引，哈希索引、空间索引和全文索引都没有覆盖索引。
   覆盖索引：查询只需要访问索引，而无须访问数据行
   覆盖索引的原理：索引的叶子节点上包含该行的所有索引列
   例如id、name、state、address四列，其中id为主键，(name,state,address)是联合索引

   # 对于下面三个sql，explain查看执行计划，发现Extra列的值为Using where; Using index，对应上面Extra的类型
   select name , address from table_name where name = 'p7';
   select id , name from table_name where name = 'p7';
   select state from table_name where name = 'p7';
   # 下面这个sql的Extra列的值是Using where，因为查询的password不是索引列也不是索引列的一部分
   select id , name , password from table_name where name = 'p7';
   

7. like尾部模糊查询索引有效，头部模糊查询索引失效，利用覆盖索引优化like头部模糊查询

   # 通过覆盖索引查询id
   select id from table_name where name like '%p7%';
   # 通过id查询数据
   select * from table_name where id = xxx;	
   

8. in索引有效，not in索引无效

9. or前的条件中使用索引，后面条件没有使用索引，那么查询不使用索引

10. MySQL执行SQL时发现全表扫描比索引快，则不使用索引。
    示例1：name是单列索引，非主键，数据库中99条数据的name都是盲僧，只有一条数据是p7，在执行where name = '盲僧'时，MySQL会放弃索引，进行全表扫描。
    示例2：name是单列索引，非主键，如果数据库中所有name字段没有null的情况，is null使用索引，is not null进行全表扫描；如果name为null占大多数，is null进行全表扫描，is not null使用索引
    上面两种情况，均是MySQL内部优化，根据数据库中的数据占比而定的