MYSQL文件与数据表、索引概述
MYSQL文件与数据表、索引概述
mysql作为操作系统的一个进程,与普通的进程并无二致,影响MySql系统整体性能的两个关键因素是内存和硬盘的读写速度，这也是为什么在存储体系那一小节中介绍内存和硬盘的原因，实际上大多数应用均受这两个因素的影响，对于服务端的程序来讲,基本上都需要与这两个物理存储元器件打交道
下面我们来详细了解下mysql的文件目录结构(具体的mysql安装可自行参考官网或者google,此处略去不表)，所用的mysql为5.7.32版本
文件目录结构见下图:
以pem结尾的文件可以暂时先略过,重要的几个文件/目录说明如下:
- mysql mysql数据库系统核心数据库目录,包含用户、授权等数据表均位于次,可以理解为系统数据库
- performance_schema 系统性能监控,与innodb存储引擎相关的的基本都在这个schema下
- sys mysql系统数据库
- test 一个测试数据库
- ib_buff_pool innodb buffer pool持久化文件
- ib_logfile0/ib_logfile1 innodb日志文件组
- ibdata1 innodb系统(共享)表空间文件(innodb核心的文件)
- ibtmp1 innodb 临时表空间文件
- xx.local.err mysql错误日志文件,通常启动问题、系统异常问题首要排查的文件
- xx.local.pid mysql进程pid文件
基本概念
- 表 mysql中最常见的就是表了,开发人员针对特定数据划分一个一个集合组合成一张张特定表,如订单表、用户表、商品表等
- 列 一张表由多列数据(字段)组成
- 行 表的数据按行存储,一张表有多行构成
一个表示例如下:
对于关系型数据库来讲,我们定位数据的方式通常是表名->行号->列名(行式数据库)的方式来具体定位某一个字段
tips:近些年流行起来的NoSQL数据库很多都是列式数据库，相对于行式数据库,数据按列存储
mysql常用的存储引擎有Myisam、Innodb,从5.5.5开始Innodb替代Myisam成为mysql的默认存储引擎,大致介绍下这两种存储引擎的区别
-
Myisam简易结构如下:
数据按照写入的顺序存放,由系统生成RowId,主键索引与普通的索引并无特别大的区别,均指向RowId,底层数据分为数据文件(MYD后缀)和索引文件(MYI)
-
Innodb索引组织表,可以简单看成在isam结构的基础上,将RowId提取为主键,数据按主键的顺序存放(可以类比为学生时代新华字典,主键即是页码,拼音、偏旁为两个辅助索引,实际上大部分介绍数据库索引入门的文章都会拿字典来举例),底层数据文件为ibd数据文件
myisam与innodb均使用B+树索引结构,在数据查询过程上并无特别大的区别,均需要从B+树根节点开始进行查找,二者的辅助(二级)索引查找过程是相同的,不同的是innodb的主键查找叶子结点存放的就是数据，对于主键类型的查找，无需在回表查询。
早期后端开发领域流行一条SQL开发规范,相信不少人应该听过"业务系统在设计上主键应该是业务无关的,由系统生成维护"这样的论调,应用代码使用的字段(诸如order_id,user_id)实际均为辅助索引,私以为这条规则已经不适合于目前大型的互联网后端应用了,白白浪费了主键查找速度上的优势,具体可以参见上述的主键索引与二级索引查找过程中的区别
以一个测试数据表为实例简要说明下数据定位的过程,测试建表语句如下
CREATE TABLE `user_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(20) NOT NULL COMMENT '名称',
`phone` varchar(30) NOT NULL COMMENT '手机',
`email` varchar(50) NOT NULL DEFAULT '' COMMENT '邮箱',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
以一个简单的查询语句举例:
select id,name from user_info where id = 1;
- from后接表名(user_info)定位到具体的表(对应磁盘存储设备上的表空间文件)
- id=1 B+树索引查找定位到具体的某一行(主索引查找(通常会产生磁盘IO))
- id,name 定位到指定行的具体列(页查找,位于内存)
