MySQL索引和引擎

一.索引

应用

在使用MySQL或其他数据库的时候，往往需要给表添加索引，这样一般可以给数据的查询速度带来极大的提升。

概念

索引，可以理解成一本书的目录，用于快速了解和定位书本中的内容。而在MySQL中，索引是帮助其高效获取数据的、已排好序的数据结构

索引的数据结构

• 二叉树
• 红黑树
• Hash表
• B-Tree
• …

索引案例

比如现在，MySQL中有这么一张表 test，需要进行查询（select * from test where id = 5）

id	value
1	12
2	23
3	232
4	121
5	45
6	67
7	55

在不使用索引时，MySQL将会对全表进行扫描，取得满足条件的数据。这样的话，如果数据量特别大，这样就会很浪费时间了。所以，为了提升效率，对表加入索引。

使用不同索引进行检测

假设使用二叉树结构

根据二叉树的特性，在进行数据查找时只需要对某一侧进行查询即可，确实可以缩短查找需要的时间，但这样还是需要进行5次查询，在数据量大时依旧不是最优的结构。

假设使用红黑树

红黑树能够保持自动平衡，这的确缩短了查询的时间，可是如果存在大量数据，则红黑树的层数将很高，这样的话，就需要进行一层一层的查询，一次次的IO，必然影响了性能；所以这依旧不是最优的方案。

假设使用Hash表/散列表（MySQL自带）

根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。但是，在进行范围查询时，就失去了优势了。

B-Tree

叶子节点具有相同的深度，一个节点中有多个索引和数据，且节点中的数据索引从左到右依次递增，但是没有很好的办法解决范围查询的问题。

B+Tree（B-Tree的变种，MySQL自带、默认）

非叶子节点不存储data，只存储索引，这样可以存储更多的索引、降低树高度；数据记录都存放在叶子节点中，且叶子节点之间还有链指针，满足了范围查找问题。

二.MySQL存储引擎

在使用时常见的M有SQL表引擎有MyISAM和InnoDB。

1）MyISAM引擎

• 非聚集索引：索引和数据分开，叶子节点存储数据的位置指针，需要进行二次查询才可以获得数据。

创建MyISAM引擎的表

-- 在创建时需要指定引擎
create table temp (id int(10)  primary key,
	name varchar(50),
    age int(10)
)
engine=MyISAM;

此时查看$MYSQL_PATH/data/test，可以看到会多出三个文件：temp.frm、temp.MYD、temp.MYI

-- 对应数据表结构的定义文件
temp.frm

-- 存储所有数据行
temp.MYD

-- 存储了索引
temp.MYI

当有相关查询时，数据库就会通过temp.MYI文件快速定位到元素；在MyISAM引擎中，B+Tree的叶子节点的data元素存储的是元素行的磁盘文件指针，所以它会去查找temp.MYD文件，从中取得数据。

2）InnoDB引擎

• 聚集索引：索引和数据是聚集在一起，叶子节点就是数据节点，通过索引就能直接获取数据。

创建一张student表，若没有修改过SQL配置，那么默认就是InnoDB引擎

此时查看$MYSQL_PATH/data/test，可以看到多出两个文件：student.frm、student.ibd

-- 对应数据表结构的定义文件
student.frm

-- 存储了完整的索引以及数据记录
student.ibd

• InnoDB必须存在主键，因为必须要有一个标识来维护B+Tree结构。如果用户没有自己创建主键，InnoDB会在底层自动创建一个主键（比如：rowid）。
• InnoDB推荐使用Int类型自增主键，因为更加比较起来更加简便，可以提升性能