一文搞懂mysql，欢迎纠正补充~

java干货仓库

已于 2024-02-21 22:35:17 修改

阅读量782

点赞数 20

分类专栏： mysql 文章标签： mysql 数据库 oracle 面试

于 2024-02-21 22:10:47 首次发布

本文链接：https://blog.csdn.net/qq_36929638/article/details/130244040

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

创作不易，如果觉得写的不错就投币支持一下吧~

一文搞懂mysql

一、mysql基础
二、索引
三、锁
四、MVCC

一、mysql基础

1.1 三大范式

第一范式（1NF）：确保每个列都具有原子性，不可再分。这意味着每个数据都只能单独出现，不能存在于一个数据组中。
第二范式（2NF）：确保每张表只描述一种数据实体，并且每个表中的信息都与主键相关。这意味着每个非主键列都直接依赖于主键，而不是相互依赖。
第三范式（3NF）：在满足第二范式的前提下，任何非主键列之间都不允许存在传递依赖关系。也就是说，如果一个非主键列只依赖于主键的某些列而不是整个主键组合，则需要将其拆分成独立的表。

1.2 SQL分类

数据定义语言DDL（Data Definition Language）CREATE，DROP，ALTER。包括操作表结构，视图和索引等。
数据查询语言DQL（Data Query Language）SELECT
数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE
数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK。对数据库安全性完整性等有操作的，包括权限控制等。

drop、delete与truncate的区别？

drop： 删除一张表
delete： 删除表数据，可以部分删
truncate： 保留表而删除所有数据

1.3 SQL关联

sql关联查询我们这里就讲两种：union和join

1.3.1 union和union all

UNION 和 UNION ALL 都是用于组合两个或多个 SELECT 语句结果集的操作符。

区别：

UNION 会自动去除结果集中的重复行。
UNION ALL 则不进行去重处理，保留所有行。UNION ALL查询效率高

示例：

-- UNION 会自动去除两个 SELECT 语句中的重复记录
SELECT column_name FROM table_name_1
UNION
SELECT column_name FROM table_name_2;

-- UNION ALL 不会去重，保留所有记录
SELECT column_name FROM table_name_1
UNION ALL
SELECT column_name FROM table_name_2;

1.3.2 join

JOIN 是一种用于连接两个或多个表中的记录的操作，常常用于在多个表中查询和过滤数据。

JOIN 可以分为以下几种：

INNER JOIN：内连接，也称等值连接，是指根据两个表中相同的字段值将它们连接起来。只返回满足连接条件的记录。
LEFT JOIN：左连接，是指以左侧表为主表，将它与右侧表中匹配的记录连接起来。如果右侧表中没有与左侧表匹配的记录，会返回 NULL 值。
RIGHT JOIN：右连接，是指以右侧表为主表，将它与左侧表中匹配的记录连接起来。如果左侧表中没有与右侧表匹配的记录，会返回 NULL 值。

1.4 Mysql逻辑架构

MySQL 的逻辑架构分为三层，包括：

连接层（Connection Layer）：负责连接处理、身份验证和安全等操作。
服务器层（Server Layer）：负责查询处理和分析、语法解析和优化等操作，同时包括了查询缓存和 SQL 接口等组件。
存储引擎层（Storage Engine Layer）：负责数据的存储和访问操作，支持多种存储引擎（如 InnoDB、MyISAM 等）。

1.5 sql执行流程

1.5.1 查询流程

sql执行流程

客户端请求连接数据库。连接池管理器从连接池中获取可用连接或创建新连接，将连接分配给客户端，最后客户端发送 SQL 查询语句。
MySQL 服务端接收到查询请求后，进入缓存模块。如果配置开启了缓存，并且缓存中有存储该查询的结果，直接返回结果给客户端，否则进入解析器模块。
解析器模块负责sql的词法解析和语法解析，会将 SQL 查询语句转换成内部查询表示形式，并将解析好的语句交给优化器。
进入优化器模块，对内部查询表示形式进行多个执行计划的生成和成本评估，选择成本最小的执行计划。
进入执行器模块，根据最终执行计划，向存储引擎模块发送数据请求，获取数据，进行过滤、排序、聚合等操作，并返回结果给客户端。
客户端接收到查询结果，关闭连接，将连接返回给连接池。

