MySQL常见面试题总结

当 MySQL 单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化：

单表优化

除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种
复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事
实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量：

设计

• 尽量控制单表数据量的大小，建议控制在 500 万以内。
• 谨慎使用 MySQL 分区表：谨慎选择分区键，跨分区查询效率可能更低；建议采用物理分表的方管理大数据。
• 尽量做到冷热数据分离，减小表的宽度：减少磁盘 IO，保证热数据的内存缓存命中率（表越宽把表装载进内存缓冲池时所占用的内存也就越大，也会消耗更多的 IO）；更有效的利用缓存，免读入无用的冷数据；经常一起使用的列放到一个表中（避免更多的关联操作）。
• 禁止在数据库中存储图片，文件等大的二进制数据：通常文件很大，会短时间内造成数据量快速长，数据库进行数据库读取时，通常会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时。件通常通常存储于文件服务器，数据库只存储文件地址信息。

字段

• 优先选择符合存储需要的最小的数据类型。

列的字段越大，建立索引时所需要的空间也就越大，样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多，索的性能也就越差。

如：
1、将字符串转换成数字类型存储：将IP地址转换成整型数据。
2、对于非负型数据，优先使用无符号型存储（无符号型多一倍存储空间）。
3、尽量使用 TINYINT 、 SMALLINT 、 MEDIUM_INT 作为整数类型而非 INT 。
4、VARCHAR 的长度只分配真正需要的空间。
5、尽量使用 TIMESTAMP （4个字节）而非 DATETIME (8个字节)。

• 避免使用 ENUM 类型。

修改 ENUM 值需要使用 ALTER 语句；ENUM 类型的 ORDER BY 操作效率低，需要额外操作；止使用数值作为 ENUM 的枚举值

• 单表不要有太多字段，建议在20以内。
• 避免使用NULL字段

索引 NULL 列需要额外的空间来保存，所以要占用更多的空间。进行比较计算时要对 NULL 值做特别的处理，很难查询优化。

索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDER BY这些命令的时候，性能提高更为明显。

• 限制每张表上的索引数量。避免建立冗余索引和重复索引。
• 选择合适的索引列：

1、出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列；
2、包含在 ORDER BY、GROUP BY、DISTINCT 中的字段；
3、并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更
好；
4、多表 join 的关联列；
5、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索
引。

• 选择索引列的顺序

1、希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少；
2、区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）；
3、尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）；
4、使用最频繁的列放到联合索引的左侧。

• 主键选择

1、不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）
2、不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）
3、主键建议使用自增 ID 值

• 对于频繁的查询优先考虑使用覆盖索引

1、索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
2、一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
3、对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

• 尽量避免使用外键约束
  不建议使用外键约束，但一定要在表与表之间的关联键上建立索引。
  外键可用于保证数据的参照完整性，但建议在业务端实现，外键会影响父表和子表的写操作从而降低性能。

查询SQL：充分利用索引，减少IO和全表扫描

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。
首先，最好是在相同类型的字段间进行比较的操作。其次，在建有索引的字段上尽量不要使用函数进行操作。第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

• 可通过开启慢查询日志来找出较慢的SQL；
• 不做列运算： SELECT id WHERE age + 1 = 10 ，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边；
• sql语句尽可能简单：一条sql只能在一个CPU运算；大语句拆小语句，减少锁时间；
• 不用 SELECT *；原因：消耗更多的 CPU 和 IO 以网络带宽资源；无法使用覆盖索引；
• OR 改写成 IN ： OR 的效率是n级别， IN 的效率是log(n)级别，in的个数建议控制在200以内；
• 避免使用JOIN关联太多的表。

MySQL 存在关联缓存，同一个 SQL 多关联一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。
同时对于关联操作来说，会产生临时表操作，影响查询效率。

• 列表数据不要拿全表，要使用 LIMIT 来分页，每页数量也不要太大

使用连接（JOIN）来代替子查询（使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易）。

使用联合(UNION)来代替手动创建的临时表。union查询可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要相同。

引擎

目前广泛使用的是MyISAM和InnoDB两种引擎：
总体来讲，MyISAM适合 SELECT 密集型的表，而InnoDB适合 INSERT 和 UPDATE 密集型的表。

事务

作用是：要么语句块中每条语句都操作成功，要么都失败，从而可以保持数据库中数据的一致性和完整性；另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

缺点：尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性（数据库将会被锁定），会影响数据库的性能，尤其是在很大的应用系统中。

锁定表

包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。缺点：锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';
...
UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES

使用外键

可以保证数据的关联性。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。

Java方面

• 尽可能的少造对象；
• 合理摆正系统设计的位置。大量数据操作，和少量数据操作一定是分开的；
• 控制好内存，让数据流起来，而不是全部读到内存再处理，而是边读取边处理；
• 合理利用内存，有的数据要缓存；
• 如果是反复使用的查询，使用 PreparedStatement 减少查询次数。

