首页  思维导图  详情

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：属于表锁