MySql教程

MySQL

本文主要是介绍MySQL,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

MySQL架构介绍

  1. 客户端:支持接口(标准的API、JDBC、ODBC、.NET、PHP、Phython等)
  2. 服务端:
    • MySQL软件
      • Server层
        • 连接池:验证与授权、线程、内存与缓存管理
        • 管理服务与工具:备份与恢复、安全、复制、集群、分区管理、事务管理、数据库模板管理、工作台、合并工具
        • 核心业务处理层:
          • SQL接口:数据管理语言和数据定义语言、存储过程、视图、触发器等
          • 解析器:查询\事务 对象优先级
          • 优化控制器:访问路径统计
          • 缓存和缓冲池:全局和局部引擎的缓存和缓冲池
      • 存储层
        • 插件式存储引擎:MyISM、InnoDB、Cluster。。。。。
    • 磁盘文件
      • 文件系统:NTFS、UTS、EXT2/3、NFS、NAS。。。
      • 文件和日志:REDO、UNDO、DATA。。。。

连接池

  • show fulll processlist 查询所有连接
  • show processlist 只能查询前100个连接
  • 通过max_connections控制修改最大连接数

SQL接口:CRUD分流执行SQL语句(执行器)

Parser:解析器

Optimizer:查询优化器

Cache和Buffer:查询缓存(MySQL8.0去掉:缓存命中太差,同一条SQL语句才能命中)

SQL执行过程

 

 

  1.  连接器:客户端通过连接器与服务端简历连接(mysql -uroot -p123456),验证用户,建立连接
  2. 查询缓存
    • SQL语句一致才会存储缓存
    • 表修改缓存则清空
  3. 解析器和预处理
    • 词法分析:按照空格和逗号切割语句
    • 语法分析:鉴别关键字、表名称、列名称、查询条件
    • 形成语法树:
    • 预处理 :检查语法树是否合法(检查权限,列名是否存在、表名是否存在、别名改为表名)

  4. 优化器
    • 解析语法树的执行一般有多种计划
    • 最终只能选择其中一种执行计划去执行
      • 基于成本计算
        • 根据索引的执行情况选择
        • 一般一个SQL语句执行,只会选择一个索引去使用
        • 如果通过join连接的多表,可以再查询计划中选择基表
        • 自己写的SQL语句,不一定按照你写的条件执行
  5. 执行器
    • 按照执行计划调用存储引擎接口,通过存储引擎去操作磁盘中的数据。

 

 

 MySQL的存储引擎

存储引擎针对表制定,存储引擎也是一种性能优化

  1. InnoDB
    • MySQL5.以后默认
    • 特点:行锁、事务
    • 适合于读写不分离,并发高的场景
  2. MyISAM
    • 特点:不支持行锁、不支持事务
    • 适合于读多、写少
    • 写操作时,进行表级锁定
  3. CSV :csv文件
  4. Memory:内存的存储引擎,类似Redis

MySQL磁盘文件

查看磁盘文件位置:show variables like ‘datadir%’;可以通过datadir指定磁盘文件位置。

  1. 数据文件
    • InnoDB
      • frm表结构文件
      • idb数据和索引文件(用户表空间)
    • MyISAM
      • frm表结构文件
      • myd数据文件
      • myi索引文件
    • ibdata系统表空间文件
      • 不断变大
      • 性能优化的一个点
  2. 日志文件
    • binlog:二进制日志文件(数据恢复、主从复制)
    • errlog:错误日志文件 mycentos.err
    • slow_query_log:慢查询日志文件(默认关闭,性能分析)
    • general_query_log:通用查询日志文件;
    • redo_log:重做日志文件(保证事务ACID特性)ib_logfile0、ib_logfile1
    • undo_log:回滚日志文件(保证事务ACID特性)ibdata1
    • relay_log:中继日志文件
  3. 磁盘如何存储数据
    • 随机写(数据文件:性能低)
      • 旋转时间
      • 寻找磁道
      • 寻找扇区
      • 判断扇区是否占用
      • 占用继续上面
    • 顺序写(日志文件:性能高)
      • 旋转时间
      • 寻找磁道
      • 寻找扇区
    •  