读写分离

一个主库（Master）用于写数据，多个从库（Slaver）进行轮询读取数据的过程，主从库之间通过某种通讯机制进行数据的同步，是一种常见的数据库架构。一般不要采用双主或多主引入很多复杂性，尽量采用文中的其他方案来提高性能。同时目前很多拆分的解决方案同时也兼顾考虑了读写分离。

大多数互联网数据操作往往都是读多写少，随着数据的增长，数据库的“读”会首先成为瓶颈。如果我们希望能线性地提升数据库的读性能和写性能，就需要让读写尽可能的不相互影响，各自为政。在使用读写分离之前我们应该考虑使用缓存能不能解决问题。然后再考虑对数据库按照 “读” 和 “写” 进行分组。

读写分离意味着将一体的结构的进行分散，在数据量大、高并发的情景中要考虑以下这些问题：

1. 如何保证 Master 的高可用，故障转移，熔断限流等。
2. 读写操作的区分规则，代码层面如何处理好读命令和写命令，尽量无感知无业务入侵。
3. 数据一致性的容忍度。虽然是数据同步，但是由于网络的不确定性这仍然是一个不可忽视的问题。
4. 事务问题。如果一个事务中同时包含了读的请求和写的请求,如果读的请求走从库,写的请求走主库。由于跨了多个库,那么本地事务已经无法控制。
   而分布式事务非常复杂且效率较低，因此读写分离目前主流的做法是：将事务中所有sql统一走主库，由于只涉及到一个库，本地事务可以解决。

缓存

缓存可以发生在这些层次：

• MySQL内部：在系统调优参数介绍了相关设置
• 数据访问层：比如MyBatis针对SQL语句做缓存
• 应用服务层：这里可以通过编程手段对缓存做到更精准的控制和更多的实现策略，这里缓存的对象是数据传输对象 Data Transfer Object
• Web层：针对web页面做缓存
• 浏览器客户端：用户端的缓存

可以根据实际情况在一个层次或多个层次结合加入缓存。

批量读取和延迟修改

批量读取和延迟修改的原理是通过减少操作数据库的操作来提高效率。
批量读取是将多次查询合并到一次中进行读取，因为每一个数据库的请求操作都需要链接的建立和链接的释放，还是占用一部分资源的，批量读取可以通过异步的方式进行读取。
延迟修改是对于一些高并发的并且修改频繁修改的数据，在每次修改的时候首先将数据保存到缓存中，然后定时将缓存中的数据保存到数据库中，程序可以在读取数据时可以同时读取数据库中和缓存中的数据。

分库分表

分库：数据库垂直拆分、数据库水平拆分 统称 分库。是指按照特定的条条件和维度，将同一个数据库中的数据拆分到多个数据库（主机）上面以达到分散单库（主机）负载的效果。这样我们变相地降低了数据集的大小，以空间换时间来提升性能。

分表也分为 数据表垂直拆分 和 数据表水平拆分 。
分区：将大表进行分区，不同分区可以放置在不同存储设备上，这些分区在逻辑上组成一个大表，对客户端透明。

垂直拆分

数据库垂直拆分 指的是按照业务对数据库中的表进行分组，同组的放到一个新的数据库（逻辑上，并非实例）中。需要从实际业务出发将大业务分割成小业务。比如猫眼的整个业务中的 用户相关表，订单相关表，影院相关表，影片相关表 各自独立分类形成 用户系统数据库，订单系统数据库，影院系统数据库，影片系统数据库。

优点：

• 解决业务系统层面的耦合，业务清晰
• 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
• 高并发场景下，垂直切分一定程度的提升 IO、数据库连接数、单机硬件资源的瓶颈

缺点：

• 部分表关系不能 join 只能通过服务相互调用来维系。甚至由于网络问题引发数据不一致。
• 形成跨库事务，分布式事务处理复杂
• 依然存在单表数据量过大的问题（需要水平切分）

数据表垂直拆分就是纵向地把表中的列分成多个表，把表从“宽”变“窄”。一般遵循以下几个点进行拆分：

• 冷热分离，把常用的列放在一个表，不常用的放在一个表。
• 大字段列独立存放
• 关联关系的列紧密的放在一起

水平拆分

在数据库垂直拆分后遇到单机数据库性能瓶颈之后，就可以考虑数据库水平拆分了。 之所以先垂直拆分才水平拆分，是因为垂直拆分后数据业务清晰而且单一，更加方便指定水平的标准。比如我们对猫眼业务垂直拆分后的 用户系统 进行水平拆分就比对整个业务进行水平拆分好找维度，我们可以根据用户注册时间的区间、用户的区域或者用户 ID 的范围、 hash 等条件，然后关联相关表的记录将数据进行拆分。

