首页  思维导图  详情

MySQL入门

2021-10-29 08:39:50   28  举报





AI智能生成

MySQL基础知识概要

MySQL

mysql索引

数据库

b树

作者其他创作

大纲/内容

基础架构/执行流程

MySQL基础架构

模块详解

Connectors：可与各种语言交互的连接器

Connection Pool：连接池，管理需要缓冲的资源，包括用户名密码权限线程等

SQL Interface：用来接收SQL命令，发挥用户需要查询的结果

Parser：解析SQL语句

Optimizer：优化器

Cache & Buffers：查询缓冲，行记录缓冲，表缓存，key缓存，权限缓存

 Pluggable Storage Engine：插件式引擎，提供给服务层使用

架构分层

连接层：负责客户端连接到服务器3306端口，建立连接，管理连接，验证身份权限

 服务层：查询缓存的判断，SQL语句的解析，优化器对SQL进行优化，交给执行器去执行

存储引擎层：正在存储数据的地方，支持不同的存储引擎

查询语句执行流程

1、一条查询SQL语句是如何执行的（首先会与客户端建立连接） 2、查询缓存  （命中则直接返回结果）   一般不建议使用，每次修改操作都会清空缓存，MySQL8之后移除了查询缓存 3、分析器（进行词法分析、语法分析） 4、优化器（生成执行计划、选择索引） 5、执行器（操作引擎、返回结果）

更新语句执行流程

1、客户端，与服务端建立连接 2、解析SQL语句， 比如 update user set password = '123' where user_id=1; 3、将要修改的SQL语句，记录到Buffer Pool中 ，写入redo log，并修改状态为prepare 4、写入 bin-log（保证日志的一致性） 5、commit 6、将 redo log 里面的事务状态修改成 commit WAL（Write Ahead Log）、两阶段提交

索引

索引是什么？

加速数据查询的一种数据结构，但修改操作时会牺牲维护索引消耗的性能

分类

联合索引：多个字段组成的索引

聚簇索引：索引键值的逻辑顺序和表数据行的物理顺序一致    比如字典的目录是按照拼音排序的，内容也是按照拼音排序的这种目录就是聚簇索引 InnoDB引擎的表中一定会有聚簇索引 - 有主键的情况下，默认主键就是聚簇索引 - 没有定义主键的情况下，默认获取第一个非null的唯一列作为聚簇索引 - 没有满足条件的，则默认使用6字节的RowId作为聚簇索引

前缀索引： - 解决某些字段过长，全字段作为索引的话会占用大量空间 - 既要考量索引占用的空间，也要考量前缀索引对应长度的离散度，以选择合适的长度

索引数据结构推演

二分查找：有序数组、等值查询效率高，插入数据

Binary Searche Tree：极端情况下会变成链表，查询效率相对低下

AVL：极端情况下 会导致树可能会很深，就极大的增加了磁盘IO的次数

B Tree：树高可能还是会比较高、每个节点上都存储了当前id对应的所有数据  关键字数 = 路数-1

B+Tree - 合理了利用了操作系统的空间局部性原理（操作系统的预读能力），数据存储在叶子节点 大大减少了IO次数 - 数据都存储在叶子节点，一次可以加载更多数据节点 - 查询性能相对比较稳定（每次都要到叶子节点取数据） - 叶子节点有一个指针，会顺序链向下一个元素 - 排序、范围查询能力增强 - 关键字数等于路数，左开右闭区间

为什么不用红黑树 红黑树的特征： 1、节点分为红色或者黑色 2、根节点必须是黑色 3、叶子节点都是黑色的NULL节点 4、红色节点的两个子节点都是黑色（不允许两个相邻的共色节点） 5、从任意节点出发，到其每个叶子节点的路径中包含相同数量的黑色节点 结论：只有两路，不够平衡

