MySQL索引的分类、数据结构及其使用建议

索引的分类

建议 一张表最多建索引不要超过5个。

分组之前必排序

单值索引

一个索引只包含单个列，一个表可以有多个单列索引。

建议 一张表最多建索引不要超过5个

排序尽量遵守所建索引的顺序和个数。

group by 尽量遵守所建索引的顺序和个数。

唯一索引

索引列的值必须唯一，但允许有空值。

复合索引

即一个索引包含多个列。

索引的基本语法

创建索引

create [unique] index idnexName on tableName(columnname(length));

alter table tableName add [unique] index [indexName] on (columnname(length));

删除索引

drop index [indexName] on tableName;

alter table tableName drop index indexName; 

查看索引

show index from tableName;

例子:

mysql索引结构

• BTree索引

查找过程

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，29在17-35之间，锁定磁盘块1的p2指针，内存时间(相比磁盘的IO)忽略不计，通过指针p2找到磁盘块3，发生第二次IO,29在23-34之间，锁定磁盘块3的p2指针，通过指针加载磁盘块8，通过对比发现磁盘块8的数据项29即为查找项，结束查询，共计三次IO。

• Hash索引

• Full-text全文索引

• R-Tree索引

需要创建索引的情况

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 频繁更新的字段不适合创建索引
• where条件里用不到的字段不创建索引
• 在高并发场景下倾向创建复合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
• 查询中统计或者分组字段

不需要创建索引的情况

• 表记录太少

  因为索引也是以文件的方式在磁盘存放

• 经常增删改的表

  可能会经常出现索引失效的情景

• 数据重复且分布平均的字段

  即列的离散性越大越适合创建索引，count(distinct column) / count(*) 该值越大说明该列重复数据越少，越适合创建索引