这种拆分的好处在于：（a）单个库的容量可控 （b）单条记录保证了数据完整性 （c）数据关系可以通过 join 维持 （d） 避免了跨库事务 ；
缺点同样存在：（a）拆分规则对编码有一定的影响 （b）不同业务的分区交互需要统筹设计（c）无法解决集中写入瓶颈的问题。

库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻MySQL服务器的压力来说，并没有太大的作用，大家还是竞争同一个物理机上的IO、CPU、网络，这个就要通过分库来解决。
并非所有表都需要水平拆分，要看增长的类型和速度，水平拆分是大招，拆分后会增加开发的复杂度，不到万不得已不使用。

水平切分规则：

1、根据数值范围

按照时间区间或ID区间来切分。例如：按日期将不同月甚至是日的数据分散到不同的库中；将 userId为 1 ~ 9999 的记录分到第一个库， 10000 ~ 20000 的分到第二个库，以此类推。某种意义上，某些系统中使用的“冷热数据分离”，将一些使用较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。

这样的优点在于：

• 单表大小可控
• 天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移
• 使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。

缺点： 热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，而有些分片存储的历史数据，则很少被查询。

2、根据数值取模

一般采用 hash 取模 mod 的切分方式，例如：将 Customer 表根据 cusno 字段切分到 4 个库中，余数为0的放到第一个库，余数为 1 的放到第二个库，以此类推。这样同一个用户的数据会分散到同一个库中，如果查询条件带有 cusno 字段，则可明确定位到相应库去查询。

优点：
数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈
缺点：

• 后期分片集群扩容时，需要迁移旧的数据（使用一致性 hash 算法能较好的避免这个问题：一致性哈希的目的就是为了在节点数目发生改变时尽可能少的迁移数据，将所有的存储节点排列在收尾相接的Hash环上，每个key在计算Hash 后会顺时针找到临接的存储节点存放。而当有节点加入或退时，仅影响该节点在Hash环上顺时针相邻的后续节点。）

• 容易面临跨分片查询的复杂问题。比如上例中，如果频繁用到的查询条件中不带 cusno 时，将会导致无法定位数据库，从而需要同时向 4 个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。

表分区

分区是根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言，逻辑上就只有一个表或者一个索引，但实际上这个表可能有N个物理分区对象组成，每个分区都是一个独立的对象，可以独立处理，可以作为表的一部分进行处理。分区对应用来说是完全透明的，不影响应用的业务逻辑。

分区有利于管理非常大的表，它采用分而治之的逻辑，分区引入了分区键的概念，分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中。

MySQL分区即可以对数据进行分区也可以对索引进行分区。

水平拆分需要用户预先手动显式创建出多张分表(如tbl_user0, tbl_user1, tbl_user2)，在物理上实实在在的创建多张表，通过客户端代理(Sharding-JDBC等)或者中间件代理(Mycat等)来实现分表逻辑。

分区是MySQL的一个插件功能，将一张大表的数据在数据库底层分成多个分区文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd)，和水平分表不同的是分区不需要显式的创建“分表”，数据库会自动创建分区文件的，用户看到的只是一张普通的表，其实是对应的是多个分区，这个是对用户是屏蔽的、透明的，在使用上和使用一张表完全一样，不需要借助任何功能来实现。分区是一种逻辑上的水平分表，在物理层面还是一张表。

优点：

• 分区表的数据更容易维护，可以通过清除整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作；
• 优化查询。部分查询能够从查询条件确定只落在少数分区上，速度会很快；
• 分区表的数据还可以分布在不同的物理设备上，从而高效利用多个硬件设备，获得更大的查询吞吐量；
• 可以备份和恢复单个分区。

缺点：

• 一个表最多只能有1024个分区
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
• 分区表无法使用外键约束
• 所有分区必须使用相同的存储引擎

分区的类型：

• range分区：基于一个给定的连续区间范围，把数据分配到不同分区（如1-1000行一个分区；1001-2000行一个分区）

使用range分区时表结构要么没有主键，要么分区字段必须是主键。

• list分区：设置若干个固定值进行分区，如果某个字段的值在这个设置的值列表中就会被分配到该分区。适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列。
• hash分区：基于给定的分区个数，把数据分配到不同分区。（如5000行数据4个分区，分区号 =行数%分区个数）

常规hash分区：取模，均匀分布。但分区数改变后，需要重新分区，在管理上带来了的代价太大，不适合需要灵活变动分区的需求。

线性hash分区，分区函数是一个线性的2的幂的运算法则。同样线性hash分区的记录被存在那个分区也是能被计算出来的。线性hash分区的优点是在分区维护(增加、删除、合并、拆分分区)时，mysql能够处理的更加迅速，缺点是：对比常规hash分区，线性hash各个分区之间数据的分布不太均衡。

• key分区：类似于hash分区。只不过hash分区允许使用用户自定义的表达式，而key分区不允许使用用于自定义的表达式，需要使用mysql服务器提供的hash函数，同时hash分区只支持整数分区，而key分区支持使用出blob or text类型外的其他类型的列作为分区键。

