Mysql索引

概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。

作用

• 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
• 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
• 索引对于提高数据库的性能有很大的帮助

使用场景

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间

使用

1）主键索引：primary key
2）唯一索引：unique
3）普通索引：index
4）全文索引：fulltext ——— 解决中文子索引问题

创建索引

创建主键索引

• 在创建表的时候，直接在字段名后指定primary key

主键索引特点：
1）一个表中，最多只有一个主键索引，可以使用复合主键
2）主键索引的效率高（主键不可重复）
3）创建主键索引的列，它的值不能为null，且不能重复
4）主键索引的列基本上是int
唯一索引的创建

• 在定义表时，在某列后直接指定unique唯一属性

唯一索引特点：
1）一个表中，可以有多个唯一索引
2）查询效率高
3）如果在某一列创建唯一索引，必须保证这一列不能有重复数据
4）如果一个唯一索引上指定not null，等价于主键索引
普通索引的创建

• 在表的定义最后，指定某列为索引 create index 索引 on 表名

普通索引特点：
1）一个表中可以有多个普通索引，在实际开发中用的比较多
2）如果某列需要创建索引，但是该列有重复的值，就要使用普通索引

全文索引的创建
当对文章字段或有大量文字的字段进行检索时，会使用全文索引。
全文索引比较特殊，它只有少数的几个存储引擎支持，且只有类型为char、vchar、text的列能建立全文索引。以前，只有MyISAM引擎支持全文索引，现在InnoDB也可以用。

全文索引因为用上了索引，性能更高，有词库支撑可以进行分词提供了一些语义查询的功能，有词语停用表忽略某些词语，有词语最大最小值可以设置等更灵活。

查看索引

show index from 表名

删除索引

drop index 索引名 on 表名

数据库索引大部分用 B+ 树，少部分用 B 树

不用哈希表的原因：
哈希表虽然能够再 O(1) 查找到目标数据，不过如果我们要进行模糊查找的话，却只能遍历所有数据，并且如果出现了极端情况，哈希表冲突的元素太多，也会导致线性时间的查找效率的。
不用二叉搜索树的原因：
如果是查找效率（即比较次数）的话，实际上二叉树可以说是最快的了，但是，我们的文件索引是存放在磁盘上的，所以我们不仅要考虑查找效率，还要考虑磁盘的寻址加载次数，所以用 B 树。

B数：B 树和二叉查找树一样，都是树，B 树相当于是一棵多叉查找树。
和二叉查找树相似，都是有序，且左孩子小，右孩子大，只是 B 树的一个节点可以有多个元素，并且有多个分支。

把磁盘里的数据加载到内存中的时候，是以页为单位来加载的，而我们也知道，节点与节点之间的数据是不连续的，所以不同的节点，很有可能分布在不同的磁盘页中。
而由于 B 树的每一个节点，可以存放多个元素，所以磁盘寻址加载的次数会比较少。

总结：内存的运算速度是非常快的，至少比磁盘的寻址加载速度，快了几百倍，而我们进行数值比较的时候，是在内存中进行的，虽然 B 树的比较次数可能比二叉查找树多，但是磁盘操作次数少，所以总体来说，还是 B 树快的多，所以用 B 树来存储。

B+树与B树的不同在于：

• 所有关键字存储在叶子节点，非叶子节点不存储真正的data
• 为所有叶子节点（左右相邻的节点之间）增加了一个链指针

为什么mysql的索引大多使用B+树而不是B树？

• 范围查找更快，mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树的数据有一部分存在在非叶子节点上面，而且默认的B树的相邻的叶子节点之间是没有指针的，所以范围查找相对更慢。
• 降低树的高度，但是最底下一层的节点会更多，因为所有的数据都堆积在最底下一层了，用空间换速度。B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。