```sql

1. 引擎对并发的支持
2. 引擎的缓存支持对比
3. mysql与PG的对比
4. 索引 - 高效获取数据的数据结构，提高查询效率
5. B+树索引
6. Hash索引 通过Hash算法将数据库字段数据转换成定长的Hash值(redis的hash处理)
7. Full-Text全文索引 字段数据分割后再进行索引
8. R-Tree 主要用于地理空间数据类型
9. 创建索引的情况 10.事务基本要素是什么？ 11.并发事务带来的问题？- 更新丢失，脏读(回滚了)，不可重复读(多次读不一样)，幻读(读数量不一样)
10. 事务隔离机制级别？ - 读未提交，读已提交，可重复读(默认级别)，可串行化
11. mvcc是什么？
12. mysql的日志种类？
13. mysql的分布式事务提交
14. 乐观锁和悲观锁？乐观锁：假定不会发生并发更新冲突(数据版本记录机制实现)；悲观锁：假定会发生并发冲突(数据库自己实现) 17.死锁？ 两个或多个事务在同一资源相互占用
15. 最左前缀匹配原则
16. 索引下推
17. 覆盖索引，联合索引 ```

    架构

    • 画出 MySQL 架构图
    • MySQL 的查询流程具体是？or 一条SQL语句在MySQL中如何执行的？

mysql

mysql的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

• 连接层：最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
• 服务层：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等
• 引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取
• 存储层：第四层为数据存储层，主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

**

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

postgresql

PG使用经典的C/S架构，进程架构。在服务器端有主进程、服务进程、子进程、共享内存以及文件存储几大部分

PostgreSQL由连接管理系统（系统控制器），编译执行系统，存储管理系统，事务系统，系统表五大部分组成。

• 连接管理系统接受外部操作对系统的请求，对操作请求进行预处理和分发，起系统逻辑控制作用；
• 编译执行系统由查询编译器，查询执行器组成，完成操作请求在数据库中的分析处理和转化工作，最终实现物理存储介质中数据的操作；
• 存储管理系统由索引管理器，内存管理器，外存管理器组成，负责存储和管理物理数据，提供对编译查询系统的支持；
• 事务系统由事务管理器，日志管理器，并发控制，锁管理器组成，日志管理器和事务管理器完成对操作请求的事务一致性支持，锁管理器和并发控制提供对并发访问数据的一致性支持；
• 系统表是PostgreSQL数据库的元信息管理中心，包括数据库对象信息和数据库管理控制信息。系统表管理元数据信息，将PostgreSQL数据库的各个模块有机地连接在一起，形成一个高效的数据管理系统。

  mysql使用的是多线程，而PG使用的是多进程

存储引擎

• 说说MySQL有哪些存储引擎？都有哪些区别？
• 一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？
• 哪个存储引擎执行 select count(*) 更快，为什么?

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

mysql

查看存储引擎

-- 查看支持的存储引擎
SHOW ENGINES
-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'
--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename
--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"

设置存储引擎

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;
-- 修改存储引擎
ALTER TABLE t ENGINE = InnoDB;
-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;

存储引擎对比

常见的存储引擎就 InnoDB、MyISAM、Memory、NDB。
InnoDB 现在是 MySQL 默认的存储引擎，支持事务、行级锁定和外键
**

文件存储结构对比

在 MySQL中建立任何一张数据表，在其数据目录对应的数据库目录下都有对应表的 .frm 文件，.frm 文件是用来保存每个数据表的元数据(meta)信息，包括表结构的定义等，与数据库存储引擎无关，也就是任何存储引擎的数据表都必须有.frm文件，命名方式为 数据表名.frm，如user.frm。
查看MySQL 数据保存在哪里：show variables like 'data%'

• MyISAM：
  • .frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
  • .MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据
  • .MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息
• InnoDB：
  • .frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
  • .ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

    独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

InnoDB与MyISAM对比

1. InnoDB支持事务，MyISAM不支持事务。
2. InnoDB支持外键，而MyISAM不支持
3. InnoDB是聚簇索引（数据和索引都在叶子节点上），MyISAM是非聚簇索引（索引和数据分离）。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

   mysql中innodb和myisam引擎中的B-tree索引使用的是B+tree（即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历，并且除叶子节点外其他节点只存储键值和指针）。

1. InnoDB 不保存表的具体行数，执行select count(*) from table时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
2. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁（一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限）这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一 | 对比项 | MyISAM | InnoDB | | :—- | :—- | :—- | | 主外键 | 不支持 | 支持 | | 事务 | 不支持 | 支持 | | 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作 | | 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 | | 表空间 | 小 | 大 | | 关注点 | 性能 | 事务 | | 默认安装 | 是 | 是 |

