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. 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的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！