mysql高级

1.MySQL 事务

何为事务？ 一言蔽之，事务是逻辑上的一组操作，要么都执行，要么都不执行。

大多数情况下，我们在谈论事务的时候，如果没有特指分布式事务，往往指的就是数据库事务。

1.1事务的四大特性

• 原子性（Atomicity） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
• 一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
• 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
• 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

A向B转账500，转账成功，A扣除500元，B增加500元，原子操作体现在要么都成功，要么都失败

在转账的过程中，数据要一致，A扣除了500，B必须增加500

在转账的过程中，隔离性体现在A像B转账，不能受其他事务干扰

在转账的过程中，持久性体现在事务提交后，要把数据持久化

1.2并发事务问题

脏读（Dirty read）：一个事务读取到另外一个事务还没有提交的数据。

不可重复读（Unrepeatable read）：一个事务内多次读同一数据，但两次读取的数据不同。称之为不可重复读。在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。

幻读（Phantom read）：一个事务读取了几行数据，接着另一个并发事务插入了一些数据时。在随后的查询中，第一个事务就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

通过对事务进行隔离来解决这些问题

1.3事务隔离级别

• READ-UNCOMMITTED(读取未提交) ： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交) ： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读) ： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化) ： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。但性能比较低。所以，我们一般使用的都是mysql默认的隔离级别:可重复读

---

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ（mysql默认）	×	×	√
SERIALIZABLE	×	×	×

---

1.4MySQL 事务的隔离级别是如何保证的 （MVCC和锁）

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。不同的隔离级别对锁的使用是不同的。

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

• select语句均是快照读（MVCC解决，不加锁）
• delete / update / select ...for update 等语句是加锁实现的

什么是MVCC?

其中mvcc的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

什么是当前读和快照读？

• 当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

• 快照读

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

MVCC的实现原理

它的底层实现主要是分为了三个部分隐藏字段、Read View、undo log（回滚日志）。

他的内部实现其实就是当快照读某条数据时，通过隐藏字段trx_id(事务id)获取当前最新数据的事务id，根据ReadView内部定义的一些匹配规则来判断数据的可见性，如果不可见，就通过另外一个隐藏字段roll_pointer(回滚指针)，它指向了该行的 undo log,找到undo log版本链中最近的历史记录来继续判断数据可见性，该访问哪一个版本。如果还无法看到，就会一直遍历undo log版本链，直到找到满足可见性的历史版本，即为结果。

然后在不同的隔离级别下，mvcc也是有差异的，如果是rc(读取已提交)隔离级别，每一次执行快照读时都生成一个ReadView，导致同一事务中两次读取数据不一致，就会有不可重复读问题。如果是rr（可重复读）隔离级别仅在事务中第一次执行快照读时生成ReadView，后续复用。

也正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同

2.MySQL三大日志

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。

其中，比较重要的还要属二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。

1.redo log（重做日志）和undo log （回滚日志）

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

思考一个问题：只要每次把修改后的数据页直接刷盘不就好了，还有 redo log 什么事？

数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，没必要把完整的数据页刷盘，所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

redo log是InnoDB存储引擎独有的，记录的是数据页的物理变化，服务宕机可用来同步数据，保证数据的持久性。

而undo log主要记录的是所有事务的修改，如果执行过程中遇到异常的话，我们可以利用 undo log中的信息将数据回滚恢复原来的数据。

并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

总结：

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

2.binlog

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，DDL语句和DML语句，但不包括数据查询语句，不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志.

binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

3.MySQL主从复制原理

主从复制的核心其实是二进制日志binlog

他其实主要有三个步骤：

• 主库在提交事务的同时，把数据的更改记录写入二进制日志binlog中。
• 从库读取主库的二进制文件binlog，写入从库的中继日志中。
• 从库读取并重做中继日志中的SQL语句，生成自己的数据

3.读写分离和分库分表

什么是读写分离？

读写分离主要是为了将对数据库的读写操作分散到不同的数据库节点上。 这样的话，就能够小幅提升写性能，大幅提升读性能

如何实现读写分离？

• 部署多台数据库，选择其中的一台作为主数据库，其他的一台或者多台作为从数据库。
• 保证主数据库和从数据库之间的数据是实时同步的，这个过程也就是我们常说的主从复制。
• 系统将写请求交给主数据库处理，读请求交给从数据库处理。

