1.索引的优点
1、大大减少了服务器需要扫描的数据量
2、帮助服务器避免排序和临时表
3、将随机io变成顺序io
2.索引的用处
1、快速查找匹配WHERE子句的行
2、从consideration中消除行,如果可以在多个索引之间进行选择,mysql通常会使用找到最少行的索引
3、如果表具有多列索引,则优化器可以使用索引的任何最左前缀来查找行
4、当有表连接的时候,从其他表检索行数据
5、查找特定索引列的min或max值
6、如果排序或分组时在可用索引的最左前缀上完成的,则对表进行排序和分组
7、在某些情况下,可以优化查询以检索值而无需查询数据行
3.索引的分类
主键索引
唯一索引
普通索引
全文索引
组合索引
4.索引匹配方式
4.1全值匹配(全值匹配指的是和索引中的所有列进行匹配)
创建一个表并且建一个复合索引(name,age,pos)
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default '' comment '职位',
add_time timestamp not null default current_timestamp comment '入职时间'
) charset utf8 comment '员工记录表';
-----------alter table staffs add index idx_nap(name, age, pos);
进行查询分析索引
explain select * from staffs where name = 'July' and age = 23 and pos = 'dev';
ref:表示常量池,const表示复合索引的字段使用了几个,
4.2匹配最左前缀
explain select * from staffs where name = 'July' and age = 23;
ref:只有2个const而且key_len变成78
explain select * from staffs where name = 'July';
ref:只有1个const而且key_len变成74
在mysql中高版本会索引自动调整值得顺序
5.匹配列前缀
可以匹配某一列的值的开头部分
explain select * from staffs where name like 'J%';
当%在前面的时候是全匹配会导致索引失效
explain select * from staffs where name like '%y';
6. 匹配范围值
可以查找某一个范围的数据
explain select * from staffs where name > 'Mary';
当使用范围查询时会导致索引失效
pos没有走索引
精确匹配某一列并范围匹配另外一列
可以查询第一列的全部和第二列的部分
explain select * from staffs where name = 'July' and age > 25;
只访问索引的查询
查询的时候只需要访问索引,不需要访问数据行,本质上就是覆盖索引
explain select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev';
7.哈希索引
基于哈希表的实现,只有精确匹配索引所有列的查询才有效
在mysql中,只有memory的存储引擎显式支持哈希索引
哈希索引自身只需存储对应的hash值,所以索引的结构十分紧凑,这让哈希索引查找的速度非常快
哈希索引的限制
1、哈希索引只包含哈希值和行指针,而不存储字段值,索引不能使用索引中的值来避免读取行
2、哈希索引数据并不是按照索引值顺序存储的,所以无法进行排序
3、哈希索引不支持部分列匹配查找,哈希索引是使用索引列的全部内容来计算哈希值
4、哈希索引支持等值比较查询,也不支持任何范围查询
5、访问哈希索引的数据非常快,除非有很多哈希冲突,当出现哈希冲突的时候,存储引擎必须遍历链表中的所有行指针,逐行进行比较,直到找到所有符合条件的行
6、哈希冲突比较多的话,维护的代价也会很高
当需要存储大量的URL,并且根据URL进行搜索查找,如果使用B+树,存储的内容就会很大
select id from url where url=""
也可以利用将url使用CRC32做哈希,可以使用以下查询方式:
select id fom url where url="" and url_crc=CRC32("")
此查询性能较高原因是使用体积很小的索引来完成查找
8.组合索引
当包含多个列作为索引,需要注意的是正确的顺序依赖于该索引的查询,同时需要考虑如何更好的满足排序和分组的需要
案例,建立组合索引a,b,c
不同SQL语句使用索引情况