一个sql语句如何执行？

mysql软件架构是怎么样的？

1. mysql常用的存储引擎

• Innodb


• MyISAM


• Memory


• Archive
  
  Innodb是目前最流行的存储引擎，适合各种互联网业务
  
  查询效率非常高的业务可以考虑MyISAM
  
  日志信息归档可以考虑Archive,因为有压缩，磁盘利用率非常高
  
  临时表可以考虑memory，因为是基于内存的

2. 软件工程中的经典架构

• 分层架构


• 事件驱动架构


• 管道过滤器架构


• 微核架构
  
  大型的软件往往不是单一的架构设计，而是多种混合的

3. mysql tcp的通讯协议

• tcp三次握手建立链接


• 认证链接


• 认证通过后，客户端开始与服务端进行交互


• 断开mysql链接


• tcp四次挥手断开tcp链接

4. 客户端与mysql的连接方式

• tcp/ip连接


• 命名管道


• 共享内存


• unix域套接字

5. 执行器的主要作用
   
   


6. 存储引擎的主要作用
   
   存储引擎的任务是将执行器的指令落实在数据文件上
   
   不同存储引擎的原理和执行方法有很大的不同

一个sql语句是按照分析、优化、执行、落盘的步骤执行的，mysql8.0之后已经停用了server层的缓存

如何建表更符合业务

什么叫索引组织表

1. 索引组织表：index organized table

• 索引组织表不是一种 "组织表"


• 索引组织表是由索引 "组织起来的" 表


• InnoDB中表都是根据主键顺序组织存放的

2. 索引
   
   索引是数据库中对某一列或多列的值进行预排序的数据结构
   
   索引可以理解为数据的目录
   
   InnoDB中，主键是一个特殊的索引字段

总结：InnoDB数据表均为索引组织表，索引组织表中的数据被主键的索引组织起来

B+树的B是什么意思

1. 主流索引查找算法

• 线性查找: 时间复杂度O(n), 从第一个数据开始逐个匹配


• 二分查找：时间复杂度O(logN),拿出有序数列中点位置作为比较对象，根据中点数据大小，选取一半数据作为新的数列，每次可以将数据量减小一半


• 二叉查找树：时间复杂度O(logN)，使用经典的二叉树数据结构，由根节点开始查找，可能退化为线性查找，最坏时间复杂度是O(n)


• 平衡二叉树：(avl tree/ balance tree),时间复杂度O(logN) 查找时与二叉查找树相同，增删改时，通过旋转操作，维护树的平衡，AVL树就是平衡二叉树保证不会退化成线性查找。


• B tree：
  
  
  
  B树特点：

1. B树是线性数据结构和树的结合，


2. B树通过多数据节点大大降低了树的高度


3. B树不需要旋转就可以保证树的平衡

• B+ tree（B tree + 线性查找）
  
  因为B树对范围查找的效率非常低，所以就有了B+树，B+树是由B树发展而来的一种数据结构
  
  B+树的所有数据形成一个线性表
  
  B+树的特点是所有的数据都在叶子节点，上面的节点值存储索引
  
  
  
  B+树对范围查询非常的高效，因为数据都在叶子节点，只要找到范围的开始值，通过遍历链表的方式就可以找到范围内的值，而且B+树有B树的特点就是一个节点可以存储多个数据

总结：B+树是目前最主流的数据库索引算法，B+树由线性表+二叉树+B树发展而来的，B+树集成了线性表、平衡二叉树的优势

既有查询速度快的优势，又有范围查询效率高的优势，还有磁盘空间利用率高的优势

为什么说InnoDB索引即数据

1. Innodb使用B+树作为索引的数据结构

   
   



2. B+树的高度一般为2-4层，查找速度非常高

   
   



3. InnoDB的索引分为聚簇索引（主索引）和辅助索引

   
   



4. 聚簇索引

   
   

• 根据表的主键构造一个B+树


• 叶子节点直接存放行数据，而不是指针


