gtd54789的博客

叶子节点要存储真实数据，就按照1k来计算，一页 16 条记录 那就是 16 * 1600 * 1600 条记录 大约四千万条。根节点 只存 主键 + 页数 + 其他信息 约等于 10字节，一页是16k 也就是说存储了1600条。那么也就是说第二层有 1600 页，每一页也同样是存储了16000条。mysql 索引分为三类：B+树、hash索引、全文索引。也就是说叶子节点有 1600 * 1600 页。没有创建索引，也没有主键。没有创建索引，但有主键。页中大致有以下几个部分。

Mysql的全套教程笔记，入门使用进阶优化。

一、 设计规则数据在表中，表在数据库中。一个数据库可以有多个表，每个表都有一个唯一标识符（名字）。表具有特性，类似于类的设计。表、记录、字段E-R(entity-relationship,实体-联系)模型有三个主要概念是：实体集（表）、属性（字段）、联系集。ORM思想（Object Relational Mapping）数据库中的表对应（表）-> Python中的一个类表中的一条数据(记录) -> 类中的一个对象表中的一个列（字段） -> 类中的一个字段表的关

Mysql专栏入口目录一、基础数据库操作1.1 数据库登录1.2 查看所有数据库1.3 创建数据库1.4 使用某个数据库1.5 查看所有表1.6 创建表1.7 给表里插入数据1.8 查询表中所有数据1.9 修改数据1.10 查看创表代码1.11 删除数据1.12 删除表1.13 删除数据库1.14 数据库的复原一、基础数据库操作1.1 数据库登录mysql -uroot -p '你的密码'1.2 查看所有数据库show databases;1.3 创建数据库create database

Mysql专栏入口文章目录一、sql的分类二、数据库的导入导出一、sql的分类DDL：数据定义语言。CREAT（创建） \ ALTER（修改） \ DROP（删除） \ RENAME（名命名）\ TRUNCATE（清空）DML:数据操作语言。INSERT(增) \ DELETE(删) \ UPDATE(改) \ SELECT(查) DCL:数据控制语言。COMMIT(提交) \ ROLLBACK(回滚) \ SAVEPOINT(保存点) \ GRANT(赋予) \ REVOKE(收回)

Mysql专栏入口一、别名AS(alias 别名):SELECT account_id, product_cd, cust_idFROM account# 当我们编写这个样的sql时运行后，我们会得到如下图的数据。这样的数据没有问题，但并不是所有人都对英文很敏感，那么我们可以使用 as 使得表头更直观。SELECT account_id AS "账户id", product_cd AS "产品", cust_id AS "顾客id"FROM account 以上sql

Mysql专栏入口数据库sql文件链接如下链接：https://pan.baidu.com/s/1g6GKTZ6GVg04gxs4VzhxJA提取码：ttst专栏中有mysql的导入导出，里面有教如何导入文章目录一、 算数运算符二、比较运算符2.1 基础比较运算符2.2 比较符关键字/函数2.2.1 IS NULL \ IS NOT NULL \ ISNULL()2.2.2 LEAST() \ GREATEST()2.2.3 BETWEEN...AND...2.2.4 IN() \ NOT I

Mysql专栏入口数据库sql文件链接如下链接：https://pan.baidu.com/s/1g6GKTZ6GVg04gxs4VzhxJA提取码：ttst专栏中有mysql的导入导出，里面有教如何导入文章目录一、排序一、排序如果没有使用排序操作，默认情况下返回的数据是按照添加数据的顺序显示的。练习：按照 monthly_income2 从高到低排序 默认ASC(升序)SELECT * FROM account ORDER BY monthly_income DESC排序和分组的

Mysql专栏入口从这章开始使用新的数据库记得切换为什么要多表查询？练习：查询员工名为’Abel’的人在哪个城市工作首先我需要在员工表中查询这个人的部门这时就查到了这个人的部门id知道部门号后在从部门表中查询其地点id知道地点id后再地点表中查询地点我们需要三个表employees、departments、locations才能找出我们需要的数据............

Mysql专栏入口函数大致分为：数值，字符串，时间，流程控制函数,信息函数。我这边强调的讲一下流程控制函数，其他函数用到直接百度，无需记忆。小案例:查询员工工资，大于等于6000的为高工资，低于为低工资其实在生活中收入由 固定工资 + 奖金组成，那么如何计算。CASE WHEN小案例:想将工资分为1.5w+高，1w+中，6k+低，6k-超低二、加密函数MD5给密码加密...............

Mysql专栏入口

