MySQL 基础

MySQL 是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息。

优点：

成熟稳定，功能完善。

开源免费。

文档丰富，既有详细的官方文档，又有非常多优质文章可供参考学习。

开箱即用，操作简单，维护成本低。

兼容性好，支持常见的操作系统，支持多种开发语言。

社区活跃，生态完善。

事务支持优秀， InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失，并且，InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的。

支持分库分表、读写分离、高可用。

MySQL 字段类型

数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）

字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。

日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

MySQL 存储引擎

MySQL 当前默认的存储引擎是 InnoDB。并且，所有的存储引擎中只有 InnoDB 是事务性存储引擎。MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MyISAM 和 InnoDB 区别

InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。

MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。

MyISAM 不支持外键，而 InnoDB 支持。

MyISAM 不支持 MVCC，而 InnoDB 支持。虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持。

InnoDB 的性能比 MyISAM 更强大。

MySQL 事务（事务是逻辑上的一组操作，要么都执行，要么都不执行。）

关系型数据库（例如：MySQL、SQL Server、Oracle 等）事务都有 ACID 特性：

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用

一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；

隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务的问题

脏读（Dirty read）

丢失修改（Lost to modify）

不可重复读（Unrepeatable read）

幻读（Phantom read）

不可重复读和幻读区别？

不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；

幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

并发事务控制

MySQL 中并发事务的控制方式无非就两种：锁 和 MVCC。锁可以看作是悲观控制的模式，多版本并发控制（MVCC，Multiversion concurrency control）可以看作是乐观控制的模式。

锁 控制方式下会通过锁来显示控制共享资源而不是通过调度手段，MySQL 中主要是通过 读写锁 来实现并发控制。

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁（锁不兼容）。

读写锁可以做到读读并行，但是无法做到写读、写写并行。另外，根据根据锁粒度的不同，又被分为 表级锁(table-level locking) 和 行级锁(row-level locking) 。

MVCC 是多版本并发控制方法，即对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本。通常会有一个全局的版本分配器来为每一行数据设置版本号，版本号是唯一的。

MVCC 在 MySQL 中实现所依赖的手段主要是: 隐藏字段、read view、undo log。

undo log : undo log 用于记录某行数据的多个版本的数据。

read view 和 隐藏字段 : 用来判断当前版本数据的可见性。

SQL 标准定义了四个隔离级别：

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

MySQL 锁

锁是一种常见的并发事务的控制方式。

表级锁和行级锁对比：

表级锁： MySQL 中锁定粒度最大的一种锁（全局锁除外），是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。不过，触发锁冲突的概率最高，高并发下效率极低。表级锁和存储引擎无关，MyISAM 和 InnoDB 引擎都支持表级锁。

行级锁： MySQL 中锁定粒度最小的一种锁，是 针对索引字段加的锁 ，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。行级锁和存储引擎有关，是在存储引擎层面实现的。

注意事项：

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，MySQL InnoDB 支持三种行锁定方式：

记录锁（Record Lock）：也被称为记录锁，属于单个行记录上的锁。

间隙锁（Gap Lock）：锁定一个范围，不包括记录本身。

临键锁（Next-Key Lock）：Record Lock+Gap Lock，锁定一个范围，包含记录本身，主要目的是为了解决幻读问题（MySQL 事务部分提到过）。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

在 InnoDB 默认的隔离级别 REPEATABLE-READ 下，行锁默认使用的是 Next-Key Lock。但是，如果操作的索引是唯一索引或主键，InnoDB 会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。

排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

意向锁

如果需要用到表锁的话，如何判断表中的记录没有行锁呢，一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。

意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是相互兼容的。

当前读和快照读

快照读（一致性非锁定读）就是单纯的 SELECT 语句。

快照即记录的历史版本，每行记录可能存在多个历史版本（多版本技术）。

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读：

在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。

在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。

自增锁

关系型数据库设计表的时候，通常会有一列作为自增主键。InnoDB 中的自增主键会涉及一种比较特殊的表级锁— 自增锁（AUTO-INC Locks） 。

CREATE TABLE `sequence_id` (

`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,

`stub` CHAR(10) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

UNIQUE KEY `stub` (`stub`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

更准确点来说，不仅仅是自增主键，AUTO_INCREMENT的列都会涉及到自增锁，毕竟非主键也可以设置自增长。

MySQL 性能分析

使用 EXPLAIN 命令来分析 SQL 的 执行计划。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

执行计划通常用于 SQL 性能分析、优化等场景。通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

分析 EXPLAIN 结果

id

SELECT 标识符，是查询中 SELECT 的序号，用来标识整个查询中 SELELCT 语句的顺序。

id 如果相同，从上往下依次执行。id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。

select_type

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

SIMPLE：简单查询，不包含 UNION 或者子查询。

PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。

SUBQUERY：子查询中的第一个 SELECT。

UNION：在 UNION 语句中，UNION 之后出现的 SELECT。

DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。

UNION RESULT：UNION 查询的结果。

table

查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

<unionM,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果；

<derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。

<subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。

type（重要）

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。

const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。

eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。

ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。

index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。

range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。

index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。

ALL：全表扫描。# possible_keys

possible_keys

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

key（重要）

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

key_len

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

rows

rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要）

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。

Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。Using index condition：表示查询优化器选择使用了索引条件下推这个特性。

Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。

Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

SQL优化：

查询SQL尽量不要使用select *，而是select具体字段。

如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1

应尽量避免在where子句中使用or来连接条件

优化limit分页

优化你的like语句

使用where条件限定要查询的数据，避免返回多余的行

尽量避免在索引列上使用mysql的内置函数

应尽量避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫

Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则。

对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描。

如果插入数据过多，考虑批量插入。

在适当的时候，使用覆盖索引。

慎用distinct关键字

删除冗余和重复索引

如果数据量较大，优化你的修改/删除语句。

where子句中考虑使用默认值代替null。

不要有超过5个以上的表连接

exist & in的合理利用

尽量用 union all 替换 union

索引不宜太多，一般5个以内。

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。

尽量避免向客户端返回过多数据量。

当在SQL语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰。

尽可能使用varchar/nvarchar 代替 char/nchar。

为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。

如何字段类型是字符串，where时一定用引号括起来，否则索引失效

使用explain 分析你SQL的计划