• 索引组织表中， 数据也是B+树的一部分
  
  

5. 辅助索引

• 每张表可以有多个辅助索引


• 叶子节点并不包含行数据


• 叶子节点记录了行数据的主键，用来指示行数据的位置

一个表中如果有一个主索引，两个辅助索引，那么这张表有三个B+树

所谓的回表就是通过辅助索引查到对应的主索引，在通过主索引查到对应的数据

总结：

• InnoDB的索引分为聚簇索引（主索引）和辅助索引


• 同层B+树节点之间为双向链表


• 在B+树节点之内，数据条目之间为单向链表


• 所谓索引即数据，就是把数据直接记录在了主索引里

InnoDB的数据表是如何存储的

1. 逻辑存储结果
   
   

   
   



2. 表空间

   
   

• 表空间指的是数据表在硬盘上的存储空间


• 默认，所有表的数据都存在共享表空间


• 每个表的数据也可以放在独占表空间（ibd）文件

3. 段

• 数据段：B+树的叶子结点


• 索引段：B+树的非叶子节点


• InnoDB中，段由存储引擎自动管理

4. 区

• 区是由连续页组成的空间，每个页16kb,一共64个页，也就是每个区是1M


• 一次从磁盘申请4-5个区

5. 页

• 页是InnoDB中磁盘读写的最小逻辑单位，默认16kb


• 一个数据页就是B+树的一个节点


• 页的大小充分考虑了机械硬派和SSD的最小单元（512B和4KB）

总结：

InnoDB的逻辑存储结构分为表空间、段、区、页、行

InnoDB的逻辑存储结构充分考虑了基于B+树的表结构

InnoDB中的页是InnoDB自身的逻辑概念，与硬件的页无关

InnoDB的数据行长什么样

1. InnoDB中的变长列

• 长度不固定的数据类型，VARCHAR VARBINARY BLOB TEXT


• 变长编码下的CHAR

2. 行溢出数据

• 由于InnoDB每个数据页容量有限，导致数据字段也是有限的


• 当数据字段过大时，InnoDB会使用行溢出机制


• 行溢出机制会把超长字段放入单独开辟的数据页

3. InnoDB行记录格式
   
   
   
   
   
   InnoDB用的最多的mysql5.7之后的行记录格式 Row Format
   
   
   
   

总结：

索引有哪些左侧用法（索引的注意事项）

1. 联合索引

• 使用两个或两个以上字段生成的索引


• 联合索引也可以加速最左前缀的查询


• 联合索引可以代替最左侧字段的单独索引
  
  使用联合索引一句话："带头大哥不能死，中间兄弟不能丢"

2. 字符串的前缀索引

• 如果字符串过长，可以考虑使用前缀索引节约空间


• 如果前缀区分度太小，可以考虑两种变通方法

1. 倒叙存储

   
   



2. 新建hash字段

   
   



3. 字符串like模糊查询

   
   

• like %关键字% like %关键字 不会走索引


• 模糊查询只有 like 关键字%才会走索引


• 如果mysql数据量特别大，使用模糊查询会把数据库拖垮的话，就可以使用搜索引擎，例如：es

总结：

1. mysql有很多左侧用法


2. 联合索引可以代替最左侧字段的单独索引


3. 字符串的前缀索引可以节约存储空间


4. 字符串的左模糊可以利用索引，如果是全模糊或者右模糊对性能影响比较大的可以使用搜索引擎es

如何约束数据

1. primary key, unique key

• 通过将数据字段设置为索引，约束数据内容


• primary key 唯一 不为null


• unique key 唯一


• 唯一约束插入时的性能开销较大

2. foreign key外键

• 外键可以对数据的正确性实现约束

如何使用不存在的数据表-视图

1. 视图可以在不改变原有数据表的情况下，创建一张虚拟的表


2. 使用视图时尽量使用merge算法，而且避免无法使用merge的sql,如：union union all 聚合查询，group by having等

总结：

数据库查询效率影响最大的就是磁盘的IO。