mysql

Mysql的基本架构

数据库的三大范式

数据库三范式是指关系型数据库设计中的三种规范化设计原则，旨在减少数据冗余、提高数据一致性和可维护性。

第一范式规定表中的每个列都应该是不可分割的最小单元。第二范式是在满足第一范式的基础上，规定表中的非主键列不存在对主键的部分依赖。第三范式是在满足第一范式和第二范式的基础上，规定表中的列不存在对非主键列的传递依赖。使用数据库三范式的优势是：表的结构更简单、优雅，表的逻辑和条理性更强，并且使用三范式可以很大程度的减少表中的冗余数据，很好的节省了数据库的存储资源。

索引

索引类型

按照数据结构分：

1、B+Tree vs B Tree

B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。

2、B+Tree vs 二叉树

对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 34 层左右，也就是说一次数据查询操作只需要做 34 次的磁盘 I/O 操作就能查询到目标数据。

而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。

3、B+Tree vs Hash

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

按照物理存储分：

按照字段特性分类：

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

按照字段个数分：

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引；

MySql为什么使用B+树作为索引的数据结构

• B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。
• B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；
• B+ 树叶子节点之间用链表连接了起来，有利于范围查询，而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

索引的设计原则

1针对数据量大且查询较频繁的表建立索引

2针对常作为查询、排序、分组条件的字段建立索引

3尽量选区分度高的字段作为索引

4尽量使用联合索引，提高覆盖查询的概率，避免回表

5如果字符串字段较长，可建立联合前缀索引

6如果索引列不能存储null，在建表时使用not null，约束它

7尽量使用数据量小的字段建立索引

8增删改较多的字段不宜建立索引

9控制索引数量

索引失效的情况

不满足最左前缀法则

错误模糊查询

like’A%’ like’%A’ like’%A%',只有第一个会用到索引。

索引列使用了运算

select * from user where id+1=2;

查询列使用了mysql提供的函数例如or

索引列存在类型转换，例如字符串不加引号

where age='15’不会导致索引失效

当在查询中使用了 is not null 也会导致索引失效，而 is null 则会正常触发索引的

锁

事务

事务原理

事务是在 MySQL 引擎层实现的，我们常见的 InnoDB 引擎是支持事务的，事务的四大特性是原子性、一致性、隔离性、持久性，我们这次主要讲的是隔离性。

当多个事务并发执行的时候，会引发脏读、不可重复读、幻读这些问题，那为了避免这些问题，SQL 提出了四种隔离级别，分别是读未提交、读已提交、可重复读、串行化，从左往右隔离级别顺序递增，隔离级别越高，意味着性能越差，InnoDB 引擎的默认隔离级别是可重复读。

要解决脏读现象，就要将隔离级别升级到读已提交以上的隔离级别，要解决不可重复读现象，就要将隔离级别升级到可重复读以上的隔离级别。

而对于幻读现象，不建议将隔离级别升级为串行化，因为这会导致数据库并发时性能很差。MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了，详见这篇文章 (opens new window)），解决的方案有两种：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
• 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同：

• 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。
• 「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

在可重复读隔离级别中，普通的 select 语句就是基于 MVCC 实现的快照读，也就是不会加锁的。而 select … for update 语句就不是快照读了，而是当前读了，也就是每次读都是拿到最新版本的数据，但是它会对读到的记录加上 next-key lock 锁。

MVCC

undolog redolog binlog

• undo log（回滚日志） ：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
• redo log（重做日志） ：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• binlog （归档日志） ：是 Server 层生成的日志，主要用于数据备份和主从复制；

undo log 两大作用：

• 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。
• 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

InnoDB和MyISAM

Mysql的优化手段