Mysql专栏入口第一种方法：分开两次查第二种方法：自连接第三种就是我们今天学习的子查询1.2 子查询分类1.2.1 单行子查询 vs 多行子查询返回结果条目只有一行的为单行子查询，返回多行的为多行子查询。子查询和主查询相关联的则为相关子查询，否则为不相关子查询。相关子查询:查询工资大于本部门平均工资的员工信息不相关子查询:查询工资大于本公司平均工资的员工信息..............................

Mysql专栏入口CREATE DATABASE mytest1;CREATE DATABASE mytest2 CHARACTER SET ‘gbk’;CREATE DATABASE IF NOT EXISTS mytest2 CHARACTER SET ‘gbk’;SHOW DATABASES；USE atguigudbSHOW TABLES；SELECT DATABASE() FROM DUAL;SHOW TABLES FROM mysql;通常不使用ALTER DATABASE mytest2 CH

链接https提取码ttst提取码ttst专栏中有mysql的导入导出，里面有教如何导入。

链接https提取码ttst提取码ttst专栏中有mysql的导入导出，里面有教如何导入。

链接https提取码ttst提取码ttst专栏中有mysql的导入导出，里面有教如何导入。

UNSIGNED无符号类型（非负），所有的整数类型都有一个可选的属性UNSIGNED（无符号属性），无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。int类型默认显示宽度为int(11)，无符号int类型默认显示宽度为int(10)。......

数据完整性（DataIntegrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制。实体完整性（EntityIntegrity）例如，同一个表中，不能存在两条完全相同无法区分的记录域完整性（DomainIntegrity）例如年龄范围0-120，性别范围“男/女”例如例如。.........

优点：1.操作简单将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作。2.减少数据冗余视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余。3.数据安全。...

链接：https://pan.baidu.com/s/1zAhDUNv-yuJiWmaFLvTk4w提取码：ttst提取码：ttst专栏中有mysql的导入导出，里面有教如何导入。

可以为角色添加或移除权限。用户可以被赋予角色，同事也被授予角色包含的权限。对角色进行操作需要较高的权限，并且像用户账户一样，角色可以拥有授予和撤销的权限。root用户是超级管理员,所拥有的权限，包括创建、删除、修改密码等所有权限。drop会将user的对应的权限也删除，但delete不会。如果密码使用的时间大于允许时间，服务器会自动设置为过期，不需要手动设置。MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在的，并完成与存储引擎的交互。这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。Connectors，指的是不同语言中与SQL的交互。前面的结构图很复杂，我们需要抓取最核心的部分:SQL的执行原理。不同的DBMS的SQL的执行原理是相通的，只是在不同的软件中，各有各的实现路径。

对于使用InnoDB作为存储引擎的表来说，不管是用于存储用户数据的索引(包括聚簇索引和二级索引)，还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只不过是InnoDB对文件系统上一个或几个实际文件的抽象，也就是说我们的数据说到底还是存储在磁盘上的。所以InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中,也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。当查询数据的时候，会先去Buffer Pool中查询。

InnoDB 引擎具备外键支持功能的事务存储引擎（InnoDB的优点一）MySQL从3.23.34a开始就包含InnoDB存储引擎。大于等于5.5之后，默认采用InnoDB引擎。InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)（InnoDB的优点二）除了增加和查询外（MyISAM擅长增加和查询）,还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。

们快速定位数据的话，我们查找Col2=89这条记录，就要逐行查找，去比较。从Col2=34开始，进行比较，发现不是，继续下一行。索引是存储引擎用于快速找到数据记录的一种数据结构，就好比一本教科书的目录部分，通过目录找到对应文章的页码，便可以快速定位到需要的文章。因为新插入的值可能是不连续的，所以我们需要将 28页的 4 和 10页的 5 交换位置。从图中可以看出，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也称为。的数据页，我们都把它放到B+树这个数据结构中了，所以我们也称这些数据页为。

这个树存储的是 目标查询字段和主键值。当找到目标字段后会通过主键值从聚簇索引中查询具体记录。，不同的B+树种的数据采取不同的排序规则。我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引。（所有的用户记录都存储在了叶子节点），也就是所谓的。术语 “聚簇” 表示数据行和相邻的键值聚簇的存储在一起。这种聚簇索引并不需要我们在MySQL语句中显式的使用。以为只能有一种排序存储方式，但可以有多个。当记录中的主键，被层层存储这样的树就叫做。并不是一种单独的索引类型，而是一种。表只能有一个聚簇索引。