1.5.2 更新流程

更新的执行过程

这里存储引擎我们以innodb为例。

客户端向MySQL连接池获取连接，进行身份验证和权限检查后，建立连接。
客户端向 MySQL 服务器发送一个 UPDATE 的 SQL 语句。
MySQL 的解析器会对该语句进行语法检查和语义分析等操作，然后生成相应的解析树。
SQL 查询优化器会对解析树进行优化，选择一个最优的执行计划返回给执行器。
执行器会根据优化器生成的执行计划，调用存储引擎执行更新操作。具体过程是执行器通过存储引擎找到id=2这行数据，如果id=2这一行的数据页在内存中，直接返回给执行器；如果不在，则要从磁盘的数据页读入内存，再返回给执行器；
执行器拿到id=2这一行数据，将这行数据写入到undo log中，以便后面事务的回滚。把c的value值加1，产生一行新的数据，并将这行新的内存更新到内存中。
执行器开始写redo log，redo log的日志文件是托管在innodb中的，此时进入二阶段提交的prepare状态。
执行器写更新操作的binlog，binlog是所有存储引擎共有的，在服务器层；
执行器调用引擎的事务提交接口，引擎将刚刚写入的redo log改成commit提交状态，更新完成。

更新执行过程
为什么会需要二阶段提交？写一次redo log行不行？

如果redo log只写一次的话，存在以下两种情况：

先写binlog，再写redo log：当前事务提交后，写入binlog成功，之后主节点崩溃。在主节点重启后，由于没有写入redo log，因此不会恢复该条数据。而从节点依据binlog在本地回放后，会相对于主节点多出来一条数据，从而产生主从不一致。
先写redo log，再写binlog：当前事务提交后，写入redo log成功，之后主节点崩溃。在主节点重启后，主节点利用redo log进行恢复，就会相对于从节点多出来一条数据，造成主从数据不一致。

因此，只写一次redo log，无法保证主节点崩溃恢复与从节点本地回放数据的一致性。两阶段提交的主要用意是：为了保证redolog和binlog数据的安全一致性。只有在这两个日志文件逻辑上高度一致了。你才能放心的使用redolog帮你将数据库中的状态恢复成crash之前的状态，使用binlog实现数据备份、恢复、以及主从复制。而两阶段提交的机制可以保证这两个日志文件的逻辑是高度一致的。

1.6 日志

MySQL 的日志文件可以分为以下几类：

错误日志（error log）：记录 MySQL 服务器在启动、运行过程中发生的错误和警告信息，以及服务器停止或重启的时刻。
查询日志（general query log）：记录 MySQL 服务器接收到的所有查询请求，包括成功和失败的查询操作。开启查询日志会对 MySQL 的性能产生一定影响。
慢查询日志（slow query log）：记录执行时间超过阈值的查询操作，用于诊断和优化查询操作性能。
二进制日志（binary log）：记录 MySQL 服务器数据的所有修改操作，可以用于主从复制、数据恢复等应用场景。
中继日志（relay log）：用于主从复制架构中的从节点将主节点的二进制日志复制到本地的中转日志文件中。
InnoDB redo 日志：记录 InnoDB 存储引擎正在进行的事务操作，用于保证事务的原子性和持久性。
InnoDB undo 日志：用于回滚事务操作，保证事务的原子性和一致性。

这里主要讲一下redo log、undo log和binlog

redo log 用于记录事务对数据页所做的物理修改操作，以便在崩溃后能够将未写入磁盘的事务操作进行重做，保证事务的持久性和原子性。
undo log 记录了修改前的值，用于支持事务回滚和多版本并发控制，以保证事务的一致性。
binlog 记录了对数据库逻辑操作的记录，在主从复制、数据恢复等方面都有很大的作用。

