1.什么是索引
排好序的快速查找的数据结构
2.索引的类型
主键索引:数据列不允许重复,不允许为null,一个表只有一个主键
唯一索引:数据列不允许重复,允许为null,一个表允许多个列创建唯一索引
普通索引:基本的索引类型,没有唯一性的限制,允许为null值
全文索引:目前搜索引擎使用的一种关键技术,对文本的内容进行分词、搜索
覆盖索引:查询列要被创建的索引覆盖,不必读取数据行
组合索引:多列值组成一个索引,用于组合搜索,效率大于索引合并
3.索引底层的数据结构
Hash索引:Memory表默认的索引类型
B+树索引:Inodb和Myisam存储引擎模式的索引类型,B+树是最常用的索引数据结构
B+树的优点:
1、B+树所有索引数据都在叶子节点上,并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针。这样做是为了提高区间效率,例如查询key为10到50的所有数据记录,当找到10之后,只要顺着节点和指针顺序遍历就可以依次访问到所有数据节点,极大提高了区间查询效率。
2、大大减少了磁盘IO次数,数据库系统的设计者巧妙的利用了磁盘预读原理,将一个节点的大小设为一个页,这样每个节点只需要一次IO就可以完全载入
4.如何创建和查看索引
创建索引
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
alter table class add index X(card);
查看索引
show index form table;
5.那些情况下需要创建索引
-
主键自动建立唯一索引
-
频繁作为查询条件的字段应该建立索引
-
查询中与其他表关联的字段,外键关系需要建立索引
-
频繁更新的字段不适合创建索引,每次不仅要更新记录还要更新索引。
-
where条件里的要建立索引
-
where条件中用不到的字段不需要建立索引
-
单键/组合索引的选择问题,who?(高并发情况下建立倾向于建立组合索引)
-
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
-
查询统计或者分组字段
6.索引失效的情况
- 不遵循最左前缀法则
- 在索引上做任何操作(计算、函数、类型转换),会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件最右边的列
- 尽量使用索引覆盖,减少使用select *
- 使用!=或<>会导致索引失效
- is null和is not null无法使用索引
- like以通配符开头('%abc')会导致索引失效
- 字符串不加单引号索引失效
- 用or连接会导致索引失效