Mysql学习笔记

注明： 内容为视频公开课总结的学习笔记

MYSQL学习笔记

目录

• 索引的本质
• 索引的数据结构
• 存储引擎----针对于表，不针对于库
• 聚集索引与非聚集索引
• 聚集索引和非聚集索引，单从结构的角度，谁更快？
• 回表
• 覆盖索引
• 联合索引的最左前缀原理
• mysql联合索引一定遵循最左前缀原则么？
• 为什么mysql要遵循最左前缀原理？
• Mysql锁机制详解

Mysql索引详解

1、索引的本质：

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

2、索引的数据结构

• 二叉树
  缺点：查找单边增加数据列时，链路长，相当于读取指定索引前的所有索引，影响效率。

• 红黑树
  缺点：数据量过大时树的高度过大，极端情况下，链路过长，影响效率。

• Hash表
  对索引的key进行一次hash计算就可以定位出数据存储的位置
  很多时候Hash索引要比B+Tree索引更高效
  缺点1：hash冲突问题
  缺点2：仅能满足"=",“IN”,但无法使用索引进行范围查询，例如 where col > 1 ；
  

• B-Tree

  • 叶节点具有相同的深度
  • 叶节点的指针为空
  • 节点中的数据索引从左向右递增排序
    

• B+Tree

  • 非叶子节点不存储data，只存储索引，可以放更多的索引
  • 双向顺序访问指针，提高区间访问的性能 ---- 例如：where col>20，可以通过顺序访问指针，将指向的数据放入结果集
    

3、存储引擎----针对于表，不针对于库

• ①MyISAM存储引擎（非聚集）

  • 索引文件和数据文件是分离的（非聚集）： .frm文件存储表结构， .MYD文件存储数据记录， .MYI文件存储索引。
  • 叶子节点存的是索引所在数据行的磁盘文件指针，通过磁盘文件指针从.MYD文件中快速定位数据。
  • 文件索引和非主键索引是一样的。
    
    

• ②InnoDB存储引擎（聚集）

  • 表数据文件本身就是按B+Tree组织的一个索引结构文件： .frm文件存储表结构， .ibd文件存储索引及数据记录。
  • 聚集索引----叶节点里包含完整的数据记录
  • 为什么InnoDB必须有主键？
    InnoDB通过主键租住B+Tree数据文件，如果没有建立主键，mysql在后台会自己生成主键。
  • 为什么InnoDB推荐使用整形的自增主键？
    整形主键相对于UUID这种字符串主键，更节省内存中的空间，并且在索引进行比较时，效率更高；自增主键可以避免在叶子节点中间插入节点，从而导致原叶子节点分裂，而导致效率降低。
  • 为什么非主键索引结构的叶子节点存储的主键值？
    保持一致性和节省内存空间

• InnoDB存储引擎的表只会有一个聚集索引，就是主键索引（Primary Key），其他普通索引(Secondary Key)都是非聚集索引 ，叶子节点存储的是主键值。
  

4、聚集索引与非聚集索引

- 聚集索引定义： 叶节点里包含完整的数据记录，如InnoDB的主键索引。
- 非聚集索引定义： 索引与对应表的数据分开存储，如MyISAM的主键索引与非主键索引。
- 简单的说：聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的索引顺序与数据的物理排列顺序无关。
- 正式聚簇索引的顺序就是数据的物理存储顺序，所以一个表最多只能有一个聚簇索引，因为物理存储顺序只能有一个顺序。正因为一个表最多只能有一个聚簇索引，所以一个表设置什么为聚簇索引对性能很关键。

5、聚集索引和非聚集索引，单从结构的角度，谁更快？

聚集索引更快，因为非聚集索引需要利用索引获取数据需要跨文件，多一次磁盘IO。

6、回表

当mysql查询时，使用非聚簇索引（二级索引，辅助索引也是非聚簇索引），查到相应的叶子节点获取主键值，然后通过 主键索引（聚簇索引） 再查到相应的数据行信息，找到主键后通过聚簇索引找到相应数据行的过程叫做回表。

7、覆盖索引

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点：
1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。

8、联合索引的最左前缀原理

CREATE TABLE users1 (
id int(11) NOT NULL AUTO_INCREMENT,
nick varchar(30) DEFAULT NULL,
phone varchar(25) NOT NULL,
password varchar(64) DEFAULT NULL,
email varchar(50) DEFAULT NULL,
account varchar(15) DEFAULT NULL,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_nick_phone (nick,phone,create_time) USING BTREE --联合索引
) ENGINE=InnoDB AUTO_INCREMENT=9980018 DEFAULT CHARSET=utf8mb4;


explain select * from users1 where nick = 'cat';   --使用索引
explain select * from users1 where nick = 'cat' and create_time= '2020-02-09';  --使用索引
explain select * from users1 where phone = '13700000000';  --全表扫描，索引失效

这种情况指的是只使用索引的第一列进行匹配，如果使用索引的第二列查询，无法命中索引；
如果where条件没有使用到最左列，无法使用索引，根据phone 、create_time查询无法使用索引，因为这两列不是最左列。

mysql联合索引一定遵循最左前缀原则么？

不一定。

MYSQL8.0开始，增加了索引跳跃扫描：当第一列索引的唯一值较少，即使where条件没有第一列索引，查询的时候也可以用到联合索引。

例如

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '姓名',
  `gender` tinyint NOT NULL COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_gender_name` (`gender`,`name`)
) ENGINE=InnoDB COMMENT='用户表';

在性别和姓名两个字段上( gender , name )建立联合索引，性别字段只有两个枚举值。

执行sql：

explain select * from user where name='111';

虽然SQL查询条件只有name字段，但是从执行计划中看到依然是用了联合索引。
并且Extra列中显示增加了 Using index for skip scan ，表示用到了 索引跳跃扫描 的优化逻辑。

为什么mysql要遵循最左前缀原理？

首先，还是需要搞清楚索引的本质：

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

mysql会按照组合索引从左到右的顺序，为索引进行排序。

例如： KEY idx_nick_phone (nick,phone,create_time) USING BTREE

mysql会先为nick排序，然后再对phone排序，最后对create_time排序。

如果不遵循最左前缀原则使用索引，比如跳过nick，直接使用phone查询，索引的排序就失效了，也就是索引就失效了。

Mysql锁机制详解

传送门：
https://blog.csdn.net/weixin_40955398/article/details/122645924