这三种日志维护的位置不同：

redo log 由存储引擎层维护，在 InnoDB 存储引擎实现中，redo log 被存储在共享表空间的 ib_logfile* 文件中。
undo log 由存储引擎层维护，通常与系统表空间放在一起，mysql 系统变量 innodb_undo_tablespaces 可以配置将其放在其他表空间中。
binlog 由 MySQL 服务层维护，被存储为一个二进制文件，并通过复制机制同步到其他 MySQL 服务器中。

binlog和redo log的区别：

作用不同：redo log是用于crash recovery宕机恢复的，保证MySQL宕机也不会影响持久性；binlog是用于主从复制和数据的备份恢复的。
内容不同： binlog是逻辑日志，记录某个语句的基本逻辑，即SQL语句；redo log是物理日志，记录对某个数据页所做的修改；
层次不同：binlog是在MySQL的Server层实现，所有的存储引擎都可以使用binlog这个日志模块；redo log是InnoDB存储引擎特有的日志模块；
写入时机不同：binlog 是在事务最终提交前写入的；redo log是在事务执行过程不断的将DML语句写入缓冲区；
写入方式不同：binlog是追加写，在写满或重启之后，会生成新的binlog文件，之前的日志不会进行覆盖；redo log是循环写，空间大小是固定的；

1.7 存储引擎

1.7.1 MyISAM

MyISAM具有以下特点：

不支持事务处理：MyISAM 存储引擎不支持事务处理，这意味着如果发生宕机等异常情况，可能会导致数据丢失或者出现一些非预期结果。
不支持外键约束：MyISAM 存储引擎也不支持外键约束，无法保证数据之间的一致性和完整性，需要在应用程序中手动实现。
支持全文索引：MyISAM 存储引擎支持全文索引，可以对文章、日志等长文本内容进行搜索，提高查询效率。
适用于查询比较频繁的场景：MyISAM 存储引擎采用表级锁定，适用于查询比较频繁，更新比较少的场景，因为在并发写入的情况下，可能出现大量的锁等待，从而降低系统性能。
不支持行级锁定：MyISAM 存储引擎只支持表级锁定，不能对表中某一行进行锁定，这意味着并发操作时可能会引起锁争用问题。
不支持多版本并发控制（MVCC）：MyISAM 存储引擎不支持 MVCC，因此在并发读写场景下，可能会出现无法重现的读取错误。

1.7.2 InnoDB

支持事务处理：InnoDB 存储引擎支持事务处理，具有原子性、一致性、隔离性和持久性（ACID）特性，可以保证数据的完整性和一致性。
支持外键约束：InnoDB 存储引擎支持外键约束，可以保证数据之间的一致性和完整性，避免了在应用程序中手动实现外键限制的繁琐工作。
支持行级锁定：InnoDB 存储引擎支持行级锁定，可以提高并发操作的效率，在多用户并发读写场景下，可以减少因为锁等待而导致的性能瓶颈问题。
支持 MVCC：InnoDB 存储引擎支持多版本并发控制（MVCC），可以实现非阻塞读取，同时避免了脏读、不可重复读等问题。
支持缓冲池：InnoDB 存储引擎支持缓冲池，可以将磁盘上的数据缓存在内存中，提高查询效率。
支持自适应哈希索引：InnoDB 存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，会自动建立哈希索引以提升查询速度，故称为自适应哈希索引（adaptive hash index）。

1.8 事务

1.8.1 事务的特性（ACID）

MySQL 事务的特性分别是原子性（Atomicity）、一致性（Consistent）、隔离性（Isolation）和持久性（Durability）。这四个特性分别表示了事务处理过程中的四个方面，分别为：

