MySQL-MySQL索引原理深入剖析

1.     索引是一种数据结构，能够提高数据的检索速度。

栗子：从如下数据中找出所有为2的数据：1，3，2，5，7，9，2，5，6？

无索引：由于数据是没有顺序的就只能通过顺序查找的方式一个一个的查找比对。

有索引：会先将数据排序，排序后为1，2，2，3，5，5，6，7，9，这个时候就不用顺序查找了，顺序查找效率也不高，这个时候我们就可以使用比较高效的二分法查找了，所以速度一定比顺序查找快。

2.     结合上面例子可以引出索引的特点：排好序，快速查找，数据结构（mysql里面的索引index_type有btree，hash等，这些都可以理解成不同的数据结构，如btree就和数据结构中二叉树非常类似）

3.     Btree数据结构特点：

由上图可以基本上了解到建立索引的好处了，就是查找快，以前要查询7次才能查询到对应的所有数据，现在只需要查找二叉树层数（n）对应次数就能查到数据，使用索引后只需要查询3次就能查取到结果。

4.     二叉树层级特点：

第一层：2^0=1

第二层：2^1=2

第三层：2^2=4

第n层：2^(n-1)

当n=32：2^31=21亿（无符号）

5.     Hash数据结构特点：

如Memory内存数据表存储引擎就是使用的hash索引，而myisan和innode也用到了hash索引，但是它们二个是伪hash，是自己实现。优点：查找高效，理论上面只需要查找一次。缺点：会产生地址冲突，如果地址冲突比较多的话，索引的维护就比较麻烦，代价很高。

6.     索引本身存储也是存放到文件中的，占据硬盘资源。

7.     Hash索引局限：

     1）hash索引只能用于等值比较，也就是范围查找用不上索引。

    等值比较包含=，<=>，in()。

    2）无法使用索引最左前缀列原则

    create index idx_id_name(id,name)

                 where id=1 这个时候是不走hash索引的。

8.     Mysql如何管理数据库文件的？

    .frm文件：存储数据表的框架结构，文件名与表名相同，每个表对应一个同名frm文件，与操作系统和存储引擎无关，即不管MySQL运行在何种操作系统上，使用何种存储引擎，都有这个文件。

    除了必有的.frm文件，根据MySQL所使用的存储引擎的不同（MySQL常用的两个存储引擎是MyISAM和InnoDB），存储引擎会创建各自不同的数据库文件。

    1）MyISAM数据库表文件：.MYD文件：即MY Data，表数据文件 .MYI文件：即MY Index，索引文件 .log文件：日志文件。

    2） InnoDB数据库表文件：.ibd文件：InnoDB引擎开启了独立表空间产生的存放该表的数据和索引的文件。

8.     聚集索引和非聚集索引原理（数据的物理存储）

非聚集索引：如MyISAM中.MYD （MYData，存储数据）和.MYI （MYIndex，存储索引）是存放在不同的文件中的，数据文件和索引文件可以放置在不同的目录，平均分布io，获得更快的速度。这样分开存的方式就叫非聚集索引。

而InnoDB是存放在一个.ibd文件中的，这是聚集索引。

参考博客：