MySQL索引实现原理

2、MySQL 索引实现原理

MySQ 索引的原理和数据结构能介绍一下吗、MySQL 聚簇索引和非聚簇索引的区别是什么、他们分别是如何存储的？使用 MySQL 索引都有哪些原则、MySQL 复合索引如何使用。

基本会涉及这些知识点，稍微好点公司的面试官哈。

2.1）、索引存储结构

其实大多面试官问你 mysql 的索引底层是什么数据结构实现的，可能还会现场让你优化 sql，为什么如此优化呢；还可能问你数据库常见的使用规则呢。

mysql 的索引就是用一个数据结构组织某一列的数据，然后如果你要根据那一列的数据查询的时候，就可以不用全表扫描，只要根据那个特定的数据结构去找到那一列的值，然后找到对应的行的物理地址即可。

mysql 的索引实现是基于 B+ 树，这样查找数据的高度就算数据量很大，也很低，关于 B+ 树原理和实现，可以参考任何一本数据结构的书籍了解下哈。

myisam 存储引擎的索引中，每个叶子节点的 data 存放的是数据行的物理地址，比如 0x07 之类的东西，一行一行的，每行对应一个物理地址。

innodb 存储引擎的索引实现，跟 myisam 最大的区别在于，innodb 的数据文件本身就是个索引文件，就是 key 就是主键，然后叶子节点的 data 就是那个数据行。

innodb 存储引擎，必须有主键，可以默认内置的就会根据主键（6 字节的 rowid）建立一个索引，叫做聚簇索引，innodb 的数据文件本身同时也是个索引文件，这个索引就是默认根据主键建立的叫做聚簇索引。

innodb 这种原生的数据文件就是索引文件的组织结构，就叫默认的主键索引为聚簇索引。就是因为这个原因，innodb 表是要求必须有主键的，但是 myisam 表不要求必须有主键。另外一个是，innodb 存储引擎下，如果对某个非主键的字段创建个索引，那么最后那个叶子节点的值就是主键的值，因为可以用主键的值到聚簇索引里根据主键值再次查找到数据。

一般 innodb 表里，建议统一用 auto_increment 自增值作为主键值，因为这样可以保持聚簇索引直接加记录就可以，如果用那种不是单调递增的主键值，可能会导致 b+ 树分裂后重新组织，会浪费时间。这也就是为啥 innodb 下不要用 UUID 生成的超长字符串作为主键？因为这么玩儿会导致所有的索引的 data 都是那个主键值，最终导致索引会变得过大，浪费很多磁盘空间。

2.2）、索引使用规则

尽量根据 where 条件、选择列，综合考虑建立最合适的索引。

2.2.1）、全列匹配

尽量 where 条件里，根据最左匹配原则，建立联合索引。

比如 select name,place_id from car_parking_place where car_id=xxxx and ctime='2018-01-10 00：00:00'; 就可以建立 create index (car_id,ctime)。

2.2.2)、最左前缀匹配

如果你的 sql 里，正好就用到了联合索引最左边的一个或者几个列表，那么也可以用上这个索引，在索引里查找的时候就用最左边的几个列。

2.2.3）、前缀匹配

如果你不是等值的，比如 =，>=，<= 的操作，是 like 操作，那么必须要是 like ‘XX%’ 这种才可以用上索引。

2.2.4）、范围列匹配

如果你是范围查询，比如 >=，<=，between操作，你只能是符合最左前缀的规则才可以范围，范围之后的列就不用索引。

2.2.5）、禁用函数

目前 mysql 还不支持函数索引，如果你对某个列用了函数，比如 substring 之类的东西，那么那一列不用索引。

2.3）、索引的缺点

索引是有缺点的，比如常见的就是会增加磁盘消耗，因为要占用磁盘文件，同时高并发的时候频繁插入和修改索引，会导致性能损耗的。尽量创建少的索引，比如说一个表一两个索引，两三个索引，十几个索引，高并发场景下还可以。

尽量对表中唯一性相对较高、经常查询的字段建立索引，那种比如 sex（性别），世上就男女可能2种值，建立索引，几乎跟全表扫描没什么区别。