原子性：一个事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。
一致性：事务执行前后，数据都必须保持一致状态，即数据的完整性、正确性和业务规则的一致性不能被破坏。
隔离性：多个事务并发执行时，每个事务都感觉不到其他事务的存在，各个事务之间是相互隔离的。
持久性：事务一旦提交，它对数据库中数据的改变就是永久性的，即使在系统故障的情况下也不会丢失。

1.8.2 事务的隔离级别

MySQL 数据库的事务隔离级别包括：读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。每个事务隔离级别都有其对应的问题：

读未提交：事务中的修改即使没有提交，也会立即影响到其它事务中的查询结果，存在脏读、不可重复读和幻读的问题。
读已提交：事务在提交之前所做的修改不会被其它事务看到，但是在同一事务内的两个查询操作可能会由于其它事务的修改操作而导致不可重复读的问题，幻读也有可能发生。
可重复读：保证在同一事务内对同一数据的多次读取结果一定相同，但是在同一事务内，其它事务可能会插入新的记录或者提交已有的记录，从而导致幻读问题。
串行化：最高的隔离级别，确保事务串行执行，但是并发程度较低，可能导致大量的锁等待和死锁的问题。

1.8.2 ACID实现原理

MySQL 事务的 ACID （原子性,一致性,隔离性和持久性）是通过多种机制实现的。下面是一些具体的实现细节：

原子性：MySQL 使用 redo log 和 undo log 机制实现原子性。redo log 记录事务所做的修改信息，当发生宕机等问题时，可以通过重放 redo log 将数据恢复到某一个状态，从而保证事务的原子性。undo log 记录事务的回滚信息，也可以用于数据恢复。
一致性：MySQL 通过锁机制、MVCC（多版本并发控制）等技术来实现一致性。锁机制可以保证并发访问时数据的正确性和完整性，MVCC 允许多个事务在不互相干扰的情况下同时对同一数据进行读操作。
隔离性：MySQL 支持四种隔离级别，包括读未提交、读已提交、可重复读和串行化。不同隔离级别通过锁机制、MVCC 及其他技术实现，从而保证事务之间的隔离性。
持久性：MySQL 定期将 redo log 中的数据刷入磁盘，从而保证事务的持久性。此外，MySQL 还支持数据备份、主从同步等机制，以保证数据不会因为硬件故障等原因丢失。

二、索引

MySQL 索引是一种数据结构，用于提高查询效率。它可以让数据库更快地找到匹配索引条件的记录，从而减少了数据库在硬盘上的扫描次数，提高了数据检索的速度。

2.1 索引的分类

普通索引：普通索引是最基本的索引类型，它允许在索引列中存在重复值。普通索引的主要作用是提高查询速度，但它不会对数据完整性施加任何限制。
唯一索引：唯一索引与普通索引类似，但它要求索引列的值是唯一的。这意味着在创建唯一索引时，如果有重复值会抛出错误。唯一索引可用于实现数据库表中某个列的唯一性约束，避免出现重复数据。
主键索引：主键索引是一种特殊的唯一索引，用于标识表中每一行记录的唯一性。如果没有显式地定义主键，InnoDB 存储引擎会选择一个不为NULL且唯一的列作为主键。主键索引可以提高查询的效率，同时也具有唯一性约束的作用。
组合索引：组合索引指的是由多个列共同组成的索引。组合索引可以优化多列条件查询的性能，但需要注意组合索引的列顺序也会影响查询性能，应根据实际情况进行调整。[3]
全文索引：全文索引是对文本内容进行分词索引，支持全文搜索。全文索引的主要作用是提高匹配查询的效率，可以用于实现网站搜索等功能。MySQL 中只有 MyISAM 和 InnoDB 存储引擎支持全文索引。
空间索引：是一种专门用于处理空间数据（如点、线、面等）的索引方式。MySQL 5.7 版本及以上提供了对空间数据类型的支持，空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon。

2.2 索引的数据结构

2.2.1 Hash