什么是分库？

分库 就是将数据库中的数据分散到不同的数据库上，可以垂直分库，也可以水平分库。

垂直分库 就是把单一数据库按照业务进行划分，不同的业务使用不同的数据库，进而将一个数据库的压力分担到多个数据库。

使用最多的就是垂直分库，因为我们都是微服务开发，每个微服务对应了一个数据库，是根据业务进行拆分的，这个其实就是垂直拆分。

水平分库 是把同一个表按一定规则拆分到不同的数据库中，每个库可以位于不同的服务器上。解决了单表的存储和性能瓶颈的问题。

当单表存储了非常多的数据，就可以考虑水平分库

什么是分表？

分表 就是对单表的数据进行拆分，可以是垂直拆分，也可以是水平拆分。

垂直分表 是对数据表列的拆分，把一张列比较多的表拆分为多张表。

举个例子：我们可以将用户表中的一些列单独抽出来作为一个表。将用户基本信息放着用户信息表，用户描述信息放着用户描述表。

水平分表 是对数据表行的拆分，把一张行比较多的表拆分为多张表，可以解决单一表数据量过大的问题。（还是在同一个库中，所以库级别的数据库（主机）操作还是有IO瓶颈）

什么情况下需要分库分表？

垂直方向（即业务方向）很简单

在水平方向（即数据方向）上，分库和分表的作用，其实是有区别的，不能混为一谈。

分库：是为了解决数据库连接资源不足问题，和磁盘IO的性能瓶颈问题。

分表：是为了解决单表数据量太大，sql语句查询数据时，即使走了索引也非常耗时问题。此外还可以解决消耗cpu资源问题。

分库分表：可以解决 数据库连接资源不足、磁盘IO的性能瓶颈、检索数据耗时 和 消耗cpu资源等问题。

如果在有些业务场景中，用户并发量很大，但是需要保存的数据量很少，这时可以只分库，不分表。

如果在有些业务场景中，用户并发量不大，但是需要保存的数量很多，这时可以只分表，不分库。

如果在有些业务场景中，用户并发量大，并且需要保存的数量也很多时，可以分库分表。
 

实际运用

比如说某个电商平台的订单表，他每天有20w左右的订单，单表数据量到了百万级，这时候就可以考虑水平分表，可以将统计表拆分成二张表，每张表的表结构是一模一样的，只是存储的数据不一样。如果以后用户数据量越来越大，只需再多分几张用户表即可。

路由策略的话，鉴于订单表特性，无论是按照id、时间进行分表均不合适，应该保证每个人的订单在同一张表里，所以应该按照客户id(customer_id)进行分表

具体做法：可以采用mycat来作为数据库的中间件，按照客户id取模平均分配到两张表里，这样就可以保证同一个人的订单分配在同一张表中，可以解决单表数据量太大的问题，减轻数据库的读写压力，解决性能的问题。

4.MySQL存储引擎

mysql默认存储引擎

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MySQL 存储引擎架构

MySQL 存储引擎采用的是 插件式架构 ，支持多种存储引擎。存储引擎是基于表的，而不是数据库

MyISAM 和 InnoDB 有什么区别？

1.是否支持事务

MyISAM 不提供事务支持，InnoDB 提供事务支持。

2.锁机制

MyISAM只有表级锁，而 InnoDB 支持行级锁和表级锁,默认为行级锁。

3.是否支持外键

MyISAM 不支持，而 InnoDB 支持。

4.性能有差别

5.索引实现不一样。

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

在MyISAM中，B+树索引都是非聚簇索引，而InnoDB引擎的主键索引是聚簇索引

6.是否支持数据库异常崩溃后的安全恢复

MyISAM 不支持，崩溃后通常需要较长时间的恢复，容易出现数据损坏。

而 InnoDB 支持。使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

5.索引

索引的概念

索引是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库的IO成本，同时通过索引列对数据进行排序，降低数据排序的成本，也能降低了CPU的消耗。

索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度， 因为在执行这些写操作时，还要操作索引文件。

索引的底层数据结构

索引底层数据结构有Hash表、二叉查找树、红黑树、B树和B+树