和hash功能一样，不同的是分区的字段可以是非int类型,如字符串、日期等类型。

分区适合的场景有：

• 最适合的场景数据的时间序列性比较强，则可以按时间来分区，如下所示：

CREATE TABLE members (
firstname VARCHAR(25) NOT NULL,
lastname VARCHAR(25) NOT NULL,
username VARCHAR(16) NOT NULL,
email VARCHAR(35),
joined DATE NOT NULL
) 

PARTITION BY RANGE( YEAR(joined) ) (
PARTITION p0 VALUES LESS THAN (1960),
PARTITION p1 VALUES LESS THAN (1970),
PARTITION p2 VALUES LESS THAN (1980),
PARTITION p3 VALUES LESS THAN (1990),
PARTITION p4 VALUES LESS THAN MAXVALUE
);

查询时加上时间范围条件效率会非常高，同时对于不需要的历史数据能很容的批量删除。

• 如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将热点数据单独放在一个分区，让这个分区的数据能够有机会都缓存在内存中，查询时只访问一个很小的分区表，能够有效使用索引和缓存。

在实际操作之前要做好数据量的预估，这样能够根据预测未来数据的增量来进行选型。业务数据增长较小，常用于表的拆分。增长特别大达到上万级别则可以选择分库，比如一些资金积分流水，历史记录之类的。有些时候并不是拆分完就万事大吉了，比如我们按照地区拆分后，A地区业务增长很快业绩很好，而B地区推广不力竞争激烈业绩萧条，造成了数据倾斜。也会影响分库分表的期望效果。这需要建立长效的监控预测机制来应对，甚至根据实际情况及时调整策略。数据拆分还面临分布式的很多问题，分布式事务，高可用，数据一致性，全局唯一性都是应该考虑的问题。

分库分表导致的问题

事务一致性问题（见分布式事务）

批量插入记录到四个不同的库，如何保证要么同时成功，要么同时失败。
关于分布式事务，mysql支持XA事务，但是效率较低。
柔性事务是目前比较主流的方案,柔性事务包括：最大努力通知型、可靠消息最终一致性方案以及TCC两个阶段提交。
但是无论XA事务还是柔性事务,实现起来都是非常复杂的。

跨节点join问题

当进行了业务分库，或者其它切片方式将库放置在不同数据库实例上时，因为跨了实例，将无法进行join操作。
切分之前，系统中很多列表和详情页所需的数据可以通过 sql join 来完成。而切分之后，数据可能分布在不同的节点上，此时 join 带来的问题就比较麻烦了，考虑到性能，尽量避免使用 join 查询。

解决这个问题的一些方法：

• 全局表

全局表，也可看做是数据字典表，就是系统中所有模块都可能依赖的一些表，为了避免跨库 join 查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。

• 数据组装

在系统层面，分两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。

• ER 分片

关系型数据库中，如果可以先确定表之间的关联关系，并将那些存在关联关系的表记录存放在同一个分片上，那么就能较好的避免跨分片 join 问题。

扩容和数据迁移艰难

对于那些以范围、取模方式做水平切分的大表，扩容以及扩容时的数据迁移很艰难。需要解决几个问题：

• 扩容到多少节点比较满足自己的期望。
• 扩容时，哪些数据需要从旧节点清洗掉，哪些数据需要从旧节点迁移到新节点。
• 如何实现在线迁移。（使用一致性 hash 算法能较好的避免这个问题）

什么时候考虑切分

• 能不切分尽量不要切分

并不是所有表都需要进行切分，主要还是看数据的增长速度。切分后会在某种程度上提升业务的复杂度，数据库除了承载数据的存储和查询外，协助业务更好的实现需求也是其重要工作之一。
不到万不得已不用轻易使用分库分表这个大招，避免"过度设计"和"过早优化"。分库分表之前，不要为分而分，先尽力去做力所能及的事情，例如：升级硬件、升级网络、读写分离、索引优化等等。当数据量达到单表的瓶颈时候，再考虑分库分表。

• 数据量过大，正常运维影响业务访问

1、对数据库备份，如果单表太大，备份时需要大量的磁盘 IO 和网络 IO。例如1T的数据，网络传输占 50MB 时候，需要 20000 秒才能传输完毕，整个过程的风险都是比较高的。
2、对一个很大的表进行 DDL 修改时，MySQL 会锁住全表，这个时间会很长，这段时间业务不能访问此表，影响很大。如果使用 pt-online-schema-change，使用过程中会创建触发器和影子表，也需要很长的时间。在此操作过程中，都算为风险时间。将数据表拆分，总量减少，有助于降低这个风险。
3、大表会经常访问与更新，就更有可能出现锁等待。将数据切分，用空间换时间，变相降低访问压
力。

