索引是帮助MySQL高效获取数据的排好序的数据结构(本质是一种优化查询的数据结构)
索引存储在文件里
索引结构(索引底层的数据结构)
二叉树
红黑树
Hash
B-树
存在表Test,表的字段分别为:Col 1和Col 2,为该表的Col 2 字段添加索引。为了方便理解,假设索引底层用的数据结构是二叉搜索树,则如下图:
我们都知道,MySQL数据库中的表中的数据是存储在磁盘上的。即该Test表的数据是存储在磁盘上的。如果我们有SQL语句:
SELECT Col1, Col2 FROM Test WHERE Col2 = 89;
现在要查找 Col 2 = 89 这条记录。CPU必须先去磁盘查找这条记录,找到之后加载到内存,再对数据进行处理。这个过程最耗时间的就是磁盘I/O(涉及到磁盘的旋转时间(速度较快)、磁头的寻道时间(速度慢、费时))。
如果我们不借助任何索引结构帮助我们快速定位数据的话,我们查找Col 2 = 89 这条记录,就要逐行去查找、去比较。从Col 2 = 34 开始,进行比较,发现不是,继续下一行。。。我们当前的Test表只有不到10行数据,但如果表很大的话,有上千万条数据,就意味着要做很多很多次磁盘I/O才能找到。速度是很慢的。
所以,这就是我们为什么要建索引,目的就是为了减少磁盘I/O的次数 ,加快查询速率。
什么时候【要】创建索引
(1)表经常进行 SELECT 操作
(2)表很大(记录超多),记录内容分布范围很广
(3)列名经常在 WHERE 子句或连接条件中出现
什么时候【不要】创建索引
(1)表经常进行 INSERT/UPDATE/DELETE 操作
(2)表很小(记录超少)
(3)列名不经常作为连接条件或出现在 WHERE 子句中
索引优缺点:
索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度(虽然索引可以提高查询速度,但是它们也会导致数据库系统更新数据的性能下降,因为大部分数据更新需要同时更新索引)
唯一索引可以确保每一行数据的唯一性,通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能
索引需要占物理和数据空间