周陆军博客

Appearance

On this page

MYSQL详解 ​

架构 ​

  • 连接层:连接、认证、安全

    客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

  • 服务层:解析、分析、优化

    完成大部分的核心服务功能，包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等

  • 引擎层

    存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

    一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求：

    • MyISAM：如果应用程序通常以检索为主，只有少量的插入、更新和删除操作，并且对事物的完整性、并发程度不是很高的话，通常建议选择 MyISAM 存储引擎。
    • InnoDB：如果使用到外键、需要并发程度较高，数据一致性要求较高，那么通常选择 InnoDB 引擎，一般互联网大厂对并发和数据完整性要求较高，所以一般都使用 InnoDB 存储引擎。
    • BDB
    • MEMORY：MEMORY 存储引擎将所有数据保存在内存中，在需要快速定位下能够提供及其迅速的访问。MEMORY 通常用于更新不太频繁的小表，用于快速访问取得结果。
    • MERGE：MERGE 的内部是使用 MyISAM 表，MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上， 可以有效地改善 MERGE 表的访问效率。
    • EXAMPLE
    • NDB Cluster
    • ARCHIVE
    • CSV
    • BLACKHOLE
    • FEDERATED

    查看与设置

    sql
    -- 查看支持的存储引擎
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;

    MyISAM与InnoDB区别

    • InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
    • InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
    • InnoDB 是聚簇索引，MyISAM 是非聚簇索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
    • InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
    • InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 * 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丢失。

    哪个存储引擎执行 select count(*) 更快，为什么?

    • 在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
    • 在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。

  • 存储层:磁盘储存

    将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互

  • 功能

    • 并发
    • 支持事务
    • 完整性约束
    • 物理存储
    • 支持索引
    • 性能帮助

数据类型 ​

  • 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
  • 浮点数类型：FLOAT、DOUBLE、DECIMAL
  • 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
  • 日期类型：Date、DateTime、TimeStamp、Time、Year
  • 其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

CHAR和VARCHAR的区别？ ​

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

  • char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。 存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间

  • 相同点：

    • char(n)，varchar(n)中的n都代表字符的个数
    • 超过char，varchar最大长度n的限制后，字符串会被截断。

  • 不同点：

    • char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。因为varchar保存数据时除了要保存字符串之外还会加一个字节来记录长度（如果列声明长度大于255则使用两个字节来保存长度）。
    • 能存储的最大空间限制不一样：char的存储上限为255字节。
    • char在存储时会截断尾部的空格，而varchar不会。

  • char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。

  • 在 MySQL 中，不同的存储引擎对 CHAR 和 VARCHAR 的使用原则也有不同

    • MyISAM：建议使用固定长度的数据列替代可变长度的数据列，也就是 CHAR
    • MEMORY：使用固定长度进行处理、CHAR 和 VARCHAR 都会被当作 CHAR 处理
    • InnoDB：建议使用 VARCHAR 类型

列的字符串类型可以是什么？ ​

字符串类型是：SET、BLOB、ENUM、CHAR、TEXT、VARCHAR

BLOB和TEXT的区别？ ​

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

BLOB和TEXT如何提高性能 ​

  • 使用 OPTIMIZE TABLE 功能对表进行碎片整理。

  • 使用合成索引

    合成索引就是根据大文本（BLOB 和 TEXT）字段的内容建立一个散列值，把这个值存在对应列中，这样就能够根据散列值查找到对应的数据行。一般使用散列算法比如 md5() 和 SHA1() ，如果散列算法生成的字符串带有尾部空格，就不要把它们存在 CHAR 和 VARCHAR 中

  • 非必要的时候不要检索 BLOB 和 TEXT 索引

  • 把 BLOB 或 TEXT 列分离到单独的表中。

浮点数和定点数的选择 ​

浮点数指的就是 float 和 double，定点数指的是 decimal，定点数能够更加精确的保存和显示数据。