使用 Hash 表存储数据，Key 存储索引列，Value 存储行记录或行磁盘地址。Hash 只支持等值查询（“=”，“IN”，“<=>”），不支持任何范围查询（原因在于 Hash 的每个键之间没有任何的联系），Hash 的查询效率很高，时间复杂度为 O(1)。

2.2.2 B树

B树属于多叉树，又名多路平衡查找树。索引使用平衡树来对索引列进行排序，每个节点包含若干个指向子节点的指针。B-Tree 索引适用于单列或多列索引，支持范围查询和排序等操作。

它具有以下特点：

每个节点可以有多个子节点。节点中既包含数据项也包含子节点指针。
B树的查找效率比较高，因为在每个节点中都包含了一些实际数据。
B树叶子节点中保存的数据项可以直接被查找到，因此对于范围查询来说不需要遍历整棵树。
删除和插入操作比较复杂，因为需要对节点进行分裂合并等操作。

B树的数据结构
查询过程，例如：Select * from table where id = 6;
B树的查找过程
BTree 的不足：

不支持范围查询的快速查找（每次查询都得从根节点重新进行遍历）
节点都存储数据会导致磁盘数据存储比较分散，查询效率有所降低

2.2.3 B+树

在 B树的基本上，对 BTree 进行了优化：只有叶子节点才会存储键值 - 数据，非叶子节点只存储键值和子节点的地址；叶子节点之间使用双向指针进行连接，形成一个双向有序链表。

它具有以下特点：

每个节点可以有多个子节点。非叶子节点中仅包含关键字和子节点指针，而叶子节点中却包含了所有数据项。
B+树的查找性能优于B树，因为非叶子节点中只包含了关键字和指针，可以在单次磁盘访问中读取更多的关键字和指针。
B+树的范围查询效率比B树更高，因为所有数据项都保存在叶子节点中，并且叶子节点之间用双向指针链接，所有叶子节点可以看成是一个有顺序的链表，支持快速的进行顺序遍历。
删除和插入操作简单，因为不需要涉及实际数据的移动。

B+树的数据结构
等值查询，例如：Select * from table where id = 8;
B+数的查询过程
范围查询，例如：Select * from table where id between 8 and 22;
B+树范围查询
B+Tree 的优点：

保证了等值查询和范围查询的快速查找
单一节点存储更多的元素，减少了查询的 IO 次数

2.2.4 为什么不使用二叉树和红黑树？会有什么问题？

使用二叉树的主要问题是树的高度过高，导致每次查询需要进行大量的磁盘I/O操作，效率较低。当数据的分布不均时，二叉树的高度将接近于n，查询效率将急剧下降。
使用红黑树的主要问题在于树的节点不是按顺序存储的，每个节点中只包含一个数据项。这就意味着，如果要实现范围查找，则必须遍历整棵红黑树，这将导致查询性能的下降。此外，在插入或删除节点时，由于要维护平衡性并进行旋转操作，因此会增加时间和空间的复杂度。

2.3 MyISAM索引

MyISAM 的数据文件（.myd）和索引文件（.myi）是分开存储的，这种数据和索引分开存储的，我们称之为非聚簇索引。
MyISAM（B+Tree）叶子节点中存储的是指向数据文件中实际数据行的指针。
MyISAM 的主键索引（Primary key）和辅助索引（Secondary key）在结构上没有任何区别，只是主键索引要求键值唯一，而辅助索引键值可以重复。

MyISAM索引结构

2.4 InnoDB索引

数据和索引都存储在一个文件中（.ibd）。
一般情况下，聚簇索引等同于主键索引；除聚簇索引外的所有索引均称为辅助索引，也被成为二级索引。
InnoDB（B+Tree）叶子节点中存储的键值为索引列的值。如果是聚簇索引，数据为整行记录（除了主键值）。如果是辅助索引，数据为该行的主键值。
每一张表都有一个聚簇索引

