Mysql

0.数据类型

• 整型
  TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

  INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

• 浮点数
  FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

  FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

• 字符串
  主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

  VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

  在进行存储和检索时，会保留 VARCHAR 末尾的空格，而会删除 CHAR 末尾的空格。

• 时间和日期
  MySQL 提供了两种相似的日期时间类型：DATETIME 和 TIMESTAMP。

1. DATETIME
   能够保存从 1000 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。它与时区无关。默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

2. TIMESTAMP
   和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。

   它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

   MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

   默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

   应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

1.SQL注入

攻击者利用发送给SQL服务器的输入参数构造可执行的SQL代码（可加入到get请求、post请求、http头信息、cookie中），传递到服务器解析并执行的一种攻击手法。

分两类：
1.数字注入

在浏览器地址栏输入：learn.me/sql/article.php?id=1，这是一个get型接口，发送这个请求相当于调用一个查询语句：

$sql="SELECT\ast FROMarticleWHEREid=",$id
正常情况下，应该返回一个id=1的文章信息。那么，如果在浏览器地址栏输入：learn.me/sql/article.php?id=-1 OR 1 =1，这就是一个SQL注入攻击了，可能会返回所有文章的相关信息。为什么会这样呢？

这是因为，id = -1永远是false，1=1永远是true，所有整个where语句永远是ture，所以where条件相当于没有加where条件，那么查询的结果相当于整张表的内容

2.字符串注入

有这样一个用户登录场景：登录界面包括用户名和密码输入框，以及提交按钮。输入用户名和密码，提交。

这是一个post请求，登录时调用接口learn.me/sql/login.html，首先连接数据库，然后后台对post请求参数中携带的用户名、密码进行参数校验，即sql的查询过程。假设正确的用户名和密码为user和pwd123，输入正确的用户名和密码、提交，相当于调用了以下的SQL语句：

SELECT * FROM user WHERE username = ‘user’ ADN password = ‘pwd123’
由于用户名和密码都是字符串，SQL注入方法即把参数携带的数据变成mysql中注释的字符串。mysql中有2种注释的方法：
‘–’’，#’：’#'后所有的字符串都会被当成注释来处理

用户名输入：user’#（单引号闭合user左边的单引号），密码随意输入，如：111，然后点击提交按钮。等价于SQL语句：

SELECT * FROM user WHERE username = ‘user’#'ADN password = ‘111’
'#'后面都被注释掉了，相当于：

SELECT * FROM user WHERE username = ‘user’
可能输入一个错误的密码或者不输入密码就可登录用户名为’user’的账号，这是十分危险的事情。

SQL注入的预防方法：

• 分级管理：对用户进行分级管理，严格控制用户的权限，对于普通用户，禁止给予数据库建立、删除、修改等相关权限，只有系统管理员才具有增、删、改、查的权限。
• 参数过滤：在获取用户输入提交的参数时，对于单引号、双引号、冒号等字符进行转换或者过滤，从而有效防止SQL注入。
• 数据库信息加密。

2.Mysql存储引擎

引擎主要有MyisAm、Innodb、memory。

• MyisAm：不支持事务，支持锁结构但是仅支持表锁，存储速度快，但是性能差。
• Innodb：mysql默认的存储引擎，支持事务，使用聚集索引，索引结构为B+树，支持锁机制并同时支持表锁和行锁。
• Memory：使用内存创建表，每个memory对应一个磁盘文件，因为数据存在于内存中，所以访问非常快，并且默认使用hash索引，也可以是用B树的索引结构，hash索引的优点是单个查询非常快，但是不支持like、范围这种方式查询

3.Mysql的索引

索引是对数据库表中一个或多个列的值进行排序的结构，也是一张表，该表保存了主键与索引字段，并指向实体表的记录。可以快速的进行数据的查找。索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。

索引类型

普通索引（单列索引）
cREATE INDEX index_name ON table_name(col_name);使用表中的普通列构建出来的索引，没有任何限制
复合索引（组合索引）
create index index_name on table_name(col_name1,col_name2,…);
复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。这多个列中的值不允许有空值
唯一索引： UNIQUE
表明此索引的每一个索引值只对应唯一的数据记录，但允许存在空值（只允许存在一条空值），对于单列唯一性索引，这保证单列不包含重复的值。对于多列唯一性索引，保证多个值的组合不重复。
CREATE UNIQUE INDEX index_name ON table_name (column_name)

