MySQL底层索引的原理

MySQL引擎：

MySQL索引是基于引擎级别，引擎分为InnoDB和MyISAM两种。虽然二者索引的底层都是由B+树实现。但在使用形式上又有所不同！

---

MySQL索引：

MySQL的索引分为很多种：主键索引、普通索引、联合索引等……这里主要讲主键、普通以及联合索引在InnoDB存储引擎上的实现原理

InnoDB

1、主键索引：（PRIMARY KEY）又叫聚簇索引，在Innodb存储级别上，每个数据表都要有一个主键索引，如果你没有设置主键，则会默认生成一列不可见的主键列。推荐使用自增数字主键！
2、 普通索引：（Index）普普通通，加快查询效率

--create index [INDEX_NAME] on [TABLE_NAME] ([column_NAME](length))
create index userNameIndex on t_user (`userName`(50))
--alter table [TABLE] add index [INDEX_NAME] ([column_NAME](length))
ALTER table t_user ADD INDEX user_name_index (`user_name`(50))

3、 联合索引：多个字段组合成一个索引列。遵从【最左原则】

--create index [INDEX_NAME] on [TABLE_NAME] ([column_NAME],[column_NAME2](length))
create index user_name_p on t_user (`user_name`(50),`password`(50))
--alter table [TABLE] add index [INDEX_NAME] ([column_NAME][column_NAME2](length))
ALTER table t_user ADD INDEX user_name_index (`user_name`,`password`(50))

---

原理分析：

B+树特点：

1、数据存储有序
2、多路查找（相比二叉树）
3、每个叶子节点可存放多个元素
4、叶子与叶子节点相互链路（跟B树的区别）
5、所有非叶子（根）节点的数据会在叶子节点冗余一份

索引特点：

1、主键索引：底层由B+树实现，树的非叶子（根）节点存储主键值，叶子节点存储所有的数据+主键。所有索引中，主键查找效率是最高的！
（MyISAM）：叶子节点存放实际数据的地址

2、普通索引：跟主键索引有些不同，它的根节点存储索引值（加了普通索引字段的值），叶子节点存放的不是所有数据，而是（索引值+主键值）。如果是普通索引查找，当找到对应的叶子节点后，还需根据拿到的主键值，再去走一遍主键索引的查找，才能拿到想查询的数据。这一过程称之为“回表”！
（MyISAM）：叶子节点存放实际数据的地址（不用回表）

3、联合索引：联合索引就是特殊的普通索引，它是可以将多个字段组合在一起创建一个联合的索引。能满足一些多条件查询且需快速查找的场景。使用联合索引需遵从一个“最左原则”。

打个比方：

create index user_name_p on t_user (`user_name`(50),`password`(50))

我创建一个user_name和password的联合索引。当我在多条件查询时，想快速查找，走索引。那么我的sql语句就一定要有user_name

select * from t_user where user_name = XXX and password = XXX 或
select * from t_user where password = XXX and user_name = XXX 或
select * from t_user where user_name = XXX 

这三种情况都是会走索引的，最左原则就是，左边的字段一定要有，否则索引将不生效。走全表扫描！

4、最左原则：不止联合索引，普通索引查询时也应遵循最左原则。例：

select * from t_user where user_name like 'a%'  --走索引
select * from t_user where user_name like '%a'  --不走索引

总结：索引的创建，就是以空间换时间！创建索引需要额外占用磁盘空间，而且当数据有更新的时候，也要额外更新索引。所以索引也不是越多越好！

---

问题：

1、为什么加索引能提升查询速度？
答：创建索引后，数据库会额外维护一个根据索引值排好序的索引树，当查询的语句的筛选条件中有索引列时，则会去查索引树，由于是排好序的数据。根据二分查找法，很容易定位到想查询的数据所在。另外，假如你索引没有设计好，使用索引查询的时间大于全表扫描的时间时，则会走全表扫描
2、为什么推荐使用自增Id当主键？
答：因为有序，由于树这个数据结构的特性，使得要想成为树结构，就必须保证有序。自增Id就完全符合这个特点，在数据插入时，不必额外去给主键排序。能有效提升数据插入性能；占用空间小！占用空间越小，一个数据页就能放更多的数据。数据页越少，查询相应加快。插入时也不用频繁分页。
3、什么是数据页？
答：计算机从磁盘读取数据就是以页为单位的，当你想获取某个资源时，计算机会将这个资源前后的一些数据一起返回给你。这样你下次再想获取相邻资源的时候，就能减少一次磁盘IO，提升查询性能。Mysql也是以这种方式存储、读取数据。按主键值顺序分页。假如一页能存16KB的数据，那么Mysql将会把你插入的数据一条一条顺序放到数据页里。存放满了就新开一页，依次循环……假如说现有一页存放了10条数据：主键id 1~10。当你想查询id=8的数据时，第一次查询，mysql会将1-10这一整页从磁盘中一次性拿走，缓存到内存中。当你下次又想查询id=2的记录时，则可以直接将内存中的数据返回。内存要比磁盘快很多。