MySQL知识点总结

年少有为112
292 阅读15分钟

MySQL执行流程

客户端发出一条SQL语句后，有以下几个步骤

  1. 通过连接器建立连接，处理验证权限等相关的事情
  2. 在分析器中进行词法分析和语法分析，得到抽象语法树
  3. 在优化器中进行优化，这里有一个CBO（cast）基于成本的优化和一个RBO基于规则的优化，优化完得到最优的执行方式
  4. 把执行方式交给执行器来执行
  5. 执行器通过存储引擎，查询相应的数据，返回给server层，再给客户端

（8.0之前有查询缓存，8.0以后被废弃。大部分操作在分析器和优化器中处理。）

大致就是这个流程，其中select和insert、delete、update略有不同，select只需将磁盘的数据加载到内存中即可，其余三个将数据加载到内存后进行增删改的时候，还会向undolog，redolog，binlog中写入相应的日志，其中undolog是作用于事务回滚以及MVCC中的，redolog和binlog利用两阶段提交保证数据的一致性；处理完日志，最后再将数据刷写到磁盘中。（数据写到磁盘中也是有相应的流程的，这就涉及到Innodb的双写机制，首先会将数据刷写到内存中的Double write Buffer，再将数据分别刷到一个共享空间和真正应该存在的位置。这种机制的作用就是避免刷新脏页的时候只写入一半造成数据混乱。这里有个知识点，WAL，write ahead log预写日志。）数据刷写到磁盘中的结果有两种，第一种是成功，那就正常操作就行，第二种是失败，失败又分SQL执行失败和特殊情况（比如断电），如果是SQL执行失败的话，那就利用undolog进行回滚，如果是特殊情况的话，那就要利用redolog和binlog来恢复数据。

MySQL的两阶段提交

主要解决 binlog 和 redo log 的数据一致性的问题。

  1. 执行器先从引擎中找数据，如果数据在内存中直接返回，否则查询后返回；
  2. 执行器拿到数据后会先修改数据，然后调用引擎接口重新写入数据；
  3. 引擎将数据更新到内存，同时写数据到redo log中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作；
  4. 执行器生成这个操作的binlog；
  5. 执行器调用引擎的事务提交接口，引擎把刚刚写完的redo log改成commit状态，更新完成；

redolog和binlog的区别

  1. redolog是Innodb引擎特有的日志，binlog是MySQL Server层的日志；
  2. redo log 是物理日志，记录的是在某个数据页上做了什么修改。binlog 是逻辑日志，记录的是这个语句的原始逻辑，记录了 DDL 和 DML 语句，主要目的是复制和恢复；
  3. redo log 是循环写的，空间固定会用完。binlog 是可以追加写入的。

Innodb的四大特性

  1. 插入缓冲：能将多个插入合并到一个操作中，可以减少随机IO带来性能损耗；
  2. 双写机制：避免刷新脏页的时候只写入一半造成数据混乱；
  3. 自适应哈希：如果观察到建立hash索引可以提升性能，就会在缓冲池中建立hash索引；
  4. 预读：预测哪些页会被读取到，将它读到缓冲池中。

数据库事务的四个特性：

ACID

  1. atomicity(原子性)：一个事务要么全部完成，要么什么也不做，执行过程中发生错误会回滚到事务开始前的状态。通过undolog实现；
  2. consistency(一致性)：开始事务之前和结束事务之后，数据库的完整性约束没有被破坏。例如转账，A,B的总和不会被破坏；通过两阶段提交实现；
  3. isolation(隔离性)：多个并发的事务会相互被隔离。通过MVCC加锁实现；
  4. durability(持久性)：事务完成后，对数据库的操作便持久的保存在数据库中。通过redolog实现。

