MySQL基础

  |   0 评论   |   0 浏览

MySQL的where条件查询是从左往右的。(Oracle的where是从右往左的)

select * from A, B ; 这是笛卡尔积

select * from A, B where a.id =b.aid; 这个结果必须是a表中的id 和b表中的aid完全相等的结果才会显示。

MySQL

innodb引擎数据和索引存储文件是一个 .ibd 表的定义文件是 .frm

myisam引擎数据和索引是分开存储的 .myi 索引文件 .myd 数据文件 .frm 表定义文件

| 存储文件 | Innodb | Myisam |
| - | - | - |
| 存储文件 | .frm 表定义文件.ibd 数据文件和索引文件 | .frm 表定义文件.myd 数据文件.myi 索引文件 |
| 锁 | 表锁、行锁 | 表锁 |
| 事物 | 支持 | 不支持 |
| CRUD | 读、写 | 读多 |
| count | 扫表 | 专门存储的地方(加where也扫表) |
| 索引结构 | B+ Tree 聚集索引 | B+ Tree 非聚集索引 |
| 外键 | 支持 | 不支持 |

此外还有存储引擎类型 Memory (数据主要在内存中,所以可能丢数据)、Archive(压缩存储)、CSV(基于CSV格式文件存储)

注:

同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理,可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表,可以选择MEMORY存储引擎。


MySQL是通过文件系统对数据和索引进行存储的。

MySQL从物理结构上可以分为日志文件和数据索引文件。

MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下。

日志文件采用顺序IO方式存储、数据文件采用随机IO方式存储。

顺序io [首地址/偏移量](适合日志)
优势: 记录速度快,只能追加
劣势: 浪费空间

随机IO [记录地址0x2133] (适合数据+索引)
优势: 省空间
劣势: 相对慢

[mysqld]

#MySQL设置大小写不敏感:默认:区分表名的大小写,不区分列名的大小写

#0大小写敏感 1:大小写不敏感

lower_case_table_names=1

#默认字符集

character-set-server=utf8
#开启慢查询日志(记录执行时间超过long_query_time秒的所有查询,便于收集查询时间比较长的SQL语句)

slow_query_log=ON

#慢查询的阈值

long_query_time=3

#日志记录文件如果没有给出file_name值, 默认为主机名,后缀为-slow.log。如果给出了文件名,但不是绝对路径名,文件则写入数据目录。

slow_query_log_file=file_name
查看日志开启情况
show variables like 'log_%';
查看数据文件
SHOW VARIABLES LIKE '%datadir%';

数据文件:
InnoDB:
.frm 主要存放与表相关的数据信息,主要包括表结构定义信息
.ibd: 使用独享表空间存储表数据和索引信息,一张表对应一个ibd文件
.ibdata 使用共享表空间存储表数据和索引信息,所有表共同使用一个或者多个ibdata文件。

Myisam:
.frm: 主要存放与表相关的数据信息,主要包括表结构的定义信息
.myd: 主要存储表数据信息
.myi: 主要存储表数据文件中任何索引的数据树.

索引

数据结构可以查看以下链接

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B+Tree (数据都在叶子节点,并且叶子节点是个链表带顺序)
image.png

BTree
image.png

B树和B+树的最大区别是: 非叶子节点是否存储数据.
B树: 叶子节点和非叶子节点都会存储数据
B+树: 只有叶子节点才会存储数据,而且数据在一行上,数据都是有指针指向的,也就是有序的。

explain 执行计划
type列 会出现以下值,从好到坏依次:

system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery, index_subquery,range,index_merge,index,ALL

除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一个索引, 应该最少要用到range级别

逻辑架构

connector -> conection pool

SQL Interface(接口) -- Parser(解析器) --- Optimizer (解析器) -- Cache(缓存【SQL语句hash值作为key,查询结果作为结果存储, mysql8.0以后不用缓存了】)

| 存储引擎 | 说明 |
| - | - |
| MyISAM | 高速引擎,较高插入、查询速度,但不支持事物、不支持行锁,空间和内存使用比较低 |
| InnoDB | 5.5版本以后的默认引擎,支持事物和行级锁定,事务处理、回滚、崩溃修复能力和多版本并发控制的事物安全,比MyISAM处理速度稍慢,支持外键 |
| Memory | 内存存储引擎,拥有极高的插入、更新和查询效率。但是会占用和数据量成正比的内存空间.只在内存上保存数据,意味着数据可能会丢失,数据安全性低,适用于不需要持久保存的数据 |

Myisam索引
由于是非聚集索引,数据文件和索引文件是分开存放的。所以索引树(.myi文件)上只存放地址。主键索引存的是数据的地址, 数据地址指向的是数据文件(.myd)中的位置。

InnoDB索引
由于是聚集索引,索引和数据在一个文件上。 数据就挂载到主键索引的叶子节点下面。
image.png

主键索引里面存的是数据, 辅助索引里面存的是主键值. 所以
如果是select * from user where name = '李四';

  • 这条SQL是非主键查询,则需要搜索两次索引树(第一次是查辅助索引树通过name找到主键值,然后根据主键值再查主键索引树,这个动作就叫做【回表】),最终取出数据。 如果不想回表操作,那么则需要在第一次查询辅助索引树的时候就把所有字段(假如表有主键id,姓名name,和年龄age) 找到, 那么需要建立组合索引,将name 和 age 都挂载到辅助索引上,利用组合索引完成,叫做【覆盖索引】。
  • 是否回表,主要看select的列是否都在辅助索引树的一颗树上面,如果查询的列都在这颗索引树上则不用回表。

问:

MySQL 在innodb中一个3层的B+树最多大概可以存放多少行数据?

首先,在innodb存储引擎里面,最小的存储单元是页(page),一个页的大小是16KB。这就说明了一个页的大小为16384B, 存放总记录数为:根节点指针数*单个叶子节点记录行数.

然后因为非叶子节点的结构是:“页指针+键值”,我们假设主键ID为bigint类型,长度为8字节(byte),而指针大小在InnoDB源码中设置为6字节(byte),这样一共14字节(byte),因为一个页可以存放16k个byte,所以一个页可以存放的指针个数为16384/14=1170个.

因为单个页的大小为16kb,而一行数据的大小为1kb,也就是说一页可以存放16行数据.

所以一个三层的B+树大概能存储的数据为
第一层1170个指针* 第二层1170个指针 * 每个page存储16行 = 21902400行记录

参考:
https://blog.csdn.net/qq_35590091/article/details/107361172

https://www.136.la/jingpin/show-138972.html


标题:MySQL基础
作者:码农路上
地址:http://wujingjian.club/articles/2021/03/31/1617180192368.html