日期类型选择 ​

  • TIMESTAMP 和时区相关，更能反映当前时间，如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。
  • DATE 用于表示年月日，如果实际应用值需要保存年月日的话就可以使用 DATE。
  • TIME 用于表示时分秒，如果实际应用值需要保存时分秒的话就可以使用 TIME。
  • YEAR 用于表示年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。 默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

字符集 ​

字符集是否定长编码方式
ASCII是单字节 7 位编码
ISO-8859-1是单字节 8 位编码
GBK是双字节编码
UTF-8否1 - 4 字节编码
UTF-16否2 字节或 4 字节编码
UTF-32是4 字节编码

查看方法

sql
select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;

索引 ​

对MySQL索引的理解？ ​

  • 索引（Index）是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构

  • 索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。

  • 所有的 MySQL 类型都可以进行索引，对相关列使用索引是提高 SELECT 查询性能的最佳途径。MyISAM 和 InnoDB 都是使用 BTREE 作为索引，MySQL 5 不支持函数索引，但是支持 前缀索引。(前缀索引顾名思义就是对列字段的前缀做索引，前缀索引的长度和存储引擎有关系。MyISAM 前缀索引的长度支持到 1000 字节，InnoDB 前缀索引的长度支持到 767 字节，索引值重复性越低，查询效率也就越高。)

  • 可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。

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

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

索引类型 ​

  • 数据结构角度

    • 全局索引(FULLTEXT)：全局索引，目前只有 MyISAM 引擎支持全局索引，它的出现是为了解决针对文本的模糊查询效率较低的问题，并且只限于 CHAR、VARCHAR 和 TEXT 列。

    • 哈希索引(HASH)：哈希索引是 MySQL 中用到的唯一 key-value 键值对的数据结构，很适合作为索引。HASH 索引具有一次定位的好处，不需要像树那样逐个节点查找，但是这种查找适合应用于查找单个键的情况，对于范围查找，HASH 索引的性能就会很低。默认情况下，MEMORY 存储引擎使用 HASH 索引，但也支持 BTREE 索引。

    • B-Tree 索引：B 就是 Balance 的意思，BTree 是一种平衡树，它有很多变种，最常见的就是 B+ Tree，它被 MySQL 广泛使用。

    • R-Tree 索引：R-Tree 在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种，相对于 B-Tree 来说，R-Tree 的优势在于范围查找。

  • 物理存储角度

    • 聚集索引（clustered index）

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

    • 聚集索引和非聚集索引都是B+树结构

  • 逻辑角度

    • 主键索引：主键索引是一种特殊的唯一索引，不允许有空值

    • 普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引

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

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

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

索引的优劣势 ​

  • 优势

    • 提高数据检索效率，降低数据库IO成本

    • 降低数据排序的成本，降低CPU的消耗

  • 劣势

    • 索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存

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

索引设计原则与使用场景 ​

  • 选择索引位置，选择索引最合适的位置是出现在 where 语句中的列，而不是 select 关键字后的选择列表中的列。

  • 选择使用唯一索引，可以更快速的确定某条记录，例如学生的学号就适合使用唯一性索引，而学生的性别则不适合使用，因为不管搜索哪个值，都差不多有一半的行。

  • 为经常使用的字段建立索引，如果某个字段经常用作查询条件，那么这个字段的查询速度在极大程度上影响整个表的查询速度，因此为这样的字段建立索引，可以提高整个表的查询速度。

  • 不要过度索引，限制索引数目，索引的数目不是越多越好，每个索引都会占据磁盘空间，索引越多，需要的磁盘空间就越大。

  • 尽量使用前缀索引，如果索引的值很长，那么查询速度会受到影响，这个时候应该使用前缀索引，对列的某几个字符进行索引，可以提高检索效率。

  • 利用最左前缀，在创建一个 n 列的索引时，实际上是创建了 MySQL 可利用的 n 个索引。多列索引可以起到几个索引的作用，利用索引最左边的列来匹配行，这样的列称为最左前缀。

  • 对于使用 InnoDB 存储引擎的表来说，记录会按照一定的顺序保存。如果有明确的主键定义，那么会按照主键的顺序进行保存；如果没有主键，但是有唯一索引，那么就按照唯一索引的顺序进行保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序进行保存。一般来说，使用主键的顺序是最快的

  • 删除不再使用或者很少使用的索引

  • 需要创建索引的情况：

    • 主键自动建立唯一索引

    • 频繁作为查询条件的字段

    • 查询中与其他表关联的字段，外键关系建立索引

    • 单键/组合索引的选择问题，高并发下倾向创建组合索引

    • 查询中排序的字段，排序字段通过索引访问大幅提高排序速度

    • 查询中统计或分组字段

  • 不要创建索引的情况：

    • 表记录太少

    • 经常增删改的表

    • 数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）

    • 频繁更新的字段不适合创建索引（会加重IO负担）

    • where条件里用不到的字段不创建索引