一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？

• 如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；
• 如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。

  postgresql

  PG数据库的存储引擎Heap完全遵循ACID，使用的是行级索(类比Mysql的InnoDB)

它并非直接执行操作，而是通过预写日志(如mysql的bin_log),然后再根据日志来执行数据库变更操作。

PG与mysql对比

1. PG支持事务的强一致性，事务保证性好，完全支持ACID特性；而mysql只有innodb引擎支持事务，事务一致性保证上可根据实际需求调整，为了最大限度的保护数据，mysql可配置双一模式，对ACID的支持上比PG稍弱。
2. PG几乎支持所有SQL标准，mysql只支持部分SQL标准
3. mysql的复制是基于binlog的逻辑异步复制，无法实现同步复制，它的所有高可用方案都是基于binlog做的同步；而PG可以做到同步、异步、半同步复制，以及基于日志逻辑复刻，可以实现表级别的订阅和发布
4. 并发控制：PG通过MVCC有效解决了并发问题，从而实现了非常高的并发性。而mysql仅有innodb支持mvcc
5. 数据存储和数据类型：PG主表采用堆表存放，存放的数据量较大，访问方式类似于oracle的堆表；mysql采用索引组织表，innodb中所有数据访问都是通过主键实现，二级索引访问时，需要扫描两遍索引

   数据类型

   • CHAR 和 VARCHAR 的区别？
   • 列的字符串类型可以是什么？
   • BLOB和TEXT有什么区别？

支持多种数据类型: 整数类型，浮点数类型，字符串类型，日期类型，其他数据类型
**

CHAR 和 VARCHAR 的区别？

char是固定长度，varchar长度可变

BLOB和TEXT有什么区别？

BLOB是一个二进制对象，可以容纳可变数量的数据。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB
TEXT是一个不区分大小写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。
BLOB 保存二进制数据，TEXT 保存字符数据。

索引

• 说说你对 MySQL 索引的理解？
• 数据库索引的原理，为什么要用 B+树，为什么不用二叉树？
• 聚集索引与非聚集索引的区别？
• InnoDB引擎中的索引策略，了解过吗？
• 创建索引的方式有哪些？
• 聚簇索引/非聚簇索引，mysql索引底层实现，为什么不用B-tree，为什么不用hash，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？聚簇索引/非聚簇索引，mysql索引底层实现，为什么不用B-tree，为什么不用hash，叶子结点存放的是数据还是指向数据的内存地址，使用索引需要注意的几个地方？
• 为什么推荐使用整型自增主键而不是选择UUID？
• 为什么非主键索引结构叶子节点存储的是主键值？
• 为什么Mysql索引要用B+树不是B树？
• 为何不采用Hash方式？

MYSQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构
索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。
可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到对应的数据，从而快速检索出符合条件的记录。

索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上

平常说的索引，没有特别指明的话，就是B+树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，符合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。此外还有哈希索引等。

建立索引的优劣

• 优势：
  • 提高数据检索效率，降低数据库IO成本
    
  • 降低数据排序的成本，降低CPU的消耗

• 劣势：

  • 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段， 都会调整因为更新所带来的键值变化后的索引信息

    基本语法

    创建

• 创建索引：CREATE [UNIQUE] INDEX indexName ON mytable(username(length));

• 修改表结构(添加索引)：ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)

  删除

• DROP INDEX [indexName] ON mytable;

  查看

• SHOW INDEX FROM table_name\G

  使用alert命令

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通索引，索引值可出现多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)该语句指定了索引为 FULLTEXT ，用于全文索引。

  索引分类

1. 数据结构角度：

• B+树索引：
  • 聚簇索引(InnoDB)，叶子节点存放的是实际的数据记录，辅助索引搜索时先用辅助索引找到叶子节点获取对应主键，然后再使用主键在主索引上进行检索
  • 非聚簇索引(MyISAM),叶子节点存放的时数据记录的地址，主键索引和辅助索引搜索过程类似

• Hash索引：通过Hash算法，将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。目前使用Hash索引的数据库并不多，主要有Memory等。

  Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。

• Full-Text全文索引：同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

• R-Tree索引: 主要用于地理空间数据类型

  用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。

1. 物理存储角度

• 聚集索引（clustered index）
  
• 非聚集索引（non-clustered index），也叫辅助索引（secondary index）
  

1. 逻辑角度

• 主键索引：主键索引是一种特殊的唯一索引，不允许有空值
• 普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引