MySQL的默认的存储引擎InnoDB采用的B+树的数据结构来存储索引

选择B+树的主要的原因是：磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据，并且B+树查询效率更稳定，便于区间查询，叶子节点是一个双向链表

B树和B+树的区别是什么

• B树中，非叶子节点和叶子节点都会存放数据，而B+树的所有数据都在叶子节点，在查询的时候，B+树查找效率更加稳定。（为什么B+稳定？B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。）
• B 树的叶子节点都是独立的;B+树的叶子节点是一个双向链表。所以在进行范围查询的时候，B+树效率更高

什么是聚簇索引什么是非聚簇索引

聚簇索引主要是指数据与索引放到一块，InnoDB 中的主键索引就属于聚簇索引。B+树叶子节点保存了整行的数据。

非聚簇索引指的是数据与索引分开存储，二级索引(辅助索引)就属于非聚簇索引，B+树的叶子节点保存对应的主键，可以有多个，一般我们自己定义的索引都是非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

回表查询

跟聚簇索引和非聚簇索引是有关系的,回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。

非聚簇索引不一定回表查询。

比如用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

所以如果按照二级索引查询数据的时候，返回的列中包含没有创建索引的字段，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都是添加索引的字段。

或者直接走主键索引，避免回表查询。

覆盖索引

覆盖索引就是需要查询的字段正好是索引的字段，在索引中全部都能找到，直接返回数据，不用回表查询，效率高。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。

MYSQL超大分页怎么处理

超大分页一般在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，效率就很低。

可以采用覆盖索引和子查询来解决：例如可以先分页查询数据的id字段，再用子查询来过滤，只查询这个id列表中的数据就可以了。因为查询id的时候，走的覆盖索引，所以效率可以提升很多。

联合索引

使用表中的多个字段创建索引，就是 联合索引

最左前缀匹配原则

在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 >、<）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

索引下推

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

索引的创建原则

首先创建索引的前提是数据量比较大的时候，大概单表超过十万数据就会创建索引。

我们要尽量选择查询、排序比较频繁的字段来创建索引。

还有就是尽量创建联合索引，因为可以更好的使用覆盖索引，避免回表查询。

注意避免冗余索引

当然并不是所有的字段都要添加索引，被频繁更新的字段应该慎重建立索引，要控制索引的数量。单表不超过5个。因为太多的话，维护成本很高，会降低插入和更新的效率。

索引不适合建在有大量重复数据的字段上

字符串类型的字段使用前缀索引代替普通索引

索引失效的情况

索引失效的情况有很多，比如说像我之前项目中遇到的一个联合索引失效问题，我创建了一个联合索引，里面有3个字段，但是查询条件没有遵守最左匹配原则，就是没有使用最左边的索引字段，只使用了后面的字段，导致索引失效。我通过使用 EXPLAIN分析sql执行计划，发现key为null，并未走索引来查询。所以我们使用联合索引要遵守最左匹配。

其他常见索引失效情况还有在索引列上进行运算、函数、类型转换等操作。

尽量避免在where子句上使用!=，<>操作符

如果字段有默认值，不允许为空，这时查询条件再用where age IS NOT NULL 就会索引失效

以%开头的Like模糊查询，还有使用select * 也有可能带来一些其他的性能问题，无法使用索引覆盖。

查询条件中使用 or，且 or 的前后条件中有一个列没有索引，没有索引的查询条件就得全表扫描。处于效率与成本考虑，遇到or条件，索引还是可能失效的。

6.MySQL 锁

首先不论是表级锁还是行级锁，通常都可以使用共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类，所以先介绍一下共享锁和排他锁。

MySQL数据库中的锁分为共享锁(Shared Lock)和排它锁(Exclusive Lock)两种。

这两种锁类型的作用是控制对数据的并发访问以维护数据的一致性和完整性。

• 共享锁：也被称为读锁，允许多个事务同时读取同一份数据，但不允许任何事物对数据进行修改，直到所有共享锁都被释放。
• 排他锁：也被称为写锁，只允许一个事务对数据进行修改，其他事务无法读取或修改数据，直到排他锁被释放。

MySQL支持表级锁行级锁两种粒度的锁。