• 随着业务发展，需要对某些字段垂直拆分

举个例子，假如项目一开始设计的用户表如下：

id 				bigint 	 #用户的ID
name 			varchar  #用户的名字
last_login_time datetime #最近登录时间
personal_info 	text 	 #私人信息
..... 	             	 #其他信息字段

在项目初始阶段，这种设计是满足简单的业务需求的，也方便快速迭代开发。而当业务快速发展时，用户量从 10w 激增到 10 亿，用户非常的活跃，每次登录会更新 last_login_name 字段，使得 user 表被不断update，压力很大。而其他字段：id, name, personal_info 是不变的或很少更新的，此时在业务角度，就要将 last_login_time 拆分出去，新建一个 user_time 表。

personal_info 属性是更新和查询频率较低的，并且 text 字段占据了太多的空间。这时候，就要对此垂直拆分出 user_ext 表了。

• 数据量快速增长

随着业务的快速发展，单表中的数据量会持续增长，当性能接近瓶颈时，就需要考虑水平切分，做分库分表了。此时一定要选择合适的切分规则，提前预估好数据容量

• 安全性和可用性

鸡蛋不要放在一个篮子里。在业务层面上垂直切分，将不相关的业务的数据库分隔，因为每个业务的数据量、访问量都不同，不能因为一个业务把数据库搞挂而牵连到其他业务。利用水平切分，当一个数据库出现问题时，不会影响到 100% 的用户，每个库只承担业务的一部分数据，这样整体的可用性就能提高。

主从复制

为了减轻主库的压力，应该在系统应用层面做读写分离，写操作走主库，读操作走从库。可以分散数据库的访问压力，提升整个系统的性能和可用性，降低大访问量引发数据库宕机的故障率。

复制的结果是集群中的所有数据库服务器得到的数据理论上都是一样的，都是同一份数据，只是有多个copy。MySQL默认内建的复制策略是异步的，基于不同的配置，Slave不一定要一直和Master保持连接不断的复制或等待复制，我们可以指定复制所有的数据库，一部分数据库，甚至是某个数据库的某部分的表。

复制策略

• 同步策略：Master要等待所有Slave应答之后才会提交（MySql对DB操作的提交通常是先对操作事件进行二进制日志文件写入然后再进行提交）。
• 半同步策略：Master等待至少一个Slave应答就可以提交。确保事务提交后bin log至少传输到一个从库，解决宕机数据丢失问题。
• 异步策略：Master不需要等待Slave应答就可以提交。
• 延迟策略：Slave要至少落后Master指定的时间。

复制模式

根据bin log日志格式的不同，MySQL复制同时支持多种不同的复制模式：

• 基于语句的复制，即Statement Based Replication（SBR）：记录每一条更改数据的sql

优点：binlog文件较小，节约I/O，性能较高。
缺点：不是所有的数据更改都会写入bin log文件中，尤其是使用MySQL中的一些特殊函数（如LOAD_FILE()、UUID()等）和一些不确定的语句操作，从而导致主从数据无法复制的问题。

• 基于行的复制，即Row Based Replication（RBR）：不记录sql，只记录每行数据的更改细节

优点：详细的记录了每一行数据的更改细节，这也意味着不会由于使用一些特殊函数或其他情况导致不能复制的问题。
缺点：由于row格式记录了每一行数据的更改细节，会产生大量的bin log日志内容，性能不佳，并且会增大主从同步延迟出现的几率。

• 混合复制（Mixed）

一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

优点

• 性能方面：MySQL复制是一种Scale-out方案，也即“水平扩展”，将原来的单点负载扩散到多台Slave机器中去，从而提高总体的服务性能。在这种方式下，所有的写操作，当然包括UPDATE操作，都要发生在Master服务器上。读操作发生在一台或者多台Slave机器上。这种模型可以在一定程度上提高总体的服务性能，Master服务器专注于写和更新操作，Slave服务器专注于读操作，我们同时可以通过增加Slave服务器的数量来提高读服务的性能。——实现读写分离
• 故障恢复：同时存在多台Slave提供读操作服务，如果有一台Slave挂掉之后我们还可以从其他Slave读取，如果配置了主从切换的话，当Master挂掉之后我们还可以选择一台Slave作为Master继续提供写服务，这大大增加了应用的可靠性。
• 数据分析：实时数据可以存储在Master，而数据分析可以从Slave读取，这样不会影响Master的性能。

实现

mysql主从复制需要三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread）。

• binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
• I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
• SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

主从同步的延迟

mysql的主从复制都是单线程的操作，主库对所有DDL和DML产生bin log，bin log是顺序写，所以效率很高，slave的I/O线程到主库取日志，效率也比较高，但是，slave的SQL线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随即的，不是顺序的，成本高很多，还可能存在slave上的其他查询产生lock争用的情况，由于SQL也是单线程的，所以一个DDL卡住了，需要执行很长一段事件，后续的DDL线程会等待这个DDL执行完毕之后才执行，这就导致了延时。当主库的TPS并发较高时，产生的DDL数量超过slave一个sql线程所能承受的范围，延时就产生了，除此之外，还有可能与slave的大型query语句产生了锁等待导致。