MySQL数据在磁盘中存取

  1. MySQL在磁盘中存数据
    • 磁盘和MySQL交互
      • 磁盘和操作系统(内核空间)交互
        • 磁盘中最小的存储单位是扇面(扇区),默认最小512字节,小于512字节也占用512字节
        • 磁盘和操作系统(内核空间)交互最小单位是4K,该大小由操作系统限制
      • 操作系统和MySQL应用(用户空间)交互
        • 操作系统和MySQL应用交互的最小单位是16K,该大小可以通过参数(innodb_page_size)控制,索引结构中的节点是一个物理页的大小
    • 思考:100条数据存储在磁盘上如何存储
      • 100条数据对应100个磁盘地址,需要通过IO读取100次才能遍历所有数据
      • 如何快速查找
        • 索引数据结构
          • 数据结构学习网站
          • Hash
            • Memory数据引擎应用的此数据结构,查询快,O(1);不支持范围查询。
            • ;对key进行hash计算(数组下标 = hashcode /  数组长度)
            • hash表特点
              • 一次性把表数据加载到内存中
            • hash冲突:
              • 多个KV对都存储到同一数组下标下
              • 链地址存储解决hash冲突
              • 链表越长,查询越慢
            • reHash:当数据长度扩充的时候,同一个key再前后两个数组存储位置不一样

          • 二叉树
            • 二叉树是一种高瘦树
            • 按需加载
              • 一次加载一次IO
              • 一次加载两个节点,防止内存装不下
              • 左子树和右子树从小到大
            • 特点:会退化成链表
          • 平衡二叉树
            • 红黑树是一种近似平衡二叉树()
            • 左子树和右子树高度差不能超过1,超过1发生左旋、右旋
            • 使用平衡二叉树查找的性能接近于二分查找,时间复杂度是O(log2n)
            • 存在问题
              • 时间复杂度和树高度有关。树高等于查询次数即IO次数,磁道每次查询时间10ms
              • 平衡二叉树不支持范围查找,查询从根节点查找,效率不高
          • B树(改造二叉树)
            • 一个节点可以插入多少个索引?
              • 假如一个值(key)weibigint=8字节,每个字节两个指针,每个指针为4个字节,一个字节占用的空间为16个字节,(8 + 4 * 2 = 16)
              • 磁盘一次IO读取一页(一个节点)16K的数据量,一个节点可以存储1000个索引(16K / 16 = 1000)
              • 树高度为2时,存储的数据为 1000 * 1000 = 1百万(记录)
            • 叶子节点之间没有指向
            • 叶子节点和非叶子节点之间的值是非重复的
            • 每个节点都包含多个元素,非叶子节点中会在元素前中后指向与另一个子节点
              • 等值查询
              • 范围查询
            • MySQL特点:所有节点都存储数据 
          • B+树(改造B树)
            • 叶子节点之间是有指向的
            • 叶子节点和非叶子节点之间值是有重复的
            • 左子节点中的最大值小于父节点值
            • 右子节点中最小值大于等于父节点值
            • MySQL的索引采用B+树对叶子节点进行改造,MySQL使用的B+树的叶子节点之间使用的是双向链表  
              • 等值查询
                • 第一次磁盘IO:将磁盘块1加入到内存中,在内存中遍历比较,15 < 28走左路到磁盘寻址磁盘块2
                • 第二次磁盘IO:将磁盘块2加载到内存中,在内存中遍历比较,10 < 15 < 17,到磁盘中寻址磁盘块5
                • 第三次磁盘IO:将磁盘块5加载到内存中,在内存中遍历比较, 15 = 15,取出data,data存储的是数据则查询结束,磁盘中存储的磁盘地址,则根据磁盘地址查询数据取出。
              • 范围查询(15-26)
                • 首先查找值等于15的数据,将缓存结果存储到数据集(查询步骤同上)。
                • 查询到15之后,底层的叶子节点是一个有序列表,从磁盘块5向后遍历筛选所有符合条件的数据。
            • MySQL特点:只有叶子节点存储数据 

    • MySQL索引结构
      • MyISAM索引结构
        • 主键索引为非聚簇索引::叶子节点存储索引和数据地址
        • 辅助索引为非聚簇索引:叶子节点存储索引和数据地址
      • InnoDB索引结构
        • 主键索引为聚簇索引:叶子节点存储索引和数据
          • 一个表只会再主键索引树存储数据
        • 辅助索引为非聚簇索引:叶子节点存储索引和数据主键Id
          • 一个表中会存在多个索引,非主键索引是不存储数据的(因为数据只存一份,再主键索引树),只存主键值
          • 通过次要索引查找数据,需要先通过次要索引树查找主键,再通过主键所索引树查找数据。
  2. MySQL在磁盘中读取数据

 