总结创建出的第一个节点，称为根节点，我们插入数据就会插入到其中。当这个节点满了，并不是向上创建目录页。而是根节点本身变为目录页。原本根节点的数据会被复制一份到根节点下方，然后再在根节点上创建新的子节点存储新的记录。如下图所示根节点根节点根节点可用空间根节点根节点根节点。

主键索引和二级索引结构上没有任何区别，只是主键索引要求Key是唯一的，而二级索引Key可以重复。

采取Hash进行检索效率非常高，基本上一次检索就可以找到数据，而 B+Tree 需要自顶向下依次寻找，多次访问节点才能找到，中间需要多次 IO。B+树目录页只存放记录页的页号，不存放记录，这样就会使得B+Tree更加。B树在范围查询上如果要进行排序则需要将树遍历，而B+Tree只需要遍历叶子结点（最后的记录层）B树在范围查询上如果要进行排序则需要将树遍历，而B+Tree只需要遍历叶子结点（最后的记录层）不同，B树的叶子节点和非叶子节点都存放数据，搜索有可能在非叶子节点结束。B+树也是多路搜索树，

页与页之间不在物理结构上相连只需要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大组成单向链表。单向链表优势在于插入和删除操作速度快速，但查找却只能遍历，于是在页中还有页目录是一个数组这样就可以使用二分法快速找到数据。

当我们使用 变长字段时，在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。varchar()最大的字节为65535 而一个页最大存储16kb也就是16384字节这时发现，一页连一个字段真实数据都放不下 这就叫做。当创建聚簇索引时，没有设置主键，那么将会使用这里面的 row_id字段进行创建。他们将会在真实数据中空出20字节用来记录溢出的数据存放的页。可以将一条记录中的某些数据存储在真正的数据页之外。当有记录中有NULL时，这个列表会存在。

因为一个B+Tree中，不仅有数据页，还有目录页 也就是叶子节点和非叶子节点。但我们主要需要的还是数据页，目录页如果页和数据页存放在同一个区那么会减少。这不就太浪费了么，这时就有了碎片区。数据和索引信息存放的最大单位，由页，区，段组成。一张表对应一个表空间。如果右区将页统一存放，那么页与页之间将会物理连续，查询的时候就会。页与页之间如果不连续，那么数据查询的时候将会进行。连续的数据页的存放数量，也会造成没必要的性能损失。虽然可能会损失一些空间但性能上有了提升。我们将目录页和数据页分开存放，分为。

多列索引是在表的多个字段组合上创建一个索引。MySQL支持多种方法在单个或多个列上创建索引:在创建表的定义语句CREATE TABLE中指定索引列，使用ALTER TABLE语句在存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOT NULL+UNIQUE，一张表里最多只有一个主键索引。

从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关（use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。在某些场景下，降序索引意义重大。

2.创建模拟数据必需的存储函数创建函数，假如报错：由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。主从复制，主机会将写操作记录在bin-log日志中。从机读取bin-log日志，执行语句来同步数据。如果使用函数来操作数据，会导致从机和主键操作时间不一致。所以，默认情况下，mysql不开启创建函数设置命令开启：允许创建函数设置：mysqld重启，上述参数又会消失。永久方法：windows下：my.ini[mysqld]加上：linux下：/et

定位了查询慢的SQL之后，就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。MySQL中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)

对于单列索引，尽量选择针对当前query过滤性更好的索引在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好在选择组合索引的时候，尽量选择能够包含当前query中的where了句中更多字段的索引在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面总之，书写SQL语句时，尽量避免造成索引失效的情况。

不再是逐条获取驱动表的数据，而是一块一块的获取，引入了join buffer缓冲区，将驱动表join相关的部分数据列(大小受join buffer的限制）缓存到join buffer中，然后全表扫描被驱动表，被驱动表的每一条记录一次性和join buffer中的所有驱动表记录进行匹配(内存中操作)，将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。驱动表中的每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故mysql优化器都倾向于使用记录数少的表作为驱动表（外表)。

从磁盘读取查询需要的 所有列 ，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。如果是InnoDB存储引擎，因为InnoDB支持事务，采用行级锁和MVCC机制，所以无法像 MyISAM—样，维护一个row_count变量，因此需要采用扫描全表，进行循环＋计数的方式来完成统计。取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。但是，子查询的执行效率不高。

查询的字段和条件字段，都在联合索引中就构成覆盖索引，不会回表。SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。`一个索引包含了满足查询结果的数据就叫做覆盖索引#删除之前的索引#举例1：/**//*使用了索引，打破了前面说的“不等于”的查询索引会失效的原则。