主键索引： primary key
数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，对数据的快速访问。
ALTER TABLE table_name ADD PRIMARY KEY ( column )

全文索引
用于全文搜索，只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列。
ALTER TABLE table_name ADD FULLTEXT ( column )
通配符%做开头作查询时，索引会失效，此时用全文索引。

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

索引的数据结构

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，选择B+Tree索引。

什么情况下会使用索引

第一：表的数据量比较大，至少是五位数以上的数据量
第二：MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引，所以如果是其他场景，你建立了索引也没有用的
第三：查找符合where条件的记录
第四：进行sort或group操作时

某些时候的LIKE才会使用索引，原因是以通配符%和_开头作查询时，MySQL不会使用索引。例如
SELECT * FROM mytable WHERE username like’admin%’

索引使用场景

适合用索引的场景
1、主键
主键一般为id等具有唯一性标识的字段，需要频繁查找、连接。InnoDB中会自动为主键建立聚集索引，即使没有定义主键，也会自动生成一个隐藏主键建立索引；MyISAM中不会自动生成主键。建议给每张表指定主键。
2、频繁作为查询条件的字段
索引是以空间换时间的，某字段如果频繁作为查询条件，建议建立索引
3、查询中作为与其他表关联的字段（外键）
4、查询中常作为查询排序条件的字段
这里要注意，order by的字段出现在where条件中才能使用索引，否则索引失效。
5、查询中的统计、分组字段
group by和union也属于需要排序的操作，这里也要注意字段出现在where条件中才能使用索引，否则索引会失效。
6.高并发下倾向使用组合索引

不适合用索引的场景
1、频繁更新的字段、表
如果字段添加了索引，在更新时不仅要更新数据本身，还要维护其索引，如果频繁更新会带来很多额外开销。再者，如果一个表频繁进行增删改操作，也不适合索引
2、很少作为查询条件的字段、很少作为查询条件的字段
3、表的记录不多、表的记录不多一般数据量达到300万-500万时考虑建立索引。
4、数据重复且分布平均的字段、数据重复且分布平均的字段由于大量的重复，索引对性能的提升很有限，比如年龄、性别…

索引原则

1、选择唯一性索引，唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
2、为经常需要排序、分组和联合操作的字段建立索引：
3、为常作为查询条件的字段建立索引。
4、限制索引的数目：越多的索引，会使更新表变得很浪费时间。尽量使用数据量少的索引
6、如果索引的值很长，那么查询的速度会受到影响。尽量使用前缀来索引
7、如果索引字段的值很长，最好使用值的前缀来索引。
8、删除不再使用或者很少使用的索引
9、最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
10、尽量选择区分度高的列作为索引，区分度的公式是表示字段不重复的比例
11、索引列不能参与计算，保持列“干净”：带函数的查询不参与索引。
12、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
13.对于定义为text、image和bit的数据类型的列不要建立索引。

索引失效的常见场景

1.使用 OR 关键字会导致索引失效，不过如果要想使用OR 又不想让索引失效，那就得需要为or条件中的每个列都建立索引。
2.联合索引如果不遵循最左前缀原则，那么索引也将失效
3.使用模糊查询的时候以%开头也会导致索引失效
4.索引列如果使用了隐式转换也会导致索引失效
5.如果字段基数小也可能会导致索引失效，具体在本文的上面部分已经详细解释了，也就是MySQL 查询优化器导致的

索引优化

1. 独立的列
   在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
   SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;查询不能使用 actor_id 列的索引

2. 多列索引
   在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。

3. 索引列的顺序
   让选择性最强的索引列放在前面。索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。
   ELECT COUNT(DISTINCT staff_id)/COUNT() AS staff_id_selectivity,
   COUNT(DISTINCT customer_id)/COUNT() AS customer_id_selectivity,
   COUNT(*)
   FROM payment;
   结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

4. 前缀索引
   对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。前缀长度的选取需要根据索引选择性来确定。

