索引的基本知识

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语句使用索引情况