如果表中有定义主键，主键索引用作聚簇索引
如果表中没有定义主键，选择第一个不为 NULL 的唯一索引列用作聚簇索引
如果以上都没有，使用一个 6 字节长整形的隐式字段 ROWID （自增）用作聚簇索引

根据在辅助索引树中获取的主键id，再到主键索引树查询数据的过程，称为回表查询

2.5 聚簇索引

聚簇索引是将数据存储与索引放到了一块，找到索引也就找到了数据。换句话说，聚簇索引的叶子节点就是存储这行数据的页。在聚簇索引中，每个表只能有一个聚簇索引，InnoDB的主键索引就是聚簇索引。

主键索引结构

2.6 非聚簇索引

非聚簇索引则是将数据和索引分开存储，索引结构的叶子节点存储了指向实际存储数据数据行的指针。该索引方式下，叶子节点不再是数据页，而是指向存储数据的位置。一个表可以有多个非聚簇索引。根据上面的特性，我们可以知道MyISAM的主键索引和辅助索引都是非聚簇索引。

非聚簇索引

2.7 辅助索引

辅助索引（也称为二级索引）和非聚簇索引类似，其叶子节点中存储的不是数据行，而是主键（或唯一键），通过辅助索引查找到主键值后，再通过主键值去聚簇索引中查询到数据行，这个过程我们称之为回表查询。辅助索引可以提高查询效率，并允许在没有聚簇索引的情况下进行查询。同样，一个表也可以拥有多个辅助索引。

辅助索引结构

2.8 组合索引

用户表
以上面这张表为例，将(age,height,weight)三个字段组合成索引。在建索引的过程中，构建B+树的时候，会先对第一列age进行排序，如果第一列age的值相同，则再会对第二列进行排序，如果第二列相同，再对第三列进行排序。具体结构如下图：
组合索引数据结构
我们可以看到，其实组合索引构建的B+树中只有第一列是有序的，第二列和第三列其实是无序的。所以在使用组合索引进行查询的时候，如果在第二列进行了范围查询（大于或小于等），将会导致后面的列无法参与到组合索引的查询中。
所以组合索引具有以下几个特点：

遵循最左匹配（最左前缀）原则：

使用组合索引查询时，MySQL 会一直向右匹配直至遇到范围查询（>、<、between、like）就停止匹配。
只有第一列是有序的，其它列都是无序的（相对于整颗B+树来说）
在使用组合索引时，查询条件中必须包含索引中的第一个字段，否则索引无法起到作用，查询会退化成全表扫描。

2.9 覆盖索引

覆盖索引不是一种索引结构，而是一种优化手段。我们只需要查询组合索引中的字段，而不需要表中的其它字段，在这过程中不会产生回表现象，这种情况称为覆盖索引。

2.10 索引下推

在开始之前先先准备一张用户表(user)，其中主要几个字段有：id、name、age、address。建立联合索引（name，age）。
假设有一个需求，要求匹配姓名第一个为陈的所有用户，sql语句如下：

SELECT * from user where  name like '陈%' and age=20

根据上面组合索引介绍的最左匹配原则，那么用了like右%的写法进行了范围查询，会导致age不能参与到组合索引的查询中，就会让匹配到“陈”开头的数据全部进行回表查询数据。回表过程如图所示：
组合索引回表

在MySQL 5.6之后，引入了索引下推的概念。原先没有索引下推之前，当某一列进行了范围查询，会导致组合索引后面的列无法进行匹配，此时会让前面匹配成功的所有列去回表查询。引入索引下推之后，为了减少回表的次数，并不会忽略无法匹配的组合索引字段，而是会在索引内部继续对后面的索引进行匹配，如果没有匹配成功，则会跳过，不进行会表查询。具体过程如下图所示：

索引下推回表

2.11 选择性

选择性指的是索引列包含不同值的数量与行数的比值，即count(distinct(column)) / count(1)。它可以用来表示该索引对于查询的优化程度，即该索引是否可以有效地减少查询的数据量。选择性的范围在 0~1 之间，值越大表示索引的选择性越高，对于查询的优化作用越明显。

