mySQL笔记（二十三）：索引1-简介、结构、分类

索引官方定义：索引（index）是帮助mysql高效获取数据的数据结构。

一般来说索引本身很大，不可能全部存在内存中，因此索引往往以索引文件的形式存储在磁盘上。

优势：

1. 提高数据检索的效率，降低数据库IO成本；
2. 通过索引对数据进行排序，降低数据排序的成本，降低了CPU消耗。

劣势：

1. 虽然索引大大提高了查询速度，同时会降低更新表的速度。
2. 实际上索引也是一张表，保存了主键与索引字段，并指向实体表的记录，所以需要占用空间。

索引结构：平衡树结构

BTree和B+Tree的区别：

1. B树的每个节点存储索引信息和数据；B+树的非叶子节点中只存储索引信息，不存储数据，数据只放在叶子节点中。
2. 在B树中，越靠近根节点的记录查找时间越快；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。
3. B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B树多，树高比B树小，叶子节点存在链指针，可以减少磁盘访问次数。

为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引？

1. B+树的磁盘读写代价更低
2. B+树的查询效率更稳定

 时间复杂度：平衡树的时间复杂度是logN

•  聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式。术语“聚簇”表示数据行和相邻的键值聚簇的储存在一起。主键索引是聚簇索引。
• 非聚簇索引：除了主键索引外我们建的其他索引都是非聚簇索引。
• 区别：
  • INNODB的聚簇索引与非聚簇索引存在不同：
    • 非聚簇索引的叶子节点存放的是非聚簇索引信息和相应聚簇索引的信息，不存放数据信息，这减小了移动数据或者数据页面分裂时维护非聚簇索引的开销。
    • 即非聚簇索引查询时，首先从非聚簇索引中查到聚簇索引的信息（对应主键的地址），然后从聚簇索引中查找具体数据。
  • MYISAM的非聚簇索引的叶子节点中存放了数据，不用二次查找。

索引分类：

1. 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
2. 唯一索引：索引列的值必须唯一，但允许有空值。
3. 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引。
4. 复合索引：即一个索引包含多个列。

基本语法：

#查看索引
show index from 表名

#创建索引
create [inique] index [indexName] on 表名（字段名）

#删除索引
drop index [indexName] on 表名；

####使用alter命令

#1、添加一个主键，索引值必须是唯一的，且不能为null
alter table tbl_name add prmary key(column_list);

#2、创建索引的值必须是唯一的（除了null之外，null可能会出现很多次）
ALTER TABLE tbl_name ADD UNIQUE index_name(column_list);

#3、添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD INDEX index_name(column_list);

#4、该语句指定索引为FULLTEXT，用于全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list);

 哪些情况需要创建索引：

1. 主键自动建立唯一索引；
2. 频繁作为查询条件的字段应该创建索引；
3. 查询中与其他表关联的字段，外键关系建立索引；
4. 单键/组合索引的选择问题，组合索引的性价比更高；
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度；
6. 查询中统计或者分组字段。

哪些情况不需要创建索引：

1. 表记录太少
2. 经常增删改的表或字段
3. where条件里用不到的字段不建索引
4. 过滤性不好的不适合建索引