MySQL原理

一、概论

使用索引查找数据性能很快，避免了全表扫描时的多次磁盘IO。

但是，使用索引实际上也需要在索引中查找数据，而且数据量和表中的是一样的

那为什么索引就能快呢？

这就跟索引使用了哪些数据结构有关

而索引是帮助Mysql高效获取数据的 排好序 的 数据结构

二、索引分类

主键索引

主键自带索引效果，也就意味着通过主键来查询表中的记录，性能是非常好的

普通索引

为普通的列创建索引

# 格式
create index 索引名称 on 表名(列名)
# 例子
create index idx_name on employees(name)

唯一索引

就像是唯一列，列中的数据是唯一的。性能比普通的性能更好

# 格式
create unique index 索引名称 on 表名(列名)
# 例子
create unique index index_name on employees(name)

联合索引（组合索引）

一次性维表中的多个字段一起创建索引，但建议不要超过5个；最左前缀法则（如何命中联合索引中的索引列）

# 格式
create index 索引名称 on 表名(列1,列2,列3)
# 例子
create index idx_name_age_position on employees(name,age,position)

全文索引

MyISAM存储引擎支持全文索引。在实际开发中，并不会使用MySQL提供的MyISAM存储引擎的全文索引功能来实现全文查找，而是会通过使用第三方的搜索引擎中间件比如说ElasicSearch（使用较多）、Solr

三、InnoDB和MyISAM的区别

InnoDB

• 也称聚簇索引——把索引和数据存放在一个**文件(.ibd)**中，通过找到所有后就能直接在索引树上的叶子节点中获取完整的数据
• 可以实现行/表锁
• 支持外键
• 支持事务

MyISAM

• 也称非聚簇索引——把索引和数据分开放到(.myi)和(.myd)文件中，查找到索引后还要去另一个文件中查找数据，相比聚簇索引而言查询性能会稍微差一些
• 天然支持表锁
• 支持全文索引
• 不支持外键
• 不支持事务

四、索引常问面试题

为什么非主键索引的叶子节点存放的数据是主键值

节约存储空间。避免存储冗余重复的数据

维护简单。因为id是唯一且难以变更的，数据的增删很难会影响到id的变化

​ 倘若存储的数据还包含其他非主键数据，当其被修改时不仅在数据库中需要修改，而且还需要对索引进行修改

为什么InnoDB表必须创建主键

增加性能。如果InnoDB表不创建主键，MySQL优化器会自动创建一个虚拟的主键，于是普通索引（辅助索引）就会使用这个虚拟主键进行查找，并且创建虚拟主键则会增加性能开销

为什么使用主键时推荐使用整型的自增主键

• 使用整型

避免不必要的开销。整型的数据大小是非常好比较的，如果使用字符串则需要进行依次编码后才能进行比较

• 使用自增

避免不必要的开销。当使用了不规律的数据时，索引树需要通过自旋的方式使叶子节点从小到大-从左到右有序排列，而多次的自旋会给系统带来一定的性能开销

五、联合索引和最左前缀法则

联合索引的存储结构

联合索引会使节点中存放的索引键增多

最左前缀法则

表示一条sql语句在联合索引中有没有走索引（命中索引/不会全表扫描）

# 创建联合索引
create index idex_a_b_c on table1(a,b,c)
# 判断sql语句有没有命中索引

# 命中a
select * from table1 where a = 10;
# 命中a
select * from table1 where a = 10 and b = 20;
# 命中a
select * from table1 where a = 10 and b = 20 and c = 30;
# 未命中b，因为a没有被命中，所以b也不会被命中
select * from table1 where b = 10;
# 未命中b也没命中c，因为a没有被命中，所以b也不会被命中，b没有被命中c也不会被命中
select * from table1 where b = 10 and c = 30;
# 命中a但没有命中b，因为b没有被命中，所以c也不会被命中
select * from table1 where a = 10 and c = 30;
# 既没有命中a，也没有命中b，更没有命中c
select * from table1 where c = 30;
# 命中abc，mysql中有一个内部优化器，会做一次内部优化
select * from table1 where a = 10 and c = 30 and b = 20;