什么是索引
索引是一个二叉树
首先每个表有一个主键,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