Mysql学习

最新推荐文章于 2022-11-20 10:56:06 发布

呵呵哒666

最新推荐文章于 2022-11-20 10:56:06 发布

阅读量124

点赞数

分类专栏： # RDBMS

本文链接：https://blog.csdn.net/qq_34609370/article/details/101694343

版权

RDBMS 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、MySQL 体系结构
在这里插入图片描述
1、连接者：不同语言的代码程序和mysql的交互（SQL交互）
2、连接池管理、缓冲用户的连接，线程处理等需要缓存的需求
3、管理服务和工具组件系统管理和控制工具，例如备份恢复、Mysql复制、集群等
4、sql接口接受用户的SQL命令，并且返回用户需要查询的结果
5、查询解析器 SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)
6、查询优化器 SQL语句在查询之前会使用查询优化器对查询进行优化

select id,name from user where age = 40;
a、这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行age过滤
b、这个select查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤
c、将这两个查询条件联接起来生成最终查询结果

7、缓存如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
8、插入式存储引擎存储引擎说白了就是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

二、MySQL 数据库存储引擎
1、数据库存储引擎主要是算法+IO
【1】算法：如何存数据，比如存在什么样的文件里，格式是什么样的等等
【2】IO：和数据库交互时，IO的访问数据的方式，比如是根据物理地址访问，还是根据扇区连续索引依次访问等等

2、存储引擎的数据结构

【1】 B树（B-树）
B树是2-3树的一种扩展，对于M阶的B树来说：
（1）根节点至少有两个子节点
（2）每个节点至多有M-1个key，以升序排列，以及Nk+1个指针，其中Nk代表key的数量。
（3）对于一个key1来说，它左侧的指针指向的子节点的key值<=key1,右侧子指针指向的子节点的key值>key1（详见下图）
（4）其他节点至少有M/2个子节点
在这里插入图片描述关于B树中插入节点的过程，在下面这个博客中有一个动图解释的很详细：
http://www.cnblogs.com/yangecnu/p/Introduce-B-Tree-and-B-Plus-Tree.html
他的插入过程和红黑树很相似，总结一下就是：
（1）当要插入一个新值时，首先根据第三条原则找到他在叶子节点的位置并插入
（2）如果当前叶子节点的key数目等于M，那么就要拆分，拆分的过程就是把所有key通过一个中间值（如M=4取第二个数，M=5取第三个数），分成相同的两份（如果M是偶数会相差一），然后中间数为父节点，两边的树作为左右子节点，但要注意中间数是插入到他们的父节点中的，而不是新生成一棵树，这也就意味着如果这时候父节点的key树等于M，那么就要通过相同的变换把key值接着向上传递，直到key数<M。

【2】 B+树
存储引擎常用的一种数据结构，一种多叉平衡查找树，特点（对于M阶的B+树）：
（1）除叶子结点外所有节点都有M个键以及M个指向子节点的指针。
（2）所有叶子节点都在同一层
（3）非叶子结点的子树指针与关键字（Key）个数相同；
（4）非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
（5）为所有叶子结点增加一个链指针；
（6）所有关键字（key）都在叶子结点出现；，因此所有查找只会在叶子结点命中

结构如下图所示：
在这里插入图片描述
》》相比B树的优点：

（1）支持范围查找
（2）遍历更方便
（3）节省空间：因为B+树只有叶子节点才存数据，因此内部节点不需要只想关键字具体信息的指针。
（4）所有查询操作都需要命中子节点，所以是相同的。
PS: B*树就是在B+树基础上，为非叶子结点也增加链表指针

3、常见数据库存储引擎
（1）ISAM

主要适用于，查询远大于更新的场景。优点，读取操作速度很快，但不支持事务，不支持容错恢复。目前用的比较少

（2）MyISAM

ISAM 增强版（mysql 5.5之前版本，默认的是此引擎）
保留了读取速度很快的特点，进行了大量扩展，如索引功能。但表损坏后无法修复（硬盘崩溃可以）、不支持事务

（3）InnoDB

有跨越性特性的引擎，造就了目前MySQL 灵活性的产品(5.5以上常用版本的默认引擎。)
虽然查询性能没有ISAM和myISAM高，但整体来说处理大量数据时的组大性能设计，整体性能很高，支持事务和外键（悲观锁，乐观锁都有），耗用存储资源也比较小。

 5.7版本中，InnoDB管理的数据文件是 .frm文件（表和数据）和 .idb（索引）文件

（4）Memory

数据存在内存中，只有结构信息存在磁盘中，所以读取速度极快，但死机之后数据丢失。但应用场景极少，一般如临时数据。

问题：说说自己对于 MySQL 常见的两种存储引擎：MyISAM与 InnoDB的理解？

关于二者的对比与总结:

count运算上的区别：因为MyISAM缓存有表meta-data（行数等），因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。
是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
是否支持外键： MyISAM不支持，而InnoDB支持。

MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。在数据库做主从分离的情况下，经常选择MyISAM作为主库的存储引擎。一般来说，如果需要事务支持，并且有较高的并发读取频率(MyISAM的表锁的粒度太大，所以当该表写并发量较高时，要等待的查询就会很多了)，InnoDB是不错的选择。如果你的数据量很大（MyISAM支持压缩特性可以减少磁盘的空间占用），而且不需要支持事务时，MyISAM是最好的选择。

在这里插入图片描述
PS1：数据库锁介绍

表级锁：开销小，加锁块；不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发性也最高。
页面锁：开销和加锁界于表锁和行锁之间，会出现死锁；锁定粒度界与表锁和行锁之间，并发一般。

PS2: 那么为什么大家喜欢说 MyisAM 查询快呢？

那是因为，InnoDB 的表是根据主键进行展开的 B+tree 的聚集索引。MyIsam 则非聚集型索引，myisam 存储会有两个文件，一个是索引文件，另外一个是数据文件，其中索引文件中的索引指向数据文件中的表数据。
聚集型索引并不是一种单独的索引类型，而是一种存储方式，InnoDB 聚集型索引实际上是在同一结构中保存了 B+tree 索引和数据行。当有聚簇索引时，它的索引实际放在叶子页中。

可以看出：INNODB 在做 SELECT 的时候，要维护的东西比 MYISAM 引擎多很多。

【1】 INNODB 要缓存数据块，MYISAM 只缓存数据的索引块，这中间涉及的开销就少；
【2】 innodb 寻址要映射到块，再到行，MYISAM 记录的直接是数据的 OFFSET，定位比 INNODB 要快
【3】INNODB 还需要维护 MVCC 一致；虽然你的场景没有，但他还是需要去检查和维护 MVCC (Multi-Version Concurrency Control) 多版本并发控制。
【4】MYISAM 不支持事务，开销就小，也是它查询快的一个原因！

4、数据库存储引擎管理命令
【1】查看数据库支持的存储引擎
show engines
【2】查看数据库当钱使用的存储引擎
show variables like ‘%storage_engine%’
【3】查看数据库表所用的存储引擎
show create table table_name
【4】创建表指定存储引擎
create table table_name(column_name column_type) engine = engine_name
【5】修改表的存储引擎
alter table table_name engine=engine_name
【6】修改默认的存储引擎
在mysql配置文件中修改下述内容：
default-storage-engine=INNODB
Mysql配置文件：
windows系统-Mysql安装目录/my.ini
Linux系统 /etc/my.cnf

三、数据库索引
1、索引介绍

索引优点：创建索引可以大大提升系统的查询性能
原理：索引的值换算成该行数据在物理磁盘上的存储位置
所以：创建唯一性索引，保证每行数据的唯一性，大大加快数据检索速度

索引的缺点：耗费时间、耗费空间、拖累速度
（1）索引的创建和维护（数据库负责维护）需要耗费时间，并且索引需要占用物理空间。
（2）对数据表中的数据增加、修改时，索引页需要动态维护，降低了数据更新的速度

PS：

【1】什么样的字段适合建立索引？

（1）经常搜索的列上，可以加快搜索速度

（2）经常使用的外键，外键一般作为表间连接，可以加快连接速度

（3）经常使用在where子句的列上，加快条件判断速度
（where f1 and f2 ，必须在字段f1、f2上同时建立索引）

【2】什么样的字段不适合建立索引：

（1）不经常使用或数据值很少的列（如：人事表的性别）

（2）定义为text（大文本）、image、bit（文件）数据类型，因为数据量大或值很少

2、MySQL支持的索引类型，即索引的存储形式/数据结构

（1）B-Tree索引

索引按照平衡树（balancetree）的结构来存储，和普通树结构不同的是主要压缩树的深度（越深索引越慢），广度比较大，索引时比较快

（2）Full-text索引

全文索引，存储结构也是，平衡树。主要把索引中的关键字进行了全数据排列，是解决需要用like查询效率低的问题，只能解决"**%"形式的like查询。

3、索引的建立和管理

【1】MySQL通过命令行创建索引

四大索引（普通索引、唯一索引、全文索引、聚合索引）

（1）普通索引

最基础的索引，允许建立在任意的字段中，没有严格要求，可以有空数据。也是5.5以上默认的索引类型（5.5以上存储引擎是InnoDB，InnoDB默认索引时普通索引）。

》》creat index [index_name] on [table_name]（column(length)）直接创建方式

如：creat index name_index on users（name(3)）给users表中的name列（取该列数据的前三位放到索引里，可以不写，即所有数据都放到索引里），创建索引名字为name_index，

》》查看：show index form user

alter table [table_name] and index [index_name]（column(length)）修改表结构的方式添加索引

查看索引

show index from [table_name]

show keys from [table_name]

（2）唯一索引
创建唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复（在插入数据的时候，如果唯一索引的插入值重复，所以插入就会报错），索引可以有多个但索引列的值必须唯一，索引列的值允许有空值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE把它定义为一个唯一索引。