并发事务带来的问题：

  1. 脏读：指一个事务读取到了另外一个事务中未提交的数据；
  2. 不可重复读：指一个事务读取到了另外一个事务中提交的update的数据；
  3. 幻读：指一个事务读取到了另外一个事务中提交的insert，delete的数据。如果事务中都是用快照读，那么不会产生幻读的问题，但是快照读和当前读一起使用的时候就会产生幻读

MVCC如何实现多版本并发控制的？

mvcc的实现原理主要依赖于记录中的三个隐藏字段，undolog，read view来实现的。这三个隐藏字段是DB_ROW_ID，隐藏的主键；DB_TRX_ID，最近修改事务id，记录创建这条记录或者最后一次修改该记录的事务id，DB_ROLL_PTR回滚指针，用于配合undolog，指向上一个旧版本。undolog为回滚日志，在进行insert，delete，update操作的时候产生的方便回滚的日志。Read View是事务进行快照读操作的时候生成的读视图，他有三个属性，分别是trx_list，一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID，up_limit_id，记录trx_list列表中事务ID最小的ID，low_limit_id，Read View生成时刻系统尚未分配的下一个事务ID。他还有三个比较规则：

  1. 首先比较DB_TRX_ID < up_limit_id,如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于等于进入下一个判断；
  2. 接下来判断DB_TRX_ID >= low_limit_id,如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的，那么对于当前事务肯定不可见，如果小于，则进入下一步判断；
  3. 判断DB_TRX_ID是否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read View生成之前就已经开始commit，那么修改的结果是能够看见的。 举例子！画图

在RC隔离级别下，是每个快照读都会生成并获取最新的Read View，而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View。

MySQL的锁

MySQL的表级锁有两种模式：表共享读锁和表独占写锁。可以通过lock table table_name read 或者write来手动去加锁（释放unlock tables）。读锁的话就是多个线程可以同时去读一张表，但会阻塞对该表的写请求。写锁的话就是只有持有锁的线程可以对表进行读写操作。其他线程的读写操作都会等待，直到锁释放为止。

行锁和表锁原理差不多，不过行锁是给某条数据加锁，表锁是直接锁一张表，InnoDB行锁是通过给索引上的索引项加锁来实现的，所以只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

innodb有共享锁（lock in share mode） 和排他锁（for update）

myisam innodb 区别

查看引擎语句：show table status from 数据库库名 where name = 表名;

支持事务支持外键索引类型锁全文索引保存行数唯一索引
innodb支持支持聚簇，也可以有非聚簇表锁，行锁有不保存需要
myisam不支持不支持非聚簇表锁无保存不需要

为什么innodb没有保存行数：因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的。

InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己生成一个隐藏列Row_id来充当默认主键），而Myisam可以没有。

聚簇索引和非聚簇索引

聚簇索引就是索引和数据在一起，非聚簇所以就是索引和数据分开。Myisam中主索引和次级索引都指向物理行，而Innodb主键索引直接存储当前行数据，次级索引下存储的是主键，通过次级索引（非聚簇索引）查询的时候是先查询到主键，再通过主键拿数据信息。

误区：例如InnoDB的聚簇索引，索引和数据是在一起，例如索引是id，不是说数据中就没有id了，索引是索引，数据是数据，数据就是一整行记录。

数据库优化：

对数据库字段的优化，可以根据实际情况选择合适的字段，例如对数字的操作会比对字符操作快，例如varchar和char 的选择，varchar在硬盘中是变长存储的，在内存中是按照初始设定的大小分配空间的，char就是定长的，最大长度不超过255个字符。

然后就是一些语句的优化，也就是有关索引的东西。比如说要避免索引失效的情况：避免使用is null条件或者!=的操作，还有使用like %..的操作，避免在索引上做计算、函数、类型转换的操作。还有就是要注意最左匹配原则。举个分页查询的例子，如果直接select * from table limit 90000, 10会很慢，所以可以先select id from table order by id limit 90000, 1，然后再根据刚刚查出来的id，select * from table where id > 刚刚的id limit 10。再者可以结合覆盖索引，建立前缀索引的方式来建立索引优化查询。