自适应的Hash索引 - MySQL内部会监控 索引的扫描、遇到合适的情况会自动优化成内存Hash索引 - 前提是要开关没有关闭（默认 是打开的）innodb_adaptive_hash_index(ON)

为什么要用索引？

大量数据查询， 直接扫描表数据会非常耗时，索引就应运而生

怎么用索引才好？

索引使用原则

最左前缀原则：（仅针对联合索引）   查询数据库表有哪些索引：show index from  表名; 联合索引 alter table table_name add index  idx_1_2_3 (column1,column2,column3) 可以用到的索引条件有： column1、（column1，column2）、（column1，column2，column3） 用不到索引的条件： column2、column3、（column2，column3） 出现这种情况的原因： 因为联合索引的字段顺序就是（column1,column2,column3），如果查询条件不包含最左边条件的话， 那么MySQL索引不知道走左边还是右边

离散度高原则： 计算字段的离散度 => select  count(distinct(column_name)) / count(*)   from table_name；

索引下推：（ICP/Index Condition Pushdown）索引条件下推 直接在二级索引中过滤出不符合条件的数据，从而减少回表的次数 set optimezer_switch='index_condition_pushdown=on'

覆盖索引： 可通过Explain查看extra字段为 Using Index InnoDB中，就是通过索引可以获取找到对应的数据，而不需要回表  回表： InnoDB的普通索引叶子节点存储的是主键Id，并没有实际表Row数据，需要再次通过主键索引表查询到对应的数据

索引的创建注意事项

索引的创建

索引的创建tips： - 用于WHERE，ORDER BY，JOIN ON等条件字段 - 创建的索引不宜过多、索引会占用大量空间，也会影响数据增删改的效率 - 离散度低的字段不宜建索引 - 频繁更新的值，不适合作为索引，会频繁造成页分裂 - 联合索引，把离散度高的放前面 - 尽量建立联合索引，而不是单列索引

过长的字段如何建索引 - 前缀索引，测试离散度/ 通过Hash算法索引（新增字段存储）

为什么不建议用无序的值（例如身份证和UUID）作为索引 - 会频繁造成页的分裂和合并

索引失效

索引失效场景： - 索引列上使用了函数 - 索引类型和条件类型不匹配（出现隐式转换） - like条件中%放前面 - 负向查询 not like - != (<>) NOT IN 在某些情况下可以使用 - 和数据量等信息有关、具体看优化器 - 优化器基于开销（Cost Base Optimzer）

事务

什么是数据库事务？

事务的定义： 数据库管理系统执行过程中的一个逻辑单位，包含有限的数据库操作序列组成数据库最小的工作单元

事务的典型场景：转账、下单 数据库什么时候会出现事务：DDL（create/drop/alter）、DML（insert/update/delete） 哪些存储引擎支持事务：InnoDB MySQL InnoDB 对隔离级别的支持

事务的四大特性： - Automic，原子性，事务是最小执行单位，不可分割，要么都执行成功，要么都失败 - C 一致性，数据库的完整性约束没有被破坏，事务执行前后都是合法的状态 事务回滚：依赖undo log - I，隔离性，事务之间是互不干扰的， - D，持久性，事务一旦提交，就永久生效 持久性是如何实现的？redo log和double write双写缓冲来实现的

事务并发会带来什么问题： - 数据不一致 两大实现方案： - LBCC （基于锁的并发控制） - MVCC（基于多版本并发控制）

为什么要有数据库事务？

保证数据和业务逻辑的逻辑的一致性

如何合理的使用数据库事务？

隔离级别的实现 - Read Uncommited（读未提交-可能造成脏读） - Read Commited（读提交-不可重复读） - Repeatable Read（可重复度-幻读（insert才造成）） - Serializable（串行化-读加读锁，写加写锁）

锁

锁是什么？

MySQL InnoDB锁的基本类型

锁的粒度：行锁、表锁

共享锁 S：可以多个会话共享

排它锁 X：独占

意向锁 E：属于表锁