索引的选择性受到许多因素的影响，包括索引列中有多少个唯一值、数据表中总行数、数据分布的均匀性等。一般来说，如果一个索引的选择性低于 0.1，那么它可能就无法有效地减少查询的数据量，因此对于优化查询没有太大的作用。而如果一个索引的选择性高于 0.5，那么它就会具有很好的优化效果。

需要说明的是，虽然选择性与索引的优化作用有很大关系，但并不是决定是否需要创建索引的唯一考虑因素。在实际应用中，还需要综合考虑查询的复杂度、数据表的大小、并发性等因素，才能选择出最合适的索引策略。

2.12 SQL优化

避免使用select *。select * 会增加很多不必要的消耗（CPU、IO、内存、网络带宽），增加了使用覆盖索引的可能性。
当只需要一条数据的时候，使用limit 1。这是为了使EXPLAIN中type列达到const类型。
尽量在where和order by条件字段上添加索引。如果排序字段不是索引，就尽量少排序。
如果or两边的字段，有字段不是索引字段，那会导致索引字段不走索引。可以将sql拆成两个查询，然后用union all或union拼接
尽量用union all代替union。union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。
使用in时，尽量用大表驱动小表（in里面的子查询称为小表）。使用exist则尽量使用小表驱动大表（exist里面的子查询称为大表）。如果两个表的数量相当，则in和exist的性能差不多。尽量不适用not in，会导致内外表都无法使用索引，而not exist的子查询可以使用索引。所以使用not exist的性能都会比not in性能好。
不建议使用%前缀模糊查询。因为这样毁导致索引字段失效，如果想要使用左百分号进行模糊查询，可以在需要模糊查询的字段建全文索引。
尽量使用inner join而不是left join。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

索引失效场景：

对索引列进行运算或函数运算
在索引列上使用 LIKE ‘%xxx%’ 左百分号进行模糊查询
在联合索引中不使用联合索引中最左边字段进行查询
对索引列进行类型转换（隐式转换）。原因是sql传入的数据类型和数据库类型不一致，数据库会自动进行隐式转换，导致索引失效
在查询条件中使用 OR 操作符，并且or两边的字段中，有一个字段不是索引字段
在查询条件中使用非等值判断（>、<、!=等），可能会导致索引失效
查询条件中使用 IN（）作为范围查询，而 IN（）子句中的元素个数过多
查询条件中使用了 NOT IN 和 NOT EXISTS，会导致索引失效。
在字段被设置为字符集不同时，字符集转换会导致无法使用索引。
在查询条件中，索引列有计算。如select * from user where id+1=2。

三、锁

锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能，实现了锁机制，MySQL中的锁是在服务器层或者存储引擎层实现的。

3.1 锁用来解决什么问题

锁是用来解决并发事务的访问问题，我们知道事务并发执行时可能带来的各种问题，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据，尤其是一个事务进行读取操作，另一个同时进行改动操作的情况下，这种情况下可能发生脏读、不可重复读、幻读的问题。

怎么解决脏读、不可重复读、幻读这些问题呢？Mysql采用了两种解决方案：

方案一：读操作MVCC，写操作进行加锁
该方案性能较好，但可能会读到旧版本记录

方案二：读写操作都加锁
该方案性能一般，但是每次都可以读取到最新的记录，比如在银行场景中，对安全性要求非常高

3.2 锁的分类

MySQL 中锁有很多，按照模式、粒度等可以分为如下几种类型
锁的分类

3.2.1 乐观锁

乐观锁不会在操作之前对资源进行加锁，而是在进行操作提交更新时，才会检查数据是否被修改，如果被修改了则回滚。

实现：乐观锁实现方式一般是在数据库添加version字段或者使用时间戳，在跟新数据之前，把需要修改的数据的version读到内存，然后在更新的时候，将读取出来的version的值和数据库的version的值比对，如果一致则更新，不一致则不更新。