索引使用 ​

  • 创建：

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

      如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

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

  • 删除：DROP INDEX [indexName] ON mytable;

  • 查看：SHOW INDEX FROM table_name\G --可以通过添加 \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 ，用于全文索引。

视图 ​

视图的理解 ​

  • 它是一种虚拟存在的表，它并不在数据库中实际存在，视图是使用数据库行和列动态组成的表。

  • 视图相对于普通的表有如下优势：

    • 使用视图可以简化操作：使用视图我们不用关注表结构的定义，我们可以把经常使用的数据集合定义成视图，这样能够简化操作。
    • 安全性：用户对视图不可以随意的更改和删除，可以保证数据的安全性。
    • 数据独立性：一旦视图的结构 确定了， 可以屏蔽表结构变化对用户的影响， 数据库表增加列对视图没有影响；具有一定的独立性

视图操作 ​

  • 创建：create view v1 as select * from product;
  • 查看所有视图：show tables;
  • 删除：drop view v1;
  • 查看视图结构：describe v1;
  • 更新视图：update v1 set name = "grape" where id = 1;

查询 ​

count(*)和count(1)和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条件的叠加

  • 如果查询的两个表大小相当，那么用in和exists差别不大。

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

UNION和UNIONALL的区别? ​

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

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

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

SQL执行顺序 ​

  • 手写

    sql
    SELECT DISTINCT <select_list>
FROM  <left_table> <join_type>
JOIN  <right_table> ON <join_condition>
WHERE  <where_condition>
GROUP BY  <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

  • 机读

    sql
    FROM  <left_table>
ON <join_condition>
<join_type> JOIN  <right_table> 
WHERE  <where_condition>
GROUP BY  <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

  • 顺序

    from/on->join/where->group by/having->select/order by->limit

各种join的区别 ​

join

存储过程 ​

存储过程理解 ​

  • 存储过程是在数据库系统中完成一组特定功能的 SQL 语句集，它存储在数据库系统中，一次编译后永久有效。

  • 优点：

    • 使用存储过程具有可封装性，能够隐藏复杂的 SQL 逻辑。
    • 存储过程可以接收参数，并返回结果
    • 存储过程性能非常高，一般用于批量执行语句

  • 缺点：

    • 存储过程编写复杂
    • 存储过程对数据库的依赖性比较强，可移植性比较差

存储过程使用 ​

  • 创建

    sql
    -- delimiter 自定义符号 包裹的部分执行完才有结果
delimiter $$
create procedure sp_product()
begin
select * from product;
end $$

  • 调用和传参

    sql
    call sp_product(2)

  • 删除

    sql
    drop procedure sp_product ;

  • 查看

    sql
    show create procedure proc_name;

  • 变量

    • 用户变量

      用户变量是基于会话变量实现的，可以暂存，用户变量与连接有关，也就是说一个客户端定义的变量不能被其他客户端使用看到。当客户端退出时，链接会自动释放。

      sql
      set @myId = "cxuan";

    • 局部变量

      作用域是所在的存储过程，使用 declare 来声明

    • 会话变量

      服务器会为每个连接的客户端维护一个会话变量。

      查看和使用

      sql
      show session variables;