解决方案：

1. 可以通过pt-query-digest工具分析备库上的慢查询日志，找出是查询导致的问题还是写入负载太大导致的问题，如果是查询导致的问题可以看是否可以通过拆分查询等方式来加快速度，如果是负载过大，可以通过预读的方式（在早于重放事务的时候将事务修改为select语句将需要涉及的页预读进内存），从而提高实际重放时的速度，这样可以间接弥补单线程的限制。
2. 业务的持久化层的实现采用分库架构，mysql服务可平行扩展，分散压力。
3. 服务的基础架构在业务和mysql之间加入memcache或者Redis的cache层。降低mysql的读压力；
4. 使用比主库更好的硬件设备作为slave；
5. slave主要是读，对数据安全要求没有master高，可以将 sync_binlog 设置为0或者关闭bin log，innodb_flushlog也可以设置为0来提高sql的执行效率。

提升效率

MySQL的主从复制，实际上就是Master记录自己的执行日志binlog，然后发送给Slave，Slave解析日志并执行，来实现数据复制。
对于复制效率，binlog的大小是非常重要的因素，因为它涉及了I/O和网络传输。

1、master 端

master端有2个参数可以控制：
Binlog_Do_DB : 设定哪些数据库需要记录Binlog
Binlog_Ignore_DB : 设定哪些数据库不要记录Binlog

这两项很重要，指定必要数据库，忽略不需要复制的数据库，可以减少binlog的大小，提高了I/O效率，加快网络传输。但这两项也同样比较危险，需要谨慎使用，因为可能会有主从数据不一致和复制出错的风险。因为MySQL判断是否须要复制某个Event，不是根据产生该Event的语句所在的数据库，而是根据执行时所在的默认数据库，也就是登录时指定的数据库，或运行“USE DATABASE”中所指定的数据库。如果执行语句中明确指定了数据库名称，而这个数据库是被指定不记录Binlog的，那么这个语句在slave中执行时就会出错。

2、slave 端

slave端有6个参数可以控制：
Replicate_Do_DB : 设定须要复制的数据库，多个DB用逗号分隔
Replicate_Ignore_DB : 设定可以忽略的数据库
Replicate_Do_Table : 设定须要复制的Table
Replicate_Ignore_Table : 设定可以忽略的Table
Replicate_Wild_Do_Table : 功能同Replicate_Do_Table，但可以带通配符来进行设置
Replicate_Wild_Ignore_Table : 功能同Replicate_Ig-nore_Table，可带通配符设置

slave端的配置优化效果要明显小于master端的，因为master端日志都写完了，日志也传过来了
但这几个参数可以帮助我们减少日志的应用量，因为设置了过滤，实际写入的sql数量变少了，slave端的复制也就加快了

池化设计思想

池话设计应该不是一个新名词。我们常见的如Java线程池、JDBC连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。除了初始化资源，池化设计还包括如下这些特征：池子的初始值、池子的活跃值、池子的最大值等，这些特征可以直接映射到Java线程池和数据库连接池的成员属性中。

数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。连接池还减少了用户必须等待建立与数据库的连接的时间。

一条SQL语句在MySQL中如何执行

MySQL 基本架构

• 连接器： 跟客户端建立连接、获取权限、维持和管理连接。连接的时候会经过TCP握手，然后身份验证，然后输入用户名密码。但是这个时候处于空闲状态。

默认8小时没有响应，连接会自动断开。
解决：
1、使用长连接。但使用长连接之后，内存会飙得很快，MySQL在执行过程中临时使用的内存是管理在连接对象里面的，只有在链接断开的时候才能得到释放。如果一直使用长连接，那就会导致OOM，在JVM里面就会导致频繁的Full GC。
2、执行比较大的一个查询后，执行mysql_reset_connection，重新初始化连接资源。

• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。缓存这一块，我们还是交给 ORM 框架（比如 MyBatis 默认开启了一级缓存），或者独立的缓存服务，比如 Redis 来处理更合适。）
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛。

第一步，词法分析，一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。
第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

• 优化器： 按照 MySQL 认为最优的方案去执行。比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。
• 执行器: 执行语句，然后从存储引擎返回数据

语句分析

查询语句

select * from tb_student A where A.age='18' and A.name=' 张三 ';

• 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL 8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
• 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 sql语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
• 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。
b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

• 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

更新语句

update tb_student A set A.age='19' where A.name=' 张三 ';

语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候要记录日志，引入日志模块。MySQL自带的日志模块式 bin log（归档日志） ，所有的存储引擎都可以使用， InnoDB 引擎还自带了一个日志模块 redo log（重做日志），以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