5. 覆盖索引
   索引包含所有需要查询的字段的值。具有以下优点：
   a.索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
   b.一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
   c.对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

索引的优缺点

优点

• 大大减少了服务器需要扫描的数据行数。

• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。

• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

缺点
时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

如何选择索引

• 对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效；

• 对于中到大型的表，索引就非常有效；

• 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

百万级别或以上的数据如何删除

（1）可以先删除索引（此时大概耗时三分多钟）

（2）然后删除数据（此过程需要不到两分钟）

（3）删除完成后重新创建索引。

（4）与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

什么是最左前缀原则

（1），在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

（2）最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（3）=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

前缀索引

index(field(N))，使用字段值的前N个字符建立索引，默认是使用字段的全部内容建立索引。

前提：前缀的标识度高。比如密码就适合建立前缀索引，因为密码几乎各不相同。

索引算法有哪些

索引算法有 BTree算法和Hash算法

BTree算法

BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量， 例如：

– 只要它的查询条件是一个不以通配符开头的常量selectfromuserwhere name like’jack%’;
– 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： selectfromuserwhere name like’%jack’;

Hash算法

Hash Hash索引只能用于对等比较，例如=,<=>（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。

聚簇索引与非聚簇索引

聚簇索引
聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

聚簇索引的优缺点

优点：
　　　　1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快

2.聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：

1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
辅助索引（非聚簇索引）
　　在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

4 B+ Tree 原理

数据结构

B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层。

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现，它具有 B Tree 的平衡性，并且通过顺序访问指针来提高区间查询的性能。

在 B+ Tree 中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 key i和key i+1；且不为 null，则该指针指向节点的所有 key 大于等于 key i且小于等于 keyi+1。

操作

进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。

插入删除操作会破坏平衡树的平衡性，因此在进行插入删除操作之后，需要对树进行分裂、合并、旋转等操作来维护平衡性。

B树与B+树范围查询

B树与B+树范围查询

与红黑树的比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，这是因为使用 B+ 树访问磁盘数据有更高的性能。

（一）B+ 树有更低的树高

平衡树的树高 O(h)=O(log_dN)O(h)=O(log
d
​
N)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多。

（二）磁盘访问原理

操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。

如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。

（三）磁盘预读特性

为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

与B Tree比较

1.B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
2. 由于B+树的叶子节点的数据都是使用链表连接起来的，而且他们在磁盘里是顺序存储的，所以当读到某个值的时候，磁盘预读原理就会提前把这些数据都读进内存，使得范围查询和排序都很快，而B-树每个节点 key 和 data 在一起，则无法区间查找。
3. 由于B树的节点都存了key和data，而B+树只有叶子节点存data，非叶子节点都只是索引值，没有实际的数据，这就时B+树在一次IO里面，能读出的索引值更多。从而减少查询时候需要的IO次数！

与Hash索引区别

首先要知道Hash索引和B+树索引的底层实现原理:

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.

不同:

• hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.

因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.

• hash索引不支持使用索引进行排序,原理同上.
• hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性.
• hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
• hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.

因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.

数据库为什么使用B+树而不是B树

（1）B树只适合随机检索，而B+树同时支持随机检索和顺序检索；

（2）B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；

（3）B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。

（4）B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。

（5）增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

5.物理分页和逻辑分页

物理分页就是数据库本身提供了分页方式，如MySQL的limit。

逻辑分页依赖的是程序员编写的代码。数据库返回的不是分页结果，而是全部数据，然后再由程序员通过代码获取分页数据，常用的操作是一次性从数据库中查询出全部数据并存储到List集合中，因为List集合有序，再根据索引获取指定范围的数据。

物理分页每次都访问数据库，逻辑分页只访问一次数据库，物理分页对数据库造成的负担大。

逻辑分页一次性将数据读取到内存，占用了较大的内容空间，物理分页每次只读取一部分数据，占用内存空间较小。

逻辑分页一次性将数据读取到内存，数据发生改变，数据库的最新状态不能实时反映到操作中，实时性差。物理分页每次需要数据时都访问数据库，能够获取数据库的最新状态，实时性强。

逻辑分页主要用于数据量不大、数据稳定的场合，物理分页主要用于数据量较大、更新频繁的场合。

