MySQL索引与存储引擎-优快云博客

本文链接：https://blog.youkuaiyun.com/cwl_0514/article/details/106135354

一.两种储存引擎：

myisam
- 不支持事务，不支持外键约束，索引文件和数据文件分开，这样在内存里可以缓存更多的索引，对查询的性能会更好，适用于那种少量的插入，大量查询的场景
innodb
- mysql 5.5之后的默认存储引擎,主要特点就是支持事务，走聚簇索引，强制要求有主键，支持外键约束，高并发、大数据量、高可用等相关成熟的数据库架构，分库分表、读写分离、主备切换

二. B+树和B-树结构图

B-树结构图:
在这里插入图片描述

B+树结构图:
在这里插入图片描述

三. 什么是索引：

mysql的索引说白了就是用一个数据结构组织某一列的数据，然后如果你要根据那一列的数据查询的时候，就可以不用全表扫描，只要根据那个特定的数据结构去找到那一列的值，然后找到对应的行的物理地址即可。（数据库索引是表中的一个特殊的数据结构，存放的记录的快速检索的值，也称为目录）

四.myisam数据索引的实现

在myisam存储引擎的索引中，每个叶子节点的data存放的是数据行的物理地址，物理地址指向对应的存放数据：
在这里插入图片描述

五. innodb存储引擎的索引

innodb的数据文件本身就是个索引文件，就是主键key，然后叶子节点的data就是那个数据的所在行。
在这里插入图片描述
innodb存储引擎，要求必须有主键，会根据主键建立一个默认索引，叫做聚簇索引，innodb的数据文件本身同时也是个索引文件，索引存储结构大致如下：
15，data：完整的一行数据，（34,Bob，22）
18，data：完整的一行数据，（77,Alice，33）

innodb某个非主键的字段创建个索引
如果对某个非主键的字段创建个索引，那么最后那个叶子节点的值就是主键的值，因为可以用主键的值到聚簇索引里根据主键值再次查找到数据，即所谓的回表。例如如下：

select * from table where name = ‘Bob’，
先到name的索引里去找，找到张三对应的叶子节点，叶子节点的data就是那一行的主键，在根据主键索引找到需要的数据。

六. 索引的使用规则

create index (shop_id,product_id,gmt_create)

全列匹配
这个就是说，你的一个sql里，正好where条件里就用了这3个字段，那么就一定可以用到这个联合索引的：
select * from product where shop_id=1 and product_id=1 and gmt_create=’2018-01-01 10:00:00
最左前缀匹配
这个就是说，如果你的sql里，正好就用到了联合索引最左边的一个或者几个列表，那么也可以用上这个索引，在索引里查找的时候就用最左边的几个列就行了：
select * from product where shop_id=1 and product_id=1，这个是没问题的，可以用上这个索引的
最左前缀匹配了，但是中间某个值没匹配
这个是说，如果你的sql里，就用了联合索引的第一个列和第三个列，那么会按照第一个列值在索引里找，找完以后对结果集扫描一遍根据第三个列来过滤，第三个列是不走索引去搜索的，就是有一个额外的过滤的工作，但是还能用到索引，所以也还好,例如：
select * from product where shop_id=1 and gmt_create=’2018-01-01 10:00:00’
没有最左前缀匹配
那就不行了，那就在搞笑了，一定不会用索引，所以这个错误千万别犯
select * from product where product_id=1，这个肯定不行
前缀匹配
这个就是说，如果你不是等值的，比如=，>=，<=的操作，而是like操作，那么必须要是like ‘XX%’这种才可以用上索引，比如说
select * from product where shop_id=1 and product_id=1 and gmt_create like ‘2018%’
范围列匹配
如果你是范围查询，比如>=，<=，between操作，你只能是符合最左前缀的规则才可以范围，范围之后的列就不用索引了
select * from product where shop_id>=1 and product_id=1
这里就在联合索引中根据shop_id来查询了
包含函数
如果你对某个列用了函数，比如substring之类的东西，那么那一列不用索引
select * from product where shop_id=1 and 函数(product_id) = 2
上面就根据shop_id在联合索引中查询

七.索引的缺点以及使用注意

优点:可以大大加快数据的检索速度，这也是创建索引的最主要的原因,且通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
缺点：增加磁盘消耗，因为要占用磁盘文件，同时高并发的时候频繁插入和修改索引，会导致性能损耗的

八. 索引分类

索引名称	创建命令
普通索引：在任何字段上都可以创建的索引	CREATE INDEX 索引名 on 表名（列名）
唯一索引：该列上的值不能重复	CREATE UNIQUE INDEX 索引名 on 表名（列名）
主键索引	表中的主键是自带主键索引，也就是说创建了主键就有主键索引
聚合索引	CREATE INDEX 索引名 on 表名（列名1，列名2），聚合索引按照从左到右的匹配原则。也就是必须先匹配列名1才能匹配列名2查询
全文索引（MyISAM独有的索引）	ALTER TABLE 表明 ADD FULLTEXT INDEX 索引名称(title, content)