Mysql基础知识总结

字段类型

• unsigned：是指无符号整数；
• char和varchar：char是定长字符串，如果设置了长度会在右边自行补充空格；varchar是不定长字符串。
• decimal和float/Double：decimal是用于金融领域，提供了固定精度和精确算数，float和double就是二进制浮点数。
• Text和Blob：用于存储长文本数据，Blob用于存储视频的二进制文件。
• Datetime和TimeTamp：前者是时间，后者是时间戳，后者会考虑时区；
• null和‘’：null不是为空，而是指值不确定，前者会占内存，后者不会占用；
• tinyin（1）：没有专门的boolean类型，这个是，1或者0.

Mysql的基础构架

mysql的基础构架？

连接器、解析器、优化器、执行器和存储引擎。

mysql是怎么执行一条select语句过程发生了什么？

• 连接器先连接客户端，身份验证，权限验证；
• 解析器进行语法解析、语义解析；
• 优化器成本，并且选择成本最小的方案；
• 执行器；执行器执行计划，并且通过存储引擎获取数据。

mysql是怎么执行一条update语句过程发生了什么？

• 连接器先连接客户端，验证权限；
• 解析器进行语法解析、语义解析；
• 优化器，选择成本最小的方案；
• 执行器：

• • 先查找到需要修改的数据行，将数据进行修改；
  • 然后将修改写入到redo log中，记录为prepare状态；
  • 再将其写入到binlog里面，并将其写入磁盘，
  • 最后在redo log里面改commit状态。

完成了updata语句的。

Mysql的存储引擎

Mysql又是什么存储引擎？

Innodb、MyISAM和Memery

说说Innodb、MyISAM的区别？

在mysql5.5.5之前默认使用的MyISAM,在Innodb之后是Innodb。

二者的区别：

• 锁：MyISAM只支持表级锁；Innodb支持行级锁，也支持表级锁；
• 多版本并发控制MVCC：MyISAM不支持，Innodb支持；
• 事务：MyISAM不支持事务，Innodb支持事务；
• 外键的支持：IMyISAM不支持外键，Innodb支持外键；
• 异常恢复：MyISAM不支持异常崩溃上的安全恢复，Innodb支持；（Innodb的日志redolog和undolog）
• 索引：虽然二者都是B+树但是具体的实现方式不同，Innodb是聚簇索引，MyISAM是非聚簇索引。

（什么是聚簇索引，什么是非聚簇索引？

聚簇索引和非聚簇索引最主要的区别是数据和索引是否分开存储。

聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行。

非聚簇索引：将数据进和索引分开存储，索引叶子节点存储的是指向数据行的地址。

在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为主索引，也是聚簇索引，在主索引之上创建的索引为辅助索引，也是非聚簇索引。为什么说辅助索引是在主索引之上创建的呢，因为辅助索引中的叶子节点存储的是主键。

在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，也就是说索引结构的叶子节点存储的都是一个指向数据行的地址。并且使用辅助索引检索无需访问主键的索引。
原文链接：www.mianshi.online/mysql-clust…）

日志文件

常见的日志文件：

• error log：错误日志，记录Mysql启动，运行，关闭的日志；
• general log：一般查询日志，记录所有对Mysql的请求，无论请求是否正确；
• bin log:二进制日志，记录对数据库表结构或表数据的修改，多用于对数据的备份和复制；

Innodb（独有）：

• redo log：重做日志，记录了对于Innodb存储引擎的事务日志，实现了事务的持久性；
• undo log：回滚日志，作用于对事务进行回滚，实现了事务的原子性；

bin log和redo log有什么区别？

• bin log多种存储引擎都存在，redo log只存在于Mysql；
• 使用场景：bin log多用于备份和复制，redo log则是用于故障恢复；
• 写入方式不同：bin log是追加写入，而redo log是循环写；
• bin log记录的是关于一个事务的具体操作，是逻辑日志；而redo log记录的是对于某页具体的操作，也就是物理日志，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；。

bin log什么时候刷盘？

为了持久化考虑，大概有三种情形：

• 提交事务后刷盘；
• 在binlog有一个bin log buffer区，如果超过一半左右也会刷盘；
• 后台有线程，每秒刷一次盘。

索引

索引有哪些?

类型来说：唯一索引，组合索引，普通索引