• 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

  当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

• 唯一索引或者非唯一索引

• 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

  创建索引的情况

1. 主键自动建立唯一索引
   
2. 频繁作为查询条件的字段
   
3. 查询中与其他表关联的字段，外键关系建立索引
   
4. 单键/组合索引的选择问题，高并发下倾向创建组合索引
   
5. 查询中排序的字段，排序字段通过索引访问大幅提高排序速度
   
6. 查询中统计或分组字段

   查询

   • mysql 的内连接、左连接、右连接有什么区别？
   • 什么是内连接、外连接、交叉连接、笛卡尔积呢？
   • count(*) 和 count(1)和count(列名)区别 ps：这道题说法有点多
   • MySQL中 in和 exists 的区别？
   • UNION和UNION ALL的区别?

count(*) 和 count(1)和count(列名)区别

count()函数是用来统计表中记录的一个函数，返回匹配条件的行数。

执行效果上：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
• count(1)包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

• 列名为主键，count(列名)会比count(1)快
• 列名不为主键，count(1)会比count(列名)快
• 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
• 如果有主键，则 select count（主键）的执行效率是最优的

• 如果表只有一个字段，则 select count(*) 最优。

  MySQL中 in和 exists 的区别？

• exists：exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录行时（无论记录行是的多少，只要能返回），条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为false

• in：in查询相当于多个or条件的叠加

  如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in

SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

UNION和UNION ALL的区别?

UNION和UNION ALL都是将两个结果集合并为一个，两个要联合的SQL语句 字段个数必须一样，而且字段类型要“相容”（一致）；

• UNION在进行表连接后会筛选掉重复的数据记录（效率较低），而UNION ALL则不会去掉重复的数据记录；
  

• UNION会按照字段的顺序进行排序，而UNION ALL只是简单的将两个结果合并就返回；

  join

  在 SQL 中，不仅实体对象存储在关系表中，对象之间的联系也存储在关系表中。因此，当我们想要获取这些相关的数据时，需要使用到另一个操作：连接查询（JOIN）。

• 内连接 inner join 返回两个表中满足连接条件的数据

• 左外连接 left outer join 返回左表中所有的数据；对于右表，返回满足连接条件的数据；如果没有就返回空值。

• 右外连接 right outer join 返回右表中所有的数据；对于左表，返回满足连接条件的数据；如果没有就返回空值。右外连接与左外连接可以互换，以下两者等价

  t1 RIGHT JOIN t2
  t2 LEFT JOIN t1

• 全外连接 full outer join 等价于左外连接加上右外连接，同时返回左表和右表中所有的数据；对于两个表中不满足连接条件的数据返回空值。

• 交叉连接 cross outer join 也叫笛卡尔积Cartesian Product，两个表的交叉连接相当于一个表的所有行和另一个表的所有行两两组合，结果的数量为两个表的行数相乘

事务

• 事务的隔离级别有哪些？MySQL的默认隔离级别是什么？
• 什么是幻读，脏读，不可重复读呢？
• MySQL事务的四大特性以及实现原理
• MVCC熟悉吗，它的底层原理？
• 事务是如何通过日志来实现的，说得越深入越好。
• 你知道MySQL 有多少种日志吗？

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

ACID 事务基本要素

• A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样
• C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏
• I (Isolation)隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰
• D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

  并发事务处理带来的问题

1. 更新丢失（Lost Update)：事务A和事务B选择同一行，然后基于最初选定的值更新该行时，由于两个事务都不知道彼此的存在，就会发生丢失更新问题
2. 脏读(Dirty Reads)：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
3. 不可重复读（Non-Repeatable Reads)：事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
4. 幻读（Phantom Reads)：幻读与不可重复读类似。它发生在一个事务A读取了几行数据，接着另一个并发事务B插入了一些数据时。在随后的查询中，事务A就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

幻读和不可重复读的区别：

• 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）

• 幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）

  并发事务问题处理方法

• 更新丢失通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。

• 脏读 、 不可重复读和幻读 ，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决：

  • 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。
  • 另一种是数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照 （Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

    事务隔离级别

    数据库事务的隔离级别有4种，由低到高分别为

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

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

查看当前数据库的事务隔离级别：

show variables like 'tx_isolation'

数据库的事务隔离越严格，并发副作用越小，但付出的代价就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行，这显然与并发是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对不可重复读和幻读并不敏感，可能更关心数据并发访问的能力。

MVCC多版本并发控制

MySQL的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是实现机制各不相同。
可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。
MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事物看到的数据都是一致的。
典型的MVCC实现方式，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

