MySQL学习day7(sql执行顺序,使用索引原则，避免全局扫描)

在学习过程中借鉴很多大佬的博客，感谢他们. 侵必删

MySQL 架构

（1）MySQL向外提供的交互接口（Connectors）

（2）管理服务组件和工具组件(Management Service & Utilities)

（3）连接池组件(Connection Pool)

（4）SQL接口组件(SQL Interface)

（5）查询分析器组件(Parser)

（6）优化器组件（Optimizer）

（7）缓存主件（Caches & Buffers）

（8）插件式存储引擎（Pluggable Storage Engines）

（9）物理文件（File System）
- 图如下所示
分为4大层:
- 连接层(Connectors)
- 服务层(Management Service & Utilities, Connection Pool, SQL Interface, Parser, Optimizer, Caches & Buffers)
- 引擎层(Pluggable Storage Engines)
- 文件层(File System)
各模块作用:
- MySQL向外提供的交互接口（Connectors）
Connectors组件，是MySQL向外提供的交互组件，如java,.net,php等语言可以通过该组件来操作SQL语句，实现与SQL的交互。
- 管理服务组件和工具组件(Management Service & Utilities)
提供对MySQL的集成管理，如备份(Backup),恢复(Recovery),安全管理(Security)等
- 连接池组件(Connection Pool)
负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。
- SQL接口组件(SQL Interface)
接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户。
- 查询分析器组件(Parser)
首先分析SQL命令语法的合法性，并尝试将SQL命令分解成数据结构，若分解失败，则提示SQL语句不合理。
- 优化器组件（Optimizer）
对SQL命令按照标准流程进行优化分析。
- 缓存主件（Caches & Buffers）
缓存和缓冲组件
- MySQL存储引擎
  - 什么是MySQL存储引擎
    
    MySQL属于关系型数据库，而关系型数据库的存储是以表的形式进行的，对于表的创建，数据的存储，检索，更新等都是由MySQL存储引擎完成的，这也是MySQL存储引擎在MySQL中扮演的重要角色。
    
    研究过SQL Server和Oracle的读者可能很清楚，这两种数据库的存储引擎只有一个，而MySQL的存储引擎种类比较多，如MyISAM存储引擎，InnoDB存储引擎和Memory存储引擎.
    
    MySQL之所以有多种存储引擎，是因为MySQL的开源性决定的。MySQL存储引擎，从种类上来说，大致可归结为官方存储引擎和第三存储引起。MySQL的开源性，允许第三方基于MySQL骨架，开发适合自己业务需求的存储引擎。
  - MySQL存储引擎作用
    
    MySQL存储引擎在MySQL中扮演重要角色，其作比较重要作用，大致归结为如下两方面：
    
    作用一：管理表创建，数据检索，索引创建等
    
    作用二：满足自定义存储引擎开发。
  3.MySQL引擎种类
不同种类的存储引擎，在存储表时的存储引擎表机制也有所不同，从MySQL存储引擎种类上来说，可以分为官方存储引擎和第三方存储引擎。

当前，也存在多种MySQL存储引擎，如MyISAM存储引擎，InnoDB存储引擎，NDB存储引擎，Archive存储引擎，Federated存储引擎，Memory存储引擎，Merge存储引擎，Parter存储引擎，Community存储引擎，Custom存储引擎和其他存储引擎。

其中，比较常用的存储引擎包括InnoDB存储引擎，MyISAM存储引擎和Momery存储引擎。
Innodb 和 MyISAM 区别
- 外键
  - Innodb 支持外键,
  - MyISAM 不支持
- 事务
  - Innodb 支持事务,
  - MyISAM 不支持
- 行表锁
  - Innodb 支持行锁、表锁,
  - MyISAM 支持表锁
- 缓存
  - Innodb 不仅缓存索引，还会缓存真实数据, 对内存要求高,
  - MyISAM 只缓存索引
- 事务
  - Innodb 事务性，
  - MyISAM 性能
- 文件结构:
  - MyISAM 文件为: b.frm(描述表结构文件，字段长度)，b.MYD: 数据信息文件(如果采用独立表存储模式), b.MYI(索引信息文件)
  - Innodb: b.frm(表结构，字段长度); b.ibd(存储数据信息和索引信息)
SQL 执行顺序
- sql 语句语法顺序
- sql 执行顺序
- sql 执行流程
join 类型
- 七种join
创建索引原则
- 对于查询频率高的字段创建索引
- 对排序、分组、联合查询频率高的字段创建索引
- 索引的数目不宜太多
- 当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率
- 选择唯一性索引
- 尽量使用数据量少的索引
- 尽量使用前缀来索引(最左原则)
- 删除不再使用或者很少使用的索引
- 索引列不能参与计算，保持列“干净”(不要使用函数)
- 尽量的扩展索引，不要新建索引
- 在不同值较少的字段上不必要建立索引，如性别字段
- 表的主键、外键必须有索引
- 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引
- 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引
避免全局扫描的方法
- 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
- 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
- 避免在索引列上使用计算，也就是说，应尽量避免在 where 子句中对字段进行表达式操作和函数操作，这将导致引擎放弃使用索引而进行全表扫描
- 很多时候用 exists 代替 in 是一个好的选择：exists用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false。
- 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
- 用>=替代>
  - 高效: SELECT * FROM EMP WHERE DEPTNO >=4
    
    低效: SELECT * FROM EMP WHERE DEPTNO >3
    
    两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录

MySQL学习day7(sql执行顺序,使用索引原则， 避免全局扫描)

相关推荐

MySQL学习day7(sql执行顺序,使用索引原则，避免全局扫描)