代码编织梦想

存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信,这些API屏蔽了不同存储引擎之间的差异,使得这些差异对上层查询过程透明。存储引擎不会去解析SQL。

二、对比InnoDB与MyISAM


1、 存储结构

MyISAM:每个MyISAM在磁盘上存储成三个文件。分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

2、 存储空间

MyISAM: MyISAM支持支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后,不会再进行修改操作,可以使用压缩表,极大的减少磁盘的空间占用。

InnoDB: 需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3、 可移植性、备份及恢复

MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。

4、 事务支持

MyISAM:强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。

InnoDB:提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

5、 AUTO_INCREMENT

MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。

InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。

6、 表锁差异

MyISAM: 只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。

InnoDB: 支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。

7、 全文索引

MyISAM:支持 FULLTEXT类型的全文索引

InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。

8、表主键

MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。

InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。

9、表的具体行数

MyISAM: 保存有表的总行数,如果select count() from table;会直接取出出该值。

InnoDB: 没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。

10、CRUD操作

MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。

InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。

11、 外键

MyISAM:不支持

InnoDB:支持

三、sql优化简介


1、什么情况下进行sql优化

性能低、执行时间太长、等待时间太长、连接查询、索引失效。

2、sql语句执行过程

(1)编写过程


select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

(2)解析过程


from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

3、sql优化就是优化索引

索引相当于书的目录。

索引的数据结构是B+树。

四、索引


1、索引的优势

(1)提高查询效率(降低IO使用率)

(2)降低CPU使用率

比如查询order by age desc,因为B+索引树本身就是排好序的,所以再查询如果触发索引,就不用再重新查询了。

2、索引的弊端

(1)索引本身很大,可以存放在内存或硬盘上,通常存储在硬盘上。

(2)索引不是所有情况都使用,比如①少量数据②频繁变化的字段③很少使用的字段

(3)索引会降低增删改的效率

3、索引的分类

(1)单值索引

(2)唯一索引

(3)联合索引

(4)主键索引

备注:唯一索引和主键索引唯一的区别:主键索引不能为null

4、创建索引


alter table user add INDEX `user_index_username_password` (`username`,`password`)

5、MySQL索引原理 -> B+树

MySQL索引的底层数据结构是B+树

B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构。

B-Tree结构图中每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同:

非叶子节点只存储键值信息。

所有叶子节点之间都有一个链指针。

数据记录都存放在叶子节点中。

将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:

通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

可能上面例子中只有22条数据记录,看不出B+Tree的优点,下面做一个推算:

InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为〖10〗3)。也就是说一个深度为3的B+Tree索引可以维护103 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在24层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引(clustered index)和辅助索引(secondary index)。上面的B+Tree示例图在数据库中的实现即为聚集索引,聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,InnoDB存储引擎会遍历辅助索引找到主键,然后再通过主键在聚集索引中找到完整的行记录数据。

五、如何触发联合索引


1、对user表建立联合索引username、password

2、触发联合索引

(1)使用联合索引的全部索引键可触发联合索引

(2)使用联合索引的全部索引键,但是用or连接的,不可触发联合索引

(3)单独使用联合索引的左边第一个字段时,可触发联合索引

(4)单独使用联合索引的其它字段时,不可触发联合索引

六、分析sql的执行计划—explain


explain可以模拟sql优化执行sql语句。

1、explan使用简介

(1)用户表

(2)部门表

(3)未触发索引

(4)触发索引

(5)结果分析

explain中第一行出现的表是驱动表。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/m0_60707579/article/details/119996141

mysql索引原理-爱代码爱编程

B+树索引是B+树在数据库中的一种实现,是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡(balance),而不是二叉(binary),因为B+树是从最早的平衡二叉树演化而来的。在讲B+树之前必须先了解二叉查

mysql中innodb和myisam对比及索引原理区别_杰哥一号号的博客-爱代码爱编程_myisam和inndb