set session auto_increment_increment=1;
--或者使用
set @@session.auto_increment_increment=2;

    • 全局变量

      当服务启动时，它将所有全局变量初始化为默认值。其作用域为 server 的整个生命周期。

      查看和使用

      sql
      show global variables;
set global sql_warnings=ON;
--或者使用
set @@global.sql_warnings=OFF;

  • 流程语句

    • IF

      txt
      IF ... THEN ...

    • CASE

      txt
      CASE ...
    WHEN ... THEN...
    ...
END CASE

    • LOOP

      txt
      label:LOOP
    ...
END LOOP label;

    • LEAVE

      用来表示从标注的流程构造中退出，通常和 BEGIN...END 或者循环一起使用

    • ITERATE

      ITERATE 语句必须用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。

    • REPEAT相当于do while

      txt
      REPEAT
    ... 
    UNTIL
END REPEAT;

    • WHILE与repeat类似

触发器 ​

触发器一般作用在表上，在满足定义条件时触发，并执行触发器中定义的语句集合，比如你有一个日志表和金额表，你每录入一笔金额就要进行日志表的记录

  • 触发器的作用

    • 在添加一条数据前，检查数据是否合理，例如检查邮件格式是否正确
    • 删除数据后，相当于数据备份的作用
    • 可以记录数据库的操作日志，也可以作为表的执行轨迹

  • 触发器的使用有两个限制

    • 触发程序不能调用将数据返回客户端的存储程序。也不能使用 CALL 语句的动态 SQL 语句。
    • 不能在触发器中开始和结束语句，例如 START TRANSACTION

  • 创建

    时机+事件组成6种触发器

    sql
    create trigger 触发器的名字 触发器触发时机(BEFORE 还是 AFTER) 触发器触发事件(INSERT、UPDATE 或者 DELETE) on 触发器创建的表名 for each row 触发器的程序体(sql语句)

  • 删除

    sql
    drop trigger tg_pinfo;

  • 查看

    sql
    show triggers

事务 ​

事务的理解 ​

  • 事务主要用于处理操作量大，复杂度高的数据。例如，在人员管理系统中，删除一个人员，需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样形成一个事务

  • 事务是由一组SQL语句组成的逻辑处理单元，具有4个属性，通常简称为事务的ACID属性。

    1. A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样

    2. C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏

    3. I (Isolation)隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰

    4. 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就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

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

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

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

  • 并发事务处理带来的问题的解决办法：

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

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

      • 一种是加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。

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

事务隔离级别 ​

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

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

      读未提交，就是一个事务可以读取另一个未提交事务的数据。

      事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

      分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

      解决脏读:Read committed 读提交。

    • READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

      读提交，就是一个事务要等另一个事务提交后才能读取数据。

      事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候，程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的

      分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

      解决可能的不可重复读问题:Repeatable read

    • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

      重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。 MySQL的默认事务隔离级别

      事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

      分析：重复读可以解决不可重复读问题。不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

      事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

      解决幻读问题：Serializable

    • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

      Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。简单来说，Serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

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

    sql
    show variables like 'tx_isolation'

    InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

    可以通过SELECT @@tx_isolation;命令来查看

    MySQL 8.0 该命令改为SELECT @@transaction_isolation;

    与 SQL 标准不同的地方在于InnoDB 存储引擎在 **REPEATABLE-READ（可重读）**事务隔离级别下使用的是Next-Key Lock 算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 **SERIALIZABLE(可串行化)**隔离级别，而且保留了比较好的并发性能。

    因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读已提交):，但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。

MVCC多版本并发控制 ​

MySQL的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是实现机制各不相同。

可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。

MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事物看到的数据都是一致的。