6.数据库三大范式

在设计数据库时，为了使其冗余较小、结构合理，设计数据库时必须遵循一定的规则。在关系型数据库中，这种规则被称为范式。
第一范式：要求一张表中的数据每一列都是不可分割的原子项数据

第二范式：消除部分依赖，要求一张表中的每一列都完全依赖于主键（针对于组合主键），也就是不会出现某一列只和部分主键相关

第三范式：消除传递依赖，要求一张表中的每一列都和主键是直接依赖的，不是间接依赖。

7.mysql优化

7.1 数据类型优化

选择合适的数据类型对于节省内存和提升性能意义重大

• 如果能够正常存储数据那么一般使用更小的数据类型。
• 尽量避免null值，null值可以是任何数据类型的默认值，但是null使得索引、索引统计和值的比较都变得复杂。
• 整数类型有可选的unsigned属性，如果我们的数值无需负数的话那么推荐使用这种类型，举例来说TINYINT UNSIGNED与TINYINT的存储范围都是256但是前者是0_{255，后者是-128}127。
• Varchar与char。它们的存储方式与存储引擎相关，下面以InnoDB或MyISAM为例。
  Varchar长度可变一般来说是节省空间的，但是如果使用ROW_FORMAT=FIXED参数创建表的话那么varchar也是固定的长度。Varchar有一个特点就是需要在存储空间末尾开辟一到两个字节记录字符串的长度。当字符串长度的方差大的时候（比如说使用了UTF-8这样的字符集）那么推荐使用varchar。
• Char是定长的数据类型，非常适合存储定长的数据比如说MD5码。对于非常短的列也有非常高的效率，比如说表示一个只有true/false的值char(1)的效率要高于varchar(1)因为varchar(1)后面会附加一个记录长度的额外字节。

7.2 查询优化

• 优化数据访问
  减少请求无用的数据，比如说我们查询了100条数据但是最后只在得到的结果中取了其中的10条（在应用程序中完成的）。这样不仅仅是耗费了大量的计算机资源也耗费了大量的网络资源。所以在这种情况下我们尽早使用limit语句来过滤或者是使用索引来过滤。

• 重构查询方式
  一般情况下我们倾向于使用尽可能少的SQL语句去完成查询操作，这样能够减少发送查询的次数。但是有时候我们需要将大的查询拆分成一些小的查询。比如说在删除大量的数据时数据库（假设数据库支持锁）会将大量的数据锁住这样的话其它的语句就会在队列中等待。这时候将删除的任务分为几波就可以更加高效。

• 有时候我们也需要将查询的连接放在应用中，将一个连接查询拆分成几个小的查询。这样带来的的好处就是避免了几张表连接时占用的大量内存，而且更加有利于利用缓存（如果关联的表中某个表发生了改变将无法使用缓存，但是当拆开之后某个表发生了细微的变换之后还可以使用缓存）。最后拆开之后还可以减少锁的竞争。

• 优化特定类型的查询
  1.当统计行数的时候尽量使用count(*)，当我们以某一列为统计指标的时候，如果碰到null的时候就会不把这一行统计在内。而在MySQL的底层也会将count(col)优化成count(*)。
  2.要尽可能的保证GROUP BY和ORDERBY语句中只包含一个列，这样MySQL才有可能使用索引来优化查询。
  3.优化LIMIT分页，在一般情况下我们会使用LIMIT加上偏移量来搞定，这时如果有合适的索引的话效率一般不会错，但是如果只是一味的使用LIMIT语句的话，比如有1000,20这样的语句的话前面的需要查询1020条记录然后抛弃前面的1000条数据，这个代价是非常高的。一般的做法是尽可能的使用覆盖索引（里面可以包含我们想要的值）做延迟关联这样的效率就会很高。

7.3慢查询日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。
慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？
所以优化也是针对这三个方向来的，

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

7.4 explain

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段

select_type : 查询类型，有简单查询、联合查询、子查询等
key : 使用的索引
rows : 扫描的行数

10列信息

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,
答案

不同type性能区别

由最差到最好
a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
b.index：Full Index Scan，index与ALL区别为index类型只遍历索引树
c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
e.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
g.NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