• 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
• 然后拿到查询的语句，把 age 改为 19，然后调用引擎接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态（两段式提交，防止数据不一致），然后告诉执行器，执行完成了，随时可以提交。
• 执行器收到通知后记录 bin log，然后调用引擎接口，提交 redo log 为提交状态。
• 更新完成。

一条SQL语句执行得很慢的原因有哪些？

偶尔很慢

1、数据库在刷新脏页（flush）

• redo log写满了：redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢。

• 内存不够用了：如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。

• MySQL 认为系统“空闲”的时候：这时系统没什么压力。

• 正常关闭的时候：这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

2、拿不到锁
用 show processlist这个命令来查看当前的状态，判断是否真的在等待锁。

一直很慢：SQL语句自身的问题

1、索引问题
没有用索引：

• 字段没有索引；
• 字段有索引但没有用上：由于对字段进行运算、函数操作导致无法用索引。

用索引： 数据库选择其他索引：索引的区分度、这条查询语句是否需要使用使用临时表、是否需要排序等影响系统的选择。

2、查询语句不好
见性能优化。

3、查询出的数据量过大
一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH)
LIMIT 10000")
} while rows_affected > 0

4、分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• 减少锁竞争；
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

数据库批量操作优化

一条SQL语句插入多条数据

INSERT INTO insert_table (datetime, uid, content, type) VALUES ('0', 'userid_0','content_0', 0);
INSERT INTO insert_table (datetime, uid, content, type) VALUES ('1', 'userid_1','content_1', 1);
//修改成：
INSERT INTO insert_table (datetime, uid, content, type) VALUES ('0', 'userid_0','content_0', 0), ('1', 'userid_1', 'content_1', 1);

合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

在事务中进行插入处理

把插入修改成：

START TRANSACTION;
INSERT INTO insert_table (datetime, uid, content, type) VALUES ('0', 'userid_0','content_0', 0);
INSERT INTO insert_table (datetime, uid, content, type) VALUES ('1', 'userid_1','content_1', 1);
...
COMMIT;

使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。

数据有序插入

数据有序的插入是指插入记录在主键上是有序排列，例如datetime是记录的主键：

INSERT INTO insert_table (datetime, uid, content, type) VALUES ('1', 'userid_1','content_1', 1);
INSERT INTO insert_table (datetime, uid, content, type) VALUES ('0', 'userid_0','content_0', 0);
INSERT INTO insert_table (datetime, uid, content, type) VALUES ('2', 'userid_2','content_2',2);

修改成：

INSERT INTO insert_table (datetime, uid, content, type) VALUES ('0', 'userid_0','content_0', 0);
INSERT INTO insert_table (datetime, uid, content, type) VALUES ('1', 'userid_1','content_1', 1);
INSERT INTO insert_table (datetime, uid, content, type) VALUES ('2', 'userid_2','content_2',2);

由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。我们可以参照InnoDB使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。

数据库日志

MySQL中有六种日志文件，
分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（bin log）、错误日志（error log）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。

redo log

确保事务的持久性。在发生故障时，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。
修改一条数据，首先写redo日志，然后再写数据。当数据库挂了之后，通过扫描redo日志，就能找出那些没有刷盘的数据页，保证数据不丢。
当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。
内存中有重做日志缓冲，逐步写入硬盘中的重做日志文件。

undo log

事务提交前需要将 undo Log 写磁盘，保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供 MVCC。

数据库还提供类似撤销的功能，当你发现修改错一些数据时，可以使用rollback指令回滚之前的操作。这个功能需要undo日志来支持。此外，现代的关系型数据库为了提高并发(同一条记录，不同线程的读取不冲突，读写和写读不冲突，只有同时写才冲突)，都实现了类似MVCC的机制，在 InnoDB 中，这个也依赖undo日志。为了实现统一的管理，与redo日志不同，undo日志在Buffer Pool中有对应的数据页，与普通的数据页一起管理，依据LRU规则也会被淘汰出内存，后续再从磁盘读取。与普通的数据页一样，对undo页的修改，也需要先写redo日志。

检查点: 英文名为checkpoint。数据库为了提高性能，数据页在内存修改后并不是每次都会刷到磁盘上。checkpoint之前的数据页保证一定落盘了，这样之前的日志就没有用了(由于InnoDB redol og日志循环使用，这时这部分日志就可以被覆盖)，checkpoint之后的数据页有可能落盘，也有可能没有落盘，所以checkpoint之后的日志在崩溃恢复的时候还是需要被使用的。 InnoDB 会依据脏页的刷新情况，定期推进checkpoint，从而减少数据库崩溃恢复的时间。检查点的信息在第一个日志文件的头部。