MySQL 优化分为两大类：单表优化和多表优化。如果数据量不大的情况下使用单表优化即可，而如果数据量比较大的情况下可以考虑使用多表优化。
(#单表优化) 单表优化

单表优化手段有：

1. 建立并使用索引：索引是提升查询效率最有效的手段，所以对比查询比较频繁的字段一定要创建合适的索引。创建了索引之后，还要能正常的使用索引。
2. 优化查询语句：避免 SELECT *，只查询需要的字段。使用小表驱动大表，比如，当 B 表的数据小于 A 表时，先查 B 表，再查 A 表，查询语句：select * from A where id in (select id from B)。
3. 优化表结构和数据类型：单表不要有太多字段，建议在 20 个字段以内，使用可以存下数据最小的数据类型，尽可能使用 not null 定义字段，因为 null 占用 4 字节空间。

多表优化

多表优化手段有：

1. 表拆分：也就是分表，让每张表的数据量变小，从而提高查询效率。表拆分又分为：垂直分隔和水平分隔。

   1. 垂直拆分：是指数据表列的拆分，把一张列比较多的表拆分为多张表，比如，用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中，插入数据时，使用事务确保两张表的数据一致性。

      1. 垂直拆分的原则：

         1. 把不常用的字段单独放在一张表；
         2. 把 text，blob 等大字段拆分出来放在附表中；
         3. 经常组合查询的列放在一张表中。

   2. 水平拆分：指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。通常情况下，我们使用取模的方式来进行表的拆分，比如，一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，users3，users4，然后通过用户 ID 取模的方法，同时查询、更新、删除也是通过取模的方法来操作。

2. 读写分离：一般情况下对数据库而言都是“读多写少”，换言之，数据库的压力多数是因为大量的读取数据的操作造成的，我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以缓解对数据库的访问压力。

---

sql性能分析

explain执行计划

作用：获取mysql如何执行SELECT语句的信息，包括在执行过程中表如何连接和连接的顺序。

用法：在任意的查询语句前加上该关键字。

慢查询日志

慢查询日志记录了所有时间超过参数值的的sql语句，默认不开启，开始默认值为10s。

profiles详情

show profiles 查看一条sql的执行耗时。

show profile for query query_id 查看指定query_id的sql语句在各个阶段的耗时。

show profile cpu for query query_id 查看指定query_id的sql语句CPU的使用情况。

mysql中的日志

错误日志：记录发生任何严重错误时的相关信息。当出现故障时，先查它。

二进制日志：记录了所有DDL(数据定义语言)和DML(数据操作语言)，不包括查询。

       用于灾难恢复和主从一致。默认开启。

查询日志：用于记录所有客户端所有操作语句，默认不开启。

慢查询日志

如何排查慢SQL

慢 SQL 指的是 MySQL 中执行比较慢的 SQL，排查慢 SQL 最常用的方法是通过慢查询日志来查找慢 SQL。 MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过默认时间10S的 SQL，就会被记录到慢查询日志中，以上的语句就会被当做慢 SQL 记录到日志中。

查看慢 SQL 是否开启

可以使用 SQL 命令来查看慢 SQL 记录功能是否开启，使用 mysql> show variables like '%slow_query_log%'; 来查询慢查询日志是否开启，执行效果如下图所示： slow_query_log 的值为 OFF 时，表示未开启慢查询日志。

开启慢 SQL 记录

开启慢查询日志，可以使用如下 MySQL 命令：

mysql> set global slow_query_log=1

不过这种设置方式，只对当前数据库生效，如果 MySQL 重启也会失效，如果要永久生效，就必须修改 MySQL 的配置文件 my.cnf，配置如下：

slow_query_log =1

slow_query_log_file=/tmp/mysql_slow.log

分析慢 SQL

得到慢 SQL 之后，可以通过 explain 执行计划分析 MySQL 执行慢的原因并进行优化，比如以下这样： !其中最重要的就是 type 字段，type 值类型如下：

• all — 扫描全表数据
• index — 遍历索引
• range — 索引范围查找
• index_subquery — 在子查询中使用 ref
• unique_subquery — 在子查询中使用 eq_ref
• ref_or_null — 对 null 进行索引的优化的 ref
• fulltext — 使用全文索引
• ref — 使用非唯一索引查找数据
• eq_ref — 在 join 查询中使用主键或唯一索引关联
• const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。

如果 type=all 说明没走索引，此时就需要给查询慢的字段加上相应索引就可以提高查询效率。 当然，优化慢 SQL 需要综合考虑的因素有很多，比如索引、查询优化（减少联表查询等）、减少锁竞争等因素，所以具体的慢 SQL 优化，需要根据实际的业务场景再做优化决策。

约束

binlog

MySQL的binlog是二进制日志，用于记录MySQL数据库的所有修改操作，包括INSERT、UPDATE、DELETE等操作。它是MySQL的核心功能之一，可以用于数据恢复、数据备份、数据同步等操作。

binlog文件存储在MySQL服务器的数据目录下，文件名格式为“hostname-bin.index”和“hostname-bin.nnnnnn”，其中“hostname”表示主机名，“index”文件记录了所有binlog文件的信息，而“nnnnnn”文件则记录了具体的操作内容。

binlog的主要作用如下：

1. 数据恢复：当数据出现问题时，可以通过binlog文件来恢复数据，尤其是在出现误操作、数据丢失等情况下非常有用。

2. 数据备份：可以通过binlog文件来备份数据库，将binlog文件复制到另一台MySQL服务器上，以便进行数据同步或数据备份。

3. 数据同步：可以通过binlog文件来实现主从复制，将主服务器上的修改操作同步到从服务器上，以保证数据的一致性。

4. 数据审计：可以通过binlog文件来记录数据库的所有修改操作，以便进行数据审计和安全检查。

总之，binlog是MySQL数据库非常重要的一个组件，可以帮助我们更好地管理和保护数据。

分库分表

MySQL的分库分表是一种水平扩展的方式，用于解决单一数据库难以满足大规模应用的数据存储和查询需求的问题。它将一个大的数据库拆分成多个小的数据库或表，每个小的数据库或表只存储部分数据。这样可以提高数据的读写性能、减少单点故障、降低成本等。

下面分别介绍分库和分表的概念和实现方式：

1. 分库

分库是将一个大的数据库拆分成多个小的数据库，每个小的数据库只存储部分数据。分库的实现方式有垂直分库和水平分库两种：

• 垂直分库：按照业务模块或数据类型将数据分散到不同的数据库中，每个数据库只存储一部分数据，例如将用户信息、订单信息、商品信息分别存储到不同的数据库中。

• 水平分库：按照数据的某个维度将数据分散到不同的数据库中，每个数据库只存储一部分数据，例如按照用户ID、订单ID、地区等维度将数据分散到不同的数据库中。

分库的实现需要考虑数据的划分方式、数据的迁移、跨库事务的处理等问题。

2. 分表

分表是将一个大的数据表拆分成多个小的数据表，每个小的数据表只存储部分数据。分表的实现方式有水平分表和垂直分表两种：

• 水平分表：按照数据的某个维度将数据分散到不同的数据表中，每个数据表只存储一部分数据，例如按照用户ID、订单ID、时间等维度将数据分散到不同的数据表中。

• 垂直分表：按照数据的列进行拆分，将数据表中的一些列拆分到不同的数据表中，例如将用户信息中的基本信息和扩展信息分别存储到不同的数据表中。

分表的实现需要考虑数据的划分方式、查询的优化、数据迁移、跨表事务的处理等问题。

主从复制

MySQL的主从复制是一种数据同步的方式，用于将主服务器上的数据复制到从服务器上，以保证数据的一致性和高可用性。主从复制的基本原理是将主服务器上的修改操作记录到二进制日志（binlog）中，从服务器通过读取主服务器的binlog文件，将其中的修改操作复制到从服务器上，从而实现数据的同步。

主从复制的主要优点如下：

1. 提高数据的可用性：主从复制可以将主服务器上的数据复制到从服务器上，如果主服务器出现故障，从服务器可以立即接管服务，提高了数据库的可用性。

2. 分担主服务器压力：主从复制可以将读操作分担到从服务器上，减轻了主服务器的负担，提高了主服务器的性能。

3. 数据备份：从服务器可以作为数据备份，以便在主服务器出现故障时进行数据恢复。

主从复制的实现方式如下：

1. 配置主服务器：主服务器需要开启binlog功能，并配置binlog格式和binlog文件名等参数。

2. 配置从服务器：从服务器需要配置主服务器的IP地址、端口号、用户名和密码等参数。

3. 启动从服务器：从服务器启动时，将连接到主服务器，并从主服务器上读取binlog文件，将其中的修改操作复制到从服务器上。

需要注意的是，主从复制也存在一些问题和限制，例如主从延迟、单点故障、数据不一致等问题，需要进行合理的配置和管理，以保证数据的一致性和可用性。

总之，分库分表是一种常用的数据库水平扩展方式，可以提高数据的读写性能、减少单点故障、降低成本等，但也需要考虑数据的划分方式、数据迁移、跨库事务的处理等问题。

sql是怎样加锁的