如何检测MySQL是否命中索引？

用explain这个命令来查看SQL语句的执行计划，查看该SQL语句有没有使用上了索引,key列显示MySQL实际决定使用的键（索引）,如果没有选择索引，键是NULL。

7.4 大表数据查询，怎么优化

优化shema、sql语句+索引；

第二加缓存，memcached, redis；

主从复制，读写分离；

垂直拆分，根据模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

7.5.超大分页怎么处理?

超大的分页一般从两个方向上来解决.

• 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.
• 从需求的角度减少这种请求….主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种。

8. 事务的ACID属性

1. 原子性（atomicity)
   一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性

2. 一致性（consistency)
   事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。
   如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态

3. 隔离性（isolation）
   事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不能不被其他事务干扰。不同的事务并发操作相同的数据时，每个事务都有各自完成的数据空间，即一个事务内部的操作及使用的数据对其他并发事务时隔离的，并发执行的各个事务之间不能相互干扰。
   在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同，分别是：未授权读取，授权读取，可重复读取和串行化

4. 持久性（durability）
   一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。–即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态

ACID 之间的关系

• 只有满足一致性，事务的执行结果才是正确的。
• 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
• 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
• 事务满足持久化是为了能应对系统崩溃的情况。

AUTOCOMMIT

MySQL 默认采用自动提交模式。也就是说，如果不显式使用 START TRANSACTION 语句来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交。

9.脏读、幻读（并发一致性问题）

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。

• 更新丢失：多个事务同时更新同一行的数据，并且不知道其他事务的存在，导致最后有的更新失效（丢失）了。

• 脏读：指在不同的事务下，当前事务可以读到另外事务未提交的数据。例如：A事务修改一个数据但未提交，B事务随后读取这个数据。如果A事务 撤销了这次修改，那么B事务 读取的数据是脏数据。

• 不可重复读：在一次的事务操作中，对某一数据进行了两次（及以上）的读操作，此时有另一个事务在两次读操作的中间修改了数据，造成了两次读取的数据不同，即不可以重复读。

• 幻读：在一次的事务操作中，先读取了几行数据后，另一个事务又增加或删除了数据，在此之后，此事务又去读取数据，发现数据凭空生成或消失，跟幻觉一样，即幻读。

总结:更新丢失为多个事务几乎同时修改数据出现的问题；脏读为一个事务在修改数据，另一个事务读取(SELECT)事务出现的问题；不可重复读为一个事务在查询数据，中间有另一个事务修改(UPDATE)了数据的问题；幻读为一个事务在查询数据，另一个事务在插入或删除(INSERT or DELETE)数据的问题。
解决方法：
解决脏读，不可重复读使用MVCC来实现
解决幻读，MVCC+Next-Key Locks

10.事务的隔离级别了解过吗？

1、读未提交（Read Uncommited），该隔离级别允许脏读取，其隔离级别最低；比如事务A和事务B同时进行，事务A在整个执行阶段，会将某数据的值从1开始一直加到10，然后进行事务提交，此时，事务B能够看到这个数据项在事务A操作过程中的所有中间值（如1变成2，2变成3等），而对这一系列的中间值的读取就是未授权读取

2、授权读取也称为已提交读（Read Commited），授权读取只允许获取已经提交的数据。比如事务A和事务B同时进行，事务A进行+1操作，此时，事务B无法看到这个数据项在事务A操作过程中的所有中间值，只能看到最终的10。另外，如果说有一个事务C，和事务A进行非常类似的操作，只是事务C是将数据项从10加到20，此时事务B也同样可以读取到20，即授权读取允许不可重复读取。

3、可重复读（Repeatable Read)（默认隔离级别）

就是保证在事务处理过程中，多次读取同一个数据时，其值都和事务开始时刻是一致的，因此该事务级别禁止不可重复读取和脏读取，但是有可能出现幻影数据。所谓幻影数据，就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。在上面的例子中，可重复读取隔离级别能够保证事务B在第一次事务操作过程中，始终对数据项读取到1，但是在下一次事务操作中，即使事务B（注意，事务名字虽然相同，但是指的是另一个事务操作）采用同样的查询方式，就可能读取到10或20；

4、串行化