InnoDB和MyISAM是很多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,5.7之后就不一样了 1、事务和外键 InnoDB具有事务,支持4个事务隔离级别,回滚,崩溃修复能力和多版本并发的事务安全,包括ACID。如果应用中需要执行大量的INSERT或UPDATE操作,则应该使用InnoDB,这样可以提高多用户并发操作的性能

myisam与innodb 的区别(9个不同点)_chackca的博客-爱代码爱编程_myisam和innodb的区别

区别: 1. InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;  2. InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败;  3. InnoDB是聚集索引,使

mysql索引实现原理分析_代码搬运工.的博客-爱代码爱编程_mysql索引原理

       目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tr

myisam和innodb 区别总结_守护那份情的博客-爱代码爱编程

MyISAM和InnoDB 区别总结   MyISAM是MySQL的默认数据库引擎(5.5版之前).   5.5版本之后,MySQL数据库引擎默认InnoDB。 MyISAM和InnoDB两者之间有着明显区别: 1)

学习笔记 | 完整的MySQL知识体系 思维导图-爱代码爱编程

学习目标: 数据库的架构设计、性能调优、大规模数据库集群运维如何利用 MySQL 来支撑互联网的海量数据和高并发请求掌握 MySQL 的底层运行机制,教你如何发挥 MySQL 的最佳性能、让你掌握 MySQL 在互联网应用领域的最佳实践,学会如何处理亿级业务数据和高并发请求。MySQL 的 8 大优点 第一个是体积小、速度快,可以在不占用太多资源的情况

mysql parititon 索引_mysql索引是什么?浅谈mysql索引-爱代码爱编程

本篇文章给大家带来的内容是mysql索引是什么?浅谈mysql索引,让大家对mysql索引有一个简单的了解。有一定的参考价值,有需要的朋友可以参考一下,希望对你们有所帮助。 一:什么是索引 索引本身是一个独立的存储单位,在该单位里边有记录着数据表某个字段和字段对应的物理空间。索引内部有算法支持,可以使查询速度非常快。【相关视频教程推荐:mysql教

Java架构师成长路线总结2021版(持续更新)-爱代码爱编程

这个小程序免费使用 2013年年底的时候,我看到了网上流传的一个叫做《Java面试题大全》的东西,认真的阅读了以后发现里面的很多题目是重复且没有价值的题目,还有不少的参考答案也是错误的,于是我花了半个月时间对这个所谓的《Java面试大全》进行了全面的修订并重新发布在我的CSDN博客。在修订的过程中,参照了当时JDK最新版本(Java 7)给出了题目的答案和

java进阶路线_Java架构师技术进阶路线图详解-爱代码爱编程

在企业里,一名架构师已经可以算是高端人物了,但架构师也是需要学习的,任何人与事物都需要学习,下面我们就来了解一下Java架构师到底该如何进阶,请看如下。 一、阅读源码 深入的Java学习,经典源码阅读不可少: 常见的设计模式,编码必备 Spring5,做应用必不可少的框架 MyBatis,数据库必备框架 二、分布式架构 随着业务越

mysql索引-爱代码爱编程

索引的定义: 索引是帮助mysql搞笑获取数据的排好序的数据结构。 索引的数据结构: 二叉树 1.左子树上所有的节点的值均小于或等于其他的根节点的值。 2.右子树上所有的节点的值均大于或等于其他的根节点的值。 3.左右子树也分别为二叉排序树。红黑树 1.节点是红色或者黑色。 2.根节点是黑色。 3.每个叶子的节点都是黑色的空节点。 4.每个红色节点的

简述在MySQL 数据库中 MyISAM 和InnoDB 的区别-爱代码爱编程

MyISAM: 不支持事务, 但是每次查询都是原子的;支持表级锁, 即每次操作是对整个表加锁; 一个 MYISAM 表有三个文件: 索引文件、表结构文件、数据文件;采用菲聚集索引, 索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致, 但是辅索引不用保证唯一性。 InnoDb: 支持 ACID 的事务, 支持事务的四种隔离级别