崩溃恢复: 用户修改了数据，并且收到了成功的消息，然而对数据库来说，可能这个时候修改后的数据还没有落盘，如果这时候数据库挂了，重启后，数据库需要从日志中把这些修改后的数据给捞出来，重新写入磁盘，保证用户的数据不丢。这个从日志中捞数据的过程就是崩溃恢复的主要任务，也可以成为数据库前滚。当然，在崩溃恢复中还需要回滚没有提交的事务，提交没有提交成功的事务。由于回滚操作需要undo日志的支持，undo日志的完整性和可靠性需要redo日志来保证，所以崩溃恢复先做redo前滚，然后做undo回滚。

redo日志前滚数据库
前滚数据库，主要分为两阶段，首先是日志扫描阶段，扫描阶段按照数据页的space_id和page_no分发redo日志到hash_table中，保证同一个数据页的日志被分发到同一个哈希桶中，且按照lsn大小从小到大排序。扫描完后，再遍历整个哈希表，依次应用每个数据页的日志，应用完后，在数据页的状态上至少恢复到了崩溃之前的状态。

undo日志回滚数据库
重建起事务的状态，按照事务不同的状态回滚或者提交即可。

bin log日志模块

bin log是属于MySQL Server层面的，又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑，依靠binlog是没有 crash-safe 能力的
redo log是物理日志，记录该数据页更新的内容；bin log是逻辑日志，记录的是这个更新语句的原始逻辑。

慢查询

慢查询怎么看，怎么优化？

分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句。
EXPLAIN SELECT * FROM res_user ORDER BY modifiedtime LIMIT 0,1000

EXPLAIN列的解释：
table：显示这一行的数据是关于哪张表的；
type：重要列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL；
rows：显示需要扫描行数；
key：使用的索引。

常见的慢查询优化

• 索引没起作用的情况

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。
MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

• 优化数据库结构

合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。
对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

• 分解关联查询

将一个大的查询分解为多个小查询是很有必要的。很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。

• 优化LIMIT分页

在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。
当偏移量非常大的时候，例如limit 10000,20这样的查询，即查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

• 分析具体的SQL语句

两个表选哪个为驱动表，表面是可以以数据量的大小作为依据，但是实际经验最好交给mysql查询优化器自己去判断。

例如： select * from a where id in (select id from b );
对于这条sql语句它的执行计划其实并不是先查询出b表的所有id，然后再与a表的id进行比较。
mysql会把in子查询转换成exists相关子查询，所以它实际等同于这条sql语句：select * from a where exists(select * from b where b.id=a.id );

exists相关子查询的执行原理是: 循环取出a表的每一条记录与b表进行比较，比较的条件是a.id = b.id。看a表的每条记录的id是否在b表存在，如果存在就行返回a表的这条记录。
exists查询有什么弊端？
a表(外表)使用不了索引，必须全表扫描，因为是拿a表的数据到b表查。而且必须得使用a表的数据到b表中查（外表到里表中），顺序是固定死的。
如何优化？
建索引。但是由上面分析可知，要建索引只能在b表的id字段建，不能在a表的id上，mysql利用不上。
由于exists查询它的执行计划只能拿着a表的数据到b表查（外表到里表中），虽然可以在b表的id字段建索引来提高查询效率。但是并不能反过来拿着b表的数据到a表查，exists子查询的查询顺序是固定死的。
为什么要反过来？
因为首先可以肯定的是反过来的结果也是一样的。这样就又引出了一个更细致的疑问：在双方两个表的id字段上都建有索引时，到底是a表查b表的效率高，还是b表查a表的效率高？
该如何进一步优化？
把查询修改成inner join连接查询：select * from a inner join b on a.id=b.id; （但是仅此还不够，接着往下看）
为什么不用left join 和 right join？
这时候表之间的连接的顺序就被固定住了，比如左连接就是必须先查左表全表扫描，然后一条一条的到另外表去查询，右连接同理。仍然不是最好的选择。
为什么使用inner join就可以？
inner join中的两张表，如：a inner join b，但实际执行的顺序跟写法的顺序没有关系，最终执行也可能会是b连接a，顺序不是固定死的。如果on条件字段有索引的情况下，同样可以使用上索引。
那我们又怎么能知道a和b什么样的执行顺序效率更高？
你不知道，我也不知道。谁知道？mysql自己知道。让mysql自己去判断（查询优化器）。具体表的连接顺序和使用索引情况，mysql查询优化器会对每种情况做出成本评估，最终选择最优的那个做为执行计划。

在inner join的连接中,mysql会自己评估使用a表查b表的效率高还是b表查a表高。如果两个表都建有索引的情况下，mysql同样会评估使用a表条件字段上的索引效率高还是b表的。
利用explain字段查看执行时运用到的key（索引）
而我们要做的就是：把两个表的连接条件的两个字段都各自建立上索引，然后explain 一下，查看执行计划，看mysql到底利用了哪个索引，最后再把没有使用索引的表的字段索引给去掉就行了。