典型的MVCC实现方式，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。

InnoDB的简化版行为来说明 MVCC 是如何工作的：

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

可重读隔离级别下MVCC如何工作 ​

  • SELECT

    InnoDB会根据以下两个条件检查每行记录：

    1. InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在要么是事务自身插入或者修改过的

    2. 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除

    只有符合上述两个条件的才会被查询出来

  • 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。

事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。

  • 事务日志包括：重做日志redo和回滚日志undo：
  1. 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按语句的执行顺序，依次交替的记录在一起。

  1. undo log（回滚日志） 实现一致性

undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录redo log，还会记录一定量的undo log。

undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

Undo记录的是已部分完成并且写入硬盘的未完成的事务，默认情况下回滚日志是记录下表空间中的（共享表空间或者独享表空间）

  1. 对比

二种日志均可以视为一种恢复操作，redo_log是恢复提交事务修改的页操作，而undo_log是回滚行记录到特定版本。

二者记录的内容也不同，redo_log是物理日志，记录页的物理修改操作，而undo_log是逻辑日志，根据每行记录进行记录。

  • MySQL 有多少种日志

  1. 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。

  2. 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。

  3. 慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。

  4. 二进制日志：记录对数据库执行更改的所有操作。

  5. 中继日志：中继日志也是二进制日志，用来给slave 库恢复

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

MySQL对分布式事务的支持 ​

分布式事务的实现方式有很多，既可以采用 InnoDB 提供的原生的事务支持，也可以采用消息队列来实现分布式事务的最终一致性。

MySQL 从 5.0.3 InnoDB 存储引擎开始支持XA协议的分布式事务。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。

在MySQL中，使用分布式事务涉及一个或多个资源管理器和一个事务管理器。

  • MySQL 的分布式事务模型。模型中分三块：应用程序（AP）、资源管理器（RM）、事务管理器（TM）:
  1. 应用程序：定义了事务的边界，指定需要做哪些事务；
  2. 资源管理器：提供了访问事务的方法，通常一个数据库就是一个资源管理器；
  3. 事务管理器：协调参与了全局事务中的各个事务。
  • 分布式事务采用两段式提交（two-phase commit）的方式：

  1. 第一阶段所有的事务节点开始准备，告诉事务管理器ready。

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

锁 ​

锁的分类 ​

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

    1. 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响

    2. 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁

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

    1. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；

    2. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；

    3. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

MyISAM表锁 ​

MyISAM 的表锁有两种模式：

  1. 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

  2. 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；

MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待，直到锁被释放为止。

默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。

InnoDB行锁 ​

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

  1. 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

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

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

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

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

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

  • 加锁机制

乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题

  1. 乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式

  2. 悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

  • 锁模式(InnoDB有三种行锁的算法)
  1. 记录锁(Record Locks)： 单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
sql
SELECT * FROM table WHERE id = 1 FOR UPDATE;

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行

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

sql
-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;
  1. 间隙锁（Gap Locks）： 当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙”。InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于Next-Key Locking 算法，使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

sql
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锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况

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

通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

  • select for update有什么含义，会锁表还是锁行还是其他?

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

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

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

  1. 明确指定主键，并且有此笔资料，行锁
sql
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;
  1. 明确指定主键，若查无此笔资料，无锁
sql
SELECT * FROM products WHERE id='-1' FOR UPDATE;
  1. 无主键，表锁
sql
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
  1. 主键不明确，表锁
sql
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
  1. 主键不明确，表锁
sql
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

如何解决死锁 ​

  • 死锁产生

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

  • 检测死锁

    数据库系统实现了各种死锁检测和死锁超时的机制。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获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
    • 改变事务隔离级别

  • 确定最后一个死锁产生的原因

    sql
    show engine innodb status;

    返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

调优 ​