索引的磁层原理与使用原则

  1. 索引常用的数据结构
    • 数据结构学习网址
    • B+树
    • Hash
  2. 索引的使用
    • 索引分类
      • 主键索引
        • 索引中的值是唯一的,不允许为空
      • 唯一索引
        • 索引中的值是唯一的,允许为空
      • 普通索引
        • MySQL基本引用类型,没什么限制
      • 全文索引
        • 只能用在char、varchar、text文本类型的字段上。字段长度较大时,如果用普通索引效率低,可以用全文索引
      • 前缀索引
        • 在char、varchar、text文本类型的字段,可以指定索引列长度(前十个字符组成索引),但是数据类型不指定
      • 组合索引
        • 最左前缀原则
        • 建议使用组合索引代替单列索引,主键索引除外
  3. MySQL索引实现
    • MyISAM索引(B+树)
      • 主键索引
        • 等值查询
        • 范围查询
      • 辅助索引(B+树)
    • InnoDB索引(一个索引一个索引树)
      • 主键索引(聚簇索引)
      • 辅助索引(非聚簇索引)
        • 叶子节点存储的时主键索引的key值
        • 回表,回到主键索引树查询数据
      • 组合索引(辅助索引)
        • 针对多列创建索引,索引中的列是有顺序的
        • 组合索引存储
        • 组合索引查找 

      • 最左匹配原则
        • 组合索引(a,b,c)相当于创建三个索引(a;a,b;a,b,c),在where条件里,必须有a,才能使用索引,a可以在条件最右侧,优化器会自动优化到最左侧
        • 书写SQL的顺序不一定是执行顺序,优化器会优化
        • 使用组合索引时,MySQL会一直向右查询,直到遇到(<,>,between,like)范围查询
        • 何时创建组合索引
          • 频繁出现在where条件中的的非主键列,建议创建组合索引
          • 频繁出现在order by和group by中的列,建议按照顺序去创建索引(顺序不一致用不到索引,优化器不能优化,因为order by是明确需求)
          • 常出现在select语句中列也建议创建组合索引(所以不建议使用*)
      • 覆盖索引
        • 防止了回表(一次回标,多次IO),辅助索引中查找到的一条记录,会进行一次回表
        • 主要是针对select中的列进行优化,如果查找的select列,就不想去主键索引中查找对应的列,查询数据了
          •  红色标识的字段,均用到索引覆盖

          • 紫色框标识的由于有字段d,不在组合索引列,需要回表,没有用到覆盖索引 

      • 索引下推ICP
        • 索引下推是为了减少回表次数
        • 索引下推设置参数:Index_Condition_PushDown
          • 展示MySQL优化器中优化参数开关 show variables like ‘optimizer_switch’
          • 关闭索引下推(ICP)set optimizer_switch='index_Condition_PushDown=off';


          •  ICP未开启

            1. 存储引擎根据索引a=13,b>=15会匹配三条索引记录  三条索引记录会涉及三次回标
            2. 存储引擎将三条记录返回给Server层
            3. Server层进行c=5过滤  两条记录
            4. Server层根据d=‘pdf’过滤 一条记录
          • ICP开启 

            1. 存储引擎根据索引a=13,b>=15会匹配三条记录
            2. 存储引擎根据索引下推的条件c=5在辅助索引树中进行过滤 两条记录涉及两次回表
            3. 存储引擎层会将两条记录返回Server层
            4. Server层进行d='pdf'过滤 一条记录
  4. explain工具查看执行计划
    • 执行explain查询计划:explain select * from table_name;
      • id:标识符(多张表时标识哪个先执行)
      • select_type:查询类型(子查询,关联查询)
        • 展示单位查询的查询类型
        • simple 简单查询不需要unton和子查询(列为查询语句)
        • primary 主查询(区分主表、关联表或者子表)
        • subquery 子查询
        • derived from字句中出现子查询,就是派生表
      • table:查询表名称
      • partitions:匹配的分区,表被分区时才会用到
      • type:join类型
        • 单位查询的连接类型或访问类型
          • System 效率最高 说明表要么只有一条记录,要么是空表
          • const :使用到唯一或者主键索引,单表,where条件是一个常量值
          • eq_ref:等值连接(join...on中的on)的列用到唯一或者主键列,多表
          • ref:等值连接(join...on中的on)的列用到非唯一索引列,多表;或者使用最左前缀
          • fulltext
          • req_or_null
          • unique_subquery
          • index_subquery
          • rang:组合索引,第二个索引列用的范围(> < ...)
          • index_merge
          • index :全部索引扫描(select a from table;a是索引)一般是用到覆盖索引
          • ALL 效率最低(全表扫描,没用索引)select a,d from table;a是索引,d不是索引
        • 除all之外都用到索引;除index_merge,其他只可以用一个索引;优化时最少要用到rang级别
      • possible_keys:此次查询中可能选用的索引
      • key:此次查询中确切使用到的索引
      • ref:哪个字段或常数与key一起使用
      • rows:显示查询一共扫描了多少行
      • filtered:表示此次查询条件所过滤的数据的百分比
      • extra:额外的信息
        • using idex 使用到覆盖索引
          • 没有using where 索引用来查找数据;
          • 如果同时出现using where表明索引用来查找键值
        • using where 表示对存储引擎层返回结果在Server层过滤,过滤字段无索引
        • using index condition 用到索引下推
        • using filesort 说名排序没有用到index 在Server层排序
  5. 索引失效
    • 全值匹配
    • 最佳左前缀原则(多列索引遵循此原则)
      • 带头索引死(不使用索引,全表扫)
      • 中间索引断(带头索引生效,其他索引失效)
    • 不要在索引上计算会失效
    • 索引条件右边的列失效 (不要在索引中间使用 范围比骄)
    • 范围条件右边的列索引失效
    • 尽量使用覆盖索引 selelct 索引1,索引2 from
    • 索引字段上不要使用 != 会索引失效
    • 索引字段使用like不以通配符开头
      • 使用通配符可以用覆盖索引,解决索引失效
    • 索引字段字符串要加单引号
    • 索引字段不要使用or
    • 总结:全值匹配我最爱,最左前缀要遵守。带头大哥不能死,中间兄弟不能断;索引列上少计算,范围之后全失效;Like百分写最右,覆盖索引不写星,不等空值还有or,索引失效要少用。
  6. 索引创建原则
    • 索引需要创建的情况
      • 频繁出现在where、order排序、group by
      • select频繁出现的列,创建组合索引,覆盖索引
      • 多表join关联,on字段两边的字段要创建索引
    • 索引创建的建议
      • 表记录少的不需要
      • 一个表索引记录不要过多
        • 空间占用
        • 时间占用 更新变慢、增加优化器选择时间
      • 频繁更新字段不建议创建索引
      • 区分度低字段(男、女区分数据度不大)
      • InnoDB主键索引建议自增,避免使用长字段
      • 不建议使用无序值作为索引
      • 尽量创建组合索引
      • 字符太长,使用前缀索引
  7. 索引优缺点
    • 优点
      • 提高检索效率,降低磁盘IO
      • 通过索引进行数据排序,降低排序 成本
    • 缺点
      • 占据额外的磁盘空间
      • 对写、更新操作不友好

MySQL锁和事物

  1. 锁划分
    • 按照锁粒度:、
      • 全局锁
      • 表级锁:开销小,加锁快;不会出现死锁;锁粒度大,出现冲突的概率高,并发度最小。
      • 行级锁:开销大,加锁慢;会出现死锁;锁粒度小,出现冲突概率低,并发度也高
    • 按照锁的功能:排他写锁、共享读锁
    • 按照锁的实现方式:悲观锁(物理锁,真正锁)、乐观锁(版本等逻辑控制)

 

这篇关于MySQL的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!