是最严格的事务隔离级别，它要求所有事务被串行执行，即事务只能一个接一个的进行处理，不能并发执行。

参考

11. mysql支持事务吗

在缺省模式下，MYSQL是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，mysql是不支持事务的。

但是如果你的MYSQL表类型是使用InnoDB Tables 或 BDB tables的话，你的MYSQL就可以使用事务处理,使用SET AUTOCOMMIT=0就可以使MYSQL允许在非autocommit模式，
在非autocommit模式下，你必须使用COMMIT来提交你的更改，或者用ROLLBACK来回滚你的更改。

12、Myql 中的事务回滚机制概述

13.完整性约束包括哪些

1.实体完整性：若属性a是基本关系r的主属性，则属性a不能取空值。
2.域的完整性：表中的列必须满足某种特定的数据类型或约束。其中约束又包括取值范围、精度等规定。用Check来实现约束
3.参照完整性：就是定义外码与主码之间的引用规则。用PK、FK、表级触发器来实现。原理规则就是若属性(或属性组)f是基本关系r的外码，它与基本关系s的主码ks相对应(基本关系r和s不一定是不同的关系)，则对于r中每个元组在f上的值必须为：或者取空值(f的每个属性值均为空值)或者等于s中某个元组的主码值。

4.用户定义完整性：用户定义的完整性是针对某一具体关系数据库的约束条件，反映某一具体应用所涉及的数据必须满足的语义要求。关系模型应提供定义和检验这类完整性的机制，以便用统一的系统的方法处理它们，而不要由应用程序承担这一功能。它是一些业务规则，用存储过程和触发器来实现

14. MySQL中锁的种类：

全局锁

局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是Flush tables with read lock。当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句

表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）
表锁的语法是lock tables tablename read/write。可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象.

如果在某个线程A中执行lock tables t1 read,t2 wirte;这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许

在MySQL5.5版本引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁

读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁

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

• 共享锁（S锁、读锁）： 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。即多个客户可以同时读取同一个资源，但不允许其他客户修改。

• 排他锁（X锁、写锁)： 允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的读锁和写锁。写锁是排他的，写锁会阻塞其他的写锁和读锁。

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

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

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

间隙锁GAP

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

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

例子：假如emp表中只有101条记录，其id的值分别是1,2,…,100,101

Select * from emp where id > 100 for update;

上面是一个范围查询，InnoDB不仅会对符合条件的id值为101的记录加锁，也会对id大于101（这些记录并不存在）的“间隙”加锁

InnoDB使用间隙锁的目的有2个：

为了防止幻读(上面也说了，Repeatable read隔离级别下再通过GAP锁即可避免了幻读)

满足恢复和复制的需要：MySQL的恢复机制要求在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

乐观锁、悲观锁

乐观锁(Optimistic Lock)：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。 乐观锁不能解决脏读的问题。
乐观锁, 每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

悲观锁(Pessimistic Lock)：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。
悲观锁，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

优化锁性能的建议

• 尽量使用较低的隔离级别；
• 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会
• 选择合理的事务大小，小事务发生锁冲突的几率也更小
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响
• 不要申请超过实际需要的锁级别
• 除非必须，查询时不要显示加锁。 MySQL的MVCC可以实现事务中的查询不用加锁，优化事务性能；MVCC只在COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能

15.死锁

死锁产生

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

检测死锁

数据库系统实现了各种死锁检测和死锁超时的机制。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 INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

InnoDB加锁方法（啥语句加啥锁）

• 意向锁是 InnoDB 自动加的， 不需用户干预。
• 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB
  会自动给涉及数据集加排他锁（X)；
• 对于普通 SELECT 语句，InnoDB 不会加任何锁；
  事务可以通过以下语句显式给记录集加共享锁或排他锁：
• 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
• 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

参考：锁1
参考 锁2

15 多版本并发控制MVCC

多版本并发控制是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

基本思想

加锁能解决多个事务同时执行时出现的并发一致性问题。在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。读写锁中读和写操作仍然是互斥的，而 MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系。

在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。

脏读和不可重复读最根本的原因是事务读取到其它事务未提交的修改。在事务进行读取操作时，为了解决脏读和不可重复读问题，MVCC 规定只能读取已经提交的快照。

