数据库索引讲解

什么是数据库索引

1. 数据索引的作用是什么?

数据库索引其实就是为了使查询数据效率快.

2. 数据库索引有哪些呢？

• 聚集索引（主键索引）：在数据库里面，所有行数都会按照主键索引进行排序。
• 非聚集索引：就是给普通字段加上索引。

key ‘idx_age’ (‘age’)

• 联合索引：就是好几个字段组成的索引，称为联合索引。

key ‘idx_age_name_sex’ (‘age’,‘name’,‘sex’)

• 联合索引遵从最左前缀原则,通过下面的一个案例为大家进行解释,如下的A,B,C,D,E,F那些会走索引操作.

A:select * from student where age = 16 and name = ‘小张’
B:select * from student where name = ‘小张’ and sex = ‘男’
C:select * from student where name = ‘小张’ and sex = ‘男’ and age = 18
D:select * from student where age > 20 and name = ‘小张’
E:select * from student where age != 15 and name = ‘小张’
F:select * from student where age = 15 and name != ‘小张’

A遵从最左匹配原则，age是在最左边，所以A走索引；
B:直接从name开始，没有遵从最左匹配原则，所以不走索引；
C:虽然从name开始，但是有索引最左边的age，mysql内部会自动转成where age = ‘18’ and name = ‘小张’ and sex = ‘男’ 这种，所以还是遵从最左匹配原则；
D:这个是因为age>20是范围，范围字段会结束索引对范围后面索引字段的使用，所以只有走了age这个索引；
E:这个虽然遵循最左匹配原则，但是不走索引，因为!= 不走索引；如果不是很清楚{!= 不走索引}可以去了解一下sql语句优化.
F:这个只走age索引，不走name索引，原因同上；

3. 那些不走索引的案例

表student中两个字段age,name加了索引
1.key ‘idx_age’ (‘age’)
2.key ‘idx_name’ (‘name’)

• 1.Like这种就是%在前面的不走索引，在后面的走索引

A:select * from student where ‘name’ like ‘王%’
B:select * from student where ‘name’ like ‘%小’

A走索引，B不走索引

• 2.用索引列进行计算的，不走索引

A:select * from student where age = 10+8
B:select * from student where age + 8 = 18

A走索引，B不走索引

• 3.对索引列用函数了，不走索引

A:select * from student where concat(‘name’,‘哈’) =‘王哈哈’;
B:select * from student where name = concat(‘王哈’,‘哈’);

A不走索引，B走索引

• 4.索引列用了!= 不走索引

select * from student where age != 18

4. 索引在磁盘上面的的存储?

聚集索引和非聚集索引存储的不相同，那么来说下都是怎么存储的？

• 学生表

create table `student` (
 `id` int(11) not null auto_increment comment '主键id',
 `name` varchar(50) not null default '' comment '学生姓名',
 `age` int(11) not null default 0 comment '学生年龄',
 primary key (`id`),
 key `idx_age` (`age`),
 key `idx_name` (`name`)
) ENGINE=InnoDB default charset=utf8 comment ='学生信息';

id 为主键索引，name和age为非聚集索引

• 聚集索引在磁盘中的存储
  
  聚集索引里面存储的是表里面所以行的数据.每一个数据页在不同的磁盘上面
  如果要查询id=5的数据,那么先吧磁盘0读到内存中,然后用二分法查找到id=5是存在3-6之间,通过指针p1查找到磁盘2地址,然后将磁盘2读入内存中，用二分查找方式查找到id=5的数据。
• 非聚集索引在磁盘中的存储
  

叶子结点存储的是聚集索引键，而不存储表里面所有的行数据，所以在查找的时候，只能查找到聚集索引键，再通过聚集索引去表里面查找到数据。
第一步 :如果要查找到name = 小徐，首先将磁盘0加载到内存中，然后用二分查找的方法查到在指针p1所指的地址上，然后通过指针p1所指的地址可知道在磁盘2上面，然后通过二分查找法得知小徐id=4；
第二步:然后在根据id=4将磁盘0加载到内存中，然后通过二分查找的方法查到在指针p1所指的地址上，然后通过指针p1所指的地址可知道在磁盘2上面，然后通过id=4查找出郑正行数据，就查找出name=小徐的数据了。