mysql


mysql 执行流程

mysql 大体上可分为 Server 层和 存储引擎 层两部分

  1. server层

    • 连接器：TCP握手后服务器来验证登录用户身份，A用户创建连接后，管理员对A用户权限修改该了也不会影响到已经创建的链接权限，必须重新登录。
    • 查询缓存：查询后的结果存储位置，Mysql8.0版本以后已经取消，因为查询缓存失效太频繁，得不偿失。
    • 分析器：根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法
    • 优化器：多种执行策略可实现目标，系统自动选择最优进行执行
    • 执行器：判断是否有权限，将最终任务提交到存储引擎

  2. 存储引擎

    负责数据的存储和提取，其架构模式是插件式的

    支持 InnoDB、MyISAM、Memory 等存储引擎。现在最常用的存储引擎是InnoDB，MySQL 5.5.5（默认）


事务

  1. 原子性（Atomicity）

    把多个操作放到一个事务中，要么全部做完，要么全部不做。

  2. 一致性（Consistency）

    事务在开始和结束后，保证 数据的完整性。如A向B转账，必须保证A扣了钱，B一定能收到钱

  3. 隔离性（Isolation）

    事务之间的完全隔离。比如A向一张银行卡转账，避免在同一时间过多的操作导致账户金额的缺损，所以在A转入结束之前是不允许其他针对此卡的操作的。

  4. 持久性（Durability）

    事务的对数据的影响是永久性的。事务完成后，对数据的操作都要进行持久化。事务一旦完成就是不可逆的，在数据库的操作上表现为事务一旦完成就是无法回滚


事务的并发

  1. 脏读

    一个事务读取另外一个事务还没有提交的数据 叫脏读。

    例如：事务T1修改了一行数据，但是还没有提交，这时候事务T2读取了被事务T1修改后的数据，之后事务T1因为某种原因Rollback了，那么事务T2读取的就是脏数据

  2. 不可重复读

    同一个事务中，多次读出的同一数据是不一致。

    例如：事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果

  3. 幻读

    一个事务在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行

    例如：在仓库管理中，管理员要给刚到的一批商品进入库管理，当然入库之前肯定是要查一下之前有没有入库记录，确保正确性。管理员A确保库中不存在该商品之后给该商品进行入库操作，假如这时管理员B因为手快将已将该商品进行了入库操作。这时管理员A发现该商品已经在库中。就像刚刚发生了幻读一样，本来不存在的东西，突然之间他就有了。

  • 脏读侧重数据的正确性
  • 不可重复度侧重对数据的修改
  • 幻读侧重于数据的新增和删除

事务隔离级别

隔离级别 脏读 不可重复度 幻读
读未提交（read-uncommitted） 是 是 是
读已提交（read-committed 否 是 是
可重复读（repeatable-read） 否 否 是
可串行化（serializable） 否 否 否

Char 和 Varchar的区别别

长度大小区别

  • CHAR(M)定义的列的长度为固定的
  • VARCHAR(M)定义的列的长度为可变长

存数据时的区别

  • CHAR值存储时，如果字符数没有达到定义的位数，会在后面用空格补全，再存入数据库中。比如定义 CHAR(10)，那么不论存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充
  • VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充

取数据时的区别

  • 数据库取CHAR值时，尾部的空格会被删除
  • 数据库取VARCHAR值时，尾部的空格仍然保留

查询事务在各个级别下的值

答案 读未提交 读已提交 可重复读 可串行化
V1 2 2 1 1
v2 2 2 1 1
V3 2 2 2 2

参考链接


myisam 和 innodb 的区别

  1. 事务方面

    innodb 支持事务，myisam 不支持事务

  2. 外键方面

    innodb 支持外键，而 myisam 不支持。对一个包含外键的 innodb 表转为 myisam 会失败。

  3. 索引层面

    innodb 是聚簇索引，myisam 是非聚簇索引

    myisam 支持 fulltext类型的全文索引， innodb 不支持 FULLTEXT 类型的全文索引，但是 innodb 可以使用 sphinx 插件支持全文索引，并且效果更好。

  4. 锁粒度方面

    innodb 最小的锁粒度是 行锁 ，myisam 最小的锁粒度是 表锁 。

    一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

  5. 硬盘存储结构

    myisam 在磁盘上存储成三个文件

    • . frm 文件存储表的定义
    • 数据文件 的扩 展名为 . MYD (MYData)。
    • 索引文件 的扩 展名是 . MYI (MYIndex)。

    innodb 存储引擎存储数据库数据，一共有两个文件(没有专门保存数据的文件)：

    • Frm 文件：表的定义文件。
    • Ibd 文件：数据和索引存储文件。数据以主键进行聚集存储，把真正的数据保存在叶子节点中。