总而言之，就是最好是根据索引来查找数据，尽量避免全表查询，通过explain，show profiles等一些工具来查找慢的原因，提升执行计划的type等级。但是索引也是需要维护的，所以要根据实际需求创建合适的索引。

type：system>const>ref>range>index>all

B+树

它是一种多路搜索树，不同于二叉搜索数，AVL数，红黑树，他们都会因为树的深度过深而造成io次数变多，影响读取的速率。它也不同于B树，B树他每个节点有键值，有指针，也有数据，B+树在这个基础上，让数据只存在叶子节点上，非叶子节点都只放键值和指针，这样每个块就可以存储更多的键值和指针，相对于B树就可以表示更广的数据范围，也就是说同样次数的io，B+树可以读更范围的数据。

mysql 索引类型：

  1. 普通索引 index
    alter table table_name add index index_name(column)
    create index index_name on table_name(column)
    drop index index_name on table_name
    alter table table_name drop index index_name
  2. 唯一索引 unique
    alter table table_name add unique index_name(column)
    create unique index index_name on table_name(column)
  3. 主键索引 primary key
    alter table table_name add primary key index_name(column)
    alter table table_name drop primary key
  4. 组合索引 index
    alter table table_name add index index_name(column1, column2, ...)
    create index index_name on table_name(column1, column2, …)
  5. 全文索引 fulltext
    适用于char，varchar，text类型，要配合用match against使用
    alter table table_name add fulltext index_name(column)
    create fulltext index index_name on table_name(column)

回表、覆盖索引、组合索引、索引下推

回表：先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。

覆盖索引：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

组合索引：非叶子节点存储的是联合索引，并且按照一定的顺序在节点间排队；对于innodb，叶子节点存储的是联合索引+主键索引；对于MyISAM，叶子节点存储的是联合索引+data地址。使用组合索引要遵循最左匹配原则。例如组合索引(a, b, c)，实际创建索引(a)，(a, b)，(a, b, c)，查询条件中要先a，再b，再c。如果使用比较符号，like %在前，or连接符，索引就可能会失效。例如where a = 1 and b > 1 and c = 1;那么实际只使用到(a, b)。

索引下推：使用组合索引时，在存储引擎取数据时就把数据过滤出来。例如name age组合索引，本来是根据name把age全取出来，现在直接在存储引擎中把age过滤掉，减少io

drop truncate delete 区别

drop是删除表；
truncate是删除表中所有的数据，并不能进行回滚；
delete 是根据条件删除表中的数据，可以进行回滚；
执行速度drop > truncate > delete。

SQL join 原理

  1. Simple Nested-Loop Join
    驱动表的每一列去和匹配表进行匹配。原理简单，但是开销大。
  2. Index Nested-Loop Join
    可以通过索引来减少比较，从而加速查询。在索引嵌套链接的方式下，如果非驱动表的关联键是主键的话，这样来说性能就会非常的高，如果不是主键的话，关联起来如果返回的行数很多的话，效率就会特别的低，因为要多次的回表操作。
  3. Block Nested-Loop Join
    使用join buffer将驱动表的查询join相关列都给缓冲到了 join buffer 当中，然后批量与非驱动表进行比较，可以极大减少磁盘的IO，相较于Simple Nested-Loop Join开销较小。这里开销小的意思是将驱动表的一部分数据缓存到内存中，和非驱动表去做比较，这样减少了非驱动表的扫描次数（IO次数），从而提升了性能，牺牲内存，提升性能

int类型

TINYINT 1字节 8位
SMALLINT 2字节 16位
MEDIUMINT 3字节24位
INT 4字节32位
BIGINT 8字节64位

time类型

datatime （精确到ms，8字节）
timestamp （常用，精确到s，4字节） 依赖数据库设置时区
date （精确到day，3字节）

Mysql指令