• 行级锁：最细粒度的锁，是针对单个数据行的锁，只锁定需要修改的数据行，避免其他事务对该数据进行操作。行级锁的优点是粒度小，发生冲突概率低，可以提高并发性能；缺点是实现复杂，需要维护锁定的数据行和事务的状态，容易出现死锁。InnoDB支持行锁（必须有索引才能实现，否则会自动锁全表，变成表级锁）
• 表级锁：是对整张表的锁，可以锁定整个表，避免其他事务对该表进行任何操作。表级锁的优点是简单，系统开销小，适用于对表进行全局性操作；缺点是粒度太大，会导致并发性能下降。MyISAM使用表级锁。

总结：无论是表级锁还是行级锁，都可以使用共享锁和排他锁来实现不同程度的并发控制。表级锁更适合对整个表进行操作的场景，而行级锁更适合需要高度并发读写的场景。共享锁允许多个事务同时读取数据，而排他锁防止其他事务同时读取或写入数据，确保数据的完整性和一致性。

7.定位慢查询，分析SQL执行计划

如何定位慢查询

通过MySQL中提供的慢日志查询的功能，开发环境中可以在MySQL的系统配置文件中开启这个慢日志的功能，找到执行慢的SQL。

如何分析执行慢的SQL语句

通常会使用mysql的explain命令去查看这条sql的执行计划，比如可以通过key和key_len检查是否命中了索引，还可以通过type字段查看sql是否有进一步的优化空间，另外可以通过extra建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。

SQL优化手段

SQL优化在项目里还是比较常见的，我会从这几个方面考虑，比如表的优化、索引的优化、SQL语句的优化还有主从复制，读写分离，另外如果量比较大的话，可以考虑分库分表。

• 就像创建表的时候，我会参考阿里的开发手册，就比如，在定义字段的时候需要结合字段的内容来选择合适的类型，尽量使用数值替代字符串类型，比如说性别用0和1来表示男女，优先选择符合条件的最小的数据类型，mysql推荐使用tinyint。还有就是可以使用varchar代替char，因为varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间。
• 首先要善用EXPLAIN查看SQL执行计划，看他是否使用了索引。索引的优化就是首先根据索引的创建原则选择合适的字段来创建索引（被频繁查询的字段/被作为条件查询的字段等），然后正确使用索引避免索引失效（比如尽量不要在列上进行运算避免在索引列上进行运算、函数、类型转换等操作）。避免冗余索引，删除长期未使用的索引等等。
• 关于SQL语句的优化有这样几条原则，首先我们在开发环境开启慢查询日志，找到执行慢SQL优化。找到先运行一下，看看是否真的很慢，再使用explain命令查看SQL执行计划，分析语句，没走索引，可以创建索引或者优化语句，使它走索引；不要直接使用select *，尽量写全字段名。因为select * 要返回的数据行太多，可能无法走覆盖索引，mysql考虑性能和成本等因素很可能不会走索引，就会直接全表扫描；聚合查询尽量用union all代替union ，union会多一次过滤去重，效率比较低；连接查询的话，尽量小表驱动大表，即小表join大表。比如尽量使用内连接 ，不要使用外连接，因为内连接会对两个表优化，把小表放在外边，大表放在里边，小表驱动大表从而让性能更优，而left join right join不会调整表关联顺序；对于经常使用的查询，可以开启缓存；用连接查询代替子查询；避免使用or做连接条件，可能会使索引失效，从而全表扫描，可以使用union all 代替；数据量大时多使用limit。
• 关于主从复制、读写分离，如果数据库读的操作比较多，为了避免写的操作影响读操作，我们可以使用读写分离的架构。主库进行写的操作，从库进行读的操作，写完就把数据同步到从库。读写分离可以解决数据库的写入造成的性能影响。

高级SQL优化

批量插入性能提升(能用batch insert就不要用单个的insert)

批量提交：

INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2');

理由：

• 默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升
• 数据量小体现不出来
• 但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。

批量删除优化

避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。

理由：

• 一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作

小表驱动大表（*）

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。

假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。

这时如果想查一下，所有有效的用户下过的订单列表。

可以使用in关键字实现：

select 字段,字段,字段,字段 from order where user_id in (select id from user where status=1)
也可以使用exists关键字实现：

