白佛网站建设,wordpress 角色和权限管理,首页2免费八度电影院,网站开发建面向面试知识–MySQL数据库与索引
优化难点与面试点
什么是MySQL索引#xff1f;
索引的MySQL官方定义#xff1a;索引是帮助MySQL快速获取数据的数据结构。 动力节点原文#xff1a; MysQL官方对于索引的定义:索引是帮助MySQL高效获取数据的数据结构。 MysQL在存储数据之…面向面试知识–MySQL数据库与索引
优化难点与面试点
什么是MySQL索引
索引的MySQL官方定义索引是帮助MySQL快速获取数据的数据结构。 动力节点原文 MysQL官方对于索引的定义:索引是帮助MySQL高效获取数据的数据结构。 MysQL在存储数据之外数据库系统中还维护着满足特定查找算法的数据结构这些数据结构以某种引用(指向)表中的数据,这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。 简单理解为“排好序的帮助我们快速查找数据的数据结构” 索引的分类
逻辑分类
按照功能划分
主键索引一张表只能有一个主键索引不允许重复、不允许为null唯一索引数据列不允许重复允许为 NULL 值一张表可有多个唯一索引索引列的值必须唯一但允许有空值。如果是组合索引则列值的组合必须唯一。普通索引一张表可以创建多个普通索引一个普通索引可以包含多个字段允许数据重复允许 NULL 值插入阿里开发者手册高并发场景的表索引数量控制在五个以内全文索引它查找的是文本中的关键词主要用于全文检索。篇幅较长下文有独立主题说明
按照列数划分
单列索引一个索引只包含一个列一个表可以有多个单例索引。组合索引一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。索引失效的坑
物理分类
聚簇索引 聚簇是为了提高某个属性(或属性组)的查询速度把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。 聚簇索引clustered index不是单独的一种索引类型而是一种数据存储方式。这种存储方式是依靠B树来实现的根据表的主键构造一棵B树且B树叶子节点存放的都是表的行记录数据时方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块找到索引也就找到了数据。 优点 查询和更新快
数据访问速度快因为聚簇索引将索引和数据都放在同一棵B树因此聚簇索引中获取数据比飞聚簇索引更快聚簇索引对于主键的排序查找和范围查找速度非常快。 缺点 删除和插入慢插入速度严重依赖于插入顺序插入速度严重依赖于插入顺序按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键主键列不要选没有意义的自增列选经常查询的条件列才好不然无法体现其主键索引性能更新主键代价很高不推荐更改主键更新主键的代价很高因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新。二级索引需要两次索引查找回表二级索引访问需要两次索引查找第一次找到主键值第二次根据主键值找到行数据。 补充MySQL中key、primary key、unique key、与index的区别。 详见文章一文搞懂MySQL索引清晰明了 非聚簇索引
数据和索引是分开的B树叶子节点存放的不是数据表的行记录。 虽然InnoDB和MyISAM存储引擎都默认使用B树结构存储索引但是只有InnoDB的主键索引才是聚簇索引InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。
InnoDB和MyISAM索引实现索引的数据结构
InnoDB索引实现
InnoDB使用B树存储数据除了主键索引为聚簇索引其他索引均为非聚簇索引。 一个表中只能存在一个聚簇索引主键索引但是可以存在多个非聚簇索引。 InnoDB表和索引的数据是在一起的表数据和索引的文件都放在.ibd文件中。
聚簇索引主键索引
B树叶子节点包含数据表中行记录就是聚簇索引索引和数据是存放在一块的 可以看到叶子节点包含了完整的数据记录这就是聚簇索引。因为InnoDB的数据文件.idb按主键聚集所以InnoDB必须有主键MyISAM可以没有如果没有显示指定主键则选取首个为唯一且非空的列作为主键索引如果还没具备则MySQL自动为InnoDB表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整形。 ; 主键索引结构分析
数据保存以key-value的形式保存。 B树所有的数据都存放在叶子结点非叶子结点只保存键值方便查找 B树所有的结点都保存数据 对于MySQL中的InnoDB数据库引擎的主索引索引即数据数据即索引。方便范围查找以及顺序查找同时存在普通索引帮助查找某些被查找频率较高的字段。如果需要较多索引推荐使用联合索引以提高索引的利用效率。
索引的优缺点
参考文章一文搞懂MySQL索引清晰明了
MySQL体系结构 自上而下分为四层 ① 网络接入层 ② 服务层 ③ 存储引擎层 ④ 文件系统层
网络接入层
提供了应用程序进入MySQL服务的接口。客户端与服务器建立连接客户端发送SQL到服务端。
服务层
管理工具和服务
系统管理和控制工具例如备份恢复、MySQL复制、集群等。
连接池
主要负责连接管理、授权认证、安全等。 主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时服务器对其进行认证。可以通过用户名与密码认证也可以通过SSL证书进行认证。登录认证后服务器还会验证客户端是否有执行某个查询的操作权限。
SQL接口
查询解析器
查询优化器
缓存8.0以前支持查询缓存8.0之后就不支持了
存储引擎层
索引是占用物理空间的在不同的的存储引擎中索引存在的文件也不同。存储引擎是给予表的以下是分别使用MyISAM和InnoDB分别建立的两张表。 当存储引擎是MyISAM时
*.frm与表相关的元数据信息都存放在frm文件包括表结构的定义信息等*.MYDMyISAM DATA用于存储MyISAM表的数据*.MYIMyISAM INDEX用于存储MyISAM表的索引信息 当是InnoDB时
文件系统层
*.frm与表相关的元数据信息都存放在frm文件包括表结构的定义信息等
*.ibdInnoDB DATA表数据和索引的文件。该表的索引B树的每个非叶子节点存储索引叶子节点存储索引和索引对应的数据
参考文章10分钟了解MySQL体系构架、存储引擎和索引结构
