MySQL InnoDB索引介绍及优化

create index idx_name on tb_student(name);
              索引名        表名   字段名

create index idx_name_age on tb_student(name,age);
#索引中先根据name排序，name相同的情况下根据age排序

如果索引所在字段发生了修改、删除、插入等操作，那么索引项就会发生变化，因此如果不能保证索引的有序，那么就不能索引的准确与效率，而索引的排序发生了变化的这个行为，我们称为索引维护

在insert/delete/update操作时，为了维护索引的排序，数据库会自动的完成索引项的维护，索引的排序，这些行为对用户是透明的，感觉不到的

在一个有索引的表中，创建它时，实际上还同时创建了索引排序的表，因此在DML中，插入等操作不再是普通的插入，MySQL将它封装成了一个事务，连着索引项的排序表一起操作

因此，我们应当严格控制表上的索引数量，否则容易影响数据库的性能

eg:
select a,b from tb_test where c = ?;
idx_c(c)   ->正确

select a,b from tb_test where c = ? and b = ?
idx_cd(c,d)  ->正确

eg:
select * from tb_test order by a;
select a,count(*) from tb_test group by a;
idx_a(a)  ->正确

select * from tb_test order by a,b;
idx_a_b(a,b)  ->正确

select * from tb_test order where c = ? by a;
idx_c_a(c,a)  ->正确

 如：男女比例相仿的表中，性别不适合创建单列索引，如果走索引不如走全表扫描，
 因为走索引的I/O开销更大
    
 但如果男女比例极度不平衡，要查询的又是少数方,如：理工学校、IT公司等可以考虑使用索引

select * from tab_a where gender=? and name=?
idx_name_gender(name,gender)   ->正确

idx_smp(a,b,c)
where a=?;                ->正确
where a=? and b=?;        ->正确
where a=? and c=?;        ->正确 （注：需要MySQL5.6版本以上；在5.5及以前版本，可以对a字段进行索引扫描，但c字段不行    ）
where a=? and b=? and c=? ->正确

(a),(a,b),(a,b,c)      ->不可取
(a,b,c)                ->正确，可以覆盖前两个

主要根据类型来分别处理：
1、Email类，可以建立前缀索引
mail_addr varchar(2048)
idx_mailadd(mail_addr(39))   -> 正确
解析：由于email邮件类型字段，一般后缀都有较大可能相同，如.com .cn等等，而前缀相同的可能性较低，且邮箱一般长度较短，因此可以建立前缀索引

2、住址类，分拆字段
home_addr varchar(2048)
idx_homeadd(home_addr(30))    ->错误，很可能前半段是相同的省市区街道名

province_add varchar(1024),city_add varchar(1024), district_add `varchar(1024),lolcal_add varchar(1024)    --建立联合索引或者单列索引 ->正确`

select name from tb_user where userid=?
key idx_uid_name(userid,name)   ->覆盖索引扫描

eg:
   where id+1=10;    ->错误，无法利用到索引
   where id=(10-1)   ->正确

   where year(id) < 2016      ->错误，无法利用到索引
   where col < &#39;2016-01-01&#39;   ->正确

idx_abc(a,b,c)
where b=? and c=?   ->错误，无法利用到索引
正确的建立索引方式(b,c)

like '%ttt%'   ->错误，无法利用到索引
like "ttt%"    ->正确

字段类型并不绝对匹配时，可能会导致无法使用索引
a int(11) ,idx_a(a)
where a = '123'   ->错误，可能导致未知的错误,这个跟编码有关系
where a = 123     ->正确

order by a;
a = 3 order by b;
order by a,b;
order by a desc ,b desc;
a > 5 order by a;

order by b; #没有使用到联合索引的第一个字段

a > 5 order by b;  #一旦前缀操作是一个range而非＝操作，那么就无法利用到索引，
这里 a>5无法利用索引，二联合索引的第一个字段未利用，
因此 order by b也无法利用索引查询

a in (1,3) order by b; #in里面的值没有建立索引，因此无法利用索引，a未用因此order by b也无法使用

order by a asc, b desc; #这里order by a esc是利用了索引，但是b desc未利用到，因为b要和a排序方式一致才可利用到索引

explain select * from tb_test;

1、type : 查询access的方式，表的连接类型
      
      index |  索引  
      full  |  全表扫描 
      ref   |  参照查询，也就是等值查询  
      range |  范围查询
2、key  : 本次查询最终选择使用哪个索引，NULL为未使用索引
3、key_len : 选择的索引使用的前缀长度或者整个长度
4、rows    : 查询逻辑扫描过的记录行数
5、extra   : 额外信息，主要是指fetch data的具体方式