局簇索引和非局簇索引的区别

  1. 聚簇索引（innodb）

    将数据存储与索引 放到了一块，索引结构的叶子节点 保存行数据

    表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。

    innodb 中，在聚簇索引之上创建的索引称之为辅助索引，像复合索引、前缀索引、唯一索引等等。

    • 聚簇索引默认是主键，
    • 如果表中没有定义主键，innodb 会选择一个 唯一的非空索引 代替。
    • 如果没有这样的索引，innodb 会在内部生成一个名为 GEN_CLUST_INDEX 的隐式的聚簇索引。

  2. 非聚簇索引（MyISAM）

    将数据与索引 分开存储，表数据存储顺序与索引顺序无关


索引查询过程

  1. 非聚簇索引存储结构（MyISAM）

    image

    MyISAM的 B+树 的叶子节点上，记录的是真实数据的存储地址

    通过主键id查询的时候，InnoDB比MyIsam快一些，因为InnoDB只需要一次B+树查找就能取出数据。MyIsam通过B+树查找到地址后，还需要根据地址去查询真正的数据

    InnoDB重构数据的时候就不会这样，因为他记录的是主键id，地址会变化，主键id是不会变的。

  2. 聚簇索引存储结构（innodb）

    image

    InnoDB的 B+树 的叶子节点上，记录的是真实行数据


9. 分库分表（先垂直后水平）

  1. 垂直分表：大表拆小表，基于字段拆分，一般表示字段较多不常用的。或者长度较长，如text类型。一般是针对那种几百列的大表，也避免查询时，数据量太大造成的“跨页”问题。

  2. 垂直分库：根据系统不同业务拆分，如user，order，Producet表一个库，切分后要放在多个服务器上。因为单个库会让服务器的磁盘空间，内存，tps等非常吃紧

  3. 水平分表：单张表按照HASH取模、RANGE排列，切分到多张表里。但是这些表还是同一张库，会用IO瓶颈。

  4. 水平分库：将单张表数据切分多个服务器上，每个服务器都有相应的库和表，只是表中的数据集合不同。解决单机、单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。


10. 分库分表注意

  1. 水平分库分表切分规则

    1. 分布式id：确保唯一性跟数字递增性

    2. HASH取模：取用户id，然后hash取模，分配到不同的数据库上

    3. 地理区域：比如按照华东，华南，华北这样来区分业

    4. 时间：按照时间切分就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”

  2. 查询

    1. 全局表：基础数据，所有库都拷贝一份。

    2. 字段冗余：这样有些字段就不用join去查询了。

    3. 系统层组装：分别查询出所有，然后组装起来，较复杂。


11. mysql千万级数据表结构修改

当需要对表进行ddl操作如加索引、增删列时，数据量小时直接在线修改表结构影响不大

当表达到百万、千万数据就不能直接在线修改表结构

// 先备份数据
select * from order into outfile '/bak/order.txt';
// 复制原表物理结构
create table new_order like order;
// 执行表结构修改，这里是增加索引
alter table  `new_order` add index (  `consulate` );
// 把原表数据导入新表
insert into new_order select * from order;
// 删除原表
drop table order;
// 新表重命名原表
rename table new_order to order;

注意：

  1. 这种方法会影响业务运行，建议使用在线ddl工具：pt-online-schema-change，且在访问量少时进行
  2. 大多数alter table会涉及到锁表，而过程不可被kill,一旦执行就不可回退
  3. 从mysql5.6开始，Online DDL特性被引进。增强了很多种类的Alter Table操作避免拷贝表和锁表，在运行Alter操作的同时允许运行select,insert,update,delete语句。我们可以通过使用ALGORITHM和LOCK选项抑制文件拷贝和加锁。

12. mysql 出现宕机

查看mysql错误日志，在错误日志中查找从服务器宕机的binlog信息。重新在从服务器中配置change master to。

  1. mysql 磁盘占满了会宕机

    数据迁移

  2. innodb 运行内存占满 （Cannot allocate memory for the buffer pool）

    修改内存值

    为系统swap分区分配一个值


13. mysql强制索引和禁止某个索引

  1. mysql 强制使用索引：force index（索引名或者主键PRI）
select * from table force index(PRI) limit 2;
  1. mysql 禁止某个索引：ignore index （索引名或者主键PRI）
select * from table ignore index(PRI) limit 2;