REPEATABLE READ（可重读）隔离级别下MVCC如何工作

• SELECT
  InnoDB会根据以下两个条件检查每行记录：
  • InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在要么是事务自身插入或者修改过的
    
  • 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除
    
• INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号
  
• DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识
  
• UPDATE：InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

保存这两个额外系统版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且也能保证只会读取到符合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

事务日志

InnoDB 使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。
事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机 IO。
InnoDB 假设使用常规磁盘，随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。
InnoDB 用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务。
InnoDB 使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。

事务日志可以帮助提高事务效率：

• 使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
• 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。
• 事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。
• 如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回到磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这一部分修改的数据。

目前来说，大多数存储引擎都是这样实现的，我们通常称之为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘。

事务的实现

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。

事务的实现就是如何实现ACID特性。

事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现 。
事务日志包括：重做日志redo和回滚日志undo

重做日志redo log

实现持久化和原子性
在innoDB的存储引擎中，事务日志通过重做(redo)日志和innoDB存储引擎的日志缓冲(InnoDB Log Buffer)实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是DBA们口中常说的日志先行(Write-Ahead Logging)。当事务提交之后，在Buffer Pool中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据redo log中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。
在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录Redo Log，通过顺序IO来改善性能。所有的事务共享redo log的存储空间，它们的Redo Log按语句的执行顺序，依次交替的记录在一起。

回滚日志undo log

实现一致性
undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。
Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）

二种日志均可以视为一种恢复操作，redo_log是恢复提交事务修改的页操作，而undo_log是回滚行记录到特定版本。二者记录的内容也不同，redo_log是物理日志，记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。

Mysql的日志类型

• 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
  
• 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
  
• 慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
  
• 二进制日志：记录对数据库执行更改的所有操作。
  
• 中继日志：中继日志也是二进制日志，用来给slave 库恢复
  

• 事务日志：重做日志redo和回滚日志undo
  

  Mysql对于分布式事务的支持

  分布式事务的实现方式有很多，既可以采用 InnoDB 提供的原生的事务支持，也可以采用消息队列来实现分布式事务的最终一致性。这里我们主要聊一下 InnoDB 对分布式事务的支持。
  一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。
  在MySQL中，使用分布式事务涉及一个或多个资源管理器和一个事务管理器。
  
  MySQL 的分布式事务模型。模型中分三块：应用程序（AP）、资源管理器（RM）、事务管理器（TM）:

• 应用程序：定义了事务的边界，指定需要做哪些事务；

• 资源管理器：提供了访问事务的方法，通常一个数据库就是一个资源管理器；
• 事务管理器：协调参与了全局事务中的各个事务。

mysql分布式事务采用两段式提交的方式(2PC):

• 第一阶段所有的事务节点开始准备，告诉事务管理器ready。
• 第二阶段事务管理器告诉每个节点是commit还是rollback。如果有一个节点失败，就需要全局的节点全部rollback，以此保障事务的原子性。

  锁机制

  • 数据库的乐观锁和悲观锁？
  • MySQL 中有哪几种锁，列举一下？
  • MySQL中InnoDB引擎的行锁是怎么实现的？
  • MySQL 间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决,MySQL 有没有提供什么机制去解决死锁
  • select for update有什么含义，会锁表还是锁行还是其他
  • MySQL 遇到过死锁问题吗，你是如何解决的？

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

锁的分类

从对数据操作的类型分类：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
  
• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分类：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；
  
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；
  
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
  

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用**，如一些在线事务处理（OLTP）系统。

| | 行锁 | 表锁 | 页锁 | | MyISAM |
| √ |
| | —- | —- | —- | —- | | BDB |
| √ | √ | | InnoDB | √ | √ |
| | Memory |
| √ |
|

MyISAM表锁

MyISAM 的表锁有两种模式：

• 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。其他线程的读、 写操作都会等待，直到锁被释放为止。
默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

InnoDB行锁

InnoDB 实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
• 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。

加锁机制

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题
乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式
悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

锁模式（InnoDB有三种行锁的算法）

• 记录锁(Record Locks)：单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；

  -- 它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行
  SELECT * FROM table WHERE id = 1 FOR UPDATE;

  在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

  -- id 列为主键列或唯一索引列
  UPDATE SET age = 50 WHERE id = 1;

• 间隙锁（Gap Locks）：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做间隙。

InnoDB 也会对这个间隙加锁，这种锁机制就是所谓的间隙锁。

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;

即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。
GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况

• 临键锁(Next-key Locks)：临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。)

Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

for update

for update 仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过for update语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