-- 查看事务隔离级别
select @@tx_isolation
-- 设置事务隔离级别
set session transaction isolation level repeatable read(read uncommitted read committed serializable);
-- 查看引擎
show table status from 数据库库名 where name = 表名;
-- 查看表结构
desc table_name;
-- 查看数据
show databases;
-- 重置密码
alter user 'root'@'localhost' identified with mysql_native_password by ''
-- 建表
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  `age` int(11) NOT NULL,
  `sex` int(1) NOT NULL COMMENT '0为男，1为女，2为未知',
  `desc` varchar(300) DEFAULT NULL COMMENT '自我介绍，300字以内',
  PRIMARY KEY (`id`),
  KEY `age_name` (`age`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

join关联查询和where的关联查询区别：

join是将符合on条件的数据连接起来行成新的表，where是先将两张表通过笛卡尔积关联成新表，再去判断条件，取符合条件的数据行成一张表。

MySQL视图

视图是一个虚拟表，是sql的查询结果，其内容由查询定义。视图的数据变化会影响到基表，基表的数据变化也会影响到视图。安全，性能好，灵活。

日期函数总结

获取系统当前时间current_timestamp，current_timestamp()

批量更新数据

UPDATE categories 
SET orderId = 
    CASE id 
    WHEN 1 THEN 3 
    WHEN 2 THEN 4 
    WHEN 3 THEN 5 
END, 
title = 
    CASE id
    WHEN 1 THEN 'New Title 1'
    WHEN 2 THEN 'New Title 2' 
    WHEN 3 THEN 'New Title 3' 
END WHERE id IN (1,2,3)

说明：那更新orderId来说明，WHEN 1 THEN 3表示当id为1，将orderId更新为3。

Linux 安装MySQL

  1. 解压，创建data目录

2022/06/20复习新增知识点

  1. char 类型最大长度255，会自动去除首尾空格
  2. blob、text一般不会用，会使用文件地址加文件的形式实现
  3. datetime精确到毫秒，8字节，与时区无关；timestamp精确到秒，4字节，最大存储到2038年；date，3字节，存日期
  4. 枚举类型 create table enum_table(e enum("男", "女")); "男"对应的就是1，"女"对应的就是2，实际存储的时候存的是1， 2。地址省市区可以使用该类型
  5. ip使用UNSIGNED INT进行存储，使用INET_ATON()和INET_NTOA()函数进行转换，INET_ATON()是将字符ip转换成相应的整形
  6. 三范式：第一范式，列不可分；第二范式，属性完全依赖于主键（消除部分子函数依赖）；第三范式，属性不依赖于其他非主属性
  7. 尽量使用代理主键，不要使用自然主键，代理主键与业务不耦合，因此更容易维护
  8. 各种字符集：
  9. 视图是什么
  10. 垂直切分，水平切分
  11. 数据库会为唯一键建索引
  12. 前缀索引，使用某个列开头的部分字符串做索引
  13. 利用索引扫描进行排序操作，where 查询条件和 order by 组成的语句满足最左匹配即可利用索引扫描进行排序操作，并且order by条件中的升降序要一致
  14. in通过父查询表和子自查询表作hash连接操作查询；or是对父查询表作loop循环后再对子查询表进行查询；在没有索引或者主键的情况下，随着in或or后面的数据量增多，or的执行效率会有明显下降，in的执行效率不会明显下降。
  15. in or 都有可能会不走索引，具体要看优化器优化的结果，最好执行前explain一下，也可以强制使用索引 force index
  16. select ... from a exist(...)，先查外层，当exist中执行的语句有结果，保留外层，否则舍弃外层
  17. 更新十分频繁，数据区分度不高的字段上不宜建立索引
  18. join on 后面的是连接条件 where 后面的是筛选条件，可以使用左右外连接测试
  19. STRAIGHT_JOIN 强制join的顺序
  20. mysql 等值传播，用外连接测试一下
avatar
文章
阅读
粉丝