MYSQL系列-索引原理和实现

每日一记：最新发现知道的越多，不知道的就越多，还得多积累多学习，今天整理一下mysql索引吧。

      提到mysql的索引，想必很多人都能说上几句：提高查询效率、优化sql的方式。但是索引的底层实现到底是什么？索引是如何存储的？

什么是索引
      MySQL官方对索引的定义为：索引(Index)是帮助mysql高效的获取数据的数据结构。可简单理解为排好序的快速查找数据结构。 也就是说数据库除了维护数据本身之外，还维护着一个满足特定查找算法的数据结构。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。 

操作索引语法

创建

ALTER TABLE 表名 ADD [UNIQUE] INDEX 索引名(字段名);
或
CREATE INDEX 索引名 ON 表名(字段名);

删除

DROP INDEX 索引名 ON 表名

查看

show INDEX FROM 表名

索引的物理结构
1.数据库文件存储的位置：window系统在my.ini配置文件中；Linux系统在/etc/my.cnf文件中 的 dataDir参数对应的数据目录中。

2.每一个数据库对应一个文件夹
(1)MYISAM引擎：每一个表(table_name)
table_name.MYI:存放的是数据表对应的索引信息和索引内容
table_name.FRM:存放的是数据表的结构信息
table_name.MYD:存放的是数据表的内容
(2)InnoDB引擎:每一个表(table_name)
table_name.frm:存放的是数据表的结构信息
数据文件和索引文件都是统一存放在ibdata文件中

3.索引文件都是额外存在的，对索引的查询和维护都是需要消耗IO的

InnoDB引擎表： 

 

MYISAM引擎

索引的优缺点
优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本；
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；
索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大。构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表。