14. BinLog、RedoLog、UndoLog

  1. Binlog

    记录所有数据库表结构变更以及表数据修改的二进制日志

    主从数据库同步用到的都是BinLog文件。BinLog日志文件有三种模式。

    1. statement 模式：binlog 只会记录可能引起数据变更的 sql 语句
    2. row 模式：记录每次操作的源数据与修改后的目标数据
    3. mixed 模式：是对上述STATEMENT 跟 ROW 两种模式的混合使用

  2. redolog

    理解为饭点记账可以把账单写在 账本 上也可以写在 粉板 上。有人赊账或者还账的话，一般有两种做法：

    1. 直接把账本翻出来，把这次赊的账加上去或者扣除掉
    2. 先在粉板上记下这次的账，等打烊以后再把账本翻出来核算

    生意忙时选后者，因为前者太麻烦了。得在密密麻麻的记录中找到这个人的赊账总额信息，找到之后再拿出算盘计算，最后再将结果写回到账本上

    同样在MySQL中如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。而粉板和账本配合的整个过程就是MySQL用到的是Write-Ahead Logging 技术，它的关键点就是 先写日志，再写磁盘。此时账本 = BinLog，粉板 = RedoLog

    write pos ：是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。

    check point：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

  3. binlog跟redolog区别：

    1. redo log是InnoDB引擎特有的，binlog是MySQL的Server层实现
    2. redo log是物理日志，记录的是在某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给ID=2这一行的c字段加1
    3. redo log是循环写的,空间固定会用完。binlog log可以追加写入的

15. 主从同步流程

image


16. mysql查看binlog文件日志

  1. 查看binlog是否开启
show variables like '%log_bin%';

OFF已关闭，ON已开启。对于存放重要的业务数据的mysql，强烈建议开启binlog

  1. 查看binlog存放文件目录
show variables like '%datadir%'
  1. 查询binlog文件，写入备用txt中
mysqlbinlog --no-defaults --database=youxi --start-datetime="2020-11-12 09:00:00" --stop-datetime="2020-11-13 20:00:00" /data/mysql/mysql-bin.000015 > a.txt
  1. 查询binlog文件，输出到屏幕上
mysqlbinlog --no-defaults --database=youxi --start-datetime="2020-11-12 09:00:00" --stop-datetime="2020-11-13 20:00:00" /data/mysql/mysql-bin.000015 | more

17. 索引失效的原因

  1. 单值索引B+树

    一个节点只存一个键值对

image

  1. 联合索引B+树

    由数据库a，b组成联合索引。和单值索引不同的是，联合索引的键值对不是1，而是大于1
    image

  2. a，b 排序分析

    a 顺序：1，1，2，2，3，3

    b 顺序：1，2，1，4，1，2

    a 字段是有序的，b 字段是无须的，因为 b+数 只能选购一个字段来构建有序树

    在 a 相等的的情况下，b 字段是有序的


18. 最佳左前缀

select * from user where a = 1 and b = 2

首先 a 字段 在 B+树 上是 有序的，所以可以通过二分查找法来定位到 a=1 的位置

其次 在 a 确定的情况下，b 是有序的，可以通过二分查找法找到 b=2 的位置。

select * from user where b = 2

b 顺序的前提是有 a，但找不到 a，b 无法用二分查找定位


19. 范围查询右边失效原理

select * from user where a > 1 and b = 2

首先 a 字段在 B+树 上是有序的，所以可以用二分查找法定位到 1，将所有大于 1 的数据取出来， a 可以用到索引

b 有序的前提是 a 是确定的值，那么现在a的值是取大于 1 的，可能有 10 个大于 1 的 a，也可能有一百个 a

大于 1 的 a 那部分的B+树里，b 字段是无序的，所以 b 不能在无序的 B+树 里用二分查找来查询，b用不到索引


20. like索引失效原理

select * from user where name like "a%"

select * from user where name like "%a%"

select * from user where name like "%a"

%放在右边，代表查询以"a"开头的数据，前缀，如：abc

两个%%，代表查询数据中包含"a"的数据，中缀，如：cab、cba、abc

%放在左边，代表查询以"a"为结尾的数据，后缀，如cba


依旧使用最佳左前缀法则概念，先按照第一个字母排序，如果第一个字母相同，就按照第二个字母排序。。。以此类推
image

  1. %号放右边（前缀）

    由于B+树的索引顺序，是按照首字母的大小进行排序，前缀匹配先匹配首字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。所以有些时候可以用到索引。

  2. 两个%%号（中缀）

    这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的

  3. %号放左边 （后缀）

    是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的，所以不能按照索引顺序查询，就用不到索引。


21. 数据结构可视化在线演示

  1. data 可视化
posted @ 2021-03-12 19:05  linsonga  阅读(145)  评论(0)  编辑  收藏  举报