Mysql——索引

什么是索引

索引是一个二叉树

首先每个表有一个主键，Mysql会以主键的值构造一个棵树，叶子节点存放着以该主键对应的整行数据。所以一张表在数据结构上等价于一颗以主键排序好的树。
如果没有主键呢？
如果没有主键，那么mysql会自动生成一个内部列，以内部列构造一棵树。（按照主键或内部列访问是最快的）
那么这张表的树和索引的树有什么关系呢？
对于其他的自己建的索引，一般是叫辅助索引。
对于辅助索引，叶子结点存放了两个东西：辅助索引的值（排序好的）和对应的主键。

比如执行如下查询语句

//adress加索引
select * from User where address=xxx;

mysql直接从address索引找到符合条件的叶子结点，从该叶子节点中取出主键的值，跑去主键那棵树取回整行索引。

但如果是

//adress加索引
select address from User where address=xxx;

就不用跑回主键索引查整行数据。

什么时候加索引

1.查询分组和排序分组的时候最好是加索引
2.对于区分度高的字段加索引

强制索引

对于索引比较多的时候，可以使用强制索引

select * from XXX FORCE INDEX(address)

联合索引

//添加联合索引
alter index u_index on user（name,address）;
select XXX from user where name=XXX and  address=XXX;

这时候会触发联合索引提高查询速率，如果没有加索引就会进行外部排序影响查询效率。
触发联合查询？
1、使用联合索引的全部索引键，可触发索引的使用。
例如：SELECT * FROM user WHERE name=‘Jacke’ AND address=‘s’

2、使用联合索引的前缀部分索引键，如“name”，可触发索引的使用。
例如：SELECT * FROM user WHERE name=‘Jacke’

3、使用部分索引键，但不是联合索引的前缀部分，如“key_part_2 常量”，不可触发索引的使用。
例如：SELECT * FROM user WHERE address=‘s’

4、使用联合索引的全部索引键，但索引键不是AND操作，不可触发索引的使用。
例如：SELECT * FROM user WHERE name=‘Jacke’ OR addres=‘s’

join字段加索引

先看简单的

select XXX from t1 join t2 on t1.id = t2.id

mysql会比较t1和t2哪个表小，假设t1小（这里主要指行数少）。然后就把t1的id进行全表扫描，然后去t2里一个一个查。所以这里可以给t2加上索引。t1反正要全表扫描了加索引也没用。

如果加上过滤条件

select XXX from t1 join t2 on t1.id = t2.id where  t1.address=xxx AND t2.address=xxx

如果这样的话大小表就不绝对了，可能之前大表查出来的结果集反而小。所以此时加联合索引是最优。

索引失效有哪些情况

• like通配符放最前的，还有这几种运算符无法用到索引。

select … like ‘%xxx%’
select … not in()
select … != XXX
select … <> xxx

• 对列进行函数运算无法用到索引
  select … where md5(password)=‘xxx’
• 数据类型转换走不到索引
  select … where mobile = 13711112222