假设有个表单 products ，里面有id跟name二个栏位，id是主键。

• 明确指定主键，并且有此笔资料，row lock

  SELECT * FROM products WHERE id='3' FOR UPDATE;
  SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

• 明确指定主键，若查无此笔资料，无lock

  SELECT * FROM products WHERE id='-1' FOR UPDATE;

• 无主键，table lock

  SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

• 主键不明确，table lock

  SELECT * FROM products WHERE id<>'3' FOR UPDATE;

• 主键不明确，table lock

  SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

  • FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
  • 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

死锁

• 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
• 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
• 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。

检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。

死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。

外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决

死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。

MyISAM避免死锁：

• 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。

InnoDB避免死锁：

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
• 通过SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
• 改变事务隔离级别

如果出现死锁，可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

调优

• 日常工作中你是怎么优化SQL的？
• SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？
• 如何写sql能够有效的使用到复合索引？
• 一条sql执行过长的时间，你如何优化，从哪些方面入手？
• 什么是最左前缀原则？什么是最左匹配原则？
• 查询中哪些情况不会使用索引？

影响mysql的性能因素

• 业务需求对MySQL的影响(合适合度)
  
• 存储定位对MySQL的影响
  
  • 系统各种配置及规则数据
  • 活跃用户的基本信息数据
  • 活跃用户的个性化定制信息数据
  • 准实时的统计信息数据
  • 其他一些访问频繁但变更较少的数据
  • 二进制多媒体数据
  • 流水队列数据
  • 超大文本数据
  • 不适合放进MySQL的数据
  • 需要放进缓存的数据
• Schema设计对系统的性能影响
  
  • 尽量减少对数据库访问的请求
  • 尽量减少无用数据的查询请求

• 硬件环境对系统性能的影响
  

  性能分析**

  mysql常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
  

• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  

• 服务器硬件的性能瓶颈：top，free，iostat 和 vmstat来查看系统的性能状态
  

  性能下降SQL慢 执行时间长 等待时间长 原因分析

• 查询语句写的烂

• 索引失效（单值、复合）
• 关联查询太多join（设计缺陷或不得已的需求）

• 服务器调优及各个参数设置（缓冲、线程数等）

  慢查询日志

  MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。

• long_query_time 的默认值为10，意思是运行10秒以上的语句

• 默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数开启

在生产环境中，如果手工分析日志，查找、分析SQL，还是比较费劲的，所以MySQL提供了日志分析工具mysqldumpslow。

• 得到返回记录集最多的10个SQL
  mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log
  
• 得到访问次数最多的10个SQL
  mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log
  
• 得到按照时间排序的前10条里面含有左连接的查询语句
  mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log
  
• 也可以和管道配合使用
  mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more
  

  索引优化

1. 全值匹配我最爱
2. 最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
6. is null ,is not null 也无法使用索引
7. like “xxxx%” 是可以用到索引的，like “%xxxx” 则不行(like “%xxx%” 同理)。like以通配符开头(‘%abc…’)索引失效会变成全表扫描的操作，
8. 字符串不加单引号索引失效
9. 少用or，用它来连接时会索引失效
10. <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描

一般性建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
  
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
  
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
  
• 少用Hint强制索引
  

查询优化

永远小表驱动大表（小的数据集驱动大的数据集）

select * from A where id in (select id from B)`等价于
#等价于
select id from B
select * from A where A.id=B.id

当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists

select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`

当 A 表的数据集小于B表的数据集时，用 exists优于用 in

A表与B表的ID字段应建立索引。

order by 关键字优化

• order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序
  
• MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低；
  
• ORDER BY 满足两种情况，会使用Index方式排序；①ORDER BY语句使用索引最左前列 ②使用where子句与ORDER BY子句条件列组合满足索引最左前列
  
• 尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀
  
• 如果不在索引列上，filesort 有两种算法，mysql就要启动双路排序和单路排序
  
  • 双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据
  • 单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序
• 优化策略
  
  • 增大sort_buffer_size参数的设置
  • 增大max_lencth_for_sort_data参数的设置

GROUP BY关键字优化

• group by实质是先排序后进行分组，遵照索引建的最佳左前缀
• 当无法使用索引列，增大 max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置
• where高于having，能写在where限定的条件就不要去having限定了

数据类型优化

MySQL 支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

• 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。
  简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。
  
• 尽量避免NULL：通常情况下最好指定列为NOT NULL
  

  知识链接

1. MySQL 三万字精华总结 + 面试100 问，吊打面试官绰绰有余（收藏系列）
2. 图解 SQL