select  字段,字段,字段,字段 字段,字段,字段,字段 字段,字段,字段,字段 from order 
where exists
 (select 1 from user where order.user_id = user.id and status=1)
前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。

为什么呢？

因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。

将id进行分页子查询，得到的id集合为子查询条件，再去查其他字段，用的就是这个道理。

而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。

这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。

总结一下：

in 适用于左边大表，右边小表。

exists 适用于左边小表，右边大表。

不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。

包括能用内连接就不用外连接的道理也是小表驱动大表
 

多用limit

有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间。

反例：

select id, create_date   from order  where user_id=123  order by create_date asc;
根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合。 然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。

List<Order> list = orderMapper.getOrderList();
 Order order = list.get(0);
虽说这种做法在功能上没有问题，但它的效率非常不高，需要先查询出所有的数据，有点浪费资源。

那么，如何优化呢？

正例：

select id, create_date   from order  where user_id=123  order by create_date asc  limit 1; 
使用limit 1，只返回该用户下单时间最小的那一条数据即可。

此外，在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。

例如：

update order set status=0,edit_time=now(3)  where id>=100 and id<200 limit 100;
这样即使误操作，比如把id搞错了，也不会对太多的数据造成影响。
 

 in中值太多

对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息。

sql语句如下：

select id,name from category where id in (1,2,3...100000000);
如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。

这时该怎么办呢？

select id,name from category where id in (1,2,3...100) limit 500;
可以在sql中对数据用limit做限制。

不过我们更多的是要在业务代码中加限制，伪代码如下：

    public List<Category> getCategory(List<Long> ids) { 
 
       if(CollectionUtils.isEmpty(ids))  
           return null;   
         
       if(ids.size() > 500)   
           throw new BusinessException("一次最多允许查询500条记录") 
          
       return mapper.getCategoryList(ids); 
 
    }

还有一个方案就是：如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。

不过这只是一个临时方案，不适合于ids实在太多的场景。因为ids太多，即使能快速查出数据，但如果返回的数据量太大了，网络传输也是非常消耗性能的，接口性能始终好不到哪里去。
 

 高效的分页

有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。

在mysql中分页一般用的limit关键字：

select id,name,age  from user limit 10,20; 
如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。

比如现在分页参数变成了：

select id,name,age  from user limit 1000000,20;
mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。

那么，这种海量数据该怎么分页呢？

优化sql：

select id,name,age  from user where id > 1000000 limit 20;
先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。

还能使用between优化分页。

select id,name,age  from user where id between 1000000 and 1000020;
需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题。
 

用连接查询代替子查询

mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询 和 连接查询。

子查询的例子如下：

select  字段,字段,字段,字段 from order 
where user_id in 
(select id from user where status=1) 
子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。

子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。

但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。而且不论是内存临时表还是磁盘临时表都不会存在索引。

由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。这时可以改成连接查询。

具体例子如下：

select o. 字段,字段,字段,字段 from order o 
inner join user u on o.user_id = u.id 
where u.status=1
 

提高group by语句的效率

可以在执行到该语句前，把不需要的记录过滤掉

反例：先分组，再过滤

select job，avg（salary） from employee  
group by job 
having job ='president' or job = 'managent';

正例：先过滤，后分组

select job，avg（salary） from employee 
where job ='president' or job = 'managent' 
group by job;
 

伪删除设计

商品状态（state）：1-上架、2-下架、3-删除

理由：

这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
同时，一个大型系统中，表关系是非常复杂的，如电商系统中，商品作废了，但如果直接删除商品，其它商品详情，物流信息中可能都有其引用。
通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
操作速度快，特别数据量很大情况下

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

• 关联的表个数越多，编译的时间和开销也就越大
• 每次关联内存中都生成一个临时表
• 应该把连接表拆开成较小的几个执行，可读性更高
• 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了
• 阿里规范中，建议多表联查三张表以下

复合索引最左特性

理由：

• 复合索引也称为联合索引
• 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
• 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的

排序字段创建索引

什么样的字段才需要创建索引呢？原则就是where和order by中常出现的字段就创建索引

删除冗余和重复的索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引

有联合索引，可以删除单独索引

主键会自动创建索引，删除主键索引

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

使用force index来强制查询sql走某个索引

有时候mysql会选错索引。