使用场景：乐观锁使用与读多写少的场景，因为写多的场景，version字段很容易被修改，并发高的话，写失败的频率会很高。

3.2.2 悲观锁

在进行数据操作之前，悲观锁会先将数据所在的资源进行加锁，确保其他事务无法对资源进行修改，直到当前事务完成操作后释放锁。

实现：悲观锁主要是通过共享锁和排他锁实现，这两种锁下面会讲到。
使用场景：悲观锁使用与写多读少且并发量不大的场景

3.2.3 共享锁

共享锁（Shared Lock），也称为读锁，是一种并发控制机制，用于保证并发读取操作的数据一致性。在MySQL中，当进行SELECT等只读操作时会自动加上共享锁，允许多个并发事务对同一个资源加上共享锁，以保证数据一致性。

具体来说，如果一个事务对某个资源加上了共享锁，则其他事务也可以对该资源进行读取操作，但不能对其进行写入操作，即无法获取排它锁（Exclusive Lock）。只有当所有共享锁都被释放后，其他事务才能获取排它锁，并进行写入操作。

**共享锁可以通过在SQL语句中使用LOCK IN SHARE MODE或SELECT … FOR SHARE语句来实现。**需要注意的是，如果在数据库中使用了高并发读取操作，过多地使用共享锁可能会导致性能问题，因此需要合理地设置和调整锁粒度，以减少锁冲突的可能性。

3.2.3 排他锁

MySQL中的排他锁（Exclusive Lock），又称为写锁、独占锁，是一种并发控制机制。顾名思义，获取了排他锁的事务就成为了对资源的唯一拥有者，其他事务无法读取或修改该资源，直到该事务释放了排他锁。

在MySQL中，如果一个事务对某个资源加上了排他锁，则其他事务不能对该资源进行读取或写入操作，直到该事务释放了排他锁。而且，如果一个事务获取了一个数据行的排它锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。

排他锁可以通过在SQL语句中使用FOR UPDATE或SELECT … FOR UPDATE语句来实现。需要注意的是，如果在数据库中使用了高并发写入操作，过多地使用排他锁可能会导致锁冲突和性能问题，因此需要合理地设置和调整锁粒度，以减少锁冲突的可能性。

3.2.4 全局锁

全局锁，从名称上可以理解，全局锁就是对整个 MySQL 数据库实例加锁，加锁期间，对数据库的任何增删改操作都无法执行。

实现：MySQL 提供了一个加全局读锁的方法，命令是Flush tables with read lock (FTWRL)
使用场景：全库数据备份，可以使用全局锁，其他情况不要使用

3.2.5 表锁

表锁是一种粒度相对较大的锁机制，当事务需要对整张表进行修改时，会对该表进行加锁。这种锁机制主要适用于低并发的场景，因为表锁机制会影响到整个表的读写操作，可能会导致性能问题。MyISAM 与 InnoDB 引擎都支持表锁。

由于表锁的粒度较大，因此使用不当可能会带来性能问题或锁冲突。例如，在高并发情况下，若多个事务同时对同一个表进行读写操作，则容易产生锁等待和死锁的问题，导致系统性能下降。

实现：lock table read/write

3.2.6 页锁

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。

3.2.7 行锁

行级锁是 MySQL 粒度最细的锁，发生锁冲突概率最低，但是加锁慢，开销大。MySQL 中只有 InnoDB 引擎支持行锁，其他不支持。

实现：如果sql语句中使用到了索引，那么会自动加上行锁。否则将会变成表锁。

3.2.8 意向锁

InnoDB支持多粒度锁（multiple granularity locking），允许在表级别与行级别之间进行锁定，提高并发性能。而意向锁（Intention Locks）是其中一种表级别的锁。它是为了实现在锁住一个数据对象的行级锁前，进行预告和协调作用的锁定机制。意向锁有两种类型：IS 和 IX。