影响mysql的性能因素 ​

  1. 业务需求对MySQL的影响(合适合度)

  2. 存储定位对MySQL的影响

    • 不适合放进MySQL的数据

      • 二进制多媒体数据
      • 流水队列数据
      • 超大文本数据

    • 需要放进缓存的数据

      • 系统各种配置及规则数据
      • 活跃用户的基本信息数据
      • 活跃用户的个性化定制信息数据
      • 准实时的统计信息数据
      • 其他一些访问频繁但变更较少的数据

  3. Schema设计对系统的性能影响

    • 尽量减少对数据库访问的请求
    • 尽量减少无用数据的查询请求

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

性能分析 ​

  1. 查询优化

    • MySQL 中有专门负责优化 SELECT 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是 DBA 认为是最优的，这部分最耗费时间）

    • 当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimize r时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。

  2. 常见瓶颈

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

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

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

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

    • 查询语句写的烂

    • 索引失效（单值、复合）

    • 关联查询太多join（设计缺陷或不得已的需求）

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

  4. 常见性能分析手段

    常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

    • 性能瓶颈定位

      通过 show 命令查看 MySQL 状态及变量

      sql
      Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）

Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）

Mysql> show innodb status ——显示InnoDB存储引擎的状态

Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

Shell> mysqladmin variables -u username -p password——显示系统变量

Shell> mysqladmin extended-status -u username -p password——显示状态信息

    • Explain(执行计划)

      使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈

      • 能处理的事情

        • 表的读取顺序
        • 数据读取操作的操作类型
        • 哪些索引可以使用
        • 哪些索引被实际使用
        • 表之间的引用
        • 每张表有多少行被优化器查询

      • 使用

        1. Explain + SQL语句

        2. 查看执行计划包含的信息（如果有分区表的话还会有partitions）

          • 字段解释：

            • id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）

              • id相同，执行顺序从上往下
              • id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
              • id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行

            • select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）

              • SIMPLE ：简单的select查询，查询中不包含子查询或UNION
              • PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
              • SUBQUERY：在select或where列表中包含了子查询
              • DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
              • UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
              • UNION RESULT：从UNION表获取结果的select

            • table（显示这一行的数据是关于哪张表的）

            • type（显示查询使用了那种类型，从最好到最差依次排列：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

              • system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现
              • const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量
              • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
              • ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体
              • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引
              • index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
              • ALL：Full Table Scan，将遍历全表找到匹配的行

              一般来说，得保证查询至少达到range级别，最好到达ref

            • possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）

            • key

              • 实际使用的索引，如果为NULL，则没有使用索引

              • 查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中

            • key_len

              • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
              • key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

            • ref （显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

            • rows （根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）

            • Extra（包含不适合在其他列中显示但十分重要的额外信息）

              • using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中

              • Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

              • using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作

              • using where：使用了where过滤

              • using join buffer：使用了连接缓存

              • impossible where：where子句的值总是false，不能用来获取任何元祖

              • select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

              • distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

    • 慢查询日志

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

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

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

      查看开启状态：

      sql
      SHOW VARIABLES LIKE '%slow_query_log%'

      开启慢查询日志:

      • 临时配置：(​使用set操作开启慢查询日志只对当前数据库生效，如果MySQL重启则会失效。)

        sql
        set global slow_query_log='ON';
