mysql索引

索引

为什么需要索引？

快速查找内容，加快查询速度，如果数据过多，再按照我们平时查找的方式查找，那么就太慢了

select是一行一行的扫描完，再定位数据位置，数据一旦多了就会很慢；

作用:加快表的查询速度，还可以对字段进行排列

索引是什么？

索引就是目录，是一个排列的列表，包含了索引字段的值和其对应的行数据

索引的工作原理

在没有索引的情况下，根据where条件查询某数据时，需要先扫描全表，再来定位某行数据的位置；

有了索引后，会先通过查询条件的字段值，找到其索引对应的行记录的数据物理地址，然后根据物理地址的访问相应的记录数据；

简单来说，就像我们看书，在我们没有索引的请求下，只可以一页一页的去查看书的章节，来寻找我们需要的内容；

而我们有了索引后，可以使用通过书的目录，找到具体的页数（数据物理地址），然后快速寻找到我想要的内容。

索引的优点：

设置了适合的索引后，加快表与表之间的连接速度，查询速度；

索引的缺点：

占用额外的磁盘空间；更新时，需要额外更新索引，导致更新效率会慢；

索引引擎：

MyI(i)SAM:索引文件和数据文件分开存储；

InnoDB:表数据文件和索引文件一块存储；

二者区别：

InnoDB支持事务（ACID）和回滚，MyISAM不支持；

InnoDB使用行级锁，MyISAM使用表级锁；

InnoDB支持外键约束，MyISAM不支持；

InnoDB有Redo Log自动恢复，MyISAM容易损坏需要手动恢复；

InnoDB是聚簇索引，MyISAM是非聚簇索引；

一句总结：InnoDB是mysql数据库的默认引擎，支持事务和行级锁；MyISAM只适合特定读密集型场景。mysql5.5+的默认引擎是InnoDB，若无特殊要求，推荐优先使用。这里的特殊要求类似：纯读且极少修改，如历史数据；资源紧张需要节省内存/磁盘。

索引的类别

数据结构：B+tree（默认），hash，全文，空间索引；

应用结构：主键，唯一，普通，联合，覆盖索引；

存储结构：聚簇索引与非聚簇索引；

特殊用途：前缀索引与倒排索引；

建立索引

create table 表名(字段1 类型1,字段2 类型2 ..... index 索引名称(字段名));#建议索引名称以'_index'结尾，便于辨识；

create index 索引名 on 表名 (字段名(位置));#创建索引

alter table 表名 add index  索引名(字段);#替换索引

分析索引

我们在创建索引后，可以使用

explain select * from students where age = 20 \G;#已创建好索引

explain select * from students where name ='L%'\G;#未创建索引

分别得到的结果为：