数据结构来分有：hash索引，B树，B+数

物理存储：

• 聚簇索引：索引和数据放在一起
• 非聚簇索引：索引和数据不放在一起

为什么使用索引？索引怎么加快查询？

原因：加快查询效率

当建立索引会根据BTREE算法生成索引文件，查找数据就先查索引，再根据索引找到具体试数据，就和看书翻目录一样。

什么时候创建索引？

• 在数据量大的表；
• 和其他表有联系的字段，比如外键；
• 需要排序的列：索引能够加快查询效率；
• 有唯一性的约束的列。

什么时候不适合创建索引？

• 小表，数据少：索引可能还没有直接遍历快；
• 数据离散差：比如性别，只有男女，建立索引就不是很好；
• 需要频繁更改的表：频繁的更改索引，提高资源开销。

什么情况下索引会失效？（9：4个关键字的问题，三个在对列上进行操作的问题，编码格式问题）

• 关键字or：某一条件没有索引，可能会导致，直接放弃使用索引全表查询；
• like：是模糊查询，%的使用，可能会导致失效；
• ！= <>：
• is null 和 is not null：不适等值判断
• 联合索引，查询的一个列不是联合索引的第一列：因为根据最左匹配原则；
• 在索引列上使用内置函数；
• 对查询字段进行加减乘除处理；
• 对于字符串没有用双引号，被隐式转换（解决办法：通过concat进行显式转换）
• 左连接或者有连接，连接查询的关键字编码格式不一样。

索引的数据结构？为什么使用B+锁？为什么树不深？

Innodb的底层使用的式B+树，叶子节点之间被双向链表连接，叶子结点只存放索引，不存放数据，数据只存放在非叶子节点，Innodb的为6b，一页为16kb。

考虑到三个方面：

• 从增删来说，B+数有很多冗余节点，对数据进行增删，很方便并且不易导致数据的变形；
• 从查询来说：扫库或者扫表，查询效率很稳定；
• 数据结构，让B+树不深，但是能存储千万级数据，减少了磁盘的IO数据。

回表查询？覆盖索引？索引下推优化？

回表查询就是，根据辅助索引查找主键索引的键值之后，还得再去根据这个键值回去查找数据，这就是回表查询。

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。通过覆盖索引可以解决，就是把主键值作为查询条件。

索引下推优化一定程度也可以减少回表，具体来说，就是把部分查询条件推至存储引擎。

（索引下推优化的核心思想是在执行索引查找的时候，将一部分查询条件下推至存储引擎层，由存储引擎直接进行判断。如果存储引擎能够判断某个条件并在索引上进行过滤，就可以避免将不符合条件的数据传递到 MySQL 服务器层，减少了回表的次数。）

最左前缀原则？/最左匹配原则？

最左匹配原则：在 InnoDB 的联合索引中，查询的时候只有匹配了前一个/左边的值之后，才能匹配下一个。

根据最左匹配原则，我们创建了一个组合索引，如 (a1,a2,a3)，相当于创建了（a1）、(a1,a2)和 (a1,a2,a3) 三个索引。

事务

什么是事务？

事务指的是一组操作，要么都执行，要么都不执行。

事务的四大特性？

A：原子性，事务要么全部完成，要么全部不完成，没有中间状态；undo log，会在日志中记录旧值，如果提交成功，在某个时间会自动删除，如果没有提交，会回滚撤销所有更改。

C：一致性：事务开始之前和结束之后，数据不会被破坏。

D：持久性：事务一旦提交修改，将持久的保存在数据库中。通过redo log实现，具体来说就是，通过先不修改数据，而是“先写日志”，即使数据库崩溃，通过redo log恢复，如果redo log写满了，就有可能直接去改数据库文件。

I：隔离性：事务和事务之间存在隔离。MVCC

事务的隔离级别：

读未提交：事务还没有提交修改，就被其他事务读取该修改。脏读

读已提交：事务提交之后才可以被读到。不可重复读。

可重复读：在同一事务多次读取某个数据，数据是一样的。

串行化：后访问的事务，必须等前一个事务执行完成，才会执行。（并发性差）

隔离级别	脏读	不可重复读	幻读
读未提交	是	是	是
读已提交	否	是	是
可重复读	否	否	是
串行化	否	否	否

什么是幻读，脏读，不可重复读？