set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
set global long_query_time=2;

      • 永久配置

        修改配置文件my.cnf或my.ini，在[mysqld]一行下面加入两个配置参数

        txt
        [mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3

        可以用 select sleep(4) 验证是否成功开启。

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

      通过 mysqldumpslow --help 查看操作帮助信息

      • 得到返回记录集最多的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

      也可使用 pt-query-digest 分析 RDS MySQL 慢查询日志

    • Show Profile 分析查询

      Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

      默认情况下，参数处于关闭状态，并保存最近15次的运行结果

      分析步骤:

      1. 是否支持，看看当前的mysql版本是否支持

        sql
        Show  variables like 'profiling';  --默认是关闭，使用前需要开启

      2. 开启功能，默认是关闭，使用前需要开启

        sql
        set profiling=1;

      3. 运行SQL

      4. 查看结果

      5. 诊断SQL，show profile cpu,block io for query id(上一步前面的问题SQL数字号码)

      6. 日常开发需要注意的结论

        • converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。

        • create tmp table 创建临时表，这个要注意

        • Copying to tmp table on disk 把内存临时表复制到磁盘

        • locked

性能优化 ​

  1. 索引优化

  • 全值匹配

  • 最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)

  • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

  • 存储引擎不能使用索引中范围条件右边的列

  • 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select

  • is null ,is not null 也无法使用索引

  • like "xxxx%" 是可以用到索引的，like "%xxxx" 则不行(like "%xxx%" 同理)。like以通配符开头('%abc...')索引失效会变成全表扫描的操作，

  • 字符串不加单引号索引失效

  • 少用or，用它来连接时会索引失效

  • <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描

  • 一般性建议:

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

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

    当 A 表的数据集小于B表的数据集时(AB表id建立了索引)，用exists优,反之用in优

    sql
    select * from A where exists (select * from B where B.id=A.id)
slect * from A where id in (select id from B)

  • 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限定了

  • 数据类型优化

    • 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。

    • 简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。

    • 尽量避免NULL：通常情况下最好指定列为NOT NULL

数据划分 ​

MySQL分区 ​

一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI和.MYD文件，使用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。

当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率

  • 分区作用

    • 逻辑数据分割
    • 提高单一的写和读应用速度
    • 提高分区范围读查询的速度
    • 分割数据能够有多个不同的物理文件路径
    • 高效的保存历史数据

  • 使用

    • 查看当前数据库是否支持分区

      • MySQL5.6以及之前版本：SHOW VARIABLES LIKE '%partition%';
      • MySQL5.6：show plugins;

    • 分区类型及操作

      • RANGE分区

        基于属于一个给定连续区间的列值，把多行分配给分区。mysql将会根据指定的拆分策略，,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。扩容的时候很简单。

      • LIST分区

        LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值

      • HASH分区

        基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

        好处在于可以平均分配每个库的数据量和请求压力；

        坏处在说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表

      • KEY分区

        只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

为什么选择自己分库分表来水平扩展？ ​

  • 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
  • 一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
  • 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎样的，不太可控

高并发读写操作均超过单个数据库服务器的处理能力怎么办？ ​

这个时候就出现了数据分片，数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中。数据分片的有效手段就是对关系型数据库进行分库和分表。

区别于分区的是，分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

MySQL分表 ​

  1. 垂直拆分

    垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

  2. 水平拆分(数据分片)

    单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。

    水平分割的几种方法：

    • 使用MD5哈希，做法是对UID进行md5加密，然后取前几位，然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。

    • 还可根据时间放入不同的表，比如：article_201601，article_201602。

    • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。

    • 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

MySQL分库 ​

为什么要分库? ​

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

分库是什么？ ​

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

  • 优点：

    • 减少增量数据写入时的锁对查询的影响

    • 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

  • 缺点

    • 无法解决单表数据量太大的问题

分库分表后有什么问题？ ​

分布式事务的问题，数据的完整性和一致性问题。

数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。

跨库联合查询的问题，可能需要两次查询 跨节点的count、order by、group by

聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并

额外的数据管理负担，如：访问数据表的导航定位 额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升

没有太好的框架解决，更多依赖业务看如何分，如何合

主从复制 ​

复制的基本原理 ​

slave 会从 master 读取 binlog 来进行数据同步

三个步骤

  1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；

  2. salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）;

  3. slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

复制的基本原则 ​

  • 每个 slave只有一个 master
  • 每个 salve只能有一个唯一的服务器 ID
  • 每个master可以有多个salve

复制的最大问题 ​

延时

三个范式 ​

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y

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

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

  1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
  2. 然后删除其中无用数据（此过程需要不到两分钟）
  3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
  4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。