Mysql的索引

1 索引的概念

从mysql数据库中查找书籍，就类似于从图书馆找书，如果书比较少，那么查找的速度会比较快，如果书比较多的时候，那么查找的速度就会非常的慢。这个时候图书的分类管理就显得非常重要了，图书分类管理就类似于索引（或者理解成给所有的图书设定目录）。

索引的本质就是一种数据结构。

mysql中有这样一张表，emp表，表中的数据有1000000条数据。

假如在没有索引的情况下，当你执行如下的sql语句，那么执行的速度相对来说比较慢。Sql语句如下：select *from emp where ename = 'c999017'

查询结果如下：

可以对ename的列创建索引：

select* from emp where ename = 'c999017'
-- 显示emp表所有的索引
showindex from emp
-- 对象ename列创建索引
CREATEINDEX index_ename on emp (ename)
-- 删除索引drop index index_ename on emp

创建索引后，执行查询的结果：

为什么要使用索引？

        其目的就是为了减少磁盘的IO操作，这样就可以提高查询数据的速度。

索引本质是什么？

        索引的本质就是一种数据结构。也就是说你创建了一个索引，其实mysql的数据库会帮你创建一个数据结构，然后使用该数据结构来存储数据，该数据结构就是索引。

2 Mysql索引底层原理

2.1二叉查找树

采用二叉树做为索引的数据结构。

二叉树是一颗相对平衡的有序二叉树，对其进行插入，查找，删除等操作性能都比较好。

特点：它的左子节点的值比父节点的值要小，右节点的值要比父节点的大。

自己测试例子:

二叉树的优点：可以优化磁盘IO的次数。节点存在有顺序，可以进行范围的查询。

二叉树的缺点：插入数据的速度会比较慢，因为会更改数据结构。不平衡的问题，会产生倾斜的二叉树。

2.2平衡二叉查找树

插入数据会平衡，但是插入的时候会改变树的结构。插入的数据的时候比较慢。而且树的层级会变高，会增加磁盘IO的次数。

二叉树是有顺序的，范围查找都是支持的。

2.3B树和B+树

B树：

B+树：

B树或者B+树的节点可以存储多个数据，所以相对于完全平衡二叉树的高度肯定会低，那么就会降低磁盘IO的次数。

B+树相对于B树有数据的冗余，叶子节点中的数据是有顺序的。那么再进行顺序查找的时候就非常的方便，只要在叶子节点顺序向后遍历即可。

3 mysql索引是如何存储数据的

3.1 mysql中是如何使用B+树的

mysql中Innodb和myisam存储引擎默认都是使用B+树数据结构做索引的，存储的数据结构如下：

为什么只有叶子节点会存储数据，而非叶子节点不存储数据呢？

局部性原理：当一个数据被用到时，其附近的数据也可能被使用，所以操作系统为了提高效率，读取数据的时候往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，操作系统也会从这个位置开始，顺序向后读取一定长度的数据放入内存中，这里的长度叫做页。也就是计算机操作系统操作磁盘的基本单位，一般操作系统中一页的大小为4kb。

   在mysql中可以使用如下的命令查看Innodb引擎页的默认大小

 SHOW GLOBAL STATUS LIKE 'Innodb_page_size' 

Mysql的页的大小默认是16kb，B+数的设计非常适合读取数据。

   如果节点又存储数据和索引，数据比较多，索引就比较少，那么树的高度就越高。导致磁盘IO变多，效率变低。

3.2 叶子节点可以存储多少数据

假设一行记录的大小为1kb（其实已经比较大了）。

那么一个叶子节点就能存储16条数据。

非叶子节点里面存储的是索引的值和指针，mysql默认的索引值大小是8B，指针大小是6B，合在一起是14B。那么非叶子节点可以存储的索引+指针的个数为：

16*1024/14 = 1170 个

那么如果数的高度是2层，叶子节点的个数也就是 1170个，那么可以存储的数据条数为：1170 * 16 = 18720条。

如果树的高度是3层，那每个叶子节点可以存储的条数为：1170*1170*16= 21902400条

3.3 引擎存储结构

数据文件：

user.frm是创建表的文件

user.ibd是数据+索引文件

索引Innodb引擎是聚集索引，索引和数据文件在一起的。

如果已name字段设置索引，那就是二级索引（辅助索引）。叶子节点中存储的该列的数据和主键值，也就意味着还需要再通过主键去查找一次数据。

3.4 myisam引存储结构

可以使用命令创建一张新的表结构，使用myisam引擎来创建。Myisam引擎的索引采用的非聚集索引，索引和表数据是分开存储的。

        user2.frm文件是创建表的文件 

        user2.MYD文件是表的数据文件

        user2.MYI文件是索引文件

        myisam引擎的索引结构图。

MYI索引文件：

MYD数据文件：

Myisam引擎通过索引查找数据的时候，通过索引值找到对应的地址，通过地址找到数据。

如果已name字段设置索引，效果是一样的。