MySQL 索引

 

索引的概念

不使用索引，要操作某些行时，需要遍历遍历整张表来找到匹配的行，很花时间，且有点耗资源。

书：目录=>快速定位到指定章节，不用一页一页地找

数据库：索引=>快速定位到指定记录，不用遍历数据表去找，索引相当于数据表的目录

 

 

索引的优缺点

• 提高查询效率，尤其是记录数超级多的时候，查询速度提升极大（优）
• 没有遍历数据表的资源开销（优）
• 索引也是一张表，要占空间，有额外的空间开销（缺）
• 增改删会同步到索引（维护索引），有额外的时间、资源开销，会降低增改删的效率（缺）

 

 

常见的几种索引

• 单值索引：索引中只包含数据表的一列（一个字段）
• 唯一索引：索引中列的每个值必须唯一，一般是数据表的主键列
• 复合索引：索引中包含数据表的多个列
• 全文索引：只能对myisam引擎的表使用，且索引中的列要是char、varchar、text等文本类型

 

 

索引常用操作

create index index_student on tb_student(id,name);  #创建索引,指定索引名称，要使用哪张表的哪些字段来创建索引

drop index index_student on tb_student;  #删除索引

show index from tb_student;  #查看某个表上所有的索引

其实不管是索引，还是视图，都很少用命令行来操作，太麻烦，一般都是在数据库管理工具（比如navicat）中直接操作。

索引是在 表结构|表定义|设计表 中操作的。

 

 

 

索引方式

索引方式有2种：

（1）btree  使用b+树的方式建立索引

比如选择id列来创建索引，假设有7条记录，id 1~7

查找id=7的记录的地址：4 -> 6 -> 7 

一般都是使用btree，因为很多存储引擎都支持btree。innodb只支持btree，不支持hash。

 

 

（2）hash   通过hash值一次直接找到记录的地址

比如使用id列建立索引，查找id=7的记录，根据7的hash值直接定位到对应记录的地址。

btree要一级级的找，要通过多个节点；hash一次就找到，查询效率远高于btree。

但hash用得较少，因为只有memory存储引擎支持hash。

 

 

 

说明

• 一张表添加主键时，会自动给主键列创建唯一索引（unique）；添加外键时，会自动给外键列创建普通索引（normal）。
• 在数据库管理工具（navicat）中能看到外键自动创建的索引，但看不到主键自动创建的索引；在mysql自带的命令行客户端使用 show index from 表名; 可以看到该表所有的索引，包括主键自动创建的索引。
• 创建索引后，从表中找匹配的记录时会自动选择合适的索引来使用，不需要我们指定索引。

 

 

 

哪些字段适合创建索引？

• 主键
• 外键   这2个不用管，会自动创建
• 频繁作为条件的字段，比如经常要用where name=' xxx'，那就给name字段创建索引
• group by分组使用的字段
• order by排序使用的字段
• 统计（聚合函数）使用的字段

 

 

哪些字段不适合创建索引？

• 频繁更新（update）的字段。要频繁同步到索引（维护索引），开销很大。
• 有很多重复值的字段。比如dep_id，tb_student很多记录的dep_id都是相同的。

 

 

哪些表不用创建索引？

• 记录很少的表
• 经常进行增删改的表。维护索引开销大