幻读：事务A查询到某个结果集，事务B提交对结果集进行了增删，事务A再次查询的结果集就不一致了

脏读：某一个事务读数据的时候，读取到的是另一个事务修改的数据，但是另一事务没有提交。

不可重复读：同一个事务在读数据的时候，前面读到的数据和后面读到的数据不一致。

如何保证事务开启/Mysql的事务传播行为？

MVCC?

MVCC也是多版本并发控制，用来保持原子性。

每一个undo log都有一个回滚指针指向上一个undo log这样就行一个版本链，但是具体要差哪一个版本就要靠read view，在read view中有四个关键的值：创建该事务的id，当前所有活跃的事务id，最小活跃事务的id，下一个要分配事务的id。

如果小于最小活跃的事务id，说明已经执行完了，数据可见；

如果大于下一个要分配的事务id，说明还没有开始执行，数据不可见；

如果大于最小的活跃的事务id，但是大于最大活跃的事务id：

• 查询是否在当前所有的事务的id中，如果在是说明，还没执行，数据不可见；
• 如果不在，说明执行完了，数据可见。

读取数据的方式：快照读和当前读？

快照读：读的数据不是最新的数据，而是快照数据，适用于对一致性要求高的场景。为了解决幻读，通过MVCC
；

当前读：读取的时当前最新已经提交的数据，适用于对一致性要求不高的场景。为了解决幻读通过临值锁（记录锁+间隙锁）实现。

锁

Innodb中有几种锁？

行级锁：锁住某一行记录，锁粒度更小，针对索引字段加的锁，锁开销大；

表级锁：锁住某张表，实现简单资源消耗小，不会死锁。

MyISM只有表级锁，Innodb都有。

说一说行锁的实现？

记录锁：锁住某一行记录；

间隙锁：锁住某记录的中间的值，不包括记录本身值；

临值锁：记录锁+间隙锁，锁住记录和其间隙；

Inndb默认使用的时记录锁和间隙锁，但是如果对象是唯一索引，那么会降级为记录锁。

共享锁和排他锁？

共享锁：又称读锁，多个事务都可以获得，

排他锁：又称写锁，由某个事务独占，锁独占。

排他锁和任何锁都不兼容，共享锁只和共享锁兼容。

意向锁是什么知道吗？

意向锁是表级锁，为了判断该表中是否有其他锁，所以通过意向锁，在加锁之前需要先获取到意向锁。

意向锁之间不互相排斥，意向锁和表级别的锁，只有共享锁和意向共享锁兼容，其他的都互斥。

自增锁

这是对于设置为自增的键，增加的锁。

分布式

读写分离

• 数据库搭建了主从集群，或者一主多群；
• 数据库中都有所有的业务数据；
• 主数据库负责接收写请求，从数据库接收读请求。

主从复制

主从复制的步骤：

• 主机的修改记录进入bin log；
• 从机通过dump线程将bin log推送到从数据库；
• 从数据库连接到master的时候，创建IO线程接收bin log,复制到中继（relay log）日志；
• 从数据库通过relay log执行；
• 将修改记录进入到从数据库的bin log.

慢SQL优化

慢 SQL 优化的步骤可以精简为以下几个方面：

• 分析语句的执行计划，查看SQL语句的索引是否命中 优化数据库的结构，
• 将字段很多的表分解成多个表，或者考虑建立中间表。
• 优化LIMIT分页。

使用索引： 确保查询中涉及的字段上有适当的索引，以加速数据检索。

避免全表扫描： 尽量避免在大表上执行全表扫描，可以通过使用合适的索引或者优化查询语句来实现。

减少返回的数据量： 只选择需要的列，避免一次性返回过多的数据。

使用合适的数据类型： 选择合适的数据类型，避免不必要的数据类型转换。

优化查询语句： 确保 SQL 查询语句的写法是高效的，避免不必要的子查询，使用连接操作等。

分析执行计划： 使用数据库的执行计划工具，分析查询语句的执行计划，找出可能存在的性能瓶颈。（slow_query_log）

缓存重复查询结果： 对于一些查询结果比较稳定的数据，可以考虑使用缓存，避免重复查询。

分析数据库结构： 确保数据库表的设计是合理的，避免冗余字段、重复数据等问题。

定期清理无用数据： 删除不再需要的数据，定期清理日志等，保持数据库的轻量级。