写在前面:
在了解索引之前 我们需要知道索引的几个关键词,数据结构、设计原则、索引优化、索引失效、回表、索引覆盖、最左匹配、索引下推、聚簇索引等;
索引作用:提高查询效率
概念:
聚簇索引与非聚簇索引
- 聚簇索引
数据与索引存储在一起—>聚簇索引
innodb存储引擎进行数据插入时,数据必须跟某一个索引存储在一起,这个索引可以是主键;没有主键时,可以选择唯一健;没有唯一健时会选择6字节的rowid进行存储;
数据必定是跟一个索引绑定在一起,绑定数据的索引成为聚簇索引;
- 非聚簇索引
将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因;
回表
举例说明
字段名称 | id | name | age |
---|---|---|---|
说明 | 主键 | 普通索引 | - |
select * from table where name='niuniu'
会根据name的nameB+tree获取到对应节点,查询对应的id;再根据id去表中检索整行记录,这个过程称为回表;(ps:避免回表)
覆盖索引
举例说明
字段名称 | id | name | age |
---|---|---|---|
说明 | 主键 | 普通索引 | - |
select id from table where name='niuniu'
会根据name去nameB+tree检索对应记录,能够获取到id的属性,索引的叶子结点包含查询的所有列,不会进行回表操作,这个过程称为索引覆盖;
最左匹配
举例说明
字段名称 | id | name | age |
---|---|---|---|
说明 | 主键 | name、age联合索引 | name、age联合索引 |
- 全值匹配查询
# 可以用到索引(name,age),用到(name,age)
select id from table where name='niuniu' and age=18;
# 可以使用索引(name,age),用到(name,age)
select id from table where age=18 and name='niuniu';
where子句几个搜索条件顺序调换不影响查询结果,因为Mysql中有查询优化器,会自动优化查询顺序
# 可以使用索引(name,age),用到(name)
select id from table where name='niuniu';
# 未使用索引,不满足最左匹配原则
select id from table where age=18;
最左匹配原则可以定义一个联合索引,但是使得多数查询条件都可以用到该索引;需要注意的是:当遇到范围查询(>、<、between、like)就会停止匹配;
select * from tabel where a=1 and b>2 and c=3
a,b可以用到索引(a、b、c), c用不到索引
小结:
举例记录mysql的几个概念;