版本号

系统版本号 SYS_ID：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
事务版本号 TRX_ID ：事务开始时的系统版本号。

Undo 日志

MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。

ReadView

MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。
在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：

• TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。

• TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。

• TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：

  • 提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
  • 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。

在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。

快照读与当前读

1. 快照读
   MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。
2. 当前读
   对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

15. Next-Key Locks、record locks、gap locks

• Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。
  MVCC 不能解决幻影读问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。

  并且它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间，例如一个索引包含以下值：10, 11, 13, and 20，那么就需要锁定以下区间：
  (-∞, 10]
  (10, 11]
  (11, 13]
  (13, 20]
  (20, +∞)

• Record Locks
  锁定一个记录上的索引，而不是记录本身。如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。

• Gap Locks
  锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE，其它事务就不能在 t.c 中插入 15。

16.order by 与 group by

order by:用来对数据库的一组数据进行排序 desc：降， asc：升序
select * from aaa order by num desc;
group by：对“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。
select lei,SUM(NUM) AS num from aaa group lei
两个连用：先分组 再排序
select lei，SUM（num）as num from aaa group by lei order by num desc。

同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

1.执行where xx对全表数据做筛选，返回第1个结果集。

2.针对第1个结果集使用group by分组，返回第2个结果集。

3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。

4.针对第3个结集执行having xx进行筛选，返回第4个结果集。

5.针对第4个结果集排序。

17. mysql 日志文件

MySQL中有六种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系。

重做日志（redo log）

1、作用

确保事务的持久性。

防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

2、内容

物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

3、什么时候产生

事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。

4、什么时候释放

当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

5、对应的物理文件

默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2

innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。

innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2

关于文件的大小和数量，由一下两个参数配置

innodb_log_file_size 重做日志文件的大小。

innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

回滚日志（undo log）

1、作用

保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

2、内容

逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

3、什么时候产生

事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

4、什么时候释放

当事务提交之后，undo log并不能立马被删除，

而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

5、对应的物理文件

MySQL5.6之前，undo表空间位于共享表空间的回滚段中，共享表空间的默认的名称是ibdata，位于数据文件目录中。

MySQL5.6之后，undo表空间可以配置成独立的文件，但是提前需要在配置文件中配置，完成数据库初始化后生效且不可改变undo log文件的个数

如果初始化数据库之前没有进行相关配置，那么就无法配置成独立的表空间了。

关于MySQL5.7之后的独立undo 表空间配置参数如下

innodb_undo_directory = /data/undospace/ –undo独立表空间的存放目录

innodb_undo_logs = 128 –回滚段为128KB

innodb_undo_tablespaces = 4 –指定有4个undo log文件

如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。

二进制日志（binlog）

1、作用

用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步；

用于数据库的基于时间点的还原；

2、内容

逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。

但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，

也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

在使用mysqlbinlog解析binlog之后一些都会真相大白。

因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

3、什么时候产生

事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。

这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。

这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

4、什么时候释放

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

5、对应的物理文件

配置文件的路径为log_bin_basename，binlog日志文件按照指定大小，当日志文件达到指定的最大的大小之后，进行滚动更新，生成新的日志文件。

对于每个binlog日志文件，通过一个统一的index文件来组织。

一般查询 慢查询 中继日志

一般查询日志――用于记录select查询语句的日志。general_log、general_log_file 默认关闭，建议关闭。

慢查询日志――log-slow-queries记录所有超过long_query_time时间的SQL语句，

中继日志――从主服务器的二进制文件中复制的事件，并保存为二进制文件，格式和二进制日志一样。

18 sql语句执行过程

当向MySQL发送一个请求的时候，MySQL到底做了什么：

• 客户端发送一条查询给服务器。
• 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
• 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。
• MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询。
• 将结果返回给客户端。
  详细见
  详细见

19. 内连接 外连接

内连接：（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

外联接：外联接可以是左外联接、右外联接或全外联接。
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
1）LEFT JOIN或LEFT OUTER JOIN
左外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
全外联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

交叉联接 ：交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积

参考：
索引
聚集索引与非聚集索引的总结
物理分页与逻辑分页