html5 社团网站模板 代码下载,上海做营销网站哪个公司好,微盟小程序收费标准,云南网站建设一度科技公司简介
所有关于Mysql数据库优化的介绍仿佛都有存储引擎的身影。本文介绍Mysql常用的有MyISAM存储引擎和Innodb存储引擎#xff0c;还有常见的索引。 Mysql有两种常见的存储引擎#xff0c;MyISAM和Innodb#xff0c;它们各有优劣#xff0c;经过多次优化和迭代#xff0c;…简介
所有关于Mysql数据库优化的介绍仿佛都有存储引擎的身影。本文介绍Mysql常用的有MyISAM存储引擎和Innodb存储引擎还有常见的索引。 Mysql有两种常见的存储引擎MyISAM和Innodb它们各有优劣经过多次优化和迭代现在常用的一般是Innodb引擎因此MyISAM仅作基本介绍。
Innodb存储引擎
B树结构
Mysql默认采用B树存储结构MyISAM也是采用B树存储结构。然而两者的不同在于Innodb的叶子结点存储的数据而MyISAM的叶子结点存储的索引。 聚簇索引和非聚簇索引
由于Innodb的叶子结点存储的实际数据叶子结点又是B树索引结构的一部分因此这种将数据和索引结合起来的结构我理解为聚簇索引。而没有将实际数据和索引结构结合起来的索引理解为非聚簇索引。
聚簇索引 实际数据是索引结构的一部分
非聚簇索引实际数据不是索引结构的一部分虽然MyISAM存储引擎也是B树数据结构但其叶子结点存储的数据地址因此它属于非聚簇索引。 回到Innodb由于实际数据是索引结构的一部分存储于物理介质之中因此聚簇索引是唯一的对于Innodb而言一般主键索引是聚簇索引如果未设置主键索引为了保证Innodb的聚簇索引结构会自动生成主键。
主键索引与辅助索引
索引可以分为主键索引和辅助索引二级索引。
主键索引主键索引的要求就是key的唯一性对于Innodb而言是聚簇索引对于MyISAM而言是非聚簇索引。
辅助索引辅助索引都是非聚簇索引因为这些索引都不包含实际的数据只存储部分列值。它可以包含一列或多列字符串前缀等等。
辅助索引的原理是将部分列重新构建为一个新的B树结构其叶子结点存放主键的ID然后再通过主键ID回到主键索引中寻找实际的行数据这个过程称为回表从逻辑过程上来看辅助索引一般情况下会查询两次B树其耗时会更长因此在辅助索引优化过程中需要尽量减少回表次数。
如何减少回表次数是的辅助索引的优化原则之一就是尽量减少回表次数如果要减少回表次数就需要增加辅助索引的命中率。 根据辅助索引的特性可以分为普通索引联合索引覆盖索引前缀索引普通索引和前缀索引 之所以将它们放在一起是因为它们具有共性。前缀索引是通过截取字符串前N个字符构成一个索引普通索引是通过单列来构成。在构造这类索引时都需要考虑 每个索引数据的强唯一性这样才能保证辅助索引命中率更高回表次数更少。 联合索引 普通索引和前缀索引都是通过单列数据来构建而联合索引通过多列数据来构建。由于是多个列共同构建的索引因此它的结构如下 最左匹配原则Mysql一直向靠右的列匹配直到遇到范围查找like、、、between时停止。如果要弄清最左匹配原则需要明白联合索引的查找原理。
联合索引的查找原理 联合索引构建时会生成一颗B树这颗B树是有序的它的排序规则为 左侧排序优先当左侧相同时再排右侧。覆盖索引 如果查询的列值已经可以通过辅助索引得到则不需要回表这个称为覆盖索引。 单列索引和联合索引都可以建立成覆盖索引。
B树与Innodb的存储结构
B树是Mysql底层的数据结构其分为叶子结点和非叶子结点最底层为叶子结点上层为非叶子结点。通过从上至下的不断二分查找来快速定位。B树又称为Balanced Tree属于平衡二叉树。 由于B树只具备随机查找特性不具备连续查找特性因此在B树上衍生出了B树其底层为双向链表。 当进行查询时双向链表提供了顺序查询的基础。叶子结点和非叶子结点属于页结构除了页结构还有行、区、段、表空间等等。
存储结构由外到内表空间 - 段 - 区 - 页 - 行页是内存与磁盘交互的最小单位16KB。 也就是说Innodb内存每次最少取一个叶子结点的数据然后在内存中操作。 叶子结点中的页属于数据页。
explain关键字
通过explain关键字可以实现查询优化explain关键字将会给出所列sql的执行信息包括
id语句执行顺序的序号
key索引名称如果为null则没有索引
type: 访问类型常见值有system、const、ref、range、index、all
rows: 扫描的行数通过以上一些指标可以检测当前的sql语句执行效率如何。
MyISAM
MyISAM由于不支持事务、不支持行级锁、不支持数据容灾等特点它更多适用于查询较多的情况。 它是非聚簇索引叶子结点存放物理地址。拿到物理地址后再去数据堆里取数据。它的存储结构如下
MYD: 存放实际数据
MYI: 存放索引结构
FMI 存放表定义等
