MySQL底层原理及优化

1.执行原理

2.存储引擎

查看存储引擎

SHOW ENGINES;

2.1.InnoDB

• 默认的存储引擎
• 支持事务
• 天生支持行锁、手动支持表锁
• 支持外键
• 使用聚簇索引——只有主键索引是聚簇索引，其他索引是非聚簇索引

2.2.MyISAM

• 不支持事务
• 只支持表锁，不支持行锁
• 不支持外键
• 使用非聚簇索引

3.索引

一种能够提升查询性能的数据结构

底层采用的是B+树

分类

• 聚簇（集）索引【主键索引】
• 非聚簇（集）索引【非主键索引】

树的详解参考

(27条消息) b+树详解_源头源脑的博客-CSDN博客_b+树

B+树

• 非叶子节点——存储的是索引值 和指向下个索引值的的指针
• 叶子节点——冗余存储非叶子节点的索引值、存储索引值和数据、叶子节点之间存在指针，提升区间访问效率

为什么不选择hash？——因为hash只支持等值查询，不支持范围查询

主键索引

InnoDB引擎的表一定需要主键，如果不创建，数据库会自动创建并维护一个主键索引

非叶子节点存储的是主键值

叶子节点存储的是行数据

推荐使用主键自增：

因为插入的数据始终会放在最后面，可以快速的找到插入的位置，无需做额外的开销，如移动数据的位置，旋转树等

非主键索引

创建索引的要求

• 单表索引不超过5个
• 联合索引的字段不超过五个
• 经常增删改的字段不适合创建索引
• 枚举值字段不适合创建索引
• 不经常修改，经常查询的字段适合创建索引

回表：

每次创建索引的时候，数据库底层会创建一个B+树，非叶子节点存储的是索引的字段，叶子节点存储的是主键值，当你要查询的字段中没有索引，数据库会先查询索引的B+树找到主键值，再通过主键值来获取行数据，进行两次查询树，要想优化SQL应减少回表的产生。

4.SQL优化

 1.合理设计表结构

字段的数据类型、长度......

参考58军规版

58到家数据库30条军规解读_w3cschool

2.查询SQL优化

最左前缀法则

• 最终原则：尽量让SQL命中索引，提升查询性能
• 最左前缀法则：在使用联合索引时，必须满足从左边开始匹配索引列
• 
• 在写查询条件时，应该从联合索引得最左列开始依次编写

 索引失效的情况