索引的设计原则
索引有提高查询效率,但是给表的索引过多,效率反而会低下,因此在设计索引的时候,我们应该注意一些事项
- 给区分度高得字段创建索引 。例如:学号身份证号
- 给经常需要排序、分组和多表联合的字段创建索引
- 经常作为查询条件的字段创建索引
- 索引的数据不易过多
- 使用数据量少的索引(例如前缀索引,主要针对字符串索引)
- 对于多列索引,优先指定最左边的列集
- 删除不再使用或者很少使用的索引
索引的优化
单表查询优化
首先我们先看一下表的结构
这里有两个索引,一个是主键索引,一个是name的索引。
我们查询SID = 1 的学生信息。
首先MySQL默认使用INNODB的主键索引结构是B+树,非叶子节点存储主键关键字,叶子节点存储主键关键字和数据。
我们使用主键索引时,就在这个B+树上查询。索引SQL在执行过中,只需要查询一条数据(rows : 1)就能查询到了。
如果我们通过name = ‘赵雷’(赵雷的SID =1)去查询数据呢?
我们可以看到,我们如果通过Sname 去查询,我们使用的是辅助索引 idx_name 此时,我们使用的不仅仅是辅助索引B+树了。(辅助索引B+树结构:非叶子节点保存关键字,叶子节点保存关键字和对应的主键)
我们在辅助索引上查询到对应的Sname = ‘赵雷’ 关键字后,我们得到对应的主键SID = 1,再去主键索引B+树上去查询对应的数据。
那么假如我们查询的是SID 呢(SID是主键)?
那么我们就不需要再去主键索引上去寻找数据了,因为在辅助索引上已经查到了SID,已经满足查询了,不需要再去主键索引上去查询。
同样,我们如果查的是非主键字段
例如 Sage。
这个过程就是通过Sname 辅助索引,在叶子节点上得到对应的SID =1 ,再去主键索引得到对应的所有数据,得到Sage。
单表查询 + 简单排序或者分组
我们现在有一个订单表(orderlist)属性分别是用户ID,商品ID,时间。